本文所用到的数据
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)
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)