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