SQL 进阶：让查询更强大
一句话总结：SQL 进阶的核心是多表连接（JOIN）把分散的数据拼起来，用聚合函数（COUNT/SUM/AVG/MAX/MIN）做统计，用 GROUP BY 做分组汇总，用子查询和视图让复杂查询变得优雅可复用。
一、为什么需要进阶 SQL？
上一篇学会了单表查询，但真实世界的数据分散在多个表中。例如：
- 学生信息在"学生表"，系名在"系表"——想知道"张三属于哪个系"，需要跨表查询
- 订单在"订单表"，商品信息在"商品表"——想知道"每笔订单买了什么"，需要跨表查询
- 要统计"每个系的平均成绩"，需要分组计算
进阶 SQL 就是解决这些多表、统计、复杂条件问题的利器。
二、多表连接：JOIN 的艺术
2.1 为什么需要连接？
假设有两张表：
学生表：
| 学号 | 姓名 | 系号 |
|---|---|---|
| 001 | 张三 | D1 |
| 002 | 李四 | D2 |
| 003 | 王五 | D1 |
系表：
| 系号 | 系名 | 系主任 |
|---|---|---|
| D1 | 计算机 | 赵教授 |
| D2 | 电子 | 钱教授 |
如何查询"张三属于哪个系"？单靠学生表只能拿到"D1"，但"D1"是什么意思需要查系表。JOIN 就是解决这个问题的。
2.2 内连接：INNER JOIN（最常用）
只返回两表匹配的行。
SELECT学生.学号,学生.姓名,系.系名FROM学生INNERJOIN系ON学生.系号=系.系号;结果：
| 学号 | 姓名 | 系名 |
|---|---|---|
| 001 | 张三 | 计算机 |
| 002 | 李四 | 电子 |
| 003 | 王五 | 计算机 |
如果学生表中有"系号 = D3"，但系表里没有 D3，这条记录不会出现在结果中。
简写形式（用 WHERE 实现相同效果）：
SELECT学生.学号,学生.姓名,系.系名FROM学生,系WHERE学生.系号=系.系号;推荐使用
JOIN ... ON语法，更清晰、更安全（不易遗漏连接条件）。
2.3 左连接：LEFT JOIN
返回左表的所有行，右表中没有匹配的行用 NULL 填充。
SELECT学生.学号,学生.姓名,系.系名FROM学生LEFTJOIN系ON学生.系号=系.系号;如果学生表中有"D3"而系表没有，结果会显示：
| 学号 | 姓名 | 系名 |
|---|---|---|
| 004 | 赵六 | NULL |
适合场景：查找"没有分配系的学生"、"没有下过订单的客户"等。
2.4 右连接：RIGHT JOIN
与 LEFT JOIN 相反，返回右表的所有行。
SELECT学生.学号,学生.姓名,系.系名FROM学生RIGHTJOIN系ON学生.系号=系.系号;实际中较少使用，通常用 LEFT JOIN 调换表顺序即可替代。
2.5 全外连接：FULL OUTER JOIN
返回两表的所有行，没有匹配的行用 NULL 填充。
SELECT学生.学号,学生.姓名,系.系名FROM学生FULLOUTERJOIN系ON学生.系号=系.系号;⚠️ MySQL 不支持 FULL OUTER JOIN，需要用 UNION 模拟。
2.6 连接类型总结
┌─────────────────────────────────────────────────┐ │ JOIN 类型图解 │ ├─────────────────────────────────────────────────┤ │ │ │ 左表 ○○○○○ 右表 □□□□□ │ │ │ │ INNER JOIN: ○○□□○ (交集) │ │ LEFT JOIN: ○○□□○○○ (左表全部 + 匹配) │ │ RIGHT JOIN: ○○□□□□□ (右表全部 + 匹配) │ │ FULL JOIN: ○○□□○○○○□□□ (并集) │ │ │ │ CROSS JOIN: ○○○○○ × □□□□□ = 25 行 (笛卡尔积) │ │ │ └─────────────────────────────────────────────────┘2.7 自连接：表连接自己
有时候需要把同一张表连接两次。
示例：查找"同一个系的学生"（即"和我同系的同学"）
SELECTa.姓名AS学生A,b.姓名AS学生B,a.系号FROM学生 aJOIN学生 bONa.系号=b.系号ANDa.学号!=b.学号;需要给表起别名（a 和 b），否则数据库分不清。
2.8 多表连接
三张或以上表连接，只需继续 JOIN：
SELECTs.姓名,d.系名,c.课程名,sc.成绩FROM学生 sJOIN系 dONs.系号=d.系号JOIN选课 scONs.学号=sc.学号JOIN课程 cONsc.课程号=c.课程号WHEREs.姓名='张三';给表起简短别名（s, d, c, sc）能大幅简化 SQL。
三、聚合函数：数据统计的瑞士军刀
3.1 五大聚合函数
| 函数 | 功能 | 示例 |
|---|---|---|
COUNT(*) | 统计行数 | SELECT COUNT(*) FROM 学生;→ 总人数 |
COUNT(列) | 统计非 NULL 行数 | SELECT COUNT(专业) FROM 学生; |
SUM(列) | 求和 | SELECT SUM(成绩) FROM 选课; |
AVG(列) | 平均值 | SELECT AVG(成绩) FROM 选课; |
MAX(列) | 最大值 | SELECT MAX(成绩) FROM 选课; |
MIN(列) | 最小值 | SELECT MIN(成绩) FROM 选课; |
3.2 GROUP BY：分组统计
需求：统计每个系的男女生人数。
SELECT系号,性别,COUNT(*)AS人数FROM学生GROUPBY系号,性别;结果：
| 系号 | 性别 | 人数 |
|---|---|---|
| D1 | 男 | 15 |
| D1 | 女 | 10 |
| D2 | 男 | 8 |
| D2 | 女 | 12 |
GROUP BY 后，SELECT 中只能出现聚合函数或分组列。
3.3 HAVING：分组后的筛选
WHERE 是在分组前过滤行，HAVING 是在分组后过滤组。
-- 查找"人数超过 10 人"的系SELECT系号,COUNT(*)AS人数FROM学生GROUPBY系号HAVINGCOUNT(*)>10;WHERE 和 HAVING 的顺序不可颠倒！
-- 完整执行顺序（了解即可）FROM→WHERE→GROUPBY→HAVING→SELECT→ORDERBY→LIMIT四、子查询：查询嵌套查询
4.1 什么是子查询？
子查询就是嵌套在另一个查询中的查询，用括号()包裹。
4.2 子查询作为条件
示例：查找"计算机系"的所有学生
SELECT*FROM学生WHERE系号=(SELECT系号FROM系WHERE系名='计算机');4.3 子查询作为集合
示例：查找"选修了课程 C01"的学生
SELECT*FROM学生WHERE学号IN(SELECT学号FROM选课WHERE课程号='C01');4.4 相关子查询
子查询依赖外层查询的值。
示例：查找"成绩高于自己平均成绩"的学生
SELECTs.学号,s.课程号,s.成绩FROM选课 sWHEREs.成绩>(SELECTAVG(成绩)FROM选课WHERE学号=s.学号);相关子查询效率较低，大数据量时慎用。
4.5 子查询 vs JOIN
很多子查询可以改写成 JOIN，通常 JOIN 效率更高：
-- 子查询写法SELECT*FROM学生WHERE系号IN(SELECT系号FROM系WHERE系名='计算机');-- 等价 JOIN 写法（推荐）SELECT学生.*FROM学生JOIN系ON学生.系号=系.系号WHERE系.系名='计算机';五、视图：虚拟表的魅力
5.1 什么是视图？
视图（View）是从一个或多个表导出的虚拟表，不存储实际数据，只保存查询定义。
-- 创建视图：计算机系学生视图CREATEVIEW计算机系学生ASSELECT学号,姓名,性别,年龄FROM学生WHERE系号='D1';-- 使用视图（就像查一张表）SELECT*FROM计算机系学生WHERE年龄>19;5.2 视图的优点
- 简化复杂查询：把复杂 JOIN 封装成视图，查询时像查单表
- 数据安全性：只暴露部分列给用户（如隐藏工资字段）
- 逻辑独立性：底层表结构变化，只需改视图，不用改应用
5.3 视图的限制
- 不是所有视图都能更新（涉及聚合、DISTINCT、GROUP BY 的视图通常不可更新）
- 视图查询本质是执行底层 SQL，复杂视图可能影响性能
六、动手练习
练习 1：多表连接
给定学生表、课程表、选课表，查询"张三选修的所有课程名称及成绩"。
练习 2：聚合与分组
统计：
- 每个系的男生人数、女生人数、总人数
- 每门课程的平均分、最高分、最低分
- 找出平均分低于 60 分的课程
练习 3：子查询改写
将以下子查询改写为 JOIN：
SELECT姓名FROM学生WHERE学号IN(SELECT学号FROM选课WHERE成绩>90);练习 4：创建视图
创建视图"优秀学生"，包含成绩 >= 90 分的学生的学号、姓名、课程名和成绩。
七、常见错误与排错
| 错误 | 原因 | 解决 |
|---|---|---|
Column is ambiguous | 两表有同名列，没指定表名 | 加表名前缀：a.系号 |
Invalid use of group function | 聚合函数不能用在 WHERE 中 | 用 HAVING 替代，或把聚合放子查询 |
Subquery returns more than one row | 子查询返回多行，但用了= | 改用IN或确保子查询只返回一行 |
View is not updatable | 视图不可更新 | 检查视图是否包含聚合、DISTINCT、GROUP BY |
| JOIN 结果行数暴增 | 忘了写连接条件，产生笛卡尔积 | 确保 JOIN 有 ON 条件 |
八、下篇预告
下一篇，我们将学习数据库完整性约束——如何通过主键、外键、CHECK、NOT NULL 等机制，在数据库层面"守住数据质量底线"，防止脏数据、非法数据进入系统。
