oracle表分组统计,Oracle入门笔记【3】多表查询与分组统计查询

1，先实现多表查询：

select * from emp,dept;

(可以发现两个集合发生了乘积，这叫笛卡尔积问题。)

消除笛卡尔积：

select *

from emp e,dept d

where e.deptno=d.deptno;

(这只是消除了显示的笛卡尔积而已，开发中多表查询尽量避免)

范例1：

select e.empno

,e.ename,e.job,e.sal,e.sal,d.dname,d.loc

from emp e,dept d

where e.deptno=d.deptno;

范例2：

select e.empno,e.ename,e.job,e.sal,s.grade,d.dname

from emp e,salgrade s,dept d

where e.sal between s.losal and s.hisal and

e.deptno=d.deptno;

2，外连接：

以上为内连接，而外连接是让等值判断左右两边有一边数据全部显示出来

左外连接：

select

e.empno,e.ename,e.job,d.deptno,d.dname,d.loc

from emp e,dept d

where e.deptno=d.deptno(+);

右外连接：

select

e.empno,e.ename,e.job,d.deptno,d.dname,d.loc

from emp e,dept d

where e.deptno(+)=d.deptno;

范例：查询每个雇员的姓名，职位，领导姓名。(此题为自身关联)

select e.ename,e.job,m.ename

from emp e,emp m

where e.mgr=m.empno(+);

3，SQL1999语法：

交叉连接：

select * from emp cross join dept;

自然连接：

select * from emp natural join dept;

USING子句：

select * from emp join dept using(deptno);

ON子句：

select * from emp e join salgrade s on(e.sal between s.losal

and s.hisal);

外连接：

左：

select * from emp left outer join dept using(deptno);

右：

select * from emp right outer join dept using(deptno);

全：

select * from emp full outer join dept using(deptno);

4，数据集合(负责连接查询的结果)：

验证union(并集)：

select empno,ename,job,deptno from emp

where deptno=10

union

select empno,ename,job,deptno from emp ;

验证union all(所有):

select empno,ename,job,deptno from emp

where deptno=10

union all

select empno,ename,job,deptno from emp ;

验证intersect(交集):

select empno,ename,job,deptno from emp

where deptno=10

intersect

select empno,ename,job,deptno from emp ;

验证minus(差集):

select empno,ename,job,deptno from emp

minus

select empno,ename,job,deptno from emp where

deptno=10 ;

5，统计函数：

查询所有雇员中最高和最低工资：

select max(sal),min(sal)from emp;

统计出所有雇员的总工资和平均工资：

select sum(sal),avg(sal) from emp;

统计所有雇员的平均工作年限：

select avg(months_between(sysdate,hiredate)/12)from emp;

请解释count(),count(字段)，count(distinc字段)的区别：

count(*)：明确返回表中的数据个数，是最准确的；

count(字段)：不统计为null的数据个数，如果某一列的数据不可能为空结果与上面相同；

count(distinc 字段)：统计消除掉重复数据后的数据个数；

6，分组统计查询：

要求按照职位分组，统计出每个职位的名称、人数、平均工资：

select job ,count(empno),avg(sal)

from emp

group by job;

查询每个部门的名称、人数、平均工资：

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.deptno,d.dname,d.loc,count(e.empno),avg(months_between(sysdate,e.hiredate)/12)

from emp e,dept d

where e.deptno(+)=d.deptno

group by d.deptno,d.dname,d.loc;

只有在group by 中出现的才可以在select中出现。

要求查询平均工资高于2000的职位名称以及平均工资：

select job,avg(sal)

from emp

group by job

having avg(sal)>2000;

where与having的区别：

where发生在group by 之前，属于分组前的筛选，即：从所有的数据之中筛选出可以分组的数据。不允许使用统计函数

having发生在group by之后，是针对于分组后的数据进行筛选；可以使用统计函数。