视图

视图

视图

可以通过创建表的视图来表现数据的逻辑子集或数据的组合。视图是基于表或另一个视 图的逻辑表，一个视图并不包含它自己的数据，它象一个窗口，通过该窗口可以查看或改变 表中的数据。视图基于其上的表称为基表。

视图的优越性

• 视图限制数据的访问，因为视图能够选择性的显示表中的列。

• 视图可以用来构成简单的查询以取回复杂查询的结果。例如，视图能用于从多表中查 询信息，而用户不必知道怎样写连接语句。

• 视图对特别的用户和应用程序提供数据独立性，一个视图可以从几个表中取回数据。

视图类型

简单视图和复杂视图

特性	简单视图	复杂视图
表的数目	一个	一个或多个
包含函数	无	有
包含数据分组	无	有
通过视图进行DML操作	是	不允许

视图有两种分类：简单和复杂，基本区别涉及 DML(INSERT、UPDATE 和 DELETE) 操作。

简单视图：

• 数据仅来自一个表

• 不包含函数或数据分组

• 能通过视图执行 DML 操作

复杂视图：

• 数据来自多个表

• 包含函数或数据分组

• 不允许通过视图进行 DML 操作

创建简单视图

创建一个视图，视图中包含部门id=80的员工的id、名字和薪水。

CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

创建一个视图，包含部门 id 为 50 的员工 id 使用 ID_NUMBER 命名该列，包含员工名 字使用 NAME 命名该列，包含员工的年薪使用 ANN_SALARY 命名该列。

/*
创建一个视图，包含部门 id 为 50 的员工 id 使用 ID_NUMBER 命名该列，
包含员工名 字使用 NAME 命名该列，包含员工的年薪使用 ANN_SALARY 命名该列。 
*/
CREATE VIEW empvu50
AS SELECT employee_id ID_NUMBER, last_name NAME, salary * 12 ANN_SALARY
   FROM employees
   WHERE department_id = 50;

从视图中取数据

查询部门 id 为 80 的员工信息，包含他们的 id，名字以及薪水。

SELECT * FROM empvu80;

查询部门 id 为 50 的员工信息，包含他们的 id 和薪水。

SELECT id_number, ann_salary FROM empvu50;

创建复杂视图

创建一个视图，包含每个部门的部门名称，部门最低薪水、部门最高薪水以及部门的平 均薪水。

/*
创建一个视图，包含每个部门的部门名称，部门最低薪水、部门最高薪水以及部门的平均薪水。 
*/
CREATE VIEW v_dept(name, min_sal, max_sal, avg_sal)
AS SELECT dep.department_name, MIN(emp.salary), MAX(emp.salary), AVG(emp.salary)
   FROM employees emp, departments dep
   WHERE emp.department_id = dep.department_id
   GROUP BY department_name;

视图中 DML 操作的执行规则

如果视图中包含下面的部分就不能修改数据：

• 组函数

• GROUP BY 子句

• DISTINCT 关键字

• 用表达式定义的列

语句与表相似。

拒绝DML操作

创建一个简单视图，包含 employees 表中的所有数据，单该视图拒绝 DML 操作。

/*
创建一个简单视图，包含 employees 表中的所有数据，单该视图拒绝 DML 操作。 
*/
CREATE VIEW v_emp
AS SELECT *
   FROM employees
   WITH READ ONLY;

删除视图

删除视图不会丢失数据，因为视图是基于数据库中的基本表的。

删除名称为 V_EMP_90 的视图。

DROP VIEW V_EMP_90;

内建视图

• 内建视图是一个带有别名 (或相关名) 的可以在 SQL 语句中使用的子查询。

• 一个主查询的在 FROM 子句中指定的子查询就是一个内建视图。
  内建视图：内建视图由位于 FROM 子句中命名了别名的子查询创建。该子查询定义一 个可以在主查询中引用数据源。

/*
显示那些雇员低于他们部门最高薪水的雇员的名字、薪水、部门号和他们部门最高的薪水。
*/
SELECT emp.last_name, emp.salary, emp.department_id, max_sal
FROM employees emp,
    (SELECT department_id, MAX(salary) max_sal
    FROM employees
    GROUP BY department_id) memp
WHERE emp.department_id = memp.department_id
    AND emp.salary < memp.max_sal

Top-N 分析

什么是“Top-N”分析

Top-N 查询在需要基于一个条件，从表中显示最前面的 n 条记录或最后面的 n 条记录时是有用的。该结果可以用于进一步分析，例如，用 Top-N 分析你可以执行下面的查询 类型：

• 在中挣钱最多的三个人
• 公司中最新的四个成员
• 销售产品最多的两个销售代表
• 过去 6 个月中销售最好的 3 种产品

执行“Top-N”分析

Top-N 查询使用一个带有下面描述的元素的一致的嵌套查询结构：

• 子查询或者内建视图产生数据的排序列表，该子查询或者内建视图包含 ORDERBY 子 句来确保排序以想要的顺序排列。为了取回最大值，需要用 DESC 参数

• 在最后的结果集中用外查询限制行数。外查询包括下面的组成部分：

  • ROWNUM 伪列，它为从子查询返回的每一行指定一个从 1 开始的连续的值
  • 一个 WHERE 子句，它指定被返回的 n 行，外 WHERE 子句必须用一个<或 者<=操作。

从 EMPLOYEES 表中显示挣钱最多的 3 个人的名字及其薪水。

/*
从 EMPLOYEES 表中显示挣钱最多的 3 个人的名字及其薪水。 
*/
SELECT last_name, salary
FROM
  (SELECT last_name, salary
  FROM employees
  ORDER BY salary DESC)
WHERE rownum <= 3

显示公司中 4 个资格最老的雇员显示他们的入职时间与名字。

/*显示公司中 4 个资格最老的雇员显示他们的入职时间与名字。*/
SELECT *
FROM (SELECT last_name, hire_date
  FROM employees
  ORDER BY hire_date)
WHERE ROWNUM <= 4;

Oracle 的分页查询

什么是分页查询

当查询的结果集数据量过大时，可能会导致各种各样的问题发生，例如：服务器资源被 耗尽，因数据传输量过大而使处理超时，等等。最终都会导致查询无法完成。解决这个问题 的一个策略就是“分页查询”，也就是说不要一次性查询所有的数据，每次只查询一部分数 据。这样分批次地进行处理，可以呈现出很好的用户体验，对服务器资源的消耗也不大。

分页查询原则： 在内建视图中通过 rownum 伪劣值的判断来指定获取数据的数量。

查询雇员表中数据，每次只返回 10 条数据。

/*
查询雇员表中数据，每次只返回 10 条数据。
*/
SELECT * FROM (SELECT ROWNUM rn, e.* FROM employees e) WHERE rn BETWEEN 1 AND 10;

创建序列

通过 DDL 语句创建序列

CREATE SEQUENCE sequence
	[INCREMENT BY n]
	[START WITH n]
	[{MAXVALUE n | NOMAXVALUE}]
	[{MINVALUE n | NOMINVALUE}]
	[{CYCLE | NOCYCLE}]
	[{CACHE n | NOCACHE}]

在语法中：

sequence 是序列发生器的名字

• INCREMENTBY n 指定序列号之间的间隔，在这儿 n 是一个整数 (如果该子句被省 略，序列增量为 1)

• STARTWITH n 指定要产生的第一个序列数 (如果该子句被省略，序列从 1 开始)

• MAXVALUE n 指定序列能产生的最大值 NOMAXVALUE 对于升序序列指定 10^27 为最大值，对于降序序列指定-1 为最大值 (这是默认选项)

• MINVALUE n 指定最小序列值

• NOMINVALUE 对于升序序列指定 1 为最小值，对于降序序列指定-(10^26)为最小值 (这是默认选项)

• CYCLE|NOCYCLE 指定序列在达到它的最大或最小值之后，是否继续产生(NOCYCLE 是默认选项) +

• CACHE n|NOCACHE 指定 Oracle 服务器预先分配多少值，并且保持在内存中(默认 情况下，Oracle 服务器缓冲 20 个值)

创建一个序列名称为：dept_seq,增长间隔为 10，从 120 开始，最大值为 9999，不缓存。 不循环使用。