Oracle表设计

VIEW视图的概念

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

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

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

▪ 视图看上去非常象数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据 时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的 视图中。由于逻辑上的原因，有些Oracle视图可以修改对应的基表，有些则不能（仅仅能查 询）。

 

创建视图的语法：

 

CREATE [OR REPLACE] VIEW view

[(alias[, alias]...)]

AS subquery

[WITH READ ONLY];

CREATE OR REPLACE VIEW V$_EMP_DEPTAS SELECT EMP.DEPTNO,ENAME,DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WITH READ ONLY

授权视图

▪ 使用system用户为scott增加权限：grant create view,create table to scott;

▪ 使用system用户为scott解锁：alter user scott account unlock;

 

使用视图

 

--视图
/*
语法：
CREATE [OR REPLACE] VIEW VIEW
[(ALIAS[, ALIAS]...)]
AS SUBQUERY
[WITH READ ONLY];

*/
--如果普通用户第一次创建视图，提示没有权限，要使用管理员去修改权限 GRANT CREATE VIEW TO SCOTT;
--1.CMD 打开命令行 SQLPLUS /NOLOG 回车
--2.管理员连接语法：CONN SYS/123456@ORCL AS SYSDBA;
--创建视图
CREATE VIEW V_EMP AS SELECT * FROM EMP;
--查看视图
SELECT * FROM V_VIEW;
--向视图中添加数据,执行成功之后，需要提交事务，绿色表示提交事务，让数据生效，红色表示回滚事务，让数据恢复原状态
INSERT INTO V_EMP(EMPNO,ENAME) VALUES (111111,'小七')
SELECT * FROM V_EMP;
--如果定义的视图是非只读视图的话，可以通过视图向表中插入数据，如果是只读视图，则不可以插入数据
CREATE VIEW V_EMP2 AS SELECT * FROM EMP WITH READ ONLY;
SELECT * FROM V_EMP2;
--只读视图只提供查询的需求，无法进行增删改操作
INSERT INTO V_EMP2(EMPNO,ENAME) VALUES(1234,'LISI');
--删除视图
DROP VIEW V_EMP2;
--当删除视图中的数据的时候，如果数据来源于多个基表，则此时不能全部进行删除，只能删除一个表中的数据

--我们要求平均薪水的等级最低的部门，它的部门名称是什么.
--1、求平均薪水
SELECT E.DEPTNO,AVG(E.SAL) 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) TON 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) T ON T.DEPTNO = D.DEPTNOWHERE T.GD =  (SELECT MIN(T.GD) FROM (SELECT T.DEPTNO, SG.GRADE GD FROM SALGRADE SG JOIN (SELECT E.DEPTNO, AVG(E.SAL) VSALFROM EMP E GROUP BY E.DEPTNO) T ON T.VSAL BETWEEN SG.LOSAL AND SG.HISAL) T);
--查看SQL语句能够发现，SQL中有很多的重复的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) TON T.VSAL BETWEEN SG.LOSAL AND SG.HISAL;
--使用视图替换
SELECT D.DNAME, D.DEPTNO FROM DEPT D  JOIN V_DEPTNO_GRADE T ON T.DEPTNO = D.DEPTNO WHERE T.GD = (SELECT MIN(T.GD) FROM V_DEPTNO_GRADE T);