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

妖狐艹你老母 2023-10-11 15:01 37阅读 0赞

### 一、子查询含义、分类 ###

*  含义：出现在其他语句中的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
    )