表的创建和改进
表是Oracle数据库最基本的对象,其他许多数据库对象(如索引、视图)都是以表为基础的。
表被用于实际存储数据。系统的数据,用户的数据都被分门别类地,按行和列保存在各个表中。
在关系数据库中,不同表中的数据通过主键,外键关系,彼此可能是关联的,由此这些数据成为一个逻辑上的整体。
约束(constraint)可以被看作是在数据库中定义的各种规则或者策略,用来保证数据的完整性和业务规则。
表的创建
早Oracle中,有多种类型的表,不同类型的表各有一些特殊的属性,适应于保存某种特殊的数据,进行某些特殊的操作,即在某些方面可能比其他类型的表的性能更好,如处理速度更快,占用的磁盘空间更少。
- 表与列的命令
当创建一个表时,必须给它赋予一个名称,还必须给各个列赋予一个名称。表和列的名称有下列要求,如果违反了就会创建失败,并产生错误。
、长度必须在1~30个字节之间
、必须以一个字母开头
、能够包含字母、数值、下划线_、英镑符号#和美元符号$
、不能使用保留字
、若名称被围在双引号””中,唯一的要是是名称的长度在1~30个字符之间,并且不能汗有嵌入的双引号。
、每个列名称在单个表内必须是唯一的。
表名称在用于表、视图、序列、专用同义词、过程、函数、包、物理视图和用户定义的名称空间内必须是唯一的。
- 列的类型
Oracle数据表中列的数据类型和PL/SQL中的数据类型基本相同,在一些细节上稍微有差异,Oracle数据库表中的列的数据类型如下:
、字符数据类型
)CHAR [(<size>) [BYTE | CHAR]]
)NCHAR[(<size>)]
)VARCHAR2(size [BYTE | CHAR])
)NVARCHAR2(<size>)
、大对象数据类型
) CLOB
) NCLOB
) BLOB
) BFILE
、数字数据类型
)NUMBER[ (<precision >) [.scale]]
、日期和时间数据类型
)DATE
)TIMESTAMP [(precision)]
)TIMESTAMP [(<precision>)] WITH TIME ZONE
)TIMESTAMP [(<precision>)] WITH LOCAL TIME ZONE
)INTERVAL DAY [(<precision>)] TO second
、二进制数据类型
) ROW (size)
) LONG ROW
、行数据类型
)ROWID
)UROWID
- 列的约束
在Oracle中可以建立的约束条件包括NOT NULL、UNIQUE、CHECK、PRIMARY KEY 、FOREIGN KEY。
- NOT NULL 约束
NOT NULL即非空约束,主要用于防止NULL值进入指定的列,这些数据类型的约束是在单列上定义的。在默认情况下.Oracle准许在任何列中有NULL值。NOT NULL约束具有如下特点:
、定义了NOT NULL约束的列中不能包含NULL值或无值。
、只能在单个列上定义NOT NULL 约束
、在同一个表中可以在多个列上定义分别定义NOT NULL约束。
- UNIQUE约束
UNIQUE即唯一约束,该约束用于保证在该表中指定的各个列的组合中没有重复的值。
、定义了UNIQUE约束的列中不能包含重复值,但是如果在一个列上仅定义了UNIQUE约束,而没有定义NOT NULL约束,则该列可以包含多个NULL值或无值。
、可以为单个列定义UNIQUE约束,也可以为多个列的组合定义UNIQUE约束。因此UNIQUE约束即可以在列级定义,也可以在表级定义
、Oracle会自动为具有UNIQUE约束的列建立一个唯一索引(NUQUE INDEX)。如果这个列已经具有唯一或非唯一索引,Oracle将使用已有的所有索引
、对同一个列,可以同时定义UNIQUE约束和NOT NULL约束。
、在定义UNIQUE约束时可以为它的索引指定存储位置和存储参数。
- CHECK约束
CHECK约束即检查约束,用于检查在约束中指定的条件是否得到了满足,CHECK约束具有如下特点:
、定义了CHECK约束的列必须满足约束表达式中指定的条件,但可以为NULL。
、在约束表达式中必须引用表中的单个列或多个列,并且约束表达式的计算机结果必须是一个布尔值
、在约束表达式中不能包含子查询
、在约束表达式中不能包含SYSDATE、UID、USER、USERENV等内置的SQL函数,也不能包含ROWID、ROWNUM等为例
、CHECK约束即可以在列级定义,也可用在在表级定义。
、对同一个列,可以定义多个CHECK约束,也可以同时定义CHECK和NOT NOT NULL约束。
- PRIMARY KEY约束
PRIMARY KEY约束即主键约束,其用来唯一地标识出表的每一行,并且防止出现NULL值,一个表只能有一个主键约束,PRIMARY KEY 约束具体如下:
、定义了PRIMARY KEY约束的列(或列组合) 不能包含重复值,并且不能包含NULL值。
、Oracle会自动为具有PRIMARY KEY约束的列(或列组合)建立一个唯一索引(unique index)和一个NOT NULL约束。
、同一个表中只能够定义一个PRIMARY KEY约束,也可以在多个列的组合上定义PRIMARY KEY约束。因此PRIMARY KEY约束既可以在列级定义,也可以在表级定义。
- FOREIGN KEY约束
FOREIGN KEY 约束即外键约束,通过使用外键,可保证表与表之间的参照完完整性。在参照表上定义的外键需要参照主表的主键,该约束具有如下特点:
、定义了FOREIGN KEY约束的列中只能包含相应的在其他表中引用的列的值或为NULL。
、定义了FOREIGN KEY约束的外键列和相应的引用列可以存在于同一个表中,这种情况称为“自引用”。
、对同一个列,可以同时定义FOREIGN KEY约束和NOT NULL约束。
、FOREIGN KEY约束必须参照一个PRIMARY KEY 约束或UNIQUE约束。
、可以在单个列上定义FOREIGN KEY 约束,也可以在多个列上组合上定义FOREIGN KEY约束。
- 表的创建
在Oracle数据库中,CREATE TABLE语句的基本语法格式是:
CREATE [[GLOBAL] TEMPORORY | TABLE | schema.] table_name(
Column1 datatype1 [DEFAULT exp1] [column1 constraint],
Column2 datetype2 [DEFAULT exp2] [column2 constraint]
[table contraint]
)
[ON COMMIT (DELETE | PRESERVE | ROWS)]
[ORGANIZITION { HEAP | INDEX | EXTERNAL ….}]
[PARTITION BY…(….)]
[TABLESPACE tablespace_name]
[LOGGING | NOLOGGING]
[COMPRESS | NOCOMPRESS]
NOTE:
Column1 datatype 为列指定的数据类型
DEFAULT exp1为列指定默认值
Column1 constraint 为列定义完整性约束(constraint)
[table constraint]为表定义完整性约束(constraint)
[ORGANIZTION {HEAP | INDEX | EXTERNAL ….}]为表的类型,如关系型(标准,按堆组织)、临时、索引、外部型或者对象型。
[PARTITION BY …(…)]为分区及子分区信息
[TABLESPACE tablespace_name]指示用于存储表或索引的表空间
[LOGGIN | NOLOGGING] 指示是否保留重做日志
[COMPRESS | NOCOMPRESS]z指示是否压缩
如果要在自己的方案中创建表,要求用户必须具有CREATE TABLE系统权限。如果要在其他的方案中创建表,则要求用户必须具有CREATE ANY TABLE系统权限。创建表时,Oracle会为该表分配相应的表段,表段的名称与表名完全相同,并且所有数据都会被存放到该表段中。
例如早EMPLOYEE表空间上建立department表时,Oracle会在EMPLOYEE表空间中创建department表段,所以要求表的创建这必须指定的表空间上具有空间分配或具有UNLIMITED TABLESPACHE系统权限。
修改表
普通用户只能对自己方案中的表进行更改,而具有ALTERANYTABLE系统权限的用户可以修改任何方案的表。需要对已经建立的表进行修改的情况包括以下几种:
、添加或删除表中的列,或者修改表中列的定义(包括数据类型、长度、默认值以及NOT NULL约束等等)
、对表进行重新命名
、将表移动到其他数据段或表空间中,以便重新组织表。
、添加修改或删除表中约束条件
、启用或禁用表中的约束条件,触发器等等。
修改表结构一方面可以在SQL*Plus中使用ALTER TABLE命令完成,也可以在OEM中完成。
1. 用ALTER TABLE 命令修改表结构
、增加列
如果需要在表中增加新的列,在一个现有表中增加一个新列的语法格式:
ALTER TABLE [schema.] table_name ADD(column definitionl,column definition2);
新添加的列总是位于表的末尾。Column definition部分包括列名、列的数据类型以及将具有的任何默认值。
、更改列
如果需要调整一个表中某些列的数据类型,长度和默认值,就需要更改这些列的属性,没有更改的列不受任何影响,更改表中现有列
语法如下:
ALTER TABLE[schema.] table_name MODIFY (column_name new_attrbutes1,
…………………..)
、直接删除列
当不再需要某些列时,可以将其删除。直接删除列的语法是:
ALTER TABLE[schema.] table_name DROP(column_name1, column_name2) [CASCADE CONSTRAINTS];
可以在括号中使用多个列名,每个列用逗号分隔,相关列的索引和约束也会被删除,如果删除的列是一个多行约束的组成部分,那么就必须指定CASCADE CONSTRAINTS选项,这样才会删除相关的约束
、将列标记为UNUSED状态
删除列时,将删除表中每条记录的相应列的值,同时释放所占用的存储空间,因此如果要删除一个大表的列,由于必须对每条记录进行处理,删除操可能会占用大量的系统资源,为了避免这种情况,可以暂时同ALTERTABLE SET UNUSED语句将要删除的列设置为UNUSED状态。语法格式:
ALTERTABLE [schema.] table_name SET UNUSED (column_name, column_name….)
[CASCADE CONSTRAINTS];
被标记为UNUSED状态的列与被删除的列之间是没有分别的,都无法通过数据字典或在查询中看到,另外,升值可以为表田间与UNUSED状态具有同名成的新列。
在数据字典视图USER_UNUSED_COL_TABS,ALL_UNUSED_CLOTABS和DBA_UNUSED_COL_TABS中可以查看到数据库中有那些表哪几列被标记为UNUSED状态。