分组函数(聚合函数)、分组查询
0 该博客所要用的数据库表的属性
1 分组函数
1.1 简单的使用
-
COUNT(expression)
:计算符合指定条件的行数。常用于计算记录数量。 -
SUM(expression)
:对指定列的值求和。通常用于计算数值型字段的总和。 -
AVG(expression)
:计算指定列的平均值。常用于计算数值型字段的平均值。 -
MIN(expression)
:计算指定列的最小值。用于找出某一列中的最小值。 -
MAX(expression)
:计算指定列的最大值。用于找出某一列中的最大值。
select
sum(salary) as 和,
count(salary) as 个数,
round(avg(salary),2) as 平均,
max(salary) as 最高,
min(salary) as 最低
from
employees;
1.2 是否忽略null值
null+任意值=null
select sum(commission_pct),avg(commission_pct)
from employees;
1.1的分组函数都忽略null值
1.3 和关键字搭配使用
select count(distinct salary) as 有几种工资
from employees;
1.4 count函数的详细介绍
统计行数
select count(*)
from employees;
1.5 练习
1
select
max(salary) as 最大值, min(salary) as 最小值, avg(salary) as 平均值, sum(salary) as 总和
from employees;
2
select DATEDIFF(max(hiredate),min(hiredate)) as DIFFERENCE
from employees;
3
select count(*) as 员工个数
from employees
where department_id=90;
2 分组查询Group by
select
from
where
group by
order by
2.1 简单的分组查询
1.查询每个工种的最高工资
select max(salary),job_id
from employees
group by job_id;
2.查询哪个部门的员工个数>2
select count(*),department_id
from employees
group by department_id
having count(*)>2;
3.查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select max(salary),job_id
from employees
where commission_pct is not null
group byjob_id
having max(salary)>12000;
4.查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
select manager_id,min(salary)
from employees
where manager_id>102
group by manager_id
having min(salary)>5000;
2.2 练习
1.查询各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 asc;
2.查询员工最高工资和最低工资的差距(DIFFERENCE)
select max(salary)-min(salary) as DIFFERENCE
from employees;
3.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
select manager_id,min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary)>=6000;
4.查询所有部门的编号,员工数量和工资平均值,并按平均工资降序
select department_id,count(*) as 员工数量,avg(salary)
from employees
group by department_id
order by avg(salary) desc;
5.选择具有各个job_id的员工人数
select job_id,count(*) as 员工人数
from employees
group by job_id;
3 格式投票😋
题外话,有一个格式投票,大家认为哪种好看呢
格式1.
select employee_id
from employee;
格式2.
select
employee_id
from
employee;