SQL之视图

如果遇到没有创建的表的解决方案

->点我进入表的创建与数据插入

视图

视图是数据库对象之一，所有数据库对象名字不能重复，所以视图名字一般是以”V”开头的。
视图在SQL语句中体现的角色与表相同，但是视图并不是一张真实存在的表，而只是对应一个SELECT语句的查询结果集，并将其当做表看待而已。
使用视图的目的是简化SQL语句的复杂度，重用子查询，限制数据访问

创建视图

该视图包含的数据为10号部门的员工信息

CREATE VIEW v_emp_10_swm
AS
SELECT empno,ename,sal,deptno
FROM emp_swm
WHERE deptno=10

查看视图数据

SELECT * FROMv_emp_10_swm 
等价于
SELECT * FROM
    (SELECT empno,ename,sal,deptno 
     FROM emp_swm WHERE deptno=10) 
SELECT * FROMv_emp_10_swm

视图对应的子查询中的字段若含有函数或者表达式，那么该字段必须指定别名。
当视图对应的子查询中的字段使用了别名，那么视图中该字段就用别名来命名。

创建或修改视图

由于视图仅对应一个SELECT语句，所以修改视图就是替换该SELECT语句而已。

CREATE OR REPLACE VIEW v_emp_10_swm
AS
SELECT empno id,ename name,
    sal salary,deptno
FROM emp_swm
WHERE deptno=10
DESC v_emp_10

视图的作用

简化复杂查询

限制数据访问

视图的分类

视图分为简单视图和复杂视图

简单视图：

对应的子查询中不含有关联查询，查询的字段不包含函数，表达式等，没有分组，没有去重。

复杂视图

简单视图反之则是复杂视图

对视图进行DML操作

仅能对简单视图进行DML操作。对视图进行DML操作就是对视图数据来源的基础表进行操作

INSERT INTO v_emp_10_swm
    (id,name,salary,deptno)
VALUES
    (1001,'JACK',2000,10)
SELECT * FROM v_emp_10_swm
SELECT * FROM emp_swm

对视图的DML操作就是对基表操作，那么操作不当就可能对基表进行数据污染。

INSERT INTO v_emp_10_swm
    (id,name,salary,deptno)
VALUES
    (1002,'ROSE',3000,20)

视图对ROSE不可见

SELECT * FROM v_emp_10_swm
SELECT * FROM emp_swm

更新,同样存在更新后对数据不可控的情况

UPDATE v_emp_10_swm
SET deptno=20

删除不会对基表产生数据污染

DELETE FROM v_emp_10_swm
WHERE deptno=20

视图的约束

WITH CHECK OPTION

为视图添加检查选项，可以保证对视图的DML的操作后视图对其可见，否则不允许进行该DML操作，这样就避免了对基表进行数据污染。

CREATE OR REPLACE VIEW v_emp_10_swm
AS
SELECT empno id,ename name,
    sal salary,deptno
FROM emp_swm
WHERE deptno=10
WITH CHECK OPTION

WITH READ ONLY

为视图添加只读选项，那么该视图不允许进行DML操作。

CREATE OR REPLACE VIEW v_emp_10_swm
AS
SELECT empno id,ename name,
    sal salary,deptno
FROM emp_swm
WHERE deptno=10
WITH READ ONLY

和视图相关的数据字典：

USER_OBJECTS

在数据字段USER_OBJECTS中查询后缀是 _SWM视图名称

SELECT object_name
FROM user_objects
WHERE object_type='VIEW'
AND object_name LIKE '%_SWM'

USER_VIEWS

在数据字典USER_VIEW中查询指定视图

SELECT TEXT,view_name
FROM user_views
WHERE view_name='V_EMP_10_SWM'

USER_UPDATE_COLUMNS

在数据字典USER_UPDATEBLE_COLUMNS中查询可以更新的列视图

SELECT column_name, insertable, updatable, deletable 
FROM user_updatable_columns
WHERE table_name = 'V_EMP_10'

查看表的名称

SELECT table_name
FROM user_tables

复杂视图

创建一个含有公司部门工资情况的视图，内容为：部门编号，部门名称，部门的最高，最低，平均，以及工资总和信息。

CREATE VIEW v_dept_sal_swm
AS
SELECT d.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_swm e JOIN dept_swm d
ON e.deptno=d.deptno
GROUP BY d.deptno,d.dname
查看视图
SELECT * FROM v_dept_sal_swm

查看谁比自己所在部门平均工资高？

SELECT e.ename,e.sal,e.deptno
FROM emp_swm e JOIN v_dept_sal_swm v
ON e.deptno=v.deptno
AND e.sal>v.avg_sal

删除视图

删除视图本身并不会影响基表数据。
但是删除视图数据会将对应基表数据删除
语法：DROP VIEW view_name;

DROP VIEW v_emp_10_swm