数据库MySql学习之旅 七

二、分组函数

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

分类：

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

 特点：

     1、sum、avg一般用于处理数值型

            max，min，count可以处理任何类型

       2、是否忽略null值

           分组函数都忽略null值

       3、可以和distinct 搭配实现去重的运算

       4、count函数的单独介绍

           一般使用count(*) 用作统计行数

        5、和分组函数一同查询的字段要求是group by之后的字段      

1、简单的使用

select sum(salary) from employees;

select avg(salary) from employees;

select max(salary) from employees;

select min(salary) from employees;

2、参数支持哪些类型

select sum(last_name), avg(last_name) from employees;

注意：count 统计非空类型个数

3、是否忽略null

4、和distinct搭配

select sum(distinct salary) from employees;

5、count函数的详细介绍

select count(salary) from employees;

select count(*) from employees;

select count(1) from employees;

效率：

MYISAM存储引擎下，count(*)的效率高

INNODB存储引擎下，一般高

6、和分组函数一同查询的字段有限制

select AVG(salary),employee_id from employees;

 

进阶5：分组查询

select 分组函数，列（要求出现在group by的后面）from 表

【where 筛选条件】

group by 分组的列表

【order by 子句】

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

特点：

         1、分组查询中的筛选条件分为两类

                                               数据源                                     位置                                        关键字

             分组前筛选                原始表                                   group by子句的前面                   where

             分组后筛选                分组后的结果集                     group by子句的后面                   having

           

            一、分组函数做条件肯定是放在having子句中

            二、能用分组前筛选的优先放在分组前筛选

         2、group by子句支持单个字段分组，多个字段分组（多个字段用逗号隔开，没有顺序要求），表达式和函数

        3、可以添加排序（放在最后）

 

案例一：查询每个工种的最高工资

select max（salary），job_id from emplpyees group by job_id;

案例二：查询每个位置上的部门个数

select count（*）， loaction_id from deparements group  by loaction_id;

 

添加筛选条件

案例一：查询邮箱中包含a字符的，每个部门的平均工资

select AVG(salary), department_id from employees

where email like '%a%'

group by department_id;

案例二：查询有奖金的每个领导手下员工的最高工资

select max(salary), manager_id from employees

where commission_pct is not null

gruop by manager_id;

 

添加复杂的筛选条件

案例一： 查询哪个部门的员工个数大于2

1、查询每个部门的员工个数

select count(*) , department_id from employees

group by department_id;

2、根据1的结果进行筛选，查询哪个部门的员工个数大于2

select count(*) , department_id from employees

group by department_id

having count（*）> 2；

案例2：查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资

 1、查询每个工种有奖金的员工的最高工资

select max(salary) , job_id from employees

where commission_pct is not null

group by job_id

2、根据1结果继续筛选，最高工资大于12000

select max(salary) , job_id from employees

where commission_pct is not null

group by job_id having max(salary) > 120000

案例3： 查询领导编号大于102的每个领导手下的最低工资大于5000的领导编号是哪个，以及其最低工资

1、查询每个领导编号大于102的每个领导手下的员工最低工资

select min(salary), manager_id 

from employees

where manager_id > 102

group by manager_id

2、添加筛选条件，最低工资大于5000

select min(salary), manager_id 

from employees

where manager_id > 102

group by manager_id

having min(salary) >5000;

 

按照表达式函数分组

案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的有哪些

1、查询每个长度的员工个数

select count(*), length(last_name) len_name

from employees 

group by length(last_name)

2、添加筛选条件

select count(*), length(last_name) len_name

from employees 

group by length(last_name)

having count(*)>5

 

按照多个字段分组

案例：查询每个部门每个工种的员工的平均工资

select avg(salary), department_id, job_id 

from employees 

group by department_id, job_id;

 

添加排序

案例：查询每个部门每个工种的员工的平均工资，降序排序

select avg(salary), department_id, job_id 

from employees 

group by department_id, job_id

order by avg(salary) DESC;