Oracle 由易到难源代码四 子查询 深度查询 简单分页

             Oracle  查询方法

子查询

1 select * from emp where sal<(select avg(sal)from emp);

2 select (select dname form dept where deptno = e.deptno),ename,job from emp;

3  select * from (select * from emp where deptno = 20)e where job = 'manager';

深度查询

1  select lpad(ename,5*level,'_')from emp

2  connect by prior empno = mgr

3  start with mgr is null;

4  select empno||lpad(ename,5*level,'-')||mgr from emp

5  start with mgr is null

6  connect by prior empno = mgr;

分页显示

1  select * from(select rownum as num,emp.* from emp)e where num>=4 and num<=7；

2  select rowid,emp.* from emp;