MySql（9）多表查询

Mysql专栏入口

链接：https://pan.baidu.com/s/1zAhDUNv-yuJiWmaFLvTk4w
提取码：ttst
提取码：ttst
专栏中有mysql的导入导出，里面有教如何导入

从这章开始使用新的数据库记得切换

一、多表查询

为什么要多表查询？
练习：查询员工名为’Abel’的人在哪个城市工作

1.1 单查询

首先我需要在员工表中查询这个人的部门

SELECT * FROM employees WHERE last_name = 'Abel';

这时就查到了这个人的部门id

知道部门号后在从部门表中查询其地点id

SELECT * FROM departments WHERE department_id = 80;

知道地点id后再地点表中查询地点

SELECT * FROM locations WHERE location_id = 2500;

我们需要三个表employees、departments、locations才能找出我们需要的数据

1.2 多表查询

我们一步一步来首先查询一下员工姓名和所在的部门名称

这个写法 缺失了连接条件 所以是错误的 这错误有个名字，叫做笛卡儿积

SELECT last_name,department_name FROM employees,departments

更具上方sql添加入连接条件这样就正确了

SELECT
	last_name,
	department_name 
FROM
	employees,
	departments 
WHERE
	employees.department_id = departments.department_id

额外讲一下别名 取公共字段的时候需要将别名加上确定取哪个表里的值

SELECT
	last_name,
	department_name,
	d.department_id
FROM
	employees as e,
	departments as d
WHERE
	e.department_id = d.department_id

这时候我们就成功的把两个表连接了起来。那么我们来连接一下三张表如以下代码。

# 如果有n个表实现多表查询，则至少需要n-1个连接条件
SELECT
	e.employee_id,
	e.last_name,
	d.department_name,
	l.city 
FROM
	employees e,
	departments d,
	locations l
WHERE
	e.department_id = d.department_id AND d.location_id = l.location_id

最后不要忘了，我们最终的目的是查询员工名为’Abel’的人在哪个城市工作。只需要再where里添加一个新的条件就可以了

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

二、各类连接概念

2.1 等值连接 vs 非等值连接

等值连接我们上面那个案例就是，我就不在多举例，直接看一下非等值连接。
练习:获取某个人的职位

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

2.2 自连接 vs 非自连接

自连接就是自己连接自己。
练习：查询员工id，员工姓名，及其管理者的id和姓名

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

2.3 内连接 vs 外连接

我们之前使用的都是sql92规则 在这里我们使用sql99规则方便讲解

内连接：取出两个表都存在的记录。

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

# 修改为sql99规则后我们如何做到三表连接
SELECT
	employee_id,
	department_name 
FROM
	employees e
	JOIN departments d ON e.department_id = d.department_id
	JOIN locations l ON d.location_id = l.location_id

外连接：以某一个表（左/右）为主(左表有但右表没有，反之也成立。)，取出记录。外连接又分为常见的两种和不常见的四种.

常见 左连接/右连接

左连接(left join)

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

右连接(right join)

SELECT
	employee_id,
	department_name 
FROM
	employees e
	Right JOIN departments d ON e.department_id = d.department_id

不常用的四种: 左连接反/右连接反/满外连接/满外连接反

左连接反

其实就是写出左连接，然后将都有的部分去掉，只留下左边独有的

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
	e.department_id IS NULL

满外连接

满外连接就是，左边独有的我要，左右都有的我要，右边独有的我也要！
这时要借助俩关键字，UNION ALL 和 UNION

假设两个表，左边独有的记录数量 1 公共部分 106 右边独有的 16

那么UNION ALL（推荐使用）在连接的时候会这样计算, (左) 1+106 + (右) 106 + 16 不会进行去重操作所以会加快速度，减少运算流程。

使用UNION时计算是这样的，1 + 106 +16,会进行去重操作，降低了运算速度。

而满外连接可以使用 左连接表 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.所有有部门的人员信息

SELECT
	employee_id,
	last_name 
FROM
	employees e
	JOIN departments d ON e.department_id = d.department_id

2.列出所有用户，并显示其部门信息

SELECT
	e.employee_id,
	e.last_name,
	d.department_name
FROM
	employees e
	left JOIN departments d ON e.department_id = d.department_id

3.列出所有的部门

SELECT
	d.department_name,
	d.department_id
FROM
	employees e
	right JOIN departments d ON e.department_id = d.department_id

4.所有不入部门的人

SELECT
	d.department_name,
	d.department_id,
	e.last_name
FROM
	employees e
	left JOIN departments d ON e.department_id = d.department_id
WHERE
	d.department_id is NULL

5.所有没人入的部门

SELECT
	d.department_name,
	d.department_id,
	e.employee_id
FROM
	employees e
	right JOIN departments d ON e.department_id = d.department_id
	WHERE e.employee_id is NULL

6.列出所有人员和部门的对照关系

SELECT
	e.employee_id,
	e.last_name,
	d.department_name,
	d.department_id
FROM
	employees e
	left JOIN departments d ON e.department_id = d.department_id

UNION all

SELECT
	e.employee_id,
	e.last_name,
	d.department_name,
	d.department_id
FROM
	employees e
	right JOIN departments d ON e.department_id = d.department_id
	WHERE e.employee_id is NULL

7.列出所有没入部门的人员和没人入的部门

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

UNION ALL

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