一、概述
视图(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语句将变的极其简洁