SQL - 使用 DDL 語句

使用 DDL 語句建立和管理表

模式是多個數據庫物件的集合，這些物件稱為模式物件。這些物件可以透過其所有者模式直接訪問。下表列出了模式物件。

表 - 用於儲存資料
檢視 - 用於以所需格式從一個或多個表中投影資料
序列 - 用於生成數值
索引 - 用於提高對錶的查詢效能
同義詞 - 物件的替代名稱

建立資料庫的第一步之一是建立將儲存組織資料的表。資料庫設計涉及識別各種組織系統（如訂單輸入、庫存管理和應收賬款）的系統使用者需求。無論資料庫的大小和複雜程度如何，每個資料庫都由表組成。

建立表

要在資料庫中建立表，DBA 必須掌握某些資訊 - 表名、列名、列資料型別和列大小。所有這些資訊都可以稍後使用 DDL 命令修改。

表命名約定 -

您為表選擇的名稱必須遵循以下標準規則
名稱必須以字母 A-Z 或 a-z 開頭
可以包含數字和下劃線
可以是大寫或小寫
長度最多可達 30 個字元
不能使用模式中其他現有物件的相同名稱
不能是 SQL 保留字

按照上述指南，“EMP85”可以是有效的表名。但 85EMP 不是。類似地，UPDATE 不能用作表名，因為它是一個 SQL 保留關鍵字。

CREATE TABLE 語句

CREATE TABLE 是一個 DDL 語句，用於在資料庫中建立表。一旦執行 CREATE TABLE 指令碼，表就會被建立，並隨時準備儲存資料。使用者必須擁有 CREATE TABLE 系統許可權才能在其自己的模式中建立表。但是，要在任何使用者的模式中建立表，使用者必須擁有 CREATE ANY TABLE 模式。

以下是基本 CREATE TABLE 語句的語法。可能還有許多其他子句來明確提供儲存規範或段值。

CREATE TABLE [schema.]table 
          ( { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} 
         [, { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} ]...) 
         [AS subquery]

在上述語法中，DEFAULT 指定預設值，如果忽略該列，則可以在 INSERT 語句中使用。它不能包含對其他表列或偽列（CURRVAL、NEXTVAL、LEVEL 和 ROWNUM）的引用，除了 SYSDATE 和 USER，或者未完全指定的日期常量。

約束是在列級別或表級別（在本節後面介紹）可選定義的規則。這些規則在對錶的任何資料操作（插入、更新）期間進行檢查，並在違反規則時引發錯誤以中止操作。

例如，下面的 CREATE TABLE 語句建立了一個名為 EMP_TEST 的表。請注意列規範、資料型別和精度。

CREATE TABLE SCOTT.EMP_TEST
(EMPID NUMBER,
ENAME VARCHAR2(100),
DEPARTMENT_ID NUMBER,
SALARY NUMBER,
JOB_ID VARCHAR2(3),
HIREDATE DATE,
COMM NUMBER);

使用者可以透過在表名字首使用者名稱或模式來引用其他使用者模式中的表。例如，使用者 GUEST 希望從 SCOTT 擁有的 EMP_TEST 表中查詢員工姓名和薪資。他可以發出以下查詢 -

SELECT  ENAME, SALARY,
FROM 	GUEST.EMP_TEST;

列可以在建立表時儲存預設值。它有助於限制 NULL 值進入列。預設值可以從文字、表示式或 SQL 函式推匯出，該函式必須返回與列相容的資料型別。在下面的 CREATE TABLE 語句中，請注意 LOCATION_ID 列的預設值為 100。

CREATE TABLE SCOTT.DEPARTMENT
(DEPARTMENT_ID NUMBER,
   DNAME VARCHAR2 (100),
   LOCATION_ID NUMBER DEFAULT 100);

CTAS - 使用子查詢建立表

可以使用子查詢選項從資料庫中的現有表建立表。它複製表結構以及表中的資料。資料也可以根據條件複製。列資料型別定義（包括顯式施加的 NOT NULL 約束）將複製到新表中。

以下 CTAS 指令碼建立了一個名為 EMP_BACKUP 的新表。部門 20 的員工資料被複制到新表中。

CREATE TABLE EMP_BACKUP
AS
SELECT * FROM EMP_TEST
WHERE department_id=20;

資料型別

資料型別用於指定表中列的基本行為。從更廣泛的角度來看，列行為可以屬於數字、字元或日期系列。還有許多其他子型別屬於這些系列。

數字資料型別

NUMBER 資料型別包含整數、定點和浮點數值。早期版本的 Oracle 為每種不同型別的數字定義了不同的資料型別，但現在 NUMBER 資料型別滿足所有這些用途。當列必須儲存可用於數學計算的數值資料時，選擇 NUMBER 資料型別。有時，NUMBER 資料型別用於儲存標識號，其中這些號碼由 DBMS 作為順序號生成。

NUMBER (p, s)，其中 p 是最多 38 位的精度，s 是標度（小數點右邊的位數）。標度範圍從 -84 到 127。

NUMBER (p) 是一個定點數字，標度為零，精度為 p。

FLOAT [(p)]，其中 p 是二進位制精度，範圍從 1 到 126。如果未指定 p，則預設值為二進位制 126。

日期資料型別

對於每個 DATE 資料型別，世紀、年份、月份、日期、小時、分鐘、秒都儲存在資料庫中。每個資料庫系統都有一個由初始化引數 NLS_DATE_FORMAT 定義的預設日期格式。此引數通常設定為 DD-MON-YY。如果未指定時間，則預設時間為上午 12:00:00。

字元資料型別

Oracle 支援三種預定義的字元資料型別，包括 CHAR、VARCHAR、VARCHAR2 和 LONG。VARCHAR 和 VARCHAR2 實際上是同義詞，Oracle 建議使用 VARCHAR2 而不是 VARCHAR。當列將儲存固定長度的字元值時，使用 CHAR 資料型別。例如，美國的社會安全號碼 (SSN) 分配給每個公民，並且始終為 9 個字元（即使 SSN 嚴格由數字組成，這些數字也被視為字元），並且將指定為 CHAR(9)。使用 VARCHAR2 資料型別儲存長度可變的字母數字資料。例如，客戶姓名或地址在要儲存的字元數方面會有很大差異。VARCHAR2 列的最大大小為 4,000 個字元。

LOB 資料型別

Oracle 提供了幾種不同的 LOB 資料型別，包括 CLOB（字元大物件）和 BLOB（二進位制大物件）。這些資料型別的列可以儲存非結構化資料，包括文字、影像、影片和空間資料。CLOB 資料型別可以使用 CHAR 資料庫字元集儲存高達 8 TB 的字元資料。BLOB 資料型別用於儲存非結構化二進位制大物件，例如與影像和影片資料關聯的物件，其中資料只是“位”值的流。BLOB 資料型別可以儲存高達 8 TB 的二進位制資料。NCLOB 資料型別可以使用多位元組國家字元集儲存高達 8TB 到 128TB 的字元大物件。BFILE 資料型別值用作檔案定位器或指向伺服器檔案系統上檔案的指標。支援的最大檔案大小為 8TB 到 128TB。

約束

約束是在 Oracle 表中定義的一組規則，用於確保資料完整性。這些規則針對每個列或列集強制執行。每當表參與資料操作時，都會驗證這些規則，並在違反規則時引發異常。可用的約束型別有 NOT NULL、主鍵、唯一鍵、檢查和外部索引鍵。

以下語法可用於在列級別強制約束。

語法

column [data type] [CONSTRAINT constraint_name] constraint_type

除了 NOT NULL 之外的所有約束也可以在表級別定義。複合約束只能在表級別指定。

NOT NULL 約束

NOT NULL 約束意味著資料行必須具有指定為 NOT NULL 的列的值。如果將列指定為 NOT NULL，則 Oracle RDBMS 將不允許將違反此約束的行儲存到員工表中。它只能在列級別定義，不能在表級別定義。

語法

COLUMN [data type] [NOT NULL]

UNIQUE 約束

有時需要對不是主鍵列的列值強制唯一性。可以使用 UNIQUE 約束來強制執行此規則，Oracle 將拒絕任何違反唯一約束的行。唯一約束確保列值是不同的，沒有任何重複。

語法

列級

COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]

**表級：**CONSTRAINT [約束名稱] UNIQUE (列名)

注意：Oracle 在內部建立唯一索引以防止列值重複。索引將在後面的 PL/SQL 中討論。

CREATE TABLE TEST
( ... ,
  NAME VARCHAR2(20) 
          CONSTRAINT TEST_NAME_UK UNIQUE,
  ... );

對於複合唯一鍵，必須在表級別定義如下。

CREATE TABLE TEST
( ... ,
  NAME VARCHAR2(20),
  STD VARCHAR2(20) ,
      CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD)
 );

主鍵

每個表通常必須包含一個列或一組列，這些列唯一地標識儲存在表中的資料行。此列或列集稱為主鍵。大多數表只有一個列作為主鍵。主鍵列被限制為不允許 NULL 和重複值。

需要注意的要點 -

一個表只能有一個主鍵。
多個列可以組合在一個複合主鍵下。
Oracle 在內部建立唯一索引以防止列值重複。索引將在後面的 PL/SQL 中討論。

語法

列級

COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]

表級

CONSTRAINT [constraint name] PRIMARY KEY [column (s)]

以下示例顯示如何在列級別使用 PRIMARY KEY 約束。

CREATE TABLE TEST
( ID  NUMBER CONSTRAINT TEST_PK PRIMARY KEY,
  ...  );

以下示例顯示如何在表級別使用 PRIMARY KEY 約束定義複合主鍵。

CREATE TABLE TEST
 ( ...,
   CONSTRAINT TEST_PK PRIMARY KEY (ID) 
 );

外部索引鍵

當兩個表基於特定列共享父子關係時，子表中的連線列稱為外部索引鍵。父表中對應列的此屬性稱為引用完整性。子表中的外部索引鍵列值可以為空，也可以必須是父表中現有值。請注意，只有被引用表的列才能強制引用完整性。

如果在子表中的列上定義了外部索引鍵，則 Oracle 不允許刪除父行，如果它包含任何子行。但是，如果在定義外部索引鍵時給出了 ON DELETE CASCADE 選項，則 Oracle 在刪除父行時會刪除所有子行。類似地，ON DELETE SET NULL 表示當父表中的行被刪除時，外部索引鍵值將設定為 null。

語法

列級

COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]

表級

CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]

以下示例顯示如何在列級別使用 FOREIGN KEY 約束。

CREATE TABLE TEST
(ccode varchar2(5) 
     CONSTRAINT TEST_FK REFERENCES PARENT_TEST(ccode),
   ...
);

ON DELETE CASCADE 子句的使用

CREATE TABLE TEST
(ccode varchar2(5) 
   CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode)
   ON DELETE CASCADE,
   ...
);

檢查約束

有時特定列中儲存的資料值必須落入某些可接受的值範圍內。CHECK 約束要求為儲存在表中的每一行，指定的檢查條件必須為真或未知。檢查約束允許在列上強制執行條件規則，在將資料插入列之前必須驗證該規則。條件不能包含子查詢或偽列 CURRVAL NEXTVAL、LEVEL、ROWNUM 或 SYSDATE。

Oracle 允許單個列具有多個 CHECK 約束。實際上，為列定義的 CHECK 約束數量沒有實際限制。

語法

列級

COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]

表級

CONSTRAINT [name] CHECK (condition)

以下示例顯示如何在列級別使用 CHECK 約束。

CREATE TABLE TEST
( ...,
   GRADE char (1) CONSTRAINT TEST_CHK
   CHECK (upper (GRADE) in ('A','B','C')),
   ...
);

以下示例顯示如何在表級別使用 CHECK 約束。

CREATE TABLE TEST
( ...,
   CONSTRAINT TEST_CHK
   CHECK (stdate < = enddate),
);

ALTER TABLE 語句

資料庫管理員 (DBA) 可以在資料庫中建立表之後更改表結構或列定義。DDL 命令 ALTER TABLE 用於執行此類操作。ALTER 命令提供了多個專用於模式物件的實用程式。ALTER TABLE 語句用於在表中新增、刪除、重新命名和修改列。

以下 ALTER TABLE 語句將表 EMP 重新命名為 EMP_NEW。

ALTER TABLE EMP RENAME TO EMP_NEW;

以下 ALTER TABLE 語句向 EMP_NEW 表新增一個新的列 TESTCOL。

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

以下 ALTER TABLE 語句將列 TESTCOL 重新命名為 TESTNEW。

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

以下 ALTER TABLE 語句從 EMP_NEW 表中刪除列 TESTNEW。

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

以下 ALTER TABLE 語句在 EMPLOYEE_ID 列上新增主鍵。

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

以下 ALTER TABLE 語句刪除主鍵。

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

以下 ALTER TABLE 語句將表的模式切換為只讀。

ALTER TABLE EMP_NEW READ ONLY;

只讀表

只讀表是 Oracle 11g 中的一個增強功能。它允許將表用於只讀目的。在早期的 Oracle 版本中，透過授予其他使用者 SELECT 許可權來使表成為只讀，但所有者仍然具有讀寫許可權。但是現在，如果將表設定為只讀，即使是所有者也無法訪問資料操作。

語法

ALTER TALE [TABLE NAME] READ ONLY

ALTER TALE [TABLE NAME] READ WRITE

示例

SQL>CREATE TABLE ORATEST (id NUMBER)

SQL>INSERT INTO ORATEST VALUES (1);

SQL>ALTER TABLE ORATEST READ ONLY;

SQL> INSERT INTO ORATEST VALUES (2);
INSERT INTO ORATEST VALUES (2)
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> UPDATE ORATEST SET id = 2;
UPDATE ORATEST SET id = 2
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> DELETE FROM ORATEST;
DELETE FROM ORATEST
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> TRUNCATE TABLE ORATEST;
TRUNCATE TABLE ORATEST
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> ALTER TABLE ORATEST ADD (description VARCHAR2 (50));
ALTER TABLE ORATEST ADD (description VARCHAR2 (50))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> ALTER TABLE ORATEST READ WRITE;

Table altered.

SQL> DELETE FROM ORATEST;

1 row deleted.

DROP TABLE 語句

DROP TABLE 語句用於從資料庫中刪除表。刪除的表及其資料將不再可用進行選擇。如果回收站中可用，可以使用 FLASHBACK 實用程式恢復已刪除的表。刪除表會刪除與其關聯的索引和觸發器。

語法

DROP TABLE [TABLE NAME] [PURGE]

以下語句將刪除表並將其放入回收站。

DROP TABLE emp_new;

以下語句將刪除表並將其從回收站中清除。

DROP TABLE emp_new PURGE;

列印頁面