一、前言
在 Oracle 的表使用过程中,实际上表中还有一些附加的列,称为伪列。伪列就像表中的列一样可以显示出来,但是并不在表中存储。伪列只能用来查询,不能进行增删改操作。
二、ROWID
定义:表中的每一行在数据文件中都有一个物理地址,ROWID 伪列返回的就是该行的物理地址。
作用:通过 ROWID 伪列可以删除重复数据,相同的数据只保留一条。
示例:
--ROWID:物理地址(只有插入了数据才会有伪列:ROWID)
--作用:消除重复数据的,相同的数据只保留一行
SELECT E.EMPNO,
E.ENAME,
E.JOB,
E.MGR,
E.HIREDATE,
E.SAL,
E.COMM,
E.DEPTNO,
ROWID
FROM EMP E;
CREATE TABLE ROWID_TEST(
ID NUMBER,
NAME VARCHAR2(10)
);
INSERT INTO ROWID_TEST(ID, NAME) VALUES(1,'aaa');
COMMIT;
INSERT INTO ROWID_TEST(ID, NAME) VALUES(2,'bbb');
COMMIT;
SELECT ID,NAME,ROWID
FROM ROWID_TEST;
--使用ROWID实现删除表中的重复数据
--查询ROWID最大的那条数据
SELECT T.ID,T.NAME,MAX(ROWID)
FROM ROWID_TEST T
GROUP BY T.ID,T.NAME;
--删除ROWID不等于最大值的所有数据
DELETE FROM ROWID_TEST
WHERE ROWID NOT IN(
SELECT MAX(ROWID)
FROM ROWID_TEST T
GROUP BY T.ID,T.NAME
);
三、ROWNUM
定义:在查询的结果集中,ROWNUM为结果集中每一行标识一个行号,第一行返回1,第二行返回2,以此类推。
作用:通过 ROWNUM 伪列可以限制查询结果集中返回的行数。
注意点:筛选条件中如果使用到ROWNUM,那么只能用 < 或者 <=来查询,其它的关系运算符返回的都是0行结果。
示例:
--查询EMP表中所有人的ROWID、ROWNUM
SELECT E.EMPNO, E.ENAME, ROWID RI, ROWNUM RN FROM EMP E
--查询EMP表中所有人的ROWID、ROWNUM,只取前5条数据
SELECT E.EMPNO, E.ENAME, ROWID RI, ROWNUM RN
FROM EMP E
WHERE ROWNUM <= 5
--查询EMP表中所有人的ROWID、ROWNUM,只取第5条的数据
SELECT *
FROM (SELECT E.EMPNO, E.ENAME, ROWID RI, ROWNUM RN
FROM EMP E
WHERE ROWNUM <= 5) T
WHERE RN = 5;
--ROWNUM不能直接取排序后的前N行,需要先进行排序,将排序后的结果作子表,进行取前N行
SELECT T.*, ROWID, ROWNUM
FROM (SELECT E.EMPNO, E.SAL FROM EMP E ORDER BY E.SAL) T
WHERE ROWNUM < 3;
四、总结
ROWID与ROWNUM的不同:
1、ROWID是物理地址,ROWNUM是行号;
2、ROWID是插入数据时生成的,而ROWNUM是查询数据时生成的!