bitscn.com
mysql学习足迹记录10--汇总数据--max(),min(),avg(),sum(),count()
本文所用到的数据
  mysql> select prod_price from products;+------------+| prod_price |+------------+| 5.99 || 9.99 || 14.99 || 13.00 || 10.00 || 2.50 || 3.42 || 35.00 || 55.00 || 8.99 || 50.00 || 4.49 || 2.50 || 10.00 |+------------+14 rows in set (0.00 sec)
1.聚集函数
avg(): 返回某列的平均值
count(): 返回会某列的行数
max(): 返回会某列的最大值
min(): 返回会某列的最小值
sum(): 返回会某列值之和
2.avg()函数
examples:mysql> select avg(prod_price) as avg_price -> from products;+-----------+| avg_price |+-----------+| 16.133571 |+-----------+1 row in set (0.01 sec)*返回特定列或行的平均值 examples: mysql> select avg(prod_price) as avg_price #过滤出vend_id为1003的产品，再求平均值 -> from products -> where vend_id = 1003;+-----------+| avg_price |+-----------+| 13.212857 |+-----------+1 row in set (0.00 sec)
tips:
avg()只能用来求特定数值列的平均值，为了获得多个列的平均值，必须使用多个avg()函数
avg()函数忽略列值为null的行
3.count()函数
*count（*）对表中行的数目进行计数，不管列标中包含的是空值（null）还是非空值
*count（column)对特定的列中具有值的行进行计数，忽略null值
examples: mysql> select count(*) as count_prod from products;+------------+ #products表中行的数目进行计数| count_prod |+------------+| 14 |+------------+1 row in set (0.00 sec)先列出cust_email的内容mysql> select cust_email from customers;+---------------------+| cust_email |+---------------------+| ylee@coyote.com || null || rabbit@wascally.com || sam@yosemite.com || null |+---------------------+5 rows in set (0.00 sec) 对cust_email进行计数mysql> select count(cust_email) as num_cust -> from customers; #忽略null值+----------+| num_cust |+----------+| 3 |+----------+1 row in set (0.00 sec)
4.max（）函数
返回指定列中的最大值,忽略null值
examples: mysql> select max(prod_price) as max_price -> from products;+-----------+| max_price |+-----------+| 55.00 |+-----------+1 row in set (0.00 sec)
5.min（）函数
*返回指定列的最小值
mysql> select min(prod_price) as min_price -> from products;+-----------+| min_price |+-----------+| 2.50 |+-----------+1 row in set (0.00 sec)
6.sum（）函数
*返回指定列值的和
mysql> select sum(prod_price) as sum_price -> from products;+-----------+| sum_price |+-----------+| 225.87 |+-----------+1 row in set (0.00 sec)
*sum也可用来合计计算值
examples:
下面先列出要计算的数据
mysql> select item_price,quantity -> from orderitems -> where order_num = 20005;+------------+----------+| item_price | quantity |+------------+----------+| 5.99 | 10 || 9.99 | 3 || 10.00 | 5 || 10.00 | 1 |+------------+----------+4 rows in set (0.01 sec)mysql> select sum(item_price*quantity) as total_price -> from orderitems #返回订单中所有的物品价钱之和 -> where order_num = 20005;+-------------+| total_price |+-------------+| 149.87 |+-------------+1 row in set (0.00 sec)
7.聚集不同的值，关键字distinct
对于sum（），max（），min（），avg（），count（），默认的参数为all，如果要计算只包含不同的值，需指定distinct参数
examples: mysql> select avg(distinct prod_price) as avg_price -> from products -> where vend_id = 1003;+-----------+| avg_price |+-----------+| 15.998000 |+-----------+1 row in set (0.02 sec)
8.组合聚集函数
eg: mysql> select count(*) as num_items, -> min(prod_price) as price_min, -> max(prod_price) as price_min, -> avg(prod_price) as price_avg -> from products;+-----------+-----------+-----------+-----------+| num_items | price_min | price_min | price_avg |+-----------+-----------+-----------+-----------+| 14 | 2.50 | 55.00 | 16.133571 |+-----------+-----------+-----------+-----------+1 row in set (0.00 sec)
bitscn.com