关于一个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 DESC

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

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’ IN field)、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 ));