Oracle 19C学习 - 16. 视图

视图是什么

视图几乎在所有的数据库软件中都存在。
视图是在逻辑上代表一个或者多个表的数据子集，和同义词一样，都是映射关系，视图的功能和地位高于同义词。

为什么使用视图

1. 限制数据访问。
2. 将复杂查询的结果变成视图，使的将来使用更加容易。
3. 提供了数据的独立性，可以先访问视图，确定以后再访问表，减少对于数据库的不必要访问。
   
   
   

视图的种类

1. 简单视图
2. 复杂视图

                      简单视图                复杂视图
表的数量                  一个                 一个或者多个
包含函数                  不                   包含
是否分组                  不                   包含
支持DML                  是                    不一定

HR用户下，用CTAS创建一个employees的备用表

CREATE TABLE EMP1 AS SELECT * FROM EMPLOYEES;

简单视图

创建简单试图

CREATE VIEW 视图名 AS 子查询

CREATE VIEW EMP_VW1 AS 
   SELECT 
      EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
   FROM EMP1;

DESC EMP_VW1;
Name        Null     Type         
----------- -------- ------------ 
EMPLOYEE_ID          NUMBER(6)    
FIRST_NAME           VARCHAR2(20) 
LAST_NAME   NOT NULL VARCHAR2(25) 
SALARY               NUMBER(8,2) 

创建或修改简单视图的语句

CREATE OR REPLACE VIEW 视图名
(列1别名， 列2别名...)
AS 查询语句

CREATE OR REPLACE VIEW empvw2
(id, name, sal)
AS 
   SELECT employee_id, first_name || ' ' || last_name, salary
   FROM Employees;

视图的工作原理

使用视图的时候，Oracle进行如下操作：

1. 从User_Views数据字典中检索视图的定义。
2. 检查试图基表(上例的EMP1表)的访问权限。
3. 将视图的查询转换为对基表的操作，数据依然是从基表进行检索，或者对于基表进行相关的DML操作。

复杂视图

创建复杂视图

CREATE OR REPLACE VIEW empvw2
(列名， 列名)
AS 多表查询;

CREATE OR REPLACE VIEW emp_vw_salary
(Department_name, Max_Salary, Min_Salary, Average_Salary)
AS
SELECT D.DEPARTMENT_NAME, MAX(E.salary), MIN(E.SALARY), ROUND(AVG(E.SALARY),2) 
FROM DEPARTMENTS D INNER JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME;

SELECT * FROM emp_vw_salary;
=>output:
Administration	4400	4400	4400
Accounting	12008	8300	10154
Purchasing	11000	2500	4150


select average_salary from emp_vw_salary where department_name = 'IT';
=>output:
5760

视图上操作DML的规则

1. 可以在简单视图进行DML操作。
2. 如果在视图包含以下内容，不能进行DML操作：
   • 分组函数
   • Group By
   • Distinct
   • 伪列Rownum等
   • 由表达式定义的列
   • 基表中存在未被视图选中的Not Null的列。
     
     
     

视图上添加约束条件

不允许修改条件字段的内容

CREATE OR REPLACE 视图名
AS 子查询
WITH CHECK OPTION CONSTRAINT 约束名;

添加约束以后，就无法修改创建视图WHERE子句之后的条件字段了，其他的字段可以修改。

CREATE OR REPLACE VIEW emp_it_salary
(DEPARTMENT_ID, NAME, SALARY)
AS
SELECT DEPARTMENT_ID, FIRST_NAME, SALARY 
FROM employees 
WHERE department_ID = 60
WITH CHECK OPTION CONSTRAINT emp_it_salary_constraint;

UPDATE emp_it_salary
SET department_id = 100;
=> output:
SQL Error: ORA-01402: view WITH CHECK OPTION where-clause violation
01402. 00000 -  "view WITH CHECK OPTION where-clause violation"

让视图只读

CREATE OR REPLACE 视图名
AS 子查询
WITH READ ONLY;

将视图变为只读视图，拒绝所有更改。

删除视图

DROP VIEW 视图名

内联视图

1. 内联视图是带有可以在SQL中使用的别名[相关名称]的子查询
2. 主查询FROM 子句中的命名子查询就是一个内联视图实例
3. 内联视图不是数据对象

SELECT A.FIRST_NAME, A.SALARY, B.DEPARTMENT_ID
FROM employees A inner join 
     (
      SELECT DEPARTMENT_ID, MAX(salary) MAX_SAL
      FROM EMPLOYEES
      GROUP BY DEPARTMENT_ID 
     ) B     -- >这里B就成为一个带有别名的子查询
ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
WHERE A.SALARY < B.MAX_SAL;

排序TOP-N分析

显示EMPLOYEES表中，收入最高的三个人的信息。

SELECT rownum as Rank, first_name, salary
FROM
  (SELECT * FROM employees ORDER BY salary DESC)
WHERE rownum <= 3;
=> output:
1	Steven	24000
2	Neena	17000
3	Lex	17000