oracle视图学习基础知识分享

/*create or replace 表示在创建视图时,如果已存在同名的视图,则重新创建,

如果只用create 创建,则需将原有的视图删除后才能创建*/
drop view v1;
create view v1
as
select * from stud where name like '%a%';
–直接查询view

select * from v1;

–当往stud中增加信息也会增加到viewzhong
insert into stud(id,name) values(5,'rajk');
–创建一个可以修改表的视图
create or replace view v2(id,name)
as
select * from stud where name like '%a%';
select * from v2;
--通过视图写如数据
insert into v2 values(6,'sssss');--视图查询是查不到的
insert into v2 values(7,'sasss');--可以查到

–包含一部分列的视图
drop view v3;
select * from v3;
create or REPLACE view v3
as
select id,name from stud where id<3;
insert into v3 values(8,'dd');
–一个只可以查询不能写入的视图
create or replace view v4(id,name)
as
select id,name from stud where id>4
WITH READ ONLY constraint v4_only;

–带检查的视图
create or replace view v5(id,name)
as
select id,name from stud where id>20
WITH check option;
insert into v5 values(10,'我是10号');
--视图 WITH CHECK OPTION where 子句违规
insert into v5 values(21,'我是21号');

–来自多个视图的表
drop view v6;
create  or REPLACE view v6
as
select s.name as 学生 ,c.name as 课程
from stud s inner join sc on s.id=sc.sid
inner join course c on sc.cid=c.id;
select * from v6;
—dba查询用户的视图
select * from USER_VIEWS;