阅读量:0
目录
- MySQL 50 道查询题，足以巩固大部分查询
MySQL 50 道查询题，足以巩固大部分查询
数据准备：
创建表sql
只是简单的演示，就没有搞什么主键和限制之类的。
需要的后续可以添加。
可视化工具使用的是Navicat。
-- 数据准备 -- 创建表命令========================================================== -- 创建学生表 CREATE TABLE student ( s_id VARCHAR (50) COMMENT '学生编号', s_name VARCHAR (50) COMMENT '学生姓名', s_birth DATE COMMENT '出生年月', s_sex VARCHAR (50) COMMENT '性别' ); -- 创建课程表 CREATE TABLE course ( c_id VARCHAR (50) COMMENT '课程编号', c_name VARCHAR (50) COMMENT '课程名称', t_id VARCHAR (50) COMMENT '教师编号' ); -- 创建教师表 CREATE TABLE teacher ( t_id VARCHAR (50) COMMENT '教师编号', t_name VARCHAR (50) COMMENT '教师姓名' ); -- 创建成绩表 CREATE TABLE score ( s_id VARCHAR (50) COMMENT '学生编号', c_id VARCHAR (50) COMMENT '课程编号', s_score VARCHAR (20) COMMENT '分数' ); 添加表数据sql
-- 添加表数据 ========================================================== -- 添加学生表数据 INSERT INTO student ( s_id, s_name, s_birth, s_sex ) 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', '女' ); -- 添加课程表数据 INSERT INTO course (c_id, c_name, t_id ) VALUES ( '01', '语文', '02' ), ( '02', '数学', '01' ), ( '03', '英语', '03' ); -- 添加教师表数据 INSERT INTO teacher ( t_id, t_name ) VALUES ( '01', '张三'), ( '02', '李四'), ( '03', '王五'); -- 添加成绩表数据 INSERT INTO score ( s_id, c_id, s_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); 50道查询题目汇总
因为都写在一篇文章里面会太长，所以分成多篇文章，对应一些更详细的演示，截图和完整的sql，方便查看。
01 - 05 题：
1-5 题：涉及： 内连接、inner join 三表联结，group by、case when ，子查询、聚合函数、子句 等~~
1、查询 “01” 语文成绩比 “02” 数学成绩高的学生的信息及课程分数
2、查询 "01语文课程"比"02数学课程"成绩低的学生的信息及课程分数
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
06 - 10 题：
6-10 题：涉及 count(*/1/字段名)的区别 、not in 、 not exists 、显示、隐式连接、case when 的用法等
6、查询 姓“李”的老师的数量
7、查询学过“张三”老师课程的同学的信息（普通表连接）
8、查询没有学过“张三”老师课程的同学的信息
9、查询学过编号为’01语文’并且也学过编号‘02数学’的课程的同学的信息
10、查询学过编号为‘01语文’但是没有学过编号为‘02数学’的课程的同学的信息
11 - 15 题：
11-15 题：涉及 子查询、in \ not in 关键字、distinct 去重、count()函数、group by、having、case when ~~~
11、查询没有学全所有课程的同学的信息（子查询）
12、查询至少有一门课与【学号为‘01’的同学所学课程相同】的同学的信息
13、查询和‘01’号的同学学习的课程完全相同的其他同学的信息
14、查询没学过“张三” 老师讲授的任一门课程的学生的姓名
15、查询两门及以上不及格课程的同学的学号、姓名及其平均成绩
16 - 20 题：
16-20 题：涉及 临时表用法演示、子查询的使用，开窗函数 rank()、row_number()，其他包括round()、max、min、case when ~~
16、查询 "01"语文课程分数小于60，按分数降序排列的学生信息
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分、及格率，中等率，优良率，优秀率
19、按各科成绩进行排序，并显示排名
20、查询学生的总成绩并进行排名
21 - 25 题：
21-25 题：涉及 子查询、开窗函数dense_rank和rank的区别、case when、round()、in、between 等~~~
21、查询不同老师所教不同课程平均分从高到低显示
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
23、统计各科成绩各分数段人数:课程编号,课程名称,[100-86],[85-70],[69-60]，[0-59]及所占百分比
24、查询学生平均成绩及其名次
25、查询各科成绩前三名的记录
26 - 35 题：
26-35 题：解释在case when 前面加max()函数的原因、一些函数使用、分组排序查询等
26、查询每门课程被选修的学生数
27、查询出只有两门课程的全部学生的学号和姓名
28、查询男生、女生人数
29、查询名字中含有"风"字的学生信息
30、查询同名同性别学生名单，并统计同名人数
(演示：创建临时表并插入演示数据） 31、查询1990年出生的学生名单
32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
34、查询课程名称为"数学”，且分数低于60的学生姓名和分数
35、查询所有学生的课程及分数情况
36 - 45 题：
36-45 题：一些分组排序查询、开窗函数 dense_rank、distinct 去重函数 等 ~
36、查询每一门课程成绩都在70分以上的姓名、课程名称和分数
37、查询不及格的课程及学生
38、查询课程编号为01语文且课程成绩在80分以上的学生的学号和姓名
39、求每门课程的学生人数
40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其所有成绩
41、查询不同课程但成绩相同的学生的学生编号、课程编号、学生成绩
42、查询每门课程成绩最好的前两名
43、统计每门课程的学生选修人数(超过5人的课程才统计)，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
44、检索至少选修两门课程的学生学号
45、查询选修了全部课程的学生信息
46 - 50 题：
日期相关的查询函数
46、查询各学生的年龄
47、查询本周过生日的学生
48、查询下周过生日的学生
49、查询本月过生日的学生
50、查询下月过生日的学生
具体 SQL 汇总
01 - 05 题：
-- 1、查询 “01” 语文成绩比 “02” 数学成绩高的学生的信息及课程分数====================================== -- 涉及到 student、score 这两张表 -- 写法1：内连接 SELECT s.*, s1.s_score AS '语文成绩', s2.s_score '数学成绩' FROM score s1,-- 隐式内连接的写法来连接这三个表 score s2, student s WHERE s1.c_id = '01' -- 查询score表的语文成绩 AND s2.c_id = '02' -- 查询score表的数学成绩 AND s1.s_id = s2.s_id -- 进行表连接 AND s1.s_score > s2.s_score -- 进行成绩判断 AND s.s_id = s1.s_id -- 关联student表的条件 -- 写法2：长型数据变宽型数据写法 SELECT s.*, t.s01 '语文成绩', t.s02 '数学成绩' FROM ( SELECT s.s_id, -- 如果 s.c_id = '01' ，则返回 s.s_score ，否则返回null（else null可省略） max( CASE WHEN s.c_id = '01' THEN s.s_score ELSE NULL END ) AS s01, max( CASE WHEN s.c_id = '02' THEN s.s_score ELSE NULL END ) AS s02 FROM score s GROUP BY s.s_id ) t LEFT JOIN student s ON s.s_id = t.s_id WHERE t.s01 > t.s02 -- 写:3：子查询 select t1.s_name ,t1.c_name, t1.s_score, t2.c_name, t2.s_score from ( -- 查询所有学生的数学成绩 01 select st.s_name , st.s_id, c.c_name, sc.s_score from student st left join score sc on st.s_id = sc.s_id left join course c on c.c_id = sc.c_id where sc.c_id = '01' ) t1 join ( -- 查询所有学生的语文成绩 02 select st.s_name, st.s_id, c.c_name, sc.s_score from student st left join score sc on st.s_id = sc.s_id left join course c on c.c_id = sc.c_id where sc.c_id = '02' ) t2 on t1.s_id = t2.s_id where t1.s_score > t2.s_score -- 写法4：三表联结 SELECT st.*, s1.s_score AS '01语文成绩', s2.s_score AS '02数学成绩' FROM student AS st INNER JOIN ( SELECT * FROM score WHERE c_id = '01' ) AS s1 ON st.s_id = s1.s_id INNER JOIN ( SELECT * FROM score WHERE c_id = '02' ) AS s2 ON s1.s_id = s2.s_id AND s1.s_score > s2.s_score; -- 2、查询 "01语文课程"比"02数学课程"成绩低的学生的信息及课程分数====================================== SELECT sc.*, s1.c_id '语文课程编号', s1.s_score '语文成绩', s2.c_id '数学课程编号', s2.s_score '数学成绩' FROM score s1, score s2, student sc WHERE s1.s_id = s2.s_id -- 表的内连接 AND s1.c_id = '01' -- 表示s1这张表查询出来的是“01”语文成绩 AND s2.c_id = '02' -- 表示s2这张表查询出来的是“02”数学成绩 AND s1.s_score < s2.s_score -- 查询语文成绩比数学成绩低的条件 AND s1.s_id = sc.s_id -- 连接学生表 -- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩====================================== -- 用到 student 学生表 、score 成绩表， -- AVG() 函数用于取平均数 -- ROUND() 函数用于将数值四舍五入到指定的小数位数 -- FLOOR() 函数会返回不大于给定参数的最大整数值 SELECT st.*, ROUND( avg( sc.s_score ), 1 ) '分数' -- 取小数点后1位数 FROM score sc LEFT JOIN student st ON sc.s_id = st.s_id GROUP BY sc.s_id HAVING ROUND( avg( sc.s_score ), 1 ) >= 60 -- 子查询 SELECT sc.s_id, -- 这个子查询的作用是动态地根据主查询中的每个学生 ID，在 student 表中查找对应的学生姓名，并将这个学生姓名作为一个查询字段返回给用户 -- 在这个查询中，首先是从 score 表开始获取数据，然后才会执行子查询来获取对应的学生姓名 (select st.s_name from student st where sc.s_id = st.s_id ) '学生姓名', ROUND( avg( sc.s_score ), 1 ) '分数' -- 取小数点后1位数 FROM score sc GROUP BY sc.s_id HAVING ROUND( avg( sc.s_score ), 1 ) >= 60 -- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩====================================== SELECT st.*, IFNULL( ROUND( AVG( sc.s_score ), 1 ), 0 ) '分数' -- ROUND() 函数用于取小数点后1位数 FROM score sc RIGHT JOIN student st ON sc.s_id = st.s_id -- 右连接，就是连接student这张表 GROUP BY sc.s_id HAVING IFNULL( ROUND( avg( sc.s_score ), 1 ), 0 ) < 60 -- having 后面写条件判断 -- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩====================================== SELECT st.s_id, st.s_name, count( sc.c_id ) '选课总数', IFNULL(sum( sc.s_score ),0) '总成绩' -- 如果成绩为null，则返回0 FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id GROUP BY sc.s_id 06 - 10 题：
-- 6、查询 姓“李”的老师的数量====================================== -- 用到的是 teacher 表 select COUNT(t_name) from teacher where t_name like '李%' -- 7、查询学过“张三”老师课程的同学的信息====================================== -- 用到 student、course、score、teacher 这四张表 SELECT st.* FROM student st JOIN score sc ON sc.s_id = st.s_id JOIN course co ON co.c_id = sc.c_id JOIN teacher t ON t.t_id = co.t_id WHERE t.t_name = '张三' -- 8、查询没有学过“张三”老师课程的同学的信息====================================== -- not in 写法 SELECT * FROM student st WHERE st.s_id NOT IN ( SELECT sc.s_id FROM course co JOIN score sc ON sc.c_id = co.c_id JOIN teacher t ON t.t_id = co.t_id WHERE t.t_name = '张三' ) -- not exists 写法 EXPLAIN SELECT * FROM student st WHERE NOT EXISTS ( SELECT 1 FROM ( SELECT sc.s_id FROM course co JOIN score sc ON sc.c_id = co.c_id JOIN teacher t ON t.t_id = co.t_id WHERE t.t_name = '张三' ) t WHERE t.s_id = st.s_id ) -- 9、查询学过编号为'01语文'并且也学过编号‘02数学’的课程的同学的信息====================================== -- 要查一张表内的同个字段的值的两种判断，可以连接该表2次 -- join 写法 SELECT st.* FROM student st JOIN score s1 ON st.s_id = s1.s_id JOIN score s2 ON s1.s_id = s2.s_id WHERE s1.c_id = '01' AND s2.c_id = '02' -- 自连接写法 SELECT st.* FROM student st, score s1, score s2 WHERE st.s_id = s1.s_id AND s1.s_id = s2.s_id AND s1.c_id = '01' AND s2.c_id = '02' -- 10、查询学过编号为‘01语文’但是没有学过编号为‘02数学’的课程的同学的信息====================================== SELECT st.* FROM student st LEFT JOIN ( -- 子表，用来把score表里面每个学生的‘01’和‘02’课程数据拿出来 SELECT s.s_id, max( CASE WHEN c_id = '01' THEN s_score ELSE NULL END ) s01, max( CASE WHEN c_id = '02' THEN s_score ELSE NULL END ) s02 FROM score s GROUP BY s_id ) t ON t.s_id = st.s_id WHERE t.s01 >= 0 -- 查询学过‘01’课程的学生 AND t.s02 IS NULL -- 查询没有学过‘02’课程的学生 11 - 15 题：
-- 11、查询没有学全所有课程的同学的信息====================================== SELECT st.*, count(sc.c_id) FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id GROUP BY sc.s_id HAVING count(sc.c_id) < ( SELECT count(c_id) FROM course ) -- 12、查询至少有一门课与【学号为‘01’的同学所学课程相同】的同学的信息====================================== -- 先查出学号为‘01’的同学所学的课程有哪些 -- 再查有哪些同学所学的课程至少有一课和‘01同学’相同 SELECT st.* FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id WHERE sc.c_id IN ( SELECT sc.c_id FROM score sc WHERE sc.s_id = '01' ) GROUP BY 1,2,3,4 -- 13、查询和‘01’号的同学学习的课程完全相同的其他同学的信息====================================== -- 14、查询没学过“张三” 老师讲授的任一门课程的学生的姓名====================================== -- 先把学过张三老师课程的学生的id查询出来，然后再使用not in 学生的id是否存在这个数据集合里面 SELECT * FROM student st WHERE -- 2、然后再使用 not in 查出没学过的学生 st.s_id NOT IN ( -- 1、子查询，先把学过张三老师课程的学生的id查询出来 SELECT sc.s_id FROM score sc LEFT JOIN course co ON co.c_id = sc.c_id LEFT JOIN teacher t ON t.t_id = co.t_id WHERE t.t_name = '张三' ) -- 15、查询两门及以上不及格课程的同学的学号、姓名及其平均成绩====================================== -- 写法1： SELECT st.*, count( sc.s_score ) '课程数量', ROUND( avg( sc.s_score ), 1 ) '平均成绩' FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id WHERE sc.s_score < 60 GROUP BY st.s_id HAVING count( sc.s_score ) >= 2 -- 写法2： SELECT st.*, ROUND( AVG( sc.s_score ), 1 ) FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id GROUP BY st.s_id HAVING -- 分数小于60则返回1，然后计算总数，大于2表示有2科不及格 sum(CASE WHEN sc.s_score < 60 THEN 1 ELSE 0 END ) >=2 16 - 20 题：
-- 16、查询 "01"语文课程分数小于60，按分数降序排列的学生信息====================================== SELECT st.*, sc.s_score '分数' FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id WHERE sc.s_score < 60 GROUP BY sc.s_id ORDER BY sc.s_score DESC -- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩====================================== -- 写法1： SELECT st.s_name, MAX(case when sc.c_id = '01' then sc.s_score else 0 end) '01语文', MAX(case when sc.c_id = '02' then sc.s_score else 0 end) '02数学', MAX(case when sc.c_id = '03' then sc.s_score else 0 end) '03英语', ROUND( AVG( SC.s_score ), 1 ) '平均成绩' FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id GROUP BY sc.s_id ORDER BY ROUND( AVG( SC.s_score ), 1 ) DESC -- 写法2： SELECT t1.*, t2.avg_s FROM ( SELECT * FROM score sc1 ) t1, ( SELECT sc2.s_id, ROUND( avg( sc2.s_score ), 1 ) avg_s FROM score sc2 GROUP BY sc2.s_id ) t2 WHERE t1.s_id = t2.s_id ORDER BY avg_s DESC -- 写法3：开窗函数 SELECT sc.*, avg(sc.s_score) over(partition by sc.s_id) '平均分数' FROM score sc -- ====================================== -- 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分、及格率，中等率，优良率，优秀率 -- D及格为： >= 60 , C中等为：70-80，B优良为：80-90，A优秀为：>=90 SELECT sc.c_id, co.c_name, round(MAX( sc.s_score ),2) max_s, round(MIN( sc.s_score ),2) min_s, round(AVG( sc.s_score ),2) avg_s, -- 如果分数>=60,则返回1，表示该行数据符合条件，否则返回0， -- 然后用sum函数把符合条件的数据求和，再除以总数，就是及格率 round(sum(case when sc.s_score >= 60 then 1 else 0 end )/count(1),2) D, round(sum(case when 80 > sc.s_score >= 70 then 1 else 0 end )/count(1),2) C, round(sum(case when 90 > sc.s_score >= 80 then 1 else 0 end )/count(1),2) B, round(sum(case when sc.s_score >= 90 then 1 else 0 end )/count(1),2) A FROM course co LEFT JOIN score sc ON sc.c_id = co.c_id GROUP BY sc.c_id -- 19、按各科成绩进行排序，并显示排名====================================== -- 开窗函数写法 explain SELECT sc.*, rank() over(partition by sc.c_id order by sc.s_score desc) rk FROM score sc SELECT sc.*, row_number() over(partition by sc.c_id order by sc.s_score desc) rk FROM score sc -- 子查询写法 SELECT sc.* , -- 这个子查询：拿主查询的表数据和相同数据的子表进行比较，用 count() 函数统计， -- 比较当前学生（sc表的数据）所在课程（c_id）的成绩（s_score）是否比其他学生（sc2表）在同一门课程下的成绩低 -- +1 是因为排名是从1开始的，不是从0开始的 (select count(s_score) from score sc2 where sc.c_id = sc2.c_id and sc.s_score < sc2.s_score)+1 '分数排名' FROM score sc ORDER BY sc.c_id, sc.s_score DESC -- 20、查询学生的总成绩并进行排名====================================== -- 写法1：子查询+开窗函数 SELECT t.*, rank() over(ORDER BY t.sum_s DESC) rk FROM ( SELECT sc.s_id, sum( sc.s_score ) sum_s FROM score sc GROUP BY sc.s_id ) t -- 写法2：临时表+子查询 -- 创建临时表 CREATE TEMPORARY TABLE sum_s_temp AS SELECT sc.s_id, sum( sc.s_score ) sum_s FROM score sc GROUP BY sc.s_id -- 查询临时表 select * from sum_s_temp -- 用两张一样的临时表来一行一行比对 SELECT t1.*, rank() OVER (ORDER BY sum_s DESC) AS rk -- 这个子查询报错信息【- Can't reopen table: 't1'】 -- (SELECT count(sum_s) FROM sum_s_temp t2 WHERE t1.sum_s < t2.sum_s )+1 rk FROM sum_s_temp t1 ORDER BY sum_s DESC 21 - 25 题：
-- 21、查询不同老师所教不同课程平均分从高到低显示 -- 普通分组排序写法： SELECT te.*, co.c_name, ROUND(AVG( sc.s_score ),1) avg_s FROM teacher te LEFT JOIN course co ON te.t_id = co.t_id LEFT JOIN score sc ON sc.c_id = co.c_id GROUP BY te.t_id ORDER BY AVG( sc.s_score ) DESC -- ☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆ -- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 -- 即语文数学英语成绩都放在一起的第二和第三名 -- 2、然后主查询这里再根据条件获取数据 SELECT * FROM ( -- 1、先用子查询，把分数排名排好序 SELECT st.s_name, sc.c_id, co.c_name, sc.s_score, rank () over ( ORDER BY sc.s_score DESC ) rk FROM course co LEFT JOIN score sc ON sc.c_id = co.c_id LEFT JOIN student st ON st.s_id = sc.s_id ) t WHERE t.rk IN ( 2, 3 ) -- ☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆ -- 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-86],[85-70],[69-60]，[0-59]及所占百分比 SELECT co.c_id,co.c_name, ROUND(sum( CASE WHEN sc.s_score BETWEEN 86 AND 100 THEN 1 ELSE 0 END ),2) AS "[100-86]", ROUND(sum( CASE WHEN sc.s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END ),2) AS "[85-70]", ROUND(sum( CASE WHEN sc.s_score BETWEEN 60 AND 69 THEN 1 ELSE 0 END ),2) AS "[69-60]", ROUND(sum( CASE WHEN sc.s_score BETWEEN 0 AND 59 THEN 1 ELSE 0 END ),2) AS "[0-59]", ROUND(sum( CASE WHEN sc.s_score BETWEEN 86 AND 100 THEN 1 ELSE 0 END )/ count( 1 ),2) AS "[100-86]%", ROUND(sum( CASE WHEN sc.s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END )/ count( 1 ),2) AS "[85-70]%", ROUND(sum( CASE WHEN sc.s_score BETWEEN 60 AND 69 THEN 1 ELSE 0 END )/ count( 1 ),2) AS "[69-60]%", ROUND(sum( CASE WHEN sc.s_score BETWEEN 0 AND 59 THEN 1 ELSE 0 END )/ count( 1 ),2) AS "[0-59]%" FROM score sc left join course co on sc.c_id = co.c_id GROUP BY sc.c_id -- ☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆ -- 24、查询学生平均成绩及其名次 SELECT st.s_name, t.avg_s, -- 2、再用 rank 函数给成绩排名并加上排序 rank () over ( ORDER BY t.avg_s DESC ) rk FROM student st LEFT JOIN ( -- 1、先查询每个学生的平均成绩 SELECT s_id, round( avg( s_score ), 2 ) avg_s FROM score GROUP BY s_id ) t ON st.s_id = t.s_id -- ☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆ -- 25、查询各科成绩前三名的记录 SELECT st.s_id, st.s_name, co.c_name, t.s_score, t.rk '名次' FROM ( SELECT *, dense_rank () over ( PARTITION BY c_id ORDER BY s_score DESC ) rk FROM score ) t LEFT JOIN student st ON st.s_id = t.s_id LEFT JOIN course co ON co.c_id = t.c_id WHERE t.rk IN ( 1, 2, 3 ) -- dense_rank 和 rank 的区别 SELECT *, dense_rank() over ( PARTITION BY c_id ORDER BY s_score DESC ) rk FROM score SELECT *, rank() over ( PARTITION BY c_id ORDER BY s_score DESC ) rk FROM score 26 - 35 题：
-- 26、查询每门课程被选修的学生数 -- 写法1： SELECT sum( CASE WHEN c_id = '01' THEN 1 ELSE 0 END ) '语文', sum( CASE WHEN c_id = '02' THEN 1 ELSE 0 END ) '数学', sum( CASE WHEN c_id = '03' THEN 1 ELSE 0 END ) '英语' FROM score -- 写法2： SELECT sc.c_id, co.c_name, count( sc.c_id ) '选修人数' FROM score sc LEFT JOIN course co ON sc.c_id = co.c_id GROUP BY sc.c_id -- 27、查询出只有两门课程的全部学生的学号和姓名 SELECT st.s_id, st.s_name FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id GROUP BY st.s_id HAVING count( sc.c_id ) = 2 -- 28、查询男生、女生人数 -- 写法1： SELECT sum(case when s_sex = '男' then 1 else 0 end) '男生人数', sum(case when s_sex = '女' then 1 else 0 end) '女生人数' FROM student -- 写法2： SELECT t1.boy, t2.girl FROM ( SELECT count( s_sex ) boy FROM student WHERE s_sex = '男' ) t1 JOIN ( SELECT count( s_sex ) girl FROM student WHERE s_sex = '女' ) t2 -- 写法3： SELECT s_sex, count( s_id ) FROM student GROUP BY s_sex -- 29、查询名字中含有"风"字的学生信息 SELECT * FROM student WHERE s_name LIKE '%风%' -- 30、查询同名同性别学生名单，并统计同名人数 -- 创建临时表 CREATE TEMPORARY TABLE temp_students ( s_id INT PRIMARY KEY, s_name VARCHAR(255), s_birth VARCHAR(255), s_sex VARCHAR(255) ); -- 插入数据 -- INSERT INTO ... SELECT 是一种从一个表复制数据到另一个表的方法 INSERT INTO temp_students ( s_id, s_name, s_birth, s_sex ) SELECT s_id, s_name, s_birth, s_sex FROM student -- 插入同名同性别的数据 INSERT INTO temp_students ( s_id, s_name, s_birth, s_sex ) VALUES ( '9', '赵雷' ,'1990-01-01', '男' ), ( '10', '钱电', '1990-01-01' ,'女' ) -- 查询数据 SELECT * FROM temp_students -- 查询同名同性别学生名单，并统计同名人数 SELECT s_name, s_sex, count( s_name ) FROM temp_students GROUP BY s_name, s_sex HAVING count( s_name ) > 1 -- 31、查询1990年出生的学生名单 -- like 写法 select * from student where s_birth like '1990%' -- YEAR() 函数用于从日期中提取年份部分 select * from student where year(s_birth) = 1990 -- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 SELECT co.c_name, round( avg( sc.s_score ), 2 ) FROM score sc LEFT JOIN course co ON co.c_id = sc.c_id GROUP BY sc.c_id ORDER BY round( avg( sc.s_score ), 2 ) DESC, sc.c_id ASC -- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 SELECT st.s_id, st.s_name, IFNULL(round(avg( sc.s_score )),0) '平均成绩' FROM score sc RIGHT JOIN student st ON st.s_id = sc.s_id GROUP BY sc.s_id HAVING round(avg( sc.s_score )) >= 85 -- 34、查询课程名称为"数学”，且分数低于60的学生姓名和分数 SELECT st.s_name, sc.s_score FROM course co LEFT JOIN score sc ON co.c_id = sc.c_id LEFT JOIN student st ON st.s_id = sc.s_id WHERE co.c_name = "数学" AND sc.s_score < 60 select * from score where c_id = '02' and s_score < 60 -- 35、查询所有学生的课程及分数情况 select t.s_name, max(case when t.c_id = '01' then t.s_score else 0 end) '语文', max(case when t.c_id = '02' then t.s_score else 0 end) '数学', max(case when t.c_id = '03' then t.s_score else 0 end) '英语' from (select st.s_name,sc.* from student st left join score sc on st.s_id = sc.s_id) t GROUP BY t.s_id select t.s_name, case when t.c_id = '01' then t.s_score else 0 end '语文', case when t.c_id = '02' then t.s_score else 0 end '数学', case when t.c_id = '03' then t.s_score else 0 end '英语' from (select st.s_name,sc.* from student st left join score sc on st.s_id = sc.s_id) t GROUP BY t.s_id 36 - 45 题：
-- 36、查询每一门课程成绩都在70分以上的学生的姓名、课程名称和分数 SELECT st.s_name, co.c_name, sc.s_score FROM score sc LEFT JOIN course co ON co.c_id = sc.c_id LEFT JOIN student st ON st.s_id = sc.s_id WHERE st.s_id in ( -- 先查询出3个成绩都70分以上的学生的id select s_id from score group by s_id having min(s_score) >= 70 ) -- 37、查询不及格的课程及学生 SELECT st.s_name, co.c_name, sc.s_score FROM score sc LEFT JOIN course co ON sc.c_id = co.c_id LEFT JOIN student st ON st.s_id = sc.s_id WHERE sc.s_score < 60 -- 38、查询课程编号为01语文且课程成绩在80分以上的学生的学号和姓名 SELECT st.s_id, st.s_name, co.c_name, sc.s_score FROM course co LEFT JOIN score sc ON sc.c_id = co.c_id LEFT JOIN student st ON st.s_id = sc.s_id WHERE co.c_id = '01' AND sc.s_score >= 80 -- 39、求每门课程的学生人数 -- 宽型数据格式 SELECT sum(case when sc.c_id = '01' then 1 else 0 end) '语文', sum(case when sc.c_id = '02' then 1 else 0 end) '数学', sum(case when sc.c_id = '03' then 1 else 0 end) '英语' FROM course co LEFT JOIN score sc ON co.c_id = sc.c_id -- 长型数据格式 SELECT co.c_name, count(sc.s_id) '人数' FROM course co LEFT JOIN score sc ON sc.c_id = co.c_id GROUP BY co.c_id -- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其所有成绩 SELECT st.*,co.c_name,sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id LEFT JOIN course co ON co.c_id = sc.c_id WHERE st.s_id = ( SELECT sc.s_id FROM teacher te LEFT JOIN course co ON co.t_id = te.t_id LEFT JOIN score sc ON sc.c_id = co.c_id WHERE te.t_name = '张三' ORDER BY sc.s_score DESC -- limit 1 返回查询结果的第一行数据 LIMIT 1 ) -- 41、查询不同课程但成绩相同的学生的学生编号、课程编号、学生成绩 SELECT distinct s1.*, co.c_name, st.s_name FROM score s1 LEFT JOIN score s2 ON s1.c_id != s2.c_id LEFT JOIN course co ON co.c_id = s1.c_id LEFT JOIN student st ON st.s_id = s1.s_id WHERE s1.s_score = s2.s_score -- 42、查询每门课程成绩最好的前两名 -- 开窗函数 dense_rank 写法 SELECT st.s_id, st.s_name, t.c_name, t.s_score, t.drk FROM student st RIGHT JOIN ( SELECT sc.*, co.c_name, dense_rank () over ( PARTITION BY sc.c_id ORDER BY sc.s_score DESC ) drk FROM score sc LEFT JOIN course co on co.c_id = sc.c_id ) t ON t.s_id = st.s_id WHERE t.drk IN (1,2) -- 子查询写法 SELECT * FROM score s1 WHERE -- 这个子查询相当于上面的开窗函数 ( SELECT count( s2.s_score ) FROM score s2 WHERE s1.c_id = s2.c_id AND s1.s_score < s2.s_score ) + 1 <= 2 ORDER BY s1.c_id, s1.s_score DESC -- 43、统计每门课程的学生选修人数(超过5人的课程才统计)， -- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 SELECT sc.c_id '课程编号', count( 1 ) cnt FROM score sc GROUP BY sc.c_id HAVING count( 1 )>= 5 ORDER BY cnt DESC, -- 按人数降序排列 sc.c_id ASC -- 按课程号升序排列 -- 44、检索至少选修两门课程的学生学号 SELECT sc.s_id , st.s_name, count( sc.s_id ) '选修课程数' FROM score sc LEFT JOIN student st ON st.s_id = sc.s_id GROUP BY sc.s_id HAVING count( sc.s_id ) >= 2 -- 45、查询选修了全部课程的学生信息 SELECT st.* FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id GROUP BY sc.s_id HAVING count( sc.s_id ) = ( SELECT count( 1 ) FROM course ) 46 - 50 题：
-- 日期相关================================== -- 返回当前日期，格式为： 2024-04-05 22:21:27 select now(); -- 获取当前日期，返回 年月日，格式为：2024-04-05 select CURDATE(); -- 日期格式化，格式为：0405 select date_format(now(),'%m%d') -- 不规范的字符串日期格式化 select STR_TO_DATE('980101','%y%m%d') -- 格式为：1998-01-01 select STR_TO_DATE('170101','%y%m%d') -- 格式为：2017-01-01 -- 返回当前日期的年份，格式为：2024 select year(now()); -- 返回当前日期的月份，格式为：4 select month(now()); -- 返回当前日期的天数，格式为：5 select day(now()); -- 返回当前日期在当前年份中的第几天，格式为：96 select dayofyear(now()); -- 返回当前日期所在的年份中的周数，就是这周是今年的第几个周，格式为：14 select weekofyear(now()); -- 46、查询各学生的年龄 select *,YEAR(now()) - YEAR(s_birth) '年龄' from student select st.*,timestampdiff(YEAR,st.s_birth,CURDATE()) '年龄' from student st -- 47、查询本周过生日的学生 -- 修改一条数据，让一个学生的生日是今天，CURRENT_DATE 返回当前年月日 UPDATE student set s_birth = CURRENT_DATE where s_id = '01' select * from student -- 写法1： select * from student where weekofyear(s_birth) = weekofyear(now()) -- 写法2： 针对不规范日期格式的判断写法 select * from student where weekofyear( str_to_date( concat( year ( now()), -- 获取当前年份：2024 date_format( s_birth, '%m%d' ) -- 把每个学生的生日的月份和天数 ), '%Y%m%d' -- 通过 concat 把两个值合并成这个年月日格式 ) -- 把不规范的字符串日期格式化 ) -- 获取学生生日日期所在的周是今年的第几个周 = weekofyear(now()) -- 获取当前日期是今年的第几个周 -- 格式化： SELECT * FROM student WHERE weekofyear( str_to_date( concat( YEAR ( now()), date_format( s_birth, '%m%d' ) ), '%Y%m%d' ) ) = weekofyear(now()) -- 48、查询下周过生日的学生 -- interval '7' day 给指定日期加 7 天, day就是+天数，month +月份，year +年份 select now(), now() + interval '7' day -- 把今天生日的01号学生，再次修改为下周生日 UPDATE student set s_birth = now() + interval '7' day where s_id = '01' select * from student -- 和第47到一样，就是多了 【 + interval '7' day 】 SELECT * FROM student WHERE weekofyear( str_to_date( concat( YEAR ( now()), date_format( s_birth, '%m%d' ) ), '%Y%m%d' ) ) = weekofyear(now() + interval '7' day) -- 简单写法： select * from student where weekofyear(s_birth) = weekofyear(now() + interval '7' day) -- 49、查询本月过生日的学生 -- 查当前月份 select month(now()) -- 获取每个学生生日的月份 select *,month(s_birth) from student select * from student where month(now()) = month(s_birth) -- 50、查询下月过生日的学生 -- 当前日期 select now(); -- 当前日期再加一个月 select now() + interval '1' month; select * from student select * from student where month(now() + interval '1' month) = month(s_birth) 