多表查询
多表查询,也称为关联查询,是指两个或更多个表一起完成查询操作。这些表之间通过关联字段(可能是外键)建立关系,包括一对一、一对多或多对多等关系。多表查询从多个表中获取相关数据,以满足复杂的查询需求。
连接查询、子查询 和 联合查询都属于多表查询。
连接查询
连接查询包括内连接、外连接 和 自连接,以下图理解这三者的区别:
两个椭圆表示两张表,内连接 查询结果为 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 = '男';
多表查询会因复杂业务变得十分复杂,建议多加练习!