Oracle视图，序列，索引,约束

视图

什么是视图

视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。
视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

1. CREATE[ORREPLACE]VIEW view_name[(alias[, alias…])]
2. ASsubquery ;

视图创建后，可以像操作表一样操作视图，主要是查询操作。
语法中的Subquery是SELECT查询语句，对应的表被称作基表。
根据视图所对应的子查询种类分为几种类型:

SELECT语句是基于单表建立的，且不包含任何函数运算、表达式或分组函数，叫做简单视图，此时视图是基表的子集；
SELECT语句同样是基于单表，但包含了单行函数、表达式、分组函数或GROUP BY子句，叫做复杂视图；
SELECT语句是基于多个表的，叫做连接视图。

视图的作用

如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可， 简化复杂查询；
视图本质上就是一条SELECT语句，所以当访问视图时，只能访问到所对应的SELECT语句中涉及到的列，对 基表中的其它列起到安全和保密的作用，可以限制数据访问。

创建简单视图（单表）

创建一个简单视图V_EMP_10，来显示部门10中的员工的编码、姓名和薪水：

1. CREATEVIEW v_emp_10
2. AS
3. SELECTempno, ename, sal, deptno
4. FROM emp
5. WHEREdeptno = 10;

查看视图结构：

1. DESCv_emp_10;

对视图进行INSERT操作

视图本身并不包含数据，只是基表数据的逻辑映射。所以当对视图执行DML操作时，实际上是对基表的DML操作。对视图执行DML操作的基本原则：

简单视图能够执行DML操作，下列情况除外：在基表中定义了非空列，但简单视图对应的SELECT语句并没有包含这个非空列，导致这个非空列对视图不可见，这时无法对视图执行INSERT操作；
如果视图定义中包含了函数、表达式、分组语句、DISTINCT关键字或ROWNUM伪列，不允许执行DML操作；
DML操作不能违反基表的约束条件。

 创建具有CHECK OPTION约束的视图

WITH CHECK OPTION短语表示，通过视图所做的修改，必须在视图的可见范围内：

假设INSERT，新增的记录在视图仍可查看
假设UPDATE，修改后的结果必须能通过视图查看到
假设DELETE，只能删除现有视图里能查到的记录

1. CREATE OR REPLACEVIEW v_emp_10
2. AS
3. SELECTempno id,ename name,sal salary, deptno
4. FROM emp
5. WHEREdeptno = 10
6. WITHCHECK OPTION;

创建具有READ ONLY约束的视图

对简单视图进行DML操作是合法的，但是不安全的。如果没有在视图上执行 DML 操作的必要，在建立视图时声明为只读来避免这种情况，保证视图对应的基表数据不会被非法修改。加入READ ONLY约束的视图语法如下：

1. CREATE OR REPLACEVIEW v_emp_10
2. AS
3. SELECTempno, ename, sal, deptno FROM emp
4. WHEREdeptno = 10
5. WITHREAD ONLY;

通过查询USER_VIEWS获取相关信息

和视图相关的数据字典有：
USER_OBJECTS
USER_VIEWS
USER_UPDATABLE_COLUMNS

例一：在数据字典USER_OBJECTS中查询所有视图名称：

1. SELECTobject_name FROM user_objects
2. WHEREobject_type = 'VIEW';

例二：在数据字典USER_VIEWS中查询指定视图：

1. SELECT textFROM user_views
2. WHEREview_name = 'V_EMP_10';

例三：在数据字典USER_UPDATABLE_COLUMNS中查询视图：

1. SELECTcolumn_name, insertable, updatable, deletable
2. FROMuser_updatable_columns
3. WHEREtable_name = 'V_EMP_10';

创建复杂视图（多表关联）

复杂视图指在子查询中包含了表达式、单行函数或分组函数的视图。此时必须为子查询中的表达式或函数定义别名。
例如，创建一个视图V_EMP_SALARY，把职员表的数据按部门分组，获得每个部门的平均薪水、薪水总和、最高薪水和最低薪水：

1. CREATEVIEW v_emp_salary
2. AS
3. SELECTd.dname,avg(e.sal) avg_sal,sum(e.sal) sum_sal,
4. max(e.sal) max_sal,min(e.sal) min_sal
5. FROM emp e join dept d
6. ONe.deptno= d.deptno
7. GROUPBY d.dname;

序列

什么是序列

序列(SEQUENCE)是一种用来生成唯一数字值的数据库对象。序列的值由Oracle程序按递增或递减顺序自动生成，通常用来自动产生表的主键值，是一种高效率获得唯一键值的途径。

序列是独立的数据库对象，和表是独立的对象，序列并不依附于表。

通常情况下，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值。

创建序列

1. CREATE SEQUENCE [schema.]sequence_name
2.     [ START WITH i ] [ INCREMENT BY j ]
3.     [ MAXVALUE m | NOMAXVALUE ]
4.     [ MINVALUE n | NOMINVALUE ]
5.     [ CYCLE | NOCYCLE ][ CACHE p | NOCACHE ]

其中：
sequence_name是序列名，将创建在schema方案下
序列的第一个序列值是i，步进是j
如果j是正数，表示递增，如果是负数，表示递减
序列可生成的最大值是m，最小值是n
如果没有设置任何可选参数，序列的第一个值是1，步进是1
CYCLE表示在递增至最大值或递减至最小值之后是否继续生成序列号，默认是NOCYCLE
CACHE用来指定先预取p个数据在缓存中，以提高序列值的生成效率，默认是20

使用序列

创建一个序列，起始数据是100，步进是10：

1. CREATE SEQUENCE emp_seq
2. START WITH 100
3. INCREMENT BY 10;

当序列被创建后，第一个序列值将是100，将要生成的序列号分别是110、120、130等。
序列中有两个伪列：

NEXTVAL：获取序列的下个值
CURRVAL：获取序列的当前值

当序列创建以后，必须先执行一次NEXTVAL，之后才能使用CURRVAL。
获取序列的第一个值，并且使用序列值为EMP表插入新的记录：

1. SELECT emp_seq.NEXTVAL FROM DUAL;
2. INSERT INTO emp(empno, ename)
3. VALUES(emp_seq.NEXTVAL, 'donna');

索引

索引的原理

索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是独立于表的对象，可以存放在与表不同的表空间（TABLESPACE）中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。
索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引，是一种提高查询效率的机制。

ROWID: 伪列，唯一标识一条数据记录，可理解为行地址。

1. CREATE [UNIQUE] INDEX index_name
2.         ON table(column[, column…]);

其中：
index_name表示索引名称
table表示表名
column表示列名，可以建立单列索引或复合索引
UNIQUE表示唯一索引
在EMP表的ENAME列上建立索引：

1. CREATE INDEX idx_emp_ename ON emp(ename);

复合索引也叫多列索引，是基于多个列的索引。如果经常在ORDER BY子句中使用job和salary作为排序依据，可以建立复合索引：

1. CREATE INDEX idx_emp_job_sal ON emp(job, sal);

当做下面的查询时，会自动应用索引idx_emp_job_sal

1. SELECT empno, ename, sal, job FROM emp
2. ORDER BY job, sal;

创建基于函数的索引

如果需要在emp表的ename列上执行大小写无关搜索，可以在此列上建立一个基于UPPER函数的索引:

1. CREATE INDEX emp_ename_upper_idx
2. ON emp(UPPER(ename));

当做下面的查询时，会自动应用刚刚建立的索引:

1. SELECT * FROM emp
2. WHERE UPPER(ename) = 'KING';

 修改和删除索引

如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间利用率，语法如下：

ALTER INDEX index_name REBUILD;

重建索引idx_emp_ename：

ALTER INDEX idx_emp_ename REBUILD;

当一个表上有不合理的索引，会导致操作性能下降，删除索引的语法：

DROP INDEX index_name;

合理使用索引提升查询效率

为提升查询效率，创建和使用索引的原则：
为经常出现在WHERE子句中的列创建索引
为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
为经常作为表的连接条件的列上创建索引
不要在经常做DML操作的表上建立索引
不要在小表上建立索引
限制表上的索引数目，索引并不是越多越好
删除很少被使用的、不合理的索引

约束

约束概述

约束的作用

约束（CONSTRAINT）的全称是约束条件，也称作完整性约束条件。约束是在数据表上 强制执行的一些数据校验规则，当执行DML操作时，数据必须符合这些规则，如果不符合则无法执行。
约束条件可以 保证表中数据的完整性，保证数据间的商业逻辑。

约束的类型

约束条件包括：

• 非空约束(Not Null)，简称NN
• 唯一性约束(Unique)，简称UK
• 主键约束(Primary Key)，简称PK
• 外键约束(Foreign Key)，简称FK
• 检查约束(Check)，简称CK

非空约束

建表时添加非空约束

非空约束用于确保字段值不为空。默认情况下，任何列都允许有空值，但业务逻辑可能会要求某些列不能取空值。当某个字段被设置了非空约束条件，这个字段中必须存在有效值，即：
当执行INSERT操作时，必须提供这个列的数据
当执行UPDATE操作时，不能给这个列的值设置为NULL
建表时添加非空约束：

1. CREATE TABLE employees (
2. eid NUMBER(6),
3. name VARCHAR2(30) NOT NULL,
4. salary NUMBER(7, 2),
5. hiredate DATE
6. CONSTRAINT employees_hiredate_nn NOT NULL
7. );

可以在建表之后，通过修改表的定义，添加非空约束：

1. ALTER TABLE employees
2. MODIFY (eid NUMBER(6) NOT NULL);

如果业务要求取消某列的非空约束，可以采用重建表或者修改表的方式：

1. ALTER TABLE employees
2. MODIFY (eid NUMBER(6));

唯一性约束

唯一性(Unique)约束条件用于 保证字段或者字段的组合不出现重复值。当给表的某个列定义了唯一约束条件，该列的值不允许重复，但允许是NULL值。
唯一性约束条件可以在建表同时建立，也可以在建表以后再建立。

在建表employees的同时，在eid、email列上创建唯一约束条件，并在建表后在name列上建立一个名为employees_name_uk的唯一约束条件：

1. DROP TABLE employees ; --将表删掉重新创建
2. CREATE TABLE employees (
3. eid NUMBER(6) UNIQUE,
4. name VARCHAR2(30),
5. email VARCHAR2(50),
6. salary NUMBER(7, 2),
7. hiredate DATE,
8. CONSTRAINT employees_email_uk UNIQUE(email)
9. );

在建表之后增加唯一性约束条件：

1. ALTER TABLE employees
2. ADD CONSTRAINT employees_name_uk UNIQUE(name);

主键约束

主键(Primary Key)约束条件从功能上看 相当于非空（NOT NULL）且唯一（UNIQUE）的组合。主键字段可以是单字段或多字段组合，即：在主键约束下的单字段或者多字段组合上不允许有空值，也不允许有重复值。
主键可以用来在表中唯一的确定一行数据。 一个表上只允许建立一个主键，而其它约束条件则没有明确的个数限制。

主键选取的原则

主键应是对系统无意义的数据
永远也不要更新主键，让主键除了唯一标识一行之外，再无其他的用途
主键不应包含动态变化的数据，如时间戳
主键应自动生成，不要人为干预，以免使它带有除了唯一标识一行以外的意义
主键尽量建立在单列上

添加主键约束

在建表时添加主键约束条件：

1. CREATE TABLE employees2 (
2. name VARCHAR2(30),
3. email VARCHAR2(50),
4. salary NUMBER(7, 2),
5. hiredate DATE
6. );

建表后创建主键约束条件，并自定义约束条件名称：

1. CREATE TABLE employees3 (
2. eid NUMBER(6),
3. name VARCHAR2(30),
4. email VARCHAR2(50),
5. salary NUMBER(7, 2),
6. hiredate DATE
7. );
9. ALTER TABLE employees3
10. ADD CONSTRAINT
11. employees3_eid_pk PRIMARY KEY (eid);

外键约束

外键约束条件定义在 两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系。比如 emp表的deptno列参照dept表的deptno列，则dept称作主表或父表，emp表称作从表或子表。

先建表，在建表后建立外键约束条件：

1. CREATE TABLE employees4 (
2. eid NUMBER(6),
3. name VARCHAR2(30),
4. salary NUMBER(7, 2),
5. deptno NUMBER(4)
6. );
8. ALTER TABLE employees4
9. ADD CONSTRAINT employees4_deptno_fk
10. FOREIGN KEY (deptno) REFERENCES dept(deptno);

外键约束对一致性的维护

外键约束条件包括两个方面的数据约束：
从表上定义的外键的列值，必须从主表被参照的列值中选取，或者为NULL；
当主表参照列的值被从表参照时，主表的该行记录不允许被删除。

1. --成功DML语句：
2. INSERT INTO employees4(eid, name, deptno)
3. VALUES(1234, ‘rose tyler’, 40);--成功
4. INSERT INTO employees4(eid, name, deptno)
5. VALUES(1235, ‘martha jones’, NULL); --成功
7. --失败DML语句：
8. INSERT INTO employees4(eid, name, deptno)
9. VALUES(1236, 'donna noble', 50);
10. --失败，不存在部门50
11. DELETE FROM dept WHERE deptno = 40;
12. --失败，40被参照，不允许删除

外键约束对性能的降低

如果在一个频繁DML操作的表上建立外键，每次DML操作，都将导致数据库自动对外键所关联的对应表做检查，产生开销，如果已在程序中控制逻辑，这些判断将增加额外负担，可以省去。
另外外键确定了主从表的先后生成关系，有时会影响业务逻辑。

关联不一定需要外键约束

如果业务逻辑要求保证数据完整性，可由程序或触发器控制，不一定需要外键约束。

另外为了简化开发，维护数据时不用考虑外键约束，以及大量数据DML操作时不需考虑外键耗费时间。

检查约束

检查(Check)约束条件用来强制在字段上的 每个值都要满足Check中定义的条件。当 定义了Check约束的列新增或修改数据时，数据必须符合Check约束中定义的条件。

添加检查约束

员工的薪水必须大于2000元，增加检查约束：

1. ALTER TABLE employees4
2. ADD CONSTRAINT employees4_salary_check
3. CHECK (salary > 2000);

当插入大于2000的数据，操作成功：

1. INSERT INTO employees4(eid, name, salary, deptno)
2. VALUES(1236, 'donna noble', 2500, 40);