oralce 经典习题系列--视图

oralce 经典习题系列

数据库视图

The problem

1 如何在一个表上创建视图？
2 如果在多个表上建立视图？
3 如何更改视图？
4 如何查看视图的详细信息？
5 如何更新视图的内容？
6 如何理解视图和基本表之间的关系、用户操作的权限？
表1

name	owner	Species	sex	birth	death
fluffy	Horold	cat	f	2003-10-12	2010-08-12
Claws	Gwen	cat	m	2004-08-10	null
Buffy	null	dog	f	2009-08-11	null
Fang	Benny	dog	m	2000-05-15	null
Bowser	Diane	dog	m	2003-04-16	2009-11-12
Chirpy	null	bird	f	2008-05-19	null
表1Pesonpet
sno	ownername	gender	addr	tel
—	—	—	—	—	—
201	Gwen	f	Shanghai	1522511
202	Benny	m	Beijing	1524664
203	Diane	f	Hangzhou	1364546

The answer

代码
–视图创建

create table pet(
  name varchar(20) primary key,
  owner varchar(20),
  species varchar(20) not null,
  sex varchar(1)  not null,
  birth date not null,
  death date,
  CONSTRAINT ck_sex CHECK (sex in('m','f'))
  );
insert into pet values ('Fluffy','Harold','cat','f','2003-10-12','2010-08-12');
insert into pet values ('Claws','Gwen','cat','m','2004-08-10','');
insert into pet values ('Buffy','null','dog','f','2009-08-11','');
insert into pet values ('Fang','Benny','dog','m','2000-05-15','');
insert into pet values ('Bowser','Diane','dog','m','2003-04-16','2009-11-12');```
insert into pet values ('Chirpy','null','bird','f','2008-05-19','');

create table Pesonpet (
  sno number(5) primary key,
  ownername varchar2(20) not null,
  gender varchar2(1) not null,
  addr varchar2(50) ,
  tel varchar2(20)
);
insert into Pesonpet values(201,'Gwen','f','Shanghai','1522511');
insert into Pesonpet values(202,'Benny','m','Beijing','1524664');
insert into Pesonpet values(203,'Diane','f','Hangzhou','1364546');
commit;
select * from pet;
--1创建单表视图
create or replace view vpet as select name ,owner ,species,sex from pet 
where death is null with check option ;
select * from vpet;
--2创建多表视图
create or replace view vpetpeason as select a.name,a.owner,a.species,b.addr,b.tel 
from pet a left join Pesonpet b on a.owner =b.ownername WITH READ ONLY;
select * from VPETPEASON;

--3修改视图vpet
create or replace view vpet as select * from pet ;
--修改视图增加视图约束
alter view vpetpeason add constraint un_tel unique(tel) disable NOVALIDATE;
commit;
--4查看
describe vpetpeason;
--5更新update ,insert ,drop 
select * from  vpet  ;
update vpet set owner ='Dack' where name ='Buffy';
commit;
insert into vpet values('Jim','Goder','java','f','2019-06-05','');
drop view vpet;
6 视图是基本表的逻辑表现，通过视图可以更好的控制人员对数据的修改，安全性更好。