寫在前面：本篇博客大部分內(nèi)容參考數(shù)據(jù)庫系統(tǒng)概念（本科教學(xué)版）第三章(第三章部分的多表操作沒有在此處講亭珍，準(zhǔn)備挪到第四章再一起討論)
筆者接下來的代碼示例會主要在SQL Server數(shù)據(jù)庫中測試

在開始今天的摸魚大業(yè)之前，讓我們先構(gòu)造一組表筝闹，用于演示下面的例子(′`)

BONUS.png

DEPT.png

EMP.png

SALGRADE.png

Having 子句

由于where子句中不能用聚合函數(shù)（組函數(shù)）做條件航夺，故引入having子句

• 讓我們先看一下各個(gè)子句的書寫順序（下面的只是我們一般在寫SQL語句時(shí)候的書寫順序）
  • SELECT
  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY
  • 上面各子句的執(zhí)行順序：FROM-->WHERE-- > GROUP BY--> HAVING-->SELECT-->ORDER BY

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

• 從上面的討論中可以看出
  • HAVING一般緊隨GROUP BY之后阳掐，置于ORDER BY之前
  • 而且當(dāng)聚集函數(shù)（組函數(shù)）做條件時(shí)始衅，只用用HAVING來處理
• 舉個(gè)栗子：求所有平均工資大于2000的部門的部門號以及該部門的平均工資
  • 先來看看每個(gè)部門的平均工資

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

    得到如下結(jié)果
  • 1.png
  • 接下來開始求題設(shè)的問題

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

    得到如下結(jié)果
  • 2.png

聚合函數(shù)的嵌套

• 不同數(shù)據(jù)庫對聚集函數(shù)嵌套的規(guī)定不同
  • Oracle數(shù)據(jù)庫規(guī)定最多只能有兩層嵌套
  • SQL Server不允許聚集函數(shù)嵌套
• 舉個(gè)栗子：

  --下面的語句在Oracle數(shù)據(jù)庫中執(zhí)行是可以的
  SELECT max(avg(sal))
  FROM EMP
  GROUP BY deptno
  
  --上面的語句在SQL Server中執(zhí)行會報(bào)下面的錯(cuò)
  [S0001][130] 不能對包含聚合或子查詢的表達(dá)式執(zhí)行聚合函數(shù)冷蚂。
  

• ┐(′?_?`)┌ 實(shí)際上二層以上的嵌套也是毫無意義的（只是瞎嗶嗶，不是什么重點(diǎn)汛闸，有助于理解為什么聚集函數(shù)多層嵌套是沒有意義的）
  • 首先回憶一下蝙茶，聚集函數(shù)的操作是多行輸入，得到一行輸出
  • 舉個(gè)栗子

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

  • 現(xiàn)在來分析上面語句執(zhí)行的過程
    • （avg）首先根據(jù)部門號deptno分成若干組诸老，并計(jì)算每組的平均工資（返回若干條記錄）
    • （max）然后取各部門最高的平均工資（返回一條記錄）
    • （max）這個(gè)時(shí)候只有一條記錄隆夯，取最大值也還是那一條記錄

子查詢

這部分是重點(diǎn)也是難點(diǎn)呀?(?`^′?)?，不用擔(dān)心别伏，讓筆者帶你細(xì)細(xì)解剖(?_? )
這部分參考了兩份筆記和課本

• 嵌套子查詢

  即SQL標(biāo)準(zhǔn)提供嵌套子查詢機(jī)制蹄衷，允許將一個(gè)完整的select-from-where查詢表達(dá)式嵌入到另一個(gè)查詢語句中

• 子查詢嵌套在where和having子句中的情況（把子查詢整體當(dāng)做一組值，可以是0個(gè)畸肆，1個(gè)宦芦，或多個(gè)）

  • 單行子查詢
    • 在單行子查詢中，子查詢語句只能返回一行數(shù)據(jù)
    • 當(dāng)where子句中用下列邏輯符號連接子查詢時(shí)轴脐，便只能連接單行子查詢
      • =
      • <
      • >
      • <=
      • >=
      • <>
    • 舉個(gè)栗子(?_? )
      • 我們現(xiàn)在要找所有員工中调卑，拿到最高工資的人的名字以及工資

      SELECT ENAME, SAL
      FROM EMP
      WHERE SAL = (SELECT max(SAL)
                   FROM EMP);
      

      得到如下結(jié)果
    • 3.png
  • 多行子查詢
    • 多行子查詢中，子查詢語句可以返回多行數(shù)據(jù)
    • 當(dāng)where子句中用下列關(guān)鍵字連接子查詢時(shí)大咱，便是多行子查詢($代表上面提到的=, >等符號)
      • $ any/some
      • $ all
    • 舉幾個(gè)簡單的栗子
      • < any(...) 表示小于子查詢結(jié)果集中的最大值
      • > any(...) 表示大于子查詢結(jié)果集中的最小值
      • = any(...) 等價(jià)于in
      • = all(...) 沒有語法錯(cuò)誤恬涧，但是只要子查詢結(jié)果集的個(gè)數(shù)大于1，則整體的結(jié)果必然為空
      • < all(...) 表示小于子查詢結(jié)果集中的最小值
      • > all(...) 表示大于子查詢結(jié)果集中的最大值
    • 來舉個(gè)正經(jīng)的栗子
      • 求比部門號為20的部門的所有員工的工資都要高的員工的名字和工資

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

      得到如下結(jié)果
    • 4.png
    • 還有一個(gè)要注意的問題(?_?)碴巾，就是子查詢中SELECT后面的字段要根據(jù)主查詢語句中的字段來確定（簡單的說就是子查詢語句前面溯捆，where后面，出現(xiàn)了什么字段厦瓢，子查詢語句的SELECT列表中理論上就應(yīng)該出現(xiàn)這些字段）
      • 舉個(gè)栗子

      --下面這個(gè)語句是匹配的
      SELECT ENAME, SAL
      FROM EMP
      WHERE SAL > SOME (SELECT SAL
                      FROM EMP
                      WHERE DEPTNO = 10);
      --下面這個(gè)語句就不匹配的
      --因?yàn)镋NAM并沒有出現(xiàn)在主查詢語句中
      SELECT ENAME, SAL
      FROM EMP
      WHERE SAL > SOME (SELECT ENAME, SAL
                      FROM EMP
                      WHERE DEPTNO = 10);
      

      • 其實(shí)換個(gè)角度想想提揍，具體執(zhí)行的時(shí)候是將where后面的字段與子查詢語句的結(jié)果集進(jìn)行比較，如果字段都不一樣煮仇，就沒有可比性了
    • 對了劳跃，還有一個(gè)補(bǔ)充，not in + 子查詢浙垫，如果子查詢的結(jié)果集中包含NULL值刨仑，那整體的結(jié)果必為空。因?yàn)镹ULL值是不確定的值夹姥，誰也不能保證某個(gè)具體的值是否等于NULL杉武。。辙售。
  • 子查詢中存在性的判別可以用exists關(guān)鍵字
    • 舉個(gè)栗子就好

    --下面的語句就查出了所有手底下管了人的經(jīng)理的信息
    --其實(shí)這里已經(jīng)用到了相關(guān)子查詢轻抱，具體的下面將會講到
    SELECT *
    FROM EMP as e
    WHERE exists(SELECT 1
                 FROM EMP
                WHERE EMP.MGR = e.EMPNO);
    

  • 試思考下面兩個(gè)問題，根據(jù)提示理解一下旦部，也可以實(shí)際操作驗(yàn)證一下
    • =some 等價(jià)于 in十拣， 然而 <>some不等價(jià)于not in（<>some表示的是只要不等于結(jié)果集中的任意元素即可封拧，可想而知，只要結(jié)果集中有大于一個(gè)元素夭问，這個(gè)條件是恒成立的）
    • <>all 等價(jià)于 not in，然而=all不等價(jià)于in

• 相關(guān)子查詢

  • 慎用曹铃，因?yàn)橐坏┦褂孟嚓P(guān)子查詢缰趋，子查詢語句可能會被執(zhí)行很多次，很影響效率
  • 舉個(gè)栗子

  --下面的語句找出了所有比自己部門平均工資高的員工的信息
  --檢索時(shí)陕见，每檢索一行數(shù)據(jù)秘血，子查詢語句就要被執(zhí)行一次
  --（因?yàn)樵撟硬樵冋Z句只有在某個(gè)具體的部門號下才會有結(jié)果
  --而，要獲取部門號评甜，就必須依賴于主查詢語句中的某行具體數(shù)據(jù)）
  SELECT ENAME, SAL, DEPTNO
  FROM EMP e
  WHERE SAL > (SELECT avg(EMP.SAL)
               FROM EMP
              WHERE e.DEPTNO = EMP.DEPTNO);
  

  • 舉個(gè)上面舉過的栗子

  --下面的語句就查出了所有手底下管了人的經(jīng)理的信息
  --用exists時(shí)灰粮，只要找到數(shù)據(jù)即會返回，不會繼續(xù)向下檢索
  SELECT *
  FROM EMP as e
  WHERE exists(SELECT 1
               FROM EMP
              WHERE EMP.MGR = e.EMPNO);
  

  由于exists會在找到數(shù)據(jù)后立即返回忍坷，而不是繼續(xù)向下檢索粘舟，所以用exists的相關(guān)子查詢效率會稍微高一丟丟

  • 可以自己驗(yàn)證一下下面結(jié)論
    • not exists 不受空值的影響
    • not in 受空值的影響