数据库04笔记（分页）

--分页：rowid和rownum
--ROWID相当于对象的地址，在数据插入表中的时候已经存在，rowid不是地址，是地址转换的
--ROWWID和ROWNUM都是伪列

select * from tb_student;
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(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(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);

--项目数据值保留唯一的,删除重复的
--查询到要保留的数据
select deptno,dname,rowid from dept;
select name,course,score,min(rowid) from tb_student group by name,course,score;
--要删除的数据
select * from tb_student where not rowid in(select min(rowid) from tb_student group by name,course,score);
--删除数据
delete from tb_student where not rowid in(select min(rowid) from tb_student group by name,course,score)


--分页 rownum 结果集的序号  从1开始
--如果按照主键进行排序,先排序后确定rownum
--如果根据非主键进行排序,先确定rownum再排序
select  deptno,dname,rownum from dept order by deptno desc;
select empno,ename,sal,rownum from emp order by sal desc;

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

--显示前5个数据  根据员工编号降序排序,找到前5条数据
select empno,ename,rownum from emp order by empno desc;
select deptno,dname,rownum from dept where rownum>=3 and rownum<=5 order by deptno desc;

select deptno,dname,rownum,n from (select deptno,dname,rownum n from dept order by deptno desc) where n>=3 and n<=5 ;

--一页显示2 n条数据  第3 i页     这一页的起始数据的rownum=(i-1)*n+1     结束的rownum=i*n
select deptno,dname,rownum,n from (select deptno,dname,rownum n from dept order by deptno desc) where n>=5 and n<=6 ;

---- 按照工资降序排序， 找第三页的数据  一页显示2条


select empno, ename ,sal, rownum r from (select deptno, dname, rownum);