视图-Oracle高级知识(1)-资料库（21）
这篇文章算是Oracle资料库的高级知识：视图(View)）是非常重要的内容。知识点繁杂，靠理解、实战和笔记来熟练运用。
1 视图(View)
1.1 什么是视图
可以通过创建表的视图来表现数据的逻辑子集或数据的组合。视图是基于表或另一个视
图的逻辑表，一个视图并不包含它自己的数据，它象一个窗口，通过该窗口可以查看或改变
表中的数据。视图基于其上的表称为基表。
1.2 视图的优越性
? 视图限制数据的访问，因为视图能够选择性的显示表中的列。
? 视图可以用来构成简单的查询以取回复杂查询的结果。例如，视图能用于从多表中查
询信息，而用户不必知道怎样写连接语句。
? 视图对特别的用户和应用程序提供数据独立性，一个视图可以从几个表中取回数据。
1.3 视图类型
简单视图和复杂视图
1.4 简单视图 VS 复杂视图
视图有两种分类：简单和复杂，基本区别涉及 DML (INSERT、UPDATE 和 DELETE)
操作。
简单视图：
? 数据仅来自一个表
? 不包含函数或数据分组
? 能通过视图执行 DML 操作
复杂视图：
? 数据来自多个表
? 包含函数或数据分组
? 不允许通过视图进行 DML 操作。
1.5 创建简单视图。
1.5.1 示例
创建一个视图，视图中包含部门 id 为 80 的员工的 id，名字以及薪水。
create view emp80 as select
e.employee_id,e.last_name,e.salary from
employees e;
1.6 用子查询中的列别名创建视图
如果在创建视图的查询语句中含有列别名，那么列别名将作为视图的列名。
1.6.1 示例
创建一个视图，包含部门 id 为 50 的员工 id 使用 ID_NUMBER 命名该列，包含员工名
字使用 NAME 命名该列，包含员工的年薪使用 ANN_SALARY 命名该列。
create view emp50 as select e.employee_id id_number,e.last_name name,12*e.salary
ann_salary from employees e;
1.7 从视图中取回数据
1.7.1 示例一
查询部门 id 为 80 的员工信息，包含他们的 id，名字以及薪水。
select * from emp80;
1.7.2 示例二
查询部门 id 为 50 的员工信息，包含他们的 id 和薪水。
select e.id_number,e.ann_salary from emp50 e;
1.8 创建复杂视图
1.8.1 示例
创建一个视图，包含每个部门的部门名称，部门最低薪水、部门最高薪水以及部门的平
均薪水。
create view dept_name as select d.department_name,min(e.salary)
min ,max(e.salary) max ,avg(e.salary)avg from employees e, departments d where
e.department_id = d.department_id group by d.department_name;
1.9 在定义视图时指定列名
1.9.1 示例
创建一个视图，包含每个部门的部门名称、部门最低薪水、部门最高薪水以及部门的平
均薪水。将部门名称命名为 name、最低薪水命名为 minsal、最高薪水命名为 maxsal、平
均薪水命名为 avgsal。
create view dept_name1(name,minsal,maxsal,avgsal) as
select d.department_name,min(e.salary) ,max(e.salary) ,avg(e.salary)avg from
employees e,departments d where e.department_id = d.department_id group by
d.department_name;
1.10 视图中 DML 操作的执行规则
如果视图中包含下面的部分就不能修改数据：
? 组函数
? GROUP BY 子句
? DISTINCT 关键字
? 用表达式定义的列
1.10.1 示例
删除 emp80 视图中雇员 ID 为 190 的雇员。
delete from emp80 e where e.employee_id = 190;
1.11 拒绝 DML 操作
1.11.1 示例
创建一个简单视图，包含 employees 表中的所有数据，单该视图拒绝 DML 操作。
create view v_emp as select * from employees with read only;
1.12 通过工具创建视图
1.13 删除视图
删除视图不会丢失数据，因为视图是基于资料库中的基本表的。
1.13.1 示例
删除名称为 emp90 的视图。
drop view emp90;
1.14 内建视图
1.14.1 是什么内建视图
? 内建视图是一个带有别名 (或相关名) 的可以在 SQL 语句中使用的子查询。
? 一个主查询的在 FROM 子句中指定的子查询就是一个内建视图。
内建视图：内建视图由位于 FROM 子句中命名了别名的子查询创建。该子查询定义一
个可以在主查询中引用数据源。
1.14.1.1 示例
显示那些雇员低于他们部门最高薪水的雇员的名字、薪水、部门号和他们部门最高的薪水
select
em.last_name,em.salary,em.department_id,e.maxsal from employees em ,(select
e.department_id, max(e.salary) maxsal from employees e group by
e.department_id)e whereem.department_id = e.department_id and
em.salary < e.maxsal;
1.15 Top-N 分析
1.15.1 什么是「Top-N」 分析
Top-N 查询在需要基于一个条件，从表中显示最前面的 n 条记录或最后面的 n 条记
录时是有用的。该结果可以用于进一步分析，例如，用 Top-N 分析你可以执行下面的查询
类型：
? 在中挣钱最多的三个人
? 公司中最新的四个成员
? 销售产品最多的两个销售代表
? 过去 6 个月中销售最好的 3 种产品
1.15.2 执行「Top-N」 分析
Top-N 查询使用一个带有下面描述的元素的一致的嵌套查询结构：
?子查询或者内建视图产生数据的排序列表，该子查询或者内建视图包含 ORDER BY 子
句来确保排序以想要的顺序排列。为了取回最大值，需要用 DESC 参数。
? 在最后的结果集中用外查询限制行数。外查询包括下面的组成部分：
? ROWNUM 伪列，它为从子查询返回的每一行指定一个从 1 开始的连续的值
? 一个 WHERE 子句，它指定被返回的 n 行，外 WHERE 子句必须用一个<或
者<=操作。
1.15.3 示例一
从 EMPLOYEES 表中显示挣钱最多的 3 个人的名字及其薪水。
select rownum ,last_name,salary from (select last_name, salary from employees order
by salary desc) where rownum <=3;
1.15.4 示例二
显示公司中 4 个资格最老的雇员显示他们的入职时间与名字。
select rownum, e.last_name,e.hire_date from (select last_name, hire_date from
employees order by hire_date) e where rownum <= 4;
1.16 Oracle 的分页查询
1.16.1 什么是分页查询
当查询的结果集数据量过大时，可能会导致各种各样的问题发生，例如：伺服器资源被
耗尽，因数据传输量过大而使处理超时，等等。最终都会导致查询无法完成。解决这个问题
的一个策略就是「分页查询」，也就是说不要一次性查询所有的数据，每次只查询一部分数
据。这样分批次地进行处理，可以呈现出很好的用户体验，对伺服器资源的消耗也不大。
分页查询原则：
在内建视图中通过 rownum 伪劣值的判断来指定获取数据的数量。
1.16.2 示例
查询雇员表中数据，每次只返回 10 条数据。
select * from (select rownum rn,e.* from employees e) em where em.rn between 11
and 20;
推荐阅读：
