说明
有时候我们需要汇总数据信息而不是将数据本身的内容检索出来,比如统计某张表中的行数(不关心具体内容),MySQL提供了5个聚集函数来进行数据汇总操作:
1. AVG()
:返回某列的平均值
2. COUNT()
:返回某列的行数
3. MAX()
:返回某列的最大值
4. MIN()
:返回某列的最小值
5. SUM()
:返回某列之和
操作
- AVG()
mysql> SELECT AVG(prod_price) AS avg_price FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.00 sec)
可以指定求特定行的平均值
mysql> SELECT AVG(prod_price) AS abg_price FROM products WHERE vend_id = 1003;
+-----------+
| abg_price |
+-----------+
| 13.212857 |
+-----------+
1 row in set (0.01 sec)
- COUNT()
使用COUNT(*)对表中行的数目进行计数,不管表中包含的是否是空值(NULL)
mysql> SELECT COUNT(*) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
使用COUNT(column)对指定的列进行计数,忽略值为NULL的项
mysql> SELECT COUNT(*) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
- MAX()
mysql> SELECT COUNT(*) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
- MIN()
mysql> SELECT MIN(prod_price) AS min_price FROM products;
+-----------+
| min_price |
+-----------+
| 2.50 |
+-----------+
1 row in set (0.00 sec)
- SUM
SUM()用来返回指定列值得和,也可以迎来合计计算值,执行时会忽略值为NULL的行,例如orderitems表中包含了订单汇总的实际物品,表达内容如下:
mysql> SELECT * FROM orderitems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
| 20005 | 1 | ANV01 | 10 | 5.99 |
| 20005 | 2 | ANV02 | 3 | 9.99 |
| 20005 | 3 | TNT2 | 5 | 10.00 |
| 20005 | 4 | FB | 1 | 10.00 |
| 20006 | 1 | JP2000 | 1 | 55.00 |
| 20007 | 1 | TNT2 | 100 | 10.00 |
| 20008 | 1 | FC | 50 | 2.50 |
| 20009 | 1 | FB | 1 | 10.00 |
| 20009 | 2 | OL1 | 1 | 8.99 |
| 20009 | 3 | SLING | 1 | 4.49 |
| 20009 | 4 | ANV03 | 1 | 14.99 |
+-----------+------------+---------+----------+------------+
11 rows in set (0.00 sec)
检索指定订单所订购的物品总数
mysql> SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
| 19 |
+---------------+
1 row in set (0.00 sec)
计算指定订单每项物品的单价乘以订购数量,得出总的订单金额
mysql> SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
| 19 |
+---------------+
1 row in set (0.00 sec)
聚集不同值
以上5个聚集函数默认都是对所有行执行计算,默认参数为ALL
,也可以只包含不同的值,需要指定参数DISTINCT
,如返回特定供应商提供的产品的平均价格,正确做应该是每个价钱不同的商品只取一件来进行计算,只考虑不同的价格
mysql> SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.00 sec)
可以看到求出的平均价格比上面求得的要高,因为之前的计算中有多个价格较低的相同物品一起被计算。
注意 使用DISTINCT
参数时必须指定列名,不能用于子计算或表达式,同时对MAX()和MIN()使用DISTINCT没有意义,最大值最小值计算不受重复值影响。
一条语句中也可以包含多个聚集函数
mysql> 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.00 sec)