詳解MySQL聚合函數

更新時間：2023年04月19日 08:41:36 作者：世真

在 MySQL 中，聚合函數是用于計算多行數據的統(tǒng)計信息的函數，例如總和、平均值、最大值、最小值和行數等。將多行數據聚合成單個結果，這是聚合函數得名的由來。本文將詳細介紹MySQL的聚合函數，感興趣的小伙伴可以參考一下

聚合函數

在 MySQL 中，聚合函數是用于計算多行數據的統(tǒng)計信息的函數，例如總和、平均值、最大值、最小值和行數等。聚合函數用于在查詢結果中創(chuàng)建單個值，該值代表聚合操作的結果。將多行數據聚合成單個結果，這是聚合函數得名的由來。

以下是 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; 的結果的行數。

統(tǒng)計 chinese 列，NULL 行被忽略

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

將 distinct 寫在 count() 內外的區(qū)別：

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)

很明顯，寫在里面才是對去重后的結果統(tǒng)計行數，寫在外面是在已經統(tǒng)計好行數后對count的結果去重。

SUM 函數

在 MySQL 中，SUM 是一個聚合函數，用于計算指定列或表中所有行的數值之和。可以將 SUM 用于任何數值類型的列，包括整數、小數等。

語法：

SELECT SUM(column_name) FROM table_name WHERE conditions;

column_name 是要計算總和的列的名稱

例：

統(tǒng)計所有人的語文成績的和

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 公司創(chuàng)建的一個示例數據庫，用于教學和測試。

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 會先將表按部門分組，然后對分出的每個組，分別執(zhí)行 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)

上述用例先按部門分組，然后對每組再按崗位分組，對每個小組執(zhí)行 select 語句。

下圖展示分組的過程：

having 子句

查詢平均工資低于 2000 的部門及其平均工資

錯誤寫法：

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

where 的執(zhí)行在 group 之前，執(zhí)行 where 的時候還沒分組吶，根本無法求平均值和篩選。

我們知道，having 篩選在 group by 之后，正確的應該用 having

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

總結

group by 是通過分組，為聚合統(tǒng)計提供基本的功能支持，即，group by 一定是配合聚合函數使用的
group by 后面跟的是分組的字段依據，只有在 group by 后面出現(xiàn)的字段，才能在 select 中作為字段出現(xiàn)
having 通常是在完成分組聚合統(tǒng)計，然后再進行篩選。where 通常是對表中數據進行初步篩選，where 后面不能跟聚合函數。

以上就是詳解MySQL聚合函數的詳細內容，更多關于MySQL聚合函數的資料請關注腳本之家其它相關文章！

您可能感興趣的文章:

MySQL 8.0.26版本升級32版本查詢數據為空的解決方案
這篇文章主要介紹了MySQL 8.0.26版本升級32版本查詢數據為空的跟蹤,本文給大家分享兩種解決方法,結合實例代碼給大家介紹的非常詳細,需要的朋友參考下吧
2024-03-03
MySQL Antelope和Barracuda的區(qū)別分析
這篇文章主要介紹了MySQL Antelope和Barracuda的區(qū)別分析,Antelope和Barracude都是一種文件格式,需要的朋友可以參考下
2014-07-07
mysql數據庫遷移數據目錄至另一臺服務器詳細步驟
MySQL數據庫轉移到新服務器是指將現(xiàn)有的MySQL數據庫遷移至一個新的服務器環(huán)境中,下面這篇文章主要給大家介紹了關于mysql數據庫遷移數據目錄至另一臺服務器的詳細步驟,文中通過代碼介紹的非常詳細,需要的朋友可以參考下
2024-07-07
MySQL8.0.20安裝教程及其安裝問題詳細教程
這篇文章主要介紹了MySQL8.0.20安裝教程及其安裝問題處理，本文通過圖文并茂的形式給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下
2020-05-05
MySQL中的運算符使用實例展示
請問，什么是最好的參考文檔，我的答案是：真實可行的樣例語句。
2010-12-12
mysql count詳解及函數實例代碼
這篇文章主要介紹了mysql count詳解及函數實例代碼的相關資料,需要的朋友可以參考下
2017-01-01
數據庫管理中19個MySQL優(yōu)化方法
小編給大家總結了19條非常實用的MySQL數據庫優(yōu)化方法，這是每個服務器管理人員都必須知道的，一起學習下。
2017-11-11
mysqldump進行數據備份詳解
這篇文章主要介紹了mysqldump進行數據備份詳解，文章圍繞主題展開詳細的內容介紹，具有一定的參考價值，需要的小伙伴可以慘一下
2022-07-07
Navicat連接MySQL出現(xiàn)2059錯誤的解決方案
當使用Navicat連接MySQL時,如果出現(xiàn)錯誤代碼2059,表示MySQL服務器不接受Navicat提供的加密插件,解決方法主要有兩種：一是修改MySQL用戶的認證插件為mysql_native_password,二是升級Navicat到最新版本以支持MySQL8.0及其默認的caching_sha2_password認證插件
2024-10-10
關于Win10下MySQL5.7.17免安裝版基本配置教程(圖文詳解)
這數據庫應用是一個應用系統(tǒng)不可或缺的部分，關系型數據庫應用大同小異，這里選擇MySQL作為數據庫平臺。下面通過本文給大家介紹關于Win10下MySQL5.7.17免安裝版基本配置教程(圖文詳解),需要的朋友可以參考下
2017-06-06