Oracle筆記（九） 表的建立及管理

對於資料庫而言實際上每一張表都表示的是一個資料庫的對象，而資料庫物件指的就是DDL定義的所有操作，例如：表、視圖、索引、序列、約束等等，都屬於對象的操作，所以表的建立就是對象的建立，而對象的操作主要分為以下三類文法：

• 建立對象：CREATE 對象名稱 …；
• 刪除對象：DROP 對象名稱 …；
• 修改對象：ALTER 對象名稱 …；

一、常用的資料欄位

每一張資料表實際上都是由若干個欄位所組成，而每一個欄位都會有其對應的資料類型，而在Oracle之中，常用的資料類型有如下幾種：

№	資料類型	關鍵字	描述
1	字串	VARCHAR2(n)	其中n表示的是字串所能儲存的最大長度，基本上儲存200個左右的內容
2	整數	NUMBER(n)	表示最多為n位的整數，有時候也可以使用INT代替
3	小數	NUMBER(n,m)	其中m為小數位，n-m為整數位，有時候也可以使用FLOAT代替
4	日期	DATE	存放日期-時間
5	大文本	CLOB	可以儲存海量文字（4G），例如儲存《三國演藝》、《紅樓夢》
6	大對象	BLOB	存放位元據，例如：電影、MP3、圖片、文字

一般在開發之中使用最多的：VARCHAR2()、NUMBER、DATE、CLOB，而對於BLOB欄位一般使用較少，首先BLOB可以存放4G的位元據，但是存放進去之後，一是資料庫過於龐大，二是讀取不方便；

二、表的建立

如果現在要想進行表的建立，可以使用如下的操作文法：

CREATE TABLE 表名稱 (    欄位1 資料類型 [DEFAULT預設值],    欄位2 資料類型 [DEFAULT預設值],    … …,    欄位n 資料類型 [DEFAULT預設值]);

下面建立一張成員表（member），有如下儲存的資訊：姓名、年齡、生日、簡歷。

CREATE TABLE member (     name VARCHAR2(50) DEFAULT '無名氏',     age NUMBER(3),     birthday DATE DEFAULT SYSDATE,     content CLOB);

表建立成功之後，下面開始向表中增加資料：

INSERT INTO member(name,age,birthday,content) VALUES('張三',20,TO_DATE('1990-08-12','yyyy-mm-dd'),'一個好人');

INSERT INTO member(age,content) VALUES(20,'一個好人');

一定要再次記住，表的建立是屬於資料庫物件的建立，所以使用的是CREATE文法。

三、表的複製

在之前學習過表的複製操作，下面給出其完整的操作文法：

CREATE TABLE 複製表名稱 AS 子查詢;

範例：複製一張只包含20部門僱員資訊的表

CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20;

範例：現在要求將emp表的表結構複製出來，不要資料 —— 寫一個永遠滿足不了的條件即可。

CREATE TABLE empnull AS SELECT * FROM emp WHERE 1=2;

但是以上的文法只是Oracle資料庫所支援的操作，其他的資料庫文法上會有一些區別。

四、為表重新命名

在Oracle資料庫之中，所有的資料實際上都是通過資料字典儲存的，例如，在之前曾經使用過如下的一種查詢：

SELECT * FROM tab;

以上就是一個資料字典，而在Oracle之中，提供了三種類型的資料字典，最常用的是：dba_、user_，所以下面查詢一個user_tables資料字典：

SELECT * FROM user_tables;

也就是說Oracle中的所有資料都是按照檔案儲存的，那麼所有的內容都會在資料字典中註冊，既然這樣，所謂的修改表名稱實際上對於Oracle而言就相當於修改一條資料而已，而修改表名稱的方法如下：

RENAME 舊的表名稱 TO 新的表名稱;

範例：將member表更名為person表

RENAME member TO person;

但是這種操作由於是Oracle資料庫所專屬的一種特性，所以瞭解即可，不用做深入的掌握。

五、截斷表

在之前曾經講解過一個刪除表資料的操作，使用的是DELETE操作，但是這種刪除操作本身有一個特點，即：可以進行事務的復原，也就是說刪除之後並不會立刻釋放資料的資源，如果現在希望徹底釋放掉一張表所佔用的全部資源（資料表空間、索引等等）就可以使用截斷表的文法，此文法如下：

TRUNCATE TABLE 表名稱;

範例：截斷person表

TRUNCATE TABLE person;

但是這種文法本身只是Oracle所有的，所以只做瞭解即可。

六、表的刪除

表的刪除操作指的是資料庫物件的刪除，既然是刪除則使用DROP語句，刪除表的文法如下：

DROP TABLE 表名稱;

範例：刪除person表

DROP TABLE person;

七、Oracle 10g的新特性：閃回技術

在Oracle 10g之後，為了預防使用者的誤刪除表操作，專門提供了資源回收筒的功能，使用者所刪除的表預設情況下會在一個資源回收筒之中儲存，而使用者也可以通過資源回收筒，進行表的恢複，所以此技術稱為閃回（FLASHBACK）；

範例：查看資源回收筒

SHOW RECYCLEBIN;

這個時候可以發現所有已經刪除的表都在資源回收筒之中儲存，那麼下面就可以使用如下的文法恢複表：

FLASHBACK TABLE 表名稱 TO BEFORE DROP;

範例：恢複myemp表

FLASHBACK TABLE myemp TO BEFORE DROP;

當然，現在也可以直接刪除掉資源回收筒中的一些資料表，文法如下：

PURGE TABLE 表名稱;

範例：刪除資源回收筒中的person表

PURGE TABLE person;

範例：清空資源回收筒

PURGE RECYCLEBIN;

如果現在希望刪除一張表，而又不希望其進入到資源回收筒之中，則可以在刪除的時候增加PURGE。

DROP TABLE myemp PURGE;

這種技術是在Oracle 10g之後才有的，而Oracle 11g也是存在的。

問題：現在在資源回收筒之中存在了一張tab表，而後又建立了一張tab表，那麼如果從資源回收筒中恢複的話，可以嗎？

答：無法恢複。

八、修改表結構

如果一張建立好的資料表，發現其初期的結構已經不滿足於後期的使用要求，則可以進行表的修改操作，而表的修改操作實際上就是資料庫物件的修改操作，使用ALTER指令完成，例如，現在有如下一張表：

CREATE TABLE member (     mid NUMBER,     name VARCHAR2(50));

現在希望可以向表中增加欄位，所以此時可以採用如下的文法完成：

ALTER TABLE 表名稱 ADD(列名稱 資料類型 [DEFAULT 預設值],　　列名稱 資料類型 [DEFAULT 預設值],…);

範例：為member表增加欄位

ALTER TABLE member ADD(age NUMBER(3), birthday DATE DEFAULT SYSDATE);

如果增加的資料列沒有預設值，則所有已有的資料的列的內容都是null，而如果增加的列指定了DEFAULT預設值的話，則所有已有的資料列都是設定的預設值。

現在也可以修改已有的表結構，此時的文法如下：

ALTER TABLE 表名稱 MODIFY(列名稱 資料類型 [DEFAULT 預設值],　　列名稱 資料類型 [DEFAULT 預設值],…);

範例：將name欄位的預設值定義為無名氏

ALTER TABLE member MODIFY(name VARCHAR2(100) DEFAULT '無名氏');

雖然在SQL文法之中以及Oracle資料庫之中，都給出了修改表結構的操作，但是這種操作能不使用就不使用，從大型資料庫來講，世界上效能最高的資料庫是IBM DB2，但是IBM DB2本身有一個平台的限制問題，所以如果說是跨平台的資料庫則是Oracle資料庫的效能最高。

在IBM DB2資料庫之中是不允許修改表結構的，即：表建立完成之後就不能修改了，所以以後在開發之中也儘可能的不要去修改表結構。

九、思考題

現在要求建立一張nation表，表中有一個name欄位，裡面儲存四條記錄：中國、美國、巴西、荷蘭，要求通過查詢實現如下的操作效果：

中國         美國

中國         巴西

中國         荷蘭

美國         巴西

美國         中國

美國         荷蘭

剩下的依次類推，現在要求建立新的表並完成此查詢的操作。

本題目的主要目的並不是在於查詢的編寫上，而是在於正常化資料庫建立指令碼的格式，以後只要是碰到了類似的要求，首先必須編寫一個資料庫建立指令碼，這個指令碼的要求如下：

1、  本檔案的檔案名稱尾碼必須是“*.sql”；

2、  先刪除相應的資料表；

3、  編寫建立表的語句；

4、  增加測試資料；

5、  提交事務；

--1、刪除表DROP TABLE nation PURGE;--2、建立表CREATE TABLE nation(    name VARCHAR2(50));--3、測試資料INSERT INTO nation(name) VALUES('中國');INSERT INTO nation(name) VALUES('美國');INSERT INTO nation(name) VALUES('巴西');INSERT INTO nation(name) VALUES('荷蘭');--4、事務提交COMMIT;

本程式如果要想完成依靠笛卡爾積完成，屬於表的自身關聯。

SELECT n1.name,n2.nameFROM nation n1,nation n2WHERE n1.name<>n2.name;

而且以後如果面試之中，出現了一些複雜的查詢，建議把指令碼寫全了。