python学习体系

Appearance

章节2：SQL 高级查询 ​

一、学习目标 ​

完成本章学习后，你将能够：

  1. 掌握排序分页与聚合函数的组合使用
  2. 熟练运用 GROUP BY 分组与 HAVING 条件过滤
  3. 辨析 INNER / LEFT / RIGHT JOIN 的差异并正确联查多表
  4. 使用子查询与 EXISTS / NOT EXISTS 优化查询逻辑
  5. 应用窗口函数（ROW_NUMBER / RANK）和 CTE 公用表达式

二、核心知识点 ​

2.1 排序分页与聚合函数 ​

定义 ​

  • ORDER BY：对查询结果按一列或多列排序（ASC 升序 / DESC 降序）
  • LIMIT：限制返回的行数，实现分页
  • 聚合函数：对一组值执行计算并返回单个值
聚合函数作用
COUNT()统计行数
SUM()求和
AVG()求平均值
MAX()求最大值
MIN()求最小值

语法与示例 ​

sql
-- 准备表和数据
USE school;
CREATE TABLE score (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    subject VARCHAR(50),
    score DECIMAL(5,2),
    exam_date DATE
);

INSERT INTO score (student_id, subject, score, exam_date) VALUES
(1, '数学', 88.5, '2024-01-15'),
(1, '语文', 92.0, '2024-01-15'),
(2, '数学', 75.0, '2024-01-15'),
(2, '语文', 85.5, '2024-01-15'),
(3, '数学', 95.0, '2024-01-15'),
(3, '语文', 78.0, '2024-01-15'),
(1, '英语', 90.0, '2024-06-20'),
(2, '英语', 82.0, '2024-06-20'),
(3, '英语', 88.0, '2024-06-20');

-- ORDER BY 排序
SELECT * FROM score ORDER BY score DESC;
SELECT * FROM score ORDER BY score DESC, student_id ASC;

-- LIMIT 分页
-- 每页3条，第1页
SELECT * FROM score ORDER BY id LIMIT 3 OFFSET 0;
-- 每页3条，第2页
SELECT * FROM score ORDER BY id LIMIT 3 OFFSET 3;
-- 简化语法：LIMIT 偏移量, 条数
SELECT * FROM score ORDER BY id LIMIT 3, 3;

-- 聚合函数
SELECT COUNT(*) AS 总记录数 FROM score;
SELECT COUNT(DISTINCT student_id) AS 学生数 FROM score;

SELECT
    AVG(score) AS 平均分,
    MAX(score) AS 最高分,
    MIN(score) AS 最低分,
    SUM(score) AS 总分
FROM score;

-- 聚合结合 WHERE
SELECT AVG(score) AS 数学平均分
FROM score
WHERE subject = '数学';

2.2 分组查询与 HAVING 条件 ​

定义 ​

  • GROUP BY：将结果集按一个或多个列分组，配合聚合函数使用
  • HAVING：对分组后的结果进行条件过滤（WHERE 在分组前过滤，HAVING 在分组后过滤）

执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

语法与示例 ​

sql
-- 按学科分组，计算各科平均分
SELECT
    subject AS 学科,
    COUNT(*) AS 考试次数,
    AVG(score) AS 平均分,
    MAX(score) AS 最高分
FROM score
GROUP BY subject;

-- 按学生分组，统计每人总分与平均分
SELECT
    student_id,
    COUNT(*) AS 科目数,
    SUM(score) AS 总分,
    ROUND(AVG(score), 2) AS 平均分
FROM score
GROUP BY student_id;

-- HAVING 过滤：筛选平均分 > 85 的学科
SELECT
    subject,
    AVG(score) AS avg_score
FROM score
GROUP BY subject
HAVING avg_score > 85;

-- WHERE + GROUP BY + HAVING 组合
-- 查询2024年上半年考试中，平均分>=85的学科
SELECT
    subject,
    COUNT(*) AS 考试次数,
    AVG(score) AS 平均分
FROM score
WHERE exam_date BETWEEN '2024-01-01' AND '2024-06-30'
GROUP BY subject
HAVING 平均分 >= 85
ORDER BY 平均分 DESC;

2.3 多表联查（JOIN） ​

定义 ​

JOIN 用于将两个或多个表的行基于相关列合并在一起。

JOIN 类型返回结果
INNER JOIN两表匹配的记录（交集）
LEFT JOIN左表全部 + 右表匹配行（无匹配则为 NULL）
RIGHT JOIN右表全部 + 左表匹配行（无匹配则为 NULL）

语法与示例 ​

sql
-- 准备班级表
CREATE TABLE class (
    id INT PRIMARY KEY,
    name VARCHAR(50) COMMENT '班级名称'
);
INSERT INTO class VALUES (1, 'AI一班'), (2, 'AI二班'), (3, 'AI三班');

-- student 表已有的 class_id 包含 1,2,3

-- INNER JOIN：只返回有班级的学生
SELECT
    s.name AS 学生姓名,
    c.name AS 班级名称
FROM student s
INNER JOIN class c ON s.class_id = c.id;

-- LEFT JOIN：返回所有学生，无班级匹配的显示 NULL
-- 假设某学生 class_id=99
SELECT
    s.name,
    c.name AS 班级名称
FROM student s
LEFT JOIN class c ON s.class_id = c.id;

-- RIGHT JOIN：返回所有班级，无学生的显示 NULL
SELECT
    s.name,
    c.name AS 班级名称
FROM student s
RIGHT JOIN class c ON s.class_id = c.id;

-- 三表联查：学生 + 班级 + 成绩
SELECT
    s.name AS 学生,
    c.name AS 班级,
    sc.subject AS 学科,
    sc.score AS 成绩
FROM student s
INNER JOIN class c ON s.class_id = c.id
INNER JOIN score sc ON s.id = sc.student_id
ORDER BY s.name, sc.subject;

-- 表的别名（使用 AS 简写）
SELECT s.name, c.name AS class_name
FROM student s
LEFT JOIN class c ON s.class_id = c.id;

2.4 子查询与 EXISTS ​

定义 ​

  • 子查询：嵌套在另一个 SQL 语句内部的 SELECT 查询，分为标量子查询、行子查询、表子查询
  • EXISTS / NOT EXISTS：检查子查询是否返回任何行，常用于存在性判断

语法与示例 ​

sql
-- 标量子查询：查询分数高于平均分的学生记录
SELECT * FROM score
WHERE score > (SELECT AVG(score) FROM score);

-- 表子查询（FROM 子句）
SELECT
    t.student_id,
    t.avg_score
FROM (
    SELECT
        student_id,
        AVG(score) AS avg_score
    FROM score
    GROUP BY student_id
) t
WHERE t.avg_score > 85;

-- IN 子查询：查询有考试成绩的学生姓名
SELECT name FROM student
WHERE id IN (SELECT DISTINCT student_id FROM score);

-- EXISTS：查询有考试成绩的学生（效率更高）
SELECT name FROM student s
WHERE EXISTS (
    SELECT 1 FROM score sc
    WHERE sc.student_id = s.id
);

-- NOT EXISTS：查询没有考试成绩的学生
SELECT name FROM student s
WHERE NOT EXISTS (
    SELECT 1 FROM score sc
    WHERE sc.student_id = s.id
);

-- 子查询 vs JOIN 对比
-- 场景：每个学生最近一次考试记录
SELECT s.name, sc.*
FROM student s
INNER JOIN score sc ON s.id = sc.student_id
WHERE sc.exam_date = (
    SELECT MAX(exam_date)
    FROM score sc2
    WHERE sc2.student_id = s.id
);

优化提示：EXISTS 在子查询表很大时通常比 IN 快，因为它是半连接，找到匹配即停止。

2.5 窗口函数与 CTE 公用表达式 ​

定义 ​

  • 窗口函数：在不合并行的前提下，对每一行进行跨行计算。语法：函数() OVER (PARTITION BY 列 ORDER BY 列)
  • CTE（Common Table Expression）：使用 WITH AS 定义临时结果集，提高可读性和复用性

常用窗口函数 ​

函数作用
ROW_NUMBER()为每行分配唯一递增序号（相同值也递增）
RANK()排名，相同值并列，后续跳过位次
DENSE_RANK()排名，相同值并列，后续不跳位次
LAG() / LEAD()访问前/后行的值
SUM() OVER()累计求和

语法与示例 ​

sql
-- ======== 窗口函数示例 ========

-- ROW_NUMBER：每科成绩按分数排名（不重复）
SELECT
    subject,
    student_id,
    score,
    ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS 排名
FROM score;

-- RANK：相同分数并列，跳过后续名次
SELECT
    subject,
    student_id,
    score,
    RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS 排名
FROM score;

-- 每个学生总分排名（全量排名）
SELECT
    student_id,
    SUM(score) AS 总分,
    RANK() OVER (ORDER BY SUM(score) DESC) AS 总分排名
FROM score
GROUP BY student_id;

-- LAG：查看前一科成绩对比
SELECT
    student_id,
    subject,
    score,
    LAG(score, 1) OVER (PARTITION BY student_id ORDER BY exam_date) AS 上次成绩
FROM score;

-- ======== CTE 公用表达式示例 ========

-- 用 CTE 计算每个学生平均分，再筛选
WITH student_avg AS (
    SELECT
        student_id,
        ROUND(AVG(score), 2) AS avg_score,
        COUNT(*) AS exam_count
    FROM score
    GROUP BY student_id
)
SELECT
    s.name,
    sa.avg_score,
    sa.exam_count
FROM student s
INNER JOIN student_avg sa ON s.id = sa.student_id
WHERE sa.avg_score >= 85;

-- 递归 CTE：生成连续日期序列
WITH RECURSIVE date_range AS (
    SELECT '2024-01-01' AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_range
    WHERE dt < '2024-01-10'
)
SELECT * FROM date_range;

-- CTE + 窗口函数：每科前三名
WITH subject_rank AS (
    SELECT
        subject,
        student_id,
        score,
        ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS rn
    FROM score
)
SELECT *
FROM subject_rank
WHERE rn <= 3;

三、本章小结 ​

知识点关键要点
排序分页ORDER BY + LIMIT OFFSET 实现高效分页
聚合分组GROUP BY 配合聚合函数，HAVING 过滤分组结果
多表联查INNER JOIN 取交集 / LEFT JOIN 保左表 / RIGHT JOIN 保右表
子查询可出现在 SELECT / FROM / WHERE 中，EXISTS 适合存在性判断
窗口函数ROW_NUMBER() / RANK() 不合并行进行排名，OVER (PARTITION BY ...) 定义窗口
CTEWITH ... AS 定义临时命名结果集，支持递归，提升查询可读性

四、练习 ​

基础练习 ​

  1. 查询每个学生的总成绩，按总分降序排列，取前 3 名
  2. 查询每个学科的平均分，只显示平均分 ≥ 85 的学科
  3. 使用 LEFT JOIN 查出所有学生及其成绩信息，包括没有成绩的学生
  4. 查找分数高于该科平均分的学生记录（使用子查询）
  5. 使用 CTE 查询每科成绩排名前三的学生

进阶练习 ​

  1. 有一张 attendance 表（student_id, date, status 出勤/缺勤），用窗口函数统计每个学生连续缺勤的最大天数
  2. 使用递归 CTE 生成从当月第一天到当天的日期列表，并左连接每日销售额，补零
  3. 对比 IN 和 EXISTS 两种写法，分析在 10 万 + 数据量下的性能差异

Python 学习资料

仅供学习使用