范例:统计所有雇员的人数,支付的总工资,平均工资,最高工资,最低工资
select count(empno),sum(sal),avg(sal),max(sal),min(sal) from emp; |
|
范例:统计带工薪的总工资和平均工资
select sum(sal+nvl(comm,0)*12) ,avg(sal+nvl(comm,0)*12) from emp; |
|
面试题:请解释count(*) count(字段)count(distinct 字段)的区别?
- count(*):明确返回表中数据的条数
- count(字段):不统计为null的数据条数,如果不为null,那么就等于count(*)
- count(distinct 字段):统计消除重复数据后的数据条数
在实现具体操作之前必须要先解决一个问题,什么情况下可以分组?
·例如:全班,男生一组,女生一组,互相拔河
·例如:全部,戴眼镜一组,不戴眼镜一组,互相比裸眼视力
·例如:25岁之前一组,25岁之后一组
实际上所谓的分组,指的是某些群体具备共同的特征。现在回到emp表中,可以发现如果存在有重复的数据,那么就证明这两个字段上都可以进行分组。
但是一定要记住,实际上一条记录也可以进行分组。只不过这类的做法是没有意义的。可以采用如下语法完成分组。
Select [distinct] * |列名称 [别名],列名称 [别名],…|统计函数 From 数据表 [别名],数据表 [别名],… [where 条件(s)] [group by 分组字段,分组字段,…] [order by 字段 [ASC|DESC]]; |
范例:按照职位进行分组,统计出每个职位的平均工资、最高工资和最低工资、人数。
select job ,avg(sal),max(sal),min(sal),count(empno) from emp group by job; 1、group by 和第一个字段相同; 2、后边的都是统计函数否则会出错; |
|
范例:按照部门编号进行分组,统计出每个部门的人数,平均工资,平均服务年限
select deptno ,count(empno),avg(sal),trunc(avg((sysdate-hiredate)/12)) from emp group by deptno; |
|
使用统计函数的限制:
- 统计函数单独使用时,没有group by子句,只能够出现统计函数;
- 使用统计函数时,存在group by子句,select 子句只能够出现分组字段,其他任何字段字段都不能出现;
- 统计函数在分组之中可以嵌套使用,但是嵌套之后的统计查询之中不能出现任何字段,包括分组字段;
以上的操作都是针对于单张表一个字段的分组,那么通过之前给出的语法可以设置多个分组字段,那么就要求这多个字段必须同时重复。
范例:查询出每个部门的名称,部门人数和平均工资。
|- 确立使用的数据表:
dept表:找到部门名称;
emp表:统计部门人数、平均工资。
|- 确立已知的关联字段
雇员和部门关联:emp.deptno=dept.deptno;
1、先不考虑分组的问题,换个角度,实现查询;查询出每个部门的名称,雇员编号,平均工资 |
select e.empno,d.dname,sal from emp e,dept d where e.deptno = d.deptno; |
|
2、在以上查询的过程中,发现了部门的名称数据是重复的,但是这个时候并不是对原始表进行操作,而是对这个临时表进行操作。 |
select d.dname,count(e.empno),avg(e.sal) from emp e,dept d where e.deptno=d.deptno group by d.dname; |
|
但是,有四个部门的数据,所以得建立右外连接,左边加➕号
|
select d.dname,count(e.empno),avg(e.sal) from emp e,dept d where e.deptno(+)=d.deptno group by d.dname; |
|
范例:查询出每个部门的编号,名称,位置,部门人数,平均工资
|- 确定所需要的数据表
- emp表:找到部门编号,名称,位置
- dept表:统计部门人数,平均工资
|- 确定已知的关联字段
雇员和部门关联:emp.deptno = dept.deptno;
select d.deptno,d.dname,d.loc,e.empno,e.sal from emp e, dept d where e.deptno=d.deptno; |
|
此时,最重要的是发现了临时表里边有deptno,loc,dname数据存在重复,并且想要对部门人数和平均工资使用统计函数,那么就用三个分组字段,并且使用右外连接 |
select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal) from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname,d.loc; |
|
错误:
select job ,avg(sal) from emp where avg(sal)>2000 group by job;
|
|
原因:
where子句之中不能够使用统计函数,where是在group by之间执行的,而此时的条件明显是在group by之后执行,所以使用having子句完成。
select job,avg(sal) from emp group by job having avg(sal)>2000; |
|
sql语句执行顺序:
- from子句确定数据来源
- where子句过滤数据行
- group子句执行分组操作
- having子句针对于分组之后进行数据过滤
- select子句确定数据列
- orderby子句进行数据排序
二、子查询
下面情况适用于大部分子查询:
- 子查询返回单行单列:having、where
- 子查询返回单行多列:where
- 子查询返回多行多列:from
范例:子查询返回单行单列è要求统计出所有高于公司平均工资的全部雇员信息
select * from emp where emp.sal>(select avg(sal) from emp); |
|
范例:子查询返回多行多列è要求找到与SCOTT工资相同,职位相同的雇员信息
select sal,job from emp where (sal,job)=(select sal,job from emp where emp.ename='SCOTT'); 注意:此时‘字符串’是区分大小写的,找不到小写字母的名字 |
|
IN:指的是在指定范围之内查询è
select * from emp where sal in(select sal from emp where job='MANAGER'); |
|
使用not in的时候必须注意此时不能包含null,否则会进行全选。
·>ANY:比子查询的最小值要大;
·<ANY:比子查询的最大值要小;
·>ALL:比子查询的最大值要大;
·<ALL:比子查询的最小值要小 ;
在having子句中使用子查询意味着进行了分组查询。
范例:查询高于公司平均工资的部门编号和平均工资
select deptno ,avg(sal) from emp group by deptno having avg(sal)>(select avg(sal) from emp); |
|
范例:查询出平均工资最低的职位信息、人数和平均工资
select job,avg(sal) from emp group by job having avg(sal) =(select min(avg(sal)) from emp group by job); select job,count(empno),avg(sal) from emp where sal=(select min(avg(sal)) from emp group by job ) group by job;
|
|
在from子句之中使用子查询。from子句主要功能是确定数据来源,而且数据来源是数据表,表是一种行和列的集合。from子句里边出现的子查询,其返回的结果一定是多行多列数据。
范例:查询出每个部门的编号,名称,位置,部门人数,平均工资
|- 确定表:
- emp表:部门的人数,平均工资
- dept表:部门的编号,名称,位置
|- 关联的字段:
emp.deptno = dept.deptno;
select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal) from emp e,dept d group by d.deptno,d.dname,d.loc; |
|
通过以上的程序虽然实现了与之前多个字段分组相同的功能,但是代码实在是过于复杂了。形成的笛卡尔积很大,造成了性能的下降,所以使用子查询。
范例:要求查询出高于公司平均工资的雇员姓名,职位,工资,领导姓名、部门名称,部门人数,部门平均工资,工资等级的雇员人数。
|-确定要使用的数据表
- emp表:统计公司的平均工资
- emp表:雇员姓名、职位、工资、领导姓名
- dept表:部门的名称
- emp表:部门的人数、平均工资
- salgrade表:工资等级
- salgrade表:统计一个工资等级的人数
|- 确立已知的关联字段:
- emp.mgr = emp2.mgr
- emp.deptno = dept.deptno;
- emp.sal between salgrade.losal and hisal;
select e.ename 雇员姓名,e.job 雇员职位,e.sal 雇员工资,m.ename 领导姓名,m.job 领导职位 from emp e,emp m where e.sal>(select avg(sal) from emp) and e.mgr=m.empno(+); |
|
加入dept表,找到部门名称
select e.ename 雇员姓名,e.job 雇员职位,e.sal 雇员工资,m.ename 领导姓名,m.job 领导职位,d.dname 部门名称 from emp e,emp m ,dept d where e.sal>(select avg(sal) from emp) and e.mgr=m.empno(+) and e.deptno = d.deptno; |
|
select e.ename 雇员姓名,e.job 雇员职位,e.sal 雇员工资,m.ename 领导姓名,m.job 领导职位,d.dname 部门名称,dtemp.count 部门人数 ,s.grade 工资等级 from emp e,emp m ,dept d,(select deptno dno,count(empno) count from emp group by deptno) dtemp ,salgrade s where e.sal>(select avg(sal) from emp) and e.mgr=m.empno(+) and e.deptno = d.deptno and dtemp.dno=d.deptno and e.sal between s.losal and s.hisal; |
|
|