一、聚合函數(shù)

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

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

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

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

2、實(shí)例

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

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

使用*做統(tǒng)計(jì)

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

select count(*) from students;

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

2.2 統(tǒng)計(jì)班級(jí)收集的QQ號(hào)有多少個(gè)

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

select count(qq) from students;

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

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

下面是我們的測(cè)試用例表：

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

select count(distinct math) from exam_result;

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

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

select sum(math) from exam_result;

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

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

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

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

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

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

2.7 求英語(yǔ)最高分

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

select max(english) from exam_result;

二、分組查詢

不知?jiǎng)偛拍阕⒁獾經(jīng)]，前面我們進(jìn)行聚合統(tǒng)計(jì)時(shí)都是在對(duì)整張表進(jìn)行聚合統(tǒng)計(jì)，但是有時(shí)我們想要對(duì)不同的情況進(jìn)行分別統(tǒng)計(jì)。

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

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

1、group by子句

分組查詢的SQL語(yǔ)法如下：

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

（小聲bb：雖然語(yǔ)法看起來(lái)很難，但是在使用一兩次以后你就會(huì)發(fā)現(xiàn)其實(shí)很簡(jiǎn)單）

說(shuō)明一下：

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

2、準(zhǔn)備工作

分組查詢測(cè)試表 —— 雇員信息表

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

• EMP員工表
• DEPT部門表
• SALGRADE工資等級(jí)表

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

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

vim scott_data.sql

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

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

source 該文件的絕對(duì)路徑;

例如我這里是：

source /root/MySQL/scott_data.sql;

執(zhí)行成功！

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

show databases;

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

use scott;show tables;

這三張表就是我們所說(shuō)的：

• EMP員工表
• DEPT部門表
• SALGRADE工資等級(jí)表

我們先分別查看一下表結(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;

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

desc salgrade;

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

select * from salgrade;

3、實(shí)例

3.1 顯示每個(gè)部門的平均工資和最高工資

由于要顯示每個(gè)部門平均工資和最高工資，所以我們一定要借助group by來(lái)將這個(gè)整表進(jìn)行劃分為多個(gè)組，然后我們?cè)偈褂?code>avg與max函數(shù)來(lái)對(duì)工資分別求平均和最高工資。

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

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

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

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

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

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

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

4、having 條件

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

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

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

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

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

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

含有having子句的SQL如下：

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

說(shuō)明一下：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 總結(jié)

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

最新評(píng)論