ORACLE_OCP之视图VIEW
- 视图不光是ORACLE的概念,在所有的数据库中基本都包含了视图这个数据对象。
一、什么是视图?
- 视图是在逻辑上代表来自一个和多个表的数据子集[结果集]。他和同义词一样都是一种映射关系。但是视图的功能和在数据库中的地位要远远的高于同义词,是ORACLE和其他数据库中一个重要的数据对象概念。
二、为什么我们要使用视图?
- 1 限制数据访问
- 2 使复杂的查询变得更容易 将复杂查询的结果变成视图 或者 将复杂查询要查询的条件提前视图化。
- 3 提供数据的对立性 我不用什么都去访问表 可以先访问视图 确定以后再去访问表 减少对表的不必要访问
- 4 提供相同数据但是不同的视图[范围]
三、视图的种类
备注 | 简单视图 | 复杂视图 |
---|---|---|
表的数量 | 一个 | 一个或者多个 |
包含函数 | 不 | 包含 |
是否分组 | 不 | 包含 |
是否支持DML | 是 | 不一定 |
四、视图操作
- 建立一个简单的视图:
CREATE VIEW EMPVW1 AS SELECT EMPLOYEE_ID,LAST_NAME,SALARY FROM EMP1;
CREATE VIEW EMPVW2 AS SELECT EMPLOYEE_ID,LAST_NAME,SALARY*12 ANNSAL FROM EMP1;
DESC EMPVW1;
DESC EMPVW2;
SELECT * FROM EMPVW1;
SELECT * FROM EMPVW2;
- 创建视图还有一个语句
CREATE OR REPLACE VIEW EMPVW1
(ID,NAME,SAL)
AS SELECT EMPLOYEE_ID,LAST_NAME,SALARY FROM EMP1;
-
使用视图访问数据:当我们使用视图访问数据时,ORACLE将做以下操作:
- 1 他从USER_VIEWS 数据字典中检索视图的定义
SELECT * FROM USER_VIEWS;
- 2 他检查视图基表的访问权限。对于EMPVW1和EMPVW2来说EMP1就是视图的基表,也就是说创建视图所用到的表,我们称之为视图的基表。
- 3 他将视图查询装换成对基表的等同操作。也就是说数据依然从基表中检索,或对基本进行相关的DML操作。
-
创建以后通过查询我们可以看到新增了别名,那我们别名有两种修改方式
- 1 SELECT语句后面加入相关的别名
- 2 通过CREATE OR RELPLACE方式修改
- 栗子:
CREATE OR REPLACE VIEW EMPVW1
(ID,NAME,SAL)
AS SELECT EMPLOYEE_ID,LAST_NAME,SALARY FROM EMP1;
-
创建一个复杂的视图:
CREATE OR REPLACE VIEW EMPVWF1
(NAME ,MINSAL,MAXSAL,AVGSAL)
AS SELECT D.DEPARTMENT_NAME,MIN(E.SALARY),MAX(E.SALARY),AVG(E.SALARY)
FROM EMPLOYEES E,DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME;
-
视图上执行DML操作规则
-
1 可以在简单视图上执行DML操作
-
2 如果视图包含了以下内容,则不能发生相关DML操作
- 分组函数
- GROUP BY
- DISTINCT
- 伪劣ROWNUM等
- 有表达式定义的列
- 基表中未被视图选中的NOT NULL的列
-
例子
select * from empvw1;
select * from emp1 where employee_id = 100;
update empvw1 set name=‘MONKEY KING’ where id = 100;
select * from empvw1;
rollback; -
-
对视图加入两种相关的约束:
- 未加约束的
CREATE OR REPLACE VIEW EMPVW3
(ID,NAME,SAL,DEPTNO)
AS SELECT EMPLOYEE_ID,LAST_NAME,SALARY ,DEPARTMENT_ID FROM EMP1 WHERE DEPARTMENT_ID=20;
select * from emp1;- 加了约束的
CREATE OR REPLACE VIEW EMPVW4
(ID,NAME,SAL,DEPTNO)
AS SELECT EMPLOYEE_ID,LAST_NAME,SALARY ,DEPARTMENT_ID FROM EMP1 WHERE DEPARTMENT_ID=20
WITH CHECK OPTION CONSTRAINT EMPVW4_CK20_1;
//加了约束之后,不能修改
update empvw4 set NAME=‘FAY11’ WHERE ID=202; —修改报错
SELECT * FROM EMPVW4;- 我们可以发现,加了约束条件之后,我们就没有办法去修改DEPTNO,但是其他可以照常修改,也就是说我们加了WITH CHECK OPTION CONSTRAINT以后,就没有办法修改创建视图时where子句之后的条件字段了。
-
彻底不让你修改:
- WITH READ ONLY 将视图变成只读视图 拒绝所有修改!
CREATE OR REPLACE VIEW EMPVW5
(ID,NAME,SAL,DEPTNO)
AS SELECT EMPLOYEE_ID,LAST_NAME,SALARY ,DEPARTMENT_ID FROM EMP1 WHERE DEPARTMENT_ID=20
WITH READ ONLY; -
删除视图
DROP VIEW 视图名;
DROP VIEW EMPVW5;
-
内联视图
- 1 内联视图是带有可以在SQL语句中使用的别名[相关名称]的子查询
- 2 主查询FROM 子句中的命名子查询就是一个内联视图实例
- 3 内联视图不是数据对象
- 内联视图小DEMO
SELECT A.LAST_NAME ,A.SALARY,A.DEPARTMENT_ID ,B.MAXSAL
FROM EMPLOYEES A,
(SELECT DEPARTMENT_ID,MAX(SALARY) MAXSAL FROM EMPLOYEES GROUP BY DEPARTMENT_ID) B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
AND A.SALARY < B.MAXSAL; -
排序TOP-N分析
- 显示EMPLOYEES表中收入最高的三个人的工资的信息
SELECT ROWNUM AS RANK,LAST_NAME,SALARY
FROM (SELECT * FROM EMPLOYEES ORDER BY SALARY DESC)
WHERE ROWNUM <= 3;