Oracle表的創建與管理一(Oracle數據類型，表和約束的創建修改與刪除）

一.數據類型

1.Character 數據類型用來存儲字符型數據:

 2.number 數據類型用來存儲帶符號的整數或浮點數，其具有精度（precision）和范圍（scale）

例：NUMBER（5，2）可以用來存儲表示-999.99...999.99間的數值 ，若在一行數據中的這個字段輸入575.316，則真正保存到字段中的數值是575.32

3.float數據類型用來存儲精度為126位二進制（相當于38位的十進制）的浮點數。

4.Integer數據類型用于存儲整型數據。

5.日期時間數據類型

日期時間數據類型用來存儲日期和時間的組合數據.

可使用獲取系統當前日期時間的函數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。

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,表結構如下：

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表中錄入如下記錄：

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';