[Oracle数据库学习]十一、视图
D7
常见的数据库对象
| 对象 | 描述 |
| 表 | 基本的数据存储集合，由行和列组成 |
| 视图 | 从表中抽出的逻辑上相关的数据集合 |
| 序列 | 提供有规律的数值 |
| 索引 | 提高查询的效率 |
| 同义词 | 给对象起别名 |
视图
为什么使用视图
1）控制数据访问
2）简化查询
3）数据独立性
4）避免重复访问相同的数据
视图的分类
| 特性 | 简单视图 | 复杂视图 |
| 表的数量 | 一个 | 一个或多个 |
| 函数 | 没有 | 有 |
| 分组 | 没有 | 有 |
| DML操作 | 可以 | 有时可以 |
创建视图CREATE VIEW
可以在CREATE VIEW语句中嵌入子查询，子查询可以是很复杂的SELECT语句。
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]];
示例：
CREATE VIEW empvu80 AS SELECT employee_id, employee_name, salary FROM employees WHERE department_id = 1;
查看定义：
DESCRIBE empvu80;
创建视图时在子查询中给列定义别名，在选择视图中的列时，应使用别名。
CREATE VIEW emp_salvu50 AS SELECT employee_id ID_NUMBER, employee_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id = 1;
查询视图
SELECT * FROM emp_salvu50;
| ID_NUMBER | NAME | ANN_SALARY |
|---|---|---|
| 1 | Alice | 120000 |
| 2 | Bob | 60000 |
注：查询时，Oracle Server先找到视图，然后通过视图查询对应的表，得到数据。另可以看到查询出的列使用的是别名。
修改视图
使用CREATE OR REPLACE VIEW子句修改视图，
CREATE VIEW子句中各列的别名应和子查询中各列相对应。
CREATE OR REPLACE VIEW empvu80 (id_number, name, sal, department_id) AS SELECT employee_id, employee_name, salary, department_id FROM employees WHERE department_id = 1;
查询：
SELECT * FROM empvu80;
| ID_NUMBER | NAME | SAL | DEPARTMENT_ID |
|---|---|---|---|
| 1 | Alice | 10000 | 1 |
| 2 | Bob | 5000 | 1 |
注：已经修改了列。
创建复杂视图
CREATE VIEW dept_sum_vu (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;
注：复杂视图：涉及多个表、使用了分组函数、分组等。
查询：
SELECT * FROM dept_sum_vu;
| NAME | MINSAL | MAXSAL | AVGSAL |
|---|---|---|---|
| Development | 5000 | 10000 | 7500 |
| Qulity | 10000 | 10000 | 10000 |
| Trade | 4000 | 8000 | 6000 |
视图中使用DML
规定
当视图定义中包含以下元素之一时不能使用insert :
1)组函数
2)GROUP BY子句
3)DISTINCT关键字
4)ROWNUM伪列
5)列的定义为表达式
6)表中非空的列在视图定义中未包括
当视图定义中包含以下元素之一时不能使用update :
1)组函数
2)GROUP BY子句
3)DISTINCT关键字
4)ROWNUM伪列
5)列的定义为表达式
WITHCHECKOPTION子句
使用WITH CHECK OPTION子句确保DML只能在特定的范围内执行，任何违反WITH CHECK OPTION约束的请求都会失败。
CREATE OR REPLACE VIEW empvu20 AS SELECT * FROM employees WHERE department_id = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck ;
注：这里的约束在哪里定义？
屏蔽DML 操作
可以使用WITH READ ONLY选项屏蔽对视图的DML操作，
任何DML操作都会返回一个Oracle server错误。
CREATE OR REPLACE VIEW empvu10 (employee_number, employee_name, job_title) AS SELECT employee_id, employee_name, job_id FROM employees WHERE department_id = 10 WITH READ ONLY;
注：这里如何使用？
删除视图
删除视图只是删除视图的定义，并不会删除基表的数据。
语法：
DROP VIEW view_name;
示例：
DROP VIEW empvu80;
临时视图
临时视图可以是嵌套在SQL语句中的子查询，在FROM子句中的的子查询是临时视图，
临时视图不是数据库对象。
Top-N分析
Top-N分析查询一个列中最大或最小的n个值:
如：
销售量最高的十种产品是什么?
销售量最差的十种产品是什么?
Top-N分析关心的是最大和最小的值的集合。
SELECT [column_list], ROWNUM FROM (SELECT [column_list] FROM table ORDER BY Top-N_column) WHERE ROWNUM <= N;
示例：
SELECT ROWNUM as RANK, employee_name, salary FROM (SELECT employee_name,salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 3;
| RANK | EMPLOYEE_NAME | SALARY |
|---|---|---|
| 1 | Gina | 12000 |
| 2 | Alice | 10000 |
| 3 | Elsa | 8000 |
总结：
本节介绍视图和TOP-N分析：
1）视图：创建CREATE VIEW、更新CREATE OR REPLACE VIEW、删除DROP VIEW等；
2）视图中使用：DML语句的规则、WITH CHECK OPTION（确保DML使用范围）、WITH READ ONLY（屏蔽DML使用）。
3）TOP-N分析：获取最大或最小的N个值的集合。