文章目錄

• • 1、聚合函數(shù)介紹
  • 2、五個常用聚合函數(shù)
  • 3、GROUP BY
  • 4、HAVING
  • 5、SELECT的執(zhí)行過程

---

1、聚合函數(shù)介紹

聚合函數(shù)作用于一組數(shù)據(jù)，并對一組數(shù)據(jù)返回一個值。聚合函數(shù)不能嵌套調(diào)用。比如不能出現(xiàn)類似“AVG(SUM(字段名稱))”形式的調(diào)用。

---

2、五個常用聚合函數(shù)

2.1 AVG和SUM函數(shù)
可以對數(shù)值型數(shù)據(jù)使用AVG（取平均）和SUM（求和）函數(shù)。（忽略NULL值行）

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';

2.2 MIN和MAX函數(shù)
可以對任意數(shù)據(jù)類型的數(shù)據(jù)使用 MIN 和 MAX 函數(shù)。

SELECT MIN(hire_date), MAX(hire_date) FROM employees;

2.3 COUNT函數(shù)
COUNT(*)返回表中記錄總數(shù)，適用于任意數(shù)據(jù)類型。

SELECT COUNT(*) FROM employees WHERE department_id = 50;

COUNT(expr) 返回expr不為空的記錄總數(shù)。

SELECT COUNT(commission_pct) FROM employees WHERE department_id = 50;

問題：用count(＊)，count(1)，count(列名)誰好呢?
其實，對于MyISAM引擎的表是沒有區(qū)別的。這種引擎內(nèi)部有一計數(shù)器在維護著行數(shù)。Innodb引擎的表用count(＊),count(1)直接讀行數(shù)，復雜度是O(n)，因為innodb真的要去數(shù)一遍。但好于具體的count(列名)。

問題：能不能使用count(列名)替換count(＊)?
不要使用 count(列名)來替代 count() ， count() 是 SQL92 定義的標準統(tǒng)計行數(shù)的語法，跟數(shù)據(jù)庫無關，跟 NULL 和非 NULL 無關。

說明：count(*)會統(tǒng)計值為 NULL 的行，而 count(列名)不會統(tǒng)計此列為 NULL 值的行。

---

3、GROUP BY

3.1 基本使用

可以使用GROUP BY子句將表中的數(shù)據(jù)分成若干組

SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];

明確：WHERE一定放在FROM后面

在SELECT列表中所有未包含在組函數(shù)中的列都應該包含在 GROUP BY子句中

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;

3.2 使用多個列分組

SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;

3.3 GROUP BY中使用WITH ROLLUP
使用 WITH ROLLUP 關鍵字之后，在所有查詢出的分組記錄之后增加一條記錄，該記錄計算查詢出的所有記錄的總和，即統(tǒng)計記錄數(shù)量。

SELECT department_id,AVG(salary) FROM employees WHERE department_id > 80 GROUP BY department_id WITH ROLLUP;

注意：
當使用ROLLUP時，不能同時使用ORDER BY子句進行結果排序，即ROLLUP和ORDER BY是互相排斥的。

---

4、HAVING

4.1 基本使用

過濾分組：HAVING子句
①行已經(jīng)被分組。
②使用了聚合函數(shù)。
③滿足HAVING 子句中條件的分組將被顯示。
④HAVING 不能單獨使用，必須要跟 GROUP BY 一起使用。

SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ;

非法使用聚合函數(shù) ：不能在 WHERE 子句中使用聚合函數(shù)。

4.2 WHERE和HAVING的對比
區(qū)別1：WHERE 可以直接使用表中的字段作為篩選條件，但不能使用分組中的計算函數(shù)作為篩選條件；HAVING 必須要與 GROUP BY 配合使用，可以把分組計算的函數(shù)和分組字段作為篩選條件。這決定了，在需要對數(shù)據(jù)進行分組統(tǒng)計的時候，HAVING 可以完成 WHERE 不能完成的任務。這是因為，在查詢語法結構中，WHERE 在 GROUP BY 之前，所以無法對分組結果進行篩選。HAVING 在 GROUP BY 之后，可以使用分組字段和分組中的計算函數(shù)，對分組的結果集進行篩選，這個功能是 WHERE 無法完成的。另外，WHERE排除的記錄不再包括在分組中。
區(qū)別2：如果需要通過連接從關聯(lián)表中獲取需要的數(shù)據(jù)，WHERE 是先篩選后連接，而 HAVING 是先連接后篩選。 這一點，就決定了在關聯(lián)查詢中，WHERE 比 HAVING 更高效。因為 WHERE 可以先篩選，用一個篩選后的較小數(shù)據(jù)集和關聯(lián)表進行連接，這樣占用的資源比較少，執(zhí)行效率也比較高。HAVING 則需要先把結果集準備好，也就是用未被篩選的數(shù)據(jù)集進行關聯(lián)，然后對這個大的數(shù)據(jù)集進行篩選，這樣占用的資源就比較多，執(zhí)行效率也較低。

開發(fā)中的選擇：
WHERE 和 HAVING 也不是互相排斥的，我們可以在一個查詢里面同時使用 WHERE 和 HAVING。包含分組統(tǒng)計函數(shù)的條件用 HAVING，普通條件用 WHERE。這樣，我們就既利用了 WHERE 條件的高效快速，又發(fā)揮了 HAVING 可以使用包含分組統(tǒng)計函數(shù)的查詢條件的優(yōu)點。當數(shù)據(jù)量特別大的時候，運行效率會有很大的差別。

---

5、SELECT的執(zhí)行過程

5.1 查詢的結構

#方式1： SELECT ...,....,... FROM ...,...,.... WHERE 多表的連接條件 AND 不包含組函數(shù)的過濾條件 GROUP BY ...,... HAVING 包含組函數(shù)的過濾條件 ORDER BY ... ASC/DESC LIMIT ...,... #方式2： SELECT ...,....,... FROM ... JOIN ... ON 多表的連接條件 JOIN ... ON ... WHERE 不包含組函數(shù)的過濾條件 AND/OR 不包含組函數(shù)的過濾條件 GROUP BY ...,... HAVING 包含組函數(shù)的過濾條件 ORDER BY ... ASC/DESC LIMIT ...,... #其中： #（1）from：從哪些表中篩選 #（2）on：關聯(lián)多表查詢時，去除笛卡爾積 #（3）where：從表中篩選的條件 #（4）group by：分組依據(jù) #（5）having：在統(tǒng)計結果中再次篩選 #（6）order by：排序 #（7）limit：分頁

5.2 SELECT執(zhí)行順序
①關鍵字的順序是不能顛倒的：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

②SELECT 語句的執(zhí)行順序：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT SELECT DISTINCT player_id, player_name, count(*) as num # 順序 5 FROM player JOIN team ON player.team_id = team.team_id # 順序 1 WHERE height > 1.80 # 順序 2 GROUP BY player.team_id # 順序 3 HAVING num > 2 # 順序 4 ORDER BY num DESC # 順序 6 LIMIT 2 # 順序 7

在 SELECT 語句執(zhí)行這些步驟的時候，每個步驟都會產(chǎn)生一個 虛擬表 ，然后將這個虛擬表傳入下一個步驟中作為輸入。需要注意的是，這些步驟隱含在 SQL 的執(zhí)行過程中，對于我們來說是不可見的。

5.3 SQL 的執(zhí)行原理
SELECT 是先執(zhí)行 FROM 這一步的。在這個階段，如果是多張表聯(lián)查，還會經(jīng)歷下面的幾個步驟：
①首先先通過 CROSS JOIN 求笛卡爾積，相當于得到虛擬表 vt（virtualtable）1-1；
②通過 ON 進行篩選，在虛擬表 vt1-1 的基礎上進行篩選，得到虛擬表 vt1-2；
③添加外部行。如果我們使用的是左連接、右鏈接或者全連接，就會涉及到外部行，也就是在虛擬表 vt1-2 的基礎上增加外部行，得到虛擬表 vt1-3。
當然如果我們操作的是兩張以上的表，還會重復上面的步驟，直到所有表都被處理完為止。這個過程得到是我們的原始數(shù)據(jù)。當我們拿到了查詢數(shù)據(jù)表的原始數(shù)據(jù)，也就是最終的虛擬表 vt1 ，就可以在此基礎上再進行 WHERE 階段 。在這個階段中，會根據(jù) vt1 表的結果進行篩選過濾，得到虛擬表 vt2 。然后進入第三步和第四步，也就是 GROUP 和 HAVING 階段 。在這個階段中，實際上是在虛擬表 vt2 的基礎上進行分組和分組過濾，得到中間的虛擬表 vt3 和 vt4 。當我們完成了條件篩選部分之后，就可以篩選表中提取的字段，也就是進入到 SELECT 和 DISTINCT階段 。首先在 SELECT 階段會提取想要的字段，然后在 DISTINCT 階段過濾掉重復的行，分別得到中間的虛擬表vt5-1 和 vt5-2 。當我們提取了想要的字段數(shù)據(jù)之后，就可以按照指定的字段進行排序，也就是ORDER BY階段 ，得到虛擬表vt6。最后在 vt6 的基礎上，取出指定行的記錄，也就是 LIMIT 階段 ，得到最終的結果，對應的是虛擬表vt7 。當然我們在寫 SELECT 語句的時候，不一定存在所有的關鍵字，相應的階段就會省略。

總結

以上是生活随笔為你收集整理的SELECT执行过程，MySQL聚合函数，多行分组函数，GROUP BY HAVING，详细完整可收藏的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。