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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

①

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

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

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

圖2.35添加表對話框

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

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

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

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

⑥

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

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

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

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

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）一對一的聯(lián)系（1：1）。

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

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

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

①方框

②橢圓框

③箭頭

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

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

列：每一列代表由表建模的對象的某個屬性。例如，一個部件表有編號列、顏色列和重量列。

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

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

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

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

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

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

（2）域完整性

（3）引用完整性

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

PRIMARYKEY即主鍵，是最重要的約束類型，一個表通常可以通過一個字段(或多個字段組合)的數(shù)據(jù)來唯一標(biāo)識表中的每一行，這個字段(或字段組合)被稱為表的主鍵。2)空值(NULL)約束

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

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

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

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

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

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

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

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

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

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

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

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

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

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