SQL经典50题(MySQL版)

建站1250 更新时间：2025-06-08 05:34:39

2023年6月21日发(作者：)

SQL经典50题（MySQL版）近段时间⼀直都在学习MySQL，为了检验⾃⼰的学习效果，特意拿了⽹上经典的SQL50题练了⼀下。我⽤的是MySQL Server 8.0版本（新增了窗⼝函数rank(）等，接下来我会把⾃⼰的SQL语句以及运⾏结果放到这⾥来。答案不唯⼀，仅供参考。若有更好的查询⽅式，欢迎交流学习。已知有以下4张表：学⽣表：student(s_id,s_name,s_age,s_sex) – –学号,学⽣姓名,出⽣年⽉,性别成绩表：score(s_id,c_id,score) – –学号,课程号,成绩课程表：course(c_id,c_name,t_id) – –课程号,课程名称,教师号教师表：teacher(t_id,t_name) – –教师号,教师姓名以上4个表是通过加粗的字段建⽴连接的。⼀、创建数据库和表--

创建数据库，并且使⽤CREATE DATABASE IF NOT EXISTS df_school;USE df_school;1、创建学⽣表并插⼊数据：--

创建学⽣表CREATE TABLE IF NOT EXISTS student( s_id VARCHAR(10), s_name VARCHAR(20), s_age DATE, s_sex VARCHAR(10));--

往学⽣表中插⼊数据INSERT INTO student VALUES('01' , '赵雷' , '1990-01-01' , '男');INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男');INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '⼥');INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '⼥');INSERT INTO Student VALUES('07' , '郑⽵' , '1989-07-01' , '⼥');INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '⼥');2、创建课程表并插⼊数据--

创建课程表CREATE TABLE IF NOT EXISTS course( c_id VARCHAR(10), c_name VARCHAR(20), t_id VARCHAR(10));--

往课程表插⼊数据INSERT INTO Course VALUES('01' , '语⽂' , '02');INSERT INTO Course VALUES('02' , '数学' , '01');INSERT INTO Course VALUES('03' , '英语' , '03');3、创建教师表并插⼊数据--

创建教师表CREATE TABLE IF NOT EXISTS teacher ( t_id VARCHAR(10), t_name VARCHAR(20));--

往教师表插⼊数据INSERT INTO Teacher VALUES('01' , '张三');INSERT INTO Teacher VALUES('02' , '李四');INSERT INTO Teacher VALUES('03' , '王五');4、创建成绩表并插⼊数据--

创建成绩表CREATE TABLE IF NOT EXISTS score ( s_id VARCHAR(10), c_id VARCHAR(10), score VARCHAR(10));--

往成绩表插⼊数据INSERT INTO Score VALUES('01' , '01' , 80);INSERT INTO Score VALUES('01' , '02' , 90);INSERT INTO Score VALUES('01' , '03' , 99);INSERT INTO Score VALUES('02' , '01' , 70);INSERT INTO Score VALUES('02' , '02' , 60);INSERT INTO Score VALUES('02' , '03' , 80);INSERT INTO Score VALUES('03' , '01' , 80);INSERT INTO Score VALUES('03' , '02' , 80);INSERT INTO Score VALUES('03' , '03' , 80);INSERT INTO Score VALUES('04' , '01' , 50);INSERT INTO Score VALUES('04' , '02' , 30);INSERT INTO Score VALUES('04' , '03' , 20);INSERT INTO Score VALUES('05' , '01' , 76);INSERT INTO Score VALUES('05' , '02' , 87);INSERT INTO Score VALUES('06' , '01' , 31);INSERT INTO Score VALUES('06' , '03' , 34);INSERT INTO Score VALUES('07' , '02' , 89);INSERT INTO Score VALUES('07' , '03' , 98);创建好的4张表如下：1、查询"01"课程⽐"02"课程成绩⾼的学⽣的学号及课程分数-- 1、查询"01"课程⽐"02"课程成绩⾼的学⽣的学号及课程分数SELECT a.s_id AS s_id, score1, score2FROM

(SELECT s_id, score AS score1 FROM score WHERE c_id = '01') aINNER JOIN(SELECT s_id, score AS score2 FROM score WHERE c_id = '02') bON a.s_id = b.s_id WHERE score1 > score2;2、查询"01"课程⽐"02"课程成绩⾼的学⽣的信息及课程分数-- 2、查询"01"课程⽐"02"课程成绩⾼的学⽣的信息及课程分数SELECT s.*, AS score1, AS score2FROM student s, (SELECT s_id,score FROM score WHERE c_id = '01') a, (SELECT s_id,score FROM score WHERE c_id = '02') bWHERE a.s_id = b.s_id AND > AND s.`s_id` = a.s_id;3、查询平均成绩⼤于等于60分的同学的学⽣编号和学⽣姓名和平均成绩-- 3、查询平均成绩⼤于等于60分的同学的学⽣编号和学⽣姓名和平均成绩SELECT s.`s_id`,s.`s_name`,AVG(score) AS avg_score

FROM student AS s,score AS scWHERE s.`s_id` = sc.`s_id`

GROUP BY s.`s_id`HAVING avg_score >= 60;-- 3、(法⼆)查询平均成绩⼤于等于60分的同学的学⽣编号和学⽣姓名和平均成绩SELECT s.`s_id`,s.`s_name`,_score AS avg_score

FROM student sRIGHT JOIN(SELECT s_id, AVG(score) AS avg_score FROM scoreGROUP BY s_id HAVING avg_score >= 60) bON s.`s_id` = b.s_id;4、查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩显⽰null)这道题得⽤到left join，不能⽤where连接，因为题⽬说了要求有显⽰为null的，where是inner join，不会出现null，如果⽤where在这道题⾥会查不出第08号学⽣-- 4、查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩显⽰null)SELECT s.`s_id`,s_name,COUNT(c_id)AS 选课总数,SUM(score) AS 总成绩FROM student sLEFT JOIN score scON s.`s_id` = sc.`s_id`GROUP BY s_id;5、查询姓“李”的⽼师的个数-- 5、查询姓“李”的⽼师的个数SELECT COUNT(t_name) AS ⼈数FROM teacher

WHERE t_name LIKE '李%';6、查询没学过“张三”⽼师课的学⽣的学号、姓名-- 6、查询没学过“张三”⽼师课的学⽣的学号、姓名SELECT s_id, s_name FROM student WHERE s_id NOT IN(SELECT s_id FROM score WHERE c_id IN(SELECT c_id FROM course WHERE t_id IN

(SELECT t_id

FROM teacher

WHERE t_name = '张三')));--

法⼆SELECT s_id, s_name

FROM studentWHERE s_id NOT IN(SELECT sc.s_id FROM score scINNER JOIN course co ON sc.`c_id` = co.`c_id`INNER JOIN teacher te ON co.`t_id`= te.`t_id`WHERE te.`t_name`='张三');--

法三SELECT s_id, s_nameFROM studentWHERE s_name NOT IN ( SELECT s.s_name FROM student AS s, course AS c, teacher AS t, score AS sc WHERE s.s_id = sc.s_id AND sc.c_id = c.c_id AND c.t_id = t.t_id AND t.t_name = '张三');7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学⽣的学号、姓名-- 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学⽣的学号、姓名SELECT s_id,s_name

FROM student

WHERE s_id IN (SELECT s_idFROM scoreWHERE c_id = '01' OR c_id = '02'GROUP BY s_idHAVING COUNT(c_id) >= 2);8、查询课程编号为“02”的总成绩-- 8、查询课程编号为“02”的总成绩SELECT SUM(score) AS 总成绩FROM scoreWHERE c_id = '02';9、查询没有学全所有课的学⽣的学号、姓名-- 9、查询没有学全所有课的学⽣的学号、姓名SELECT st.`s_id`,st.`s_name`

FROM student stINNER JOIN score scON st.`s_id` = sc.`s_id`GROUP BY sc.`s_id`HAVING COUNT(sc.`c_id`) < (SELECTCOUNT(DISTINCT c_id)

FROM course);10、查询⾄少有⼀门课与学号为“01”的学⽣所学课程相同的学⽣的学号和姓名-- 10、查询⾄少有⼀门课与学号为“01”的学⽣所学课程相同的学⽣的学号和姓名SELECT st.`s_id`,st.`s_name`FROM student st

WHERE st.`s_id` IN(SELECT DISTINCT sc.`s_id`

FROM score scWHERE sc.`c_id` IN(SELECT sc.`c_id`FROM score scWHERE sc.`s_id` = 01)) ANDst.`s_id` <> '01';--

法⼆SELECT DISTINCT st.`s_id`,st.`s_name`FROM student st

INNER JOIN score scON st.`s_id`= sc.`s_id`WHERE sc.`c_id` IN(SELECT sc.`c_id`FROM score scWHERE sc.`s_id` = '01') ANDst.`s_id` <> '01';11、查询和“01”号同学所学课程完全相同的其他同学的信息-- 11、查询和“01”号同学所学课程完全相同的其他同学的信息SELECT DISTINCT st.*

FROM student stINNER JOIN score scON st.`s_id` = sc.`s_id`WHERE sc.`c_id` IN(SELECT sc.`c_id`FROM score scWHERE sc.`s_id`= '01') AND sc.`s_id` <> '01'GROUP BY sc.`s_id`HAVING COUNT(sc.`c_id`) = (SELECTCOUNT(c_id) FROM score WHERE s_id = '01');12、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩-- 12、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩SELECT st.`s_id`,st.`s_name`,AVG() AS avg_scoreFROM student st, score sc

WHERE st.`s_id` = sc.`s_id`AND sc.`score` < 60GROUP BY sc.`s_id`HAVING COUNT(sc.`c_id`) >= 2;13、检索"01"课程分数⼩于60，按分数降序排列的学⽣信息-- 13、检索"01"课程分数⼩于60，按分数降序排列的学⽣信息SELECT st.*,sc.`score`FROM student stINNER JOIN score sc ON

st.`s_id`= sc.`s_id`

WHERE sc.`c_id` = '01' AND sc.`score` < 60ORDER BY sc.`score` DESC;14、按平均成绩从⾼到低显⽰所有学⽣的所有课程的成绩以及平均成绩-- 14、按平均成绩从⾼到低显⽰所有学⽣的所有课程的成绩以及平均成绩SELECT s_id,

SUM(CASE WHEN c_id = '01' THEN score ELSE NULL END) AS score1,SUM(CASE WHEN c_id = '02' THEN score ELSE NULL END) AS score2,SUM(CASE WHEN c_id = '03' THEN score ELSE NULL END) AS score3,AVG(score)FROM scoreGROUP BY s_idORDER BY AVG(score) DESC;15、查询各科成绩最⾼分、最低分、平均分、及格率、中等率、优良率、优秀率要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列。以如下形式显⽰：课程ID，课程name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率。及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90-- 15、查询各科成绩最⾼分、最低分、平均分、及格率、中等率、优良率、优秀率--

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

以如下形式显⽰：课程ID，课程name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率--

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

--

先从简单的写法开始，及格率直接⽤⼩数表⽰，没⽤百分号SELECT co.c_id AS 课程ID, co.c_name AS 课程名称, COUNT(*) AS 选修⼈数,

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 BETWEEN 70 AND 80 THEN 1 ELSE 0 END) / COUNT(*) AS '中等率', SUM(CASE WHEN score BETWEEN 80 AND 90 THEN 1 ELSE 0 END) / COUNT(*) AS '优良率', SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) / COUNT(*) AS '优秀率'FROM score sc, course coWHERE sc.c_id = co.c_id

GROUP BY co.c_idORDER BY COUNT(*) DESC, co.c_id;--

复杂点的写法，及格率⽤百分号表⽰SELECT a.c_id AS '课程ID', course.c_name AS '课程name', COUNT(*) AS 选修⼈数, MAX() AS '最⾼分', MIN() AS '最低分', CAST(AVG() AS DECIMAL(5,2)) AS '平均分', CONCAT(CAST(SUM(pass)/COUNT(*)*100 AS DECIMAL(5,2)),'%') AS '及格率', CONCAT(CAST(SUM(medi)/COUNT(*)*100 AS DECIMAL(5,2)),'%') AS '中等率', CONCAT(CAST(SUM(good)/COUNT(*)*100 AS DECIMAL(5,2)),'%') AS '优良率', CONCAT(CAST(SUM(excellent)/COUNT(*)*100 AS DECIMAL(5,2)),'%') AS '优秀率'

FROM (SELECT * , CASE WHEN score>=60 THEN 1 ELSE 0 END AS pass, CASE WHEN score>=70 AND score<80 THEN 1 ELSE 0 END AS medi, CASE WHEN score>=80 AND score<90 THEN 1 ELSE 0 END AS good, CASE WHEN score>=90 THEN 1 ELSE 0 END AS excellent FROM score) aLEFT JOIN course ON a.c_id=course.c_idGROUP BY a.c_idORDER BY COUNT(*) DESC, a.c_id;16、按平均成绩进⾏排序，显⽰总排名和各科排名，Score 重复时保留名次空缺Score 重复时保留名次空缺,指的是rank()和dense_rank()的区别，也就是两个并列第⼀名之后的那个⼈是第三名(rank)还是第⼆名(dense_rank)的区别。SELECT s.*, rank_01, rank_02, rank_03, rank_totalFROM student sLEFT JOIN (SELECT s_id, rank() over(PARTITION BY c_id ORDER BY score DESC) AS rank_01 FROM score WHERE c_id=01) A ON s.s_id=A.s_idLEFT JOIN (SELECT s_id, rank() over(PARTITION BY c_id ORDER BY score DESC) AS rank_02 FROM score WHERE c_id=02) B ON s.s_id=B.s_idLEFT JOIN (SELECT s_id, rank() over(PARTITION BY c_id ORDER BY score DESC) AS rank_03 FROM score WHERE c_id=03) C ON s.s_id=C.s_idLEFT JOIN (SELECT s_id, rank() over(ORDER BY AVG(score) DESC) AS rank_total FROM score GROUP BY s_id) D ON s.s_id=D.s_idORDER BY rank_total ASC;17、按各科成绩进⾏排序，并显⽰排名-- 17、按各科成绩进⾏排序，并显⽰排名SELECT a.`c_id`,a.`s_id`,a.`score`,COUNT(b.`score`)+1 AS rank

FROM score aLEFT JOIN score bON a.`score`< b.`score` AND a.`c_id`= b.`c_id`GROUP BY a.`c_id`,a.`s_id`,a.`score`ORDER BY a.`c_id`,rank;18、查询学⽣的总成绩并进⾏排名-- 18、查询学⽣的总成绩并进⾏排名SELECT a.*, @rank:= @rank+1 AS rank

FROM

(SELECT s_id,SUM(score) FROM score

GROUP BY s_id

ORDER BY SUM(score) DESC) a, (SELECT @rank:=0) b;--

法⼆SELECT s_id, SUM(score), rank() over(ORDER BY SUM(score) DESC) AS rankingFROM scoreGROUP BY s_idORDER BY ranking;19、查询不同⽼师所教不同课程平均分从⾼到低显⽰-- 19、查询不同⽼师所教不同课程平均分从⾼到低显⽰SELECT te.`t_id`, te.`t_name`, AVG(sc.`score`)FROM score sc INNER JOIN course co ON sc.`c_id`= co.`c_id`INNER JOIN teacher te ON co.`t_id`= te.`t_id`GROUP BY te.`t_id`ORDER BY AVG(sc.`score`) DESC;20、查询所有课程的成绩第2名到第3名的学⽣信息及该课程成绩-- 20、查询所有课程的成绩第2名到第3名的学⽣信息及该课程成绩SELECT result.c_id, result.s_id, , student.`s_name`,student.`s_age`,student.`s_sex`FROM

(SELECT *, IF(@pa=a.c_id, @rank:= @rank+1, @rank:=1) AS rank, @pa:=a.c_idFROM

(SELECT c_id, s_id,score FROM scoreGROUP BY c_id, s_id ORDER BY c_id, score DESC) a,(SELECT @rank:=0,@pa:=NULL) b) resultLEFT JOIN student ON result.s_id = student.`s_id`WHERE rank BETWEEN 2 AND 3GROUP BY c_id, score DESC;21、使⽤分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段⼈数：课程ID和课程名称-- 21、使⽤分段[85-100],[70-84],[60-69],[<60]来统计各科成绩，分别统计各分数段⼈数：课程ID和课程名称SELECT a.c_id AS '课程编号',course.c_name AS '课程名称',SUM(level1) AS '[85-100]⼈数', SUM(level1)/COUNT(1) AS '[85-100]占⽐',SUM(level2) AS '[70-84]⼈数', SUM(level2)/COUNT(1) AS '[70-84]占⽐',SUM(level3) AS '[60-69]⼈数', SUM(level3)/COUNT(1) AS '[60-69]占⽐',SUM(level4) AS '[0-59]⼈数', SUM(level4)/COUNT(1) AS '[0-59]占⽐' FROM(SELECT *,(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS 'level1',(CASE WHEN score BETWEEN 70 AND 84 THEN 1 ELSE 0 END) AS 'level2',(CASE WHEN score BETWEEN 60 AND 69 THEN 1 ELSE 0 END) AS 'level3',(CASE WHEN score BETWEEN 0 AND 59 THEN 1 ELSE 0 END) AS 'level4'FROM score) aLEFT JOIN course ON a.c_id=course.c_idGROUP BY a.c_id;22、查询学⽣平均成绩及其名次-- 22、查询学⽣平均成绩及其名次SELECT a.*,@rank:=@rank+1 AS rankFROM

(SELECT s_id, AVG(score) AS '平均成绩'

FROM score

GROUP BY s_id ORDER BY AVG(score) DESC) a, (SELECT @rank:=0) b;--

法⼆：SELECT s_id, AVG(score),

rank() over(ORDER BY AVG(score) DESC) AS rank_totalFROM scoreGROUP BY s_id;23、查询各科成绩前三名的记录-- 23、查询各科成绩前三名的记录SELECT a.`c_id`,a.`s_id`,a.`score`FROM score aWHERE (SELECT COUNT(b.s_id) FROM score b WHERE a.`c_id`=b.`c_id` AND a.`score`< b.`score`) < 3GROUP BY a.`c_id`, a.`s_id`;--

法⼆：SELECT * FROM (SELECT c_id, s_id, score, dense_rank() over(PARTITION BY c_id ORDER BY score DESC) rank1 FROM score) AWHERE 1<=3;24、查询每门课程被选修的学⽣数-- 24、查询每门课程被选修的学⽣数SELECT c_id, COUNT(s_id) AS '选修⼈数'FROM scoreGROUP BY c_id;25、查询出只有两门课程的全部学⽣的学号和姓名-- 25、

查询出只有两门课程的全部学⽣的学号和姓名SELECT s_id,s_name

FROM student

WHERE s_id IN(SELECT s_id

FROM score

GROUP BY s_idHAVING COUNT(c_id)=2);26、查询男⽣、⼥⽣⼈数-- 26、查询男⽣、⼥⽣⼈数SELECT s_sex AS '性别',COUNT(*) AS '⼈数'FROM studentGROUP BY s_sex;27、查询名字中含有"风"字的学⽣信息-- 27、查询名字中含有"风"字的学⽣信息SELECT *

FROM student

WHERE s_name LIKE '%风%';28、查询同名同姓学⽣名单，并统计同名⼈数-- 28、查询同名同姓学⽣名单，并统计同名⼈数SELECT s_name, num AS '同名⼈数'FROM ( SELECT *, COUNT(s_id) -1 AS num

FROM student GROUP BY s_name) a;29、查询1990年出⽣的学⽣名单-- 29、查询1990年出⽣的学⽣名单SELECT *FROM studentWHERE YEAR(s_age) = '1990';30、查询平均成绩⼤于等于85的所有学⽣的学号、姓名和平均成绩-- 30、查询平均成绩⼤于等于85的所有学⽣的学号、姓名和平均成绩SELECT st.`s_id` AS '学号',st.`s_name` AS '姓名',AVG(score) AS '平均成绩'FROM student stINNER JOIN score sc

ON st.`s_id`=sc.`s_id`GROUP BY sc.`s_id`HAVING AVG(score)>= 85;31、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列-- 31、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列SELECT c_id, AVG(score) AS '平均成绩'FROM scoreGROUP BY c_idORDER BY AVG(score),c_id DESC;32、查询课程名称为"数学"，且分数低于60的学⽣姓名和分数-- 32、查询课程名称为"数学"，且分数低于60的学⽣姓名和分数SELECT st.`s_id`,st.`s_name`,score

FROM student stINNER JOIN score scON st.`s_id`=sc.`s_id`WHERE score < 60 AND sc.`c_id` IN (SELECT c_id

FROM course

WHERE c_name = '数学');33、查询所有学⽣的课程及分数情况-- 33、查询所有学⽣的课程及分数情况SELECT sc.`s_id`,SUM(CASE WHEN co.c_name = '语⽂' THEN ELSE NULL END) AS '语⽂成绩',SUM(CASE WHEN co.c_name = '数学' THEN ELSE NULL END) AS '数学成绩',SUM(CASE WHEN co.c_name = '英语' THEN ELSE NULL END) AS '英语成绩'FROM score sc INNER JOIN course co ON sc.`c_id`= co.`c_id`GROUP BY sc.`s_id`;34、查询任何⼀门课程成绩在70分以上的姓名、课程名称和分数-- 34、查询任何⼀门课程成绩在70分以上的姓名、课程名称和分数SELECT st.`s_name` AS '姓名', co.`c_name` AS '课程名称',sc.`score` AS '分数'FROM student stINNER JOIN score sc ON st.`s_id`= sc.`s_id`INNER JOIN course co ON sc.`c_id`= co.`c_id`WHERE sc.`score` >= 70;35、查询不及格的课程并按课程号从⼤到⼩排列-- 35、查询不及格的课程并按课程号从⼤到⼩排列SELECT co.`c_id`,co.`c_name`,sc.`score`FROM course coINNER JOIN score sc ON co.`c_id`=sc.`c_id`WHERE score < 60ORDER BY c_id DESC;36、查询课程编号为03且课程成绩在80分以上的学⽣的学号和姓名-- 36、查询课程编号为03且课程成绩在80分以上的学⽣的学号和姓名SELECT st.`s_id` AS '学号',st.`s_name` AS '姓名'FROM student stINNER JOIN score sc ON st.`s_id`= sc.`s_id`

WHERE c_id = '03' AND score > 80;37、求每门课程的学⽣⼈数-- 37、求每门课程的学⽣⼈数SELECT c_id, COUNT(s_id) AS '选课⼈数'FROM scoreGROUP BY c_id;38、成绩不重复，查询选修“张三”⽼师所授课程的学⽣中成绩最⾼的学⽣姓名及其成绩-- 38、成绩不重复，查询选修“张三”⽼师所授课程的学⽣中成绩最⾼的学⽣姓名及其成绩SELECT st.`s_id` AS '学号' , st.`s_name` AS '姓名' ,MAX(sc.`score`) AS '成绩'

FROM student stINNER JOIN score sc ON st.`s_id`= sc.`s_id`WHERE sc.`c_id` IN (SELECT c_id FROM course WHERE t_id IN (SELECT t_id FROM teacher

WHERE t_name = '张三' ));39、成绩有重复的情况下，查询选修「张三」⽼师所授课程的学⽣中，成绩最⾼的学⽣信息及其成绩-- 39、成绩有重复的情况下，查询选修「张三」⽼师所授课程的学⽣中，成绩最⾼的学⽣信息及其成绩SELECT * FROM (SELECT *, DENSE_RANK() over(ORDER BY score DESC) A FROM score WHERE c_id = (SELECT c_id FROM Course WHERE t_id = (SELECT t_id FROM Teacher WHERE t_name='张三'))) BWHERE B.A=1;40、查询不同课程成绩相同的学⽣的学⽣编号、课程编号、学⽣成绩-- 40、查询不同课程成绩相同的学⽣的学⽣编号、课程编号、学⽣成绩SELECT a.`s_id`,a.`c_id`,b.`c_id`,a.`score`,b.`score`FROM score a, score bWHERE a.`s_id`= b.`s_id` AND a.`score`= b.`score` AND a.`c_id`<> b.`c_id`;41、查询每门功课成绩最好的前两名-- 41、查询每门功课成绩最好的前两名(select * from score where c_id = '01' order by score desc limit 2)union

(select * from score where c_id = '02' order by score DESC limit 2)union(select * from score where c_id = '03' order by score DESC limit 2);42、统计每门课程的学⽣选修⼈数（超过5⼈的课程才统计）。要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列。-- 42、统计每门课程的学⽣选修⼈数（超过5⼈的课程才统计）。--

要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列SELECT c_id,COUNT(s_id) AS '选修⼈数'FROM scoreGROUP BY c_idHAVING COUNT(s_id) >= 5ORDER BY COUNT(s_id) DESC, c_id;43、检索⾄少选修两门课程的学⽣学号-- 43、检索⾄少选修两门课程的学⽣学号SELECT s_id, COUNT(c_id) AS '选修课程数'FROM score

GROUP BY s_idHAVING COUNT(c_id) >= 2;44、查询选修了全部课程的学⽣信息-- 44、查询选修了全部课程的学⽣信息SELECT *

FROM student

WHERE s_id IN ( SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id) = (SELECT COUNT(DISTINCT c_id) FROM course));45、查询各学⽣的年龄-- 45、查询各学⽣的年龄SELECT s_id, s_name, (YEAR(NOW()) - YEAR(s_age)) AS '年龄'FROM student;46、按照出⽣⽇期来算，当前⽉⽇ < 出⽣年⽉的⽉⽇，则年龄减⼀TIMESTAMPDIFF函数：有参数设置，可以精确到年（YEAR）、天（DAY）、⼩时（HOUR），分钟（MINUTE）和秒（SECOND），使⽤起来⽐datediff函数更加灵活。对于⽐较的两个时间，时间⼩的放在前⾯，时间⼤的放在后⾯。datediff函数：返回值是相差的天数，不能定位到⼩时、分钟和秒。-- 46、按照出⽣⽇期来算，当前⽉⽇ <

出⽣年⽉的⽉⽇,则年龄减⼀SELECT s_id, s_name, TIMESTAMPDIFF(YEAR,s_age,NOW()) AS '年龄'FROM student;47、查询本周过⽣⽇的学⽣week(时间)默认从0开始，并却星期天默认为第⼀天，国外的算法week(时间，1)从1开始，并却星期⼀为第⼀天，国内算法-- 47、查询本周过⽣⽇的学⽣SELECT *FROM studentWHERE WEEK(s_age) = WEEK(NOW());--

以周⼀为⼀周的开始SELECT *FROM studentWHERE WEEK(s_age) = WEEK(NOW(),1);因为学⽣表中的学⽣没有11⽉份出⽣的，所以结果为null48、查询下周过⽣⽇的学⽣-- 48、查询下周过⽣⽇的学⽣SELECT *FROM studentWHERE WEEK(s_age) = WEEK(NOW())+1;--

以周⼀为⼀周的开始SELECT *FROM studentWHERE WEEK(s_age) = WEEK(NOW(),1)+1;49、查询本⽉过⽣⽇的学⽣-- 49、查询本⽉过⽣⽇的学⽣SELECT *FROM studentWHERE MONTH(s_age) = MONTH(NOW());50、查询下个⽉过⽣⽇的学⽣-- 50、查询下个⽉过⽣⽇的学⽣SELECT *FROM studentWHERE MONTH(s_age) = MONTH(NOW())+1;--

创建数据库，并且使⽤CREATE DATABASE IF NOT EXISTS df_school;USE df_school;--

创建学⽣表CREATE TABLE IF NOT EXISTS student( s_id VARCHAR(10), s_name VARCHAR(20), s_age DATE, s_sex VARCHAR(10));--

往学⽣表中插⼊数据INSERT INTO student VALUES('01' , '赵雷' , '1990-01-01' , '男');INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男');INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '⼥');INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '⼥');INSERT INTO Student VALUES('07' , '郑⽵' , '1989-07-01' , '⼥');INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '⼥');--

创建课程表CREATE TABLE IF NOT EXISTS course( c_id VARCHAR(10), c_name VARCHAR(20), t_id VARCHAR(10));--

往课程表插⼊数据INSERT INTO Course VALUES('01' , '语⽂' , '02');INSERT INTO Course VALUES('02' , '数学' , '01');INSERT INTO Course VALUES('03' , '英语' , '03');--

创建教师表CREATE TABLE IF NOT EXISTS teacher (t_id VARCHAR(10),t_name VARCHAR(20));--

往教师表插⼊数据INSERT INTO Teacher VALUES('01' , '张三');INSERT INTO Teacher VALUES('02' , '李四');INSERT INTO Teacher VALUES('03' , '王五');--

创建成绩表CREATE TABLE IF NOT EXISTS score ( s_id VARCHAR(10), c_id VARCHAR(10), score VARCHAR(10));--

往成绩表插⼊数据INSERT INTO Score VALUES('01' , '01' , 80);INSERT INTO Score VALUES('01' , '02' , 90);INSERT INTO Score VALUES('01' , '03' , 99);INSERT INTO Score VALUES('02' , '01' , 70);INSERT INTO Score VALUES('02' , '02' , 60);INSERT INTO Score VALUES('02' , '03' , 80);INSERT INTO Score VALUES('03' , '01' , 80);INSERT INTO Score VALUES('03' , '02' , 80);INSERT INTO Score VALUES('03' , '03' , 80);INSERT INTO Score VALUES('04' , '01' , 50);INSERT INTO Score VALUES('04' , '02' , 30);INSERT INTO Score VALUES('04' , '03' , 20);INSERT INTO Score VALUES('05' , '01' , 76);INSERT INTO Score VALUES('05' , '02' , 87);INSERT INTO Score VALUES('06' , '01' , 31);INSERT INTO Score VALUES('06' , '03' , 34);INSERT INTO Score VALUES('07' , '02' , 89);INSERT INTO Score VALUES('07' , '03' , 98);-- 1、查询"01"课程⽐"02"课程成绩⾼的学⽣的学号及课程分数SELECT a.s_id AS s_id, score1, score2FROM

(SELECT s_id, score AS score1 FROM score WHERE c_id = '01') aINNER JOIN(SELECT s_id, score AS score2 FROM score WHERE c_id = '02') bON a.s_id = b.s_id WHERE score1 > score2;

-- 2、查询"01"课程⽐"02"课程成绩⾼的学⽣的信息及课程分数SELECT s.*, AS score1, AS score2FROM student s, (SELECT s_id,score FROM score WHERE c_id = '01') a, (SELECT s_id,score FROM score WHERE c_id = '02') bWHERE a.s_id = b.s_id AND > AND s.`s_id` = a.s_id;-- 3、查询平均成绩⼤于等于60分的同学的学⽣编号和学⽣姓名和平均成绩SELECT s.`s_id`,s.`s_name`,AVG(score) AS avg_score

FROM student AS s,score AS scWHERE s.`s_id` = sc.`s_id`

GROUP BY s.`s_id`HAVING avg_score >= 60;-- 3、(法⼆)查询平均成绩⼤于等于60分的同学的学⽣编号和学⽣姓名和平均成绩SELECT s.`s_id`,s.`s_name`,_score AS avg_score

FROM student sRIGHT JOIN(SELECT s_id, AVG(score) AS avg_score FROM scoreGROUP BY s_id HAVING avg_score >= 60) bON s.`s_id` = b.s_id;-- 4、查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩显⽰null)SELECT s.`s_id`,s_name,COUNT(c_id)AS 选课总数,SUM(score) AS 总成绩FROM student sLEFT JOIN score scON s.`s_id` = sc.`s_id`GROUP BY s_id;-- 5、查询姓“李”的⽼师的个数SELECT COUNT(t_name) AS ⼈数FROM teacher

WHERE t_name LIKE '李%';-- 6、查询没学过“张三”⽼师课的学⽣的学号、姓名SELECT s_id, s_name FROM student WHERE s_id NOT IN(SELECT s_id FROM score WHERE c_id IN(SELECT c_id FROM course WHERE t_id IN

(SELECT t_id

FROM teacher WHERE t_name = '张三')));--

法⼆SELECT s_id, s_name

FROM studentWHERE s_id NOT IN(SELECT sc.s_id FROM score scINNER JOIN course co ON sc.`c_id` = co.`c_id`INNER JOIN teacher te ON co.`t_id`= te.`t_id`WHERE te.`t_name`='张三');--

法三SELECT s_id, s_nameFROM studentWHERE s_name NOT IN ( SELECT s.s_name FROM student AS s, course AS c, teacher AS t, score AS sc WHERE s.s_id = sc.s_id AND sc.c_id = c.c_id AND c.t_id = t.t_id AND t.t_name = '张三');

-- 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学⽣的学号、姓名SELECT s_id,s_name

FROM student

WHERE s_id IN (SELECT s_idFROM scoreWHERE c_id = '01' OR c_id = '02'GROUP BY s_idHAVING COUNT(c_id) >= 2);-- 8、查询课程编号为“02”的总成绩SELECT SUM(score) AS 总成绩FROM scoreWHERE c_id = '02';

-- 9、查询没有学全所有课的学⽣的学号、姓名SELECT st.`s_id`,st.`s_name`

FROM student stINNER JOIN score scON st.`s_id` = sc.`s_id`GROUP BY sc.`s_id`HAVING COUNT(sc.`c_id`) < (SELECTCOUNT(DISTINCT c_id)

FROM course);-- 10、查询⾄少有⼀门课与学号为“01”的学⽣所学课程相同的学⽣的学号和姓名SELECT st.`s_id`,st.`s_name`FROM student st

WHERE st.`s_id` IN(SELECT DISTINCT sc.`s_id`

FROM score scWHERE sc.`c_id` IN(SELECT sc.`c_id`FROM score scWHERE sc.`s_id` = 01)) ANDst.`s_id` <> '01';--

法⼆SELECT DISTINCT st.`s_id`,st.`s_name`FROM student st

INNER JOIN score scON st.`s_id`= sc.`s_id`WHERE sc.`c_id` IN(SELECT sc.`c_id`FROM score scWHERE sc.`s_id` = '01') ANDst.`s_id` <> '01';-- 11、查询和“01”号同学所学课程完全相同的其他同学的信息SELECT DISTINCT st.*

FROM student stINNER JOIN score scON st.`s_id` = sc.`s_id`WHERE sc.`c_id` IN(SELECT sc.`c_id`FROM score scWHERE sc.`s_id`= '01') AND sc.`s_id` <> '01'GROUP BY sc.`s_id`HAVING COUNT(sc.`c_id`) = (SELECTCOUNT(c_id) FROM score WHERE s_id = '01');-- 12、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩SELECT st.`s_id`,st.`s_name`,AVG() AS avg_scoreFROM student st, score sc

WHERE st.`s_id` = sc.`s_id`AND sc.`score` < 60GROUP BY sc.`s_id`HAVING COUNT(sc.`c_id`) >= 2;-- 13、检索"01"课程分数⼩于60，按分数降序排列的学⽣信息SELECT st.*,sc.`score`FROM student stINNER JOIN score sc ON

st.`s_id`= sc.`s_id`

WHERE sc.`c_id` = '01' AND sc.`score` < 60ORDER BY sc.`score` DESC;-- 14、按平均成绩从⾼到低显⽰所有学⽣的所有课程的成绩以及平均成绩SELECT s_id,

SUM(CASE WHEN c_id = '01' THEN score ELSE NULL END) AS score1,SUM(CASE WHEN c_id = '02' THEN score ELSE NULL END) AS score2,SUM(CASE WHEN c_id = '03' THEN score ELSE NULL END) AS score3,AVG(score)FROM scoreGROUP BY s_idORDER BY AVG(score) DESC;-- 15、查询各科成绩最⾼分、最低分、平均分、及格率、中等率、优良率、优秀率--

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

以如下形式显⽰：课程ID，课程name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率--

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

--

先从简单的写法开始，及格率直接⽤⼩数表⽰，没⽤百分号SELECT co.c_id AS 课程ID, co.c_name AS 课程名称, COUNT(*) AS 选修⼈数,

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 BETWEEN 70 AND 80 THEN 1 ELSE 0 END) / COUNT(*) AS '中等率', SUM(CASE WHEN score BETWEEN 80 AND 90 THEN 1 ELSE 0 END) / COUNT(*) AS '优良率', SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) / COUNT(*) AS '优秀率'FROM score sc, course coWHERE sc.c_id = co.c_id

GROUP BY co.c_idORDER BY COUNT(*) DESC, co.c_id;--

复杂点的写法，及格率⽤百分号表⽰SELECT a.c_id AS '课程ID', course.c_name AS '课程name', COUNT(*) AS 选修⼈数, MAX() AS '最⾼分', MIN() AS '最低分', CAST(AVG() AS DECIMAL(5,2)) AS '平均分', CONCAT(CAST(SUM(pass)/COUNT(*)*100 AS DECIMAL(5,2)),'%') AS '及格率', CONCAT(CAST(SUM(medi)/COUNT(*)*100 AS DECIMAL(5,2)),'%') AS '中等率', CONCAT(CAST(SUM(good)/COUNT(*)*100 AS DECIMAL(5,2)),'%') AS '优良率', CONCAT(CAST(SUM(excellent)/COUNT(*)*100 AS DECIMAL(5,2)),'%') AS '优秀率'

FROM (SELECT * , CASE WHEN score>=60 THEN 1 ELSE 0 END AS pass, CASE WHEN score>=70 AND score<80 THEN 1 ELSE 0 END AS medi, CASE WHEN score>=80 AND score<90 THEN 1 ELSE 0 END AS good, CASE WHEN score>=90 THEN 1 ELSE 0 END AS excellent FROM score) aLEFT JOIN course ON a.c_id=course.c_idGROUP BY a.c_idORDER BY COUNT(*) DESC, a.c_id;-- 16、按平均成绩进⾏排序，显⽰总排名和各科排名，Score

重复时保留名次空缺SELECT s.*, rank_01, rank_02, rank_03, rank_totalFROM student sLEFT JOIN (SELECT s_id, rank() over(PARTITION BY c_id ORDER BY score DESC) AS rank_01 FROM score WHERE c_id=01) A ON s.s_id=A.s_idLEFT JOIN (SELECT s_id, rank() over(PARTITION BY c_id ORDER BY score DESC) AS rank_02 FROM score WHERE c_id=02) B ON s.s_id=B.s_idLEFT JOIN (SELECT s_id, rank() over(PARTITION BY c_id ORDER BY score DESC) AS rank_03 FROM score WHERE c_id=03) C ON s.s_id=C.s_idLEFT JOIN (SELECT s_id, rank() over(ORDER BY AVG(score) DESC) AS rank_total FROM score GROUP BY s_id) D ON s.s_id=D.s_idORDER BY rank_total ASC;-- 17、按各科成绩进⾏排序，并显⽰排名SELECT a.`c_id`,a.`s_id`,a.`score`,COUNT(b.`score`)+1 AS rank

FROM score aLEFT JOIN score bON a.`score`< b.`score` AND a.`c_id`= b.`c_id`GROUP BY a.`c_id`,a.`s_id`,a.`score`ORDER BY a.`c_id`,rank;-- 18、查询学⽣的总成绩并进⾏排名SELECT a.*, @rank:= @rank+1 AS rank

FROM

(SELECT s_id,SUM(score) FROM score

GROUP BY s_id

ORDER BY SUM(score) DESC) a, (SELECT @rank:=0) b;

-- 19、查询不同⽼师所教不同课程平均分从⾼到低显⽰SELECT te.`t_id`, te.`t_name`, AVG(sc.`score`)FROM score sc INNER JOIN course co ON sc.`c_id`= co.`c_id`INNER JOIN teacher te ON co.`t_id`= te.`t_id`GROUP BY te.`t_id`ORDER BY AVG(sc.`score`) DESC;-- 20、查询所有课程的成绩第2名到第3名的学⽣信息及该课程成绩SELECT result.c_id, result.s_id, , student.`s_name`,student.`s_age`,student.`s_sex`FROM

(SELECT *, IF(@pa=a.c_id, @rank:= @rank+1, @rank:=1) AS rank, @pa:=a.c_idFROM

(SELECT c_id, s_id,score FROM scoreGROUP BY c_id, s_id ORDER BY c_id, score DESC) a,(SELECT @rank:=0,@pa:=NULL) b) resultLEFT JOIN student ON result.s_id = student.`s_id`WHERE rank BETWEEN 2 AND 3GROUP BY c_id, score DESC;-- 21、使⽤分段[85-100],[70-84],[60-69],[<60]来统计各科成绩，分别统计各分数段⼈数：课程ID和课程名称SELECT a.c_id AS '课程编号',course.c_name AS '课程名称',SUM(level1) AS '[85-100]⼈数', SUM(level1)/COUNT(1) AS '[85-100]占⽐',SUM(level2) AS '[70-84]⼈数', SUM(level2)/COUNT(1) AS '[70-84]占⽐',SUM(level3) AS '[60-69]⼈数', SUM(level3)/COUNT(1) AS '[60-69]占⽐',SUM(level4) AS '[0-59]⼈数', SUM(level4)/COUNT(1) AS '[0-59]占⽐' FROM(SELECT *,(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS 'level1',(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS 'level1',(CASE WHEN score BETWEEN 70 AND 84 THEN 1 ELSE 0 END) AS 'level2',(CASE WHEN score BETWEEN 60 AND 69 THEN 1 ELSE 0 END) AS 'level3',(CASE WHEN score BETWEEN 0 AND 59 THEN 1 ELSE 0 END) AS 'level4'FROM score) aLEFT JOIN course ON a.c_id=course.c_idGROUP BY a.c_id;-- 22、查询学⽣平均成绩及其名次SELECT a.*,@rank:=@rank+1 AS rankFROM

(SELECT s_id, AVG(score) AS '平均成绩'

FROM score

GROUP BY s_id ORDER BY AVG(score) DESC) a, (SELECT @rank:=0) b;

-- 23、查询各科成绩前三名的记录SELECT a.`c_id`,a.`s_id`,a.`score`FROM score aWHERE (SELECT COUNT(b.s_id) FROM score b WHERE a.`c_id`=b.`c_id` AND a.`score`< b.`score`) < 3GROUP BY a.`c_id`, a.`s_id`;-- 24、查询每门课程被选修的学⽣数SELECT c_id, COUNT(s_id) AS '选修⼈数'FROM scoreGROUP BY c_id;-- 25、

查询出只有两门课程的全部学⽣的学号和姓名SELECT s_id,s_name

FROM student

WHERE s_id IN(SELECT s_id

FROM score

GROUP BY s_idHAVING COUNT(c_id)=2);-- 26、查询男⽣、⼥⽣⼈数SELECT s_sex AS '性别',COUNT(*) AS '⼈数'FROM studentGROUP BY s_sex;-- 27、查询名字中含有"风"字的学⽣信息SELECT *

FROM student

WHERE s_name LIKE '%风%';-- 28、查询同名同姓学⽣名单，并统计同名⼈数SELECT s_name, num AS '同名⼈数'FROM ( SELECT *, COUNT(s_id) -1 AS num

FROM student GROUP BY s_name) a;-- 29、查询1990年出⽣的学⽣名单SELECT *FROM studentWHERE YEAR(s_age) = '1990';-- 30、查询平均成绩⼤于等于85的所有学⽣的学号、姓名和平均成绩SELECT st.`s_id` AS '学号',st.`s_name` AS '姓名',AVG(score) AS '平均成绩'FROM student stINNER JOIN score sc

ON st.`s_id`=sc.`s_id`GROUP BY sc.`s_id`GROUP BY sc.`s_id`HAVING AVG(score)>= 85;-- 31、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列SELECT c_id, AVG(score) AS '平均成绩'FROM scoreGROUP BY c_idORDER BY AVG(score),c_id DESC;-- 32、查询课程名称为"数学"，且分数低于60的学⽣姓名和分数SELECT st.`s_id`,st.`s_name`,score

FROM student stINNER JOIN score scON st.`s_id`=sc.`s_id`WHERE score < 60 AND sc.`c_id` IN (SELECT c_id

FROM course

WHERE c_name = '数学');-- 33、查询所有学⽣的课程及分数情况SELECT sc.`s_id`,SUM(CASE WHEN co.c_name = '语⽂' THEN ELSE NULL END) AS '语⽂成绩',SUM(CASE WHEN co.c_name = '数学' THEN ELSE NULL END) AS '数学成绩',SUM(CASE WHEN co.c_name = '英语' THEN ELSE NULL END) AS '英语成绩'FROM score sc INNER JOIN course co ON sc.`c_id`= co.`c_id`GROUP BY sc.`s_id`;-- 34、查询任何⼀门课程成绩在70分以上的姓名、课程名称和分数SELECT st.`s_name` AS '姓名', co.`c_name` AS '课程名称',sc.`score` AS '分数'FROM student stINNER JOIN score sc ON st.`s_id`= sc.`s_id`INNER JOIN course co ON sc.`c_id`= co.`c_id`WHERE sc.`score` >= 70;-- 35、查询不及格的课程并按课程号从⼤到⼩排列SELECT co.`c_id`,co.`c_name`,sc.`score`FROM course coINNER JOIN score sc ON co.`c_id`=sc.`c_id`WHERE score < 60ORDER BY c_id DESC;-- 36、查询课程编号为03且课程成绩在80分以上的学⽣的学号和姓名SELECT st.`s_id` AS '学号',st.`s_name` AS '姓名'FROM student stINNER JOIN score sc ON st.`s_id`= sc.`s_id`

WHERE c_id = '03' AND score > 80;-- 37、求每门课程的学⽣⼈数SELECT c_id, COUNT(s_id) AS '选课⼈数'FROM scoreGROUP BY c_id;-- 38、成绩不重复，查询选修“张三”⽼师所授课程的学⽣中成绩最⾼的学⽣姓名及其成绩SELECT st.`s_id` AS '学号' , st.`s_name` AS '姓名' ,MAX(sc.`score`) AS '成绩'

FROM student stINNER JOIN score sc ON st.`s_id`= sc.`s_id`WHERE sc.`c_id` IN (SELECT c_id FROM course WHERE t_id IN (SELECT t_id FROM teacher

WHERE t_name = '张三' ));

-- 39、成绩有重复的情况下，查询选修「张三」⽼师所授课程的学⽣中，成绩最⾼的学⽣信息及其成绩SELECT * FROM (SELECT *, DENSE_RANK() over(ORDER BY score DESC) A (SELECT *, DENSE_RANK() over(ORDER BY score DESC) A FROM score WHERE c_id = (SELECT c_id FROM Course WHERE t_id = (SELECT t_id FROM Teacher WHERE t_name='张三'))) BWHERE B.A=1;-- 40、查询不同课程成绩相同的学⽣的学⽣编号、课程编号、学⽣成绩SELECT a.`s_id`,a.`c_id`,b.`c_id`,a.`score`,b.`score`FROM score a, score bWHERE a.`s_id`= b.`s_id` AND a.`score`= b.`score` AND a.`c_id`<> b.`c_id`;-- 41、查询每门功课成绩最好的前两名(SELECT * FROM score WHERE c_id = '01' ORDER BY score DESC LIMIT 2)UNION

(SELECT * FROM score WHERE c_id = '02' ORDER BY score DESC LIMIT 2)UNION(SELECT * FROM score WHERE c_id = '03' ORDER BY score DESC LIMIT 2);-- 42、统计每门课程的学⽣选修⼈数（超过5⼈的课程才统计）。--

要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列SELECT c_id,COUNT(s_id) AS '选修⼈数'FROM scoreGROUP BY c_idHAVING COUNT(s_id) >= 5ORDER BY COUNT(s_id) DESC, c_id;-- 43、检索⾄少选修两门课程的学⽣学号SELECT s_id, COUNT(c_id) AS '选修课程数'FROM score

GROUP BY s_idHAVING COUNT(c_id) >= 2;-- 44、查询选修了全部课程的学⽣信息SELECT *

FROM student

WHERE s_id IN ( SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id) = (SELECT COUNT(DISTINCT c_id) FROM course));-- 45、查询各学⽣的年龄SELECT s_id, s_name, (YEAR(NOW()) - YEAR(s_age)) AS '年龄'FROM student;-- 46、按照出⽣⽇期来算，当前⽉⽇ <

出⽣年⽉的⽉⽇,则年龄减⼀SELECT s_id, s_name, TIMESTAMPDIFF(YEAR,s_age,NOW()) AS '年龄'FROM student;-- 47、查询本周过⽣⽇的学⽣SELECT *FROM studentWHERE WEEK(s_age) = WEEK(NOW());--

以周⼀为⼀周的开始SELECT *FROM studentWHERE WEEK(s_age) = WEEK(NOW(),1);-- 48、查询下周过⽣⽇的学⽣SELECT *FROM studentFROM studentWHERE WEEK(s_age) = WEEK(NOW())+1;--

以周⼀为⼀周的开始SELECT *FROM studentWHERE WEEK(s_age) = WEEK(NOW(),1)+1;-- 49、查询本⽉过⽣⽇的学⽣SELECT *FROM studentWHERE MONTH(s_age) = MONTH(NOW());

-- 50、查询下个⽉过⽣⽇的学⽣SELECT *FROM studentWHERE MONTH(s_age) = MONTH(NOW())+1;