菜鸟上路之oracle中rowid和rownum的使用 笔记整理

rowid和rownum区别

---

     rownum和rowid都是伪列，但是两者的根本是不同的，rownum是根据sql查询出的结果给每行分配一个逻辑编号，即你的sql不同也就会导致最终rownum不同(变)，但是rowid是物理结构上的，在每条记录insert到数据库中时，都会有一个唯一的物理记录(不变)。

rowid小试

   我在处理一张表中的删除重复记录,只保留一条记录..这时候rowid(在数据库里为每一条记录配有内部的唯一编号): 找出不同字段.
   测试代码如下:

-- 第一次创建的话,不需要的
-- drop table tb_student;  
create table tb_student(
       id number(4) ,
       name varchar2(20),
       course varchar2(20),
       score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',86);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
insert into tb_student values(3,'李四','语文',86);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
insert into tb_student values(3,'李四','语文',86);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
insert into tb_student values(3,'李四','语文',86);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;

– 查看rowid在内部是否唯一
select t.*, rowid from tb_student t;

– 查询学员的信息重复记录(分组及过滤组)
select name, course, count(2) from tb_student group by name, course having count(2) > 1;

– 删除重复记录,利用rowid找出不同字段, 只保留一条记录.

select name,course,max(rowid) from tb_student group by name, course;

delete from tb_student where rowid not in(select max(rowid) from tb_student s group by name,course);
commit;

rownum小试

/*rownum
1、排序后的结果集的顺序号
2、不能直接使用大于 1的数
*/
–默认 rownum 为emp 的顺序号 , 错误的(select优先于order by执行)
select e.*,rownum from emp e order by sal desc;

–排序后的结果集的顺序号
select e., rownum rn from (select from emp e order by sal desc) e;

–获取前二十名的
select e.*, rownum rn
from (select * from emp e order by sal desc) e
where rownum <= 20;
–获取前 5到20名的 ,不能直接大于1
– 结果不是我们所想的那样
select e.*, rownum rn
from (select * from emp e order by sal desc) e
where rownum <= 20
and rownum >= 5;

– 还需要在嵌套
select emp.*, rownum
from (select e.*, rownum rn
from (select * from emp e order by sal desc) e
where rownum <= 10) emp
where rn >= 5;

rownum小结

/*
第一层: 排序的后结果集
第二层: 获取最大值 <或<= ，给rownum 取个别名(确定一个绳的长度)
第三层: 使用别名获取最小>=或>(从这根绳的中在剪,得到你所需要的最终长度)
select *
from (select t.*, rownum rn
from (排序后的结果集) t
where rownum <= 最大值)
where rn >= 最小值;

*/

–1、找出工资前15名的 雇员姓名
select *
from (select t.*, rownum rn
from (select * from emp order by sal desc) t
where rownum <= 15)
where rn >= 1;
– 2、找出工资6-20名的员工信息
select *
from (select t.*, rownum rn
from (select * from emp order by sal desc) t
where rownum <= 20)
where rn >= 6;

常用的增删改查

1、crud
1)、查询: select count() from where
2)、添加: insert into 表名(字段列表) values(值列表);
3)、修改: update 表名 set 字段值=值,字段值2=值2 where
4)、删除: delete 表名 where

分页拓展

oracle
分页思想: 当前页(current) | 每页记录数(per) | 起始 | 结束
1 | 4 |1 | 4
2 | 4 | 5 | 8
3 | 4 |8 | 12

起始: per*(current-1)+1
结束: per*current

总记录数: total 23
每页数: per 4
总页数: (total+per-1)/per

mysql
分页思想: 当前页(current) |每页记录数(per) | 起始
1 | 4 | 0
2 | 4 | 4
3 | 4 | 8
起始: (current-1) *per, per