ORACLE_SQL基础(4)

约束的概念和原则
约束的作用：来防止无效数据输入到表中
约束存储在数据字典中
建议为约束定一个有意义的名字，这样例于管理
使用USER_CONSTRAINTS视图可以查看约束的定义。

CREATE TABLE "TEST"."EMPLOYEES"
( "EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0),
CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL");

PRIMARY KEY约束为表创建一个主键。每个表只能创建一个主键。PRIMARY KEY约束是表中的对行唯一标识的一个列或者多个列的组合，该约束强制列或列组合的唯一性，并且确保作为主键一部分的列不能包含空值。
注：对于一个PRIMARY KEY列UNIQUE索引被自动创建。

FOREIGN KEY，引用完整性约束，指明一个列或者列的组合作为一个外键，并且在相同表或者不同表的主键或者唯一键和外键之间建立一个关系。前提是被引用的外键必须是另外一个表的主键。
一个外键值必须匹配一个在父表中存在的值或者空值。外键基于数据值，并且纯粹是逻辑的，不是物理的，指针。
FOREIGN KEY约束的原则：
外键被定义在子表中，包含引用列的表是父表。外键用下面关键字的组合定义：
FOREIGN KEY 被用于在表约束级定义子表中的列。
REFERENCES 确定父表中的表和列。
ON DELETE CASCADE 指出当父表中的行被删除时，子表中相依赖的行也将被级联删除。
ON DELETE SET NULL 当父表的值被删除时，转换外键值为空。
默认行为被称为约束规则，该规则不允许引用数据的更新或删除。
无ON DELETE CASCADE或ON DELETE SET NULL选项，如果父表中的行在子表中引用，则它不能被删除。

CHECK约束
CHECK约束定义一个每行都必须满足的条件，该条件可以用和查询条件一样的结构，下面的情况例外：
引用CURRVAL, NEXTVAL, LEVEL和ROWNUM伪列。调用SYSDATE, UID, USER和USERENV函数。查询涉及其它行中的其它值。
伪列不是表中实际的列，但它们的行为象列一样，例如，你能从伪列中选择够选择值，可是，你不能插入、更新或从伪列中删除。伪列能被用于SQL语句中。
一个单个列在它的定义中可以有多个CHECK约束，在一个列上能够定义的CHECK 约束的数目无限制。
CHECK约束能够被定义在列级或表级。

管理约束
约束的常用管理操作有：
添加约束
删除约束
禁用、启用约束
在删除对象或在表中删除行时级联约束
添加约束
你可以用带ADD子句的ALTER TABLE语句为已经存在的表添加一个约束。语法：
ALTER TABLE table ADD [CONSTRAINT constraint] type (column);

添加的原则：
你可以添加、删除或禁用一个约束，但你不能修改它的结构。
你可以用ALTER TABLE语句的MODIFY子句添加一个NOT NULL约束到一个已经存在的列。只有在表是空的或者每个行的该列都有非空值的情况下，你才可以定义一个NOT NULL列。
你可以延迟检查约束的有效，直到事务结束。
如果仅在提交的时候进行系统检查，约束将被延迟(deferred)。如果一个延迟的约束被违反，则该提交导致事务回退。
如果在每条语句结束时进行检查，约束是立即的(immediate)。如果该约束被违反，则该语句被立即回退。
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk FOREIGN Key (manager_id)
REFERENCES employees (employee_id);

可以先从USER_CONSTRAINTS和USER_CONS_COLUMNS数据字典视图中确定约束的名字，然后使用带DROP子句的ALTER TABLE语句。DROP子句的CASCADE选项导致任何与其相依赖的约束也被删除。
ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;
ALTER TABLE departments DROP PRIMARY KEY Cascade;

可以在CREATE TABLE语句也可以在ALTER TABLE语句中使用DISABLE子句。CASCADE子句禁用相依赖的完整性约束。禁用唯一或主键约束会移除唯一性索引。
例如：ALTER Table employees DISABLE Constraint emp_emp_id_pk CASCADE;
可以用带ENABLE子句的ALTER TABLE语句启用一个禁用的约束，而不需要重新创建它。
启用一个带CASCADE选项的被禁用的主键约束不会起用任何依赖于该主键的外键。

级联约束
CASCADE CONSTRAINTS子句连同DROP COLUMN子句一起被使用，CASCADE CONSTRAINTS子句删除所有定义在被删除列上的涉及主键和唯一键的引用完整性约束，
CASCADE CONSTRAINTS子句也删除所有定义在被删除列上的多列约束。下面的例子说明级联约束：
建表：
CREATE TABLE test1 (
pk NUMBER PRIMARY KEY,
fk NUMBER,
col1 NUMBER,
col2 NUMBER,
CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test1,
CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),
CONSTRAINT ck2 CHECK (col2 > 0));

ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS;
如果所有由定义在已删除列上的约束引用的列也被删除，那么就不需要CASCADE CONSTRAINTS，例如，假设没有其它表引用PK，提交下面的不带CASCADE CONSTRAINTS子句的语句是恰当的：
ALTER TABLE test1 DROP (pk, fk, col1);
如果任何约束被来自其它表中的列或在目的表中的保留列所引用，那么必须指定CASCADE CONSTRAINTS，否则，该语句异常中断，并且返回错误提示ORA-12991：

查看约束
约束相关的视图有：
- USER_CONSTRAINTS
- USER_CONS_COLUMNS
Select Constraint_Name, Constraint_Type, Search_Condition
From User_Constraints
Where Table_Name = 'EMPLOYEES';
可以用USER_CONS_COLUMNS数据字典视图查看与约束相关的列名。该视图对于那些由系统指定名字的约束特别有用。
Select Constraint_Name, Column_Name
From User_Cons_Columns
Where Table_Name = 'EMPLOYEES';

++++++++++++++++++++++++++++++++++++++
创建视图

视图的两大作用：
- 限制数据访问
- 简化命令文本

视图有两种分类：简单和复杂，基本区别涉及DML (INSERT、UPDATE和DELETE) 操作。
下面是简单视图：数据仅来自一个表，不包含函数或数据分组，能通过视图执行DML操作。
下面是复杂视图：数据来自多个表，包含函数或数据分组，不总是允许通过视图进行DML操作。

创建视图的语法：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
OR REPLACE 如果视图已经存在重新创建它
FORCE 创建视图，而不管基表是否存在
NOFORCE 只在基表存在的情况下创建视图(这是默认值)
subquery 是一个完整的SELECT语句(对于在SELECT列表中的字段你可以用别名)
WITH CHECK OPTION 指定只有可访问的行在视图中才能被插入或修改
constraint 为CHECK OPTION约束指定的名字
WITH READ ONLY 确保在该视图中没有DML操作被执行

创建视图的原则：
定义一个视图的子查询可以包含复杂的SELECT语法，包括连分组和多个字查询。
定义视图的子查询不能包含ORDER BY子句，当你从视图取回数据时可以指定ORDER BY子句。
如果你没有为用WITH CHECK OPTION选项创建的视图指定一个约束名字，系统将以SYS_Cn格式指定一个默认的名字。
你可以用OR REPLACE选项改变视图的定义而无须删除和重新创建它，或重新授予以前已经授予它的对象权限。

可以查询数据字典视图USER_VIEWS来看视图的名字和视图定义。构成视图的SELECT语句的文本被存储在一个LONG列中。
当你用视图存取数据时，Oracle 服务器执行下面的操作：
1. 从数据字典表USER_VIEWS中取回视图定义。
2. 检查对视图的基表的数据存取权限。
3. 转换视图查询为一个在基表或表上的等价操作，换句话说，数据从基表得到，或更新基表。

如果视图中的任何列源自函数或表达式，别名是必需的。
CREATE VIEW dept_sum_vu(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

视图中DML 操作的执行规则
如果视图中不包含下面的部分，你就可以从视图中删除数据：组函数、GROUP BY子句、DISTINCT关键字、伪列ROWNUM关键字。
如果视图中包含下面的部分就不能修改数据：组函数，GROUP BY子句，DISTINCT关键字，伪列ROWNUM关键字，用表达式定义的列
如果视图中包含下面的部分就不能通过视图添加数据：组函数，GROUP BY子句，DISTINCT关键字，伪列ROWNUM关键字，用表达式定义的列，基表中的NOT NULL列不在视图中
在一个连接视图上，只有在一个基表上，任何UPDATE、INSERT或DELETE 语句才能够执行。
如果在子查询连接中至少有一行有一个唯一索引，那么在一个连接视图中修改基表是可能的。
你可以查询USER_UPDATABLE_COLUMNS来看是否在连接视图中的列可以被更新。

使用WITH CHECK OPTION子句，可以通过视图执行引用完整性检查，你也可以在数据库级别强制约束。视图能用于保护数据的完整性，但用途非常有限。
WITH CHECK OPTION子句指出通过视图执行的INSERTs和UPDATEs不能创建视图不能选择的行，因此，该子句在数据开始插入或更新时允许完整性约束和数据验证检查。
在创建视图时带上WITH READ ONLY选项，以确保无DML操作发生。如果用户没有提供约束名，系统将以SYS_Cn格式指定一个名字，这里n是一个整数，在系统中约束名是唯一的。

管理视图
视图无法在创建后修改
要修改视图只能使用重建命令以新的定义重建视图
重建视图命令语法如下：
CREATE OR REPLACE VIEW 视图名
注意，FROM后的子查询可以被称为内建视图。

删除视图
我们可以用DROP VIEW语句来删除视图。该语句从数据库中删除视图定义。删除视图不影响用于建立视图的基表。
基于已删除视图上的其它视图或应用程序将无效。只有创建者或具有DROP ANY VIEW权限的用户才能删除视图。

物化视图简介
物化视图和视图的区别是，视图并不真正的包含数据，而物化视图则真正的包含数据
物化视图等于是对其基表的一种预处理
物化视图中的数据可以随基表的变化而变化
物化视图可以加快某些查询操作的速度，但它减慢了DML的速度

物化视图应看作是一种特殊的视图，它物理上存在于数据库内部，可以包括联接和/或聚合。它能够在执行之前预先计算开销大的联接和聚合操作，因此它的存在缩短了查询执行时间。
现在DBA 将仅须在开始时创建物化视图，而无论数据源何时发生变化，它都将被自动更新。此外还有一个概要顾问组件，它向DBA 推荐创建、删除和保留哪些物化视图。
数据仓库或数据库用户将可以体会到使用物化视图的最大好处之一，DBA 无须再告诉他们存在哪些物化视图。他们可以对数据库中的表或视图编写自己的查询。
然后Oracle 服务器的查询重写机制将自动重写SQL 查询以使用物化视图。这样就大大缩短了查询响应时间，终端用户无须“了解概要”。
使用Oracle概要管理所带来的额外好处是：
1、Oracle 中的查询重写机制是透明的并采用物化视图（即使它仅能部分满足查询的需要）。
2、具有高级的查询重写，可以使用物化视图对不同聚合级别（例如按照星期、月和年）进行报告。
3、自动化机制刷新物化视图，单个请求刷新所有物化视图。
4、DBA 不再需要花时间查找应创建哪些物化视图。系统将基于过去对数据库或数据仓库的查询，向DBA 提供有关需要哪些概要的信息。

组成概要管理的有五个组件：
1、维度。
2、物化视图。
3、刷新。
4、查询重写。
5、概要顾问。
并不需要使用所有组件，但所选用的组件越多，获得的优势就越多。用于物化视图的模式类型或设计没有什么限制。因此在数据仓库环境中，模式可以是雪花式的设计，但这并不是必须的。

视图的优点：
限制数据访问
简化查询
提供数据独立性
提供相同数据的多种视图
能够在不影响底层数据的情况下被删除
视图选项：
可以是基于一个表的简单视图
可以是基于多表或包含函数组的复杂视图
可以用相同的名字替换其它视图
可以包含检查约束
可以是只读的