SQL多表查询 – 大雄的家

单表查询内容参考：https://www.yuque.com/u39220520/rtqkia/rbmtd38g1hg1logf

链表查询

一、准备工作

建表

CREATE TABLE department (
    id INT,
    name VARCHAR(20)
);

CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    sex ENUM('male', 'female') NOT NULL DEFAULT 'male',
    age INT,
    dep_id INT
);

插入数据

INSERT INTO department VALUES
(200, '技术'),
(201, '人力资源'),
(202, '销售'),
(203, '运营');

INSERT INTO employee (name, sex, age, dep_id) VALUES
('egon', 'male', 18, 200),
('alex', 'female', 48, 201),
('wupeiqi', 'male', 38, 201),
('yuanhao', 'female', 28, 202),
('liwenzhou', 'male', 18, 200),
('jingliyang', 'female', 18, 204);

查看表结构和数据

DESC department;

结果：

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

DESC employee;

结果：

+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int(11)               | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)           | YES  |     | NULL    |                |
| sex    | enum('male','female') | NO   |     | male    |                |
| age    | int(11)               | YES  |     | NULL    |                |
| dep_id | int(11)               | YES  |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+

SELECT * FROM department;

结果：

+------+--------------+
| id   | name         |
+------+--------------+
| 200  | 技术         |
| 201  | 人力资源     |
| 202  | 销售         |
| 203  | 运营         |
+------+--------------+

SELECT * FROM employee;

结果：

+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+

二、多表连接查询

多表连接查询用于从多个表中获取相关数据。常见的多表连接包括交叉连接、内连接、左连接、右连接和全外连接。

1. 交叉连接

交叉连接不使用任何匹配条件，生成笛卡尔积：

SELECT * FROM employee, department;

结果：

+----+------------+--------+------+--------+------+--------------+
| id | name       | sex    | age  | dep_id | id   | name         |
+----+------------+--------+------+--------+------+--------------+
|  1 | egon       | male   |   18 |    200 |  200 | 技术         |
|  1 | egon       | male   |   18 |    200 |  201 | 人力资源     |
|  1 | egon       | male   |   18 |    200 |  202 | 销售         |
|  1 | egon       | male   |   18 |    200 |  203 | 运营         |
|  2 | alex       | female |   48 |    201 |  200 | 技术         |
|  2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|  2 | alex       | female |   48 |    201 |  202 | 销售         |
|  2 | alex       | female |   48 |    201 |  203 | 运营         |
|  3 | wupeiqi    | male   |   38 |    201 |  200 | 技术         |
|  3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|  3 | wupeiqi    | male   |   38 |    201 |  202 | 销售         |
|  3 | wupeiqi    | male   |   38 |    201 |  203 | 运营         |
|  4 | yuanhao    | female |   28 |    202 |  200 | 技术         |
|  4 | yuanhao    | female |   28 |    202 |  201 | 人力资源     |
|  4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|  4 | yuanhao    | female |   28 |    202 |  203 | 运营         |
|  5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|  5 | liwenzhou  | male   |   18 |    200 |  201 | 人力资源     |
|  5 | liwenzhou  | male   |   18 |    200 |  202 | 销售         |
|  5 | liwenzhou  | male   |   18 |    200 |  203 | 运营         |
|  6 | jingliyang | female |   18 |    204 |  200 | 技术         |
|  6 | jingliyang | female |   18 |    204 |  201 | 人力资源     |
|  6 | jingliyang | female |   18 |    204 |  202 | 销售         |
|  6 | jingliyang | female |   18 |    204 |  203 | 运营         |
+----+------------+--------+------+--------+------+--------------+

2. 内连接

内连接只连接匹配的行：

SELECT employee.id, employee.name, employee.age, employee.sex, department.name 
FROM employee 
INNER JOIN department ON employee.dep_id = department.id;

结果：

+----+-----------+------+--------+--------------+
| id | name      | age  | sex    | name         |
+----+-----------+------+--------+--------------+
|  1 | egon      |   18 | male   | 技术         |
|  2 | alex      |   48 | female | 人力资源     |
|  3 | wupeiqi   |   38 | male   | 人力资源     |
|  4 | yuanhao   |   28 | female | 销售         |
|  5 | liwenzhou |   18 | male   | 技术         |
+----+-----------+------+--------+--------------+

上述 SQL 等同于：

SELECT employee.id, employee.name, employee.age, employee.sex, department.name 
FROM employee, department 
WHERE employee.dep_id = department.id;

3. 左连接（左外连接）

左连接优先显示左表全部记录：

SELECT employee.id, employee.name, department.name AS depart_name 
FROM employee 
LEFT JOIN department ON employee.dep_id = department.id;

结果：

+----+------------+--------------+
| id | name       | depart_name  |
+----+------------+--------------+
|  1 | egon       | 技术         |
|  5 | liwenzhou  | 技术         |
|  2 | alex       | 人力资源     |
|  3 | wupeiqi    | 人力资源     |
|  4 | yuanhao    | 销售         |
|  6 | jingliyang | NULL         |
+----+------------+--------------+

4. 右连接（右外连接）

右连接优先显示右表全部记录：

SELECT employee.id, employee.name, department.name AS

 depart_name 
FROM employee 
RIGHT JOIN department ON employee.dep_id = department.id;

结果：

+------+-----------+--------------+
| id   | name      | depart_name  |
+------+-----------+--------------+
|    1 | egon      | 技术         |
|    2 | alex      | 人力资源     |
|    3 | wupeiqi   | 人力资源     |
|    4 | yuanhao   | 销售         |
|    5 | liwenzhou | 技术         |
| NULL | NULL      | 运营         |
+------+-----------+--------------+

5. 全外连接

全外连接显示左右两个表全部记录：

SELECT * 
FROM employee LEFT JOIN department ON employee.dep_id = department.id
UNION
SELECT * 
FROM employee RIGHT JOIN department ON employee.dep_id = department.id;

结果：

+------+------------+--------+------+--------+------+--------------+
| id   | name       | sex    | age  | dep_id | id   | name         |
+------+------------+--------+------+--------+------+--------------+
|    1 | egon       | male   |   18 |    200 |  200 | 技术         |
|    5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|    2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|    6 | jingliyang | female |   18 |    204 | NULL | NULL         |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 运营         |
+------+------------+--------+------+--------+------+--------------+

注意：UNION 会去掉相同的记录。

三、符合条件连接查询

示例 1

以内连接的方式查询 employee 和 department 表，并且 employee 表中的 age 字段值必须大于 25，即找出年龄大于 25 岁的员工以及员工所在的部门：

SELECT employee.name, department.name 
FROM employee 
INNER JOIN department ON employee.dep_id = department.id 
WHERE age > 25;

示例 2

以内连接的方式查询 employee 和 department 表，并且以 age 字段的升序方式显示：

SELECT employee.id, employee.name, employee.age, department.name 
FROM employee, department 
WHERE employee.dep_id = department.id 
AND age > 25 
ORDER BY age ASC;

四、子查询

子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果，可以为外层查询语句提供查询条件。子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS 等关键字，还可以包含比较运算符：= 、 !=、> 、< 等。

1. 带 `IN` 关键字的子查询

查询平均年龄在 25 岁以上的部门名

SELECT id, name FROM department
WHERE id IN 
    (SELECT dep_id FROM employee GROUP BY dep_id HAVING AVG(age) > 25);

查看技术部员工姓名

SELECT name FROM employee
WHERE dep_id IN 
    (SELECT id FROM department WHERE name='技术');

查看不足 1 人的部门名（子查询得到的是有人的部门 id）

SELECT name FROM department 
WHERE id NOT IN (SELECT DISTINCT dep_id FROM employee);

注意：NOT IN 无法处理 NULL 的值

示例：

SELECT * FROM employee WHERE dep_id NOT IN (SELECT DISTINCT dep_id FROM employee);

如果子查询中存在 NULL，需要加上 IS NOT NULL 条件：

SELECT * FROM department 
WHERE id NOT IN (SELECT DISTINCT dep_id FROM employee WHERE dep_id IS NOT NULL);

2. 带 `ANY` 关键字的子查询

在 SQL 中 ANY 和 SOME 是同义词。

示例

SELECT * FROM employee 
WHERE salary = ANY (
    SELECT MAX(salary) FROM employee GROUP BY dep_id
);

SELECT * FROM employee 
WHERE salary IN (
    SELECT MAX(salary) FROM employee GROUP BY dep_id
);

注意：ANY 必须和其他比较运算符共同使用

SELECT * FROM T_Book 
WHERE FYearPublished < ANY (2001, 2003, 2005);  -- 错误示例

3. 带 `ALL` 关键字的子查询

ALL 表示所有，ANY 表示任一。

查询出那些薪资比所有部门的平均薪资都高的员工

SELECT * FROM employee 
WHERE salary > ALL (
    SELECT AVG(salary) FROM employee GROUP BY dep_id
);

查询出那些薪资比所有部门的平均薪资都低的员工

SELECT * FROM employee 
WHERE salary < ALL (
    SELECT AVG(salary) FROM employee GROUP BY dep_id
);

查询出那些薪资比任意一个部门的平均薪资低的员工

SELECT * FROM employee 
WHERE salary < ANY (
    SELECT AVG(salary) FROM employee GROUP BY dep_id
);

查询出那些薪资比任意一个部门的平均薪资高的员工

SELECT * FROM employee 
WHERE salary > ANY (
    SELECT AVG(salary) FROM employee GROUP BY dep_id
);

4. 带比较运算符的子查询

查询大于所有人平均年龄的员工名与年龄

SELECT name, age FROM employee 
WHERE age > (SELECT AVG(age) FROM employee);

查询大于部门内平均年龄的员工名、年龄

SELECT t1.name, t1.age FROM employee t1
INNER JOIN (
    SELECT dep_id, AVG(age) AS avg_age FROM employee GROUP BY dep_id
) t2 ON t1.dep_id = t2.dep_id
WHERE t1.age > t2.avg_age;

查询练习：查询每个部门最新入职的那位员工

表结构和数据准备

假设有如下的 company.employee 表：

列名	字段名	类型
员工id	id	int
姓名	emp_name	varchar
性别	sex	enum
年龄	age	int
入职日期	hire_date	date
岗位	post	varchar
职位描述	post_comment	varchar
薪水	salary	double
办公室	office	int
部门编号	depart_id	int

创建表

CREATE TABLE employee (
    id INT NOT NULL UNIQUE AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    sex ENUM('male', 'female') NOT NULL DEFAULT 'male',
    age INT(3) UNSIGNED NOT NULL DEFAULT 28,
    hire_date DATE NOT NULL,
    post VARCHAR(50),
    post_comment VARCHAR(100),
    salary DOUBLE(15,2),
    office INT,
    depart_id INT
);

插入记录

INSERT INTO employee (name, sex, age, hire_date, post, salary, office, depart_id) VALUES
('egon', 'male', 18, '2017-03-01', '老男孩驻沙河办事处外交大使', 7300.33, 401, 1), -- 教学部
('alex', 'male', 78, '2015-03-02', 'teacher', 1000000.31, 401, 1),
('wupeiqi', 'male', 81, '2013-03-05', 'teacher', 8300, 401, 1),
('yuanhao', 'male', 73, '2014-07-01', 'teacher', 3500, 401, 1),
('liwenzhou', 'male', 28, '2012-11-01', 'teacher', 2100, 401, 1),
('jingliyang', 'female', 18, '2011-02-11', 'teacher', 9000, 401, 1),
('jinxin', 'male', 18, '1900-03-01', 'teacher', 30000, 401, 1),
('成龙', 'male', 48, '2010-11-11', 'teacher', 10000, 401, 1),

('歪歪', 'female', 48, '2015-03-11', 'sale', 3000.13, 402, 2), -- 销售部门
('丫丫', 'female', 38, '2010-11-01', 'sale', 2000.35, 402, 2),
('丁丁', 'female', 18, '2011-03-12', 'sale', 1000.37, 402, 2),
('星星', 'female', 18, '2016-05-13', 'sale', 3000.29, 402, 2),
('格格', 'female', 28, '2017-01-27', 'sale', 4000.33, 402, 2),

('张野', 'male', 28, '2016-03-11', 'operation', 10000.13, 403, 3), -- 运营部门
('程咬金', 'male', 18, '1997-03-12', 'operation', 20000, 403, 3),
('程咬银', 'female', 18, '2013-03-11', 'operation', 19000, 403, 3),
('程咬铜', 'male', 18, '2015-04-11', 'operation', 18000, 403, 3),
('程咬铁', 'female', 18, '2014-05-12', 'operation', 17000, 403, 3);

查看表结构

DESC employee;

结果：

+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

查询每个部门最新入职的那位员工

方法一：使用 `INNER JOIN`

SELECT t1.*
FROM employee AS t1
INNER JOIN (
    SELECT post, MAX(hire_date) AS max_date
    FROM employee
    GROUP BY post
) AS t2 ON t1.post = t2.post
WHERE t1.hire_date = t2.max_date;

方法二：使用子查询和 `LIMIT`

SELECT t3.name, t3.post, t3.hire_date
FROM employee AS t3
WHERE id IN (
    SELECT (
        SELECT id FROM employee AS t2
        WHERE t2.post = t1.post
        ORDER BY hire_date DESC
        LIMIT 1
    ) FROM employee AS t1
    GROUP BY post
);