快速回顧MySQL：匯總和分組-有解無憂

10.3 匯總資料

我們經常需要匯總資料而不用把它們實際檢索處出來，為此MySQL提供了專門的函式，使用這些函式，MySQL查詢可用于檢索資料，以便分析和報表的生成，這種型別的檢索例子有以下幾種：

確定表中的行數（或者滿足某個條件或包含某個特定值的行數），
獲得表中行組的和，
找出表列（或所有行或某些特定的行）的最大值、最小值和平均值，

上述的例子都需要對表中資料（而不是實際資料本身）匯總，因此，回傳實際表資料是對時間和處理資源的一種浪費（更不用說帶寬了），

MySQL提供了5個聚集函式，

聚集函式（aggregate function）運行在行組上，計算和回傳單個值的函式，

函式	說明
AVG()	回傳某列的平均值
COUNT()	回傳某列的行數
MAX()	回傳某列的最大值
MIN()	回傳某列的最小值
SUM()	回傳某列值之和

下面簡單介紹這些函式

先創建下面的表：

CREATE TABLE student(
    sid INT PRIMARY KEY,
    sname VARCHAR(20),
    ssex  CAHR(1)
);
CREATE TABLE score(
    sid INT PRIMARY KEY,
    sname VARCHAR(20),
    smath INT,
    senglish INT
);

10.3.1 AVG()函式

AVG()通過對表中行數計數并計算特定列值之和，求得該列的平均值，AVG()可用來回傳所有列的平均值，也可以用來回傳特定列平均值，

AVG()只能用來確定特定數值列的平均值，而且列名必須作為函式引數給出，為了獲得多個列的平均值，必須使用多個AVG()函式，
AVG()函式忽略列值為NULL的行，

來使用AVG()函式來查詢成績表score中數學成績的平均分（自己隨便加上幾個數）：

SELECT AVG(smath) AS avg_smath FROM score;

AVG()也可用用來確定特定列平均值，配合WHERE子句，

10.3.2 COUNT()函式

COUNT()函式確定表中行的數目或符合特定條件的行的數目，

COUNT()函式有兩種使用的方式：

使用COUNT(*)對表中行的數目進行計數，不管表列中包含的是空值（NULL）還是非空值，
使用COUNT(column)對特定列中具有值的行進行計數，忽略NULL值，

比如：回傳學生總數：

SELECT COUNT(*) FROM student;

回傳女生總數（特定列）：

SELECT COUNT(ssex) FROM student WHERE ssex='女';

如果指定列名，則指定列的值為空的行被COUNT()函式忽略，但如果COUNT()函式中用的是星號（*），則不忽略，

10.3.3 MAX()函式

MAX() 回傳指定列中的最大值，MAX() 要求指定列名，

比如：回傳成績表中數學成績的最高分：

SELECT MAX(smath) FROM score;

雖然MAX()一般用來找出最大的數值或日期值，但MySQL允許將它用來回傳任意列中的最大值，包括回傳文本列中的最大值，在用于文本資料時，如果資料按相應的列排序，則MAX()回傳最后一行，MIN()函式也是，但是相反，回傳最前面的行，

MAX()、MIN()都是忽略列值為NULL的行，

10.3.4 MIN()函式

MIN() 回傳指定列中的最小值，MIN() 要求指定列名，

比如：回傳成績表中數學成績的最低分：

SELECT MIN(smath) FROM score;

10.3.5 SUM()函式

SUM()用來回傳指定列值的和（總計），

比如回傳一個班中的數學成績的總分：

SELECT SUM(smath) FROM score;

利用標準的算術運算子，所有的聚集函式都可以用來執行多個列上的計算，

SUM()函式忽略列值為NULL的行，

10.3.6 聚集不同值

以上5個聚集函式都可以如下使用：

對所有的行執行計算，指定引數或不給引數（MAX和MIN函式）；
只包含不同的值，指定DISTINCT引數，（去重）

下面舉例指定DISTINCT引數的用法：比如成績表中數學成績的平均分，平均分只考慮各個不同的分數：

SELECT AVG(DISTINCT smath) AS avg_smath FROM score;

如果指定列名，則DISTINCT只能用于COUNT()，DISTINCT不能用于COUNT(*)，因此不允許使用COUNT(DISTINCT)，否則會產生錯誤，類似地，DISTINCT必須使用列名，不能用于計算或運算式，

DISTINCT可用于MAX()和MIN()，但是沒有意義，

10.3.7 組合聚集函式

實際上SELECT陳述句可根據需要包含多個聚集函式，

使用聚集函式，最好使用別名，

小結：聚集函式用來匯總資料，這些函式都是高效設計，它們回傳的結果一般比你在自己的客戶機應用程式中計算要快得多，

10.4 分組資料

前面已經學習了SQL聚集函式來匯總資料，但是，比如：要求出學生表中男生和女生各有多少人，可用使用兩條陳述句分別列印，下面介紹另一種方式：分組，

分組：把資料分為多個邏輯組，以便能對每個組進行聚集計算，

10.4.1 創建分組

分組時在SELECT陳述句的GROUP BY子句中建立的，按照上面的例子，對學生表的男女生分組：

SELECT ssex, COUNT(ssex) AS total FROM student GROUP BY ssex;

輸出：

+---------+----------------+
| stu_sex |      total     |
+---------+----------------+
| 女      |              6 |
| 男      |             11 |
+---------+----------------+
3 rows in set (0.05 sec)

但是也可這樣使用：

SELECT ssex, COUNT(*) AS total FROM student GROUP BY ssex;

輸出結果同上，

為什么會這樣？？因為使用了GROUP BY，就不必指定要計算和估值得每個組了，系統會自動完成，GROUP BY子句指示MySQL分組資料，然后對每個組而不是整個結果集進行聚集，

在具體使用GROUP BY子句前，需要知道一些重要的規定：

GROUP BY子句可以包括任意數目的列，這使得能對分組進行嵌套，為資料分組提供更細致的控制，
如果在GROUP BY子句中嵌套了分組，資料將在最后規定的分組上進行匯總，換句話說，在建立分組時，指定的所有列都一起計算（所以不能從個別的列取回資料），
GROUP BY子句中列出的每個列都必須是檢索列或有效的運算式（但不能是聚集函式），如果在SELECT中使用運算式，則必須在GROUP BY子句中指定相同的運算式，不能使用別名，
除聚集計算陳述句外，SELECT陳述句中的每個列都必須在GROUP BY子句中給出，
如果分組列中具有NULL值，則NULL將作為一個分組回傳，如果列中有多行NULL值，它們將分為一組，
GROUP BY子句必須出現在WHERE子句之后，ORDER BY子句之前，

使用WITH ROLLUP關鍵字，可以得到每個分組以及每個分組匯總級別（針對每個分組）的值
比如：

SELECT ssex, COUNT(*) AS total 
FROM student 
GROUP BY ssex WITH ROLLUP;

輸出：

+---------+-------+
| stu_sex | total |
+---------+-------+
| 女      |    10 |
| 男      |    16 |
| NULL    |    26 |
+---------+-------+
3 rows in set (0.05 sec)

引入一個函式：IFNULL(expression, alt_value):IFNULL() 函式用于判斷第一個運算式是否為 NULL，如果為 NULL 則回傳第二個引數的值，如果不為 NULL 則回傳第一個引數的值，

SELECT IFNULL(ssex,'總計')  AS ssex, COUNT(*) AS total 
FROM student 
GROUP BY ssex WITH ROLLUP;

輸出：

+---------+-------+
| stu_sex | total |
+---------+-------+
| 女      |    10 |
| 男      |    16 |
| 總計    |    26 |
+---------+-------+
3 rows in set (0.05 sec)

10.4.2 過濾分組

MySQL允許過濾分組，規定包括哪些分組，排除哪些分組，但是WHERE子句過濾指定的是行而不是分組，所以MySQL提供了HAVING子句，HAVING子句類似于WHERE，至今為止的所有型別的WHERE子句都可以用HAVING子句來替代，唯一的差別是WHERE子句過濾行，而HAVING子句過濾分組（即是先分組再過濾），

比如：顯示學生表的男生或者女生人數大于15個的，

SELECT ssex, COUNT(*) AS total 
FROM student 
GROUP BY ssex HAVING COUNT(*)>=15;

注意：HAVING子句在GROUP BY之后，因為是對分組的結果進行過濾，而WHERE子句是對行進行過濾，所以必須在GROUP BY之前，所以WHERE子句過濾的行可能會影響到GROUP BY的分組結果，

那么可以同時使用WHERE子句和HAVING子句嗎？？答案是可以的，

10.4.3 分組和排序

說一說排序（ORDER BY）和分組（GROUP BY）的區別：

排序	分組
排序產生的輸出	輸出可能不是分組的順序
任意列都可以使用（甚至非選擇的列也可以使用）	只能使用選擇列或運算式列，而且必須使用每個選擇列運算式
不一定需要	如果與聚集函式一起使用列（或運算式），則必須使用

上面表格的第一行，可以經常發現GROUP BY分組的資料確實是以分組順序輸出的，但情況并不總這樣，它并不是SQL規范所要求的，此外，用戶可能會要求以不同于分組的順序排序，僅因為你以某種方式分組資料，并不表示你需要以相同的方式排序輸出，應該提供明確的BRDER BY子句，即使其效果等同于GROUP BY子句的輸出結果，

所以一般使用GROUP BY子句最好跟上ORDER BY子句，

那么在位置如何放置：ORDER BY子句一定是放在sQL陳述句最后的，

所以修改上面的陳述句：例子舉得不是很好，就看看怎么寫，

SELECT ssex, COUNT(*) AS total 
FROM student 
GROUP BY ssex 
HAVING COUNT(*)>=15
ORDER BY ssex;

小結：學習了如何用SQL聚集函式對資料進行匯總計算，也介紹如何使用GROUP UP子句對資料組進行這些匯總金酸，回傳每個組的結果，而且還學了HAVING子句過濾特定的組，WHERE和HAVING之間以及ORDER BY和GROUP BY之間的差異

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/86018.html

標籤：MySQL

上一篇：mysql查詢時不區分大小寫

下一篇：MySql筆記(一)