Oracle学习——视图、序列、索引

 　　本文将介绍Orcale数据库中的视图，序列，索引这三个数据库对象。

1、视图

　　视图数基于表或另一个视图的逻辑表，一个视图并不包含它自己的数据，它好比是一个窗口，通过该窗口可以查看或改变表中的数据。视图基于其上的表称为基表。

　　视图的作用：

• 限制数据的访问，因为视图能够选择性的显示表中的列。
• 视图可以用来构成简单的查询以及取回复杂查询的结果，视图能用于从多表中查询信息，而用户不必知道怎样写连接语句
• 视图对于特别的用户和应用程序提供数据独立性，一个视图可以从几个表中取回数据

　　视图的类型包括有简单视图和复杂视图，区别如下：

　　简单视图：

　　　　- 数据仅来自于一个表

　　　　- 不包含函数或数据分组

　　　　- 能够通过视图来执行DML操作（增删改）

　　复杂视图：

　　　　- 数据来自多个表

　　　　- 包含函数或数据分组

　　　　- 不允许通过视图来执行DML操作

　1.1、创建简单视图

语法结构：

CREATE VIEW 视图名称
AS 查询子句

　　创建视图的过程中，如果查询子句中使用了列别名，那么在创建的视图中这个列别名将作为视图的别名。

使用视图时的语法结构：

SELECT * FROM 视图名

　　当我们使用视图，只能对视图中包含的列做列投影。当使用视图时，其实就是在执行视图中AS后面的查询子句，不过我们将其封装好了，不将查询逻辑展示出来。

　1.2、创建复杂视图

语法结构：

CREATE VIEW 视图名(列名1, 列名2, ...)
AS 复杂查询子句

示例：创建一个视图，包含每个部门的部门名称(在departments表中)，部门最低薪水(在employees表中)，部门最高薪水以及部门的平均薪水。

CREATE VIEW my_view(dep_name, min_sal, max_sal, avg_sal)
AS SELECT d.department_name, min(e.salary), max(e.salary), avg(e.salary)
   FROM departments d, employees e
   WHERE d.department_id=e.department_id
   GROUP BY d.department_name

　1.3、视图中执行DML操作

　　我们只能对简单视图做DML操作，并且还有一些更具体的操作规则，如下

　　1、包含组函数的视图不能做DML操作

　　2、包含GROUP BY子句的视图不能做DML操作

　　3、包含DISTINCT关键字的视图不能做DML操作

　　4、包含有用表达式定义的列的视图不能做DML操作

示例：删除雇员ID为100的雇员

DELETE FROM emp80 e WHERE e.department_id=100

　1.4、在创建视图时设定权限

　　有时我们创建了一个简单视图（或者复杂视图），并且不希望有别人能通过这个视图去执行DML操作，那么我们可以在创建视图时，使用关键字来设定视图的权限。

CREATE VIEW 视图名(列名1, 列名2, ...)
AS 复杂查询子句
WITH READ ONLY

　1.5、删除视图

DROP VIEW 视图名

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

　1.6、内建视图

　　内建视图是一个带有别名（或相关名）的可以在SQL语句中使用的子查询。一个主查询的在FROM子句中指定的子查询就是一个内建视图。

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

　　示例：显示那些雇员低于他们部门最高薪水的雇员的名字，薪水、部门号和他们部门最高的薪水。

SELECT em.name, em.salary, em.department_id, ed.max_sal
FROM employees em, (SELECT max(e.salary) max_sal, department_id
                    FROM employees e 
                    GROUP BY e.department_id) ed
WHERE em.department_id=ed.department_id
AND em.salary < ed.max_sal

　　创建一个内建视图包括了部门id和部门最高薪水，将这个内建视图与雇员表进行内连接。

　1.7、Top-N分析

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

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

　　其实Top-N分析就是在排名。因此Top-N分析需要具有以下的查询结构：

　　1、子查询或者内建视图产生数据的排序列表，该子查询或者内建视图包含有ORDER BY子句来确保排序以想要的顺序排列（可能会使用到DESC）。

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

　　　　- ROWNUM（关键字）伪列，从子查询返回的每一行指定一个从1开始的连续的值

　　　　- 一个WHERE子句，它指定杯返回的n行，外WHERE子句必须使用一个 < 或者 <= 操作

示例一：从employees表中显示挣钱最多的3个人的名字以及薪水

SELECT rownum, last_name, salary
FROM (SELECT salary, last_name 
      FROM employees 
      ORDER BY salary DESC)
WHERE rownum <= 3

示例二：显示公司中资历最老的3名员工名称，和入职时间。

SELECT rownum, last_name，hire_date
FROM (SELECT last_name, hire_date 
      FROM employees 
      ORDER BY hire_date)
WHERE rownum <= 3

　　其实我们排序的逻辑是在内建视图中完成的，而外部的主查询主要就是选择显示什么，以及显示多少条。

　1.8、Oracle的分页查询

　　当查询的结果集过大时，可能会导致各种问题，比如资源内存被耗尽，数据传输超时。因此我们可以使用”分页查询“的手段，也就是不要一次性查询所有的数据，每次只查询一部分数据，分批次的处理大块数据。

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

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

SELECT rownum, *
FROM (SELECT *
      FROM employees)
WHERE rownum >=1 AND rownum<= 10

2、序列

　　序列是用户创建的数据库对象，序列会产生唯一的整数。序列的一个典型用途就是创建一个主键的值，它对于每一行必须是唯一的。序列由Oracle内部程序产生并增加或减少。

　　序列是一个节省时间的对象，因为它可以减少应用程序中产生序列代码量（在多线程程序中生成主键数据再插入到数据库中可能会出现问题）。序列号独立于表被单独储存和产生，因此，相同的序列可以被多个表使用（如果多个表使用同一序列那么可能出现某一张表中主键可能会不连续）。

　　注意：MySQL是没有序列对象的。

　2.1、创建序列

语法结构：

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

参数介绍：

• INCREMENT BY n ：指定序列之间的间隔，n为一个整数，表示序列增量（默认1）
• START WITH n ：指定序列开始的序列值（默认1）
• MAXVALUE n（NOMAXVALUE）：指定序列能产生最大的值，对于升序序列指定10^27为最大值，降序序列为-1为最大值
• MINVALUE n （NOMINVALUE）：指定序列能产生的最小值，对于升序序列指定1为最小值，降序序列为-10^26为最小值
• CYCLE （NOCYCLE）：指定序列达到最大值或者最小值之后，是否继续产生。默认是NOCYCLE：不再继续产生。
• CACHE n （NOCACHE）：Oracle服务器预先分配多少值，并且保持再内存中。

　2.2、操作序列（查询和使用）

　　查询序列语法结构：

SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences

　　其实用户创建的每一个序列都存储在user_sequences这个数据字典中，我们可以通过检索这个表来查看现存的序列，以及它们的配置。

　　使用序列：

　　在Oracle中由NEXTVAL和CURRVAL这两个伪列（保留字）用来提取序列的下一个可用的序列值和当前的序列值，需要注意的是新创建的序列需要先使用NEXTVAL才能使用CURRVAL。使用这两个关键字的时候采用：序列名.NEXTVAL或者序列名.CURRVAL。

示例：在department这个表中，插入一个locationID为2500的新的部门，名称是Support。

INSERT INTO department(department_id, department_name, location_id)
VALUES(dep_seq.nextval, 'Support', 2500)

　　其中dep_seq是之前创建好的一个序列。

　2.3、修改与删除序列

修改序列语法：

ALTER SEQUENCE 序列名
      [INCREMENT BY n]
      [{MAXVALUE n | NOMAXVALUE}]
      [{MINVALUE n | NOMINVALUE}]
      [{CYCLE | NOCYCLE}]
      [{CACHE n | NOCACHE}]

修改序列原则：

• 修改者必须对被修改序列具有ALTER权限。
• ALTER SEQUENCE语句，只有以后的序列数受到影响。
• START WITH选项不能被修改，如果想重新计数，则只能删除再重新创建

删除序列语法：

DROP SEQUENCE 序列名

3、索引

　　关系型数据库中索引是一种单独的、物理的对数据库中一列或多列的值进行排序的一种储存结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于目录。

　　索引由Oracle服务器自动使用和维护，索引逻辑地和物理地独立于他们索引的表，这意味着索引可以在任何时间被创建和删除，并且并不影响基表或其他索引。当删除表时，对应的索引也会被删除。

　　索引类型：常见数据库中使用的索引类型包括有B树索引和位图索引。B树索引就是根据该列的值构造排序二叉树，从而提高查询效率。位图索引则常适用于某些列包含少量值却重复有多行的数据表（低基数），比如一张销售表中产品名这一列可能只有几个产品值，但每一种产品都会重复有多行，此时采用位图索引就会更好（很显然位图索引不会使用在主键列中）。

　3.1、使用索引

　　在表上建立更多的索引并补意味着能更快的查询，因为带索引的表上执行每个DML操作，服务器都会对索引进行更新，与表连接的索引越多，对Oracle数据库的影响也就越大。因此我们创建所以常遵循以下原则：

• 一列包含一个大范围的值
• 一列包含有很多空值
• 一列或者多列经常会同时在一个WHERE子句中或者一个连接条件中被使用
• 表很大，并且经常的查询期望取回的数少于4%的行

创建索引的语法：

CREATE [UNIQUE] INDEX 索引名
ON 表名(列名1，[列名2，...])

　　如果使用UNIQUE关键字则会创建一个唯一索引，该索引相当于对索引列增加了一个唯一性约束。如果表名后跟有多列则表示创建的索引是多列索引，多列索引需要注意的是考虑哪一列作为第一列哪一列作为第二列，在查询中也需要遵循这样的顺序，特别是如果查询中只给定第二列值则该索引并没有起作用（因为会服务器会浏览所有的第一列的值再确定第二列）。