Orcle基本语句（五）

 --分页查询---begin

 --sqlserver top

 --mysql limit

 --oracle rownum(伪列),oracle中伪列(rownum,rowid)

 --查询工资为前五的信息

 SELECT ROWNUM,tmp.* FROM (SELECT * FROM emp WHERE SAL IS NOT NULL ORDER BY sal DESC) tmp;

 --查询工资在第6条至第12条的信息

 --方法一：

 --1.查询在前12（包含第12条）的信息

 SELECT sal FROM (SELECT * FROM emp WHERE SAL IS NOT NULL ORDER BY sal DESC) WHERE ROWNUM <=12;

 --2.查询在前6（包含第六条）的信息

 SELECT sal FROM (SELECT * FROM emp WHERE SAL IS NOT NULL ORDER BY sal DESC) WHERE ROWNUM <=5;

 --3.

 SELECT ROWNUM, tmp.*

   FROM (SELECT * FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC) tmp

  WHERE sal IN

        (SELECT sal

           FROM (SELECT * FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC)

          WHERE rownum <= 12)

    AND sal NOT IN

        (SELECT sal

           FROM (SELECT * FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC)

          WHERE rownum <= 5);

 --方法二：嵌套子查询

 SELECT tem.*

   FROM (SELECT rownum r, tmp.*

           FROM (SELECT * FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC) tmp) tem

  WHERE tem.r BETWEEN 6 AND 12;

 --查询工资最高的第6条到第12条的员工信息

 --1.

 SELECT *

   FROM (SELECT rownum row_top ,tt.*

           FROM (SELECT e.*

                   FROM employees e

                  WHERE e.salary IS NOT NULL

                  ORDER BY salary DESC) tt)

  WHERE row_top BETWEEN 6 AND 12;

 --2. 效率较于1

 SELECT *

   FROM (SELECT rownum row_top, tt.*

           FROM (SELECT rownum rn, e.*

                   FROM employees e

                  WHERE e.salary IS NOT NULL

                  ORDER BY salary DESC) tt)

  WHERE row_top <= 12

    AND row_top >= 6;

 --3.效率较于2---推荐使用

 SELECT *

   FROM (SELECT rownum rn, tt.*

           FROM (SELECT *

                   FROM employees e

                  WHERE e.salary IS NOT NULL

                  ORDER BY salary DESC) tt

          WHERE rownum <= 12)

  WHERE rn >= 6

 --视图

 --查询全部的20号 部门的雇员信息（雇员编号，姓名，工作，雇佣日期）

 CREATE OR REPLACE VIEW view20 AS SELECT empno, ename, job, hiredate FROM emp WHERE deptno = 20;

 --创建视图v_dept：查询每个部门的编号，名称，员工人数，员工工资总和

 CREATE OR REPLACE VIEW v_dept AS

 SELECT e.deptno,d.dname,COUNT(e.empno) c, SUM(e.sal) s

   FROM emp e

   FULL JOIN dept d

     ON e.deptno = d.deptno

  GROUP BY e.deptno,d.dname;

 SELECT * FROM v_dept WHERE c >= 3;

 SELECT Count(*) from emp;