13-高级DQL-分页查询

分页查询

• 不同的数据库对于分页查询的SQL语句是不同的，没有一个标准

• 它指的是：当查询表中的数据时分段查询出来，而不是一次性将所有数据查询出来

  • 有时查询的数据量非常庞大，这会导致系统资源消耗大，响应速度长，数据冗余严重

  • 为此当遇到这种情况时一般使用分页查询解决

• Oracle中分页查询是基于伪列（ROWNUM）实现的

• Oracle提供了一个特殊的列：伪列（ROWNUM），它不存在与任何一张表上，但是任何表都可查询该字段的值

  • 该字段的值是随着查询自动生成的，方式是：每查询出一条记录就会生成一个行号即它的值，从1开始，逐次递增

  • 在使用ROWNUM对结果集进行编号的查询过程中不能使用“ROWNUM做>1”以上的数字判断，否则将查不出任何数据

计算区间公式

• pageSize：每页显示的条目数

• page：页数

• star：(page-1) * pageSize + 1

• end：pageSzie * page

演示：分页查询

• 显示emp表中6到10行数据

  • 错误查询1
    SELECT ROWNUM,ename,job,sal FROM emp WHERE ROWNUM BETWEEN 6 AND 10;

  • 错误查询2，因为数据库对e.ROWNUM不能认为是e表的某个字段
    SELECT ename,job,sal FROM(SELECT ROWNUM ,ename,job,sal FROM emp) e
    WHERE e.ROWNUM BETWEEN 6 AND 10;

  • 正确查询，需要采用别名
    SELECT ename,job,sal FROM(SELECT ROWNUM rn,ename,job,sal FROM emp) e
    WHERE e.rn BETWEEN 6 AND 10;

• 查看工资排名的6-10人

  • 错误结果，因为伪列的值先生成了，然后ORDER BY在排序了，伪列的值被打乱
    SELECT ename,sal FROM(SELECT ROWNUM rn,ename,sal FROM emp ORDER BY sal DESC) s
    WHERE s.rn BETWEEN 6 AND 10;

  • 正确结果
    SELECT ename,sal FROM(
    SELECT ROWNUM rn,t.* FROM(
    SELECT ename,sal FROM emp ORDER BY sal DESC
    ) t
    ) s
    WHERE s.rn BETWEEN 6 AND 10;

  • 提高上述查询的效率
    SELECT * FROM(
    SELECT ROWNUM rn,t.* FROM(
    SELECT ename,sal FROM emp ORDER BY sal DESC
    ) t
    WHERE ROWNUM <=10)
    WHERE rn>=6;