目录

一、聚集函数说明

1 聚合函数的适用情况

2  聚合函数的注意事项

二、聚合函数 

1 AVG()函数

2 COUNT() 函数

 3 MAX()函数

4 MIN()函数

5 SUM()函数

6 组合聚集函数

7 MOD()函数

一、聚集函数说明

1 聚合函数的适用情况

  1. 默认对所有行执行。ALL参数可加可不加（如未指定Distinct,则默认为all）
  2. 如需数值去重,则应指定distinct参数
  • distinct不能用于count（*）,可以用于count（）+列名
  • distinct用时必须加列名,不能用于计算和表达式

e.g. 加上Distinct语句后,再用avg函数,求得各个产品不同的价格

SELECT AVG( DISTINCT prod_price )
AS avg_price 
FROM products;

2  聚合函数的注意事项

  1. 聚合函数的运算，除了对count（*）以外，都会忽略 列值为NULL的行
  2. 使用聚合函数时，select子句中只能出现：常数，聚合函数，聚合键（group子句中指定的列）

SQL聚集函数

函数

返回内容的说明

AVG()

某列的平均值

COUNT()

某列的行数

MAX()

某列的最大值

MIN()

某列的最小值

SUM()

某列的列值总和

MOD()

求余数 

二、聚合函数 

1 AVG()函数

  • 对行数求其平均值
  • 可用于返回所有列的均值、特定列或行的均值

e.g1. 返回products表的所有产品的均价，命名为avg_price

SELECT AVG(prod_price)
AS avg_price 
FROM products;

e.g2. 返回products表内的DLL01供应商所供应的所有产品的均价，命名为avg_price

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

 e.g3.从SHIRTABLE表中，选取出销售单价shirt_price高于全部衬衫的平均价格的衬衫名字

SELECT shirt_id,shirt_name,shirt_price 
FROM SHIRTABLE 
WHERE shirt_price> ( SELECT AVG(shirt_price) 
                     FROM SHIRTABLE);

2 COUNT() 函数

  • 用于确定表的行数，或者符合特定条件的行数
  • 用COUNT(*)来对表的所有行数计数   
  • 用COUNT(*) 时，不忽略null值；用 COUNT(column)对特定列计数时，忽略NULL值

e.g1. 返回customers表的所有客户总数，命名为num_cust

SELECT COUNT(*) 
AS num_cust
FROM customers;

e.g2. 返回customers表中有邮件的顾客数，命名为num_email

SELECT COUNT(cust_email)
AS num_email
FROM customers;

 3 MAX()函数

  • 返回指定列的最大值，同样要求指定列名
  • 用于文本型列时，通常max（）返回的是该列排序的最后一行

e.g. 求prod_price列的最大值

SELECT MAX(prod_price)
AS max_price
FROM products;

4 MIN()函数

  •  返回指定列的最小值，同样要求指定列名
  • 一般用于数值型列or日期型的最小值返回
  • 可用于求最早的日期，如需筛选出（用户最早登录日期）则用min（）
  • 用于文本型列时，通常min（）返回的是该列排序的最前面的一行

5 SUM()函数

返回指定列值的总和

  • 同样可以结合where语句，限制住要求和的条件范围
  • sum（）函数会忽略NULL值
  • sum（）还可以用于计算式子的求和。例子如下：
SELECT SUM(item_price*quantity)
AS total_price
FROM orderitem;

6 组合聚集函数

  • select 语句可以包含多个聚集函数,来一次性汇总多种数据
  • 指定别名时,不应该用表中已经存在了的列名
e.g.列出prod_price的最大、最小和均值。记得在最前面一列加上序号列（注：列与列之间记得加逗号）

SELECT COUNT(*) AS num_items,
       MAX(prod_price) AS max_price,
       MIN(prod_price) AS min_price,
       AVG(prod_price) AS avg_price 
FROM products;

7 MOD()函数

 MOD( 被除数 , 除数 即分母 )

求余函数例题

计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以'M'开头，那么他的奖金是他工资的100%，否则奖金为0

SELECT 
    employee_id,
    CASE 
        WHEN MOD(employee_id,2)!=0 AND LEFT(name,1)!='M' THEN salary
        WHEN MOD(employee_id,2)=0 OR LEFT(name,1)='M' THEN 0
        ELSE 0  --这一步可省略写
    END AS bonus
FROM Employees;