当前位置：首页 > article >正文

MySQL_聚合函数分组查询

article 2024/11/14 19:16:06

上篇复习：

设计数据库时的三大范式
1.第一范式，一行数据中每一列不可再分
关系型数据库必须要满足第一范式，设计表的时候，如果每一列都可以用SQL规定的数据类型描述，就天然满足第一范式.

2.第二范式，在第一范式的基础上，消除了部分函数依赖
一个表中存在复合主键，当有一列只依赖复合主键中的某一个键，那就这种设计就不满足第二范式不满足第二范式时会出现一些问题:数据冗余，更新异常，插入异常，删除异常如果一个表中的键只有一列时，那么这种设计就天然满足第二范式

3.第三范式，在第二范式的基础上，消除了传递依赖

数据库的关系模型
1.一对一关系
2.一对多关系
3.多对多关系
4.没有关系

设计表的过程
通过需求找出实体
确定实体之间的关系
根据不同的关系按照固定的方法去创建表

1.聚合函数

1.在 MySQL 中，聚合函数是用于计算多行数据的统计信息的函数，例如总和、平均值、最大值、最小值和行数等。聚合函数用于在查询结果中创建单个值，该值代表聚合操作的结果。将多行数据聚合成单个结果，这是聚合函数得名的由来。

以下是 MySQL 中常见的聚合函数：

在介绍以上函数时我们先看一下我们要操作表的数据：

1. `COUNT()`- 统计数量

功能：统计指定列中的非空值个数，或者统计表中的总行数。
使用场景：用于统计表中数据条数或分组数据条数。

- COUNT(*)：统计所有行的数量（包括空值）。
- COUNT(column)：统计指定列非空值的数量。

由上我们可以看到当数学行中有一个值为空时，count函数并没有那个为空的数据给统计当中。

2. `SUM()`- 计算总和

功能：计算指定数值列的所有行的总和。
使用场景：用于对数值列求和，如统计总销售额或总薪资。

当我们计算非数字的数据是，我们可以看到爆出了错误

如果对非数值类型的列进行运算，会得到一些警告信息

3. `AVG()`- 计算平均值

功能：计算指定数值列的召唤。
使用场景：用于求特定分数列的平均分数，如计算平均薪资或平均分数。

求语文的平均值

2.求语文，数学，英语三门课的总分的平均值

4. `MAX()`- 获取峰值

功能：返回指定列中的顶部。
使用场景：用于查找特定列的顶峰，如最高薪资或最高分数。

1.找到数学最高分数：

5.-`MIN()`获取最小值

功能：简单地返回指定列中。
使用场景：用于轻松查找特定列，如最低薪资或最低份额。

1.找到英语最低分数：

2. GROUP BY 子句的讲解：

GROUP BY子句在MySQL中非常常用，通常用于对某些或某些列对数据进行分组。它结合聚合函数（如COUNT()、SUM()、AVG()等）一起使用，用于对每个分组的数据进行统计或汇总。子句经常出现在查询语句的SELECT部分，目的是让数据库返回每一组的统计结果，而不是返回原始的每一行数据。

`GROUP BY`基本概念

分组：GROUP BY会根据指定的列将数据拆分为若干组。每个组中的数据具有相同的值（即在分组字段中相同的值会被归为一组）。
聚合函数：GROUP BY通常和聚合函数结合使用，比如COUNT()、SUM()、AVG()、MAX()、MIN()等，用于对每一组的数据进行汇总统计。

column1,column2等：这些是你希望根据其进行分组的列。
aggregate_function(column3)：这个代表评估每个分组的聚合函数，column3是你要汇总的数据列。
table_name：数据表的名称。
WHERE：任选的筛选条件，GROUP BY会在应用WHERE筛选条件之后对数据进行分组。

`GROUP BY`应用程序

统计每个部门的员工数量。
计算每个部门的平均工资。
找到每个部门的最高薪资。
按时间部分分组，统计每日或月刊的销售全国。

`GROUP BY`的常见用法和示例

假设我们有如下表：

1.按部门分组，统计每个部门的员工数量

SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department;

解释：按department列分组，统计每个部门的员工数量。

2.按部门分组，计算每个部门的薪资总和

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

解释：按department列分组，计算每个部门的薪资总和。

3.按部门分组，计算每个部门的平均工资

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

解释：按department列分组，计算每个部门的平均工资。

这些代码示例展示了如何使用GROUP BY子句按不同的字段进行分组，并结合聚合函数（如COUNT()、SUM()、AVG()、MAX()、MIN()）进行数据汇总。HAVING子句则用于对分组结果进行进一步筛选。

GROUP BY：将数据按一个或多个字段进行分组。
聚合函数：对每个分组的值进行聚合计算，如COUNT()、SUM()、AVG()等。

3. HAVING子句

在SQL中，HAVING子句允许对分组后的结果进行筛选，它通常与GROUP BY子句一起使用。你可以理解为，HAVING是WHERE的“分组后”版本，你在数据分组后进行过滤。

`HAVING`子句的作用

WHERE子句：用于在分组之前对数据进行筛选。
HAVING子句：用于在分组之后对分组结果进行筛选。

一般来说，HAVING可以过滤聚合函数计算后面的结果，比如COUNT()、SUM()、AVG()等聚合结果。通常情况下，WHERE子句不能用于聚合函数的筛选，因为WHERE是在数据行级别进行过滤的，而聚合函数是在分组中随后对数据进行计算的。

`HAVING`基本原理

WHERE：用于过滤原始数据行（可选）。
GROUP BY：用于将数据按一个或多个列进行分组。
HAVING：用于对分组后的结果进行筛选，常用于聚合函数的条件。

`HAVING`子句注意事项

HAVING是在分组后使用的，它只能在聚合（如COUNT()、、SUM()等AVG()）计算完成后使用。
HAVING子句可以处理聚合函数，而WHERE不能。
HAVING可以与GROUP BY子句一起使用，来过滤那些不条件符合的分组。

1. 查询每个部门的员工数量，但只返回员工数量大于 2 的部门

SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department HAVING COUNT(*) > 2;

解释：按department字段分组，统计每个部门的员工数量，并用来HAVING COUNT(*) > 2筛选员工数量大于2的部门。

2.查询每个部门的薪资总和，但只返回薪资总和超过15000的部门

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 15000;

解释：按department字段分组，计算每个部门的薪资总和，并使用HAVING SUM(salary) > 15000来筛选薪资总和大于15000的部门。

3.查询每个部门的平均薪资，但只返回平均薪资大于5500的部门

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 5500;

解释：按department字段分组，计算每个部门的平均薪资，并使用HAVING AVG(salary) > 5500来筛选平均薪资大于 5500 的部门。

4. 查询每个部门的最高薪资，但只返回最高薪资大于 6000 的部门

SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 6000;

解释：按department字段分区，查找每个部门的最高薪资，并使用HAVING MAX(salary) > 6000来筛选最高薪资大于 6000 的部门。

`HAVING`与此`WHERE`对比：

示例：`WHERE`与`HAVING`的联合使用

有时你可能希望在WHERE子句中先过滤原始数据，然后再利用HAVING对分组结果进行进一步筛选。

SELECT department, COUNT(*) AS num_employees
FROM employees
WHERE salary > 5000 -- 先筛选薪资大于 5000 的员工
GROUP BY department
HAVING COUNT(*) > 1; -- 然后筛选员工数量大于 1 的部门

解释：首先通过WHERE salary > 5000过滤薪资大于 5000 的员工，然后使用HAVING COUNT(*) > 1进一步筛选员工数量大于 1 的部门。