mysql为什么avg会计算空行,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)