16、MySQL的数据汇聚

确定表中行数（或者满足某个条件或包含某个特定值得行数）
获得表中航组的和
找出表列（或所有行或某些特定的行）的最大值、最小值和平均值
count sum max min avg
1、avg聚合函数 平均值

mysql> select avg(prod_price) from products;
+-----------------+
| avg(prod_price) |
+-----------------+
| 16.133571       |
+-----------------+

带条件查询平均值

mysql> select avg(prod_price) from products where vend_id = '1005';
+-----------------+
| avg(prod_price) |
+-----------------+
| 45.000000       |
+-----------------+

2、count函数

mysql> select count(*) as num_count from products;
+-----------+
| num_count |
+-----------+
|        14 |
+-----------+

3、max/min函数

mysql> select max(prod_price) from products;
+-----------------+
| max(prod_price) |
+-----------------+
| 55.00           |
+-----------------+
1 row in set (0.07 sec)

mysql> select min(prod_price) from products;
+-----------------+
| min(prod_price) |
+-----------------+
| 2.50            |
+-----------------+
1 row in set (0.07 sec)

连用

mysql> select min(prod_price), max(prod_price), avg(prod_price) from products;
+-----------------+-----------------+-----------------+
| min(prod_price) | max(prod_price) | avg(prod_price) |
+-----------------+-----------------+-----------------+
| 2.50            | 55.00           | 16.133571       |
+-----------------+-----------------+-----------------+

4、sum（）求和函数 计算订单总价

mysql> select sum(quantity * item_price) from orderitems;
+----------------------------+
| sum(quantity * item_price) |
+----------------------------+
| 1368.34                    |
+----------------------------+

5、多个汇总的使用

mysql> select count(*), sum(prod_price), max(prod_price), min(prod_price), avg(prod_price) from products;
+----------+-----------------+-----------------+-----------------+-----------------+
| count(*) | sum(prod_price) | max(prod_price) | min(prod_price) | avg(prod_price) |
+----------+-----------------+-----------------+-----------------+-----------------+
|       14 | 225.87          | 55.00           | 2.50            | 16.133571       |
+----------+-----------------+-----------------+-----------------+-----------------+