统计函数
count(*) --- 对于count()函数而言 可以传递三类内容:
*, 字段, distinct字段
面试题:
请问count(*) count(字段),count(distinct 字段)有什么区别
如果列上存在null,那么null不统计,如果使用distinct , 那么列上如果有重复,重复的记录也不统计
使用count(*)是最方便的,
最好用count(字段)
在所有统计函数,
只有count()函数可以在表中没有任何数据的时候,依然返回内容
. count()永远会有数据返回
sum()
avg()
max()
min()
median() -----返回中间值 例如,现在有三个数字 2000,3000,2500,所谓的中间值就是2500----select median(sal) from emp
variance() -----返回方差
stddev() -------返回标准差 select stddev(sal),variance(sal) from emp;
select max(sal) ,min(sal),avg(sal),round(avg(sal),2) from emp
oracel 中的函数是可以进行数据类型的互相转换,最早雇佣hiredate值一定是最小的,最晚雇佣的 hiredate 是最大的
select min(hiredate) ,max(hiredate) from emp
单字段分组查询
对于统计函数而言,单独使用的情况一定是有的
本章目标
掌握group by 字句的使用
掌握分组操作的使用限制
范例:
统计出
每个部门
的人数
select deptno,count(empno) from emp group by deptno
范例
:统计出
每种职位
的最低和最高工资
select job, min(sal) ,max(sal) from emp group by job
注意事项一:
如果在一个查询之中不存在group by 字句 那么在select 字句之中只允许 出现统计函数 ,其他的任何字段不允许出现
错误的程序:
select
deptno
,count(empno) from emp
正确的程序:
select count(empno) from emp
注意事项二
在统计查询之中,存在了group by 字句 ,select字句之中只允许出现分组字段(group by之后定义的字段) 和统计函数,其他的
任何字段都不允许出现
范例:错误的程序:
select deptno ,
ename
,count(empno) from emp group by deptno
正确的程序:
select deptno
,count(empno) from emp group by deptno
注意事项三:
所有统计函数允许嵌套使用,但是一旦使用了嵌套的统计函数之后,
select字句之中不允许在出现任何字段
,包括分组字段
范例:求出每个部门平均工资最高的工资
错误代码:
select
deptno
max(avg(sal))
from emp group by deptno 此时因为select 字句之中存在了deptno的字段,所以这个时候出现了错误
正确代码
select
max(avg(sal))
from emp group by deptno
范例
:查询每个部门的名称,部门人数,平均工资,平均服务年限
select
d.dname,
count
(e.empno),
round
(
avg
(e.sal)),
round
(
avg
(months_between(
sysdate
,e.hiredate)/
12
),
2
) avgyear
from
emp e,dept d
where
e.deptno = d.deptno
group
by
d.dname
select
d.dname,
count
(e.empno),
round
(
avg
(e.sal)),
round
(
avg
(months_between(
sysdate
,e.hiredate)/
12
),
2
) avgyear
from
emp e,dept d
where
e.deptno (+)= d.deptno
group
by
d.dname
字句顺序:
from --- where --- group by --- select ---- order by
范例
:查询公司各个工资等级雇员的数量,平均工资
select
s.group,
count(e.empno),avg(e.sal) from emp e, salgrade s where emp.sal between s.losal and s.hisal group by s.group
范例:统计出领取佣金与不领取佣金的雇员的平均工资,平均服务年限,雇员人数
错误代码:select comm round(avg(sal),2) avgsal, round(avg(months_between(sysdate,hiredate/12),2) avgyear,count(empno) count
from emp group by comm
正确代码:
select
'领取佣金'
,
round
(
avg
(sal),
2
) avgsal,
round
(
avg
(months_between(
sysdate
,hiredate)/
12
),
2
) avgyear,
count
(empno)
count
from
emp
where
comm
is
not
null
union
select
'不领取佣金'
,
round
(
avg
(sal),
2
) avgsal,
round
(
avg
(months_between(
sysdate
,hiredate)/
12
),
2
) avgyear,
count
(empno)
count
from
emp
where
comm
is
null
多字段统计查询
本章目标
在group by 中使用多个字段进行分组实现
既然可以在group by字句中出现多个分组字段,那么在select 字句里面也一定可以
定义多个分组字段的查询显示
范例:
现在要求查询出每个部门的详细信息(部门编号,名称,位置,平均工资,总工资,最高工资,最低工资,部门人数)
步骤一:
select d.deptno,d.dname,d.loc,e.empno,e.ename
from emp e,dept d
where e.deptno = d.deptno
三个列上的数据整体都在重复,既然列上的信息重复,就一定 具备了分组的条件
步骤二 使用多字段分组,同时实现统计信息
select d.deptno,d.dname,d.loc,count(e.empno) count , round(avg(e.sal),2) avg ,sum(e.sal) sum, max(e.sal) max ,min(e.sal) min
from emp e,dept d
where e.deptno (+)= d.deptno
group by d.deptno,d.dname,d.loc
HAVING字句
本章目标
掌握having字句的使用
理解having与where字句的区别
having字句一定要与group by 子句一起使用
范例:查询出所有
select ..
from ...
where ...=...
group by ...
having ...
order by...