第3章 創(chuàng)建、修改和刪除表

SQL的數(shù)據(jù)定義功能主要體現(xiàn)在對(duì)表、索引、視圖的創(chuàng)建、修改和刪除功能。在SQL環(huán)境中，表是數(shù)據(jù)管理的基本單元，大部分SQL編程都與它有一定關(guān)系。SQL提供了3種語(yǔ)句用于定義、修改和刪除表：CREAT TABLE語(yǔ)句用戶創(chuàng)建表，ALTER TABLE語(yǔ)句用戶修改表，DROP TABLE用于刪除表。

3.1 表的基礎(chǔ)知識(shí)

關(guān)系數(shù)據(jù)庫(kù)通常包含多個(gè)表。數(shù)據(jù)庫(kù)實(shí)際是表的集合，數(shù)據(jù)庫(kù)的數(shù)據(jù)或信息都是存儲(chǔ)在表中的。表是對(duì)數(shù)據(jù)進(jìn)行存儲(chǔ)和操作的一種邏輯結(jié)構(gòu)。對(duì)用戶而言，一個(gè)表表示一個(gè)數(shù)據(jù)庫(kù)對(duì)象。例如，一個(gè)公司數(shù)據(jù)庫(kù)中，會(huì)有雇員表、部門表、庫(kù)存表、銷售表、工資表等。

3.1.1 表的基本結(jié)構(gòu)

數(shù)據(jù)庫(kù)中的表與人們?nèi)粘Ｉ钪惺褂玫谋砀耦愃疲彩怯尚校≧ow）和列（Column）組成。列由同類的信息組成，每列又稱為一個(gè)字段，每列的標(biāo)題稱為字段名。行包括了若干列信息項(xiàng)，一行數(shù)據(jù)稱為一條記錄，表達(dá)有一定意義的信息組合。一個(gè)表由一條或多條記錄組成，沒有記錄的表稱為空表。每個(gè)表中通常都有一個(gè)主關(guān)鍵字，用于唯一地確定一條記錄。

經(jīng)常見到的成績(jī)表就是一種表，它由行和列組成，并且可以通過(guò)名字來(lái)識(shí)別數(shù)據(jù)，列包含了列的名字、數(shù)據(jù)類型以及列的其他屬性；行包含了列的記錄或者數(shù)據(jù)。下面我們給出一個(gè)成績(jī)表MARK，其中學(xué)號(hào)、姓名、語(yǔ)文、數(shù)學(xué)、英語(yǔ)都是列，而行包含了這個(gè)表的數(shù)據(jù)，即每個(gè)人的各科成績(jī)，表的結(jié)構(gòu)如圖3-1所示。在SQL中，表有如下規(guī)定。

? 每張表都有一個(gè)名字，通常稱為表名或關(guān)系名。表名必須以字母開頭，最大長(zhǎng)度為30個(gè)字符。

? 一張表可以由若干列組成。同一張表中，列名唯一，列名也稱為屬性名或字段。

? 同一列的數(shù)據(jù)必須有相同的數(shù)據(jù)類型。

? 表中的每一列值必須是不可分割的基本數(shù)據(jù)項(xiàng)。

? 表中的一行稱為一個(gè)元組，相當(dāng)于一條記錄。

3.1.2 表的種類

在SQL中，并不是所有的表都是相同的。有些表是永久的，有些表則是臨時(shí)的。有些表是模式對(duì)象，而有些表則包含在模塊中，所有的模塊表也是臨時(shí)的。

SQL支持以下3種類型的表。

1.永久表

永久表保存存儲(chǔ)在數(shù)據(jù)庫(kù)中的SQL數(shù)據(jù)。它是一種最常見的表，如果沒有特別說(shuō)明，通常所說(shuō)的表就是指永久表。只要表的定義存在，永久表就始終存在。它的創(chuàng)建語(yǔ)句為CREAT TABLE。

2.全局臨時(shí)表

這種表只有在SQL會(huì)話的上下文引用該表的定義時(shí)實(shí)際的表才會(huì)存在，對(duì)話結(jié)束后表就不再存在，不能從一個(gè)SQL會(huì)話訪問在另一個(gè)會(huì)話中創(chuàng)建的表。全局臨時(shí)表的創(chuàng)建語(yǔ)句為CREAT GLOBAL TEMPORARY TABLE。

3.局部臨時(shí)表

和全局臨時(shí)表一樣，局部臨時(shí)表只有在SQL會(huì)話的過(guò)程中才能被引用，并且不能從另一個(gè)SQL會(huì)話對(duì)其進(jìn)行訪問。而與全局臨時(shí)表不同之處在于：我們?cè)赟QL會(huì)話內(nèi)的任何地方都可以訪問全局臨時(shí)表；而局部臨時(shí)表只有在相關(guān)的SQL模塊內(nèi)才能被訪問。局部臨時(shí)表的創(chuàng)建語(yǔ)句為CREAT LOCAL TEMPORARY TABLE。

本章介紹的表的創(chuàng)建、修改和刪除操作，所指的表都是永久表。

3.2 SQL數(shù)據(jù)類型

表中的列包含了列的名字、數(shù)據(jù)類型等屬性，在介紹表的創(chuàng)建之前我們有必要介紹一下有關(guān)SQL支持的數(shù)據(jù)類型。SQL支持的數(shù)據(jù)類型主要包括預(yù)定義的數(shù)據(jù)類型和用戶自定義的數(shù)據(jù)類型，其中，SQL預(yù)定義的數(shù)據(jù)類型主要包括：字符型數(shù)據(jù)、數(shù)字型數(shù)據(jù)、日期時(shí)間型數(shù)據(jù)和二進(jìn)制數(shù)據(jù)。

3.2.1 字符型數(shù)據(jù)

在數(shù)據(jù)庫(kù)表中，字符型數(shù)據(jù)是最常用的數(shù)據(jù)類型之一。例如經(jīng)常用到的通信錄中的姓名、地址、電子信箱甚至電話號(hào)碼都是字符型的。

字符型變量又可分為兩種——固定長(zhǎng)度型字符變量和可變長(zhǎng)度字符變量。固定長(zhǎng)度型字符變量的字符數(shù)在數(shù)據(jù)庫(kù)表創(chuàng)建的時(shí)候就指定了，并分配了存儲(chǔ)空間。如指定通信錄表姓名的字符數(shù)為20，如果用戶輸入姓名的字符數(shù)超過(guò)20，那么數(shù)據(jù)庫(kù)只記錄前20個(gè)字符；如果輸入的字符數(shù)少于20，則數(shù)據(jù)庫(kù)會(huì)自動(dòng)在字符右邊以空格填補(bǔ)到20個(gè)字符。

可變長(zhǎng)度字符串可以存儲(chǔ)任意長(zhǎng)度的字符（其最大存儲(chǔ)長(zhǎng)度取決于采用的數(shù)據(jù)類型和數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）），它不需要預(yù)先指定存儲(chǔ)長(zhǎng)度，而是根據(jù)用戶的輸入動(dòng)態(tài)地分配存儲(chǔ)空間。既然可變長(zhǎng)度字符變量用起來(lái)如此靈活，那么為什么我們還要采用固定長(zhǎng)度字符變量呢？這是因?yàn)楣潭ㄩL(zhǎng)度字符變量相比于可變長(zhǎng)度字符變量，有以下兩點(diǎn)優(yōu)勢(shì)。

? DBMS在進(jìn)行排序或處理字符時(shí)，對(duì)固定長(zhǎng)度字符變量的處理效率要遠(yuǎn)遠(yuǎn)高于可變長(zhǎng)度字符變量。

? 許多DBMS不允許為可變長(zhǎng)度字符變量創(chuàng)建索引，而只允許對(duì)固定長(zhǎng)度字符變量創(chuàng)建索引。

SQL常用的字符型數(shù)據(jù)類型如表3-1所示。

在數(shù)據(jù)庫(kù)中，字符型數(shù)據(jù)的應(yīng)用是十分靈活與廣泛的。如前面提到的電話號(hào)碼信息，雖然是一些數(shù)字信息，但是一般均采用字符型數(shù)據(jù)進(jìn)行存儲(chǔ)。如某人的電話號(hào)碼為“01067846050”，采用數(shù)字?jǐn)?shù)據(jù)存儲(chǔ)，則最左邊的“0”將被忽略，記錄的信息實(shí)際為“1067846050”；而采用字符型數(shù)據(jù)進(jìn)行存儲(chǔ)則不會(huì)出現(xiàn)這個(gè)問題。

3.2.2 數(shù)字型數(shù)據(jù)

數(shù)字型數(shù)據(jù)主要用來(lái)存儲(chǔ)數(shù)據(jù)信息。所有的數(shù)字?jǐn)?shù)據(jù)類型都有精度，有些數(shù)據(jù)類型還有范圍。精度是指可以存儲(chǔ)的數(shù)字的數(shù)據(jù)，范圍是指該值的小數(shù)部分（小數(shù)點(diǎn)右邊的數(shù)字）數(shù)字的數(shù)目。比如，數(shù)字3428.54的精度是6，范圍是2。范圍不能是負(fù)數(shù)，也不能大于精度。范圍為0表示該數(shù)字是一個(gè)整數(shù)，不包含小數(shù)部分。

多數(shù)數(shù)據(jù)庫(kù)管理系統(tǒng)支持多種類型的數(shù)字型數(shù)據(jù)，不同的類型支持不同的數(shù)字形式與數(shù)字范圍。SQL常用的數(shù)字型數(shù)據(jù)類型如表3-2所示。

當(dāng)處理那種只有兩個(gè)值的數(shù)據(jù)時(shí)，我們可以使用Bit數(shù)據(jù)類型，如可使用Bit字段來(lái)存儲(chǔ)Yes/No或者Ture/Fasle調(diào)查問題的答案。按照通常的約定，在Bit中的1表示True，0表示Fasle。

在數(shù)據(jù)庫(kù)中，Number數(shù)據(jù)類型運(yùn)用最普遍，它常用兩種形式Number(size)和Number(size,d)，具體含義如表3-3所示。

另外，多數(shù)數(shù)據(jù)庫(kù)管理系統(tǒng)提供了一種特殊的數(shù)字類型數(shù)據(jù)——貨幣數(shù)據(jù)類型。貨幣數(shù)據(jù)類型用于存儲(chǔ)貨幣值，在使用貨幣數(shù)據(jù)類型時(shí)，應(yīng)在數(shù)據(jù)前加上貨幣符號(hào)，系統(tǒng)才能辨識(shí)其為哪國(guó)的貨幣。如果不加貨幣符號(hào)，則默認(rèn)為“￥”。

在SQL Server數(shù)據(jù)庫(kù)系統(tǒng)中，貨幣數(shù)據(jù)類型有MONEY和SMALLMONEY兩種類型，如表3-4所示。

3.2.3 日期數(shù)據(jù)類型

和數(shù)字型數(shù)據(jù)類型一樣，DBMS提供了多種日期類型，用于存儲(chǔ)不同精度和范圍的時(shí)間、日期數(shù)據(jù)。SQL-92標(biāo)準(zhǔn)指定了5種日期數(shù)據(jù)類型，如表3-5所示。

然而，并不是所有的DBMS產(chǎn)品都支持所有這5種標(biāo)準(zhǔn)的SQL日期時(shí)間數(shù)據(jù)類型。不同DBMS中往往采用的時(shí)間、日期數(shù)據(jù)類型也不盡相同。

如在Oracle數(shù)據(jù)庫(kù)中，日期型數(shù)據(jù)，用7個(gè)字節(jié)表示，每個(gè)日期型數(shù)據(jù)包含如下內(nèi)容。

? Century（世紀(jì)）

? Year（年）

? Month（月）

? Day（天）

? Hour（小時(shí)）

? Minute（分）

? Second（秒）

在SQLServer數(shù)據(jù)庫(kù)系統(tǒng)中，用到的日期和時(shí)間數(shù)據(jù)類型為DATETIME和SMALL DATETIME。

? DATETIME數(shù)據(jù)類型用于存儲(chǔ)日期和時(shí)間的結(jié)合體。它可以存儲(chǔ)從公元1753年1月1日零時(shí)起到公元9999年12月31日23時(shí)59分59秒之間的所有日期和時(shí)間，其精確度可達(dá)三百分之一秒，即3.33毫秒。DATETIME數(shù)據(jù)類型所占用的存儲(chǔ)空間為8個(gè)字節(jié)，其中前4個(gè)字節(jié)用于存儲(chǔ)1900年1月1日以前或以后的天數(shù)，數(shù)值分正負(fù)，正數(shù)表示在此日期之后的日期，負(fù)數(shù)表示在此日期之前的日期；后4個(gè)字節(jié)用于存儲(chǔ)從此日零時(shí)起所指定的時(shí)間經(jīng)過(guò)的毫秒數(shù)。如果我們?cè)谳斎霐?shù)據(jù)時(shí)省略了時(shí)間部分，則系統(tǒng)將12:00:00:000AM作為時(shí)間缺省值；如果省略了日期部分，則系統(tǒng)將1900年1月1日作為日期缺省值。

? SMALLDATETIME數(shù)據(jù)類型與DATETIME數(shù)據(jù)類型相似，但其日期時(shí)間范圍較小，為從1900年1月1日到2079年6月6日。精度較低，只能精確到分鐘，以30秒為界進(jìn)行四舍五入。如DATETIME時(shí)間為14:38:30.283時(shí)，SMALLDATE TIME認(rèn)為是14:39:00。SMALLDATETIME數(shù)據(jù)類型使用4個(gè)字節(jié)存儲(chǔ)數(shù)據(jù)，其中前兩個(gè)字節(jié)存儲(chǔ)從基礎(chǔ)日期1900年1月1日以來(lái)的天數(shù)，后兩個(gè)字節(jié)存儲(chǔ)此日零時(shí)起所指定的時(shí)間經(jīng)過(guò)的分鐘數(shù)。

下面我們以SQL Server數(shù)據(jù)庫(kù)系統(tǒng)為準(zhǔn)，介紹日期和時(shí)間的輸入格式。

1.日期輸入格式

日期的輸入格式很多，大致可分為3類。

（1）英文+數(shù)字格式：此類格式中，月份可用英文全名或縮寫，且不區(qū)分大小寫；年和月日之間可不用逗號(hào)；年份可為4位或兩位，當(dāng)其為兩位時(shí)，若值小于50，則視為20××年，若大于或等于50，則視為19××年；若日部分省略，則視為當(dāng)月的1號(hào)。以下格式均為正確的日期格式。

    June 212000     Oct 11999     January 2000        2000 February
    2000 May 1    2000 1 Sep        99 June          July 00

（2）數(shù)字+分隔符格式：允許把斜杠（/）、連接符（-）和小數(shù)點(diǎn)（.）作為用數(shù)字表示的年、月、日之間的分隔符，如下所示。

    YMD ：  2000/6/22   2000-6-22   2000.6.22
    MDY ：  3/5/2000    3-5-2000    3.5.2000
    DMY ：  31/12/1999  31-12-1999  31.12.2000

（3）純數(shù)字格式：純數(shù)字格式是以連續(xù)的4位、6位或8位數(shù)字來(lái)表示日期。如果輸入的是6位或8位數(shù)字，系統(tǒng)將按年、月、日來(lái)識(shí)別，即YMD格式，并且月和日都是用兩位數(shù)字來(lái)表示；如果輸入的數(shù)字是4位數(shù)，系統(tǒng)認(rèn)為這4位數(shù)代表年份，其月份和日缺省為此年度的1月1日，如下所示。

    20000601——2000年6月1日
    991212——1999年12月12日
    1998——1998年

2.時(shí)間輸入格式

在輸入時(shí)間時(shí)，我們必須按“小時(shí)、分鐘、秒、毫秒”的順序來(lái)輸入，在其間用冒號(hào)“:”隔開，毫秒部分可以用小數(shù)點(diǎn)“.”分隔，其后第一位數(shù)字代表十分之一秒，第二位數(shù)字代表百分之一秒，第三位數(shù)字代表千分之一秒。

當(dāng)使用12小時(shí)制時(shí)，我們用AM（am）和PM（pm）分別指定時(shí)間是午前或午后，若不指定，系統(tǒng)默認(rèn)為AM。AM與PM均不區(qū)分大小寫，如下所示。

    3:5:7.2pm——下午3時(shí)5分7秒200毫秒
    10:23:5.123Am——上午10時(shí)23分5秒123毫秒

在SQL Server中，我們可以使用SET DATEFORMAT命令來(lái)設(shè)定系統(tǒng)默認(rèn)的日期時(shí)間格式。

3.2.4 二進(jìn)制數(shù)據(jù)類型

與前面介紹的數(shù)據(jù)類型不同，二進(jìn)制數(shù)據(jù)類型不專門用于存儲(chǔ)特定類型的數(shù)據(jù)，幾乎可以存儲(chǔ)任何類型的數(shù)據(jù)，包括圖形圖像、多媒體和字處理文檔等。SQL中常用的二進(jìn)制數(shù)據(jù)類型如表3-6所示。

SQL Server數(shù)據(jù)庫(kù)系統(tǒng)中，采用的二進(jìn)制數(shù)據(jù)類型為BINARY和VARBINARY。一般情況下，BINARY數(shù)據(jù)類型長(zhǎng)度固定，因此它比VARBINARY類型的處理速度快。我們分別作一下簡(jiǎn)單介紹。

1.BINARY

BINARY數(shù)據(jù)類型用于存儲(chǔ)二進(jìn)制數(shù)據(jù)。其定義形式為BINARY（n），n表示數(shù)據(jù)的長(zhǎng)度，取值為1到8000。在使用時(shí)必須指定BINARY類型數(shù)據(jù)的大小，至少應(yīng)為1個(gè)字節(jié)。BINARY類型數(shù)據(jù)占用n+4個(gè)字節(jié)的存儲(chǔ)空間，在輸入數(shù)據(jù)時(shí)必須在數(shù)據(jù)前加上字符“0x”作為二進(jìn)制標(biāo)識(shí)，例如要輸入“abc”則應(yīng)輸入“0xabc”，如果輸入的數(shù)據(jù)過(guò)長(zhǎng)，將會(huì)截掉其超出部分；如果輸入的數(shù)據(jù)位數(shù)為奇數(shù)，則會(huì)在起始符號(hào)“0x”后添加一個(gè)0，如上述的“0xabc”會(huì)被系統(tǒng)自動(dòng)變?yōu)椤?x0abc”。

2.VARBINARY

VARBINARY數(shù)據(jù)類型的定義形式為VARBINARY（n）。與BINARY類型相似，n的取值也為1到8000，如果輸入的數(shù)據(jù)過(guò)長(zhǎng)，將會(huì)截掉其超出部分。不同的是，VARBINARY數(shù)據(jù)類型具有變動(dòng)長(zhǎng)度的特性，因?yàn)閂ARBINARY數(shù)據(jù)類型的存儲(chǔ)長(zhǎng)度為實(shí)際數(shù)值長(zhǎng)度+4個(gè)字節(jié)。當(dāng)BINARY數(shù)據(jù)類型允許NULL值時(shí)，將被視為VARBINARY數(shù)據(jù)類型。

3.2.5 文本和圖形數(shù)據(jù)類型

另外，SQL Server還提供了文本和圖形數(shù)據(jù)類型，這類數(shù)據(jù)類型用于存儲(chǔ)大量的字符或二進(jìn)制數(shù)據(jù)，分別介紹如下。

1.TEXT

TEXT數(shù)據(jù)類型用于存儲(chǔ)大量文本數(shù)據(jù)，其容量理論上為1～231-1（2147 483647）個(gè)字節(jié)，在實(shí)際應(yīng)用時(shí)需要視硬盤的存儲(chǔ)空間而定。

2.NTEXT

NTEXT數(shù)據(jù)類型與TEXT類型相似。不同的是，NTEXT類型采用UNICODE標(biāo)準(zhǔn)字符集（Character Set），因此其理論容量為230-1（1073 741823）個(gè)字節(jié)。

3.IMAGE

IMAGE數(shù)據(jù)類型用于存儲(chǔ)大量的二進(jìn)制數(shù)據(jù)（Binary Data），理論容量為231-1（2147 483647）個(gè)字節(jié)，存儲(chǔ)數(shù)據(jù)的模式與TEXT數(shù)據(jù)類型相同。它通常用來(lái)存儲(chǔ)圖形等OLE（Object Linking and Embedding，對(duì)象連接和嵌入）對(duì)象。在輸入數(shù)據(jù)時(shí)同BINARY數(shù)據(jù)類型一樣，我們必須在數(shù)據(jù)前加上字符“0x”作為二進(jìn)制標(biāo)識(shí)。

3.2.6 自定義數(shù)據(jù)類型

除了使用系統(tǒng)提供的數(shù)據(jù)類型外，用戶還可以用自定義的數(shù)據(jù)類型來(lái)定義表的列或聲明變量。SQL提供的CREATE TYPE語(yǔ)句用于自定義數(shù)據(jù)類型，如下面語(yǔ)句將創(chuàng)建一個(gè)基于NUMERIC數(shù)據(jù)類型的用戶自定義的類型。

    CREATE TYPE   Wage   AS  NUMERIC(6,1)

創(chuàng)建了這個(gè)用戶定義的數(shù)據(jù)類型后，我們就可以像其他預(yù)定義數(shù)據(jù)類型一樣使用了。因此Wage數(shù)據(jù)類型就代表NUMERIC數(shù)據(jù)類型，其精度為6，范圍為1。

然而，創(chuàng)建自定義數(shù)據(jù)類型的方法和數(shù)據(jù)庫(kù)產(chǎn)品有很大的關(guān)系，如在SQL Server中，我們就不能采用CREATE TYPE語(yǔ)句定義用戶的數(shù)據(jù)類型，可以采用其提供的企業(yè)管理器（Enterprise Manager）創(chuàng)建一個(gè)用戶定義的數(shù)據(jù)類型。

實(shí)例1 使用企業(yè)管理器建一個(gè)用戶定義的數(shù)據(jù)類型

我們同樣以創(chuàng)建Wage數(shù)據(jù)類型為例，講解其創(chuàng)建步驟。

（1）啟動(dòng)企業(yè)管理器（Enterprise Manager），找到要操作的數(shù)據(jù)庫(kù)。

（2）右擊該數(shù)據(jù)庫(kù)名，打開快捷菜單，選擇“新建”→“用戶定義的數(shù)據(jù)類型”命令，如圖3-2所示。

（3）在彈出的用戶定義的數(shù)據(jù)類型對(duì)話框中，在“名稱”區(qū)域鍵入數(shù)據(jù)類型的名稱。對(duì)于本例，鍵入“Wage”。

（4）在“數(shù)據(jù)類型”下拉列表中選擇一個(gè)數(shù)據(jù)類型，對(duì)于本例來(lái)說(shuō)，選擇“numeric”。

（5）在“長(zhǎng)度”區(qū)域鍵入數(shù)據(jù)長(zhǎng)度，對(duì)于本例鍵入“6,1”。如果想要對(duì)用戶定義的數(shù)據(jù)類型的列允許NULL值，則選中“允許NULL值”復(fù)選框，對(duì)話框的設(shè)置如圖3-3所示。

（6）單擊“確定”按鈕即可實(shí)現(xiàn)數(shù)據(jù)類型的定義。

完成后，企業(yè)管理器將把數(shù)據(jù)類型定義保存在DBMS的系統(tǒng)表中。這樣我們就可以使用剛才定義的數(shù)據(jù)類型了。

3.3 表的創(chuàng)建（CREATE）

常用的創(chuàng)建數(shù)據(jù)庫(kù)表的方法有兩種：一種是通過(guò)數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）提供的交互式創(chuàng)建工具創(chuàng)建，另一種是通過(guò)SQL直接創(chuàng)建。本節(jié)主要討論通過(guò)SQL直接創(chuàng)建數(shù)據(jù)庫(kù)表。

3.3.1 創(chuàng)建基本表

在SQL中，創(chuàng)建數(shù)據(jù)庫(kù)表的基本關(guān)鍵字為Create Table，在其后要指明創(chuàng)建的數(shù)據(jù)庫(kù)表的名稱，接著要分別定義表中各列的名稱、數(shù)據(jù)類型等。語(yǔ)法如下。

    CREATE TABLE table_name
    (
    column_name1    data_type,
    column_name2    data_type,
    ......
    )

表的名字對(duì)大小寫不敏感。表名要緊接在Create Table關(guān)鍵詞的后面，且第一個(gè)字符必須是A～Z之一，其余的字符可以是字母，也可以是“_”、“#”、“$”和“@”等符號(hào)。表中各列的定義在括號(hào)中完成，且各列之間以逗號(hào)隔開。不同的表，其列名可以相同，但是在同一個(gè)表中，不允許出現(xiàn)相同的列名。在定義了列名后，我們一定要指明該列的數(shù)據(jù)類型。

實(shí)例2 創(chuàng)建數(shù)據(jù)庫(kù)表

創(chuàng)建一個(gè)學(xué)生表（student），包含學(xué)號(hào)（sno，DECIMAL型）、姓名（sname，CHAR型）、性別（sex，CHAR型）、出生年月（birthday，DATETIME型）、系號(hào)（dno，CHAR型）等幾個(gè)字段信息。實(shí)現(xiàn)代碼如下。

    CREATE TABLE student
    (
    sno  DECIMAL,
    sname  CHAR,
    sex  CHAR,
    birthday  DATETIME,
    dno  CHAR
    )

運(yùn)行結(jié)果如圖3-4所示。

默認(rèn)情況下，SQL Server會(huì)根據(jù)類型不同，為每個(gè)字段設(shè)定最大數(shù)據(jù)長(zhǎng)度。用戶實(shí)際創(chuàng)建表時(shí)，都需要根據(jù)自己的數(shù)據(jù)特點(diǎn)，定義字段的最大存儲(chǔ)長(zhǎng)度。

實(shí)例3 指定表字段的最大長(zhǎng)度

如果最多有幾萬(wàn)個(gè)學(xué)生，那么學(xué)號(hào)sno則有五位數(shù)就可以了，即可以定義為：sno DECIMAL (5)。對(duì)實(shí)例2，指定最大字段長(zhǎng)度可用以下代碼實(shí)現(xiàn)。

    CREATE TABLE student
    (
    sno  DECIMAL (5) ,
    sname  CHAR(6) ,
    sex  CHAR(2) ,
    birthday  DATETIME ,
    dno  CHAR(3)
    )

運(yùn)行結(jié)果如圖3-5所示。

3.3.2 非空約束

SQL允許表中列的值為NULL，即空值。表中某列的值為NULL，表明該列不存在值。創(chuàng)建數(shù)據(jù)庫(kù)表時(shí)，數(shù)據(jù)庫(kù)管理系統(tǒng)允許通過(guò)NOT NULL關(guān)鍵字為創(chuàng)建的每列指明非空約束，這樣在添加數(shù)據(jù)時(shí)，如果該列的值為NULL，那么數(shù)據(jù)庫(kù)就會(huì)報(bào)錯(cuò)，添加失敗。

非空約束在實(shí)際應(yīng)用中是十分有用的，它保證了信息的完整性，避免了由于用戶的誤操作而導(dǎo)致的數(shù)據(jù)的不完整。

實(shí)例4 創(chuàng)建非空約束

創(chuàng)建與3.3.1節(jié)相同的學(xué)生表（student），為學(xué)號(hào)（sno）、姓名（sname）、性別（sex）3列添加非空約束。代碼如下。

    CREATE TABLE student
    (
    sno  DECIMAL(5)   NOT NULL,
    sname  CHAR(6)    NOT NULL,
    sex  CHAR(2)      NOT NULL,
    birthday  DATETIME ,
    dno  CHAR(3)
    )

運(yùn)行結(jié)果如圖3-6所示。

實(shí)例4 創(chuàng)建的表中，sno、sname和sex 3列為非空（NOT NULL）的，即如果插入的值為NULL，則數(shù)據(jù)庫(kù)會(huì)報(bào)錯(cuò)，插入失敗。而余下的birthday和dno兩列則允許為空（NULL）。在創(chuàng)建表時(shí)，各列缺省設(shè)置認(rèn)為是允許為NULL的，除非指明NOT NULL屬性。

例如，通過(guò)如下代碼向student表中添加數(shù)據(jù)。

    INSERT INTO student(sno,sex,birthday,dno)
    VALUES (12,'男','1979/9/4',5)

由于沒有給非空字段sname賦值，數(shù)據(jù)庫(kù)系統(tǒng)會(huì)報(bào)告錯(cuò)誤信息，插入操作被取消。在查詢分析器的Result窗格將顯示如下信息。

    Cannot insert the value NULL into column 'sname', table 'pubs.dbo.student';
column does not allow nulls. INSERT fails.
    The statement has been terminated.

實(shí)例5 允許為空字段的插入操作

當(dāng)然，如果對(duì)允許為空的字段不給賦值，插入操作也會(huì)正確地執(zhí)行，如下面代碼所示。

    INSERT INTO student(sno,sname,sex,dno)
    VALUES (12,'王小二','男',5)
    SELECT * FROM student

運(yùn)行結(jié)果如圖3-7所示。

我們可以發(fā)現(xiàn)，雖然沒有對(duì)birthday字段賦值，但是因?yàn)樗梢詾镹ULL，所以插入數(shù)據(jù)操作能夠成功執(zhí)行。

3.3.3 DEFAULT指定缺省值

采用SQL創(chuàng)建表時(shí)，允許為每列指定缺省值。即向表中插入值時(shí)，當(dāng)不指明該列的值時(shí)，數(shù)據(jù)庫(kù)會(huì)自動(dòng)地采用缺省值。指定缺省值是通過(guò)DEFAULT關(guān)鍵字來(lái)實(shí)現(xiàn)的。

具有缺省值的列定義的簡(jiǎn)單語(yǔ)法如下。

    <column name>  <data type>  DEFAULT  <default value>

在DEFAULT關(guān)鍵字后面指定該列的缺省值，該值可以是SQL數(shù)據(jù)值、日期值或者與會(huì)話有關(guān)的用戶功能。無(wú)論<default value>使用什么類型的值，它都必須符合在列定義中指定的數(shù)據(jù)要求。比如，如果我們使用INT數(shù)據(jù)類型，那么指定的默認(rèn)值也必須是INT數(shù)據(jù)類型的。

實(shí)例6 指定表中列的缺省值

創(chuàng)建與3.3.2節(jié)相同的學(xué)生表（student），為性別（sex）列指定缺省值為“男”。

實(shí)例的代碼如下。

    CREATE TABLE student
    (
    sno  DECIMAL(5)   NOT NULL,
    sname  CHAR(6)    NOT NULL,
    sex  CHAR(2)      NOT NULL   DEFAULT  '男',
    birthday  DATETIME,
    dno  CHAR(3)
    )

當(dāng)向表student中插入記錄時(shí)，如果不指明sex的值，則數(shù)據(jù)庫(kù)會(huì)為其添加缺省值“男”，如下面的插入數(shù)據(jù)代碼。

    INSERT INTO student(sno,sname,dno)
    VALUES (12,'王小二',5)

代碼并沒有給sex字段插入數(shù)據(jù)，而sex字段設(shè)置有非空約束，但是因?yàn)樵趧?chuàng)建表時(shí)，為sex字段指定了默認(rèn)值，所以數(shù)據(jù)庫(kù)系統(tǒng)并沒有報(bào)錯(cuò)，插入操作成功完成。

    SELECT * FROM student

運(yùn)行結(jié)果如圖3-8所示。

在設(shè)置日期類型值時(shí)，我們通常指定當(dāng)前日期為缺省日期。在SQL Server中，我們可采用DEFAULT GETDATE()指定當(dāng)前日期為缺省日期，而在Oracle數(shù)據(jù)庫(kù)系統(tǒng)則是通過(guò)DEFAULT SYSDATE來(lái)實(shí)現(xiàn)。表3-7列出了不同數(shù)據(jù)庫(kù)系統(tǒng)獲取當(dāng)前日期的函數(shù)。

3.4 表的修改

在實(shí)際設(shè)計(jì)和創(chuàng)建數(shù)據(jù)庫(kù)表的時(shí)候，我們很難做到一步到位，往往需要在使用的過(guò)程中，不斷地修改完善。在SQL中，我們可以采用ALTER TABLE命令來(lái)修改已經(jīng)創(chuàng)建的表結(jié)構(gòu)。使用ALTER TABLE命令可以向表中增加新列、刪除已有的列、也可以修改已經(jīng)創(chuàng)建的列。

3.4.1 增加新列

給表增加新列在數(shù)據(jù)庫(kù)的應(yīng)用中是經(jīng)常需要用到的。當(dāng)一個(gè)數(shù)據(jù)庫(kù)表創(chuàng)建完成后，在使用過(guò)程中隨著時(shí)間的推移，往往就需要向其中增加新的信息。如前面介紹的student表，應(yīng)用中我們還需要統(tǒng)計(jì)每個(gè)學(xué)生的Email信息，由于student表中可能包含了大量的數(shù)據(jù)，再重新創(chuàng)建表顯然已經(jīng)不現(xiàn)實(shí)了，這時(shí)，就可以采用SQL提供的ADD關(guān)鍵字向表中添加新列。語(yǔ)法如下。

    ALTER TABLE         table_name
    ADD     column_name         data_type

table_name指的是要修改的表的名字，ADD關(guān)鍵字后面接要?jiǎng)?chuàng)建列的列名、數(shù)據(jù)類型等，當(dāng)然也可以對(duì)列設(shè)置非空約束和缺省值。

當(dāng)用ALTER TABLE語(yǔ)句向表中添加新列時(shí)，DBMS向表的列定義的尾部添加列，即在查詢中將位于表的最右邊。除非指定默認(rèn)值，DBMS為已有行上的新列設(shè)NULL值。

由于DBMS為已有行上的新列設(shè)NULL值，當(dāng)使用ALTER TABLE語(yǔ)句向表中添加新列時(shí)，我們不能簡(jiǎn)單地添加NOT NULL約束，還必須提供缺省值。因?yàn)槿绻麤]有提供缺省值，DBMS假設(shè)已有行上的新列為NULL值，這就和NOT NULL約束相抵觸。當(dāng)然，如果表中不存在數(shù)據(jù)，則不存在這個(gè)問題。

實(shí)例7 向已有表中增加非空約束的列

這里向3.3.3節(jié)創(chuàng)建的學(xué)生表（student）中添加一列Email信息，并要求該列具有非空約束。

因?yàn)樵搒tudent表中已經(jīng)有了一條記錄，所以如果對(duì)增加列采用NOT NULL約束而不指定缺省值，如下面的代碼所示。

    ALTER TABLE    student
    ADD    Email  CHAR(15)  NOT NULL

則SQL Server數(shù)據(jù)庫(kù)系統(tǒng)會(huì)報(bào)錯(cuò)，在查詢分析器的Result窗格將顯示如下信息。

    ALTER TABLE only allows columns to be added that can contain nulls or have a
DEFAULT definition specified. Column 'Email' cannot be added to table 'student'
because it does not allow nulls and does not specify a DEFAULT definition.

實(shí)例8 增加非空約束的列，指明缺省值

對(duì)增加的列采用NOT NULL約束，必須指明缺省值。實(shí)例代碼如下。

    ALTER TABLE    student
    ADD    Email  CHAR(15)  NOT NULL DEFAULT 'No Email'

運(yùn)行結(jié)果如圖3-9所示。

查看student表中的數(shù)據(jù)，代碼如下。

    SELECT * FROM student

結(jié)果如圖3-10所示。

我們可以發(fā)現(xiàn)，在表的最右邊增加了一列Email，并將記錄中該列的值設(shè)為默認(rèn)值“No Email”。

3.4.2 刪除列

同樣，在使用數(shù)據(jù)庫(kù)表的過(guò)程中，如果其某列信息已經(jīng)無(wú)效或不再需要，為了節(jié)省數(shù)據(jù)庫(kù)空間，提高查詢性能，我們可以采用DROP COLUMN關(guān)鍵字刪除表中的某列，語(yǔ)法如下。

    ALTER TABLE table_name
    DROP COLUMN  column_name

table_name是要修改的表的名字，DROP COLUMN關(guān)鍵字后面接要?jiǎng)h除列的名字。當(dāng)然，一次可以刪除多個(gè)列，只需要在DROP COLUMN關(guān)鍵字后面依次列出要?jiǎng)h除的列的名字，中間用逗號(hào)分開即可。

實(shí)例9 刪除學(xué)生表中的birthday列

將3.4.1節(jié)中最終生成的學(xué)生表（student）中的birthday列刪除。實(shí)例代碼如下。

    ALTER TABLE     student
    DROP COLUMN     birthday

運(yùn)行結(jié)果如圖3-11所示。

我們可以發(fā)現(xiàn)birthday列被刪除，當(dāng)然，birthday列中的數(shù)據(jù)也就不復(fù)存在。

然而，在SQL Server中，那些賦予默認(rèn)值的列、作為主鍵或者在另外表中標(biāo)識(shí)為外鍵約束的列將不能被刪除。而具有非空約束的列則可以被刪除。

實(shí)例10 直接刪除具有非空約束的列

繼續(xù)刪除student表中的sex列。

    ALTER TABLE     student
    DROP COLUMN     sex

查詢分析器的Result窗格將顯示如下信息。

    Server: Msg 5074, Level 16, State 1, Line 1
    The object 'DF__student__sex__4CA06362' is dependent on column 'sex'.
    Server: Msg 4922, Level 16, State 1, Line 1
    ALTER TABLE DROP COLUMN sex failed because one or more objects access this
column.

由此可見，；因?yàn)閟ex列設(shè)有默認(rèn)值，所以系統(tǒng)不允許直接刪除sex列。如果一定要?jiǎng)h除該列，我們必須首先采用ALTER TABLE語(yǔ)句刪除默認(rèn)值約束。語(yǔ)法如下。

    ALTER TABLE table_name
    DROP CONSTRAINT  constraint_name

constraint_name為要?jiǎng)h除的約束對(duì)應(yīng)的約束名，是由DBMS創(chuàng)建的。對(duì)于sex列的缺省值約束來(lái)說(shuō)，約束名可從直接刪除該列系統(tǒng)的出錯(cuò)報(bào)告信息中獲取，為“DF__student__sex__4CA06362”。

實(shí)例11 刪除約束后，刪除非空約束的列

刪除sex列的約束后，再刪除sex列的代碼如下。

    ALTER TABLE     student
    DROP CONSTRAINT     DF__student__sex__4CA06362
    ALTER TABLE     student
    DROP COLUMN     sex

運(yùn)行結(jié)果如圖3-12所示。

可見，sex列已經(jīng)被成功刪除。

3.4.3 修改列

如果發(fā)現(xiàn)數(shù)據(jù)庫(kù)表中某列的結(jié)構(gòu)不能滿足實(shí)際的需求，在不破壞數(shù)據(jù)的情況下，SQL允許利用MODIFY關(guān)鍵字修改表中某列的結(jié)構(gòu)。常用的修改操作主要包括字符長(zhǎng)度限制的修定和非空約束的限制或取消，語(yǔ)法如下。

    ALTER TABLE table_name
    MODIFY column_name data_type

table_name是要修改的表的名字，MODIFY關(guān)鍵字后面接要修改列的列名和修改后的數(shù)據(jù)條件。

實(shí)例12 修改列

在3.4.2節(jié)中最終生成的學(xué)生表（student）中，將Email列的最大字符數(shù)由15增加到25。實(shí)例代碼如下。

    ALTER TABL      student
    ALTER COLUMN Email  CHAR(25)

運(yùn)行結(jié)果如圖3-13所示。

我們可以發(fā)現(xiàn)，Email列的數(shù)據(jù)允許的最大長(zhǎng)度已經(jīng)更改為25。

當(dāng)然，我們也可以通過(guò)MODIFY或者ALTER COLUMN關(guān)鍵字增加或取消表中某列的非空約束。

實(shí)例13 取消student表中sname列的非空約束

    ALTER TABLE     student
    ALTER COLUMN        sname  CHAR(10)  NULL

運(yùn)行結(jié)果如圖3-14所示。

修改表中的某列，為其增加非空約束時(shí)，我們必須要確保該列已經(jīng)存在的所有記錄中沒有NULL值。

與許多DBMS產(chǎn)品不同，SQL Serve不僅允許改變列的數(shù)據(jù)長(zhǎng)度，而且還允許改變其數(shù)據(jù)類型。但是當(dāng)列為以下情況時(shí)，我們則不能改變其數(shù)據(jù)類型。

? 列是TEXT、IMAGE、NTEXT或TIMESTAMP類型。

? 列是索引的一部分。

? 列是PRIMARY或者KEY FOREIGN KEY的一部分。

? 列具有缺省值約束。

? 列具有CHECK約束。

? 列具有UNIQUE約束。

當(dāng)改變列的數(shù)據(jù)類型時(shí)，列中已有的數(shù)據(jù)必須與新的數(shù)據(jù)類型兼容。比較常用的轉(zhuǎn)換是從INTEGER轉(zhuǎn)換為字符型，因?yàn)镃HAR列可保存數(shù)字、字母和特殊符號(hào)。但是，如果要將CHAR類型轉(zhuǎn)換為INTEGER類型，必須保證表的每行上所轉(zhuǎn)換的CHAR字段具有數(shù)字或NULL值。

實(shí)例14 將student表的sno列的類型轉(zhuǎn)換為CHAR類型

    ALTER TABLE     student
    ALTER COLUMN        sno CHAR(5)  NOT NULL

運(yùn)行結(jié)果如圖3-15所示。

我們可以發(fā)現(xiàn)，sno列的數(shù)據(jù)類型已經(jīng)變?yōu)镃HAR類型了。

    SELECT * FROM student

結(jié)果如圖3-16所示。

sno列的記錄并沒有變化，實(shí)際上只是變?yōu)樽址?2”而已。

3.5 表的刪除與重命名

3.5.1 重命名表

在創(chuàng)建表的時(shí)候，表的名字就被確定了，但在實(shí)際應(yīng)用中，有時(shí)候需要修改表的名字而不改變其他信息，這時(shí)候就可以采用SQL的重命名表命令。

不同的DBMS對(duì)表的重命名提供的命令不盡相同。在DB2、MySQL,、Oracle數(shù)據(jù)庫(kù)系統(tǒng)中可采用RENAME關(guān)鍵詞，而在SQL Server和Sybase數(shù)據(jù)庫(kù)系統(tǒng)中可采用SP_RENAME關(guān)鍵詞重命名表。

實(shí)例15 重命名表

將學(xué)生表（student）重命名為Mystudent。

    SP_RENAME  'student' , 'Mystudent'

運(yùn)行結(jié)果如下。

    The object was renamed to 'Mystudent'

此時(shí)我們會(huì)發(fā)現(xiàn)數(shù)據(jù)庫(kù)中的student表不見了，取而代之的是表Mystudent。

除了可以對(duì)表進(jìn)行重命名以外，我們還可以對(duì)表中的列進(jìn)行重命名。在SQL Server數(shù)據(jù)庫(kù)系統(tǒng)中，重命名表中的列同樣使用SP_RENAME關(guān)鍵詞。

實(shí)例16 重命名表中的列

將Mystudent表（由student表重命名生成的表）中的sname列更名為Studentname。

    SP_RENAME  'Mystudent.sname' , 'Studentname', 'COLUMN'

運(yùn)行結(jié)果如下。

    The COLUMN was renamed to 'Studentname'.

此時(shí)，表Mystudent的結(jié)構(gòu)如圖3-17所示。

3.5.2 刪除表

表的刪除非常容易，使用DROP TABLE關(guān)鍵詞即可實(shí)現(xiàn)。語(yǔ)法如下。

    DROP TABLE table_name

只要在DROP TABLE關(guān)鍵詞后面接上要?jiǎng)h除表的名字即可。這里表的刪除不僅刪除了表內(nèi)存儲(chǔ)的數(shù)值，而是整個(gè)表結(jié)構(gòu)都被刪除了，也就是該表不存在了。下面我們通過(guò)一個(gè)具體的實(shí)例說(shuō)明表的刪除操作。

實(shí)例17 刪除表

將學(xué)生表（Mystudent）刪除。實(shí)例代碼如下。

    DROP  TABLE     Mystudent

執(zhí)行該語(yǔ)句后，我們會(huì)發(fā)現(xiàn)Mystudent已經(jīng)從數(shù)據(jù)庫(kù)中刪除。

3.6 創(chuàng)建、刪除數(shù)據(jù)庫(kù)

本章主要介紹表的相關(guān)操作，然而表是存在于特定的數(shù)據(jù)庫(kù)中的。如本章前面表的有關(guān)操作實(shí)例都是在pubs數(shù)據(jù)庫(kù)中進(jìn)行的。對(duì)于那些支持多數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)系統(tǒng)，一般都支持基于SQL語(yǔ)言的CREATE DATABASE語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)，這些數(shù)據(jù)庫(kù)系統(tǒng)包括Oracle、MySQL、SQL Server等。而刪除數(shù)據(jù)庫(kù)則通過(guò)DROP DATABASE語(yǔ)句來(lái)實(shí)現(xiàn)。

3.6.1 數(shù)據(jù)庫(kù)的創(chuàng)建

雖然我們可以基于CREATE DATABASE語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)，但是該語(yǔ)句可以定義哪些參數(shù)，執(zhí)行該語(yǔ)句需要什么許可，以及系統(tǒng)如何實(shí)現(xiàn)數(shù)據(jù)庫(kù)對(duì)象等，都會(huì)因?yàn)閿?shù)據(jù)庫(kù)產(chǎn)品的不同而不同。不過(guò)，大部分產(chǎn)品都使用相同的語(yǔ)法創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象。語(yǔ)法如下。

    CREATE  DATABASE   database_name
    additional parameters

這里，CREATE DATABASE語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)，除了要指明數(shù)據(jù)庫(kù)名字外，還需要設(shè)置數(shù)據(jù)庫(kù)的一些基本參數(shù)。

3.6.2 SQL Server中數(shù)據(jù)庫(kù)的創(chuàng)建

SQL Server允許為每個(gè)數(shù)據(jù)庫(kù)服務(wù)創(chuàng)建多個(gè)數(shù)據(jù)庫(kù)。在MS-SQL Server中，使用CREATE DATABASE語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)的完整語(yǔ)法如下所示。

    CREATE DATABASE  database_name
    [ ON
        [ < filespec > [ ,...n ] ]
        [ , < filegroup > [ ,...n ] ]
    ]
    [ LOG ON { < filespec > [ ,...n ] } ]

其中< filespec >定義為：

    ( [ NAME = logical_file_name , ]
        FILENAME = 'os_file_name'
        [ , SIZE = size ]
        [ , MAXSIZE = { max_size | UNLIMITED } ]
        [ , FILEGROWTH = growth_increment ] ) [ ,...n ])

各關(guān)鍵字和選項(xiàng)的含義如表3-8所示。

實(shí)例18 創(chuàng)建數(shù)據(jù)庫(kù)

在MS-SQL Server中，創(chuàng)建一數(shù)據(jù)庫(kù)Sales。

    CREATE DATABASE Sales
    ON
    ( NAME = Sales_dat,
      FILENAME = 'c:\program files\microsoft sql
server\mssql\data\saledat.mdf',
      SIZE = 10,
      MAXSIZE = 50,
      FILEGROWTH = 5 )
    LOG ON
    ( NAME = 'Sales_log',
      FILENAME = 'c:\program files\microsoft sql
server\mssql\data\salelog.ldf',
      SIZE = 5MB,
      MAXSIZE = 25MB,
      FILEGROWTH = 5MB )

在查詢分析器中，運(yùn)行該代碼，即可完成數(shù)據(jù)庫(kù)Sales的創(chuàng)建。此時(shí)查詢分析器的Result窗格將顯示如下信息。

    The CREATE DATABASE process is allocating 10.00 MB on disk 'Sales_dat'.
    The CREATE DATABASE process is allocating 5.00 MB on disk 'Sales_log'.

通過(guò)SQL Server企業(yè)管理器查看數(shù)據(jù)庫(kù)Sales的結(jié)構(gòu)和屬性，結(jié)果如圖3-18所示。

3.6.3 刪除數(shù)據(jù)庫(kù)

與表的刪除類似，數(shù)據(jù)庫(kù)的刪除是通過(guò)DROP DATABASE語(yǔ)句來(lái)實(shí)現(xiàn)的，語(yǔ)法如下。

    DROP DATABASE database_name [ ,...n ]

實(shí)例19 刪除數(shù)據(jù)庫(kù)

刪除數(shù)據(jù)庫(kù)Sales。

    DROP DATABASE Sales

此時(shí)，Sales數(shù)據(jù)庫(kù)被刪除，查詢分析器的Result窗格將顯示如下信息。

    Deleting database file 'c:\program files\microsoft sql server\mssql\data\
    salelog.ldf'.
    Deleting database file 'c:\program files\microsoft sql server\mssql\data\
    saledat.mdf'.

如果該數(shù)據(jù)庫(kù)正在被使用，通過(guò)DROP DATABASE語(yǔ)句刪除數(shù)據(jù)庫(kù)操作就會(huì)失敗。