08oracle之伪列（ruwnum、ruwid）

伪列：

• 伪列是在oracle中的一个虚拟的列。
• 列的数据是由ORACLE进行维护和管理的，用户不能对这个列修改，只能查看。
• 所有的伪列要得到值必须要显式的指定。

最常用的两个伪列：rownum和rowid。

• ROWNUM:
• rownum（行号）：是在查询操作时由ORACLE为每一行记录自动生成的一个编号。
• 每一次查询ROWNUM都会重新生成。（查询的结果中Oracle给你增加的一个编号，根据结果来重新生成）
• rownum永远按照默认的顺序生成。（不受order by的影响）
• rownum只能使用< <=，不能使用> >=符号，原因是：Oracle是基于行的数据库，行号永远是从1开始，即必须有第一行，才有第二行。

行号的产生：

1. ROWNUM是由数据库自己产生的。
2. ROWNUM查询的时候自动产生的。

行号的排序：

示例：

--需求：查询出所有员工信息，按部门号正序排列，并且显示默认的行号列信息。
SELECT ROWNUM,t.* FROM emp t ORDER BY deptno;--order by 的原理：将查询结果（
此时行号已经有了，已经和每一行数据绑定了）进行排序。
- --order by是查询语句出来的结果之后再排序的，rownum是在查询出来结果的时候产生。order by
不会影响到行号
--先排序，再查询
SELECT ROWNUM,t.* FROM
(
SELECT * FROM emp ORDER BY deptno
) t

结论：

• order by排序，不会影响到rownum的顺序。rownum永远按照默认的顺序生成。
• 所谓的“默认的顺序”，是指系统按照记录插入时的顺序（其实是rowid）。

利用行号进行数据分页：

mysql如何分页？

select * from table limit m,n
其中m是指记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条。
select * from tablename limit 3,3
即取出第4条至第6条，3条记录

结论：Mysql使用limit的关键字可以实现分页，但Oracle没有该关键字，无法使用该方法进行分页。

示例：

--需求：根据行号查询出第四条到第六条的员工信息。
SELECT ROWNUM,t.* FROM emp t;
SELECT ROWNUM,t.* FROM emp t WHERE ROWNUM >=4 AND ROWNUM<=6;

--rownum只能使用< <=，不能使用> >=符号，
SELECT ROWNUM,t.* FROM emp t WHERE ROWNUM<=6;

--方案：可以使用子查询
SELECT rownum,t2.* FROM
(
SELECT ROWNUM r,t.* FROM emp t WHERE ROWNUM<=6--此时子查询的rownum已经变成了
虚表的一个列
) t2--尽量让虚表尽量小
WHERE t2.r >=4

--优化
SELECT * FROM
(
SELECT ROWNUM r,t.* FROM emp t WHERE ROWNUM <=6
)  WHERE r >=4;

--结果指定字段
SELECT empno,ename,job FROM(
SELECT ROWNUM r,t.* FROM emp t WHERE ROWNUM <=6
)  WHERE r >=4;

--按照薪资的高低排序再分页
SELECT * FROM
(
SELECT ROWNUM r,t.* FROM emp t WHERE ROWNUM <=6 ORDER BY sal DESC
)  WHERE r >=4 ;

分析原因：
rownum只能使用< <=，不能使用> >=符号，原因是：Oracle是基于行的数据库，行号永远是从1开始，即必须有第一行，才有第二行。

• ROWID：

ROWID（记录编号）：是表的伪列，是用来唯一标识表中的一条记录，并且间接给出了表行的物理位置，定位表行最快的方式。

• 主键:标识唯一的一条业务数据的标识。主键是业务给用户用的。不是给数据库用的。
• 记录编号rowid：标识唯一的一条数据的。主要是给数据库用的。类似UUID。

ROWID的产生：

• 使用insert语句插入数据时，oracle会自动生成rowid并将其值与表数据一起存放到表行中。
• 这与rownum有很大不同，rownum不是表中原本的数据，只是在查询的时候才生成的。

提示：rownum默认的排序就是根据rowid

ROWID的作用：
这里列举两个常见的应用：

• 去除重复数据。–面试题—了解
• 在plsql Developer中，加上rowid可以更改数据。

关于主键和rowid的区别：

• 相同点：
• 为了标识唯一一条记录的。
• 不同点：
• 主键：针对业务数据，用来标识不同的一条业务数据。
• rowid：针对具体数据的，用来标识不同的唯一的一条数据，跟业务无关。

示例：

需求：删除表中的重复数据，要求保留重复记录中最早插入的那条。（DBA面试题）
--查看rowid
SELECT t.*,ROWID FROM emp t;
--需求：删除表中的重复数据，要求保留重复记录中最早插入的那条。（面试题）
--准备测试表和测试数据：
--参考建表语句如下：
-- Create table
create table test
(
  id   number,
  name varchar2(50)
)  ;
--插入测试数据
INSERT INTO TEST VALUES(1,'xiaoming');
INSERT INTO TEST VALUES(2,'xiaoming');
INSERT INTO TEST VALUES(3,'xiaoming');
COMMIT;
SELECT * FROM TEST ;
--通过rowid，剔除重复xiaoming，保留最早插入的xiaoming
SELECT t.*,ROWID FROM TEST t;
--删除的的时候，可以先查询你要删除的东东
SELECT  t.*,ROWID FROM TEST t WHERE ROWID > (SELECT MIN(ROWID) FROM TEST);

DELETE FROM TEST t WHERE ROWID > (SELECT MIN(ROWID) FROM TEST);

--语句有缺点：条件不足，会只保留一条数据，误删其他数据
--重新插入测试数据
INSERT INTO TEST VALUES(1,'xiaoming');
INSERT INTO TEST VALUES(2,'xiaoming');
INSERT INTO TEST VALUES(3,'xiaoming');
INSERT INTO TEST VALUES(4,'Rose');
INSERT INTO TEST VALUES(5,'Rose');
COMMIT;
--剔除重复数据
SELECT * FROM TEST WHERE ROWID NOT in(SELECT MIN(ROWID) FROM TEST GROUP BY NAME);
DELETE FROM TEST WHERE ROWID NOT in(SELECT MIN(ROWID) FROM TEST GROUP BY NAME);

注意:删除重复记录一定要小心,万一你的条件有问题,就会删错数据.建议删除之前，可以先用查询查一下，看是否是目标数据。
数据一旦删除恢复比较麻烦，但可以恢复，采用日志回滚。一般不要轻易用。