详解MySQL聚合函数
目录 聚合函数 COUNT 函数 SUM 函数 AVG 函数 MAX 函数 MIN 函数 group by 子句 简介 示例：scott 数据库 单列分组 多列分组 having 子句 总结 聚合函数 在 MySQL 中，聚合函数是用于计算多行数据的
目录聚合函数COUNT 函数SUM 函数AVG 函数MAX 函数 MIN 函数group by 子句简介示例：scott 数据库单列分组多列分组having 子句总结聚合函数
在 MySQL 中，聚合函数是用于计算多行数据的统计信息的函数，例如总和、平均值、最大值、最小值和行数等。聚合函数用于在查询结果中创建单个值，该值代表聚合操作的结果。将多行数据聚合成单个结果，这是聚合函数得名的由来。
以下是 MySQL 中常见的聚合函数：
这些函数通常用于 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 语句。
下图展示分组的过程：
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聚合函数的资料请关注每日运维其它相关文章！
