《SQLServer2005數(shù)據(jù)庫(kù)案例教程》第2章數(shù)據(jù)庫(kù)及表的操作

第2章數(shù)據(jù)庫(kù)及表的操作

隨著信息技術(shù)的普及，學(xué)校使用計(jì)算機(jī)進(jìn)行輔助教學(xué)管理，尤其是在學(xué)籍管理，圖書檔案管理，人事檔案管理等存在大量數(shù)據(jù)的地方，使用數(shù)據(jù)庫(kù)管理系統(tǒng)，可以大量節(jié)省人力，并且成倍提高工作效率。英才職業(yè)技術(shù)學(xué)院經(jīng)慎重考慮，決定在教務(wù)處和圖書館等部門率先使用數(shù)據(jù)庫(kù)管理系統(tǒng)，方便學(xué)生的成績(jī)查詢、圖書借閱、及教師對(duì)成績(jī)進(jìn)行統(tǒng)計(jì)分析等工作。 本章主要圍繞英才職業(yè)技術(shù)學(xué)院教學(xué)管理系統(tǒng)這個(gè)案例的數(shù)據(jù)庫(kù)及其數(shù)據(jù)表的構(gòu)建過程，展開闡述相關(guān)的知識(shí)點(diǎn)。本章的實(shí)訓(xùn)將根據(jù)本章的知識(shí)帶領(lǐng)學(xué)生完成該校圖書管理系統(tǒng)的數(shù)據(jù)庫(kù)構(gòu)建過程。為后續(xù)章節(jié)在此基礎(chǔ)上進(jìn)行數(shù)據(jù)的查詢與簡(jiǎn)單統(tǒng)計(jì)分析打下基礎(chǔ)。2.1.2相關(guān)知識(shí)及注意事項(xiàng)1.數(shù)據(jù)庫(kù)存儲(chǔ)結(jié)構(gòu)數(shù)據(jù)庫(kù)的存儲(chǔ)結(jié)構(gòu)分為邏輯存儲(chǔ)結(jié)構(gòu)和物理存儲(chǔ)結(jié)構(gòu)兩種。數(shù)據(jù)庫(kù)的邏輯存儲(chǔ)結(jié)構(gòu)指的是數(shù)據(jù)庫(kù)是由哪些性質(zhì)的信息所組成，SQLServer的數(shù)據(jù)庫(kù)不僅僅只是數(shù)據(jù)的存儲(chǔ)，所有與數(shù)據(jù)處理操作相關(guān)的信息都存儲(chǔ)在數(shù)據(jù)庫(kù)中。實(shí)際上，SQLServer的數(shù)據(jù)庫(kù)是由諸如表、視圖、索引等各種不同的數(shù)據(jù)庫(kù)對(duì)象所組成，它們分別用來存儲(chǔ)特定信息并支持特定功能，構(gòu)成數(shù)據(jù)庫(kù)的邏輯存儲(chǔ)結(jié)構(gòu)。數(shù)據(jù)庫(kù)的物理存儲(chǔ)結(jié)構(gòu)則是討論數(shù)據(jù)庫(kù)文件是如何在磁盤上存儲(chǔ)的。數(shù)據(jù)庫(kù)在磁盤上是以文件為單位存儲(chǔ)的，由數(shù)據(jù)庫(kù)文件和事務(wù)日志文件組成，一個(gè)數(shù)據(jù)庫(kù)至少應(yīng)該包含一個(gè)數(shù)據(jù)庫(kù)文件和一個(gè)事務(wù)日志文件。2.數(shù)據(jù)庫(kù)文件

1)主數(shù)據(jù)庫(kù)文件（PrimaryDatabaseFile）用來存放數(shù)據(jù)，每個(gè)數(shù)據(jù)庫(kù)都必須有一個(gè)主數(shù)據(jù)文件。主要數(shù)據(jù)文件是所有數(shù)據(jù)庫(kù)文件的起點(diǎn)，包含指向其他數(shù)據(jù)文件的指針。主要數(shù)據(jù)文件的擴(kuò)展名是(.mdf)。

2)輔助數(shù)據(jù)庫(kù)文件（SecondaryDatabaseFile）又稱二級(jí)數(shù)據(jù)文件，也用來存放數(shù)據(jù)，數(shù)據(jù)庫(kù)中可以有零個(gè)或多個(gè)Secondary文件。其數(shù)據(jù)文件的擴(kuò)展名為.ndf。

3)事務(wù)日志文件用來存放日志，每個(gè)數(shù)據(jù)庫(kù)必須有一個(gè)或多個(gè)日志文件。日志文件用于存放恢復(fù)數(shù)據(jù)庫(kù)所用的所有日志信息。事務(wù)日志文件擴(kuò)展名為.ldf。3.數(shù)據(jù)庫(kù)文件組

為了便于分配和管理，SQLServer允許將多個(gè)數(shù)據(jù)庫(kù)文件歸納為同一組，并賦予此組一個(gè)名稱，并對(duì)它們整體進(jìn)行管理，這就是文件組。與數(shù)據(jù)庫(kù)文件一樣，文件組也分為主文件組（PrimaryFileGroup）和次文件組（SecondaryFileGroup）。例如，可以將三個(gè)數(shù)據(jù)庫(kù)文件(data1.mdf、data2.mdf和data3.mdf)分別創(chuàng)建在三個(gè)盤上，這三個(gè)文件組成文件組fgroup1。在創(chuàng)建表的時(shí)候，就可以指定一個(gè)表創(chuàng)建在文件組fgroup1上。這樣該表的數(shù)據(jù)就可以分布在三個(gè)盤上，系統(tǒng)在對(duì)該表執(zhí)行查詢時(shí)可以并行操作，提高了查詢效率。★重點(diǎn)：SQLServer的數(shù)據(jù)庫(kù)文件和文件組必須遵循以下規(guī)則：①一個(gè)文件或文件組只能被一個(gè)數(shù)據(jù)庫(kù)使用；②一個(gè)文件只能屬于一個(gè)文件組；③日志文件不能屬于文件組。4.系統(tǒng)數(shù)據(jù)庫(kù)1)master數(shù)據(jù)庫(kù)2)model數(shù)據(jù)庫(kù)3)msdb數(shù)據(jù)庫(kù)4)tempdb數(shù)據(jù)庫(kù)

5．?dāng)?shù)據(jù)庫(kù)對(duì)象

每個(gè)數(shù)據(jù)庫(kù)都由以下幾個(gè)部分的數(shù)據(jù)庫(kù)對(duì)象所組成：關(guān)系圖、表、視圖、存儲(chǔ)過程、用戶、角色、規(guī)則、默認(rèn)、用戶自定義數(shù)據(jù)類型和用戶自定義函數(shù)

2.2數(shù)據(jù)表的操作

數(shù)據(jù)表是十分重要的數(shù)據(jù)對(duì)象，用戶所關(guān)心的數(shù)據(jù)分門別類的存儲(chǔ)在各個(gè)表中，許多操作都是圍繞表進(jìn)行的。因此，表的設(shè)計(jì)好壞直接影響數(shù)據(jù)庫(kù)系統(tǒng)的執(zhí)行效率，在創(chuàng)建表之前，一定要做好系統(tǒng)分析，以免表創(chuàng)建以后再修改，這樣有時(shí)會(huì)有很大麻煩。2.2.1案例實(shí)現(xiàn)過程1.構(gòu)造“教學(xué)管理系統(tǒng)”的實(shí)體-聯(lián)系（E-R）圖將每個(gè)實(shí)體的屬性也畫上，就成為圖2.26所示的E-R圖了。

2.設(shè)計(jì)“教學(xué)管理系統(tǒng)”的邏輯數(shù)據(jù)模型3.設(shè)計(jì)“教學(xué)管理系統(tǒng)”的物理數(shù)據(jù)模型教學(xué)管理系統(tǒng)的5個(gè)表的結(jié)構(gòu)見表2-1~表2-5。表2-1學(xué)生表字段名數(shù)據(jù)類型長(zhǎng)度允許為空學(xué)號(hào)char8NOTNULL姓名char8NOTNULL性別char2出生日期datetime專業(yè)char20

表2-2課程表表2-3成績(jī)表字段名數(shù)據(jù)類型長(zhǎng)度允許為空課程編號(hào)char3NOTNULL課程名char20NOTNULL成績(jī)int字段名數(shù)據(jù)類型長(zhǎng)度小數(shù)位允許為空學(xué)號(hào)char8NOTNULL課程編號(hào)char3NOTNULL成績(jī)decimal4(精度)1

表2-4教師表表2-5授課表字段名數(shù)據(jù)類型長(zhǎng)度允許為空教工編號(hào)char8NOTNULL姓名char8NOTNULL性別char2出生日期datetime所在部門varchar20字段名數(shù)據(jù)類型長(zhǎng)度允許為空教工編號(hào)char8NOTNULL課程編號(hào)char3NOTNULL授課學(xué)期char2

4.構(gòu)造“教學(xué)管理系統(tǒng)”的數(shù)據(jù)表（1）啟動(dòng)SQLSERVER2005管理平臺(tái)，在對(duì)象資源管理器的“樹”型窗格中打開“教學(xué)關(guān)系系統(tǒng)”數(shù)據(jù)庫(kù)，右擊【表】對(duì)象，并從彈出的快捷菜單中選擇【新建表】選項(xiàng)，系統(tǒng)將彈出表設(shè)計(jì)器窗口，如圖2.27所示，使用表設(shè)計(jì)器可以對(duì)表的結(jié)構(gòu)進(jìn)行更改，設(shè)置主鍵及字段屬性。在這里輸入“學(xué)生表”的各字段的屬性。

（2）表設(shè)計(jì)器窗口的上半部分有一個(gè)表格，在這個(gè)表格中輸入字段的屬性。每一行對(duì)應(yīng)一字段，對(duì)每一字段都需要進(jìn)行以下設(shè)置，其中前三項(xiàng)是必須輸入的（3）表設(shè)計(jì)器窗口的下半部分是特定字段的詳細(xì)屬性，包括是否使用默認(rèn)值，是否是標(biāo)識(shí)列，設(shè)置精度及小數(shù)位數(shù)等。（4）設(shè)置主鍵約束。選中要作為主鍵的列，這里選【學(xué)號(hào)】，并單擊工具欄上的按鈕，主鍵列的前方將顯示鑰匙標(biāo)記。也可以，右擊表中需設(shè)置主鍵的行，則彈出一個(gè)快捷菜單，如圖2.28所示，選擇【設(shè)置主鍵】。設(shè)置主鍵，是為了保證每條記錄的唯一性。圖2.28彈出【設(shè)置主鍵】的快捷菜單

（5）在表的各字段屬性均編輯完后，單擊工具欄上的【保存】按鈕，出現(xiàn)【選擇名稱】對(duì)話框。輸入表名“學(xué)生表”，單擊【確定】按鈕，表就創(chuàng)建好了。（6）按照上面的步驟，依次建立“教師表”、“課程表”、“成績(jī)表”、“授課表”。（7）設(shè)置“成績(jī)表”和“學(xué)生表”的外鍵關(guān)系①在“對(duì)象資源管理器”中右擊要建立外鍵的表“成績(jī)表”，在出現(xiàn)的快捷菜單中選擇【修改】命令，將出現(xiàn)設(shè)計(jì)表對(duì)話框。②單擊工具欄中的【關(guān)系】按鈕，將出現(xiàn)【外鍵關(guān)系】對(duì)話框，如圖2.29所示。圖2.29外鍵關(guān)系對(duì)話框③單擊左下角的【添加】按鈕，出現(xiàn)如圖2.30所示的對(duì)話框。

圖2.30【外鍵關(guān)系】對(duì)話框圖2.31設(shè)置主鍵表和外鍵表的對(duì)話框④單擊常規(guī)選項(xiàng)中【表和規(guī)范】文本框內(nèi)的按鈕，出現(xiàn)如圖2.31所示的對(duì)話框。④單擊常規(guī)選項(xiàng)中【表和規(guī)范】文本框內(nèi)的

按鈕，出現(xiàn)如圖2.31所示的對(duì)話框。

⑤修改主鍵表為“學(xué)生表”，與外鍵表中“學(xué)號(hào)”列選擇相對(duì)應(yīng)的主鍵表的“學(xué)號(hào)”列，其它列選擇“無”，其結(jié)果如圖2.32所示。圖2.32外鍵關(guān)系表和列設(shè)置后的結(jié)果

⑥單擊【確定】按鈕，出現(xiàn)如圖2.33所示的外鍵關(guān)系對(duì)話框，在這里可以設(shè)置是否“在創(chuàng)建或重新啟用時(shí)檢查現(xiàn)有數(shù)據(jù)”，是否“強(qiáng)制外鍵約束”和“強(qiáng)制用于復(fù)制”。圖2.33外鍵關(guān)系（8）設(shè)置“成績(jī)表”和“課程表”的外鍵關(guān)系使用數(shù)據(jù)庫(kù)關(guān)系圖建立外鍵約束的步驟如下。

①

在【對(duì)象資源管理器】窗格中選中并展開要建立外鍵的表所在數(shù)據(jù)庫(kù)節(jié)點(diǎn)。右擊“數(shù)據(jù)庫(kù)關(guān)系圖”節(jié)點(diǎn)，從彈出的快捷菜單中選擇【新建數(shù)據(jù)庫(kù)關(guān)系圖】命令，如果是第一次在該數(shù)據(jù)庫(kù)中新建數(shù)據(jù)庫(kù)關(guān)系圖，系統(tǒng)將彈出是否創(chuàng)建此數(shù)據(jù)庫(kù)所需的支持對(duì)象對(duì)話框，如圖2.34所示。單擊【是】按鈕。圖2.34是否創(chuàng)建所需的支持對(duì)象的對(duì)話框

②再次右擊【數(shù)據(jù)庫(kù)關(guān)系圖】節(jié)點(diǎn)，從彈出的快捷菜單中選擇【新建數(shù)據(jù)庫(kù)關(guān)系圖】命令，出現(xiàn)如圖2.35所示的【添加表】對(duì)話框。

③在彈出的對(duì)話框中選擇要建立圖表的表，雙擊或單擊【添加】按鈕，這里選擇“成績(jī)表”、“課程表”。單擊【關(guān)閉】按鈕，結(jié)束圖表的創(chuàng)建，出現(xiàn)如圖2.36所示的圖表。

圖2.35添加表對(duì)話框

圖2.36剛添加數(shù)據(jù)表后的關(guān)系圖

④按照事先設(shè)計(jì)好的各表的主鍵約束、唯一性約束等，規(guī)劃好外鍵的關(guān)系。選中主鍵表“課程表”中的“課程編號(hào)”字段(在該行的字段名左側(cè)按鍵，該列會(huì)變黑)，按住鼠標(biāo)左鍵不放，拖到“成績(jī)表”表，釋放鼠標(biāo)，則系統(tǒng)將彈出如圖2.37所示的【表和列】對(duì)話框。

⑤可以在這里調(diào)整主外鍵字段，然后單擊確定，出現(xiàn)如圖2.38所示的對(duì)話框。圖2.37創(chuàng)建關(guān)系的表和列對(duì)話框

圖2.38建立成績(jī)表和課程表的外鍵關(guān)系

⑥

用戶可以在這個(gè)對(duì)話框中設(shè)置外鍵約束的各種特性。建立好的外鍵約束如圖2.39所示。

要?jiǎng)h除已經(jīng)建立的外鍵關(guān)系，可以單擊連接各表的連線，再右擊，在彈出的快捷菜單中選擇【從數(shù)據(jù)庫(kù)中刪除關(guān)系】命令即可。圖2.39建立好外鍵關(guān)系的數(shù)據(jù)庫(kù)關(guān)系圖（9）按照(7)或(8)的方法，設(shè)置“授課表”與“教師表”、“授課表”與“課程表”的外鍵關(guān)系。（10）在SQLSERVER管理平臺(tái)中，右擊所在表，選擇【打開】，輸入部分測(cè)試數(shù)據(jù)。2.2.2相關(guān)知識(shí)及注意事項(xiàng)1．基本表的實(shí)體關(guān)系圖（E-R圖）

模型是現(xiàn)實(shí)世界特征的模擬和抽象。而數(shù)據(jù)模型是現(xiàn)實(shí)世界數(shù)據(jù)特征的抽象，是數(shù)據(jù)庫(kù)系統(tǒng)的核心和基礎(chǔ)。數(shù)據(jù)模型應(yīng)滿足三方面要求，一是能比較真實(shí)地模擬世界；二是容易為人所理解；三是便于在計(jì)算機(jī)上實(shí)現(xiàn)。不同的數(shù)據(jù)模型實(shí)際上是提供給我們模型化數(shù)據(jù)和信息的不同工具。概念模型是一種常用的數(shù)據(jù)模型。

概念模型，也稱信息模型，它是按用戶的觀點(diǎn)來對(duì)數(shù)據(jù)和信息建模，主要用于數(shù)據(jù)庫(kù)設(shè)計(jì)。

1)信息世界的基本概念

（1）實(shí)體（Entity）?？陀^存在并可相互區(qū)別的事物稱為實(shí)體。

（2）屬性（Attribute）。實(shí)體所具有的某一特性稱為屬性。

（3）實(shí)體型（EntityType）。具有相同屬性的實(shí)體必然具有共同的特征和性質(zhì)。

（4）聯(lián)系（Relationship）。在現(xiàn)實(shí)世界中，事物內(nèi)部以及事物之間是有聯(lián)系的，這些聯(lián)系在信息世界中反映為實(shí)體（型）內(nèi)部的聯(lián)系和實(shí)體（型）之間的聯(lián)系。

2)實(shí)體之間的聯(lián)系的類型

（1）一對(duì)一的聯(lián)系（1：1）。

（2）一對(duì)多的聯(lián)系（1：n）。

（3）多對(duì)多的聯(lián)系（m：n）。3)E-R圖

E-R圖是表示數(shù)據(jù)對(duì)象及其關(guān)系的圖形語言機(jī)制，它包含以下幾個(gè)符號(hào)

①方框

②橢圓框

③箭頭

④菱形框2.表的基本概念

在數(shù)據(jù)庫(kù)中，表是包含數(shù)據(jù)庫(kù)中所有數(shù)據(jù)的數(shù)據(jù)庫(kù)對(duì)象，其主要功能是存儲(chǔ)數(shù)據(jù)。表定義是一個(gè)列集合。SQLServer2005中的表包括下列主要組件。

列：每一列代表由表建模的對(duì)象的某個(gè)屬性。例如，一個(gè)部件表有編號(hào)列、顏色列和重量列。

行：每一行代表由表建模的對(duì)象的一個(gè)單獨(dú)的實(shí)例。例如，公司運(yùn)送的每個(gè)部件在部件表中均占一行。3.數(shù)據(jù)類型

設(shè)計(jì)表時(shí)首先要執(zhí)行的操作之一是為每個(gè)列指定數(shù)據(jù)類型。數(shù)據(jù)類型定義了各列允許使用的數(shù)據(jù)值。在數(shù)據(jù)類型中經(jīng)常使用三個(gè)術(shù)語：精度、小數(shù)位數(shù)和長(zhǎng)度。

4.創(chuàng)建表的T-SQL命令

使用CREATETABLE命令創(chuàng)建表非常靈活，它允許對(duì)表設(shè)置幾種不同的選項(xiàng)，包括表名、存放位置和列的屬性等。5.創(chuàng)建完整性約束

在數(shù)據(jù)庫(kù)中保證數(shù)據(jù)的完整性是很重要的。所謂完整性，就是指存儲(chǔ)在數(shù)據(jù)庫(kù)中數(shù)據(jù)的一致性和正確性。實(shí)施數(shù)據(jù)庫(kù)完整性的目的是確保數(shù)據(jù)的質(zhì)量。約束是SQLserver提供的自動(dòng)保持?jǐn)?shù)據(jù)庫(kù)完整性的一種方法。在關(guān)系數(shù)據(jù)庫(kù)中，數(shù)據(jù)完整性可歸納以下三種類型。

（1）實(shí)體完整性

（2）域完整性

（3）引用完整性

SQLServer2005提供了一些在字段上強(qiáng)制數(shù)據(jù)完整性的機(jī)制，它們是如下的六種約束：1)主鍵(PRIMARYKEY)約束。

PRIMARYKEY即主鍵，是最重要的約束類型，一個(gè)表通?？梢酝ㄟ^一個(gè)字段(或多個(gè)字段組合)的數(shù)據(jù)來唯一標(biāo)識(shí)表中的每一行，這個(gè)字段(或字段組合)被稱為表的主鍵。2)空值(NULL)約束

空值約束即是否允許該字段的值為NULL，空值表示“不知道”、“不清楚”或“沒有數(shù)據(jù)”的意思。主鍵不允許為空值。3)默認(rèn)值(DEFAULT)約束。

DEFAULT約束用于指定一個(gè)字段的默認(rèn)值。

【例2.5】在“學(xué)生”表中，將字段專業(yè)設(shè)置默認(rèn)值為“專業(yè)方向暫時(shí)未定”啟動(dòng)SQLSERVER2005管理平臺(tái)，在“對(duì)象資源管理器”窗格中展開數(shù)據(jù)庫(kù)“學(xué)生成績(jī)管理系統(tǒng)”，右擊“學(xué)生表”，在出現(xiàn)的快捷菜單中選擇【修改】，將打開設(shè)計(jì)表對(duì)話框，將光標(biāo)定位于“專業(yè)”字段，在下面的屬性框的【默認(rèn)值或綁定】欄中輸入：專業(yè)方向暫時(shí)未定，結(jié)果如圖2.40所示。

圖2.40表設(shè)計(jì)器中修改默認(rèn)值4)外鍵(FOREIGNKEY)約束。

外鍵約束是用于強(qiáng)制參照完整性，用來保證相關(guān)聯(lián)的表中的主鍵或外鍵的數(shù)據(jù)保持一致，用于維護(hù)同一數(shù)據(jù)庫(kù)中兩表之間的一致性關(guān)系。5)唯一性(UNIQUE)約束

唯一性約束的作用是保證在不是主鍵的字段上不會(huì)出現(xiàn)重復(fù)的數(shù)據(jù)。6)檢查(CHECK)約束

檢查約束即是對(duì)表中的數(shù)據(jù)設(shè)置檢查條件，用來限制字段上可以接受的數(shù)據(jù)值，從而保證數(shù)據(jù)的完整性。

【例2.6】使用SQLServer管理平臺(tái)對(duì)“課程表”的學(xué)分字段添加CHECK約束：學(xué)分字段取值限制到1語4之間。

①啟動(dòng)SQLSERVER2005管理平臺(tái)，在【對(duì)象資源管理器】窗格中展開數(shù)據(jù)庫(kù)“學(xué)生成績(jī)管理系統(tǒng)”，右擊“課程表”，在出現(xiàn)的快捷菜單中執(zhí)行【修改】。將打開設(shè)計(jì)表對(duì)話框，右擊任一字段所在的行，從彈出的快捷菜單中選擇【CHECK約束】命令，出現(xiàn)如圖2.43所示的對(duì)話框。圖2.43check約束對(duì)話框

②單擊【添加】按鈕，在左邊的選定的CHECK約束框中就出現(xiàn)了一項(xiàng)后面帶*的項(xiàng)。如圖2.44所示。圖2.44添加約束后的check約束對(duì)話框

③單擊表達(dá)式欄后的

按鈕，出現(xiàn)如圖2.45所示的對(duì)話框。圖2.45輸入約束表達(dá)式對(duì)話框

④在【約束表達(dá)式】文本框中輸入檢查約束的邏輯表達(dá)式：學(xué)分>=1and學(xué)分<=4。單擊確定按鈕，出現(xiàn)如圖2.46所示的對(duì)話框。圖2.46輸入表達(dá)式后的check約束對(duì)話框

⑤對(duì)該約束進(jìn)行其它設(shè)置，如可以選擇【在創(chuàng)建或重新啟動(dòng)時(shí)檢查現(xiàn)有數(shù)據(jù)】復(fù)選框?qū)ΜF(xiàn)存的數(shù)據(jù)進(jìn)行檢查；而設(shè)置【強(qiáng)制用于INSERT和UPDATE】選項(xiàng)為是，會(huì)使插入或修改數(shù)據(jù)時(shí)，檢查約束；當(dāng)設(shè)置【對(duì)復(fù)制強(qiáng)制約束】，以使在進(jìn)行數(shù)據(jù)復(fù)制