Oracle【序列、索引、视图、分页】
1、Oracle序列 1 --创建默认序列 2 create sequence cc;--创建序列 3 select cc.nextval from dual;--递增的方式去创建序列 4 select cc.currval from dual;--查询当前序列 1 --创建测试表 2 create table student( 3 tid number(10) primary key, 4 tname varchar(100) not null 5 ) 6 --添加测试数据 7 --之前写法:【多复制多改动很多数据,费时间】 8 insert into student values(1,‘迪丽热巴‘); 9 insert into student values(2,‘柚子解说‘); 10 --使用序列添加 11 insert into student values(cc.nextval,‘迪丽热巴‘); 12 insert into student values(cc.nextval,‘柚子解说‘); 1 --创建自定义序列 2 create sequence c1--创建序列 3 start with 5 --设置开始位置 4 increment by 2 --设置步长 5 select c1.nextval from dual 6 select c1.currval from dual 7 --删除序列:drop sequence 序列名 8 drop sequence cc; 2、Oracle索引 1 --创建语法 2 create index 索引名 on 表名(字段名) 3 --测试数据 4 create index index_student_tname on student(tname)--创建索引 5 select * from student where tname=‘柚子解说‘ 6 --删除索引 7 drop index index_student_tname 3、Oracle视图 1 --测试视图 2 --创建视图并对外提供了sid,sname,ssex三个字段 3 create view stu as select sid,ssex from student 4 select * from student--可查询所有的详细数据 5 select * from stu--只能查询到视图对外开放的字段 --视图的只读模式 create view stu2 as select sid,ssex from student with read only 4、分页 1 --查询员工信息前5条数据[第一页] 2 select rownum,e.* from emp e where rownum<=5 3 select * from (select rownum r,e.* from emp e where rownum <=5) t where r>0; 1 --查询员工信息的6-10条后所有数据[第二页] 2 select * from (select rownum r,e.* from emp e where rownum<=10) t where r>5 1 --查询员工信息的11-15条数据[第三页] 2 select * from (select rownum r,e. * from emp e where rownum<=15) t where r>10; 分页的总结:每页显示n条数据,查询第m页数据? select * from (select rownum r,e. * from 分页的表 e where rownum<=n*m) t where r>n*m-n; 例子:每页显示3条数据,查询第2页的数据 select * from (select rownum r,e. * from emp e where rownum<=3*2) t where r>3*2-3; select * from (select rownum r,e. * from emp e where rownum<=6) t where r>3; 灵活应变: 1 --只查询员工姓名,工资。【每页显示3条数据,查询第4页的数据】 2 select * from (select rownum r,e. * from emp e where rownum<=3*4) t where r>3*4-3; 3 select t.ename,t.sal from (select rownum r,e. * from emp e where rownum<=12) t where r>9; 1 --分页查询员工信息按照工资排序 2 select * from (select rownum r,t.* from (select * from emp order by sal desc) t where rownum<=10 ) where r>5 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |