15.数据库对象:视图
数据库对象:视图(VIEW)
-
VIEW 也被称作虚表,即虚拟的表,是一组数据的逻辑显示
-
视图对应于一条SELECT语句,结果集被赋予一个名字,即视图名字,SELECT语句对应的表被称为基表
-
视图本身并不包含数据,它只包含映射到基表的一个查询语句,当基表数据发生变化,视图数据也随之变化
-
使用视图的目的是简化SQL语句的复杂度,重用子查询,限制数据访问
授权创建视图
-
创建视图的语句是:CREATE VIEW
-
用户必须有 CREATE VIEW 系统权限,才能创建视图,如果没有权限,会提示:权限不足
-
管理员可以通过DCL语句授予用户创建视图的权限:CRANT 何种权限 TO 用户名;
- 如:CRANT CREATE VIEW TO tarena,给用户tarena授予创建视图的权限
创建视图
-
语法:CREATE VIEW 视图名 AS 查询语句
-
查看视图结构:DESC 视图名
-
一般视图名设置成:v_表名_条件,'v’代表视图
-
创建视图时,可以给列赋予别名,视图对应的查询语句中若含有函数或者表达式,那么该字段必须指定别名
-
可以使用 OR REPLACE 短语修改视图对应的SQL查询语句,也可以理解成重新创建视图
- 语法:CREATE OR REPLACE VIEW 视图名 AS 新的查询语句
视图的分类
-
简单视图:SELECT语句时基于单表建立的,且不包含任何函数运算、表达式、或分组函数,此时视图是基表的子集
-
复杂视图:SELECT语句同样是基于单表,但是包含了DISTINCT关键字、ROWNUM伪列、单行函数,表达式,分组函数或GROUP BY子句
-
连接视图:SELECT语句是基于多个表的
对视图进行DML操作(简单视图)
-
当对视图执行DML操作时,实际上时对基表进行DML操作
-
对视图执行DML操作的基表原则:
-
只有简单视图能够执行DML操作
-
在基表中定义了非空列,但简单视图对应的SELECT语句中并没有包含这个非空列,导致非空列对视图不可见,这时无法对视图执行INSERT操作
-
DELETE操作时,只能能删除现有视图里能查到的记录
-
-
如果对视图操作不当(UPDATE和INSERT),会对基表数据污染
创建具有约束的视图(简单视图)
-
检查约束:
-
语法:CREATE VIEW 视图名 AS 查询语句 WITH CHECK OPTION;
-
WITH CHECK OPTION 短语作用是:通过对视图所作的修改,必须在视图的可见范围内,否则不允许该操作执行
- 如:INSERT,新增记录必须是视图可见的数据,UPDATE,修改后的数据必须能通过视图查看到
-
-
只读约束:
-
语法:CREATE VIEW 视图名 AS 查询语句 WITH READ ONLY;
-
对视图进行DML操作是合法的,但是不安全的,修饰后,只能对视图做DQL操作
-
如果没有在视图上执行DML操作的必要,在建立视图时声明为只读来避免这种情况,保证视图对应的基表数据不会被非法修改
-
数据字典
-
数据字典实际上是一个表,存放的是我们对数据库做了哪些操作这种数据
-
和视图相关的数据字典:
-
USER_OBJECTS:记录了我们在数据库上创建的所有数据库对象
-
USER_VIEWS:记录了我们在数据库上创建的所有视图
-
删除视图
-
当不在需要视图的定义,可以使用 DROP VIEW 语句删除视图
-
视图虽然时存放在数据字典中的独立对象,但视图仅仅时基于表的一个查询定义,所以对视图的删除不会导致基表数据的丢失,不会影响基表数据
演示:视图
-
创建视图
CREATE VIEW v_emp_10
AS
SELECT empno,ename,sal,deptno FROM emp WHERE deptno=10;-
可以查看视图的结构
DESC v_emp_10; -
数据来源是emp表,视图本身是不包含数据的
SELECT * FROM v_emp_10;
-
-
OR REPLACE
CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,sal salary,deptno FROM emp WHERE deptno=10;SELECT * FROM v_emp_10;
-
复杂视图
- 创建一个含有公司部门工资情况的视图,内容为:部门的编号、名称、最高、最低、平均工资,以及工资总和
CREATE VIEW v_dept_sal
AS
SELECT e.deptno,d.dname,
MAX(e.sal) max_sal,MIN(e.sal) min_sal,
AVG(e.sal) avg_sal,SUM(e.sal) sum_sal
FROM emp e,dept d
WHERE e.deptno=d.deptno GROUP BY e.deptno,d.dname;
SELECT * FROM v_dept_sal;
- 查看谁比自己所在部门平均工资高
SELECT e.ename,e.sal,v.sal FROM emp e,v_dept_sal v
WHERE e.deptno=v.deptno AND e.sal>v.avg_sal;
- 创建一个含有公司部门工资情况的视图,内容为:部门的编号、名称、最高、最低、平均工资,以及工资总和
-
简单视图
INSERT INTO v_emp_10(id,name,salary,deptno) VALUES(‘1001’,‘JACK’,4600,10);
SELECT * FROM v_emp_10;
SELECT * FROM emp;
UPDATE v_emp_10 SET salary=3000 WHERE name=‘JACK’;
DELETE FROM v_emp_10 WHERE name=‘JACK’;
-
对视图插入数据可能导致数据污染
INSERT INTO v_emp_10(id,name,salary,deptno) VALUES(‘1002’,‘ROOS’,3000,20);
-
因为20号部门不能通过视图对应的查询语句显示到视图中
SELECT * FROM v_emp_10; -
但是这条数据被存入到emp表中了
SELECT * FROM emp;
-
-
更新同样存在数据污染的问题
UPDATE v_emp_10 SET deptno=20;
- emp表中的部门号都被改成20
SELECT * FROM v_emp_10;
- emp表中的部门号都被改成20
-
删除不会对基表产生数据污染
DELETE FROM v_emp_10 WHERE deptno=20;
-
WITH CHECK OPTION
CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,sal salary,deptno dept FROM emp WHERE deptno=10
WITH CHECK OPTION;-
再次插入’ROSE’记录,显示是违规操作
INSERT INTO v_emp_10(id,name,salary,dept) VALUES(‘1002’,‘ROOS’,3000,20); -
再次更新部门号为20,显示是违规操作
UPDATE v_emp_10 SET dept=20;
-
-
WITH READ ONLY
CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,sal salary,deptno dept FROM emp WHERE deptno=10
WITH READ ONLY;- 不能对只读视图进行DML操作
UPDATE v_emp_10 SET dept=20;
- 不能对只读视图进行DML操作
-
在数据字典USER_OBJECTS中查询所有视图名称
SELECT object_name FROM USER_OBJECTS WHERE object_type=‘VIEW’;
-
在数据字典USER_VIEWS中查询所有视图对应的查询语句
SELECT view_name,text FROM USER_VIEWS;
-
删除视图
DROP VIEW v_emp_10;