聊聊MySQL中的聚合函數(shù)，實(shí)踐掌握分頁查詢！

本篇文章帶大家了解一下mysql內(nèi)置函數(shù)中的聚合函數(shù)，并了解一下怎么進(jìn)行分頁查詢，希望對(duì)大家有所幫助。

mysql聚合函數(shù)和分頁查詢

參考鏈接：#MySQL數(shù)據(jù)庫（mysql安裝/基礎(chǔ)/高級(jí)/優(yōu)化）https://www.bilibili.com/video/BV1iq4y1u7vj

我們?cè)谥傲私獾搅?SQL 單行函數(shù)。實(shí)際上 SQL 函數(shù)還有一類，叫做聚合（或聚集、分組）函數(shù)，它是對(duì)一組數(shù)據(jù)進(jìn)行匯總的函數(shù)，輸入的是一組數(shù)據(jù)的集合，輸出的是單個(gè)值。【相關(guān)推薦：mysql視頻教程】

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

什么是聚合函數(shù)

聚合函數(shù)作用于一組數(shù)據(jù)，并對(duì)一組數(shù)據(jù)返回一個(gè)值。

聚合函數(shù)類型

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

聚合函數(shù)語法

聚合函數(shù)不能嵌套調(diào)用

比如不能出現(xiàn)類似“AVG(SUM(字段名稱))”形式的調(diào)用。

1.1 AVG和SUM函數(shù)

可以對(duì)數(shù)值型數(shù)據(jù)使用AVG 和 SUM 函數(shù)。

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

1.2 MIN和MAX函數(shù)

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

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

1.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;?//忽略了Null值

計(jì)算表中有多少條記錄

• 方式1：count(*)
• 方式2：count(1)
• 方式3：count(某具體字段)，但是因?yàn)楹雎粤薾ull值，所以不一定對(duì)

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

其實(shí)，對(duì)于MyISAM引擎的表是沒有區(qū)別的。這種引擎內(nèi)部有一計(jì)數(shù)器在維護(hù)著行數(shù)，但是COUNT(*)的效率略高

Innodb引擎的表用count(*)，count(1)直接讀行數(shù)，復(fù)雜度是O(n)，因?yàn)閕nnodb真的要去數(shù)一遍。但好于具體的count(列名)。

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

不要使用 count(列名)來替代 count(*)，count(*)是 SQL92 定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語法，跟數(shù)據(jù)庫無關(guān)，跟 NULL 和非 NULL 無關(guān)。

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

注意：

• 以上分組函數(shù)都忽略null值

• 可以和distinct搭配實(shí)現(xiàn)去重的運(yùn)算

• count函數(shù)的單獨(dú)介紹，一般使用count(*)用作統(tǒng)計(jì)行數(shù)

• 和分組函數(shù)一同查詢的字段要求是group by后的字段

2. GROUP BY

2.1 基本使用

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

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

明確：WHERE一定放在FROM后面

1、 在SELECT列表中所有未包含在組函數(shù)中的列都應(yīng)該包含在 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 使用多個(gè)列分組

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關(guān)鍵字之后，在所有查詢出的分組記錄之后增加一條記錄，該記錄計(jì)算查詢出的所有記錄的總和，即統(tǒng)計(jì)記錄數(shù)量。

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

注意： 當(dāng)使用ROLLUP時(shí)，不能同時(shí)使用ORDER BY子句進(jìn)行結(jié)果排序，即ROLLUP和ORDER BY是互相排斥的，當(dāng)然這是只在5.7才存在的

3. HAVING(過濾數(shù)據(jù))

3.1 基本使用

過濾分組：HAVING子句

• 行已經(jīng)被分組。

• 使用了聚合函數(shù)。

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

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

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

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

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

練習(xí)：查詢部門id為10,20,30,40這4個(gè)部門中最高工資比10000高的部門信息

#方式1：推薦，執(zhí)行效率高于方式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);

結(jié)論：

• 當(dāng)過濾條件中有聚合函數(shù)時(shí)，則此過濾條件必須聲明在HAVING中。

• 當(dāng)過濾條件中沒有聚合函數(shù)時(shí)，則此過濾條件聲明在WHERE中或HAVING中都可以。但是，建議大家聲明在WHERE中

3.2 WHERE和HAVING的對(duì)比

1.?從適用范圍上來講，HAVING的適用范圍更廣。? 2.?如果過濾條件中沒有聚合函數(shù)：這種情況下，WHERE的執(zhí)行效率要高于HAVING

區(qū)別1：WHERE 可以直接使用表中的字段作為篩選條件，但不能使用分組中的計(jì)算函數(shù)作為篩選條件；HAVING 必須要與 GROUP BY 配合使用，可以把分組計(jì)算的函數(shù)和分組字段作為篩選條件。

這決定了，在需要對(duì)數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì)的時(shí)候，HAVING 可以完成 WHERE 不能完成的任務(wù)。這是因?yàn)椋诓樵冋Z法結(jié)構(gòu)中，WHERE 在 GROUP BY 之前，所以無法對(duì)分組結(jié)果進(jìn)行篩選。HAVING 在 GROUP BY 之后，可以使用分組字段和分組中的計(jì)算函數(shù)，對(duì)分組的結(jié)果集進(jìn)行篩選，這個(gè)功能是 WHERE 無法完成的。另外，WHERE排除的記錄不再包括在分組中。

區(qū)別2：如果需要通過連接從關(guān)聯(lián)表中獲取需要的數(shù)據(jù)，WHERE 是先篩選后連接，而 HAVING 是先連接后篩選。 這一點(diǎn)，就決定了在關(guān)聯(lián)查詢中，WHERE 比 HAVING 更高效。因?yàn)?WHERE 可以先篩選，用一個(gè)篩選后的較小數(shù)據(jù)集和關(guān)聯(lián)表進(jìn)行連接，這樣占用的資源比較少，執(zhí)行效率也比較高。HAVING 則需要先把結(jié)果集準(zhǔn)備好，也就是用未被篩選的數(shù)據(jù)集進(jìn)行關(guān)聯(lián)，然后對(duì)這個(gè)大的數(shù)據(jù)集進(jìn)行篩選，這樣占用的資源就比較多，執(zhí)行效率也較低。

小結(jié)如下：

開發(fā)中的選擇：

WHERE 和 HAVING 也不是互相排斥的，我們可以在一個(gè)查詢里面同時(shí)使用 WHERE 和 HAVING。包含分組統(tǒng)計(jì)函數(shù)的條件用 HAVING，普通條件用 WHERE。這樣，我們就既利用了 WHERE 條件的高效快速，又發(fā)揮了 HAVING 可以使用包含分組統(tǒng)計(jì)函數(shù)的查詢條件的優(yōu)點(diǎn)。當(dāng)數(shù)據(jù)量特別大的時(shí)候，運(yùn)行效率會(huì)有很大的差別。一般來講，能用分組前篩選的，盡量使用分組前篩選，提高效率

4. 回顧：分頁查詢 ★

應(yīng)用場景：當(dāng)要顯示的數(shù)據(jù)，一頁顯示不全，需要分頁提交sql請(qǐng)求

語法：

??select?查詢列表 ??from?表 ??【join?type?join?表2 ??on?連接條件 ??where?篩選條件 ??group?by?分組字段 ??having?分組后的篩選 ??order?by?排序的字段】 ??limit?【offset,】size; ??offset?要顯示條目的起始索引（起始索引從0開始） ??size?要顯示的條目個(gè)數(shù)

特點(diǎn)：

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

• 公式

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

假設(shè)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： 有獎(jiǎng)金的員工信息，并且工資較高的前10名顯示出來

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

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

5.1 SELECT語句的完整結(jié)構(gòu)

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

5.2 SELECT執(zhí)行順序

你需要記住 SELECT 查詢時(shí)的兩個(gè)順序：

1. 關(guān)鍵字的順序是不能顛倒的：

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

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

FROM?->?WHERE?->?GROUP?BY?->?HAVING?->?SELECT?的字段?->?DISTINCT?->?ORDER?BY?->?LIMIT

比如你寫了一個(gè) SQL 語句，那么它的關(guān)鍵字順序和執(zhí)行順序是下面這樣的：

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í)行這些步驟的時(shí)候，每個(gè)步驟都會(huì)產(chǎn)生一個(gè)虛擬表，然后將這個(gè)虛擬表傳入下一個(gè)步驟中作為輸入。需要注意的是，這些步驟隱含在 SQL 的執(zhí)行過程中，對(duì)于我們來說是不可見的。

從這里的執(zhí)行順序我們也看出來了，因?yàn)閣here是先篩選的，因此group by語句事先分組，參與分組的數(shù)據(jù)要少，因此執(zhí)行效率要高

5.3 SQL 的執(zhí)行原理

SELECT 是先執(zhí)行 FROM 這一步的。在這個(gè)階段，如果是多張表聯(lián)查，還會(huì)經(jīng)歷下面的幾個(gè)步驟：

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

• 通過 ON 進(jìn)行篩選，在虛擬表 vt1-1 的基礎(chǔ)上進(jìn)行篩選，得到虛擬表 vt1-2；

• 添加外部行。如果我們使用的是左連接、右鏈接或者全連接，就會(huì)涉及到外部行，也就是在虛擬表 vt1-2 的基礎(chǔ)上增加外部行，得到虛擬表 vt1-3。

當(dāng)然如果我們操作的是兩張以上的表，還會(huì)重復(fù)上面的步驟，直到所有表都被處理完為止。這個(gè)過程得到是我們的原始數(shù)據(jù)。

當(dāng)我們拿到了查詢數(shù)據(jù)表的原始數(shù)據(jù)，也就是最終的虛擬表 vt1，就可以在此基礎(chǔ)上再進(jìn)行 WHERE 階段。在這個(gè)階段中，會(huì)根據(jù) vt1 表的結(jié)果進(jìn)行篩選過濾，得到虛擬表 vt2。

然后進(jìn)入第三步和第四步，也就是 GROUP 和 HAVING 階段。在這個(gè)階段中，實(shí)際上是在虛擬表 vt2 的基礎(chǔ)上進(jìn)行分組和分組過濾，得到中間的虛擬表 vt3 和 vt4。

當(dāng)我們完成了條件篩選部分之后，就可以篩選表中提取的字段，也就是進(jìn)入到 SELECT 和 DISTINCT 階段。

首先在 SELECT 階段會(huì)提取想要的字段，然后在 DISTINCT 階段過濾掉重復(fù)的行，分別得到中間的虛擬表 vt5-1 和 vt5-2。

當(dāng)我們提取了想要的字段數(shù)據(jù)之后，就可以按照指定的字段進(jìn)行排序，也就是 ORDER BY 階段，得到虛擬表 vt6。

最后在 vt6 的基礎(chǔ)上，取出指定行的記錄，也就是 LIMIT 階段，得到最終的結(jié)果，對(duì)應(yīng)的是虛擬表 vt7。

當(dāng)然我們?cè)趯?SELECT 語句的時(shí)候，不一定存在所有的關(guān)鍵字，相應(yīng)的階段就會(huì)省略。

同時(shí)因?yàn)?SQL 是一門類似英語的結(jié)構(gòu)化查詢語言，所以我們?cè)趯?SELECT 語句的時(shí)候，還要注意相應(yīng)的關(guān)鍵字順序，**所謂底層運(yùn)行的原理，就是我們剛才講到的執(zhí)行順序。**更細(xì)致的內(nèi)容參考后續(xù)的高級(jí)篇架構(gòu)

6. 課后練習(xí)

綜合練習(xí)1

1.where子句可否使用組函數(shù)進(jìn)行過濾? 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.選擇具有各個(gè)job_id的員工人數(shù)

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.查詢各個(gè)管理者手下員工的最低工資，其中最低工資不能低于6000，沒有管理者的員工不計(jì)算在內(nèi)

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

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

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.查詢每個(gè)工種、每個(gè)部門的部門名、工種名和最低工資

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

綜合練習(xí)2

1.簡單的分組

案例1：查詢每個(gè)工種的員工平均工資

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

案例2：查詢每個(gè)位置的部門個(gè)數(shù)

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

2.可以實(shí)現(xiàn)分組前的篩選

案例1：查詢郵箱中包含a字符的 每個(gè)部門的最高工資

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

案例2：查詢有獎(jiǎng)金的每個(gè)領(lǐng)導(dǎo)手下員工的平均工資

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

3.分組后篩選

案例1：查詢哪個(gè)部門的員工個(gè)數(shù)>5

#①查詢每個(gè)部門的員工個(gè)數(shù) SELECT?COUNT(*),department_id FROM?employees GROUP?BY?department_id;  #②?篩選剛才①結(jié)果 SELECT?COUNT(*),department_id FROM?employees GROUP?BY?department_id HAVING?COUNT(*)>5;

案例2：每個(gè)工種有獎(jiǎng)金的員工的最高工資>12000的工種編號(hào)和最高工資

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

案例3：領(lǐng)導(dǎo)編號(hào)>102的每個(gè)領(lǐng)導(dǎo)手下的最低工資大于5000的領(lǐng)導(dǎo)編號(hào)和最低工資

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

4.添加排序

案例：每個(gè)工種有獎(jiǎng)金的員工的最高工資>6000的工種編號(hào)和最高工資,按最高工資升序

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.按多個(gè)字段分組

案例：查詢每個(gè)工種每個(gè)部門的最低工資,并按最低工資降序

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

更多編程相關(guān)知識(shí)，請(qǐng)?jiān)L問：mysql視頻教程！！

以上就是聊聊MySQL中的