MySQL基础之汇总数据

聚集函数

经常需要汇总数据而不用把他们实际检索出来。这种类型的检索例子有以下几种

确定表中的行数(或者满足某个条件或包含某个特定值的行数)
获得表中行组的和
找出表列(或所有行或某些特定的行)的最大值、最小值和平均值

为了方便这种类型的检索, MySQL给出了5个聚集函数。

聚集函数(aggregate function): 运行在行组上, 计算和返回单个值的函数

AVG函数

AVG()通过对表中的行数计数并计算特定列值的和, 求得该列的平均值。AVG()可用来返回所有列的平均值, 也可以返回特定列或行的平均值

例一: 返回所有产品的平均价格

MariaDB [crashcourse]> SELECT AVG(prod_price) AS avg_price FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.002 sec)

MariaDB [crashcourse]>

例二: 返回特定供应商所提供的产品的平均价格

MariaDB [crashcourse]> SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
1 row in set (0.002 sec)

MariaDB [crashcourse]>

AVG()函数只能用于特定的数值列的平均值, 而且列名必须作为函数参数给出。为了获得多个列的平均值, 必须使用多个AVG()函数

AVG函数忽略列值为NULL的行

COUNT函数

COUNT()函数可以确定表中行的数目或符合特定条件的行的数目

COUNT()函数有两种使用方式

使用COUNT(*)对表中行的数目进行计数, 不管表列中包含的是空值(NULL)还是非空值
使用COUNT(column)对特定的列中具有值的行进行计数, 忽略NULL值

例1: 返回customers表中客户的总数

MariaDB [crashcourse]> SELECT COUNT(*) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+
1 row in set (0.001 sec)

MariaDB [crashcourse]>

例2: 返回具有电子邮件地址的客户总数

MariaDB [crashcourse]> SELECT COUNT(cust_email) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
|        3 |
+----------+
1 row in set (0.000 sec)

MariaDB [crashcourse]>

如果指定列名, 则指定列的值为空的行被COUNT()函数忽略, 但如果COUNT()函数中用的是星号(*), 则不忽略

MAX()函数

MAX()函数返回指定列的最大值。MAX()要求指定列名

MariaDB [crashcourse]> SELECT MAX(prod_price) AS max_price FROM products;
+-----------+
| max_price |
+-----------+
|     55.00 |
+-----------+
1 row in set (0.001 sec)

MariaDB [crashcourse]>

MAX()函数忽略列值为NULL的行

MIN()函数

MIN()函数返回指定列的最小值。MIN()要求指定列名

MariaDB [crashcourse]> SELECT MIN(prod_price) AS min_price FROM products;
+-----------+
| min_price |
+-----------+
|      2.50 |
+-----------+
1 row in set (0.000 sec)

MariaDB [crashcourse]>

MIN()函数忽略列值为NULL的行

SUM()函数

SUM()用于返回指定列值的和(总计)

MariaDB [crashcourse]> SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
|            19 |
+---------------+
1 row in set (0.001 sec)

MariaDB [crashcourse]>

SUM()也可以用来合计计算值

MariaDB [crashcourse]> SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;
+-------------+
| total_price |
+-------------+
|      149.87 |
+-------------+
1 row in set (0.001 sec)

MariaDB [crashcourse]>

SUM()函数忽略列值为NULL的行

聚集不同的值

以上5个聚集函数都可以如下使用:

对所有的行进行计算, 指定ALL参数或不给参数(因为ALL是默认行为)
只包含不同的值, 指定DISTINCT参数

MariaDB [crashcourse]> SELECT AVG(DISTINCT prod_price)AS avg_price FROM products WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.001 sec)

MariaDB [crashcourse]>

注意: 如果指定列名, 则DISTINCT只能用于COUNT(), DISTINCT不能用于COUNT(*)。类似的, DISTINCT必须使用列名, 不能用于计算或表达式

组合聚集函数

SELECT语句可以根据需要包含多个聚集函数

MariaDB [crashcourse]> SELECT COUNT(*) AS num_items,
    -> Min(prod_price) AS price_min,
    -> MAX(prod_price) AS price_max,
    -> AVG(prod_price) AS price_avg
    -> FROM products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
1 row in set (0.000 sec)

MariaDB [crashcourse]>

在指定别名以包含某个聚集函数的结果时, 不应该用表中实际的列名。虽然这样做并非不合法, 但使用唯一的名字会使SQL更易于理解和使用