ORACLE_ROWNUM_ORDER BY _分页_查询

1.伪列 ROWNUM  SELECT ROWNUM FROM *

     SELECT ROWNUM,E.* FROM EMP E;

2.排序  ORDER BY  SELECT * FROM * ORDER BY *(ASC,DESC)

     SELECT * FROM EMP ORDER BY SAL ASC; //升序

     SELECT * FROM EMP ORDER BY SAL DESC;//降序

     SELECT * FROM EMP ORDER BY HIREDATE;//按时间的类型排序

     SELECT * FROM EMP ORDER BY ENAME;//按照字符串排序

     查询员工信息，按照工资升序，如果一致，就按照入职时间排序

     SELECT * FROM EMP ORDER BY SAL ASC,HIREDATE DESC;

3.分页查询

   SELECT S.* FROM (SELECT ROWNUM R,E.* FROM (SELECT * FROM EMP ORDER BY SAL ) E) WHERE R>5 AND             R<=10;

 4.BETWEEN .....AND   //介于两者之间  SELECT * FROM * WHERE * BETWEEN * AND *;

    SELECT * FROM EMP   WHERE SAL BETWEEN 1500 AND 2000;

 5.IN   //包含  SELECT * FROM * WHERE * IN (*,*,*,*)

    SELECT * FROM EMP WHERE SAL IN(100,200,1500,3000,740,550);

6.LIKE  模糊查询  //SELECT * FROM * WHERE * LIKE "% * %";

    SELECT * FROM EMP WHERE ENAME LIKE "%TOM%";

7.虚表  DUAL

    查询当前登录用户

    SELECT USER FROM  DUAL;

    查询表的约束：

     SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME="EMP";

    查询表的结构：

      SELECT * FROM USER_TAB_COLS WHERE TABLE_NAME="EMP";

    查询当前用户下的表：

      SELECT * FROM USER_TABLES;