10 MySQL聚合函数&数据分组

客官°小女子只卖身不卖艺 2023-09-28 12:09 140阅读 0赞

**目录**

聚合函数

一、聚合函数定义：

二、聚合函数类型：

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

四、聚合函数说明

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

--------------------

## **聚合函数** ##

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

> ## **二、聚合函数类型：** ##
> 
> ![f06fa9735bd44004b3483d649ea2c9dd.png][]
> 
> ![16c2429510044cbd8da622ce8959a6db.png][]

> ## **三、****使用聚合函数的原则****：** ##
> 
> 1.**可以使用****DISTINCT 使得函数只考虑不重复的值**；
> 
> 2.**所有聚合函数忽略空值**。为了用一个值代替空值，用 IFNULL 或 COALESCE 函数。

> ## **四、聚合函数说明** ##
> 
> ### **1.****AVG 和 SUM 函数****：** ###
> 
> #### 注意：AVG、SUM函数**只对数字类型**做处理，**其他类型返回0**； ####
> 
> #### **1.1****AVG(arg)函数****：**对分组数据做平均值运算。 ####
> 
> arg:参数类型**只能是数字类型**。
> 
> #### **1.2****SUM(arg)函数：**对分组数据求和。 ####
> 
> arg:参数类型**只能是数字类型**。
> 
> 例：SELECT **AVG(salary),SUM(salary)**
> 
> FROM employees
> 
> WHERE job\_id LIKE '%REP%';
> 
> 表示：计算员工表中工作编号含有REP的工作岗位的**平均薪水与薪水总和**。
> 
> ### **2.****MIN 和 MAX 函数****：** ###
> 
> **2.1****MIN(arg)函数****：**求分组中最小数据。
> 
> arg:参数类型**可以是字符、数字、 日期**。
> 
> #### **2.2****MAX(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 子句语法** ##
> 
> ![80d11d7ba49b47bca6d6726b8c3acced.png][]
> 
> ### **1.使用GROUP BY****原则****：** ###
> 
> **（1）****使用 WHERE 子句，可以在划分行成组以前过滤行****（即GROUP BY在WHERE条件结果上进行分组）****。**
> 
> （2）如果有WHERE子句，那么**GROUP BY 子句必须在WHERE的子句后面****，在ORDER BY前面**。
> 
> **（3）****在 GROUP BY 子句中必须包含列。**
> 
> ![59410de6c481458f8869dfd8381617a9.png][]
> 
> 例：SELECT DEPARTMENT\_ID, COUNT(\*)
> 
> FROM employees
> 
> GROUP BY DEPARTMENT\_ID;
> 
> 表示：计算每个部门的员工总数。
> 
> 1.  ### **在多列上使用分组：** ###
> 
> ![cafaa1a9107d400b9a72efc224ffe0a5.png][]
> 
> 注意：上图中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子句语法：**
> 
> ![c5c74ef8fd2f4b2f800d2ba05a93aabc.png][]
> 
> 例：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;
> 
> 表示：写一个查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。

[f06fa9735bd44004b3483d649ea2c9dd.png]: https://img-blog.csdnimg.cn/f06fa9735bd44004b3483d649ea2c9dd.png
[16c2429510044cbd8da622ce8959a6db.png]: https://img-blog.csdnimg.cn/16c2429510044cbd8da622ce8959a6db.png
[80d11d7ba49b47bca6d6726b8c3acced.png]: https://img-blog.csdnimg.cn/80d11d7ba49b47bca6d6726b8c3acced.png
[59410de6c481458f8869dfd8381617a9.png]: https://img-blog.csdnimg.cn/59410de6c481458f8869dfd8381617a9.png
[cafaa1a9107d400b9a72efc224ffe0a5.png]: https://img-blog.csdnimg.cn/cafaa1a9107d400b9a72efc224ffe0a5.png
[c5c74ef8fd2f4b2f800d2ba05a93aabc.png]: https://img-blog.csdnimg.cn/c5c74ef8fd2f4b2f800d2ba05a93aabc.png