Oracle表的创建与管理一(Oracle数据类型，表和约束的创建修改与删除）

一.数据类型

1.Character 数据类型用来存储字符型数据:

类型	特点	长度
CHAR(n)	用来存储本地数据库字符集的定长字符串，如果不指定长度，缺省为1	1-2000B
NCHAR(n)	用来存储Unicode字符数据,即双字节存储	1-2000B

类型	特点	长度
VARCHAR2(n)	可变长度,n定义其最大长度,数据不足时不会以空格补充	字段长度可达4000B
NVARCHAR2(n)	用来存储Unicode字符数据,即双字节存储	字段长度可达4000B

 2.number 数据类型用来存储带符号的整数或浮点数，其具有精度（precision）和范围（scale）

类型	特点	范围
Number (p,s)	p定义精度，是字段的取值位数，不算小数点；s定义范围，表示小数点后允许几位数字	P∈[1-38],默认为38 S∈[-84~127],默认为0

例：NUMBER（5，2）可以用来存储表示-999.99...999.99间的数值 ，若在一行数据中的这个字段输入575.316，则真正保存到字段中的数值是575.32

3.float数据类型用来存储精度为126位二进制（相当于38位的十进制）的浮点数。

4.Integer数据类型用于存储整型数据。

5.日期时间数据类型

日期时间数据类型用来存储日期和时间的组合数据.

类型	特点
date	可存储日期和时间信息；有效范围为公元前4712年1年1月1日到公元后9999年12月31日，按7个字节来保存日期数据，包括世纪、年、月、日、小时、分、秒。缺省格式为DD-MON-YY。
timestamp	可存储日期和时间信息，秒的默认精度为6位小数,秒最大可达到小数点后9位。最大存储空间11Bytes.

可使用获取系统当前日期时间的函数SYSDATE:

select sysdate from dual;

插入数据时如不采用系统给定的格式,则需要使用日期类函数如TO_DATE进行转换.
重新定义date格式:

ALTER SESSION SET nls_date_format=‘mm-dd-yyyy hh24:mi:ss'；

6.LOB数据类型

LOB数据类型用于存储大型的未被结构化的数据,如WORD文档、二进制文件、图片文件及其他外部文件，最大长度是128TB。

类型	特点
BLOB	可存储可变长度的大型的、未被结构化的二进制数据，如图像、音频、视频等,最大128TB
CLOB	存储可变长度的字符数据，最大128TB。
BFILE	可存储二进制格式的外部文件，该字段为只读字段，不能通过数据库对其中的数据进行修改,最大128TB

7.ROWID数据类型

ROWID数据类型是ORACLE数据表中的一个伪列，它是数据表中每行数据内在的唯一的标识 ，保存了每条记录的物理地址，该字段为隐含的。
ROWID字段的构成

由18个字符组成，即OOOOOODDDBBBBBBRRR四组
OOOOOO表示数据库对象编号
DDD表示表空间中的数据文件编号
BBBBBB存储记录的数据块的编号
RRR标识同一数据块中不同的记录

二.表的创建与修改

1.创建表

语法格式：

CREATE  TABLE [schema_name.]table_name//[schema_name.]注释：加[schema_name.]代表不同模式下的表
([column_name data_type
     [DEFAULT expr][PRIMARY KEY] [NULL|NOT NULL] ]
[ , …]
)
[TABLESPACE tablespace_name]
[NOLOGGING|LOGGING]
[CACHE|NOCACHE]
[STORAGE(…… )]]
[AS subquery]

语法说明：
TABLESPACE ：指定表所存放的表空间,用户需拥有在表空间的CREATE ANY TABLE权限才可使用此子句
NOLOGGING|LOGGING ：不记录到日志或记录到日志
CACHE|NOCACHE ：是否将读中的数据块放置到LRU中最近最常使用的一端
STORAGE子句 ：设置存储参数,主要用于数据字典管理方式,11g中在本地化管理方式中将忽略STORAGE子局设置的部分存储参数.
AS subquery:使用子查询创建表
【例1】以orac_test用户在默认表空间中创建一个学生表student,表结构如下：

字段名	说明	类型	长度	约束	是否为空
stuid	学号	Varchar2	8	主键	否
sname	学生姓名	nVarchar2	20		否
sbirth	出生日期	DATE			是
ssex	性别	nChar	1	默认‘男’	是
stall	身高	NUMBER	3		是
sclass	所属班级	Varchar2	20		是
saddress	地址	nVarchar2	50		是

SQL语句创建学生表student,语句如下：

CREATE  TABLE student
(stuid  varchar2(8) PRIMARY KEY,
  sname nvarchar2(20) NOT NULL,
  ssex  nchar(1) DEFAULT ‘男’,
  sbirth  date,
   stall  number(3),
   sclass varchar2(20),
   saddress  nvarchar2(50)
)
[TABLESPACE users];

添加记录的语法格式：

INSERT INTO table_name
(column_name1[,…])
VALUES(value1,…valuen)

 说明：
如果未指定列名，则VALUES子句必须按表结构定义的列次序提供列值；

如指定了列名，则每个指定的列只能有一个值，且值的次序必须与表定义的次序相同；
如使用SELECT子句，则指定的列必须匹配一致；
字符串类型应注意用单引号括住

【例2】在STUDENT表中录入如下记录：

STUID	SNAME	SBIRTH	SSEX	STALL	SCLASS
a01	张海	1995-1-11	‘男’	175	软件1141
a02	王丽	1995-6-24	‘女’	165	软件1142

SQL>INSERT INTO student
VALUES(‘a01’,‘张海’,‘男’, ‘11-1月-1995',175,'软件1441','长春');
SQL>INSERT INTO student
VALUES(‘a02’,‘王丽','女',TO_DATE('1995-4-24','yyyy-mm-dd'),165,'软件1442','吉林')；

 注: 在为表指定表空间时,用户必须在相应的表空间中拥有足够的配额,或者拥有UNLIMITED TABLESPACE系统权限

2.修改表

1、增加字段

语句格式：
ALTER TABLE table_name
ADD (column_name datatype[, column_name  datatype])

【例3】为emp表添加 phone_number和 hiredate两列。

SQL>ALTER TABLE emp

ADD(phone_ number VARCHAR2(20),  hiredate DATE DEFAULT SYSDATE NOT NULL);

2、修改列定义

语句格式：
ALTER TABLE table_name
MODIFY column_name  datatype [DEFAULT expr][,column datatype …];
【例4】修改emp表中 first_name和 phone_ number两列的数据类型

SQL>ALTER TABLE emp MODIFY first _name CHAR(25);
 SQL>ALTER TABLE emp MODIFY phone _number CHAR(30);

 3、修改列名

语句格式：
ALTER TABLE table_name
RENAME COLUMN column_name TO new_col_name;
【例5】修改emp表中 hiredate列的名称为hire_date 

SQL>ALTER TABLE emp RENAME COLUMN hiredate TO hire_ date;

 4、删除列

1）删除一列
语句格式：
ALTER TABLE table_name
DROP COLUMN column_name;
2）删除多列
语句格式：
ALTER TABLE table_name
DROP (column1_name, column2_name);

【例6】删除emp表中的emp_id（主键）, phone_number,hire_date三列

 SQL>ALTER TABLE emp DROP COLUMN emp_ id CASCADE CONSTRAINTS;
sQL>ALTER TABLE emp DROP(phone _number,hire_date);

5.将列设置为不可用

LTER TABLE table_name

SET UNUSED COLUMN  column1_name；或者SET UNUSED COLUMN  (column1_name,column2_name)；

【例7】将emp表中的 first_name, last_name, salary 列设置为UNUSED状态。

SQL>ALTER TABLE emp SET UNUSED COLUMN salary;
SQL>ALTER TABLE emp SET UNUSED(first_name,last_name);
 SQL>ALTER TABLE emp DROP UNUSED COLUMNS;注释：恢复可用状态

 6.重命名表名

【例8】为emp表重新命名为new_emp.

SQL>ALTER TABLE emp RENAME TO new_ emp;

7.查询表

可以通过查询数据字典视图DBA_TABLES、 ALL_TABLES、 USER_TABLES、DBA_TAB COLUMNS、 ALL_TAB_COLUMNS、USER_TAB_ COLUMNS获取表及其列的信息。

【例9】查询当前用户拥有的所有表的信息。

SQL>SELECT table name, tablespace_name, status, logging FROM user_tables;

三.删除表

如果表不再需要,可以使用 DROP TABLE语句将其删除。如果要删除的表中包含有被其他表外键引用的主键列或唯一性约束列并且希望在删除该表的同时删除其他表中相关的外键约束,则需要使用 CASCADE CONSTRAINTS子句。
删除 player表 

SQL>DROP TABLE player CASCADE CONSTRAINTS;

但是在 Oracle1g数据库中,使用 DROP TABLE语句删除一个表时,通常并不立即回收该表的空间,只是将表及其关联对象的信息重命名后写入一个称为“回收站”(RECYCLEBIN)的逻辑容器中,从而可以实现表的闪回删除( FLASHBACK DROP)操作。如果要回收该表的存储空间,可以清空“回收站”(PURGE RECYCLEBIN)或在DROP TABLE语句中使用 PURGE语句。例如:

SQL>DROP TABLE player CASCADE CONSTRAINTS PURGE； 

四.数据完整性

可添加单列或多列约束:

若约束应用于单列，称为列级约束
若约束引用了多列，称为表级约束
使用 CREATE TABLE 或者 ALTER TABLE:
CREATE TABLE 是在创建表时创建约束
ALTER TABLE 是在一个已有的表上添加约束

1.创建主键约束 

1） 创建主键可以在建表时使用CREATE TABLE 命令完成. 

  【例11】在student库中，建立一个民族表（民族代码，民族名称），将民族代码指定为主键。其程序清单如下：

CREATE TABLE native
(native_id char(2) CONSTRAINT pk_mzdm PRIMARY KEY,
  native_name varchar2(30)  NOT NULL); 

 查询用户创建的约束信息,用数据字典USER_CONSTRAINTS。 

2） 为已存在的表创建主键约束，其语法格式如下：   

  ALTER  TABLE  table_name
     ADD   CONSTRAINT constraint_name
    PRIMARY KEY [CLUSTERED|NONCLUSTERED]
     {(column[,…n])}

【例12】修改教师表teacher,在该表中设置教师编号（TID）为主键 ：

  ALTER TABLE teacher
               ADD CONSTRAINT pk_bh PRIMARY KEY  (TID);

 在为表添加主键约束时,如该表已存在数据,则要求主键列不能具有相同的值,也不能存在NULL值,否则将添加失败。

 【例13】修改教师表teacher,删除该表中的主键约束 ：

 ALTER TABLE teacher
              DROP  CONSTRAINT pk_bh;   

PRIMARY KEY 约束的注意事项：

每张表只能有一个 PRIMARY KEY 约束
主键列的值必须是惟一的
主键列不允许为空值
会自动为主键列创建惟一索引,以及一个非空约束
主键约束可以是列级约束，也可以是表级约束。

2.创建惟一约束

1）创建表时创建惟一约束，其语法格式如下：

 CREATE  TABLE  table_name
            (column_name data_type CONSTRAINT constraint_name    UNIQUE) ；

 2) 为存在的表创建惟一约束，其语法格式如下：        

    ALTER  TABLE  table_name
             ADD   CONSTRAINT constraint_name
              UNIQUE {(column[,…n])} 

 【例14】：在student库中，为“民族”native表中的“民族名称”native_name字段创建一个惟一约束。其程序清单如下：

ALTER  TABLE native
            ADD CONSTRAINT uk_mzmz
            UNIQUE  (native_name )

 UNIQUE 约束的注意事项:          
设置唯一约束的列,字段值不允许包含重复的值
该列允许包含多个NULL值
在一个表上允许多个 UNIQUE 约束
可定义在列级或表级
ORACLE会自动为其建立一个唯一索引
对某列UNIQUE约束可以和NOT NULL约束同时定义,且对位置无要求。

3.创建检查约束

1）创建表时创建检查约束，其语法格式如下：

CREATE  TABLE  table_name
     (column_name  data_type  CONSTRAINT constraint_name  CHECK(logical_expression) )

2）使用SQL语句为已存在的表创建检查约束，其语法格式如下：            

  ALTER  TABLE  table_name
               ADD  CONSTRAINT constraint_name
               CHECK  (logical_expression) 

 【例15】在student库中，为教师表的出生日期列创建一个检查约束，以保证输入的数据大于1950年1月1日。其程序清单如下：

  ALTER  TABLE teacher
      ADD CONSTRAINT ck_csrq
       CHECK (tbirth>to_date (‘1950-1-1’,’yyyy-mm-dd’));

 CHECK 约束的注意事项:
表达式的计算结果应是布尔值。
只能引用同表中的其他列,但不能引用其他表中的列。
该约束可定义在列级或表级。
约束表达式中不能包含子查询，也不能包含sysdate、user等SQL函数；
对于同一个字段,可以定义多个CHECK约束。
在每次执行 INSERT 或者 UPDATE 语句的时候校验数据值。

4.创建外键约束

1）创建表时创建外键约束，其语法格式如下：

CREATE  TABLE  table_name
     (column_name  data_type  CONSTRAINT constraint_name REFERENCES ref_table   [ ( ref_column_name[,…] ) ] )

 2) 修改表时增加外键约束的语法格式为：       

 ALTER TABLE table_name
         ADD  CONSTRAINT constraint_name
          FOREIGN KEY (column_name[,…])
         REFERENCES ref_table
         [ ( ref_column_name[,…] ) ] 

 【例16】在student库的student表上，增加一个民族字段,并为该字段创建一个外键约束，从而保证输入有效的民族代码。其程序清单如下：

 ALTER TABLE  student
           Add native_id char(2);
           ALTER TABLE  student
           ADD CONSTRAINT  fk_mzdm  FOREIGN KEY (native_id)   
           REFERENCES native(native_id);

 FOREIGN KEY 约束的注意事项：
提供了列级或表级的引用完整性。FOREIGN KEY 子句中指定的列的个数和数据类型必须和 REFERENCES 子句中指定的列的个数和数据类型匹配。
具有该约束的字段值只能为相关表中引用的字段值或NULL值。
具有该约束的字段可引用本表中的其他字段,即自引用。
在一个表上创建外键之前,父表必须已经存在,必须为该表的引用列定义UNIQUE约束或PRIMARY KEY约束。
外键约束既可以是列级约束，也可以是表级约束

5.DROP命令删除表约束

1）删除约束的语法格式为：   

ALTER TABLE  table_name
       DROP CONSTRAINT    constraint_name
       [KEEP INDEX] [CASCADE]

 【例17】删除学生表中的fk_class 、ck_csrq的约束，其程序清单如下：

  ALTER TABLE student
      DROP CONSTRAINT fk_class,ck_csrq;

6.更改约束的状态

约束具有两种状态:
激活状态(ENABLE):在此状态对表操作,若操作与约束冲突,则操作被取消.
禁用状态(DISABLE):在此状态对表操作,即使操作与约束冲突, 操作仍然会执行.
创建约束时默认为激活状态

1）创建表时禁止约束的语法格式为：

 CREATE TABLE  table_name
       (column_name data_type CONSTRAINT constraint_name  constraint_typ

 【例18】创建emp表，并设置emp_id列为主键，但禁用该约束

 CREATE TABLE  emp
       (emp_id number(4) CONSTRAINT pk_ygbh  PRIMARY KEY DISABLE,
        emp_name varchar2(20)
        );

2）禁止/激活已存在的主键或唯一约束的语法格式为：   

ALTER TABLE  table_name
       [DISABLE] | [ENABLE]  [unique|primary key](column_name) [KEEP INDEX]［CASCADE］

【例19】修改emp表，并激活emp_id列的主键约束    

 
ALTER TABLE  emp
             ENABLE PRIMARY KEY （emp_id）
       ALTER TABLE  emp
             DISABLE PRIMARY KEY （emp_id）KEEP INDEX

【例20】修改student表,禁用该表中的外键约束fk_class  

  ALTER TABLE  class
             DISABLE CONSTRAINT pk_cid CASCADE
       ALTER TABLE  student
            DISABLE CONSTRAINT fk_class    

注：在禁止唯一约束或主键约束时使用CASCADE关键字,将连同与该键相关的外键约束一同禁止。但反之则不成。

7.查询约束

可以通过查询数据字典视图 DBA_CONSTRAINTS、ALL_CONSTRAINTS、USER_CONSTRAINTS、DBA_CONS_COLUMNS、USER_CONS_COLUMNS、USER_CONS COLUMNS等获取表中存在约束信息。

【例21】查询 employees表中所有约束的名称与类型。

SQL>SELECT constraint _name, constraint_ type, status FROM user _constraints WHERE table_ name='EMPLOYEES';