--分页语句rownum
select * from (select d.*,rownum rn from ( select * from emp order by sal desc) d ) where rn>5 and rn<=10 ;
--序列
--创建序列
create sequence seq_class start with 1 increment by 2 maxvalue 5000 cache 30;
--修改序列
alter sequence seq_class cache 20;
--删除序列
drop sequence seq_class;
--查询当前用户下的序列信息
select * from user_sequences;
select seq_class.nextval from dual;
--序列建成功后,第一次不能用currval获取当前值,必须使用一次nextval,才可以查询当前值
select seq_class.currval from dual;
--为用户授予创建视图的权限
grant create view to scott;
revoke create view from scott;
--视图
--创建视图 不带with check option 也不带where 条件 数据可以通过视图和源表添加
create or replace view emp_view as
select empno,ename,job from emp;
select empno,ename from emp_view; --操作视图
select * from emp;
delete from emp_view where empno=6666;
insert into emp_view values(1111,'lily','clerk');--通过视图添加数据,视图和源表都能添加
--创建视图 带 with check option 条件, 没带where条件 ,也可以添加
create or replace view emp_view2 as
select empno,ename,job from emp with check option;
select * from emp_view2;
insert into emp_view2 values(1111,'lily','clerk'); --也可以添加
select * from emp;
delete from emp where empno=1111;
--创建视图 带where条件 带 with check option
create or replace view emp_view3 as
select empno ,ename ,job,sal from emp where sal >1000 and sal<2000 with check option;
select * from emp_view3;
insert into emp_view3 values(1111,'lily','clerk',2500);--不满足where条件,不能添加
insert into emp_view3 values(2222,'lucy','clerk',1500); --满足where条件是可以添加的
--创建视图 带where 条件 不带with check option
create or replace view emp_view4 as
select empno,ename ,job ,sal from emp where sal>1000 and sal<2000;
insert into emp_view4 values(1111,'lily','clerk',2500);
select * from emp_view4;
select * from emp;
delete from emp where empno=1111;
--创建视图 with read only 只读操作
create or replace view emp_view5 as
select empno,ename,job from emp with read only;
select * from emp_view5;
insert into emp_view5 values(3333,'偶发清','搬砖');--不能对只读视图 做DML操作 也就是增删改