mysql 常用聚合函数总结

前言

聚合函数在工作中大量使用，比如常用的 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;

聚合函数介绍

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

常用聚合函数类型

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

聚合函数语法

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 值的行。*

基本使用

可以使用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 ;

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

使用多个列分组

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

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量；

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

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

HAVING 使用

过滤分组：HAVING子句

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

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

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

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