#第06章_多表查询
#1.熟悉常见的几个表
DESC employees;
DESC departments;
DESC locations;
#查询员工名为‘Abel’的人在哪个城市工作？
SELECT *
FROM employees
WHERE last_name='Abel';
SELECT *
FROM departments
WHERE department_id=80;
SELECT *
FROM locations
WHERE location_id=2500;
#2.多表查询如何实现
#2.1出现笛卡尔积的错误
#错误的实现方式：每个员工都与每个部门匹配了一遍
#错误的原因：缺少了多表的连接条件
SELECT employee_id,department_name
FROM employees,departments;#2889条记录
#错误的实现方式：
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;#2889条记录
SELECT *
FROM employees;#107条记录
SELECT 2889/107
FROM DUAL;
SELECT *
FROM departments;#27条记录
#3.多表查询的正确方式：需要有连接条件
SELECT employee_id,department_name
FROM employees,departments;
#两个表的连接条件
where employees.`department_id`=departments.`department_id`;
#如果查询语句中出现了多张表中都存在的字段，则必须指明此字段所在的表
SELECT employees.employee_id,departments.department_name,employees.`department_id`
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
#建议：从sql优化的角度，建议多表查询时，每个字段都指明其所在的表
#5.可以给表起别名，在SELECT和WHERE中使用别名
SELECT emp.employee_id,dept.department_name,emp.`department_id`
FROM employees emp,departments dept
WHERE emp.`department_id`=dept.`department_id`;
#如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
#如下的操作是错误的：
SELECT emp.employee_id,dept.department_name,emp.`department_id`
FROM employees emp,departments dept
WHERE emp.`department_id`=departments.`department_id`;
#6.结论：如果有n个表实现多表查询，则至少需要n-1个连接条件
#练习：查询员工的employee_id,last_name,department_name,city
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`
#7.多表查询的分类
/*
角度一： 等值连接 vs 非等值连接
角度二： 自连接 vs 非自连接
角度三： 内连接 vs 外连接
*/
#7.1等值连接 vs 非等值连接
#非等值连接的例子：
seLECT *
FROM job_grades;
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`;
where e.`salary`>=j.`lowest_sal`and e.`salary`<=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 emp.`manager_id`=mgr.`employee_id`;
#内连接:合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`;#只有106条记录
#外连接：合并具有同一列的两个以上的表的行, 结果集中除了一个表与另一个表不匹配的行之外
# 还查询到了左表或者右表中不匹配的行
#外连接的分类：左外连接、右外连接、满外连接
#左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行 ，这种连接称为左 外连接。
#右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行 ，这种连接称为右 外连接。
#练习：查询所有员工的last_name,department_name信息
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`;#需要使用左外连接,MySQL不持支SQL92外连接的写法！
#不支持！
#SQL92语法实现内连接：见上
#SQL92语法实现外连接：使用 +
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id(+);
#SQL99语法中使用JOIN...ON的方式实现多表查询。这种方式也能解决外连接的问题，MySQL是支持这种方式的。
#SQL99语法如何实现多表的查询。
#SQL99语法实现内连接：
SELECT last_name,department_name
FROM employees e inner JOIN departments d
ON e.`department_id`=d.`department_id`;
SELECT last_name,department_name,city
FROM employees e 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 JOIN departments d
ON e.`department_id`=d.`department_id`;
#满外连接：mysql不支持FULL OUTER JOIN
SELECT last_name,department_name
FROM employees e full OUTER JOIN departments d
ON e.`department_id`=d.`department_id`;
#8.UNION和UNION ALL的使用
#UNION：会执行去重的操作
#UNION ALL：不会执行去重的操作
#结论：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，
#或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
#9. 7种JOIN的实现
#中图：内连接
select employee_id,department_name
from employees e 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`;
#右上图：右外连接
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;
#左下图：满外连接
#方式1：左上图 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;
#方式2：左中图 UNION ALL 右上图
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`;
#右下图：左中图 UNION ALL 右中图
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
#10.SQL99语法新特性1：自然连接
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`;
#NATURAL JOIN:它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。
SELECT employee_id,last_name,department_name
FROM employees e natural JOIN departments d;
#11.SQL99语法新特性2：USING
SELECT employee_id,last_name,department_name
FROM employees e join departments d
on e.department_id = d.department_id;
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
using(department_id);
#拓展
SELECT last_name,job_title,department_name
FROM employees INNER JOIN departments INNER JOIN jobs
ON employees.department_id = departments.department_id
AND employees.job_id = jobs.job_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 job_id,location_id
from employees e,departments d
where e.`department_id`=d.`department_id`
and e.`department_id`=90;
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
select e.last_name , e.`commission_pct`,d.department_name , d.location_id , l.city
from employees e left join departments d
on e.`department_id`=d.`department_id`
left join locations l
on l.`location_id`=d.`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_id`
join locations l
on d.`location_id`=l.`location_id`
where l.`city`='Toronto';
#sql92语法：
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';
# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
select d.department_name,l.street_address,e.last_name,e.job_id,e.salary
from departments d left join employees e
on e.`department_id`=d.`department_id`
left 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 "employees",emp.employee_id "Emp#",mgr.last_name "manager",mgr.employee_id "Mgr#"
from employees emp left join employees mgr
on emp.manager_id=mgr.employee_id;
# 7.查询哪些部门没有员工
select d.department_id
from departments d left join employees e
on d.`department_id`=e.`department_id`
where e.`department_id` is null;
#本题也可以使用子查询
# 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 e.employee_id,e.last_name,e.department_id
from employees e join departments d
on e.`department_id`=d.`department_id`
where d.`department_name`IN('Sales','IT')
|
