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

1、聚合函数介绍

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

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

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

2、GROUP BY 分组

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

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

GROUP BY子句的语法:

SELECT
    column1,
    column2,
    GROUP_FUNCTION (column3)
FROM
    table1
WHERE
    a = b
GROUP BY
    column1,
    column2
HAVING
    c = d
ORDER BY
    column2 DESC;

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

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

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

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

3、常见的聚合函数

函数名称作用
MAX查询指定列的最大值
MIN查询指定列的最小值
COUNT统计查询结果的行数
SUM求和,返回指定列的总和
AVG求平均值,返回指定列数据的平均值

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_salary
FROM employees e
JOIN departments d on e.department_id = d.department_id
GROUP BY department_name
ORDER BY department_name;

-- SUM 返回每个部门中所有员工的总薪水
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id;

-- MAX / MIN  返回每个部门中员工的最低和最高薪水
SELECT department_name, MIN(salary) min_salary,MAX(salary) max_salary
FROM employees e
JOIN departments d on e.department_id = d.department_id
GROUP BY department_name
ORDER BY department_name;

-- COUNT  返回每个部门的人数并根据部门名升序
SELECT department_name, COUNT(*) headcount
FROM employees e
JOIN departments d on e.department_id = d.department_id
GROUP BY department_name
ORDER BY department_name;

-- 查询最低薪资大于6000的各个部门的信息、最高薪资和平均薪资
select e.department_id,department_name,min(salary) min_salary,max(salary) max_salary,round(avg(salary),2) average_salary
from employees e
join departments d on e.department_id = d.department_id
GROUP BY e.department_id
having min_salary > 6000
order by department_id ;

-- 查找人数大于5的部门
SELECT e.department_id,department_name,COUNT(employee_id) headcount
FROM employees e
JOIN departments d ON d.department_id = e.department_id
GROUP BY e.department_id
HAVING headcount > 5
ORDER 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 employees
group by job_id;

#4.选择各个job_id具有的员工人数
select job_id,count(*)
from employees
group by job_id;

#5.查询员工最高工资和最低工资的差距(DIFFERENCE)
select max(salary) - min(salary) DIFFERENCE
from employees;

#6.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
select emp.employee_id,emp.manager_id, min(emp.salary) min_salary
from employees emp
join employees mang
on emp.manager_id = mang.employee_id
group by emp.manager_id
having min_salary >= 6000;
-- 或
select employee_id,manager_id, min(salary) min_salary
from employees
where manager_id is not null
group by manager_id
having min_salary >= 6000;

#7.查询所有部门的名字,location_id,员工数量和平均工资,并按平均工资降序
select department_name,location_id,count(employee_id),round(avg(salary),2) avg_salary
from  departments d
left join employees e on e.department_id = d.department_id
group by department_name
order by avg_salary desc;

#8.查询每个工种、每个部门的部门名、工种名和最低工资
select department_name,job_id,min(salary)
from employees e
right join departments d on e.department_id = d.department_id
group by job_id, d.department_id;

4、SQL执行顺序

SELECT 语句的完整结构(SQL99)

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

SQL语句的执行顺序

在这里插入图片描述

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐