10 MySQL聚合函数&数据分组

目录

聚合函数

一、聚合函数定义：

二、聚合函数类型：

三、使用聚合函数的原则：

四、聚合函数说明

1.AVG 和 SUM 函数：

注意：AVG、SUM函数只对数字类型做处理，其他类型返回0；

1.1AVG(arg)函数：对分组数据做平均值运算。

1.2SUM(arg)函数：对分组数据求和。

2.MIN 和 MAX 函数：

2.2MAX(arg)函数：求分组中最大数据。

3.COUNT 函数：返回分组中的总行数。

4.使用 DISTINCT 关键字:不允许重复

数据分组 GROUP BY

一、创建数据组前言

二、GROUP BY 子句语法

1.使用GROUP BY原则：

在多列上使用分组：

三、约束分组结果：HAVING

---

聚合函数

一、聚合函数定义：

聚合函数也称之为多行函数，组函数或分组函数。聚合函数不像单行函数，聚合函数对行的分组进行操作，并给出一个结果。如果在查询中没有指定分组，那么聚合函数则将查询到的结果集视为一组（即将整列作为一个组）。

二、聚合函数类型：

三、使用聚合函数的原则：

1.可以使用DISTINCT 使得函数只考虑不重复的值；

2.所有聚合函数忽略空值。为了用一个值代替空值，用 IFNULL 或 COALESCE 函数。

四、聚合函数说明

1.AVG 和 SUM 函数：

注意：AVG、SUM函数只对数字类型做处理，其他类型返回0；

1.1AVG(arg)函数：对分组数据做平均值运算。

arg:参数类型只能是数字类型。

1.2SUM(arg)函数：对分组数据求和。

arg:参数类型只能是数字类型。

例：SELECT AVG(salary),SUM(salary)

FROM employees

WHERE job_id LIKE '%REP%';

表示：计算员工表中工作编号含有REP的工作岗位的平均薪水与薪水总和。

2.MIN 和 MAX 函数：

2.1MIN(arg)函数：求分组中最小数据。

arg:参数类型可以是字符、数字、 日期。

2.2MAX(arg)函数：求分组中最大数据。

arg:参数类型可以是字符、数字、 日期。

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

表示：查询员工表中入职时间最短与最长的员工，并显示他们的入职时间。

3.COUNT 函数：返回分组中的总行数。

COUNT 函数有三种格式：

COUNT(*)：返回表中满足 SELECT 语句的所有列的行数，包括重复行，包括有空值列的行。

COUNT(expr)：返回在列中的由 expr 指定的非空值的数。

COUNT(DISTINCT expr)：返回在列中的由 expr 指定的唯一的非空值的数。

4.使用 DISTINCT 关键字:不允许重复

COUNT(DISTINCT expr) 返回对于表达式 expr 非空并且值不相同的行数显示 EMPLOYEES 表中不同部门数的值。

例：SELECT COUNT(commission_pct) FROM employees

WHERE department_id = 80;

表示：显示员工表中部门编号是80中有佣金的雇员人数。

数据分组 GROUP BY

一、创建数据组前言

在没有进行数据分组之前，所有聚合函数是将结果集作为一个大的

信息组进行处理。但是，有时，则需要将表的信息划分为较小的

组，可以用 GROUP BY 子句实现。

二、GROUP BY 子句语法

1.使用GROUP BY原则：

（1）使用 WHERE 子句，可以在划分行成组以前过滤行（即GROUP BY在WHERE条件结果上进行分组）。

（2）如果有WHERE子句，那么GROUP BY 子句必须在WHERE的子句后面，在ORDER BY前面。

（3）在 GROUP BY 子句中必须包含列。

例：SELECT DEPARTMENT_ID, COUNT(*)

FROM employees

GROUP BY DEPARTMENT_ID;

表示：计算每个部门的员工总数。

1. 在多列上使用分组：

注意：上图中group by department_id,job_id表示先对department_id分组，在department_id分组的基础上再对job_id 分组；

例：SELECT e.DEPARTMENT_ID, e.JOB_ID,COUNT(*)

FROM employees e

GROUP BY e.DEPARTMENT_ID,e.JOB_ID;

表示：计算每个部门的不同工作岗位的员工总数。

三、约束分组结果：HAVING

1.HAVING 子句

HAVING 子句是对查询出结果集分组后的结果进行过滤。

注意：HAVING只能用在分组结果中

2.HAVING子句语法：

例：SELECT job_id, SUM(salary) PAYROLL  FROM employees

 WHERE job_id NOT LIKE '%REP%'

GROUP BY job_id HAVING SUM(salary) > 13000

ORDER BY SUM(salary);

表示：显示那些合计薪水超过 13,000 的每个工作岗位的合计薪水。排除那些JOB_ID中含有REP的工作岗位，并且用合计月薪排序列表。

例：SELECT MANAGER_ID,min(SALARY)

FROM employees

where MANAGER_ID is not null

GROUP BY MANAGER_ID having MIN(SALARY)>6000;

ORDER BY MIN(salary) DESC;

表示：显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人，排除最低薪水小于等于￥6,000的组，按薪水降序输出。

例：SELECT

d.DEPARTMENT_NAME,d.LOCATION_ID,COUNT(*),round(AVG( e.SALARY)

FROM employees e,departments d

where e.DEPARTMENT_ID = d.DEPARTMENT_ID

GROUP BY d.DEPARTMENT_NAME,d.LOCATION_ID;

表示：写一个查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。