1.统计函数
组函数 |
描述 |
count(*|[distinct] 列) |
求出全部的记录数 |
sum(列) |
求出总和,操作的列是数字 |
avg(列) |
平均值 |
max(列) |
最大值 |
min(列) |
最小值 |
median(列) |
返回中间值 |
统计函数的应用
1. 查询出公司每个月支出的月工资的总和
select sum(sal)
from emp;
--1.1支出的奖金总和
select sum(comm)
from emp;
/* sum函数的作用
获取出查询结果中某列非空列值之和*/
2查询出公司的最高工资、最低工资、平均工资
select max(sal),min(sal),avg(sal）
from emp;
--2.1
select max(comm),min(nvl(comm,0)),avg(comm）
from emp;
3统计出公司最早雇佣和最晚雇佣的雇佣日期
--越早出现的时间越小，越迟出现的时间越大
select min(hiredate),max(hiredate)
from emp;
4统计公司中间的工资值
select median(sal)
from emp;
5 统计出公司的雇员人数
select count(*)
from emp;
--5.1
select count(distinct deptno)
from emp;
--5.2
select count(distinct comm)
from emp;
2.分组统计
2.1单字段分组统计
分组统计语法:
select [distinct] 分组字段 [as] [列别名],...|
统计函数 [as] [别名],...
from 表名称1 [表别名 1],表名称2 [表别名 2],...
[where 条件(s)]
[group by 分组字段]
[order by 排序字段 asc | desc];
group by子句是写在where子句之后的,并且需要指定一个分组的字段
单字段分组统计应用分组操作注意事项
1统计出每个部门的人数
/*select count(*)
from emp;*/
select deptno, count(*)
from emp
group by deptno;
--第一步：获取每个部门的员工信息，左外连接
select d.*,e.*
from dept d
left outer join emp e
on(d.deptno=e.deptno);
--第二步:对第一部的查询结果
select d.deptno,count(e.empno)
from dept d
left outer join emp e
on(d.deptno=e.deptno)
group by d.deptno;
2统计出每种职位的最低工资和最高工资
select job,min(sal),max(sal)
from emp
group by job;
select job,min(nvl(sal,0)),max(sal)
from emp
group by job;
3.求出每个部门平均工资中的最高的平均工资
--第一步：获取每个部门的员工信息
select d.*,e.*
from dept d
left outer join emp e
on(d.deptno=e.deptno);
--第二步：获取每个部门的平均工资
select d.deptno,avg(sal)
from dept d
left outer join emp e
on(d.deptno=e.deptno)
group by d.deptno;
select d.deptno,avg(nvl(e.sal,0))
from dept d
left outer join emp e
on(d.deptno=e.deptno)
group by d.deptno;
--第三步：获取每个部门的平均工资中的最高的平均工资
select max(avg(sal))
from dept d
left outer join emp e
on(d.deptno=e.deptno)
group by d.deptno;
select max(avg(nvl(e.sal,0)))
from dept d
left outer join emp e
on(d.deptno=e.deptno)
group by d.deptno;
/*
select d.deptno, max(avg(nvl(e.sal,0)))
from dept d
left outer join emp e
on(d.deptno=e.deptno)
group by d.deptno;*/
4查询每个部门的名称、部门人数、部门平均工资、 平均服务年限
--第一步：获取每个部门的员工信息
select d.*,e.*
from dept d
left outer join emp e
on(d.deptno=e.deptno);
--第一步：按部门分组，获取每个部门的名称，
select d.dname,count(e.empno),avg(nvl(e.sal,0)),
avg(months_between(sysdate,hiredate)/12)
from dept d
left outer join emp e
on(d.deptno=e.deptno)
group by d.dname;
5.查询公司各个工资等级雇员的数量、平均工资
--第一步：查出每个工资等级的雇员
select s.grade,avg(e.sal)
from salgrade s
left outer join emp e
on(e.sal between s.losal and s.hisal);
--第二步：按工资等级分组，查询各个工资等级雇员的数量、平均工资
select s.grade,avg(e.sal),count(e.empno)
from salgrade s
left outer join emp e
on(e.sal between s.losal and s.hisal)
group by s.grade;
6.统计出领取佣金与不领取佣金的雇员的平均工资、平均雇佣年限、雇员人数
--第一步：查询领取佣金的雇员的平均工资、平均雇佣年限、雇员人数
select avg(sal),avg(months_between(sysdate,hiredate)/12),count(empno)
from emp
where comm is not null and nvl(comm,0)>0;
/*
这条语句会出现错误，原因是：在没有group by 字句的select字句中出现了统计函数，就不能出现单个字段
select avg(sal),avg(months_between(sysdate,hiredate)/12),count(empno)
from emp
where comm is not null and nvl(comm,0)>0;*/
--第二步：查询领取不佣金的雇员的平均工资、平均雇佣年限、雇员人数
select avg(sal),avg(months_between(sysdate,hiredate)/12),count(empno)
from emp
where comm is null or nvl(comm,0)<=0;
--第三步：统计出领取佣金与不领取佣金的雇员的平均工资、平均雇佣年限、雇员人数
select avg(sal),avg(months_between(sysdate,hiredate)/12),count(empno)
from emp
where comm is not null and nvl(comm,0)>0
union
select avg(sal),avg(months_between(sysdate,hiredate)/12),count(empno)
from emp
where comm is null or nvl(comm,0)<=0;
1 如果没有group by子句,则在select子句中出现统计函数, 其他任何字段都不允许出现
2 如果有group by子句,select子句后只允许出现分组字段和统计函数,而其他的非分组字段不能使用.
3 统计函数允许嵌套使用,但是嵌套统计函数之后的select子中不允许再出现任何的字段,包括分组字段.
sql语句中加入了group by子句之后的执行顺序
(1) 执行from子句,确定要检索的数据来源
(2) 执行where子句,使用限定符对数据进行过滤
(3) 执行group by子句,根据指定字段进行分组
(4) 执行select子句,确定要检索出的分组字段以及编写相应统计函数
(5) 执行order by子句的排序
2.2 多字段分组统计
多字段分组统计语法：
select [distinct] 分组字段1 [as] [列别名],
[分组字段2 [as] [列别名],...]
| 统计函数 [as] [别名],...
from 表名称1 [表别名 1],表名称2 [表别名 2],...
[where 条件(s)]
[group by 分组字段1 ,分组字段2,... ]
[order by 排序字段 asc | desc];多字段查询应用：1.查询出每个部门的编号、名称、位置、部门人数、
平均工资、总工资、最高工资、最低工资
--第一步：查询出每个部门的编号、名称、位置、以及该部门的员工信息
select d.deptno,d.dname,d.loc,e.*
from dept d
left outer join emp e
on d.deptno=e.deptno;
--第二步：查询出每个部门的编号、名称、位置、部门人数、平均工资、总工资、最高工资、最低工资
select d.deptno,d.dname,d.loc,count(e.empno),avg(nvl(sal,0)),sum(nvl(e.sal,0)),max(nvl(e.sal,0)),min(nvl(e.sal,0))
from dept d
left outer join emp e
on d.deptno=e.deptno
group by d.deptno,d.dname,d.loc;
select d.deptno,d.dname,d.loc,count(e.empno),avg(sal),sum(e.sal),max(e.sal),min(e.sal)
from dept d
left outer join emp e
on d.deptno=e.deptno
group by d.deptno,d.dname,d.loc;
having子句作用
having子句完成对分组之后的数据进行再次过滤，对分组之后的数据进行再次过滤的语法
select [distinct] 分组字段1 [as] [列别名],
分组字段2 [as] [列别名],...]
| 统计函数 [as] [别名],...
from 表名称1 [表别名 1],表名称2 [表别名 2],...
[where 条件(s)]
[group by 分组字段1 ,分组字段2,... ]
[having 过滤条件(s)]
[order by 排序字段 asc | desc];having子句作用1 查询出所有平均工资大于2000元的职位信息、平均工资、
雇佣人数
--第一步：查询出每个职位的平均工资，雇佣人数
select job,avg(sal),count(empno)
from emp
group by job;
--第二步：查询出所有平均工资大于2000元的职位信息、平均工资、雇佣人数
select job,avg(sal),count(empno)
from emp
group by job
having avg(sal)>2000;
2 列出至少一个员工的所有部门编号、名称,并统计出这些
部门的平均工资、最低工资、最高工资.
--第一步：查询出每个部门编号、名称，以及该部门的员工信息
select d.deptno,d.dname,e.*
from dept d
left outer join emp e
on d.deptno=e.deptno;
--第二步：按部门名称，编号进行分组查询出每个部门的部门编号、名称，平均工资、最低工资、最高工资.
select d.deptno,d.dname,avg(nvl(e.sal,0)),min(nvl(e.sal,0)),max(nvl(e.sal,0))
from dept d
left outer join emp e
on d.deptno=e.deptno
group by d.deptno,d.dname;
--第三步：对分组进行过滤，只显示满足having中条件表达式的分组数据，从而查出至少一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高工资.
select d.deptno,d.dname,avg(nvl(e.sal,0)),min(nvl(e.sal,0)),max(nvl(e.sal,0))
from dept d
left outer join emp e
on d.deptno=e.deptno
group by d.deptno,d.dname
having count(e.empno)>=1;
3 显示非销售人员工作名称以及从事同一工作雇员的月工资
的总和,并且要满足从事同一工作的雇员的月工资合计
大于5000元输出结果按月工资的合计升序排列.
--第一步：查询出每一个工作的雇员月工资总和
select job,sum(sal)
from emp
group by job;
--第二步：查询出非销售人员从事的每一个工作的的月工资总和
select job,sum(sal)
from emp
where job<>'SALESMAN'
group by job;
--第三步：过滤部分分组，查询月工资合计大于5000元的工作，以及该工作的月工资总和
select job,sum(sal)
from emp
where job<>'SALESMAN'
group by job
having sum（sal）>5000;
--第四步：/*对第三步的查询结果按月工资的合计升序排列.
从而非销售人员工作名称以及从事同一工作雇员的月工资
的总和,并且要满足从事同一工作的雇员的月工资合计
大于5000元输出结果按月工资的合计升序排列.*/
select job,sum(sal)
from emp
where job<>'SALESMAN'
group by job
having sum（sal）>5000
order by sum(sal) asc;
六.子查询
1.查询公司中工资最低的雇员的完整信息
--第一步：查询公司中的最低工资
select min(sal)
from emp;
--第二步：查询公司中雇员的完整信息
select *
from emp;
--第三步：查询公司中工资最低的雇员的完整信息
/* select *
from emp
where sal=800;*/
select *
from emp
where sal=( select min(sal)
from emp); //作为逻辑表达式的操作数
2.子查询返回单行单列数据
2-1 查询出基本工资比ALLEN低的全部雇员信息
--第一步：查询出ALLEN的基本工资
select sal
from emp
where ename='ALLEN';
--第二步：查询出全部雇员信息
select *
from emp;
--第三步：查询出基本工资比ALLEN低的全部雇员信息
select *
from emp
where sal<( select sal
from emp
where ename='ALLEN' );
2-2 查询基本工资高于公司平均薪资的全部雇员信息
--第一步：查询公司平均薪资
select avg(sal)
from emp;
--第二步：查询全部雇员信息
select *
from emp;
--第三步： 查询基本工资高于公司平均薪资的全部雇员信息
select *
from emp
where sal>( select avg(sal)
from emp);
2-3 查找出与ALLEN从事同一工作,并且基本工资高于
雇员编号为7521的全部雇员信息.
--第一步： ALLEN从事的工作,
select job
from emp
where ename='ALLEN';
--第二步： 查找出编号为7521的雇员的基本工资
select sal
from emp
where empno=7521;
--第三步：查询全部雇员信息
select *
from emp;
--第四步： 查找出与ALLEN从事同一工作,并且基本工资高于雇员编号为7521的全部雇员信息.
select *
from emp
where job=( select job
from emp
where ename='ALLEN')
and sal>( select sal
from emp
where empno=7521)
and ename<>'ALLEN';
sql中加入了having子句之后的执行顺序
(1) 执行from子句,确定要检索的数据来源.
(2) 执行where子句,使用限定符对数据进行过滤.
(3) 执行group by子句,根据指定字段进行分组.
(4) 执行having子句,对分组后的统计数据进行过滤.
(5) 执行select子句,确定要检索出的分组字段以及编写相应的统计函数
(6) 执行order by子句排序.
where子句和having子句的区别
where子句: 是在分组之前使用,表示从所有数据中筛选出数据,以完成分组的要求,在where子句中不允许使用统计函数,没有group by子句也可以使用.
having子句: 是在分组之后使用的,表示对分组统计后的数据执行再次过滤,可以使用统计函数,有group by子句之后才可以出现having子句.