第五章 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
这题需要拆分来看
- 先要查询出来每个部门的员工个数,如下sql,用分组函数
select count(*),department_id
from employees
group by department_id;
- 根据上面第1步的查询结果进行筛选,在来查询每个部门的员工个数大于2的 采用having关键字来连接,记住要放在group by后面
select count(*),department_id
from employees
group by
department_id
having
count(*)>2;
查询每个工种有奖金的员工的最高工资>12000的工种编号,和最高工资
- 分为两步做
- 查询每个工种有奖金的员工的最高工资
select
max(salary) as "最高工资",
job_id as "每个工种"
from
employees
where
commission_pct is not null
group by
job_id;
- 根据上面第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的有哪些
- 分两步操作
- 查询每个长度的员工个数
select
count(*),
length(last_name) as "姓名长度"
from
employees
group by
length(last_name);
- 添加筛选条件 大于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分组查询,一分努力一分收获!坚持才是胜利!