MySQL复习笔记（六）：统计函数、分组函数（Group By）

统计函数

功能：主要用于统计，又称为分组函数、聚合函数

简单使用

SELECT sum(salary) from employees;
SELECT avg(salary) from employees;
SELECT max(salary) from employees;
SELECT min(salary) from employees;
SELECT count(salary) from employees;

SELECT sum(salary) '和', ROUND(avg(salary), 2) '平均值', max(salary) '最大值', min(salary) '最小值', count(salary) '个数' from employees;

特点

1. sum、avg一般用于处理数值型，max、min、count可以处理任何类型。
2. 以上统计函数都忽略null值
3. 都支持distinct
4. 一般使用count(*)统计行数
5. 和统计函数一同查询的字段要求是group by后的字段，不能是其他字段

和distinct搭配使用

#去重之后在计数
SELECT COUNT(DISTINCT job_id), count(job_id) FROM employees; #   19    107

count函数的详细介绍

#一般使用count(*)用来统计行数，使用count(1)也可以
#MYISAM存储引擎下，count(*)效率更好
#INNODB下，两个相差不多，但是都比count(字段)效率高
select count(*) from employees
select count(1) from employees

练习

#查询公司员工工资的最大值，最小值，平均值和总和
SELECT max(salary), min(salary),avg(salary),sum(salary) FROM employees;


#查询员工表中最早入职和最晚入职相差的天数
#DATEDIFF()函数用于计算两个日期之间相差的天数
SELECT DATEDIFF(max((hiredate)),min(hiredate)) as 'dif' FROM employees;



#查询部门编号为90的员工个数
SELECT count(*) FROM employees where department_id=90;

分组查询

语法：

SELECT 统计函数，列（要求出现在group by的后面）
FROM 表名
【where 筛选条件】
group by  分组的列表
【order by 子句】

注意：查询的列表，要求是分组函数和group by后出现的字段

特点：

1. 分组查询中的筛选分为两类：

   分组前筛选： 数据源来自原始表，使用where关建字，放在group by之前

   分组后筛选：数据源来自group by的结果，使用having关建字，放在group by之后

   统计函数做筛选条件时，一定是放在having中

   能放在分组前筛选的，就优先考虑分组前筛选

2. group by后面既可以跟字段，也可以跟表达式或者函数

简单分组

1、查询每个工种的最高工资：

SELECT job_id,(salary) from employees GROUP BY job_id;

2、查询每个地区的部门个数：

SELECT location_id, count(*) FROM departments GROUP BY location_id;

分组前的筛选

3、查询邮箱中包含a字符的，每个部门的平均工资

SELECT department_id, avg(salary)
FROM employees
WHERE email like '%a%'
GROUP BY department_id;

分组后的筛选，使用到到了分组结果（使用HAVING）

技巧：写完group by，再看筛选条件从哪里获得选择使用where(原表)或者having(group by后统计的结果)

4、查询哪个部门的员工个数大于2

#1.查询每个部门的员工个数
#2.根据1的结果进行筛选，查找员工个数大于2的分组
SELECT department_id, count(*)
FROM employees
GROUP BY department_id
HAVING count(*)>2;

5、查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id, max(salary) as 'mx_sal'
FROM employees
WHERE commission_pct is not null
GROUP BY job_id
HAVING mx_sal>12000;

6、查询领导编号>102的每个领导手下员工的最低工资>5000的领导编号和最低工资

SELECT manager_id, min(salary) as 'mn_sal'
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING mn_sal > 5000;

按照表达式、函数和别名分组

7、按照员工姓名长度进行分组，查询每一组中员工个数，查询个数大于5的有哪些

SELECT LENGTH(last_name) as len_name, count(*) as cnt
FROM employees
GROUP BY LENGTH(last_name)	#按照表达式分组
#GROUP BY len_name    		#按照别名分组
HAVING cnt > 5;

按照多个字段进行分组

8、查询每个部门每个工种的员工平均工资

SELECT avg(salary), department_id, job_id
FROM employees
GROUP BY department_id, job_id