MySQL學習足跡記錄10--匯總數據--MAX(),MIN(),AVG(),SUM(),COUNT()

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)