MySQL聚合函数查询

【图书推荐】《MySQL 9从入门到性能优化（视频教学版）》-优快云博客

《MySQL 9从入门到性能优化（视频教学版）（数据库技术丛书）》(王英英)【摘要 书评 试读】- 京东图书 (jd.com)

MySQL9数据库技术_夏天又到了的博客-优快云博客

有时候并不需要返回表中的实际数据，而需要对数据进行总结。MySQL提供了一些聚合函数，可以对获取的数据进行分析和报告。这些函数的功能包括计算数据表中记录行数的总数、计算某个字段列下数据的总和，以及计算表中某个字段下的最大值、最小值或者平均值。这些聚合函数的名称和作用如表5.2所示。

5.3.1  COUNT()函数

COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。其使用方法有两种：

• COUNT(*) ：计算表中的总行数，而不管某列是否有数值或者是否为NULL。
• COUNT(字段名)：计算指定列下的总行数，计算时将忽略NULL的行。

【例5.34】查询表customers中的总行数，SQL语句如下：

mysql> SELECT COUNT(*) AS cust_num FROM customers;
+----------+
| cust_num |
+----------+
|        4   |
+----------+

由查询结果可以看到，COUNT(*)返回表customers中记录的总行数，而不管其值是什么，返回的总数的名称为cust_num。

【例5.35】查询表customers中有电子邮箱的顾客的总数，SQL语句如下：

mysql> SELECT COUNT(c_email) AS email_num  FROM customers;
+-----------+
| email_num |
+-----------+
|         3   |
+-----------+

由查询结果可以看到，只有3个顾客有email，email为NULL的记录没有被COUNT()函数  计算。

前面介绍分组查询的时候，介绍了如何用COUNT()函数与GROUP BY关键字一起来计算不同分组中的记录总数。下面再来看一个例子。

【例5.36】在表orderitems中，使用COUNT()函数统计不同订单号中订购的水果种类，SQL语句如下：

mysql> SELECT o_num, COUNT(f_id) FROM orderitems GROUP BY o_num;
+-------+-------------+
| o_num | COUNT(f_id) |
+-------+-------------+
| 30001 |           4   |
| 30002 |           1   |
| 30003 |           1   |
| 30004 |           1   |
| 30005 |           4   |
+-------+-------------+

从查询结果中可以看到，GROUP BY关键字先按照订单号进行分组，然后计算每个分组中的总记录数。

5.3.2  SUM()函数

SUM()是一个求和函数，返回指定列值的总和。

【例5.37】在表orderitems中，查询30005号订单一共购买的水果的总量，SQL语句如下：

mysql>SELECT SUM(quantity) AS items_total FROM orderitems WHERE o_num = 30005;
+-------------+
| items_total |
+-------------+
|     30        |
+-------------+

SUM(quantity)函数返回订单中所有水果数量之和，WHERE子句指定查询的订单号为30005。

SUM()可以与GROUP BY一起使用，来计算每个分组的总和。

【例5.38】在表orderitems中，使用SUM()函数统计不同订单号中订购的水果的总量，SQL语句如下：

mysql> SELECT o_num, SUM(quantity) AS items_total FROM orderitems GROUP BY o_num;
+-------+-------------+
| o_num | items_total |
+-------+-------------+
| 30001 |          33   |
| 30002 |           2   |
| 30003 |         100   |
| 30004 |          50   |
| 30005 |          30   |
+-------+-------------+

由查询结果可以看到，GROUP BY按照订单号o_num进行分组，SUM()函数计算每个分组中订购的水果的总量。

5.3.3  AVG()函数

AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

【例5.39】在表fruits中，查询s_id=103的水果供应商的水果价格平均值，SQL语句如下：

mysql> SELECT AVG(f_price) AS avg_price FROM fruits WHERE s_id = 103;
+-----------+
| avg_price |
+-----------+
|  5.700000 |
+-----------+

本例中，查询语句增加了一个WHERE子句，并且添加了查询过滤条件，只查询s_id = 103的记录中的f_price。因此，通过AVG()函数计算的结果只是指定的供应商水果的价格平均值，而不是市场上所有水果价格的平均值。

AVG()可以与GROUP BY一起使用，来计算每个分组的平均值。

【例5.40】在表fruits中，查询每一个水果供应商的水果价格的平均值，SQL语句如下：

mysql> SELECT s_id,AVG(f_price) AS avg_price FROM fruits GROUP BY s_id;
+------+-----------+
| s_id | avg_price |
+------+-----------+
|  101 |  6.200000  |
|  103 |  5.700000  |
|  104 |  7.000000  |
|  107 |  3.600000  |
|  102 |  8.933333  |
|  105 |  7.466667  |
|  106 | 15.700000  |
+------+-----------+

GROUP BY关键字根据s_id字段对记录进行分组，然后计算出每个分组的平均值。这种分组求平均值的方法非常有用，例如，求不同班级学生成绩的平均值、求不同部门员工的平均工资、求各地的年平均气温等。

5.3.4  MAX()函数

MAX()返回指定列中的最大值。

【例5.41】在表fruits中，查找市场上水果价格的最高值，SQL语句如下：

mysql>SELECT MAX(f_price) AS max_price FROM fruits;
+------------+
| max_price  |
+------------+
|   15.70     |
+------------+

由结果可以看到，MAX()函数查询出f_price字段的最大值为15.70。

MAX()也可以和GROUP BY关键字一起使用，来计算每个分组中的最大值。

【例5.42】在表fruits中，查找不同水果供应商提供的水果的最高价格，SQL语句如下：

mysql> SELECT s_id, MAX(f_price) AS max_price FROM fruits GROUP BY s_id;
+------+-----------+
| s_id | max_price |
+------+-----------+
|  101 |     10.20  |
|  103 |      9.20  |
|  104 |      7.60  |
|  107 |      3.60  |
|  102 |     11.20  |
|  105 |     11.60  |
|  106 |     15.70  |
+------+-----------+

由结果可以看到，GROUP BY关键字根据s_id字段对记录进行分组，然后计算出每个分组中的最大值。

MAX()函数不仅适用于数值类型，也可用于字符类型。

【例5.43】在表fruits中查找f_name的最大值，SQL语句如下：

mysql> SELECT MAX(f_name) FROM fruits;
+-------------+
| MAX(f_name) |
+-------------+
| xxxx          |
+-------------+

由结果可以看到，MAX()函数可以对字母进行大小判断，并返回最大的字符或者字符串。

5.3.5  MIN()函数

MIN()返回指定列中的最小值。

【例5.44】在表fruits中，查找市场上水果价格的最低值，SQL语句如下：

mysql>SELECT MIN(f_price) AS min_price FROM fruits;
+-----------+
| min_price |
+-----------+
|   2.20     |
+-----------+

由结果可以看到，MIN()函数查询出f_price字段的最小值为2.20。

MIN()也可以和GROUP BY关键字一起使用，用来计算每个分组中的最小值。

【例5.45】在表fruits中，查找不同水果供应商提供的水果的最低价格，SQL语句如下：

mysql> SELECT s_id, MIN(f_price) AS min_price  FROM fruits GROUP BY s_id;
+------+-----------+
| s_id | min_price |
+------+-----------+
|  101 |      3.20   |
|  103 |      2.20   |
|  104 |      6.40   |
|  107 |      3.60   |
|  102 |      5.30   |
|  105 |      2.60   |
|  106 |     15.70   |
+------+-----------+

由结果可以看到，GROUP BY关键字根据s_id字段对记录进行分组，然后计算出每个分组中的最小值。MIN()函数与MAX()函数类似，不仅适用于数值类型，也可用于字符类型。