MySQL_Z6多表查询

数据库>>>服务器>>>Web

DB(database)：存储数据的“容器”，有组织的一系列数据；

DBMS(Database Management System)：数据库是通过DBMS创造和操作的。MySQL,Oracle…

SQL(structure Query Language)：专门用来与数据库通信的语言。（官话 vs 方言）

————————————————

Z6J26_多表查询

如果有n个表实现多表查询，则需要至少n - 1 个连接条件

select e.emplyee_id, e.last_name, d.department_name, l.city
from employee e, departments d, loccation l
where e.'department_id' = d.'department_id' and d.'location_id' = l.'location_id';

Z6J27/28_等值连接 vs 非等值连接，自连接 vs 非自连接， 内连接 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';

#自连接例子

select *
from employees;

#查询员工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';

#内连接 vs 外连接

外连接：合并具有同一列的两个以上的表的行，结果除了包含一个表与另一个表匹配的行之外，还查询到了左表 或 右表中不匹配的行。（分为左外连接，右外连接，满外连接）

#查询所有员工！ 的last_name, department_name信息
#SQL92语法实现内连接：
select employee_id, department_name
from employees e, departments d
where e.'department_id' = d.'department_id';

#SQL92语法实现外连接：（使用 +）不过mysql不支持sql92外连接方式
select employee_id, department_name
from employees e, departments d
where e.'department_id' = d.'department_id'(+);

#SQL99语法中使用JOIN...ON的方式实现多表查询
#SQL99语法实现内连接:
select employee_id, department_name
from employees e JOIN departments d
ON e.'department_id' = d.'department_id';

select employee_id, department_name, city
from employees e JOIN departments d
ON e.'department_id' = d.'department_id';
JOIN location l
ON  d.'location_id' = l.'location_id';

#SQL99语法实现外连接:
#left join ... on 左外连接
#right join ... on 右外连接
select last_name, department_name
from employees e LEFT JOIN departments
ON e.'department_name' = d.'department_name';
#满外连接： full ... join ... on 不过MySQL不支持full满外连接  

Z6J29_使用SQL99实现7种JOINs操作（重点！）

UNION关键字的使用

（辅助完成满外连接）

考虑到效率问题，建议使用UNION ALL操作符；

#中图：内连接

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 department d
ON e.'department_id' = d.'department_id'
where d.'depaerment_id' IS NULL;

#右中图

select employee_id, department_name
from employees e RIGHT JOIN department d
ON e.'department_id' = d.'department_id'
where e.'depaerment_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 department d
ON e.'department_id' = d.'department_id'
where e.'depaerment_id' IS NULL;

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

#右下图

#左中图 UNION ALL 右中图
select employee_id, department_name
from employees e LEFT JOIN department d
ON e.'department_id' = d.'department_id'
where d.'depaerment_id' IS NULL
UNION ALL
select employee_id, department_name
from employees e RIGHT JOIN department d
ON e.'department_id' = d.'department_id'
where e.'depaerment_id' IS NULL;

Z6J31_多表查询课后练习

1、’‘显示’'所有员工的姓名，部门号和部门名称

select e.last_name, e.department_id, d.department_name
from employees e left join departmentsgt 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 e.last_name, d.department_name, d.location_id, l.city
from employees e left join departments d 
on e.'department_id' = d.'department_id'
left join location l 
on d.'location_id' = l.'location_id'
where e.'commission_pct' is not null;

4、选择city在Toronto工作的员工的last_name,jon_id,department_id,department_name

select e.last_name, e.jon_id, d.department_id, d.department_name
from employees e join departments d
on e.'department_id' = d.'department_id'
join location l
on d.'location_id' = l.'location_id'
where l.'city' = 'Toronto';

5、查询员工所在的department_name, 部门地址 , last_name, 工作 , salary，其中员工所在部门的部门名称为"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 location l 
on d.'location_id' = l.'location_id'
where d.'department_name' = "Executive";

6、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号

select e.last_name "员工", e.employee_id "员工id", m.last_name "管理者", m.employee_id "管理者id"
from employees e left join employees m
on e.'manager_id' = m.'employee_id'

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

select d.department_id
from departmets d left join employees e
on d.'department_id' = e.'department_id'
where e.'department_id' is null

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

select l.city 
from location 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, d.department_name
from employees e right join departments d
on e.'department_id' = d.'department_id'
where d.'department_name' in ('Sales','IT');