《SQL Server數(shù)據(jù)庫應(yīng)用》課件 項目5-8　查詢數(shù)據(jù)庫 -設(shè)計與實現(xiàn)政務(wù)平臺數(shù)據(jù)庫

項目五查詢數(shù)據(jù)庫12任務(wù)1基本查詢?nèi)蝿?wù)2條件查詢?nèi)蝿?wù)3查詢結(jié)果排序任務(wù)4分組查詢?nèi)蝿?wù)5連接查詢?nèi)蝿?wù)6集合查詢?nèi)蝿?wù)7子查詢基本查詢?nèi)蝿?wù)131.?能根據(jù)任務(wù)要求，使用SELECT語句進行數(shù)據(jù)庫查詢。2.?能使用關(guān)鍵字DISTINCT、AS和TOP子句進行數(shù)據(jù)庫查詢。4本任務(wù)要求根據(jù)導(dǎo)入數(shù)據(jù)庫ssts中的學(xué)生成績表stuScore.xls，完成以下查詢。1.查詢stuScore表中的所有記錄。2.查詢總成績排名前10的學(xué)生，其中，總成績=平時成績

×40%+考試成績

×60%，保留兩位小數(shù)。5一、SELECT語法查詢語句SELECT是從數(shù)據(jù)庫中檢索行，并允許從SQLServer中的一個或多個表中選擇一個或多個行或列。雖然SELECT語句的完整語法較為復(fù)雜，但其主要子句可歸納如下。6可在查詢之間使用UNION、EXCEPT和INTERSECT運算符，以便將各個查詢的結(jié)果合并到一個結(jié)果集中或進行比較。SELECT后面列出要查詢的字段名（列名），多個字段名之間要用英文逗號隔開。FROM后面一般為表名（table_name）

或視圖名(view_name)；WHERE后面為搜索條件表達式。GROUPBY表示分組；HAVING表示對結(jié)果過濾，通常與GROUPBY一起使用。ORDERBY表示對結(jié)果集排序時，ASC為升序，DESC為降序。7二、單列和多列查詢1.?單列查詢一般情況下，在數(shù)據(jù)庫中，每個表包含若干個列信息。如果只需要查詢表中的某一列數(shù)據(jù)時，可直接使用以下代碼。82.?多列查詢多列查詢和單列查詢方法基本相同，只需要在多個字段名之間加上逗號分隔即可。3.?查詢所有的列若要查詢的表中有很多列，一一列舉比較麻煩，則可以使用“*”符號代表所有的列，語法格式如下。9三、使用DISTINCT去除結(jié)果的重復(fù)信息如果要去掉重復(fù)的查詢結(jié)果，要加上DISTINCT關(guān)鍵字。這時DISTINCT對所有的列都起作用，若列1內(nèi)容相同，列2內(nèi)容不同，則結(jié)果集中會出現(xiàn)列1相同而列2不同，也會出現(xiàn)列1不同而列2相同的記錄。10四、使用AS取別名用關(guān)鍵字AS可以命名新列，也可以給現(xiàn)有字段取別名。用AS重新指定返回字段或表的名字，稱為別名（aliasname）。1.?列的別名數(shù)據(jù)表中的列名有的是英文，為了方便查看查詢結(jié)果，可以使用別名來代替，以便增強可讀性；另外，在多個表查詢中，出現(xiàn)相同的列，通常也可以使用別名，其語法格式如下。112.?表的別名在進行多表查詢時，為了方便查詢也經(jīng)常給表取別名，其語法格式如下。此時，AS可以省略，可以在原表名后加上空格，再加上別名。123.?結(jié)果集的別名結(jié)果集的別名也稱臨時表T，語法格式如下。134.?別名的使用場合（1）常用在有兩個名字重復(fù)的表，需要為其中一個起一個別名，如自連接查詢。（2）當(dāng)兩個表有重復(fù)的列名，也可以給表取別名，加以區(qū)分。（3）表名或列名較長時，可以為其取別名。（4）把一個查詢結(jié)果當(dāng)作另一個表來查詢，可以理解查詢結(jié)果為一個臨時表T。14五、使用TOP查詢前若干行TOP子句用于規(guī)定要返回的記錄的數(shù)目，TOPnPERCENT則按照前n%返回記錄，語法格式如下。15條件查詢?nèi)蝿?wù)2161.?根據(jù)任務(wù)要求，能使用條件表達式進行查詢。2.?能描述在查詢中AND、OR、NOT、BETWEEN、IN、LIKE的作用。3.?能描述通配符的作用，并會使用通配符查詢數(shù)據(jù)，驗證查詢結(jié)果。17現(xiàn)要求在數(shù)據(jù)庫ssts中，使用學(xué)生成績表stuScore，完成以下查詢?nèi)蝿?wù)。1.查詢考試成績exam在95分及以上的所有記錄。2.根據(jù)公式“總成績total=平時成績normal×40%+考試成績exam×60%”，總成績保留兩位小數(shù)。查詢總成績在95分及以上學(xué)生的學(xué)號、姓名、平時成績、考試成績、總成績、系部。3.查詢平時成績和考試成績都不及格（小于60分）的學(xué)生的姓名、平時成績、考試成績。4.查詢信息工程系姓“李”的所有學(xué)生信息。18一、條件查詢用WHERE子句來說明查詢條件，緊跟在FROM子句的后面。WHERE后面的條件表達式包括由關(guān)系運算符、邏輯運算符、其他運算符構(gòu)成的表達式。其他運算符主要有IN、NOTIN、BETWEEN、NOTBETWEEN、LIKE、NOTLIKE、ISNULL、ISNOTNULL、EXISTS、ANY、SOME。簡單的條件查詢可以在WHERE后加上一個搜索條件。19二、使用關(guān)系運算符表達式查詢在WHERE子句中，條件表達式中使用的關(guān)系運算符主要包括:=、<、>、<=、>=、<>、!=。三、使用邏輯表達式查詢在WHERE子句中，條件表達式中使用的邏輯運算符主要包括NOT、AND、OR。20四、使用BETWEEN限定數(shù)據(jù)查詢范圍在WHERE子句中，使用BETWEEN可以限制查詢數(shù)據(jù)的范圍。五、使用IN限定檢索數(shù)據(jù)查詢范圍IN操作符允許在WHERE子句中規(guī)定多個值，其語法格式如下。21六、使用通配符模糊查詢在搜索數(shù)據(jù)庫中的數(shù)據(jù)時，通配符可以替代一個或多個字符。1.?通配符“*”在SELECT語句中，可以使用通配符“*”來顯示所有字段，SELECT與*之間要有空格。2.?查詢常用的通配符查詢常用的通配符見下表，其中下劃線符號“_”是在英文狀態(tài)下，按Shift鍵和減號鍵（數(shù)字0右側(cè)的鍵）得到的。22在SQL?Server中nchar、nvarchar使用的是Unicode字符集。

而char、varchar使用的是ASCII字符集。當(dāng)Unicode數(shù)據(jù)（nchar或nvarchar）與like?一起使用時，尾隨空格有意義。但對非Unicode數(shù)據(jù)，尾隨空格無意義。而ASCII中char、varchar字符串后面的空格沒有意義。23查詢常用的通配符查詢結(jié)果排序任務(wù)3241.?根據(jù)任務(wù)要求，查詢時能使用ORDER?BY進行排序。2.?能敘述多個字段排序時應(yīng)遵循的順序關(guān)系。25本任務(wù)要求使用ORDERBY完成以下任務(wù)。1.在數(shù)據(jù)庫ssts的book表中，按照price價格從大到小排序。2.在數(shù)據(jù)庫ssts的教師表teacher中，查詢工齡最長的前5名員工的工號、姓名、工齡。3.在數(shù)據(jù)庫ssts中，查詢數(shù)據(jù)表competition。刪除ID不同，其他字段的值都相同的冗余信息。4.將數(shù)據(jù)表competition重命名為newCompetition，查詢Name中帶有“林”字的所有學(xué)生的信息。26一、排序ORDERBY在SQLServer中，為了方便查看查詢結(jié)果，按某種規(guī)律排序，可使用ORDERBY子句進行數(shù)據(jù)排序，還可以進行多字段排序。默認(rèn)的排序方式是升序ASC，如果要降序排列，可在列名后加上DESC。ORDERBY語法格式如下。對查詢結(jié)果集按“列或表達式1”排序，再按“列或表達式2”排序，以此類推。27二、排序原則1.?對于數(shù)值型數(shù)據(jù)，按其數(shù)值大小進行比較。2.?對于日期型數(shù)據(jù)，按年月日的數(shù)值大小進行比較。3.?對于邏輯型數(shù)據(jù)，false小于true。4.?對于中英文字符，按其ASCII碼大小進行比較。5.?對于NULL值，若按升序排列，則含NULL的記錄將最先顯示。6.?不能按ntext、text、image類型排序。28分組查詢?nèi)蝿?wù)4

291.?能在學(xué)習(xí)常用聚合函數(shù)的基礎(chǔ)上，使用GROUP?BY進行分組查詢。2.?能使用HAVING對分組查詢結(jié)果進行篩選。30本任務(wù)要求使用分組查詢，完成以下查詢?nèi)蝿?wù)。1.在數(shù)據(jù)庫ssts的book表中，按書的種類分類，求出各種類型type書籍的價格總和、平均價格及各類書籍的數(shù)量。2.在數(shù)據(jù)庫ssts的book表中，查詢所有價格超過35元的書籍的種類和平均價格。3.在數(shù)據(jù)庫ssts的book表中，在所有價格超過35元的書籍中，查詢所有平均價格超過40元的書籍的種類和平均價格。31一、聚合函數(shù)聚合函數(shù)對一組值執(zhí)行計算并返回單一的值，常用的聚合函數(shù)見下表。32常用的聚合函數(shù)二、分組GROUPBYGROUPBY子句可以將查詢結(jié)果按屬性列或?qū)傩粤薪M合在行的方向上進行分組，每組在屬性列或?qū)傩粤薪M合上具有相同的聚合值。1.?使用GROUP?BY進行簡單分組使用GROUPBY子句對單個字段進行簡單分組，通常與聚合函數(shù)配合使用?！纠繌膶W(xué)生表student中按照性別sex分組，統(tǒng)計男女生人數(shù)。33統(tǒng)計男女生人數(shù)的執(zhí)行結(jié)果如圖所示。（2）反轉(zhuǎn)查詢結(jié)果當(dāng)查詢的結(jié)果不是理想的行列顯示時，可以使用反轉(zhuǎn)查詢進行轉(zhuǎn)換?？梢岳肅OUNT函數(shù)忽略NULL值的規(guī)則，借助于CASE…END表達式實現(xiàn)。34統(tǒng)計男女生人數(shù)的執(zhí)行結(jié)果2.?使用HAVING子句設(shè)置統(tǒng)計條件GROUPBY子句還可以與WHERE子句配合使用，WHERE子句先于GROUPBY子句執(zhí)行，將滿足條件的記錄保留下來，然后，再按照GROUPBY子句分成小組。若在分組后還要按照一定的條件進行篩選，則需使用HAVING子句。在SQL中增加HAVING子句的原因是，WHERE關(guān)鍵字無法與統(tǒng)計函數(shù)一起使用。如果只想要得到分組的統(tǒng)計結(jié)果，那么不需要全部的統(tǒng)計信息。35連接查詢?nèi)蝿?wù)5361.?能根據(jù)公式計算笛卡爾積。2.?能獨立對表做交叉連接查詢、內(nèi)連接查詢和外連接查詢。37本任務(wù)要求在數(shù)據(jù)庫ssts中，使用連接查詢，完成以下3個查詢操作。1.根據(jù)teacher、teach、course三張表，查詢陳老師所任教的課程名稱。2.根據(jù)book表和author表，查詢出版了程序設(shè)計類的圖書作者，而且作者編號authorID是包含2022字符的，輸出作者的authorID、姓名authorName、所在城市city。3.根據(jù)author表，查找居住在“北京”的手機號碼前三位相同的作者。這里的teacher是教師表、teach是教學(xué)表、course是課程表，三表之間有主外鍵關(guān)系；author是作者表，book是圖書表，兩者之間也有主外鍵關(guān)系。38一、交叉連接交叉連接（crossjoin）又稱笛卡爾連接，是指兩個表之間做笛卡爾積操作，返回結(jié)果表的行數(shù)為兩個表行數(shù)的乘積。笛卡爾積：設(shè)A和B為集合，用A中元素為第一元素，B中元素為第二元素構(gòu)成的有序?qū)?，所有這樣的有序?qū)M成的集合叫做A與B的笛卡爾積，記作A×B。笛卡爾積的符號化表示為：A×B={(x,y)|x∈A,y∈B}。39二、內(nèi)連接內(nèi)連接（innerjoin）也稱為等同連接，返回的結(jié)果集是兩個表中所有相匹配的數(shù)據(jù)，而舍棄不匹配的數(shù)據(jù)。內(nèi)連接使用比較運算符來完成，其可分為等值連接與非等值連接。連接條件的一般格式如下。[<表名1.>]<列名><比較運算符>[<表名2.>]<列名>其中，比較運算符主要包括=、>、<、>=、<=、！=。當(dāng)比較運算符為“=”時，稱為等值連接，其他情況稱為非等值連接。若定義了表的別名，則在T-SQL語句中必須用別名代替表名。40三、外連接內(nèi)連接（INNERJOIN）操作只輸出滿足連接條件的元組；外連接（OUTERJOIN）操作以指定表為連接主體，將主體表中不滿足連接條件的元組一起輸出，可分為左外連接（LEFTJOIN）、右外連接(RIGHTJOIN)和全外連接(FULLJOIN)。1.?左外連接左外連接的語法格式如下。41左外連接的結(jié)果集包括LEFTOUTER子句或LEFTOUTERJOIN中指定的左表的所有行，而不僅僅是連接列所匹配的行。如果左表的某行在右表中沒有匹配行，那么在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值。左連接是將左表中的所有記錄與右表中的每條記錄進行組合，結(jié)果集中除返回內(nèi)部連接的記錄外，還在查詢結(jié)果中返回左表中不符合條件的記錄，并在右表的相應(yīng)列上加上NULL，bit類型以0填充。422.?右外連接右外連接的語法格式如下。右外連接RIGHTJOIN或RIGHTOUTERJOIN將返回右表的所有行。右外連接是將左表中的所有記錄與右表中的每條記錄進行組合，結(jié)果集中除返回內(nèi)部連接的記錄外，還在查詢結(jié)果中返回右表中不符合條件的記錄，并在左表的相應(yīng)列上加上NULL，bit類型以0填充。433.?全外連接全外連接的語法格式如下。全外連接FULLJOIN是將左表中的所有記錄與右表中的每條記錄進行組合，結(jié)果集中除返回內(nèi)部連接的記錄外，還在查詢結(jié)果中返回兩個表中不符合條件的記錄，并在左表或右表的相應(yīng)列上加上NULL，bit類型以0填充。44集合查詢?nèi)蝿?wù)6451.?能使用UNION聯(lián)合查詢，將多個查詢結(jié)果合并為一個結(jié)果集。2.?能敘述UNION?ALL關(guān)鍵字的用法，用于合并查詢結(jié)果時保留所有的行，包括重復(fù)行。3.?能敘述UNION、EXCEPT、INTERSECT關(guān)鍵字的用法。4.?能使用SELECT?INTO語句把聯(lián)合查詢的結(jié)果生成新表。46本任務(wù)要求使用數(shù)據(jù)庫ssts中的數(shù)據(jù)表classOne和classTwo進行組合查詢，具體查詢?nèi)蝿?wù)如下。1.查詢兩個班級總分高于320分的學(xué)生的學(xué)號、姓名和成績。2.查詢兩個班級網(wǎng)絡(luò)安全課程成績小于60分的學(xué)生名單。要求輸出學(xué)號、姓名和課程的成績。3.對于網(wǎng)絡(luò)安全課程成績不及格的學(xué)生，單獨生成一張新表，表名為“網(wǎng)絡(luò)安全補考名單”。47一、UNION聯(lián)合查詢在SQLServer中，將多個查詢的結(jié)果放在一起，以一個查詢結(jié)果的形式顯示出來，可以使用UNION關(guān)鍵字把多個SELECT連接起來。每個SELECT查詢語句應(yīng)有相同數(shù)量的字段，若字段個數(shù)不等，可以使用NULL來代替；每個查詢語句中相應(yīng)的字段的類型必須相互兼容，若不兼容，可使用類型轉(zhuǎn)換函數(shù)強制轉(zhuǎn)換字段類型。UNION缺省在合并結(jié)果集后消除重復(fù)項，UNIONALL指定在合并結(jié)果集后保留重復(fù)項。UNION結(jié)果集中的列名總是等于UNION中第一個SELECT語句中的列名。48二、INTERSECT交集查詢UNION將查詢結(jié)果合并到一個結(jié)果集中，而INTERSECT運算符從最終結(jié)果集中刪除重復(fù)的行，取兩個查詢的交集。三、EXCEPT集合差查詢EXCEPT用于從第一個查詢的結(jié)果中去除第二個查詢結(jié)果中也出現(xiàn)的行，即找出在第一個查詢結(jié)果中獨有的行，而不在第二個查詢結(jié)果中出現(xiàn)的行。49子查詢?nèi)蝿?wù)7501.?能使用單值子查詢，將單值子查詢轉(zhuǎn)化為連接查詢。2.?能使用帶有ANY、SOME或ALL子句的子查詢，并驗證查詢結(jié)果。3.?能使用帶有IN、EXISTS和FROM子句的子查詢，并驗證查詢結(jié)果。51本任務(wù)要求在數(shù)據(jù)庫ssts中完成以下3個查詢?nèi)蝿?wù)。1.根據(jù)課程表course，查詢cno號碼是c01、c02或c03的所有信息。2.根據(jù)學(xué)生表student，查詢dept不是信息工程系，也不是創(chuàng)意服務(wù)系的所有的學(xué)生信息。3.根據(jù)newCompetition表的出生日期Date，查詢年齡最小的學(xué)生，數(shù)據(jù)表NewCompetition如圖所示。5253數(shù)據(jù)表NewCompetition一、子查詢子查詢也稱內(nèi)部查詢或嵌套查詢，是指將一個SELECT查詢（子查詢）的結(jié)果作為另一個T-SQL語句（主查詢）的數(shù)據(jù)來源或判斷條件。當(dāng)一個查詢構(gòu)成另一個查詢的條件時，這個查詢稱為子查詢。子查詢是一個嵌套在SELECT、INSERT、DELETE語句或其他子查詢中的查詢。任何允許使用表達式的地方都可以使用子查詢。子查詢可以從任何表中提取數(shù)據(jù)，只要對該表有適當(dāng)?shù)脑L問權(quán)限即可。通過子查詢可以在主查詢的條件中使用子查詢的結(jié)果，以便根據(jù)特定的需求進行過濾和排序。54一般而言，子查詢的組件包括選擇列表組件的常規(guī)SELECT子句、一個表或多個表或視圖名稱的常規(guī)FROM子句、可選的WHERE子句、可選的GROUPBY子句、可選的HAVING子句等。子查詢在上一級查詢處理之前求解，子查詢的結(jié)果用于建立父查詢的查找條件。1.?單值子查詢單值子查詢可以由一個比較運算符引入，由比較運算符引入的子查詢必須返回單個值而不是值列表。552.?帶有比較運算符的子查詢【例】根據(jù)數(shù)據(jù)庫ssts的選課表sc，查詢超出課程平均成績學(xué)生的學(xué)號和課程號。一個SELECTFROMWHERE語句稱為一個查詢塊，將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING子句的條件中的查詢稱為嵌套查詢。56二、帶有ANY、SOME或ALL的子查詢1.?使用ALL返回一組值的普通子查詢ALL運算符用于比較子查詢返回列表中的每個值?！?lt;ALL”表示小于最小的；“>ALL”表示大于最大的；“=ALL”表示沒有返回值，因為在等于子查詢的情況下，返回列表中的所有值是不符合邏輯的。2.?使用ANY返回一組值的普通子查詢ANY運算符用于比較子查詢返回列表中的某個值?！?lt;ANY”表示小于最大的；“>ANY”表示大于最小的；“=ANY”表示等于IN。573.?使用SOME返回一組值的普通子查詢SOME運算符與ANY運算符是同義的，它們都允許比較運算符前面的單值與后面的子查詢返回值集合中的某個值進行比較。如果比較運算符前面的單值與比較運算符后面的子查詢返回值集合中的某個值之間任何比較結(jié)果為TRUE，那么比較表達式求值的結(jié)果為TRUE。58三、帶有IN子查詢通過IN(或NOTIN)運算符引入的子查詢結(jié)果是包含零個或多個值的列表。子查詢返回結(jié)果后，外部查詢將利用這些結(jié)果。帶有IN運算符的子查詢是指在外層查詢和子查詢之間用IN運算符進行連接，判斷某個屬性列是否在子查詢的結(jié)果中，其返回的結(jié)果可以包含零個或多個值。59在IN子句中，子查詢和輸入多個運算符數(shù)據(jù)的區(qū)別在于，使用多個運算符輸入時，一般都會輸入兩個或兩個以上的值；而使用子查詢時，不能確定其返回結(jié)果的數(shù)量。但是，即使子查詢返回的結(jié)果為空，語句也能正常運行。NOTIN運算符也可以應(yīng)用在子查詢中，能夠產(chǎn)生NOTIN使用的清單，但是帶有NOTIN運算符的子查詢，其查詢速度很慢，在對T-SQL語句的性能有所要求時，就要使用性能更好的語句來代替NOTIN子句。60四、帶有EXISTS子查詢帶有EXISTS運算符的子查詢，其功能是判斷子查詢的返回結(jié)果中是否有數(shù)據(jù)行。如果帶有EXISTS運算符的子查詢返回的結(jié)果集是空集，那么判斷為不存在數(shù)據(jù)行，即帶有EXISTS運算符的子查詢失敗。如果帶有EXISTS運算符的子查詢返回至少一行的數(shù)據(jù)記錄，那么判斷存在，即帶有EXISTS運算符的子查詢成功。由于帶有EXISTS運算符的子查詢不用返回具體值，因此該子查詢的選擇列表常用“SELECT*”格式。61在使用EXISTS運算符引入子查詢時，應(yīng)注意以下情況。1.EXISTS運算符一般直接跟在外層查詢的WHERE子句后面，它的前面沒有列名、常量或表達式。2.EXISTS運算符引入子查詢的SELECT列表清單通常是由“*”組成的。3.EXISTS強調(diào)是否返回結(jié)果集，不要求知道返回的是什么。EXISTS子句的返回值是一個布爾值。62五、在FROM子句中使用子查詢SQLServer非常靈活，允許在FROM子句中嵌套使用子查詢。當(dāng)在FROM子句中使用子查詢時，為了更方便地引用這個子查詢的結(jié)果，可以通過AS關(guān)鍵字給它起一個別名，并且還可以對子查詢中的列進行重命名，讓它們在查詢中更加清晰易懂。63項目六管理索引和視圖6465任務(wù)1創(chuàng)建索引任務(wù)2管理索引任務(wù)3管理視圖任務(wù)4通過視圖操作數(shù)據(jù)表創(chuàng)建索引任務(wù)1661.?能根據(jù)應(yīng)用場景選擇創(chuàng)建不同類型的索引。2.?能使用SSMS窗口或T-SQL語句創(chuàng)建索引，能優(yōu)化數(shù)據(jù)表的查詢性能。67為了提升查詢的速度與效率，要在經(jīng)常查詢的表的相應(yīng)字段上創(chuàng)建索引。根據(jù)數(shù)據(jù)庫ssts中的數(shù)據(jù)表classThree，表中包含學(xué)號、姓名、性別、身份證號碼、出生日期、手機等字段，可以使用SSMS方式，在classThree表的“身份證號碼”列上創(chuàng)建聚集索引idx_idcard，可以使用T-SQL語句方式在classThree表的“姓名”列上創(chuàng)建非聚集索引idx_name。創(chuàng)建索引的結(jié)果如圖所示。

68創(chuàng)建索引的結(jié)果一、索引的概念索引是根據(jù)表中一列或多列的值按照一定順序建立的列值與記錄之間的對應(yīng)關(guān)系，是以表列為基礎(chǔ)建立的數(shù)據(jù)庫對象。索引可以提高檢索數(shù)據(jù)行的速度，當(dāng)查閱圖書的某一章節(jié)內(nèi)容時，為了方便查找，可以選擇目錄索引，快速找到頁碼。在學(xué)生成績數(shù)據(jù)庫中，為加快查詢速度，要給經(jīng)常使用的表創(chuàng)建索引，并設(shè)置相關(guān)屬性。因為索引在搜索數(shù)據(jù)上所花的時間比在表中逐行搜索花的時間更長，若列中有幾個不同的值，或表中僅包含幾行值，則不推薦為其創(chuàng)建索引。69二、索引的類型1.?唯一索引唯一索引不允許兩行具有相同的索引值，也就是說，對于表中的任何兩行記錄來說，索引鍵的值都各不相同。如果創(chuàng)建了唯一約束，那么將自動創(chuàng)建唯一索引。2.?主鍵索引為表定義一個主鍵將自動創(chuàng)建主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每一個值都是唯一的，并且不能為空。703.?聚集索引(clustered?index)聚集索引是指表中各行的物理順序與鍵值的邏輯順序（索引順序）相同的索引，每個表只能有一個聚集索引。聚集索引通常創(chuàng)建在表中經(jīng)常被搜索到的列或按順序訪問的列上，在默認(rèn)情況下，主鍵約束自動創(chuàng)建聚集索引。4.?非聚集索引(non-clustered?index)非聚集索引是指表中各行的物理順序與索引順序不相同的索引。數(shù)據(jù)存儲在一個位置，索引存儲在另一個位置，索引中包含指向數(shù)據(jù)存儲位置的指針。71三、創(chuàng)建索引使用T-SQL語句創(chuàng)建索引的簡單語法如下。其中，UNIQUE表示唯一索引，CLUSTERED表示聚集索引，NONCLUSTERED表示非聚集索引，F(xiàn)ILLFACTOR表示填充因子，指定一個0到100之間的值，該值指示索引頁填滿的空間所占的百分比，該值是可選的。使用CREATEINDEX方法可以指定索引的類型、唯一性等，可以創(chuàng)建聚集索引，也可以創(chuàng)建非聚集索引，既可以在一個列上創(chuàng)建索引，又可以在兩個或多個列上創(chuàng)建索引。72四、查看索引在SSMS中查看索引，在對象資源管理器窗口中，依次展開數(shù)據(jù)庫ssts，展開表節(jié)點classThree，展開索引節(jié)點，右擊需要查看索引信息的索引名稱，如idx_mobilephone，在彈出的快捷菜單中選擇“屬性”選項，如圖所示。73選擇“屬性”選項在彈出的“索引屬性”對話框中，可以查看當(dāng)前索引idx_mobilephone的詳細信息，如圖所示。74查看當(dāng)前索引idx_mobilephone的詳細信息也可以展開“統(tǒng)計信息”節(jié)點，雙擊所要查看統(tǒng)計信息的索引idx_mobilephone，查看索引idx_mobilephone的統(tǒng)計信息屬性，如圖所示。在上面的“統(tǒng)計信息屬性”對話框中，在“選擇頁”中選擇“詳細信息”選項，顯示當(dāng)前索引的統(tǒng)計信息，可以查看索引idx_mobilephone的統(tǒng)計信息。75查看索引idx_mobilephone的統(tǒng)計信息屬性

管理索引任務(wù)2761.?能使用存儲過程查看索引。2.?能使用SSMS或T-SQL語句修改索引。3.?能使用SSMS或T-SQL語句刪除索引。77根據(jù)數(shù)據(jù)庫ssts中的數(shù)據(jù)表classThree，該表中包含學(xué)號、姓名、性別、身份證號碼、出生日期、手機等字段，要求使用T-SQL語句在classThree表中的“手機”列上創(chuàng)建唯一的非聚集索引idx_mobilephone，要求使用存儲過程查看classThree表上的所有索引，要求使用T-SQL語句修改idx_mobilephone索引名為idx_phone，

重建idx_phone索引，最后刪除idx_phone索引。78一、修改索引1.?重命名索引重命名索引的語法格式如下。其中，oldIndexName是原索引名稱，newIndexName是新索引名稱。sp_renameoldName和newName[,object_type]必須在索引前面加上表名前綴。792.重新生成索引重新生成索引的語法格式如下。3.?禁用索引禁用索引的語法格式如下。80二、刪除索引使用T-SQL語句中的DROPINDEX命令可以刪除表中的索引，其語法格式如下。在刪除索引時，要注意不能使用DROPINDEX語句刪除由主鍵約束或唯一性約束創(chuàng)建的索引。要想刪除這些索引，必須先刪除這些約束。當(dāng)刪除表時，該表的全部索引也將被刪除。當(dāng)刪除一個聚集索引時，該表的全部非聚集索引重新自動創(chuàng)建。81管理視圖任務(wù)3821.?能在SSMS中查看視圖、創(chuàng)建視圖、修改視圖和刪除視圖。2.?能通過視圖查詢、添加、修改、刪除表數(shù)據(jù)。83現(xiàn)要求附加上已有的數(shù)據(jù)庫ssts，在數(shù)據(jù)庫中分別打開學(xué)生表student、課程表course和選課表sc。需要在SSMS中創(chuàng)建視圖vi_score，要求查詢數(shù)學(xué)課程的考試成績大于等于60分的學(xué)生的學(xué)號、姓名、課程名稱和成績。使用系統(tǒng)存儲過程sp_help查看視圖，來顯示視圖的名稱、擁有者、類型和創(chuàng)建時間等信息。使用系統(tǒng)存儲過程sp_helptext查看視圖的定義。查看已經(jīng)創(chuàng)建的視圖，修改視圖vi_score。創(chuàng)建的視圖vi_score如圖所示。84創(chuàng)建的視圖vi_score一、視圖的概念視圖是一種數(shù)據(jù)庫對象，是一個虛擬的數(shù)據(jù)表，該數(shù)據(jù)表中的數(shù)據(jù)記錄是從一個或多個表（稱為基礎(chǔ)表）中進行查詢篩選后的結(jié)果。85當(dāng)對視圖中的數(shù)據(jù)進行修改時，相應(yīng)的基本表數(shù)據(jù)也被修改；若基本表的數(shù)據(jù)被修改，視圖中的對應(yīng)數(shù)據(jù)也會自動修改。視圖可以是一個數(shù)據(jù)表的一部分，也可以是多個基本表的聯(lián)合，視圖還可以由一個或多個其他視圖產(chǎn)生。視圖上的操作和基本表類似，但是數(shù)據(jù)庫管理系統(tǒng)對視圖的更新操作等往往存在一定的限制。視圖簡化了操作，也提供了數(shù)據(jù)庫的安全機制，可以只允許用戶通過視圖訪問數(shù)據(jù)，而不允許用戶直接訪問基礎(chǔ)表。86二、使用存儲過程查看視圖1.使用存儲過程sp_help查看視圖信息，顯示視圖的名稱、擁有者、類型和創(chuàng)建時間等信息，語法格式如下。2.使用存儲過程sp_helptext查看視圖文本信息，即可以查看到CREATEVIEW的T-SQL語句，語法格式如下。87三、刪除視圖在SSMS中刪除視圖的操作與刪除表一樣，右擊需要刪除的視圖名，如vi_student，在彈出的快捷菜單中，選擇“刪除”選項，在彈出的對話框中單擊“確定”按鈕，即可完成刪除操作。可以使用DROPVIEW語句刪除視圖，其語法格式如下。88通過視圖操作數(shù)據(jù)表任務(wù)4891.?能使用T-SQL語句創(chuàng)建視圖、查看視圖的記錄內(nèi)容、修改視圖和刪除視圖。2.?能通過視圖查詢、添加、修改、刪除表數(shù)據(jù)。90本任務(wù)要求附加上已有的數(shù)據(jù)庫ssts，在數(shù)據(jù)庫中打開學(xué)生表student、課程表course和選課表sc。通過視圖vi_score查詢視圖的執(zhí)行結(jié)果，查詢成績最高的學(xué)生的姓名和成績。創(chuàng)建查詢學(xué)生表student所有信息的視圖vi_student后，通過視圖添加表數(shù)據(jù)，增加一個名為“馮剛”的學(xué)生記錄。通過視圖vi_student修改表數(shù)據(jù)，將“馮剛”的系部由“機電工程系”修改為“電氣工程系”。通過視圖vi_student刪除表數(shù)據(jù)，刪除名為“馮剛”的學(xué)生記錄。91一、視圖的操作1.使用T-SQL語句創(chuàng)建視圖使用T-SQL語句創(chuàng)建視圖的簡單語法結(jié)構(gòu)如下。922.使用T-SQL語句修改視圖可以使用ALTERVIEW語句來修改視圖，語法格式如下。93二、視圖的應(yīng)用1.通過視圖查詢表數(shù)據(jù)通過視圖對基本表做添加、修改和刪除時，要注意限制條件。以視圖的查詢?yōu)槔晥D的一個重要作用是簡化查詢，為復(fù)雜的查詢建立一個視圖，不必輸入復(fù)雜的查詢語句，只需對此視圖做簡單的查詢即可。942.通過視圖添加表數(shù)據(jù)使用視圖插入數(shù)據(jù)與在基本表中插入數(shù)據(jù)一樣，都可以通過INSERT語句來實現(xiàn)。插入數(shù)據(jù)的操作是針對視圖中的列的插入操作，而不是針對基本表中的所有列的插入操作。使用INSERT語句進行插入操作的視圖必須能夠在基本表中插入數(shù)據(jù)，否則插入操作將會失敗。對于由多個基本表連接而成的視圖來說，一個插入操作只能作用于一個基本表上，語法格式如下。953.通過視圖修改表數(shù)據(jù)使用視圖修改數(shù)據(jù)與在基本表中修改數(shù)據(jù)一樣，都可以通過UPDATE語句來實現(xiàn)，語法格式如下。若通過視圖修改數(shù)據(jù)，視圖必須定義在一個表上，并且不包括統(tǒng)計函數(shù)，SELECT語句中不包括GROUPBY子句。964.通過視圖刪除表數(shù)據(jù)盡管視圖不一定包含基礎(chǔ)表的所有列，但可以通過視圖刪除基礎(chǔ)表的數(shù)據(jù)行，語法格式如下。通過視圖刪除數(shù)據(jù)與通過基本表刪除數(shù)據(jù)的方式一樣，在視圖中刪除的數(shù)據(jù)同時在基本表中也被刪除。當(dāng)一個視圖連接了兩個以上的基本表時，對數(shù)據(jù)的刪除操作則是不允許的。97項目七維護數(shù)據(jù)庫安全9899任務(wù)1配置SQLServer身份驗證模式任務(wù)2管理服務(wù)器登錄和數(shù)據(jù)庫用戶任務(wù)3管理角色任務(wù)4管理權(quán)限配置SQLServer身份驗證模式任務(wù)11001.?了解兩種不同的身份驗證模式。2.?了解身份驗證模式的工作原理、優(yōu)勢和限制。3.?能在SQL?Server中更改身份驗證模式，適應(yīng)特定的身份驗證需求。4.?能更改身份驗證模式，并了解其對應(yīng)用程序和數(shù)據(jù)庫的影響，保證應(yīng)用程序和數(shù)據(jù)庫正常運行。101SQLServer安全管理模式是建立在安全身份驗證和訪問許可的基礎(chǔ)上的。SQLServer通過驗證登錄名和口令的方式來保證其安全性，登錄名和口令又稱賬號和密碼。Windows操作系統(tǒng)和SQLServer都是微軟公司的產(chǎn)品，因此，SQLServer的驗證可以由Windows操作系統(tǒng)來完成。102SQLServer身份驗證模式有Windows驗證機制和SQLServer驗證機制兩種。本任務(wù)要求把Windows驗證機制修改為SQLServer驗證機制，SQLServer驗證機制如圖所示。103SQL?Server驗證機制一、安全身份驗證安全身份驗證用來確認(rèn)登錄SQLServer用戶的登錄賬號和密碼的正確性，由此來驗證該用戶是否具有連接SQLServer的權(quán)限。任何用戶在使用SQLServer數(shù)據(jù)庫之前，必須經(jīng)過系統(tǒng)的安全身份驗證。1041.?Windows身份驗證SQLServer數(shù)據(jù)庫系統(tǒng)通常運行在Windows服務(wù)器上，而Windows作為網(wǎng)絡(luò)操作系統(tǒng)，本身就具備管理登錄、驗證用戶合法性的能力，因此Windows驗證模式正是利用了這一用戶安全性和賬號管理機制，允許SQLServer可以使用Windows的用戶名和口令。在這種模式下，只需要通過Windows身份驗證，就可以連接到SQLServer。1052.?SQL?Server身份驗證SQLServer身份驗證模式允許用戶使用SQLServer安全性連接到SQLServer。在該認(rèn)證模式下，用戶在連接SQLServer時必須提供登錄名和登錄密碼，這些登錄信息存儲在系統(tǒng)表syslogins中，與Windows的登錄賬號無關(guān)。SQLServer自身執(zhí)行認(rèn)證處理，如果輸入的登錄信息與系統(tǒng)表syslogins中的某條記錄相匹配，那么表明登錄成功。

106二、驗證模式的設(shè)置利用SQLServer管理平臺可以進行認(rèn)證模式的設(shè)置，操作步驟如下。1.打開SQLServer管理平臺，右擊要設(shè)置認(rèn)證模式的服務(wù)器，在彈出的快捷菜單中，選擇“屬性”選項，彈出“服務(wù)器屬性”對話框。2.在“服務(wù)器屬性”對話框中選擇“安全性”選擇頁，SQLServer服務(wù)器屬性如圖所示。1073.在“數(shù)據(jù)庫設(shè)置”選擇頁，可以更改“數(shù)據(jù)”“日志”“備份”文件的位置，這樣在每次創(chuàng)建數(shù)據(jù)庫時，可以省略更改數(shù)據(jù)庫文件和日志文件的保存位置。108SQL?Server服務(wù)器屬性三、訪問許可確認(rèn)通過了認(rèn)證并不代表用戶就能訪問SQLServer中的數(shù)據(jù)，同時必須通過許可確認(rèn)。用戶只有在具有訪問數(shù)據(jù)庫的權(quán)限之后，才能對服務(wù)器上的數(shù)據(jù)庫進行權(quán)限許可下的各種操作，這種用戶訪問數(shù)據(jù)庫權(quán)限的設(shè)置是通過用戶賬號來實現(xiàn)的。109管理服務(wù)器登錄和數(shù)據(jù)庫用戶任務(wù)21101.?能使用SSMS界面管理SQL?Server登錄，能創(chuàng)建、修改和刪除登錄賬號。2.?能使用T-SQL語言管理SQL?Server登錄，能創(chuàng)建、修改和刪除登錄賬號。3.?能管理登錄賬號和數(shù)據(jù)庫用戶，能分配適當(dāng)?shù)臋?quán)限和角色。111通過命令行新建一個登錄名stuLogin，并設(shè)置密碼為1234qwer!。創(chuàng)建成功后，使用stuLogin登錄數(shù)據(jù)庫服務(wù)器，并在此登錄名下添加數(shù)據(jù)庫用戶stuLoginUser。隨后，檢測并分析stuLoginUser所能訪問的數(shù)據(jù)庫列表。完成分析后，刪除數(shù)據(jù)庫用戶stuLoginUser，并最終刪除登錄名stuLogin。112為教師用戶群體創(chuàng)建一個登錄名teaLogin，并設(shè)置密碼為258asd!。同時，在該登錄名下創(chuàng)建數(shù)據(jù)庫用戶teaUser，并指定其默認(rèn)架構(gòu)為dbo。在數(shù)據(jù)庫ssts中，將teaUser用戶添加至db_owner角色，以對其賦予高級數(shù)據(jù)庫管理權(quán)限。最后，執(zhí)行對數(shù)據(jù)庫ssts中數(shù)據(jù)表的查詢操作，以確保權(quán)限配置正確無誤。113一、服務(wù)器的登錄名在SQLServer中，利用SQLServer管理平臺即SSMS可以創(chuàng)建服務(wù)器的登錄名，也可以通過T-SQL語句新建服務(wù)器的登錄名。服務(wù)器的登錄名用來登錄SQLServer數(shù)據(jù)庫服務(wù)器，一個數(shù)據(jù)庫服務(wù)器上可能有若干個數(shù)據(jù)庫。114二、管理服務(wù)器的登錄名1.?使用SSMS新建服務(wù)器登錄名打開SQLServer管理平臺，單擊需要登錄的服務(wù)器左側(cè)的“+”按鈕，然后展開“安全性”文件夾。如圖所示，右擊“登錄名”文件夾，在彈出的快捷菜單中，選擇“新建登錄名”選項，彈出如圖所示的“登錄名-新建”對話框。

115當(dāng)前服務(wù)器的名稱和登錄名

選擇“新建登錄名”選項116“登錄名-新建”對話框在“登錄名”文本框中輸入登錄名，可以選擇Windows身份驗證和SQLServer身份驗證兩種模式?？梢匀コ催x“強制密碼過期”和“用戶在下次登錄時必須更改密碼”及“強制實施密碼策略”三個復(fù)選框，這樣密碼會簡單些。選擇“服務(wù)器角色”選擇頁，在如圖所示的“服務(wù)器角色”列表框中，列出了系統(tǒng)的固定服務(wù)器角色。在這些固定服務(wù)器角色的左側(cè)有相應(yīng)的復(fù)選框，勾選相應(yīng)的復(fù)選框表示該登錄賬號是相應(yīng)的服務(wù)器角色成員。117118“服務(wù)器角色”列表框選擇“用戶映射”選擇頁，在如圖所示的“用戶映射”列表框中列出了“映射到此登錄名的用戶”，單擊左側(cè)的復(fù)選框設(shè)定該登錄賬號可以訪問的數(shù)據(jù)庫及該賬號在各個數(shù)據(jù)庫中對應(yīng)的用戶名。下面的列表框列出了相應(yīng)的“數(shù)據(jù)庫角色成員身份”清單，從中可以指定該賬號所屬的數(shù)據(jù)庫角色。119“用戶映射”列表框設(shè)置完成后，單擊“確定”按鈕即可完成登錄賬號的創(chuàng)建?？梢栽凇暗卿浢蔽募A下找到所創(chuàng)建的登錄名。斷開數(shù)據(jù)庫的連接，使用新建的登錄名chensir和密碼登錄數(shù)據(jù)庫服務(wù)器。1202.使用T-SQL語句新建服務(wù)器的登錄名（1）Windows身份認(rèn)證Windows身份認(rèn)證的語法格式如下。121（2）SQLServer身份認(rèn)證SQLSever身份認(rèn)證的語法格式如下。1223.修改服務(wù)器的登錄名（1）啟用已禁用的登錄名【例】啟用已禁用的登錄名techerLogin。（2）更改登錄密碼【例】更改登錄名techerLogin的登錄密碼。（3）更改登錄名稱【例】將登錄名techerLogin更改為techLogin。1234.刪除登錄名不能刪除正在登錄的登錄名，也不能刪除擁有任何安全對象、服務(wù)器級對象或SQLServer代理作業(yè)的登錄名?！纠縿h除登錄名techerLogin。124三、數(shù)據(jù)庫用戶數(shù)據(jù)庫用戶訪問指定的數(shù)據(jù)庫。登錄成功后，根據(jù)登錄名找到對應(yīng)的數(shù)據(jù)庫用戶，再去訪問某個具體用戶數(shù)據(jù)庫。找到該數(shù)據(jù)庫用戶對應(yīng)的權(quán)限，操作數(shù)據(jù)庫。服務(wù)器的登錄名與數(shù)據(jù)庫用戶是一對多的關(guān)系，數(shù)據(jù)庫用戶與數(shù)據(jù)庫是一對一的關(guān)系。125四、管理數(shù)據(jù)庫用戶在一個數(shù)據(jù)庫中，數(shù)據(jù)庫用戶賬號可唯一標(biāo)識一個用戶，數(shù)據(jù)庫用戶對數(shù)據(jù)庫的訪問權(quán)限及對數(shù)據(jù)庫對象的所有關(guān)系都是通過用戶賬號進行控制的。1.?使用SSMS新建數(shù)據(jù)庫用戶利用SQLServer管理平臺可以授予SQLServer登錄訪問數(shù)據(jù)庫的許可權(quán)限。利用SQLServer管理平臺創(chuàng)建一個新數(shù)據(jù)庫用戶賬號的過程如下。126打開SQLServer管理平臺，展開要登錄的服務(wù)器和數(shù)據(jù)庫文件夾，然后展開要創(chuàng)建用戶的數(shù)據(jù)庫及“安全性”文件夾，右擊“用戶”圖標(biāo)，在彈出的快捷菜單中，選擇“新建用戶”選項，彈出如圖所示的“數(shù)據(jù)庫用戶-新建”對話框。在“用戶類型框”文本框中輸入數(shù)據(jù)庫用戶名稱，在“登錄名”選擇框內(nèi)選擇已經(jīng)創(chuàng)建的登錄名，然后在下面的“默認(rèn)架構(gòu)”選擇框中為該用戶選擇數(shù)據(jù)庫角色，最后單擊“確定”按鈕，即可完成數(shù)據(jù)庫用戶的創(chuàng)建。127“數(shù)據(jù)庫用戶-新建”對話框2.?使用SSMS查看或刪除數(shù)據(jù)庫用戶在SQLServer管理平臺中，可以查看或刪除數(shù)據(jù)庫用戶。（1）展開某一數(shù)據(jù)庫，展開“用戶”文件夾，則會顯示當(dāng)前數(shù)據(jù)庫的所有用戶。（2）在右側(cè)的頁框中，右擊所要刪除的數(shù)據(jù)庫用戶，在彈出的快捷菜單中，選擇“刪除”選項，如圖所示，即可刪除數(shù)據(jù)庫用戶。128選擇“刪除”選項3.使用T-SQL語句新建數(shù)據(jù)庫用戶語法格式如下。如果已省略FORLOGIN，那么新的數(shù)據(jù)庫用戶將被映射到同名的SQLServer登錄名。1294.使用T-SQL語句修改數(shù)據(jù)庫用戶語法格式如下。130上述語句中的DEFAULT_SCHEMA即默認(rèn)架構(gòu)，它是數(shù)據(jù)庫中的一個概念，類似于Windows系統(tǒng)中的默認(rèn)用戶文件夾。在數(shù)據(jù)庫中，每個用戶或會話都有一個默認(rèn)架構(gòu)，用于在沒有明確指定架構(gòu)時解析對象名。這意味著，如果只輸入表名而不指定架構(gòu)，數(shù)據(jù)庫將自動在DEFAULT_SCHEMA中查找該表。簡單來說，DEFAULT_SCHEMA就是數(shù)據(jù)庫用戶默認(rèn)的“工作空間”，用于存儲和訪問用戶最常用的數(shù)據(jù)庫對象。如果不能確定默認(rèn)架構(gòu)，就使用dbo架構(gòu)。其中，principle_id可以通過下面的查詢語句得到：1315.使用T-SQL語句刪除數(shù)據(jù)庫用戶語法格式如下。不能從數(shù)據(jù)庫中刪除擁有安全對象的用戶，必須先刪除或轉(zhuǎn)移安全對象的所有權(quán)，才能刪除擁有這些安全對象的數(shù)據(jù)庫用戶。132管理角色任務(wù)31331.?能使用SSMS或T-SQL語句管理固定服務(wù)器角色及其成員。2.?能使用SSMS或T-SQL語句管理固定數(shù)據(jù)庫角色及其成員。3.?能使用SSMS或T-SQL語句管理用戶自定義數(shù)據(jù)庫角色及其成員。4.?能使用SSMS或T-SQL語句管理應(yīng)用程序角色。134本任務(wù)要求完成以下管理角色的操作。使用SSMS管理固定服務(wù)器角色，查看固定服務(wù)器角色，增加用戶linkLogin為sysadmin服務(wù)器成員，一段時間后，刪除服務(wù)器角色成員linkLogin。使用SSMS管理固定數(shù)據(jù)庫角色，查看固定數(shù)據(jù)庫角色及其成員和權(quán)限，添加登錄名linkLogin，默認(rèn)登錄數(shù)據(jù)庫ssts，添加數(shù)據(jù)庫ssts的用戶zhangyong，其登錄名為linkLogin，設(shè)置為db_owner數(shù)據(jù)庫角色的成員。135使用SSMS管理用戶自定義數(shù)據(jù)庫角色，在數(shù)據(jù)庫ssts中，創(chuàng)建用戶角色teacher，并添加用戶zhangyong作為角色成員，為角色teacher授予查詢和插入選課表sc的權(quán)限。用戶自定義的數(shù)據(jù)庫角色teacher的屬性和成員如圖所示。使用SSMS方式管理應(yīng)用程序角色：創(chuàng)建應(yīng)用程序角色AppNewRole。為此角色配置擁有的架構(gòu)db_owner。136用戶自定義的數(shù)據(jù)庫角色teacher的屬性和成員一、服務(wù)器角色服務(wù)器角色根據(jù)SQLServer的管理任務(wù)，及這些任務(wù)相對的重要性等級來把具有SQLServer管理職能的用戶劃分為不同的用戶組，每一組所具有的管理權(quán)限都是SQLServer內(nèi)置的。服務(wù)器角色也稱為固定服務(wù)器角色，它不能被用戶創(chuàng)建，其權(quán)限作用域為服務(wù)器范圍。137服務(wù)器角色可用來管理服務(wù)器上的權(quán)限。當(dāng)用戶成功安裝SQLServer后，服務(wù)器角色就存在于數(shù)據(jù)庫服務(wù)器中，且已具備了執(zhí)行指定操作的權(quán)限。可以將服務(wù)器級主體（SQLServer登錄名、Windows賬戶和Windows組）添加到服務(wù)器角色。固定服務(wù)器角色的每個成員都可以將其他登錄名添加到該角色，用戶定義的服務(wù)器角色的成員無法將其他服務(wù)器級主體添加到角色。不是每個用戶都應(yīng)該分配為服務(wù)器角色，只有高級用戶如數(shù)據(jù)庫管理員，應(yīng)分配為服務(wù)器角色。SQLServer提供了9個固定服務(wù)器角色，服務(wù)器角色的權(quán)限見下表。138139服務(wù)器角色的權(quán)限1.查看固定服務(wù)器角色及其成員在新建查詢窗口輸入以下代碼。執(zhí)行后，查看固定服務(wù)器角色如圖所示。140查看固定服務(wù)器角色由上圖可以看出，顯示有8個固定服務(wù)器角色，另外還有1個public角色沒有顯示，原因是sp_helpsrvrole無法識別public角色。在服務(wù)器上創(chuàng)建的每個登錄名都是public服務(wù)器角色的成員，不能將用戶、組或角色指派為public角色的成員。不要為服務(wù)器public角色授予服務(wù)器權(quán)限，可以通過對public設(shè)置權(quán)限從而為所有數(shù)據(jù)庫設(shè)置相同的權(quán)限。1412.增加服務(wù)器角色成員將登錄賬號添加到固定服務(wù)器角色中，使用系統(tǒng)存儲過程sp_addsrvrolemember，語法格式如下。其中，login是待添加的登錄名，可以是Windows登錄名或SQLServer登錄名；role是固定服務(wù)器角色名稱。1423.?刪除服務(wù)器角色成員將登錄名從固定服務(wù)器角色中刪除，使用系統(tǒng)存儲過程sp_dropsrvrolemember，語法格式如下。143二、數(shù)據(jù)庫角色數(shù)據(jù)庫角色是為某一用戶或某一組用戶授予不同級別的管理或訪問數(shù)據(jù)庫及數(shù)據(jù)庫對象的權(quán)限，這些權(quán)限是數(shù)據(jù)庫專有的，并且還可以使一個用戶具有屬于同一數(shù)據(jù)庫的多個角色。數(shù)據(jù)庫角色定義在數(shù)據(jù)庫級上，保存在各自數(shù)據(jù)庫的系統(tǒng)表sysusers之中，作用在各自的數(shù)據(jù)庫之內(nèi)。數(shù)據(jù)庫管理員給數(shù)據(jù)庫用戶指定角色，也就是將該用戶添加到相應(yīng)的角色組中。通過角色簡化了直接向數(shù)據(jù)庫用戶分配權(quán)限的煩瑣操作，對于用戶數(shù)量多、安全策略復(fù)雜的數(shù)據(jù)庫系統(tǒng)，能夠簡化安全管理工作。數(shù)據(jù)庫角色分為固定數(shù)據(jù)庫角色和用戶自定義數(shù)據(jù)庫角色。1441.?固定數(shù)據(jù)庫角色固定數(shù)據(jù)庫角色是系統(tǒng)預(yù)先定義在數(shù)據(jù)庫級上的角色。除public角色外，角色的種類和權(quán)限都是固定的，不可更改或刪除，只允許為其添加或刪除成員。SQLServer提供了9個固定數(shù)據(jù)庫角色，固定數(shù)據(jù)庫角色的權(quán)限見下表。145固定數(shù)據(jù)庫角色的權(quán)限146固定數(shù)據(jù)庫角色的權(quán)限（1）查看固定數(shù)據(jù)庫角色及其成員查看數(shù)據(jù)庫ssts固定數(shù)據(jù)庫角色，在新建查詢窗口輸入以下代碼。執(zhí)行后，查看固定數(shù)據(jù)庫角色如圖所示。147查看固定數(shù)據(jù)庫角色（2）添加固定數(shù)據(jù)庫角色成員與固定服務(wù)器角色類似，SQLServer提供的系統(tǒng)存儲過程sp_addrolemember，能夠為數(shù)據(jù)庫角色添加成員，語法格式如下。其中，role是當(dāng)前數(shù)據(jù)庫中數(shù)據(jù)庫角色的名稱；security_account是添加到該角色的安全賬戶；security_account可以是數(shù)據(jù)庫用戶、數(shù)據(jù)庫角色、Windows登錄或Windows組。148（3）刪除固定數(shù)據(jù)庫角色成員若要刪除數(shù)據(jù)庫角色的成員，使用的存儲過程為sp_droprolemember，語法格式如下。1492.用戶自定義數(shù)據(jù)庫角色（1）創(chuàng)建用戶自定義數(shù)據(jù)庫角色使用CREATEROLE語句可以自定義數(shù)據(jù)庫角色，語法格式如下。其中，role_name是用戶自定義角色的名稱，AUTHORIZATIONowner_name是將擁有新角色的數(shù)據(jù)庫用戶或角色，如果未指定用戶，那么執(zhí)行CREATEROLE的用戶將擁有該角色。（2）添加用戶為角色成員與添加固定服務(wù)器角色成員類似，將用戶添加到角色中，使用戶成為角色成員，獲得與角色一樣的權(quán)限，將用戶添加到角色中可以使用sp_addrolemember語句完成。150（3）授予角色權(quán)限為主體授予安全對象的權(quán)限，使用“GRANT<某種權(quán)限>ON<某個對象>TO<某個用戶、登錄名或組>”，語法格式如下。如果指定了WITHGRRANTOPTION子句，那么獲得某種權(quán)限的用戶還可以把這種權(quán)限再授予給其他用戶。如果沒有指定WITHGRRANTOPTION子句，那么獲得某種權(quán)限的用戶只能使用該權(quán)限，不能傳播該權(quán)限。GRANT語句向用戶授予權(quán)限，REVOKE語句撤銷已經(jīng)授予用戶的權(quán)限。151（4）刪除用戶自定義數(shù)據(jù)庫角色刪除用戶自定義數(shù)據(jù)庫角色時，可以使用DROPROLE命令實現(xiàn)。152三、應(yīng)用程序角色1.創(chuàng)建應(yīng)用程序角色使用CREATEAPPLICATIONROLE語句創(chuàng)建應(yīng)用程序角色，語法格式如下。其中，application_role_name是指定應(yīng)用程序角色的名稱，此名稱不得已用于引用數(shù)據(jù)庫中的任何主體；password是指定數(shù)據(jù)庫用戶將用于激活應(yīng)用程序角色的密碼；schema_name是指定服務(wù)器在解析此角色的對象名稱時將搜索的第一個架構(gòu)，如果未定義默認(rèn)架構(gòu)，那么應(yīng)用程序角色將使用dbo作為其默認(rèn)架構(gòu)。1532.?激活應(yīng)用程序角色當(dāng)用戶執(zhí)行客戶端應(yīng)用程序并連接到SQLServer服務(wù)器時，需要調(diào)用系統(tǒng)存儲過程sp_setapprole來激活應(yīng)用程序角色，語法格式如下。其中，[@rolename=]'role'在當(dāng)前數(shù)據(jù)庫中定義應(yīng)用程序角色的名稱。[@password=]{encryptN'password'}激活應(yīng)用程序角色所需的密碼。使用encrypt函數(shù)時

，必須將N放在第一個引號之前，將密碼轉(zhuǎn)換為Unicode字符串。使用sp_setapprole激活應(yīng)用程序角色后，該角色將保持活動狀態(tài)，直到用戶與服務(wù)器斷開連接或停用該角色。1543.?刪除應(yīng)用程序角色若從當(dāng)前數(shù)據(jù)庫刪除應(yīng)用程序角色，則需要使用“DROPAPPLICATIONROLE”語句，語法格式如下。4.?使用應(yīng)用程序角色的過程（1）用戶執(zhí)行客戶端應(yīng)用程序，客戶端應(yīng)用程序以用戶身份連接到SQLServer服務(wù)器。（2）應(yīng)用程序通過指定密碼和應(yīng)用程序角色執(zhí)行系統(tǒng)存儲過程sp_setapprole。（3）若應(yīng)用程序角色生效，此時連接會放棄用戶的原有權(quán)限。（4）使用應(yīng)用程序角色操作數(shù)據(jù)庫。155管理權(quán)限任務(wù)41561.?能使用GRANT語句授予對象級權(quán)限，授予表、視圖、存儲過程等的訪問權(quán)限。2.?能使用GRANT語句授予系統(tǒng)級權(quán)限，授予登錄、數(shù)據(jù)庫訪問、備份恢復(fù)等的權(quán)限。3.?能使用DENY語句完成拒絕授予權(quán)限的操作，阻止用戶或角色對某些對象的訪問或操作。4.?能使用REVOKE語句完成收回權(quán)限的操作，撤銷已經(jīng)授予的權(quán)限。157管理員有權(quán)限允許用戶創(chuàng)建、修改和刪除數(shù)據(jù)表。在數(shù)據(jù)庫ssts中，管理員使用T-SQL語句管理用戶chensir的權(quán)限，授予用戶chensir對數(shù)據(jù)表student的SELECT權(quán)限，拒絕用戶chensir對數(shù)據(jù)庫ssts中學(xué)生表student進行增加、刪除、修改的權(quán)限。數(shù)據(jù)庫用戶chensir的權(quán)限如圖所示。158159數(shù)據(jù)庫用戶chensir的權(quán)限經(jīng)過一段時間后，管理員撤銷授予用戶chensir對數(shù)據(jù)表student的SELECT權(quán)限。最后刪除數(shù)據(jù)庫用戶chensir，刪除登錄名chensir。一、授予權(quán)限對數(shù)據(jù)對象授予權(quán)限的基本語法如下。160為主體授予安全對象的權(quán)限，一般順序是“GRANT<某種權(quán)限>ON<某個對象>TO<某個用戶、登錄名或組>”。其中，ALL表示授予為全部可能的權(quán)限；PRIVILEGES包含此參數(shù)是為了符合ISO標(biāo)準(zhǔn)；permission是權(quán)限的名稱；column指定表中將授予權(quán)限的列的名稱，需要使用圓括號；class指定將授予權(quán)限的安全對象的類，需要使用作用域限定符::；securable指定將授予權(quán)限的安全對象；TOprincipal是主體的名稱；GRANTOPTION指示被授權(quán)者在獲得指定權(quán)限的同時，還可以將指定權(quán)限授予其他主體。161二、拒絕授予權(quán)限拒絕為主體授予權(quán)限，防止該主體通過組或角色成員身份繼承權(quán)限。DENY優(yōu)先于所有權(quán)限，但DENY不適用于sysadmin固定服務(wù)器角色的對象所有者或成員。需要注意的是，sysadmin固定服務(wù)器角色的成員和對象所有者不能拒絕授予權(quán)限。162對數(shù)據(jù)對象拒絕授予權(quán)限的基本語法如下。其中，參數(shù)的含義與GRANT語句的參數(shù)相同，CASCADE指示拒絕授予指定主體該權(quán)限時，被該指定主體授予了該權(quán)限的所有其他主體也被拒絕授予該權(quán)限。當(dāng)主體具有帶GRANTOPTION的權(quán)限時，CASCADE為必選項。163三、收回權(quán)限撤銷以前授予或拒絕的權(quán)限，基本語法如下。參數(shù)的含義與GRANT語句的參數(shù)相同。164項目八設(shè)計與實現(xiàn)政務(wù)平臺數(shù)據(jù)庫165166任務(wù)1創(chuàng)建政務(wù)平臺數(shù)據(jù)庫任務(wù)2編輯政務(wù)平臺數(shù)據(jù)庫任務(wù)3查詢政務(wù)平臺數(shù)據(jù)庫任務(wù)4使用索引和視圖優(yōu)化政務(wù)平臺數(shù)據(jù)庫創(chuàng)建政務(wù)平臺數(shù)據(jù)庫任務(wù)11671.?能對數(shù)據(jù)庫功能需求和性能需求進行分析。2.?能繪制數(shù)據(jù)庫的E-R圖，能寫出數(shù)據(jù)表的關(guān)系模式。3.?能使用CREATE?DATABSE創(chuàng)建數(shù)據(jù)庫，能使用CREATE?TABLE創(chuàng)建數(shù)據(jù)表。4.?能使用INSERT?INTO?VALUES語句，插入一條或多條記錄到表中，能選擇恰當(dāng)?shù)臄?shù)據(jù)類型。5.?能在創(chuàng)建表時使用主鍵、外鍵等約束。168政務(wù)平臺面向廣大社會群眾，發(fā)布政府政策新聞、公開信息、機構(gòu)設(shè)置等信息，其后臺需要政務(wù)數(shù)據(jù)庫的支持，在開發(fā)數(shù)據(jù)庫之前，需要對政務(wù)平臺的數(shù)據(jù)庫進行前期的

需求分析，數(shù)據(jù)庫分析與設(shè)計完成后，公司的數(shù)據(jù)庫工程師接到創(chuàng)建政務(wù)平臺數(shù)據(jù)庫(policyplatformdatabase)的任務(wù)，現(xiàn)要求創(chuàng)建一個名為zw的數(shù)據(jù)庫，包括Information、Organization、User三個表，

分別定義主鍵、外鍵等約束。數(shù)據(jù)庫zw的數(shù)據(jù)庫關(guān)系圖如圖所示。169170數(shù)據(jù)庫zw的數(shù)據(jù)庫關(guān)系圖一、政務(wù)平臺數(shù)據(jù)庫需求分析通過對系統(tǒng)的終端用戶、政府職能部門的調(diào)查分析，定義政務(wù)平臺數(shù)據(jù)庫的功能需求。政務(wù)平臺數(shù)據(jù)庫的功能需求主要包括用戶管理、機構(gòu)管理及信息發(fā)布和查詢等功能。用戶管理包括用戶的注冊、登錄、權(quán)限控制等；機構(gòu)管理包括機構(gòu)信息的錄入和管理；信息發(fā)布和查詢功能包括發(fā)布新信息、查詢已發(fā)布信息、按機構(gòu)過濾信息等。此外，用戶角色的劃分也是必要的，以便不同角色的用戶有不同的操作權(quán)限。171政務(wù)平臺數(shù)據(jù)庫的性能需求涉及查詢和更新操作的效率，尤其在信息發(fā)布頻繁的情況下，要求數(shù)據(jù)庫能夠迅速處理大量用戶登錄、信息發(fā)布和查詢的請求。由于機構(gòu)與信息存在一對多關(guān)系，需要優(yōu)化查詢機構(gòu)發(fā)布的所有信息的性能。同時，對于信息的查詢，可能需要考慮按標(biāo)題、按內(nèi)容等字段進行模糊查詢，因此需要有相應(yīng)的索引來提高政務(wù)平臺信息的檢索速度。172二、概念結(jié)構(gòu)設(shè)計階段在需求分析的基礎(chǔ)上，通過實體關(guān)系模型（ER模型）等工具，將需求轉(zhuǎn)化為數(shù)據(jù)庫設(shè)計的概念模型，確定實體、關(guān)系及其屬性，并建立實體間的關(guān)系模式。在設(shè)計政務(wù)平臺數(shù)據(jù)庫時，需要考慮實體包括信息、機構(gòu)、用戶及其屬性。在概念結(jié)構(gòu)設(shè)計階段，繪制出數(shù)據(jù)庫的E-R圖。政務(wù)平臺數(shù)據(jù)庫的E-R圖如圖所示。173174政務(wù)平臺數(shù)據(jù)庫的E-R圖將需求轉(zhuǎn)化為概念模型，每個實體轉(zhuǎn)化為一個表，實體的屬性轉(zhuǎn)化為列，并確定主鍵。信息（信息ID、信息標(biāo)題，信息內(nèi)容）Information(InformationID,Tile,Content)機構(gòu)（機構(gòu)ID，機構(gòu)名稱，負(fù)責(zé)人，聯(lián)系電話）Organization(OrganizationID,Name,Head,ContactNumber)175用戶（用戶ID，用戶名，密