oracle中rowid和rownum的案例

 oracle中rowid和rownum的案例

我就直接po截图和代码

图1:

图2:

图3:

图4:

图5:

图6:

下面po代码

/*
Oracle学习笔记(rownum和rowid)

rownum和rowid都是伪列(准确来说,ROWID应该不算伪列,因为它实
际上是存在的),但是两者的根本是不同的,
rownum是根据sql查询出的结果给每行分配一个逻辑编号,rownum可以理解为行号
所以你的sql不同也就会导致最终rownum不同,
但是rowid是物理结构上的,在每条记录insert到数据库中时,
都会有一个唯一的物理记录
*/

/*
ROWNUM伪列
可以这么理解ROWNUM,
ROWNUM是伪列,该伪列在表中本身是不存在,只是在查
询的时候,是根据查询结果集而产生的一个"伪数列",
在Oracle中,我们分页的时候可以使用ROWNUM
*/


--使用ROWNUM伪列
--查到1条记录
SELECT ROWNUM, DEPARTMENTS.* FROM SCOTT.DEPARTMENTS WHERE ROWNUM = 1;
--查到0条记录
SELECT ROWNUM, DEPARTMENTS.* FROM SCOTT.DEPARTMENTS WHERE ROWNUM = 2;
--查到2条记录
SELECT ROWNUM, DEPARTMENTS.* FROM SCOTT.DEPARTMENTS WHERE ROWNUM < 3;
--查到0条记录
SELECT ROWNUM, DEPARTMENTS.* FROM SCOTT.DEPARTMENTS WHERE ROWNUM > 1;
--查到表中所有记录(查到28条记录)
SELECT ROWNUM, DEPARTMENTS.* FROM SCOTT.DEPARTMENTS WHERE ROWNUM > 0;
--查到28条记录
SELECT ROWNUM, DEPARTMENTS.* FROM SCOTT.DEPARTMENTS;
--
SELECT ROWNUM, DEPARTMENTS.* FROM SCOTT.DEPARTMENTS;

--查询前5条记录
select ROWNUM, employees.* from employees where 
salary > 12000 and ROWNUM <= 5;

select ROWNUM, employees.* from employees where 
salary > 12500 and ROWNUM <= 8;


/*
在Oracle数据库中,利用ROWNUM来分页

注意:分页关键字各种数据库不一样
Mysql数据库分页使用limit关键字
sqlServer数据库分页使用top关键字
*/
SELECT ROWNUM, emp.* from employees emp;
--取第5到第10条记录
SELECT myEmp.tempId, myEmp.* from (SELECT ROWNUM tempId, emp.* from employees emp) myEmp
WHERE myEmp.tempId >=5 and myEmp.tempId <10;
--取第5到第10条记录
SELECT emp.tempId, emp.* from (SELECT ROWNUM tempId, emp.* from employees emp) emp
WHERE emp.tempId BETWEEN 5 and 10; --BETWEEN AND 包含边界
--以上两种写法都可以,任选一种即可

/*
Oracle中的rownum不能使用大于>的问题
可以参考网页https://www.cnblogs.com/java0819/archive/2011/08/03/2146205.html
因为rownum总是从1开始的,第一条不满足去掉的话,第
二条的rownum 又成了1。依此类推,所以永远没有满足条件的记录。
可以这样理解:rownum是一个序列,是Oracle数据库从数据文件或缓冲区中读取数据的顺序。
它取得第一条记录则rownum值为1,第二条为2。依次类推。
当使用“>、>=、=、between...and”这些条件时,从缓冲区或数据文件中得到的第一条记
录的rownum为1,不符合sql语句的条件,会被删除,接着取下条。
下条的rownum还会是1,又被删除,依次类推,便没有了数据。
*/

/*
Oracle对rownum的处理:
rownum是在得到结果集的时候产生的,用于标记结果集中结果顺序的一个字段,
这个字段被称为“伪数列”,也就是事实上不存在的一个数列。
它的特点是按顺序标记,而且是逐次递加的,
换句话说就是只有有rownum=1的记录,才可能有rownum=2的记录
*/

/*
注意:  
对 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据,当
然啦,你如果=1的话,是可以返回数据的
(ROWNUM可以理解成一个伪列,如果把伪列变成一个真实的列就可以使用>、=、>=了)

*/

/*
ROWID知识点
ROWID的作用:由于ROWID用来唯一标识表中数据的唯一性,所以可以利用这个特性去除重复
很显然,如果表中的数据有重复的,但是ROWID肯定是不会重复的,那么就可以利用这个特性去重
*/
--
SELECT ROWID FROM dual;
--
SELECT ROWID, emp.* from employees emp;
/*
可以利用rowid来查询记录,而且通过rowid查询记录是查询速度最快的查询方法。
(这个我没有试过,另外要记住一个长度在18位,而且没有太明显规律的字
符串是一个很困难的事情,所以我个人认为利用rowid查询记录的实用性不是很大)
*/
SELECT ROWID, emp.* from employees emp WHERE ROWID = 'AAASNeAAEAAAAJbAAF';



/*
如下演示ROWID案例
ROWID可以用来去掉重复的数据(去重),当然啦,ROWID还
有其他的功能,可以自己去查阅oracle的相关api文档
*/

--创建一张测试表
CREATE TABLE test20190417(
	id number not null,
	name VARCHAR2(50) not null,
	age NUMBER not null
);
--插入数据(故意插几条重复的数据,方便后面我们做测试)
INSERT INTO test20190417(id, name, age) VALUES(1, '令狐冲', 16);
INSERT INTO test20190417(id, name, age) VALUES(2, '令狐冲', 16);
INSERT INTO test20190417(id, name, age) VALUES(3, '令狐冲', 16);
INSERT INTO test20190417(id, name, age) VALUES(4, '韦小宝', 15);
INSERT INTO test20190417(id, name, age) VALUES(5, '韦小宝', 15);
INSERT INTO test20190417(id, name, age) VALUES(6, '张无忌', 22);
INSERT INTO test20190417(id, name, age) VALUES(7, '杨过', 18);
INSERT INTO test20190417(id, name, age) VALUES(8, '令狐冲', 16);

/*
小知识点(小注意点):
ORACLE中,如果表中的字段名,正好跟sql中关键字重名,写sql语句时要注意:
1.要将该字段名大写
2.字段名两边要加双引号(注:必须是双引号,单引号将无效)

但是在mysql数据库中是使用`着重号(注意是数字键1旁边的反引号`)

如下sql语句
*/
--字段名大写并在两边加上双引号
SELECT "NAME", age , MAX(t.ID) from test20190417 t group by "NAME",age;
--如下,小写加双引号的写法会报错,必须是大写加双引号才可以
--SELECT "name", age , MAX(t.ID) from test20190417 t group by "name",age;

--如下写法也可以
SELECT name, age , MAX(t.ID) from test20190417 t group by name,age;
--如下写法也可以
SELECT NAME, age , MAX(t.ID) from test20190417 t group by NAME,age;


/*
需求:去掉test20190417表中重
复的数据(test20190417表中只要name和age这2个字
段的值一样就认为是重复的数据)即根据name和age这2个字段去重

删除重复数据分两种情况:
情况1:重复数据完全删除
情况2:删除重复数据,但是要保留一条
根据你自己的具体需求,选择两种情况中的一种

去掉重复数据,有两种方法
*/

--方法1

/*
步骤1:创建一张testTemp临时表,把test20190417原
表中的数据插入到testTemp临时表中(注意:原
表中根据name, age这两个字段去重后再插入数据到临时表中)
如下:
*/
--步骤1:
create table testTemp as 
SELECT a.* from test20190417 a where a.id in (
select max(b.id) --这里也可以换成min(b.id),效果是一样的
from test20190417 b
where a.name = b.name
and a.age = b.age);

--子查询返回多列
--select * from test20190417 where (name, age) in (SELECT distinct name, age from test20190417);

--查询一下testTemp临时表
select * from testTemp;
--查询一下原表
select * from test20190417;

--步骤2:清空原表记录
truncate table test20190417;

--再查询一下原表,发现原表中的数据已经都清空了
select * from test20190417;

--步骤3:将临时表中的数据插回原表中
insert into test20190417 select * from testTemp;

--此时再查询一下原表,发现去重的目的已经达到了
select * from test20190417;

/*
当然啦,此时我们可以把临时表给drop删除掉,反
正此时testTemp临时表已然没有任何利用价值了
*/
--步骤4:drop删除掉临时表
DROP TABLE testTemp;


/*
ROWID知识点:
ROWID应该不算伪列,因为它是实际存在的

*/

/*
如下摘自网络
oracle还提供了另外一个伪数列:rowid
rowid和rownum不同,一般说来每一行数据对应的rowid是固定而且唯一的

在这一行数据存入数据库的时候就确定了。

可以利用rowid来查询记录,而且通过rowid查询记录是查询速
度最快的查询方法。(这个我没有试 过,另外要记住一个长度在18位,而且没有太明显规
律的字符串是一个很困难的事情,所以我个人认为利用rowid查询记录的实用性不
是很大)

rowid只有在表发生移动(比如表空间变化,数据导入/导出以后),才会发生变化

可以把rowid理解成java中的对象的内存地址
*/


--方法2,我们可以使用ROWID来去重

--
--先看看ROWID长什么样子
SELECT ROWID, ROWNUM, test20190417.* FROM test20190417;
--
SELECT a.ROWID, b.ROWID, a.*, b.* FROM test20190417 a,test20190417 b WHERE a.ROWID > b.rowid 
and a.name = b.name  and a.age = b.age;


--查询有重复数据的记录
SELECT NAME, age, COUNT(*) FROM test20190417
GROUP BY NAME, age
HAVING COUNT(*) > 1;
--
SELECT * FROM test20190417;
--重复数据完全删除
DELETE FROM test20190417 WHERE NAME IN (
SELECT NAME FROM test20190417
GROUP BY NAME, age
HAVING COUNT(*) > 1);
--重复数据完全删除(子查询返回多列)
DELETE FROM test20190417 WHERE (NAME, age) IN (
SELECT NAME, age FROM test20190417
GROUP BY NAME, age
HAVING COUNT(*) > 1);
--
SELECT ROWID, ROWNUM, test20190417.* FROM test20190417;
--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT a.ROWID AS rowid1, a.*, b.ROWID AS rowid2, b.* FROM test20190417 a, test20190417 b WHERE 
a.rowid > b.rowid AND a.name = b.name AND a.age = b.age;
--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT a.ROWID AS rowid1, a.*, b.ROWID AS rowid2, b.* FROM test20190417 a, test20190417 b WHERE 
a.rowid < b.rowid AND a.name = b.name AND a.age = b.age;
--其实也可以用主键,执行下面的sql语句,观察一下查到的结果,就明白了
SELECT a.ROWID AS rowid1, a.*, b.ROWID AS rowid2, b.* FROM test20190417 a, test20190417 b WHERE 
a.id > b.id AND a.name = b.name AND a.age = b.age;

--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT * FROM test20190417 WHERE ROWID IN(
SELECT a.ROWID AS myRowId FROM test20190417 a, test20190417 b WHERE 
a.rowid > b.rowid AND a.name = b.name AND a.age = b.age);
--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT a.ROWID AS myRowId FROM test20190417 a, test20190417 b WHERE 
a.rowid > b.rowid AND a.name = b.name AND a.age = b.age
--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT a.ROWID AS myRowId, a.* FROM test20190417 a, test20190417 b WHERE 
a.rowid > b.rowid AND a.name = b.name AND a.age = b.age
--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT * FROM test20190417 WHERE ROWID NOT IN(
SELECT a.ROWID AS myRowId FROM test20190417 a, test20190417 b WHERE 
a.rowid > b.rowid AND a.name = b.name AND a.age = b.age);
--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT * FROM test20190417 WHERE ROWID IN(
SELECT a.ROWID AS myRowId FROM test20190417 a, test20190417 b WHERE 
a.rowid > b.rowid AND a.name = b.name AND a.age = b.age);

--先查一下原表中的数据
SELECT * FROM test20190417;
--利用rowid去掉重复数据,但是要保留一条
DELETE FROM test20190417 WHERE ROWID IN(
SELECT a.ROWID AS myRowId FROM test20190417 a, test20190417 b WHERE 
a.rowid > b.rowid AND a.name = b.name AND a.age = b.age);
--再查一下原表中的数据,发现重复的数据已经删除了,并且还保留了一条,我们的目的达到了
SELECT * FROM test20190417;

--利用rowid去掉重复数据,但是要保留一条(如下写法也是可以的)
DELETE FROM test20190417 WHERE ROWID IN(
SELECT a.ROWID AS myRowId FROM test20190417 a, test20190417 b WHERE 
a.rowid < b.rowid AND a.name = b.name AND a.age = b.age); --改成a.rowid < b.rowid也可以,就是改成小于号而已
--
--利用主键去掉重复数据也是可以的,但是要保留一条(如下写法也是可以的)
DELETE FROM test20190417 WHERE id IN(
SELECT a.id AS myId FROM test20190417 a, test20190417 b WHERE 
a.id < b.id AND a.name = b.name AND a.age = b.age);
--利用主键去掉重复数据也是可以的,但是要保留一条(如下写法也是可以的)
DELETE FROM test20190417 WHERE id IN(
SELECT a.id AS myId FROM test20190417 a, test20190417 b WHERE 
a.id > b.id AND a.name = b.name AND a.age = b.age); --改成大于号而已

--
--先查一下原表中的数据
SELECT ROWNUM, ROWID, t.* FROM test20190417 t;


SELECT ROWID, ROWNUM, test20190417.* FROM test20190417;
--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT MAX(a.ROWID), MIN(a.ROWID), MAX(b.ROWID), MIN(b.ROWID) FROM test20190417 a, 
test20190417 b WHERE a.name = b.name AND a.age = b.age;
--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT a.ROWID, a.*, b.ROWID, b.* FROM test20190417 a, 
test20190417 b WHERE a.name = b.name AND a.age = b.age;
--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT MAX(a.id), MIN(a.id), MAX(b.id), MIN(b.id) FROM test20190417 a, 
test20190417 b WHERE a.name = b.name AND a.age = b.age;
--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT ROWID, a.* FROM test20190417 a WHERE ROWID IN
(SELECT MAX(b.ROWID) FROM test20190417 b WHERE a.name = b.name AND a.age = b.age);
--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT * FROM test20190417 a WHERE ROWID IN
(SELECT MAX(b.ROWID) FROM test20190417 b WHERE a.name = b.name AND a.age = b.age);
--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT * FROM test20190417 a WHERE ROWID NOT IN
(SELECT MAX(ROWID) FROM test20190417 b WHERE a.name = b.name AND a.age = b.age);
--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT * FROM test20190417 a WHERE ROWID IN
(SELECT MIN(ROWID) FROM test20190417 b WHERE a.name = b.name AND a.age = b.age);
--执行下面的sql语句,观察一下查到的结果,就明白了
SELECT * FROM test20190417 a WHERE ROWID NOT IN
(SELECT MIN(b.ROWID) FROM test20190417 b WHERE a.name = b.name AND a.age = b.age);

--其实也可以使用主键
SELECT * FROM test20190417 a WHERE id IN
(SELECT MIN(id) FROM test20190417 b WHERE a.name = b.name AND a.age = b.age);
--
SELECT * FROM test20190417 a WHERE id NOT IN
(SELECT MIN(b.id) FROM test20190417 b WHERE a.name = b.name AND a.age = b.age);
--
SELECT * FROM test20190417 a WHERE id IN
(SELECT MAX(b.id) FROM test20190417 b WHERE a.name = b.name AND a.age = b.age);
--
SELECT * FROM test20190417 a WHERE id NOT IN
(SELECT MAX(id) FROM test20190417 b WHERE a.name = b.name AND a.age = b.age);

/*
由于ROWID用来唯一标识表中数据的唯一性,所以可以利用这个特性去除重复
很显然,如果表中的数据有重复的,但是ROWID肯定是不会重复的,那么就可以利用这个特性去重
*/

/*
利用rowid去掉重复数据还有其他的写法
如下:
*/
--先查一下原表中的数据
SELECT ROWNUM, ROWID, t.* FROM test20190417 t;

/*
利用rowid去掉重复数据,但是要保留一条(如下写法也是可以的)
*/
--注意:使用not IN 效率是很低的, not是不带索引的

--使用min()和max()效果是一样的,任选一个都可以
DELETE FROM test20190417 a WHERE ROWID NOT IN (
SELECT MIN(b.ROWID) FROM test20190417 b WHERE a.name = b.name AND a.age = b.age);
--使用max()
DELETE FROM test20190417 a WHERE ROWID NOT IN (
SELECT MAX(ROWID) FROM test20190417 b WHERE a.name = b.name AND a.age = b.age);
--使用主键的写法也是可以的,如下:
DELETE FROM test20190417 a WHERE id NOT IN (
SELECT MIN(id) FROM test20190417 b WHERE a.name = b.name AND a.age = b.age);
--使用max()
DELETE FROM test20190417 a WHERE id NOT IN (
SELECT MAX(id) FROM test20190417 b WHERE a.name = b.name AND a.age = b.age);

--利用rowid去掉重复数据,但是要保留一条,使用分组的写法也是可以的
--先查一下原表中的数据
SELECT ROWNUM, ROWID, t.* FROM test20190417 t;

--使用rowid和分组去重
DELETE FROM test20190417 a WHERE ROWID NOT IN (
SELECT MAX(ROWID) FROM test20190417 b GROUP BY b.name, b.age);
--使用rowid和分组去重
DELETE FROM test20190417 a WHERE ROWID NOT IN (
SELECT MIN(ROWID) FROM test20190417 b GROUP BY b.name, b.age);

--使用主键和分组去重
DELETE FROM test20190417 a WHERE id NOT IN (
SELECT MAX(id) FROM test20190417 b GROUP BY b.name, b.age);
--使用主键和分组去重
DELETE FROM test20190417 a WHERE id NOT IN (
SELECT MIN(id) FROM test20190417 b GROUP BY b.name, b.age);

 

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页