Dawn's Blogs

分享技术 记录成长

0%

MySQL基础 (5) 多表查询

发表于 更新于 分类于

多表查询基本实现

多表查询需要连接条件（有n个表实现多表查询，至少需要n-1个连接条件）

SQL92语法

1
2
3
4
5
6
7
8
9
10
11
# 给表起别名时必须用别名，不能用原名
SELECT emp.employee_id, dept.department_name
FROM employees emp, departments dept
WHere emp.department_id = dept.department_id;
+-------------+------------------+
| employee_id | department_name  |
+-------------+------------------+
|         200 | Administration   |
|         201 | Marketing        |
|         202 | Marketing        |
|         114 | Purchasing       |

SQL99语法 JOIN ON

SQL99语法采用表1 JOIN 表2 ON 连接条件的方式实现多表连接

1
2
3
SELECT emp.employee_id, dept.department_name
FROM employees emp JOIN departments dept
ON emp.department_id = dept.department_id;

注意：

  • 多表查询需要有连接条件。若没有连接条件，可能会出现笛卡尔积错误（两个集合中的每一个成员，都与对方集合中的任意一个成员有关）
  • 多表查询时，建议每个字段前指明其所在的表。从SQL优化的角度来讲，这样避免了在各个表中找相应字段的时间。
  • 给表起别名时必须用别名，不能用原名

UNION 关键字

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果合并成单个结果。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。

  • UNION 返回两个查询的结果集的并集，去除重复记录
  • UNION ALL返回两个查询结果集的并集，对于两个结果集重复部分，不去重

多表查询的分类

角度1 - 等值连接 非等值连接

等值连接

连接条件是等号连接的

非等值连接

连接条件是非等号连接的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT last_name, salary, grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
+-------------+----------+-------------+
| last_name   | salary   | grade_level |
+-------------+----------+-------------+
| King        | 24000.00 | E           |
| Kochhar     | 17000.00 | E           |
| De Haan     | 17000.00 | E           |
| Hunold      |  9000.00 | C           |
| Ernst       |  6000.00 | C           |
| Austin      |  4800.00 | B           |
| Pataballa   |  4800.00 | B           |
| Lorentz     |  4200.00 | B           |
| Greenberg   | 12000.00 | D           |

角度2 - 自连接 非自连接

自连接

同一个表的自我连接，对应表关系中的自我引用关系

1
2
3
4
5
6
7
8
9
10
SELECT emp.employee_id "员工ID", emp.last_name, mgr.employee_id "管理者ID", mgr.last_name
FROM employees emp, employees mgr
WHERE emp.manager_id = mgr.employee_id;
+--------+-------------+----------+-----------+
| 员工ID | last_name   | 管理者ID | last_name |
+--------+-------------+----------+-----------+
|    101 | Kochhar     |      100 | King      |
|    102 | De Haan     |      100 | King      |
|    103 | Hunold      |      102 | De Haan   |
|    104 | Ernst       |      103 | Hunold    |

非自连接

不是一个表的自我连接即为非自连接

角度3 - 内连接 外连接

内连接

合并具有同一列的两个以上的表的行，结果集不包含一个表与另一个表不匹配的行

外连接

外连接的分类：

  • 左外连接：结果集中结果集中除了返回一个表与另一个表匹配的行，还返回左表不匹配的行
  • 右外连接：结果集中结果集中除了返回一个表与另一个表匹配的行，还返回右表不匹配的行
  • 满外连接：结果集中结果集中除了返回一个表与另一个表匹配的行，还返回左表或者右表不匹配的行

SQL92语法 —— MySQL不支持

1
2
3
4
5
# 查询所有的员工
SELECT e.employee_id, d.department_name
FROM employees e, departments d
# 因为有些员工没有部门，需要使用左外连接
WHere e.department_id = d.department_id(+);

SQL99语法实现外连接

  • 左外连接LEFT [OUTER] JOHN

  • 右外连接RIGHT [OUTER] JOHN

  • 满外连接

    • FULL [OUTER] JOHN（MySQL不支持该语法）
1
2
3
4
5
# 查询所有的员工
SELECT e.employee_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
# 因为有些员工没有部门，需要使用左外连接
ON e.department_id = d.department_id;

7 种 JOHN 的实现

若有左、右两个表，分别对应两个集合A和B

A ∩ B - 内连接

1
2
3
SELECT employee_id, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id

A ∪ (B非) - 左外连接

1
2
3
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id

B ∪ (A非) - 右外连接

1
2
3
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id

A - B

A - B = A∪(B非) - A∩B = 左外连接 - 内连接

1
2
3
4
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

B - A

B - A = B∪(A非)- A∩B = 右外连接 - 内连接

1
2
3
4
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

A ∪ B - 满外连接

A ∪ B = A∪(B非) + (B-A) = 左外连接 + (B-A) 

1
2
3
4
5
6
7
8
9
10
# A∪(B非) 左外连接
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
# B-A
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

等价于

A ∪ B = B∪(A非) + (A-B) = 右外连接 + (A-B) 

1
2
3
4
5
6
7
8
9
10
# B∪(A非) 右外连接
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
# A-B
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

(A-B) ∪ (B-A)

1
2
3
4
5
6
7
8
9
10
11
# A-B
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
# B-A
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

自然连接和USING（SQL99）- 不重要

自然连接

自然连接NATURAL JOIN，会自动查询两张表所有相同的字段，然后进行等值连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# SELECT emp.employee_id, dept.department_name
# FROM employees emp JOIN departments dept
# ON emp.department_id = dept.department_id;
# 等价于
SELECT emp.employee_id, dept.department_name
FROM employees emp NATURAL JOIN departments dept;
+-------------+-----------------+
| employee_id | department_name |
+-------------+-----------------+
|         202 | Marketing       |
|         115 | Purchasing      |
|         116 | Purchasing      |
|         117 | Purchasing      |
|         118 | Purchasing      |

USING

USING的括号种填入两个表同名的字段，可以进行两个表的等值连接

1
2
3
4
5
6
7
8
9
10
11
12
SELECT emp.employee_id, dept.department_name
FROM employees emp JOIN departments dept
USING (department_id);
+-------------+------------------+
| employee_id | department_name  |
+-------------+------------------+
|         200 | Administration   |
|         201 | Marketing        |
|         202 | Marketing        |
|         114 | Purchasing       |
|         115 | Purchasing       |
|         116 | Purchasing       |