day03--mysql子查询

非等值连接

案例1：查询员工的工资以及对应的工资级别
SELECT salary,grade_level FROM employees e,job_grades g WHERE e.salary BETWEEN g.lowest_sal AND g.highest_sal;

案例2：查询名字中第三个字符为a，第五个字符为e的员工的工资以及对应的工资级别
SELECT salary,grade_level from employees e,job_grades g where e.salary BETWEEN g.lowest_sal and g.highest_sal and e.last_name like '__a_e%'

内连接

语法：
select 字段1，字段2，…
from 表1
inner join 表2 on 连接条件;

特点：
① 效果和等值连接是一样的！
② 表的顺序可以调换
③ 可以为表起别名
④ 可以添加筛选、分组、排序
⑤ 将筛选条件和连接条件实现了分类，提高代码的可读性
⑥ n表连接，至少需要n-1 个连接条件

案例1 ：查询员工名、部门名

SELECT last_name,department_name
from employees e join departments d 
on e.department_id=d.department_id;

案例2：查询有奖金的员工名、部门名

SELECT last_name,department_name,e.commission_pct
from employees e join departments d 
on e.department_id=d.department_id
where e.commission_pct is not null;

案例3：查询城市名、员工名和部门名

SELECT city,last_name,department_name
from locations l join departments d
on l.location_id=d.location_id
join employees e on e.department_id=d.department_id;

外连接

语法：
select 字段1，字段2，…
from 表1
left|right 【outer】 join 表2
on 连接条件

特点：
①查询结果：内连接的结果+主表中有但从表没有的记录（从表的字段用null填充）
②左连接，左边的就是主表； 右连接，右边的就是主表
③一般来讲要查询的字段来自于哪个表，那哪个表就是主表
④一般用于查询主表中有但从表中没有的记录

案例1：查询没有男朋友的女神名称
SELECT 'name',boyName from beauty b LEFT JOIN boys o on b.boyfriend_id=o.id where o.id is not null

案例2：查询哪个城市没有部门
SELECT city,department_name from locations l LEFT JOIN departments d on l.location_id= d.location_id where department_id is not null

案例3：查询哪个工种没有员工
SELECT job_title,employee_id from jobs j LEFT JOIN employees e on j.job_id=e.job_id where e.job_id is null;

自连接

案例：查询员工名和上级领导的名字
SELECT e.last_name,m.last_name from employees e INNER JOIN employees m on e.manager_id=m.employee_id

子查询

概念：
嵌套在另一个查询中的查询语句称为子查询，外部的查询称为主查询，类似于java中的内部类
语法
select 字段1，字段2
from 表1
where Id in(
select 字段1 from 表2
)
特点或注意事项：
1、子查询放在小括号内
2、放在条件右侧
3、子查询优先于主查询执行

分类：
单行子查询：子查询的结果只有一个值，使用单行操作符（> < >= <= = <>）
多行子查询:使用多行操作符（any、all、in、not in）

单行子查询

案例1：谁的工资比Abel高
select * from employees where salary>(SELECT salary from employees where last_name ='Abel');

案例2：题目：返回job_id与141号员工相同，salary比143号员工多的员工 的姓名，job_id 和工资
SELECT last_name,job_id,salary from employees 

案例3：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary from employees WHERE salary=(select MIN(salary) from employees );

案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT MIN(salary),department_id from employees GROUP BY department_id
HAVING MIN(salary)>(SELECT MIN(salary) from employees where department_id=50); 

多行子查询

案例：返回location_id是1400或1700的部门中的所有员工姓名
SELECT
last_name
FROM
employees
WHERE
department_id IN (
	SELECT
		department_id
	FROM
		departments
	WHERE
		location_id IN (1400, 1700)
)

查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（三步走）

①、SELECT department_id,avg(salary) av_s from employees GROUP BY department_id

②、SELECT employee_id,last_name,salary from employees e,(
	SELECT department_id,avg(salary) av_s from employees GROUP BY department_id
) a
where e.department_id=a.department_id
and e.salary>a.av_s

③、SELECT employee_id,last_name,salary from employees e,(
	SELECT avg(salary) av,department_id  from employees GROUP BY department_id
) a
where e.department_id=a.department_id
and e.salary>a.av

查询平均工资最低的部门信息

①、查询每个部门的平均工资
SELECT avg(salary) av,department_id from employees GROUP BY department_id

②、查询①结果中avg(salary)字段中的最低值
SELECT MIN(av) from (
	SELECT avg(salary) av,department_id from employees GROUP BY department_id
) a

③、查询部门编号，满足平均工资=②结果
SELECT department_id,avg(salary) from employees GROUP BY department_id HAVING avg(salary)=(
	SELECT MIN(av) from (
	SELECT avg(salary) av,department_id from employees GROUP BY department_id
	) a
)

④、查询部门信息，满足 department_id=③
SELECT * from departments d,(
	SELECT department_id,avg(salary) from employees GROUP BY department_id HAVING avg(salary)=(
	SELECT MIN(av) from (
	SELECT avg(salary) av,department_id from employees GROUP BY department_id
		) a
	)
) m
where d.department_id=m.department_id