SQL 进阶：让查询更强大

SQL 进阶让查询更强大一句话总结SQL 进阶的核心是多表连接JOIN把分散的数据拼起来用聚合函数COUNT/SUM/AVG/MAX/MIN做统计用 GROUP BY 做分组汇总用子查询和视图让复杂查询变得优雅可复用。一、为什么需要进阶 SQL上一篇学会了单表查询但真实世界的数据分散在多个表中。例如学生信息在学生表系名在系表——想知道张三属于哪个系需要跨表查询订单在订单表商品信息在商品表——想知道每笔订单买了什么需要跨表查询要统计每个系的平均成绩需要分组计算进阶 SQL 就是解决这些多表、统计、复杂条件问题的利器。二、多表连接JOIN 的艺术2.1 为什么需要连接假设有两张表学生表学号姓名系号001张三D1002李四D2003王五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 多表连接三张或以上表连接只需继续 JOINSELECTs.姓名,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男15D1女10D2男8D2女12GROUP 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子查询改写将以下子查询改写为 JOINSELECT姓名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 BYJOIN 结果行数暴增忘了写连接条件产生笛卡尔积确保 JOIN 有 ON 条件八、下篇预告下一篇我们将学习数据库完整性约束——如何通过主键、外键、CHECK、NOT NULL 等机制在数据库层面守住数据质量底线防止脏数据、非法数据进入系统。