第五章 MySql-DQL命令

分组函数

• 功能：用作统计使用，又称聚合函数或者统计函数或者组函数 常见的有：sum求和， AVG求平均数， max求最大值， min求最小值， count计算个数, 以上这种函数操作统计的时候都是忽略null值得，null值不参与计算统计.

select sum(salary) as "总和" from employees ;
select avg(salary) as "平均值" from employees;
select max(salary) as "最大值" from employees;
select min(salary) as "最小值" from employees;
select count(salary) as "计算个数" from employees;

• 和distinct 搭配使用

select 
      sum(distinct commission_pct) as "总数" 
from 
      employees;

• 时间差计算获得结果为多少天

两个时间相减，结果为天
select datediff(max(hiredate),min(hiredate)) from employees;

分组函数 group by

• 案例1. 查询每个工种的最高工资

select 
        max(salary) as "最高工资" ,
        job_id as "工种列表"
from 
        employees
group by job_id;

• 案例2. 查询每个位置上的部门个数

select 
        count(*) as "总个数",
        xlocation_id as "每个位置"
from 
    departments
group by 
    location_id;

添加筛选条件 分组查询

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

select 
      avg(salary),
      department_id
from 
      employees
where 
      email like '%a%'
group by 
    department_id;

• 案例2.查询有奖金的每个领导手下员工的最高工资

select
       max(salary) as "最高工资",
       manager_id as "每个领导"
from
        employees
where
        commission_pct is not null
group by 
    manager_id;

• 案例2.添加复杂的筛选条件 查询哪个部门的员工个数大于2

这题需要拆分来看

1. 先要查询出来每个部门的员工个数，如下sql，用分组函数

select count(*),department_id
from employees
group by department_id;

2. 根据上面第1步的查询结果进行筛选，在来查询每个部门的员工个数大于2的 采用having关键字来连接，记住要放在group by后面

select count(*),department_id
from employees
group by 
    department_id 
having 
    count(*)>2;

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

• 分为两步做

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

select
       max(salary) as "最高工资",
       job_id as "每个工种"
from   
    employees
where  
    commission_pct is not null
group by 
    job_id;

2. 根据上面第1步的结果筛选最高工资大于12000

select 
    max(salary) as "最高工资",
    job_id as "每个工种"
from   
    employees
where  
    commission_pct is not null
group by 
    job_id
having 
    max(salary) >12000;

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

• 分两步操作

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

select 
      count(*),
      length(last_name) as "姓名长度"
from 
      employees
group by 
      length(last_name);

2. 添加筛选条件 大于5的有哪些

select 
       count(*),
       length(last_name) as "姓名长度"
from 
        employees
group by 
        length(last_name)
having  
    count(*) > 5;

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

select 
   avg(salary) "平均工资",
   department_id "部门",
   job_id "工种"
from 
       employees
group by 
    job_id, department_id;

查询每个部门每个工种的员工的平均工资,并且按平均工资的由高到低显示出来。

select avg(salary) as "平均工资",
       department_id as "部门",
       job_id as "工种"
from
       employees
group by department_id, job_id
order by avg(salary) desc ;

查询每个部门不为空的每个工种的员工的平均工资,并且按平均工资的高低显示出来。

select   avg(salary) as "平均工资",
         department_id as "部门编号",
         job_id as "工种"
from
         employees
where
         department_id is not null
group by
         department_id, job_id
order by
         avg(salary) desc ;

查询每个部门不为空的每个工种的员工的平均工资,要求平均工资高于1000的,并且按平均工资又高到低显示出来。

这句sql需要注意:
1. 要求平均工资高于10000的,而平局工资不在原始表中,
也就是说是分组后的结果集进行筛选，所以需要在group by后面，
自然就需要用到了having筛选；
2. 而查询每个不为空的部门,
而部门本来就在原始表数据中所以就要用到where筛选,而不用where；
select
         avg(salary) as "平均工资",
         department_id,job_id
from
         employees
where
         department_id is not null
group by
         department_id, job_id
having   
    avg(salary) > 10000
order by 
    avg(salary) desc;

练习

• 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

select
       min(salary) as "最低工资",
       manager_id as "管理者"
from
       employees
where
       manager_id is not null
group by
         manager_id
having
       min(salary) >= 6000;

• 查询所有部门的编号，员工数量和工资平均值，并按平均工资降序

select 
       department_id "部门编号",
       count(*) "数量",
       avg(salary) "平均工资"
from 
       employees
group by 
    department_id
order by 
    avg(salary) desc;

• 选择employees表中具有各个 job_id的员工人数

select
        count(*) "员工人数",
        job_id "工种编号"
from
        employees
group by 
    job_id;

以上就是group by分组查询，一分努力一分收获！坚持才是胜利！