MySQL 数据库 4（Day52）

主题：上节复习、分组查询（GROUP BY + 聚合函数 COUNT/SUM/AVG/MAX/MIN）、HAVING 与 WHERE 区别、ORDER BY 排序与 LIMIT 分页、链表查询（多表连接）、物理表与虚拟表连接、子查询（标量/列/行/表子查询、IN/NOT IN/EXISTS/NOT EXISTS）

---

结构总览

MySQL 数据库 4
  ├─ 上节复习：表关系/外键/CRUD/单表查询
  ├─ 分组查询 GROUP BY
  │    ├─ 聚合函数：COUNT / SUM / AVG / MAX / MIN
  │    ├─ 按单字段/多字段分组
  │    └─ 普通字段必须出现在 GROUP BY 中
  ├─ HAVING vs WHERE
  │    ├─ WHERE：分组前过滤原始记录
  │    └─ HAVING：分组后过滤统计结果（可聚合）
  ├─ ORDER BY 与 LIMIT
  │    ├─ ASC / DESC、多字段排序
  │    ├─ LIMIT 起始位置, 返回数量（从 0 开始）
  │    └─ 分页：偏移量 = (页码 - 1) × 每页记录数
  ├─ 链表查询（多表连接）
  │    ├─ 隐式连接（FROM 多表 + WHERE 连接条件）
  │    ├─ 显式连接（INNER JOIN ... ON）
  │    ├─ 表别名、三表连接、LEFT JOIN 保留左表
  │    └─ 笛卡尔积陷阱
  ├─ 物理表与虚拟表连接
  │    ├─ 物理表：真实存储数据（student/class/teacher）
  │    ├─ 虚拟表：子查询/派生表/视图生成的结果集
  │    └─ 视图 CREATE VIEW / DROP VIEW
  └─ 子查询（Subquery）
       ├─ 标量子查询（返回一个值）：= > <
       ├─ 列子查询（返回一列）：IN / NOT IN
       ├─ EXISTS / NOT EXISTS（是否存在记录）
       ├─ 表子查询（多行多列）：FROM 后作派生表（必须别名）
       └─ SELECT 中嵌套标量子查询

关键要点

分组查询 GROUP BY： 按一个或多个字段分组，配合聚合函数统计每组数据。聚合函数：COUNT() 统计数量、SUM() 求和、AVG() 平均值、MAX() 最大值、MIN() 最小值。注意：COUNT(字段名) 不统计 NULL，统计全部记录应使用 COUNT(*)；查询中同时出现普通字段和聚合函数时，普通字段必须出现在 GROUP BY 中。

-- 按班级统计人数与平均年龄
SELECT class_id, COUNT(*) AS student_count, AVG(age) AS average_age
FROM student
GROUP BY class_id;
 
-- 按多个字段分组
SELECT class_id, gender, COUNT(*) AS total
FROM student
GROUP BY class_id, gender;

HAVING vs WHERE：

对比项	WHERE	HAVING
过滤对象	原始记录	分组结果
执行阶段	分组前	分组后
聚合函数	通常不适合	适合

查询执行顺序：FROM → WHERE → GROUP BY → 聚合 → HAVING → ORDER BY → LIMIT。不能写 WHERE COUNT(*) > 3，应写 HAVING COUNT(*) > 3。

ORDER BY 与 LIMIT： ORDER BY 字段 ASC/DESC（默认 ASC）；多字段排序 ORDER BY class_id ASC, age DESC。LIMIT 起始位置, 返回数量，起始从 0 开始；分页偏移量 = (页码 - 1) × 每页记录数（每页 10 条第 4 页 = LIMIT 30, 10）。分页必须配合 ORDER BY 保证结果稳定。

-- 学生人数最多的前 3 个班级
SELECT class_id, COUNT(*) AS student_count
FROM student
GROUP BY class_id
ORDER BY student_count DESC
LIMIT 3;

链表查询（多表连接）： 通过连接条件组合多表数据（student.class_id = class.id）。

-- 隐式连接
SELECT student.name, class.name
FROM student, class
WHERE student.class_id = class.id;
 
-- 显式连接（推荐，表别名）
SELECT s.name AS student_name, c.name AS class_name
FROM student AS s
INNER JOIN class AS c
    ON s.class_id = c.id;
 
-- 三张表连接（至少两个连接条件）
SELECT s.name, c.name, t.name
FROM student AS s
INNER JOIN class AS c ON s.class_id = c.id
INNER JOIN teacher AS t ON c.teacher_id = t.id;

常见错误：忘记连接条件产生笛卡尔积（结果数量异常增加）；两表同名字段需用 表别名.字段名；定义别名后统一使用别名。

物理表与虚拟表连接： 物理表真实存储数据；虚拟表是查询结果（子查询/派生表/视图）。可以先分组统计生成虚拟表再 JOIN：

-- 统计每个班级人数后与班级表连接
SELECT c.name, result.student_count
FROM class AS c
INNER JOIN (
    SELECT class_id, COUNT(*) AS student_count
    FROM student
    GROUP BY class_id
) AS result ON c.id = result.class_id;
 
-- LEFT JOIN 保留没有学生的班级（COALESCE 处理 NULL）
SELECT c.name, COALESCE(result.student_count, 0)
FROM class AS c
LEFT JOIN (...) AS result ON c.id = result.class_id;
 
-- 视图：把查询结果固化为虚拟表
CREATE VIEW class_student_count AS
SELECT class_id, COUNT(*) AS student_count FROM student GROUP BY class_id;
DROP VIEW class_student_count;

注意：FROM/JOIN 后的子查询必须设置别名；INNER JOIN 会丢弃无匹配的主表记录，需保留全部左表记录时用 LEFT JOIN。

子查询（Subquery）： 嵌套在另一条 SQL 中的查询，外层为主查询。

类型	返回结果	常用运算符/位置
标量子查询	一个值	=、>、<（如 age > (SELECT AVG(age) FROM student)）
列子查询	一列数据	IN、NOT IN
行子查询	一行数据	比较运算
表子查询	多行多列	放在 FROM 后作派生表（必须别名）

-- 标量子查询：年龄大于平均年龄
SELECT name, age FROM student
WHERE age > (SELECT AVG(age) FROM student);
 
-- IN：属于指定班级的学生
SELECT name, class_id FROM student
WHERE class_id IN (SELECT id FROM class WHERE name IN ('一班', '二班'));
 
-- EXISTS：判断是否存在学生（关联子查询）
SELECT c.id, c.name FROM class AS c
WHERE EXISTS (SELECT 1 FROM student AS s WHERE s.class_id = c.id);
 
-- NOT EXISTS：没有学生的班级
SELECT c.id, c.name FROM class AS c
WHERE NOT EXISTS (SELECT 1 FROM student AS s WHERE s.class_id = c.id);
 
-- 表子查询作为派生表
SELECT result.class_id, result.student_count
FROM (SELECT class_id, COUNT(*) AS student_count FROM student GROUP BY class_id) AS result
WHERE result.student_count >= 2;
 
-- SELECT 中嵌套标量子查询
SELECT s.name, s.age, (SELECT AVG(age) FROM student) AS average_age FROM student AS s;

排错：= 连接返回多行的子查询会报错（改用 IN）；子查询返回 NULL 会导致 NOT IN 结果为空（改用 NOT EXISTS）；FROM 后子查询必须设别名。

当日总结

• 复习：表关系（外键）、表结构修改、CRUD、单表查询
• GROUP BY 分组 + 聚合函数（COUNT/SUM/AVG/MAX/MIN）统计每组数据
• WHERE 过滤分组前原始记录，HAVING 过滤分组后统计结果
• ORDER BY 排序（ASC/DESC），LIMIT 分页（偏移从 0 开始）
• 链表查询：隐式连接 / 显式 INNER JOIN / LEFT JOIN / 三表连接，避免笛卡尔积
• 物理表真实存储，虚拟表来自子查询/派生表/视图
• 子查询按返回结果选择运算符：单值 =/></<，多值 IN/NOT IN，存在性 EXISTS/NOT EXISTS，多行多列作派生表

相关页面

• MySQL 概念页 — 本课核心概念页（分组/HAVING/排序/分页/连接/子查询）
• MySQL 数据库 3（Day51） — 上一实操课（表关系/外键/单表查询/WHERE）
• MySQL 数据库 2（Day50） — 数据类型/约束/存储引擎
• MySQL 数据库 1（Day49） — 基础实操（密码/配置/SQL/安装）
• MySQL 概念页 — 数据库层枢纽