SQL 进阶：让查询更强大

SQL 进阶：让查询更强大

一句话总结：SQL 进阶的核心是多表连接（JOIN）把分散的数据拼起来，用聚合函数（COUNT/SUM/AVG/MAX/MIN）做统计，用 GROUP BY 做分组汇总，用子查询和视图让复杂查询变得优雅可复用。

一、为什么需要进阶 SQL？

上一篇学会了单表查询，但真实世界的数据分散在多个表中。例如：

• 学生信息在"学生表"，系名在"系表"——想知道"张三属于哪个系"，需要跨表查询
• 订单在"订单表"，商品信息在"商品表"——想知道"每笔订单买了什么"，需要跨表查询
• 要统计"每个系的平均成绩"，需要分组计算

进阶 SQL 就是解决这些多表、统计、复杂条件问题的利器。

二、多表连接：JOIN 的艺术

2.1 为什么需要连接？

假设有两张表：

学生表：

系表：

如何查询"张三属于哪个系"？单靠学生表只能拿到"D1"，但"D1"是什么意思需要查系表。JOIN 就是解决这个问题的。

2.2 内连接：INNER JOIN（最常用）

只返回两表匹配的行。

SELECT学生.学号,学生.姓名,系.系名FROM学生INNERJOIN系ON学生.系号=系.系号;

结果：

如果学生表中有"系号 = D3"，但系表里没有 D3，这条记录不会出现在结果中。

简写形式（用 WHERE 实现相同效果）：

SELECT学生.学号,学生.姓名,系.系名FROM学生,系WHERE学生.系号=系.系号;

推荐使用JOIN ... ON语法，更清晰、更安全（不易遗漏连接条件）。

2.3 左连接：LEFT JOIN

返回左表的所有行，右表中没有匹配的行用 NULL 填充。

SELECT学生.学号,学生.姓名,系.系名FROM学生LEFTJOIN系ON学生.系号=系.系号;

如果学生表中有"D3"而系表没有，结果会显示：

适合场景：查找"没有分配系的学生"、"没有下过订单的客户"等。

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 五大聚合函数

3.2 GROUP BY：分组统计

需求：统计每个系的男女生人数。

SELECT系号,性别,COUNT(*)AS人数FROM学生GROUPBY系号,性别;

结果：

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 分的学生的学号、姓名、课程名和成绩。

七、常见错误与排错

八、下篇预告

下一篇，我们将学习数据库完整性约束——如何通过主键、外键、CHECK、NOT NULL 等机制，在数据库层面"守住数据质量底线"，防止脏数据、非法数据进入系统。