MySQL经典50题练习

题目来自于平台，代码均已经确认可以执行，也欢迎大家指点。 练习之前创建数据表并导入数据，并且在具体的题目处会适当插入新数据，用来验证语句是否准确。

01 创建学生表，插入数据

#创建学生表 create table Student( Sid varchar(6) PRIMARY KEY, Sname varchar(10) NOT NULL, Sage date NOT NULL, Ssex varchar(10) NOT NULL ); #插入数据 insert into Student values ('01' , '赵雷' , '1990-01-01' , '男'), ('02' , '钱电' , '1990-12-21' , '男'), ('03' , '孙风' , '1990-05-20' , '男'), ('04' , '李云' , '1990-08-06' , '男'), ('05' , '周梅' , '1991-12-01' , '女'), ('06' , '吴兰' , '1992-03-01' , '女'), ('07' , '郑竹' , '1989-07-01' , '女'), ('08' , '王菊' , '1990-01-20' , '女'); #查看学生表数据 SELECT * FROM student; SidSnameSageSsex01赵雷1990-01-01男02钱电1990-12-21男03孙风1990-05-20男04李云1990-08-06男05周梅1991-12-01女06吴兰1992-03-01女07郑竹1989-07-01女08王菊1990-01-20女

02 创建课程表，插入数据

#创建课程表 CREATE TABLE course( cid VARCHAR(10) PRIMARY KEY, cname VARCHAR(10) NOT NULL, tid varchar(10) NOT NULL ); #插入课程表数据 INSERT INTO course VALUES ('01','语文','02'), ('02','数学','01'), ('03','英语','03'); # 查看数据 SELECT * FROM course; cidcnametid01语文0202数学0103英语03

03 创建教师表，插入数据

#创建教师表 CREATE TABLE teacher( tid VARCHAR(10) PRIMARY KEY, tname VARCHAR(20) NOT NULL ); #插入数据 INSERT INTO teacher VALUES ('01','张三'), ('02','李四'), ('03','王五'); #查看当前表格数据 SELECT * FROM teacher; tidtname01张三02李四03王五

04 创建成绩表，插入数据

#创建成绩表 CREATE TABLE score( sid VARCHAR(6), cid VARCHAR(10), score float(5,3) ); #插入数据 INSERT INTO score VALUES ('01','01',80), ('01','02',90), ('01','03',99), ('02','01',70), ('02','02',60), ('02','03',80), ('03','01',80), ('03','02',80), ('03','03',80), ('04','01',50), ('04','02',30), ('04','03',20), ('05','01',76), ('05','02',87), ('06','01',31), ('06','03',34), ('07','02',89), ('07','03',98); #查看数据表 SELECT * FROM score; sidcidscore010180010290010399020170020260020380030180030280030380040150040230040320050176050287060131060334070289070398

下方为练习题

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

#将学生表、01课程成绩表、02课程成绩表连接，再对比大小 SELECT s.*, sc1.score as score_01, sc2.score as score_02 FROM student s JOIN (SELECT * FROM score WHERE cid = '01') sc1 ON sc1.sid=s.sid JOIN (SELECT * FROM score WHERE cid = '02') sc2 ON sc2.sid = s.sid WHERE sc1.score > sc2.score; SidSnameSageSsexscore_01score_0202钱电1990-12-21男706004李云1990-08-06男5030

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

#先查询每个学生的平均成绩--- 筛选平均成绩大于60的信息 SELECT s.sid,s.sname, AVG(score) as score_avg FROM score sc JOIN student s ON sc.sid = s.sid GROUP BY s.sid HAVING AVG(score) >= 60; sidsnamescore_avg01赵雷89.666666702钱电7003孙风8005周梅81.507郑竹93.5

3. 查询在 SC 表存在成绩的学生信息

#关键：确认SC表中存在成绩的学生 sid 信息 SELECT * FROM student WHERE sid IN (SELECT sid FROM score WHERE score is not null); SidSnameSageSsex01赵雷1990-01-01男02钱电1990-12-21男03孙风1990-05-20男04李云1990-08-06男05周梅1991-12-01女06吴兰1992-03-01女07郑竹1989-07-01女

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

#将学生表、成绩表连接，并根据学生 sid 分组，没有成绩的学生，需要学生表left join 成绩表 SELECT s.sid,s.sname, COUNT(DISTINCT cid) as course_num, SUM(score) as score_sum FROM student s LEFT JOIN score sc ON s.sid = sc.sid GROUP BY s.sid; sidsnamecourse_numscore_sum01赵雷326902钱电321003孙风324004李云310005周梅216306吴兰26507郑竹218708王菊0(null)

4.1 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩，每一个科目的成绩

SELECT s.sid,s.sname, COUNT(DISTINCT cid) as course_num, SUM(score) as score_sum, SUM(CASE WHEN cid = '01' THEN score ELSE null END) AS score_01, SUM(CASE WHEN cid = '02' THEN score ELSE null END) AS score_02, SUM(CASE WHEN cid = '03' THEN score ELSE null END) AS score_03 FROM student s JOIN score sc ON s.sid = sc.sid GROUP BY s.sid; sidsnamecourse_numscore_sumscore_01score_02score_0301赵雷326980909902钱电321070608003孙风324080808004李云310050302005周梅21637687(null)06吴兰26531(null)3407郑竹2187(null)8998

5. 查询「李」姓老师的数量

SELECT COUNT(*) 李_num FROM teacher WHERE tname LIKE '李%'; | 李_num | |-------| | 1 |

6. 查询学过「张三」老师授课的同学的信息

SELECT s.* FROM student s JOIN score sc ON s.sid = sc.sid JOIN course c ON c.cid = sc.cid JOIN teacher t ON t.tid = c.tid WHERE t.tname = '张三'; SidSnameSageSsex01赵雷1990-01-01男02钱电1990-12-21男03孙风1990-05-20男04李云1990-08-06男05周梅1991-12-01女07郑竹1989-07-01女

7. 查询没有学全所有课程的同学的信息

SELECT s.*, COUNT(DISTINCT cid) course_num FROM student s JOIN score sc ON s.sid = sc.sid GROUP BY s.sid HAVING COUNT(DISTINCT cid)<3; SidSnameSageSsexcourse_num05周梅1991-12-01女206吴兰1992-03-01女207郑竹1989-07-01女2

8. 查询没有学全所有课程的同学的信息 - 假设并不知道课程数量为3

SELECT s.*, COUNT(DISTINCT cid) course_num FROM student s JOIN score sc ON s.sid = sc.sid GROUP BY s.sid HAVING COUNT(DISTINCT cid)< (SELECT COUNT(*) FROM course);

9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

SELECT s.* FROM student s JOIN score sc ON s.sid = sc.sid WHERE s.sid != '01' GROUP BY s.sid HAVING COUNT(DISTINCT sc.cid) = (SELECT COUNT(*) FROM score WHERE sid = '01'); SidSnameSageSsex02钱电1990-12-21男03孙风1990-05-20男04李云1990-08-06男

9.1. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

SELECT s.*,COUNT(DISTINCT sc.cid) course_num #新增字段方便确认课程数量 FROM student s JOIN score sc ON s.sid = sc.sid WHERE s.sid != '01' GROUP BY s.sid HAVING COUNT(DISTINCT sc.cid) <= (SELECT COUNT(*) FROM score WHERE sid = '01'); SidSnameSageSsexcourse_num02钱电1990-12-21男303孙风1990-05-20男304李云1990-08-06男305周梅1991-12-01女206吴兰1992-03-01女207郑竹1989-07-01女2

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT sname FROM student WHERE sid NOT IN ( SELECT sc.sid FROM course c JOIN teacher t ON t.tid = c.tid JOIN score sc ON c.cid = sc.cid WHERE t.tname = '张三'); sname吴兰王菊

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

SELECT s.sid, s.sname, AVG(score) score_avg FROM student s JOIN score sc ON s.sid = sc.sid WHERE score < 60 GROUP BY s.sid HAVING COUNT(score)>=2; sidsnamescore_avg04李云33.333333306吴兰32.5

此处检查04、06两名学生的成绩情况

SELECT s.sid, s.sname, sc.cid, score FROM student s JOIN score sc ON s.sid = sc.sid WHERE score < 60 GROUP BY s.sid, sc.cid; sidsnamecidscore04李云015004李云023004李云032006吴兰013106吴兰0334

12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息

SELECT s.*, score FROM student s JOIN score sc ON s.sid = sc.sid WHERE sc.cid = '01' AND sc.score < 60 ORDER BY sc.score DESC; SidSnameSageSsexscore04李云1990-08-06男5006吴兰1992-03-01女31

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

SELECT sid, SUM(CASE WHEN cid = '01' THEN score ELSE null END) as score_语文, SUM(CASE WHEN cid = '02' THEN score ELSE null END) as score_数学, SUM(CASE WHEN cid = '03' THEN score ELSE null END) as score_英语, AVG(score) as score_avg FROM score GROUP BY sid ORDER BY AVG(score) DESC; sidscore_语文score_数学score_英语score_avg07(null)899893.50180909989.6666667057687(null)81.5038080808002706080700450302033.33333330631(null)3432.5

14. 查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）。

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT c.cid, c.cname, COUNT(sc.sid) '选修人数', MAX(score) as '最高分', MIN(score) as '最低分', AVG(score) as '平均分', SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END)/COUNT(*) as '及格率', SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END)/COUNT(*) as '中等率', SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END)/COUNT(*) as '优良率', SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END)/COUNT(*) as '优秀率' FROM score sc JOIN course c ON sc.cid = c.cid GROUP BY c.cid ORDER BY '选修人数' DESC, c.cid; cidcname选修人数最高分最低分平均分及格率中等率优良率优秀率01语文6803164.50.66670.33330.3333002数学6903072.66666670.833300.50.166703英语6992068.50.666700.33330.3333

15. 按平均成绩进行排序，显示总排名和各科排名，Score 重复时保留名次空缺

/*此处可以使用窗口函数 RANK() OVER() 进行重复非空排名 但是因为MySQL 5.7 尚且不支持窗口函数*/ SELECT s*, rank_01, rank_02,rank_03, rank_avg FROM student s LEFT JOIN (SELECT sid, rank() over(partition by cid order by score DESC) as rank_01 FROM score WHERE cid = '01') sc01 ON sc01.sid = s.sid LEFT JOIN (SELECT sid, rank() over(partition by cid order by score DESC) as rank_02 FROM score WHERE cid = '02') sc02 ON sc02.sid = s.sid LEFT JOIN (SELECT sid, rank() over(partition by cid order by score DESC) as rank_03 FROM score WHERE cid = '03') sc03 ON sc03.sid = s.sid LEFT JOIN (SELECT sid, rank() over(order by AVG(score) DESC) as rank_avg FROM score) sc_avg ON sc_avg.sid = s.sid ORDER BY rank_avg DESC; #方法二：不使用窗口函数进行排名 SELECT s.sid, s.sname, t1.rank_01,t2.rank_02,t3.rank_03,t4.rank_avg FROM student s LEFT JOIN #下方为成绩表自连接，判断课程1的排名 ( SELECT sc1.sid, sc1.cid, sc1.score,COUNT(DISTINCT sc.score) as 'rank_01' FROM score sc1 JOIN score sc ON sc.cid = sc1.cid AND sc.score >= sc1.score WHERE sc1.cid = '01' GROUP BY sc1.sid ) t1 ON s.sid = t1.sid LEFT JOIN #下方为成绩表自连接，判断课程2的排名 ( SELECT sc2.sid, sc2.cid, sc2.score,COUNT(DISTINCT sc.score) as 'rank_02' FROM score sc2 JOIN score sc ON sc.cid = sc2.cid AND sc.score >= sc2.score WHERE sc2.cid = '02' GROUP BY sc2.sid ) t2 ON s.sid = t2.sid LEFT JOIN #下方为成绩表自连接，判断课程3的排名 ( SELECT sc3.sid, sc3.cid, sc3.score,COUNT(DISTINCT sc.score) as 'rank_03' FROM score sc3 JOIN score sc ON sc.cid = sc3.cid AND sc.score >= sc3.score WHERE sc3.cid = '03' GROUP BY sc3.sid ) t3 ON s.sid = t3.sid RIGHT JOIN #下方为成绩表自连接，判断平均成绩的排名 ( SELECT sc4.sid,sc4.avg_score, COUNT(DISTINCT sc5.avg_score) as 'rank_avg' FROM (SELECT sid, AVG(score) avg_score FROM score GROUP BY sid ) sc4 JOIN (SELECT sid, AVG(score) avg_score FROM score GROUP BY sid ) sc5 ON sc5.avg_score >= sc4.avg_score GROUP BY sc4.sid ) t4 ON s.sid = t4.sid ORDER BY rank_avg ASC; sidsnamerank_01rank_02rank_03rank_avg07郑竹(null)22101赵雷111205周梅23(null)303孙风143402钱电353504李云465606吴兰5(null)47

下方展示每一个科目和平均成绩，方便检查以上结果

sidsnamescore01score02score03avg_score07郑竹(null)89989401赵雷8090999005周梅7687(null)8203孙风8080808002钱电7060807004李云5030203306吴兰31(null)3432

16. 按平均成绩进行排序，显示总排名和各科排名，Score 重复时合并名次

#依旧使用窗口函数 dense_rank() over() 进行留空排名 SELECT s*, rank_01, rank_02,rank_03, rank_avg FROM student s LEFT JOIN (SELECT sid, dense_rank() over(partition by cid order by score DESC) as rank_01 FROM score WHERE cid = '01') sc01 ON sc01.sid = s.sid LEFT JOIN (SELECT sid, dense_rank() over(partition by cid order by score DESC) as rank_02 FROM score WHERE cid = '02') sc02 ON sc02.sid = s.sid LEFT JOIN (SELECT sid, dense_rank() over(partition by cid order by score DESC) as rank_03 FROM score WHERE cid = '03') sc03 ON sc03.sid = s.sid LEFT JOIN (SELECT sid, dense_rank() over(order by AVG(score) DESC) as rank_avg FROM score) sc_avg ON sc_avg.sid = s.sid ORDER BY rank_avg DESC; #方法二：不使用窗口函数进行排名 #注意，因为需要合并名次，不建议使用自连接，子查询可以帮助避免遗失最大值问题 SELECT s.sid, s.sname, rank01, rank02, rank03,rankavg FROM student s LEFT JOIN (SELECT sc1.sid, (SELECT COUNT(sc.score) FROM score sc WHERE sc1.cid = sc.cid AND sc.score > sc1.score) + 1 as rank01 FROM score sc1 WHERE sc1.cid='01') t1 ON t1.sid = s.sid LEFT JOIN (SELECT sc2.sid, (SELECT COUNT(sc.score) FROM score sc WHERE sc2.cid = sc.cid AND sc.score > sc2.score) + 1 as rank02 FROM score sc2 WHERE sc2.cid='02' ) t2 ON t2.sid = s.sid LEFT JOIN (SELECT sc3.sid, (SELECT COUNT(sc.score) FROM score sc WHERE sc3.cid = sc.cid AND sc.score > sc3.score) + 1 as rank03 FROM score sc3 WHERE sc3.cid='03' ) t3 ON t3.sid = s.sid RIGHT JOIN (SELECT sc4.sid, (SELECT COUNT(sc.avg_score) FROM (SELECT sid, AVG(score) avg_score FROM score GROUP BY sid ) sc WHERE sc.avg_score > sc4.avg_score) + 1 as rankavg FROM (SELECT sid, AVG(score) avg_score FROM score GROUP BY sid ) sc4 GROUP BY sc4.sid) t4 ON t4.sid = s.sid ORDER BY rankavg ASC; sidsnamerank01rank02rank03rankavg07郑竹(null)22101赵雷111205周梅33(null)303孙风143402钱电453504李云566606吴兰6(null)57

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

SELECT c.cid, c.cname, SUM(case when score>=85 THEN 1 ELSE 0 END) as num_100_85, SUM(case when score>=70 AND score < 85 THEN 1 ELSE 0 END) as num_85_70, SUM(case when score>=60 AND score < 70 THEN 1 ELSE 0 END) as num_60_70, SUM(case when score < 85 THEN 1 ELSE 0 END) as num_60_0, CONCAT(ROUND(SUM(case when score>=85 THEN 1 ELSE 0 END)/COUNT(*),2)*100,"%") as p_100_85, CONCAT(ROUND(SUM(case when score>=70 AND score < 85 THEN 1 ELSE 0 END)/COUNT(*),2)*100,"%") as p_85_70, CONCAT(ROUND(SUM(case when score>=60 AND score < 70 THEN 1 ELSE 0 END)/COUNT(*),2)*100,"%") as p_60_70, CONCAT(ROUND(SUM(case when score < 85 THEN 1 ELSE 0 END)/COUNT(*),2)*100,"%") as p_60_0 FROM course c JOIN score sc ON c.cid = sc.cid GROUP BY c.cid cidcnamenum_100_85num_85_70num_60_70num_60_0p_100_85p_85_70p_60_70p_60_001语文04060.00%67.00%0.00%100.00%02数学311350.00%17.00%17.00%50.00%03英语220433.00%33.00%0.00%67.00%

18. 查询各科成绩前三名的记录

SELECT a.cid, a.sid, a.score FROM score a JOIN score b ON a.cid = b.cid WHERE a.score<=b.score GROUP BY a.cid,a.score,a.sid HAVING COUNT(DISTINCT b.score)<=3 ORDER BY a.cid ASC, a.score DESC cidsidscore010180010380010576010270020190020789020587030199030798030280030380

19. 查询每门课程被选修的学生数

SELECT cid, COUNT(DISTINCT sid) as num_c FROM score GROUP BY cid cidnum_c016026036

20. 查询出只选修两门课程的学生学号和姓名

SELECT s.sid, s.sname FROM student s LEFT JOIN score a ON s.sid = a.sid GROUP BY s.sid HAVING count(distinct a.cid) =2 sidsname05周梅06吴兰07郑竹

21. 查询男生、女生人数

SELECT ssex, COUNT(*) as num_sex FROM student GROUP BY ssex ssexnum_sex女4男4

22. 查询名字中含有「风」字的学生信息

SELECT * FROM student WHERE sname LIKE '%风%' SidSnameSageSsex03孙风1990-05-20男

23. 查询同名同姓学生名单，并统计同名人数

（此处数据不足够，建议再插入几行测试数据）

insert into Student values ('09' , '张三' , '1992-01-01' , '女'), ('10' , '李四' , '2012-06-06' , '女'), ('11' , '李四' , '2017-12-25' , '男'), ('12' , '赵六' , '2013-10-13' , '男'), ('13' , '孙七' , '1993-09-01' , '女') SELECT sname, COUNT(*) AS num_s FROM student GROUP BY sname HAVING COUNT(*)>1 snamenum_s李四2

24. 查询 1990 年出生的学生名单

SELECT * FROM student WHERE sage LIKE '1990-%' SidSnameSageSsex01赵雷1990-01-01男02钱电1990-12-21男03孙风1990-05-20男04李云1990-08-06男08王菊1990-01-20女

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

SELECT cid, ROUND(AVG(score),2) as score_avg FROM score GROUP BY cid ORDER BY AVG(score) DESC, cid ASC; cidscore_avg0272.670368.50164.5

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

SELECT s.sid,s.sname, AVG(SCORE) AS score_avg FROM student s JOIN score sc ON s.sid = sc.sid GROUP BY s.sid HAVING avg(score) >=85 sidsnamescore_avg01赵雷89.666666707郑竹93.5

27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT c.cname, s.sname, sc.score FROM student s JOIN score sc ON s.sid = sc.sid JOIN course c ON c.cid = sc.cid WHERE c.cname = '数学' AND sc.score < 60 cnamesnamescore数学李云30

28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT s.sname, c.cname, a.score FROM student s LEFT JOIN score a ON s.sid = a.sid LEFT JOIN course c ON c.cid = a.cid; snamecnamescore赵雷语文80赵雷数学90赵雷英语99钱电语文70钱电数学60钱电英语80孙风语文80孙风数学80孙风英语80李云语文50李云数学30李云英语20周梅语文76周梅数学87吴兰语文31吴兰英语34郑竹数学89郑竹英语98王菊(null)(null)张三(null)(null)李四(null)(null)李四(null)(null)赵六(null)(null)孙七(null)(null)

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

SELECT s.sname, c.cname, sc.score FROM student s JOIN score sc ON s.sid = sc.sid JOIN course c ON c.cid = sc.cid WHERE sc.score >70; snamecnamescore赵雷语文80赵雷数学90赵雷英语99钱电英语80孙风语文80孙风数学80孙风英语80周梅语文76周梅数学87郑竹数学89郑竹英语98

30.查询不及格的课程

SELECT s.sname, c.cname, sc.score FROM student s JOIN score sc ON s.sid = sc.sid JOIN course c ON c.cid = sc.cid WHERE sc.score <60 snamecnamescore李云语文50李云数学30李云英语20吴兰语文31吴兰英语34

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

SELECT s.sid, s.sname,score FROM student s JOIN score sc ON s.sid= sc.sid WHERE sc.cid = '01' AND sc.score >=80; sidsnamescore01赵雷8003孙风80

32.求每门课程的学生人数

SELECT cname, COUNT(DISTINCT sc.sid) as num_s FROM score sc JOIN course c ON sc.cid = c.cid GROUP BY cname; #如果不体现课程名称 SELECT cid, COUNT(DISTINCT sc.sid) as num_s FROM score GROUP BY cid; cnamenum_s数学6英语6语文6

33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT s.*, tname, cname, score FROM student s JOIN score sc ON s.sid = sc.sid JOIN course c ON c.cid = sc.cid JOIN teacher t ON t.tid = c.tid WHERE tname = '张三' ORDER BY score DESC LIMIT 1; SidSnameSageSsextnamecnamescore01赵雷1990-01-01男张三数学90

34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

为了体现结果，在成绩表中插入数据

INSERT INTO score VALUES ('09','02',90), ('09','03',73); SELECT s.*,cid,sc.score FROM student s JOIN score sc ON s.sid = sc.sid WHERE score = ( SELECT MAX(score) FROM score sc1 JOIN course c ON c.cid = sc1.cid JOIN teacher t ON t.tid = c.tid WHERE t.tname = '张三'); SidSnameSageSsexcidscore01赵雷1990-01-01男029009张三1992-01-01女0290

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

SELECT DISTINCT s1.sid, S1.cid, s1.score FROM score s1 JOIN score s2 ON s1.cid != s2.cid WHERE s1.score = s2.score ORDER BY s1.sid sidcidscore010180020380030180030280030380

36.查询每门功成绩最好的前两名

(SELECT * FROM score WHERE cid='01' ORDER BY score DESC LIMIT 2) UNION ALL (SELECT * FROM score WHERE cid='02' ORDER BY score DESC LIMIT 2) UNION ALL (SELECT * FROM score WHERE cid='03' ORDER BY score DESC LIMIT 2) sidcidscore010180030180010290070289010399070398

37.统计每门课程的学生选修人数（超过 5 人的课程才统计)

SELECT cid, COUNT(DISTINCT sid) as num_s FROM score GROUP BY cid HAVING num_s>5; #此时的数据已经包含后续插入的2个成绩数据 cidnum_s016027037

38.检索至少选修两门课程的学生学号

SELECT sid, COUNT(DISTINCT cid) as num_c FROM score GROUP BY sid HAVING num_c >=2; sidnum_c013023033043052062072092

39.查询各学生的年龄，只按年份来算

SELECT sname, YEAR(NOW())-YEAR(sage) as age FROM student snameage赵雷30钱电30孙风30李云30周梅29吴兰28郑竹31王菊30张三28李四8李四3赵六7孙七27

40.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

#timestampdiff()会根据实际的月日判断是否减一 #SELECT TIMESTAMPDIFF(YEAR,'2001-02-01','2003-01-01'); -->1 #SELECT TIMESTAMPDIFF(YEAR,'2001-02-01','2003-02-01'); -->2 #SELECT TIMESTAMPDIFF(MONTH,'2001-02-01','2003-01-01'); -->23 SELECT sname, TIMESTAMPDIFF(YEAR,sage, NOW()) as age FROM student; snameage赵雷30钱电29孙风30李云30周梅28吴兰28郑竹31王菊30张三28李四8李四2赵六7孙七26

41. 查询本周过生日的学生

SELECT *, WEEKOFYEAR(sage) FROM student WHERE WEEKOFYEAR(sage) = WEEKOFYEAR(curdate()); #目前数据中没有本周生日的 #查看每个学生生日所在的周 SELECT *, weekofyear(sage) from student; SidSnameSageSsexweekofyear(sage)01赵雷1990-01-01男102钱电1990-12-21男5103孙风1990-05-20男2004李云1990-08-06男3205周梅1991-12-01女4806吴兰1992-03-01女907郑竹1989-07-01女2608王菊1990-01-20女309张三1992-01-01女110李四2012-06-06女2311李四2017-12-25男5212赵六2013-06-13男2413孙七1993-12-01女48

42.查询下周过生日的学生

SELECT *, weekofyear(sage) from student WHERE WEEKOFYEAR(sage) -1 = WEEKOFYEAR(CURDATE());

43.查询本月过生日的学生

SELECT *, MONTH(sage) FROM student WHERE MONTH(Sage)= MONTH(curdate()) SidSnameSageSsexMONTH(sage)13孙七1993-09-01女9

44.查询下月过生日的学生

SELECT *, MONTH(sage) FROM student WHERE MONTH(Sage) -1 = MONTH(curdate()) SidSnameSageSsexMONTH(sage)12赵六2013-10-13男10

45.查询同时存在" 01 “课程和” 02 "课程的情况(*)

SELECT * FROM (SELECT * FROM score WHERE cid = '01') a JOIN (SELECT * FROM score WHERE cid = '02') b ON a.sid = b.sid; sidcidscoresidcidscore010180010290020170020260030180030280040150040230050176050287

46. 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )(*)

SELECT * FROM (SELECT * FROM score WHERE cid = '01') a LEFT JOIN (SELECT * FROM score WHERE cid = '02') b ON a.sid = b.sid sidcidscoresidcidscore010180010290020170020260030180030280040150040230050176050287060131(null)(null)(null)

47. 查询不存在" 01 “课程但存在” 02 "课程的情况(**)

SELECT * FROM (SELECT * FROM score WHERE cid = '01') a RIGHT JOIN (SELECT * FROM score WHERE cid = '02') b ON a.sid = b.sid WHERE a.sid IS NULL sidcidscoresidcidscore(null)(null)(null)070289(null)(null)(null)090290 SELECT * FROM score WHERE sid NOT IN (select sid FROM score WHERE cid = '01') AND cid = '02'; sidcidscore070289090290

48. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺(****)

SELECT b.*, COUNT(DISTINCT a.total_score) as rank_total FROM (SELECT sid, SUM(score) total_score FROM score GROUP BY sid) a JOIN (SELECT sid, SUM(score) total_score FROM score GROUP BY sid) b ON a.total_score >= b.total_score GROUP BY b.sid ORDER BY rank_total ASC; sidtotal_scorerank_total01269103240202210307187409163505163504100606657

49. 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

SELECT a.*, (SELECT COUNT(b.total_score) FROM (SELECT sid, SUM(score) total_score FROM score GROUP BY sid) b WHERE b.total_score > a.total_score ) +1 as rank_total FROM (SELECT sid, SUM(score) total_score FROM score GROUP BY sid) a ORDER BY rank_total ASC sidtotal_scorerank_total01269103240202210307187405163509163504100706658

50. 查询选修了全部课程的学生信息(**)

SELECT s.* FROM student s JOIN score sc ON s.sid = sc.sid GROUP BY s.sid HAVING count(distinct sc.cid) = (SELECT COUNT(DISTINCT cid) FROM course) SidSnameSageSsex01赵雷1990-01-01男02钱电1990-12-21男03孙风1990-05-20男04李云1990-08-06男