创建表、序列、索引和视图（转）

原文链接：创建表、序列、索引和视图

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中可以获取有关视图约束的信息。