目录零、准备条件一、从一个简单的例子说起学生表和班级表二、连接条件救星来了三、实战练习查询孙悟空的信息四、内连接更优雅的写法五、内连接练习从简单到复杂练习1查询唐三藏的成绩​编辑练习2查询所有同学的总成绩​编辑练习3查询所有同学每门课的成绩三表连接六、外连接处理孤儿数据总结原来你写的SQL查询这么危险从笛卡尔积到内外连接我用这篇彻底搞懂了多表查询今天整理学习笔记--MySQL多表查询的内容。原本以为查几个表就是简单的把表名一写就完事了结果差点把数据库干崩溃 今天就把这段心路历程分享出来希望能帮到同样在学SQL的你本身需求中就会出现带有关联关系的实体~~ 查询的时候通常要把多个实体的信息放到一起进行查询~~联合查询也称为 “多表查询”比单个表的查询更复杂 / 更低效~~零、准备条件-- 0.1 切换或创建数据库 create database if not exists java117_2; use java117_2; -- 0.2 【清理旧环境】为了避免和之前课程的表冲突先把之前创建的表删掉如果存在 -- 注意生产环境慎用 DROP这里是教学演示确保环境纯净 drop table if exists student; drop table if exists score; drop table if exists course; drop table if exists class; -- 0.3 【创建核心表结构】 -- 课程表存储课程信息 create table course ( course_id int primary key auto_increment, name varchar(20) ); -- 班级表存储班级信息 create table class ( class_id int primary key auto_increment, name varchar(20) ); -- 学生表存储学生信息包含外键 class_id 关联班级 create table student ( student_id int primary key auto_increment, name varchar(20), sno varchar(10), class_id int ); -- 成绩表存储学生成绩包含外键 student_id 和 course_id create table score ( student_id int, course_id int, score int ); -- 0.4 【批量插入初始化数据】 -- 插入课程数据 insert into course (name) values (Java), (C), (MySQL), (操作系统), (计算机网络), (数据结构); -- 插入班级数据 insert into class(name) values (Java001班), (C001班), (前端001班); -- 插入学生数据包含学号、姓名、所属班级ID insert into student values (null, 唐三藏, 100001, 1), (null, 孙悟空, 100002, 1), (null, 猪八戒, 100003, 1), (null, 沙悟净, 100004, 1), (null, 宋江, 200001, 2), (null, 武松, 200002, 2), (null, 李逵, 200003, 2), (null, 不想毕业, 200004, 2); -- 插入成绩数据student_id, course_id, score insert into score (student_id, course_id, score) values (70.5, 1, 1), (98.5, 1, 3), (33, 1, 5), (98, 1, 6), (60, 2, 1), (59.5, 2, 5), (33, 3, 1), (68, 3, 3), (99, 3, 5), (67, 4, 1), (23, 4, 3), (56, 4, 5), (72, 4, 6), (81, 5, 1), (37, 5, 5), (56, 6, 2), (43, 6, 4), (79, 6, 6), (80, 7, 2), (92, 7, 6); -- 0.5 【验证数据是否创建成功】 select * from course; select * from class; select * from student; select * from score;运行完上述代码后会得到以下图片的四张表格一、从一个简单的例子说起学生表和班级表一开始我遇到的问题是这样的我有两个表student表存学生信息学号、姓名、班级IDclass表存班级信息班级ID、班级名称。现在我想查某个学生的详细信息包括他所在的班级名称。我天真地以为直接SELECT * FROM student, class;就行了结果你猜怎么着笛卡尔积来了​就像图片里展示的那样如果student表有4条数据class表有3条数据那么直接这样查会得到 4×312 条数据而且很多都是无效的比如张三学号1被错误地配对了Java100、Java101、Java102三个班级。这就好比你有一件上衣和三条裤子结果你试穿的时候把这件上衣和每条裤子都拍了照但其实你只想看它搭配其中一条的效果。二、连接条件救星来了要避免这种无效的笛卡尔积我们必须加上连接条件student.class_id class.class_id。这样就能过滤掉那些无效的匹配只保留学生和班级真正对应的记录。特别提醒了一个细节二义性问题。当两个表都有class_id这个字段时直接写WHERE class_id class_id数据库会懵逼不知道你指的是哪个表的class_id。所以必须加上前缀student.class_id class.class_id或者使用别名来简化SELECT * FROM student AS s, class AS c WHERE s.class_id c.class_id;这样一来查询就清晰多了此处使用.这个操作符有点像JavaSE语法中的--成员访问操作符那么我们可以这样理解数据库的表--对象数据库表中的列--对象的属性三、实战练习查询孙悟空的信息先明白在进行多表查询时, 必须存在某一列, 同时存在于两个表里~~(如果不存在这样的列, 可以认为笛卡尔积, 没有意义)需求来了查询学生姓名为孙悟空的详细信息包括学生个人信息和班级信息。我这样思考明确要从哪些表查student表和class表先算笛卡尔积SELECT * FROM student, class;虽然知道数据会很多但这是基础增加连接条件去掉无效数据WHERE s.class_id c.class_id;再补充查询条件AND s.name 孙悟空;最后为了精简我还可以只选择需要的列SELECT s.name, s.sno, s.class_id, c.name FROM student AS s, class AS c WHERE s.class_id c.class_id AND s.name 孙悟空;看结果就完美了只有孙悟空那一条记录带着他的班级信息四、内连接更优雅的写法其实上面那种写法就是内连接INNER JOIN。MySQL提供了更直观的语法-- 方式一传统写法隐式内连接 SELECT * FROM student, class WHERE student.class_id class.class_id; -- 方式二标准内连接写法显式内连接 SELECT * FROM student INNER JOIN class ON student.class_id class.class_id;两种方式效果一样但第二种可读性更好特别是当你需要连接多个表的时候。PSinner可以省略那么可以把第三点中的SQL语句写成下方的形式五、内连接练习从简单到复杂练习1查询唐三藏的成绩需要联合student表和score表。思路一样找连接条件student.student_id score.student_id然后加上条件student.name 唐三藏。SELECT student.name, score.score FROM student JOIN score ON student.student_id score.student_id WHERE student.name 唐三藏;练习2查询所有同学的总成绩这次需要用到聚合函数SUM()了。先连接两个表然后按学生分组求和SELECT student.name, SUM(score.score) AS total FROM student JOIN score ON student.student_id score.student_id GROUP BY student.name;看数据就出来了再次复习一下思考流程明确表学生表, 分数表笛卡尔积~~指定连接条件指定其他条件 先跳过~~ 此问题不涉及精简列,加入聚合操作~~练习3查询所有同学每门课的成绩三表连接思考流程查询所有同学每门课的成绩及同学的个人信息列出同学信息, 课程信息, 分数从三个表进行联合查询学生表, 课程表, 分数表笛卡尔积~指定连接条件指定进一步的筛选条件~~ 不涉及针对列进行调整这次要同时显示学生姓名、课程名称、分数需要连接三个表student、course、score。笛卡尔积阶段SELECT * FROM student, course, score;然后指定连接条件student.student_id score.student_idcourse.course_id score.course_id最后精简列并加上别名SELECT student.name AS 学生姓名, course.name AS 课程名, score.score FROM student, course, score WHERE student.student_id score.student_id AND course.course_id score.course_id;或者用显式JOIN语法更清晰。这里有个很重要的提醒多表连接尤其是笛卡尔积是一个低效操作​ 如果表很大千万要谨慎不要一次性联合太多的表。早期数据量小的时候可能感觉不到但随着数据量增加联合查询会越来越慢甚至系统卡顿。为了优化有时候甚至需要在分数表中冗余存储课程名字段减少连接操作。最后附上思路流程的代码# 查询每个同学每个课程的成绩列出同学信息和课程信息。 select * from student, course, score; select * from student, course, score where student.student_id score.student_id and course.course_id score.course_id; select student.name as 学生姓名, course.name as 课程名, score.score from student, course, score where student.student_id score.student_id and course.course_id score.course_id; -- 也可以加别名 select student.name as 学生姓名, course.name as 课程名, score.score from student join score on student.student_id score.student_id join course on course.course_id score.course_id;六、外连接处理孤儿数据-- 六、外连接处理“孤儿”数据 -- 1. 准备测试环境 create database java117_2; use java117_2; -- 创建学生表和成绩表 create table student(student_id int, name varchar(20)); create table score(student_id int, score int); -- 插入初始数据张三、李四、王五及其成绩 insert into student values (1, 张三), (2, 李四), (3, 王五); insert into score values (1, 100), (2, 99), (3, 98); -- 验证初始数据此时数据一一对应 select * from student; select * from score; -- 2. 模拟“孤儿”数据故意修改王五的成绩让其指向不存在的学生ID 4 -- 此时 score 表中 98分 这条记录就变成了“孤儿”数据student_id4 在 student 表中找不到 update score set student_id 4 where score 98; -- 3. 验证修改后的数据状态 select * from student; select * from score; -- 4. 查询对比内连接 vs 左外连接 vs 右外连接 -- 内连接只查两个表都能对应上的数据 -- 结果只会查出张三、李四王五的成绩因为ID对不上被过滤掉了 select student.name, score.score from student inner join score on student.student_id score.student_id; -- 左外连接以左表student为主保留左表所有数据 -- 结果会查出张三、李四以及王五但王五的成绩显示为 NULL因为他变成了孤儿 select student.name, score.score from student left join score on student.student_id score.student_id; -- 右外连接以右表score为主保留右表所有数据 -- 结果会查出张三、李四以及那条 ID4 的孤儿成绩学生姓名显示为 NULL select student.name, score.score from student right join score on student.student_id score.student_id;没有第2点的内连接和左/右外连接是完全一样的如下图内连接只能查出两个表中都能对应上的数据。但如果有些学生还没有成绩呢或者有些成绩没有对应的学生呢这时候就需要外连接OUTER JOIN了。我创建了一个测试环境student表有张三、李四、王五score表有张三(100分)、李四(99分)、王五(98分)。一开始数据一一对应内连接、左外连接、右外连接结果都一样。然后我故意把成绩表改了一下UPDATE score SET student_id 4 WHERE score 98;让王五的成绩指向不存在的4号学生。这时候再查内连接只返回有对应成绩的张三和李四王五没了因为成绩表里的王五现在对应不到学生左外连接以左表student为主王五依然会出现成绩部分显示为NULL右外连接以右表score为主4号学生的成绩也会出现学生部分显示为NULL看图里的韦恩图就明白了内连接只取交集中间重叠部分左外连接取左边全部 交集右外连接取右边全部 交集全外连接取两边全部MySQL不支持Oracle支持特别提醒内连接可以用FROM 表1, 表2但左右外连接只能用JOIN语法所以掌握JOIN ... ON语法非常重要总结从笛卡尔积的灾难到连接条件的救赎再到内连接外连接的灵活运用多表查询真的是SQL里最常用也最容易踩坑的地方。记住几点先明确要从哪些表查笛卡尔积是所有多表查询的基础虽然我们要避免无效数据连接条件ON或WHERE必不可少注意二义性大表慎连考虑冗余字段优化内连接取交集外连接保全集根据业务需求选择希望这篇笔记能帮你少走弯路别像我一样一开始傻乎乎地直接查笛卡尔积了有问题的评论区交流一起进步