六.Mysql 聚合函数

Instance
630 阅读5分钟

一.聚合函数介绍

1.1 什么是聚合函数:

  • 聚合函数作用于一组数据，并对一组数据返回一个值。
  • 在Mysql中不可以使用聚合函数进行嵌套使用

1.2 聚合函数类型

  • AVG() :平均值
  • SUM() ：总和
  • MAX() ：最大
  • MIN() ：最小
  • COUNT()：数量

1.3 AVG和SUM函数

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

  • 举例：
SELECT AVG(salary),SUM(salary),AVG(salary) * 107
FROM employees;
#如下的操作没有意义
SELECT SUM(last_name),AVG(last_name),SUM(hire_date)
FROM employees;

1.4 MIN和MAX函数

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

  • 举例：
SELECT MAX(salary),MIN(salary)
FROM employees;

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

1.5 COUNT函数

COUNT(*)返回表中记录总数，适用于任意数据类型。（不包含null值）

  • 问题：用count(*)，count(1)，count(列名)谁好呢?

  • 举例：

SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary),COUNT(1),COUNT(2),COUNT(*)
FROM employees ;

SELECT *
FROM employees;

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

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

SELECT commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

#③ 公式：AVG = SUM / COUNT
SELECT AVG(salary),SUM(salary)/COUNT(salary),
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),
SUM(commission_pct) / 107
FROM employees;

#需求：查询公司中平均奖金率
#错误的！
SELECT AVG(commission_pct)
FROM employees;

#正确的：
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),
AVG(IFNULL(commission_pct,0))
FROM employees;

# 如何需要统计表中的记录数，使用COUNT(*)、COUNT(1)、COUNT(具体字段) 哪个效率更高呢？
# 如果使用的是MyISAM 存储引擎，则三者效率相同，都是O(1)
# 如果使用的是InnoDB 存储引擎，则三者效率：COUNT(*) = COUNT(1)> COUNT(字段)

二.GROUP BY

2.1 基本的使用

  • 作用：可以使用GROUP BY子句将表中的数据分成若干组
  • 使用情景：SELECT中同时出现组函数和非组函数字段时，一定要使用GROUP BY
  • 举例
#需求：查询各个部门的平均工资，最高工资
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id

#需求：查询各个job_id的平均工资
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;

#错误的！
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id;

结论1：SELECT中出现的非组函数的字段必须声明在GROUP BY 中。反之，GROUP BY中声明的字段可以不出现在SELECT中。

2.2 多个列分组与排序的使用

  • 举例：
#需求：查询各个department_id,job_id的平均工资
#方式1：
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY  department_id,job_id;
#方式2：
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;

#需求：查询各个部门的平均工资，按照平均工资升序排列
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC;

结论2：GROUP BY 声明在FROM后面、WHERE后面，ORDER BY 前面、LIMIT前面

2.3 GROUP BY中使用WITH ROLLUP

  • 使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所 有记录的总和，即统计记录数
  • 举例：
#WITH ROLLUP的使用
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;

#错误的：
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;

结论3：MySQL中GROUP BY中可以使用WITH ROLLUP，进行一个汇总查询把整表数据当成一个组。

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

三.HAVING

3.1 基本使用

  • 作用：过滤数据
  • 使用情景：
    • 行已经被分组，即不能单独使用，必须要跟 GROUP BY 后面使用。。
    • 使用了聚合函数的过滤条件时，不能使用WHERE，只能使用HAVING。
    • 满足HAVING 子句中条件的分组将被显示。
  • 举例：
#练习：查询各个部门中最高工资比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。

3.2 HAVING与WHERE的对比

  • 区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件； HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
  • 区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接 后筛选。如果过滤条件中没有聚合函数：这种情况下，WHERE的执行效率要高于HAVING。

image.png

四.SELECT的执行过程

4.1 SELECT 语句的完整结构

sql92语法：
SELECT ....,....,....(存在聚合函数)
FROM ...,....,....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....


#sql99语法：
SELECT ....,....,....(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件 
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....

4.2 SQL语句的执行过程：

FROM ...,...-> ON -> (LEFT/RIGNT JOIN) -> 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
avatar
文章
阅读
粉丝