ORACLE 视图

-- 视图View 
-- 1. 父表的 View 展示父表一部分数据
-- 2. 视图修改了 父表相应也修改（如果创建一个子表，子表和父表是没有任何关联的，所以想要相互之间有关系必须要用视图）

-- 创建视图
--  ORA-01031: insufficient privileges 没得权限 
-- 我用的是navicate, 编辑连接使用system用户登录，点击顶部菜单用户->点击列表scott用户 -> 点击服务器权限->找到create view->打勾
CREATE VIEW view_emp
as 
SELECT EMPLOYEE_ID,LAST_NAME,SALARY FROM EMPLOYEES

--查询视图 
SELECT * from view_emp order by salary asc 

-- 修改视图，表数据一样被修改
UPDATE view_emp set SALARY = 20000 WHERE EMPLOYEE_ID = 132
-- 表数据一样被修改了
SELECT * from EMPLOYEES where EMPLOYEE_ID = 132


--起别名

CREATE VIEW view_emp1
as 
SELECT EMPLOYEE_ID as id,LAST_NAME as name,SALARY as sal FROM EMPLOYEES

SELECT * from view_emp1


-- 基于多个表创建视图

CREATE VIEW view_emp2
as 
SELECT EMPLOYEE_ID as id,LAST_NAME as name,SALARY as sal,DEPARTMENT_NAME 
FROM EMPLOYEES e,DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID


SELECT * FROM view_emp2

-- 修改视图 去掉salary

CREATE  or replace VIEW  view_emp2
as 
SELECT EMPLOYEE_ID as id,LAST_NAME as name,DEPARTMENT_NAME 
FROM EMPLOYEES e,DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID

SELECT * from view_emp2


-- 视图改为只读
CREATE  or replace VIEW  view_emp2
as 
SELECT EMPLOYEE_ID as id,LAST_NAME as name,DEPARTMENT_NAME 
FROM EMPLOYEES e,DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
with read only

--  cannot perform a DML operation on a read-only view 不能修改因为该视图只读
UPDATE view_emp2 
set name = 'aaa' 
where id = 200

-- 创建复杂视图 含有分组 函数等  
-- 复杂视图一般不能进行增删改操作
-- 创建各个部门平均工资视图 
create or replace view view_emp3
as
SELECT d.DEPARTMENT_NAME as dept_name, avg(e.SALARY) as avg_sal
FROM DEPARTMENTS d,EMPLOYEES e
WHERE d.DEPARTMENT_ID = e.EMPLOYEE_ID
GROUP BY d.DEPARTMENT_NAME

SELECT * FROM view_emp3
-- ORA-01732: data manipulation operation not legal on this view  非法操作
-- 因为有组函数 而且逻辑不合理 改了平均工资那每个人工资应该多少不好算
UPDATE view_emp3 
set avg_sal = 30000 
where dept_name = 'Treasury'

-- 删除视图 跟删除表一样
drop view_emp3


-- top n 分析
-- 虽然工资是 从高到底  但是 行号没有按顺序排列
SELECT rownum,id,name,sal
FROM view_emp1
where rownum <= 10 
ORDER BY sal desc

-- 整理 效果达到 但是很复杂
SELECT rownum,id,name,sal 
FROM (
			SELECT rownum,id,name,sal
			FROM view_emp1 
			ORDER BY sal desc
		)
WHERE rownum <= 10


-- 注意 对ROWNUM 只能用 < 或 <= ,而用 = , > , >= 都将不能放任何数据

-- 查询工资由高到底 40-50行的数据

SELECT * FROM (
							SELECT rownum as rn,id,name,sal 
							FROM (
										SELECT rownum,id,name,sal
										FROM view_emp1 
										ORDER BY sal desc
									)
							)
WHERE rn <= 50 and rn >= 40 

-- 分页查询
--查询员工表当中 salary前10的员工信息
 
SELECT * FROM (
							select rownum as rn ,LAST_NAME,SALARY 
							FROM (
										select LAST_NAME,SALARY 
										from EMPLOYEES 
										ORDER BY SALARY desc
										)
							)
WHERE rn <= 10 

-- 查询员工表当中 salary 10-20 的员工信息

SELECT * FROM (
							select rownum as rn ,LAST_NAME,SALARY 
							FROM (
										select LAST_NAME,SALARY 
										from EMPLOYEES 
										ORDER BY SALARY desc
										)
							)
WHERE rn >= 10 and rn <= 20


-- 分页查询  0 - 10   10 - 20   
/**
					0										10
	 RN <= (1-1) * 10 AND RN >= 1 * 10
					10									20
	 RN <= (2-1) * 10 AND RN >= 2 * 10
	 
*/
SELECT * FROM (
							SELECT ROWNUM AS RN,LAST_NAME,SALARY 
							FROM ( 
										SELECT LAST_NAME,SALARY 
										FROM EMPLOYEES
										ORDER BY SALARY DESC
										) 
							)
WHERE RN >= (pageNum-1) * pageSize  AND RN <= pageNum * pageSize


-- 查询第5页 每页10条记录

SELECT * FROM (
							SELECT ROWNUM AS RN,LAST_NAME,SALARY 
							FROM ( 
										SELECT LAST_NAME,SALARY 
										FROM EMPLOYEES
										ORDER BY SALARY DESC
										) 
							)
WHERE RN >= (5-1) * 10  AND RN <= 5 * 10


-- 练习

-- 1.	使用表employees创建视图employee_vu，其中包括姓名（LAST_NAME），员工号（EMPLOYEE_ID），部门号(DEPARTMENT_ID).

CREATE OR REPLACE VIEW employee_vu
AS 
SELECT LAST_NAME,EMPLOYEE_ID,DEPARTMENT_ID 
FROM EMPLOYEES

-- 3.	查询视图中的全部内容
SELECT * FROM employee_vu 

-- 2.	显示视图的结构

-- desc employee_vu;

-- 4.	将视图中的数据限定在部门号是80的范围内

CREATE OR REPLACE VIEW employee_vu 
AS 
SELECT LAST_NAME,EMPLOYEE_ID,DEPARTMENT_ID 
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80

-- 5.	将视图改变成只读视图

CREATE OR REPLACE VIEW employee_vu 
AS 
SELECT LAST_NAME,EMPLOYEE_ID,DEPARTMENT_ID 
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
WITH READ ONLY