聊聊MySQL中的聚合函數，實踐掌握分頁查詢！

MySQL聚合函數和分頁查詢

參考連結：#MySQL資料庫（mysql安裝/基礎/高階/優化）

https://www.bilibili.com/video/BV1iq4y1u7vj

我們在之前瞭解到了 SQL 單行函數。實際上 SQL 函數還有一類，叫做聚合（或聚集、分組）函數，它是對一組資料進行彙總的函數，輸入的是一組資料的集合，輸出的是單個值。【相關推薦：】

1. 聚合函數介紹

什麼是聚合函數

聚合函數作用於一組資料，並對一組資料返回一個值。

聚合函數型別

• AVG()
• SUM()
• MAX()
• MIN()
• COUNT()

聚合函數語法

聚合函數不能巢狀呼叫

比如不能出現類似「AVG(SUM(欄位名稱))」形式的呼叫。

1.1 AVG和SUM函數

可以對數值型資料使用AVG 和 SUM 函數。

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

1.2 MIN和MAX函數

可以對任意資料型別的資料使用 MIN 和 MAX 函數。

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

1.3 COUNT函數

• COUNT(*)返回表中記錄總數，適用於任意資料型別。

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

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

SELECT COUNT(commission_pct)
FROM   employees
WHERE  department_id = 50; //忽略了Null值

計算表中有多少條記錄

• 方式1：count(*)
• 方式2：count(1)
• 方式3：count(某具體欄位)，但是因為忽略了null值，所以不一定對

問題：用count(*)，count(1)，count(列名)誰好呢?

其實，對於MyISAM引擎的表是沒有區別的。這種引擎內部有一計數器在維護著行數，但是COUNT(*)的效率略高

Innodb引擎的表用count(*)，count(1)直接讀行數，複雜度是O(n)，因為innodb真的要去數一遍。但好於具體的count(列名)。

問題：能不能使用count(列名)替換count(*)?

不要使用 count(列名)來替代 count(*)，count(*)是 SQL92 定義的標準統計行數的語法，跟資料庫無關，跟 NULL 和非 NULL 無關。

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

注意：

• 以上分組函數都忽略null值

• 可以和distinct搭配實現去重的運算

• count函數的單獨介紹，一般使用count(*)用作統計行數

• 和分組函數一同查詢的欄位要求是group by後的欄位

2. GROUP BY

2.1 基本使用

可以使用GROUP BY子句將表中的資料分成若干組，語法如下：

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

明確：WHERE一定放在FROM後面

1、 在SELECT列表中所有未包含在組函數中的列都應該包含在 GROUP BY子句中

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

2、包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

SELECT   AVG(salary)
FROM     employees
GROUP BY department_id ;

2.2 使用多個列分組

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

2.3 GROUP BY中使用WITH ROLLUP

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

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

注意： 當使用ROLLUP時，不能同時使用ORDER BY子句進行結果排序，即ROLLUP和ORDER BY是互相排斥的，當然這是隻在5.7才存在的

3. HAVING(過濾資料)

3.1 基本使用

過濾分組：HAVING子句

• 行已經被分組。

• 使用了聚合函數。

• 滿足HAVING 子句中條件的分組將被顯示。

• HAVING 不能單獨使用，必須要跟 GROUP BY 一起使用。

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

非法使用聚合函數 ： 不能在 WHERE 子句中使用聚合函數來代替過濾條件。如下：

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

練習：查詢部門id為10,20,30,40這4個部門中最高工資比10000高的部門資訊

#方式1：推薦，執行效率高於方式2.
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;

#方式2：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);

結論：

• 當過濾條件中有聚合函數時，則此過濾條件必須宣告在HAVING中。

• 當過濾條件中沒有聚合函數時，則此過濾條件宣告在WHERE中或HAVING中都可以。但是，建議大家宣告在WHERE中

3.2 WHERE和HAVING的對比

1. 從適用範圍上來講，HAVING的適用範圍更廣。 
2. 如果過濾條件中沒有聚合函數：這種情況下，WHERE的執行效率要高於HAVING

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

這決定了，在需要對資料進行分組統計的時候，HAVING 可以完成 WHERE 不能完成的任務。這是因為，在查詢語法結構中，WHERE 在 GROUP BY 之前，所以無法對分組結果進行篩選。HAVING 在 GROUP BY 之後，可以使用分組欄位和分組中的計算函數，對分組的結果集進行篩選，這個功能是 WHERE 無法完成的。另外，WHERE排除的記錄不再包括在分組中。

區別2：如果需要通過連線從關聯表中獲取需要的資料，WHERE 是先篩選後連線，而 HAVING 是先連線後篩選。 這一點，就決定了在關聯查詢中，WHERE 比 HAVING 更高效。因為 WHERE 可以先篩選，用一個篩選後的較小資料集和關聯表進行連線，這樣佔用的資源比較少，執行效率也比較高。HAVING 則需要先把結果集準備好，也就是用未被篩選的資料集進行關聯，然後對這個大的資料集進行篩選，這樣佔用的資源就比較多，執行效率也較低。

小結如下：

開發中的選擇：

WHERE 和 HAVING 也不是互相排斥的，我們可以在一個查詢裡面同時使用 WHERE 和 HAVING。包含分組統計函數的條件用 HAVING，普通條件用 WHERE。這樣，我們就既利用了 WHERE 條件的高效快速，又發揮了 HAVING 可以使用包含分組統計函數的查詢條件的優點。當資料量特別大的時候，執行效率會有很大的差別。一般來講，能用分組前篩選的，儘量使用分組前篩選，提高效率

4. 回顧：分頁查詢 ★

應用場景：當要顯示的資料，一頁顯示不全，需要分頁提交sql請求

語法：

  select 查詢列表
  from 表
  【join type join 表2
  on 連線條件
  where 篩選條件
  group by 分組欄位
  having 分組後的篩選
  order by 排序的欄位】
  limit 【offset,】size;
  offset 要顯示條目的起始索引（起始索引從0開始）
  size 要顯示的條目個數

特點：

• limit語句放在查詢語句的最後

• 公式

  select 查詢列表
  from 表
  limit (page-1)*size,size;

假設size=10，即每頁顯示10條記錄，page從1開始，即第一頁

• page=1，則顯示條目的起始索引為0，頁面顯示0-10條
• page=2，則顯示條目的起始索引為10，頁面顯示11-20條
• page=3，則顯示條目的起始索引為20，頁面顯示21-30條

案例1：查詢前五條員工資訊

SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;

案例2：查詢第11條——第25條

SELECT * FROM employees LIMIT 10,15;

案例3： 有獎金的員工資訊，並且工資較高的前10名顯示出來

SELECT *
FROM employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC
LIMIT 10 ;

5. SELECT的執行過程

5.1 SELECT語句的完整結構

#方式1：sql92語法：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的連線條件
AND 不包含組函數的過濾條件
GROUP BY ...,...
HAVING 包含組函數的過濾條件
ORDER BY ... ASC/DESC
LIMIT ...,...

#方式2：sql99語法
SELECT ...,....,...
FROM ... JOIN ... 
ON 多表的連線條件
JOIN ...
ON ...
WHERE 不包含組函數的過濾條件
AND/OR 不包含組函數的過濾條件
GROUP BY ...,...
HAVING 包含組函數的過濾條件
ORDER BY ... ASC/DESC
LIMIT ...,...

#其中：
#（1）from：從哪些表中篩選
#（2）on：關聯多表查詢時，去除笛卡爾積
#（3）where：從表中篩選的條件
#（4）group by：分組依據
#（5）having：在統計結果中再次篩選
#（6）order by：排序
#（7）limit：分頁

5.2 SELECT執行順序

你需要記住 SELECT 查詢時的兩個順序：

1. 關鍵字的順序是不能顛倒的：

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

2.SELECT 語句的執行順序（在 MySQL 和 Oracle 中，SELECT 執行順序基本相同）：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的欄位 -> DISTINCT -> ORDER BY -> LIMIT

比如你寫了一個 SQL 語句，那麼它的關鍵字順序和執行順序是下面這樣的：

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 語句執行這些步驟的時候，每個步驟都會產生一個虛擬表，然後將這個虛擬表傳入下一個步驟中作為輸入。需要注意的是，這些步驟隱含在 SQL 的執行過程中，對於我們來說是不可見的。

從這裡的執行順序我們也看出來了，因為where是先篩選的，因此group by語句事先分組，參與分組的資料要少，因此執行效率要高

5.3 SQL 的執行原理

SELECT 是先執行 FROM 這一步的。在這個階段，如果是多張表聯查，還會經歷下面的幾個步驟：

• 首先先通過 CROSS JOIN 求笛卡爾積，相當於得到虛擬表 vt（virtual table）1-1；

• 通過 ON 進行篩選，在虛擬表 vt1-1 的基礎上進行篩選，得到虛擬表 vt1-2；

• 新增外部行。如果我們使用的是左連線、右連結或者全連線，就會涉及到外部行，也就是在虛擬表 vt1-2 的基礎上增加外部行，得到虛擬表 vt1-3。

當然如果我們操作的是兩張以上的表，還會重複上面的步驟，直到所有表都被處理完為止。這個過程得到是我們的原始資料。

當我們拿到了查詢資料表的原始資料，也就是最終的虛擬表 vt1，就可以在此基礎上再進行 WHERE 階段。在這個階段中，會根據 vt1 表的結果進行篩選過濾，得到虛擬表 vt2。

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

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

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

當我們提取了想要的欄位資料之後，就可以按照指定的欄位進行排序，也就是 ORDER BY 階段，得到虛擬表 vt6。

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

當然我們在寫 SELECT 語句的時候，不一定存在所有的關鍵字，相應的階段就會省略。

同時因為 SQL 是一門類似英語的結構化查詢語言，所以我們在寫 SELECT 語句的時候，還要注意相應的關鍵字順序，**所謂底層執行的原理，就是我們剛才講到的執行順序。**更細緻的內容參考後續的高階篇架構

6. 課後練習

綜合練習1

1.where子句可否使用組函數進行過濾? No

2.查詢公司員工工資的最大值，最小值，平均值，總和

SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees;

3.查詢各job_id的員工工資的最大值，最小值，平均值，總和

SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees
GROUP BY job_id;

4.選擇具有各個job_id的員工人數

SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;

5.查詢員工最高工資和最低工資的差距（DIFFERENCE）

SELECT MAX(salary), MIN(salary), MAX(salary) - MIN(salary) DIFFERENCE
FROM employees;

6.查詢各個管理者手下員工的最低工資，其中最低工資不能低於6000，沒有管理者的員工不計算在內

SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) > 6000;

7.查詢所有部門的名字，location_id，員工數量和平均工資，並按平均工資降序

SELECT department_name, location_id, COUNT(employee_id), AVG(salary) avg_sal
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY department_name, location_id
ORDER BY avg_sal DESC;

8.查詢每個工種、每個部門的部門名、工種名和最低工資

SELECT department_name,job_id,MIN(salary)
FROM departments d LEFT JOIN employees e
ON e.`department_id` = d.`department_id`
GROUP BY department_name,job_id

綜合練習2

1.簡單的分組

案例1：查詢每個工種的員工平均工資

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

案例2：查詢每個位置的部門個數

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

2.可以實現分組前的篩選

案例1：查詢郵箱中包含a字元的 每個部門的最高工資

SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

案例2：查詢有獎金的每個領導手下員工的平均工資

SELECT AVG(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

3.分組後篩選

案例1：查詢哪個部門的員工個數>5

#①查詢每個部門的員工個數
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;

#② 篩選剛才①結果
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;

案例2：每個工種有獎金的員工的最高工資>12000的工種編號和最高工資

SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

案例3：領導編號>102的每個領導手下的最低工資大於5000的領導編號和最低工資

SELECT manager_id,MIN(salary)
FROM employees
GROUP BY manager_id
Where manager_id>102
HAVING MIN(salary)>5000;

4.新增排序

案例：每個工種有獎金的員工的最高工資>6000的工種編號和最高工資,按最高工資升序

SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;

5.按多個欄位分組

案例：查詢每個工種每個部門的最低工資,並按最低工資降序

SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;

更多程式設計相關知識，請存取：！！

以上就是聊聊MySQL中的聚合函數，實踐掌握分頁查詢！的詳細內容，更多請關注TW511.COM其它相關文章！