首页 专题 文章 代码 归档
Mysql 子查询
2020.03.01 15:27 2020.03.01 15:35

1. 子查询

含义:出现在其它语句中的select语句,成为子查询或内查询;

内部嵌套其它select语句也称外查询或主查询;

分类:

1、按子查询出现的位置:

  • select 后面
    • 仅支持标量子查询
  • from后面
    • 支持表子查询
  • where或having后面【重要】
    • 标量子查询☆☆
    • 列子查询☆☆
    • 行子查询
  • exists后面(相关子查询)
    • 表子查询

2、按结果集的行列数不同:

  • 标量子查询(结果集一行一列)
  • 列子查询(结果集一列多行)
  • 行子查询(结果集一行多列)
  • 表子查询(结果集一般为多行多列)

1.1. where、having后面

1、标量子查询(单行)

2、列子查询(多行)

3、行子查询(多列多行)

特点:

  • 子查询一般都会放在小括号内;
  • 子查询一般放在条件的右侧;
  • 标量子查询,一般搭配单行操作使用(> < >= <= = <>);

列子查询,一般搭配多行操作符(in、any/some、all);

1.1.1. 标量子查询

1、谁的工资比Abel高?

# 1/先查询abel的工资
select salary
from employees
where last_name = 'Abel';

# 2/查询员工的信息,满足:salary>1

select *
from employees
where salary > (select salary
                from employees
                where last_name = 'Abel'
);

2、返回Job_id与141号员工相同,salary比143号员工多的员工,姓名、job_id和工资

-- 1、查询141号员工的job_id,行列数是1行1列
use employees;
select job_id
from employees
where employee_id = 141;

-- 2.143号员工的salary

select salary
from employees
where employee_id = 143;

-- 3. 查询员工姓名、job_id和工资

select job_id, salary, last_name
from employees
where salary > (select salary
                from employees
                where employee_id = 143)
  and job_id = (select job_id
                from employees
                where employee_id = 141);

3、返回公司工资最少的员工的last_name、job_id、salary【聚合函数】

# 1.
select min(salary)
from employees;

# 2.
select last_name, job_id, salary
from employees
where salary = (select min(salary)
                from employees);

4、查询最低工资大于50号部门最低工资的部门id和其最低工资;

# 1.
select min(salary)
from employees
where department_id = 50;

# 2. 查询每个部门的最低工资

select min(salary) s, department_id
from employees
group by department_id;

# 3.在2基础上加上1的条件,筛选
select min(salary) s, department_id
from employees
group by department_id
having s > (select min(salary)
            from employees
            where department_id = 50);

总结:

基本上都是用到了子查询的结果去查询外部查询,也即:子查询先查询,再把结果给父查询;

1.1.2. 例子查询

一列多行;

需要搭配多行操作符来使用:

in/not in:对于列表中的任意一个

any|some:和子查询中的某一个值比较

all:和子查询中的所有值比较;


1、返回location_id是1400或1700的部门中的所有员工姓名

# 1单列多行
select distinct department_id
from departments
where location_id in (1400, 1700);

# 2员工姓名,要求部门号是上面查询出来的
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

# 2.1
select salary
from employees
where job_id = 'IT_PROG';

# 2.2 工号、姓名、job_id、salary
select last_name, job_id, salary, employee_id
from employees
where salary < any (
    select salary
    from employees
    where job_id = 'IT_PROG'
)
  and job_id <> 'IT_PROG';

注:any是表示子查询结果中任意一个,all表示子查询结果中的所有。

<any表示只要小于子查询结果中的任一个,表达式就成立;

>any表示只要大于子查询结果中的任一个,表达式就成立;

=any表示等于子查询中的任一个,相当于in;

换句话说,上面例子的中子查询可以换成:即<最大的一个

select last_name, job_id, salary, employee_id
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, job_id, salary, employee_id
from employees
where salary < all (
    select distinct salary
    from employees
    where job_id = 'IT_PROG'
)
  and job_id <> 'IT_PROG';

或者:

select last_name, job_id, salary, employee_id
from employees
where salary <  (
    select min(salary) -- 小于最小的
    from employees
    where job_id = 'IT_PROG'
)
  and job_id <> 'IT_PROG';
1.1.3. 行子查询

结果集:一行多列或多行多列

1、查询员工编号最小且工资最高的员工信息;

# 1、查询员工编号最小且工资最高的员工信息

# 1.1查询最小编号
select min(employee_id)
from employees;

# 1.2查询最高工资
select max(salary)
from employees;

# 1.3 查询员工信息
select *
from employees
where employee_id = (
    select min(employee_id)
    from employees
)
  and salary = (
    select max(salary)
    from employees
);

注:上面的语句也可以写成:

select *
from employees
where (employee_id, salary) = (
    select min(employee_id), max(salary)
    from employees
);

1.2. select后面

这里面的子查询只支持一行一列;

1、查询每个部门的员工个数

select d.*,
       (
           select count(*)
           from employees e
           where e.department_id = d.department_id -- 外查询字段
)
from departments d;

2、查询员工号=102的部门名;

select (
           select department_name
           from departments d
                    inner join employees e on d.department_id = e.department_id
           where e.employee_id = 102
       );

1.3. from后面

这个暂时不管...

1.4. exists后面(相关子查询)

exists就是是否存在,1代表存在(子查询有结果集);

1代表有结果集,0代表没有结果集;

select exists(select employee_id from employees); -- 1

1、查询有员工的部门名;

select department_name
from departments d
where exists(select * from employees e where d.department_id = e.department_id);
本节阅读完毕! (分享
二维码图片 扫描关注我们哟