通過條件查詢可以查詢到符合條件的數(shù)據(jù)，但如同要實(shí)現(xiàn)對字段的值進(jìn)行計(jì)算、根據(jù)一個(gè)或多個(gè)字段對查詢結(jié)果進(jìn)行分組等操作時(shí)，就需要使用更高級(jí)的查詢，MySQL提供了聚合函數(shù)、分組查詢、排序查詢、限量查詢、內(nèi)置函數(shù)以實(shí)現(xiàn)更復(fù)雜的查詢需求。接下來將針對這些高級(jí)查詢的知識(shí)進(jìn)行講解。

1.聚合函數(shù)

在實(shí)際開發(fā)中，經(jīng)常需要做一些數(shù)據(jù)統(tǒng)計(jì)操作，例如統(tǒng)計(jì)某個(gè)字段的最大值、最小值、平均值等。像這樣對一組值執(zhí)行計(jì)算并將計(jì)算后的值返回的操作稱為聚合操作，聚合操作一般通過聚合函數(shù)實(shí)現(xiàn)。使用聚合函數(shù)實(shí)現(xiàn)查詢的基本語法格式如下。

SELECT [字段名1，字段名2，···，字段名n] 聚合函數(shù) FROM 數(shù)據(jù)表名；

MySQL中常用的聚合函數(shù)如下：

COUNT(e)	返回查詢的記錄總數(shù)，參數(shù)e可以是字段名或*
SUM(e)	返回e字段中值的總和
AVG(e)	返回e字段中值的平均值
MAX(e)	返回e字段中的最大值
MIN(e)	返回e字段中的最小值

上面的聚合函數(shù)都是MySQL中內(nèi)置的函數(shù)，使用者根據(jù)函數(shù)的語法格式直接調(diào)用即可。

接下來，通過一些例子學(xué)習(xí)聚合函數(shù)在數(shù)據(jù)統(tǒng)計(jì)中的使用。

為了方面演示，我把之前的員工表刪了，又創(chuàng)建了一個(gè)員工表并插入了一些數(shù)據(jù)：

mysql> CREATE TABLE emp(-> empno INT PRIMARY KEY,-> ename VARCHAR(16),-> job VARCHAR(16),-> sal INT,-> bon INT-> );
Query OK, 0 rows affected (0.01 sec)mysql> DESC emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empno | int         | NO   | PRI | NULL    |       |
| ename | varchar(16) | YES  |     | NULL    |       |
| job   | varchar(16) | YES  |     | NULL    |       |
| sal   | int         | YES  |     | NULL    |       |
| bon   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

表格介紹：

empno：員工編號(hào)

ename ：員工姓名

job：職位

sal：工資

bon：獎(jiǎng)金

插入語句：

mysql> INSERT INTO emp VALUES-> (9880,'張三','銷售',3000,200),-> (9885,'李四','保潔',2500,100),-> (9775,'王五','銷售',3500,500),-> (9900,'孫七','銷售',2500,200),-> (9990,'周八','經(jīng)理',7000,1000)-> (9770,'吳九','保潔',2500,null),-> (9888,'鄭十','銷售',3500,null);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

表中的具體內(nèi)容如下：

mysql> SELECT*FROM emp;
+-------+--------+--------+------+------+
| empno | ename  | job    | sal  | bon  |
+-------+--------+--------+------+------+
|  9770 | 吳九   | 保潔   | 2500 | NULL |
|  9775 | 王五   | 銷售   | 3500 |  500 |
|  9880 | 張三   | 銷售   | 3000 |  200 |
|  9885 | 李四   | 保潔   | 2500 |  100 |
|  9888 | 鄭十   | 銷售   | 3500 | NULL |
|  9900 | 孫七   | 銷售   | 2500 |  200 |
|  9990 | 周八   | 經(jīng)理   | 7000 | 1000 |
+-------+--------+--------+------+------+
7 rows in set (0.00 sec)

1.COUNT()函數(shù)

COUNT()函數(shù)用于檢索數(shù)據(jù)表行中的值的計(jì)數(shù)，COUNT(*)可以統(tǒng)計(jì)數(shù)據(jù)表中記錄的總條數(shù)，即數(shù)據(jù)表中有多少行記錄。例如，想要使用SQL語句查詢員工表中有多少個(gè)員工的記錄。在查詢時(shí)可以使用COUNT()函數(shù)進(jìn)行統(tǒng)計(jì)，具體SQL語句及執(zhí)行結(jié)果如下。

mysql> SELECT COUNT(*) FROM emp;

+----------+

| COUNT(*) |

+----------+

| 7 |

+----------+

1 row in set (0.00 sec)

由上述執(zhí)行結(jié)果可以得出，數(shù)據(jù)表emp中有7條記錄，也就是說員工表中有7個(gè)員工的記錄。

COUNT()函數(shù)中的參數(shù)除可以使用*號(hào)，還可以使用字段的名稱。兩者不同的是，使用COUNT(*)統(tǒng)計(jì)結(jié)果時(shí)，相當(dāng)于統(tǒng)計(jì)數(shù)據(jù)表的行數(shù)，不會(huì)忽略字段中值為NULL的行；如果使用COUNT(字段)統(tǒng)計(jì)，那么字段值為NULL的記錄不會(huì)被統(tǒng)計(jì)。例如，想要使用SQL語句查詢員工表中獎(jiǎng)金不為NULL的員工個(gè)數(shù)，具體SQL語句及執(zhí)行結(jié)果如下。

mysql> SELECT COUNT(bon) FROM emp;
+------------+
| COUNT(bon) |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

由上述執(zhí)行結(jié)果可以得出，數(shù)據(jù)表emp中獎(jiǎng)金不為NULL的員工有5個(gè)。

2.SUN()函數(shù)

如果字段中存放的是數(shù)值型數(shù)據(jù)，需要統(tǒng)計(jì)該字段中所有值的總數(shù)，可以使用SUM()函數(shù)。SUM()函數(shù)會(huì)對指定字段中的值進(jìn)行累加，并且在數(shù)據(jù)累加時(shí)忽略字段中的NULL值。

例如，想要使用SQL語句查詢員工表中員工獎(jiǎng)金的總和?？梢栽诓樵儠r(shí)使用SUM()函數(shù)進(jìn)行統(tǒng)計(jì)，具體SQL語句及執(zhí)行結(jié)果如下。

mysql> SELECT SUM(bon) FROM emp;
+----------+
| SUM(bon) |
+----------+
|     2000 |
+----------+
1 row in set (0.00 sec)

上述SELECT語句使用SUM()函數(shù)對COMM字段中的值進(jìn)行求和統(tǒng)計(jì)，執(zhí)行結(jié)果中顯示員工獎(jiǎng)金總和為2000元。

3.AVG()函數(shù)

如果字段中存放的是數(shù)值型數(shù)據(jù)，需要統(tǒng)計(jì)該字段中所有值的平均值，可以使用AVG()函數(shù)。AVG()函數(shù)會(huì)計(jì)算指定字段值的平均值，并且計(jì)算時(shí)會(huì)忽略字段中的NULL值。

例如，想要使用SQL語句查詢員工表中員工的平均獎(jiǎng)金。查詢時(shí)可以使用AVG()函數(shù)進(jìn)行統(tǒng)計(jì)，具體SQL語句及執(zhí)行結(jié)果如下。

mysql> SELECT AVG(bon) FROM emp;
+----------+
| AVG(bon) |
+----------+
| 400.0000 |
+----------+
1 row in set (0.00 sec)

上述SELECT語句使用AVG()函數(shù)計(jì)算bon字段的平均值。由執(zhí)行結(jié)果可以得出，bon字段的平均值為400.0000。AVG()函數(shù)在計(jì)算時(shí)會(huì)忽略bon字段中的NULL值，即只對非NULL的數(shù)值進(jìn)行累加，然后將累加和除以非NULL的行數(shù)計(jì)算出平均值。

上面已經(jīng)設(shè)置了是五個(gè)人有獎(jiǎng)金，有兩個(gè)是沒有獎(jiǎng)金的，獎(jiǎng)金是空值，因此那兩個(gè)人也不會(huì)計(jì)算入內(nèi)。

如果想要統(tǒng)計(jì)所有員工的平均獎(jiǎng)金，即獎(jiǎng)金平均到所有員工身上，可以借助IFNULL()函數(shù)。

IFNULL(v1,v2)

上述格式表示，如果v1的值不為NULL，則返回v1的值，否則返回v2。例如，想要使用SQL語句查詢所有員工的平均獎(jiǎng)金。查詢時(shí)可以調(diào)用AVG()函數(shù)和IFNULLO函數(shù)進(jìn)行統(tǒng)計(jì)，先調(diào)用IFNULL()函數(shù)將bon字段中所有的NULL值轉(zhuǎn)換為0，再調(diào)用AVG()函數(shù)統(tǒng)計(jì)平均值，具體SQL語句及執(zhí)行結(jié)果如下。

mysql> SELECT AVG(IFNULL(BON,0)) FROM emp;
+--------------------+
| AVG(IFNULL(BON,0)) |
+--------------------+
|           285.7143 |
+--------------------+
1 row in set (0.00 sec)

上述SELECT語句在執(zhí)行AVG()函數(shù)之前調(diào)用IFNULL()函數(shù)對bon字段中的值進(jìn)行判斷，如果是NULL值就轉(zhuǎn)換成0返回；由執(zhí)行結(jié)果并結(jié)合數(shù)據(jù)表中的數(shù)據(jù)可以得出，本次統(tǒng)計(jì)的平均獎(jiǎng)金是所有員工的平均獎(jiǎng)金。

4.MAX()函數(shù)

MAX(函數(shù)用于計(jì)算指定字段中的最大值，如果字段的值是數(shù)值類型，則比較的是值的大小。例如，想要使用SQL語句查詢員工表中最高的工資。查詢時(shí)可以使用MAX()函數(shù)進(jìn)行計(jì)算，具體SQL語句及執(zhí)行結(jié)果如下。

mysql> SELECT MAX(sal) FROM emp;
+----------+
| MAX(sal) |
+----------+
|     7000 |
+----------+
1 row in set (0.00 sec)

上述SELECT語句使用MAX()函數(shù)獲取了sal字段中最大的數(shù)值。

5.MIN()函數(shù)

MIN()函數(shù)用于計(jì)算指定字段中的最小值，如果字段的值是數(shù)值類型，則比較的是值的大小。例如，想要使用SQL語句查詢員工表中最低的工資。查詢時(shí)可以使用MIN()函數(shù)進(jìn)行計(jì)算，具體SQL語句及執(zhí)行結(jié)果如下。

mysql> SELECT MIN(sal) FROM emp;
+----------+
| MIN(sal) |
+----------+
|     2500 |
+----------+
1 row in set (0.00 sec)

在上述代碼中，使用MIN()函數(shù)獲取了sal字段中最小的數(shù)值。

分組查詢

在對數(shù)據(jù)表中的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)時(shí)，有時(shí)需要按照一定的類別作統(tǒng)計(jì)。例如，財(cái)務(wù)在統(tǒng)計(jì)每個(gè)部門的工資總數(shù)時(shí)，屬于同一個(gè)部門的所有員工就是一個(gè)分組。在MySQL中，可以使用GROUP BY根據(jù)指定的字段結(jié)果集進(jìn)行分組，如果某些記錄的指定字段具有相同的價(jià)值，那么分組后被合并為一條數(shù)據(jù)。使用GROUP BY分組查詢的語法如下：

1.單獨(dú)使用GROUP BY 分組

單獨(dú)使用GROUP BY進(jìn)行分組時(shí)將根據(jù)指定的字段合并數(shù)據(jù)行。例如，我們想要使用SQL語句查詢員工表的職位有有哪幾種，具體SQL語句及執(zhí)行結(jié)果如下：

mysql> SELECT job FROM emp GROUP BY job;
+--------+
| job    |
+--------+
| 保潔   |
| 銷售   |
| 經(jīng)理   |
+--------+
3 rows in set (0.00 sec)

在上述SELECT語句中，使用GROUP BY根據(jù)job字段中的值對數(shù)據(jù)表中的記錄進(jìn)行分組；從執(zhí)行結(jié)果來看，員工的職位一共有三種。

2.GROUP BY和聚合函數(shù)一起使用

如果分組查詢時(shí)要進(jìn)行統(tǒng)計(jì)匯總，此時(shí)需要將GROUP BY和聚合函數(shù)一起使用。例如，統(tǒng)計(jì)員工表各部門的薪資總和或平均薪資，可以使用GROUP BY和聚合函數(shù)AVG()、SUM()進(jìn)行統(tǒng)計(jì)，具體SQL語句及執(zhí)行結(jié)果如下:

mysql> SELECT job,AVG(sal),SUM(sal) FROM emp GROUP BY job;
+--------+-----------+----------+
| job    | AVG(sal)  | SUM(sal) |
+--------+-----------+----------+
| 保潔   | 2500.0000 |     5000 |
| 銷售   | 3125.0000 |    12500 |
| 經(jīng)理   | 7000.0000 |     7000 |
+--------+-----------+----------+
3 rows in set (0.01 sec)

在上述SELECT語句中，使用GROUP BY根據(jù)job字段中的值對數(shù)據(jù)表的記錄進(jìn)行分組，值相同的為一組，并計(jì)算出各個(gè)職位的總工資和平均工資。

3.GROUP BY和HAVING關(guān)鍵字一起使用

通常情況下GROUP BY和HAVING關(guān)鍵字一起使用，用于對分組后的結(jié)果進(jìn)行條件過濾。例如，假如我們想要使用SQL語句查詢員工表中的平均工資小于3000的部門編號(hào)及這些部門的平均工資。查詢時(shí)可以使用GROUP BY和HAVING進(jìn)行統(tǒng)計(jì)，具體SQL語句及執(zhí)行結(jié)果如下：

mysql> SELECT empno,AVG(sal) FROM emp GROUP BY empno HAVING AVG(sal)<3000;
+-------+-----------+
| empno | AVG(sal)  |
+-------+-----------+
|  9770 | 2500.0000 |
|  9885 | 2500.0000 |
|  9900 | 2500.0000 |
+-------+-----------+
3 rows in set (0.00 sec)

在上述SELECT語句中，使用GROUP BY根據(jù)empno字段中的值對數(shù)據(jù)表的記錄進(jìn)行分組，并且使用HAVING篩選平均工資小于3000的數(shù)據(jù)，最終返回了平均工資小于3000的部門編號(hào)及平均工資。

在MySQL中，HAVING 子句用于在 GROUP BY 子句后對聚合結(jié)果進(jìn)行過濾。它通常與聚合函數(shù)（如 SUM(), COUNT(), AVG(), MAX(), MIN() 等）一起使用，以篩選滿足特定條件的分組。

與 WHERE 子句不同，WHERE 子句在聚合之前對單個(gè)記錄進(jìn)行過濾，而 HAVING 子句在聚合之后對分組進(jìn)行過濾。這一點(diǎn)不要弄錯(cuò)哦。

排序查詢

對數(shù)據(jù)表的數(shù)據(jù)進(jìn)行查詢時(shí)，可能查詢出來的數(shù)據(jù)是無序的，或者其排列順序不是用戶期望的。如果想要對查詢結(jié)果按指定的方式排序，例如對員工信息按姓名順序排列等，可以使用ORDER BY對查詢結(jié)果進(jìn)行排序。查詢語句中使用ORDER BY的基本語法格式如下。

SELECT*|{字段名1，字段名2,···} FROM 表名 ORDER BY 字段名1 [ASC | DESC], 字段名2 [ASC | DESC]......

在上面的語法格式中，ORDER BY后指定的字段名1、字段名2等是對查詢結(jié)果排序的依據(jù)，即按照哪一個(gè)字段進(jìn)行排序。參數(shù)ASC表示按照升序進(jìn)行排序，DESC表示按照降序進(jìn)行排序。

使用ORDER BY對查詢結(jié)果進(jìn)行排序時(shí)，如果不指定排序方式，默認(rèn)按照ASC方式進(jìn)行排序。例如，技術(shù)人員想要使用SQL語句查詢員工表中職位為銷售的員工信息，查詢出的結(jié)果根據(jù)員工工資升序排列，具體SQL語句及執(zhí)行結(jié)果如下：

mysql> SELECT * FROM emp WHERE job='銷售' ORDER BY sal;
+-------+--------+--------+------+------+
| empno | ename  | job    | sal  | bon  |
+-------+--------+--------+------+------+
|  9900 | 孫七   | 銷售   | 2500 |  200 |
|  9880 | 張三   | 銷售   | 3000 |  200 |
|  9775 | 王五   | 銷售   | 3500 |  500 |
|  9888 | 鄭十   | 銷售   | 3500 | NULL |
+-------+--------+--------+------+------+
4 rows in set (0.00 sec)

上述SELECT語句使用ORDER BY對job字段值為銷售的所有記錄按照工資從低到高進(jìn)行排序，即sal字段的值按升序排序。因?yàn)闆]有設(shè)置怎么排序，所以默認(rèn)ASC升序排序。

要注意的是如果有字段中的值為NULL，那么NULL會(huì)被當(dāng)做最小值進(jìn)行排序，下面按照獎(jiǎng)金對銷售員工進(jìn)行排序：

mysql> SELECT * FROM emp WHERE job='銷售' ORDER BY bon;
+-------+--------+--------+------+------+
| empno | ename  | job    | sal  | bon  |
+-------+--------+--------+------+------+
|  9888 | 鄭十   | 銷售   | 3500 | NULL |
|  9880 | 張三   | 銷售   | 3000 |  200 |
|  9900 | 孫七   | 銷售   | 2500 |  200 |
|  9775 | 王五   | 銷售   | 3500 |  500 |
+-------+--------+--------+------+------+
4 rows in set (0.00 sec)

上述SELECT語句查詢職位為銷售的員工信息，并且根據(jù)員工獎(jiǎng)金值進(jìn)行升序排序。從執(zhí)行結(jié)果可以看出，獎(jiǎng)金值為NULL的員工信息排在第一位，說明排序時(shí)NULL被當(dāng)作最小值。

ORDER BY可以對多個(gè)字段的值進(jìn)行排序，并且每個(gè)排序字段可以有不同的排序順序。例如，技術(shù)人員想要使用SQL語句查詢員工表中工資為2500的員工所有記錄，查詢出的記錄先按職位的升序排序，再按員工編號(hào)降序排序，具體SQL語b 句及執(zhí)行結(jié)果如下。

mysql> SELECT * FROM emp WHERE sal=2500 ORDER BY job,empno DESC;
+-------+--------+--------+------+------+
| empno | ename  | job    | sal  | bon  |
+-------+--------+--------+------+------+
|  9885 | 李四   | 保潔   | 2500 |  100 |
|  9770 | 吳九   | 保潔   | 2500 | NULL |
|  9900 | 孫七   | 銷售   | 2500 |  200 |
+-------+--------+--------+------+------+
3 rows in set (0.00 sec)

在上述SELECT語句中，查詢sal字段工資為2500的所有記錄，先將這些記錄按照job字段的值升序排序，如果job字段的值相同，則按照empno字段的值進(jìn)行降序排序。如果排序字段的值是字符串類型，則會(huì)按字符串中字符的ASCII碼值進(jìn)行排序。

上面的例子job字段后面沒有設(shè)置排序，因此默認(rèn)是升序，然后又給empno字段設(shè)置了降序，大家不要迷了啊。

mysql> SELECT * FROM emp WHERE sal=2500 ORDER BY bon DESC,empno DESC;
+-------+--------+--------+------+------+
| empno | ename  | job    | sal  | bon  |
+-------+--------+--------+------+------+
|  9900 | 孫七   | 銷售   | 2500 |  200 |
|  9885 | 李四   | 保潔   | 2500 |  100 |
|  9770 | 吳九   | 保潔   | 2500 | NULL |
+-------+--------+--------+------+------+
3 rows in set (0.00 sec)

這個(gè)是兩個(gè)字段都設(shè)置了排序，并且都是降序。

限量查詢

查詢數(shù)據(jù)時(shí)，SELECT語句可能會(huì)返回很多條記錄，而用戶需要的記錄可能只是其中的一條或幾條。中的一條或幾條。例如，在員工管理系統(tǒng)中，希望每一頁默認(rèn)展示10條員工信息，并且可以通過下拉框更改每頁展示的員工信息數(shù)。MySQL中提供了一個(gè)關(guān)鍵字LIMIT可以指定查詢結(jié)果從哪一條記錄開始以及一共查詢多少條信息。在SELECT語句中使用LIMIT的基本語法格式如下。

SELECT 字段名1，字段名2,..… FROM 數(shù)據(jù)表名 LIMIT [OFFSET，] 記錄數(shù)；

在上面的語法格式中，LIMIT后面可以跟2個(gè)參數(shù)。第一個(gè)參數(shù)OFFSET為可選值，表示偏移量，如果偏移量為0則從查詢結(jié)果的第一條記錄開始，偏移量為1則從查詢結(jié)果的第二條記錄開始，以此類推。如果不指定OFFSET的值，其默認(rèn)值為0。第二個(gè)參數(shù)“記錄數(shù)”表示返回查詢記錄的條數(shù)。

例如，技術(shù)人員想要使用SQL語句查詢員工表中工資最高的前3名的員工信息，查詢時(shí)可以使用LIMIT進(jìn)行限量，具體SQL語句及執(zhí)行結(jié)果如下。

mysql> SELECT * FROM emp ORDER BY sal DESC LIMIT 3;
+-------+--------+--------+------+------+
| empno | ename  | job    | sal  | bon  |
+-------+--------+--------+------+------+
|  9990 | 周八   | 經(jīng)理   | 7000 | 1000 |
|  9775 | 王五   | 銷售   | 3500 |  500 |
|  9888 | 鄭十   | 銷售   | 3500 | NULL |
+-------+--------+--------+------+------+
3 rows in set (0.00 sec)

在上述SELECT語句中，首先使用ORDER BY根據(jù)字段sal的值對數(shù)據(jù)表中的記錄進(jìn)行降序排序，接著使用LIMIT指定返回第1~3條記錄。

除了指定查詢記錄數(shù)，LIMIT還可以通過指定OFFSET的值指定查詢的偏移量，也就是查詢時(shí)跳過幾條記錄。

例如，我們要使用SQL語句查詢員工表中工資第二名到第五名的員工信息。具體SQL語句及執(zhí)行結(jié)果如下：

mysql> SELECT * FROM emp ORDER BY sal DESC LIMIT 1,4;
+-------+--------+--------+------+------+
| empno | ename  | job    | sal  | bon  |
+-------+--------+--------+------+------+
|  9775 | 王五   | 銷售   | 3500 |  500 |
|  9888 | 鄭十   | 銷售   | 3500 | NULL |
|  9880 | 張三   | 銷售   | 3000 |  200 |
|  9770 | 吳九   | 保潔   | 2500 | NULL |
+-------+--------+--------+------+------+
4 rows in set (0.00 sec)

在上述SELECT語句中，先使用ORDER BY根據(jù)字段sal的值對數(shù)據(jù)表中的記錄進(jìn)行降序排序，然后指定返回記錄的偏移量為1，查詢記錄的條數(shù)為4.執(zhí)行結(jié)果跳過了排序后的第一條員工信息，返回工資前2~5名的員工信息。

這里和Python的索引有點(diǎn)像，可以聯(lián)系理解一下，下標(biāo)就像偏移量，都是從0開始。

下篇文章是MySQL中的內(nèi)置函數(shù)的講解。點(diǎn)個(gè)關(guān)注不迷路。