SQL 基础之多表查询 mysql

一、前言 SQL 最强大的功能之一就是能在数据检索查询的执行中联结表。联结是利用 SQL 的 SELECT 执行的最重要的操作，很好地理解联结及其语法是学习 SQL 的一个极为重要的组成部分。
二、E-R 图与班级学生表 E-R 图如下：

文章图片

学生表如下，如果学生已经分配了班级，class_id 列则为分配班级的编号，否则为 NULL（意为未分配）：

文章图片

班级表如下：

文章图片

三、内联结

内联结将两个表中满足指定联结条件的记录联结成新的结果集，并舍弃所有不满足联结条件的记录。

（一）两个表之间的联结语法：

SELECT ... FROM tb_1 INNER JOIN tb_2 ON tb_1.f_1 = tb_2.f_1; // 1. tb_1 INNER JOIN tb_2 意为 tb_1 内联 tb_2，INNER 可省略：tb_1 JOIN tb_2。 // 2. ON 后面接的是联结条件。

例子：假如我要查看所有已经分配班级的学生信息，以及对应的班级信息。

SELECT student_id, student_no, student_name, class_no, class_name FROM students AS s JOIN classes AS c ON s.class_id = c.class_id;

结果如下：

文章图片

（二）多个表的联结 SQL 对一条 SELECT 语句中可以联结的表的数目没有限制。
语法：

SELECT ... FROM tb_1,tb_2,tb_3 WHERR tb_1.f_1 = tb_2.f_1 AND tb_1.f_1 = tb_3.f_1;

四、外联结 【SQL 基础之多表查询】（1）左联结
左表的行一定会列出，右表如果没有匹配的行，那么列值就为null 。
特别需要注意的是如果右表有多行和左表匹配，那么左表相同的行会出现多次。
语法：

SELECT ... FROM tb_1 LEFT JOIN tb_2 ON tb_1.f_1=tb_2.f_1// tb_1 LEFT JOIN tb_2，tb_1 左联结 tb_2，意味着查询结果集中必须包含 tb_1 的全部记录。 // 然后按指定的连接条件与 tb_2 进行连接。 // 若 tb_2 中没有满足连接条件的记录，则结果集中 tb_2 相应的字段填入 NULL。

例如：假如要检索出所有学生对应的班级信息。

SELECT student_id, student_no, student_name, class_no, class_name FROM students AS s LEFT JOIN classes AS c ON s.class_id = c.class_id;

结果如下：

文章图片

结果分析：
students LEFT JOIN classes：students 左联结 classes，会列出 students 表的所有学生。
由 students 表可知，赵八是没有分配班级的，即 class_id 为 NULL ，而 classes 表中没有满足连接条件的记录，故结果集中 classes 相应的字段填入 NULL。
（二）右联结
和左联结类似，只不过以右表为主表而已，左联结和右联结可以相互转化。
例子：假如要检索出所有班级对应的学生信息。

SELECT student_id, student_no, student_name, class_no, class_name FROM students AS s RIGHT JOIN classes AS c ON s.class_id = c.class_id;

结果如下：

文章图片

结果分析：
students AS s RIGHT JOIN classes：students 右联结 classes，会列出 classes 表的所有班级。
由 classes、students 表可知，自动化和日语班级的编号并没有出现在 students 表，故结果集中 students 相应的字段填入 NULL。
由于班级和学生的关系是一对多，所以左表有多行和右表匹配，故右表相同的行出现了多次。
五、自联结就是一个表自己和自己联结，一般用来替代子查询。
例如：假如要检索出张三丰所在班级的所有学生。

SELECT s1.student_id, s1.student_no, s1.student_name, s1.class_id FROM students AS s1 JOIN students AS s2 ON s1.class_id = s2.class_id AND s2.student_name = '张三丰';

结果如下：

文章图片

有的人可能会好奇，为什么是

AND s2.student_name = '张三丰';

，而非

AND s1.student_name = '张三丰';

？让我们来看一下，把 s2 换成 s1 会得到什么结果？

文章图片

在上述 SQL 语句中，只把 s2 换成 s1，却没有得到想要的结果。
既然把不同表的 name 字段当做过滤条件会造成结果的不一致，那么就先不加该条件，并且把所有字段都找出来，看看会得到什么样的结果。

SELECT * FROM students AS s1 JOIN students AS s2 ON s1.class_id = s2.class_id;

结果如下：

文章图片

在此基础上，分别测试加

s2.student_name = '张三丰';

、

s1.student_name = '张三丰';

条件看看会得到什么样的结果：

// 加 s1.student_name = '张三丰' 条件 SELECT * FROM students AS s1 JOIN students AS s2 ON s1.class_id = s2.class_id AND s1.student_name = '张三丰'; // 加 s2.student_name = '张三丰' 条件 SELECT * FROM students AS s1 JOIN students AS s2 ON s1.class_id = s2.class_id AND s1.student_name = '张三丰';

结果如下：

文章图片

有点感觉了，我们再来尝试把字段中的 s1 换成 s2，把 name 条件的表换成 s1：

SELECT s2.student_id, s2.student_no, s2.student_name, s2.class_id FROM students AS s1 JOIN students AS s2 ON s1.class_id = s2.class_id AND s1.student_name = '张三丰';

结果如下：

文章图片

得到了预期的结果。
总结：在使用自联结时，查询字段的表名与过滤字段的表名必须不同。例如，在本例中查询字段为 s1.student_id 时，过滤字段的表名就必须为 s2，即 s2.name='张三丰'。
如果想了解更多，前往参考 2 查看更多细节。
六、组合查询多数SQL 查询都只包含一个或多个表中返回数据的单条 SELECT 语句，MySQL也允许多条 SELECT 语句，并将结果作为单个查询结果集返回。
（一）使用情况需要使用组合查询的两种基本情况：

在单个查询中从不同的表返回类似结构的数据。
对单个表执行多个查询，按单个查询返回数据。

（二）基本语法语法，UNION 关键字：

SELECT ... FROM tb_1 UNION SELECT ... FROM tb_2;

例如，假如要检索出张三丰和李四的个人信息：

SELECT * FROM students WHERE student_name = '张三丰' UNION SELECT * FROM students WHERE student_name = '李四';

结果如下：

文章图片

注意：UNION 中的每个查询必须包含相同的列、表达式或聚合函数（不过各个列不需要按相同的次序列出）。
（三）包含或取消重复的行 UNION 会自动去除结果集中重复的行（默认去重）。
如果想返回所有匹配行，可以使用 UNION ALL。
（四）对组合查询进行排序在用 UNION 组合查询时，只能使用一条 ORDER BY 字句，它必须出现在最后一条 SELECT 语句之后。