MySQL——子查询用法

1、子查询概述

子查询指一个查询语句嵌套在另一个查询语句内部的查询，内部的查询是外部查询的条件，这个特性从MySQL4.1开始引入。

SQL中子查询的使用大大增强了SELECT查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

子查询基本使用

SELECT -- 主查询
    select_list
FROM
    table
WHERE
    expr operator > (SELECT -- 子查询
            select_list
        FROM
            table);

子查询(内查询)在主查询之前执行完成。

子查询的结果被主查询(外查询)使用。

注意事项：

• 子查询要包含在括号内
• 将子查询放在比较条件的右侧
• 单行操作符对应单行子查询，多行操作符对应多行子查询

子查询的分类

按子查询的结果返回一条还是多条记录，将子查询分为单行子查询和多行子查询。

按子查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。

2、子查询的使用

2.1、单行子查询

单行比较操作符

=等于，>大于，>=大于等于，<小于，<=小于等于，<>不等于

使用实例

-- 查找薪水大于所有员工平均薪水的员工
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees);

-- 查询工资大于149号员工工资的员工的信息
select employee_id,last_name,salary
from employees
where salary > (
    select salary from employees where employee_id = 149)
   
-- 返回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
    );

-- 返回公司工资最少的员工的last_name,job_id和salary
select last_name,job_id,salary
from employees
where salary = (
    select min(salary) from employees
    );

-- 查询与147号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id
select employee_id,manager_id,department_id
from employees
where manager_id = (
    select manager_id from employees where employee_id = 147
    )
and department_id = (
    select department_id from employees where employee_id = 147
    )
and employee_id <> 147;    
    
-- 查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id,min(salary) min_salary
from employees
where department_id is not null
group by department_id
having min_salary > (
    select min(salary) from employees where department_id = 50
    );
    
    

2.2、多行子查询

多行子查询比较操作符

-- 查找属于位置ID为1700的所有员工
SELECT
    employee_id, first_name, last_name
FROM
    employees
WHERE
    department_id IN (SELECT
            department_id
        FROM
            departments
        WHERE
            location_id = 1700)
ORDER BY first_name , last_name;

-- 返回其它job id中比job id为'IT_PROG’部门任一工资低的员工的员工号姓名、job id以及salary
select last_name,job_id,salary
from employees
where salary < any (
    select salary from employees where job_id = 'IT_PROG'
)
and job_id <> 'IT_PROG';

-- 返回其它job id中比job id为'IT_PROG’部门所有工资低的员工的员工号姓名、job id以及salary
select last_name,job_id,salary
from employees
where salary < all (
    select salary from employees where job_id = 'IT_PROG'
)
and job_id <> 'IT_PROG';

-- 查询平均工资最低的部门ID
-- 方式一：
select department_id,avg(salary) from employees group by department_id order by avg(salary) limit 1;
-- 方式二：
select department_id
from employees
group by department_id
having avg(salary) = (
    select min(avg_sal)
    from (
     select avg(salary) avg_sal from employees group by department_id
         ) t_dept_avg_sal
    )
-- 方式三：
select department_id
from employees
group by department_id
having avg(salary) <= all (
    select avg(salary) from employees group by department_id
);

-- 查询员工表中是领导的员工信息
select employee_id,last_name,manager_id
from employees
where employee_id in (
    select manager_id from employees
    );
    
-- 查询员工表中不是领导的员工信息
select employee_id,last_name,manager_id
from employees
where employee_id not in (
    select manager_id from employees where manager_id is not null
    );