Oracle之创建表、序列、索引和视图

本文主要包括：

   · 创建、修改和删除表

   · 如何创建并使用序列：序列可以生成一系列数字

   · 如何创建并使用索引：索引可以提高查询的性能

   · 如何创建并使用视图：视图是预定义的查询

   · 闪回数据归档将一段时间内对表所做的改变保存

一、 表

1. 创建表

可以使用 CREATE TABLE 语句创建表，CREATE TABLE 语句的简化语法如下：

CREATE [GLOBAL TEMPORARY] TABLE table_name 
(

   column_name type [CONSTRAINT constraint_def DEFAULT default_exp]

   [, column_name type [CONSTRAINT constraint_def DEFAULT default_exp] ... ]

)

[ON COMMIT [DELETE | PRESERVE] ROWS]

[TABLESPACE tab_space];

注：ON COMMIT 控制临时表中行的有效期，DELETE 说明这些行在事务结束时被删除

       PRESERVE 说明这些行被保留到用户会话结束时才被删除

       如果对临时表没有指定 ON COMMIT 选项，那么默认值是 DELETE

2. 创建表的同时创建约束

2.1 五种具体的约束：
   · 主键    PRIMARY KEY
                这个字段是非空的 并且是唯一的
                一个表的主键只能有一个 
   · 唯一    UNIQUE 

                这个字段的值 是不能重复的     

   · 非空    NOT NULL
                这个字段的值  不能是NULL值 
   · 检查    CHECK
                这个字段的值 必须符合检查条件     
   · 外键    REFERENCES        
                on delete cascade  
                on delete set  null

2.2 列级别约束：在定义表的一列时，直接在这一列的后面加约束限制

CRAETE TABLE orders

(

   id INTEGER CONSTRAINT orders_pk PRIMARY KEY

);

2.3  表级约束：在定义完所有的列之后，再选择某些列加约束

CREATE TABLE orders2 

(

   id INTEGER,

   CONSTRAINT orders2_pk PRIMARY KEY(id)

);

2.4 外键约束

外键约束涉及到两张表   一张叫父表  (主表)， 另一张叫子表   (从表)    
定义了外键的表就是子表，如果两张表建立了主外键关系，则外键字段的值
要么引用自父表，要么是空值

CREATE TABLE products

(
   product_id INTEGER CONSTRAINT products_pk PRIMARY KEY
   product_type_id INTEGER CONSTRAINT products_fk_product_types
   REFERENCES product_types(product_type_id)

);

2.5 创建临时表

CREATE GLOBAL TEMPORARY TABLE order_satatus_temp

(

   id INTEGER,

   status VARCHAR2(10),

   last_modified DATE DEFAULT SYSDATE

)

ON COMMIT PRESERVE ROWS;     --  保留到回话结束时才会被删除

3. 获得有关表的信息

   · user_tables     系统里所有表的信息，需要 DBA 权限才能查询

   · all_tables        当前用户下能够查询的所有表的信息

   · dba_tables      当前用户下的所有表的信息

4. 获取表中列的信息

从 user_tab_columns 视图中可以获得有关表中各列的信息

    all_tab_columns

5. 修改表

ALTER TABLE 语句可以用于对表进行修改，ALTER TABLE 语句可以执行以下任务：

   · 添加、修改或删除列

   · 添加或删除约束

   · 启用或禁用约束

5.1 添加列

范例：向 order_status2 表中添加名为 modified_by 的列，数据类型是 INTEGER

ALTER TABLE order_status2 ADD modified_by INTEGER;

范例：向 order_status2 表中添加名为 initially_created 列

ALTER TABLE order_status2 ADD initially_created DATE DEFAULT SYSDATE NOT NULL;

5.2 添加虚拟列

Oracle 11g 引入了虚拟列，虚拟列只引用表中已有的其他列

范例：向 salary_grades 表中添加名为 average_salary 的虚拟列

ALTER TABLE salary_grades ADD (average_salary AS ((low_salary + high_salary)/2));

5.3 修改列

下面列出了使用 ALTER TABLE 语句可以对列进行修改的内容：

   · 修改列的长度(条件是该列的数据类型的长度可以修改，如 CHAR 或 VARCHAR2) 

   · 修改数值列的精度

   · 修改列的数据类型

   · 修改列的默认值

5.4 修改列的长度

范例：将 order_status2.status 列的最大长度增加为 15 个字符