第四节----子查询

含义：出现在其他语句中的select语句，称为子查询或内查询
分类：
    按子查询出现的位置： 
          select后面：
                仅支持标量子查询
          from后面：
                支持表子查询
          where或having后面：
                 标量子查询
                 列子查询
                 行子查询
          exists（相关子查询）
                 表子查询
     按结果集的行列数不同：
          标量子查询（结果只有一行一列）
          列子查询（结果集只有一列多行）
          行子查询（结果集只有一行多列）
          表子查询（结果集一般为多行多列）   
①子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

一、where或having后面

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

1.标量子查询
#案例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 where job_id=(select job_id from employees where employee_id=141) and salary >(select salary from employees where employee_id=143);
#案例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);

2.列子查询

#案例1:返回location——id是1400或1700的部门中的所有员工姓名
select last_name from employees where department_id in(select distinct department_id from departments where location_id in (1400,1700));
#案例2：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salary
方式一：
select last_name,employee_id,job_id,salary from employees where salary<any(select distinct salary from employees where job_id = 'IT_PROG') and job_id <>'IT_PROG';
方式二：
select last_name,employee_id,job_id,salary from employees where salary< (select max(salary) from employees where job_id = 'IT_PROG') and job_id <>'IT_PROG';
#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
方式一：
select last_name,employee_id,job_id,salary from employees where salary<all(select distinct salary from employees where job_id = 'IT_PROG') and job_id <>'IT_PROG';
方式二：
select last_name,employee_id,job_id,salary from employees where salary<(select distinct min(salary) from employees where job_id = 'IT_PROG') and job_id <>'IT_PROG';

3.行子查询
#案例：查询员工编号最小并且工资最高的员工信息
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) 个数 from departments d;
#查询员工号=102的部门名
select (select department_name from departments d inner 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) ag,department_id from employees group by department_id)ag_dep inner join job_grades g on ag_dep.ag between lowest_sal and highest_sal;

四、exists后面（相关子查询）

语法:
exists(完整的查询语句) 
结果：1或0

#是否存在
select exists(select employee_id from employees);
select exists(select employee_id from employees where salary = 30000);
#案例1：查询有员工的部门名
select department_name from departments d where exists(select * from employees e where d.department_id=e.department_id);
#案例2：查询没有女朋友的男神信息
方式一：
select bo.* from boys bo where bo.id not in (select boyfriend_id from beauty);
方式二：
select bo.* from boys bo where not exists(select boyfriend_id from beauty b where bo.id=b.boyfriend_id);