夜猫子的知识栈
夜猫子
2024-01-01
目录

多表查询

# 多表查询

# 多表查询简介

表与表之间的关系有3种：①一对一；②一对多；③多对多。其中一对多和多对一实际上是一样的，只是角度不同而已。

在MySQL中，多表连接的方式主要有以下4种：

  1. 联合查询
  2. 内连接
  3. 外连接
  4. 笛卡尔积连接。

# 集合运算

表的集合运算，跟数学中的集合运算是非常相似的。对于表的集合运算，主要包括以下3种:

  1. 并集（union）
  2. 交集（intersect）
  3. 差集（except）

提示

特别说明：对于MySQL来说，它只提供了实现并集的方式，并未提供可以直接获取交集和差集的方式。对于表的交集和差集，我们需要使用“曲线救国”的方式才能实现，也就是使用子查询方式来实现。不过在实际开发中，对表求交集和差集用得不多，我们简单了解一下即可。

# 并集（union）

在MySQL中，我们可以使用union关键字来对两个表求并集。求两个表的并集，也就是对两个表进行加法运算。这种求并集的操作，也叫做“联合查询”。

联合查询可以分为以下几种类型：

  1. UNION: 合并结果集并去除重复的行。
  2. UNION ALL: 合并结果集但保留所有行，包括重复的行。
-- UNION
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

-- UNION ALL
SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;
1
2
3
4
5
6
7
8
9
10
11
12
13

注意

  1. 列数和数据类型:
    • 所有参与联合查询的 SELECT 语句必须返回相同数量的列。
    • 对应位置的列的数据类型必须兼容。
  2. 列名:
    • 结果集的列名默认取自第一个 SELECT 语句中的列名。
    • 可以使用别名来指定结果集的列名。
  3. 排序:
    • ORDER BY 子句只能放在最后一个 SELECT 语句之后。
  4. LIMIT:
    • 如果需要限制总结果集的行数，可以在最后一个 SELECT 语句后使用 LIMIT。

# 示例

假设我们有两个表 students_2020 和 students_2021，每个表包含学生的学号、姓名和专业。我们希望将这两个表的数据合并在一起，并去除重复的学生记录。

创建示例表:

-- 创建 students_2020 表
CREATE TABLE students_2020 (
    sno INT PRIMARY KEY,
    name VARCHAR(50),
    major VARCHAR(50)
);

-- 创建 students_2021 表
CREATE TABLE students_2021 (
    sno INT PRIMARY KEY,
    name VARCHAR(50),
    major VARCHAR(50)
);

-- 插入数据到 students_2020 表
INSERT INTO students_2020 (sno, name, major) VALUES
(1, 'Alice', 'Computer Science'),
(2, 'Bob', 'Mathematics');

-- 插入数据到 students_2021 表
INSERT INTO students_2021 (sno, name, major) VALUES
(2, 'Bob', 'Mathematics'),
(3, 'Charlie', 'Physics');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

合并数据:

-- 使用 UNION 合并数据
SELECT sno, name, major
FROM students_2020
UNION
SELECT sno, name, major
FROM students_2021;

-- 使用 UNION ALL 合并数据
SELECT sno, name, major
FROM students_2020
UNION ALL
SELECT sno, name, major
FROM students_2021;
1
2
3
4
5
6
7
8
9
10
11
12
13

# 交集（intersect）

其中一种方法是使用子查询来实现交集操作。

SELECT *
FROM students_2020
WHERE sno IN (SELECT sno FROM students_2021);
1
2
3

# 差集（except）

类似的，其中一种方法是使用子查询来实现交集操作。

SELECT *
FROM students_2020
WHERE sno NOT IN (SELECT sno FROM students_2021);
1
2
3

# 多表连接

将两个表的通过指定字段进行关联。

select 列名
from 表A
join 表B
on 表A.列名 = 表B.列名;
1
2
3
4

主要为内连接和外连接。

内连接类似于连接之后取交集，注意是连接之后再取交集，而不是直接取两个表的交集。

这是最常用的连接方式。它返回两个表中满足连接条件的所有行。只有当连接条件匹配时，才会出现在结果集中。

image-20250214144009090

对于外连接来说，并不一定是连接之后求并集。只有完全外连接才是连接之后取并集。如果是左外连接或右外连接，则是根据左表或右表来显示一个表的所有行和另一个表中匹配行的结果。

image-20250214144150998

注意

在实际开发中，我们应该清楚以下两点:

  1. 对于多表连接来说，最常用的是内连接，外连接用得比较少。
  2. 如果使用外连接，一般只会用到左外连接，个别情况会用到完全外连接。

# 内连接

对于表的集合运算来说，它本质上是以“行”为单位进行操作的。而对于表的连接运算（包括内连接和外连接），则是以“列”为单位进行操作的。

在MySQL中，我们可以使用inner join关键字来实现内连接。所谓的内连接，指的是多个表通过“共享列”来进行连接。

select 列名
from 表A
inner join 表B
on 表A.列名 = 表B.列名;
1
2
3
4

# 示例

通过inner join 实现交集查询：

SELECT s1.sno, s1.name, s1.major
FROM students_2020 s1
INNER JOIN students_2021 s2 ON s1.sno = s2.sno;
1
2
3

# 深入了解

# 单表查询

对于单表查询来说，列名前面的表名前缀是可以省略的，也就是“表名.列名”可以直接写成“列名”。

-- 简写方式
select sno, name, major
from students_2020;

-- 完整写法
select students_2020.sno, students_2020.name, students_2020.major
from students_2020;

-- 设置别名
SELECT s1.sno, s1.name, s1.major
FROM students_2020 s1
1
2
3
4
5
6
7
8
9
10
11

# using（列名）

下面两种方式是等价的，也就是on staff.sid=market.sid可以等价于using(sid)。

-- 方式1
select *
from staff
inner join market
on staff.sid = market.sid;
1
2
3
4
5
-- 方式2
select *
from staff
inner join market
using(sid);
1
2
3
4
5

# 连接多个表

在MySQL中，内连接（inner join）不仅可以连接两个表，还可以同时连接多个表（3个或3个以上）。如果想要连接多个表，我们只需要多次使用 inner join…on… 即可。

select 列名
from 表A
inner join 表B on 连接条件
inner join 表C on 连接条件
……
1
2
3
4
5

# 查询条件

对于内连接的查询条件，我们并不一定要使用“=”。除了等值连接，我们还可以使用非等值连接。所谓的非等值连接，指的是on子句使用的是除了等号（=）的其他比较运算符（如>、>=、<、<=、<>等）进行的连接。比如：

staff.sid <> market.sid
1

# 外连接

用于从两个或多个表中检索数据，即使某些表中没有匹配的记录。

在MySQL中，根据连接时要提取的是哪个表的全部记录，外连接可以分为以下3种类型:

  1. 左外连接：根据左表来提取结果。
  2. 右外连接：根据右表来提取结果。
  3. 完全外连接：同时对左表和右表提取结果。

# 左外连接

左外连接，指的是根据“左表”来获取结果。在MySQL中，我们可以使用left outer join来实现左外连接。

返回左表中的所有记录，以及右表中与左表匹配的记录。如果右表中没有匹配的记录，则结果集中右表的列将包含 NULL 值。

select 列名
from 表A
left outer join 表B
on 表A.列名 = 表B.列名;
1
2
3
4

# 右外连接

右外连接，指的是根据“右表”来获取结果。在MySQL中，我们可以使用right outer join来实现右外连接。

返回右表中的所有记录，以及左表中与右表匹配的记录。如果左表中没有匹配的记录，则结果集中左表的列将包含 NULL 值。

select 列名
from 表A
right outer join 表B
on 表A.列名 = 表B.列名;
1
2
3
4

# 完全外连接

像SQL Server等DBMS中，我们可以使用full outer join来实现完全外连接。所谓完全外连接，指的是连接之后同时保留左表和右表的所有记录，它相当于左外连接和右外连接的并集。

不过MySQL并没有提供full outer join这样的方式，如果想要在MySQL中实现完全外连接，我们可以使用一种“曲线救国”的方式：首先获取左外连接的结果，然后获取右外连接的结果，最后使用union求并集即可。

# 示例

假设我们有两个表 students_2020 和 students_2021，我们希望找到在这两个表中存在的所有学生记录，包括只在一个表中存在的记录。

SELECT s1.sno, s1.name AS name_2020, s1.major AS major_2020, s2.name AS name_2021, s2.major AS major_2021
FROM students_2020 s1
LEFT JOIN students_2021 s2 ON s1.sno = s2.sno

UNION 

SELECT s2.sno, s1.name AS name_2020, s1.major AS major_2020, s2.name AS name_2021, s2.major AS major_2021
FROM students_2020 s1
RIGHT JOIN students_2021 s2 ON s1.sno = s2.sno;
1
2
3
4
5
6
7
8
9

image-20250213165532537

注意

  1. 列名处理：由于 UNION 要求每个 SELECT 语句返回的列数和数据类型相同，因此需要为不同的列指定别名以避免冲突。
  2. 去重：UNION 默认会去除重复的行。如果需要保留所有行（包括重复的），可以使用 UNION ALL。

# 笛卡尔积连接

笛卡儿积连接，也叫做“交叉连接”，它指的是同时从多个表中查询数据，然后组合返回数据。笛卡儿积连接的特殊之处在于，如果它不使用where子句指定查询条件，那么它就返回多个表的全部记录。

在MySQL中，笛卡儿积连接有两种写法：一种是使用英文逗号（,）隔开，另一种是使用cross join关键字。下面两种写法是等价的。

select 列名
from 表名1, 表名2;
1
2
select 列名
from 表名1 cross join 表名2;
1
2

示例：

SELECT students.sno, students.name, students.major, courses.course_id, courses.course_name
FROM students, courses;
1
2

# 自连接

在MySQL中，还有一种很特殊的多表连接方式——自连接。在自连接时，连接的两张表是同一张表，然后我们一般需要通过起一个别名来进行区分。

select 列名
from 表名1 as 别名1, 表名2 as 别名2;
1
2
编辑 (opens new window)
上次更新: 2025/3/12 17:54:26
列的属性
视图

← 视图→

最近更新
01
IoC 解决了什么痛点问题？
03-10
02
如何调试 Nest 项目
03-10
03
Provider注入对象
03-10
更多文章>
Copyright © 2019-2025 Study | MIT License