点击“蓝字”关注我们
晟数学院DBA成长日记--SQL篇
伪列
rowid伪列
rowid：
在数据表中每一行所保存的记录，实际上Oracle都会默认为每条记录分配一个唯一的地址编号，而这个地址编号就是通过rowid进行表示的。
rowid的组成：
数据对象号（data object number）
相对文件号（relative file number）
数据块号码（block number）
数据行号（row number）
（1）观察ROWID的存在
SCOTT@SDEDU> select rowid，empno from emp;
ROWID EMPNO
------------------------------------ -------------
AAAVbeAAEAAAACXAAA 7369
AAAVbeAAEAAAACXAAB 7499
AAAVbeAAEAAAACXAAC 7521
AAAVbeAAEAAAACXAAD 7566
AAAVbeAAEAAAACXAAE 7654
AAAVbeAAEAAAACXAAF 7698
AAAVbeAAEAAAACXAAG 7782
AAAVbeAAEAAAACXAAH 7788
AAAVbeAAEAAAACXAAI 7839
AAAVbeAAEAAAACXAAJ 7844
AAAVbeAAEAAAACXAAK 7876
AAAVbeAAEAAAACXAAL 7900
AAAVbeAAEAAAACXAAM 7902
AAAVbeAAEAAAACXAAN 7934
14 rows selected.
（2）拆分ROWID，取数据
SCOTT@SDEDU> select rowid,
DBMS_ROWID.rowid_object(ROWID)object_id,
DBMS_ROWID.rowid_relative_fno(ROWID)file_id,
DBMS_ROWID.rowid_block_number(ROWID)block_id,
DBMS_ROWID.rowid_row_number(ROWID) row_id from emp;
（3）将dept表中的数据复制到mydept表中
SCOTT@SDEDU> CREATE TABLE mydept AS SELECT * FROM dept ;
Table created.
验证：
SCOTT@SDEDU> select * from mydept;
DEPTNO DNAME LOC
-------------- ------------------------ ---------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
使用ROWID可以定位一个数据库中任何数据行，接下来通过一个实验加深理解。
准备工作：
向mydept表中增加一些数据
SCOTT@SDEDU>
INSERT INTO mydept(deptno,dname,loc)
VALUES (10,'ACCOUNTING','NEW YORK') ;
INSERT INTO mydept(deptno,dname,loc)
VALUES (10,'ACCOUNTING','NEW YORK') ;
INSERT INTO mydept(deptno,dname,loc)
VALUES (20,'RESEARCH','DALLAS') ;
INSERT INTO mydept(deptno,dname,loc)
VALUES (20,'RESEARCH','DALLAS') ;
INSERT INTO mydept(deptno,dname,loc)
VALUES (20,'RESEARCH','DALLAS') ;
COMMIT ;
验证
SCOTT@SDEDU> select * from mydept;
DEPTNO DNAME LOC
------------- ----------------------- --------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
20 RESEARCH DALLAS
20 RESEARCH DALLAS
9 rows selected.
需求：
现在在mydept表中由于操作失误，所以导致mydept表中有许多的重复数据。要求将所有重复数据删除到只剩一条， 即现在表中有3个10部门的信息，要求只保留1个10部门的信息。
解题思路：
步骤一：对mydept表分组，统计出唯一的ROWID数据
SCOTT@SDEDU> select rowid,deptno from mydept;
ROWID DEPTNO
------------------------------------ ----------------
AAAVcDAAEAAAACzAAA 10
AAAVcDAAEAAAACzAAB 20
AAAVcDAAEAAAACzAAC 30
AAAVcDAAEAAAACzAAD 40
AAAVcDAAEAAAAC3AAA 10
AAAVcDAAEAAAAC3AAB 10
AAAVcDAAEAAAAC3AAC 20
AAAVcDAAEAAAAC3AAD 20
AAAVcDAAEAAAAC3AAE 20
9 rows selected.
步骤二：先统计出哪几个部门的信息是最早保留的，此时可以通过对dept数据分组，而后使用MIN( )函数查询rowid。聚合函数，先将rowid划分为一组，然后再对该组进行排序找出最小值。
SCOTT@SDEDU> select deptno,dname,loc,min(rowid)
from mydept group by deptno,dname,loc;
DEPTNO DNAME LOC MIN(ROWID)
-------- ------------------ ------------ --------------------------------------
20 RESEARCH DALLAS AAAVcDAAEAAAACzAAB
40 OPERATIONS BOSTON AAAVcDAAEAAAACzAAD
10 ACCOUNTING NEW YORK AAAVcDAAEAAAACzAAA
30 SALES CHICAGO AAAVcDAAEAAAACzAAC
说明：根据rowid可以找出最早插入的数据，如果是同一组，用这个方法就能找出相同deptno中最早插入的数据
SCOTT@SDEDU> select rowid,deptno,dname,loc
from mydept order by rowid;
ROWID DEPTNO DNAME LOC
------------------------------------ ------ ------------------- -----------------
AAAVcDAAEAAAACzAAA 10 ACCOUNTING NEW YORK
AAAVcDAAEAAAACzAAB 20 RESEARCH DALLAS
AAAVcDAAEAAAACzAAC 30 SALES CHICAGO
AAAVcDAAEAAAACzAAD 40 OPERATIONS BOSTON
AAAVcDAAEAAAAC1AAA 10 ACCOUNTING NEW YORK
AAAVcDAAEAAAAC1AAB 10 ACCOUNTING NEW YORK
AAAVcDAAEAAAAC1AAC 20 RESEARCH DALLAS
AAAVcDAAEAAAAC1AAD 20 RESEARCH DALLAS
AAAVcDAAEAAAAC1AAE 20 RESEARCH DALLAS
9 rows selected.
步骤三：编写删除语句删除重复数据
SCOTT@SDEDU> delete from mydept where rowid
not in (select min(rowid) from mydept group by deptno);
5 rows deleted.
验证
SCOTT@SDEDU> select * from mydept;
DEPTNO DNAME LOC
------------ ----------------------- -------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
rownum伪列
ROWNUM表示的是一个数据行编号的伪列，他的内容是在用户查询数据的时候，为用户动态分配的一个数字即行号
（4）查询雇员编号、姓名、职位、基本工资、雇佣日期等信息并且显示每条记录的行号
说明：ROWNUM这个数据伪列在查询的时候是直接编写的，他会根据每一行的查询结果进行自动的流水编号， 这些ROWNUM是随机生成的，不是固定的。
需求：
列出薪金高于公司平均薪金的所有员工编号、姓名、基本工资、 职位、雇佣日期，所在部门名称、位置，公司的工资等级，但是为了信息浏览方便，要求在每一行数据显示前都增加一个行号。
解题思路：
步骤一：先找出高于平均工资的信息
步骤二：在步骤一的基础上，连接两张表
步骤三：加上工资等级
步骤四：加行号
SCOTT@SDEDU> select rownum,e.empno,e.ename,e.sal,e.job,
e.hiredate,d.dname,d.loc,s.grade
2 from emp e,dept d,salgrade s
3 where e.sal>(select avg(sal) from emp)
4 and e.deptno=d.deptno
5 and e.sal between s.losal and s.hisal;
推荐阅读
晟数学院DBA成长日记
晟数学院DBA成长日记
晟数学院DBA成长日记
