Oracle初探（四）

commit用法，序列，视图，索引

--创建两个用户
create user g1 identified by g1;
create user g2 identified by g2;
--为两个用户授权dba角色
grant dba to g1;
grant dba to g2;
grant insert,delete,update,select on book to g2;
grant insert,delete,update,select on stu to g2;

grant insert,delete,update,select on book to g1;
grant insert,delete,update,select on stu to g1;
--切换g1用户，完成建表操作
create table stu
(
stuid number(8),
stuname varchar2(20)
);
--插入测试数据
insert into stu values(1,'张三');
insert into stu values(2,'李四');
insert into stu values(3,'王五');
--提交
commit;
create table book
(
bid number(8),
bname varchar2(20)
);
--插入测试数据
insert into book values(1,'收获的季节');
insert into book values(2,'清风');
--提交
commit;

--序列
create table stu
(
stuid number(8),
stuname varchar2(20)
);
--创建序列
create sequence s1
start with 1
increment by 1;
--使用序列
insert into stu values(s1.nextval,'张三'); 

--修改序列
alter sequence s1
increment by 20;

insert into stu values(s1.nextval,'张三'); 

--删除序列
drop sequence s1;

--视图
create view view_ghost as select em.* from(select e.*,rownum rn from (select * from  emp)e where rownum<=10)em where rn>=5;

--使用视图
 select * from view_ghost;
 
 --替换视图
 create or replace view view_ghost as(select em.* from(select e.*,rownum rn from (select * from  emp)e where rownum<=10)em where rn=9);
 
 --删除视图
 drop view view_ghost;
 --索引
 create index  index_ghost on stu(stuname);