MySQL-08.聚合函數和SQL(DQL)的執行流程

C-08 聚合(分組)函數和SQL(DQL)執行流程上一章，講到了SQL單行函數。實際上SQL函數還有一類，叫做聚合函數，或者多行，分組函數，它是對一組數據進行彙總的函數，輸入的是一組數據的集合，輸出的是單個值。 1.聚合函數的介紹什麼是聚合函數聚合函數作用於一組數據，並對一組數據返回一個值。 ...

C-08 聚合(分組)函數和SQL(DQL)執行流程

上一章，講到了SQL單行函數。實際上SQL函數還有一類，叫做聚合函數，或者多行，分組函數，它是對一組數據進行彙總的函數，輸入的是一組數據的集合，輸出的是單個值。

1.聚合函數的介紹

什麼是聚合函數
- 聚合函數作用於一組數據，並對一組數據返回一個值。
常用的聚合函數
- AVG()
- SUM()
- MAX()
- MIN()
- COUNT()
聚合函數語法

/*
SELECT [column,] group function(column),...
FROM table_name
[WHERE condition]
GROUP BY column,
[ORDER BY column];
*/

聚合函數不能嵌套調用。比如不能出現類似AVG(SUM(列名))形式的調用。

1.1 AVG和SUM函數

只能對數值型的數據，使用AVG和SUM函數

mysql> SELECT AVG(salary),SUM(salary),MAX(salary),MIN(salary)
    -> FROM employees;
+-------------+-------------+-------------+-------------+
| AVG(salary) | SUM(salary) | MAX(salary) | MIN(salary) |
+-------------+-------------+-------------+-------------+
| 6461.682243 |   691400.00 |    24000.00 |     2100.00 |
+-------------+-------------+-------------+-------------+
1 row in set (0.00 sec)

1.2 MIN和MAX函數

可以對任意類型的數據使用MIN和MAX函數，但是常用於數值型和字元類型，以及日期時間類型。

mysql> SELECT MIN(hire_date),MAX(hire_date)
    -> FROM employees;
+----------------+----------------+
| MIN(hire_date) | MAX(hire_date) |
+----------------+----------------+
| 1987-06-17     | 2000-04-21     |
+----------------+----------------+
1 row in set (0.00 sec)

1.3 COUNT函數

COUNT(*)返回表中記錄總數，適用於任意數據類型。

mysql> SELECT COUNT(*) FROM employees;# COUNT(1)的效果類似於COUNT(*)，相當於把一行數據看作1去計數
+----------+
| COUNT(*) |
+----------+
|      107 |
+----------+
1 row in set (0.00 sec

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

mysql> SELECT COUNT(commission_pct) FROM employees;
+-----------------------+
| COUNT(commission_pct) |
+-----------------------+
|                    35 |
+-----------------------+
1 row in set (0.00 sec)

COUNT(*)，COUNT(1)，COUNT(列名)那個好呢？
其實，對於MyISAM存儲引擎的表是沒有區別，這種引擎內部有一個計數器在維護者表的行數。
Innodb引擎的表用COUNT(*)，COUNT(1)直接讀行數，複雜度是O(n)，因為Innodb真的要去讀一遍，但好於具體的COUNT(列名)。
能不能使用COUNT(列名)替換COUNT(*)
不要使用COUNT(列名)來替代COUNT(*)，COUNT(*)是SQL92定義的標準統計行數的語法，和資料庫無關，和NULL和非NULL無關。COUNT(*)會統計值為NULL的行，而COUNT(列名)只會統計列名不為NULL值的行。

2.GROUP BY

2.1 基本使用

可以使用GROUP BY子句將表中的數據分成若幹組。

/*
SELECT [column,] group function(column),...
FROM table_name
[WHERE condition]
[GROUP BY group_by_expression,]
[ORDER BY column];
*/

註意：

GROUP BY子句一定要在WHERE後面。
在SELECT列表中所有未包含在組函數中的列，都應該包含在GROUP BY子句中。而包含在GROUP BY子句中的列不必包含在SELECT列表中。

舉例：

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

SELECT AVG(salary)
FROM employees
GROUP BY department_id;

2.2 使用多列分組

SELECT department_id,job_id,SUM(salary)
FROM employees
GROUP BY department_id,job_id;
-- GROUP BY job_id,department_id;結果一樣
-- 和排序子句不同，順序不一樣也不會對結果有影響

2.3 GROUP BY中使用WITH ROLLUP

使用WITH ROLLUP關鍵字之後，在所有查詢出的分組記錄之後增加一條記錄，該記錄計算查詢出所有記錄的總和，即統計記錄數量。
但是這個總和，和使用的分組函數有關。

mysql> SELECT department_id,AVG(salary)
    -> FROM employees
    -> WHERE department_id > 80
    -> GROUP BY department_id WITH ROLLUP;
+---------------+--------------+
| department_id | AVG(salary)  |
+---------------+--------------+
|            90 | 19333.333333 |
|           100 |  8600.000000 |
|           110 | 10150.000000 |
|          NULL | 11809.090909 |
+---------------+--------------+
4 rows in set (0.00 sec)

3.HAVING

3.1 基本使用

過濾分組，HAVING子句

1.行已經被分組
2.使用了聚合函數
3.滿足了HAVING子句中條件的分組將被顯示
4.HAVING不能單獨使用，必須要跟GROUP BY一起使用

/*
SELECT [column,] group function(column),...
FROM table_name
[WHERE condition]
[GROUP BY group_by_expression,]
[HAVING group_condition,]
[ORDER BY column];
*/

mysql> SELECT department_id,MAX(salary)
    -> FROM employees
    -> GROUP BY department_id
    -> HAVING MAX(salary) > 10000;
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
|            20 |    13000.00 |
|            30 |    11000.00 |
|            80 |    14000.00 |
|            90 |    24000.00 |
|           100 |    12000.00 |
|           110 |    12000.00 |
+---------------+-------------+
6 rows in set (0.00 sec)

不能再WHERE子句中使用聚合函數

mysql> SELECT department_id,MAX(salary)
    -> FROM employees
    -> WHERE MAX(salary) > 10000
    -> GROUP BY department_id;
ERROR 1111 (HY000): Invalid use of group function

3.2 WHERE和HAVING的對比

區別1：

WHER可以直接使用表中的欄位作為篩選條件，但不能使用分組中的計算函數作為篩選條件，HAVING必須要與GROUP BY配合使用，可以把分組計算的函數和分組欄位作為篩選條件。

區別2：

如果需要通過連接從關聯表中獲取需要的數據，WHERE是先篩選後連接，而HAVING是先連接後篩選。原因，再SQL的執行過程中會解釋。

開發中的選擇

WHERE和HAVING也不是互相互斥的，我們可以在一個查詢裡面同時使用WHERE和HAVING。包含分組統計函數的條件用HAVING，普通條件用WHERE，普通條件用WHERE。這樣，才能發貨WHERE的高效快速，又發揮HAVING可以使用包含分組統計函數的查詢條件的優點。當數據量很大的時候，運行效率會有很大的區別。

4.SELECT的執行過程

4.1 查詢的結構

/*
方式1
SELECT ...,...,...
FROM ...,...,...
WHERE 表的連接條件 AND 不包含分組函數的過濾條件
GROUP BY 分組列名
HAVING 包含分組函數的過濾條件
ORDER BY ... ASC/DESC
LIMIT ...,...;

方式2
SELECT ...,...,...
FROM ... (LEFT / RIGHT OUTER) | (INNER)JOIN ... ON 連接條件
JOIN ... ON 連接條件
WHERE　不包含分組函數的過濾條件
GROUP BY 分組列名
HAVING 包含分組函數的過濾條件
ORDER BY ... ASC/DESC
LIMIT ...,...;
*/

4.2 SELECT 執行順序

/* 
執行順序，FROM 子句，從哪些表裡查詢 -> WHERE / ON 使用表的連接條件進行過濾數據 -> 使用WHERE中的非連接條件過濾數據 ->
GROUP BY 對數據進行分組 -> HAVING 對分組後的數據進行過濾 -> SELECT 行過濾出顯示的列 -> DISTINCT 有的話去重過濾 ->
ORDER BY 對數據進行排序 -> LIMIT進行分頁
*/
-- 這裡解釋，為什麼將不包含分組函數的條件寫在WHERE會比HAVING中好，是因為，WHERE先執行，先過濾不滿足的數據，可能會讓數據量變小
-- 後續的執行就處理的數據量小，效率自然就高了
/*
關鍵字順序
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...;
*/

4.3 執行原理

SELECT 是先執行FROM這一子句，在這個階段，如果是多張表聯合查詢，還會經歷下麵的步驟：

1.首先先通過CROSS JOIN 求笛卡爾積，相當於得到虛擬表vt(virtual table) 1-1;
2.通過ON進行篩選，在虛擬表上vt1-1的基礎上進行篩選，得到虛擬表vt1-2;
3.添加外部行。如果使用的是左連接，右連接，或者全連接就會涉及到外部行，也就是在虛擬vt1-3的基礎上，增加外部行，得到虛擬表vt1-3。

當然如果我們操作的是兩張以上的表，就會重覆上面1-3步驟，知道表多處理完畢，得到原始數據。

當拿到了查詢數據表的原始數據，也就是最終的虛擬表vt1，就可以在此基礎上進行WHERE階段，對vt1表的結果進行篩選過濾，得到虛擬表vt2。

然後進行第三步和第四步，也就是GROUP BY和HAVING階段。在這個階段中，實際上是在虛擬表vt2的基礎上進行分組和過濾，得到中甲你的虛擬表vt3和vt4。

當完成了條件篩選部分後，就可以篩選表中提取的欄位，也就是進入到SELECT和DISTINCT階段。

首先在SELECT階段會提取到想要的欄位，然後在DISTINCT階段過濾掉重覆的行，分別得到中間的虛擬表vt5-1和vt5-2。

當提取到想要展示的欄位數據之後，就可以按照指定的欄位進行排序，也就是ORDER BY階段，得到虛擬表vt6。

最後在vt6的基礎上，取出指定行的記錄，也就是LIMIT階段，得到最終的結果，對應的是虛擬表vt7。

當然SELECT語句，並不一定會寫所有的關鍵字，未寫的相應的欄位就會省略。

同時因為SQL是一門結構化語言，所以在寫SELECT語句時，要註意相應的關鍵字順序，執行原理，就是執行的順序。

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。