oracle的sql语句rowid 和 rownum

–rowid 和 rownum 都是伪列
–rowid理解为记录在插入到数据库的表中时候就存在的数据的地址(对象的地址),其实不是地址,根据地址得到的值
–如果一个表中没有主键,没有不可重复的字段,可能会出现多条一模一样的数据,无法区分重复数据,可以根据rowid进行区分

select deptno,dname,loc,rowid from dept;
select empno,ename,rowid from emp;

insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
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);

select * from tb_student;

–当一个表中有多条一模一样的数据的时候,实现去重,重复数据只保留一条
–查到要保留的数据

select id,name,course,score,min(rowid) from tb_student group by id,name,course,score; 
select * from tb_student where rowid in(select min(rowid) from tb_student group by id,name,course,score);

–查到要删除的数据

select *
  from tb_student
 where not
        rowid in
        (select min(rowid) from tb_student group by id, name, course, score);

–删除这些数据

delete from tb_student
 where not
        rowid in
        (select min(rowid) from tb_student group by id, name, course, score);

–rownum 结果集的序号 有一个结果集就有一个rownum select查到的结果集
–rownum 从1开始,每次+1

select deptno,dname,loc,rowid,rownum from dept;
select empno,ename,rownum from emp where rownum<=4;

–分页:在oracle中使用rownum.因为rownum规律可循,控制rownum序号从1开始,每次+1,方便判断
–查询
–一般如果根据主键字段进行排序,先排序后确定rownum

select deptno,dname,rownum from dept order by deptno desc;

–一般如果根据非主键字段进行排序,先确定rownum再排序

select deptno,dname,rownum from dept order by loc;

–保证一定先排序后确定rownum,在结果集的外面嵌套一层select,这个select的rownum肯定就是从1开始,根据这个有规律的,已确定的row进行判断操作就可以

select deptno,dname,rownum num from dept order by loc;
select rownum,deptno,dname,num from (select deptno,dname,rownum num from dept order by loc);

select empno,ename,sal,comm,rownum from (select empno,ename,sal,comm,rownum num from emp order by sal desc); 

–分页需求:
–i页数 num每页显示几个
–num= 3 i=4
–每一页要显示的数据的rownum 第一个: rownum>=num*(i-1)+1 最后一个为: row<=num*i

select *
  from (select empno, ename, sal, comm, rownum num2
          from (select empno, ename, sal, comm, rownum num
                  from emp
                 order by sal desc))
 where num2 >= 3 * (1 - 1) + 1
   and num2 <= 3 * 1;

–select 数据 from 数据来源 where 行过滤条件 group by 分组字段 having 组过滤信息 order by 排序
–执行流程: from where group by having select order by
–decode() |case判定函数 nvl()
–单行函数
–组函数
–子查询
–in 和 exists
–rowid 和 rownum
–集合函数
–like