有关mysql的SQL语句,求帮组!

发布网友 发布时间：2022-05-02 06:29

我来回答

共3个回答

懂视网 时间：2022-05-02 10:51

（1）分清HAVING与WHERE的区别：

HAVING 子句使你能够指定过滤条件，从而控制查询结果中哪些组可以出现在最终结果里面。WHERE 子句对被选择的列施加条件，而 HAVING 子句则对 GROUP BY 子句所产生的组施加条件。

以下的SQL语句都是基于MySQL5.6.30版本。

 1、查询“1”课程比“2”课程成绩高的所有学生的学号 

select a.Sno from
(select Sno,score from SC where Cno=1 ) as a,
(select Sno,score from SC where Cno=2 ) as b
 where a.score>b.score and a.Sno=b.Sno;　

2、查询平均成绩大于60分的同学的学号和平均成绩

SELECT Sno,AVG(score) FROM SC
GROUP BY Sno HAVING AVG(score) >60;

3、查询所有同学的学号、姓名、选课数、总成绩

SELECT Student.Sno,Student.Sname,COUNT(SC.Cno),SUM(SC.score)
FROM Student 
LEFT OUTER JOIN SC ON Student.Sno=SC.Sno
GROUP BY Student.Sno,Sname;

group by是在左外连接的基础上进行分组。注意是查询所有同学的,使用如下就会使用内连接,如果一些学生无成绩将不显示。与题目不符合　　

4、查询姓“李”的老师的个数

SELECT COUNT(DISTINCT(Tname)) FROM Teacher
WHERE Tname LIKE ‘李%‘;

5、查询没学过“李小风”老师课的同学的学号、姓名

SELECT Student.Sno,Student.Sname
FROM Student 
WHERE Sno NOT IN 
(
 SELECT DISTINCT(SC.Sno) FROM SC,Course,Teacher 
 WHERE SC.Cno=Course.Cno AND Teacher.Tno=Course.Tno AND Teacher.Tname=‘李小风‘
);

6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名

SELECT Student.Sno,Student.Sname 
FROM Student,SC
WHERE Student.Sno=SC.Sno 
AND SC.Cno=1
AND EXISTS( SELECT * FROM SC AS SC_2 WHERE SC_2.Sno=SC.Sno AND SC_2.Cno=2 )
-- 查询同时学过1、2、3的课程
-- AND EXISTS( SELECT * FROM SC AS SC_3 WHERE SC_3.Sno=SC.Sno AND SC_3.Cno=3 )

7、查询学过“李小风”老师所教的“所有”课的同学的学号、姓名

SELECT Sno,Sname
FROM Student
WHERE Sno IN (
 SELECT Sno 
 FROM SC,Course ,Teacher 
 WHERE SC.Cno=Course.Cno AND Teacher.Tno=Course.Tno AND Teacher.Tname=‘李小风‘
 GROUP BY Sno HAVING COUNT(SC.Cno)=(
    SELECT COUNT(Cno) FROM Course,Teacher
    WHERE Teacher.Tno=Course.Tno AND Tname=‘李小风‘
    )
);

8、查询所有课程成绩小于60分的同学的学号、姓名（无选课无成绩的也将显示出来）；

SELECT Sno,Sname
FROM Student
WHERE Sno NOT IN (
   SELECT Student.Sno FROM Student,SC WHERE Student.Sno=SC.Sno AND score>60
   );

9、查询没有学全所有课的同学的学号、姓名；

SELECT Student.Sno,Student.Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno 
GROUP BY Student.Sno,Student.Sname 
HAVING COUNT(SC.Cno) <(SELECT COUNT(Course.Cno) FROM Course );

10、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

SELECT s.Sno,s.Sname 
FROM Student s 
GROUP BY s.Sno,s.Sname HAVING s.Sno IN ( SELECT Course.Cno FROM Course,SC WHERE SC.Sno=1 )

SELECT s.Sno,s.Sname 
FROM Student s,SC sc 
WHERE s.Sno=sc.Sno AND sc.Cno IN ( SELECT Course.Cno FROM Course,SC WHERE SC.Sno=1 ) 
GROUP BY s.Sno

11、把“SC”表中“李力”老师教的课的成绩都更改为此课程的平均成绩；

UPDATE SC,Course,Teacher 
SET SC.score=( SELECT AVG(SC_2.score) FROM (SELECT * FROM SC) AS SC_2 WHERE SC_2.Cno=SC.Cno )
WHERE Course.Cno=SC.Cno AND Course.Tno=Teacher.Tno AND Teacher.Tname=‘李力‘;

12、查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名；

SELECT Sno 
FROM SC 
WHERE Cno IN(SELECT Cno FROM SC WHERE Sno=2)
GROUP BY Sno HAVING COUNT(*)=(SELECT COUNT(*) FROM SC WHERE Sno=2);

13、删除学习“李力”老师课的SC表记录；

 DELETE SC
 FROM Course,Teacher,SC
 WHERE Course.Cno=SC.Cno AND Course.Tno=Teacher.Tno AND Tname=‘李力‘;

14、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“3”课程的同学学号、2号课的平均成绩；

INSERT SC 
 SELECT Sno,2,
 (
 SELECT AVG(score) FROM SC WHERE Cno=2
 ) 
 FROM Student WHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno=3);

15、按平均成绩从高到低显示所有学生的“高数”、“C语言”、“Java高级程序设计”三门的课程成绩，按如下形式显示： 学生ID,高数,C语言,Java高级程序设计,有效课程数,有效平均分

SELECT Sno AS 学生ID
 ,(SELECT score FROM SC WHERE SC.Sno=t.Sno AND Cno=4) AS 高数
 ,(SELECT score FROM SC WHERE SC.Sno=t.Sno AND Cno=1) AS C语言
 ,(SELECT score FROM SC WHERE SC.Sno=t.Sno AND Cno=6) AS Java程序高级设计
 ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
 FROM SC AS t
 GROUP BY Sno
 ORDER BY AVG(t.score)

16、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 

 SELECT SC.Cno,MAX(score),MIN(score)
 FROM SC 
 GROUP BY Cno

17、按各科平均成绩从低到高和及格率的百分数从高到低顺序

SELECT 
 t.Cno AS 课程号,
 MAX(c.Cname) AS 课程名,
 IFNULL(AVG(t.score),0) AS 平均成绩,
 100 * SUM(CASE WHEN IFNULL(t.score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
FROM SC t,Course c
WHERE t.Cno=c.Cno
GROUP BY t.Cno
ORDER BY 及格百分数 DESC 
 

18、查询不同老师所教不同课程平均分从高到低显示

SELECT 
	MAX(Z.Tno) AS 教师ID,
	MAX(Z.Tname) AS 教师姓名,
	C.Cno AS 课程ID,
	C.Cname AS 课程名称,
	AVG(Score) AS 平均成绩 
FROM SC AS T,Course AS C ,Teacher AS Z 
WHERE T.Cno=C.Cno AND C.Tno=Z.Tno 
GROUP BY C.Cno 
ORDER BY 平均成绩 DESC

19、查询如下课程成绩第3名到第6名的学生成绩单：

高数（1），C语言（2），Java高级程序设计（3），数据结构与算法（4）

学生ID,学生姓名,高数,C语言,Java高级程序设计,数据结构与算法,平均成绩

SELECT 
 SC.Sno AS 学生学号, 
 Student.Sname AS 学生姓名, 
 T1.score AS 高数, 
 T2.score AS C语言, 
 T3.score AS Java高级程序设计, 
 T4.score AS 数据结构与算法, 
 IFNULL(T1.score,0) +IFNULL(T2.score,0) +IFNULL(T3.score,0) +IFNULL(T4.score,0) AS 总分 
 FROM Student,SC 
  LEFT JOIN SC AS T1 ON SC.Sno = T1.Sno AND T1.Cno =1
  LEFT JOIN SC AS T2 ON SC.Sno = T2.Sno AND T2.Cno =2
  LEFT JOIN SC AS T3 ON SC.Sno = T3.Sno AND T3.Cno =3
  LEFT JOIN SC AS T4 ON SC.Sno = T4.Sno AND T4.Cno =4
 WHERE Student.Sno=SC.Sno 
 GROUP BY SC.Sno
 ORDER BY 总分 DESC
 LIMIT 3,3

20、统计列打印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

SELECT SC.Cno AS 课程ID, Cname AS 课程名称 
 ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS ‘[100 - 85]‘
 ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS ‘[85 - 70]‘
 ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS ‘[70 - 60]‘
 ,SUM(CASE WHEN score <60 THEN 1 ELSE 0 END) AS ‘[60 -]‘
 FROM SC,Course 
 WHERE SC.Cno=Course.Cno
 GROUP BY SC.Cno,Cname; 

21、查询学生平均成绩及其名次

SELECT 1+( SELECT COUNT(DISTINCT 平均成绩) 
	 FROM ( SELECT Sno,AVG(score) AS 平均成绩 FROM SC GROUP BY Sno ) AS T1 
	 WHERE 平均成绩 > T2.平均成绩

 ) AS 名次, Sno AS 学生学号,平均成绩 
FROM (SELECT Sno,AVG(score) AS 平均成绩 FROM SC GROUP BY Sno) AS T2 
ORDER BY 平均成绩 DESC; 

22、查询各科成绩前三名的记录:(不考虑成绩并列情况)

SELECT t1.Sno AS 学生ID,t1.Cno AS 课程ID,Score AS 分数 
FROM SC t1 
WHERE score IN (
	 SELECT score FROM SC 
	 WHERE t1.Cno=Cno 
	 ORDER BY score DESC
	 LIMIT 0,3 
) 
ORDER BY t1.Cno; 

不支持子查询中使用limit关键字，报错如下：

This version of MySQL doesn‘t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery‘

23、查询每门课程成绩最好的前两名

24、查询出只选修了一门课程的全部学生的学号和姓名

 SELECT SC.Sno,Student.Sname,COUNT(Cno) AS 选课数 
 FROM SC ,Student 
 WHERE SC.Sno=Student.Sno 
 GROUP BY SC.Sno ,Student.Sname HAVING COUNT(Cno)=1; 

25、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)

SELECT Sname,CAST(DATE_FORMAT(Sbirthday,‘%Y-%m-%d‘) AS CHAR(13)) AS birthday 
FROM Student
WHERE CAST(DATE_FORMAT(Sbirthday,‘%Y-%m-%d‘) AS CHAR(13))=‘2015-09-16‘ ; 

26、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按

课程号降序排列 　　

SELECT Cno,AVG(score) FROM SC GROUP BY Cno ORDER BY AVG(score),Cno DESC ;

27、查询选修“李小风”老师所授课程的学生中，成绩最高的学生姓名及其成绩

SELECT Student.Sname,score 
FROM Student,SC,Course C,Teacher 
WHERE Student.Sno=SC.Sno AND SC.Cno=C.Cno AND C.Tno=Teacher.Tno AND Teacher.Tname=‘李小风‘ AND 
SC.score=(SELECT MAX(score) FROM SC WHERE Cno=C.Cno );
-- 如下的语句是错误的
SELECT Student.Sname,score 
FROM Student,SC,Course C,Teacher 
WHERE Student.Sno=SC.Sno AND SC.Cno=C.Cno AND C.Tno=Teacher.Tno AND Teacher.Tname=‘李小风‘ 
ORDER BY SC.score DESC 
LIMIT 1

第二条语句当李小风老师教授多个课程时，查询出来的结果应该是多条的。　

28、选修至少两门课程的同学的Sno与Sname

select std.Sno,std.Sname from student std,SC sc where std.Sno=sc.Sno group by std.Sno having count(distinct(sc.Cno))>2

count()中可以使用distinct()函数 

剑指架构师系列-MySQL常用SQL语句

标签：题目   插入   百分数   distinct   like   就会   设计   类型   学习   

热心网友 时间：2022-05-02 07:59

热心网友 时间：2022-05-02 09:17