MySQL（7.SQL99标准-连接查询）

#七.SQL99语法
#7.1 内连接
/*
	1.语法
	-- SQL92
	select 查询列表
	from 表1 别名 , 表2 别名
	where 连接条件  and   筛选条件
	group by 分组列表
	having 分组后筛选
	order by 排序列表;
	
	-- SQL99
	select 查询列表
	from 表1 别名 [INNER] JOIN 表2 别名 
	on 连接条件
	where 筛选条件
	group by 分组列表
	having 分组后筛选
	order by 排序列表;
	
	2.执行顺序
	from -> inner join on -> where -> group by -> having -> select -> order by
*/

#3.案例
#1）、等值连接
-- 1.简单连接
#案例：查询员工名和部门名
select e.first_name '员工名', d.department_name '部门名'
from employees e inner join departments d
on e.department_id = d.department_id;

-- 2.添加筛选条件
#案例：查询部门编号>100的部门名和所在的城市名
select d.department_name '部门名', l.city '所在城市名'
from departments d join locations l
on d.location_id = l.location_id
where d.department_id > 100;

-- 3.添加分组
#案例：查询每个城市的部门个数
select l.city '城市' , count(d.department_id) '部门个数'
from departments d join locations l
on d.location_id = l.location_id
group by l.city;

-- 4.添加分组+排序
#案例：查询部门中员工个数>10的部门名，并按员工个数降序
select d.department_name '部门名' , count(e.employee_id) '员工个数'
from employees e join departments d
on e.department_id = d.department_id
group by d.department_name
having count(e.employee_id)>10
order by count(e.employee_id) desc;

#2）、非等值连接
#案例：查询部门编号在10-90之间的员工的工资级别，并按级别进行分组
select e.salary , e.department_id , g.grade_level '工资级别'
from employees e join job_grades g
on e.salary BETWEEN g.lowest_sal and g.highest_sal
where e.department_id BETWEEN 10 and 90;

#3）、自连接
#案例：查询员工名和对应的领导名
select e.first_name '员工' , m.first_name '领导'
from employees e join employees m
on e.manager_id = m.employee_id;

#7.2 外连接
/*
	1.语法：
	select 查询列表
	from 表1 别名 left | right | full [outer] join 表2 别名
	on 连接条件
	where 筛选条件
	group by 分组列表
	having 分组后的筛选
	order by 排序列表
	
	2.注意：
	2.1 左外连接：查询左表中的全部数据。 left [outer] join
		左外连接的数据 = 等值连接数据 + 左表中有而右表中没有的数据
	2.2 右外链接：查询右表中的全部数据。 right [outer] join
		右外连接的数据 = 等值连接数据 + 右表中有而做表中没有的数据
	2.3 全外连接（MySQL不支持）：full [outer] join
		全外连接的数据 = 
		等值连接数据+左表中有而右表中没有的数据+右表中有而做表中没有的数据
*/

#3.案例
#案例1：查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示为null
-- 左连接
select g.name '女神' , b.name '男神'
from girl g left join boy b
on g.boyfriend_id = b.id;

-- 右连接
select g.name '女神' , b.name '男神'
from boy b right join girl g
on b.id = g.boyfriend_id;

#案例2：查哪个女神没有男朋友
-- 左连接
select g.name '女神' , b.name '男神'
from girl g left join boy b
on g.boyfriend_id = b.id
where b.name is null;

-- 右连接
select g.name '女神' , b.name '男神'
from boy b right join girl g
on b.id = g.boyfriend_id
where b.name is null;

#案例3：查哪个男神没有女朋友
-- 左连接
select  b.name '男神' , g.name '女神'
from boy b left join girl g
on b.id = g.boyfriend_id
where g.name is null;

-- 右连接
select b.name '男神' , g.name '女神'
from girl g RIGHT JOIN boy b
on g.boyfriend_id = b.id
where g.name is null;

#案例4：查询哪个部门没有员工，并显示其部门编号和部门名
select d.department_id , d.department_name
from employees e right join departments d
on e.department_id = d.department_id
where e.first_name is null;

#案例5：查询城市名包含a字符的哪个城市没有部门，并按城市名降序
select l.city
from locations l left join departments d
on l.location_id = d.location_id
where l.city like '%a%' and d.department_name is null
order by l.city desc;

#全外连接  full outer join  on
#情况1：已知girl表和boy表，有关系，查询出他们所有的记录数，不重复
select count(*) from girl g full join boy b on g.boyfriend_id = b.id; -- 13+7=20

#情况2：已知girl表和departments表，没有关系，查询出他们所有的记录数，不重复
select count(*) from girl g full join departments d on 1=2; -- 13+27=40


#7.3 交叉连接（笛卡尔积） 会造成数据冗余  cross join
-- SQL92标准
select count(*) from girl g , boy b; -- 13*7=91
-- SQL99标准
select count(*) from girl cross join boy; -- 13*7=91


#7.4 自然连接  NATURAL JOIN
#使用前提：自然的发现连接表中，将字段名称和数据类型，及值一致的查询出来
select * from employees NATURAL JOIN departments;


#作业
-- 一、查询编号>3 的女神的男朋友信息，如果有则列出详细，如果没有，用 null 填充
select g.name , b.*
from girl g left join boy b
on g.boyfriend_id = b.id
where g.id>3;
-- 二、查询哪个城市没有部门
select l.city
from  locations l left join departments d
on l.location_id = d.location_id
where d.department_name is null;
-- 三、查询部门名为 SAL 或 IT 的员工信息
select e.*
from employees e inner join departments d
on e.department_id = d.department_id
where d.department_name in('SAL','IT');

-- 例子62：列出员工和领导的对应关系，包含‘Steven’
select e.first_name '员工' , m.first_name '领导'
from employees e left  join employees m
on e.manager_id = m.employee_id;

-- 例子63：列出哪些人是员工？（或者）哪些员工不是领导？
select e.first_name '员工'
from employees e left  join employees m
on e.employee_id = m.manager_id
where m.first_name is null;