MySQL分组函数

分组函数：

作用：用作统计使用，又称为聚合函数或者统计函数或者组函数。

分类：

• sum() 求和
• avg() 求平均值
• max() 求最大值
• min() 求最小值
• count() 计算个数

特点：

1. sum() , avg()一般用于处理数值

2. max() , min() , count()可以处理任何类型。

3. 以上分组函数都会自动过滤null。

4. 可以实现与distinct组合使用。

例子:

    --sum() 求和
    select sum(salary) from employees;
    
    --avg() 求平均值
    select avg(salary) from employees;
    
    --max() 求最大值
    select max(salary) from employees;
    
    --min() 求最小值
    select min(salary) from employees;
    
    --count() 计算个数
    select count(commission_pct) from employees;

分组查询：

语法：

select 查询列表 from 表 【where 筛选条件】 group by 分组字段 【order by】 【having 条件】

特点:

1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类:分组前筛选和分组后筛选
3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用

分组前筛选原始表  group by前where
分组后筛选group by后的结果集  group by后having

问题1:分组函数做筛选能不能放在where后面答:不能
问题2: where- droup by- having

一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

例题：

--查询将每个工种的员工的平均工资
select job_id,avg(salary) from employees GROUP BY job_id;

--查询邮箱中包含a字符的，每个部门的最高工资。
select department_id,MAX(salary) from employees where email like '%a%' GROUP BY department_id;
 
--查询有奖金的每个领导手下员工的平均工资。
select manager_id,AVG(salary) from employees where commission_pct is not null GROUP BY manager_id; 

--查询员工表中的最大入职时间和最小入职时间的相差天数 ( TO_DAYS() 将日期时间转换成天数)
select TO_DAYS(MAX(hiredate))-TO_DAYS(MIN(hiredate)) from employees;

select DATEDIFF(MAX(hiredate),MIN(hiredate)) from employees;		( DATEDIFF() 计算两段时间的天数 )

--查询部门编号为90的员工个数
select department_id,COUNT(employee_id) FROM employees where department_id = 90 GROUP BY department_id;
--查询公司员工工资的最大值，最小值，平均值，总和
select MAX(salary),MIN(salary),AVG(salary),SUM(salary) from employees;

--查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资。
select job_id,MAX(salary) from employees where commission_pct is not null GROUP BY job_id HAVING MAX(salary)>12000;

----查询每个工种有奖金的员工的最高工资>6000的工种编号和最高工资, 按照最高工资升序。
select job_id,MAX(salary) from employees where commission_pct is not null GROUP BY job_id HAVING MAX(salary)>6000 ORDER BY MAX(salary) DESC;

--查询每个工种每个部门的最低工资,并按最低工资降序
select job_id,department_id,min(salary) from employees GROUP BY job_id,department_id ORDER BY MAX(salary) DESC;

--查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
select MAX(salary),MIN(salary),AVG(salary),COUNT(salary) from employees GROUP BY job_id ORDER BY job_id;

--查询员工最高工资和最低工资的差距（DIFFERENCE）
select MAX(salary)-MIN(salary) DIFFERENCE from employees;

拓展：
count()的效率

SHOW ENGINES; --显示所有储存引擎

select count(1) from employees;
select count(*) from employees;

效率：

MYISAM储存引擎下，count()的效率高
INNODB储存引擎下，count()和count(1)的效率差不多

作业：

--1.查询部门编号为90的员工个数
select department_id,count(1) from employees where department_id = 90 ORDER BY department_id;

--2.领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
select manager_id,min(salary) from employees where manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;

--3.选择工资不在8000到17000的员工的姓名和工资，按工资降序
select last_name,salary from employees where salary not BETWEEN 8000 and 17000 ORDER BY salary desc;

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

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

--6.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
select job_id,max(salary),min(salary),avg(salary),sum(salary) from employees group by job_id order by job_id ;