Oracle Database 10g SQL开发指南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中可以获取有关视图约束的信息。