MySQL数据库：联合查询

适用环境MySQL 8.0示例按 MySQL 8.0.39 编写1. 联合查询解决什么问题规范化会把实体拆到不同表中读取完整业务信息时需要重新组合数据“联合查询”在本章中是一个宽泛概念主要包括类型解决的问题常用语法表连接横向组合有关联的表增加列join、left join子查询把一个查询的结果交给另一个查询使用in、exists、标量子查询集合查询纵向合并多个结构相同的结果集增加行union、union all查询结果写入把查询出的行保存到表中insert ... select、create table ... select外键不会自动连接表查询仍须写连接条件2. 多表查询的逻辑与实际执行2.1 用笛卡尔积理解逻辑结果若 A 表有 3 行、B 表有 4 行交叉连接会产生3 × 4 12行select*fromtable_acrossjointable_b;内连接可在逻辑上理解为“组合后保留满足条件的行”selects.name,c.class_namefromstudent_design2 sjoinclass_design2 conc.ids.class_id;漏写条件会使行数成倍膨胀一对多连接还会按匹配数重复左侧行2.2 MySQL 不一定真的生成完整笛卡尔积笛卡尔积是逻辑模型不等于物理执行。优化器会根据统计信息、索引和成本决定表的连接顺序而不一定按 SQL 中的书写顺序为每张表选择全表扫描、索引范围扫描或索引查找选择嵌套循环连接或 Hash Join 等算法尽早过滤无效行MySQL 常用嵌套循环合适的连接索引可加快内层查找。8.0.18 起支持 Hash Join8.0.20 起用它替代 Block Nested-Loop 的场景SQL 的逻辑处理顺序可简化为3. 内连接 INNER JOIN3.1 语法select 查询列 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 普通过滤条件;join默认就是inner join内连接只返回两边能够匹配的行。from a, b where ...也能表达内连接但工程中推荐显式join ... on因为它把连接条件与普通过滤分开更不容易漏写条件3.2 表别名与列歧义多个表都有id、name时裸写列名可能出现错误 1052。应使用短别名和别名.列名selects.idasstudent_id,s.nameasstudent_name,c.class_namefromstudent_design2 sjoinclass_design2 conc.ids.class_idwheres.name孙悟空;3.3 三表、四表连接查询每名学生的课程与成绩selects.sno,s.name,c.course_name,sc.scorefromstudent_design2 sjoinscore_design2 sconsc.student_ids.idjoincourse_design2 conc.idsc.course_idorderbys.id,c.id;每增加一张表都要确认连接列和连接基数结果异常时检查条件、列和源数据。3.4 连接后分组统计每名学生已出分课程数和平均分selects.id,s.name,count(sc.score)asgraded_count,avg(sc.score)asavg_scorefromstudent_design2 sjoinscore_design2 sconsc.student_ids.idgroupbys.id,s.name;count(sc.score)不统计NULL适合统计已出分课程count(*)统计结果行。开启only_full_group_by时查询列应参与分组、被聚合或能被分组列函数依赖4. 外连接 OUTER JOIN4.1 左连接与右连接-- 保留左表全部行 select ... from 表1 left join 表2 on 连接条件; -- 保留右表全部行 select ... from 表1 right join 表2 on 连接条件;left join保留左表全部行右侧无匹配时补NULL。交换表顺序即可把右连接改为左连接项目中统一左连接通常更易读统计所有班级人数包括 0 人班级selectc.id,c.class_name,count(s.id)asstudent_countfromclass_design2 cleftjoinstudent_design2 sons.class_idc.idgroupbyc.id,c.class_name;必须用count(s.id)若用count(*)外连接产生的空行会让空班级被统计为 14.2 查找“没有关联记录”的数据查找没有任何选课记录的学生selects.id,s.sno,s.namefromstudent_design2 sleftjoinscore_design2 sconsc.student_ids.idwheresc.student_idisnull;应检查右表中本来就不允许为NULL的主键或外键列。不要用sc.score is null判断“没有记录”因为本系统允许score为NULL表示已选课但尚未出分。这叫反连接模式也可用not exists表达4.3 ON 与 WHERE 的关键区别on决定如何匹配where过滤连接后的结果。右表条件放在where中会删除补出的NULL行使左连接近似退化为内连接-- 只返回存在及格成绩的学生selects.name,sc.scorefromstudent_design2 sleftjoinscore_design2 sconsc.student_ids.idwheresc.score60;要保留所有学生只匹配及格成绩应把条件写入onselects.name,sc.scorefromstudent_design2 sleftjoinscore_design2 sconsc.student_ids.idandsc.score60;4.4 全外连接MySQL 8.0 没有原生full outer join可用“左连接 反向左连接的未匹配部分”selecta.id,b.idfromtable_a aleftjointable_b bonb.ida.idunionallselecta.id,b.idfromtable_b bleftjointable_a aona.idb.idwherea.idisnull;5. 自连接 SELF JOIN自连接不是新关键字而是同一张表在一个查询中扮演不同角色必须使用不同别名。比较同一学生的 MySQL 与 Java 成绩selects.name,m.scoreasmysql_score,j.scoreasjava_scorefromscore_design2 mjoinscore_design2 jonj.student_idm.student_idjoinstudent_design2 sons.idm.student_idjoincourse_design2 cmoncm.idm.course_idjoincourse_design2 cjoncj.idj.course_idwherecm.course_nameMySQLandcj.course_nameJavaandm.scorej.score;用m、j区分两种成绩用student_id保证比较同一学生。6. 子查询子查询嵌套在另一条语句中其用法取决于返回一个值、一行、多行还是结果表。6.1 标量子查询返回一个值查询高于全体平均分的成绩selectstudent_id,course_id,scorefromscore_design2wherescore(selectavg(score)fromscore_design2);单值比较要求子查询至多返回一行一列返回多行会出现错误 1242。6.2 多行子查询IN查询 Java 或 MySQL 课程的成绩select*fromscore_design2wherecourse_idin(selectidfromcourse_design2wherecourse_namein(Java,MySQL));in表示等于集合中的任意值。若子查询含NULLnot in可能得到unknown而查不到行排除关联记录时优先用not exists或显式排除NULL。6.3 EXISTS 与关联子查询查询至少选过一门课的学生selects.id,s.namefromstudent_design2 swhereexists(select1fromscore_design2 scwheresc.student_ids.id);内层引用外层s.id属于关联子查询。exists只判断匹配行是否存在没有选课则用not exists。子查询不一定比连接慢。MySQL 可能将in、exists转为半连接或物化结果应通过执行计划验证。6.4 多列子查询行构造器可让多个列作为一个整体比较select*fromscore_demowhere(student_id,course_id,score)in(selectstudent_id,course_id,scorefromscore_demogroupbystudent_id,course_id,scorehavingcount(*)1);内外列的数量、顺序、类型须对应。正式表已有复合主键重复选课应在写入时被拒绝6.5 FROM 中的子查询与 CTEfrom中的子查询称为派生表MySQL 要求给它别名selectt.class_id,t.avg_scorefrom(selects.class_id,avg(sc.score)asavg_scorefromstudent_design2 sjoinscore_design2 sconsc.student_ids.idgroupbys.class_id)twheret.avg_score80;MySQL 8.0 还可用with 名称 as (子查询)定义 CTE提高复杂查询可读性。派生表或 CTE 不代表一定创建磁盘临时表优化器可能合并它也可能物化后使用6.6 如何选择返回关联表列用join判断存在性用exists与单值比较用标量子查询与集合比较用in复杂逻辑可用 CTE。先保证语义清楚再验证性能7. 集合查询连接是在同一行上横向补列集合操作把多个查询的结果纵向叠加7.1 UNION 与 UNION ALLselectsno,namefromcurrent_studentunionselectsno,namefromarchived_student;selectsno,namefromcurrent_studentunionallselectsno,namefromarchived_student;union默认去除完全相同的结果行需要额外的去重工作union all保留重复行通常更快业务允许重复时优先考虑各查询必须返回相同列数同一位置的数据类型应兼容最终列名取第一个查询的列名或别名整体排序应在最后写一次并使用最终结果的列名selectsno,namefromcurrent_studentunionallselectsno,namefromarchived_studentorderbysno;7.2 MySQL 8.0.31 之后的集合运算MySQL 8.0.31 新增intersect交集和except差集查询1 intersect [all | distinct] 查询2; 查询1 except [all | distinct] 查询2;8.0.39 支持旧版需用连接改写。集合运算默认distinct。8. 保存查询结果8.1 INSERT … SELECT把查询结果插入已有表insertintoexcellent_student(student_id,avg_score)selectstudent_id,avg(score)fromscore_design2groupbystudent_idhavingavg(score)90;目标列与查询列的数量、顺序、类型须对应并应显式写目标列名。目标表约束仍会执行自增列通常省略它只复制数据。大量迁移前先单独核对select并按原子性要求使用事务8.2 CREATE TABLE … SELECT根据查询结果直接创建新表createtableclass_score_reportasselects.class_id,count(sc.score)asgraded_count,avg(sc.score)asavg_scorefromstudent_design2 sjoinscore_design2 sconsc.student_ids.idgroupbys.class_id;它适合临时报表但不会自动创建索引auto_increment等属性也可能丢失表达式应起别名。复制同构表通常使用createtablestudent_backuplikestudent_design2;insertintostudent_backupselect*fromstudent_design2;like复制字段属性和索引但不复制外键完成后用show create table检查9. 用 EXPLAIN 理解执行计划不要凭 SQL 外观猜性能应查看执行计划explainanalyzeselects.name,sc.scorefromstudent_design2 sjoinscore_design2 sconsc.student_ids.id;explain展示估算计划explain analyze会实际执行并显示行数与耗时不要对高风险语句随意使用重点检查实际连接顺序及各步读取行数key是否使用预期索引是否出现不必要的ALL扫描估算与实际行数是否差异很大是否出现 Hash Join、临时表、排序或大量循环连接列两侧类型应一致被驱动表的连接列需要可用索引复合索引应依据真实筛选和排序设计。索引会增加写入成本并非越多越好10. 常见错误与排查现象常见原因检查方法结果行数异常巨大漏写条件或误判连接基数分步连接并统计行数错误 1052多表存在同名列使用表别名.列名左连接查不到无匹配行右表条件写进了where判断条件是否应移入on无记录被误判检查了可为NULL的业务列检查右表非空主键/外键错误 1242标量子查询返回多行改用in或保证只返回一行not in意外返回空集子查询含NULL改用not exists或排除NULLunion报列数错误各查询列数不同逐个执行并核对列的位置聚合结果偏大连接先重复了事实行确认表粒度和连接基数排查时先验证单表条件再一次加入一张表和一个on比较每步行数最后添加分组与排序。连接错误进入聚合后常只留下看似合理的错误数字11. 综合查询查询所有学生的班级、已选课程数、平均分并保留没有选课的学生selects.id,s.sno,s.name,c.class_name,count(sc.course_id)ascourse_count,round(avg(sc.score),2)asavg_scorefromstudent_design2 sjoinclass_design2 conc.ids.class_idleftjoinscore_design2 sconsc.student_ids.idgroupbys.id,s.sno,s.name,c.class_nameorderbyavg_scoredesc,s.id;设计原因学生必须属于有效班级所以学生与班级使用内连接。学生可以暂时没有选课所以成绩使用左连接。count(sc.course_id)让无选课学生得到 0而不是 1。avg忽略未出分的NULL没有有效成绩时仍为NULL不同于 0 分。聚合发生在连接之后因此分组必须与“一名学生一行”的目标粒度一致。参考MySQL 8.0嵌套循环连接MySQL 8.0Hash Join 优化MySQL 8.0子查询优化MySQL 8.0UNIONMySQL 8.0INSERT … SELECTMySQL 8.0CREATE TABLE … SELECTMySQL 8.0使用 EXPLAIN 优化查询以上是我关于MySQL的笔记分享也可以关注关注我的Syrena-Blog感谢你读到这里这也是我学习路上的一个小小记录。希望以后回头看时能看到自己的成长