美文网首页
子查询

子查询

作者: 爱折腾的傻小子 | 来源:发表于2020-12-12 20:38 被阅读0次
  • 出现在select语句中的select语句，称为子查询或内查询
  • 外部的select查询语句，称为主查询或外查询
  • 子查询分类
    • 标量子查询（结果集只有一行一列）
    • 列子查询（结果集只有一列多行）
    • 行子查询（结果集有一行多列）
    • 表子查询（结果集一般为多行多列）（包含：标量子查询、列子查询、行子查询）
  • 子查询出现在主查询中的不同位置
    • select后面：仅仅支持标量子查询（字段如：foo、foo1、...）
    • from后面：支持表子查询（如：表名、select查询的数据）
    • where或having后面：
      • 支持标量子查询（单列单行）运算符支持 >、<、=、<>、>=、<=、between and等
      • 列子查询（单列多行）运算符支持 IN 等
      • 行子查询（多列一行）运算符IN （(a,b) IN (...)）
    • exists后面（即相关子查询）：表子查询（多行、多列）
select后面的子查询
-- 子查询位于select后面的，仅仅支持标量子查询，支持一列一行
SELECT
  a.*,
(
  SELECT 
    count(*)
  FROM employees b
  WHERE 
    b.department_id = a.department_id
) AS 员工个数
FROM departments a;

-- 查询员工号=102的部门名称
SELECT 
(
  SELECT 
    a.department_name
  FROM 
    departments a, employees b
  WHERE 
    a.department_id = b.department_id
  AND 
    b.employee_id = 102
) AS 部门名;
from后面的子查询
  • 将子查询的结果集充当一张表，要求必须起别名，否者这个表找不到。
  • 然后将真实的表和子查询结果表进行连接查询。
-- 查询每个部门的平均工资
SELECT
  department_id, avg(a.salary)
FROM 
  employees a
GROUP BY 
  a.department_id;
-- 查询薪资等级表
SELECT * FROM job_grades;

-- 平均工资  between lowest_sal and highest_sal;
SELECT
    t1.department_id,
    sa AS '平均工资',
    t2.grade_level
FROM
    (
        SELECT
            department_id,
            avg(a.salary) sa
        FROM
            employees a
        GROUP BY
            a.department_id
    ) t1, job_grades t2
WHERE
    t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
where和having后面的子查询
  • 标量子查询（单行单列行子查询）
  • 列子查询（单列多行子查询）
  • 行子查询（一行多列）
  • 总结
    • 子查询放在小括号内
    • 子查询一般放在条件的右侧
    • 标量子查询，一般搭配着单行单列操作符使用 >、<、>=、<=、=、<>、!=
    • 列子查询，一般搭配着多行操作符使用
      • in(not in)：列表中的“任意一个”
      • any或者some：和子查询返回的“某一个值”比较
        • a>some(10,20,30)，a大于子查询中任意一个即可
        • a大于子查询中最小值即可，等同于a>min(10,20,30)
      • all：和子查询返回的“所有值”比较
        • a>all(10,20,30)，a大于子查询中所有值，换句话说，a大于子查询中最大值即可满足查询条件，等同于a>max(10,20,30)
      • 子查询的执行优先于主查询执行，因为主查询的条件用到了子查询的结果
    • mysql中的in、any、some、all
    • in，any，some，all 分别是子查询关键词之一
      • in：in常用于where表达式中，其作用是查询某个范围内的数据
      • any和some一样： 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据
      • all：可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据
-- 一般标量子查询
-- 查询谁的工资比Abel的高
/*①查询abel的工资【改查询是标量子查询】*/
SELECT 
  salary
FROM employees
WHERE 
  last_name = 'Abel';

/*②查询员工信息，满足salary>①的结果*/
SELECT
    *
FROM
    employees a
WHERE
    a.salary > (
        SELECT
            salary
        FROM
            employees
        WHERE
            last_name = 'Abel'
    );

-- 多个标量子查询
/*返回job_id与141号员工相同，salary比143号员工多的员工、姓名、job_id和工资*/
/*①查询141号员工的job_id*/
SELECT
    job_id
FROM
    employees
WHERE
    employee_id = 141;
/*②查询143好员工的salary*/
SELECT
    salary
FROM
    employees
WHERE
    employee_id = 143;

/*③查询员工的姓名、job_id、工资，要求job_id=① and salary>②*/
SELECT
    a.last_name 姓名,
    a.job_id,
    a.salary ⼯资
FROM
    employees a
WHERE
    a.job_id = (
        SELECT
            job_id
        FROM
            employees
        WHERE
            employee_id = 141
    )
AND a.salary > (
    SELECT
        salary
    FROM
        employees
    WHERE
        employee_id = 143
);

-- 子查询+分组函数
/*查询最低工资大于50号部门最低工资的部门id和其最低工资【having】*/
/*①查询50号部门的最低工资*/
SELECT
    min(salary)
FROM
    employees
WHERE
    department_id = 50;
/*②查询每个部门的最低⼯资*/
SELECT
    min(salary),
    department_id
FROM
    employees
GROUP BY
    department_id;
/*③在②的基础上筛选，满足min(salary)>①*/
SELECT
    min(a.salary) minsalary,
    department_id
FROM
    employees a
GROUP BY
    a.department_id
HAVING
    min(a.salary) > (
        SELECT
            min(salary)
        FROM
            employees
        WHERE
            department_id = 50
    );
-- 错误示例 (min(salary)改为salary)子查询返回的结果超过了1行记录
SELECT
    min(a.salary) minsalary,
    department_id
FROM
    employees a
GROUP BY
    a.department_id
HAVING
    min(a.salary) > (
        SELECT
            salary
        FROM
            employees
        WHERE
            department_id = 50
    );

-- 列子查询(子查询结果集一列多行)
-- 列子查询需要搭配多行操作符使用：in(not in)、any/some、all。
-- 为了提升效率，最好去重一下distinct关键字
/*返回location_id是1400或1700的部门中的所有员工姓名*/
/*方式1*/
/*①查询location_id是1400或1700的部门编号*/
SELECT DISTINCT
    department_id
FROM
    departments
WHERE
    location_id IN (1400, 1700);
/*②查询员工姓名，要求部门是①列表中的某一个*/
SELECT
    a.last_name
FROM
    employees a
WHERE
    a.department_id IN (
        SELECT DISTINCT
            department_id
        FROM
            departments
        WHERE
            location_id IN (1400, 1700)
    );
/*方式2：使用any实现*/
SELECT
    a.last_name
FROM
    employees a
WHERE
    a.department_id = ANY (
        SELECT DISTINCT
            department_id
        FROM
            departments
        WHERE
            location_id IN (1400, 1700)
    );
/*拓展，下面与not in等价*/
SELECT
    a.last_name
FROM
    employees a
WHERE
    a.department_id <> ALL (
        SELECT DISTINCT
            department_id
        FROM
            departments
        WHERE
            location_id IN (1400, 1700)
    );

/*返回其他工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id、salary*/
/*①查询job_id为'IT_PROG'部门任-工资*/
SELECT DISTINCT 
  salary
FROM employees
WHERE job_id = 'IT_PROG';
/*②查询员工号、姓名、job_id、salary，slary<①的任意一个*/
SELECT
    last_name,
    employee_id,
    job_id,
    salary
FROM
    employees
WHERE
    salary < ANY (
        SELECT DISTINCT
            salary
        FROM
            employees
        WHERE
            job_id = 'IT_PROG'
    )
AND job_id != 'IT_PROG';
/*或者*/
SELECT
    last_name,
    employee_id,
    job_id,
    salary
FROM
    employees
WHERE
    salary < (
        SELECT
            max(salary)
        FROM
            employees
        WHERE
            job_id = 'IT_PROG'
    )
AND job_id != 'IT_PROG';

/*返回其他工种中比job_id为'IT_PROG'部门所有工资低的员工的员工号、姓名、job_id、salary*/
SELECT
    last_name,
    employee_id,
    job_id,
    salary
FROM
    employees
WHERE
    salary < ALL (
        SELECT DISTINCT
            salary
        FROM
            employees
        WHERE
            job_id = 'IT_PROG'
    )
AND job_id != 'IT_PROG';
/*或者*/
SELECT
    last_name,
    employee_id,
    job_id,
    salary
FROM
    employees
WHERE
    salary < (
        SELECT
            min(salary)
        FROM
            employees
        WHERE
            job_id = 'IT_PROG'
    )
AND job_id != 'IT_PROG';

-- 行子查询 (子查询结果集一行多列)
/*查询员工编号最小并且工资最高的员工信息*/
/*①查询最小的员工编号*/
SELECT min(employee_id) FROM employees;
/*②查询最高工资*/
SELECT max(salary) FROM employees;
/*③⽅式1：查询员工信息*/
SELECT
    *
FROM
    employees a
WHERE
    a.employee_id = (
        SELECT
            min(employee_id)
        FROM
            employees
    )
AND salary = (
    SELECT
        max(salary)
    FROM
        employees
);
/*方式2*/
SELECT
    *
FROM
    employees a
WHERE
    (a.employee_id, a.salary) = (
        SELECT
            min(employee_id),
            max(salary)
        FROM
            employees
    );
/*方式3*/
SELECT
    *
FROM
    employees a
WHERE
    (a.employee_id, a.salary) IN (
        SELECT
            min(employee_id),
            max(salary)
        FROM
            employees
    );
exists后面 （也叫做相关子查询）
  • 语法：exists(完整的查询语句)
  • exists查询结果：1或0，exists查询的结果用来判断子查询的结果集中是否有值
  • 一般来说，能用exists的子查询，绝对都能用in代替，所以exists用的少
  • 和前面的查询不同，这先执行主查询，然后主查询查询的结果，在根据主查询进行过滤，子查询中涉及到主查询中用到的字段，所以叫相关子查询
select exists(
    select
        account
    from admins where id = 1
) as `exists`;
/*
| exists |
| ------- |
|   1     |
*/

-- 查询所有员工部门名
-- 先执行主查询
select 
  department_name 
form 
  departments a
where exists(
  select 
    1 
  from 
    employees b 
  where 
    a.department_id = b.department_id
);

-- 使用IN实现
select 
  department_name 
from 
  departments a 
where 
  a.department_id IN (
    select department_id from employees
  );

-- 查询没有员工的部门
select 
  * 
from 
  departments a 
where 
  not exists(
    select 
      1 
    from 
      employees b 
    where 
      a.department_id = b.department_id 
    and 
      b.department_id is not NULL
  );

-- 使用IN方式
select 
  * 
from 
  departments a 
where 
  a.department_id not in(
    select 
      department_id 
    from 
      employees b 
    where 
      b.department_id is not null
  );
NULL坑
  • not in的情况下，⼦查询中列的值为NULL的时候，外查询的结果为空。
  • 建议，建表是，列不允许为空。
-- 使用 not in的方式查询没有员工的部门
SELECT 
  *
FROM 
  departments a
WHERE 
  a.department_id NOT IN (
    SELECT 
      department_id
    FROM 
      employees b
    );
/*
Empty set (0.00 sec)
*/
总结
  • 字段值为NULL的时候，not in查询有⼤坑
  • 建议创建表的时候，列不允许为空

相关文章

  • Oracel_子查询

    SQL子查询 子查询语法 子查询 (内查询) 在主查询之前一次执行完成。 子查询的结果被主查询(外查询)使用 。 ...

  • Oracle | 子查询和伪列

    1. 子查询 （1）单行子查询 （2）多行子查询 1）ANY子查询 2）ALL 子查询 2. 伪列...

  • 《SQL必知必会》第 11 课 使用子查询

    目标： 11.1 子查询 11.2 利用子查询进行过滤 11.3 作为计算字段使用子查询 11.1 子查询 查询（...

  • MySQL 子查询

    什么是子查询 为什么要使用子查询 子查询的分类 怎样使用子查询 关联子查询 要使用的数据表 1. 什么是子查询？ ...

  • 数据库第七天

    子查询 查询里面还有查询注意: 子查询优先于主查询执行 最好子查询用括号 查询比ALLEN工资高的员工信息 单行子...

  • SQL查询_高级查询

    SQL查询_高级查询 一、子查询 子查询出现的位置一般为条件语句，oracle会先执行子查询，再执行父查询，子查询...

  • 17/12/6 子查询

    17/12/6 子查询 单行子查询 括号内的查询叫做子查询，也叫内部查询，先于主查询的执行。 子查询可以嵌入1.w...

  • MySql（七）子查询与虚表

    一、子查询 子查询在主查询前执行一次 主查询使用子查询的结果 子查询要用括号括起来 将子查询放在比较运算符的右边 ...

  • 数据分析之SQL子查询

    文章阅读路线： SQL子查询概念 独立子查询实例 相关子查询实例 SQL子查询常见玩伴 1.SQL子查询概念 子查...

  • MySql基础-子查询

    一、子查询定义 定义： 子查询允许把一个查询嵌套在另一个查询当中。 子查询用()括起来 子查询，又叫内部查询，相对...

网友评论

      本文标题：子查询

      本文链接：https://www.haomeiwen.com/subject/xjdlbktx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|子查询|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！