MySQL基础篇多表查询总结

1. 多表查询概述

①多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
②前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

多表连接

下方三个表相互关联，所以可以放在一起进行查询，实际上对MySQL有了解的就会知道：
employees表中employee_id是主键，department_id是外键
departments表中department_id是主键，location_id是外键
locations表中location_id是主键，country_id是外键

#实现多表查询的正确方式：设置连接条件

SELECT employee_id, employees.department_id,department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

查询结果：

多表查询分类

2. 等值连接 vs 非等值连接

等值连接

连接条件是通过 = 来进行判断
如下图：
这一条数据就会在结果中显示

#练习：从查询员工的employee_id, last_name, department_name, city

SELECT employee_id, last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

查询结果

非等值连接

如下图：左表显示员工信息，右表显示不同的薪水值对应的不同范围

实现样例：

SELECT last_name, salary, grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

查询结果：

3. 自连接 VS 非自连接

自连接

自己连接自己成为自连接，因为出现自己表中的某一个字段需要和表中另一个字段进行匹配查询，所以会出现自连接

查询员工对应的管理者的编号，由于管理者也在员工表中，也有自己的员工号，所以这一个就是用自连接查询

SELECT emp.employee_id, emp.last_name, mgr.employee_id, mgr.last_name
FROM employees emp, employees mgr
WHERE emp.manager_id = mgr.employee_id;

查询结果

非自连接

除了自连接，其余的都是非自连接，上面的等值和非等值都属于非自连接

4. 内连接 VS 外连接

【重中之重】

内连接

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

使用(INNER JOIN)的实现

图画形式展现就是：

获取的是公共部分

语法格式

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

实现结果

外连接

外连接：合并具有相同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行还查询到左表(或者右表)不匹配的行

左外连接

合并内容

合并具有相同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行还查询到左表不匹配的行

图示

实现样例

SELECT e.employee_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d#OUTER可以省略
ON e.department_id = d.department_id;

结果图示

右外连接

合并内容

合并具有相同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行还查询到右表不匹配的行

图示：

实现样例

SELECT employee_id, department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;#122条记录

运行结果

左外连接中去掉重复部分

实现这样的效果是为在实现满外连接的时候，可以借助于此方法，再结合右外连接达到满外连接的效果

图示：

实现方法：

在完整使用左外连接的基础上加一个判断条件把公共部分去掉（舍弃右边共有的）

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;

结果图示

右外连接中去掉重复部分

实现这样的效果是为在实现满外连接的时候，可以借助于此方法，再结合左外连接达到满外连接的效果

图示：

实现方法：

在完整使用右外连接的基础上加一个判断条件把公共部分去掉（舍弃左边共有的）

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

结果图示

满外连接

合并具有相同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行还查询到左表和右表不匹配的行

在这里需要使用到
ＵＮＩＯＮ关键字或者ＵＮＩＯＮ　ＡＬＬ关键字

ＵＮＩＯＮ：UNION 操作符返回两个查询的结果集的并集，去除重复记录。
UNION ALL：操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

图示

实现样例

实现方式一： 左外连接 加上 右连接中去除相同

SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
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;

实现方式二：右外连接 加上 左外连接中去除相同

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
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;

运行结果

左图独有记录 1 + 相同 106 + 右图独有记录 16 = 123