数据库---视图（view）

一、概述

视图（VIEW），也称为虚表，不占用物理空间，这个也是相对的概念，因为视图本身的定义语句还是要存储在数据字典里面的。视图只有逻辑定义，每次使用的是时候，只是重新执行SQL。

视图是从一个或者多个表中获得的。这些表存放在数据库中，那些用于产生视图的表叫做该视图的基表，一个视图也可以从另一个视图中产生。

视图的定义存在数据库中，于此定义相关的数据并没有再存一份在数据库中。通过视图看到的数据存放在基表中。

视图看上起非常像数据库中的物理表。对它的操作通任何其他的表一样。当通过视图修改数据时，实际上是改变基表中的数据；相反的，基表数据的改变也会自定反映在由基表产生的视图中。

由于逻辑上原因，有些Oracle视图可以修改对应的基表，有些则不能（仅仅能查询而已），在新建视图的时候进行授权是否只读视图

 

二、创建视图

-- 创建视图的语法格式
CREATE [OR REPLACE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH READ ONLY];

-- 新建一个视图
CREATE VIEW v$_emp_dept AS
    SELECT
        emp.DEPTNO,
        ENAME,
        DNAME 
    FROM
        EMP
        JOIN DEPT ON emp.DEPTNO = dept.DEPTNO
WITH READ ONLY;

 

三、视图的使用

-- 创建一个名为“v_emp”的视图
create view v_emp as select * from emp where deptno = 30;

-- 使用视图来查询
select * from v_emp;

-- 向v_emp视图中插入数据,执行成功之后，发现基表新增了一条数据
insert into v_emp(empno,ename) values(1111,'zhangsan');

-- 向视图v$_emp_dept视图插入数据，  该视图是只读视图，插入失败
insert into v$_emp_dept(empno,ename) values(1234,'lisi');

-- 删除视图
drop view v_emp;

 

四、视图的使用案例

查询出：平均薪水等级最低的部门，它的部门名称是什么，要求完全使用子查询

-- 1、求出部门平均薪水
SELECT e.DEPTNO,AVG(e.sa) FROM EMP e GROUP BY e.DEPTNO;

-- 2、求出平均薪水的等级
SELECT
    t.DEPTNO,
    sg.GRADE gd 
FROM
    SALGRADE sg
    JOIN ( SELECT e.DEPTNO, AVG( e.SAL ) vsal FROM EMP e GROUP BY e.DEPTNO ) t ON t.vsal BETWEEN sg.losal 
    AND sg.HISAL;

 

 

 

--3、求出部门平均薪水等级 最低的那个等级
SELECT
    MIN( t.gd ) 
FROM
    (
    SELECT
        t.DEPTNO,
        sg.GRADE gd 
    FROM
        SALGRADE sg
        JOIN ( SELECT e.DEPTNO, AVG( e.SAL ) vsal FROM EMP e GROUP BY e.DEPTNO ) t ON t.vsal BETWEEN sg.losal 
        AND sg.HISAL 
    ) t;

 

 

 

-- 4、求出平均薪水的等级最低等级，与部门表进行关联查询
SELECT
    d.dname,
    d.deptno 
FROM
    DEPT d
    JOIN (
    SELECT
        t.DEPTNO,
        sg.GRADE gd 
    FROM
        SALGRADE sg
        JOIN ( SELECT e.DEPTNO, AVG( e.SAL ) vsal FROM EMP e GROUP BY e.DEPTNO ) t ON t.vsal BETWEEN sg.losal 
        AND sg.HISAL 
    ) t2 ON d.DEPTNO = t2.DEPTNO 
WHERE
    t2.gd = (　
    SELECT
        MIN( t3.gd )  // 求出平均薪资等级最低的等级。
    FROM
        (
        SELECT
            t.DEPTNO,
            sg.GRADE gd 
        FROM
            SALGRADE sg
            JOIN ( SELECT e.DEPTNO, AVG( e.SAL ) vsal FROM EMP e GROUP BY e.DEPTNO ) t ON t.vsal BETWEEN sg.LOSAL 
            AND sg.HISAL 
        ) t3 
    )


--------使用1992语法
SELECT
    d.dname,
    d.deptno 
FROM
    DEPT d,
    (
    SELECT
        t.DEPTNO,
        sg.GRADE gd 
    FROM
        SALGRADE sg
        JOIN ( SELECT e.DEPTNO, AVG( e.SAL ) vsal FROM EMP e GROUP BY e.DEPTNO ) t ON t.vsal BETWEEN sg.LOSAL 
        AND sg.HISAL 
    ) t2 
WHERE
    d.DEPTNO = t2.DEPTNO 
    AND t2.gd = (
    SELECT
        MIN( t3.GD ) 
    FROM
        (
        SELECT
            t.DEPTNO,
            sg.GRADE gd 
        FROM
            SALGRADE sg
            JOIN ( SELECT e.DEPTNO, AVG( e.SAL ) vsal FROM EMP e GROUP BY e.DEPTNO ) t ON t.vsal BETWEEN sg.losal 
            AND sg.HISAL 
        ) t3 
    )

 

 四、上述案例分析

虽然在上面较长的SQL语句执行可以求出想要的结果， 可是在这一大段的SQL中很很多相似之处。  

可以将一段很长的SQL相识的语句抽离出来，新建视图；进行重构SQL。

在上面的SQL中有这样一段的SQL语句是重复的：

-- 计算出部门平均薪资的等级
SELECT
    t.DEPTNO,
    sg.GRADE gd 
FROM
    SALGRADE sg
    JOIN ( SELECT e.DEPTNO, AVG( e.SAL ) vsal FROM EMP e GROUP BY e.DEPTNO ) t ON t.vsal BETWEEN sg.losal 
    AND sg.HISAL;

所以新建一个视图（VIEW）:

-- 对抽离出的SQL新建视图
--创建视图
CREATE VIEW v_deptno_grade AS
    SELECT
    t.DEPTNO,
    sg.GRADE gd 
    FROM
        SALGRADE sg
        JOIN ( SELECT e.DEPTNO, AVG( e.SAL ) vsal FROM EMP e GROUP BY e.DEPTNO ) t 
        ON t.vsal BETWEEN sg.losal AND sg.HISAL
WITH READ ONLY;

 

重构上述的案例：

-- 查询出平均薪水等级最低的部门，它的部门名称是什么，要求完全使用子查询
SELECT
    d.DEPTNO,
    d.DNAME 
FROM
    DEPT d
    JOIN v_deptno_grade t ON d.DEPTNO = t.DEPTNO 
WHERE
    t.gd = ( SELECT MIN( vdg.gd ) FROM v_deptno_grade vdg );
-- 这样写的SQL语句将变的极其简洁