15.数据库对象：视图

最新推荐文章于 2021-12-01 22:13:36 发布

不太清楚

最新推荐文章于 2021-12-01 22:13:36 发布

阅读量633

点赞数 1

分类专栏： Oracle

本文链接：https://blog.csdn.net/weixin_43520314/article/details/102602959

版权

Oracle 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

数据库对象：视图(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操作的基表原则：
1. 只有简单视图能够执行DML操作
2. 在基表中定义了非空列，但简单视图对应的SELECT语句中并没有包含这个非空列，导致非空列对视图不可见，这时无法对视图执行INSERT操作
3. 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;
删除不会对基表产生数据污染

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;
在数据字典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;