第8章創(chuàng)建和使用表

第8章創(chuàng)建和使用表8.1表的概念8.1.1什么是表

在關(guān)系數(shù)據(jù)庫(kù)中，每一個(gè)關(guān)系都表現(xiàn)為一張表。表是用來(lái)存儲(chǔ)數(shù)據(jù)和操作數(shù)據(jù)的邏輯結(jié)構(gòu)，關(guān)系數(shù)據(jù)庫(kù)中的所有數(shù)據(jù)都表現(xiàn)為表的形式，表由列和行組成，行稱為記錄，列稱為屬性或字段。

SQLServer提供了很多種數(shù)據(jù)類型，還可以根據(jù)需要用戶自已定義新的數(shù)據(jù)類型，SQLServer中常用的數(shù)據(jù)類型如下所示。數(shù)據(jù)類型說(shuō)明number(p)整數(shù)（其中p為精度）decimal(p,s)浮點(diǎn)數(shù)（其中p為精度，d為小數(shù)位數(shù)）char(n)固定長(zhǎng)度字符串（其中n為長(zhǎng)度）varchar(n)可變長(zhǎng)度字符串（其中n為最大長(zhǎng)度）datetime日期和時(shí)間

注意：空值是列的一種特殊取值，用NULL表示。它表示對(duì)應(yīng)的數(shù)據(jù)不存在、不確定或不知道。表中主鍵列必須有確定的取值不能為空值，其余列的取值可以根據(jù)現(xiàn)實(shí)語(yǔ)義決定是否可以取空值。8.1.2表中數(shù)據(jù)的完整性

數(shù)據(jù)完整性包括規(guī)則、默認(rèn)值和約束等。1.規(guī)則

規(guī)則是指表中數(shù)據(jù)應(yīng)滿足一些基本條件。

例如，學(xué)生成績(jī)表中分?jǐn)?shù)只能在0～100之間。2.默認(rèn)值

默認(rèn)值是指表中數(shù)據(jù)的默認(rèn)取值。

例如，學(xué)生表中性別的默認(rèn)可以設(shè)置為“男”。3.約束

約束是指表中數(shù)據(jù)應(yīng)滿足一些強(qiáng)制性條件，這些條件通常由用戶在設(shè)計(jì)表時(shí)指定。

（1）非空約束（NOTNULL）：非空約束是指數(shù)據(jù)列不接受NULL值。

例如，學(xué)生表中學(xué)號(hào)通常設(shè)定為主鍵，不能接受NULL值。

（2）檢查約束（CHECK約束）：檢查約束是指限制輸入到一列或多列中的可能值。

例如，學(xué)生表中性別約束為只能取“男”或“女”值。

（3）唯一約束（UNIQUE約束）：唯一約束是指一列或多列組合不允許出現(xiàn)兩個(gè)或兩個(gè)以上的相同的值。

例如，學(xué)生成績(jī)表中，學(xué)號(hào)和課程號(hào)可以設(shè)置為唯一約束，因?yàn)橐粋€(gè)學(xué)生對(duì)應(yīng)一門課程不能有兩個(gè)或以上的分?jǐn)?shù)。（4）主鍵約束（PRIMARYKEY約束）：主鍵約束是指定義為主鍵（一列或多列組合）的列不允許出現(xiàn)兩個(gè)或兩個(gè)以上的相同值。

例如，若將學(xué)生表中的學(xué)號(hào)設(shè)置為主鍵，則不能存在兩個(gè)學(xué)號(hào)相同的學(xué)生記錄。

（5）外鍵約束（FOREIGNKEY約束）：一個(gè)表的外鍵是參照另一個(gè)表的候選主鍵的屬性或?qū)傩越M合，外鍵約束是指輸入外鍵值時(shí)必須參照所對(duì)應(yīng)的另一個(gè)表的候選主鍵值。

例如，成績(jī)表中的學(xué)號(hào)列是外鍵，對(duì)應(yīng)于學(xué)生表的學(xué)號(hào)主鍵，外鍵約束是指輸入成績(jī)表中的學(xué)號(hào)值必須在學(xué)生表的學(xué)號(hào)列中已存在，因此，在輸入上述兩個(gè)表的數(shù)據(jù)時(shí)，必須先輸入學(xué)生表的數(shù)據(jù)，然后才能輸入成績(jī)表的數(shù)據(jù)，即學(xué)生表中存在這個(gè)學(xué)生，成績(jī)表才能有其成績(jī)記錄。8.2創(chuàng)建表兩種創(chuàng)建表的方法

使用SQLServer管理控制器建立表

使用T-SQL語(yǔ)句中的createtable命令建立表創(chuàng)建表的過(guò)程1.創(chuàng)建表的結(jié)構(gòu)(定義表)

設(shè)置表各列的列名、數(shù)據(jù)類型、是否允許取null值、默認(rèn)值、主鍵約束、唯一約束、外鍵約束等2.輸入表的記錄在設(shè)計(jì)表結(jié)構(gòu)時(shí)，應(yīng)該考慮如下幾點(diǎn)因素表中需要包含的列，每一列的數(shù)據(jù)類型、精度。表中哪些列允許空值或不允許空值。表中是否設(shè)置主鍵，在何處設(shè)置主鍵。表中是否設(shè)置約束、默認(rèn)值等，以及在何處設(shè)置。表中是否設(shè)置外鍵以及在何處設(shè)置。

表中是否設(shè)置索引、在何處設(shè)置以及設(shè)置什么樣的索引。

【例8.1】

使用SQLServer管理控制器在school數(shù)據(jù)庫(kù)中建立student表（學(xué)生表）、teacher表（教師表）、course表（課程表）、allocate（課程分配表）和score表（成績(jī)表）。

解：其操作步驟如下：

（1）啟動(dòng)SQLServer管理控制器，展開“LCB-PC”服務(wù)器節(jié)點(diǎn)。（2）展開“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)。選中數(shù)據(jù)庫(kù)“school”，展開school數(shù)據(jù)庫(kù)。

（3）選中“表”，單擊鼠標(biāo)右鍵，在出現(xiàn)的快捷菜單中選擇“新建表”命令，如圖8.1所示。

（4）此時(shí)打開表設(shè)計(jì)器窗口，在“列名”欄中依次輸入表的字段名，并設(shè)置每個(gè)字段的數(shù)據(jù)類型、長(zhǎng)度等屬性。輸入完成后的結(jié)果如下圖，每個(gè)列都對(duì)應(yīng)一個(gè)“列屬性”對(duì)話框，其中各個(gè)選項(xiàng)的含義如下：名稱：指定列名稱。長(zhǎng)度：數(shù)據(jù)類型的長(zhǎng)度。默認(rèn)值或綁定：在新增記錄時(shí)，如果沒(méi)有把值賦予該字段，則此默認(rèn)值為字段值。數(shù)據(jù)類型：列的數(shù)據(jù)類型，用戶可以單擊該欄，然后單擊出現(xiàn)的下三角按鈕，即可進(jìn)行選擇。允許空：指定是否可以輸入空值。RowGuid：可以讓SQLServer產(chǎn)生一個(gè)全局唯一的列值，但列類型必須是uniqueidentifier。有此屬性的列會(huì)自動(dòng)產(chǎn)生列值，不需要用戶輸入（用戶也不能輸入）。排序規(guī)則：指定該列的排序規(guī)則。

（5）在學(xué)號(hào)列上右擊鼠標(biāo)，在出現(xiàn)的快捷菜單中選擇“設(shè)置主鍵”命令，如下圖，從而將學(xué)號(hào)列設(shè)置為該表的主鍵，此時(shí)，該列名前面會(huì)出現(xiàn)一個(gè)鑰匙圖標(biāo)。

（6）單擊工具欄中的保存按鈕，出現(xiàn)下圖所示的對(duì)話框，輸入表的名稱“student”，單擊“確定”按鈕。此時(shí)便建好了student表（表中沒(méi)有數(shù)據(jù)）。

（7）依照上述步驟，再創(chuàng)建4個(gè)表：teacher表（教師表）、course表（課程表）、allocate(課程分配表)和score表（學(xué)生成績(jī)表）。表的結(jié)構(gòu)分別如下圖。

最后在school數(shù)據(jù)庫(kù)中建立的5個(gè)表的表結(jié)構(gòu)如下（帶下劃線字段表示主鍵）：

student(學(xué)號(hào),姓名,性別,出生日期,班號(hào))

teacher(編號(hào),姓名,性別,出生日期,職稱,部門)

course(課程號(hào),課程名,任課教師)

allocate(班號(hào),課程號(hào),教師編號(hào))

score(學(xué)號(hào),課程號(hào),分?jǐn)?shù))8.3修改表的結(jié)構(gòu)

使用SQLServer管理控制器修改和查看數(shù)據(jù)表結(jié)構(gòu)與創(chuàng)建表結(jié)構(gòu)的過(guò)程相同。

【例8.2】

使用SQLServer管理控制器，先在student表中增加一個(gè)民族列（其數(shù)據(jù)類型為char(16)），然后進(jìn)行刪除。

解：其操作步驟如下：（1）啟動(dòng)SQLServer管理控制器，展開“LCB-PC”服務(wù)器節(jié)點(diǎn)。

（2）展開“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)。選中“school”，將其展開，選中“表”，將其展開，選中表“dbo.student”，單擊鼠標(biāo)右鍵，在出現(xiàn)的快捷菜單中選擇“修改”命令，如圖8.8所示。

（3）在班號(hào)列前面增加民族列，其操作是，在打開的表設(shè)計(jì)器窗口中，用鼠標(biāo)右擊班號(hào)列，然后在出現(xiàn)的快捷菜單中選擇“插入列”命令。（4）在新插入的列中，輸入“民族”，設(shè)置數(shù)據(jù)類型為char，長(zhǎng)度為16，如圖所示。

（5）現(xiàn)在刪除剛增加的民族列。用鼠標(biāo)右擊“民族”列，然后在出現(xiàn)的快捷菜單中選擇“刪除列”命令，如圖8.9所示，這樣就刪除了民族列。

（6）單擊工具欄中的保存按鈕，保存所進(jìn)行的修改。說(shuō)明：本例操作完畢后，student表保持原有的表結(jié)構(gòu)不變。8.4數(shù)據(jù)庫(kù)關(guān)系圖

一個(gè)數(shù)據(jù)庫(kù)中可能有多個(gè)表，表之間可能存在著關(guān)聯(lián)關(guān)系，建立這種關(guān)聯(lián)關(guān)系的圖示稱為數(shù)據(jù)庫(kù)關(guān)系圖。8.4.1建立數(shù)據(jù)庫(kù)關(guān)系圖通過(guò)一個(gè)示例說(shuō)明建立數(shù)據(jù)庫(kù)關(guān)系圖的過(guò)程。

【例8.3】

建立school數(shù)據(jù)庫(kù)中5個(gè)表的若干外鍵關(guān)系。

解：其操作步驟如下：（1）啟動(dòng)SQLServer管理控制器，展開“LCB-PC”股務(wù)器節(jié)點(diǎn)。（2）展開“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，選中“school”，將其展開。

（3）選中“數(shù)據(jù)庫(kù)關(guān)系圖”，單擊鼠標(biāo)右鍵，在出現(xiàn)的快捷菜單中選擇“新建數(shù)據(jù)庫(kù)關(guān)系圖”命令，如圖8.11所示。

（4）此時(shí)出現(xiàn)“添加表”對(duì)話框，由于要建立school數(shù)據(jù)庫(kù)中5個(gè)表的關(guān)系，所以選中每一個(gè)表，并單擊“添加”按鈕，添加完畢后，單擊“關(guān)閉”按鈕返回到SQLServer管理控制器。在“關(guān)系圖”中任意空白處單擊鼠標(biāo)右鍵，在出現(xiàn)的快捷菜單中選擇“添加表”命令即可出現(xiàn)“添加表”對(duì)話框。

（5）此時(shí)SQLServer管理控制器右邊出現(xiàn)如圖8.12所示的“關(guān)系圖”對(duì)話框。

（6）現(xiàn)在建立student表中學(xué)號(hào)列和score表中學(xué)號(hào)列之間的關(guān)系。

（7）采用同樣的過(guò)程建立course表中課程號(hào)列（主鍵）和score表中課程號(hào)列（外鍵）之間的外鍵關(guān)系。（8）采用同樣的過(guò)程建立teacher表中編號(hào)列（主鍵）和allocate表中教師編號(hào)列（外鍵）之間的外鍵關(guān)系。（9）采用同樣的過(guò)程建立course表中課程列（主鍵）和allocate表中課程號(hào)列（外鍵）之間的外鍵關(guān)系。

（10）最終建好的關(guān)系圖如圖所示。單擊工具欄中的保存按鈕來(lái)保存關(guān)系，此時(shí)出現(xiàn)“選擇名稱”對(duì)話框，輸入關(guān)系圖名稱，單擊“確定”按鈕保存。

建好的school數(shù)據(jù)庫(kù)中5個(gè)表之間的關(guān)系如下圖。

通過(guò)數(shù)據(jù)庫(kù)關(guān)系圖建立的關(guān)系反映在各個(gè)表的鍵中，如圖所示是allocate表的鍵列表，其中PK_allocate鍵是通過(guò)設(shè)置主鍵建立的，而FK_allocate_course和FK_allocate_teacher兩個(gè)鍵是通過(guò)上例建立的。8.4.2刪除關(guān)系和數(shù)據(jù)庫(kù)關(guān)系圖1.通過(guò)數(shù)據(jù)庫(kù)關(guān)系圖刪除關(guān)系

可通過(guò)數(shù)據(jù)庫(kù)關(guān)系圖刪除表之間的外鍵關(guān)系。

其操作方法：打開相關(guān)的數(shù)據(jù)庫(kù)關(guān)系圖，選中該外鍵關(guān)系連線，單擊鼠標(biāo)右鍵，在出現(xiàn)的快捷菜單中選擇“從數(shù)據(jù)庫(kù)中刪除關(guān)系”命令，在出現(xiàn)的對(duì)話框中選擇“是”即可。2.刪除數(shù)據(jù)庫(kù)關(guān)系圖

當(dāng)不再需要數(shù)據(jù)庫(kù)關(guān)系圖時(shí)，可以選中“數(shù)據(jù)庫(kù)關(guān)系圖”列表中的某個(gè)數(shù)據(jù)庫(kù)關(guān)系圖（如dbo.Diagram_0），單擊鼠標(biāo)右鍵，在出現(xiàn)的快捷菜單中選擇“刪除”命令即可。刪除某個(gè)數(shù)據(jù)庫(kù)關(guān)系圖后，其包含的外鍵關(guān)系仍然保存在數(shù)據(jù)庫(kù)中，不會(huì)連同該數(shù)據(jù)庫(kù)關(guān)系圖一起被刪除。若某數(shù)據(jù)庫(kù)關(guān)系圖被刪除了，還需要?jiǎng)h除其外鍵關(guān)系，只有進(jìn)入各表的鍵列表中，一個(gè)一個(gè)將不需要的外鍵刪除掉。8.5更改表名

在有些情況下需要更改表的名稱，被更名的表必須已經(jīng)存在?？墒褂肧QLServer管理控制器更改表名。

【例8.4】

將數(shù)據(jù)庫(kù)school中abc表（已創(chuàng)建）更名為xyz。

解：其操作步驟如下：（1）啟動(dòng)SQLServer管理控制器，展開“LCB-PC”股務(wù)器節(jié)點(diǎn)。（2）展開“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，展開“school”，選中“表”，將其展開。

（3）選中表“dbo.abc”，單擊鼠標(biāo)右鍵，在出現(xiàn)的快捷菜單中選擇“重命名”命令，如圖所示。

（4）此時(shí)表名稱變?yōu)榭删庉嫷?，如圖所示，直接將其修改成“xyz”即可。8.6刪除表

【例8.5】

刪除數(shù)據(jù)庫(kù)school中xyz表（已創(chuàng)建）。

解：其操作步驟如下：（1）啟動(dòng)SQLServer管理控制器，展開“LCB-PC”股務(wù)器節(jié)點(diǎn)。（2）展開“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，展開“school”，選中“表”，將其展開。

（3）選中表“dbo.xyz”，單擊鼠標(biāo)右鍵，在出現(xiàn)的快捷菜單中選擇“刪除”命令，如圖所示。

（4）此時(shí)出現(xiàn)“刪除對(duì)象”對(duì)話框，直接單擊“確定”按鈕將xyz表刪除了。8.7記錄的新增和修改

記錄的新增和修改與記錄的表內(nèi)容的查看的操作過(guò)程是相同的，就是在打開表的內(nèi)容窗口后，直接輸入新的記錄或者進(jìn)行修改。

【例8.6】

輸入school數(shù)據(jù)庫(kù)中student、teacher、course、allocate和score等5個(gè)表的相關(guān)記錄。

解：其操作步驟如下：（1）啟動(dòng)SQLServer管理控制器，展開“LCB-PC”股務(wù)器節(jié)點(diǎn)。（2）展開“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，選中“school”，將其展開，選中“表”，將其展開。

（3）選中表“dbo.student”，單擊鼠標(biāo)右鍵，在出現(xiàn)的快捷菜單中選擇“打開表”命令，如圖所示。

（4）此時(shí)出現(xiàn)student數(shù)據(jù)表編輯對(duì)話框，用戶可以在其中各字段中直接輸入或編輯相應(yīng)的數(shù)據(jù)，這里輸入6個(gè)學(xué)生記錄，如圖所示。

（5）采用同樣的方法輸入teacher、course、allocate和score表中數(shù)據(jù)記錄，分別如圖所示。說(shuō)明：本例中輸入的數(shù)據(jù)作為樣本數(shù)據(jù)，在本書后面的許多例子中用到。常用的基本數(shù)據(jù)類型種類數(shù)據(jù)類型數(shù)字整數(shù)int,bigint,smallint,tinyint精確數(shù)值decimal,numeric近似數(shù)值float,real貨幣money,smallmoney日期和時(shí)間datetime,smalldatetime字符Non-Unicodechar,varchar,varchar(max),textUnicodenchar,nvarchar,nvarchar(max),ntext二進(jìn)制binary,varbinary,varbinary(max)圖像image全局標(biāo)識(shí)符uniqueidentifierXMLxml特殊bit,cursor,timestamp,sysname,table,sql_variant數(shù)字?jǐn)?shù)據(jù)類型數(shù)字?jǐn)?shù)據(jù)類型表示符號(hào)用途精確數(shù)字類型整數(shù)數(shù)據(jù)類型int、bigint、smallint、tinyint存儲(chǔ)數(shù)字?jǐn)?shù)據(jù)，直接進(jìn)行數(shù)據(jù)運(yùn)算位數(shù)據(jù)類型bit用于一些條件邏輯判斷貨幣數(shù)據(jù)類型money、smallmoney存儲(chǔ)貨幣數(shù)據(jù)十進(jìn)制小數(shù)型decimal、numeric存儲(chǔ)帶固定精度和位數(shù)的小數(shù)近似數(shù)字類型浮點(diǎn)數(shù)據(jù)類型float、real存儲(chǔ)范圍非常大的數(shù)字，用于表示指定數(shù)的大致數(shù)據(jù)值整數(shù)數(shù)據(jù)類型：bigint型，長(zhǎng)度為8個(gè)字節(jié)，存儲(chǔ)范圍為-263～263-1；int型，長(zhǎng)度為4個(gè)字節(jié)，存儲(chǔ)范圍為-231～231-1；smallint型，長(zhǎng)度為2個(gè)字節(jié)，存儲(chǔ)范圍為-215～215-1；tinyint型，長(zhǎng)度為1個(gè)字節(jié)，存儲(chǔ)范圍為0～255。位型數(shù)據(jù)(bit)：只存儲(chǔ)0、1或NULL，長(zhǎng)度為1字節(jié)，常用于一些條件邏輯判斷。貨幣數(shù)據(jù)類型：小數(shù)位數(shù)最多4位，money型長(zhǎng)度為8個(gè)字節(jié)，存儲(chǔ)范圍為-263～263-1，smallmoney型長(zhǎng)度為4個(gè)字節(jié)，存儲(chǔ)范圍為-231～231-1，使用時(shí)在數(shù)字前面加上“$”貨幣符號(hào)。十進(jìn)制小數(shù)型:decimal和numeric，這兩種數(shù)據(jù)類型為完全相同的數(shù)據(jù)類型。decimal數(shù)據(jù)類型由整數(shù)部分和小數(shù)部分構(gòu)成，它的語(yǔ)法格式如下：decimal(p，s)其中p表示數(shù)字的精度，s表示數(shù)字的小數(shù)位數(shù)，s的默認(rèn)值為0。浮點(diǎn)數(shù)據(jù)型數(shù)：(real和float)通常用于科學(xué)計(jì)算，數(shù)據(jù)用科學(xué)計(jì)數(shù)法表示。這種數(shù)據(jù)類型的優(yōu)點(diǎn)是能夠存儲(chǔ)范圍非常大的數(shù)字。但是浮點(diǎn)數(shù)據(jù)類型容易產(chǎn)生舍入誤差。在對(duì)數(shù)據(jù)的精度要求并不是絕對(duì)嚴(yán)格時(shí)，可以考慮使用real或者float型數(shù)據(jù)。字符數(shù)據(jù)類型字符類型表示符號(hào)用法ANSI字符char存儲(chǔ)指定長(zhǎng)度的字符串varchar存儲(chǔ)可變長(zhǎng)度字符text存儲(chǔ)大量字符Unicode字符nchar用于存儲(chǔ)Unicode字符nvarchar與varchar類似，存儲(chǔ)可變長(zhǎng)度Unicode字符ntext與text類似，存儲(chǔ)大量Unicode字符數(shù)據(jù)Char：存儲(chǔ)固定長(zhǎng)度字符的ANSI字符類型，最長(zhǎng)可以容納8K個(gè)字符，當(dāng)列中字符長(zhǎng)度基本一致時(shí)(如學(xué)號(hào)、姓名等)，可以使用char類型。Varchar：用于存儲(chǔ)可變長(zhǎng)度的ANSI字符數(shù)據(jù)，最長(zhǎng)可以容納8K個(gè)字符，當(dāng)數(shù)據(jù)長(zhǎng)度相差較大時(shí)(如備注)，使用varchar類型可以節(jié)省存儲(chǔ)空間。Text：存儲(chǔ)長(zhǎng)度大于8K個(gè)字符的ANSI字符。Nchar：用于存儲(chǔ)固定長(zhǎng)度的Unicode字符數(shù)據(jù)。Nvarchar：用于存