MySQL中的聚合函數(shù)怎么用

本篇內(nèi)容介紹了“MySQL中的聚合函數(shù)怎么用”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

MySQL聚合函數(shù)和分頁(yè)查詢

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

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

什么是聚合函數(shù)

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

聚合函數(shù)類型

• AVG()

• SUM()

• MAX()

• MIN()

• COUNT()

聚合函數(shù)語(yǔ)法

聚合函數(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ì)

問(wèn)題：用count(*)，count(1)，count(列名)誰(shuí)好呢?

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

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

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

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

說(shuō)明：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ù)分成若干組，語(yǔ)法如下：

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(過(guò)濾數(shù)據(jù))

3.1 基本使用

過(guò)濾分組：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ù)來(lái)代替過(guò)濾條件。如下：

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

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

#方式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)過(guò)濾條件中有聚合函數(shù)時(shí)，則此過(guò)濾條件必須聲明在HAVING中。

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

3.2 WHERE和HAVING的對(duì)比

1. 從適用范圍上來(lái)講，HAVING的適用范圍更廣。 
2. 如果過(guò)濾條件中沒(méi)有聚合函數(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)?，在查詢語(yǔ)法結(jié)構(gòu)中，WHERE 在 GROUP BY 之前，所以無(wú)法對(duì)分組結(jié)果進(jìn)行篩選。HAVING 在 GROUP BY 之后，可以使用分組字段和分組中的計(jì)算函數(shù)，對(duì)分組的結(jié)果集進(jìn)行篩選，這個(gè)功能是 WHERE 無(wú)法完成的。另外，WHERE排除的記錄不再包括在分組中。

區(qū)別2：如果需要通過(guò)連接從關(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é)如下：

開(kāi)發(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ì)有很大的差別。一般來(lái)講，能用分組前篩選的，盡量使用分組前篩選，提高效率

4. 回顧：分頁(yè)查詢 ★

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

語(yǔ)法：

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

特點(diǎn)：

• limit語(yǔ)句放在查詢語(yǔ)句的最后

• 公式

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

假設(shè)size=10，即每頁(yè)顯示10條記錄，page從1開(kāi)始，即第一頁(yè)

• page=1，則顯示條目的起始索引為0，頁(yè)面顯示0-10條

• page=2，則顯示條目的起始索引為10，頁(yè)面顯示11-20條

• page=3，則顯示條目的起始索引為20，頁(yè)面顯示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名顯示出來(lái)

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

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

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

#方式1：sql92語(yǔ)法：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的連接條件
AND 不包含組函數(shù)的過(guò)濾條件
GROUP BY ...,...
HAVING 包含組函數(shù)的過(guò)濾條件
ORDER BY ... ASC/DESC
LIMIT ...,...

#方式2：sql99語(yǔ)法
SELECT ...,....,...
FROM ... JOIN ... 
ON 多表的連接條件
JOIN ...
ON ...
WHERE 不包含組函數(shù)的過(guò)濾條件
AND/OR 不包含組函數(shù)的過(guò)濾條件
GROUP BY ...,...
HAVING 包含組函數(shù)的過(guò)濾條件
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：分頁(yè)

5.2 SELECT執(zhí)行順序

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

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

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

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

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

比如你寫(xiě)了一個(gè) SQL 語(yǔ)句，那么它的關(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 語(yǔ)句執(zhí)行這些步驟的時(shí)候，每個(gè)步驟都會(huì)產(chǎn)生一個(gè)虛擬表，然后將這個(gè)虛擬表傳入下一個(gè)步驟中作為輸入。需要注意的是，這些步驟隱含在 SQL 的執(zhí)行過(guò)程中，對(duì)于我們來(lái)說(shuō)是不可見(jiàn)的。

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

5.3 SQL 的執(zhí)行原理

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

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

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

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

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

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

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

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

首先在 SELECT 階段會(huì)提取想要的字段，然后在 DISTINCT 階段過(guò)濾掉重復(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è)趯?xiě) SELECT 語(yǔ)句的時(shí)候，不一定存在所有的關(guān)鍵字，相應(yīng)的階段就會(huì)省略。

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

6. 課后練習(xí)

綜合練習(xí)1

1.where子句可否使用組函數(shù)進(jìn)行過(guò)濾? 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，沒(méi)有管理者的員工不計(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.查詢所有部門(mén)的名字，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è)部門(mén)的部門(mén)名、工種名和最低工資

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.簡(jiǎn)單的分組

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

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

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

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

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

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

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è)部門(mén)的員工個(gè)數(shù)>5

#①查詢每個(gè)部門(mén)的員工個(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è)部門(mén)的最低工資,并按最低工資降序

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

“MySQL中的聚合函數(shù)怎么用”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！