聚集函数
经常需要汇总数据而不用把他们实际检索出来。这种类型的检索例子有以下几种
- 确定表中的行数(或者满足某个条件或包含某个特定值的行数)
- 获得表中行组的和
- 找出表列(或所有行或某些特定的行)的最大值、最小值和平均值
为了方便这种类型的检索, 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更易于理解和使用