07_MySql数据查询语言DQL之子查询

子查询
含义：出现在其他语句中的select语句，称为子查询或内查询
			外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
	where或having后面：★
		标量子查询（单行） √
		列子查询  （多行） √
		行子查询
	select后面：仅仅支持标量子查询
	from后面：支持表子查询
	exists后面（相关子查询）
		表子查询
按结果集的行列数不同：
	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一列多行）
	行子查询（结果集有一行多列）
	表子查询（结果集一般为多行多列）


where或having后面
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>

列子查询，一般搭配着多行操作符使用
in、any/some、all

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

标量子查询
谁的工资比abel高
select * from employees where salary > (select salary from employees where last_name="abel") 

返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资
select last_name as 员工名,job_id as 工种编号,salary as 工资 from employees 
where job_id = (select job_id from employees where employee_id = 141) 
and salary > (select salary from employees where employee_id =143)

查询返回公司工资最少的员工的last_name,job_id和salary
select last_name as 员工名,job_id as 工种编号,salary as 工资 from employees
where salary = (select min(salary) from employees)

查询最低工资大于50号部门最低工资的部门id和最低工资
select department_id as 部门编号,min(salary) as 最低工资 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 as 员工名 from employees 
where department_id in (select distinct department_id from departments where location_id in (1400,1700))

查询返回其他工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、员工名、job_id以及salary
select employee_id as 员工号, last_name as 员工名,job_id as 工种编号,salary as 工资 from employees where job_id != "IT_PROG" and salary < ( select min(salary) from employees
where job_id = "IT_PROG")

行子查询(结果集一行多列或多行多列)
查询员工编号最小并且工资最高的员工信息
select * from employees where 
(employee_id,salary) = (select min(employee_id),max(salary) from employees )


select 后面(只支持标量子查询)
查询每个部门的员工个数
select d.*,(select count(*) from employees e where e.department_id = d.department_id) as 员工个数 from departments d 

查询员工号=102的部门号
select (select department_name as 部门名 from departments as d 
left join employees e on d.department_id = e.department_id where e.employee_id = 102) 部门名


from 后面
将子查询结果充当一张表，要求必须起别名
查询每个部门的平均工资的工资等级
select ag_dep.*,g.grade_level from (select avg(salary) as 平均工资,department_id from employees e group by department_id) ag_dep left join job_grades g on 
ag_dep.平均工资 between lowest_sal and highest_sal order by g.grade_level

exists后面(相关子查询)
语法：exists(完整的查询语句)
结果：1或0

查询有员工的部门名
select department_name from departments d where exists(select * from employees e
where d.department_id = e.department_id)```