Oracle之视图

/*
视图就是一个虚表，我们可以从这个表中查询数据
视图就是一个命名的查询语句
任何有效的查询语句，无论多么复杂，都可以被创建成一个视图
 CREATE [OR REPLACE] VIEW view
 [(alias[,alias]...)]
 AS subquery
 [WITH READ ONLY];
视图的好处：
  1.可以限制对数据的访问，让用户通过视图可以看到表中的一部分数据
  2.可以使复杂的查询变得简单
  3.提供了数据的独立性，用户不知道数据来源
  4.提供了相同的数据的不同显示
*/

--赋予scott用户dba权限
grant create view to scott;
--scott用户登录
--使用PLSQL developer，直接登录
--使用sql plus，执行conn scott/orcl

--人事经理视图
CREATE VIEW HR_VIEW 
AS 
SELECT * FROM EMP;

SELECT * FROM HR_VIEW;

--项目经理视图
CREATE VIEW HR_MGR 
AS
SELECT EMPNO,ENAME FROM EMP;
--查询
SELECT * FROM HR_MGR;

--修改视图，项目经理应该知道项目奖金
CREATE OR REPLACE VIEW HR_MGR 
AS
SELECT EMPNO,ENAME,COMM FROM EMP;

SELECT * FROM HR_MGR;

--删除视图
--不会影响原表的数据
DROP VIEW HR_MGR;
DROP VIEW HR_VIEW;
select * from emp;


--查询所有员工的基本信息、部门信息
CREATE OR REPLACE VIEW view_emp
AS 
SELECT DISTINCT E.*, D.*
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO ;
--会报错，重复的列名，怎么解决？

--查询所有员工的基本信息、年薪、年收入（包括奖金）、部门信息
CREATE OR REPLACE VIEW view_emp
AS 
SELECT DISTINCT E.EMPNO, E.ENAME, E.JOB, E.MGR,E.HIREDATE,
E.SAL,(E.SAL*12) AS "年薪",E.COMM, (E.SAL*12+E.COMM) as "年收入",
D.Deptno, D.DNAME,D.LOC 
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO ;

--为什么有的行没有年收入？因为没有判断COMM非空的情况
CREATE OR REPLACE VIEW view_emp
AS 
SELECT DISTINCT E.EMPNO, E.ENAME, E.JOB, E.MGR,E.HIREDATE,
E.SAL,(E.SAL*12) AS "年薪",E.COMM, (E.SAL*12+NVL(E.COMM,0)) AS "年收入",
D.Deptno, D.DNAME,D.LOC 
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO ;

select * from view_emp;
--查询年收入>20000
select * from view_emp where "年收入">20000;

CREATE OR REPLACE VIEW view_emp 
("员工编号","员工姓名","员工工资")
AS 
SELECT DISTINCT E.EMPNO, E.ENAME, E.SAL
FROM EMP E ;
--报错
select * from view_emp where ename = 'SMITH'
--正确
select * from view_emp where 员工姓名 = 'SMITH'

--统计每个部门的薪水，最高工资，最低工资，平均工资
CREATE OR REPLACE VIEW V_EMP_SAL
AS
SELECT DEPTNO, 
MAX(SAL) AS "最高工资",
MIN(SAL) AS "最低工资", 
AVG(SAL) AS "平均工资"
FROM EMP
WHERE DEPTNO IS NOT NULL
GROUP BY DEPTNO
ORDER BY DEPTNO;

select * from v_emp_sal;

--视图是一个虚表，我们可以对表插入数据，也可以对视图插入数据
CREATE OR REPLACE VIEW V_EMPINFO
AS
SELECT * FROM EMP WHERE SAL > 2000
WITH READ ONLY;--将视图设置为只读视图后，不可插入数据

--对视图插入数据,数据会被插入到原表
INSERT INTO V_EMPINFO VALUES (8000,'张三','工程师',7902, '12-8月-1987'，1900,500,20);
select * from emp;