深圳专业营销网站,济宁建设局官方网站,网站改版收录减少,网站设计奖视图
视图是通过定制的方式显示一个或者多个表的数据。
视图可以视为“虚拟表”或“存储的查询”。
视图的优点#xff1a;
提供了另外一种级别的表安全性隐藏了数据的复杂性简化了用户的SQL命令隔离基表结构的改变通过重命名列#xff0c;从另一个角度提供数据。 视图里…视图
视图是通过定制的方式显示一个或者多个表的数据。
视图可以视为“虚拟表”或“存储的查询”。
视图的优点
提供了另外一种级别的表安全性隐藏了数据的复杂性简化了用户的SQL命令隔离基表结构的改变通过重命名列从另一个角度提供数据。 视图里面不存放数据是在基表上建立的查询查询的话会从基表把数据取出返回。 创建视图
sqlplus t1/t1orcl#查看当前用户所拥有视图
select * from user_views;SQL select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
STUDENT TABLE SQL desc student;
Name Type Nullable Default Comments
----- ----------- -------- ------- --------
SNO NUMBER(4) Y
SNAME VARCHAR2(2) Y insert into student values(1,wa);
#创建视图
create view view1 as
select * from student where sno 1;select * from user_views;select * from view1;更新视图
#更新视图
update view1 set sno 5 where sno 1;
commit;#注意注意更新了视图表对应被更新了
SQL select * from student;SNO SNAME
----- -----5 wa
#视图查不到东西了
SQL select * from view1;SNO SNAME
----- -----with check option
#创建视图
create view view2 as
select * from student where sno 5 with check option;#查询视图
SQL select * from view2;SNO SNAME
----- -----5 wa#不让修改with check optin为了不让减少视图结果集的操作
SQL update view2 set sno 10 where sno 5;
update view2 set sno 10 where sno 5
ORA-01402: view WITH CHECK OPTION where-clause violationorder by
SQL create view view3 as
select * from student order by sno desc;SQL select * from view3;SNO SNAME
----- -----7 C6 B5 A5 wa链接视图
SQL select * from student;SNO SNAME
----- -----1 B2 CSQL select * from address;ID NAME
--------------------------------------- --------------------1 贵阳2 大连create view view_student_address
asselect s.sname,a.namefrom student s,address awhere s.sno a.id;SQL select * from view_student_address;
SNAME NAME
----- --------------------
B 贵阳
C 大连#无法修改与非建值保存表对应的列
#无法修改任何一列因为数据来自多张表。
#没有键保留表无法更改
SQL update view_student_address set name 河南 where sname B;
update view_student_address set name 河南 where sname B
ORA-01779: cannot modify a column which maps to a non key-preserved table键保留表
drop table student;
drop table department;
create table student(id int,name varchar2(20),deptno varchar2(20));
create table department(deptno varchar2(20),name varchar2(20));
insert into student values(1,wang,001);
insert into student values(2,li,001);
insert into student values(3,zhang,002);
insert into department values(001,人工智能系);
insert into department values(002,软件工程系);
insert into department values(003,ai系);
commit;drop view view_stu_dept;
create view view_stu_dept
as
select s.id,s.name sname,s.deptno deptno1,d.deptno deptno2,d.name deptname
from student s,department d
where s.deptno d.deptno;select * from view_stu_dept;# 因为student表的ID为主键 可以修改student而不能修改department
# student键保留表 department非键保留表SQL update view_stu_dept set sname xiaoyi where id 1;
update view_stu_dept set sname xiaoyi where id 1
ORA-01779: cannot modify a column which maps to a non key-preserved table咳咳翻车了问题不大遇到问题需要解决ALTER TABLE student ADD PRIMARY KEY(id);
ALTER TABLE department ADD PRIMARY KEY(deptno);drop view view_stu_dept;
create view view_stu_dept
as
select s.id,s.name sname,s.deptno deptno1,d.deptno deptno2,d.name deptname
from student s,department d
where s.deptno d.deptno;#成功
update view_stu_dept set sname xiaoyi where id 1;
SQL select * from view_stu_dept;# 函数建视图
create view view_student_1 as select id,upper(name) sname from student;SQL select * from view_student_1;ID SNAME
--------------------------------------- --------------------1 XIAOYI2 LI3 ZHANGselect * from view_student_1 where sname XIAOYI;删除视图
drop view view_student_1;
