oracle 创建表及其相关约束

 　　在我们项目的开发过程中，数据库的开发是很常见的，本文简单介绍下非常基本的数据库操作，主要包括以下方面：

 

 

•   创建表
•   创建触发器
•   创建存储过程
•   创建索引
• 创建视图

创建表

基本语法结构：  

 

CREATE TABLE [schema.]tablename 
( column1 datatype, 
column2,datatype ...... )
 [TABLESPACE space_name]
 [PCTFREE integer] 
[PCTUSED integer]
 [INITRANS integer]
 [MAXTRANS integer] 
[STORAGE stroage_clause]
 [LOGGING|NOLOGGING] 
[CACHE|NOCACHE] 

 

SCHEMA: 表的所有者

TABLE: 表名

column1 ：属性名

datatype ：属性的数据类型

TABLESPACE: 表的空间名

PCTFREE ：用来为一个块保留的空间百分比，以防止在今后的更新操作中增加一列或多列值的长度

PCTUSED ：一个块的使用水位的百分比，这个水位将使该块返回到可用列表中去等待更多的插入操作

INITRANS ：在块中预先分配的事物项数，默认为1

MAXTRANS ：在块中可以分配的最大事物项数，最大为255

LOGGING ：将表的创建记录到重做日志中

NOLOGGING: 部将表的创建记录到重做日志中

STORAGE ：当数据增长时，如何将区分配给表的数据增长

CACHE ：在进行全表扫描时，为表建立的索引的块将放置在高速缓存的lru 列表的最近使用的一端

NOCACHE ：在进行全表扫描时，为表建立的索引的块将放置在高速缓存的lru 列表的最近未使用的一端

例子：  

  

TABLE TAN.Student 
( StuentId INTEGER NOT NULL, 
Name VARCHAR2(200 CHAR) NOT NULL ENABLE, 
Age NUMBER, 
classid INTEGER NOT NULL, 
STATUS VARCHAR2(10) NULL, 
ADD_USER VARCHAR2(20) NULL,
 ADD_TS DATE DEFAULT SYSDATE, 
MOD_USER VARCHAR2(20) NULL, 
MOD_TS DATE NULL CONSTRAINT PK_STUDENT_ID PRIMARY KEY(StuentId) ) 
STORAGE( 
        INITIAL 100K
 　　 NEXT 100K 
　　  MINEXTENTS 2
 　    MAXEXTENTS 100 PCTINCREASE 100) 

 

 说明：

      初始给Student 表分配两个Extent ，
　　第一个Extent 是100K ，因INITIAL=100K
　　第二个Extent 是100K ，因NEXT=100K
　　如果因表内数据增长，需要分配第三个Extent ，因PCTINCREASE 是100 ，则
　　第三个Extent 是200K=100K+100K
　　第四个Extent 是400K=200K+200K

LOGGING ：创建表的过程将被记录在重做日志中

TABLESAPCE test ：该表创建在test 的空间下

INITRAN 1 ：该空间块最小的事物项目数是1 个

MAXTRANS 255 ：该空间块最多的事物项目数是255 个

PCTFREE 20 ：当数据量大于1-20% ，此块不再插入新数据

PCTUSED 40 ：当数据量要低于40% 才会接受新的数据插入

 

建立主键

在创建主键时，首先，所有的主键值都是唯一的，第二，它们必须有一个值，也就是说，作为主键的列不能 NULL

创建主键有几种方式：

1.   列约束子句:

CREATE TABLE TAN.Student ( StuentId INTEGER NOT NULL PRIMARY KEY ， ...) 

 

2.   表约束子句 

CONSTRAINT PK_STUDENT_ID PRIMARY KEY (StuentId) 

    

3.   ALTER TABLE 语句:   

ALTER TABLE Student ADD CONSTRAINT PK_STUDENT_ID PRIMARY KEY ( StuentId )

   

建立外键

1.   列约束子句：  

CREATE TABLE TAN.Student 
( StuentId INTEGER NOT NULL PRIMARY KEY ， 
classid INTEGER NOT NULL REFERENCES Class(classid), 
...) 

 

  

2.   表约束子句   

CONSTRAINT fk_students_class 
FOREIGN KEY (classid) REFERENCES Class(classid)
 ON [DELETE CASCADE] | [DELETE CASCADE] |[SET NULL]|[NO ACTION] 

  

3.   ALTER TABLE 语句:    

ALTER TABLE Student ADD CONSTRAINT fk_students_class 
FOREIGN KEY (classid) REFERENCES Class(classid) 
ON [DELETE CASCADE] |[SET NULL]|[NO ACTION]

  

   

DELETE CASCADE: 级联删除
SET NULL:-- 删除主表中的记录后，子表中的相应记录的列被设置为null( 但子表的该字段必须支持null 值) 。
NO ACTION: 不允许删除主表中被引用的数据，该操作会被禁止。

 

unique 约束

unique约束是用来保证表中的某一类，或者表中的某一类组合起来不重复的一种手段,需要注意的是引用unique约束的列并不代表一定要非空，可以为null，因为空值（null）含义是该当前列的状态不存在，永远不会和另一个空值相等，所以不违法唯一约束。

 

 

1.   列约束子句：

 

CREATE TABLE TAN.Student( ... STATUS VARCHAR2(10) unique ...)

  

 

2.   表约束子句

 

constraint name_unique unique(status)

   

3.   ALTER TABLE 语句:

alter table student add constraint status_unique unique(status) 

 

  

 

check 约束

在oracle　check约束的表达式中必须至少包含表中一个或者多个列的引用，并且表达式的计算结果必须是一个布尔型的结果，该约束的级别可以定义在列或者表的级别，同时，也可以在一个列上加多个check约束。

 

1.   列约束子句：

CREATE TABLE TAN.Student( ... Age NUMBER　check(Age>18)...) 

   

2.   表约束子句 

constraint age_check check(Age>18) 

   

3.   ALTER TABLE 语句:

alter table student add constraint age_check check(Age>18) 

 

default value

DEFAULT 约束用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新纪录。 

 

1.   列约束子句：

CREATE TABLE TAN.Student( ... Age NUMBER　defalt 18)...) 

    

2.   ALTER TABLE 语句:

 

alter table TAN modify (age number default 18)

   

 

在已有表的基础上建立新表：

 

create table newtable as select studentid,name,age,status from student where name like '%jack%' 

 

 新建了newtable 表，其结构为student 表中的studentid,name,age,status ，可以说这种手段是一种表的复制，基本用于系统的数据移植，db 的migration 。

 

ORACLE 常用基本类型，并非所有：

字段类型	说明
VARCHAR2 (size)	可变长度的字符串, 必须规定长度
CHAR(size)	固定长度的字符串, 不规定长度默认值为１
NUMBER(p,s)	数字型p 是位数总长度,s 是小数的长度, 可存负数最长38 位. 不够位时会四舍五入.
LONG	LONG 类型的列存储可变长度的字符串，最多可以存储 2GB 的数据
DATE	日期和时间类型
TIMESTAMP	使用年、月、日、小时、分钟、秒域来对日期/ 时间提供更详细的支持
CLOB	用于存储基于字符的大对象最大可达4G
BLOB	最多可以存储 4GB 数据的二进制大对象
BFILE	存储指向数据库外部文件的定位符, 外部文件最大为 4GB 。