MySQL（6.SQL92标准-连接查询-内连接）

#交叉连接：笛卡尔积查询，造成数据冗余
select * from girl , boy; -- 12*6=72

#六.SQL92标准-连接查询-内连接
/*
	1.语法
	select 查询列表
	from 表1 别名 , 表2 别名
	where 连接条件 and 筛选条件
	group by 分组列表
	having 分组条件
	order by 排序列表
	
	2.执行顺序
	from ->  where 连接条件 and 筛选条件  -> group by -> having -> select -> order by
*/

#3.案例

#6.1 等值连接
-- 1、多表等值查询
#案例1：查询女神名和对应的男神名
select g.name '女神' , b.name '男神'
from girl g , boy b
where g.boyfriend_id = b.id;

#案例2：查询员工名和对应的部门名
select e.first_name '员工名' , d.department_name '部门名'
from employees e , departments d
where e.department_id = d.department_id;

-- 2、为表起别名
#查询员工名、工种号、工种名
select e.first_name '员工名' , e.job_id '工种号' , j.job_title '公众名'
from employees e , jobs j
where e.job_id = j.job_id;

-- 3、两个表的顺序可以调换
#查询员工名、工种号、工种名
select e.first_name '员工名' , e.job_id '工种号' , j.job_title '公众名'
from jobs j , employees e
where e.job_id = j.job_id;

-- 4、可以加筛选
#案例1：查询有奖金的员工名、部门名
select e.first_name '员工名' , d.department_name '部门名'
from employees e , departments d
where e.department_id = d.department_id and e.commission_pct is not null;

#案例2：查询城市名中第二个字符为o的部门名和城市名
select d.department_name '部门名', l.city '城市名'
from departments d , locations l
where d.location_id = l.location_id and l.city like '_o%';

-- 5、可以加分组
#案例1：查询每个城市的部门个数
select l.city , count(d.department_id)
from departments d , locations l
where d.location_id = l.location_id 
group by l.city;

#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
select d.department_name '部门名' , 
d.manager_id '领导编号' , min(salary) '最低工资'
from employees e , departments d
where e.department_id = d.department_id and e.commission_pct is not null
group by d.department_name;

-- 6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
select j.job_title '工种名' , count(*) '员工个数'
from employees e , jobs j
where e.job_id = j.job_id
group by j.job_title
order by count(*) desc;

-- 7、可以实现三表连接？
#案例1：查询员工名、部门名和所在的城市
select e.first_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;

#案例2：查询员工名、部门名和所在的城市，要求城市名称以s开头，并且按照部门名称降序。
select e.first_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 and l.city like 's%'
order by d.department_name desc;

#6.2 非等值连接
#案例：查询员工的工资和工资级别
select e.first_name , e.salary , g.grade_level
from employees e , job_grades g
where e.salary between g.lowest_sal and g.highest_sal;

#6.3 自连接
#案例：查询员工名和上级的名称
select e.first_name '员工' , m.first_name '领导'
from employees e , employees m
where e.manager_id = m.employee_id;


#4.作业
-- 1. 显示所有员工的姓名，部门号和部门名称。
select e.first_name , e.department_id , d.department_name
from employees e inner join departments d
on e.department_id = d.department_id;
-- 2. 查询 90 号部门员工的 job_id 和 90 号部门的 location_id
select e.department_id , e.job_id , d.location_id
from employees e inner 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 , e.commission_pct , d.department_id , l.location_id , l.city
from employees e inner join departments d inner join locations l
on e.department_id = d.department_id and 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 , d.department_id , d.department_name , l.city
from employees e inner join locations l inner join departments d
on e.department_id = d.department_id and l.location_id = d.location_id
where l.city = 'Toronto';

-- 5.查询每个工种、每个部门的部门名、工种名和最低工资
select e.job_id , d.department_name , j.job_title , min(e.salary)
from employees e inner join departments d inner join jobs j
on e.department_id = d.department_id and e.job_id = j.job_id
group by e.job_id , d.department_name;

-- 6.查询每个国家下的部门个数大于 2 的国家编号
select l.country_id
from locations l inner join departments d
on d.location_id = l.location_id
group by l.country_id
having count(d.department_id)>2;

-- 7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格
-- 式
-- employees Emp#  manager Mgr#
-- kochhar 101 king 100
select concat(e.first_name,' ',e.employee_id,' ',m.first_name,' ',m.employee_id)
from employees e inner join employees m
on e.manager_id = m.employee_id;


-- 等值连接
-- 例子52：列出员工名字和部门名字？
select e.first_name '员工名', d.department_name '部门名'
from employees e inner join departments d
on e.department_id = d.department_id;
-- 例子53：列出部门号、部门名称、地区名称？
select e.department_id '部门号',d.department_name '部门名称' , l.city '地区名称'
from employees e inner join departments d inner join locations l
on e.department_id = d.department_id and d.location_id = l.location_id;
-- 例子54：列出Tokyo城市有多少个部门？
select count(*)
from departments d inner join locations l
on d.location_id = l.location_id
where l.city = 'Tokyo';
-- 例子55：列出‘Steven’在哪个城市上班？
select e.first_name , l.city
from employees e inner join departments d inner join locations l
on e.department_id = d.department_id and d.location_id = l.location_id
where e.first_name = 'Steven';
-- 例子56：列出Toronto城市有多少员工？
select count(*)
from employees e inner join departments d inner join locations l
on e.department_id = d.department_id and d.location_id = l.location_id
where l.city = 'Toronto';
-- 例子57：列出Beijing营销部门Sal有那些员工？
select e.first_name , d.department_name , l.city
from employees e inner join departments d inner join locations l
on e.department_id = d.department_id and d.location_id = l.location_id
where l.city = 'Beijing' and d.department_name = 'Sal';

-- 自连接
-- 例子58：列出员工名称和领导名称的对应关系？
select e.first_name '员工' , m.first_name '领导'
from employees e inner join employees m
on e.manager_id = m.employee_id;
-- 例子59：‘Neena’的领导是谁？
select m.first_name
from employees e inner join employees m
on e.manager_id = m.employee_id
where e.first_name = 'Neena';
-- 例子60：‘Steven’是谁的领导？
select e.first_name , m.first_name
from employees e inner join employees m
on e.employee_id = m.manager_id
where e.first_name = 'Steven';
-- 例子61：请问哪些员工是领导？
select distinct m.first_name
from employees e inner join employees m
on e.manager_id = m.employee_id;