新手磨成老手，都需要一个过程。SQL虽简单，但我们也不能指望仅凭昨天一篇《初学者入门SQL必背50条常用语句(由易到难)》就真的能简简单单地轻轻松松地入门SQL。实际情况是，基础的SQL看了都会，简单的SQL查询写了也对，但一碰到复杂的子查询、窗口函数就犯迷糊了，所以今天我们和大家一起来进阶。下面这50条SQL进阶语句，我们特意从基础到高级进行排序，不管是多表连接、分组统计，还是数据批量操作，都是干活时我们常碰到的场景。跟着这些场景示例进行训练，先搞懂啥时候用什么语句、执行逻辑怎么走，再动手做做对应的练习，跟着练习大胆地试，慢慢地我们就会进阶，就会蜕变，化蛹成蝶！这样慢慢地我们也就成SQL老手啦！

一、多条件、子查询等复杂查询

1、多条件查询与优先级控制

需求：我们要查询“1班或2班，年龄≥16岁，而且数学分数≥80分”的学生姓名（数学对应course_id=1）。

用SQL来实现：

-- 用括号()控制AND/OR优先级，先算括号内条件SELECT s.nameFROM student s  -- 表别名简化代码JOIN score sc ON s.id = sc.student_id  -- 关联学生表和分数表WHERE (s.class = '1班' OR s.class = '2班')  -- 先筛选班级  AND s.age >= 16  -- 再筛选年龄  AND sc.course_id = 1  -- 指定数学  AND sc.score >= 80;  -- 筛选数学分数

解析：

• AND优先级高于OR，若不加括号，会先执行“2班 AND 年龄≥16”；
• 表别名（s代表student，sc代表score）可减少重复书写，提高可读性。

练习：请查询“3班的性别为女，或4班的年龄≤17岁，而且英语（course_id=2）分数>75分”的学生ID。

参考SQL：

SELECT DISTINCT s.idFROM student sJOIN score sc ON s.id = sc.student_idWHERE (        (s.class = '3班' AND s.gender = '女')         OR         (s.class = '4班' AND s.age <= 17)      )  AND sc.course_id = 2  AND sc.score > 75;

2、单行子查询（=、>、< 连接）

需求：我们要查询“分数高于数学平均分”的学生姓名及数学分数。

用SQL来实现：

-- 子查询(括号内)先算数学平均分，外层用>对比SELECT s.name, sc.scoreFROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1  -- 数学  AND sc.score > (SELECT AVG(score)  -- 单行子查询：返回1个值                  FROM score                  WHERE course_id = 1);

解析：

• 单行子查询返回单个值，需用单行运算符（=、>、<、>=、<=、<>）与外层关联；
• 子查询可以替代“先算平均分再手动写条件”这样的两步操作，更高效。

练习：请查询“年龄大于学生平均年龄”的2班学生姓名。

参考SQL：

SELECT nameFROM studentWHERE class = '2班'  AND age > (SELECT AVG(age) FROM student);

3、多行子查询（IN、NOT IN）

需求：我们要查询“选修了数学（course_id=1）或英语（course_id=2）”的学生姓名。

用SQL来实现：

-- 子查询返回多个student_id，外层用IN匹配SELECT nameFROM studentWHERE id IN (SELECT student_id             FROM score             WHERE course_id IN (1, 2));  -- 子查询筛选两门课的学生ID

解析：

• 多行子查询返回多个值，需要用多行运算符（IN、NOT IN、ANY、ALL）；
• IN表示“存在于子查询结果中”，NOT IN表示“不存在于子查询结果中”。

练习：请查询“未选修语文（course_id=3）”的学生姓名。

参考SQL：

SELECT nameFROM studentWHERE id NOT IN (SELECT student_id                 FROM score                 WHERE course_id = 3);

4、关联子查询（内外层表关联）

需求：我们要查询“每个学生的最高分及对应课程ID”。

用SQL来实现：

-- 子查询通过student_id与外层表关联，为每个学生查找最高分SELECT sc.student_id, sc.course_id, sc.scoreFROM score scWHERE sc.score = (SELECT MAX(score)                  FROM score                  WHERE student_id = sc.student_id);  -- 子查询依赖外层student_id

解析：

• 关联子查询的子查询条件依赖外层表字段，为外层表的每一行，都会执行一次子查询；
• 适用于“按分组找极值”等等场景（如：每个学生、每个班级的最值）。

练习：请查询“每个班级的最小年龄及对应学生姓名”。

参考SQL：

SELECT s.class, s.name, s.ageFROM student sWHERE s.age = (SELECT MIN(age)               FROM student               WHERE class = s.class);

5、EXISTS子查询（判断存在性）

需求：我们要查询“至少有一门分数≥90分”的学生姓名。

用SQL来实现：

-- EXISTS判断子查询是否有结果（有的话返回TRUE，没有就FALSE）SELECT nameFROM student sWHERE EXISTS (SELECT 1  -- 子查询返回1就行了，不需要实际数据，效率更高              FROM score sc              WHERE sc.student_id = s.id                AND sc.score >= 90);

解析：

• EXISTS关注“子查询是否存在结果”，而不是具体值，因此子查询中SELECT 1比SELECT *更高效；
• 当子查询的结果数据量大时，EXISTS通常比IN效率更高（因为它避免了全表匹配）。

练习：请查询“没有不及格（<60分）科目”的学生ID。

参考SQL：

SELECT idFROM student sWHERE NOT EXISTS (SELECT 1                  FROM score sc                  WHERE sc.student_id = s.id                    AND sc.score < 60);

二、聚合与分组，过滤与统计

6、GROUP BY多字段分组

需求：我们要统计“每个班级、每个性别的学生人数及平均年龄”。

用SQL来实现：

-- 按班级+性别组合分组，统计每组的人数和平均年龄SELECT class, gender,       COUNT(id) AS 人数,  -- 统计每组学生数       ROUND(AVG(age), 1) AS 平均年龄  -- 保留1位小数FROM studentGROUP BY class, gender;  -- 先按class分组，再按gender细分

解析：

• 多字段分组时，分组顺序会影响结果层级，我们先按第一个字段分组，再按第二个字段在每组内细分；
• GROUP BY后的字段必须出现在SELECT中（聚合函数除外）。

练习：请统计“每个课程、每个分数等级（≥90优秀，80-89良好，<80及格）的学生人数”。

参考SQL：

SELECT course_id,       CASE            WHEN score >= 90 THEN '优秀'           WHEN score >= 80 THEN '良好'           ELSE '及格'       END AS 等级,       COUNT(student_id) AS 人数FROM scoreGROUP BY course_id,         CASE              WHEN score >= 90 THEN '优秀'             WHEN score >= 80 THEN '良好'             ELSE '及格'         ENDORDER BY course_id, 等级;  -- 建议加排序，结果更清晰

7、HAVING过滤分组结果

需求：我们要统计“平均分数≥85分，且至少有3名学生选修”的课程ID及平均分数。

用SQL来实现：

-- HAVING过滤分组后的结果(需配合GROUP BY)SELECT course_id,       AVG(score) AS 平均分数,       COUNT(student_id) AS 选修人数FROM scoreGROUP BY course_id  -- 先按课程分组HAVING AVG(score) >= 85  -- 过滤平均分数≥85的组   AND COUNT(student_id) >= 3;  -- 过滤选修人数≥3的组

解析：

• WHERE过滤行数据（分组前），HAVING过滤分组结果（分组后）；
• HAVING中可以使用聚合函数，但WHERE中不能使用聚合函数。

练习：请统计“1班和2班中，平均年龄≤16岁，且人数≥5”的班级及平均年龄。

参考SQL：

SELECT class,       AVG(age) AS 平均年龄,       COUNT(id) AS 人数FROM studentWHERE class IN ('1班', '2班')  -- 先筛选班级GROUP BY classHAVING AVG(age) <= 16  -- 过滤平均年龄≤16的组   AND COUNT(id) >= 5;

8、ROLLUP生成小计与总计

需求：我们要统计“每个班级的学生人数，及所有班级的总人数”。

用SQL来实现：

-- ROLLUP在GROUP BY后添加小计/总计行(MySQL 5.7+支持)SELECT IFNULL(class, '总计') AS 班级,  -- 把NULL替换为“总计”       COUNT(id) AS 人数FROM studentGROUP BY class WITH ROLLUP;  -- 对class分组并生成总计

解析：

• WITH ROLLUP会在分组结果后添加一行，其中分组字段为NULL，聚合函数为“总计”；
• 用IFNULL可将NULL替换为更容易读的文字（如：“总计”、“小计”）。

练习：请统计“每个课程、每个等级的人数，及每个课程的小计、所有课程的总计”。

参考SQL：

SELECT     IFNULL(course_id, '总计') AS 课程,    CASE         WHEN course_id IS NOT NULL AND score_level IS NULL THEN '小计'        WHEN course_id IS NULL AND score_level IS NULL THEN '总计'        ELSE score_level    END AS 等级,    COUNT(student_id) AS 人数FROM (    SELECT         course_id,        student_id,        CASE             WHEN score >= 90 THEN '优秀'            WHEN score >= 80 THEN '良好'            ELSE '及格'        END AS score_level    FROM score) AS tGROUP BY course_id, score_level WITH ROLLUP;

9、聚合函数嵌套（MAX(AVG())）

需求：我们要查询“所有班级中，平均年龄最大的班级及其平均年龄”。

用SQL来实现：

-- 先通过派生表获取所有班级的平均年龄，再筛选出平均年龄等于最大值的班级SELECT class AS 班级,        avg_age AS 最大平均年龄FROM (SELECT class,              AVG(age) AS avg_age      FROM student      GROUP BY class) AS temp-- 子查询获取最大平均年龄，与派生表匹配WHERE avg_age = (SELECT MAX(avg_age)                 FROM (SELECT AVG(age) AS avg_age                       FROM student                       GROUP BY class) AS temp2);

解析：

• 聚合函数不能直接嵌套（如：MAX(AVG(age))不允许），我们需要通过子查询分层计算；
• 先内层分组计算“班级-平均年龄”，再外层计算最大值，最后匹配对应班级，避免作用域错误。

练习：请查询“所有课程中，最高分最低的课程ID及其最高分”。

参考SQL：

SELECT course_id AS 课程ID,       max_score AS 最低最高分FROM (SELECT course_id,              MAX(score) AS max_score      FROM score      GROUP BY course_id) AS tempWHERE max_score = (SELECT MIN(max_score)                   FROM (SELECT MAX(score) AS max_score                         FROM score                         GROUP BY course_id) AS temp2);

10、窗口函数基础（ROW_NUMBER）

需求：我们需要按“数学分数降序”为学生排名（分数相同也按不同名次排列）。

用SQL来实现：

-- ROW_NUMBER()为每行分配唯一序号，即使值相同SELECT s.name, sc.score,       ROW_NUMBER() OVER (ORDER BY sc.score DESC) AS 数学排名FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1;

解析：

• 窗口函数格式：函数名() OVER (窗口规则)，“窗口”指数据的分析范围；
• ROW_NUMBER()生成连续序号，适用于“唯一排名”场景（如：选秀排名）。

练习：请按“英语分数升序”为2班学生排名。

参考SQL：

SELECT s.name, sc.score,       ROW_NUMBER() OVER (ORDER BY sc.score ASC) AS 英语排名FROM student sJOIN score sc ON s.id = sc.student_idWHERE s.class = '2班' AND sc.course_id = 2;

11、窗口函数进阶（RANK、DENSE_RANK）

需求：我们需要按“语文分数降序”为学生排名（分数相同名次相同，RANK跳过后续名次，DENSE_RANK不跳过）。

用SQL来实现：

-- RANK()：分数相同名次相同，后续名次跳过；DENSE_RANK()：不跳过SELECT s.name, sc.score,       RANK() OVER (ORDER BY sc.score DESC) AS RANK排名,       DENSE_RANK() OVER (ORDER BY sc.score DESC) AS DENSE_RANK排名FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 3;

解析：

• 示例：分数90、90、85，RANK排名为1、1、3；DENSE_RANK排名为1、1、2；
• 适用于“考试排名”（RANK）、“等级划分”（DENSE_RANK）等场景。

练习：请按“平均分数降序”为每个班级的学生排名（用DENSE_RANK）。

参考SQL：

SELECT s.class, s.name,       AVG(sc.score) OVER (PARTITION BY s.id) AS 平均分数,  -- 每个学生的平均分数       DENSE_RANK() OVER (PARTITION BY s.class ORDER BY AVG(sc.score) DESC) AS 班级排名FROM student sJOIN score sc ON s.id = sc.student_idGROUP BY s.class, s.name, s.id;

12、窗口函数分区（PARTITION BY）

需求：我们需要按“班级分区”，再按“数学分数降序”为每个班级的学生排名。

用SQL来实现：

-- PARTITION BY：将数据按指定字段分区，窗口函数在每个分区内独立计算SELECT s.class, s.name, sc.score,       ROW_NUMBER() OVER (PARTITION BY s.class  -- 按班级分区                          ORDER BY sc.score DESC) AS 班级内数学排名FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1;

解析：

• PARTITION BY类似“分组”，但不合并行，只是划定分析范围；
• 班级之间的排名分别从1开始，互不影响。

练习：请按“性别分区”，再按“年龄升序”为学生排名。

参考SQL：

SELECT gender, name, age,       ROW_NUMBER() OVER (PARTITION BY gender ORDER BY age ASC) AS 性别内年龄排名FROM student;

三、多表连接及特殊连接

13、三表连接（学生-分数-课程）

需求：我们要查询“学生姓名、课程名称、对应分数”，按课程名称排序。

用SQL来实现：

-- 三表连接：student→score→course，通过外键关联SELECT s.name AS 学生姓名,       c.course_name AS 课程名称,       sc.score AS 分数FROM student sJOIN score sc ON s.id = sc.student_id  -- 学生表关联分数表JOIN course c ON sc.course_id = c.course_id  -- 分数表关联课程表ORDER BY c.course_name;

解析：

• 多表连接需要保证“关联字段一致”（如：student.id = score.student_id）；
• 连接顺序不影响结果，但我们建议按“主表→从表”的逻辑顺序来书写（如：学生→分数→课程）。

练习：请查询“2班学生的姓名、英语（course_name='英语'）分数”，分数为空显示0。

参考SQL：

SELECT s.name, IFNULL(sc.score, 0) AS 英语分数FROM student sLEFT JOIN score sc ON s.id = sc.student_idLEFT JOIN course c ON sc.course_id = c.course_id AND c.course_name = '英语'WHERE s.class = '2班';

14、左连接保留左表所有数据

需求：我们要查询“所有学生的姓名及数学分数”，未选修数学的学生分数显示“未选修”。

用SQL来实现：

-- LEFT JOIN：保留左表（student）所有行，右表（score）无匹配则为NULLSELECT s.name,       IFNULL(CAST(sc.score AS CHAR), '未选修') AS 数学分数  -- 转换分数为字符串，避免类型冲突FROM student sLEFT JOIN score sc ON s.id = sc.student_id                  AND sc.course_id = 1;  -- 条件写在JOIN后，保留左表所有学生

解析：

• 若将sc.course_id = 1放在WHERE中，会过滤掉未选修数学的学生（NULL不满足条件）；
• CAST(sc.score AS CHAR)将数值型分数转换为字符串，与“未选修”拼接不报错。

练习：请查询“所有课程的名称及选修人数”，无人选修的课程显示0。

参考SQL：

SELECT c.course_name,       IFNULL(COUNT(sc.student_id), 0) AS 选修人数FROM course cLEFT JOIN score sc ON c.course_id = sc.course_idGROUP BY c.course_name, c.course_id;

15、右连接与左连接转换

需求：我们要查询“所有课程的名称及至少一名选修学生的姓名”（用右连接实现，再转为左连接）。

用SQL来实现：

-- 方法1：右连接（保留course所有行，关联score和student）SELECT c.course_name, s.name AS 学生姓名FROM student sJOIN score sc ON s.id = sc.student_idRIGHT JOIN course c ON sc.course_id = c.course_id;-- 方法2：左连接转换（交换表顺序，左连接等价于右连接）SELECT c.course_name, s.name AS 学生姓名FROM course cLEFT JOIN score sc ON c.course_id = sc.course_idLEFT JOIN student s ON sc.student_id = s.id;

解析：

• 右连接（A RIGHT JOIN B）等价于左连接（B LEFT JOIN A），只是表的顺序不同；
• 实战中左连接更常用，因为逻辑更直观（“保留左表，关联右表”）。

练习：请用右连接查询“所有分数≥90的记录对应的学生姓名及课程名称”，再转为左连接。

参考SQL：

-- 右连接SELECT s.name, c.course_name, sc.scoreFROM student sRIGHT JOIN score sc ON s.id = sc.student_id AND sc.score >=90RIGHT JOIN course c ON sc.course_id = c.course_id;-- 左连接转换SELECT s.name, c.course_name, sc.scoreFROM score scLEFT JOIN student s ON sc.student_id = s.idLEFT JOIN course c ON sc.course_id = c.course_idWHERE sc.score >=90;

16、全连接（UNION ALL实现）

需求：我们要查询“所有学生和所有课程的组合，显示已选修的分数，未选修的显示NULL”（MySQL不直接支持FULL JOIN，用UNION ALL实现）。

用SQL来实现：

-- 全连接：先通过CROSS JOIN生成“所有学生×所有课程”的全组合，再左连分数表补分数SELECT s.id AS 学生ID,        s.name AS 学生姓名,        c.course_id AS 课程ID,        c.course_name AS 课程名称,        sc.score AS 分数FROM student s-- 第一步：交叉连接生成学生与课程的全组合CROSS JOIN course c-- 第二步：左连分数表，未选修的分数显示NULLLEFT JOIN score sc ON s.id = sc.student_id AND c.course_id = sc.course_id;

解析：

• MySQL不支持FULL JOIN，但“所有学生 × 所有课程”的全组合无需复杂UNION ALL，直接用CROSS JOIN（笛卡尔积）生成全量组合，再左连分数表即可，逻辑更简洁且无冗余；
• 若需兼容 “课程无学生” 的极端场景（如：新创建的课程无任何学生），上述CROSS JOIN已覆盖，无需额外处理。

17、自连接（同表关联）

需求：我们要查询“和‘小明’同班的学生姓名”（小明的name='小明'）。

用SQL来实现：

-- 自连接：将同一张表视为两张表（s1和s2），通过班级关联SELECT s2.name AS 同班同学FROM student s1JOIN student s2 ON s1.class = s2.class  -- 同班级关联WHERE s1.name = '小明'  -- 筛选小明的班级  AND s2.name != '小明';  -- 排除小明自己

解析：

• 自连接需为同一张表起不同别名（如：s1、s2），否则无法区分；
• 适用于“查询同组数据”（如：同班学生、同部门员工）、“层级数据”（如：员工-领导）等场景。

练习：请查询“年龄比‘小红’大且同班的学生姓名”（小红name='小红'）。

参考SQL：

SELECT s2.nameFROM student s1JOIN student s2 ON s1.class = s2.classWHERE s1.name = '小红'  AND s2.age > s1.age;

18、交叉连接（笛卡尔积）

需求：我们要生成“所有学生和所有课程的组合”（每个学生对应每门课程）。

用SQL来实现：

-- 交叉连接：左表每行与右表每行匹配，结果数=左表行数×右表行数SELECT s.name AS 学生姓名, c.course_name AS 课程名称FROM student sCROSS JOIN course c;

解析：

• 交叉连接会产生笛卡尔积，结果集可能非常大，我们需谨慎使用；
• 常用于“生成全量组合”（如：批量生成学生-课程选修关系），后续可结合LEFT JOIN筛选未选修记录。

练习：请生成“1班学生和所有数学、英语课程的组合”。

参考SQL：

SELECT s.name, c.course_nameFROM student sCROSS JOIN course cWHERE s.class = '1班'  AND c.course_name IN ('数学', '英语');

19、条件连接（ON中多条件）

需求：我们要查询“学生姓名、课程名称及分数”，仅显示“1班学生且分数≥70分”的记录。

用SQL来实现：

-- 在JOIN的ON子句中添加多条件，提前过滤数据，提升效率SELECT s.name, c.course_name, sc.scoreFROM student sJOIN score sc ON s.id = sc.student_id             AND s.class = '1班'  -- 过滤1班学生             AND sc.score >=70  -- 过滤分数≥70的记录JOIN course c ON sc.course_id = c.course_id;

解析：

• 条件写在ON中比写在WHERE中更高效，因为前者能在连接时就过滤数据，减少后续处理的行数；
• 适用于“仅需关联满足特定条件的数据”的场景。

练习：请查询“2008年出生的学生（birthday LIKE '2008%'）的英语分数（course_id=2）”，仅显示分数>80的记录。

参考SQL：

SELECT s.name, sc.scoreFROM student sJOIN score sc ON s.id = sc.student_id             AND s.birthday LIKE '2008%'             AND sc.course_id = 2             AND sc.score >80;

20、非等值连接（BETWEEN、>、<）

需求：我们要查询“学生分数对应的等级”（等级表：90-100优秀，80-89良好，60-79及格，<60不及格）。

用SQL来实现：

-- 先创建等级表（临时表）CREATE TEMPORARY TABLE grade_level (    level_name VARCHAR(10),    min_score INT,    max_score INT);INSERT INTO grade_level VALUES ('优秀',90,100),('良好',80,89),('及格',60,79),('不及格',0,59);-- 非等值连接：通过BETWEEN匹配分数范围SELECT s.name, sc.score, gl.level_nameFROM student sJOIN score sc ON s.id = sc.student_idJOIN grade_level gl ON sc.score BETWEEN gl.min_score AND gl.max_score;

解析：

• 非等值连接不依赖“字段相等”，而是通过BETWEEN、>、<等条件来匹配；
• 适用于“范围匹配”（如：分数对应等级、年龄对应年龄段）场景。

练习：请查询“学生年龄对应的年龄段”（年龄段：≤16少年，17-18青年，>18成年），用非等值连接实现。

参考SQL：

-- 创建年龄段临时表CREATE TEMPORARY TABLE age_group (    group_name VARCHAR(10),    min_age INT,    max_age INT);INSERT INTO age_group VALUES ('少年',0,16),('青年',17,18),('成年',19,100);-- 非等值连接SELECT name, age, ag.group_nameFROM student sJOIN age_group ag ON s.age BETWEEN ag.min_age AND ag.max_age;

四、嵌套子查询与相关子查询

21、子查询作为列（标量子查询）

需求：我们要查询“学生姓名、班级，及数学分数”（数学分数作为单独一列，未选修则为NULL）。

用SQL来实现：

-- 标量子查询：子查询返回单个值，作为SELECT中的一列SELECT name, class,       (SELECT score        FROM score sc        WHERE sc.student_id = s.id          AND sc.course_id = 1) AS 数学分数FROM student s;

解析：

• 标量子查询：对于外层查询的每一行数据，子查询都仅返回一个结果（该结果可以是具体值，也可以是NULL）；
• 与左连接相比，标量子查询更简洁，适用于“仅需关联单个字段”的场景。

练习：请查询“课程名称，及该课程的最高分数”（最高分数作为单独一列）。

参考SQL：

SELECT course_name,       (SELECT MAX(score)        FROM score sc        WHERE sc.course_id = c.course_id) AS 最高分数FROM course c;

22、子查询作为表（派生表）

需求：我们要查询“平均分数≥80分的学生姓名及平均分数”。

用SQL来实现：

-- 派生表：子查询作为临时表，需要起别名，外层对其查询SELECT temp.name, temp.avg_scoreFROM (SELECT s.name, AVG(sc.score) AS avg_score  -- 子查询：每个学生的平均分数      FROM student s      JOIN score sc ON s.id = sc.student_id      GROUP BY s.name, s.id) AS temp  -- 派生表必须别名WHERE temp.avg_score >= 80;  -- 外层过滤平均分数≥80的学生

解析：

• 派生表是“从子查询生成的临时表”，仅在当前查询中有效；
• 适用于“需先分组统计，再筛选结果”的场景（替代HAVING，更灵活）。

练习：请查询“选修课程数≥2门的学生ID及选修课程数”。

参考SQL：

SELECT temp.student_id, temp.course_countFROM (SELECT student_id, COUNT(course_id) AS course_count      FROM score      GROUP BY student_id) AS tempWHERE temp.course_count >= 2;

23、嵌套子查询（多层子查询）

需求：我们要查询“数学分数高于班级数学平均分的学生姓名”。

用SQL来实现：

-- 三层嵌套：先算每个班级的数学平均分，再算每个学生的班级平均分，最后再匹配SELECT s.nameFROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1  -- 数学  AND sc.score > (SELECT class_avg  -- 外层子查询：学生所在班级的数学平均分                 FROM (SELECT class, AVG(score) AS class_avg  -- 内层子查询：每个班级的数学平均分                       FROM student s2                       JOIN score sc2 ON s2.id = sc2.student_id                       WHERE sc2.course_id = 1                       GROUP BY class) AS temp                 WHERE temp.class = s.class);  -- 关联学生班级

解析：

• 嵌套子查询通过“内层→外层”逐步传递数据，每层解决一个问题；
• 多层嵌套需要注意“关联字段的作用范围”（如：内层的class需与外层的s.class匹配）。

练习：请查询“年龄大于班级平均年龄的2班学生姓名”。

参考SQL：

SELECT nameFROM student sWHERE class = '2班'  AND age > (SELECT class_avg             FROM (SELECT class, AVG(age) AS class_avg                   FROM student                   GROUP BY class) AS temp             WHERE temp.class = s.class);

24、ANY子查询（任意匹配）

需求：我们要查询“数学分数高于1班任意一名学生数学分数”的学生姓名。

用SQL来实现：

-- ANY：满足子查询结果中的任意一个即可（即高于1班最低分）SELECT s.name, sc.scoreFROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1  AND sc.score > ANY (SELECT score  -- 子查询：1班学生的数学分数                      FROM student s2                      JOIN score sc2 ON s2.id = sc2.student_id                      WHERE s2.class = '1班'                        AND sc2.course_id = 1);

解析：

• sc.score > ANY (子查询) 等价于 sc.score > MIN(子查询结果)；
• ANY需要配合比较运算符（>、<、>=等）使用，适用于“满足任意一个条件”的场景。

练习：请查询“年龄小于2班任意一名学生年龄”的学生姓名。

参考SQL：

SELECT nameFROM studentWHERE age < ANY (SELECT age                 FROM student                 WHERE class = '2班');

25、ALL子查询（全部匹配）

需求：我们要查询“数学分数高于1班所有学生数学分数”的学生姓名。

用SQL来实现：

-- ALL：满足子查询结果中的所有条件（即高于1班最高分）SELECT s.name, sc.scoreFROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1  AND sc.score > ALL (SELECT score  -- 子查询：1班学生的数学分数                      FROM student s2                      JOIN score sc2 ON s2.id = sc2.student_id                      WHERE s2.class = '1班'                        AND sc2.course_id = 1);

解析：

• sc.score > ALL (子查询) 等价于 sc.score > MAX(子查询结果)；
• sc.score < ALL (子查询) 等价于 sc.score < MIN(子查询结果)。

练习：请查询“年龄大于3班所有学生年龄”的学生姓名。

参考SQL：

SELECT nameFROM studentWHERE age > ALL (SELECT age                 FROM student                 WHERE class = '3班');

26、相关子查询与EXISTS优化

需求：我们要查询“至少有一门课程分数与‘小明’相同”的学生姓名（排除小明）。

用SQL来实现：

-- 相关子查询：子查询依赖外层的s2.name，效率较低SELECT DISTINCT s1.nameFROM student s1JOIN score sc1 ON s1.id = sc1.student_idWHERE s1.name != '小明'  AND EXISTS (SELECT 1              FROM student s2              JOIN score sc2 ON s2.id = sc2.student_id              WHERE s2.name = '小明'                AND sc1.score = sc2.score);  -- 子查询依赖sc1.score-- 优化：先查小明的分数，用IN替代相关子查询，效率会更高SELECT DISTINCT s.nameFROM student sJOIN score sc ON s.id = sc.student_idWHERE s.name != '小明'  AND sc.score IN (SELECT score                   FROM student s2                   JOIN score sc2 ON s2.id = sc2.student_id                   WHERE s2.name = '小明');

解析：

• 相关子查询因“外层每一行都执行一次子查询”，效率较低；
• 优化思路：我们提前用子查询查出“依赖外层的条件”，再用IN匹配（减少子查询执行次数）。

练习：请查询“至少有一门课程分数与‘小红’相同”的学生姓名（排除小红），请用两种方式实现并比较效率。

参考SQL：

-- 相关子查询SELECT DISTINCT s1.nameFROM student s1JOIN score sc1 ON s1.id = sc1.student_idWHERE s1.name != '小红'  AND EXISTS (SELECT 1              FROM student s2              JOIN score sc2 ON s2.id = sc2.student_id              WHERE s2.name = '小红'                AND sc1.score = sc2.score);-- 优化版（IN子查询）SELECT DISTINCT s.nameFROM student sJOIN score sc ON s.id = sc.student_idWHERE s.name != '小红'  AND sc.score IN (SELECT score                   FROM student s2                   JOIN score sc2 ON s2.id = sc2.student_id                   WHERE s2.name = '小红');

27、子查询与连接的转换

需求：我们要查询“选修了数学（course_id=1）的学生姓名”（要分别用子查询和连接实现）。

用SQL来实现：

-- 方法1：子查询（IN）SELECT nameFROM studentWHERE id IN (SELECT student_id             FROM score             WHERE course_id = 1);-- 方法2：连接（JOIN）SELECT DISTINCT s.name  -- DISTINCT去重（避免学生多选同一门课的重复记录）FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1;

解析：

• 大部分子查询可转换为连接，连接通常效率更高（数据库优化器对连接的支持更好）；
• 当子查询结果有重复时，连接需要用DISTINCT去重。

练习：请查询“未选修英语（course_id=2）的学生姓名”，请用子查询（NOT IN）和连接（LEFT JOIN + IS NULL）两种方式实现。

参考SQL：

-- 子查询（NOT IN）SELECT nameFROM studentWHERE id NOT IN (SELECT student_id                 FROM score                 WHERE course_id = 2);-- 连接（LEFT JOIN + IS NULL）SELECT s.nameFROM student sLEFT JOIN score sc ON s.id = sc.student_id AND sc.course_id = 2WHERE sc.student_id IS NULL;

28、子查询中的LIMIT与OFFSET

需求：我们要查询“数学分数最高的前3名学生姓名及分数”。

用SQL来实现：

SELECT s.name AS 学生姓名,        sc.score AS 数学分数FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1  -- 先取前3个不重复的最高分，再筛选≥最低阈值的学生  AND sc.score >= (      SELECT DISTINCT score      FROM score      WHERE course_id = 1      ORDER BY score DESC      LIMIT 2, 1  -- 取第3个不重复分数（阈值）  )ORDER BY sc.score DESC;

解析：

• LIMIT n 取前n条记录，LIMIT m, n 从第m+1条开始取n条（m为偏移量）；
• 子查询中使用LIMIT可以提前过滤数据，减少外层连接的行数。
• 若需 “严格前 3 名（含并列）”，我们需先通过DISTINCT获取不重复的分数排序，再取第 3 个分数作为阈值，筛选所有≥该阈值的学生，避免漏选并列数据。

练习：请查询“英语分数最低的2名学生姓名及分数”。

参考SQL：

SELECT s.name AS 学生姓名,        sc.score AS 英语分数FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 2  AND sc.score <= (      SELECT COALESCE(          (SELECT DISTINCT score           FROM score           WHERE course_id = 2           ORDER BY score ASC           LIMIT 1, 1),          (SELECT MAX(score) FROM score WHERE course_id = 2)          -- 如果没有第2低分，就取最高分（即：所有分都满足 <= 最高分 → 返回全部）      )  )ORDER BY sc.score ASC, s.name;

五、聚合窗口与移动窗口

29、聚合窗口函数（SUM OVER）

需求：我们要查询“学生姓名、数学分数，及所有学生的数学总分、平均分”。

用SQL来实现：

-- SUM() OVER ()：将所有行视为一个窗口，计算总共和SELECT s.name, sc.score,       SUM(sc.score) OVER () AS 数学总分,  -- 所有学生的数学总分       AVG(sc.score) OVER () AS 数学平均分  -- 所有学生的数学平均分FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1;

解析：

• 聚合窗口函数（SUM、AVG、MAX、MIN等）与普通聚合函数的区别：不合并行，而是为每行返回聚合结果；
• OVER () 表示“窗口为整个结果集”。

练习：请查询“2班学生的姓名、年龄，及2班的总人数、平均年龄”。

参考SQL：

SELECT name, age,       COUNT(id) OVER () AS 2班总人数,       AVG(age) OVER () AS 2班平均年龄FROM studentWHERE class = '2班';

30、分区聚合窗口（SUM OVER PARTITION BY）

需求：我们要查询“学生姓名、班级、数学分数，及每个班级的数学总分、平均分”。

用SQL来实现：

-- SUM() OVER (PARTITION BY 班级)：按班级分区，计算每个分区的总分SELECT s.name, s.class, sc.score,       SUM(sc.score) OVER (PARTITION BY s.class) AS 班级数学总分,       AVG(sc.score) OVER (PARTITION BY s.class) AS 班级数学平均分FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1;

解析：

• 分区聚合窗口在每个分区内独立计算聚合结果，适用于“查看每行数据在其分组中的聚合信息”（如：每个学生的分数占班级总分的比例）。

练习：请查询“学生姓名、性别、年龄，及每个性别的总人数、最大年龄”。

参考SQL：

SELECT name, gender, age,       COUNT(id) OVER (PARTITION BY gender) AS 性别总人数,       MAX(age) OVER (PARTITION BY gender) AS 性别最大年龄FROM student;

31、移动窗口（ROWS BETWEEN）

需求：我们要查询“学生按数学分数降序排列后的姓名、分数，及当前学生和前1名、后1名学生的分数平均值”（移动平均）。

用SQL来实现：

-- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：窗口包含前1行、当前行、后1行SELECT s.name, sc.score,       AVG(sc.score) OVER (ORDER BY sc.score DESC                          ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 移动平均FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1;

解析：

• 移动窗口通过ROWS BETWEEN定义窗口范围：
• n PRECEDING：前n行；
• n FOLLOWING：后n行；
• CURRENT ROW：当前行；
• UNBOUNDED PRECEDING：第一行；
• UNBOUNDED FOLLOWING：最后一行。

练习：请查询“学生按年龄升序排列后的姓名、年龄，及当前学生和前2名学生的年龄总和”。

参考SQL：

SELECT name, age,       SUM(age) OVER (ORDER BY age ASC                      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS 移动总和FROM student;

32、窗口函数排序（NTILE）

需求：我们要求将“数学分数的学生按分数降序分为3组”，查询学生姓名、分数及分组。

用SQL来实现：

-- NTILE(n)：将结果集按顺序分为n组，返回每组的编号SELECT s.name, sc.score,       NTILE(3) OVER (ORDER BY sc.score DESC) AS 分数分组FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1;

解析：

• NTILE适用于“数据分桶”场景（如：将学生分为“高、中、低”三组）；
• 若总条数不能被n整除，前几组会比后几组多1条记录。

练习：请将“2班学生按年龄升序分为2组”，查询学生姓名、年龄及分组。

参考SQL：

SELECT name, age,       NTILE(2) OVER (ORDER BY age ASC) AS 年龄分组FROM studentWHERE class = '2班';

33、窗口函数滞后/超前（LAG、LEAD）

需求：我们要查询“学生按数学分数降序排列后的姓名、分数，及前一名学生的分数和姓名”。

用SQL来实现：

-- LAG(字段, 偏移量, 默认值)：获取当前行之前第n行的字段值SELECT s.name, sc.score,       LAG(sc.score, 1, 0) OVER (ORDER BY sc.score DESC) AS 前一名分数,       LAG(s.name, 1, '无') OVER (ORDER BY sc.score DESC) AS 前一名姓名FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1;

解析：

• LAG：获取“前n行”数据；
• LEAD：获取“后n行”数据；
• 第二个参数为偏移量（默认1），第三个参数为无匹配时的默认值（默认NULL）。

练习：请查询“学生按年龄升序排列后的姓名、年龄，及后两名学生的年龄”。

参考SQL：

SELECT name, age,       LEAD(age, 2, 0) OVER (ORDER BY age ASC) AS 后两名年龄FROM student;

34、窗口函数首/尾值（FIRST_VALUE、LAST_VALUE）

需求：我们要查询“每个班级的学生姓名、数学分数，及该班级的最高分数和最低分数”。

用SQL来实现：

SELECT     s.class AS 班级,     s.name AS 学生姓名,     sc.score AS 数学分数,    -- 降序，第一个是最高分    FIRST_VALUE(sc.score) OVER (        PARTITION BY s.class         ORDER BY sc.score DESC    ) AS 班级最高分,    -- 升序，第一个是最低分（这样直观）    FIRST_VALUE(sc.score) OVER (        PARTITION BY s.class         ORDER BY sc.score ASC    ) AS 班级最低分FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1;

练习：请查询“每个性别的学生姓名、年龄，及该性别的最小年龄和最大年龄”。

参考SQL：

SELECT gender AS 性别,        name AS 学生姓名,        age AS 年龄,       -- 按年龄升序，窗口内第一个值即性别最小年龄（默认范围覆盖全分区）       FIRST_VALUE(age) OVER (PARTITION BY gender                              ORDER BY age ASC) AS 性别最小年龄,       -- 手动指定窗口范围为“当前行到分区最后一行”，确保取到整个性别的最大年龄（升序排序后最后一个值）       LAST_VALUE(age) OVER (PARTITION BY gender                             ORDER BY age ASC                             ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS 性别最大年龄FROM student;

35、窗口函数与聚合函数结合

需求：我们要查询“学生姓名、数学分数，及分数占班级总分的比例”（保留2位小数）。

用SQL来实现：

-- 窗口函数计算班级总分，再与当前分数做除法SELECT s.name, s.class, sc.score,       CONCAT(ROUND(sc.score / SUM(sc.score) OVER (PARTITION BY s.class) * 100, 2), '%') AS 占班级总分比例FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1;

解析：

• 窗口函数可以作为“计算字段”参与算术运算，实现更复杂的统计逻辑；
• 用CONCAT将比例转换为“百分比字符串”，更容易读。

练习：请查询“学生姓名、年龄，及年龄占性别总年龄的比例”（保留2位小数）。

参考SQL：

SELECT name, gender, age,       CONCAT(ROUND(age / SUM(age) OVER (PARTITION BY gender) * 100, 2), '%') AS 占性别总年龄比例FROM student;

六、批量操作、事务及约束

36、批量插入数据（INSERT INTO ... VALUES 多组）

需求：我们要批量插入3条学生记录到student表。

用SQL来实现：

-- 批量插入：VALUES后接多组数据，要用逗号分隔INSERT INTO student (name, class, age, gender, birthday)VALUES ('张三', '1班', 16, '男', '2008-01-15'),('李四', '2班', 17, '女', '2007-05-20'),('王五', '1班', 16, '男', '2008-03-10');

解析：

• 批量插入比单条插入效率高（减少数据库连接交互次数）；
• 需保证每组数据的字段顺序和类型与INSERT INTO后的字段匹配。

练习：请批量插入2条课程记录到course表（course_id、course_name）。

参考SQL：

INSERT INTO course (course_id, course_name)VALUES (4, '物理'),(5, '化学');

37、插入查询结果（INSERT INTO ... SELECT）

需求：我们要求将“1班学生的记录复制到student_backup备份表”（假设student_backup结构与student相同）。

用SQL来实现：

-- 插入查询结果：将SELECT的结果插入目标表INSERT INTO student_backup (name, class, age, gender, birthday)SELECT name, class, age, gender, birthdayFROM studentWHERE class = '1班';

解析：

• 目标表（student_backup）需要提前创建，而且字段类型与SELECT结果匹配；
• 适用于“数据备份”、“批量复制符合条件的数据”等等场景。

练习：请将“数学分数≥90分的记录复制到score_excellent表”（score_excellent结构与score相同）。

参考SQL：

INSERT INTO score_excellent (student_id, course_id, score, create_time)SELECT student_id, course_id, score, create_timeFROM scoreWHERE course_id = 1 AND score >=90;

38、批量更新（UPDATE ... JOIN）

需求：我们要求将“1班学生的数学分数统一增加5分”。

用SQL来实现：

-- 批量更新：通过JOIN关联两张表，一次性更新符合条件的记录UPDATE student sJOIN score sc ON s.id = sc.student_idSET sc.score = sc.score + 5  -- 分数增加5分WHERE s.class = '1班'  AND sc.course_id = 1;

解析：

• UPDATE ... JOIN允许通过连接多个表来定位需要更新的记录，比单表更新更灵活；
• 更新前，我们建议用SELECT验证连接条件是否正确（避免误更新）。

练习：请将“2008年出生的学生（birthday LIKE '2008%'）的英语分数（course_id=2）统一减少2分”。

参考SQL：

UPDATE student sJOIN score sc ON s.id = sc.student_idSET sc.score = sc.score - 2WHERE s.birthday LIKE '2008%'  AND sc.course_id = 2;

39、事务处理（BEGIN、COMMIT、ROLLBACK）

需求：我们要求同时更新“小明的数学分数为95分”和“小红的数学分数为90分”，确保要么都成功，要么都失败。

用SQL来实现：

-- 开启事务BEGIN;-- 操作1：更新小明的数学分数UPDATE score scJOIN student s ON sc.student_id = s.idSET sc.score = 95WHERE s.name = '小明' AND sc.course_id = 1;-- 操作2：更新小红的数学分数UPDATE score scJOIN student s ON sc.student_id = s.idSET sc.score = 90WHERE s.name = '小红' AND sc.course_id = 1;-- 验证操作是否正确（可选）SELECT s.name, sc.score FROM student s JOIN score sc ON s.id = sc.student_id WHERE s.name IN ('小明', '小红') AND sc.course_id = 1;-- 确认无误，提交事务（永久生效）COMMIT;-- 若出错，回滚事务（撤销所有操作）-- ROLLBACK;

解析：

• 事务的ACID特性：原子性（要么全成，要么全败）、一致性、隔离性、持久性；
• BEGIN开启事务，COMMIT提交（生效），ROLLBACK回滚（撤销）。

练习：请同时插入“赵六的学生记录”和“赵六的数学分数记录”，用事务确保一致性。

参考SQL：

BEGIN;-- 插入学生记录INSERT INTO student (name, class, age, gender, birthday)VALUES ('赵六', '3班', 17, '男', '2007-08-05');-- 插入分数记录（假设赵六的id为105）INSERT INTO score (student_id, course_id, score)VALUES (105, 1, 85);-- 验证SELECT * FROM student WHERE name = '赵六';SELECT * FROM score WHERE student_id = 105;-- 提交或回滚COMMIT;-- ROLLBACK;

40、新增字段（ALTER TABLE ADD）

需求：我们要求在student表中新增“address（地址）”字段，类型为VARCHAR(100)，允许为空。

用SQL来实现：

-- ALTER TABLE ADD：新增字段ALTER TABLE studentADD COLUMN address VARCHAR(100) NULL COMMENT '学生地址';  -- COMMENT添加字段注释

解析：

• NULL表示允许字段为空（默认），NOT NULL表示不允许为空（需要确保表中现有数据有值，或设置默认值）；
• COMMENT用于添加字段说明，提高代码可读性。

练习：请在score表中新增“remark（备注）”字段，类型为VARCHAR(50)，默认值为“无”。

参考SQL：

ALTER TABLE scoreADD COLUMN remark VARCHAR(50) DEFAULT '无' COMMENT '分数备注';

41、修改字段（ALTER TABLE MODIFY）

需求：我们要求将student表中的“address”字段类型改为VARCHAR(200)，且不允许为空。

用SQL来实现：

-- 先确保现有address字段无NULL值（否则修改会失败）UPDATE student SET address = '未知' WHERE address IS NULL;-- ALTER TABLE MODIFY：修改字段类型和约束ALTER TABLE studentMODIFY COLUMN address VARCHAR(200) NOT NULL COMMENT '学生地址（详细）';

解析：

• 修改字段为NOT NULL前，需要确保表中现有数据该字段无NULL值；
• MODIFY可以修改字段类型、长度、约束，但不能修改字段名（修改字段名用CHANGE）。

练习：请将score表中的“remark”字段类型改为VARCHAR(100)，默认值改为“无备注”。

参考SQL：

ALTER TABLE scoreMODIFY COLUMN remark VARCHAR(100) DEFAULT '无备注' COMMENT '分数备注（详细）';

42、添加主键约束（ALTER TABLE ADD PRIMARY KEY）

需求：我们要求为新建的“student_temp”表添加主键约束（主键为id字段）。

用SQL来实现：

-- 校验数据完整性（可在执行前运行）-- 检查空值SELECT * FROM student_temp WHERE id IS NULL;-- 检查重复值SELECT id, COUNT(*) FROM student_temp GROUP BY id HAVING COUNT(*) > 1;-- MySQL 5.7+通用版-- 步骤1：创建无主键的表CREATE TABLE student_temp (    id INT,    name VARCHAR(20),    class VARCHAR(10));-- 步骤2：插入测试数据（含 NULL 和重复）INSERT INTO student_temp VALUES     (1, '张三', '1班'),     (2, '李四', '2班'),     (NULL, '王五', '1班'),     (1, '赵六', '2班');-- 步骤3：校验并清理数据-- 3.1 替换 NULL 值（用当前最大 id + 1）UPDATE student_temp SET id = (SELECT new_id FROM (SELECT IFNULL(MAX(id), 0) + 1 AS new_id FROM student_temp) AS t)WHERE id IS NULL;-- 3.2 添加临时唯一标识列ALTER TABLE student_temp ADD COLUMN row_id INT AUTO_INCREMENT PRIMARY KEY FIRST;-- 3.3 删除重复 id 中非最小 row_id 的记录（保留最早插入/最小 row_id 的那条）DELETE s1 FROM student_temp s1INNER JOIN student_temp s2 WHERE s1.id = s2.id   AND s1.row_id > s2.row_id;-- 3.4 删除临时列ALTER TABLE student_temp DROP COLUMN row_id;-- 步骤4：添加主键约束ALTER TABLE student_tempADD PRIMARY KEY (id);

练习：请为“course_temp”表（字段：course_id INT, course_name VARCHAR(20)）添加主键约束（主键为course_id）。

参考SQL：

-- 步骤1：创建表CREATE TABLE course_temp (    course_id INT,    course_name VARCHAR(20));-- 步骤2：插入测试数据（含NULL和重复值）INSERT INTO course_temp VALUES     (1, '数学'),     (2, '英语'),     (NULL, '语文'),     (2, '物理');-- 步骤3：清理数据-- 3.1 替换NULL值（用当前最大course_id + 1）UPDATE course_temp SET course_id = (    SELECT new_id FROM (        SELECT IFNULL(MAX(course_id), 0) + 1 AS new_id         FROM course_temp    ) AS temp)WHERE course_id IS NULL;-- 3.2 添加临时唯一标识列（用于区分重复行中保留哪一条）ALTER TABLE course_temp ADD COLUMN row_id INT AUTO_INCREMENT PRIMARY KEY FIRST;-- 3.3 删除重复course_id中row_id较大的记录（保留最早插入/最小row_id的那条）DELETE c1 FROM course_temp c1INNER JOIN course_temp c2 WHERE c1.course_id = c2.course_id   AND c1.row_id > c2.row_id;-- 3.4 删除临时列ALTER TABLE course_temp DROP COLUMN row_id;-- 步骤4：添加主键约束（先确保字段非空，再加主键）ALTER TABLE course_temp MODIFY course_id INT NOT NULL;  -- 主键字段必须非空ALTER TABLE course_temp ADD PRIMARY KEY (course_id);

七、CTE、递归及正则

43、公共表表达式（CTE：WITH 子句）

需求：我们要查询“平均分数≥80分的学生姓名及平均分数”（用CTE替代派生表）。

用SQL来实现：

-- WITH 定义CTE（临时结果集），后续查询可直接引用WITH student_avg AS (    SELECT s.name, AVG(sc.score) AS avg_score    FROM student s    JOIN score sc ON s.id = sc.student_id    GROUP BY s.name, s.id)-- 引用CTE查询SELECT name, avg_scoreFROM student_avgWHERE avg_score >= 80;

解析：

• CTE（Common Table Expression）比派生表更容易读，尤其适用于“多次引用同一子查询”的场景；
• CTE仅在当前查询中有效，查询结束后就自动销毁。

练习：请用CTE查询“选修课程数≥2门的学生ID及选修课程数”。

参考SQL：

WITH student_course AS (    SELECT student_id, COUNT(course_id) AS course_count    FROM score    GROUP BY student_id)SELECT student_id, course_countFROM student_courseWHERE course_count >= 2;

44、递归CTE（层级数据查询）

需求：我们要查询“员工层级关系”（表结构：emp_id INT, emp_name VARCHAR(20), manager_id INT，manager_id为上级员工的emp_id），显示每个员工的姓名及层级（1级为最高级）。

用SQL来实现：

-- 创建员工表并插入数据CREATE TABLE employee (    emp_id INT PRIMARY KEY,    emp_name VARCHAR(20),    manager_id INT);INSERT INTO employee VALUES(1, '老板', NULL),(2, '经理A', 1),(3, '经理B', 1),(4, '员工A1', 2),(5, '员工A2', 2);-- 递归CTE：分为“锚点成员”和“递归成员”WITH RECURSIVE emp_hierarchy AS (    -- 锚点成员：查询最高级员工（manager_id为NULL），层级为1    SELECT emp_id, emp_name, manager_id, 1 AS level    FROM employee    WHERE manager_id IS NULL    UNION ALL    -- 递归成员：关联CTE，查询下级员工，层级+1    SELECT e.emp_id, e.emp_name, e.manager_id, eh.level + 1 AS level    FROM employee e    JOIN emp_hierarchy eh ON e.manager_id = eh.emp_id)-- 查询结果SELECT emp_name, level AS 层级FROM emp_hierarchyORDER BY level;

解析：

• 递归CTE需要用WITH RECURSIVE定义，包含两部分：
  1、锚点成员：初始查询（最高层级数据）；
  2、递归成员：通过UNION ALL关联CTE，查询下级数据，直到无匹配；
• 适用于“层级数据”（如：员工-领导、部门-子部门）查询。

练习：请查询“员工层级关系”，显示每个员工的姓名、上级姓名及层级。

参考SQL：

WITH RECURSIVE emp_hierarchy AS (    SELECT emp_id, emp_name, manager_id, CAST(NULL AS VARCHAR(20)) AS manager_name, 1 AS level    FROM employee    WHERE manager_id IS NULL    UNION ALL    SELECT e.emp_id, e.emp_name, e.manager_id, eh.emp_name AS manager_name, eh.level + 1 AS level    FROM employee e    JOIN emp_hierarchy eh ON e.manager_id = eh.emp_id)SELECT emp_name, manager_name, levelFROM emp_hierarchyORDER BY level;

45、正则表达式查询（REGEXP）

需求：我们要查询“姓名以‘张’开头，且长度为2或3个字符”的学生姓名。

用SQL来实现：

-- REGEXP：使用正则表达式匹配字符串SELECT nameFROM studentWHERE name REGEXP '^张.{1,2}$';  -- 正则含义：^张（以张开头），.{1,2}（1-2个任意字符），$（结尾）

解析：

• 常用正则元字符：
• ^：开头；$：结尾；
• .：任意单个字符；
• {n,m}：重复n到m次；
• [a-z]：小写字母；[0-9]：数字；
• MySQL中REGEXP不区分大小写，REGEXP BINARY区分大小写。

练习：请查询“手机号以138开头，且后8位为数字”的学生姓名（phone字段为VARCHAR类型）。

参考SQL：

SELECT nameFROM studentWHERE phone REGEXP '^138[0-9]{8}$';

46、条件聚合（CASE WHEN + 聚合函数）

需求：我们要统计“每个班级的男生人数、女生人数及总人数”。

用SQL来实现：

-- 条件聚合：CASE WHEN作为聚合函数的参数，按条件统计SELECT class,       COUNT(CASE WHEN gender = '男' THEN 1 END) AS 男生人数,  -- 男生则计数1，否则NULL（COUNT忽略NULL）       COUNT(CASE WHEN gender = '女' THEN 1 END) AS 女生人数,       COUNT(id) AS 总人数FROM studentGROUP BY class;

解析：

• 条件聚合通过CASE WHEN在聚合时筛选数据，无需多次查询；
• COUNT(CASE WHEN ... THEN 1 END) 等价于 SUM(CASE WHEN ... THEN 1 ELSE 0 END)。

练习：请统计“每个课程的优秀（≥90）人数、良好（80-89）人数及总人数”。

参考SQL：

SELECT course_id,       COUNT(CASE WHEN score >=90 THEN 1 END) AS 优秀人数,       COUNT(CASE WHEN score BETWEEN 80 AND 89 THEN 1 END) AS 良好人数,       COUNT(student_id) AS 总人数FROM scoreGROUP BY course_id;

47、行转列（条件聚合实现）

需求：我们要求将“学生的数学、英语分数”从行转为列（格式：姓名、数学、英语）。

用SQL来实现：

-- 行转列：用条件聚合将不同行的分数转换为不同列SELECT s.name,       MAX(CASE WHEN c.course_name = '数学' THEN sc.score END) AS 数学,  -- 数学分数       MAX(CASE WHEN c.course_name = '英语' THEN sc.score END) AS 英语   -- 英语分数FROM student sJOIN score sc ON s.id = sc.student_idJOIN course c ON sc.course_id = c.course_idGROUP BY s.name, s.id;  -- 按学生分组

解析：

• 行转列：按“目标行字段”（如：学生姓名）分组，用CASE WHEN匹配“目标列字段”（如：课程名称），提取对应值；
• 用MAX/MIN/SUM等聚合函数去除NULL（同一学生同一课程只有一个分数）。

练习：请将“学生的语文、物理分数”从行转为列（格式：姓名、语文、物理）。

参考SQL：

SELECT s.name,       MAX(CASE WHEN c.course_name = '语文' THEN sc.score END) AS 语文,       MAX(CASE WHEN c.course_name = '物理' THEN sc.score END) AS 物理FROM student sJOIN score sc ON s.id = sc.student_idJOIN course c ON sc.course_id = c.course_idGROUP BY s.name, s.id;

48、列转行（UNION ALL实现）

需求：我们要求将“学生的数学、英语分数”从列转为行（格式：姓名、课程、分数）。

用SQL来实现：

-- 先创建行转列后的临时表（假设已存在）CREATE TEMPORARY TABLE student_score_pivot (    name VARCHAR(20),    数学 INT,    英语 INT);INSERT INTO student_score_pivot VALUES('小明', 90, 85),('小红', 88, 92);-- 列转行：用UNION ALL将不同列转换为不同行SELECT name, '数学' AS 课程, 数学 AS 分数 FROM student_score_pivotUNION ALLSELECT name, '英语' AS 课程, 英语 AS 分数 FROM student_score_pivotORDER BY name, 课程;

解析：

• 列转行：用UNION ALL将每一列转为一行，同时添加“列名对应的分类字段”（如：课程名称）；
• 确保UNION ALL前后的字段数和类型一致。

练习：请将“学生的语文、物理分数”从列转为行（格式：姓名、课程、分数）。

参考SQL：

-- 假设临时表已存在INSERT INTO student_score_pivot (name, 语文, 物理) VALUES('张三', 78, 85),('李四', 82, 79);-- 列转行SELECT name, '语文' AS 课程, 语文 AS 分数 FROM student_score_pivotUNION ALLSELECT name, '物理' AS 课程, 物理 AS 分数 FROM student_score_pivotORDER BY name, 课程;

49、分页查询（LIMIT+OFFSET优化）

需求：我们要查询“学生按年龄降序排列后的第3-5条记录”（每页3条，第2页）。

用SQL来实现：

-- 场景1：查询第3-5条记录（非分页，直接定位）SELECT name AS 学生姓名,        age AS 年龄FROM studentORDER BY age DESCLIMIT 2, 3;  -- 跳过前2条，取3条（第3-5条）-- 场景2：查询第2页（每页3条，即第4-6条）SELECT name AS 学生姓名,        age AS 年龄FROM studentORDER BY age DESCLIMIT 3, 3;  -- (2-1)*3=3，跳过前3条，取3条（第4-6条）-- 优化版（场景2：第2页）：用主键定位（适用于大数据量）SELECT name AS 学生姓名,        age AS 年龄FROM studentWHERE id > (    SELECT id     FROM student     ORDER BY age DESC     LIMIT 3, 1  -- 取第3条的下一条（即第4条的前一条）作为偏移点)ORDER BY age DESCLIMIT 3;  -- 取3条（第4-6条）

练习：请查询“学生按数学分数降序排列后的第4-6条记录”（每页3条，第2页），请用两种方法实现。

参考SQL：

-- 写法1：MySQL简写（LIMIT 偏移量, 记录数）SELECT   s.name AS 学生姓名,   sc.score AS 数学分数FROM student s-- 关联成绩表，获取学生的数学分数JOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1  -- 假设course_id=1代表“数学”ORDER BY sc.score DESC  -- 按数学分数降序排列LIMIT 3, 3;  -- 偏移3条（跳过前3条），取3条 → 第4-6条-- 写法2：标准SQL（LIMIT 记录数 OFFSET 偏移量），兼容性更广（如：PostgreSQL）SELECT   s.name AS 学生姓名,   sc.score AS 数学分数FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1ORDER BY sc.score DESCLIMIT 3 OFFSET 3;-- 写法3：含分数相同的兼容处理SELECT   s.name AS 学生姓名,   sc.score AS 数学分数FROM student sJOIN score sc ON s.id = sc.student_idWHERE sc.course_id = 1  -- 筛选数学课程  -- 子查询：找到“按分数降序排列的第3条记录的主键（sc.id）”  AND sc.id > (    SELECT sc_inner.id    FROM score sc_inner    WHERE sc_inner.course_id = 1    ORDER BY sc_inner.score DESC, sc_inner.id DESC  -- 分数相同则按主键降序，确保顺序唯一    LIMIT 1 OFFSET 2  -- 偏移2条，取第3条记录的主键  )-- 外层排序：与子查询排序逻辑一致，确保结果顺序正确ORDER BY sc.score DESC, sc.id DESCLIMIT 3;  -- 取3条 → 第4-6条

50、重复数据处理（DISTINCT、GROUP BY、DELETE）

需求：1、我们要查询“学生表中不重复的班级”；2、我们要求删除“score表中student_id和course_id都相同的重复记录，保留id最小的一条”。

用SQL来实现：

-- 1、查询不重复的班级（DISTINCT，适用于简单去重）SELECT DISTINCT class AS 不重复班级FROM student;-- 2、删除score表重复记录（保留id最小的，仅删除真正重复的记录）-- 步骤1：先查询重复记录的“最小id”（仅筛选出有重复的组）SELECT student_id,        course_id,        MIN(id) AS min_id  -- 每组要保留的idFROM scoreGROUP BY student_id, course_idHAVING COUNT(id) > 1;  -- 仅保留有重复的组（避免处理无重复记录）-- 步骤2：删除“属于重复组且id不是最小”的记录DELETE FROM scoreWHERE (student_id, course_id) IN (    -- 嵌套临时表，避免直接引用score    SELECT temp1.student_id, temp1.course_id    FROM (SELECT student_id, course_id          FROM score          GROUP BY student_id, course_id          HAVING COUNT(id) > 1) AS temp1)  AND id NOT IN (    SELECT temp2.min_id    FROM (SELECT student_id, course_id, MIN(id) AS min_id          FROM score          GROUP BY student_id, course_id          HAVING COUNT(id) > 1) AS temp2);

练习：1、请查询“score表中不重复的course_id”；2、请删除“student表中name和class都相同的重复记录，保留id最大的一条”。

参考SQL：

-- 1、查询不重复的course_idSELECT DISTINCT course_id AS 不重复课程IDFROM score;-- 2、删除student表重复记录（保留id最大的）-- 步骤1：定位重复组（name和class都相同的组）SELECT name,        class,        MAX(id) AS max_id  -- 每组要保留的idFROM studentGROUP BY name, classHAVING COUNT(id) > 1;-- 步骤2：删除重复记录DELETE FROM studentWHERE (name, class) IN (SELECT name, class                        FROM student                        GROUP BY name, class                        HAVING COUNT(id) > 1)  -- 定位重复组  AND id NOT IN (SELECT max_id                 FROM (SELECT name, class, MAX(id) AS max_id                       FROM student                       GROUP BY name, class                       HAVING COUNT(id) > 1) AS temp);  -- 排除要保留的最大id