MySQL基础(八)-实战演练经典50题

MySQL经典50题（必做！建议收藏） – 知乎

上面连接是我们的题目，里面包含了初始数据和初始数据的代码。下面对其中的题目进行分析。

目录

1、查询”01″课程比”02″课程成绩高的学生的信息及课程分数

2、查询”01″课程比”02″课程成绩低的学生的信息及课程分数

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

6、查询”李”姓老师的数量

7、查询学过”李鹏飞”老师授课的同学的信息

8、查询没有学过”李鹏飞”老师授课的同学的信息

9、查询学过编号为”01″并且也学过编号为”02″的课程的同学的信息

10、查询学过编号为”01″并且没有学过编号为”02″的课程的同学的信息

11、查询没有学全所有课程的同学的信息

12、查询至少有一门课与学号为”01″的同学所学相同的同学的信息

13、查询和”01″号的同学学习的课程完全相同的其他同学的信息

14、查询没学过”wang wen”老师讲授的任一门课程的学生姓名

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

16、检索”01″课程分数小于60，按分数降序排列的学生信息

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

19、按各科成绩进行排序，并显示排名

20、查询学生的总成绩并进行排名

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

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

23、统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比

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

25、查询各科成绩前三名的记录

26、查询每门课程被选修的学生数

27、查询出只有两门课程的全部学生的学号和姓名

28、查询男生、女生人数

29、查询名字中含有”风”字的学生信息

30、查询同名同性学生名单，并统计同名人数

31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

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

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

34、查询课程名称为”数学”，且分数低于60的学生姓名和分数

35、查询所有学生的课程及分数情况

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

37、查询不及格的课程

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

39、求每门课程的学生人数

40、查询选修”张三”老师所授课程的学生中，成绩最高的学生信息及其成绩

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

42、查询每门功课成绩最好的前两名

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

44、检索至少选修两门课程的学生学号

45、查询选修了全部课程的学生信息

46、查询各学生的年龄

47、查询本周过生日的学生

48、查询下周过生日的学生

49、查询本月过生日的学生

50、查询下月过生日的学生

1、查询”01″课程比”02″课程成绩高的学生的信息及课程分数

第一步：内连接，筛选出01课程与02课程

selectsc1.*,sc2.* fromsc sc1 joinsc sc2 onsc1.sid=sc2.sid andsc1.cid=01 and sc2.cid=02 ;

2、第二步：外连接，将成绩表与学生表进行连接

selectstu.*,sc1.*,sc2.cid,sc2.score fromsc sc1 joinsc sc2 onsc1.sid=sc2.sid and sc1.cid=01 and sc2.cid=02 left joinstudent stu onsc1.sid=stu.sid;

3、第三步：进行条件判断，筛选出01课程课程分数>02课程课程分数的行

selectstu.*,sc1.*,sc2.cid,sc2.score fromsc sc1 joinsc sc2 onsc1.sid=sc2.sid and sc1.cid=01 and sc2.cid=02 left joinstudent stu onsc1.sid=stu.sid where sc1.score>sc2.score;

2、查询”01″课程比”02″课程成绩低的学生的信息及课程分数

和上题一样，更改判断条件即可

select stu.*,sc1.*,sc2.cid,sc2.score from sc sc1 join sc sc2 on sc1.sid=sc2.sid and sc1.cid=01 and sc2.cid=02 left join student stu on sc1.sid=stu.sid where sc1.score<sc2.score;

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

第一步：查询各位同学的平均成绩

select *,avg(score) from sc group by sc.SID;

第二步：外连接，将成绩与学生信息进行连接

select s.*,avg(score) from sc left join student s on sc.SID = s.SIDgroup by sc.SID;

第三步：对平均分进行判断

select s.*,avg(score) avgscore from sc left join student s on sc.SID = s.SIDgroup by sc.SID having avgscore>60;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

同上题，更改判断条件

select s.*,avg(score) avgscore from sc left join student s on sc.SID = s.SIDgroup by sc.SID having avgscore<60;

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

selects.sid,s.sname,sc.cid,sum(sc.score)fromstudent s left joinsc ons.sid=sc.sid group bys.sid;

6、查询”李”姓老师的数量

第一步：查询“李”姓老师

select t.* from teacher t where t.tname like 'li%';

第二步：查询字段的数量

select count(*) from teacher t where t.tname like 'li%';

7、查询学过”李鹏飞”老师授课的同学的信息

第一步：查询李鹏飞老师教授过的课程

selectt.*,c.* fromteacher t left joincourse c ont.tid=c.tid wheret.tname='li pengfei';

第二步：将课程信息与成绩表建立外连接

selectt.*,c.*,sc.sid fromteacher t leftjoin course c ont.tid=c.tid left joinsc onsc.cid=c.cid wheret.tname='li pengfei';

第三步：将成绩表与学生建立起外连接

selectt.*,c.cname,sc.sid,s.* fromteacher t left joincourse c ont.tid=c.tid left joinsc onsc.cid=c.cid .cid left joinstudent s onsc.sid = s.sid wheret.tname='li pengfei';

8、查询没有学过”李鹏飞”老师授课的同学的信息

selectt.*,c.cname,sc.sid,s.* fromteacher t leftjoin course c on t.tid=c.tid left join sc onsc.cid=c.cid left join student s on sc.sid = s.sid where t.tname!='li pengfei';

9、查询学过编号为”01″并且也学过编号为”02″的课程的同学的信息

select s.* from sc sc1 join sc sc2 onsc1.sid=sc2.sid andsc1.cid=01 andsc2.cid=02 left join student sons.sid=sc1.sid;

10、查询学过编号为”01″并且没有学过编号为”02″的课程的同学的信息

第一步：筛选出学过’01’课程的学生A

select s.*,sc1.* from student s join sc sc1 on s.SID = sc1.SID and sc1.CID='01'；

第二步：同理筛选出学过‘02’课程的学生B

select s.* from student s join sc sc2 on s.SID = sc2.SID and sc2.CID='02';

第三步：A∩(!B)，A集合中取出不包含B集合的内容

select s.*,sc1.* from student s join sc sc1 on s.SID = sc1.SID and sc1.CID='01' where s.SID not in(select s.SID from student s join sc sc2 on s.SID = sc2.SID and sc2.CID='02');

11、查询没有学全所有课程的同学的信息

由于需要对聚合函数进行判断，且聚合函数不能用于where语句后，所以通过having来进行判断。

select s.*,count(s2.CID) count from student s left join sc s2 on s.SID = s2.SID group by s2.SID having count<3;

这里可以通过一个子查询，来对count<3进行优化。

select s.*,count(s2.CID) count from student s left join sc s2 on s.SID = s2.SID group by s2.SID having count<(select count(*) from course);

12、查询至少有一门课与学号为”01″的同学所学相同的同学的信息

第一步：查找出学号为‘01’的同学学习的课程

select s2.CID from student s left join sc s2 on s.SID = s2.SID where s.SID='01';

第二步： 将上一步作为筛选条件，筛选出符合条件的学生信息

select s.* from student s left join sc s2 on s.SID = s2.SID where s2.CID in (select s2.CID from student s left join sc s2 on s.SID = s2.SID where s.SID='01');

第三步：将结果进行去重。有的答案在这里将学号为‘01’的学生去除了，思路一样，在最后判断的时候加一个判断条件SID!=’01’，其他一样。

select distinct s.* from student s left join sc s2 on s.SID = s2.SID where s2.CID in (select s2.CID from student s left join sc s2 on s.SID = s2.SID where s.SID='01');

13、查询和”01″号的同学学习的课程完全相同的其他同学的信息

第一步：查找出学号为‘01’的同学学习的课程。这一步和上一条一样，但是区别在于，需要将‘01’号学生的全部课程看作一个整体，进行判断。

于是引入了group_concat连接函数

select group_concat(s2.CID) course01 from student s left join sc s2 on s.SID = s2.SID where s.SID='01';

第二步： 用同样的方法，求其他学生学习的课程。

select s.sid, sname, sage, ssex,group_concat(s2.CID) course from student s left join sc s2 on s.SID = s2.SID group by s2.SID

第三步：两表数据进行筛选

select s.sid, sname, sage, ssex,group_concat(s2.CID) course from student s left join sc s2 on s.SID = s2.SID group by s2.SID having course=(select group_concat(s2.CID) course01 from student s left join sc s2 on s.SID = s2.SID where s.SID='01');

但这里出来没有数据，这是为什么呢？我们发现course列出来的数据与course01列出来的数据顺序不一致。导致筛选的时候认为这是两条数据，筛选失败。

很简单，我们只需要在拼接的时候对数据进行排序就可以啦~

select s.sid, sname, sage, ssex,group_concat(s2.CID order by s2.CID) course from student s left join sc s2 on s.SID = s2.SID group by s2.SIDhaving course=(select group_concat(s2.CID) course01 from student s left join sc s2 on s.SID = s2.SID where s.SID='01');

14、查询没学过”wang wen”老师讲授的任一门课程的学生姓名

第一步：查询“wang wen”老师教授过的学生

select s.sid from student s left join sc s2 on s.SID = s2.SID left join course c on s2.CID = c.CID left join teacher t on c.TID = t.TID where t.Tname='wang wen';

第二步：筛选学生清单中不在上面清单中的学生信息

select s.* from student s where s.SID not in(select s.sid from student s left join sc s2 on s.SID = s2.SID left join course c on s2.CID = c.CID left join teacher t on c.TID = t.TID where t.Tname='wang wen');

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

第一步：筛选出不及格的同学的信息

select s.*,s2.score from student s left join sc s2 on s.SID = s2.SID where score<60;

第二步：统计学生不及格的科目数量

select s.*,s2.score,count(*) countNo,avg(score) from student s left join sc s2 on s.SID = s2.SID where score=2;

16、检索”01″课程分数小于60，按分数降序排列的学生信息

select s.* from student s left join sc s2 on s.SID = s2.SID where s2.CID='01' and s2.score<60order by score desc;

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select s.sid,avg(s2.score) avg_score from student s left join sc s2 on s.SID = s2.SID group by s2.SID order by avg_score desc;

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

第一步：最高分、最低分和平均分。直接用聚合函数就可以实现

select s2.CID,c.Cname,max(score),min(score),avg(score),from student s join sc s2 on s.SID = s2.SID join course c on s2.CID = c.CID group by s2.CID;

第二步：求及格率。既然是xx率，那我们能想到的就是“满足条件的个数/总数”。

总数可以通过count(*)来实现，那么如何统计分数>=60的个数呢？我们可以在count()函数中添加判断条件来实现。

select s2.CID,c.Cname,max(score),min(score),avg(score), count(if(score>=60,1,null))/count(score) '及格率' from student s join sc s2 on s.SID = s2.SID join course c on s2.CID = c.CID group by s2.CID;

同理，中等率、优良率和优秀率我们同样可以通过这种方式来实现。

通过round()函数实现四舍五入。这里为了代码简洁，就只在平均分上加了round()函数

select s2.CID,c.Cname,max(score) '最高分',min(score) '最低分',round(avg(score),2) '平均分', count(if(score>=60,1,null))/count(score) '及格率', count(if(score>=70 and score=80 and score=90,1,null))/count(score) '优秀率' from student s join sc s2 on s.SID = s2.SID join course c on s2.CID = c.CID group by s2.CID;

19、按各科成绩进行排序，并显示排名

select sc.CID,row_number() over (partition by sc.CID order by score desc) `rank` ,sc.SID,s.sname,sc.score from sc join student s on sc.SID = s.SID ;

20、查询学生的总成绩并进行排名

select s.*,sum(s2.score) '总成绩' from student s left join sc s2 on s.SID = s2.SID group by s.SID

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

select t.*,avg(s.score) '平均成绩' from teacher t left join course c on t.TID = c.TID left join sc s on c.CID = s.CID group by t.TID

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

第一步：按照学生成绩进行排序

select cid,sid, score,row_number() over (partition by CID order byscore desc ) ranks from sc

第二步：该表只有单表的排名，学生信息需要通过联表查询获得

select s.*,t.* from student s left join(select cid,sid, score,row_number() over (partition by CID order byscore desc ) ranks from sc) ton s.SID=t.SID

第三步： 对课程排名进行筛选，筛选条件为排名为2、3名

select s.*,t.* from student s left join(select cid,sid, score,row_number() over (partition by CID order byscore desc ) ranks from sc) ton s.SID=t.SID where t.ranks in(2,3)

23、统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比

本题思路与18题类似，不过多赘述

select c.Cname,c.CID,truncate(count(if(score>85,1,null))/count(*),2) '85-100',truncate(count(if(score70,1,null))/count(*),2) '70-85',truncate(count(if(score60,1,null))/count(*),2) '60-70',truncate(count(if(score<60,1,null))/count(*),2) '0-60'from sc left join course c on sc.CID = c.CID group by sc.CID;

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

select stu.*,truncate(avg(score),2) '平均成绩',row_number() over (order by '平均成绩' desc) '排名' from student stu left join sc s on stu.SID = s.SID left join course c on s.CID = c.CID group by stu.SID

25、查询各科成绩前三名的记录

select s.*,t.* from student s left join(select sc.SID,sc.score,row_number() over (partition by sc.CID) ranks from sc) ton s.SID=t.SIDwhere t.ranks<=3;

26、查询每门课程被选修的学生数

select c.Cname,count(s2.CID) '学生数' from sc s2 left join student s on s.SID = s2.SIDleft join course c on s2.CID = c.CID group by s2.CID

27、查询出只有两门课程的全部学生的学号和姓名

select s.* from student s left join sc s2 on s.SID = s2.SID group by s.SID having count(s2.CID)<= 2

28、查询男生、女生人数

select s.Ssex,count(*) '人数' from student s group by s.Ssex

29、查询名字中含有”风”字的学生信息

select s.* from student s where s.Sname like '%风%'

30、查询同名同性学生名单，并统计同名人数

select s1.*,count(*) countNo from student s1 join student s2 on s1.Sname=s2.Sname and s1.Ssex=s2.Ssex group by s1.SID having countNo>1;

31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

select * from student where year(Sage)=1990;

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

select s2.cid,avg(score) avg_score fromsc s2 group by s2.CIDorder by avg_score desc,s2.CID asc;

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select s.*,avg(s2.score) avg_score from student s join sc s2 on s.SID = s2.SID group by s.SID having avg_score>=85;

34、查询课程名称为”数学”，且分数低于60的学生姓名和分数

select s2.Sname,s.score from course cjoin sc s on c.CID = s.CID and c.Cname='数学' and s.score<=60join student s2 on s.SID = s2.SID;

35、查询所有学生的课程及分数情况

select s.*,s2.score,c.Cname from student s left join sc s2 on s.SID = s2.SID left join course c on s2.CID = c.CID;

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

select s.Sname,c.Cname,s2.score from student s join sc s2 on s.SID = s2.SID and score>70 join course c on s2.CID = c.CID;

37、查询不及格的课程

select sc.SID,s.Sname,c.cName,sc.score from sc left join course c on sc.CID = c.CID left join student s on sc.SID = s.SID where sc.score<60;

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

select s.sid,sname,score from student s left join sc s2 on s.SID = s2.SIDwhere score>=80 and CID='01';

39、求每门课程的学生人数

select sc.cid '课程代码',c.cname '课程名称',count(*) '选课人数' from sc left join course c on sc.CID = c.CID group by sc.CID;

40、查询选修”张三”老师所授课程的学生中，成绩最高的学生信息及其成绩​​​​​​​

第一步：查询“张三”教授的学生最高成绩

select max(score) maxScore from student s join sc s2 on s.SID = s2.SIDjoin course c on s2.CID = c.CIDjoin teacher t on c.TID = t.TIDwhere t.Tname='张三' group by s2.CID;

第二步：多表联查，筛选出最高成绩的学生信息

select s.*,s2.score,t.Tname from student s join sc s2 on s.SID = s2.SID join course c on s2.CID = c.CID join teacher t on c.TID = t.TID where t.Tname='张三' and score=(select max(score) maxScore from student s join sc s2 on s.SID = s2.SIDjoin course c on s2.CID = c.CIDjoin teacher t on c.TID = t.TIDwhere t.Tname='张三' group by s2.CID);

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select distinct s.sid,s.sname,a.cid,a.score,b.cid,b.score from student sjoin sc a on a.SID=s.SIDjoin sc b on b.SID=s.SIDwhere a.CID!=b.CID and a.score=b.scoregroup by a.CID;

42、查询每门功课成绩最好的前两名

select s.*,t.* from student s left join(select sc.SID,sc.score,row_number() over (partition by sc.CID) ranks from sc) ton s.SID=t.SIDwhere t.ranks<=2;

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select a.cid,count(*) countNo from sc a join course c on a.CID = c.CID group by a.CID having countNo>5 order by countNo desc,a.CID;

44、检索至少选修两门课程的学生学号

select s.sid,s.Sname from sc join student s on sc.SID = s.SID group by s.SID having count(*)>=2

45、查询选修了全部课程的学生信息

select s.*,count(*) count from student sjoin sc on sc.SID = s.SID group by s.SID having count=(select count(*) from course);

46、查询各学生的年龄

select s.sid,s.sname,s.Sage,year(now())-year(s.Sage)+1 '年龄' from student s;

47、查询本周过生日的学生

这里是考察对日期函数的应用。与周相关的函数有 “week” 和 “weekofyear”，两者使用上差不多，结果上有些许区别。

如2023年1月15日：

week函数是以周日作为第一天，即2023年1月1日起开始计算，

weekofyear是以周一作为第一天，即2023年1月2日开始计算。

select s.sid, sname, sage, ssex from student s where weekofyear(s.Sage)=weekofyear(now());

48、查询下周过生日的学生

select s.sid, sname, sage, ssex,now() from student s where week(s.Sage)=week(now())+1;

49、查询本月过生日的学生

select s.SID, Sname, Sage, Ssex from student s where month(s.Sage)=month(now());

50、查询下月过生日的学生

select s.Sname,s.Sage from student s where month(s.Sage)=month(now())+1;

考虑可能存在12月的情况，若当前为12月，下个月应该为1月。可以将month()函数对12取余，再和学生生日进行比较。