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 等值判断(顺序:先on再where)
where (具体的值/子查询,不包含聚合函数的过滤条件)
group by(通过那个子段来分组)
having (过滤分组后的信息,条件和where一样,位置不同,包含聚合函数的过滤条件)
order by(通过哪个字段排序)
limit (分页)
SQL语句的执行顺序