MySQL 第六章-多表查询+课后练习

#1.多表的查询如何实现
#错误的实现方式，每个员工都与每个部门匹配了一遍

SELECT employee_id,department_name
FROM employees,departments; 

#查询了2889条记录
#3.多表查询的正确方式，需要连接条件

SELECT employee_id,department_name
FROM employees,departments
//两个表的连接
WHERE employees.department_id = departments.department_id;

#4.如果查询语句中出现多个表中都存在的字段，则必须指明此字段所在的表

SELECT employee.employee_id,departments.department_name,departments.department_id
FROM employees,departments
//两个表的连接
WHERE employees.department_id = departments.department_id;

#建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表
#5.可以个表其别名，SELECT和WHER中使用表的别名
#如果给表起了别名，一旦在SELECT和WHERE中使用，则必须使用别名，不能使用原名

SELECT emp.employee_id,dep.department_name,dep.department_id
FROM employees emp,departments dep
#两个表的连接
WHERE emp.department_id = dep.department_id;

#6.结论 ：如果有n个表实现多表的查询，则需要至少n-1个连接条件
#练习：查询员工的employee_id，last_name，department_name，city

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

#7。多表查询的分类
#角度1：等值连接 vs 非等值连接
#角度2：自连接 vs 非自连接
#角度3：内连接 vs 非内连接

#7.1 等值连接 vs 非等值连接
#非等值连接

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

#7.2 自连接 vs 非自连接
#练习 ：查询员工ID，员工姓名及其管理者的id和姓名

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

#7.3 内连接 vs 非内连接
#内连接 合并具有同一列的两个以上的表的行，结果集合中不包含一个表与另一个表不匹配的行 交集

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

#外连接 合并具有同一列的两个以上的表的行，结果集合中包含一个表与另一个表匹配的行之外
#还查询到了左表 或 右表中不匹配的行
#外连接的分类 ： 左外连接、右外连接、 满外连接
#左（右）外连接 ：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

#练习 : 查询查询所有的员工的last_name， department_name 信息

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

需要是左外链接

#SQL92实现外连接 使用 +
#MySQL 不支持 SQL92 +

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

#SQL99 语法中使用 JOIN…ON 的方式实现多表查询。这种方式也能解决外连接的问题#SQL99实现内连接

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

SELECT employee_id,last_name,city
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;

#使用SQL99语法实现外连接
#练习 : 查询查询所有的员工的last_name， department_name 信息 左外链接

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

#右外连接

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

#满连接 MySQL 不支持 FULL JOIN

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

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

#7种JOIN 的实现

#中图：内连接

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

#左上图 ：左外链接

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

#右上图 ：右外链接

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

#左中图 ：

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

#右中图 ：

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

#左下图 ：满外连接

#方式一 ： 左上图 UNION ALL 右中图

SELECT last_name,department_name
FROM employees e  LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT last_name,department_name
FROM employees e  RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL ;

#方式二 ： 右上图 UNION ALL 左中图

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

#10，SQL99自然连接 它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值 连接 。

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

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d ;

#11 SQL新特性 USING

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

#课后练习

#【题目】
#1.显示所有员工的姓名，部门号和部门名称。SELECT e.last_name,e.department_id,d.department_name
FROM employees e 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 JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id = 90;

#3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT last_name,d.department_name,d.location_id,city,e.commission_pct
FROM employees e JOIN departments d
ON e.department_id = d.department_idJOIN locations l
ON d.location_id = l.location_id
WHERE e.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 JOIN departments d
ON e.department_id = d.department_idJOIN locations l
ON d.location_id = l.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_idJOIN 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 employees, emp.employee_id "Emp#", mgr.last_name manager, mgr.employee_id "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 department_id
FROM departments d
WHERE NOT EXISTS (
                                      SELECT *FROM employees e
                                      WHERE e.`department_id` = d.`department_id` );
                                      
#8. 查询哪个城市没有部门
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

#9. 查询部门名为 Sales 或 IT 的员工信息SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND d.`department_name` IN ('Sales','IT');