Oracle-视图

• 基表中的数据和视图中的数据会被互相同步

 

一：为用户授予创建视图的权限

grant create view to xxx;

二：创建视图

CREATE VIEW empview
AS
SELECT e.employee_id,e.last_name,e.department_id,e.salary,e.email,d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id = 80;

三：更新和删除视图中记录

更新

UPDATE empview e SET e.salary = 1100 WHERE e.employee_id = 145; 

删除 

DELETE FROM empview 
WHERE empview.employee_id = 158;

四：修改视图

• 只需要在create后面加上 or replace 就可以了，其他的和创建表的同。意思就是视图存在时替换，不存在时创建

CREATE OR REPLACE VIEW empview
AS
SELECT e.employee_id,e.last_name,e.department_id,e.salary,e.email,d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id = 80

五：with read only

• 当创建视图是加上with read only则视图只允许查询

CREATE OR REPLACE VIEW empview
AS
SELECT e.employee_id,e.last_name,e.department_id,e.salary,e.email,d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id = 80
WITH READ ONLY;

六：复杂视图（带分组函数）

• 复杂视图只能查询，不能修改

CREATE OR REPLACE VIEW empview1
AS
SELECT d.department_name,AVG(e.salary) avg_salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_name;

七：用途

• 经常使用的查询语句创建视图，相当于对高频使用的sql的封装
• 当不想把一张表中的所有数据暴露给别人，可以创建视图包含表中部分数据交给别人操作

八：TOP-N

场景：查询员工表中工资前十的员工

• ROWNUM：虚拟列，标识当前列序号（ROWNUM的筛选只能使用'<'或'<='不能使用'>'、'>='、'='）
• 将需要的字段查询出来并按工资排序，作为一个子表
• 子表中的记录就是按照工资排序的，行号的前十个就是工资最高的十个记录
• 查询时按照子表的前十个查询即可

SELECT ROWNUM,last_name,salary
FROM(
       SELECT e.last_name,e.salary
       FROM employees e
       ORDER BY e.salary DESC
)
WHERE ROWNUM <= 10;

场景：查询员工表中按工资排序的第40到50条记录

• 由于ROWNUM不能用‘>’做比较，所以只能将上面查询出来的结果作为子表，将查询出来的记录的ROWNUM取别名，再嵌套一次查询，

SELECT ROWNUM nu,employee_id,last_name,salary
FROM(
       SELECT ROWNUM nu,employee_id,last_name,salary
       FROM(
		SELECT e.employee_id,e.last_name,e.salary
		FROM employees e
		ORDER BY e.salary DESC
       )
)
WHERE nu > 40 AND nu < 50;