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

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

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

表的基本概念1

表的類型2

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

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

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

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

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

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

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

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

CREATETABLE表名

（列名1數(shù)據(jù)類型列級完整性約束，列名2數(shù)據(jù)類型列級完整性約束， …列名n類型約束，表級完整性約束，…）；SQLServer20194.2.2使用T-SQL語句創(chuàng)建表約束：實現(xiàn)表的完整性NULL/NOTNULL：空值/非空值約束。DEFAULT常量表達式：默認值約束。UNIQUE：單值約束。PRIMARYKEY：主鍵約束，等價非空、單值。REFERENCES父表名(主鍵)：外鍵約束。CHECK(邏輯表達式)：檢查約束。SQLServer20194.2.2使用T-SQL語句創(chuàng)建表例如：在圖書管理系統(tǒng)中的數(shù)據(jù)庫（Library）中，創(chuàng)建讀者表（Reader）,讀者類型表（ReaderType），圖書表（Book）和借閱表（Borrow）。圖書管理系統(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語句創(chuàng)建表例1：創(chuàng)建讀者類型表ReaderTypeCREATETABLEReaderType(TypeIDintNOTNULLprimarykey,--類型編號,主鍵Typenamechar(8)NULL,--類型名稱LimitNumintNULL,--限借數(shù)量LimitDaysintNULL--借閱期限)SQLServer20194.2.2使用T-SQL語句創(chuàng)建表例2：創(chuàng)建讀者表Reade

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

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

使用SSMS修改表1

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

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

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

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

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

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

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

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

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

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

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

使用SSMS刪除表1

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

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

USELibraryGODROPTABLETest

SQLServer20194.5插入記錄

使用SSMS插入記錄1

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

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

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

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

使用SSMS刪除記錄1

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

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

索引的基本知識1

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

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

使用SSMS刪除索引4

使用T-SQL語句刪除索引5SQLServer20194.8.1索引的基本知識

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

索引的類型

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

SQLServer20194.8.1索引的基本知識索引的類型

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

等的列不能建立索引。

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

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

SQL