首页 > 资讯 > 数据库 >详解MySQL聚合函数

931

分享到

详解MySQL聚合函数

MySQL聚合函数 MySQL聚合函数的使用 2023-04-19 08:04:31 931人浏览泡泡鱼

摘要

目录聚合函数COUNT 函数SUM 函数AVG 函数MAX 函数 MIN 函数group by 子句简介示例：scott 数据库单列分组多列分组having 子句总结聚合函数在 Mysql 中，聚合函数是用于

聚合函数

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

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

函数	说明
`COUNT([DISTINCT] expr)`	返回查询到的数据的数量
`SUM([DISTINCT] expr)`	返回查询到的数据的总和，不是数字没有意义
`AVG([DISTINCT] expr)`	返回查询到的数据的平均值，不是数字没有意义
`MAX([DISTINCT] expr)`	返回查询到的数据的最大值，不是数字没有意义
`MIN([DISTINCT] expr)`	返回查询到的数据的最小值，不是数字没有意义

这些函数通常用于 SELECT 查询语句中，与 GROUP BY 子句结合使用以对数据进行分组和汇总。

COUNT 函数

在 Mysql 中，count 函数用于计算指定列或表中行的数量。

语法：

SELECT COUNT(column_name) FROM table_name;

计算结果会忽略指定列中的NULL。

如果要计算表中所有行的数量，可以使用以下语法：

SELECT COUNT(*) FROM table_name;

理解：

SELECT COUNT(column_name) FROM table_name; 就是 SELECT column_name FROM table_name; 的结果的非空行数

例：

有如下表格

mariadb [test_db]> select * from student_scores;
+----+---------+---------+------+---------+
| id | name    | chinese | math | english |
+----+---------+---------+------+---------+
|  1 | Alice   |      80 |   85 |      90 |
|  3 | Charlie |      90 |   95 |      85 |
|  4 | Dave    |      80 |   90 |      95 |
|  5 | Emma    |      95 |   85 |      90 |
|  6 | Frank   |      70 |   78 |      80 |
|  7 | God     |    NULL | NULL |    NULL |
+----+---------+---------+------+---------+
6 rows in set (0.00 sec)

查询总人数：

MariaDB [test_db]> select count(name) as 总人数 from student_scores;
+-----------+
| 总人数    |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)

实际上，count() 内写成 * 也可以，甚至写成 1 这样的字面值也可以得到正确结果。

MariaDB [test_db]> select count(*) as 总人数 from student_scores;
+-----------+
| 总人数    |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)

MariaDB [test_db]> select count(1) as 总人数 from student_scores;
+-----------+
| 总人数    |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)

这是因为 * 和 1 都可以作为一个列，select count(*) as 总人数 from student_scores; 的结果就是 select * as 总人数 from student_scores; 的结果的行数。select count(1) as 总人数 from student_scores; 的结果是 select 1 as 总人数 from student_scores; 的结果的行数。

统计 chinese 列，NULL 行被忽略

MariaDB [test_db]> select count(chinese) from student_scores;
+----------------+
| count(chinese) |
+----------------+
|              5 |
+----------------+
1 row in set (0.00 sec)

将 distinct 写在 count() 内外的区别：

MariaDB [test_db]> select count(distinct chinese) from student_scores;
+-------------------------+
| count(distinct chinese) |
+-------------------------+
|                       4 |
+-------------------------+
1 row in set (0.00 sec)

MariaDB [test_db]> select distinct count(chinese) from student_scores;
+----------------+
| count(chinese) |
+----------------+
|              5 |
+----------------+
1 row in set (0.00 sec)

很明显，写在里面才是对去重后的结果统计行数，写在外面是在已经统计好行数后对count的结果去重。

SUM 函数

在 MySQL 中，SUM 是一个聚合函数，用于计算指定列或表中所有行的数值之和。可以将 SUM 用于任何数值类型的列，包括整数、小数等。

语法：

SELECT SUM(column_name) FROM table_name WHERE conditions;

column_name 是要计算总和的列的名称

例：

统计所有人的语文成绩的和

MariaDB [test_db]> select sum(chinese) from student_scores;
+--------------+
| sum(chinese) |
+--------------+
|          415 |
+--------------+
1 row in set (0.00 sec)

AVG 函数

在 MySQL 中，AVG 是一个聚合函数，用于计算指定列或表中所有行的数值平均值。AVG 函数仅适用于数值类型的列，例如整数或小数。

语法：

SELECT AVG(column_name) FROM table_name WHERE conditions;

例：

求英语的平均分

MariaDB [test_db]> select avg(english) from student_scores;
+--------------+
| avg(english) |
+--------------+
|      88.0000 |
+--------------+
1 row in set (0.00 sec)

MAX 函数 MIN 函数

语法：

SELECT MAX(column_name) FROM table_name WHERE conditions;

SELECT MIN(column_name) FROM table_name WHERE conditions;

例：

查询数学是最高分和最低分

MariaDB [test_db]> select max(math) from student_scores;
+-----------+
| max(math) |
+-----------+
|        95 |
+-----------+
1 row in set (0.00 sec)

MariaDB [test_db]> select min(math) from student_scores;
+-----------+
| min(math) |
+-----------+
|        78 |
+-----------+
1 row in set (0.00 sec)

group by 子句

简介

上面我们使用聚合函数后的结果都只有一行，这是因为我们把整个表看成了一个整体，把一列中的所有行直接聚合成了一个数字。

GROUP BY 是用于对结果集进行分组的子句。使用 GROUP BY 可以根据一个或多个列对结果集进行分组，以便在结果中显示每个组的汇总信息。

以下是 GROUP BY 子句的基本语法：

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE conditions
GROUP BY column_name;

column_name 是要分组的列的名称

aggregate_function 是要应用于分组的列的聚合函数，例如 SUM、AVG、COUNT 等

table_name 是要从中选择数据的表的名称

conditions 是一个可选的 WHERE 子句，用于指定选择数据的条件。

示例：scott 数据库

接下来的示例我们使用 scott 数据库，scott 是由 oracle 公司创建的一个示例数据库，用于教学和测试。

scott 数据库的 sql 文件

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);


DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);


insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

例：

单列分组

查询每个部门的平均工资和最高工资

从 emp 表中找，然后对 deptno 分组，分别求平均工资和最高工资

select deptno 部门编号, avg(sal) 平均工资, max(sal) 最高工资
from emp
group by deptno;

+--------------+--------------+--------------+
| 部门编号     | 平均工资     | 最高工资     |
+--------------+--------------+--------------+
|           10 |  2916.666667 |      5000.00 |
|           20 |  2175.000000 |      3000.00 |
|           30 |  1566.666667 |      2850.00 |
+--------------+--------------+--------------+
3 rows in set (0.00 sec)

上述示例，group by 会先将表按部门分组，然后对分出的每个组，分别执行 select 语句。

多列分组

查询每个部门的每种岗位的平均工资和最低工资

select deptno, job, avg(sal) 平均工资, min(sal) 最低工资
from emp
group by deptno, job;

+--------+-----------+--------------+--------------+
| deptno | job       | 平均工资     | 最低工资     |
+--------+-----------+--------------+--------------+
|     10 | CLERK     |  1300.000000 |      1300.00 |
|     10 | MANAGER   |  2450.000000 |      2450.00 |
|     10 | PRESIDENT |  5000.000000 |      5000.00 |
|     20 | ANALYST   |  3000.000000 |      3000.00 |
|     20 | CLERK     |   950.000000 |       800.00 |
|     20 | MANAGER   |  2975.000000 |      2975.00 |
|     30 | CLERK     |   950.000000 |       950.00 |
|     30 | MANAGER   |  2850.000000 |      2850.00 |
|     30 | SALESMAN  |  1400.000000 |      1250.00 |
+--------+-----------+--------------+--------------+
9 rows in set (0.00 sec)

上述用例先按部门分组，然后对每组再按岗位分组，对每个小组执行 select 语句。

下图展示分组的过程：

详解MySQL聚合函数

having 子句

查询平均工资低于 2000 的部门及其平均工资

错误写法：

select deptno, avg(sal)
from emp
where avg(sal) < 2000
group by deptno;

where 的执行在 group 之前，执行 where 的时候还没分组呐，根本无法求平均值和筛选。

我们知道，having 筛选在 group by 之后，正确的应该用 having

select deptno, avg(sal)
from emp
group by deptno
having avg(sal) < 2000;

总结

group by 是通过分组，为聚合统计提供基本的功能支持，即，group by 一定是配合聚合函数使用的
group by 后面跟的是分组的字段依据，只有在 group by 后面出现的字段，才能在 select 中作为字段出现
having 通常是在完成分组聚合统计，然后再进行筛选。where 通常是对表中数据进行初步筛选，where 后面不能跟聚合函数。

以上就是详解MySQL聚合函数的详细内容，更多关于MySQL聚合函数的资料请关注我们其它相关文章！

您可能感兴趣的文档:

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: 详解MySQL聚合函数

本文链接: https://www.lsjlt.com/news/201651.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档

去做题

猜你喜欢

详解MySQL聚合函数

目录聚合函数COUNT 函数SUM 函数AVG 函数MAX 函数 MIN 函数group by 子句简介示例：scott 数据库单列分组多列分组having 子句总结聚合函数在 mysql 中，聚合函数是用于...

99+

2023-04-19

MySQL聚合函数 MySQL聚合函数的使用
MySQL：聚合函数（全面详解）

聚合函数前言一、聚合函数介绍1、AVG和SUM函数2、 MIN和MAX函数3、COUNT函数二、GROUP BY1、基本使用2、使用多个列分组3、 GROUP BY中使用WITH ROLLUP 三、HAVING1...

99+

2023-08-16

mysql android 数据库
Oracle 聚合函数详解

Oracle 聚合函数详解一聚合函数的定义聚合函数也叫组函数，有的地方也叫集合函数，它的数据源一般来自多组数据，但返回的时候一般是一组数...

99+

2024-04-02
【MySQL】聚合函数

一、五大常用聚合函数 SUM()：求总和，只适用于数值类型字段，如果是字符串类型不会报错会返回0，会自动过滤空值AVG()：求平均值，只适用于数值类型字段，字符串类型不会报错会返回0，会自动过滤空值M...

99+

2023-08-31

mysql java 数据库
MySQL 聚合函数排序

目录MySQL 结果排序-- 聚集函数环境查询结果排序查询的分组与汇总查一下学生们平均年龄查一下总人数是多少查一下每个年龄有多少人查出最大年龄总结MySQL 结果排序-- 聚集函数...

99+

2024-04-02
mysql 聚合函数怎么用

mysql 聚合函数怎么用？这个问题可能是我们日常学习或工作经常见到的。希望通过这个问题能让你收获颇深。下面是小编给大家带来的参考内容，让我们一起来看看吧！mysql聚合函数用法：1、使用SELECT...

99+

2024-04-02
mysql聚合函数有哪些

SQL中的聚合函数有：AVG函数：返回指定组中的平均值，空值被忽略。COUNT函数：返回指定组中项目的数量。MAX函数：返回指定数据的最大值。MIN函数：返回指定数据的最小值。SUM函数：返回指定数据的和，只能用于数字列，空值被忽略。COU...

99+

2024-04-02
聚合函数和groupby的关系详解

目录前言聚合函数介绍group by介绍解释聚合函数和group by的关系使用group by和聚合函数需要注意的地方总结前言 world：世界表格continent：大洲名称na...

99+

2024-04-02
MySQL必备基础之分组函数聚合函数分组查询详解

目录一、简单使用二、搭配DISTINCT去重三、COUNT()详细介绍四、分组查询一、简单使用 SUM：求和（一般用于处理数值型） AVG：平均（一般用于处理数值型） MAX：最大（...

99+

2024-04-02
MySQL如何使用聚合函数

这篇文章主要为大家展示了“MySQL如何使用聚合函数”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MySQL如何使用聚合函数”这篇文章吧。聚合函数命令： sum...

99+

2024-04-02
MySQL中的聚合函数怎么用

本篇内容介绍了“MySQL中的聚合函数怎么用”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，...

99+

2024-04-02
mysql聚合函数求和怎么写

mysql中对聚合函数求和的方法在mysql中使用sum对聚合函数求和，sql语句如下：SELECTp.Id Id,p.ImgUrl ImgUrl,p.Title Title,p.ReferOutPrice ReferOutPric...

99+

2024-04-02
mysql中包含几种聚合函数

mysql 提供丰富的聚合函数用于数据汇总和统计，包括：count()、sum()、avg()、min()、max()、group_concat() 等。这些函数可与 grou...

99+

2024-05-01

mysql 聚合函数
mysql中的聚合函数有哪些

mysql 聚合函数用于汇总数据集中的数据，常见函数包括 sum()、count()、avg()、min() 和 max()。它通过 select 查询语句使用，格式为 sel...

99+

2024-05-01

mysql 聚合函数
mysql中常用的聚合函数包括

mysql 中常用的聚合函数用于汇总计算，包括：1. sum 求和；2. count 计数；3. avg 平均值；4. max 最大值；5. min 最小值；6. group_conca...

99+

2024-05-01

mysql 聚合函数
SQL中的开窗函数详解可代替聚合函数使用

在没学习开窗函数之前，我们都知道，用了分组之后，查询字段就只能是分组字段和聚合的字段，这带来了极大的不方便，有时我们查询时需要分组，又需要查询不分组的字段，每次都要又到子查询，这样显得sql语句...

99+

2024-04-02
【MySQL】详解聚合查询、多表查询

MySQL 增删查改（进阶）文章目录 MySQL 增删查改（进阶）01 表的设计表的三大范式 02 查询操作进阶新增聚合查询countsumavgmaxmin 分组查询 GR...

99+

2023-10-07

mysql adb 数据库
如何在MySQL中使用count聚合函数

如何在MySQL中使用count聚合函数？相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。一、基本使用count的基本作用是有两个：统计某个列的...

99+

2024-04-02
MongoDB 聚合查询详解

目录聚合管道管道阶段常见操作更多操作 - 投影更多操作 - 展开更多操作 - 分组更多操作 - 入库条件组累加器算术运算最值运算数组提取其他运算聚合管道聚合框架是 MongoDB 中的一组分析工具，可以对一个或多个集合...

99+

2022-12-26

MongoDB聚合查询 mongodb聚合查询性能 mongodb聚合查询优化
MySQL中怎么创建自定义聚合函数

要在MySQL中创建自定义聚合函数，需要遵循以下步骤：创建一个用于存储函数的数据库或选择一个现有的数据库。 CREATE DA...

99+

2024-04-30

MySQL