oracle sql 基礎（四）：數據定義語言（創建和管理表、視圖）

        數據定義語言 (Data Definition Language, DDL) 是SQL語言集中負責數據結構定義與資料庫對象定義的語言，由CREATE、ALTER與DROP三個語法所組成。資料庫對象包括表、視圖、索引、同義詞、序列等(TABLE/VIEW/INDEX/SYN/SEQUENCE)。我們先對創建和管理表和視圖進行一個介紹。

         一、創建和管理表

        數據表是資料庫中一個非常重要的對象，是其他對象的基礎。可分為用戶表和數據字典兩類。

        1、創建表（CREATE TABLE）

        CREATE TABLE 語句創建表

CREATE TABLE table                 --table：表名   column:列名
            (column datatype       --列的數據類型和長度
            [DEFAULT expr]         --指定預設值，防止插入時輸入空值到列中
            [,...]);

        最為常見的數據類型有三種，分別為字元型，數值型，日期型。

        用子查詢語法創建表（CTAS）

CREATE TABLE table
             [(column, column...)]
          AS subquery              --使用子查詢

       如果給出了指定的列，列的數目必須等於子查詢的SELECT列表的列數目。如果沒有給出了指定的列，表的列名應和子查詢中的列名是相同的。完整性規則不會被傳遞到新表中，僅列的數據類型被定義。

       2、修改表（ALTER TABLE）

ALTER TABLE table ADD (column datatype [DEFAULT expr][,column datatype]...);    --添加列

ALTER TABLE table MODIFY (column datatype [DEFAULT expr][,column datatype]...); --修改列

ALTER TABLE table DROP (column);                                                --刪除列
ALTER TABLE table SET UNUSED (column);                                          --隱藏列
ALTER TABLE table DROP UNUSED COLUMNS;                                          --刪除隱藏的列                 

       添加列時不能指定新添加的列出的位置，新列將成為最後一列；修改列時可以修改數據類型大小和預設值，當列中只包含空值時才能減少一個列的寬度和改變數據類型，對預設值的改變僅改變影響以後插入的列；刪除列時一次只能有一列被刪除。表被修改後必須至少保留一列。一旦一列被刪除，它不能再恢復；當一列從表中被刪除時，該表中任何其他的被用SET UNUSED選項標記列也被刪除，設置UNUSED的作用是為了在cpu、記憶體等資源不充足的時候，先做上UNUSED標記再等資料庫資源空閑的時候用DROP UNUSED COLUMNS刪除。

       3、刪除表（DROP TABLE）

DROP TABLE table [PURGE];

       當你刪除一個表時，資料庫丟失表中所有的數據，並且所有與其相關的索引也被刪除，任何視圖和同義詞被保留但無效。ORACLE 10g中由於引入了回收站，因此要徹底刪除一個表需要加PURGE關鍵字。

       4、截斷表（TRUNCATE TABLE）

TRUNCATE TABLE table;

       截斷表用於從表中刪除所有的行，並且釋放該表所使用的存儲空間。DELETE語句也可以從表中刪除所有的行，但它不能釋放存儲空間。TRUNCATE命令更快一些，原因如下：TRUNCATE語句是數據定義 (DDL) 語句，不產生回滾信息；截斷一個表不觸發表的刪除觸發器。

       二、定義和管理約束

       約束 (constraints)用來防止無效數據輸入到表中。你可以使用約束做下麵的事：在插入、更新行或者從表中刪除行的時候強製表中的數據遵循規則；對於成功的操作，約束必須被滿足，如果表之間有依賴關係，防止表的刪除；為 Oracle工具提供規則，例如Oracle Developer。

       約束可以在創建表時定義，也可以在表創建之後定義。你可以用USER_CONSTRAINTS和USER_CONS_COLUMNS數據字典表查看對一個表約束的定義。

       1、定義約束

CREATE TABLE [schema.]table
             (column datatype [DEFAULT expr]                                       --數據類型
             [column [CONSTRAINT constraint_name] constraint_type],...             --列級約束
             [column,...[CONSTRAINT constraint_name] constraint_type] (column,...) --表級約束
             [,...]);

       約束通常在創建表的同時被創建。在表創建後約束能夠被添加，並且約束可以可以被臨時禁用。 約束可以在兩個級別上定義：列級約束，只涉及一個單個的列，對於該列用規範定義，能夠定義完整性約束的任何類型；表級約束，涉及一個或多個列，表中的列被分別定義，除了 NOT NULL，能夠定義任意約束。

       2、約束類型

       3、管理約束

ALTER TABLE table ADD [CONSTRAINT constraint] type (column);                       --添加約束
ALTER TABLE table DROP PRIMARY KEY|UNIQUE(column)|CONSTRAINT constraint [CASCADE]; --刪除約束
ALTER TABLE table DISABLE CONSTRAINT constraint [CASCADE];                         --禁用約束
ALTER TABLE table ENABLE CONSTRAINT constraint;                                    --啟用約束

       你可以添加、刪除或禁用一個約束，但你不能修改它的結構。你可以用ALTER TABLE語句的MODIFY子句添加一個NOT NULL約束到一個已經存在的列。只有在表是空的或者每個行的該列都有非空值的情況下，你才可以定義一個NOT NULL列。
       為了刪除約束，你可以先從USER_CONSTRAINTS和USER_CONS_COLUMNS數據字典視圖中確定約束的名字，然後使用帶DROP子句的ALTER TABLE語句。DROP子句的CASCADE選項導致任何與其相依賴的約束也被刪除。
       你可以禁用一個約束而不刪除它，或者用帶DISABLE子句的ALTER TABLE語句重新創建它。我們即可以在CREATE TABLE語句也可以在ALTER TABLE語句中使用 DISABLE子句。CASCADE子句禁用相依賴的完整性約束。禁用唯一或主鍵約束會移除唯一性索引。
       你可以用帶ENABLE子句的ALTER TABLE語句啟用一個禁用的約束，而不需要重新創建它。如果啟用一個約束，約束將應用於表中所有的數據，所有在表中的數據都必須適合該約束。如果你啟用一個UNIQUE鍵或者PRIMARY KEY約束，一個UNIQUE或PRIMARY KEY索引將被自動地創建。你即可以CREATE TABLE語句也可以在ALTER TABLE語句中使用ENABLE子句。啟用一個帶CASCADE選項的被禁用的主鍵約束不會起用任何依賴於該主鍵的外鍵。

       三、創建和管理視圖

       視圖是數據來自一個或者多個表的數據子集的邏輯表示。視圖是基於表或另一個視圖的邏輯表，一個視圖並不包含它自己的數據，它象一個視窗，通過該視窗可以查看或改變表中的數據。視圖基於其上的表稱為基表。
       視圖限制數據的訪問，因為視圖能夠選擇性的顯示表中的列；視圖可以用來構成簡單的查詢以取回覆雜查詢的結果；視圖對特別的用戶和應用程式提供數據獨立性，一個視圖可以從幾個表中取回數據；視圖提供用戶組按照他們的特殊標準訪問數據。

       1、創建視圖

       視圖有兩種分類：簡單視圖和複雜視圖。簡單視圖：數據僅來自一個表，不包含函數或數據分組，能通過視圖執行DML操作。複雜視圖：數據來自多個表，包含函數或數據分組，除查詢外，只能進行少數DML操作。下麵是創建視圖的語法。

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)]
                                      AS subquery
                      [WITH CHECK OPTION [CONSTRAINT constraint]]
                         [WITH READ ONLY [CONSTRAINT constraint]];
----OR REPLACE 如果視圖已經存在重新創建它
----FORCE 創建視圖，而不管基表是否存在
----NOFORCE 只在基表存在的情況下創建視圖(這是預設值)
----view 視圖的名字
----alias 為由視圖查詢選擇的表達式指定名字(別名的個數必須與由視圖選擇的表達式的個數匹配)
----subquery 是一個完整的SELECT語句(對於在SELECT列表中的欄位你可以用別名)
----WITH CHECK OPTION 指定只有可訪問的行在視圖中才能被插入或修改
----constraint 為CHECK OPTION約束指定的名字
----WITH READ ONLY 確保在該視圖中沒有DML操作被執行

       創建視圖的原則：定義一個視圖的子查詢可以包含複雜的SELECT語法，包括連分組和多個字查詢；定義視圖的子查詢不能包含ORDER BY子句，當你從視圖取回數據時可以指定ORDER BY子句；如果你沒有為用WITH CHECK OPTION選項創建的視圖指定一個約束名字，系統將以SYS_Cn格式指定一個預設的名字。

       2、管理視圖

DROP VIEW view;      --view是視圖的名字

       我們可以用DROP VIEW語句來刪除視圖。該語句從資料庫中刪除視圖定義。刪除視圖不影響用於建立視圖的基表。基於已刪除視圖上的其它視圖或應用程式將無效。

       用OR REPLACE選項，允許創建視圖時同名的視圖已經存在，這樣舊版本的視圖會被替換，這意味著視圖可以在不被刪除、重新創建和再次授予對象許可權的情況下修改。當在CREATE VIEW子句中指定列別名時，不要望了別名的列表順序與子查詢的列順序一樣。