视图是什么
视图几乎在所有的数据库软件中都存在。
视图是在逻辑上代表一个或者多个表的数据子集，和同义词一样，都是映射关系，视图的功能和地位高于同义词。
为什么使用视图
- 限制数据访问。
- 将复杂查询的结果变成视图，使的将来使用更加容易。
- 提供了数据的独立性，可以先访问视图，确定以后再访问表，减少对于数据库的不必要访问。
视图的种类
- 简单视图
- 复杂视图
简单视图 复杂视图
表的数量 一个 一个或者多个
包含函数 不 包含
是否分组 不 包含
支持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进行如下操作：
- 从User_Views数据字典中检索视图的定义。
- 检查试图基表(上例的EMP1表)的访问权限。
- 将视图的查询转换为对基表的操作，数据依然是从基表进行检索，或者对于基表进行相关的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的规则
- 可以在简单视图进行DML操作。
- 如果在视图包含以下内容，不能进行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 视图名
内联视图
- 内联视图是带有可以在SQL中使用的别名[相关名称]的子查询
- 主查询FROM 子句中的命名子查询就是一个内联视图实例
- 内联视图不是数据对象
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