聚集函数
汇总数据的案例:
- 确定表中的行
- 获得表中某些行的和
- 找出表列的最大值,最小值,平均值
聚集函数
对某些行运行的函数,计算并返回一个值
函数 | 说明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的函数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最大值 |
SUM() | 返回某列之和 |
AVG( )函数
通过对表中行数计数并计算其列值之和,求得该列的平均值
返回PRODUCTS表中所有产品的平均价格
mysql> SELECT AVG(PROD_PRICE) AS AVG_PRICE
-> FROM PRODUCTS;
+-----------+
| AVG_PRICE |
+-----------+
| 6.823333 |
+-----------+
返回特定供应商所提供产品的平均价格
mysql> SELECT AVG(PROD_PRICE) AS AVG_PRICE
-> FROM PRODUCTS
-> WHERE VEND_ID='DLL01';
+-----------+
| AVG_PRICE |
+-----------+
| 3.865000 |
+-----------+
AVG( )函数只能用于单个列
它会自动忽略列值为NULL的行
COUNT( )函数
COUNT( )函数可以确定表中行的数目或者符合特定条件的行的数目
COUNT( )函数的使用方式:
- 使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值NULL还是非空值
- 使用COUNT(column)对特定列中具有值的行进行计数,忽略NULL值
返回CUSTOMERS表中顾客的总数
mysql> SELECT COUNT(*) AS SUM_CUSTOMERS
-> FROM CUSTOMERS;
+---------------+
| SUM_CUSTOMERS |
+---------------+
| 5 |
+---------------+
对具有电子邮件地址的客户进行计数
mysql> SELECT COUNT(CUST_EMAIL) AS SUM_EMAIL
-> FROM CUSTOMERS;
+-----------+
| SUM_EMAIL |
+-----------+
| 3 |
+-----------+
当指定了计数的列名,COUNT( )函数会自动忽略NULL值
如果是COUNT(*),则不会忽略
MAX( )函数
MAX( )函数返回列的最大值,要求指定列名
返回PRODUCTS表中价格最贵的产品
mysql> SELECT MAX(PROD_PRICE) AS MAX_PRICE
-> FROM PRODUCTS;
+-----------+
| MAX_PRICE |
+-----------+
| 11.99 |
+-----------+
MAX( )函数也可以用于返回文本列中的最大值。当用于文本数据时,MAX( )返回按该列排序后的最后一行
MAX( )忽略NULL
MIN( )函数
MIN( )函数返回指定列的最小值
返回PRODUCTS表中价格最便宜的产品
mysql> SELECT MIN(PROD_PRICE) AS MIN_PRICE
-> FROM PRODUCTS;
+-----------+
| MIN_PRICE |
+-----------+
| 3.49 |
+-----------+
MIN( )函数也可以用于返回文本列中的最小值。当用于文本数据时,MAX( )返回按该列排序后最前面的行
MIN( )忽略NULL
SUM( )函数
SUM( )函数返回指定列值的和(总和)
ORDERITEMS表中包含订单中实际的物品,每个物品有相应的数量。
检索所订购物品的总和(所有quantity值之和)
mysql> SELECT SUM(QUANTITY) AS SUM_QUANTITY
-> FROM ORDERITEMS;
+--------------+
| SUM_QUANTITY |
+--------------+
| 1430 |
+--------------+
mysql> SELECT SUM(QUANTITY) AS SUM_QUANTITY
-> FROM ORDERITEMS
-> WHERE ORDER_NUM=20005;
+--------------+
| SUM_QUANTITY |
+--------------+
| 200 |
+--------------+
SUM( )也可以用来合计计算值。
合计每项物品的ITEM_PRICE*QUANTITY,得出总的订单金额
mysql> SELECT SUM(ITEM_PRICE*QUANTITY) AS TOTAL_PRICE
-> FROM ORDERITEMS;
+-------------+
| TOTAL_PRICE |
+-------------+
| 5730.70 |
+-------------+
mysql> SELECT SUM(ITEM_PRICE*QUANTITY) AS TOTAL_PRICE
-> FROM ORDERITEMS
-> WHERE ORDER_NUM=20005;
+-------------+
| TOTAL_PRICE |
+-------------+
| 1648.00 |
+-------------+
SUM( )忽略NULL
聚集不同值
以上5个聚集函数都可以使用下述参数
ALL
ALL参数不需要指定,因为它是默认行为。
DISTINCT
只包含不同的值
使用AVG( )函数返回的平均价格是不准确的,因为AVG不会区分相同值
使用DISTINCT参数,只考虑不同的价格
mysql> SELECT AVG(DISTINCT PROD_PRICE) AS AVG_PRICE
-> FROM PRODUCTS
-> WHERE VEND_ID='DLL01';
+-----------+
| AVG_PRICE |
+-----------+
| 4.240000 |
+-----------+
DISTINCT参数不能用于COUNT(*)
组合聚集函数
多个聚集函数时,需要使用逗号,
进行区分
mysql> SELECT COUNT(*) AS NUM_ITEMS,
-> MIN(PROD_PRICE) AS MIN_PRICE,
-> MAX(PROD_PRICE) AS MAX_PRICE,
-> AVG(DISTINCT PROD_PRICE) AS AVG_PRICE
-> FROM PRODUCTS;
+-----------+-----------+-----------+-----------+
| NUM_ITEMS | MIN_PRICE | MAX_PRICE | AVG_PRICE |
+-----------+-----------+-----------+-----------+
| 9 | 3.49 | 11.99 | 7.490000 |
+-----------+-----------+-----------+-----------+