关于一个Java程序员马上要笔试了，临时抱佛脚，一晚上恶补45道简单SQL题，希望笔试能通过
MySQL随手练 / DQL篇
MySQL随手练——DQL篇
题目网盘下载：https://pan.baidu.com/s/1Ky-RJRNyfvlEJldNL_yQEQ?pwd=lana
初始数据
 表 course |  表 student |  表 teacher |  表 sc |
答案 :) —> :( —> :)
1. 查询 "01"课程比"02"课程成绩高的学生的信息及课程分数
SELECTc.S,c.Sage,c.Sname,c.Ssex,a.C AS C1,a.score AS score1,b.C AS C2,b.score AS score2
FROMsc aINNER JOIN sc b ON a.S = b.SINNER JOIN student c ON b.S = c.S
WHEREa.C = '01' AND b.C = '02' AND a.score > b.score
1.1 查询同时存在" 01 “课程和” 02 "课程的情况
SELECTa.S,a.C AS C1,a.score AS score1,b.C AS C2,b.score AS score2
FROMsc aINNER JOIN sc b ON a.S = b.S AND a.C = '01' AND b.C = '02'
1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
SELECTa.S,a.C AS C1,a.score AS score1,b.C AS C2,b.score AS score2
FROMsc aLEFT JOIN sc b # RIGHT JOIN sc bON a.S = b.S AND b.C = '02'
WHEREa.C = '01'
1.3 查询不存在"01 “课程但存在” 02 "课程的情况
SELECTa.S,a.C,a.score
FROMsc aLEFT JOIN sc b ON a.S = b.S AND b.C = '01'
WHEREa.C = '02' AND IFNULL( b.c, 'null' ) = 'null'
# WHERE a.C = '02' AND b.c IS NULL
问题环节：
一、题1.2中为什么LEFT JOIN ON后面接AND 左表a.C = '01’无效？题1.2中为什么RIGHT JOIN ON 后面接AND 右表b.C = '02’无效？题1.1中为什么INNER JOIN ON 后面接AND 左表a.C = ‘01’ AND 右表b.C = '02’却有效呢？
实现INNER JOIN和OUTER JOIN都是按照ON后面条件进行条件判断后进行笛卡尔积拼接，将符合条件的数据行标记为true，最后根据各自的特性生成临时表。而区别就在这特性上面，INNER JOIN是全连接，只有两表中都符合ON AND后面条件的数据才会保留；OUTER JOIN是左外连接（右外连接），以左表（右表）为主体，右表（左表）有符合的数据就保留，没有展示null。通俗点讲：INNER JOIN和OUTER JOIN都是将两表中都符合ON AND后面条件的数据进行保留，但OUTER JOIN要满足自己的特性，所以会把不符合条件的数据用null展示，所以LEFT JOIN ON后面接AND 左表a.C = '01’无效和RIGHT JOIN ON 后面接AND 右表b.C = '02’无效，单纯就是进行了暗箱操作，我们没看到
二、IS NULL能不能优化一下?
IFNULL() > IS NULL > ISNULL()
2. 查询平均成绩大于等于60分的同学的学生编号，姓名和平均成绩
SELECTa.S,a.Sname,AVG( b.score ) AS score_avg
FROMstudent aINNER JOIN sc b ON a.S = b.S
GROUP BYa.Sname
HAVINGscore_avg >= 60
问题环节：
一、分组聚合函数作用域？
DQL执行顺序：FROM > ON >JOIN > WHERE > GROUP BY > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT
GROUP BY分组后才能使用，所以HAVING、SELECT、ORDER BY 都可以
二、DQL有执行顺序，那DML呢？
MySQL 采用了一种 WAL(先写日志再写磁盘) 技术，涉及两个日志文件和MySQL服务层及引擎层（InnoDB）（bin log和redo log：这部分不懂的，先去了解）。
下面针对以下这句DML语句进行分析：update table set value = ‘newValue‘ where id = 1;
简单推演： 从内存中取出id=1的那行数据（如果内存中不存在就从磁盘中查询并放入内存），修改对应字段数据，存入内存，等待回刷到磁盘。<解说存在一致性问题：服务层记录bin log，引擎层记录redo log>
初级推演：采用2PC保证 DML经过MySQL服务层执行器进入引擎层，InnoDB 引擎去查看当前内存中是否存在该数据行，如果存在之间从内存中取出，如果不在那么会从磁盘中 load 到内存之后再从内存中取出相应数据行，然后将数据行进行更新并将新行写入内存中，之后就会开始写日志，首先是 redo log的写入(此时进入prepare状态)，最后进行事务的提交， bin log、redo log的写入(此时进入commit状态)
进阶推演： redo log三种形态（内存< redo log buffer >、文件系统缓存< page cache >、磁盘< disk >）。
DML经过MySQL服务层执行器进入引擎层，InnoDB 引擎去 引擎层buffer pool中获取id=1的数据行，不存在就从disk中获取然后load到 buffer pool。存在就从相应的数据页中取出给数据行进行修改再放入 buffer pool，同时将操作经过write position记录到redo log buffer（prepare状态）并且返回结果，事务提交， bin log、redo log的写入(此时进入commit状态)
究极推演： 由你完成，我顶不住了。。。。。。
三、简单介绍MySQL 8.0服务层
一条SQL语句的旅程：
- 1、解析阶段：解析为一颗Parser Tree
- 2、准备阶段：经历Resolve和Transform形成Abstract Syntax Tree
- 3、优化阶段：SQL语句优化
- 4、执行阶段：推向引擎层
3. 查询在sc表存在成绩的学生信息
SELECTa.S,a.Sname,a.Sage,a.Ssex
FROMstudent aLEFT JOIN sc b ON a.S = b.S
WHEREIFNULL( b.score, 'null' ) != 'null'
GROUP BYa.S
问题环节：
一、DISTINCT a.S,a.Sname,a.Sage,a.Ssex失效？怎么指针对a.S去重？
首先并没有失效，需要a.S,a.Sname,a.Sage,a.Ssex都相同才能去重。
针对a.S去重，可以用GROUP BY a.S、DISTINCT(a.S)、GROUP_CONCAT(DISTINCT a.S)结合GROUP BY a.S
4. 查询所有同学的学生编号，学生姓名，选课总数，所有课程总成绩(没成绩显示为0)
SELECTa.S,a.Sname,COUNT( a.S ) AS course_sum,IFNULL( SUM( b.score ), 0 ) AS score_sum
FROMstudent aLEFT JOIN sc b ON a.S = b.S
GROUP BYa.S
问题环节：
一、替换函数？
IFNULL(b.score,0.0)，COALESCE(b.score,0.0)
5. 查询李姓老师的数量
SELECTCOUNT( t.T )
FROMteacher t
WHEREt.Tname LIKE '李%'
6. 查询学过张三老师授课的同学信息
SELECTa.S,a.Sage,a.Sname,a.Ssex
FROMteacher tLEFT JOIN course c ON t.T = c.TRIGHT JOIN sc b ON c.C = b.CRIGHT JOIN student a ON a.S = b.S
WHEREt.Tname = '张三'
7. 查询没有学全所有课程的同学的信息
SELECTa.S,a.Sname,a.Sage,a.Ssex
FROMstudent aLEFT JOIN sc b ON a.S = b.S
GROUP BYa.S
HAVINGCOUNT( a.S ) < ( SELECT COUNT( 1 ) FROM course )；
8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECTa.S,a.Sname,a.Sage,a.Ssex
FROMsc bINNER JOIN sc d ON b.C = d.CLEFT JOIN student a ON d.S = a.S
WHEREb.S = '01' AND d.S <> '01'
GROUP BYd.S
9. 查询和’01’号同学学习的课程完全相同的其它同学的信息
SELECTa.S,a.Sname,a.Sage,a.Ssex
FROMsc bINNER JOIN sc d ON b.C = d.CLEFT JOIN student a ON d.S = a.S
WHEREb.S = '01' AND d.S <> '01'
GROUP BYd.S
HAVINGCOUNT( d.s ) = ( SELECT COUNT( S ) FROM sc WHERE S = '01' );
问题环节：
一、in会影响查询速度吗？
因值而定，in 的值不要超过 500 个，in 操作可以更有效的利用索引，那500哪里来的：IN查询字段值的个数受eq_range_index_dive_limit这个参数影响，建议个数不要超过该参数所配置的大小。
对于in值大的，可以分批查询，然后union all
10. 查询没有学过"张三"老师任意一门课程的学生信息
SELECTd.S,d.Sname,d.Sage,d.Ssex
FROMcourse cINNER JOIN teacher t ON c.T = t.T AND t.Tname = '张三'LEFT JOIN sc b ON c.C = b.CINNER JOIN student a ON a.S = b.SRIGHT JOIN student d ON a.S = d.S
WHEREIFNULL( a.S, 'null' ) = 'null'
11. 查询两门课及以上不及格课程的同学的学号，姓名以及平均成绩
SELECTa.S,a.Sname,AVG( b.score ) AS score_avg
FROMsc bINNER JOIN student a ON b.S = a.S AND b.score < 60 LEFT JOIN sc d ON b.S = d.S GROUP BY b.S HAVING COUNT( b.S ) >= 2
问题环节：
一、笛卡尔积乘积会不会影响求平均值？
以上面答案中出现的情况是LEFT JOIN sc d 因为ON b.S = d.S都不是唯一id导致出现笛卡尔积乘积现象，但，并不会影响b.score求平均值，因为(SUM(b.score)*n)/(COUNT(b.score)*n)，分子分母都乘上n
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT*
FROMsc bINNER JOIN student a ON b.S = a.S AND b.score < 60
WHEREb.C = '01'
ORDER BYb.S DESC
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECTa.S,a.Sname,d.score,k.score_avg
FROM(SELECTb.S,AVG( b.score ) AS score_avg FROMsc b GROUP BYb.S ORDER BYAVG( b.score ) DESC ) kLEFT JOIN sc d ON k.S = d.SLEFT JOIN student a ON k.S = a.S
14. 查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列.
SELECTb.C,c.Cname,MAX( b.score ) AS score_max,MIN( b.score ) AS score_min,AVG( b.score ) AS score_avg,SUM(IF( b.score >= 60, 1, 0 ))/ COUNT( b.S ) AS pass_rate,SUM(IF( b.score >= 70 AND b.score < 80, 1, 0 ))/ COUNT( b.S ) AS medium_rate,SUM(IF( b.score >= 80 AND b.score < 90, 1, 0 ))/ COUNT( b.S ) AS excellent_rate,SUM(IF( b.score >= 90, 1, 0 ))/ COUNT( b.S ) AS excellence_rate,COUNT( 1 ) AS cons
FROMsc bINNER JOIN course c ON b.C = c.C
GROUP BYb.C
ORDER BYCOUNT( 1 ) DESC,b.C ASC
15. 按各科成绩进行排序，并显示排名
SELECTb.C,b.S,b.score,@rank := @rank + 1 AS rk
FROMsc b,(SELECT@rank := 0 ) AS t
ORDER BYb.score DESC
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
SELECTb.C,b.S,@score := score AS score,
CASEWHEN @score = b.score THEN@rank ELSE @rank := @rank + 1 END AS rk
FROMsc b,(SELECT@rank := 0,@score := 0 ) AS t
ORDER BYb.score DESC
16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
SELECT
CASEWHEN@score = s.score_sum THEN'' ELSE @rank := @rank + 1 END AS rk,@score := s.score_sum AS score FROM( SELECT SUM( score ) AS score_sum FROM sc GROUP BY S ORDER BY score_sum DESC ) s,(SELECT@rank := 0,@score := NULL ) t
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85) ，[85-70)，[70-60)，[60-0]及所占百分比
SELECTc.C,c.Cname,COUNT( 1 ) AS '人数',SUM(IF( b.score > 85 AND b.score <= 100, 1, 0 ))/ COUNT( 1 ) AS '[100-85)',SUM(IF( b.score > 70 AND b.score <= 85, 1, 0 ))/ COUNT( 1 ) AS '[85-70)',SUM(IF( b.score > 60 AND b.score <= 70, 1, 0 ))/ COUNT( 1 ) AS '[70-60)',SUM(IF( b.score > 0 AND b.score <= 60, 1, 0 ))/ COUNT( 1 ) AS '[60-0]'
FROMsc bINNER JOIN course c ON b.C = c.C
GROUP BYb.C
18. 查询各科成绩前三名的记录
SELECTa.S,a.Sname,b.C,b.score
FROMsc bLEFT JOIN sc d ON b.C = d.C AND b.score < d.scoreRIGHT JOIN student a ON b.S = a.S
GROUP BYb.S,b.C,b.score
HAVINGCOUNT( 1 ) < 3
ORDER BYb.C,b.score DESC19. 查询每门课程被选修的学生数
SELECTb.C,COUNT( 1 )
FROMsc b
GROUP BYb.C
20 . 查询出只选修两门课程的学生学号和姓名
SELECT*
FROMsc b
GROUP BYb.S
HAVINGCOUNT( 1 )=2
21. 查询男生女生人数
SELECTa.Ssex,COUNT( a.Ssex ) AS sex_count
FROMstudent a
GROUP BYa.Ssex
22. 查询名字中含有「风」字的学生信息
SELECTa.S,a.Sname,a.Sage,a.Ssex
FROMstudent a
WHERELOCATE( '风', a.Sname ) > 0
问题环节：
一、给’%风%'提提速
LOCATE(substr,str)、LOCATE(substr,str, pos)、POSITION(‘substr’ INfield)、FIND_IN_SET(str,strlist)
23. 查询同名同性学生名单，并统计同名人数
SELECTa.Sname,COUNT( 1 ) AS name_count
FROMstudent aINNER JOIN student d ON a.Sname = d.Sname AND a.S <> d.S
24. 查询 1990 年出生的学生名单
SELECTa.Sname
FROMstudent a
WHEREYEAR ( a.Sage ) = '2022'
25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT*,AVG( a.score )
FROMsc a
GROUP BYa.C
ORDER BYAVG( a.score ) DESC,a.C ASC
26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECTa.S,a.Sname,AVG( b.score ) AS score_avg
FROMstudent aINNER JOIN sc b ON a.S = b.S
GROUP BYa.S
HAVINGscore_avg >= 85
27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECTa.Sname,b.score
FROMsc bINNER JOIN course c ON b.C = c.C AND c.Cname = '数学'INNER JOIN student a ON b.S = a.S
WHEREb.score < 60
28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECTa.S,a.Sname,IFNULL( b.C, '未选修' ) AS C,IFNULL( b.score, 0 ) AS score
FROMstudent aLEFT JOIN sc b ON a.S = b.S
29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECTa.Sname,c.Cname,b.score
FROMsc bLEFT JOIN student a ON b.S = a.SLEFT JOIN course c ON b.C = c.C
WHEREb.score > 70;
30. 查询不及格的课程
SELECT DISTINCTb.C
FROMsc b
WHEREb.score < 60
31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECTa.S,a.Sname
FROMsc bLEFT JOIN student a ON b.S = a.S
WHEREb.C = '01' AND b.score > 80
32. 求每门课程的学生人数
SELECTb.C,COUNT( b.C ) AS c_count
FROMsc b
GROUP BYb.C
33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECTa.S,a.Sname,a.Sage,a.Ssex,b.score
FROMsc bLEFT JOIN student a ON b.S = a.SLEFT JOIN course c ON b.C = c.CINNER JOIN teacher t ON c.T = t.T AND t.Tname = '张三'
ORDER BYb.score DESC LIMIT 1
34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECTt.S,t.Sname,t.Sage,t.Ssex,t.score
FROM(SELECTa.S,a.Sname,a.Sage,a.Ssex,b.score,dense_rank() over ( PARTITION BY b.C ORDER BY b.score DESC ) AS rk FROMsc bLEFT JOIN student a ON b.S = a.SLEFT JOIN course c ON b.C = c.CINNER JOIN teacher t ON c.T = t.T AND t.Tname = '张三' ) t
WHEREt.rk = 1
35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECTb.S,b.C,b.score
FROMsc bINNER JOIN sc d ON b.S = d.S AND b.C <> d.C AND b.score = d.score
GROUP BYb.S,b.C
36. 查询每门功成绩最好的前两名
SELECTb.C,b.S,b.score
FROMsc bLEFT JOIN sc d ON b.C = d.C AND b.score < d.score
GROUP BYb.S,b.C,b.score
HAVINGCOUNT( 1 ) <= 1
ORDER BYb.C
37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）
SELECTb.C,COUNT( 1 ) AS c_count
FROMsc b
GROUP BYb.C
HAVINGCOUNT( 1 ) > 5
38. 检索至少选修两门课程的学生学号
SELECTb.S,COUNT( 1 ) AS c_count
FROMsc b
GROUP BYb.S
HAVINGCOUNT( 1 ) >= 2
39. 查询选修了全部课程的学生信息
SELECTa.S,a.Sname,a.Sage,a.Ssex
FROMsc bINNER JOIN student a ON a.S = b.S
GROUP BYb.S
HAVINGcount( b.C )=(SELECTcount( 1 )
FROMcourse)
40. 查询各学生的年龄，只按年份来算
SELECTa.S,a.Sname,a.Ssex,YEAR (now())- YEAR ( a.Sage ) AS age
FROMstudent a
41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
SELECTa.S,a.Sname,a.Ssex,TIMESTAMPDIFF(YEAR,a.Sage,NOW()) AS 年龄
FROMstudent a
42. 查询本周过生日的学生
SELECTa.S,a.Sname,a.Sage,a.Ssex,SUBSTR( YEARWEEK( a.Sage ), 5, 2 ) AS birth_week,SUBSTR( YEARWEEK( CURDATE()), 5, 2 ) AS now_week
FROMstudent a
WHERESUBSTR( YEARWEEK( a.Sage ), 5, 2 ) = SUBSTR( YEARWEEK( CURDATE()), 5, 2 )
43. 查询下周过生日的学生
SELECTa.S,a.Sname,a.Sage,a.Ssex,SUBSTR( YEARWEEK( a.Sage ), 5, 2 ) AS birth_week,SUBSTR( YEARWEEK( CURDATE()), 5, 2 ) AS now_week
FROMstudent a
WHERESUBSTR( YEARWEEK( a.Sage ), 5, 2 ) = SUBSTR( YEARWEEK( CURDATE()), 5, 2 )+1
44. 查询本月过生日的学生
SELECT*
FROMstudent a
WHEREEXTRACT( MONTH FROM a.Sage )= EXTRACT(MONTH
FROMCURDATE());
45. 查询下月过生日的学生
SELECT*
FROMstudent a
WHEREEXTRACT( MONTH FROM a.Sage )= EXTRACT(MONTH
FROMDATE_ADD( CURDATE(), INTERVAL 1 MONTH ));