Oracle经典子查询及分页基础详解

             Oracle经典子查询及分页基础详解

 

一：

1.如何显示高于自己部门平均工资的员工的信息。

（1）查询出各个部门的平均工资和部门号。

select deptNO,avg(sal)mysal from emp group by deptNO;

（2）把上面的查询看成是一张子表。

select a2.ename,a2.sal,a2.deptNO,a1.mysal from emp a2,(select deptNO,avg(sal)mysal from emp group by deptNO) a1 where a2.deptNO=a1.deptNo and a2.sal>a1.mysal;

在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫内嵌式视图，当在from子句中使用子查询时，必须给子查询指定别名。给表取别名不能加as。给列加别名可以加as。

 

2.oracle的分页一共有三种方式。（根据rowId，按分析函数来分，按rownum来分。）

(1) select a1.*,rownum rn from (select * from emp) a1;

注：rownum分页。rownum给每一行分配一个行号。

 

3.显示emp表的前十行：

（1）select a1.*,rownum rn from (select * from emp) a1 where rownum<=10;

（select * from emp）虚拟表可以看成个一个真实的表，where限制语句里面的条件需要是虚拟表里的面存在的。

注：这里rownum是红色部分表中隐含的列。可以通过下面查询语句进行验证

（2）select a2.*,rownum rn from (select a1.*,rownum from emp a1)a2 where rownum<=10;

注：这里rn不能做条件条件是之前就存在的，才能用作条件。

4.显示emp表5--10条：

select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where  rn>=6;

注： rownum在子查询中where之后只能出现一次，where  rownum>=5 and  rownum<=10是错的。

5.指定查询列，只需修改最里层的子查询。

select * from (select a1.*,rownum rn from (select ename,sal from emp) a1 where rownum<=10) where  rn>=6;

6.如何排序，只需修改最里层的子查询。

select * from (select a1.*,rownum rn from (select ename,sal from emp order by sal desc) a1 where rownum<=10) where  rn>=6;

7.显示第四条到第九条：

select * from (select a1.*,rownum rn from (select ename,sal from emp order by sal desc) a1 where rownum<=9) where  rn>=4;

查询其他的表的话，只可把表名给改了就行了。

8.显示emp表有多少行

select count（*）from emp；

9.用查询结果创建新表。这个命令是一种快捷的建表方法。

create table mytable(id,name,sal,job,deptNO) as select empNO,ename,sal,job,deptNO from emp;

10.合并查询。

union并集。该操作用于取得两个结果的并集。当使用该操作时，会自动去掉结果集中重复行。

select ename,sal,job from emp where sal>2500

union

select ename,sal,job from emp where job='MANAGER';

11.union all合并查询但不会去掉重复行。

12.minus，如果a包括b，那么显示a中除b的部分。

.

 

 

注：涉及子查询时.逻辑要清楚。根据查询语句相关的条件拆分分步击破。一层一层的由里到外击破。