MySQL_聚合函数（多行函数）

一、 聚合函数介绍

聚合函数作用于一组数据，并对一组数据返回一个值。

• 常见聚合函数类型

  • AVG()
  • SUM()
  • MAX()
  • MIN()
  • COUNT()

• 聚合函数语法
  

• 聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

1、AVG和SUM函数

只适用于数值类型的字段（或变量）

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM   employees;

2、MIN和MAX函数

适用于数值类型、字符串类型、日期时间类型的字段（或变量）

SELECT MIN(hire_date), MAX(hire_date)
FROM	  employees;

3、COUNT函数

• COUNT(*)返回表中记录总数，适用于任意数据类型。

#1.作用：计算指定字段再查询结果中出现的个数
SELECT COUNT(employee_id),COUNT(salary),COUNT(1),COUNT(2),COUNT(2),COUNT(*)
FROM employees;

#如何计算表中有多少条记录，如何实现
#方式一：COUNT(*)
#方式二：COUNT(1)
#方式三：COUNT(具体字段)：不一定对！

SELECT COUNT(*),COUNT(1),COUNT(salary),COUNT(commission_pct)
FROM employees;

• COUNT(expr) 返回expr不为空的记录总数。

#注意：计算指定字段出现的个数时:是不计算NULL值的(不包含NULL值)
SELECT COUNT(commission_pct)
FROM employees;

#公式：AVG = SUM / COUNT (一定成立)

二、GROUP BY

根据某个字段进行分组操作

1、基本使用

#需求：查询各个部门的平局工资，最高工资

SELECT department_id,AVG(salary),MAX(salary)
FROM employees
GROUP BY department_id; 

2、使用多个列分组

（例如：根据department_id和job_id进行分组）

#需求：查询各个department_id,job_id的平均工资

SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
#或
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;

注意：
1、SELECT中出现的非组函数的字段必须声明在GROUP BY 中，反之，GROUP BY中声明的字段可以不出现在SELECT中
2、GROUP BY 声明在FROM后面，WHERE后面，ORDER BY前面，LIMIT前面

3、GROUP BY中使用WITH ROLLUP

使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

SELECT AVG(salary)
FROM employees;

SELECT department_id,AVG(salary),
FROM employees
GROUP BY department_id  WITH ROLLUP;

注意：
当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

三、HAVING（用来过滤数据）

1、基本使用

过滤分组：HAVING子句

1. 行已经被分组。
2. 使用了聚合函数。
3. 满足HAVING 子句中条件的分组将被显示。
4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

#练习：查询各个部门中最高工资比10000高的工资
#错误写法
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary)>10000
GROUP BY department_id;
#正确写法
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000;

要求：
1.如果过滤条件中使用了聚合函数，则必须使用HAVING 来替换WHERE。否则，报错
2.HAVING必须声明在GROUP BY 后面
3.使用HAVING的前提是SQL中使用了GROUP BY

#练习：查询部门id为10，20，30，40这四个部门中最高工资比1000高的部门信息
#方式一：推荐，执行效率高于方式二
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN(10,20,30,40)
GROUP BY department_id 
HAVING MAX(salary) > 10000;

##方式二：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id 
HAVING MAX(salary) > 10000 AND department_id IN(10,20,30,40);

结论：
当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中
当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。建议声明在WHERE中。

2、WHERE和HAVING的对比

四、SELECT的执行过程

1、查询的结构

#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#方式2：
SELECT ...,....,...
FROM ... JOIN ... 
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

2、SELECT执行顺序

记住 SELECT 查询时的两个顺序：

1. 关键字的顺序是不能颠倒的：

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT…

2. SELECT 语句的执行顺序**（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7