MySQL（六）——多表查询

多表查询

多表查询，也称为关联查询，是指两个或更多个表一起完成查询操作。这些表之间通过关联字段（可能是外键）建立关系，包括一对一、一对多或多对多等关系。多表查询从多个表中获取相关数据，以满足复杂的查询需求。

连接查询、子查询 和 联合查询都属于多表查询。

连接查询

连接查询包括内连接、外连接 和 自连接，以下图理解这三者的区别：

两个椭圆表示两张表，内连接 查询结果为 C 部分；外连接 查询结果是 A+C 或 B+C，分别代表左外连接和右外连接；自连接 并不直接对应于Venn图中的两个独立集合，因为自连接是在同一个表内部进行的。

先要了解如下SQL语句：

SELECT * FROM tbl_name1, tbl_name2 ...;

上述语句如果只有一张表，就是对这张表进行全列查询；对多张表来说（下面多以两张表为例），返回的结果集就是两表数据的笛卡尔积。

笛卡尔积：表示两个或多个集合中所有可能的有序对的集合，即多个集合的所有组合情况。

以两张表为例它们的笛卡尔积：

观察发现，取笛卡尔积的表中存在很多的 class_id 与 id(1) 不匹配的无效数据，我们在查询时需要过滤掉这些无效的笛卡尔积。

内连接

内连接是最常见的连接类型。它返回两个表中匹配连接条件的所有行。如果在一个表中存在匹配项，而在另一个表中不存在匹配项，则这些行不会出现在结果集中。内连接可以确保结果集中的每一行在两个表中都有匹配项。

【语法】

隐式内连接

SELECT 字段列表 FROM tbl_name1, tbl_name2... WHERE 过滤条件...;

显式内连接

SELECT 字段列表 FROM tbl_name1 [INNER] JOIN tbl_name2 ON 连接条件...;

两种语法的过滤条件和连接条件是一致的，都是用来过滤无效的笛卡尔积。确定过滤条件时，将所有相关表的字段放在一起，确定哪些字段的值匹配后才能为有效数据，进而确定过滤条件。

【示例】

对以下两表进行查询，要求查询出学生姓名以及所在班级：

# 隐式内连接写法
SELECT stu.name,cla.name 
FROM students stu,class cla
WHERE stu.class_id = cla.id;

# 显式内连接写法
SELECT stu.name,cla.name 
FROM students stu 
INNER JOIN class cla 
ON stu.class_id = cla.id;

查询结果如下：

注意：

• 当给表起了别名，就不能再用表名限制字段，必须使用别名；如果在已经给表名起了别名的情况下继续使用表名会报错！
• 在内连接查询中，两张表的位置是可以任意调换的，这不会影响查询的结果。内连接关注的是两个表之间基于连接条件的匹配行，而不关心这些表在查询中的物理顺序。

外连接

外连接 分为 左外连接和 右外连接。左(右)外连接查询除了返回两个表中匹配连接条件的所有行外，还会返回左(右)表的所有数据行，不论是否为NULL。（左外连接查询左表所有数据以及两表交集数据；右外连接查询右表所有数据以及两表交集数据）

继续以下图为例：

A 作为左表，B 作为右表，左连接查询的结果集为 A+C，右连接查询的结果集为 B+C。

【语法】

# 左外连接
SELECT 字段列表 FROM tbl_name1 LEFT [OUTER] JOIN tbl_name2 ON 连接条件...;

# 右外连接
SELECT 字段列表 FROM tbl_name2 RIGHT [OUTER] JOIN tbl_name2 ON 连接条件...;

• 左外连接 和 右外连接 可以相互转换，仅需要将关键字LEFT和RIGHT互换，然后将左表和右表互换。

【示例】

查询所有学生和所在班级，没有分配班级的学生也要查询：

• 内连接做不到以上需求，左外连接和右外连接都可以做到

自连接

自连接主要用于在同一张表内部进行数据的关联和查询，即自己和自己连接查询，自连接必须使用别名。

【语法】

SELECT 字段列表 FROM 表A 别名A {[INNER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN 表A 别名B ON 条件...;

• 自连接可以是内连接，也可以是外连接，自连接时将两张表（两张表是同一张）作为不同的表对待，就可以转化为内连接或外连接问题了。
• 自连接必须使用别名

【示例】

假设有一个员工表employee，包含员工ID（employee_id）、员工姓名（employee_name）和经理ID（manager_id）等字段。现在需要查询每个员工及其直接上级的姓名。由于经理也是员工，经理ID要和员工表的员工ID匹配，这时可以使用自连接来实现：

SELECT A.employee_name AS 员工姓名, B.employee_name AS 经理姓名  
FROM employee A, employee B  
WHERE A.manager_id = B.employee_id;

子查询

子查询，又称嵌套查询，是嵌套在另一个查询中的SQL查询。子查询可以出现在SELECT、INSERT、UPDATE或DELETE语句中，并且可以用于WHERE子句、HAVING子句、FROM子句或SELECT列表中。

• 根据子查询返回的结果的不同，可以分为：

• 另外，根据子查询位置不同，可以分为：WHERE之后、FROM之后、SELECT之后

涉及到子查询的查询往往比较复杂，如不同的子查询位置可以返回不同的结果，我们就以标量子查询、列子查询、行子查询和表子查询来介绍。

为了方便后续举例，我们直接给出一些表（外键关系已由箭头给出）：

标量子查询

标量子查询：返回单个值的子查询。它常用于WHERE子句中，作为一个比较值。

常用操作符：=、<>、>、>=、<、<=

【示例以及SQL】

# 演示标量子查询

-- 需求1：查询“计算机系2019级1班”的所有同学的个人信息
SELECT * FROM student WHERE class_id = (SELECT class_id FROM class WHERE name = '计算机系2019级1班');


-- 需求2：查询“许仙”的所有成绩
SELECT co.name,sc.score 
FROM score sc 
INNER JOIN course co 
ON sc.course_id = co.course_id 
WHERE student_id = (SELECT student_id FROM student WHERE name = '许仙');

需求1结果集：

需求2结果集：

列子查询

列子查询：返回一个列的值列表的子查询。

常用操作符：NOT IN、IN、ANY、SOME、ALL

【示例及SQL】

# 需求1：求“中文系2019级3班”所有同学的总成绩

-- a. 嵌套子查询实现需求
SELECT stu.name,SUM(sc.score) 总分 
FROM student stu 
INNER JOIN score sc 
ON stu.student_id = sc.student_id 
GROUP BY sc.student_id 
HAVING name IN (SELECT name FROM student WHERE class_id = (SELECT class_id FROM class WHERE name = '中文系2019级3班'));

-- b. 通过JOIN消除嵌套子查询
SELECT stu.name,SUM(sc.score) 总分 
FROM student stu 
INNER JOIN class cla ON stu.class_id = cla.class_id AND cla.name = '中文系2019级3班'
INNER JOIN score sc ON sc.student_id = stu.student_id
GROUP BY sc.student_id; 


# 进一步的需求2：查询“计算机系2019级1班”中总成绩高于“中文系2019级3班”所有同学的同学

-- 1. 先求“计算机系2019级1班”所有同学的总成绩
SELECT stu.name,SUM(sc.score) '1班总分'
FROM student stu
INNER JOIN class cla ON cla.class_id = stu.class_id AND cla.name = '计算机系2019级1班'
INNER JOIN score sc ON sc.student_id = stu.student_id
GROUP BY sc.student_id;

-- 2. 将需求1的SQL查询stu.name列删除，仅查询SUM(sc.score)，使得结果集仅剩总分一列
SELECT SUM(sc.score) 总分 
FROM student stu 
INNER JOIN class cla ON stu.class_id = cla.class_id AND cla.name = '中文系2019级3班'
INNER JOIN score sc ON sc.student_id = stu.student_id
GROUP BY sc.student_id;

-- 3. 将第二步作为第一步的子查询，并添加WHERE过滤掉非需求数据，完成需求
SELECT name 
FROM (
SELECT stu.name,SUM(sc.score) 1班总分
FROM student stu
INNER JOIN class cla ON cla.class_id = stu.class_id AND cla.name = '计算机系2019级1班'
INNER JOIN score sc ON sc.student_id = stu.student_id
GROUP BY sc.student_id) AS q1
WHERE q1.1班总分 > ALL (SELECT SUM(sc.score) 总分 
FROM student stu 
INNER JOIN class cla ON stu.class_id = cla.class_id AND cla.name = '中文系2019级3班'
INNER JOIN score sc ON sc.student_id = stu.student_id
GROUP BY sc.student_id);

对于需求1，两种解决方案的结果集均为：

• 解决方案a使用到了列子查询，并在其中嵌套了一层标量子查询

对于需求2，三步SQL的结果集分别如下图所示：

行子查询

行子查询：返回单个行而不仅仅是单个字段的值，可以同时返回多个列值。

常用操作符：=、<>、IN、NOT IN

【示例及SQL】

使用先前的表例并不能产生一个很好的行子查询的示例，创建一个简单的表演示：

# 需求：查询和“鑫哥”职位相同且薪资一样的员工

SELECT name FROM emp WHERE (role,salary) = (SELECT role,salary FROM emp WHERE name = '鑫哥') AND name <> '鑫哥';

表子查询

表子查询：返回的结果集是一个多行多列的表数据。

常用操作符：IN；常将表子查询作为一张临时表进行进一步的查询。

【示例及SQL】

# 需求：查询邮箱为空的同学的信息及其所在的班级信息

-- 1. 查询邮箱为空的同学的信息
SELECT sn,name,class_id FROM student WHERE mail <=> NULL;

-- 2. 查询邮箱为空的同学的信息及其所在的班级信息
SELECT tmp.name 姓名,tmp.sn 学号,cla.name 班级
FROM (SELECT sn,name,class_id FROM student WHERE mail <=> NULL) tmp
INNER JOIN class cla
ON cla.class_id = tmp.class_id;

联合查询

联合查询是一种将多次查询的的结果集合并为一个结果集的操作。常用于需要从不同的表中检索相似类型的数据，或者对同一表应用不同的查询条件并将结果合并时。

联合查询涉及到两个关键字UNION和UNION ALL

【语法】

SELECT 字段列表 FROM 表A ... 
UNION [ALL] 
SELECT 字段列表 FROM 表B ...;

• UNION ALL会将全部的数据直接合并起来，UNION会对合并之后的数据去重
• 联合查询的多张表的列数必须一致，类型也要匹配。当列的数量匹配但类型不匹配时，可以合并返回结果集，该结果集没有意义，这一点需要人工规避。

【示例】

现有两张表stu_table1和stu_table2，分别代表两个活动的参与人员

要求查询参与活动的所有男同学（有些同学参加了两个活动）：

# UNION ALL
SELECT name FROM stu_table1 WHERE gender = '男'
UNION ALL
SELECT name FROM stu_table2 WHERE gender = '男';

# UNION
SELECT name FROM stu_table1 WHERE gender = '男'
UNION 
SELECT name FROM stu_table2 WHERE gender = '男';

多表查询会因复杂业务变得十分复杂，建议多加练习！