創(chuàng)建數(shù)據(jù)庫和表

創(chuàng)建數(shù)據(jù)庫和表創(chuàng)建數(shù)據(jù)庫和表創(chuàng)建數(shù)據(jù)庫和表項目三新建數(shù)據(jù)庫和表了解SQLServer2008中數(shù)據(jù)庫的存儲結(jié)構(gòu)掌握使用SSMS創(chuàng)建、修改與刪除數(shù)據(jù)庫、表和關(guān)系的方法熟悉T-SQL語言并熟練編寫管理數(shù)據(jù)庫和表的語句理解SQLServer2008中的數(shù)據(jù)完整性機(jī)制2由于本人工作能力和接觸項目有限，希望借此機(jī)會將自己的體會與大家分享，更希望大家能提出更多更為深刻的意見!謝謝創(chuàng)建數(shù)據(jù)庫和表創(chuàng)建數(shù)據(jù)庫和表創(chuàng)建數(shù)據(jù)庫和表項目三新建數(shù)據(jù)項目三新建數(shù)據(jù)庫和表了解SQLServer2008中數(shù)據(jù)庫的存儲結(jié)構(gòu)掌握使用SSMS創(chuàng)建、修改與刪除數(shù)據(jù)庫、表和關(guān)系的方法熟悉T-SQL語言并熟練編寫管理數(shù)據(jù)庫和表的語句理解SQLServer2008中的數(shù)據(jù)完整性機(jī)制2項目三新建數(shù)據(jù)庫和表了解SQLServer2008中理論指導(dǎo)一、SQLServer2008中數(shù)據(jù)庫存儲結(jié)構(gòu)二、創(chuàng)建數(shù)據(jù)庫和表的方法三、用戶、角色和架構(gòu)3理論指導(dǎo)一、SQLServer2008中數(shù)據(jù)庫存儲結(jié)構(gòu)二圖1-1超市收款結(jié)算一、SQLServer2008中數(shù)據(jù)庫存儲結(jié)構(gòu)物理存儲結(jié)構(gòu)是指SQLServer2008中數(shù)據(jù)庫對象在磁盤上的存儲方式，邏輯存儲結(jié)構(gòu)是指操作系統(tǒng)和SQLServer2008所展示的存儲方式。（一）物理存儲結(jié)構(gòu)頁是SQLServer中存儲數(shù)據(jù)的最基本單位，當(dāng)表需要空間存放數(shù)據(jù)時，SQLServer以頁為單位分配空間給表，每頁的大小為8KB。標(biāo)頭96字節(jié)，存儲頁碼、頁類型、頁的可用空間等信息。每存放一個記錄，頁尾就會再用兩個字節(jié)來存放代表記錄位置的行偏移與其他信息。4圖1-1超市收款結(jié)算一、SQLServer2008中當(dāng)數(shù)據(jù)庫需要空間存儲表、索引等數(shù)據(jù)庫對象時，SQLServer會以區(qū)為單位分配空間。區(qū)是八個物理上連續(xù)的頁的集合，SQLServer有兩種類型的區(qū)：混合區(qū)：區(qū)中八頁的每頁可由不同的對象所有。例如區(qū)中有兩頁由A表使用，有一頁由B索引使用，當(dāng)建立新的表或索引時，SQLServer會查找此區(qū)中尚未使用的頁來存放。統(tǒng)一區(qū)：區(qū)中的所有八頁只能由單個對象使用。當(dāng)混合區(qū)中的表和索引大小增長到八頁時，它們就會被存放到專供自己使用的統(tǒng)一區(qū)中，以提高訪問效率。5當(dāng)數(shù)據(jù)庫需要空間存儲表、索引等數(shù)據(jù)庫對象時，SQL（二）邏輯存儲結(jié)構(gòu)1．系統(tǒng)數(shù)據(jù)庫和用戶數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫在安裝好SQLServer2008軟件后就已經(jīng)存在；用戶數(shù)據(jù)庫是指由用戶建立的數(shù)據(jù)庫。

master數(shù)據(jù)庫是SQLServer2008的總控數(shù)據(jù)庫，用于記錄系統(tǒng)配置信息、跟蹤用戶數(shù)據(jù)庫、管理系統(tǒng)資源分配以與響應(yīng)SQL語句等。

model數(shù)據(jù)庫作為SQLServer2008的原型數(shù)據(jù)庫，是系統(tǒng)創(chuàng)建所有數(shù)據(jù)庫的模板。系統(tǒng)數(shù)據(jù)庫還包括mssqlsystemresource，是一個隱藏的數(shù)據(jù)庫，系統(tǒng)數(shù)據(jù)庫的節(jié)點下看不到。

msdb數(shù)據(jù)庫是一個供系統(tǒng)使用的數(shù)據(jù)庫，用于記錄SQLServerAgent的相關(guān)信息。

tempdb數(shù)據(jù)庫用于保存所有的臨時表和臨時存儲過程。tempdb數(shù)據(jù)庫在SQLServer2008每次重啟時依據(jù)model數(shù)據(jù)庫進(jìn)行重建。

mssqlsystemresource通常被簡稱為resource數(shù)據(jù)庫，它是一個只讀數(shù)據(jù)庫，存放與SQLServer2008本身相關(guān)的系統(tǒng)對象。6（二）邏輯存儲結(jié)構(gòu)1．系統(tǒng)數(shù)據(jù)庫和用戶數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫在安裝2．?dāng)?shù)據(jù)庫文件數(shù)據(jù)庫文件是用來存儲數(shù)據(jù)庫數(shù)據(jù)和數(shù)據(jù)庫對象的文件，一般存放在數(shù)據(jù)庫安裝目錄或指定目錄的Data文件夾下面，由數(shù)據(jù)庫文件和事務(wù)日志文件組成。（1）數(shù)據(jù)庫文件主數(shù)據(jù)庫文件（PrimaryDatabaseFile）輔助數(shù)據(jù)庫文件（SecondaryDatabaseFile）一個數(shù)據(jù)庫只能有一個主數(shù)據(jù)庫文件，其擴(kuò)展名為.mdf。一個數(shù)據(jù)庫可以沒有也可以同時擁有多個輔助數(shù)據(jù)庫文件，其擴(kuò)展名為.ndf。72．?dāng)?shù)據(jù)庫文件數(shù)據(jù)庫文件是用來存儲數(shù)據(jù)庫數(shù)據(jù)和數(shù)據(jù)庫（2）事務(wù)日志文件主文件組：建立數(shù)據(jù)庫時默認(rèn)會產(chǎn)生的文件群，包含主數(shù)據(jù)庫文件。用戶定義的文件組：由用戶自己建立的文件組。默認(rèn)文件組：默認(rèn)文件組不是指不同于主文件組和用戶定義的文件組之外的另一種文件組，而是指數(shù)據(jù)庫目前默認(rèn)使用的文件組。8（2）事務(wù)日志文件主文件組：建立數(shù)據(jù)庫時默認(rèn)會產(chǎn)生的文件群，二、創(chuàng)建數(shù)據(jù)庫和表的方法可以通過SSMS中的命令或SQL語句兩種方式來實現(xiàn)。實現(xiàn)方法將在任務(wù)實踐和知識拓展中進(jìn)行介紹。9二、創(chuàng)建數(shù)據(jù)庫和表的方法可以通過SSMS中的命令或SQL語句三、用戶、角色和架構(gòu)SQLServer2008擁有強大的安全機(jī)制，而這主要是通過用戶、角色和架構(gòu)來實現(xiàn)的。10三、用戶、角色和架構(gòu)SQLServer2008擁任務(wù)實踐任務(wù)一創(chuàng)建“學(xué)生選課系統(tǒng)”數(shù)據(jù)庫任務(wù)二創(chuàng)建studentInfo表任務(wù)三創(chuàng)建studentInfo表與scoreInfo表間的關(guān)系11任務(wù)實踐任務(wù)一創(chuàng)建“學(xué)生選課系統(tǒng)”數(shù)據(jù)庫任務(wù)二創(chuàng)建s任務(wù)一創(chuàng)建“學(xué)生選課系統(tǒng)”數(shù)據(jù)庫在創(chuàng)建數(shù)據(jù)庫之前，需要考慮以下幾個問題：數(shù)據(jù)庫名稱數(shù)據(jù)庫文件的初始大小與增長方式主要包括數(shù)據(jù)和索引兩部分?jǐn)?shù)據(jù)庫的存儲位置12任務(wù)一創(chuàng)建“學(xué)生選課系統(tǒng)”數(shù)據(jù)庫在創(chuàng)建數(shù)據(jù)庫之前，需要考任務(wù)實施步驟1打開SSMS工具，連接到服務(wù)器后，在左窗格對象資源管理器中展開其樹形目錄，右擊“數(shù)據(jù)庫”文件夾，在彈出的快捷菜單中選擇“新建數(shù)據(jù)庫”選項，如圖所示。13任務(wù)實施步驟1打開SSMS工具，連接到服務(wù)器后，在左窗格步驟2打開的“新建數(shù)據(jù)庫”對話框?qū)⒛J(rèn)顯示“常規(guī)”選項頁，填寫數(shù)據(jù)庫的名稱將隨之生成同名的主數(shù)據(jù)庫文件和事務(wù)日志文件。設(shè)置數(shù)據(jù)庫文件與日志文件的初始大小和增長方式然后單擊“確定”按鈕，如圖所示。14步驟2打開的“新建步驟3接著單擊“新建數(shù)據(jù)庫”對話框中的“確定”按鈕，回到SSMS主窗口，在左窗格對象資源管理器中可以看到，“數(shù)據(jù)庫”選項下增加了我們剛剛建立的“CourseSelect”數(shù)據(jù)庫，如圖3-9所示。若對象資源管理器中暫時沒有顯示新建的數(shù)據(jù)庫，可以嘗試單擊對象資源管理器工具欄中的“刷新”按鈕。15步驟3接著單擊“新建數(shù)據(jù)庫”對話框中的“確定”按鈕，回到步驟4 增加新的數(shù)據(jù)文件：在SSMS中右擊需要修改的數(shù)據(jù)庫，選擇“屬性”選項，進(jìn)入“數(shù)據(jù)庫屬性”窗口，單擊左窗格中的“文件”選項，然后單擊右窗格下方的“添加”按鈕，在“數(shù)據(jù)庫文件”欄中將出現(xiàn)新添加的數(shù)據(jù)庫文件即可，如圖所示。16步驟4 增加新的數(shù)步驟5如果想要重命名或刪除數(shù)據(jù)庫，可在SSMS中右擊相應(yīng)數(shù)據(jù)庫，在彈出的快捷菜單中選擇“重命名”或“刪除”選項。17步驟5如果想要重命名或刪除數(shù)據(jù)庫，可在SSMS中右擊相應(yīng)任務(wù)二

創(chuàng)建studentInfo表此外，由于在admin和teacherInfo表中均存在密碼列，這里我們自定義一種密碼數(shù)據(jù)類型來方便創(chuàng)建其他表時使用。studentInfo表中，studentPassword字段用于存儲學(xué)生的密碼，系統(tǒng)最初為所有學(xué)生分配相同的密碼（如666666），學(xué)生進(jìn)行更改時設(shè)置要求輸入不小于6位的密碼。這里，我們需要創(chuàng)建一個默認(rèn)值和一個規(guī)則來實現(xiàn)上述要求。任務(wù)情境18任務(wù)二創(chuàng)建studentInfo表此外，由于在a任務(wù)實施首先創(chuàng)建password數(shù)據(jù)類型的默認(rèn)值為“666666”，規(guī)則為長度不能小于6位，然后將默認(rèn)值和規(guī)則綁定到該數(shù)據(jù)類型，最后創(chuàng)建studentInfo表時應(yīng)用此數(shù)據(jù)類型。19任務(wù)實施首先創(chuàng)建password數(shù)據(jù)類型的默認(rèn)值為“步驟1打開SSMS后，單擊工具欄中的“新建查詢”按鈕，并在工具欄中選擇“CourseSelect”數(shù)據(jù)庫。在右窗格的查詢窗口中輸入左圖中T-SQL語句（含義為創(chuàng)建默認(rèn)值對象password，其值為“666666”），單擊“執(zhí)行”按鈕。命令運行成功后，右窗格的“消息”選項卡中將提示“命令已成功完成”。同時在左窗格資源管理器“CourseSelect”→“可編程性”→“默認(rèn)值”文件夾下面可以看到新建的對象dbo.password，如右圖所示。20步驟1打開SSMS后，單擊工具欄中的“新建查詢”按鈕，并步驟2 創(chuàng)建規(guī)則與默認(rèn)值相似，在查詢查詢窗口中輸入T-SQL語句“CREATERULEpasswordLength_ruleASLEN(@password)>=6”（密碼長度大于或等于6位），然后單擊“執(zhí)行”按鈕。命令運行成功后，在左窗格資源管理器“CourseSelect”→“可編程性”→“規(guī)則”文件夾下面可以看到新建的文件。21步驟2 創(chuàng)建規(guī)則與默認(rèn)值相似，在查詢查詢窗口中輸入T-S步驟3 下面創(chuàng)建password數(shù)據(jù)類型，在左窗格對象資源管理器中依次展開“CourseSelect”→“可編程性”→“類型”文件夾，右擊“用戶定義數(shù)據(jù)類型”對象，在彈出的快捷菜單中選擇“新建用戶定義數(shù)據(jù)類型”選項，如左圖所示。在“新建用戶定義數(shù)據(jù)類型”對話框中輸入自定義數(shù)據(jù)類型的名字password與所依據(jù)的系統(tǒng)數(shù)據(jù)類型varchar和長度32，如右圖所示。22步驟3 下面創(chuàng)建password數(shù)據(jù)類型，在左窗格對象資步驟4 下面將默認(rèn)值和規(guī)則綁定到password數(shù)據(jù)類型。單擊對話框中默認(rèn)值右側(cè)的按鈕，打開“選擇對象”對話框，在該對話框中單擊“瀏覽”按鈕打開“查找對象”對話框，勾選之前創(chuàng)建的默認(rèn)值，然后單擊兩次“確定”按鈕，如圖所示。按同樣方式綁定規(guī)則。最后回到“新建用戶定義數(shù)據(jù)類型”對話框中，單擊“確定”按鈕，即完成數(shù)據(jù)類型的創(chuàng)建。23步驟4 下面將默認(rèn)值和規(guī)則綁定到password數(shù)據(jù)類型。步驟5 下面我們開始創(chuàng)建studentInfo表。如左圖所示，在左窗格對象資源管理器中右擊“CourseSelect”結(jié)點下的“表”對象，在彈出的快捷菜單中選擇“新建表”選項，在右窗格中依次輸入studentInfo表的列名并為其選擇正確的數(shù)據(jù)類型（注意studentPassword列選擇自定義的password數(shù)據(jù)類型），然后單擊studentNumber列前的灰色方塊選中該列，隨后單擊工具欄中的按鈕，將此列設(shè)為表的主鍵，如右圖所示。24步驟5 下面我們開始創(chuàng)建studentInfo表。如左圖所步驟6 表中信息填寫完成后，單擊工具欄中的“保存”按鈕，將彈出“選擇名稱”對話框。如圖所示，輸入表的名字后單擊“確定”按鈕，即完成表的創(chuàng)建。25步驟6 表中信息填寫完成后，單擊工具欄中的“保存”按鈕，將步驟7 向新創(chuàng)建的表中輸入幾條測試數(shù)據(jù)以驗證之前創(chuàng)建的默認(rèn)值和規(guī)則。如左圖所示，在左窗格對象資源管理器中依次展開“CourseSelect”→“表”結(jié)點，右擊“dbo.studentInfo”文件夾，在彈出的快捷菜單中選擇“編輯前200行”選項，然后在右窗格輸入測試數(shù)據(jù)：輸入學(xué)號信息并按回車鍵，該條記錄將自動添加密碼“666666”；將密碼改為3位后，系統(tǒng)將提示出錯，如右圖所示。26步驟7 向新創(chuàng)建的表中輸入幾條測試數(shù)據(jù)以驗證之前創(chuàng)建的默認(rèn)任務(wù)三

創(chuàng)建studentInfo表與scoreInfo表間的關(guān)系任務(wù)情境將14個表創(chuàng)建完成后，為保證參照完整性，還需要創(chuàng)建表與表之間的外鍵關(guān)系。任務(wù)實施步驟1打開SSMS后，單擊左窗格資源管理器“CourseSelect”文件下的“數(shù)據(jù)庫關(guān)系圖”對象，在彈出的快捷菜單中選擇“新建數(shù)據(jù)庫關(guān)系圖”選項，如圖所示。27任務(wù)三創(chuàng)建studentInfo表與scoreInfo表步驟2彈出的“添加表”對話框，按住【Shift】鍵選中已經(jīng)建立的14張表，然后單擊“添加”按鈕，如右圖所示。28步驟2彈出的“添加表”對話框，按住【Shift】鍵選中已步驟3默認(rèn)情況下，這14張表在SSMS右窗格中將以縮略圖，我們僅以創(chuàng)建studentInfo和scoreInfo表之間的外鍵關(guān)系為例進(jìn)行講解。選中scoreInfo表中的studentNumber列，然后參照圖中所示拖拽鼠標(biāo)至studentInfo表中的studentNumber列，當(dāng)光標(biāo)箭頭旁邊出現(xiàn)“+”號時，松開鼠標(biāo)。29步驟3默認(rèn)情況下，這14張表在SSMS右窗格中將以縮略圖步驟4如圖3-21所示，系統(tǒng)將連續(xù)打開兩個對話框，此時我們只能操作“表和列”對話框，確認(rèn)建立關(guān)系的主外鍵中的表和列正確，單擊“確定”按鈕，接下來我們就可以操作“外鍵關(guān)系”對話框了。30步驟4如圖3-21所示，系統(tǒng)將連續(xù)打開兩個對話框，此時我步驟5如左圖所示，展開該對話框中“INSERT和UPDATE規(guī)范”結(jié)點，單擊“更新規(guī)則”右側(cè)的按鈕，選擇“級聯(lián)”選項。按同樣的方式設(shè)置“刪除”規(guī)則也為“級聯(lián)”。右圖顯示了創(chuàng)建完成的外鍵關(guān)系。31步驟5如左圖所示，展開該對話框中“INSERT和UPDA知識拓展一、修改表結(jié)構(gòu)與設(shè)置標(biāo)識列二、使用T-SQL語句創(chuàng)建數(shù)據(jù)庫和表三、數(shù)據(jù)完整性32知識拓展一、修改表結(jié)構(gòu)與設(shè)置標(biāo)識列二、使用T-SQL語句創(chuàng)建一、修改表結(jié)構(gòu)與設(shè)置標(biāo)識列修改表結(jié)構(gòu)右擊該表，在彈出的快捷菜單中選擇“設(shè)計”選項，在SSMS右窗格中可進(jìn)行修改。增加或刪除列行列名和數(shù)據(jù)類型的更改默認(rèn)情況下，若增加或刪除列，應(yīng)先刪除表再重新建立。這里先更改數(shù)據(jù)庫的默認(rèn)設(shè)置。選擇“工具”→“選項”菜單，在彈出的“選項”對話框左窗格中展開“Designers”結(jié)點，選擇“表設(shè)計和數(shù)據(jù)庫設(shè)計器”選項，去掉右窗格中“阻止保存要求重新創(chuàng)建表的更改”前的框選符號，單擊“確定”按鈕。33一、修改表結(jié)構(gòu)與設(shè)置標(biāo)識列修改表結(jié)構(gòu)右擊該表，在彈出接下來右擊表中某列，在彈出的快捷菜單中選擇“插入列”或“刪除列”選項，然后編輯新添加的列，最后保存表即可，如圖所示。34接下來右擊表中某列，在彈出的快捷菜單中選擇“插入列”設(shè)置標(biāo)識列步驟1 在表結(jié)構(gòu)可編輯的狀態(tài)下，選中所需設(shè)置的列，展開下方“列屬性”選項卡中“標(biāo)識規(guī)范”結(jié)點，如圖所示。步驟2將“標(biāo)識規(guī)范”和“(是標(biāo)識)”右側(cè)單元格中數(shù)據(jù)設(shè)為“是”，然后填寫“標(biāo)識增量”和“標(biāo)識種子”后，最后單擊“保存”按鈕。35設(shè)置標(biāo)識列步驟1 在表結(jié)構(gòu)可編輯的狀態(tài)下，選中所需設(shè)置的列二、使用T-SQL語句創(chuàng)建數(shù)據(jù)庫和表（一）T-SQL語句T-SQL語句分為四大類：數(shù)據(jù)定義語句（DDL）、數(shù)據(jù)操作語句（DML）、數(shù)據(jù)控制語句（DCL）和一些附加的語言元素。數(shù)據(jù)定義語句（DDL）：用來定義和管理數(shù)據(jù)庫對象。常用命令關(guān)鍵字包括CREATE、ALTER和DROP等。數(shù)據(jù)操作語句（DML）：用于查詢和更新數(shù)據(jù)。常用命令關(guān)鍵字包括SELECT、INSERT、UPDATE和DELETE等。數(shù)據(jù)控制語句（DCL）：用于設(shè)置數(shù)據(jù)庫操作對象的權(quán)限。常用命令關(guān)鍵字包括GRANT、REVOKE和DENY等。36二、使用T-SQL語句創(chuàng)建數(shù)據(jù)庫和表（一）T-SQL語句T-SQL語法約定37T-SQL語法約定37（二）創(chuàng)建、修改與刪除數(shù)據(jù)庫語句創(chuàng)建數(shù)據(jù)庫語句其中：<>::={(NAME=logical_,='os_'[,SIZE=size[KB|MB|GB|TB]][,MAXSIZE={max_size[KB|MB|GB|TB]|UNLIMITED}][,=growth_increment[KB|MB|GB|TB|%]])[,…n]}<>::={[DEFAULT]<>[,…n]}CREATEDATABASEdatabase_name[ON[PRIMARY][<>[,…n][,<>[,…n]][LOGON{<>[,…n]}]][COLLATEcollation_name]]38（二）創(chuàng)建、修改與刪除數(shù)據(jù)庫語句創(chuàng)建數(shù)據(jù)庫語句其中：CREA修改數(shù)據(jù)庫語句

ALTERDATABASEdatabase{ADDFILE<>[,...n][TO]|ADDLOGFILE<>[,...n]|REMOVEFILElogical_|ADD_name|REMOVE_name|MODIFYFILE<>|MODIFYNAME=new_dbn