oracle rownum使用与分页

oracle rownum使用与分页

 rownum使用与分页

    rownum说明：
        rownum是个伪列，是随着结果集生成的，返回的第一行分配的是1，第二行是2等等，生成的结果是依次递加的，没有1就不会有2。
            注意，不返回的就不算，第一条返回的结果的rownum为1。
        rownum一旦生成，就不会变化了，所以要注意，指定排序时rownum就是打乱的了。
    rownum的比较操作
        我们说rownum不支持>, >=, =, between and，只支持<, <=等。
        虽说不支持，但并不会报错，只是返回的数据为空，这是因为根本不能满足这样的where条件。
        如where rownum>2：
            取回第1条数据的rownum为1，不满足，就舍弃这条记录。
            看下一条，然后取第2条数据的rownum还是为1，还是不满足，再舍弃。
            以此类推，最终舍弃了所有的数据，这就是所谓不支持的原因。
        于是我可以推出：
            where rownum=1 是只有一条结果的（要求至少有一条结果）。

SQL> select * from emp where rownum=1;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20

SQL>
           
            where rownum>=1 是返回所有结果的（要求至少有一条结果）。
SQL> select * from emp where rownum>=1;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

已选择14行。

            where rownum<9 是有8条结果的（要求至少有8条结果）。

SQL> select * from emp where rownum<9;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20

已选择8行。

SQL>
            where rownum!=9 是有8条结果的（要求至少有8条结果）。

SQL> select * from emp where rownum!=9;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20

已选择8行。

SQL>


            where rownum between 6 and 10 是没有结果的。

SQL> select * from emp  where rownum between 6 and 10;

未选定行

SQL>


            where rownum between 1 and 5 是有前5条结果（要求至少有5条结果）

SQL> select * from emp  where rownum between 1 and 5;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30

SQL>



    实现rownum>n (n>0) 的效果：
            rownum本身是不行的，所以就可以先查询出一个结果集合。
            再从这个集合中查询，这时集合中的行号就是一个普通的字段了，可以做任何比较。
            即使用子查询方法来解决。


    注意问题：
        1，查询时rownum可以直接使用，不要求一定要在select中出现，如：select id,name from user where rownum<=5;
        2，在本查询中，where子句中要直接写rownum，前面不能加表别名，也不能使用使用select子句中为rownum定义的别名。
            在order by子句中是可以使用为rownum定义的别名的。
        3，这样的操作在大数据集中会影响速度。
    分页：
        （内层排序外层选，需要三层查询）
         内：排序。
        中：使用rownum选择前n条；并给rownum指定一个别名，以供最外层过滤使用。
        外：去掉前m条结果。
例:按员工表的工资由高到低排列，只查询出第6条到10条的结果,包含第6条与第10条结果（分页）
思路:
（内层排序外层选，需要三层查询）
1、内:先排序:select * from emp order by sal desc
SQL> select * from emp order by sal desc;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7369 SMITH      CLERK           7902 17-12月-80            800                    20

已选择14行。

SQL>

2、中:使用rownum选择前n条,并给rownum指定一个别名,以供最外层过滤使用
select rownum r,e.*
    from (select * from emp order by sal desc)  e
    where rownum<=10
SQL> select rownum r,e.*
  2     from (select * from emp order by sal desc)  e
  3     where rownum<=10
  4  ;

         R      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
         1       7839 KING       PRESIDENT            17-11月-81           5000                    10
         2       7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
         3       7902 FORD       ANALYST         7566 03-12月-81           3000                    20
         4       7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
         5       7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
         6       7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
         7       7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
         8       7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
         9       7934 MILLER     CLERK           7782 23-1月 -82           1300                    10
        10       7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30

已选择10行。

SQL>

3、外:去掉前m条结果
select * from ... where r>5
select *
from
    (select rownum r,e.*
    from (select * from emp order by sal desc)  e
    where rownum<=10
     )
where r>5
SQL> select *
  2  from
  3      (select rownum r,e.*
  4     from (select * from emp order by sal desc)  e
  5     where rownum<=10
  6       )
  7  where r>5
  8  ;

         R      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
         6       7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
         7       7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
         8       7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
         9       7934 MILLER     CLERK           7782 23-1月 -82           1300                    10
        10       7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30

SQL>
rownum分页总结:
在java中定义一个pageBean分页的javaBean
public class PageBean {

    private int page; // 页数
    private int rows; // 每页显示的行数
    private int start;  // 起始页
    //构造函数，初始化赋值
    public PageBean(int page, int rows) {
        super();
        this.page = page;
        this.rows = rows;
    }
   
    public int getPage() {
        return page;
    }
    public void setPage(int page) {
        this.page = page;
    }
    public int getRows() {
        return rows;
    }
    public void setRows(int rows) {
        this.rows = rows;
    }
    public int getStart() {
        return (page-1)*rows;
    }   
}

算法:
select *
from
    (select rownum r,e.*
    from (select * from emp order by sal desc)  e
    where rownum<=10----rownum<(start+row)
     )
where r>6----r>start
假设rows=5(每页显示的为5)
第一页:
page=1时
start+row=5
start=page-1=1-1=0
SQL> edit
已写入 file afiedt.buf

  1  select *
  2  from
  3      (select rownum r,e.*
  4     from (select * from emp order by sal desc)  e
  5     where rownum<=5
  6       )
  7* where r>0
SQL> /

         R      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
         1       7839 KING       PRESIDENT            17-11月-81           5000                    10
         2       7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
         3       7902 FORD       ANALYST         7566 03-12月-81           3000                    20
         4       7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
         5       7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30

SQL>
第二页:
page=2
start+row=10   
start=5

SQL> edit
已写入 file afiedt.buf

  1  select *
  2  from
  3      (select rownum r,e.*
  4     from (select * from emp order by sal desc)  e
  5     where rownum<=10
  6       )
  7* where r>5
SQL> /

         R      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
         6       7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
         7       7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
         8       7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
         9       7934 MILLER     CLERK           7782 23-1月 -82           1300                    10
        10       7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30

SQL>
第三页:
page=3
start+row=15
start=10

SQL> ed
已写入 file afiedt.buf

  1  select *
  2  from
  3      (select rownum r,e.*
  4     from (select * from emp order by sal desc)  e
  5     where rownum<=15
  6       )
  7* where r>10
SQL> /

         R      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
        11       7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
        12       7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
        13       7900 JAMES      CLERK           7698 03-12月-81            950                    30
        14       7369 SMITH      CLERK           7902 17-12月-80            800                    20

oracle第一天基础:
sqlplus
其他的图形化工具


=====================

使用的用户：
    用户名：scott
    密码  ：tiger

登录：
    sqlplus scott/tiger

退出：
    exit

查看当前用户有哪些表：
    select * from tab;

=====================

显示当前用户
    SQL> show user
    USER 为 "SYS"

切换用户
    SQL> connect scott/tiger
    已连接。
   
命令可以使用缩写
    conn[ect] user

执行上一条sql语句
    /

设置行宽
    set linesize 150    默认为80

设置页面大小（一页显示的数据）
    set pagesize 100    默认为14

显示参数的值
    show 参数名
    如:
        show linesize

永久保存配置
    修改 glogin.sql 文件。

注释：
    单行注释：--
    多行注释： /*    */
   

查看表结构
    SQL> desc dept

====================================

SCOTT用户的表：

dept表：
    deptno    编号
    dname
    loc        位置

emp表：
    empno    员工编号
    ename
    job
    mgr        上级主管的员工编号
    hiredate 入职时间
    sal        薪水
    comm    奖金
    deptno    所属的部门编号 --> 引用dept.deptno

====================================

清屏：
    Windows中：host cls
    Linux中  ：host clear

设置某列的宽度：
    column job format a15    或    col job for a15
    column SAL format 9999    或    col sal    for 9999
    格式：
        col[umn] 列名 for[mat] 格式
        对于字符串：
            a20，20是一个数据，表示20个字符的宽度。
        对于数字
            9表示一位，有几个，就是多宽。

====================================

空值的处理
    不是一个有效的值。不是0，也不是空字符串。
    null != null
        is null
        is not null
    含有null的表达式结为null

修改上一条SQL语句
    ed[it]
    注意：后面不要写分号

滤空函数
    nvl(表达式, 当表达式为空时使用的值)

    select empno, ename, sal, sal * 12 as 年薪, nvl(comm, 0), (sal * 12 + nvl(comm,0) ) 总收入
    from emp


====================================

字符串
    是匹分大小写的，在使用时要加引号。
    在指定别名时，引号可以加，也可以不加。
    当含有空格、特殊字符时，一定要加引号。
    不加引号时，显示都为大写，加上引号后，就是按所写的显示了。

    在使用字符串时，要使用单引号。
    在写别名时，要使用双引号。

去掉重复的行   
    -- 作用于一列
    SQL> select distinct job from emp;
    -- 作用于多个列，所有列的值加一起重复才算重复的记录
    SQL> select distinct job, deptno from emp;

如果只查询一个表达式，没有用到任何表的数据，这时也必须得写from...
    可以写成 from dual
    dual是一个虚表，本身就存在的，可以直接使用。
    如：
        select 3+2
        select 'Hello' || 'World' from dual;

字符串连接符
    SQL> select ename || '的薪水是' || sal from emp;

    ENAME||'的薪水是'||SAL
    ----------------------------------------------------------
    SMITH的薪水是800
    ALLEN的薪水是1600
    WARD的薪水是1250
    JONES的薪水是2975
    MARTIN的薪水是1250
    BLAKE的薪水是2850
    CLARK的薪水是2450
    SCOTT的薪水是3000
    KING的薪水是5000
    TURNER的薪水是1500
    ADAMS的薪水是1100
    JAMES的薪水是950
    FORD的薪水是3000
    MILLER的薪水是1300

也可以使用函数 concat()：
    SQL> select concat('Hello', 'World') from dual;
   


where  (a or b) and c



====================================

LIKE
    在使用like时，可以使用 % 与 _，分别表示任意数量的任意字符或任意一个字符。
    要想表达%或_本身，需要使用转义符，例：
        SQL> select * from emp where ename like 'KI\%%' escape '\';

BETWEEN
    包含两个边界。
    一定是小值写到前面，大值写到后面，否则没有结果。

IN
    where .. in (.., .., .., ...) 如果含有null，没有影响。
        例：查询所有是经理的员工
        SQL> select * from emp where empno in (select mgr from emp);
    where .. not in (.., .., ...) 如果含有null，则不返回任何结果。
        例：查询所有不是经理的员工
        SQL> select * from emp where empno not in (select mgr from emp where mgr is not null);

=========================================

MySQL中日期类型：date, time, datetime
Oracle中只有Date.

    SimpleDateFormat
        yyyy-MM-dd


对日期的处理
    1，查询指定日期后入职的员工信息
        SQL> select * from emp where hiredate>'31-12月-81';
    2，修改日期的格式
        SQL> select * from v$nls_parameters;
        SQL> alter session set nls_date_format='yyyy-mm-dd'; // 只对当前session有效
    3，使用日期函数的方式：
        SQL> select * from emp where hiredate>to_date('1981-12-31', 'yyyy-MM-dd');
        或
        SQL> select * from emp where to_char(hiredate, 'yyyy-MM-dd')  > '1981-12-31';

    语法：TO_CHAR(date, 'format_model')
    语法：TO_DATE(str, 'format_model')


格式字符串不区分大小写：
获取当前时间：
    SQL> select to_char(sysdate,'YYYY-MM-DD') from dual;
    TO_CHAR(SY
    ----------
    2012-03-07

    SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;
    TO_CHAR(SY
    ----------
    2012-03-07

    SQL> select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
    TO_CHAR(SYSDATE,'YY
    -------------------
    2012-03-07 02:33:41
   


=========================================

排序：
    order by 列名, ...
    可以作用在：数字、日期、字符串。
    可以使用列名，表达式，别名，序号（表示select中的第几个列）

升序、降列

按一个列排列，按多个列排序

当order by所在的列中有null，会：
    升序时，null的在下面。
    降序时，null的在上面。
我们希望，不管升序还是降序，null值的始终在下面
    方式一：SQL> select * from emp order by comm desc nulls last;
    方式二：
        select empno, ename, job, hiredate, sal, nvl(comm, 0)
        from emp
        order by 6 desc


=====================================================

1，组函数
    select
        max(sal) 最高工资,
        min(sal) 最低工资,
        avg(sal) 平均工资,
        sum(sal) 所有员工的工资和,
        count(sal) 领工资的员工数量
    from emp;
       
2，组函数对null的处理
    例，查询所有员工的平均奖金（有人的奖金为null）
        select sum(comm)/count(*) 平均奖金 from emp
    组函数会自动过滤掉null值。
        在使用avg()时要注意处理null值：
        select avg( nvl(comm, 0) ) from emp;
    函数可以嵌套使用

3，分组
    Group by，写在FROM后，如果有WHERE，就在WHERE后面。
    查询的列一定要是：
        在group by中出现的列（在Select中不一定全写上）
        或是使用组函数
    按一个列分组
    按多个列分组
        参与分组的多个列有一个不相同就是不同的组。
   
4，分组结果过滤
    Having，是分完组后再进行过滤，只显示符合条件的结果。
    在Group by与Having中都不可以使用别名。
    与Where的区别
        Having是是分完组后再进行过滤。
        Where是先过滤，再进行分组操作。
    如果可以，尽量写Where条件，不写Having。


Select
    ...
From
    ...
Where
    ...
Group by
    ...
Having
    ...
Order by
    ...

================================================
子查询：
    当一步不能求解时，可以使用子查询。
    分为：
        单行子查询
        多行子查询
   
    可以在主查询的select, from, where, having 都可以放子查询
        不可以在主查询的group by 放子查询
    单行操作符对应单行子查询，多行操作符对应多行子查询

    在select中放子查询时，要求只能是单行子查询。

IN：

ANY：
    小于某集合中的任意一个值，就是小于集合中的最大值。
    大于某集合中的任意一个值，就是大于最小值。

ALL：
    小于某集合中的所有值，就是小于最小值。
    大于某集合中的所有值，就是大于最大值。



练习题一：
    题目：找到薪水大于本部门平均薪水的员工。
    答案：
        select *
        from emp e1
        where sal > ( select avg(sal) from emp e2 where e2.deptno=e1.deptno )


练习题二：
    题目：找到员工表中工资最高的前三名的员工信息
    答案：
        select rownum, empno, ename, sal
        from (select empno, ename, sal from emp order by sal desc)
        where rownum<=3
       


rownum是一个伪列，表示记录在结果集中的行号。
    1，rownum一旦生成，就不会变化（会先按没有排序时的默认顺序生成rownum，然后再执行排序）。
    2，对于rownum，只能使用<与<=，不能使用>与>=与=。





SQL> select rownum,empno,ename,sal from emp;

    ROWNUM      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
         1       7369 SMITH             800
         2       7499 ALLEN            1600
         3       7521 WARD             1250
         4       7566 JONES            2975
         5       7654 MARTIN           1250
         6       7698 BLAKE            2850
         7       7782 CLARK            2450
         8       7788 SCOTT            3000
         9       7839 KING             5000
        10       7844 TURNER           1500
        11       7876 ADAMS            1100
        12       7900 JAMES             950
        13       7902 FORD             3000
        14       7934 MILLER           1300







select
    rownum, empno, ename, sal
from (
        select rownum r1, empno, ename, sal
        from emp
        where rownum<=10
    )
where rownum<=5



select rownum, empno, ename, sal
from
    ... r1 ...
where
    rownum<=10 and r1>5






select rownum, empno, ename, sal
from (select empno, ename, sal from emp order by sal desc)
where rownum<=3





SQL> ed
已写入 file afiedt.buf

  1  select
  2     empno,
  3     ename,
  4     sal my_sal,
  5     mgr,
  6     (select sal from emp where empno=e.mgr) AS mgr_sal
  7* from emp e
SQL> /

     EMPNO ENAME          MY_SAL        MGR    MGR_SAL
---------- ---------- ---------- ---------- ----------
      7369 SMITH             800       7902       3000
      7499 ALLEN            1600       7698       2850
      7521 WARD             1250       7698       2850
      7566 JONES            2975       7839       5000
      7654 MARTIN           1250       7698       2850
      7698 BLAKE            2850       7839       5000
      7782 CLARK            2450       7839       5000
      7788 SCOTT            3000       7566       2975
      7839 KING             5000
      7844 TURNER           1500       7698       2850
      7876 ADAMS            1100       7788       3000
      7900 JAMES             950       7698       2850
      7902 FORD             3000       7566       2975
      7934 MILLER           1300       7782       2450

已选择14行。