mysql_SQL

约 77 分钟

MySQL SQL 练习

120 题 · 含全部答案

从建表到窗口函数，一套表跑完 120 道题

2026 · SQL 练习册

使用说明表结构、练习方法与评分建议

第一部分建库建表与示例数据（可直接运行）

第二部分 120 道练习题

一、基础查询与条件（Q1–Q15）

二、排序、分页与去重（Q16–Q28）

三、聚合与分组（Q29–Q45）

四、多表连接（Q46–Q62）

五、子查询（Q63–Q78）

六、日期与字符串函数（Q79–Q92）

七、窗口函数（Q93–Q106，MySQL 8.0+）

八、建表、增删改、索引与事务（Q107–Q120）

第三部分参考答案（Q1–Q120）

附录常用函数速查与易错点

项目	说明
题量	120 题，分 8 组：基础 / 排序 / 聚合 / 连接 / 子查询 / 日期字符串 / 窗口函数 / DDL 与事务
环境	MySQL 8.0+（窗口函数、CTE 需要 8.0）
配套数据	5 张表、20 名学生、8 名教师、10 门课程、55 条成绩，可直接建库运行
使用方式	先跑建库脚本，再按组练习，最后对照第 4 部分答案

使用说明

这份练习册只用一个库、五张表，覆盖日常 SQL 工作中 95% 的写法。建议顺序：

1. 先把第 1 部分的建库脚本完整执行一遍（MySQL 8.0 及以上）。

2. 按组做题，每组限时 20–30 分钟，不会的先写思路再查答案。

3. 做完立即对照第 4 部分答案，差异处回看解析（灰色注释）。

4. 三天后重做错题，能一次写对才算掌握。

表结构

表名	说明	行数	关键字段
dept	院系	4	dept_id（主键）、dept_name
teacher	教师	8	teacher_id、dept_id（外键）、salary
student	学生	20	student_id、gender、dept_id、phone（可为 NULL）
course	课程	10	course_id、credits、teacher_id（外键）
score	成绩	55	复合主键 (student_id, course_id)、score（可为 NULL）

提示：数据里故意埋了几个“坑”，用来练习边界情况：王芳重名（学号 5、15）；学号 5 和 12 的电话为 NULL；学号 7 的“操作系统”成绩为 NULL（缺考）；19、20 号没有成绩；10 号课程“大学语文”没有学生选；8 号教师孙丽没有排课。

评分建议

完成情况	水平
1–45 题	能写日常查询，可以看懂别人的 SQL
46–78 题	能处理多表关联与子查询，达到初级开发/测试要求
79–106 题	掌握函数与窗口函数，能做报表类分析
107–120 题	具备建表、索引、事务意识，能独立维护数据库

第一部分建库建表与示例数据

在 MySQL 客户端（命令行、Navicat、DataGrip、DBeaver 均可）中整段执行下面的脚本。

1. 建库与建表

-- 建库（MySQL 8.0+）
CREATE DATABASE IF NOT EXISTS practice
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;

USE practice;

-- 1. 院系
CREATE TABLE dept (
  dept_id   INT PRIMARY KEY AUTO_INCREMENT,
  dept_name VARCHAR(50) NOT NULL UNIQUE,
  location  VARCHAR(50)
) ENGINE=InnoDB;

-- 2. 教师
CREATE TABLE teacher (
  teacher_id   INT PRIMARY KEY AUTO_INCREMENT,
  teacher_name VARCHAR(50) NOT NULL,
  dept_id      INT,
  title        VARCHAR(20),
  salary       DECIMAL(10,2),
  hire_date    DATE,
  CONSTRAINT fk_teacher_dept FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
) ENGINE=InnoDB;

-- 3. 学生
CREATE TABLE student (
  student_id   INT PRIMARY KEY AUTO_INCREMENT,
  student_name VARCHAR(50) NOT NULL,
  gender       CHAR(1),
  birth_date   DATE,
  dept_id      INT,
  enroll_date  DATE,
  phone        VARCHAR(20),
  CONSTRAINT fk_student_dept FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
) ENGINE=InnoDB;

-- 4. 课程
CREATE TABLE course (
  course_id    INT PRIMARY KEY AUTO_INCREMENT,
  course_name  VARCHAR(50) NOT NULL,
  credits      DECIMAL(3,1),
  teacher_id   INT,
  CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
) ENGINE=InnoDB;

-- 5. 成绩（一个学生一门课一条记录）
CREATE TABLE score (
  student_id INT,
  course_id  INT,
  score      DECIMAL(5,2),
  exam_date  DATE,
  PRIMARY KEY (student_id, course_id),
  CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(student_id),
  CONSTRAINT fk_score_course  FOREIGN KEY (course_id)  REFERENCES course(course_id)
) ENGINE=InnoDB;

2. 插入示例数据

INSERT INTO dept (dept_id, dept_name, location) VALUES
  (1, '计算机学院', '北京'),
  (2, '软件学院', '上海'),
  (3, '数学学院', '北京'),
  (4, '外国语学院', '广州');

INSERT INTO teacher (teacher_id, teacher_name, dept_id, title, salary, hire_date) VALUES
  (1, '张伟', 1, '教授', 22000, '2010-03-01'),
  (2, '李娜', 1, '副教授', 18000, '2014-07-01'),
  (3, '王强', 1, '讲师', 12000, '2018-09-01'),
  (4, '刘敏', 2, '教授', 21000, '2009-09-01'),
  (5, '陈静', 2, '副教授', 16000, '2013-04-01'),
  (6, '杨光', 3, '讲师', 9000, '2020-09-01'),
  (7, '赵磊', 3, '副教授', 15000, '2016-02-01'),
  (8, '孙丽', 4, '讲师', 13000, '2018-09-01');

INSERT INTO student (student_id, student_name, gender, birth_date, dept_id, enroll_date, phone) VALUES
  (1, '张三', '男', '2005-03-12', 1, '2023-09-01', '13800001111'),
  (2, '李四', '男', '2005-07-08', 1, '2023-09-01', '13800002222'),
  (3, '王五', '男', '2004-11-20', 2, '2023-09-01', '13800003333'),
  (4, '赵六', '女', '2006-01-15', 2, '2024-09-01', '13800004444'),
  (5, '王芳', '女', '2005-05-05', 1, '2023-09-01', NULL),
  (6, '陈晨', '男', '2006-02-28', 3, '2024-09-01', '13800006666'),
  (7, '刘洋', '男', '2005-09-09', 3, '2023-09-01', '13800007777'),
  (8, '杨帆', '女', '2006-04-18', 4, '2024-09-01', '13800008888'),
  (9, '周婷', '女', '2004-12-01', 4, '2023-09-01', '13800009999'),
  (10, '吴磊', '男', '2005-06-30', 1, '2023-09-01', '13800001010'),
  (11, '郑爽', '女', '2006-08-08', 2, '2024-09-01', '13800001111'),
  (12, '孙悦', '女', '2005-10-10', 2, '2023-09-01', NULL),
  (13, '马超', '男', '2006-03-03', 3, '2024-09-01', '13800001313'),
  (14, '朱琳', '女', '2005-01-01', 4, '2023-09-01', '13800001414'),
  (15, '王芳', '女', '2006-05-20', 4, '2024-09-01', '13800001515'),
  (16, '王小明', '男', '2005-04-04', 1, '2023-09-01', '13800001616'),
  (17, '林心如', '女', '2006-06-06', 2, '2024-09-01', '13800001717'),
  (18, '黄晓明', '男', '2005-12-12', 3, '2023-09-01', '13800001818'),
  (19, '李雷', '男', '2006-07-07', 4, '2024-09-01', '13800001919'),
  (20, '韩梅梅', '女', '2006-09-09', 1, '2024-09-01', '13800002020');

INSERT INTO course (course_id, course_name, credits, teacher_id) VALUES
  (1, '数据库原理', 3.0, 1),
  (2, '数据结构', 4.0, 2),
  (3, '操作系统', 3.5, 4),
  (4, '计算机网络', 3.0, 5),
  (5, '高等数学', 5.0, 7),
  (6, '线性代数', 3.0, 6),
  (7, '英语听说', 2.0, 3),
  (8, '大学物理', 4.0, 1),
  (9, '软件工程', 3.0, 4),
  (10, '大学语文', 2.0, 2);

INSERT INTO score (student_id, course_id, score, exam_date) VALUES
  (1, 1, 92, '2026-06-11'),
  (1, 2, 85, '2026-06-12'),
  (1, 3, 78, '2026-06-13'),
  (1, 4, 88, '2026-06-14'),
  (2, 2, 100, '2026-06-12'),
  (2, 5, 66, '2026-06-15'),
  (2, 6, 58, '2026-06-16'),
  (2, 8, 91, '2026-07-02'),
  (3, 9, 73, '2026-07-03'),
  (4, 4, 55, '2026-06-14'),
  (4, 5, 81, '2026-06-15'),
  (4, 7, 90, '2026-06-17'),
  (5, 1, 95, '2026-06-11'),
  (5, 2, 79, '2026-06-12'),
  (5, 5, 68, '2026-06-15'),
  (5, 8, 84, '2026-07-02'),
  (6, 6, 62, '2026-06-16'),
  (6, 3, 71, '2026-06-13'),
  (6, 2, 49, '2026-06-12'),
  (7, 3, NULL, '2026-06-13'),
  (7, 5, 76, '2026-06-15'),
  (7, 1, 83, '2026-06-11'),
  (8, 7, 93, '2026-06-17'),
  (8, 8, 87, '2026-07-02'),
  (9, 9, 64, '2026-07-03'),
  (9, 6, 70, '2026-06-16'),
  (10, 1, 81, '2026-06-11'),
  (10, 2, 88, '2026-06-12'),
  (10, 3, 59, '2026-06-13'),
  (10, 7, 74, '2026-06-17'),
  (11, 4, 77, '2026-06-14'),
  (11, 5, 85, '2026-06-15'),
  (12, 6, 90, '2026-06-16'),
  (12, 7, 68, '2026-06-17'),
  (12, 8, 73, '2026-07-02'),
  (13, 2, 57, '2026-06-12'),
  (13, 9, 82, '2026-07-03'),
  (13, 4, 86, '2026-06-14'),
  (14, 5, 80, '2026-06-15'),
  (14, 6, 75, '2026-06-16'),
  (15, 3, 69, '2026-06-13'),
  (15, 4, 92, '2026-06-14'),
  (15, 8, 78, '2026-07-02'),
  (15, 9, 60, '2026-07-03'),
  (16, 1, 87, '2026-06-11'),
  (16, 6, 83, '2026-06-16'),
  (16, 7, 79, '2026-06-17'),
  (17, 2, 94, '2026-06-12'),
  (17, 5, 72, '2026-06-15'),
  (17, 9, 88, '2026-07-03'),
  (18, 3, 61, '2026-06-13'),
  (18, 7, 58, '2026-06-17'),
  (18, 8, 80, '2026-07-02');

注意：如果重复执行 INSERT 报主键冲突，先执行 TRUNCATE TABLE score; 再按外键顺序清空 teacher、student、course、dept。

第二部分 120 道练习题

一、基础查询与条件（Q1–Q15）

1. 查询 student 表的全部数据。

2. 查询所有学生的姓名和性别两列。

3. 查询计算机学院（dept_id = 1）的全部学生。

4. 查询 2006-01-01 及以后出生的学生姓名与出生日期。

5. 查询成绩在 60 到 80 分之间（含边界）的成绩记录。

6. 查询成绩为 NULL（缺考）的记录。

7. 查询所有姓“张”的学生。

8. 查询姓名第二个字是“小”的学生。

9. 查询计算机学院和软件学院（dept_id 为 1、2）的学生姓名与院系编号。

10. 查询职称为“教授”或“副教授”的教师。

11. 查询职称为“讲师”且工资高于 10000 的教师。

12. 查询没有登记电话号码的学生。

13. 查询不及格（低于 60）或高于 95 的成绩，按分数降序。

14. 查询姓名中包含“梅”或“雷”的学生。

15. 查询学分不等于 3 的课程。

二、排序、分页与去重（Q16–Q28）

16. 按工资从高到低列出教师姓名与工资。

17. 先按院系编号升序、再按工资降序列出教师。

18. 查询成绩最高的前 5 条记录。

19. 成绩表每页 10 条，取第 3 页（按学号、课程号升序）。

20. 查询学生表出现过的院系编号（去重）。

21. 查询工资第 2 高的教师（不考虑并列）。

22. 把 NULL 成绩按 0 处理，按分数从高到低列出。

23. 按入学日期从早到晚列出学生，同一天的按学号升序。

24. 查询学号最大的 3 名学生，结果仍按学号升序显示。

25. 按院系升序、学号降序取前 10 名学生。

26. 按课程名称排序输出全部课程。

27. 查询学生姓名与出生日期，列名显示为“姓名”“生日”。

28. 查询学生表中出现过的“院系 + 性别”组合（两列去重）。

三、聚合与分组（Q29–Q45）

29. 统计学生总人数。

30. 统计已录入成绩的条数（不含 NULL）。

31. 计算全部成绩的平均分，保留 2 位小数。

32. 查询最高分与最低分。

33. 统计每个院系的学生人数。

34. 统计每个院系的男生、女生人数，一行显示。

35. 统计每门课程的选课人数与平均分，筛选平均分 >= 80 的课程。

36. 统计每位教师承担的课程数，没有课程的教师显示 0。

37. 查询学生平均分最高的院系。

38. 统计各分数段人数（<60 / 60–69 / 70–79 / 80–89 / 90–100）。

39. 查询至少选了 3 门课的学生学号与课程数。

40. 统计各院系教师平均工资，只显示平均工资 > 12000 的院系，按平均工资降序。

41. 查询平均分在 60 到 75 之间的课程号与平均分。

42. 统计每个学生已修课程的总学分。

43. 查询没有任何学生选课的课程。

44. 统计每个院系学生的平均年龄（保留 1 位小数）。

45. 查询每门课程的最高分、最低分，并显示课程名。

四、多表连接（Q46–Q62）

46. 查询每个学生的姓名、课程名、分数（三表内连接）。

47. 查询所有学生及其成绩，没有成绩的学生也要列出。

48. 查询没有选修任何课程的学生。

49. 查询每门课程的名称与授课教师姓名。

50. 查询没有被安排课程的教师姓名。

51. 查询学生姓名、课程名、授课教师姓名。

52. 查询计算机学院学生的成绩（姓名、课程号、分数）。

53. 查询每个院系的学生人数，没有学生的院系显示 0。

54. 查询教师姓名及其所属院系名称。

55. 查询每门课程的选课人数，没人选的课程显示 0。

56. 查询每位学生的平均分，没有成绩的也要列出，按平均分降序。

57. 查询同时选修了 1 号课程和 2 号课程的学生姓名。

58. 查询选修了 1 号课程的学生姓名与分数，按分数降序。

59. 查询分数高于本课程平均分的成绩记录（派生表连接）。

60. 查询各院系人数占全校人数的百分比（保留 2 位小数）。

61. 查询学生人数大于 3 的院系名称与人数。

62. 用 USING 简化连接，查询学生姓名与分数。

五、子查询（Q63–Q78）

63. 查询高于全校平均分的成绩记录。

64. 查询与“张三”（student_id = 1）同院系的其他学生。

65. 用 NOT EXISTS 查询没有成绩记录的学生。

66. 查询平均分最高的课程号与平均分。

67. 查询成绩最高的学生姓名（可能有并列）。

68. 查询每门课程中成绩最高的记录（相关子查询）。

69. 用 EXISTS 查询至少有一门课程不及格的学生姓名。

70. 用 NOT EXISTS 查询所有课程都及格的学生姓名。

71. 查询选课数高于平均选课数的学生。

72. 查询没有学生的院系。

73. 查询工资高于本部门平均工资的教师。

74. 查询每个院系工资最高的教师（并列都列出）。

75. 查询成绩高于 9 号课程所有成绩的记录（> ALL）。

76. 用 IN 子查询查询选修过课程的学生姓名。

77. 查询每位学生的姓名与最高分（标量子查询）。

78. 查询与“李四”同一天入学的学生。

六、日期与字符串函数（Q79–Q92）

79. 查询每位学生的出生年份。

80. 计算每位学生的年龄。

81. 查询 2024 年入学的学生。

82. 查询每位学生已入学多少天。

83. 把入学日期格式化为“2024年09月01日”。

84. 查询本月过生日的学生。

85. 查询 2026-06-01 至 2026-06-30 之间的考试记录。

86. 输出“姓名(性别)”格式的字符串。

87. 取姓名前 2 个字符。

88. 统计姓名的字符数（中文按字计）。

89. 去掉姓名前后空格后再显示。

90. 把手机号中间四位显示为 ****。

91. 用 CONCAT_WS 输出“姓名-院系”标签。

92. 查询姓“王”且姓名字数为 3 的学生。

七、窗口函数（Q93–Q106，MySQL 8.0+）

93. 按课程分组，给每门课的成绩排名（RANK）。

94. 查询每门课程成绩前 3 名（DENSE_RANK）。

95. 计算每门课平均分，以及每条成绩与平均分的差值。

96. 按分数从高到低给所有成绩编号（ROW_NUMBER）。

97. 查询每个院系工资前 2 名的教师。

98. 计算每条成绩占其所在院系总分的百分比。

99. 计算每条成绩在本课程中的百分比排名（PERCENT_RANK）。

100. 计算同一学生按课程编号排序的相邻两次分数差（LAG）。

101. 对比每位教师的工资与全校平均工资。

102. 查询每门课程成绩最高的学生姓名（窗口 + 过滤）。

103. 用 RANK 与 DENSE_RANK 同时排名，观察并列差异。

104. 计算各院系人数占全校人数的比例（窗口函数写法）。

105. 用 NTILE(4) 把成绩分成 4 档。

106. 查询每位学生最高分对应的课程。

八、建表、增删改、索引与事务（Q107–Q120）

107. 用 CREATE TABLE ... AS SELECT 备份学生表结构与数据。

108. 只复制学生表结构，不复制数据。

109. 向 student 表插入一条记录。

110. 一次插入 3 条学生记录。

111. 把学号 5 的电话更新为 13900000005。

112. 把所有低于 60 分的成绩加 5 分。

113. 删除没有任何成绩记录的学生。

114. 给 score 表的 course_id 建普通索引。

115. 查看一条查询的执行计划。

116. 创建视图 v_course_avg：每门课程的平均分。

117. 在 score 上建复合索引，并说明最左前缀原则。

118. 用事务完成“给学号 1 的两门课各加 2 分”，并演示回滚。

119. 用 INSERT ... ON DUPLICATE KEY UPDATE 实现“有则更新、无则插入”。

120. 用 GROUP_CONCAT 列出每门课程的选课学生学号。

第三部分参考答案（Q1–Q120）

答案基于 MySQL 8.0，示例数据为第 1 部分所建。写法不唯一，能正确返回结果即可。

一、基础查询与条件（Q1–Q15）

1. SELECT * FROM student;

2. SELECT student_name, gender FROM student;

3. SELECT * FROM student WHERE dept_id = 1;

4. SELECT student_name, birth_date FROM student WHERE birth_date >= '2006-01-01';

5. SELECT * FROM score WHERE score BETWEEN 60 AND 80; — BETWEEN 包含两端。

6. SELECT * FROM score WHERE score IS NULL; — NULL 不能用 = 比较。

7. SELECT * FROM student WHERE student_name LIKE '张%';

8. SELECT * FROM student WHERE student_name LIKE '_小%'; — _ 匹配任意单个字符。

9. SELECT student_name, dept_id FROM student WHERE dept_id IN (1, 2);

10. SELECT teacher_name, title, salary FROM teacher WHERE title IN ('教授', '副教授');

11. SELECT * FROM teacher WHERE title = '讲师' AND salary > 10000; — AND 优先级高于 OR。

12. SELECT * FROM student WHERE phone IS NULL;

13. SELECT * FROM score WHERE score < 60 OR score > 95 ORDER BY score DESC;

14. SELECT * FROM student WHERE student_name LIKE '%梅%' OR student_name LIKE '%雷%';

15. SELECT * FROM course WHERE credits <> 3; — <> 与 != 等价。

二、排序、分页与去重（Q16–Q28）

16. SELECT teacher_name, salary FROM teacher ORDER BY salary DESC;

17. SELECT dept_id, teacher_name, salary FROM teacher ORDER BY dept_id ASC, salary DESC;

18. SELECT * FROM score ORDER BY score DESC LIMIT 5;

19. SELECT * FROM score ORDER BY student_id, course_id LIMIT 20, 10; — LIMIT offset, count；第 3 页 offset = 20。

20. SELECT DISTINCT dept_id FROM student;

21. SELECT teacher_name, salary FROM teacher ORDER BY salary DESC LIMIT 1, 1; — 只跳 1 条取 1 条。

22. SELECT student_id, course_id, IFNULL(score, 0) AS s FROM score ORDER BY s DESC;

23. SELECT * FROM student ORDER BY enroll_date ASC, student_id ASC;

24. SELECT * FROM (SELECT * FROM student ORDER BY student_id DESC LIMIT 3) t ORDER BY student_id; — 先倒序取 3 条，再包一层正序。

25. SELECT * FROM student ORDER BY dept_id ASC, student_id DESC LIMIT 10;

26. SELECT * FROM course ORDER BY course_name; — 中文按库默认排序规则（utf8mb4_0900_ai_ci）。

27. SELECT student_name AS 姓名, birth_date AS 生日 FROM student;

28. SELECT DISTINCT dept_id, gender FROM student; — DISTINCT 作用于整行组合。

三、聚合与分组（Q29–Q45）

29. SELECT COUNT(*) AS total FROM student;

30. SELECT COUNT(score) AS scored FROM score; — COUNT(列) 忽略 NULL。

31. SELECT ROUND(AVG(score), 2) AS avg_score FROM score;

32. SELECT MAX(score) AS max_score, MIN(score) AS min_score FROM score;

33. SELECT dept_id, COUNT(*) AS cnt FROM student GROUP BY dept_id;

34. SELECT dept_id, SUM(gender = '男') AS male, SUM(gender = '女') AS female FROM student GROUP BY dept_id; — 布尔表达式求和即计数。

35. SELECT course_id, COUNT(*) AS cnt, ROUND(AVG(score), 2) AS avg_score FROM score GROUP BY course_id HAVING AVG(score) >= 80; — 聚合条件用 HAVING。

36. SELECT t.teacher_id, t.teacher_name, COUNT(c.course_id) AS course_cnt FROM teacher t LEFT JOIN course c ON c.teacher_id = t.teacher_id GROUP BY t.teacher_id, t.teacher_name; — LEFT JOIN + COUNT(右表列) 才能得到 0。

37. SELECT s.dept_id, ROUND(AVG(sc.score), 2) AS avg_score FROM student s JOIN score sc ON sc.student_id = s.student_id GROUP BY s.dept_id ORDER BY avg_score DESC LIMIT 1;

38. SELECT CASE WHEN score < 60 THEN '<60' WHEN score < 70 THEN '60-69' WHEN score < 80 THEN '70-79' WHEN score < 90 THEN '80-89' ELSE '90-100' END AS band, COUNT(*) AS cnt FROM score WHERE score IS NOT NULL GROUP BY band ORDER BY MIN(score); — GROUP BY 可用别名；按 MIN(score) 排序才符合分数段顺序。

39. SELECT student_id, COUNT(*) AS course_cnt FROM score GROUP BY student_id HAVING COUNT(*) >= 3;

40. SELECT dept_id, ROUND(AVG(salary), 2) AS avg_salary FROM teacher GROUP BY dept_id HAVING AVG(salary) > 12000 ORDER BY avg_salary DESC;

41. SELECT course_id, ROUND(AVG(score), 2) AS avg_score FROM score GROUP BY course_id HAVING AVG(score) BETWEEN 60 AND 75;

42. SELECT sc.student_id, SUM(c.credits) AS total_credits FROM score sc JOIN course c ON c.course_id = sc.course_id GROUP BY sc.student_id;

43. SELECT c.* FROM course c LEFT JOIN score s ON s.course_id = c.course_id WHERE s.course_id IS NULL;

44. SELECT dept_id, ROUND(AVG(TIMESTAMPDIFF(YEAR, birth_date, CURDATE())), 1) AS avg_age FROM student GROUP BY dept_id;

45. SELECT c.course_name, MAX(s.score) AS max_score, MIN(s.score) AS min_score FROM course c JOIN score s ON s.course_id = c.course_id GROUP BY c.course_id, c.course_name;

四、多表连接（Q46–Q62）

46. SELECT st.student_name, c.course_name, sc.score FROM student st JOIN score sc ON sc.student_id = st.student_id JOIN course c ON c.course_id = sc.course_id;

47. SELECT st.student_name, sc.course_id, sc.score FROM student st LEFT JOIN score sc ON sc.student_id = st.student_id ORDER BY st.student_id;

48. SELECT st.* FROM student st LEFT JOIN score sc ON sc.student_id = st.student_id WHERE sc.student_id IS NULL;

49. SELECT c.course_name, t.teacher_name FROM course c JOIN teacher t ON t.teacher_id = c.teacher_id;

50. SELECT t.teacher_name FROM teacher t LEFT JOIN course c ON c.teacher_id = t.teacher_id WHERE c.course_id IS NULL;

51. SELECT st.student_name, c.course_name, t.teacher_name FROM score sc JOIN student st ON st.student_id = sc.student_id JOIN course c ON c.course_id = sc.course_id JOIN teacher t ON t.teacher_id = c.teacher_id;

52. SELECT st.student_name, sc.course_id, sc.score FROM student st JOIN score sc ON sc.student_id = st.student_id WHERE st.dept_id = 1;

53. SELECT d.dept_id, d.dept_name, COUNT(s.student_id) AS cnt FROM dept d LEFT JOIN student s ON s.dept_id = d.dept_id GROUP BY d.dept_id, d.dept_name;

54. SELECT t.teacher_name, d.dept_name FROM teacher t LEFT JOIN dept d ON d.dept_id = t.dept_id;

55. SELECT c.course_id, c.course_name, COUNT(sc.student_id) AS cnt FROM course c LEFT JOIN score sc ON sc.course_id = c.course_id GROUP BY c.course_id, c.course_name;

56. SELECT st.student_id, st.student_name, AVG(sc.score) AS avg_score FROM student st LEFT JOIN score sc ON sc.student_id = st.student_id GROUP BY st.student_id, st.student_name ORDER BY avg_score DESC;

57. SELECT st.student_name FROM score a JOIN score b ON b.student_id = a.student_id AND b.course_id = 2 JOIN student st ON st.student_id = a.student_id WHERE a.course_id = 1; — 自连接同一张表，别名区分两次行。

58. SELECT st.student_name, sc.score FROM score sc JOIN student st ON st.student_id = sc.student_id WHERE sc.course_id = 1 ORDER BY sc.score DESC;

59. SELECT sc.student_id, sc.course_id, sc.score, ROUND(t.avg_score, 2) AS course_avg FROM score sc JOIN (SELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id) t ON t.course_id = sc.course_id WHERE sc.score > t.avg_score;

60. SELECT dept_id, COUNT(*) AS n, ROUND(COUNT(*) / (SELECT COUNT(*) FROM student) * 100, 2) AS pct FROM student GROUP BY dept_id;

61. SELECT d.dept_name, COUNT(s.student_id) AS cnt FROM dept d JOIN student s ON s.dept_id = d.dept_id GROUP BY d.dept_id, d.dept_name HAVING cnt > 3;

62. SELECT st.student_name, sc.score FROM student st JOIN score sc USING (student_id); — 同名列才能用 USING。

五、子查询（Q63–Q78）

63. SELECT * FROM score WHERE score > (SELECT AVG(score) FROM score);

64. SELECT * FROM student WHERE dept_id = (SELECT dept_id FROM student WHERE student_id = 1) AND student_id <> 1;

65. SELECT st.* FROM student st WHERE NOT EXISTS (SELECT 1 FROM score sc WHERE sc.student_id = st.student_id);

66. SELECT course_id, ROUND(AVG(score), 2) AS avg_score FROM score GROUP BY course_id ORDER BY avg_score DESC LIMIT 1;

67. SELECT DISTINCT st.student_name FROM student st JOIN score sc ON sc.student_id = st.student_id WHERE sc.score = (SELECT MAX(score) FROM score);

68. SELECT sc.course_id, sc.student_id, sc.score FROM score sc WHERE sc.score = (SELECT MAX(s2.score) FROM score s2 WHERE s2.course_id = sc.course_id); — 子查询引用了外层的 course_id。

69. SELECT DISTINCT st.student_name FROM student st WHERE EXISTS (SELECT 1 FROM score sc WHERE sc.student_id = st.student_id AND sc.score < 60);

70. SELECT DISTINCT st.student_name FROM student st WHERE NOT EXISTS (SELECT 1 FROM score sc WHERE sc.student_id = st.student_id AND sc.score < 60); — 注意：没有任何成绩的学生也会被算进来。

71. SELECT student_id, COUNT(*) AS cnt FROM score GROUP BY student_id HAVING COUNT(*) > (SELECT AVG(cnt) FROM (SELECT COUNT(*) AS cnt FROM score GROUP BY student_id) t);

72. SELECT d.* FROM dept d LEFT JOIN student s ON s.dept_id = d.dept_id WHERE s.student_id IS NULL;

73. SELECT t.teacher_name, t.salary FROM teacher t WHERE t.salary > (SELECT AVG(salary) FROM teacher t2 WHERE t2.dept_id = t.dept_id);

74. SELECT t.* FROM teacher t WHERE t.salary = (SELECT MAX(salary) FROM teacher t2 WHERE t2.dept_id = t.dept_id);

75. SELECT * FROM score WHERE score > ALL (SELECT score FROM score WHERE course_id = 9); — ALL 表示大于子查询返回的每一个值。

76. SELECT st.student_name FROM student st WHERE st.student_id IN (SELECT student_id FROM score);

77. SELECT st.student_name, (SELECT MAX(score) FROM score sc WHERE sc.student_id = st.student_id) AS max_score FROM student st;

78. SELECT * FROM student WHERE enroll_date = (SELECT enroll_date FROM student WHERE student_id = 2) AND student_id <> 2;

六、日期与字符串函数（Q79–Q92）

79. SELECT student_name, YEAR(birth_date) AS birth_year FROM student;

80. SELECT student_name, TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM student;

81. SELECT * FROM student WHERE YEAR(enroll_date) = 2024; — 不要写 WHERE enroll_date = 2024，会对日期做隐式转换。

82. SELECT student_name, DATEDIFF(CURDATE(), enroll_date) AS days FROM student;

83. SELECT student_name, DATE_FORMAT(enroll_date, '%Y年%m月%d日') AS enroll_fmt FROM student;

84. SELECT * FROM student WHERE MONTH(birth_date) = MONTH(CURDATE());

85. SELECT * FROM score WHERE exam_date BETWEEN '2026-06-01' AND '2026-06-30';

86. SELECT CONCAT(student_name, '(', gender, ')') AS info FROM student;

87. SELECT student_name, LEFT(student_name, 2) AS prefix2 FROM student;

88. SELECT student_name, CHAR_LENGTH(student_name) AS len FROM student; — LENGTH 返回字节数，utf8mb4 下一个汉字 3 字节。

89. SELECT TRIM(student_name) AS name_trimmed FROM student;

90. SELECT student_name, CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS phone_masked FROM student; — phone 为 NULL 时结果仍为 NULL。

91. SELECT CONCAT_WS('-', s.student_name, d.dept_name) AS label FROM student s JOIN dept d ON d.dept_id = s.dept_id;

92. SELECT * FROM student WHERE student_name LIKE '王%' AND CHAR_LENGTH(student_name) = 3;

七、窗口函数（Q93–Q106，MySQL 8.0+）

93. SELECT course_id, student_id, score, RANK() OVER (PARTITION BY course_id ORDER BY score DESC) AS rk FROM score;

94. SELECT course_id, student_id, score, rk FROM (SELECT course_id, student_id, score, DENSE_RANK() OVER (PARTITION BY course_id ORDER BY score DESC) AS rk FROM score) t WHERE rk <= 3; — 窗口函数不能直接写在 WHERE 里，需包一层。

95. SELECT student_id, course_id, score, ROUND(AVG(score) OVER (PARTITION BY course_id), 2) AS course_avg, ROUND(score - AVG(score) OVER (PARTITION BY course_id), 2) AS diff FROM score;

96. SELECT student_id, course_id, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn FROM score;

97. SELECT dept_id, teacher_name, salary, rk FROM (SELECT dept_id, teacher_name, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk FROM teacher) t WHERE rk <= 2;

98. SELECT sc.student_id, sc.score, ROUND(sc.score / SUM(sc.score) OVER (PARTITION BY st.dept_id) * 100, 2) AS pct FROM score sc JOIN student st ON st.student_id = sc.student_id;

99. SELECT student_id, course_id, score, ROUND(PERCENT_RANK() OVER (PARTITION BY course_id ORDER BY score), 4) AS pr FROM score;

100. SELECT student_id, course_id, score, score - LAG(score) OVER (PARTITION BY student_id ORDER BY course_id) AS diff FROM score;

101. SELECT teacher_name, salary, ROUND(AVG(salary) OVER (), 2) AS school_avg, ROUND(salary - AVG(salary) OVER (), 2) AS diff FROM teacher;

102. SELECT t.course_id, st.student_name, t.score FROM (SELECT course_id, student_id, score, ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) AS rn FROM score) t JOIN student st ON st.student_id = t.student_id WHERE t.rn = 1;

103. SELECT course_id, student_id, score, RANK() OVER (PARTITION BY course_id ORDER BY score DESC) AS rk, DENSE_RANK() OVER (PARTITION BY course_id ORDER BY score DESC) AS drk FROM score; — RANK 跳号，DENSE_RANK 不跳号。

104. SELECT dept_id, COUNT(*) AS n, ROUND(COUNT(*) / SUM(COUNT(*)) OVER () * 100, 2) AS pct FROM student GROUP BY dept_id;

105. SELECT student_id, course_id, score, NTILE(4) OVER (ORDER BY score DESC) AS bucket FROM score;

106. SELECT student_id, course_id, score FROM (SELECT student_id, course_id, score, ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY score DESC) AS rn FROM score) t WHERE rn = 1;

八、建表、增删改、索引与事务（Q107–Q120）

107. CREATE TABLE student_bak AS SELECT * FROM student; — 不会复制索引、主键、自增等约束。

108. CREATE TABLE student_bak2 LIKE student; — LIKE 会复制完整表定义。

109. INSERT INTO student (student_id, student_name, gender, birth_date, dept_id, enroll_date, phone) VALUES (21, '测试学生', '男', '2006-10-01', 1, '2024-09-01', '13800002121');

110. INSERT INTO student (student_id, student_name, gender, birth_date, dept_id, enroll_date, phone) VALUES (22, '测试甲', '男', '2006-01-02', 1, '2024-09-01', NULL), (23, '测试乙', '女', '2006-03-04', 2, '2024-09-01', NULL), (24, '测试丙', '男', '2006-05-06', 3, '2024-09-01', NULL);

111. UPDATE student SET phone = '13900000005' WHERE student_id = 5; — 漏写 WHERE 会更新全表。

112. UPDATE score SET score = score + 5 WHERE score < 60; — 重复执行会反复加分，建议先 SELECT 确认。

113. DELETE FROM student WHERE student_id NOT IN (SELECT student_id FROM score); — 先查后删；外键存在时需先删关联成绩。

114. CREATE INDEX idx_score_course ON score (course_id);

115. EXPLAIN SELECT * FROM score WHERE course_id = 1; — 关注 type、key、rows、Extra。

116. CREATE VIEW v_course_avg AS SELECT c.course_id, c.course_name, ROUND(AVG(s.score), 2) AS avg_score FROM course c LEFT JOIN score s ON s.course_id = c.course_id GROUP BY c.course_id, c.course_name;

117. CREATE INDEX idx_score_sid_cid ON score (student_id, course_id); — 按 (student_id, course_id) 建索引后，仅 student_id 可用；单独 course_id 用不上。

START TRANSACTION;
UPDATE score SET score = score + 2 WHERE student_id = 1 AND course_id = 1;
UPDATE score SET score = score + 2 WHERE student_id = 1 AND course_id = 2;
ROLLBACK;  -- 确认无误后改成 COMMIT;　── InnoDB 支持事务，MyISAM 不支持。

119. INSERT INTO score (student_id, course_id, score, exam_date) VALUES (1, 5, 88, '2026-06-20') ON DUPLICATE KEY UPDATE score = VALUES(score); — 8.0.20+ 更推荐别名写法：... VALUES (...) AS new ON DUPLICATE KEY UPDATE score = new.score。

120. SELECT course_id, GROUP_CONCAT(student_id ORDER BY student_id SEPARATOR ',') AS students FROM score GROUP BY course_id;

附录常用函数速查

类别	函数	说明
数值	ROUND(x, n) / CEIL / FLOOR / ABS	四舍五入、取整、绝对值
字符串	CONCAT / CONCAT_WS / LEFT / RIGHT / SUBSTRING	拼接、截取
字符串	CHAR_LENGTH / LENGTH / TRIM / REPLACE	字符数、字节数、去空格、替换
日期	NOW() / CURDATE() / YEAR / MONTH / DAY	当前时间与取部件
日期	DATE_FORMAT / STR_TO_DATE / DATEDIFF	格式化、解析、相差天数
日期	TIMESTAMPDIFF(unit, a, b)	按年/月/日计算差值
空值	IFNULL / COALESCE / NULLIF	空值替换、取第一个非空
条件	CASE WHEN ... THEN ... ELSE ... END	行转列、分段
聚合	COUNT / SUM / AVG / MAX / MIN / GROUP_CONCAT	分组统计
窗口	ROW_NUMBER / RANK / DENSE_RANK / NTILE	排名与分档
窗口	LAG / LEAD / SUM OVER / AVG OVER	同比环比、累计、占比

常见易错点

NULL 不参与比较：score = NULL 永远为 UNKNOWN，必须用 IS NULL；聚合函数（COUNT 列、AVG、SUM）会自动忽略 NULL。
WHERE 与 HAVING：WHERE 在分组前过滤行，不能写聚合函数；HAVING 在分组后过滤组。
GROUP BY 完整性：SELECT 中的非聚合列必须出现在 GROUP BY 里（ONLY_FULL_GROUP_BY 默认开启）。
ON 与 WHERE 的位置：LEFT JOIN 的右表过滤条件写在 ON 里才保留左表全部行，写在 WHERE 里会退化成内连接。
索引失效：列上做函数/运算（YEAR(enroll_date)=2024、salary+0）、前导通配 LIKE '%x'、隐式类型转换都会导致无法使用索引。
分页越深越慢：LIMIT 100000, 10 需要扫描并丢弃前 10 万行，建议改用「延迟关联」或游标（WHERE id > ?）。
窗口函数不能直接过滤：WHERE ROW_NUMBER() OVER ... = 1 非法，必须先包一层子查询或用 CTE。
事务与引擎：只有 InnoDB 支持事务；自动提交模式下一条语句就是一个事务，批量操作要显式 START TRANSACTION。

mysql_SQL

MySQL SQL 练习

使用说明

表结构

评分建议

第一部分 建库建表与示例数据

1. 建库与建表

2. 插入示例数据

第二部分 120 道练习题

一、基础查询与条件（Q1–Q15）

二、排序、分页与去重（Q16–Q28）

三、聚合与分组（Q29–Q45）

四、多表连接（Q46–Q62）

五、子查询（Q63–Q78）

六、日期与字符串函数（Q79–Q92）

七、窗口函数（Q93–Q106，MySQL 8.0+）

八、建表、增删改、索引与事务（Q107–Q120）

第三部分 参考答案（Q1–Q120）

一、基础查询与条件（Q1–Q15）

二、排序、分页与去重（Q16–Q28）

三、聚合与分组（Q29–Q45）

四、多表连接（Q46–Q62）

五、子查询（Q63–Q78）

六、日期与字符串函数（Q79–Q92）

七、窗口函数（Q93–Q106，MySQL 8.0+）

八、建表、增删改、索引与事务（Q107–Q120）

附录 常用函数速查

常见易错点

第一部分建库建表与示例数据

第三部分参考答案（Q1–Q120）

附录常用函数速查