写在前面：本篇博客大部分内容参考数据库系统概念（本科教学版）第三章(第三章部分的多表操作没有在此处讲，准备挪到第四章再一起讨论)
笔者接下来的代码示例会主要在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的部门的部门号以及该部门的平均工资
  • 先来看看每个部门的平均工资

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

    得到如下结果
  • 1.png
  • 接下来开始求题设的问题

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

    得到如下结果
  • 2.png

聚合函数的嵌套

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

  --下面的语句在Oracle数据库中执行是可以的
  SELECT max(avg(sal))
  FROM EMP
  GROUP BY deptno
  
  --上面的语句在SQL Server中执行会报下面的错
  [S0001][130] 不能对包含聚合或子查询的表达式执行聚合函数。
  

• ┐(´•_•`)┌ 实际上二层以上的嵌套也是毫无意义的（只是瞎哔哔，不是什么重点，有助于理解为什么聚集函数多层嵌套是没有意义的）
  • 首先回忆一下，聚集函数的操作是多行输入，得到一行输出
  • 举个栗子

    SELECT max(max(avg(sal)))
    FROM EMP
    GROUP BY deptno
    

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

子查询

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

• 嵌套子查询

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

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

  • 单行子查询
    • 在单行子查询中，子查询语句只能返回一行数据
    • 当where子句中用下列逻辑符号连接子查询时，便只能连接单行子查询
      • =
      • <
      • >
      • <=
      • >=
      • <>
    • 举个栗子(≖_≖ )
      • 我们现在要找所有员工中，拿到最高工资的人的名字以及工资

      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的部门的所有员工的工资都要高的员工的名字和工资

      SELECT ENAME, SAL
      FROM EMP
      WHERE SAL > SOME (SELECT SAL
                      FROM EMP
                      WHERE DEPTNO = 10);
      

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

      --下面这个语句是匹配的
      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关键字
    • 举个栗子就好

    --下面的语句就查出了所有手底下管了人的经理的信息
    --其实这里已经用到了相关子查询，具体的下面将会讲到
    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

• 相关子查询

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

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

  • 举个上面举过的栗子

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

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

  • 可以自己验证一下下面结论
    • not exists 不受空值的影响
    • not in 受空值的影响