«

MySQL聚合函数怎么用

时间：2024-4-30 09:01     作者：韩俊     分类： Mysql

这篇文章主要介绍了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;

    标签: mysql

    热门推荐

    1 Node.js Mongodb 密码特殊字符 @的解决方法
    2 如何实现MySQL中创建视图的语句？
    3 Ubuntu自启动Mysql的三种方式
    4 MySQL开启慢查询功能
    5 Mysql5升级到Mysql5.5的方法
    6 Mysql中大小写敏感问题导致的MySql Error 1146 Tabel doen’t exist错误
    7 Mysql数据库性能优化二
    8 mysql中用什么语句查询指定字段
    9 详解数据库连接的URL的写法及总结
    10 mysql 发生系统错误1067的解决方法

    日历

    标签

    hbase maven eclipse sybase PostgreSQL spring emcache oracle mongodb redis nginx golang java vue android ios mysql linux 微信小程序 css html php教程 javascript python php

    搜索

    最新文章

    热门文章