Oracle视图（基本使用）

视图

视图是通过定制的方式显示一个或者多个表的数据。

视图可以视为“虚拟表”或“存储的查询”。

视图的优点：

提供了另外一种级别的表安全性隐藏了数据的复杂性简化了用户的SQL命令隔离基表结构的改变通过重命名列，从另一个角度提供数据。

视图里面不存放数据，是在基表上建立的查询，查询的话会从基表把数据取出返回。

创建视图 sqlplus t1/t1@orcl #查看当前用户所拥有视图 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 violation order by SQL> create view view3 as select * from student order by sno desc; SQL> select * from view3; SNO SNAME ----- ----- 7 C 6 B 5 A 5 wa 链接视图 SQL> select * from student; SNO SNAME ----- ----- 1 B 2 C SQL> select * from address; ID NAME --------------------------------------- -------------------- 1 贵阳 2 大连 create view view_student_address as select s.sname,a.name from student s,address a where 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 XIAOYI 2 LI 3 ZHANG select * from view_student_1 where sname = 'XIAOYI'; 删除视图 drop view view_student_1;