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

﹏ヽ暗。殇╰゛Y 2022-01-10 09:59 213阅读 0赞

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