数据库笔记（五）

Posted on | Edited on

写在前面：本篇博客大部分内容参考数据库系统概念（本科教学版）第三章(第三章部分的多表操作没有在此处讲，准备挪到第四章再一起讨论)
笔者接下来的代码示例会主要在SQL Server数据库中测试

在开始今天的摸鱼大业之前，让我们先构造一组表，用于演示下面的例子(´`)

BONUS.png

DEPT.png

EMP.png

SALGRADE.png

Having 子句

由于where子句中不能用聚合函数（组函数）做条件，故引入having子句

  • 让我们先看一下各个子句的书写顺序（下面的只是我们一般在写SQL语句时候的书写顺序）
    • SELECT
    • FROM
    • WHERE
    • GROUP BY
    • HAVING
    • ORDER BY
    • 上面各子句的执行顺序：FROM–>WHERE– > GROUP BY–> HAVING–>SELECT–>ORDER BY

      其中上面除了GROUP BY和HAVING可以互换位置外，其他位置均固定，但最好采用上面的顺序

  • 从上面的讨论中可以看出
    • HAVING一般紧随GROUP BY之后，置于ORDER BY之前
    • 而且当聚集函数（组函数）做条件时，只用用HAVING来处理

  • 举个栗子：求所有平均工资大于2000的部门的部门号以及该部门的平均工资

    • 先来看看每个部门的平均工资

      1
      2
      3
      SELECT DEPTNO, avg(SAL) as avg_sal
      FROM EMP
      GROUP BY DEPTNO

      得到如下结果

    • 1.png

    • 接下来开始求题设的问题

      1
      2
      3
      4
      SELECT DEPTNO, avg(SAL) as avg_sal
      FROM EMP
      GROUP BY DEPTNO
      HAVING avg(SAL) > 2000;

      得到如下结果

    • 2.png

聚合函数的嵌套

  • 不同数据库对聚集函数嵌套的规定不同
    • Oracle数据库规定最多只能有两层嵌套
    • SQL Server不允许聚集函数嵌套

  • 举个栗子：

    1
    2
    3
    4
    5
    6
    7
    --下面的语句在Oracle数据库中执行是可以的
    SELECT max(avg(sal))
    FROM EMP
    GROUP BY deptno

    --上面的语句在SQL Server中执行会报下面的错
    [S0001][130] 不能对包含聚合或子查询的表达式执行聚合函数。

  • ┐(´•_•`)┌ 实际上二层以上的嵌套也是毫无意义的（只是瞎哔哔，不是什么重点，有助于理解为什么聚集函数多层嵌套是没有意义的）

    • 首先回忆一下，聚集函数的操作是多行输入，得到一行输出

    • 举个栗子

      1
      2
      3
      SELECT max(max(avg(sal)))
      FROM EMP
      GROUP BY deptno

    • 现在来分析上面语句执行的过程

      • （avg）首先根据部门号deptno分成若干组，并计算每组的平均工资（返回若干条记录）
      • （max）然后取各部门最高的平均工资（返回一条记录）
      • （max）这个时候只有一条记录，取最大值也还是那一条记录

子查询

这部分是重点也是难点呀٩(๑`^´๑)۶，不用担心，让笔者带你细细解剖(≖_≖ )
这部分参考了两份笔记和课本

  • 嵌套子查询

    即SQL标准提供嵌套子查询机制，允许将一个完整的select-from-where查询表达式嵌入到另一个查询语句中

  • 子查询嵌套在where和having子句中的情况（把子查询整体当做一组值，可以是0个，1个，或多个）

    • 单行子查询

      • 在单行子查询中，子查询语句只能返回一行数据
      • 当where子句中用下列逻辑符号连接子查询时，便只能连接单行子查询
        • =
        • <
        • >
        • <=
        • >=
        • <>

      • 举个栗子(≖_≖ )

        • 我们现在要找所有员工中，拿到最高工资的人的名字以及工资
          1
          2
          3
          4
          SELECT ENAME, SAL
          FROM EMP
          WHERE SAL = (SELECT max(SAL)
                       FROM EMP);

        得到如下结果

      • 3.png

    • 多行子查询

      • 多行子查询中，子查询语句可以返回多行数据
      • 当where子句中用下列关键字连接子查询时，便是多行子查询($代表上面提到的=, >等符号)
        • $ any/some
        • $ all
      • 举几个简单的栗子
        • < any(…) 表示小于子查询结果集中的最大值
        • > any(…) 表示大于子查询结果集中的最小值
        • = any(…) 等价于in
        • = all(…) 没有语法错误，但是只要子查询结果集的个数大于1，则整体的结果必然为空
        • < all(…) 表示小于子查询结果集中的最小值
        • > all(…) 表示大于子查询结果集中的最大值

      • 来举个正经的栗子

        • 求比部门号为20的部门的所有员工的工资都要高的员工的名字和工资
          1
          2
          3
          4
          5
          SELECT ENAME, SAL
          FROM EMP
          WHERE SAL > SOME (SELECT SAL
                          FROM EMP
                          WHERE DEPTNO = 10);

        得到如下结果

      • 4.png

      • 还有一个要注意的问题(ㅍ_ㅍ)，就是子查询中SELECT后面的字段要根据主查询语句中的字段来确定（简单的说就是子查询语句前面，where后面，出现了什么字段，子查询语句的SELECT列表中理论上就应该出现这些字段）

        • 举个栗子

          1
          2
          3
          4
          5
          6
          7
          8
          9
          10
          11
          12
          13
          --下面这个语句是匹配的
          SELECT ENAME, SAL
          FROM EMP
          WHERE SAL > SOME (SELECT SAL
                          FROM EMP
                          WHERE DEPTNO = 10);
          --下面这个语句就不匹配的
          --因为ENAM并没有出现在主查询语句中
          SELECT ENAME, SAL
          FROM EMP
          WHERE SAL > SOME (SELECT ENAME, SAL
                          FROM EMP
                          WHERE DEPTNO = 10);

        • 其实换个角度想想，具体执行的时候是将where后面的字段与子查询语句的结果集进行比较，如果字段都不一样，就没有可比性了

      • 对了，还有一个补充，not in + 子查询，如果子查询的结果集中包含NULL值，那整体的结果必为空。因为NULL值是不确定的值，谁也不能保证某个具体的值是否等于NULL。。。

    • 子查询中存在性的判别可以用exists关键字

      • 举个栗子就好
        1
        2
        3
        4
        5
        6
        7
        --下面的语句就查出了所有手底下管了人的经理的信息
        --其实这里已经用到了相关子查询，具体的下面将会讲到
        SELECT *
        FROM EMP as e
        WHERE exists(SELECT 1
                     FROM EMP
                    WHERE EMP.MGR = e.EMPNO);

    • 试思考下面两个问题，根据提示理解一下，也可以实际操作验证一下

      • =some 等价于 in， 然而 <>some不等价于not in（<>some表示的是只要不等于结果集中的任意元素即可，可想而知，只要结果集中有大于一个元素，这个条件是恒成立的）
      • <>all 等价于 not in，然而=all不等价于in

  • 相关子查询

    • 慎用，因为一旦使用相关子查询，子查询语句可能会被执行很多次，很影响效率

    • 举个栗子

      1
      2
      3
      4
      5
      6
      7
      8
      9
      --下面的语句找出了所有比自己部门平均工资高的员工的信息
      --检索时，每检索一行数据，子查询语句就要被执行一次
      --（因为该子查询语句只有在某个具体的部门号下才会有结果
      --而，要获取部门号，就必须依赖于主查询语句中的某行具体数据）
      SELECT ENAME, SAL, DEPTNO
      FROM EMP e
      WHERE SAL > (SELECT avg(EMP.SAL)
                   FROM EMP
                  WHERE e.DEPTNO = EMP.DEPTNO);

    • 举个上面举过的栗子

      1
      2
      3
      4
      5
      6
      7
      --下面的语句就查出了所有手底下管了人的经理的信息
      --用exists时，只要找到数据即会返回，不会继续向下检索
      SELECT *
      FROM EMP as e
      WHERE exists(SELECT 1
                   FROM EMP
                  WHERE EMP.MGR = e.EMPNO);

    由于exists会在找到数据后立即返回，而不是继续向下检索，所以用exists的相关子查询效率会稍微高一丢丢

    • 可以自己验证一下下面结论
      • not exists 不受空值的影响
      • not in 受空值的影响
坚持原创技术分享，您的支持将鼓励我继续创作！