确定表中行数(或者满足某个条件或包含某个特定值得行数)
获得表中航组的和
找出表列(或所有行或某些特定的行)的最大值、最小值和平均值
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 |
+----------+-----------------+-----------------+-----------------+-----------------+