mysql 常用聚合函数总结

我不是女神ヾ 2023-10-02 19:02 35阅读 0赞

### 前言 ###

聚合函数在工作中大量使用，比如常用的 sum求和函数，avg求平均值的函数等，本篇将mysql聚合函数相关的知识点做一些完整的总结；

### 数据准备 ###

提前创建一张部门表和员工表，并插入若干数据

CREATE TABLE `employees` (
      `employee_id` int(6) NOT NULL DEFAULT '0',
      `first_name` varchar(20) DEFAULT NULL,
      `last_name` varchar(25) NOT NULL,
      `email` varchar(25) NOT NULL,
      `phone_number` varchar(20) DEFAULT NULL,
      `hire_date` date NOT NULL,
      `job_id` varchar(10) NOT NULL,
      `salary` double(8,2) DEFAULT NULL,
      `commission_pct` double(2,2) DEFAULT NULL,
      `manager_id` int(6) DEFAULT NULL,
      `department_id` int(4) DEFAULT NULL,
      PRIMARY KEY (`employee_id`),
      UNIQUE KEY `emp_email_uk` (`email`),
      UNIQUE KEY `emp_emp_id_pk` (`employee_id`),
      KEY `emp_dept_fk` (`department_id`),
      KEY `emp_job_fk` (`job_id`),
      KEY `emp_manager_fk` (`manager_id`),
      CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
      CONSTRAINT `emp_job_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`),
      CONSTRAINT `emp_manager_fk` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE `departments` (
      `department_id` int(4) NOT NULL DEFAULT '0',
      `department_name` varchar(30) NOT NULL,
      `manager_id` int(6) DEFAULT NULL,
      `location_id` int(4) DEFAULT NULL,
      PRIMARY KEY (`department_id`),
      UNIQUE KEY `dept_id_pk` (`department_id`),
      KEY `dept_loc_fk` (`location_id`),
      KEY `dept_mgr_fk` (`manager_id`),
      CONSTRAINT `dept_loc_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`),
      CONSTRAINT `dept_mgr_fk` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_17_color_FFFFFF_t_70_g_se_x_16]  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_20_color_FFFFFF_t_70_g_se_x_16]

### 聚合函数介绍 ###

> 聚合函数作用于一组数据，并对一组数据返回一个值

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_18_color_FFFFFF_t_70_g_se_x_16]

**常用聚合函数类型**

*  AVG()，SUM()，MAX()，MIN()，COUNT()；

**聚合函数语法**

![在这里插入图片描述][32db8ba6a98a417da97d3f987e2cf8fd.png]

**1.1 AVG和SUM函数**

> 可以对数值型数据使用AVG 和 SUM 函数。

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';

**1.2 MIN和MAX函数**

> 可以对任意数据类型的数据使用 MIN 和 MAX 函数

SELECT MIN(hire_date), MAX(hire_date) FROM employees;

**1.3 COUNT函数**

> COUNT(\*)返回表中记录总数，适用于任意数据类型

SELECT COUNT(*) FROM employees WHERE department_id = 50;

COUNT(expr) 返回expr不为空的记录总数

SELECT COUNT(commission_pct) FROM employees WHERE department_id = 50;

> 问题1：用count(\*)，count(1)，count(列名)谁好呢?

其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。  
Innodb引擎的表用count(\*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。

> 问题2：能不能使用count(列名)替换count(\*) ?

不要使用 count(列名)来替代 count(*) ， count(*) 是 SQL92 定义的标准统计行数的语法，跟数  
据库无关，跟 NULL 和非 NULL 无关。

***说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。**\*

### 基本使用 ###

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_20_color_FFFFFF_t_70_g_se_x_16 1]

可以使用GROUP BY子句将表中的数据分成若干组【WHERE一定放在FROM后面】

SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];

在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_9_color_FFFFFF_t_70_g_se_x_16]

包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

### 使用多个列分组 ###

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_19_color_FFFFFF_t_70_g_se_x_16]

SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_9_color_FFFFFF_t_70_g_se_x_16 1]  
使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量；

SELECT department_id,AVG(salary) FROM employees WHERE department_id > 80 GROUP BY department_id WITH ROLLUP;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_20_color_FFFFFF_t_70_g_se_x_16 2]

> 注意：  
> 当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

### HAVING 使用 ###

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_20_color_FFFFFF_t_70_g_se_x_16 3]

**过滤分组：HAVING子句**

*  行已经被分组；
 *  使用了聚合函数；
 *  满足HAVING 子句中条件的分组将被显示；
 *  HAVING 不能单独使用，必须要跟 GROUP BY 一起使用；

![在这里插入图片描述][d165a3bedfa1423b94201be658d3ea42.png]

SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_20_color_FFFFFF_t_70_g_se_x_16 4]

非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数。如下：

SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id;

![在这里插入图片描述][a6a58c124f5d45e19fbc4c890a7c0e2f.png]

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_17_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/af73b7ce91ad48f395a11636b166222b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U,size_17,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/9d9e99df252c49db9bca3606d22d3125.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_18_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/34acc4387c2042bd84454b269c2a5446.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U,size_18,color_FFFFFF,t_70,g_se,x_16
[32db8ba6a98a417da97d3f987e2cf8fd.png]: https://img-blog.csdnimg.cn/32db8ba6a98a417da97d3f987e2cf8fd.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/54ef70a386a3479bb1e8611b4e5c4921.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_9_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/ce6e1fb3dca144449398a7357ee96e79.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U,size_9,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_19_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/98618339fc234564aff82d7e015a66d5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U,size_19,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_9_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/773d2c766e6b4411bdde3b66e8e37181.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U,size_9,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/323db14521cd47a797c0cbf00b8a1bd1.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/33b08305bfc74a8985a529faf6216e44.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U,size_20,color_FFFFFF,t_70,g_se,x_16
[d165a3bedfa1423b94201be658d3ea42.png]: https://img-blog.csdnimg.cn/d165a3bedfa1423b94201be658d3ea42.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/15464557c43b4e2ab8425f7447d77737.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6YCG6aOO6aOe57-U55qE5bCP5Y-U,size_20,color_FFFFFF,t_70,g_se,x_16
[a6a58c124f5d45e19fbc4c890a7c0e2f.png]: https://img-blog.csdnimg.cn/a6a58c124f5d45e19fbc4c890a7c0e2f.png