MySQL教程 - 12 多表查询

前面查询数据的时候，都是查询一张表的数据，在前面也介绍了关系型数据库，多个表之间的数据是可能存在关联关系，已经讲解了如何在两张表之间建立外键约束关系。

下面讲解一下如何同时在多个表中查询数据。

12.1 多表关系

前面在介绍外键约束的时候，讲解了部门表和员工表，一个部门是有多个员工的，所以一个部门表的数据是对应多个员工的数据的。这里就涉及到多表之间的数据关系。

一般来说，多表之间的数据关系有以下三种情况：

• 一对多
• 一对一
• 多对多

1 一对多

再次拿部门表和员工表来举例，如下图：

一个部门有多个员工，所以部门表一条数据对应员工表多条数据，在子表（从表）存储父表（主表）的主键ID。

一对多是多表关系之中最常用的一种关系。例如学生和班级的关系、用户和订单的关系等等。

2 一对一

如果一个表中列很多，某些列很少访问、很占空间或者访问频率不一致，可以将一些字段分离到一个单独的表中。

例如用户的信息很多，我们可以将一些访问不频繁的字段分离到一个单独的表中，在这个表中存储用户的 ID，如下图：

上面的表结构关系和 一对多 是一样的，只是两个表之间的数据关系是一对一的。我们可以在从表存储主表的主键ID，同时设置唯一约束，防止在从表对应多条数据的错误。

一对一的关系中，这些数据是可以存储在一张表中的，通过分离存储，在查询数据的时候，可以降低表的查询宽度，避免了加载无关数据，只查询需要的字段，可以提高查询效率。

3 多对多

多对多也是一种经常用到的关系，例如学生与课程的关系中，一个学生可以选多个课程，一个课程也可以被多个人选。订单与商品的关系中，一个订单可以多个商品，一个商品可以被多个订单购买；在一个管理系统中，一个角色可以操作多个菜单，一个菜单也可以被多个角色拥有。这些都是多对多的关系。

下面以学生与课程的关系为例：

多对多的关系，需要一种中间表，用来存储两个主体的关系，也就是存储两个主体表的主键ID，当然，我们可以在中间表添加一些额外的信息，例如要存储学生是什么时间选的课程，就可以存储在中间表中。

12.2 多表查询

前面学习的查询都是单表查询，从一张表中查询数据。如果我们想查询员工和员工的部门信息，这就涉及到两张表了，所以就需要使用多表查询。

首先准备一下表和数据，执行下面的 SQL，会删除之前的表重新创建并添加数据，你可以直接执行一下：

-- 删除表
DROP TABLE IF EXISTS tb_employee;
-- 删除表
DROP TABLE IF EXISTS tb_department;

-- 创建部门表
CREATE TABLE tb_department (
    id INT AUTO_INCREMENT PRIMARY KEY,         -- 部门ID，自增主键
    name VARCHAR(64)                           -- 部门名称
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入部门表数据
INSERT INTO tb_department VALUES
(1, '市场部'), 
(2, '技术部'),
(3, '财务部'),
(4, '行政部'),
(5, '采购部');

-- 创建员工表
CREATE TABLE tb_employee (
    id INT AUTO_INCREMENT PRIMARY KEY,         -- 员工ID，自增主键
    dept_id INT,         											 -- 部门ID
    name VARCHAR(64),
    gender CHAR(3),
    salary DECIMAL(10, 2),
    entry_time DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入员工表数据
INSERT INTO tb_employee VALUES
(1, 1, '罗辑', '男', 12000.50, '2022-03-15'),
(2, 2, '叶文杰', '女', 15000.00, '2022-05-20'), 
(3, 2, '程心', '女', 28000.75, '2023-01-10'),
(4, 3, '史强', '男', 8000, '2022-08-22'),
(5, NULL, '庄颜', '女', 15000.00, '2022-08-22'), 
(6, 4, '云天明', '男', 18000.00, '2023-04-05'),
(7, 4, '维德', '男', 1600.80, '2023-03-02');

先查询上面两张表的数据，SQL 如下：

SELECT * FROM tb_employee, tb_department;

查询多张表，直接在后面使用 , 分割，查询多张表，看一下查询的结果：

查询的结果的确是把两张表的数据都查询出来了，上面准备数据的时候，员工有 7 个，部门有 5 个，而查询出的数据是7 * 5 = 35 条记录。也就是查询的结果会将两张表的每条记录进行一一匹配，形成一个 M*N 的 笛卡尔积 。笛卡尔积是指两个表中每一行都与另一个表中的每一行进行组合，生成一个结果集。

上面的数据对于我们而言是没什么意义的，我们要查询的是员工和其对应的部门信息，所以这里就需要进行条件限制，两张表之间存在关联的字段，就是员工表的 dept_id。

所以修改 SQL 添加限制条件，如下：

SELECT * FROM tb_employee, tb_department WHERE tb_employee.dept_id = tb_department.id;

-- 或者，有时候表名比较长，可以为表起个别名
SELECT * FROM tb_employee as e, tb_department as d WHERE e.dept_id = d.id;

在上面的 SQL 语句中限制了查询的数据中 e.dept_id = d.id 的数据，所以查询结果如下：

**需要注意：**员工的数据有 7 条，上面只查询出了 6 条，因为有一条员工的 dept_id 为 NULL，是查询不出来的，下面在介绍如何查出来。

多表查询有多种方式，主要有以下几种：

• 连接查询，包括：内连接、外连接、自连接
• 联合查询
• 子查询

下面分别介绍一下。

12.3 内连接查询

内连接查询的是两张表交集的部分。

在上面查询员工和其部门信息的时候，查询方式就是内连接查询，内连接查询有两种方式，语法如下：

-- 隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 查询条件;

-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件;   -- INNER关键字可以省略

所以上面查询员工和其部门信息的时候，用的就是隐式内连接。

下面使用内连接查询员工姓名和部门信息，SQL 如下：

-- 隐式内连接，查询所有字段
SELECT e.*, d.* FROM tb_employee as e, tb_department as d WHERE e.dept_id = d.id;

-- 显式内连接，查询所有字段
SELECT e.*, d.* FROM tb_employee as e INNER JOIN tb_department as d ON e.dept_id = d.id;

可以指定查询的字段：

-- 隐式内连接，查询指定的字段
SELECT e.name, d.name FROM tb_employee as e, tb_department as d WHERE e.dept_id = d.id;

-- 显式内连接，查询指定的字段
SELECT e.name, d.name FROM tb_employee as e INNER JOIN tb_department as d ON e.dept_id = d.id;

查询结果如下：

可以为查询出的字段设置别名：

# 为查询字段设置别名
SELECT e.name as employee_name, d.name as dept_name FROM tb_employee as e, tb_department as d WHERE e.dept_id = d.id;

可以查询指定员工及其部门信息：

-- 隐式内连接
SELECT e.name as employee_name, d.name as dept_name 
FROM tb_employee as e, tb_department as d 
WHERE e.dept_id = d.id AND e.name='云天明';

-- 显式内连接
SELECT e.name as employee_name, d.name as dept_name 
FROM tb_employee as e JOIN tb_department as d ON e.dept_id = d.id 
WHERE e.name='云天明';

查询结果如下：

注意内连接查询到的数据不包括部门ID为空的员工。

再举个栗子，添加查询条件，查询 技术部 所有的员工：

# 使用隐式内连接
SELECT e.*
FROM tb_employee as e, tb_department as d
WHERE e.dept_id = d.id AND d.name = '技术部';

# 或者使用显式内连接
SELECT e.*
FROM tb_employee as e JOIN tb_department as d ON e.dept_id = d.id
WHERE d.name = '技术部';

查询结果：

12.4 左外连接查询

外连接查询包括 左外连接 和 右外连接 查询。

左外连接查询的结果是：包含 左表所有数据 和 左右两个表交集 的数据。

左外连接，语法如下：

-- 左连接查询
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件;    -- OUTER可以省略

所以左外连接会包含 表1所有数据 以及 表1和表2交集 的数据。

举个栗子：

查询所有员工及其部门信息：

SELECT *
FROM tb_employee as e LEFT JOIN tb_department as d 
ON e.dept_id = d.id;

# 或者
SELECT e.*, d.*
FROM tb_employee as e LEFT JOIN tb_department as d 
ON e.dept_id = d.id;

执行结果如下：

会查询出所有的员工信息，包括 dept_id 为空的员工信息。

12.5 右外连接查询

右外连接查询的结果是：包含 右表所有数据 和 左右两个表交集 的数据。

右外连接，语法如下：

-- 右连接查询
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 连接条件;    -- OUTER可以省略

所以右外连接会包含 表2所有数据 以及 表1和表2交集 的数据。

举个栗子：

查询所有部门信息以及部门所有的员工信息：

SELECT *
FROM tb_employee as e RIGHT JOIN tb_department as d 
ON e.dept_id = d.id;

# 或者
SELECT d.*, e.*
FROM tb_employee as e RIGHT JOIN tb_department as d 
ON e.dept_id = d.id;

查询结果如下：

部门只有 5 条数据，但是却查询出 7 条数据，这里是查询出了所以部门及其员工的信息。右外查询会查询右边所有数据，所以采购部下面没有员工，也是查询出来了。

在实际的开发中，右外查询使用的不是很多，一般都使用左外查询来实现，可以将上面的右外查询改为左外查询来实现：

SELECT d.*, e.*
FROM tb_department as d LEFT JOIN tb_employee as e
ON e.dept_id = d.id;

12.6 自连接查询

自连接查询是一个表连接自己。

举个栗子：

一般部门会有父部门和子部门，一个系统中菜单会有父菜单和子菜单，一些有层级的数据都会涉及的父子关系，而这些数据都是存储在一张表中的，那么就涉及到一张表的外键就是这个表的主键。

以菜单举栗子，假设有下面的表：

id	name	parent_id
1	首页	NULL
2	用户管理	1
3	系统设置	1
4	添加用户	2
5	删除用户	2
6	权限管理	3

上面的表中，parent_id 就是父菜单的 id，也就是对应这个表的 id 的值，parent_id 值为NULL 表示顶级菜单。

使用 SQL 创建这张表，并添加数据：

CREATE TABLE tb_menu (
    id INT PRIMARY KEY,         											-- 菜单唯一标识
    name VARCHAR(50) NOT NULL,  											-- 菜单名称
    parent_id INT,                   									-- 父菜单的 id，NULL 表示顶级菜单
    FOREIGN KEY (parent_id) REFERENCES tb_menu(id)    -- 还可以添加外键约束，指向父菜单
);

INSERT INTO tb_menu (id, name, parent_id) VALUES
(1, '首页', NULL), 
(2, '用户管理', 1),
(3, '系统设置', 1),
(4, '添加用户', 2),
(5, '删除用户', 2),
(6, '权限管理', 3);

下面来演示一下自查询，查询子菜单及其对应的父菜单：

在进行子查询的时候，可以将一张表理解为两张表：

然后可以使用内连接或外连接进行查询，查询的时候给需要给表起别名，不然相当于两个表名是一样的了，SQL如下：

SELECT child.name AS child_menu_name, parent.name AS parent_menu_name
FROM tb_menu child
LEFT JOIN tb_menu parent
ON child.parent_id = parent.id;

连接查询，我在网上找了一张图，大家参考一下：

**连接查询是可以连接多张表的，上面只是连接了两张表，按照连接的条件进行连接即可。**在连接查询的时候，尽量避免一次连接过多的表，可以通过拆分查询或分步处理减少复杂性。

12.7 联合查询union

联合查询就是可以把多次查询的结果合并起来，形成一个新的查询结果集。

语法如下：

SELECT 字段列表 FROM 表A, ...
UNION [ALL]
SELECT 字段列表 FROM 表B, ...;    -- ALL可以省略，但是ALL效果不同，下面举例

举个栗子：

查询工资大于15000的员工，很简单，SQL如下：

# 查询工资大于15000的员工
SELECT * FROM tb_employee WHERE salary > 15000;

执行结果：

查询所有的女员工，也很简单，SQL如下：

# 查询所有的女员工
SELECT * FROM tb_employee WHERE gender = '女';

执行结果：

UNION 可以将上面两个结果合并：

SELECT * FROM tb_employee WHERE salary > 15000
UNION ALL
SELECT * FROM tb_employee WHERE gender = '女';

执行结果：

可以看到将两个查询语句的结果合并了，因为两个查询子句的结果包含重复的数据，所以最终结果出现了重复的数据。

要去掉重复的数据，将 ALL 去掉就可以了：

SELECT * FROM tb_employee WHERE salary > 15000
UNION
SELECT * FROM tb_employee WHERE gender = '女';

执行结果：

需要注意：联合查询每个 SELECT 子句的列数必须相同，对应列的数据类型应尽可能一致（不一致也能查询），结果集的列名来源于第一个 SELECT 查询。

SELECT name, gender FROM tb_employee WHERE salary > 15000
UNION ALL
SELECT id, salary FROM tb_employee WHERE gender = '女';

上面的两个查询子句，查询的列数是相同的，虽然查询的列不同，也是可以的。

查询结果：