MySQL学习足迹记录10--汇总数据--MAX(),MIN(),AVG(),SUM(),COUNT_MySQL

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