Oracle基础知识(五)——ROWID & ROWNUM

目录

一、ROWID  伪列

二、ROWNUM——限制查询结果集行数

1.ROWNUM使用介绍

2.使用ROWNUM进行分页查询

3.使用ROWNUM查看薪资前五位的员工

4.查询指定条数直接的数据

三、ROWNUM与ROWID不同

一、ROWID  伪列

1. 表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回的就是该行的物理地址。
2. 使用ROWID可以快速的定位表中的某一行，ROWID值可以唯一的标识表中的一行。
3. 用于物理定位和高效访问特定行，适合删除重复、数据验证等场景。

示例：造重复数据，将查询出来的数据往 EMP_1 表中插入

DROP TABLE emp_1;

CREATE TABLE emp_1 AS SELECT * FROM emp;

-- insert两次
INSERT INTO emp_1 SELECT * FROM emp;
COMMIT;

SELECT e.*, ROWID FROM emp_1 e order by EMPNO;

可以看到每条数据的物理地址都不同： 

        找到重复数据里的某一个ROWID，并且将这个ROWID对应的那条数据保留下来（保留一条：即删除所有多余的）

-- 使用min/max都可以
DELETE FROM emp_1 WHERE ROWID NOT IN
(SELECT MIN(ROWID) FROM emp_1 GROUP BY EMPNO);

---验证一下重复数据是否被删除
select * from emp_1;

二、ROWNUM——限制查询结果集行数

1.ROWNUM使用介绍

1. 在查询的结果集中，ROWNUM为结果集中每一行标识一个行号
2. 通过ROWNUM伪列可以限制查询结果集中返回的行数，常用于分页
3. ROWNUM 只能小于等于某个数据或者等于1 ，大于某个数据就没有数据
4. 用于限制查询结果集行数，尤其在分页查询中常用，但需注意语法陷阱
5. 只支持<=、= 和=1
6. 尽量不要将字段和ROWNUM放在一起使用
7. 使用ROWNUM进行分页查询：先排序，再使用ROWNUM，最后再between and分页

select emp.*, rownum from emp where rownum <= 5; -- 成立
select emp.*, rownum from emp where rownum < 6; -- 成立
select emp.*, rownum from emp where rownum = 1; -- 成立

select emp.*, rownum from emp where rownum >= 5; -- 不成立
select emp.*, rownum from emp where rownum > 6; -- 不成立

2.使用ROWNUM进行分页查询

-- 注意rownum起别名 

select *
from (select emp.*, rownum rn
      from emp) t
where t.rn between 6 and 10;

分页查询公式：between  (PageNum-1)*PageSize+1   and  PageNum*PageSize;

3.使用ROWNUM查看薪资前五位的员工

注意：先对薪资进行排序，再使用ROWNUM排序，最后再取前五位

SELECT a.*, ROWNUM
FROM (SELECT emp.*
      FROM emp
      ORDER BY sal DESC) a
WHERE ROWNUM <= 5;

错误演示：

SELECT emp.*, ROWNUM FROM emp
--WHERE ROWNUM<=5
ORDER BY sal DESC;

SELECT emp.*, ROWNUM rn FROM emp ORDER BY sal DESC;

上面的语句会出现乱序：

ROWNUM 返回的是行号

常用于分页，不能用大于 或者大于等

4.查询指定条数直接的数据

使用ROWNUM进行分页查询：先排序，再使用ROWNUM，最后再between and分页

with t1 as (select *
            from EMP
            order by SAL desc)
   , t2 as (select t1.*, ROWNUM rn
            from t1)
select *
from t2
where rn between 6 and 10;

三、ROWNUM与ROWID不同

1.  ROWNUM与ROWID不同，ROWID是插入记录时生成
2.  ROWNUM是查询数据时生成。ROWID标识的是行的物理地址。
3.  ROWNUM标识的是 SELECT 查询结果中的行的次序。