Oracle之创建表、序列、目录和视图

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 个字符

ALTER TABLE order_status2 MODIFY status VARCHAR2(15);

5.5 修改数值的精度

范例：将 order_status2.id 列的精度修改为 5

ALTER TABLE order_status2 MODIFY id NUMBER(5);

5.6 修改列的数据类型

范例：将 order_status2.status 列的数据类型修改为 CHAR

ALTER TABLE order_status2 MODIFY status CHAR(15);

注：如果表中没有任何行或该列只包含空值，就可以将列修改为任何一种数据类型

否则，就只能将列的数据类型修改为一种兼容的数据类型

5.7 修改列的默认值

范例：将 last_modified 列的默认值修改为 SYSDATE - 1

ALTER TABLE order_status2 MODIFY last_modified DEFAULT SYSDATE-1;

5.8 删除列

ALTER TABLE order_status2 DROP COLUMN initially_created;

5.9 添加约束

· 上述 5 种约束

· CHECK OPTION 通过视图对表行所做的改变必须先经过检查

· READ ONLY 指定视图是只读的

5.10 添加 CHECK 约束

范例：向 order_status2 表添加 CHECK 约束

ALTER TABLE order_status2 ADD CONSTRAINT order_status2_status_ck

CHECK (status IN ('PLACED','PENDING','SHIPPED'));

注：CHECK 约束确保 status 列的值始终设置为 PLACED、PENDING 或 SHIPPED

在 CHECK 约束中，可以使用其他比较运算符

范例：添加 CHECK 约束强制 id 的值大于 0

ALTER TABLE order_status2 ADD CONSTRAINT order_status2_id_ck CHECK(id > 0);

5.11 添加 NOT NULL 约束

范例：向 order_status2 表中的 status 列添加 NOT NULL 约束，注意要使用 MODIFY 来添加此约束

ALTER TABLE order_status2 MODIFY status CONSTRAINT order_status2_status_nn NOT NULL;

5.12 添加 FOREIGN KEY 约束

范例：先删除 order_status2 表的 modified_by 列，接着添加 FOREIGN KEY 约束

ALTER TABLE order_status2 DROP COLUMN modified_by;

添加一个引用了 employees 表的 employee_id 列的 FOREIGN KEY 约束

ALTER TABLE order_status2 ADD CONSTRAINT order_status2_modified_by_fk

modified_by REFERENCES employees(employee_id);

使用带有 ON DELETE CASCADE子句的 FOREGIN KEY 约束，可以指定在父表中删除一行时，

子表中匹配的所有行也被将被删除

范例：先删除 modified_by 列，然后使用 ON DELETE CASCADE 子句的 ALTER TABLE 语句

ALTER TABLE order_status2 DROP COLUMN modified_by;

ALTER TABLE order_status2 ADD CONSTRAINT order_status2_modified_by_fk

modified_by REFERENCES employees(employee_id) ON DELETE CASCADE;

注：当从 employees 表中删除一行时，order_status2 表中所匹配的行也都将被删除

使用带有 ON DELETE SET NULL 子句的 FOREIGN KEY 约束，可以指定在父表中删除一行时，

子表中匹配行的外键都将被设置为空值

ALTER TABLE order_status2 DROP COLUMN modified_by;

ALTER TABLE order_status2 ADD CONSTRAINT order_status2_modified_by_fk

modified_by REFERENCES employees(employee_id) ON DELETE SET NULL;

注：当从 employees 表中删除一行时，order_status2 表中所有匹配的 modified_by 列都将被设置为空值

5.13 添加 UNIQUE 约束

ADD

范例：向 status 列添加 UNIQUE 约束

ALTER TABLE order_status2 ADD CONSTRAINT order_status2_status_uq UNIQUE(status);

5.14 删除约束

DROP

ALTER TABLE order_status2 DROP CONSTRAINT order_status2_status_uq;

5.15 禁用约束

DISABLE

范例：向 order_status2 新添加一个约束，并禁用这个约束

ALTER TABLE order_status2 ADD CONSTRAINT order_status2_status_uq UNIQUE (status) DISABLE;

范例：禁用已存在的 order_status2_status_nn 约束

ALTER TABLE order_status2 DISABLE CONSTRAINT order_status_status2_nn;

注：在禁用作为外键约束的一部分的主键约束或唯一性约束时，必须使用 CASCADE;

5.16 启用约束

ENABLE

范例：启用 order_sales2_status_uq 约束

ALTER TABLE order_status2 ENABLE CONSTRAINT order_status2_status_uq;

注：要启用约束，表中所有的行必须能满足约束条件

范例：通过 ENABLE NOVALIDATE，可以选择只对新数据应用某个约束

ALTER TABLE order_status2 ENABLE NOVALIDATE CONSTRAINT order_status2_status_uq;

5.17 延迟约束

延迟约束 ( deferrable constraint ) 是在事务被提交时强制执行的约束

约束一旦添加之后，就不能再修改为 DEFERRABLE了；相反，只能先将其删除，再重新创建这个约束

注：

在添加 DEFERRABLE 约束时，可以将其标识为 INITIALLY IMMEDIATE 或 INITIALLY DEFERRABLE

INITIALLY IMMEDIATE 的意思是每次向表中添加数据、修改表的数据或从表中删除数据时都要检查这个

约束( 这与约束的默认行为相同 )；

INITIALLY DEFERRABLE 的意思是只有在事务被提交时，才会检查这个约束

范例：先删除 order_status2_status_uq 约束，再添加该约束并将其设置为 DEFERRABLE INITIALLY DEFERRED

ALTER TABLE order_status2 DROP CONSTRAINT order_status2_status_uq;

ALTER TABLE order_status2 ADD CONSTRAINT order_status2_status_uq UNIQUE(status)

DEFERRABLE INITIALLY DEFERRED;

注：

如果向 order_status2 表中添加行，那么只有在执行 commit 命令时，才会对

order_status2_status_uq 约束进行检查

5.18 获得有关约束的信息

· user_constraints

· all_constraints

5.19 获得有关列的约束的信息

· user_cons_columns

· all_cons_colums

6. 重命名表

范例：将 order_status2 重命名为 order_state

RNAME order_status2 TO order_state;

7. 向表中添加注释

使用 COMMENT 语句可以为表或列添加注释

范例：为表 order_status2 添加注释

COMMENT ON TABLE order_status2 IS

'order_status2 stores the state of an order';

范例：为列 last_modified 添加注释

COMMENT ON COLUMN order_status2.last_modified IS

'last_modified stores the date and time the order was modified last';

7.1 获得表的注释

· user_tab_comments

7.2 获得列的注释

· user_col_comments

8. 截断表

· TRUNCATE

9. 删除表

· DROP

· DROP .... PURGE

10. 使用 BINARY_FLOAT 和 BINARY_DOUBLE 数据类型

11. 使用 DEFAULT ON NULL 列

Oracle 12c 的一项新功能是 DEFAULT ON NULL 列子句，这个子句指定当 INSERT 语句提供

该列 NULL 值时，这个列被分配的默认值

范例：创建名为 purchases_default_null 的表，当 quantity 列被提供空值时，该列被设置为默认值 1

CREATE TABLE purchases_default_null

(

product_id INTEGER CONSTRAINT purch_default_product_fk

REFERENCES products(product_id),

quantity INTEGER DEFAULT ON NULL 1 NOT NULL,

CONSTRAINT purch_default_pk PRIMARY KEY (product_id)

);

12. 在表中使用可见及不可见列

Oracle 12c 的一项新功能是在表中定义可见和不可见列的能力

可使用 VISIBLE 表明一列可见，使用 INVISIBLE 表明一个列不可见，没有指定则默认列为可见

范例：创建名为 employees_hidden_example 的表，salary 列设置为不可见，title 列显示设置为可见

CREATE TABLE employees_hidden_example

(

employee_id INTEGER CONSTRAINT employee_hidden_example PRIMARY KEY,

title VARCHAR2(20) VISIBLE,

salary NUMBER(6,0) INVISIBLE

);

使用DESCRIBE 命令查看的时候，可以使以下命令

· SET COLINVISIBLE ON 查看不可见的列

· SET COLINVISIBLE 隐藏不可见的列

范例：查看结果集中的不可见列，必须明确指明该列

SELECT employee_id,title,salary FROM employees_hidden_example;

范例：更改表，使可见的列变为不可见

ALTER TABLE employees_hidden_example MODIFY

(

title INVISIBLE,

salary VISIBLE

);

二、序列

序列 (sequence) 是一种数据库项，可以生成整数序列，序列生成的整数通常可以用来

填充数字类型的主键列

1. 创建序列

语法如下：

CRAETE SEQUENCE sequence_name

[ START WITH start_num ]

[ INCREMENT BY increment_num ]

[ { MAXVALUE maximum_num | NOMAXVALUE } ]

[ { MINVALUE minimum_num | NOMINVALUE } ]

[ { CYCLE | NOCYCLE } ]

[ { CACHE cache_num | NOCACHE } ]

[ { ORDER | NOORDER } ];

其中：

· sequence_name 指定序列名

· start_num 指定序列从哪个整数开始，起始值默认为1

· increment_num 指定序列每次增加的整数数量，增量值默认为1

· maximum_num 指定序列的最大整数

· minmum_num 指定序列的最小整数

· NOMAXVALUE 指定升序序列的最大值为10^28 - 1

· NOMINVALUE 指定升序序列的最小值为 1

· CYCLE 指定序列即使已经达到最大值或最小值也继续生成整数

当升序序列达到最大值时，下一个生成的值是最小值

当降序序列达到最小值时，下一个生成的值是最大值

· NOCYCLE 指定序列在达到最大值或最小值之后就不能再生成整数了，NOCYCLE 是默认值

· CACHE 指定缓存，这个选项为序列预分配整数

· cache_num 指定要保留在内存中的整数的个数，默认要缓存的整数为 20 个

· NOCACHE 指定不缓存任何整数，这可以阻止数据库为序列预分配值，从而避免序列产生不连续的情况

· ORDER 确保按照请求次序生成整数

· NOORDER 不确保按照请求次序生成整数，NOORDER 为默认值

2. 获取序列的信息

· user_sequences

· all_sequences

3. 使用序列

序列生成一系列数字，序列中包含两个 “伪列”，分别是 CURRVAL 和 NEXTVAL

可以分别用来获取序列的当前值和下一个值

在检索序列的当前值之前，必须通过检索序列的下一个值对序列进行初始化

范例：SELECT s_test.NEXTVAL FROM DUAL; = 1

当查询 CURRVAL 时，NEXTVAL 保持永不变，只有在检索下一个值时 NEXTVAL 才会改变

4. 使用序列填充主键

注：在使用序列填充主键时，通常应该使用 NOCACHE 选项，这样可以避免序列产生不联系的情况

5. 使用序列指定默认列值

Oracle 12c 的一项新功能是使用序列指定默认列值的能力

6. 修改序列

ALTER SEQUENCE 语句可以用来修改序列

在修改序列是，可以修改的序列内容有如下限制：

· 不能修改序列的初值

· 序列的最小值不能大于当前值

· 序列的最大值不能小于当前值

范例：将 s_test 序列的增量修改为 2

ALTER SEQUENCE s_test INCREMENT BY 2;

7. 删除序列

DROP SEQUENCE 语句可以用来删除序列

三、索引

当任何单个查询要检索的行少于或等于整个表行数的10%时，就应当创建索引

1. 创建 B-树索引

CREATE [UNIQUE] INDEX index_name ON

table_name(column_name[,column_name ... ])

TABLESPACE tab_space;

注：由于性能方面的原因，通常应该将索引与表存储到不同的表空间中

2. 创建基于函数的索引

CREATE INDEX index_name ON

table_name(function_name(column_name));

3. 获取有关索引的信息

· user_indexes

· all_indexes

4. 获取列索引的信息

· user_ind_columns

· all_ind_columns

5. 修改索引

ALTER INDEX 可以用来修改索引

范例：将索引 i_customers_phone 重命名为 i_customers_phone_number

ALTER INDEX i_customers_phone RENAME TO i_customers_phone_number

6. 删除索引

DROP INDEX index_name;

7. 创建位图索引

位图索引一般用于数据仓库中，数据仓库是包含大量数据的数据库

四、视图

视图实际上是一个或多个表上的预定义查询，这些表称为基表 ( base table )

注：视图中并不存储行，它们始终存储在表中，视图返回存储在表中的行

视图具有以下优点：

· 可以将复杂查询编写为视图，并授予用户访问视图的权限，这样就可以对用户屏蔽复杂性

· 限制用户只能访问视图，这样就可以阻止用户直接查询基表

· 限制某个视图只能访问基表中的某些行，这样就可以对最终用户屏蔽部分行

1. 创建并使用视图

CREATE [ OR REPLACE ] VIEW [ {FORCE | NOFORCE} ] view_name

[ (alias_name[,alias_name ... ]) ] AS subquery

[ WITH {CHECK OPTION | READ ONLY} CONSTRAINT constraint_name ];

其中：

· FORCE 说明即使基表不存在也要创建视图

· NOFORCE 说明如果基表不存在，就不创建视图，NOFORCE 是默认值

· WITH CHECK OPTION 说明只有子查询检索的行才能被插入、修改或删除

默认情况下，在插入、更新或删除行之前并不会检查这些行是否能被子查询检索

· WITH READ ONLY 说明只能对基表中的行进行只读访问

1.1 授予对视图的权限

用户必须具有 CREATE VIEW 特权才能创建视图

1.2 创建并使用简单视图

简单视图只会访问一个基表

1.3 对视图执行查询

创建好视图之后，就可以用它来访问基表了

1.4 对视图执行 INSERT 操作

如果视图没有使用 WITH CHECK OPTION，则可以插入、更新或删除视图不能检索的行

1.5 创建具有 CHECK OPTION 约束的视图

通过为视图添加 CHECK OPTION 约束，可以指定对视图执行的 DML 操作必须满足子查询的条件

范例：创建视图 cheap_products_view2，该视图具有 CHECK OPTION 约束

CREATE VIEW cheap_products_view2 AS

SELECT * FROM products WHERE price < 15

WITH CHECK OPTION CONSTRAINT cheap_products_view2_price;

范例：使用 cheap_products_view2 试图插入价格为 19.5 的一行，会报错，因为该视图并不能检索出这一行

INSERT INTO cheap_products_view2 VALUES(15,1,'Southern Front',19.5);

1.6 创建具有 READ ONLY 约束的视图

通过对视图添加 READ ONLY 约束，可以指定视图是只读的 ( 不允许 DML 操作 )

1.7 获取有关视图定义的信息

· DESCRIBE

· user_views

· all_views

1.8 获取有关视图的约束信息

· user_constaints

1.9 创建并使用复杂视图

· 从多个基表中检索数据

· 使用 GROUP BY 或 DISTINCT 子句对行分组

2. 修改视图

· CRAETE OR REPLACE 可以彻底替换视图

· ALTER VIEW 可以用来修改视图的约束

范例：使用 ALTER VIEW 从 cheap_products_view 中删除 cheap_products_view 约束

ALTER VIEW cheap_products_view DROP CONSTRAINT cheap_products_view_price;

3. 删除视图

· DROP VIEW

4. 在视图中使用可见列和不可见列

Oracle 12c 的一项新功能是在视图中定义可见列和不可见列

VISIBLE 使一列可见，INVISIBLE 使一列不可见

注：要查看视图中的不可见列，必须明确指定该列

五、闪回数据归档

· 查看行在特定时间戳的情况

· 查看行在两个时间戳之间的情况

1. 创建闪回归档

使用 CREATE FLASHBACK ARCHIVE 语句创建闪回归档

范例：创建名为 test_archive 的闪回归档

CONNECT system/oracle

CREATE FLASHBACK ARCHIVE test_archive

TABLESPACE example

QUOTA 1M

RETENTION 1 DAY;

范例：修改现有的表，将数据存储在归档中

ALTER TABLE store.products FLASHBACK ARCHIVE test_archive;

随后对表 store.products 表所做的修改都会记录在归档中

范例： INSERT 语句向 store.products 表添加一行

INSERT INTO store.products VALUES (15,1,'Using Linux','How to Use Linux',39.99);

范例：查询表 store.products 在 5 min 前的情况

SELECT product_id,name,price FROM store.products

AS OF TIMESTAMP

(SYSTIMESTAMP - INTERVAL '5' MINUTE);

范例：查看表 store.products 在特定时间戳的情况

注：如果运行这个查询，需要将时间戳修改为运行前面的 INSERT 语句之前的日期和时间

SELECT product_id,name,price FROM store.products

AS OF TIMESTAMP

TO_TIMESTAMP('2014-12-06 23:10:00','hh24:mi:ss');

范例：查看表 store.products 在两个时间戳之间的情况

SELECT product_id,name,price FROM store.products

VERSIONS BETWEEN TIMESTAMP

TO_TIMESTAMP('2014-12-06 23:15:00','yyyy-mm-dd hh24:mi:ss')

AND TO_TIMESTAMP('2014-12-06 23:20:00','yyyy-mm-dd hh24:mi:ss');

范例：查看表 store.products 在某个时间戳和当前时间之间的情况

SELECT product_id,name,price FROM store.products

VERSIONS BETWEEN TIMESTAMP

TO_TIMESTAMP('2014-12-06 23:15:00','yyyy-mm-dd hh24:mi:ss')

AND MAXVALUE;

注：可以使用 ALTER TABLE 停止对表的数据归档

ALTER TABLE store.products NO FLASHBACK ARCHIVE;

注：创建表时，可以为表指定闪回归档

CREATE TABLE store.test_table

(

id INTEGER,

name VARCHAR2(10)

)

FLASHBACK ARCHIVE test_archive;

注：可以修改闪回归档

范例：将数据保留时间设置为两年

ALTER FLASHBACK ARCHIVE test_archive MODIFY RETENTION 2 YEAR;

注：可以从闪回归档中清除给定时间戳之前的数据

范例：清除一天以前的数据

ALTER FLASHBACK ARCHIVE test_archive PURGE BEFORE TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' DAY);

注：可以清除闪回归档中的所有数据

ALTER FLASHBACK ARCHIVE test_archive PURGE ALL;

注：可以删除闪回归档

DROP FLASHBACK ARCHIVE test_archive;

附：使用下面的视图可以查看闪回归档的详细信息

· user_flashback_archive 和 dba_flashback_archive ，它们显示闪回归档的总体信息

· user_flashback_archive_ts 和 dba_flashback_archive_ts ，它们显示包含闪回归档的表空间的有关信息

· user_flashback_archive_tables 和 dba_flashback_archive_tables ，它们显示启用了闪回归档的那些表的信息

Oracle之创建表、序列、目录和视图

相关推荐