oracle 创建表及其相关约束

xhqxvzsuot

　　

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

　　

 

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

创建表

基本语法结构：  

 

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 。