子查询的使用（where、select、from、exists）

一、子查询含义、分类

• 含义：出现在其他语句中的select语句，称为子查询或内查询。嵌套这个select语句的查询称为主查询或外查询。

• 分类：

  1. 按子查询出现的位置
     select后的子查询（仅仅支持标量子查询）
     from后的子查询（支持表子查询）
     where后的子查询（支持标量子查询，列子查询，行子查询）
     exists后的子查询（支持表子查询）

  2. 按子查询结果集的行列
     标量子查询（结果集只有一行一列）
     列子查询（结果集只有一行多列）
     行子查询（结果集只有一列多行）
     表子查询（结果集有多行多列）

二、子查询测试案例

• 标量子查询案例
  标量子查询结果集只有一行一列，一般搭配单行操作符使用（= 、!=、>、<等）

# 1.查询员工salary比Lorentz高的员工
select *
from employees
where salary > (select salary from  employees where last_name = 'Lorentz');


# 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.返回员工工资最少的员工信息
select *
from employees
where salary = (select min(salary) from employees);


# 4.查询最低工资大于50号部门的最低工资的部门id和其最低工资
/**
    1.查询50号部门的最低工资
    select min(salary),department_id
    from employees
    where department_id = 50

    2.having 过滤
    select min(salary), department_id
    from employees
    group by department_id
    having min(salary) > (
        select min(salary)
        from employees
        where department_id = 50
);
 */

select min(salary), department_id
from employees
group by department_id
having min(salary) > (
  select min(salary)
  from employees
  where department_id = 50
);

• 列子查询
  列子查询结果集通常一行多列，一般搭配 in、not in、some、any、all使用

# 1.查询location_id是1400或1700部门所有的员工
/**
  1.查询location_id为1400或1700的部门id
    select department_id
    from departments
    where location_id in (1400,1700)

  2.查询员工的部门id 在 第一步中结果集的id
  select *
  from employees
  where department_id in (
  select department_id
  from departments
  where location_id in (1400, 1700)
)


 */
select *
from employees
where department_id in (
  select department_id
  from departments
  where location_id in (1400, 1700)
);

# 2.返回其他部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id、salary
#   也就是返回其他部门中比比job_id为‘IT_PROG’部门最低员工工资还低的员工的员工号、姓名、job_id、salary
/**
    1.查询job_id为'IT_PROG的部门id'
    select distinct department_id
    from employees
    where job_id = 'IT_PROG';
    2.查询这个部门id的员工的最低工资
    select min(salary)
    from employees
    where department_id = (
      select distinct department_id
      from employees
      where job_id = 'IT_PROG'
  )
    3.查询工资比这个最低工资高的员工并且部门号不等于job_id为‘IT_PROG’部门
 */

select employee_id, last_name, job_id, salary
from employees
where salary > (
  select min(salary)
  from employees
  where department_id = (
    select distinct department_id
    from employees
    where job_id = 'IT_PROG'
  )
)
  and department_id != (
  select distinct department_id
  from employees
  where job_id = 'IT_PROG'
);

• 行子查询
  行子查询结果集通常为一行多列

# 1.查询员工编号最小并且工资最高的员工信息
/**
    行子查询where后的条件必须与行子查询结果集一一对应
 */
select *
from employees
where (employee_id,salary) = (
  select min(employee_id),max(salary)
  from employees
)

• select后的子查询

# select后查询
# 1.查询每个部门的信息,以及对应的人数
select d.*,(select count(*) from employees where employees.department_id = d.department_id) 员工个数
from departments d ;

• from后的子查询

# 1.查询每个部门的平均工资的工资等级
select s.*, j.grade_level
from job_grades j
inner join (
  select avg(salary) avg_sal, department_id
  from employees
  group by department_id
) s
on s.avg_sal between j.lowest_sal and j.highest_sal;

三、练习题

# 1.查询和Zlotkey相同部门的员工姓名和工资
select last_name,salary
from employees
where department_id = (
  select department_id
  from employees
  where last_name = 'Zlotkey'
  );

# 2.查询工资比公司平均工资高的员工
select *
from employees
where salary > (
  select avg(salary)
  from employees
  );

# 3.查询各部门中工资比本部门平均工资高的员工名，部门id，工资
/**
    1.查询每个部门的平均工资
    select avg(salary) as ,department_id
    from employees
    group by department_id
    2.将1的结果当做一张表，与employees表做内连接查询
    select e.last_name, e.salary, ag.agsal, ag.department_id
    from employees e
       inner join (
          select avg(salary) agsal, department_id
          from employees
          group by department_id
    ) ag
    on e.department_id = ag.department_id
    3.条件筛选 e.salary > ag.agsal
    select e.last_name, e.salary, ag.agsal, ag.department_id
    from employees e
           inner join (
      select avg(salary) agsal, department_id
      from employees
      group by department_id
    ) ag
    on e.department_id = ag.department_id
    where e.salary > ag.agsal;
 */
select e.last_name, e.salary, ag.agsal, ag.department_id
from employees e
       inner join (
  select avg(salary) agsal, department_id
  from employees
  group by department_id
) ag
on e.department_id = ag.department_id
where e.salary > ag.agsal;

# 4.查询和姓名中包含字母u的员工在相同部门的员工号和姓名
select last_name, employee_id
from employees
where department_id in (
  select distinct department_id
  from employees
  where last_name like '%u%'
);

# 5.查询在部门的location_id为1700的部门工作的员工和姓名
select last_name
from employees
where department_id in (
  select distinct department_id
  from departments
  where location_id = 1700
);

# 6.查询管理者是K_ing的员工姓名和工资
select last_name, salary
from employees
where manager_id = any (
  select employee_id
  from employees
  where last_name = 'K_ing'
);

# 7.查询工资最高的员工姓名和工资，要求firstname和lastname显示为一列，列明为姓.名
select concat(last_name, first_name) as '姓.名'
from employees
where salary = (
  select max(salary)
  from employees
)