數(shù)據(jù)庫系統(tǒng)原理與設(shè)計(jì)實(shí)驗(yàn)教程 第4版 課件 第3、4章 數(shù)據(jù)庫定義與更新、數(shù)據(jù)庫安全性與完整性

第1頁第3章數(shù)據(jù)庫定義與更新數(shù)據(jù)庫系統(tǒng)原理實(shí)驗(yàn)教程第4版3.1相關(guān)知識3.1.1數(shù)據(jù)庫定義語句3.1.2表定義語句3.1.3索引與視圖定義語句3.1.4表記錄更新語句3.2實(shí)驗(yàn)七數(shù)據(jù)庫與數(shù)據(jù)表定義3.2.1實(shí)驗(yàn)?zāi)康呐c要求3.2.2實(shí)驗(yàn)案例3.2.3實(shí)驗(yàn)內(nèi)容3.3實(shí)驗(yàn)八索引與視圖定義3.3.1實(shí)驗(yàn)?zāi)康呐c要求3.3.2實(shí)驗(yàn)案例3.3.3實(shí)驗(yàn)內(nèi)容3.4實(shí)驗(yàn)九數(shù)據(jù)更新操作3.4.1實(shí)驗(yàn)?zāi)康呐c要求3.4.2實(shí)驗(yàn)案例3.4.3實(shí)驗(yàn)內(nèi)容目錄第3頁

3.1相關(guān)知識在SQLServer2019中，數(shù)據(jù)庫對象包括表、視圖、觸發(fā)器、存儲(chǔ)過程、規(guī)則、缺省、用戶自定義的數(shù)據(jù)類型等。SQLServer的DDL是指用來定義和管理數(shù)據(jù)庫以及數(shù)據(jù)庫中的各種對象的語句，這些語句包括CREATE、ALTER和DROP等語句。SQLServer的DML是指用來查詢、添加、修改和刪除數(shù)據(jù)庫中數(shù)據(jù)的語句，包括SELECT、INSERT、UPDATE、DELETE等。第4頁3.1.1數(shù)據(jù)庫定義語句1.創(chuàng)建數(shù)據(jù)庫語法：CREATEDATABASEdatabase_name

[ON[PRIMARY]]

([NAME=logical_file_name,]

FILENAME='os_file_name'

[,SIZE=size]

[,MAXSIZE={max_size|UNLIMITED}]

[,FILEGROWTH=growth_increment])[,...n])

[LOGON]([NAME=logical_file_name,]

FILENAME='os_file_name'

[,SIZE=size]

[,MAXSIZE={max_size|UNLIMITED}]

[,FILEGROWTH=growth_increment])[,...n])第5頁其中：?database_name：被創(chuàng)建的數(shù)據(jù)庫的名字。?ON：用于指定存儲(chǔ)數(shù)據(jù)庫中數(shù)據(jù)的磁盤文件，除PRIMARY文件組外，用戶可定義用戶的文件組及相關(guān)的用戶文件。?PRIMARY：描述在主文件組中定義的相關(guān)文件，所有的數(shù)據(jù)庫系統(tǒng)表存放在PRIMARY文件組中，同時(shí)也存放沒有分配具體文件組的對象。在主文件組中第一個(gè)文件被稱為主文件，通常包括數(shù)據(jù)庫的系統(tǒng)表。對于一個(gè)數(shù)據(jù)庫來說，只能有一個(gè)PRIMARY文件組。如果主文件組沒有指明，則創(chuàng)建數(shù)據(jù)庫時(shí)所描述的第一個(gè)文件將作為主文件組成員。?LOG

ON：用來指明存儲(chǔ)數(shù)據(jù)庫日志的磁盤文件。如果沒有指定logon，系統(tǒng)將自動(dòng)創(chuàng)建單個(gè)的日志文件，使用系統(tǒng)默認(rèn)的命名方法。第6頁2.刪除數(shù)據(jù)庫語法：DROPDATABASEdatabase_name刪除數(shù)據(jù)庫將刪除數(shù)據(jù)庫所使用的數(shù)據(jù)庫文件和磁盤文件。第7頁3.1.2表定義語句1.創(chuàng)建表語法：CREATETABLE<tableName>(<columnName1><dataType>[default<defaultValue>][NULL|NOTNULL][,

<columnName2><dataType>[default<defaultValue>][NULL|NOTNULL]…][,[CONSTRAINT<constraintName1>]{UNIQUE|PRIMARYKEY}(<columName1>[,<columName2>…])[,…n]][,[CONSTRAINT<constraintName2>]FOREIGNKEY(<columName1>[,<columName2>…])REFERENCE[<dbName>.owner.]<refTable>(<refColumn1>[,<refColumn2>…])[,…n]])[ON<filegroupName>]第8頁其中：?table_name：新表的名稱，表名必須符合標(biāo)識符規(guī)則。?column_name：表中的列名，列名必須符合標(biāo)識符規(guī)則，并且在表內(nèi)唯一。?datetype：列的數(shù)據(jù)類型。?default<defaultValue>：為列設(shè)置缺省值，屬于可選項(xiàng)；?NULL|NOTNULL：為列設(shè)置是否允許為空值，屬于可選項(xiàng)；?<constraintName>：定義約束的名字，屬于可選項(xiàng)；?UNIQUE：建立唯一索引；?PRIMARYKEY：建立主碼；?FOREIGNKEY：建立外碼；?ONfilegroupName：指定該表屬于哪個(gè)文件組。第9頁2.刪除表語法：DROPTABLEtable_name第10頁3.1.3索引與視圖定義語句1.創(chuàng)建視圖語句的語法：CREATEVIEW[<database_name>.][<owner>.]view_name[(column[,...n])]

[WITH<view_attribute>[,...n]]AS

select_statement

[WITHCHECKOPTION]

<view_attribute>::={encryption|schemabinding|view_metadata}第11頁3.1.3索引與視圖定義語句其中：?view_name：視圖的名稱，視圖名稱必須符合標(biāo)識符規(guī)則。?column：視圖中的列名。當(dāng)列是從算術(shù)表達(dá)式、函數(shù)或常量派生的，或兩個(gè)或更多的列可能會(huì)具有相同的名稱(如聯(lián)接），或視圖中的某列被賦予了不同于派生來源列的名稱時(shí)必須指定列名。如果未指定column，則視圖列將獲得與SELECT語句中的列相同的名稱。?n：表示可以指定多列的占位符。?select_statement：定義視圖的SELECT語句。?WITHCHECKOPTION：表示當(dāng)對視圖進(jìn)行更新操作時(shí)必須滿足視圖定義的謂詞條件。第12頁在創(chuàng)建視圖前需考慮如下原則：(1)只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建視圖。(2)視圖名稱必須遵循標(biāo)識符的規(guī)則，且對每個(gè)用戶必須唯一，該名稱不得與該用戶擁有的任何表的名稱相同。(3)可以在其它視圖上建立視圖。(4)不能將規(guī)則或DEFAULT定義與視圖相關(guān)聯(lián)。(5)定義視圖的查詢不可以包含ORDERBY、COMPUTE或COMPUTEBY子句或INTO關(guān)鍵字。(6)不能在視圖上定義全文索引。(7)不能創(chuàng)建臨時(shí)視圖，也不能在臨時(shí)表上創(chuàng)建視圖。(8)下列情況下必須在視圖中指定每列的名稱：①視圖中有任何從算術(shù)表達(dá)式、內(nèi)置函數(shù)或常量派生出的列。②視圖中兩列或多列具有相同名稱。③希望使視圖中的列名與它的源列名不同，可在視圖中重新命名列。無論重命名與否，視圖列都會(huì)繼承其源列的數(shù)據(jù)類型。第13頁2.刪除視圖如果不需要某視圖，可以刪除該視圖。刪除視圖后，視圖所基于的數(shù)據(jù)并不受到影響。語法：DROPVIEWview_name

[,...n]第14頁3.創(chuàng)建索引當(dāng)為表建立主鍵和唯一約束時(shí)，SQLServer自動(dòng)創(chuàng)建唯一索引。如果表中不存在聚集索引，則為主鍵創(chuàng)建一個(gè)唯一的聚集索引。默認(rèn)情況下對UNIQUE約束創(chuàng)建唯一的非聚集索引。創(chuàng)建索引語句的語法：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{TABLE|VIEW}(column[ASC|DESC][,...n])

[ONfilegroup]第15頁其中：?UNIQUE：為表或視圖創(chuàng)建唯一索引，聚集索引必須是UNIQUE索引。?CLUSTERED：創(chuàng)建聚集索引，如果沒有指定CLUSTERED，則創(chuàng)建非聚集索引。?NONCLUSTERED：創(chuàng)建非聚集索引。?index_name：索引名，索引名必須遵循標(biāo)識符規(guī)則。?TABLE：要?jiǎng)?chuàng)建索引的表。?VIEW：要建立索引的視圖的名稱。?column：應(yīng)用索引的列。?ONfilegrou：在給定的filegroup上創(chuàng)建指定的索引。該文件組必須已經(jīng)通過執(zhí)行CREATEDATABASE或ALTERDATABASE創(chuàng)建。第16頁4.刪除索引語法：DROPINDEXindex[,...n]ON<tableName|viewName>

或者：DROPINDEXtableName.index|viewName.index[,...n]其中：

?tableName.index|viewName.index：要?jiǎng)h除的表或視圖的索引名稱。

?n：表示可以指定多個(gè)索引的占位符。

?ON<tableName|viewName>：指定表名或視圖名。第17頁3.1.4插入、刪除、修改記錄語句DML語句包括查詢、添加、修改和刪除數(shù)據(jù)庫中的數(shù)據(jù)等操縱語句，即SELECT、INSERT、UPDATE、DELETE等。本小節(jié)主要討論數(shù)據(jù)庫對象的INSERT、UPDATE、DELETE功能。1.插入數(shù)據(jù)語法：INSERT[INTO]table_name/view_name[(column_list)]VALUES{DEFAULT|NULL|expression}其中：?table_name/view_name：表名/視圖名?column_list：由逗號分隔的列名列表，用來指定為其提供數(shù)據(jù)的列。如果沒有指定column_list，表中的所有列都將接收數(shù)據(jù)。沒有包含在column_list的列，將在該列插入一個(gè)NULL值(或者該列定義的默認(rèn)值)。第18頁2.修改數(shù)據(jù)語法：UPDATEtable_name/view_name

SETcolumn_name=expression|DEFAULT|NULL[FROM<table_source>[,...n]][WHERE<search_condition>]其中：?table_name/view_name：需要更新的表/視圖的名稱。?column_name：要更改數(shù)據(jù)的列名。?expression：返回的值將替換column_name的現(xiàn)有值。?DEFAULT：指定使用對列定義的默認(rèn)值替換列中的現(xiàn)有值。?FROM<table_source>：指定用表來為更新操作提供準(zhǔn)則。?WHERE<search_condition>：指定條件來限定所更新的行。第19頁3.刪除數(shù)據(jù)語法：DELETEFROM<table_name/view_name>[WHERE<search_condition>]其中：?table_name/view_name：要?jiǎng)h除記錄的表名/視圖名。?WHERE<search_condition>：指出被刪除的記錄所滿足的條件，若省略，表示刪除表中的所有記錄。

第20頁3.2實(shí)驗(yàn)七數(shù)據(jù)庫與數(shù)據(jù)表定義3.2.1實(shí)驗(yàn)?zāi)康呐c要求

(1)掌握數(shù)據(jù)庫的建立、刪除和修改操作。

(2)理解基本表之間的關(guān)系，掌握表結(jié)構(gòu)的建立、修改和刪除操作，創(chuàng)建模式導(dǎo)航圖。第21頁3.2.2實(shí)驗(yàn)案例1.數(shù)據(jù)庫創(chuàng)建與刪除[例3.1]創(chuàng)建一個(gè)myorder數(shù)據(jù)庫，該數(shù)據(jù)庫的主要文件為myorder.mdb,事務(wù)日志為myorder.ldf，它們都位于e:\mySQL目錄下。CREATEDATABASEmyorderON(NAME='myorder',FILENAME='e:\mySQL\myorder.mdf',SIZE=3,MAXSIZE=50,FILEGROWTH=1)LOGON(NAME='myorderLog',FILENAME='e:\mySQL\myorderLog.ldf',SIZE=3,MAXSIZE=20,FILEGROWTH=1)本例中：myorder數(shù)據(jù)庫，只有一個(gè)主邏輯設(shè)備，對應(yīng)一個(gè)物理文件myorder.mdf，該文件初始大小3M，最大可擴(kuò)展為50M；如果初始文件裝不下數(shù)據(jù)，自動(dòng)按1M進(jìn)行擴(kuò)展，直到50M為止。日志文件為myorderLog.ldf，文件初始大小3M，最大可擴(kuò)展為20M；如果初始文件裝不下數(shù)據(jù)，自動(dòng)按1M進(jìn)行擴(kuò)展。第22頁[例3.3]刪除數(shù)據(jù)庫tmyorder。DROPDATABASEtmyorder第23頁2.創(chuàng)建表[例3.4]創(chuàng)建一個(gè)客戶表(客戶編號、客戶姓名、客戶電話、客戶地址、郵政編碼)。CREATETABLECustomer(customerNochar(9)NOTNULLPRIMARYKEY,/*客戶編號*/

customerNamevarchar(40)NOTNULL,/*客戶名稱*/

telephonevarchar(20)NOTNULL,/*客戶電話*/

addresschar(40)NOTNULL,/*客戶住址*/

zipchar(6)NULL/*郵政編碼*/)第24頁[例3.6]建立一張訂單表(訂單編號、客戶編號、業(yè)務(wù)員編號、訂貨日期、訂單金額、發(fā)票號碼)，要求給該表建立主鍵約束和關(guān)于員工表和客戶表的外鍵約束。CREATETABLEOrderMaster(orderNochar(12)NOTNULLPRIMARYKEY,/*訂單編號*/

customerNochar(9)NOTNULL,/*客戶編號*/

salerNochar(8)NOTNULL,/*業(yè)務(wù)員編號*/

orderDatedatetimeNOTNULL,/*訂貨日期*/

orderSumnumeric(9,2)NOTNULL,/*訂單金額*/

invoiceNochar(10)NOTNULL,/*發(fā)票號碼*/

CONSTRAINTOrdermasterFK1FOREIGNKEY(customerNo)REFERENCESCustomer(customerNo),CONSTRAINTOrdermasterFK2FOREIGNKEY(salerNo)REFERENCESEmployee(employeeNo))第25頁3.2.3實(shí)驗(yàn)內(nèi)容(1)創(chuàng)建一個(gè)BookDB數(shù)據(jù)庫，要求至少一個(gè)數(shù)據(jù)文件和一個(gè)日志文件。(2)創(chuàng)建圖書借閱管理相關(guān)5張關(guān)系表，表結(jié)構(gòu)如圖3-1至圖3-5所示。第26頁第27頁第28頁第29頁3.3實(shí)驗(yàn)八索引與視圖定義3.3.1實(shí)驗(yàn)?zāi)康呐c要求(1)掌握索引的建立和刪除操作。(2)掌握視圖的創(chuàng)建和查詢操作。第30頁3.3.2實(shí)驗(yàn)案例1.創(chuàng)建索引[例3.7]在員工表中按生日建立一個(gè)非聚簇索引birthdayIdx。CREATENONCLUSTEREDINDEXbirthdayIdxONEmployee(birthday)[例3.8]在訂單主表中，首先按訂單金額的降序，然后按客戶編號的升序建立一個(gè)非聚簇索引sumcustIdx。CREATEINDEXsumcustidxONOrderMaster(orderSumDESC,customerNo)[例3.9]在訂單主表中按發(fā)票號碼創(chuàng)建一個(gè)唯一性索引uniqincoiceIdx。CREATEUNIQUEINDEXuniqincoiceIdxONOrderMaster(invoiceno)[例3.10]刪除birthdayIdx索引DROPINDEXbirthdayIdxONEmployee第31頁2.定義視圖[例3.11]建立一個(gè)女員工的視圖，要求顯示員工編號、姓名、性別和薪水。CREATEVIEWemp_viewASSELECTemployeeNo,employeeName,sex,salaryFROMEmployeeWHEREsex='f'第32頁[例3.13]修改emp_view視圖，要求視圖只顯示薪水3000元以上的女員工信息。ALTERVIEWemp_viewASSELECTemployeeNo,employeeName,sex,salaryFROMEmployeeWHEREsex='f'ANDsalary>3000[例3.14]刪除視圖emp_viewDROPVIEWemp_view第33頁3.3.3實(shí)驗(yàn)內(nèi)容(1)根據(jù)基本表創(chuàng)建以下索引：①在圖書表中按出版社號建立一個(gè)非聚集索引PublishingnoIdx。②在讀者表中按身份證號建立一個(gè)非聚集索引IdentifycardIdx。③在讀者表中，首先按工作單位的升序，然后按最大借書數(shù)量降序建立一個(gè)非聚集索引WorkunitCountIdx。(2)創(chuàng)建一個(gè)圖書名稱中含有“數(shù)據(jù)”的圖書視圖BookView。(3)創(chuàng)建一個(gè)包含讀者編號、讀者姓名、圖書編號、圖書名稱、借閱日期、歸還日期的視圖BorrowView。(4)創(chuàng)建一個(gè)視圖，要求顯示至少借閱了3本書的讀者信息ReaderView。(5)在視圖BorrowView中查詢2016年3月1日以前借閱的圖書。(6)在視圖ReaderView中查詢姓張的讀者信息。(7)在視圖BorrowView基礎(chǔ)上再建一個(gè)只包含“合生元有限公司”的讀者所借圖書信息的視圖BorrowView1。(8)刪除視圖BorrowView。第34頁3.4實(shí)驗(yàn)九數(shù)據(jù)更新操作3.4.1實(shí)驗(yàn)?zāi)康呐c要求

(1)掌握基本表的INSERT、UPDATE、DELETE操作。

(2)正確理解更新操作中涉及到的相關(guān)約束問題。第35頁3.4.2實(shí)驗(yàn)案例[例3.15]在客戶表中插入一條信息(C20220004,雙良股份有限公司江陰市,220012)。INSERTCustomerVALUES('C20220004','雙良股份有限公司',,'江陰市','220012')[例3.16]刪除1987年以前出生的員工記錄。DELETEFROMEmployeeWHEREyear(Birthday)<1987[例3.17]刪除E2020002業(yè)務(wù)員的訂單明細(xì)信息。DELETEFROMOrderDetailWHEREorderNoIN(SELECTorderNoFROMOrderMasterWHEREsalerNo='E2020002')第36頁[例3.18]將客戶表中C20220003客戶的客戶名稱改為西湖商廈，電話改UPDATECustomerSETcustomerName='西湖商廈',Telephone=WHEREcustomerNo=‘C20220003’[例3.19]在OrderMaster表中找出E2020003業(yè)務(wù)員的訂單，將這些訂單對應(yīng)的每一項(xiàng)銷售商品的單價(jià)打8折。UPDATEOrderDetailSETprice=price*0.8WHEREorderNoIN(SELECTorderNoFROMOrderMasterWHEREsalerNo='E2020003')第37頁3.4.3實(shí)驗(yàn)內(nèi)容根據(jù)BookDB中5張關(guān)系表，完成以下更新操作：(1)分別給這5張表添加信息，要求圖書分類表、圖書表、出版社表、讀者表各插入5個(gè)元組，借閱表插入20個(gè)元組。(2)將“合生元有限公司”的讀者工作單位修改為聯(lián)合立華股份有限公司。(3)將入庫數(shù)量最多的圖書單價(jià)下調(diào)5%。(4)將“經(jīng)濟(jì)類”的圖書單價(jià)提高10%。(5)將借閱次數(shù)高于2次的圖書數(shù)量增加50%。(6)將“興隆股份有限公司”讀者的借書期限延長至3個(gè)月。(7)根據(jù)借閱表修改每個(gè)讀者的借書數(shù)量。(8)刪除價(jià)格超過30元的圖書借閱信息。(9)刪除借閱了大學(xué)英語的借閱記錄。(10)刪除從未借過書的讀者。第38頁第4章數(shù)據(jù)庫安全性與完整性數(shù)據(jù)庫系統(tǒng)原理實(shí)驗(yàn)教程第4版4.1相關(guān)知識4.1.1數(shù)據(jù)庫安全性4.1.2數(shù)據(jù)庫完整性4.2實(shí)驗(yàn)十安全性定義與檢查4.2.1實(shí)驗(yàn)?zāi)康呐c要求4.2.2實(shí)驗(yàn)案例4.2.3實(shí)驗(yàn)內(nèi)容4.3實(shí)驗(yàn)十一完整性定義與檢查4.3.1實(shí)驗(yàn)?zāi)康呐c要求4.3.2實(shí)驗(yàn)案例4.3.3實(shí)驗(yàn)內(nèi)容目錄第40頁數(shù)據(jù)庫的安全性是指保護(hù)數(shù)據(jù)庫以防止不合法的使用所造成的數(shù)據(jù)泄密、更改或破壞。數(shù)據(jù)庫的完整性約束是指數(shù)據(jù)的正確性與相容性，是為了防止數(shù)據(jù)庫存在不符合語義的數(shù)據(jù)第41頁4.1相關(guān)知識4.1.1數(shù)據(jù)庫安全性1.登錄賬號的管理登錄是通過帳號和口令訪問SQLServer的數(shù)據(jù)庫。登錄SQLServer服務(wù)器時(shí)，SQLServer有三個(gè)默認(rèn)的用戶登錄帳號：sa、builtin\administrators和域名\administrator。(1)sa：SQLServer系統(tǒng)管理員登錄賬號，該賬號擁有最高管理權(quán)限，可以執(zhí)行服務(wù)器范圍內(nèi)的所有權(quán)限。(2)builtin\administrators：一個(gè)Windows組賬號，凡屬于該組的用戶賬號都可以作為SQLServer登錄賬號使用。(3)域名\administrator：一個(gè)Windows用戶賬號，允許作為SQLServer登錄賬號使用。第42頁2.數(shù)據(jù)庫用戶的管理數(shù)據(jù)庫用戶有：

(1)dbo用戶：數(shù)據(jù)庫擁有者或數(shù)據(jù)庫創(chuàng)建者，dbo在其所擁有的數(shù)據(jù)庫中擁有所有的操作權(quán)限。dbo的身份可被重新分配給另一個(gè)用戶，系統(tǒng)管理員sa可作為其所管理的任何數(shù)據(jù)庫的dbo用戶。(2)guest用戶：如果guest用戶在數(shù)據(jù)庫存在，則允許任意一個(gè)登錄用戶作為guest用戶訪問數(shù)據(jù)庫，其中包括那些不是數(shù)據(jù)庫用戶的SQL服務(wù)器用戶。除系統(tǒng)數(shù)據(jù)庫master和臨時(shí)數(shù)據(jù)庫tempdb的guest用戶不能被刪除外，其他數(shù)據(jù)庫都可以將自己guest用戶刪除，以防止非數(shù)據(jù)庫用戶的登錄用戶對數(shù)據(jù)庫進(jìn)行訪問。

(3)新建的數(shù)據(jù)庫用戶：用戶根據(jù)實(shí)際需要?jiǎng)?chuàng)建不同權(quán)限的數(shù)據(jù)庫用戶。第43頁3.數(shù)據(jù)庫角色的管理用戶自定義的數(shù)據(jù)庫角色有兩種類型：即標(biāo)準(zhǔn)角色和應(yīng)用程序角色。①標(biāo)準(zhǔn)角色通過對用戶權(quán)限等級的認(rèn)定而將用戶劃分為不用的用戶組，用戶屬于一個(gè)或多個(gè)角色，從而實(shí)現(xiàn)管理的安全性。②應(yīng)用程序角色是一種比較特殊的角色。當(dāng)我們打算讓某些用戶只能通過特定的應(yīng)用程序間接地存取數(shù)據(jù)庫中的數(shù)據(jù)而不是直接地存取數(shù)據(jù)庫數(shù)據(jù)時(shí)，就應(yīng)該考慮使用應(yīng)用程序角色。當(dāng)某一用戶使用了應(yīng)用程序角色時(shí)，他便放棄了已被賦予的所有數(shù)據(jù)庫專有權(quán)限，他所擁有的只是應(yīng)用程序角色被設(shè)置的角色。第44頁4.SQLServer的權(quán)限管理SQLServer權(quán)限分為三類：對象權(quán)限、語句權(quán)限、隱含權(quán)限。(1)對象權(quán)限對象權(quán)限是指用戶是否允許對數(shù)據(jù)庫中的表、視圖、存儲(chǔ)過程等對象的操作權(quán)限，其具體內(nèi)容如圖4-1所示。

第45頁(2)語句權(quán)限語句權(quán)限相當(dāng)于數(shù)據(jù)庫定義語言的語句權(quán)限，具體內(nèi)容如圖4-2所示。第46頁(3)隱含權(quán)限隱含權(quán)限是指由SQLServer預(yù)定義的服務(wù)器角色、數(shù)據(jù)庫所有者dbo、數(shù)據(jù)庫對象所有者所擁有的權(quán)限，它相當(dāng)于內(nèi)置權(quán)限，不需要明確地授予這些權(quán)限。權(quán)限管理的內(nèi)容有3方面：①授予權(quán)限。即允許某個(gè)用戶或角色對一個(gè)對象執(zhí)行某種操作或語句。②拒絕權(quán)限。即拒絕某個(gè)用戶或角色訪問某個(gè)對象，即使某個(gè)用戶或角色被授予這種權(quán)限，仍然不允許執(zhí)行相應(yīng)的操作。③取消權(quán)限。即不允許某個(gè)用戶或角色對一個(gè)對象執(zhí)行某種操作或某種語句。不允許和拒絕是不同的，不允許還可以通過加入角色來獲得允許，而拒絕是無法通過角色來獲得允許的。三種權(quán)限沖突時(shí)，拒絕權(quán)限起作用。第47頁4.1.2數(shù)據(jù)庫完整性數(shù)據(jù)庫的完整性主要包括：實(shí)體完整性、參照完整性、用戶自定義完整性。實(shí)體完整性要求基本表的主鍵值唯一且不允許為空值；參照完整性為若干個(gè)表中的相應(yīng)元組建立聯(lián)系；用戶自定義完整性就是針對某一具體應(yīng)用的數(shù)據(jù)必須滿足的語義要求，由RDBMS提供而不必由應(yīng)用程序承擔(dān)。第48頁1.SQLServer數(shù)據(jù)完整性分類SQLServer的數(shù)據(jù)完整性可分為3類，如表4-1所示。第49頁(1)實(shí)體完整性。實(shí)體完整性為表級完整性，它要求表中所有的元組都應(yīng)該有一個(gè)惟一的標(biāo)識符，這個(gè)標(biāo)識符就是平常所說的主碼。(2)參照完整性。參照完整性是表級完整性，它維護(hù)參照表中的外碼與被參照表中主碼的相容關(guān)系。如果在被參照表中某一元組被外碼參照，那么這一行既不能被刪除，也不能更改其主碼。(3)用戶自定義完整性。用戶自定義完整性為列級和元組級完整性。它為列或列組指定一個(gè)有效的數(shù)據(jù)集，并確定該列是否允許為空。第50頁4.2實(shí)驗(yàn)十安全性定義與檢查4.2.1實(shí)驗(yàn)?zāi)康呐c要求掌握登錄賬號的創(chuàng)建、修改、刪除和禁止操作。掌握數(shù)據(jù)庫用戶的添加和刪除操作。掌握數(shù)據(jù)庫角色的創(chuàng)建、刪除；數(shù)據(jù)庫角色成員的添加和刪除。掌握權(quán)限管理中語句權(quán)限和對象權(quán)限的管理。掌握數(shù)據(jù)庫是如何進(jìn)行身份檢查和權(quán)限檢查的。熟練運(yùn)用數(shù)據(jù)庫的安全機(jī)制操作數(shù)據(jù)庫。第51頁4.2.2實(shí)驗(yàn)案例在SQLServe中，登錄賬號、數(shù)據(jù)庫用戶、數(shù)據(jù)庫角色以及權(quán)限的管理都可以通過企業(yè)管理器來完成，前面已經(jīng)講述。下面使用T-SQL語句來實(shí)現(xiàn)登錄賬號、數(shù)據(jù)庫用戶、數(shù)據(jù)庫角色以及權(quán)限的管理功能。1.登錄賬戶管理(1)創(chuàng)建登錄語法為：[EXECUTE]sp_addlogin[@loginame=]'login'[,[@passwd=]'password'][,[@defdb=]'database']第52頁其中：·@loginame='login'：登錄名稱?！passw='password'：登錄密碼，若不指定則默認(rèn)為NULL。·@defdb='database'：登錄后用戶訪問的數(shù)據(jù)庫，若不指定則默認(rèn)為master數(shù)據(jù)庫。在sp_addlogin中，除了登陸名稱之外，其余選項(xiàng)均為可選項(xiàng)。執(zhí)行sp_addlogin時(shí)，必須具有相應(yīng)的權(quán)限。只有sysadmin和securityadmin固定服務(wù)器角色的成員才能執(zhí)行該命令。

第53頁[例4.1]創(chuàng)建用戶，要求如下：創(chuàng)建用戶為victoria，密碼為’p888888’的登錄賬號。創(chuàng)建用戶為u1，密碼為’p888888’的登錄賬號。創(chuàng)建用戶為u2，密碼為’p888888’的登錄賬號。sp_addlogin

‘victoria’,‘p888888'sp_addlogin

‘u1’,‘p888888'sp_addlogin

‘u2’,‘p888888’[例4.2]創(chuàng)建登錄賬號liu，密碼為liusjj999，默認(rèn)的數(shù)據(jù)庫為orderdb。sp_addlogin'liu','liusjj999','OrderDB'

第54頁(2)修改登錄賬號屬性修改登錄賬號的命令有：修改登錄密碼、修改默認(rèn)的數(shù)據(jù)庫和刪除賬號。修改登錄密碼語法：sp_password[[@old=]'old_password',]

{[@new=]'new_password'}

[,[@loginame=]'login']第55頁[例4.3]將liu的密碼修改為’p888888’。sp_password'liusjj999','p888888','liu'本例中，'liu'是登錄賬號名稱，'liusjj999'為'liu'原來的密碼，’p888888’是新密碼。（3）修改默認(rèn)的數(shù)據(jù)庫語法：sp_defaultdb[@loginame=]'login',[@defdb=]'database'[例4.4]將liu訪問的數(shù)據(jù)庫修改為’ScoreDB’。sp_defaultdb'liu','ScoreDB'

第56頁（4）刪除登錄賬號語法：sp_droplogin@loginame='login‘[例4.5]刪除登錄賬號victoria。sp_droplogin'victoria'執(zhí)行上述語句后，'victoria'從登錄用戶中被刪除。第57頁2.用戶管理(1)添加用戶語法：sp_adduser

[@loginame=]'login'[,[@name_in_db=]'user']'其中，'login'是指登錄賬號名稱，'user'是指數(shù)據(jù)庫用戶名稱。[例4.6]為登錄賬號u1添加到OrderDB數(shù)據(jù)庫中，且用戶名為u1。sp_adduseru1,u1[例4.7]為登錄賬號u2添加到OrderDB數(shù)據(jù)庫中，且用戶名為uu2。sp_adduseru2,uu2

第58頁(2)刪除用戶語法：sp_dropuser[@name_in_db=]'user‘[例4.8]從當(dāng)前數(shù)據(jù)庫中刪除賬號u1。sp_dropuseru1

第59頁3.角色管理(1)創(chuàng)建數(shù)據(jù)庫角色語法：sp_addrole[@rolename=]'role'其中：‘role’指數(shù)據(jù)庫角色名稱，以下同義。只有固定服務(wù)器角色sysadmin、db_securityadmin及db_owner的成員才能執(zhí)行該系統(tǒng)存儲(chǔ)過程。[例4.9]建立角色r1和r2。sp_addrole'r1'sp_addrole'r2'

第60頁(2)刪除數(shù)據(jù)庫角色語法：sp_droprole[@rolename=]'role‘[例4.10]刪除數(shù)據(jù)庫角色r2。sp_droprole'r2'

第61頁(3)增加數(shù)據(jù)庫角色成員語法：sp_addrolemember[@rolename=]'role',[@membername=]'security_account'其中：[@rolename=]'role'：當(dāng)前數(shù)據(jù)庫中的數(shù)據(jù)庫角色的名稱。[@membername=]'security_account'：security_account

可以是數(shù)據(jù)庫用戶、數(shù)據(jù)庫角色、Windows登錄或Windows組。只有固定服務(wù)器角色sysadmin及db_owner的成員才能執(zhí)行該系統(tǒng)存儲(chǔ)過程。

第62頁(4)刪除數(shù)據(jù)庫角色成員語法：sp_droprolemember

[@rolename=]'role',[@membername=]'security_account'只有固定服務(wù)器角色sysadmin及db_owner的成員才能執(zhí)行該系統(tǒng)存儲(chǔ)過程。[例4.12]在數(shù)據(jù)庫角色r1中刪除用戶uu2。sp_droprolememberr1,uu2第63頁4.權(quán)限管理(1)管理語句權(quán)限的語法：GRANT/DENY{ALL|command_list}TO{PUBLIC|name_list}REVOKE{ALL|command_list}FROM{PUBLIC|name_list}其中：·command_list：是CREATEDATABASE、CREATEDEFAULT、CREATEFUNCTION、CREATEPROCEDURE、CREATERULE、CREATETABLE、CREATEVIEW、BACKUPDATABASE、BACKUPLOG。·PUBLIC：表示所有的用戶?！LL：表示上述所有權(quán)限?！ame_list：用戶名稱,可以將某組權(quán)限同時(shí)授予多個(gè)用戶，用戶名之間用逗號分割。語義：將對指定操作對象的指定操作權(quán)限授予指定的用戶。第64頁(2)管理對象權(quán)限的語法：GRANT/DENY{ALL|command_list}ON<table_name>[<col_name>,…]TO{PUBLIC|name_list}[WITHGRANTOPTION]REVOKE{ALL|command_list}ON<table_name>[<col_name>,…]FROM{PUBLIC|name_list}第65頁其中：·command_list：包括UPDATE、SELECT、INSERT、DELETE、EXCUTE、ALL?！able_name：數(shù)據(jù)庫對象名?！UBLIC：表示所有的用戶?！LL：表示上述所有權(quán)限?！ITHGRANTOPTION：將指定的對象權(quán)限授予其它安全賬號的能力。注意：當(dāng)對列授予權(quán)限時(shí)，命令項(xiàng)可以包括SELECT和UPDATE或兩者的組合，而在SELECT中若使用了SELECT*則必須對表的所有列賦予SELECT權(quán)限。第66頁[例4.13]分別創(chuàng)建登錄賬號u3，u4，u5，u6，其密碼皆為p888888，并設(shè)置為訂單數(shù)據(jù)庫的用戶。sp_addloginu3,p888888sp_addloginu4,p888888sp_addloginu5,p888888sp_addloginu6,p888888USEOrderDB//打開訂單數(shù)據(jù)庫sp_adduseru3,u3sp_adduseru4,u4sp_adduseru5,u5sp_adduseru6,u6第67頁[例4.14]把查詢Customer表權(quán)限授給用uu2。GRANTSELECTONCustomerTOuu2[例4.15]給u3授予建表和建視圖的權(quán)限。GRANTCREATETABLE,CREATEVIEWTOu3[例4.16]把對Customer表和Employee表的全部權(quán)限授予用戶u3和u4，并具有轉(zhuǎn)授權(quán)限。GRANTALLprivilegesONCustomerTOu3,u4WITHGRANTOPTIONGRANTALLprivilegesONEmployeTOu3,u4WITHGRANTOPTION[例4.17]把用戶u4修改客戶編號的權(quán)限收回。REVOKEUPDATE(customerNo)ONCustomerFROMu4CASCADE第68頁[例4.18]通過角色來實(shí)現(xiàn)將一組權(quán)限授予一個(gè)用戶。步驟如下：①創(chuàng)建角色jw。

sp_addrole

jw②給角色jw授予Employee表的SELECT、UPDATE、INSERT權(quán)限。GRANTSELECT,UPDATE,INSERTONEmployeeTOjw③將這個(gè)角色授予用戶u5、u6。

sp_addrolememberjw,u5

sp_addrolememberjw,u6④收回u5的所有權(quán)限。

sp_droprolememberjw,u5⑤修改角色jw權(quán)限，增加其刪除DELETE權(quán)限。GRANTDELETEONEmployeeTOjw第69頁5.安全性檢查[例4.19]用戶user04需要在訂單數(shù)據(jù)庫中創(chuàng)建一個(gè)視圖ProductView（查詢每種產(chǎn)品的訂購數(shù)量、訂購平均價(jià)），并將該視圖的查詢權(quán)限授予用戶user05和user06，請完成該操作。首先在master數(shù)據(jù)庫中創(chuàng)建這三個(gè)用戶，并設(shè)置為訂單數(shù)據(jù)庫的用戶。

sp_addloginuser04,p888888

sp_addloginuser05,p888888

sp_addloginuser06,p888888在OrderDB數(shù)據(jù)庫中加入這三個(gè)用戶。

sp_adduseruser04,user04

sp_adduseruser05,user05

sp_adduseruser06,user06第70頁授予user04創(chuàng)建視圖的權(quán)限。grantcreateviewtouser04以user04用戶身份登錄，創(chuàng)建視圖。createviewProductView

asselectproductNo,sum(quantity)qty,avg(quantity*price)/sum(quantity)avgPricefromOrderDetailgroupbyproductNo將ProductView視圖的權(quán)限授予用戶user05和user06。grantselectonProductViewtouser05,user06第71頁[例4.20]用戶user05查詢視圖ProductView。首先以user05身份登錄，然后查詢ProductView視圖。select*fromProductView第72頁4.2.3實(shí)驗(yàn)內(nèi)容1、使用訂單數(shù)據(jù)庫OrderDB完成下面的實(shí)驗(yàn)內(nèi)容：(1)分別創(chuàng)建登錄賬號user01、user02，其密碼皆為p888888，并設(shè)置為訂單數(shù)據(jù)庫OrderDB的用戶。(2)創(chuàng)建登錄賬號login03，并加入到OrderDB數(shù)據(jù)庫中，其用戶名為user03。(3)將員工表的所有權(quán)限授予全部用戶。(4)授予user03用戶對Product表的查詢權(quán)限，對Employee表的編號、名稱的查詢和更新權(quán)限。(5)創(chuàng)建角色r3、r4，將訂單明細(xì)表所有列的SELECT權(quán)限、PRICE列的UPDATE權(quán)限授予r3。(6)收回全部用戶對員工表的所有權(quán)限。(7)將user01、user02兩個(gè)用戶賦予r3角色。(8)收回user02對訂單明細(xì)表所有列的SELECT權(quán)限。

(9)在當(dāng)前數(shù)據(jù)庫中刪除角色r4。(10)授予user01建表和建視圖的權(quán)限，user01用戶分別建立一張表和一個(gè)視圖(表和視圖自定)，然后將該表和視圖的查詢權(quán)限授予user02和user03。第73頁2、使用訂單數(shù)據(jù)庫OrderDB完成下面的實(shí)驗(yàn)，記錄詳細(xì)的操作過程：(1)用戶user07在訂單數(shù)據(jù)庫中創(chuàng)建一張表Table1(內(nèi)容自定)。(2)用戶user02對表Table1和表OrderDetail執(zhí)行了插入和查詢操作(內(nèi)容自定)。(3)用戶user03建立兩張表Table2和Table3和一個(gè)視圖View1(內(nèi)容自定)，然后將該表和視圖的查詢權(quán)限授予user05和user06，并具有轉(zhuǎn)授權(quán)限。(4)在訂單數(shù)據(jù)庫中創(chuàng)建兩個(gè)角色r5和r6，角色r5具有創(chuàng)建表和視圖的權(quán)限，角色r6具有對Customer表的查詢、插入權(quán)限，Employee表的查詢、更新和插入權(quán)限，OrderMaster表的所有權(quán)限。(5)用戶user05將user03用戶創(chuàng)建的表和視圖的查詢權(quán)限授予了用戶user07，user07用戶對表Table2進(jìn)行了插入操作。(6)user07用戶具有角色r5，同時(shí)創(chuàng)建了表Table4(內(nèi)容自定)。第74頁4.3實(shí)驗(yàn)十一

完整性定義與檢查4.3.1實(shí)驗(yàn)?zāi)康呐c要求充分理解關(guān)系數(shù)據(jù)庫中關(guān)于數(shù)據(jù)庫完整性的概念。掌握實(shí)體完整性的定義方法。掌握參照完整性定義的方法。掌握用戶自定義完整性的方法。充分理解關(guān)系數(shù)據(jù)庫中關(guān)于數(shù)據(jù)庫完整性的概念。掌握實(shí)體完整性檢查方法。掌握參照完整性檢查方法。掌握用戶自定義完整性檢查方法。第75頁4.3.2實(shí)驗(yàn)案例創(chuàng)建表及完整性約束的語法：CREATETABLEtable_name(column_name1datetype[DEFAULTdefault_value][NULL/NOTNULL][CHECKsearch_condition],column_name2datetype......

∶[CONSTRAINTconstrain_name1{UNIQUE/PRIMARYKEY}(colum_name[,colum_name…][ONgroupfile_name])],[CONSTRAINTconstrain_name2FOREIGNKEY(column_name1),[column_name2,…]REFERENCESref_table(ref_column1[,ref_column2,…])],...)ONgroupfile_name

)第76頁1.實(shí)體完整性約束實(shí)體完整性通過設(shè)置主鍵PRIMARYKEY來實(shí)現(xiàn)，當(dāng)表的主鍵只有一列時(shí)，可以在創(chuàng)建表時(shí)直接在列后指定PRIMARYKEY，也可以由CONSTRAINT關(guān)鍵字來指定；

當(dāng)表的主鍵多于一列時(shí)，必須使用元組級的定義來指定PRIMARYKEY。[例4.21]創(chuàng)建一個(gè)客戶表，并為該表建立主鍵約束。CREATETABLECustomer(

customerNochar(9),/*客戶編號*/CONSTRAINTCustomerPK

PRIMARYKEY(customerNo),

customerName

varchar(40),/*客戶名稱*/telephonevarchar(20),/*客戶電話*/addresschar(40),/*客戶住址*/zipchar(6)/*郵政編碼*/)本例中，主鍵由CONSTRAINT定義并命名為CustomerPK。第77頁由于該表只有一列作為主鍵，所以還可以用下面的方法來定義，但約束名由系統(tǒng)自動(dòng)定義。CREATETABLECustomer(

customerNochar(9)PRIMARYKEY,/*客戶編號*/

customerName

varchar(40),/*客戶名稱*/telephonevarchar(20),/*客戶電話*/addresschar(40),/*客戶住址*/zipchar(6)/*郵政編碼*/)第78頁[例4.22]創(chuàng)建一訂單明細(xì)表，為該表建立主鍵約束。CREATETABLEOrderDetail(

orderNochar(12),/*訂單編號*/

productNochar(9),/*產(chǎn)品編號*/quantityint,/*銷售數(shù)量*/pricenumeric(7,2),/*訂貨單價(jià)*/

CONSTRAINTorderdetailpkPRIMARYKEY(orderNo,productNo))本例中，主鍵有兩列構(gòu)成，所以必須定義為元組級的約束，本例由CONSTRAINT來定義主鍵，并為該約束命名為OrderDetailPK。第79頁2.參照完整性約束參照完整性指有些表的列(或列的組合)和其他表的主鍵相關(guān)聯(lián)，用戶可以給這個(gè)列(或列的組合)定義為FOREIGNKEY，并以REFERENCES關(guān)鍵字設(shè)置它所關(guān)聯(lián)的表及其列組。[例4.24]建立一張訂單主表，要求給該表建立主鍵約束和關(guān)于員工表和客戶表的外鍵約束。CREATETABLEOrderMaster(

orderNochar(12)PRIMARYKEY,/*訂單編號*/

customerNochar(9),/*客戶號*/

salerNochar(8),/*業(yè)務(wù)員編號*/

orderDate

datetime,/*訂貨日期*/

orderSumnumeric(9,2),/*訂單金額*/

invoiceNochar(10),/*發(fā)票號碼*/

CONSTRAINTOrdermasterFK1FOREIGNKEY(customerNo)REFERENCESCustomer(customerNo))本例中，外鍵由CONSTRAINT來定義，并為該約束命名為OrdermasterFK1。第80頁3.用戶自定義完整性約束在建表時(shí)，用戶可以根據(jù)應(yīng)用的要求，定義屬性上的約束條件，即屬性限制，包括：列值非空(NOTNULL)、列值唯一(UNIQUE)、檢查是否滿足一個(gè)布爾表達(dá)式(CHECK短語)、默認(rèn)值設(shè)置(DEFAULT)等。[例4.26]創(chuàng)建一個(gè)客戶表，為該表建立客戶編號約束：編號共9位，第1位為C。CREATETABLECustomer(

customerNochar(9)NOTNULLPRIMARYKEY,/*客戶編號*/

CHECK(CustomerNoLIKE'[C][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

customerName

varchar(40)NOTNULL,/*客戶名稱*/telephonevarchar(20)NOTNULL,/*客戶電話*/addresschar(40)NOTNULL,/*客戶住址*/zipchar(6)NULL/*郵政編碼*/)第81頁[例4.27]建立一張員工表，要求給該表建立各項(xiàng)約束，包括：主鍵約束；性別是‘m’或‘f’；薪水在3000～8000。CREATETABLEEmployee(

employeeNochar(8)NOTNULL/*員工編號*/CHECK(EmployeeNoLIKE'[E][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

employeeName

varchar(10)NOTNULL,/*員工姓名*/sexchar(1)NOTNULL,/*員工性別*/

CONSTRAINTemp_sexchkCHECK(sexIN('m','f')),birthdaydatetimeNULL,/*員工生日*/addressvarchar(50)NULL,/*員工住址*/telephonevarchar(20)NULL,/*員工電話*/

hireDate

datetimeNOTNULL,/*雇傭日期*/departmentvarchar(30)NOTNULL,/*所屬部門*/titlevarchar(6)NOTNULL,/*職稱*/salarynumeric(8,2)NOTNULL,/*薪水*/

CONSTRAINTEmployeePKPRIMARYKEY(employeeNo),CONSTRAINTemp_salarychkCHECK(salaryBETWEEN3000AND8000))第82頁[例4.28]建立一張訂單主表，要求發(fā)票號碼唯一。CREATETABLEOrderMaster(

orderNochar(12)NOTNULLPRIMARYKEY,/*訂單編號*/

customerNochar(9)NOTNULL,/*客戶編號*/

salerNochar(8)NOTNULL,/*業(yè)務(wù)員編號*/

orderDate

datetimeNOTNULL,/*訂貨日期*/

orderSumnumeric(9,2),/*訂單金額*/

invoiceNochar(10)NOTNULLUNIQUE,/*發(fā)票號碼*/

CONSTRAINTOrderma