第10章 创建表、序列、索引和视图

第10章 创建表、序列、索引和视图
Alter table 还可以启用或禁用约束。
Alter table可以修改列，条件是该列的类型的长度可以修改，例如，CHAR或VARCHAR2。
ALTER TABLE ORDER_STATUS MODIFY STATUS VARCHAR2(15);

修改数据列的精度
ALTER TABLE ORDER_STATUS MODIFY id NUMBER(15);

修改列的数据类型
ALTER TABLE ORDER_STATUS MODIFY STATUS CHAR(15);
如果一个表中还没有任何行或列为空值，就可以将列修改为任何一种数据类型(包括更短的数据类型)。否则，就只能将列的数据类型修改为一种兼容的数据类型。

修改列的默认值
ALTER TABLE ORDER_STATUS MODIFY last_modified default -1;

添加列
ALTER TABLE ORDER_STATUS add last_modified integer;

删除列
ALTER TABLE ORDER_STATUS drop column last_modified ;

添加约束
CHECK 指定一列或一组列的值必须满足特定的约束。
CHECK 指定对视图执行的DML操作必须满足子查询的条件。
READ ONLY 指定视图是只读的
添加CHECK 约束
ALTER TABLE ORDER_STATUS ADD CONSTRAINT ORDER_STATUS_CK CHECK ( STATUS IN (‘PLACED’,‘PENDING’));
要添加一个约束，表中现有的所有行都必须满足这个约束条件。
添加NOT NULL约束使用modify而不是add constraint
Alter table order_status modify modified_by constraint order_status_nn not null;
Alter table order_status modify modified_by not null;此处并没有指定约束的名称。数据库会自动为约束分配一个名称，例如，SYS_C003304。
为约束起一个有意义的名字，这样在应用程序出现约束的错误时，，就可以容易判断问题所在。

添加外键FOREIGN KEY约束
ALTER TABLE ORDER_STATUS ADD CONSTRAINT ORDER_STATUS_FK s_column REFERENCE EMPLOYEE(EMPLOYEE_ID);
使用一个带有FOREIGN_KEY约束的 ON DELETE CASCADE 子句，可以指定在父表中删除一行记录时，子表中匹配的所有行也都被删除
ALTER TABLE ORDER_STATUS ADD CONSTRAINT ORDER_STATUS_FK s_column REFERENCE EMPLOYEE(EMPLOYEE_ID) ON DELETE CASCADE;
使用一个带有FOREIGN_KEY约束的 ON DELETE SET NULL子句，可以指定在父表中删除一行记录时，子表中匹配行的外键将被设置为空值。
ALTER TABLE ORDER_STATUS ADD CONSTRAINT ORDER_STATUS_FK s_column REFERENCE EMPLOYEE(EMPLOYEE_ID) ON DELETE SET NULL;

添加UNIQUE 约束
ALTER TABLE ORDER_STATUS ADD CONSTRAINT ORDER_STATUS_UN UNIQUE (status);
删除约束
ALTER TABLE ORDER_STATUS drop CONSTRAINT ORDER_STATUS_UN;
禁用约束
默认情况下，约束在创建时启用的。在创建约束时可以在CONSTRAINT子句的末尾添加DISABLE来禁用约束。
ALTER TABLE ORDER_STATUS ADD CONSTRAINT ORDER_STATUS_UN UNIQUE (status) DISABLE;
使用ALTER TABLE 的DISABLE CONSTRAINT 子句可以禁用现有约束。
ALTER TABLE order_status DISABLE CONSTRAINT ORDER_STATUS_UN；
在DISABLE CONSTRAINT 子句的末尾添加CASCADE可以禁用依赖于指定的完整性约束的任何完整性约束。
启用约束
使用ALTER TABLE的ENABLE CONSTRAINT 子句可以启用现有的约束。
ALTER TABLE ORDER_STATUS ENABLE CONSTRAINT ORDER_STATUS_UN;
注意：约束默认是ENABLE VALIDATE

延迟约束
Deferred constraint 是在事务被提交时强制执行的约束。在最初添加约束时可以使用DEFERRABLE 子句指定约束是延迟约束。约束一旦添加之后，就不能再修改为DEFERRABLE了；相反，只能先将其删除，在重新创建这个约束。
在添加DEFERRABLE 约束时，可以将其标识为INITIALLY IMMEDIATE 或INITIALLY DEFERRED。
INITIALLY IMMEDIATE 的意思是每次向表中添加数据，修改表的数据或从表中删除数据时都要检查这个约束(这与约束的默认行为相同)。INITIALLY DEFERRED的意思是只有在事务提交时，才会检查这个约束。
ALTER TABLE ORDER_STATUS ADD CONSTRAINT ORDER_STATUS_UN UNIQUE (status) DEFERRABLE INITIALLY DEFERRED;
通过查询user_contraints可以获得有关约束的信息。

重命名表
RENAME ORDER_STATUS TO NEW_STATUS;
向表添加注释
COMMENT ON TABLE order_status is ‘zhushi’;
向列添加注释
COMMENT ON TABLE order_status.status is ‘zhushi’;
获得表的注释
SELECT * FROM user_tab_comments where table_name=’’;
获得列的注释
SELECT * FROM user_col_comments where table_name=’’;

截断表
使用TRUNCATE 语句可以将表截断。这将删除表中所有的行，并重置表的存储空间
TRUNCATE TABLE tableName;
如果需要删除表中的所有行，而不是DELETE。这事因为TRUNCATE会重置表的存储空间，以准备接收新行。执行TRUNCATE 语句不需要在数据库中使用任何undo空间，也不要执行COMMIT命令使删除操作永久化。

序列
CREATE SEQUENCE sequence_name
[START WITH start_num]
[INCREMENT BY increment_num]
[{MAXVALUE maximum_num | NOMAXVALUE}]
[{MINVALUE minimum_num | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE cache_num |NOCHACHE}]
[{ORDER | NOORDER}];确保按照请求次序生成整数。在使用RAC时可以使用ORDER选项。
从user_sequences中可以获得有关序列的信息。
user_sequences是数据字典的一部分。
一个序列中包含两个“伪列”，分别是currval和nextval,可以分别用来获取该序列的当前值和下一个值。
在检索序列的当前值之前，必须通过检索序列的下一个值对序列进行初始化。在选择test.seq.nextval时，该序列就被初始化为1。
使用序列填充主键
CREATE table order_status2(
Id integer constraint order_status2_pk primary key,
Status varchar2(10));
Create sequence order_status2_seq nocache;
Insert into order_status2( id ,status) values( order_status2_seq.nextval,’PLACED’);
修改序列，可以修改的序列的内容如下限制：
不能修改序列的初值
序列的最小值不能大于当前值
序列的最大值不能小于当前值

删除序列
Drop sequence test_seq;

10.3索引
通常，在需要从包含很多行的表中检索少数几行时，都应该对列创建索引。有一条基本的准则是：
当任何单个查询要检索的行少于或等于整个表行数的10%时，索引就非常有用。
Oracle数据库会为表的主键以及包含在唯一约束中的列自动创建索引。
10.3.1创建索引
CREATE [UNIQUE] INDEX index_name ON
Table_name (column_name[,column_name …])
TABLESPACE tab_space;
UNIQUE指定索引列中的值必须是唯一的。
由于性能方面的原因，通常应该将索引与表存储到不同的表空间中。
使用唯一索引可以实现列值的唯一性。
10.3.2创建基于函数的索引
Create index customers_last_name _idx on customers(last_name);
假设执行下面这个查询：
Select first_name,last_name from customers where last_name=UPPER(‘pitt’);
由于这个查询使用了一个函数upper(),因此就不会使用索引，这就需要创建基于函数的索引。
Create index customers_last_name _func_idx on customers(UPPER(last_name));
另外，为了利用基于函数的索引，DBA必须将初始化参数QUERY_rewrite_ENABLED设置为TRUE(默认值是FALSE).例如：
CONNNET system/manager
ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE;

获取有关索引的信息
从user_indexes中可以获得有关索引的信息。
使用all_indexes可以获得有关所有可以访问的索引的信息。

从user_ind_columns中可以获得列索引的信息。
使用all_ind_columns可以获得有关所有可以访问的索引的信息。
10.4视图
视图中并不存储数据，它们只会访问基表中的行。
通过某些视图，也可以对基表进行DML操作。
视图具有以下优点：
限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽创建视图所基于的底层基表。
可以将复杂查询编写为视图。这样就可以对最终用户屏蔽一定的复杂性。
限制某个视图只能访问基表中的部分特定数据。这样可以实现另外一层安全性，并且可以对最终用户屏蔽部分行。
10.4.1创建并使用视图
CREATA [OR REPLACE] VIEW [{FORCE|NOFORCE}] VIEW view_name
[(alias_name[,alias_name …])] as subquery
[WITH {CHECK OPTION | READ ONLY} CONSTRAINT constraint_name];
Force 说明即使基表不存在也要创建该视图
Alias_name为子查询中的表达式指定一个别名。别名的个数必须与子查询中表达式的个数相同。
Subquery指定一个子查询，它对基表进行检索。如果已经提供了别名，可以在SELECT子句之后的列表中使用别名。
WITH CHECK OPTION 说明只有子查询检索的行才能被插入、修改或删除。默认情况下，在插入、更新或删除行之前并不会检查这些行是否能被子查询检索。
constraint_name指定WITH CHECK OPTION 或READ ONLY 约束的名称。
WITH READ ONLY 说明只能对基表中的行进行只读访问
简单视图，包含一个子查询，它只从一个基表中检索数据。
复杂视图，包含一个子查询，它具有以下特点：
1从多个基表中检索数据
2.使用GROUP BY或DISTINCT子句对行进行分组
3.包含函数调用
注意：只能对简单视图执行DML操作；复杂视图不支持DML操作。
获取视图有关的信息
Describe view_name;
可以从 user_views和all_views 查询有关视图的信息。从user_constraints中可以获取有关视图约束的信息。