數據庫基礎知識——聚合函數、分組查詢

一、聚合函數

1.1 count

1.1.1 統計整張表中所有記錄的總條數

1.1.2 統計單列的數據

1.1.3 統計單列記錄+限制條件

1.2 sum

1.3 avg

1.4 max, min

二、group by 分組查詢

2.1 語法

2.2 示例

2.3 having

一、聚合函數

常用的聚合函數

函數	說明
count ([distinct] expr)	返回查詢到的數據的數量
sum ([distinct] expr)	返回查詢到的數據的總和，不是數字的沒有意義
avg ([distinct] expr)	返回查詢到的數據的平均值，非數字無意義
max ([distinct] expr)	返回查詢到的數據的最大值，非數字無意義
min ([distinct] expr)	返回查詢到的數據的最小值，非數字無意義

1.1 count

mysql> select * from books;
+----+--------------------------+-----------+-------+-----------+---------------------+
| id | book_name                | author    | price | book_type | publish_date        |
+----+--------------------------+-----------+-------+-----------+---------------------+
| 29 | 阿波羅的秘密             | NULL      | 65.00 | NULL      | 2011-01-01 09:30:00 |
| 30 | 馬達加斯加的企鵝         | 薩克斯    | 15.00 | NULL      | 2013-03-12 10:30:00 |
| 31 | 極簡主義                 | NULL      | 32.00 | NULL      | 2019-04-28 00:50:00 |
| 32 | 另一種顏色               | NULL      | 32.50 | NULL      | 2019-10-01 07:10:00 |
| 33 | 夏日清泉                 | NULL      | 26.00 | NULL      | 2017-07-21 14:30:00 |
| 34 | NULL                     | NULL      | 34.00 | NULL      | 2014-06-01 11:11:00 |
| 35 | 冬日暖陽                 | 徐然      | 46.30 | NULL      | 2019-02-28 18:11:00 |
| 36 | 22歲                     | 桂芽紫    | 55.70 | NULL      | 2018-03-21 11:11:00 |
| 37 | 森林里有什么             | NULL      | 48.00 | NULL      | 2020-05-30 08:10:00 |
| 38 | 情書                     | NULL      |  NULL | NULL      | 2010-12-01 13:30:00 |
| 39 | 八音盒                   | switch    |  NULL | NULL      | 2021-06-15 15:20:00 |
| 40 | 灼燒的靈魂               | NULL      | 62.28 | NULL      | 2021-09-01 08:35:00 |
+----+--------------------------+-----------+-------+-----------+---------------------+
12 rows in set (0.01 sec)

1.1.1 統計整張表中所有記錄的總條數

? ? ? ? ① 使用 * 做統計【推薦使用】

統計 books 表中有多少條記錄：

mysql> select count(*) from books;
+----------+
| count(*) |
+----------+
|       12 |
+----------+
1 row in set (0.01 sec)

? ? ? ? ② 使用常量做統計

mysql> select count(1) from books;
+----------+
| count(1) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)mysql> select count(100) from books;  # 任意常數，無實際意義
+------------+
| count(100) |
+------------+
|         12 |
+------------+
1 row in set (0.00 sec)

1.1.2 統計單列的數據

統計有多少本書標識了作者姓名

# 指定列做統計，null 值不被計入結果集中

mysql> select count(author) from books;
+---------------+
| count(author) |
+---------------+
|             4 |
+---------------+
1 row in set (0.00 sec)

1.1.3 統計單列記錄+限制條件

統計價格小于40的圖書數量

mysql> select count(*) from books where price < 40;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)mysql> select count(price) from books where price < 40;
+--------------+
| count(price) |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)

1.2 sum

# 只能統計單列數據類型為數值的列，并且值為 null 的數據行不參與統計

mysql> select sum(price) from books;
+------------+
| sum(price) |
+------------+
|     416.78 |
+------------+
1 row in set (0.00 sec)mysql> select sum(author) from books;
+-------------+
| sum(author) |
+-------------+
|           0 |
+-------------+
1 row in set, 4 warnings (0.00 sec)  # 統計非數值的列會發出警告信息mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '薩克斯'    |
| Warning | 1292 | Truncated incorrect DOUBLE value: '徐然'      |
| Warning | 1292 | Truncated incorrect DOUBLE value: '桂芽紫'    |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'switch'    |
+---------+------+-----------------------------------------------+
4 rows in set (0.00 sec)

1.3 avg

括號里面可以是單列數據，也可以是多列數據

-- 單列統計平均值
mysql> select avg(price) from books;
+------------+
| avg(price) |
+------------+
|  41.678000 |
+------------+
1 row in set (0.00 sec)-- 多列統計平均值
mysql> select * from game;
+------+-----------+---------+-------+--------------+--------------+
| id   | name      | lifebar | power | constitution | intelligence |
+------+-----------+---------+-------+--------------+--------------+
|    1 | 孫悟空    |     100 |   100 |          100 |           80 |
|    3 | 沙悟凈    |     100 |    70 |           80 |           77 |
|    6 | 紅孩兒    |     100 |    50 |          100 |           50 |
|    7 | 牛魔王    |     100 |    76 |           89 |           50 |
+------+-----------+---------+-------+--------------+--------------+
4 rows in set (0.00 sec)mysql> select avg(power+constitution+intelligence) as 總屬性值 from game;
+--------------+
| 總屬性值     |
+--------------+
|     230.5000 |
+--------------+
1 row in set (0.00 sec)

1.4 max, min

找出 game 表中 power 最高值和 intelligence 最低值

# 多個聚合函數可以同時被使用

mysql> select max(power),min(intelligence) from game;
+------------+-------------------+
| max(power) | min(intelligence) |
+------------+-------------------+
|        100 |                50 |
+------------+-------------------+
1 row in set (0.00 sec)

# 使用別名

mysql> select max(power) 最高力量值,min(intelligence) '最低智力值' from game;
+-----------------+-----------------+
| 最高力量值      | 最低智力值      |
+-----------------+-----------------+
|             100 |              50 |
+-----------------+-----------------+
1 row in set (0.00 sec)

# 同一列可以使用不同聚合函數

mysql> select max(power) 最高力量值,min(power) 最低力量值 from game;
+-----------------+-----------------+
| 最高力量值      | 最低力量值      |
+-----------------+-----------------+
|             100 |              50 |
+-----------------+-----------------+
1 row in set (0.00 sec)

二、group by 分組查詢

????????group by 子句的作用是通過?定的規則將?個數據集劃分成若干個小的分組，然后針對若干個分組進行數據處理，比如使用聚合函數對分組進行統計。

2.1 語法

select {列 / 表達式}[,列 / 表達式...] 聚合函數(列 / 表達式) 
from 表名
group by {列 / 表達式}[,列 / 表達式...]
[having 條件]

{列 / 表達式}[,列 / 表達式...]：要查詢的列或表達式，可以有多個，必須在 group by 子句中作為分組的依據；

聚合函數(列 / 表達式)：列或者表達式如果不在 group by 子句中，則必須包含在聚合函數中。

2.2 示例

mysql> update books set book_type = '小說' where book_name = '阿波羅的秘密' or book_name = '另一種顏色' or book_name = '森林里有什么' or book_name = '灼燒的靈魂';
Query OK, 4 rows affected (0.07 sec)
Rows matched: 4  Changed: 4  Warnings: 0mysql> update books set book_type = '言情' where book_name = '情書' or book_name = '22歲' or book_name = '八音盒';
Query OK, 3 rows affected (0.06 sec)
Rows matched: 3  Changed: 3  Warnings: 0mysql> update books set book_type = '散文' where book_name = '夏日清泉' or book_name = '冬日暖陽' or book_name = 'null';
Query OK, 2 rows affected (0.06 sec)  # 影響行數只有兩條，說明null并非字符串
Rows matched: 2  Changed: 2  Warnings: 0mysql> update books set book_type = '雜志' where book_name = '馬達加斯加的企鵝' or book_name = '極簡主義' or book_name = null;
Query OK, 2 rows affected (0.07 sec)  # 影響行數只有兩條，因為null的比較不能使用 =
Rows matched: 2  Changed: 2  Warnings: 0# null的比較應該使用 <=>
mysql> update books set book_type = '雜志' where book_name <=> null;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from books;
+----+--------------------------+-----------+-------+-----------+---------------------+
| id | book_name                | author    | price | book_type | publish_date        |
+----+--------------------------+-----------+-------+-----------+---------------------+
| 29 | 阿波羅的秘密             | NULL      | 65.00 | 小說      | 2011-01-01 09:30:00 |
| 30 | 馬達加斯加的企鵝         | 薩克斯    | 15.00 | 雜志      | 2013-03-12 10:30:00 |
| 31 | 極簡主義                 | NULL      | 32.00 | 雜志      | 2019-04-28 00:50:00 |
| 32 | 另一種顏色               | NULL      | 32.50 | 小說      | 2019-10-01 07:10:00 |
| 33 | 夏日清泉                 | NULL      | 26.00 | 散文      | 2017-07-21 14:30:00 |
| 34 | NULL                     | NULL      | 34.00 | 雜志      | 2014-06-01 11:11:00 |
| 35 | 冬日暖陽                 | 徐然      | 46.30 | 散文      | 2019-02-28 18:11:00 |
| 36 | 22歲                     | 桂芽紫    | 55.70 | 言情      | 2018-03-21 11:11:00 |
| 37 | 森林里有什么             | NULL      | 48.00 | 小說      | 2020-05-30 08:10:00 |
| 38 | 情書                     | NULL      |  NULL | 言情      | 2010-12-01 13:30:00 |
| 39 | 八音盒                   | switch    |  NULL | 言情      | 2021-06-15 15:20:00 |
| 40 | 灼燒的靈魂               | NULL      | 62.28 | 小說      | 2021-09-01 08:35:00 |
+----+--------------------------+-----------+-------+-----------+---------------------+
12 rows in set (0.00 sec)

統計每種類別的書的數量

mysql> select book_type 類型,count(*) 數量 from books group by book_type;
+--------+--------+
| 類型   | 數量   |
+--------+--------+
| 小說   |      4 |
| 雜志   |      3 |
| 散文   |      2 |
| 言情   |      3 |
+--------+--------+
4 rows in set (0.00 sec)

統計每種類別的平均售價，最高售價，最低售價

mysql> select book_type,avg(price),max(price),min(price) from books group by book_type;
+-----------+------------+------------+------------+
| book_type | avg(price) | max(price) | min(price) |
+-----------+------------+------------+------------+
| 小說      |  51.945000 |      65.00 |      32.50 |
| 雜志      |  27.000000 |      34.00 |      15.00 |
| 散文      |  36.150000 |      46.30 |      26.00 |
| 言情      |  55.700000 |      55.70 |      55.70 |
+-----------+------------+------------+------------+
4 rows in set (0.00 sec)

使用 round(數值, 小數點位數) 指定保留多少位小數點，并添加別名優化上面的語句：

mysql> select book_type,round(avg(price),2) 平均售價,max(price) 最高售價,min(price) 最低售價 from books group by book_type;
+-----------+--------------+--------------+--------------+
| book_type | 平均售價     | 最高售價     | 最低售價     |
+-----------+--------------+--------------+--------------+
| 小說      |        51.95 |        65.00 |        32.50 |
| 雜志      |        27.00 |        34.00 |        15.00 |
| 散文      |        36.15 |        46.30 |        26.00 |
| 言情      |        55.70 |        55.70 |        55.70 |
+-----------+--------------+--------------+--------------+
4 rows in set (0.00 sec)

group by 之后還能跟 order by 子句對獲得的結果集進行排序：

mysql> select book_type,round(avg(price),2) 平均售價,max(price)最高售價,min(price) 最低售價 from books group by book_type order by 平均售價 desc;
+-----------+--------------+--------------+--------------+
| book_type | 平均售價     | 最高售價     | 最低售價     |
+-----------+--------------+--------------+--------------+
| 言情      |        55.70 |        55.70 |        55.70 |
| 小說      |        51.95 |        65.00 |        32.50 |
| 散文      |        36.15 |        46.30 |        26.00 |
| 雜志      |        27.00 |        34.00 |        15.00 |
+-----------+--------------+--------------+--------------+
4 rows in set (0.00 sec)

還能統計每個類別有多少本書：

mysql> select book_type,round(avg(price),2),max(price),min(price),count(*) from books group by book_type;
+-----------+---------------------+------------+------------+----------+
| book_type | round(avg(price),2) | max(price) | min(price) | count(*) |
+-----------+---------------------+------------+------------+----------+
| 小說      |               51.95 |      65.00 |      32.50 |        4 |
| 雜志      |               27.00 |      34.00 |      15.00 |        3 |
| 散文      |               36.15 |      46.30 |      26.00 |        2 |
| 言情      |               55.70 |      55.70 |      55.70 |        3 |
+-----------+---------------------+------------+------------+----------+
4 rows in set (0.00 sec)

如果我想對分組之后的結果集進行過濾，比如找出平均售價大于50，小于55的類別，用 where 語句是錯誤的，而應該使用 having 子句 ↓

2.3 having

having 子句必須跟在 group by 子句后面！

mysql> select book_type,round(avg(price),2) 平均售價,max(price)最高售價,min(price) 最低售價 from books group by book_type order by 平均售價 desc having avg(price) between 50 and 55;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'having avg(price) between 50 and 55' at line 1

mysql> select book_type,round(avg(price),2) 平均售價,max(price)最高售價,min(price) 最低售價 from books group by book_type having avg(price) between 50 and 55 order by 平均售價 desc;
+-----------+--------------+--------------+--------------+
| book_type | 平均售價     | 最高售價     | 最低售價     |
+-----------+--------------+--------------+--------------+
| 小說      |        51.95 |        65.00 |        32.50 |
+-----------+--------------+--------------+--------------+
1 row in set (0.07 sec)mysql> select book_type,round(avg(price),2) 平均售價,max(price)最高售價,min(price) 最低售價 from books group by book_type having avg(price) >= 50 and avg(price) <= 55 order by 平均售價 desc;
+-----------+--------------+--------------+--------------+
| book_type | 平均售價     | 最高售價     | 最低售價     |
+-----------+--------------+--------------+--------------+
| 小說      |        51.95 |        65.00 |        32.50 |
+-----------+--------------+--------------+--------------+
1 row in set (0.01 sec)

* where 子句用在 from 表名之后，也就是分組之前，而 having 子句跟在分組 group by 之后。如果需求要求對真實數據進行過濾，同時也需要對分組的結果進行過濾，那么在合適的位置同時寫 where 和? having 即可。