MySQL基础-多表查询

一、多表查询的规范

对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名或表名进行限定。对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名或表名，并且操作列在多个表中存在时，就会抛异常。

正确的做法是涉及多表查询时，总是给表设定别名简化SQL语句，并在列名前使用表的别名限定，这样不仅语义清晰不会出错，而且SQL查询效率更高，不需要MySQL帮我们判断列名是属于哪个连接表中的字段。

需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错，这涉及到SQL语句的执行顺序。

二、多表查询的语法

1.内连接查询

2.外连接查询

3.合并查询

合并查询结果利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION操作符返回两个查询的结果集的并集，去除重复记录。UNION ALL操作符返回两个查询的结果集的并集，不去除重复记录。

执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

# 举例：查询部门编号>90或邮箱包含a的员工信息
# 方式一
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id > 90;
# 方式二
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id > 90;

# 举例：查询部门编号>90或部门编号<50的员工信息
# 方式一
SELECT * FROM employees WHERE department_id > 90 OR department_id < 50;
# 方式二
SELECT * FROM employees WHERE department_id > 90
UNION ALL
SELECT * FROM employees WHERE department_id < 50;

三、常见的连接场景

#中图：内连接 A∩B
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e 
JOIN departments d ON e.`department_id` = d.`department_id`;

#左上图：左外连接
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e 
LEFT JOIN departments d ON e.`department_id` = d.`department_id`;

#右上图：右外连接
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e 
RIGHT JOIN departments d ON e.`department_id` = d.`department_id`;

#左中图：A - A∩B
SELECT e.employee_id, e.last_name, d.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
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e 
RIGHT JOIN departments d ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

#左下图：A∪B
SELECT e.employee_id, e.last_name, d.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 e.employee_id, e.last_name, d.department_name
FROM employees e 
RIGHT JOIN departments d ON e.`department_id` = d.`department_id`;

#右下图：A∪B - A∩B
SELECT e.employee_id, e.last_name, d.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 e.employee_id, e.last_name, d.department_name
FROM employees e 
RIGHT JOIN departments d ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

四、SQL99的新特性

1.自然连接

SQL99在SQL92的基础上提供了一些特殊语法，比如NATURAL JOIN用来表示自然连接。我们可以把自然连接理解为SQL92中的等值连接。它会帮你自动查询两张连接表中所有相同的字段 ，然后进行等值连接 。

#在SQL92标准中：
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e 
JOIN departments d ON e.`department_id` = d.`department_id` AND e.`manager_id` = d.`manager_id`;

#在SQL99标准中：
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e 
NATURAL JOIN departments d;

2.USING连接

当我们进行连接的时候，SQL99还支持使用USING指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e 
JOIN departments d USING (department_id);

#等价于下面的写法：

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

五、课后练习

1.显示所有员工的姓名，部门号和部门名称

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.`department_id` = d.`department_id`;

2.查询90号部门员工的job_id和90号部门的location_id

SELECT e.job_id, d.location_id
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id` AND d.`department_id` = 90;

#或者

SELECT e.job_id, d.location_id
FROM employees e
JOIN departments d ON e.`department_id` = d.`department_id`
WHERE e.`department_id` = 90;

3.选择所有有奖金的员工的last_name,department_name,location_id,city

SELECT e.last_name, d.department_name, d.location_id, l.city
FROM employees e
LEFT OUTER JOIN departments d ON e.`department_id` = d.`department_id`
LEFT OUTER JOIN locations l ON d.`location_id` = l.`location_id`
WHERE commission_pct IS NOT NULL;

4.选择city在Toronto工作的员工的last_name,job_id,department_id,department_name

SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id` AND d.`location_id` = l.`location_id` 
AND l.city = 'Toronto';

#或者

SELECT e.last_name, e.job_id , e.department_id , d.department_name
FROM employees e
JOIN departments d ON e.`department_id` = d.`department_id`
JOIN locations l ON l.`location_id` = d.`location_id`
WHERE l.`city` = 'Toronto';

5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

SELECT d.department_name, l.street_address, e.last_name, e.job_id, e.salary
FROM employees e 
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.`location_id` = l.`location_id`
WHERE d.department_name = 'Executive';

6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees   Emp#   manager   Mgr#
kochhar     101     king     100

#答案：
SELECT emp.last_name AS "employees", emp.employee_id AS "Emp#", mgr.last_name AS "manager",
mgr.employee_id AS "Mgr#"
FROM employees emp
LEFT OUTER JOIN employees mgr ON emp.manager_id = mgr.employee_id;

7.查询哪些部门没有员工

#方式1：
SELECT d.department_id
FROM departments d 
LEFT JOIN employees e ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

#方式2：
SELECT d.department_id
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE e.`department_id` = d.`department_id`
);

8.查询哪个城市没有部门

#方式1：
SELECT l.location_id, l.city
FROM locations l 
LEFT JOIN departments d ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL

#方式2：
SELECT l.location_id, l.city
FROM locations l
WHERE NOT EXISTS (
SELECT *
 FROM departments d 
 WHERE d.`location_id` = l.`location_id`
);