文章目录

MySQL——聚合函数和group by分组的使用1、聚合函数介绍2、GROUP BY 分组3、常见的聚合函数4、SQL执行顺序

MySQL——聚合函数和group by分组的使用

1、聚合函数介绍

SQL聚合函数计算一组数据的集合并返回单个值。

除 COUNT 以外，聚合函数忽略空值，如果COUNT函数的应用对象是一个确定列名，并且该列存在空值，此时COUNT仍会忽略空值。

因为聚合函数对一组值进行操作，所以它通常与SELECT语句的GROUP BY子句一起使用，以计算为每个分组提供信息的度量。

2、GROUP BY 分组

分组是使用数据库时必须处理的最重要任务之一。 要将行分组，使用GROUP BY子句。

GROUP BY子句是SELECT语句的可选子句，它根据指定列中的匹配值将行组合成组，每组返回一行。

GROUP BY子句的语法：

SELECTcolumn1,column2,GROUP_FUNCTION (column3)FROMtable1WHEREa = bGROUP BYcolumn1,column2HAVINGc = dORDER BYcolumn2 DESC;

在SELECT子句中包含聚合函数不是强制性的。 但是，如果使用聚合函数，它将计算每个组的汇总值。

需要强调的是，在对行进行分组之前应用WHERE子句，而在对行进行分组之后应用HAVING子句。 换句话说，WHERE子句应用于行，而HAVING子句应用于分组。

要对组进行排序，请在GROUP BY子句后添加ORDER BY子句。

GROUP BY子句中出现的列称为分组列。 如果分组列包含NULL值，则所有NULL值都汇总到一个分组中，因为GROUP BY子句认为NULL值相等。

3、常见的聚合函数

AVG/ SUM 只适用于数值类型的字段(或变量)

MAX / MIN 适用于数值类型、字符串类型、日期时间类型的字段（或变量)

使用COUNT(*)、COUNT(1)、COUNT(具体字段) 哪个效率更高：

如果使用的是MyISAM存储引擎，则三者效率相同，都是O(1)

如果使用的是InnoDB存储引擎，则三者效率 COUNT(*) = COUNT(1) >COUNT(字段)

使用实例

-- AVG 计算每个部门的平均工资SELECT e.department_id,department_name, ROUND(AVG(salary), 0) avg_salaryFROM employees eJOIN departments d on e.department_id = d.department_idGROUP BY department_nameORDER BY department_name;-- SUM 返回每个部门中所有员工的总薪水SELECT department_id, SUM(salary)FROM employeesGROUP BY department_id;-- MAX / MIN 返回每个部门中员工的最低和最高薪水SELECT department_name, MIN(salary) min_salary,MAX(salary) max_salaryFROM employees eJOIN departments d on e.department_id = d.department_idGROUP BY department_nameORDER BY department_name;-- COUNT 返回每个部门的人数并根据部门名升序SELECT department_name, COUNT(*) headcountFROM employees eJOIN departments d on e.department_id = d.department_idGROUP BY department_nameORDER BY department_name;-- 查询最低薪资大于6000的各个部门的信息、最高薪资和平均薪资select e.department_id,department_name,min(salary) min_salary,max(salary) max_salary,round(avg(salary),2) average_salaryfrom employees ejoin departments d on e.department_id = d.department_idGROUP BY e.department_idhaving min_salary > 6000order by department_id ;-- 查找人数大于5的部门SELECT e.department_id,department_name,COUNT(employee_id) headcountFROM employees eJOIN departments d ON d.department_id = e.department_idGROUP BY e.department_idHAVING headcount > 5ORDER BY headcount DESC;#1.where子句可否使用组函数进行过滤?-- 不可以#2.查询公司员工工资的最大值，最小值，平均值，总和select max(salary),min(salary),avg(salary),sum(salary) from employees;#3.查询各job_id的员工工资的最大值，最小值，平均值，总和select job_id, max(salary),min(salary),avg(salary),sum(salary)from employeesgroup by job_id;#4.选择各个job_id具有的员工人数select job_id,count(*)from employeesgroup by job_id;#5.查询员工最高工资和最低工资的差距(DIFFERENCE)select max(salary) - min(salary) DIFFERENCEfrom employees;#6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内select emp.employee_id,emp.manager_id, min(emp.salary) min_salaryfrom employees empjoin employees mangon emp.manager_id = mang.employee_idgroup by emp.manager_idhaving min_salary >= 6000;-- 或select employee_id,manager_id, min(salary) min_salaryfrom employeeswhere manager_id is not nullgroup by manager_idhaving min_salary >= 6000;#7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序select department_name,location_id,count(employee_id),round(avg(salary),2) avg_salaryfrom departments dleft join employees e on e.department_id = d.department_idgroup by department_nameorder by avg_salary desc;#8.查询每个工种、每个部门的部门名、工种名和最低工资select department_name,job_id,min(salary)from employees eright join departments d on e.department_id = d.department_idgroup by job_id, d.department_id;

4、SQL执行顺序

SELECT 语句的完整结构（SQL99）

select 去重 要查询的字段 from 表（注意：表和字段可以取别名）xxxx join 要连接的表on 等值判断(顺序：先on再where)where （具体的值/子查询，不包含聚合函数的过滤条件）group by(通过那个子段来分组)having （过滤分组后的信息，条件和where一样，位置不同，包含聚合函数的过滤条件）order by（通过哪个字段排序）limit （分页）

SQL语句的执行顺序