一、聚合函數(shù)

說明：聚合函數(shù)用來計算一組數(shù)據(jù)的集合并返回單個值，通常用這些函數(shù)完成：個數(shù)的統(tǒng)計，某列數(shù)據(jù)的求和，某列數(shù)據(jù)的最大值，最小值，或者是平均值。

1、常見的聚合函數(shù)

聚合函數(shù)一般在select語句中使用，此時select每處理一條記錄時都會將對應(yīng)的參數(shù)傳遞給這些聚合函數(shù)。

需要注意的是：聚合函數(shù)忽略空值，即NULL值不會參與運(yùn)算的。

2、實例

2.1 統(tǒng)計班級共有多少同學(xué)

這里我們使用下面的學(xué)生表來進(jìn)行演示，學(xué)生表中的內(nèi)容如下：

使用*做統(tǒng)計

這里我們直接使用count(*)進(jìn)行聚合統(tǒng)計，表示對所有的列數(shù)據(jù)進(jìn)行統(tǒng)計：

select count(*) from students;

這里的統(tǒng)計原理也很簡單，其實就是使用了select *將每一條記錄都拿到，然后將每一條待處理記錄時都傳遞給這個count聚合函數(shù)，然后我們就能夠拿到數(shù)據(jù)的總個數(shù)了。

2.2 統(tǒng)計班級收集的QQ號有多少個

值得注意的是：我們沒有對qq號碼進(jìn)行非空約束，我們繼續(xù)使用聚會函數(shù)count進(jìn)行統(tǒng)計。

select count(qq) from students;

結(jié)果為1，這證明聚合函數(shù)確實會忽略空值，即NULL值不會參與運(yùn)算的。

2.3 統(tǒng)計本次考試的數(shù)學(xué)成績分?jǐn)?shù)的個數(shù)有幾個

下面是我們的測試用例表：

注意本題目要求的是數(shù)學(xué)成績的個數(shù)，不是數(shù)學(xué)成績的值，這意味著我們需要對相同的數(shù)學(xué)成績的值進(jìn)行先去重，然后再進(jìn)行聚合統(tǒng)計，對于去重我們可以使用distinct進(jìn)行去重，然后再使用count進(jìn)行聚合統(tǒng)計。

select count(distinct math) from exam_result;

2.4 統(tǒng)計數(shù)學(xué)成績總分

對于統(tǒng)計數(shù)學(xué)成績的總分其實就是對數(shù)據(jù)進(jìn)行求和，我們可以使用sum函數(shù)來進(jìn)行求和：

select sum(math) from exam_result;

2.5 統(tǒng)計不滿100分的數(shù)學(xué)成績總分

在剛才的示例中我們已經(jīng)求得數(shù)學(xué)成績的總分了，對于不滿100分的人我們可以使用where子句進(jìn)行篩選得到。

select sum(math) from exam_result where math < 100;

2.6 統(tǒng)計總分的平均分

對于平均分我們可以使用avg函數(shù)進(jìn)行求得：

select avg(chinese + math + english) from exam_result;

2.7 求英語最高分

求最高分其實就是求最大值，我們可以使用max函數(shù)進(jìn)行求最大值：

select max(english) from exam_result;

二、分組查詢

不知剛才你注意到?jīng)]，前面我們進(jìn)行聚合統(tǒng)計時都是在對整張表進(jìn)行聚合統(tǒng)計，但是有時我們想要對不同的情況進(jìn)行分別統(tǒng)計。

例如在一個班級之中，有男生和女生，我們想要得到男生和女生中英語成績的最高分分別是多少？我們發(fā)現(xiàn)我們再使用max函數(shù)是沒有辦法達(dá)到我們想要的目的的，但是如果我們先對班級中的男女生進(jìn)行分組，然后又分別進(jìn)行聚合統(tǒng)計，使用max函數(shù)就能夠達(dá)到我們想要的目的了！

所以分組是數(shù)據(jù)庫最重要任務(wù)之一，要將行分組，我們可以使用GROUP BY子句。

1、group by子句

分組查詢的SQL語法如下：

SELECT column1 [, column2], ... FROM table_name [WHERE ...] GROUP BY column [, ...] [order by ...] [LIMIT ...];

（小聲bb：雖然語法看起來很難，但是在使用一兩次以后你就會發(fā)現(xiàn)其實很簡單）

說明一下：

• SQL中大寫的表示關(guān)鍵字，[ ]中代表的是可選項。
• 查詢SQL中各語句的執(zhí)行順序為：where、group by、select、order by、limit。
• group by后面的列名，表示按照指定列進(jìn)行分組查詢。

2、準(zhǔn)備工作

分組查詢測試表 —— 雇員信息表

準(zhǔn)備工作，創(chuàng)建一個雇員信息表（來自oracle 9i的經(jīng)典測試表）

• EMP員工表
• DEPT部門表
• SALGRADE工資等級表

雇員信息表數(shù)據(jù)庫文件:https://pan.baidu.com/s/1grLbGwFetNFBUGs32d-KWQ
提取碼: 3yur

拿到該數(shù)據(jù)庫文件以后，我們可以先打開該文件進(jìn)行查看其內(nèi)容：

vim scott_data.sql

我們會發(fā)現(xiàn)其里面都是SQL記錄，對于MySQL我們備份其數(shù)據(jù)庫時，其實備份的全部都是一條條有效的SQL記錄，通過重新執(zhí)行這些SQL，我們便能夠得到和原來一摸一樣數(shù)據(jù)庫。

接下來我們就可以在mysql中將這個數(shù)據(jù)庫給創(chuàng)建出來了：

source 該文件的絕對路徑;

例如我這里是：

source /root/MySQL/scott_data.sql;

執(zhí)行成功！

然后我們查詢我們的數(shù)據(jù)庫，發(fā)現(xiàn)數(shù)據(jù)庫中多了一個scott的數(shù)據(jù)庫：

show databases;

我們使用這個數(shù)據(jù)庫并顯示數(shù)據(jù)庫中的所有表

use scott;show tables;

這三張表就是我們所說的：

• EMP員工表
• DEPT部門表
• SALGRADE工資等級表

我們先分別查看一下表結(jié)構(gòu)和表內(nèi)容：

查看員工表結(jié)構(gòu)

desc emp;

查看員工表內(nèi)容

select * from emp;

查看部門表結(jié)構(gòu)

desc dept;

查看部門表內(nèi)容

select * from dept;

查看工資等級表結(jié)構(gòu)

desc salgrade;

查看工資等級表內(nèi)容

select * from salgrade;

3、實例

3.1 顯示每個部門的平均工資和最高工資

由于要顯示每個部門平均工資和最高工資，所以我們一定要借助group by來將這個整表進(jìn)行劃分為多個組，然后我們再使用avg與max函數(shù)來對工資分別求平均和最高工資。

所以我們可以這樣進(jìn)行查詢：

select deptno, avg(sal), max(sal) from emp group by deptno;

說明一下： 上述SQL會先將表中的數(shù)據(jù)按照部門號進(jìn)行分組，然后各自在組內(nèi)做聚合查詢得到每個組的平均工資和最高工資。

3.2 顯示每個部門的每種崗位的平均工資和最低工資

現(xiàn)在對于我們來說：“求平均工資和最低工資”，是很簡單的事情，但是題目的要求：顯示每個部門的每種崗位，顯然要求我們進(jìn)行兩次分組，對于group by子句來說，我們可以使用,分割，來進(jìn)行多個條件分組。

select deptno, job, avg(sal), min(sal) from emp group by deptno, job;

說明：group by子句中可以指明按照多個字段進(jìn)行分組，各個字段之間使用逗號隔開，分組優(yōu)先級與書寫順序相同。

4、having 條件

在講解此條件之前我們先繼續(xù)來解決下面的問題：

3.3 顯示平均工資低于2000的部門和它的平均工資

在這里我們會發(fā)現(xiàn)，我們必須先要拿到平均工資的值，然后再根據(jù)平均工資進(jìn)行篩選。

假設(shè)這里我們使用where子句，我們會發(fā)現(xiàn)在where子句中我們無法表示平均工資的（聚合函數(shù)不能夠在where子句中使用的，因為where子句是對單個記錄進(jìn)行篩選，而聚合函數(shù)是對整個結(jié)果集進(jìn)行計算的）

就算假設(shè)我們能夠表示平均工資，我們知道where子句的執(zhí)行優(yōu)先級是很高的，于是就會先按平均工資進(jìn)行篩選，然后再拿到平均工資的值。顯然這個邏輯是有問題的。

為了解決這個問題我們就要學(xué)習(xí)一下HAVING 條件了，having也是一個篩選條件。

含有having子句的SQL如下：

SELECT ... FROM table_name [WHERE ...] [GROUP BY ...] [HAVING ...] [order by ...] [LIMIT ...];

說明一下：

• SQL中大寫的表示關(guān)鍵字，[ ]中代表的是可選項。
• SQL中各語句的執(zhí)行順序為：where、group by、select、having、order by、limit。
• having子句中可以指明一個或多個篩選條件。

having子句和where子句的區(qū)別

• where子句放在表名后面，而having子句必須搭配group by子句使用，放在group by子句的后面。
• where子句是對整表的數(shù)據(jù)進(jìn)行篩選，having子句是對分組后的數(shù)據(jù)進(jìn)行篩選。
• where子句中不能使用聚合函數(shù)和別名，而having子句中可以使用聚合函數(shù)和別名。
• where子句的執(zhí)行優(yōu)先級很高，而having的執(zhí)行優(yōu)先級很低。

于是上面的問題就被轉(zhuǎn)化為了下面的問題了：

• 先統(tǒng)計每個部門的平均工資。
• 然后通過having子句篩選出平均工資低于2000的部門。

統(tǒng)計每個部門的平均工資

select deptno, avg(sal) from emp group by deptno;

通過having子句篩選出平均工資低于2000的部門

select deptno, avg(sal) 平均工資 from emp group by deptno having 平均工資<2000;

需要注意的是：聚合函數(shù)的執(zhí)行優(yōu)先級通常是在SQL查詢中確定的，這句的SQL執(zhí)行順序如下：

這句SQL的執(zhí)行順序如下：

1. FROM子句：首先，從"emp"表中檢索數(shù)據(jù)。
2. GROUP BY子句：然后，根據(jù)"deptno"列將結(jié)果集分組。相同"deptno"值的行將被分為一組。
   （AVG函數(shù)計算：接下來，在每個分組中計算"sal"列的平均工資）。
3. HAVING子句：然后，在HAVING子句中篩選出平均工資小于2000的分組。
4. SELECT子句：最后，在SELECT子句中選擇"deptno"和平均工資作為結(jié)果返回。

面試題：SQL查詢中各個關(guān)鍵字的執(zhí)行先后順序:

from > on> join > where > group by > with > having > select > distinct > order by > limit

 總結(jié)

到此這篇關(guān)于MySQL數(shù)據(jù)庫聚合函數(shù)與分組查詢的文章就介紹到這了,更多相關(guān)MySQL聚合函數(shù)與分組查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論