MySQL 自学第十二章 汇总数据

一、聚集函数

1、AVG()函数

取平均值函数

SELECT AVG(prod_price) AS avg_price FROM products;

 

通过使用WHERE进行数据过滤后再取平均值

SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;

 

2、COUNT()函数

计数函数，统计表中数据行数

SELECT COUNT(*) AS num_cust FROM customers;

• NULL值 如果指定列名，则指定列的值为空的行被COUNT()函数忽略，
• 但如果COUNT()函数中用的是星号（*），则不忽略。

 

3、MAX() 函数  & MIN()函数

MAX()返回指定列中的最大值。

SELECT MAX(prod_price) AS max_price FROM products;

MIN()返回指定列中的最小值。

SELECT MIN(prod_price) AS max_price FROM products;

 

4、SUM()函数

SUM()函数返回指定列的求和总计；

SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;

扩展使用

SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;

 

二、聚集不同的值定DISTINCT参数

SELECT prod_price FROM products WHERE vend_id = 1003;

SELECT AVG(prod_price) FROM products WHERE vend_id = 1003;

AVG(prod_price) :取所有列的总计平均值

SELECT AVG(DISTINCT prod_price) FROM products WHERE vend_id = 1003;

AVG(DISTINCT prod_price)：取剔除相同值的列求总计平均值

 

三、组合聚集函数

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;