數(shù)據(jù)庫(kù)應(yīng)用技術(shù)SQLServer5課件

數(shù)據(jù)庫(kù)應(yīng)用技術(shù)

SQLServer2019SQLServer2019第4章SQLServer2019表的創(chuàng)建與管理能力目標(biāo):能根據(jù)項(xiàng)目邏輯設(shè)計(jì)應(yīng)用SSMS和T-SQL創(chuàng)建表。能根據(jù)項(xiàng)目邏輯設(shè)計(jì)中完整性規(guī)則應(yīng)用SSMS和T-SQL設(shè)置表的主碼、約束和外碼。能根據(jù)邏輯設(shè)計(jì)設(shè)置合適的索引。理解實(shí)體和實(shí)體之間的聯(lián)系在數(shù)據(jù)庫(kù)中的實(shí)現(xiàn)。能根據(jù)項(xiàng)目邏輯設(shè)計(jì)創(chuàng)建并管理關(guān)系圖。通過(guò)數(shù)據(jù)操作規(guī)范，培養(yǎng)嚴(yán)謹(jǐn)?shù)目茖W(xué)態(tài)度。任務(wù)設(shè)計(jì):根據(jù)項(xiàng)目邏輯設(shè)計(jì)創(chuàng)建表。根據(jù)項(xiàng)目邏輯設(shè)計(jì)中完整性規(guī)則設(shè)置表的主碼、約束和外碼。SQLServer2019第4章SQLServer2019表的創(chuàng)建與管理創(chuàng)建合適的索引。創(chuàng)建關(guān)系圖。插入一定數(shù)量的記錄供后續(xù)任務(wù)使用。知識(shí)要求:SQLServer2019表的基本知識(shí)。表的創(chuàng)建、修改和刪除操作方法。記錄的插入、刪除和修改操作方法。索引的基本知識(shí)。索引的創(chuàng)建和刪除操作方法。SQLServer2019關(guān)系圖。SQLServer2019第4章SQLServer2019表的創(chuàng)建與管理掌握表的創(chuàng)建、修改和刪除操作了解SQLServer2019表的基本知識(shí)熟練掌握記錄的插入、刪除和修改操作了解索引的基本知識(shí)掌握索引的創(chuàng)建和刪除操作目標(biāo)SQLServer20194.1SQLServer2019表的基本知識(shí)

表的基本概念1

表的類(lèi)型2

表的數(shù)據(jù)類(lèi)型3

表的完整性體現(xiàn)41

表的設(shè)計(jì)5SQLServer20194.1.1表的基本概念表是數(shù)據(jù)庫(kù)對(duì)象，用于存儲(chǔ)實(shí)體集和實(shí)體間聯(lián)系的數(shù)據(jù)。SQLServer2019表主要由列和行構(gòu)成。關(guān)系模型的組成要素列：每一列用來(lái)保存對(duì)象的某一類(lèi)屬性。行：每一行用來(lái)保存一條記錄，是數(shù)據(jù)對(duì)象的一個(gè)實(shí)例。SQLServer20194.1.1表的基本概念例如：教務(wù)管理數(shù)據(jù)庫(kù)EDUC中的學(xué)生Student表。SQLServer20194.1.2表的類(lèi)型SQLServer2019除了提供了用戶(hù)定義的標(biāo)準(zhǔn)表外，還提供了一些特殊用途的表：分區(qū)表、臨時(shí)表和系統(tǒng)表。分區(qū)表當(dāng)表很大時(shí)，可以水平地把數(shù)據(jù)分割成一些單元，放在同一個(gè)數(shù)據(jù)庫(kù)的多個(gè)文件組中。用戶(hù)可以通過(guò)分區(qū)快速地訪(fǎng)問(wèn)和管理數(shù)據(jù)的某部分子集而不是整個(gè)數(shù)據(jù)表，從而便于管理大表和索引。SQLServer20194.1.2表的類(lèi)型臨時(shí)表局部臨時(shí)表：只是對(duì)一個(gè)數(shù)據(jù)庫(kù)實(shí)例的一次連接中的創(chuàng)建者是可見(jiàn)的。在用戶(hù)斷開(kāi)數(shù)據(jù)庫(kù)的連接時(shí)，局部臨時(shí)表就被刪除。全局臨時(shí)表：對(duì)所有的用戶(hù)和連接都是可見(jiàn)的，并且只有所有的用戶(hù)都斷開(kāi)臨時(shí)表相關(guān)的表時(shí)，全局臨時(shí)表才會(huì)被刪除。系統(tǒng)表用來(lái)保存一些服務(wù)器配置信息數(shù)據(jù)，用戶(hù)不能直接查看和修改系統(tǒng)表，只有通過(guò)專(zhuān)門(mén)的管理員連接才能查看和修改。不同版本的數(shù)據(jù)庫(kù)系統(tǒng)的系統(tǒng)表一般不同，在升級(jí)數(shù)據(jù)庫(kù)系統(tǒng)時(shí)，一些應(yīng)用系統(tǒng)表的應(yīng)用可能需要重新改寫(xiě)。SQLServer20195.1.3表的數(shù)據(jù)類(lèi)型下表列出了SQLServer2019中常用的數(shù)據(jù)類(lèi)型：SQLServer20194.1.4表的完整性體現(xiàn)主鍵約束體現(xiàn)實(shí)體完整性，即主鍵各列不能為空且主鍵作為行的惟一標(biāo)識(shí)系統(tǒng)表外鍵約束體現(xiàn)參照完整性默認(rèn)值和規(guī)則等體現(xiàn)用戶(hù)定義的完整性SQLServer20194.1.5表的設(shè)計(jì)設(shè)計(jì)表時(shí)需要確定如下內(nèi)容：表中需要的列以及每一列的類(lèi)型（必要時(shí)還要有長(zhǎng)度）列是否可以為空是否需要在列上使用約束、默認(rèn)值和規(guī)則需要使用什么樣的索引哪些列作為主鍵SQLServer20194.2創(chuàng)建表

使用SSMS創(chuàng)建表1

使用T-SQL語(yǔ)句創(chuàng)建表2SQLServer20194.2.1使用SSMS創(chuàng)建表創(chuàng)建主鍵約束創(chuàng)建唯一性約束創(chuàng)建外鍵約束創(chuàng)建檢查約束

保存表的定義創(chuàng)建表SQLServer20194.2.1使用SSMS創(chuàng)建表例如：在教務(wù)管理中的選課數(shù)據(jù)庫(kù)（EDUC）中創(chuàng)建學(xué)生表Student,課程表Course,選課表SC。其中教務(wù)管理中的選課數(shù)據(jù)模型為：

Student(SID,Sname,Sex,Birthday,Specialty)PK:SIDCourse(CID,Cname,Credit）PK:CIDSC(SID,CID,Grade)PK:SID,CID

FK:SID和CID

SQLServer20194.2.1使用SSMS創(chuàng)建表創(chuàng)建表：在【對(duì)象資源管理器】窗口中，展開(kāi)“數(shù)據(jù)庫(kù)”下的EDUC節(jié)點(diǎn)，右擊“表”節(jié)點(diǎn)，選擇“新建表”命令，進(jìn)入表設(shè)計(jì)器，在表設(shè)計(jì)器的第一列中輸入列名，第二列選擇數(shù)據(jù)類(lèi)型，第三列選擇是否為空；例如：表StudentSQLServer20194.2.1使用SSMS創(chuàng)建表表Course：表SC：SQLServer20194.2.1使用SSMS創(chuàng)建表創(chuàng)建主鍵約束：?jiǎn)螕暨x擇一列名，SHIFT+單擊選擇連續(xù)的列名，CTRL+單擊選擇不相鄰的列名，右鍵快捷菜單或工具欄按鈕—“設(shè)置主鍵”；例：Student中的SID，Course中的CID，SC中的SID,CID做法：?jiǎn)螕暨x擇一列名，SHIFT+單擊選擇連續(xù)的列名，CTRL+單擊選擇不相鄰的列名，右鍵快捷菜單或工具欄按鈕—“設(shè)置主鍵”；SQLServer20194.2.1使用SSMS創(chuàng)建表Student，Course表主鍵約束采用同樣的方法設(shè)置；Course表的主鍵建立示意圖如下：SQLServer20194.2.1使用SSMS創(chuàng)建表創(chuàng)建唯一性約束：例如：Student表中的Sname做法：右鍵快捷菜單或工具欄按鈕—“索引/鍵”，在彈出的【索引/鍵】對(duì)話(huà)框中，單擊“添加”按鈕添加新的主/惟一鍵或索引；在常規(guī)的“類(lèi)型”右邊選擇“惟一鍵”，在“列”的右邊單擊省略號(hào)按鈕，選擇列名Sname和排序規(guī)律；SQLServer20194.2.1使用SSMS創(chuàng)建表如圖所示：SQLServer20194.2.1使用SSMS創(chuàng)建表創(chuàng)建外鍵約束例如：SC表中的SID和CID設(shè)置為外碼。做法：（1）右鍵快捷菜單或工具欄—“關(guān)系”，在彈出的【關(guān)系】對(duì)話(huà)框中，單擊“添加”按鈕添加新的約束關(guān)系；SQLServer20194.2.1使用SSMS創(chuàng)建表（2）單擊“表和列規(guī)范”左邊的“＋”號(hào)，再單擊“表和列規(guī)范”內(nèi)容框中右邊的省略號(hào)按鈕，從彈出的【表和列】對(duì)話(huà)框中進(jìn)行外鍵約束的表和列的選擇，單擊“確定”；SQLServer20194.2.1使用SSMS創(chuàng)建表（3）回到【外鍵關(guān)系】對(duì)話(huà)框，將“強(qiáng)制外鍵約束”選項(xiàng)選擇為“是”，設(shè)置“更新規(guī)則”和“刪除規(guī)則”的值；

采用同樣的方法添加外鍵CID；SQLServer20194.2.1使用SSMS創(chuàng)建表創(chuàng)建檢查約束

例如：Student表中的Sex等于男或女。

做法：右鍵菜單或工具欄—“CHECK約束”，在打開(kāi)的【CHECK約束】對(duì)話(huà)框中單擊“添加”按鈕，在表達(dá)式文本框中輸入檢查表達(dá)式，在表設(shè)計(jì)器中進(jìn)行選項(xiàng)的設(shè)置；SQLServer20194.2.1使用SSMS創(chuàng)建表保存表的定義（1）單擊關(guān)閉表設(shè)計(jì)器窗口，彈出下圖保存對(duì)話(huà)框，單擊“是”鈕；（2）輸入表名，單擊“確定”按鈕。SQLServer20194.2.2使用T-SQL語(yǔ)句創(chuàng)建表格式：

CREATETABLE表名

（列名1數(shù)據(jù)類(lèi)型列級(jí)完整性約束，列名2數(shù)據(jù)類(lèi)型列級(jí)完整性約束， …列名n類(lèi)型約束，表級(jí)完整性約束，…）；SQLServer20194.2.2使用T-SQL語(yǔ)句創(chuàng)建表約束：實(shí)現(xiàn)表的完整性NULL/NOTNULL：空值/非空值約束。DEFAULT常量表達(dá)式：默認(rèn)值約束。UNIQUE：?jiǎn)沃导s束。PRIMARYKEY：主鍵約束，等價(jià)非空、單值。REFERENCES父表名(主鍵)：外鍵約束。CHECK(邏輯表達(dá)式)：檢查約束。SQLServer20194.2.2使用T-SQL語(yǔ)句創(chuàng)建表例如：在圖書(shū)管理系統(tǒng)中的數(shù)據(jù)庫(kù)（Library）中，創(chuàng)建讀者表（Reader）,讀者類(lèi)型表（ReaderType），圖書(shū)表（Book）和借閱表（Borrow）。圖書(shū)管理系統(tǒng)的數(shù)據(jù)模型：ReaderType(TypeID,Typename,LimitNum,LimitDays)PK:TypeIDReader(RID,Rname,TypeID,Lendnum)PK：RIDFK：TypeIDBook(BID,Bname,Author,PubComp,PubDate,Price)PK：BIDBorrow(RID,BID,LendDate,ReturnDate)PK：RID,BID,LendDateFK：RID和BID

SQLServer20194.2.2使用T-SQL語(yǔ)句創(chuàng)建表例1：創(chuàng)建讀者類(lèi)型表ReaderTypeCREATETABLEReaderType(TypeIDintNOTNULLprimarykey,--類(lèi)型編號(hào),主鍵Typenamechar(8)NULL,--類(lèi)型名稱(chēng)LimitNumintNULL,--限借數(shù)量LimitDaysintNULL--借閱期限)SQLServer20194.2.2使用T-SQL語(yǔ)句創(chuàng)建表例2：創(chuàng)建讀者表Reade

USELibraryGOCREATETABLEReader(RIDchar(10)NOTNULLPRIMARYKEY,--讀者編號(hào)，主鍵Rnamechar(8)NULL,--讀者姓名TypeIDintNULL,--讀者類(lèi)型LendnumintNULL,--已借數(shù)量FOREIGNKEY(TypeID)REFERENCESReaderType(TypeID)ONDELETENOACTION,--外鍵，不級(jí)聯(lián)刪除)SQLServer20194.2.2使用T-SQL語(yǔ)句創(chuàng)建表例3：創(chuàng)建圖書(shū)表BookUSELibraryGOCREATETABLEBook(BIDchar(9)PRIMARYKEY,--圖書(shū)編號(hào)，主鍵Bnamevarchar(42)NULL,--圖書(shū)書(shū)名Authorvarchar(20)NULL,--作者PubCompvarchar(28)NULL,--出版社PubDatedatetimeNULL,--出版日期Pricedecimal(7,2)NULLCHECK(Price>0)--定價(jià)，檢查約束)

SQLServer20194.2.2使用T-SQL語(yǔ)句創(chuàng)建表例4：創(chuàng)建圖書(shū)借閱表Borrow(RID,BID,LendDate,ReturnDate)USELibraryGOCREATETABLEBorrow(RIDchar(10)NOTNULL--讀者編號(hào)外鍵FOREIGNKEYREFERENCESReader(RID)ONDELETECASCADE,/*刪除主表記錄時(shí)級(jí)聯(lián)刪除子表相應(yīng)記錄*/BIDchar(15)NOTNULL--圖書(shū)編號(hào)外鍵FOREIGNKEYREFERENCESBook(BID)ONDELETENOACTION,/*刪除主表記錄時(shí)不級(jí)聯(lián)刪除子表相應(yīng)記錄*/LendDatedatetimeNOTNULLDEFAULT(getdate()),/*借期，默認(rèn)值為當(dāng)前日期*/ReturnDatedatetimeNULL,--還期primarykey(RID,BID,LendDate))--表級(jí)約束，主鍵SQLServer20194.3修改表

使用SSMS修改表1

使用T-SQL語(yǔ)句修改表2SQLServer20194.3.1使用SSMS修改表在【對(duì)象資源管理器】窗口中，展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)展開(kāi)所選擇的具體數(shù)據(jù)庫(kù)節(jié)點(diǎn)，展開(kāi)“表”節(jié)點(diǎn)右鍵要修改的表，選擇“修改”命令進(jìn)入表設(shè)計(jì)器即可進(jìn)行表的定義的修改SQLServer20194.3.2使用T-SQL語(yǔ)句修改表格式：

ALTERtable表名(ALTERCOLUMN列名列定義，ADD列名1類(lèi)型約束，DROP列名…）*列定義包括列的數(shù)據(jù)類(lèi)型和完整性約束

SQLServer20194.3.2使用T-SQL語(yǔ)句修改表231修改屬性添加或刪除列添加或刪除約束SQLServer20194.3.2使用T-SQL語(yǔ)句修改表修改屬性

例如：把表Book中PubComp的類(lèi)型varchar(28)改為varchar(30)

USELibraryGOALTERTABLEBookALTERCOLUMNPubCompvarchar(30)NOTNULLGOSQLServer20194.3.2使用T-SQL語(yǔ)句修改表添加或刪除列例1：為表Reader添加郵件地址

USELibraryGOALTERTABLEReaderADDE-mailvarchar(50)NULLCHECK(E-maillike'%%')GO

例2：為表Reader刪除郵件地址。

USELibraryGOALTERTABLEReaderDROPCOLUMNE-mailGO說(shuō)明：必須先刪除其上的約束。SQLServer20194.3.2使用T-SQL語(yǔ)句修改表添加或刪除約束

例1：為表Borrow添加主鍵約束（假設(shè)還沒(méi)有創(chuàng)建）。

USELibraryGOALTERTABLEBorrowADDPRIMARYKEY(RID,BID,LendDate)GO例2：為表Borrow刪除主鍵約束。

USELibraryGOALTERTABLEBorrowDROPPRIMARYKEY(RID,BID,LendDate)GOSQLServer20194.4刪除表

使用SSMS刪除表1

使用T-SQL語(yǔ)句刪除表2SQLServer20194.4.1使用SSMS刪除表在【對(duì)象資源管理器】窗口中，展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)展開(kāi)所選擇的具體數(shù)據(jù)庫(kù)節(jié)點(diǎn)，展開(kāi)“表”節(jié)點(diǎn)右鍵要?jiǎng)h除的表，選擇“刪除”命令或DELETE鍵SQLServer20194.4.2使用T-SQL語(yǔ)句刪除表格式：

DROPTABLE表名例如：先隨便在數(shù)據(jù)庫(kù)Library中建一個(gè)表Test，然后刪除。

USELibraryGODROPTABLETest

SQLServer20194.5插入記錄

使用SSMS插入記錄1

使用T-SQL語(yǔ)句插入記錄2SQLServer20194.5.1使用SSMS插入記錄在【對(duì)象資源管理器】窗口中，展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，展開(kāi)所選擇的具體數(shù)據(jù)庫(kù)節(jié)點(diǎn)，展開(kāi)“表”節(jié)點(diǎn)，右鍵要插入紀(jì)錄的表，選擇“打開(kāi)表”命令，即可輸入紀(jì)錄值例如：表ReaderTypeSQLServer20194.5.2使用T-SQL語(yǔ)句插入記錄格式：

INSERT[INTO]（表名|視圖名）[列名表]VALUES（常量表）例1：插入一行所有列的值

USELibraryGOINSERTintoReaderVALUES('2019216001','趙成剛',3,2,'zhchgsina')GO例1：插入一行部分列

USELibraryGOINSERTReader(RID,Rname,TypeID)VALUES('2019060003','李亞茜',3)GO

SQLServer20194.6修改記錄

使用SSMS修改記錄1

使用T-SQL語(yǔ)句修改記錄2SQLServer20194.6.1使用SSMS修改記錄在【對(duì)象資源管理器】窗口中，展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)展開(kāi)所選擇的具體數(shù)據(jù)庫(kù)節(jié)點(diǎn)，展開(kāi)“表”節(jié)點(diǎn)右鍵要修改紀(jì)錄的表，選擇“打開(kāi)表”命令，即可修改紀(jì)錄值。SQLServer20194.6.2使用T-SQL語(yǔ)句修改記錄格式：

UPDATE表名SET列名1＝表達(dá)式,…列名n＝表達(dá)式where邏輯表達(dá)式例1：把讀者類(lèi)型表ReaderType中學(xué)生的限借數(shù)量5本增加2本

USELibraryGOUPDATEReaderTypeSETLimitNum=LimitNum+2WHERETypename='學(xué)生'GOSQLServer20194.7刪除記錄

使用SSMS刪除記錄1

使用T-SQL語(yǔ)句刪除記錄2SQLServer20194.7.1使用SSMS修改記錄在【對(duì)象資源管理器】窗口中，展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)展開(kāi)所選擇的具體數(shù)據(jù)庫(kù)節(jié)點(diǎn)，展開(kāi)“表”節(jié)點(diǎn)右鍵要?jiǎng)h除紀(jì)錄的表，選擇“打開(kāi)表”命令，右擊要?jiǎng)h除的行，選擇“刪除”命令即可刪除紀(jì)錄。SQLServer20194.7.2使用T-SQL語(yǔ)句修改記錄格式：

DELETE表名WHERE邏輯表達(dá)式例1：刪除Borrow表中RID為'2019216001'的讀者的借書(shū)記錄USELibraryGODELETEReaderWHERERID='2019216001'GO5.3E-R模型到關(guān)系模型的轉(zhuǎn)換例2：刪除test表中的所有記錄USELibraryGODELETEtestSQLServer20194.8索引的基本操作

索引的基本知識(shí)1

使用SSMS創(chuàng)建索引2

使用T-SQL語(yǔ)句創(chuàng)建索引3

使用SSMS刪除索引4

使用T-SQL語(yǔ)句刪除索引5SQLServer20194.8.1索引的基本知識(shí)

索引基礎(chǔ)知識(shí)索引的類(lèi)型創(chuàng)建原則及注意事項(xiàng)SQLServer20194.8.1索引的基本知識(shí)索引基礎(chǔ)知識(shí)

索引的類(lèi)型

用戶(hù)可以通過(guò)合理地創(chuàng)建索引大大提高數(shù)據(jù)庫(kù)的查找速度索引也可以保證列的惟一性，從而確保表中數(shù)據(jù)的完整性索引中包含鍵值，這些鍵值存儲(chǔ)在一種數(shù)據(jù)結(jié)構(gòu)（B-樹(shù)）中，通鍵 可以快速地找到與鍵值相關(guān)的數(shù)據(jù)記錄

SQLServer20194.8.1索引的基本知識(shí)索引的類(lèi)型

聚集索引：根據(jù)索引的鍵值排序，表中的數(shù)據(jù)并保存。每個(gè)表只能有一個(gè)聚集索引。非聚集索引：索引的鍵值包含指向表中記錄存儲(chǔ)位置的指針，不對(duì)表中數(shù)據(jù)排序，只對(duì)鍵值排序。惟一索引：保證索引中不含有相同的鍵值，聚集索引和非聚集索引都可以是惟一索引。包含列的索引：一種非聚集索引，其中包含一些非鍵值的列，這些列對(duì)鍵只有輔助作用。全文（full-text）索引：上Microsoft全文引擎（full-textengine）創(chuàng)建并管理的一種基開(kāi)符號(hào)的函數(shù)（token-basedfunctional）索引，支持快速的字符串中單詞的查找。SQLServer20194.8.1索引的基本知識(shí)創(chuàng)建原則及注意事項(xiàng)下列情況適合建立索引：經(jīng)常被查詢(xún)搜索的列，如經(jīng)常在where子句中出現(xiàn)的列。在ORDERBY子句使用的列。外鍵或主鍵列。下列情況不適合建立索引：在查詢(xún)中很少被引用的列。包含太多重復(fù)值的列。數(shù)據(jù)類(lèi)型為bit、text、image

等的列不能建立索引。

SQLServer20194.8.2使用SSMS創(chuàng)建索引（1）單擊相應(yīng)表左邊的“＋”號(hào)，右擊“索引”節(jié)點(diǎn)，選擇“新建索引”命令；

（2）在彈出的【新建索引】對(duì)話(huà)框中設(shè)置要?jiǎng)?chuàng)建索引的名稱(chēng)、類(lèi)型，添加索引鍵列。SQLServer20194.8.2使用SSMS創(chuàng)建索引例：在EDUC數(shù)據(jù)庫(kù)中為Student建立非聚集的惟一的復(fù)合索引Studentindex（包含SID和Sname列）。

SQL