SQL教程——子查询

妖狐艹你老母 2022-12-25 03:58 136阅读 0赞

> 本教程中所使用的数据库的建表语句都在“SQL教程——索引”这篇文章中，点击链接直达：[索引&建表语句][Link 1]

> 摘要：本文主要介绍SQL的子查询

**目录**

一、where或having后面

1、标量子查询

2、列子查询

3、行子查询

二、select后面

三、from后面

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

--------------------

# 子查询 #

**含义：**

出现在其它语句中的select语句，称为子查询、内查询、嵌套查询

外部的查询语句，称为为主查询或外查询

**分类：**

案子查询出现的位置：

*  select后面      仅支持标量子查询
 *  from后面       支持表子查询
 *  ※where或having后面    标量子查询※、列子查询※、行子查询（较少）
 *  exists后面（相关子查询）

按结果集的行列数不同：

*  标量子查询（结果集只有一行一列）
 *  行子查询（结果集只有一行多列）
 *  列子查询（结果集只有一列多行）
 *  表子查询（结果集一般为多行多列）

## 一、where或having后面 ##

1、标量子查询（单行子查询）

2、列子查询（多行子查询）

3、行子查询（单行多列）

特点：

1.  子查询放在小括号内
2.  子查询一般放在条件的右侧
3.  标量子查询，一般搭配着单行操作符使用
    
    > < >= <= = <>
4.  子查询的执行优先于主查询执行，主查询的条件使用到了子查询的结果

列子查询，一般搭配着多行操作符的使用

in、any/some、all

### 1、标量子查询 ###

#案例1：谁的工资比Abel高？
    
    select last_name
    
    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 department_id, min(salary)
    
    from employees
    
    group by department_id
    
    having min(salary) > (
    
        select min(salary)
    
        from employees
    
        where department_id = 50
    
    );

### 2、列子查询 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70][]

#案例1：返回location_id是1400或1700的部门中所有的员工姓名
    
    #使用表连接
    
    select location_id, last_name
    
    from employees e
    
    join departments d
    
    on e.department_id = d.department_id
    
    where location_id in (1400, 1700);
    
    #使用子查询（in关键字）
    
    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（any关键字）
    
    select employee_id, last_name, job_id, salary
    
    from employees
    
    where department_id != 'IT_PROG'
    
    and salary < any (
    
        select distinct salary
    
        from employees
    
        where job_id = 'IT_PROG'
    
    );
    
    
    
    #案例3：返回其它工种的，比job_id为‘IT_PROG’工种所有员工工资都低的员工的：工号、姓名、job_id以及salary（all关键字）
    
    select employee_id, last_name, job_id, salary
    
    from employees
    
    where department_id != 'IT_PROG'
    
    and salary < all (
    
        select distinct salary
    
        from employees
    
        where job_id = 'IT_PROG'
    
    );
    
    
    
    #案例4：问题同案例1，但是要用any或all            in 和 =any 等效 ！！！       not in 和 != null等效
    
    select last_name
    
    from employees
    
    where department_id  = any (
    
        select distinct department_id
    
        from departments
    
        where location_id in (1400, 1700)
    
    );

### 3、行子查询 ###

#案例：查询员工编号最小，并且工资最高的员工的信息 
    
    select * 
    
    from employees
    
    where (employee_id, salary) = (
    
        select min(employee_id), max(salary)
    
        from employees
    
    );

## 二、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
    
    ) 部门名;

## 三、from后面 ##

#案例1：查询每个部门的平均工资的工资等级
    
    select ag_dep.*, g.grade_level
    
    from (
    
        select department_id, avg(salary) ag
    
        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);

#案例1：查询有员工的部门名
    
    select department_name
    
    from departments d
    
    where exists(
    
        select *
    
        from employees e
    
        where d.department_id = e.department_id
    
    );
    
    
    
    #案例2：查询没有女朋友的男神信息
    
    #in
    
    select bo.*
    
    from boys bo
    
    where bo.id not in(
    
        select boyfriend_id
    
        from beauty b
    
    )
    
    
    
    select bo.*
    
    from boys bo
    
    where not exists(
    
        select * 
    
        from beauty b
    
        where b.boyfriend_id = bo.id
    
    );
    
    
    
    
    
    
    
    #Test1：查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
    
    select employee_id, last_name, salary,e.department_id
    
    from (
    
        select avg(salary) ag, department_id
    
        from employees
    
        group by department_id
    
    ) ag_dep
    
    inner join employees e
    
    on e.department_id = ag_dep.department_id
    
    where salary > ag;
    
        
    
    #Test2：查询工资最高德员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名
    
    select max(salary), concat(first_name, '.', last_name)
    
    from employees;

[Link 1]: https://blog.csdn.net/IT_TIfarmer/article/details/110382794
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70]: /images/20221120/e9b4aef4bc914e17a5fc5d9ed262dc81.png