# 二、sql 99 语法
/*
	
*/
#1、等值连接
-- 案例一：查询员工名、部门名
SELECT last_name,department_name
from employees e
inner JOIN departments d
on e.department_id=d.department_id;

-- 案例二：查询名字中包含 e 的员工名和工种名（添加筛选）
select last_name,job_title
from employees e
inner join jobs j
on j.job_id=e.job_id
where e.last_name like '%e%';

-- 案例三：查询部门个数>3 的城市名和部门个数 （添加分组和筛选）
-- 下面这个建议看看 自己做的时候老是有点问题
select city,count(*)
from departments d
INNER JOIN locations l
on l.location_id=d.location_id
GROUP BY city
HAVING count(*)>3;


-- 案例四：查询哪个部门的员工个数>3 的部门名和员工个数，并按个数降序（添加排序）
select department_name,count(*) 员工个数
from departments
inner join employees
on departments.department_id=employees.department_id
GROUP BY department_name
having  count(*)>3
order by count(*) desc;

-- 案例五：查询员工名、部门名、工种名，并且按照部门名称降序
-- 注意看三表链接的格式
select last_name,department_name,job_title
from employees
inner join departments on employees.department_id=departments.department_id
INNER JOIN jobs on employees.job_id=jobs.job_id
ORDER BY department_name desc;

#2、非等值连接
-- 案例一：查询员工的工资级别
SELECT salary,grade_level
from employees e
INNER JOIN job_grades g
on e.salary between g.lowest_sal and g.highest_sal;
-- 案例二：查询每个工资级别的个数>20的个数，并且按工资级别降序
SELECT count(*),grade_level
from employees e
inner join job_grades g
on e.salary between g.lowest_sal and g.highest_sal
GROUP BY grade_level
having count(*)>20
order by count(*) desc;

#3、自连接
-- 案例；查询员工的名字、上级的名字
select e.last_name,m.last_name
from employees e
inner join employees m
on e.manager_id=m.employee_id;

# 子查询： 出现在其他语句中的select 语句 称为子查询或内查询
/*
分类：
	按子查询出现的位置：
				select 后面	： 仅仅支持标量子查询
									
				from 后面		：	支持表子查询
				
（**重要的**）where 或 having 后面		：标量子查询、列子查询、行子查询
				
				exists 后面		：表 子查询
	按结果集的行列数不同分：
				标量子查询（结果集只有一行一列）
				列子查询（结果集只有一列多行）
				行子查询（结果集有一行多列）
				表子查询（一般为多行多列）
*/
#一、where 或 having 后面
	-- 1、标量子查询（单行子查询）
	-- 2、列子查询（多行子查询）
	-- 3、行子查询（多行多列）
	/*
		特点：
		子查询放在小括号内
		子查询一般放在条件的右侧
		标量子查询，一般搭配着单行操作符使用
		>< >= <=  <>
		列子查询，一般搭配着多行操作符使用： in、any/some、all
		子查询执行顺序优于主查询
	
	*/
	#1、标量子查询
	-- 案例一：谁的工资比 Abel 高
	-- 第一步： 先查询 abel 工资
select salary
from employees
where last_name='Abel';
-- 第二步：查询员工的信息，满足 salary>1 的结果
select *
from employees
where salary>(
			select salary
			from employees
			where last_name='Abel'
);		

# 案例二：返回 job_id 与 141 号员工相同，salary 比143号员工多的员工 姓名，job_id 和工资
-- 第一步：查询 141 号员工的 job_id
select job_id 
from employees
where employee_id=141

-- 第二步：查询 143 号员工的工资
select salary 
from employees
where employee_id=143

-- 第三步 返回 job_id 与 141 号员工相同，salary 比143号员工多的员工 姓名，job_id 和工资
select last_name,job_id,salary
from employees
where job_id=(
	select job_id 
	from employees
	where employee_id=141
	)
 and salary>(
		select salary 
		from employees
		where employee_id=143
		);

# 案例三：查询最低工资大于 50 号部门最低工资的部门 id 和其最低工资
-- 第一步查询 50 号部门的最低工资
select min(salary)
from employees
where department_id=50;
-- 第二步：查询每个部门的最低工资
select min(salary)
from employees
GROUP BY department_id；
-- 第三步：筛选 min（salary）>1
select department_id,min(salary)
from employees
GROUP BY department_id
HAVING min(salary)>(
	select min(salary)
	from employees
	where department_id=50
	);

#列子查询（多行子查询）
/*
	使用多行比较操作符
		in/not in
		any|some
		all
*/
--  案例一：返回 location_id 是 1400 或 1700 的部门中的所有员工姓名
-- 第一步：查询 location_id 是 1400 或 1700de 部门编号
select distinct department_id
from departments
where location_id in(1400,1700)

-- 第二步 查询员工姓名，要求部门编号是 1 列表中的某一个
select last_name
from employees
where department_id in(
	select distinct department_id
		from departments
		where location_id in(1400,1700)
);

#3、行 子查询（使用比较少 了解）
-- 案例：查询员工编号最小并且工资最高的员工信息
-- 第一步：查询最小的员工编号
select min(employee_id)
from employees;
-- 第二步：查询最高工资
	SELECT max(salary)
	from 	employees

-- 第三步:查询员工信息
select *
from employees
where employee_id=(
	select min(employee_id)
	from employees
) and salary=(
	SELECT max(salary)
	from 	employees
);
-- 行子查询的格式
select *
from employees
where (employee_id,salary)=(
	select min(employee_id),max(salary)
	from employees
	);