程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> DB2 SQL 與 XQuery 教程，第 6 部分: 數據定義語言和數據控制語言

DB2 SQL 與 XQuery 教程，第 6 部分: 數據定義語言和數據控制語言

編輯：DB2教程

開始之前

關於本系列

本系列教程為您講解 SQL 的一些基礎和高級話題以及 XQuery 的基礎知識，並展示如何使用 SQL 查詢或 XQuery 語句將常被問起的業務問題表達為數據庫查詢。開發人員和數據庫管理員可以使用本教程來提高他們的數據庫查詢技能。Academic Initiative 成員可以使用本教程系列作為他們數據庫課程的一部分。

本文中的所有例子都是基於 Aroma，這是一個示例數據庫，其中包含了在美國各地的商店中出售的咖啡和茶用品的銷售數據。每個例子由以下三部分組成：

以日常用語表達的一個業務問題

以 SQL 或 XQuery 表達的一個或多個例子查詢

顯示從數據庫返回的結果的一個表

本指南是為了讓讀者學習 SQL 語言和 XQuery 而設計的。和學習任何其他技能一樣，重要的是一邊學習一邊實踐。本指南給出的表定義和數據為學習提供了便利。

對於使用本指南作為學校課程一部分的學生而言，他們應該向老師學習連接到 Aroma 數據庫的操作，並了解本指南的設置與您本地設置的不同之處。

感謝您選擇 IBM® DB2® 來了解服務於純關系結構和 pureXML 結構的數據的混合型數據服務器。

本教程是針對於 DB2 Express-C 9 for Linux®, UNIX®, and Windows® 而編寫的。

關於本教程

本系列教程以前的各個部分都重點討論了 SELECT 語句。本教程將介紹 SQL 的其他組成部分和語句，包括：

CREATE TABLE INSERT UPDATE DELETE ALTER TABLE CREATE VIEW CREATE INDEX DROP TABLE GRANT REVOKE

連接到數據庫

在使用 SQL 查詢或操縱數據之前，需要連接到一個數據庫。CONNECT 語句將一個數據庫連接與一個用戶名相關聯。

如果您使用本指南作為學校課程的一部分，那麼可以向老師詢問要連接到的數據庫的名稱。對於本系列，數據庫名為 aromadb。

要連接到 aromadb 數據庫，可以在 DB2 命令行處理器中輸入以下命令：

　CONNECT　TO　aromadb　USER　userid　USING　passWord

注意用老師告訴您的用戶 ID 和密碼替換 “userid” 和 “passWord”。如果不需要用戶 ID 和密碼，那麼只需使用以下命令：

　CONNECT　TO　aromadb

如果看到下面的消息，則說明您已經建立一個成功的連接：

　Database　Connection　Information　　Database　server　　　=　DB2/NT　9.0.0　　SQL　authorization　ID　=　USERID　　Local　database　alias　=　AROMADB

建立連接後，就可以開始使用數據庫了。

SQL 的組成部分

SQL 由三部分組成：數據操縱語言（DML）、數據定義語言（DDL）和數據控制語言（DCL）。DML 又由 SELECT、UPDATE、INSERT　和 DELETE 語句組成；DDL 則由 CREATE、ALTER　和 DROP 語句組成；數據控制語言則由 GRANT 和 REVOKE 語句組成。

SQL 組成部分語句描述 DML SELECT 用於顯式查詢結果 UPDATE 用於更改表中已有的值。UPDATE 會影響列級上的數據 INSERT 用於向表中插入新行。INSERT 會影響行級上的數據 DELETE 用於從表中刪除行。DELETE 會影響行級上的數據 DDL CREATE TABLE 用於創建新表 ALTER TABLE 用於添加、修改或撤銷列 CREATE VIEW 用於創建新的視圖 CREATE INDEX 用於創建新的索引 DROP TABLE 用於刪除表 DCL GRANT 用於將權限授予數據庫的用戶 REVOKE 用於撤銷數據庫用戶的權限

使用中的注意事項

本系列之前的教程都與 SELECT 語句有關。這也是終端用戶最常用的語句。

有些語句，例如 CREATE、GRANT　和 REVOKE，可能需要管理權限才能發出。作為一名用戶，您可能有、也可能沒有運行某些語句的許可。

CREATE TABLE 語句

練習

為了進行促銷，銷售部發起了一項新的會員活動。客戶可以購買一個 “金卡（Gold Card）”，然後就能享受各種折扣、免費禮品和電子郵件提醒促銷活動等好處。

每家商店出售預先編號的卡。

在 Aroma 數據庫中創建一個名為 Gold 的新表，用於存放金卡客戶的信息。這個表應該具有包含惟一卡號、商店代碼、名字、姓氏、電子郵箱和狀態等值的列。

例子查詢

CREATE　TABLE　aroma.gold　　　(card_id　SMALLINT　NOT　NULL　PRIMARY　KEY,　　　storekey　INTEGER　NOT　NULL,　　　fname　VARCHAR(20)　NOT　NULL,　　　lname　VARCHAR(20)　NOT　NULL,　　　email　VARCHAR(30),　　　status　VARCHAR(10)　NOT　NULL,　　　FOREIGN　KEY　(storekey)　REFERENCES　aroma.store　(storekey));

結果

可以發出一條 SELECT 語句，看看 CREATE TABLE 語句的結果。

SELECT　*　FROM　aroma.gold;

Card_id Storekey Fname Lname Email Status

CREATE TABLE 語句的語法

CREATE　TABLE　table_name　　　(column_name1　column_descrpition,　　　column_name2　column_descrpition,　　　column_name3　column_descrpition,　　　...　,　　　[FOREIGN　KEY　(column_name)　REFERENCES　table_name2　(column_name2)]　　　);

關於該查詢

該查詢使用一條 CREATE TABLE 語句在 Aroma 數據庫中創建一個名為 Gold 的表。CREATE 語句必須以 CREATE 這個單詞開頭。第二個單詞告訴 DB2 要創建的對象的類型（table），而第三個單詞則為對象命名（Gold）。

括號中是定義列的列描述區域。每個列必須指定三樣東西：列名、數據類型和 null 特征。

PRIMARY KEY 子句是可選的。該子句確保 card_id 列中的值是惟一的—— 兩張卡不能有相同的 ID。

FOREIGN KEY 子句也是可選的。它將 Gold 表的 storekey 列與 Store 表的 storekey 列連接起來。這樣可以確保商店代碼是存在且有效的，並且這兩個列包含具有相同數據類型的值。

要了解關於 CREATE TABLE 語句的更多信息，請查看 DB2 Information Center 。本系列的第 1 部分對 DB2 信息中心做了介紹。

列名

表和列必須遵從 DB2 命名慣例：

表名的首字符必須是一個字母字符或國際符號。這包括字母 A-Z、美元符號（$）、at 符號（@）和編號符（#）。

後續字符可以是字母字符、數字 0-9 和下劃線（_）的組合。在大多數情況下，下劃線可用作視覺上的分隔符。

表/列名的長度因平台而異。請參考 SQL Reference Guide（見參考資料），了解這方面的細節。

數據類型

一個列必須存儲一種特定數據類型的值。

　　數值所有數值都有一個符號和一個精度（precision）。精度是指除符號外數字的位數。數值類型包括 SMALLINT、INTEGER、BIGINT、REAL、DOUBLE 和 DECIMALS 字符串字符串是一個字節序列。字符串的長度是指序列中字節的個數。如果長度為 0，則該字符串稱做空串。字符串類型包括定長字符串和變長字符串（VARCHAR、LONG VARCHAR 和 CLOB）圖形字符串圖形字符串是一個雙字節字符數據序列。圖形字符串包括定長圖形字符串和變長圖形字符串二進制串二進制串是一個字節序列。它用於存放非傳統數據，例如圖片。二進制大對象（Binary Large OBject，BLOB）就是一種二進制串。詳細信息請參閱大對象（Large Object，LOB）日期時間值日期時間值是對日期、時間和時間戳（一個 14 位的字符串，以 yyyyxxddhhmmss 格式表示一個有效的日期和時間）的表示。日期時間值可用於某些數值和字符串操作，並且與某些字符串兼容。但是，它們既不是字符串，也不是數值。日期時間值包括 Date、Time 和 Timestamp null 值 null 值是一個特殊的值，不同於所有非空值。它意味著行中的那一列不存在任何其他值。所有數據類型都可以有 null 值 XML DB2 9 是業界第一款能同時管理純關系結構和 pureXML 結構的數據的混合型數據服務器。除了已有的關系引擎外，DB2 9 還引入了用於 XML 數據的一種優化的數據存儲引擎

要了解關於數據類型的詳細信息，請參閱本系列的第 1 部分。

null 特征

Card_id、storekey、fname 和 lname 都被定義為 NOT NULL，因為它們是每一行都必須有的重要信息。在這個表中，沒有 card_id 的行是沒有意義的。

如果忽略了 null 特征，那麼缺省為 NULL。Email 被允許為 null，因為並不是所有客戶都有電子郵箱。他們仍能夠享受折扣和免費禮品。

定義好所有的列之後，就可以定義主鍵和外鍵。這裡不需要指定外鍵所引用的主鍵，因為 DB2 可以通過編目信息得知主鍵。

在開始學習接下來的內容之前，您應該看一下 DB2 Information Center ，閱讀關於 CREATE TABLE 語句的完整的在線文檔。這有助於熟悉幫助文檔的格式，並且能回顧剛才學過的內容。

INSERT 語句

練習

一名客戶剛剛從商店代碼為 1 的商店購買了第一張金卡。她的名字叫 Ada Alexander，電子郵箱是 [email protected]。

為了便於練習，假設這家商店擁有號碼為 1 到 100 的金卡。

將她的記錄輸入到 Gold 表中，其狀態設為 Active。

例子查詢

INSERT　INTO　aroma.gold　 VALUES(1,　1,　'Ada',　'Alexander',　'[email protected]',　 'Active');

結果

SELECT　*　FROM　aroma.gold;

Card_id Storekey Fname Lname Email Status 1 1 Ada Alexander [email protected] Active

INSERT 語句的語法

INSERT　INTO　table_name　　VALUES(value1,　value2,　value3,　...);

關於該查詢

這個例子查詢闡釋了一個 INSERT 語句，該語句將一個新行添加到 Gold 表中。注意，INSERT 語句中值的順序與表中列的順序應該一致。還應注意，表中每個列對應一個值。

如果值是字符類型，那麼必須以單引號括起來。

要了解關於 INSERT 語句的詳細信息，請查看 DB2 Information Center 。

還可以使用一條 INSERT 語句將多個行插入到一個表中。

練習

接下來又有更多的客戶從商店代碼為 1 的商店購買了金卡。他/她們的姓名是 Ben Bowman - [email protected]、Cynthia Chen - [email protected]、David Doyle - [email protected]、Emily Enright - [email protected] 和 Frank Freeman - [email protected]。用一條語句將他/她們的記錄輸入到 Gold 表中。

例子查詢

INSERT　INTO　aroma.gold　 VALUES(2,　1,　'Ben',　'Bowman',　'[email protected]',　'Active'),　　　　(3,　1,　'Cynthia',　'Chen',　'[email protected]',　'Active'),　　　　(4,　1,　'David',　'Doyle',　'[email protected]',　'Active'),　　　　(5,　1,　'Emily',　'Enright',　'[email protected]',　'Active'),　　　　(6,　1,　'Frank',　'Freeman',　'[email protected]',　'Active');

結果

SELECT　*　FROM　aroma.gold;

Card_id Storekey Fname Lname Email Status 1 1 Ada Alexander [email protected] Active 2 1 Ben Bowman [email protected] Active 3 1 Cynthia Chen [email protected] Active 4 1 David Doyle [email protected] Active 5 1 Emily Enright [email protected] Active 6 1 Frank Freeman [email protected] Active

UPDATE 語句

練習

Emily Enright 最近和 Frank Freeman 結了婚，她的姓改成了 'Freeman'。更新 Gold 表，以反映這一變化。

例子查詢

UPDATE　aroma.gold　 SET　lname　=　'Freeman'　 WHERE　card_id　=　5;

結果

SELECT　*　FROM　aroma.gold;

Card_id Storekey Fname Lname Email Status 1 1 Ada Alexander [email protected] Active 2 1 Ben Bowman [email protected] Active 3 1 Cynthia Chen [email protected] Active 4 1 David Doyle [email protected] Active 5 1 Emily Freeman [email protected] Active 6 1 Frank Freeman [email protected] Active

UPDATE 語句的語法

UPDATE　table_name　 SET　column_name　=　new_value　 [WHERE　search_condition];

search_condition 該條件的值為 true 或 false

SET 子句指定要更新的列和新的列值。

WHERE 子句很重要。如果忽略了該子句，那麼表中所有行的 column_name 列都將被設為 new_value。

關於該查詢

在這個例子中，WHERE 子句用於讓 DB2 找到要更新的列。在這裡，一個惟一的標識符就變得很重要了。您只想 UPDATE 一個特定的 Emily Enright。雖然 Gold 表目前還很小，只包含一個 Emily Enright，但是它可能會不斷變大，然後包含多個具有相同姓名的記錄。因此，更安全的做法是堅持使用惟一標識符。

DELETE 語句

練習

Frank Freeman 取消了他的會員身份。從數據庫中刪除他的記錄。

例子查詢

DELETE　FROM　aroma.gold　 WHERE　card_id　=　6;

結果

DELETE 語句的語法

DELETE　FROM　table_name　 [WHERE　search_condition];

search_condition 這個條件的值為 true 或 false

WHERE 子句很重要。如果忽略了該子句，那麼表中所有的行都將被刪除。

關於該查詢

這裡的 DELETE 語句將把 Frank Freeman 從 Gold 表中刪除。同樣，這裡必須指出一個條件，條件中指定一個惟一標識符。如果條件是 “WHERE lname = 'Freeman'”，那麼 Frank 和 Emily Freeman 的記錄都將被刪除。

ALTER TABLE 語句

練習

銷售部又提出了另一個計劃。如果介紹 5 個人成為金卡成員的金卡客戶，將贏得價值 20 美元的免費禮品券。為 Gold 表增加一個名為 referrals 的列。

例子查詢

ALTER　TABLE　aroma.gold　 ADD　referrals　SMALLINT;

結果

SELECT　*　FROM　aroma.gold;

Card_id Storekey Fname Lname Email Status Referrals 1 1 Ada Alexander [email protected] Active 　 2 1 Ben Bowman [email protected] Active 　 3 1 Cynthia Chen [email protected] Active 　 4 1 David Doyle [email protected] Active 　 5 1 Emily Freeman [email protected] Active 　

ALTER TABLE 語句的語法

ALTER　TABLE　table_name　 ADD　column_name　column_definition;

關於該查詢

這個查詢增加一個名為 referrals 的新列，該列存放最長為 5 位數的整數。

新列的所有字段暫時為 null 值。

練習

card_id 列在創建時被定義為 SMALLINT 類型，它支持最長為 5 位數的整數。隨著 Gold 表的增長，可能要用到更大的 id 號。更改 card_id 列，使之包含 INTEGER 類型的值。

結果 —— 執行 ALTER 語句之前

在更改 card_id 的數據類型之前，對 syscolumns 表執行 SELECT 操作，查看當前的數據類型。

SELECT　*　FROM　sysibm.syscolumns　 WHERE　tbname='GOLD'　 AND　tbcreator='AROMA';

NAME TBNAME TBCREATOR REMARKS COLTYPE NULLS ... CARD_ID GOLD AROMA 　 SMALLINT N ... EMAIL GOLD AROMA 　 VARCHAR Y ... FNAME GOLD AROMA 　 VARCHAR N ... LNAME GOLD AROMA 　 VARCHAR N ... REFERRALS GOLD AROMA 　 SMALLINT Y ... STATUS GOLD AROMA 　 VARCHAR N ... STOREKEY GOLD AROMA 　 INTEGER N ...

例子查詢

ALTER　TABLE　aroma.gold　 ALTER　card_id　SET　data　type　INTEGER;

結果 —— 執行 ALTER 語句之後

syscolumns 表現在顯示執行 ALTER 語句之後的數據類型。

SELECT　*　FROM　sysibm.syscolumns　 WHERE　tbname='GOLD'　 AND　tbcreator='AROMA';

NAME TBNAME TBCREATOR REMARKS COLTYPE NULLS ... CARD_ID GOLD AROMA 　 INTEGER N ... EMAIL GOLD AROMA 　 VARCHAR Y ... FNAME GOLD AROMA 　 VARCHAR N ... LNAME GOLD AROMA 　 VARCHAR N ... REFERRALS GOLD AROMA 　 SMALLINT Y ... STATUS GOLD AROMA 　 VARCHAR N ... STOREKEY GOLD AROMA 　 INTEGER N ...

注意：出於篇幅的考慮，某些列有所刪節。

ALTER TABLE 語句的語法

ALTER　TABLE　table_name　 ALTER　column_name　SET　data　type　new_definition;

關於該查詢

這個查詢將 card_id 的數據類型改為 INTEGER，這種類型支持最長為 10 位數的整數。

還應注意，新增的 referrals 列的 nulls 值為 Y，這意味著這個列可以包含 null 值。在創建這個列時，我們沒有為之指定 null 特征。

要了解關於系統表的更多信息，請參閱本系列的第 1 部分：“元數據”。

練習

介紹制並沒有產生合理的結果，也沒有獲得正面的客戶反饋。於是，公司決定終止這個計劃。刪除 referrals 列。

例子查詢

ALTER　TABLE　aroma.gold　 DROP　COLUMN　referrals;

結果

ALTER TABLE 語句的語法

ALTER　TABLE　table_name　 DROP　COLUMN　colum_name;

關於該查詢

這個查詢刪除名為 referrals 的列。

CREATE VIEW 語句

練習

為了保護客戶的隱私，對於數據庫用戶，fname、lname 和 email 列必須不可見。創建 Gold 表的一個視圖，其中只包含 card_id、storekey 和 status 列。

例子查詢

CREATE　VIEW　aroma.gold_public(card_id,　storekey,　status)　AS　 SELECT　card_id,　storekey,　status　 FROM　aroma.gold;

結果

SELECT　*　FROM　aroma.gold_public;

Card_id Storekey Status 1 1 Active 2 1 Active 3 1 Active 4 1 Active 5 1 Active

CREATE VIEW 語句的語法

CREATE　VIEW　view_name(vIEw_column_list)　AS　 SELECT　table_column_list　 FROM　table_name;

vIEw_column_list 視圖中各列名稱的列表。這裡的列名與 SELECT 語句中的 table_column_list 是一對一的關系。如果沒有指定列名，那麼視圖就使用與 SELECT 語句的結果表中的列相同的名稱 table_column_list 要從 table_name 表中選擇的列的列表

關於該查詢

創建 Gold 表的一個公共視圖，只顯示客戶的 card_id、storekey 和 status 信息。

查詢的第一行為視圖命名（gold_public），並定義它的列（card_id、storekey 和 status）。名稱 gold_public 在它的模式內必須是惟一的。雖然視圖不包含數據，但是視圖名稱和表名的用法是類似的。這個視圖將有三個列，card_id、storekey 和 status，分別對應於 Gold 表的 card_id、storekey 和 status 列。

第二行是一個 SELECT 語句，描述要從 Gold 表中選擇哪些值。它可以包括子句 ALL、DISTINCT、FROM、WHERE、GROUP BY 和 HAVING。

CREATE INDEX 語句

練習

SELECT 語句直接從 Gold 表中查找 storekey 要花太多的時間。因此可以創建 storekey 上的一個索引來解決這個問題。

訪問計劃 —— 之前

使用索引可以提高數據表上的查詢性能。您可以看看訪問計劃，比較一下沒有查詢和有查詢這兩種情況下查詢的性能。

在 Command Editor 中輸入以下查詢，然後單擊 Execute and Access plan 按鈕。這將執行該查詢，並提供查詢的執行計劃的圖形化表示。

SELECT　*　from　aroma.gold;

在 Command Editor 中單擊 Access Plan 標簽，查看 DB2 訪問計劃。注意，aroma.gold 表上執行了一個表掃描（TBSCAN）。

圖 1. 建索引之前的訪問計劃
DB2 SQL 與 XQuery 教程，第 6 部分: 數據定義語言和數據控制語言

例子查詢

CREATE　INDEX　aroma.gold_index　ON　aroma.gold　(storekey);　 RUNSTATS　ON　TABLE　aroma.gold　ON　ALL　COLUMNS;　 COMMIT　WORK;

結果

為了查看查詢的性能是否有提高，在 Command Editor 中輸入相同的 SELECT 語句，然後單擊 Execute and Access plan 按鈕。

圖 2. 建索引之後的訪問計劃
DB2 SQL 與 XQuery 教程，第 6 部分: 數據定義語言和數據控制語言

CREATE INDEX 語句的語法

CREATE　INDEX　index_name　ON　table　(column_list);　 RUNSTATS　ON　TABLE　table　ON　ALL　COLUMNS;　 COMMIT　WORK;

column_list 要在其上創建索引的列的列表

最後兩行確保索引的結果會在隨後的查詢中反映出來。

關於該查詢

該查詢首先在 aroma.gold 表的 storekey 節點上創建一個索引，以提高查詢性能，然後在 aroma.gold 表上執行 RUNSTATS 操作，以確保 DB2 會考慮新索引。

由於目前表還比較小，因此在這次練習中觀察不到查詢性能有何變化。不過，這麼做的目的只是演示如何創建索引，對於具有相當大的數據量的數據而言，這樣做可以大大提高查詢性能。

DROP TABLE 語句

練習

金卡項目取得了巨大的成功。銷售部決定啟動一個新的、經過改良的計劃，即白金卡（Platinum Card）計劃。經過一段時間後，金卡項目已經被分階段撤銷。於是，這裡要刪除 Gold 表。

例子查詢

DROP　TABLE　aroma.gold;

結果

SELECT　*　FROM　sysibm.systables　 WHERE　creator='AROMA';

NAME CREATOR ... CLASS AROMA ... DEAL AROMA ... LINE_ITEMS AROMA ... MARKET AROMA ... ORDERS AROMA ... PERIOD AROMA ... PRODUCT AROMA ... PROMOTION AROMA ... SALES AROMA ... STORE AROMA ... SUPPLIER AROMA ...

關於該查詢

該查詢從 Aroma 數據庫刪除 Gold 表。這是一個不可逆轉的動作。存儲在這個表中的所有數據都要丟失。

要了解關於系統表的更多信息，請參閱本系列的第 1 部分：“元數據”。

數據控制語言

SQL 的第三個組成部分就是數據控制語言（Data Control Language，DCL）。

DCL 包括一些重要的語句，例如 GRANT 和 REVOKE。這些語句控制用戶擁有數據庫上的哪些權限，例如選擇行、更新表、刪除數據、修改表結構等。

具有不同角色的用戶執行不同的任務，並且應該被給予不同的權限。

GRANT 語句

練習

公司雇用了一個新的數據庫管理員來管理 Sales 表，他需要被授予 SELECT、INSERT、UPDATE　和 DELETE 權限。

例子查詢

GRANT　select,　insert,　update,　delete　ON　aroma.sales　TO　user02;