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);