在MySQL中,聚合函数是用于计算多行数据的统计信息的函数,例如总和、平均值、最大值、最小值和行数等。将多行数据聚合成单个结果,这是聚合函数得名的由来。本文将详细介绍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
语句。
下图展示分组的过程:
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聚合函数的资料请关注编程学习网其它相关文章!
本文标题为:详解MySQL聚合函数
基础教程推荐
- 如何将excel表格数据导入postgresql数据库 2023-07-20
- Python常见库matplotlib学习笔记之多个子图绘图 2023-07-27
- Mysql查询所有表和字段信息的方法 2023-07-26
- Sql Server Management Studio连接Mysql的实现步骤 2023-07-29
- Redis如何实现延迟队列 2023-07-13
- python中pandas库的iloc函数用法解析 2023-07-28
- 【Redis】数据持久化 2023-09-12
- Mysql主从三种复制模式(异步复制,半同步复制,组复 2022-09-01
- 关于MySQL中explain工具的使用 2023-07-27
- SQLServer 清理日志的实现 2023-07-29