單元7事務(wù)、視圖及索引

1、事務(wù)、視圖和索引事務(wù)、視圖和索引單元單元7 7回顧與作業(yè)點評3-1下面的下面的SQL語句有錯嗎？實現(xiàn)了什么功能？語句有錯嗎？實現(xiàn)了什么功能？ 上述語句可以用下面的聯(lián)接替換嗎？上述語句可以用下面的聯(lián)接替換嗎？ 可可 以以 SELECT * FROM Student WHERE StudentNo IN SELECT Student.* FROM Student INNER JOIN Result ON Student.StudentNo=Result.StudentNo(SELECT * FROM Result)(SELECT StudentNo FROM Result)回顧與作業(yè)點評3-2下列

2、語句實現(xiàn)了什么功能？下列語句實現(xiàn)了什么功能？上述語句可以用下面的聯(lián)接替換嗎？上述語句可以用下面的聯(lián)接替換嗎？ 查看沒有參加考試的學(xué)生查看沒有參加考試的學(xué)生 有的子查詢不有的子查詢不能用聯(lián)接替換。這就是典型的例子能用聯(lián)接替換。這就是典型的例子SELECT * FROM Student WHERE StudentNo NOT IN ( SELECT StudentNo FROM Result ) SELECT * FROM Student INNER JOIN Result ON Student.StudentNoResult.StudentNo 不可以不可以回顧與作業(yè)點評3-3下列語句實現(xiàn)了什么

3、功能？下列語句實現(xiàn)了什么功能？ 請使用請使用IN子查詢改寫上述語句？子查詢改寫上述語句？請使用聯(lián)接查詢改寫上述語句請使用聯(lián)接查詢改寫上述語句SELECT * FROM Student WHERE StudentNo IN (SELECT StudentNo FROM Result)SELECT * FROM Student INNER JOIN ResultON Student.StudentNo = Result.StudentNoSELECT * FROM Student WHERE EXISTS ( SELECT * FROM Result WHERE StudentNo = Stude

4、nt.StudentNo ) 查詢參加考試的學(xué)生查詢參加考試的學(xué)生預(yù)習(xí)檢查T-SQL語句如何提交一個事務(wù)？語句如何提交一個事務(wù)？事務(wù)具有的四個特性是什么？事務(wù)具有的四個特性是什么？視圖是虛擬表嗎？視圖是虛擬表嗎？索引的作用是什么？索引的作用是什么？本章任務(wù)模擬實現(xiàn)銀行轉(zhuǎn)帳功能模擬實現(xiàn)銀行轉(zhuǎn)帳功能批量插入學(xué)生考試成績批量插入學(xué)生考試成績辦理畢業(yè)學(xué)生離校手續(xù)辦理畢業(yè)學(xué)生離校手續(xù)查看學(xué)生成績查看學(xué)生成績查看學(xué)生檔案查看學(xué)生檔案本章目標使用事務(wù)保證操縱數(shù)據(jù)的完整性使用事務(wù)保證操縱數(shù)據(jù)的完整性掌握如何創(chuàng)建并使用視圖掌握如何創(chuàng)建并使用視圖為什么需要事務(wù)5-1銀行轉(zhuǎn)賬問題：銀行轉(zhuǎn)賬問題： 假定資金從賬戶假定

5、資金從賬戶A轉(zhuǎn)到賬戶轉(zhuǎn)到賬戶B，至少需要兩步：，至少需要兩步：賬戶賬戶A的資金減少的資金減少然后賬戶然后賬戶B的資金相應(yīng)增加的資金相應(yīng)增加賬戶賬戶A賬戶賬戶B假定張三的賬戶直接轉(zhuǎn)賬假定張三的賬戶直接轉(zhuǎn)賬1000元到李四的賬戶元到李四的賬戶為什么需要事務(wù)5-2CREATE TABLE bank( customerName CHAR(10), -顧客姓名顧客姓名 currentMoney MONEY -當前余額當前余額)GOALTER TABLE bank ADD CONSTRAINT CK_currentMoney CHECK(currentMoney = 1)GOINSERT INTO ban

6、k(customerName, currentMoney) VALUES(張三張三, 1000)INSERT INTO bank(customerName, currentMoney) VALUES(李四李四, 1)創(chuàng)建賬戶表，存放用戶的賬戶信息創(chuàng)建賬戶表，存放用戶的賬戶信息添加約束：根據(jù)銀行規(guī)定，賬戶余添加約束：根據(jù)銀行規(guī)定，賬戶余額不能少于額不能少于1元，否則視為銷戶元，否則視為銷戶 張三開戶，開戶金額為張三開戶，開戶金額為1000元元 ；李四開戶，開戶金額；李四開戶，開戶金額1元元 為什么需要事務(wù)5-3目前兩個賬戶的余額總和為：目前兩個賬戶的余額總和為：1000+1=1001元元 為什么

7、需要事務(wù)5-4模擬實現(xiàn)轉(zhuǎn)賬模擬實現(xiàn)轉(zhuǎn)賬從張三的賬戶轉(zhuǎn)賬從張三的賬戶轉(zhuǎn)賬1000元到李四的賬戶元到李四的賬戶/*-轉(zhuǎn)賬測試：張三轉(zhuǎn)賬轉(zhuǎn)賬測試：張三轉(zhuǎn)賬1000元給李四元給李四-*/-我們可能會這樣編寫語句我們可能會這樣編寫語句-張三的賬戶少張三的賬戶少1000元，李四的賬戶多元，李四的賬戶多1000元元UPDATE bank SET currentMoney = currentMoney - 1000 WHERE customerName = 張三張三UPDATE bank SET currentMoney = currentMoney + 1000 WHERE customerName = 李

8、四李四GO-再次查看轉(zhuǎn)賬后的結(jié)果再次查看轉(zhuǎn)賬后的結(jié)果 SELECT * FROM bankGO請問：請問：執(zhí)行轉(zhuǎn)賬語句后，張三、李四的賬戶執(zhí)行轉(zhuǎn)賬語句后，張三、李四的賬戶余額分別是多少？余額分別是多少？張三的賬戶沒有減少張三的賬戶沒有減少 但李四的賬戶卻多了但李四的賬戶卻多了1000元元 100010012001元元總額多出了總額多出了1000元！元！為什么需要事務(wù)5-5-張三的賬戶減少張三的賬戶減少1000元，李四的賬戶增加元，李四的賬戶增加1000元元UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=張三張三

9、UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四李四GO發(fā)生錯誤的原因發(fā)生錯誤的原因執(zhí)行失敗，張三賬戶余額還是執(zhí)行失敗，張三賬戶余額還是1000元元繼續(xù)往下執(zhí)行：執(zhí)行成功，李四賬戶余額變?yōu)槔^續(xù)往下執(zhí)行：執(zhí)行成功，李四賬戶余額變?yōu)?001元元如何解決呢？使用如何解決呢？使用事務(wù)事務(wù)UPDATE語句違反約束：語句違反約束：余額余額=1元元什么是事務(wù)事務(wù)事務(wù)(TRANSACTION)是作為單個邏輯工作單元是作為單個邏輯工作單元執(zhí)行的一系列操作執(zhí)行的一系列操作多個操作作為一個整體向系統(tǒng)提交，要么都執(zhí)行、多個操作作為

10、一個整體向系統(tǒng)提交，要么都執(zhí)行、要么都不執(zhí)行要么都不執(zhí)行 事務(wù)是一個不可分割的工作邏輯單元事務(wù)是一個不可分割的工作邏輯單元 轉(zhuǎn)賬過程就是一個事務(wù)轉(zhuǎn)賬過程就是一個事務(wù)它需要兩條它需要兩條UPDATE語句來完成，這兩條語句是一個整體語句來完成，這兩條語句是一個整體如果其中任一條出現(xiàn)錯誤，則整個轉(zhuǎn)賬業(yè)務(wù)也應(yīng)取消，兩個賬戶中的如果其中任一條出現(xiàn)錯誤，則整個轉(zhuǎn)賬業(yè)務(wù)也應(yīng)取消，兩個賬戶中的余額應(yīng)恢復(fù)到原來的數(shù)據(jù)，從而確保轉(zhuǎn)賬前和轉(zhuǎn)賬后的余額不變，即余額應(yīng)恢復(fù)到原來的數(shù)據(jù)，從而確保轉(zhuǎn)賬前和轉(zhuǎn)賬后的余額不變，即都是都是1001元元事務(wù)的特性事務(wù)必須具備以下四個屬性，簡稱事務(wù)必須具備以下四個屬性，簡稱ACID

11、屬性：屬性：原子性（原子性（Atomicity）一致性（一致性（Consistency）隔離性隔離性（Isolation）永久性（永久性（Durability）事務(wù)是一個完整的操作事務(wù)是一個完整的操作事務(wù)的各步操作是不可分的（原事務(wù)的各步操作是不可分的（原子的）；要么都執(zhí)行，要么都不子的）；要么都執(zhí)行，要么都不執(zhí)行執(zhí)行當事務(wù)完成時，數(shù)據(jù)必須處于一當事務(wù)完成時，數(shù)據(jù)必須處于一致狀態(tài)致狀態(tài)并發(fā)事務(wù)之間彼此隔離、獨立，并發(fā)事務(wù)之間彼此隔離、獨立，它不應(yīng)以任何方式依賴于或影響它不應(yīng)以任何方式依賴于或影響其他事務(wù)其他事務(wù)事務(wù)完成后，它對數(shù)據(jù)庫的修改事務(wù)完成后，它對數(shù)據(jù)庫的修改被永久保持被永久保持如何創(chuàng)建

12、事務(wù)2-1使用使用SQL語句管理事務(wù)語句管理事務(wù)開始事務(wù)開始事務(wù)提交事務(wù)提交事務(wù)回滾（撤銷）事務(wù)回滾（撤銷）事務(wù)ROLLBACK TRANSACTIONCOMMIT TRANSACTIONBEGIN TRANSACTION如何創(chuàng)建事務(wù)2-2一旦事務(wù)提交或回滾，則事務(wù)結(jié)束一旦事務(wù)提交或回滾，則事務(wù)結(jié)束判斷某條語句執(zhí)行是否出錯：判斷某條語句執(zhí)行是否出錯：使用全局變量使用全局變量ERRORERROR只判斷當前一條只判斷當前一條T-SQL語句執(zhí)行是否有錯語句執(zhí)行是否有錯為了判斷事務(wù)中所有為了判斷事務(wù)中所有T-SQL語句是否有錯，可以對錯誤語句是否有錯，可以對錯誤進行累計進行累計 如：如：事務(wù)可以嵌套事

13、務(wù)可以嵌套SET errorSum=errorSum+ERROR事務(wù)分類顯式事務(wù)顯式事務(wù)用用BEGIN TRANSACTION明確指定事務(wù)的開始明確指定事務(wù)的開始最常用的事務(wù)類型最常用的事務(wù)類型隱性事務(wù)隱性事務(wù)通過設(shè)置通過設(shè)置SET IMPLICIT_TRANSACTIONS ON 語句，語句，將隱性事務(wù)模式設(shè)置為打開將隱性事務(wù)模式設(shè)置為打開其后的其后的T-SQL語句自動啟動一個新事務(wù)語句自動啟動一個新事務(wù)提交或回滾一個事務(wù)后，下一個提交或回滾一個事務(wù)后，下一個 T-SQL 語句又將啟動語句又將啟動一個新事務(wù)一個新事務(wù)自動提交事務(wù)自動提交事務(wù)SQL Server 的默認模式的默認模式每條單獨的

14、每條單獨的 T-SQL 語句視為一個事務(wù)語句視為一個事務(wù)BEGIN TRANSACTION /*-定義變量，用于累計事務(wù)執(zhí)行過程中的錯誤定義變量，用于累計事務(wù)執(zhí)行過程中的錯誤-*/DECLARE errorSum INT SET errorSum=0 -初始化為初始化為0，即無錯誤，即無錯誤/*-轉(zhuǎn)賬：張三的賬戶減少轉(zhuǎn)賬：張三的賬戶減少1000元，李四的賬戶增加元，李四的賬戶增加1000元元*/UPDATE bank SET currentMoney = currentMoney - 1000 WHERE customerName = 張三張三SET errorSum = errorSum +

15、 ERROR -累計是否有錯誤累計是否有錯誤UPDATE bank SET currentMoney = currentMoney + 1000 WHERE customerName = 李四李四SET errorSum = errorSum + ERROR使用事務(wù)解決銀行轉(zhuǎn)賬問題4-1從張三的賬戶轉(zhuǎn)出從張三的賬戶轉(zhuǎn)出1000元，存入李四的賬戶中元，存入李四的賬戶中開始事務(wù)（指定事務(wù)從此處開始，后續(xù)開始事務(wù)（指定事務(wù)從此處開始，后續(xù)的的T-SQL語句都是一個整體）語句都是一個整體）累計是否有錯誤累計是否有錯誤IF errorSum0 -如果如果SQL語句執(zhí)行出錯語句執(zhí)行出錯 BEGIN PRI

16、NT 交易失敗，回滾事務(wù)交易失敗，回滾事務(wù) ROLLBACK TRANSACTION END ELSE BEGIN PRINT 交易成功，提交事務(wù)，寫入硬盤，永久的保存交易成功，提交事務(wù)，寫入硬盤，永久的保存 COMMIT TRANSACTION ENDGOPRINT 查看轉(zhuǎn)賬事務(wù)后的余額查看轉(zhuǎn)賬事務(wù)后的余額SELECT * FROM bank GO 使用事務(wù)解決銀行轉(zhuǎn)賬問題4-2根據(jù)執(zhí)行是否有錯誤，決定根據(jù)執(zhí)行是否有錯誤，決定提交事務(wù)，或撤銷事務(wù)提交事務(wù)，或撤銷事務(wù)如果有錯，則回滾操作，事務(wù)結(jié)束如果有錯，則回滾操作，事務(wù)結(jié)束如果成功，則提交操作，事務(wù)結(jié)束如果成功，則提交操作，事務(wù)結(jié)束從張三的

17、賬戶轉(zhuǎn)出從張三的賬戶轉(zhuǎn)出1000元，存入李四的賬戶中元，存入李四的賬戶中使用事務(wù)解決銀行轉(zhuǎn)賬問題4-3轉(zhuǎn)賬事務(wù)前轉(zhuǎn)賬事務(wù)前轉(zhuǎn)賬事務(wù)過程中轉(zhuǎn)賬事務(wù)過程中轉(zhuǎn)賬事務(wù)結(jié)束后轉(zhuǎn)賬事務(wù)結(jié)束后轉(zhuǎn)賬轉(zhuǎn)賬1000元，轉(zhuǎn)賬失敗的情況元，轉(zhuǎn)賬失敗的情況演示案例演示案例1：利用事務(wù)實現(xiàn)轉(zhuǎn)賬：利用事務(wù)實現(xiàn)轉(zhuǎn)賬1000元元使用事務(wù)解決銀行轉(zhuǎn)賬問題4-4轉(zhuǎn)賬事務(wù)前轉(zhuǎn)賬事務(wù)前轉(zhuǎn)賬事務(wù)過程中轉(zhuǎn)賬事務(wù)過程中轉(zhuǎn)賬事務(wù)結(jié)束后轉(zhuǎn)賬事務(wù)結(jié)束后轉(zhuǎn)賬轉(zhuǎn)賬800元，轉(zhuǎn)賬成功的情況元，轉(zhuǎn)賬成功的情況演示案例演示案例2：利用事務(wù)實現(xiàn)轉(zhuǎn)賬：利用事務(wù)實現(xiàn)轉(zhuǎn)賬800元元指導(dǎo)批量插入學(xué)生考試成績2-1訓(xùn)練要點：訓(xùn)練要點：使用事務(wù)向表中插入多條記錄使用事務(wù)

18、向表中插入多條記錄需求說明：需求說明：批量插入?yún)⒓咏裉炫坎迦雲(yún)⒓咏裉臁癑ava Logic”課程考試的十名課程考試的十名學(xué)生成績學(xué)生成績?nèi)绻斎氲某煽兇笥谌绻斎氲某煽兇笥?00分，將違反約束分，將違反約束講解需求說明講解需求說明指導(dǎo)批量插入學(xué)生考試成績2-2實現(xiàn)思路：實現(xiàn)思路：使用顯式事務(wù)完成批量插入使用顯式事務(wù)完成批量插入10個學(xué)生考試成績的操個學(xué)生考試成績的操作作使用全局變量使用全局變量ERROR判斷插入操作是否成功判斷插入操作是否成功使用使用IF語句判斷語句判斷ERROR值。如果插入成功，提值。如果插入成功，提交事務(wù)；否則回滾事務(wù)交事務(wù)；否則回滾事務(wù)BEGIN TRANSACTION

19、DECLARE errorSum INTSET errorSum=0/*-插入數(shù)據(jù)插入數(shù)據(jù)-*/ INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult) VALUES(10012,1,2009-5-20,102) -分數(shù)違反約束分數(shù)違反約束SET errorSum=errorSum+ERROR IF(errorSum0) -如果有錯誤如果有錯誤 BEGIN PRINT 插入失敗，回滾事務(wù)插入失敗，回滾事務(wù) ROLLBACK TRANSACTION END ELSE BEGIN PRINT 插入成功，提交事務(wù)插入成功，提交事務(wù)

20、 COMMIT TRANSACTION END完成時間：完成時間：20分鐘分鐘練習(xí)辦理畢業(yè)學(xué)生離校手續(xù)需求說明：需求說明：將畢業(yè)學(xué)生的基本信息和考試成績分別保存到歷史將畢業(yè)學(xué)生的基本信息和考試成績分別保存到歷史表中表中提示：提示：使用顯式事務(wù)使用顯式事務(wù)查詢查詢Result表中所有表中所有三年級學(xué)生的考試成績，保存三年級學(xué)生的考試成績，保存到表到表HistoreResult中中刪除刪除Result表中所有表中所有三年級學(xué)生的考試成績?nèi)昙墝W(xué)生的考試成績查詢查詢Student表中所有表中所有三年級的學(xué)生記錄，保存到三年級的學(xué)生記錄，保存到表表HistoryStudent中中刪除刪除Studet表

21、中所有表中所有三年級學(xué)生記錄三年級學(xué)生記錄完成時間：完成時間：20分鐘分鐘BEGIN TRANSACTIONDECLARE errorSum INTSET errorSum=0/*-查詢查詢Result表中所有表中所有三年級學(xué)生的考試成績?nèi)昙墝W(xué)生的考試成績，保存到新表，保存到新表HistoreResult*/SELECT Result.* INTO HistoreResult FROM Result INNER JOIN Student ON Result.StudentNo=Student.StudentNoINNER JOIN Grade ON Grade.GradeId=Student

22、.GradeId WHERE GradeName=三三年級年級SET errorSum=errorSumERROR/*-刪除刪除Result表中所有表中所有三年級學(xué)生的考試成績?nèi)昙墝W(xué)生的考試成績*/DELETE Result FROM Result JOIN Student ON Result.StudentNo=Student.StudentNoINNER JOIN Grade ON Grade.GradeId=Student.GradeId WHERE GradeName=三年級三年級SET errorSum=errorSum+ERROR/*-將將Student表中所有表中所有三年級的學(xué)

23、生記錄三年級的學(xué)生記錄，保存到新表，保存到新表HistoryStudent*/ /*-刪除刪除Studet表中所有表中所有三年級學(xué)生記錄三年級學(xué)生記錄*/ /*-根據(jù)是否有錯誤，確定事務(wù)是提交還是撤銷根據(jù)是否有錯誤，確定事務(wù)是提交還是撤銷-*/共性問題講解n常見調(diào)試問題及解決辦法常見調(diào)試問題及解決辦法n代碼規(guī)范問題代碼規(guī)范問題共性問題集中講解共性問題集中講解為什么需要視圖不同的人員關(guān)注不同的數(shù)據(jù)不同的人員關(guān)注不同的數(shù)據(jù)保證信息的安全性保證信息的安全性雇員雇員 ID姓名姓名薪金薪金職務(wù)職務(wù)績效績效E10004Ian Smith43000技術(shù)技術(shù)BE10005Ricky M.32000營銷營銷A

24、雇員數(shù)據(jù)庫雇員數(shù)據(jù)庫老板老板 出納出納 人力資源人力資源主管主管員工員工拒絕訪問拒絕訪問什么是視圖3-1教師需要的視圖：教師需要的視圖：方便查看學(xué)生的成績方便查看學(xué)生的成績CREATE VIEW vw_Student_Result AS SELECT 姓名姓名=stuName, 學(xué)號學(xué)號=Student.StudentNo, 基于學(xué)生信息表和基于學(xué)生信息表和成績表創(chuàng)建視圖成績表創(chuàng)建視圖班主任需要的視圖：班主任需要的視圖：方便查看學(xué)生的檔案方便查看學(xué)生的檔案什么是視圖3-2視圖是一視圖是一張張?zhí)摂M表虛擬表，表示一張表的部分數(shù)據(jù)或多張表的綜合數(shù)據(jù)表示一張表的部分數(shù)據(jù)或多張表的綜合數(shù)據(jù)其結(jié)構(gòu)和數(shù)據(jù)是

25、建立在對表的查詢基礎(chǔ)上其結(jié)構(gòu)和數(shù)據(jù)是建立在對表的查詢基礎(chǔ)上視圖中不存放數(shù)據(jù)視圖中不存放數(shù)據(jù)數(shù)據(jù)存放在視圖所引用的原始表中數(shù)據(jù)存放在視圖所引用的原始表中一個原始表，根據(jù)不同用戶的不同需求，可以一個原始表，根據(jù)不同用戶的不同需求，可以創(chuàng)建不同的創(chuàng)建不同的視圖視圖什么是視圖3-3視圖的用途視圖的用途篩選表篩選表中中的行的行防止未經(jīng)許可的用戶訪問敏感數(shù)據(jù)防止未經(jīng)許可的用戶訪問敏感數(shù)據(jù)降低數(shù)據(jù)庫的復(fù)雜程度降低數(shù)據(jù)庫的復(fù)雜程度將將多個多個物理數(shù)據(jù)庫抽象為一個邏輯數(shù)據(jù)庫物理數(shù)據(jù)庫抽象為一個邏輯數(shù)據(jù)庫如何創(chuàng)建視圖3-1使用管理器創(chuàng)建視圖：名稱：使用管理器創(chuàng)建視圖：名稱：vw_Student_Resultvw_

26、Student_Result操作演示：使用管理器創(chuàng)建視圖操作演示：使用管理器創(chuàng)建視圖如何創(chuàng)建視圖3-2使用使用T-SQL語句創(chuàng)建視圖語句創(chuàng)建視圖CREATE VIEW view_name AS 使用使用T-SQL語句刪除視圖語句刪除視圖IF EXISTS (SELECT * FROM sysobjects WHERE name = view_name) DROP VIEW view_name 使用使用T-SQL語句查看視圖語句查看視圖SELECT * FROM view_name 需要使用一對需要使用一對單引號括起單引號括起如何創(chuàng)建視圖3-3創(chuàng)建方便創(chuàng)建方便教師教師查看成績的視圖查看成績的視圖

27、IF EXISTS (SELECT * FROM sysobjects WHERE name=vw_student_result ) DROP VIEW vw_student_resultGOCREATE VIEW vw_student_resultAS GOSELECT * FROM vw_student_result檢測是否存在檢測是否存在創(chuàng)建視圖創(chuàng)建視圖查看視圖查看視圖刪除視圖刪除視圖演示案例演示案例3：使用：使用SQL創(chuàng)建、執(zhí)行視圖創(chuàng)建、執(zhí)行視圖使用視圖注意事項視圖中可以使用多個表視圖中可以使用多個表一個視圖可以嵌套另一個視圖一個視圖可以嵌套另一個視圖 視圖定義中的視圖定義中的 SEL

28、ECT 語句不能包括下列內(nèi)容：語句不能包括下列內(nèi)容： ORDER BY 子句，除非在子句，除非在 SELECT 語句的選擇列語句的選擇列表中也有一個表中也有一個 TOP 子句子句INTO 關(guān)鍵字關(guān)鍵字引用臨時表或表變量引用臨時表或表變量 指導(dǎo)查看學(xué)生各學(xué)期總成績2-1訓(xùn)練要點：訓(xùn)練要點：使用視圖獲取多表中數(shù)據(jù)使用視圖獲取多表中數(shù)據(jù)需求說明：需求說明：統(tǒng)計每個學(xué)生各學(xué)期（大一、大二、大三）所有課統(tǒng)計每個學(xué)生各學(xué)期（大一、大二、大三）所有課程的總成績程的總成績講解需求說明講解需求說明指導(dǎo)查看學(xué)生各學(xué)期總成績2-2實現(xiàn)思路：實現(xiàn)思路：創(chuàng)建視圖創(chuàng)建視圖編寫代碼查詢每個學(xué)生各學(xué)期參加考試的總成績，編寫代

29、碼查詢每個學(xué)生各學(xué)期參加考試的總成績，每門課成績以該生參加的最后一次考試為準每門課成績以該生參加的最后一次考試為準編譯執(zhí)行視圖，獲得學(xué)生各學(xué)期的總成績編譯執(zhí)行視圖，獲得學(xué)生各學(xué)期的總成績CREATE VIEW vw_student_result_infoAS SELECT 姓名姓名=StudentName,學(xué)號學(xué)號=Student.StudentNo, 聯(lián)系電話聯(lián)系電話=Phone,學(xué)期學(xué)期=GradeName,成績成績=Total FROM Student LEFT OUTER JOIN ( SELECT r.StudentNo,GradeName,SUM(StudentResult) To

30、tal FROM Result r INNER JOIN ( SELECT StudentNo,SubjectNo,MAX(ExamDate) ExamDate FROM Result GROUP BY StudentNo,SubjectNo) tmp ON r.ExamDate=tmp.ExamDate AND r.SubjectNo = tmp.SubjectNo AND r.StudentNo = tmp.StudentNo INNER JOIN Subject sub ON sub.SubjectNo = r.SubjectNo INNER JOIN Grade g ON g.Grad

31、eId = sub.GradeId GROUP By r.StudentNo,GradeName ) TmpResult2 ON Student.StudentNo = TmpResult2.StudentNo GROUP BY StudentName,Student.StudentNo,Phone,GradeName,TotalGOSELECT * FROM vw_student_result_info完成時間：完成時間：20分鐘分鐘SQL Server的安全模型數(shù)據(jù)庫數(shù)據(jù)庫 1數(shù)據(jù)庫數(shù)據(jù)庫 2數(shù)據(jù)庫數(shù)據(jù)庫 3表表 1表表 2表表 1表表 2表表 1表表 2增刪增刪改查改查增刪增刪改查改查增

32、刪增刪改查改查增刪增刪改查改查增刪增刪改查改查增刪增刪改查改查數(shù)據(jù)庫用戶數(shù)據(jù)庫用戶數(shù)據(jù)庫用戶數(shù)據(jù)庫用戶數(shù)據(jù)庫用戶數(shù)據(jù)庫用戶登錄帳號登錄帳號SQLserver三層安全管理三層安全管理登錄方式登錄驗證有兩種方式：登錄驗證有兩種方式：SQL身份驗證：適合于非身份驗證：適合于非windows平臺的用戶或平臺的用戶或Internet用戶，用戶， 需要提供帳戶和密碼需要提供帳戶和密碼Windows身份驗證：適合于身份驗證：適合于windows平臺用戶，不需平臺用戶，不需要提供密碼，和要提供密碼，和windows集成驗證集成驗證登錄帳戶相應(yīng)有兩種：登錄帳戶相應(yīng)有兩種：SQL 帳戶帳戶和和Windows帳戶帳

33、戶 創(chuàng)建登錄添加添加 Windows登錄帳戶登錄帳戶 EXEC sp_grantlogin jbtrainingS26301 添加添加 SQL登錄帳戶登錄帳戶 EXEC sp_addlogin zhangsan, 1234演示創(chuàng)建登錄演示創(chuàng)建登錄EXEC表示調(diào)用存儲過程，表示調(diào)用存儲過程，存儲過程類似存儲過程類似C語言的函數(shù)語言的函數(shù)內(nèi)置的系統(tǒng)管理員內(nèi)置的系統(tǒng)管理員 帳戶帳戶sa密碼默認為空，建議修改密碼密碼默認為空，建議修改密碼 域名域名用戶名用戶名用戶名用戶名,密碼密碼創(chuàng)建登錄創(chuàng)建數(shù)據(jù)庫用戶創(chuàng)建數(shù)據(jù)庫用戶需要調(diào)用系統(tǒng)存儲過程創(chuàng)建數(shù)據(jù)庫用戶需要調(diào)用系統(tǒng)存儲過程sp_grantdbaccess

34、sp_grantdbaccess，其用法為：，其用法為：EXEC sp_grantdbaccess 登錄帳戶名登錄帳戶名，數(shù)據(jù)庫用戶名數(shù)據(jù)庫用戶名 其中，其中，“數(shù)據(jù)庫用戶數(shù)據(jù)庫用戶“為可選參數(shù)，默認為登錄帳戶，為可選參數(shù)，默認為登錄帳戶，即數(shù)據(jù)庫用戶默認和登錄帳戶同名。即數(shù)據(jù)庫用戶默認和登錄帳戶同名。USE stuDBGOEXEC sp_grantdbaccess jbtrainingS26301, S26301DBUser EXEC sp_grantdbaccess zhangsan, zhangsanDBUser在在stuDB數(shù)據(jù)庫中添加兩個用戶數(shù)據(jù)庫中添加兩個用戶創(chuàng)建數(shù)據(jù)庫用戶系統(tǒng)內(nèi)置

35、的數(shù)據(jù)庫用戶系統(tǒng)內(nèi)置的數(shù)據(jù)庫用戶dbo 用戶用戶表示數(shù)據(jù)庫的所有者（表示數(shù)據(jù)庫的所有者（DB Owner）無法刪除無法刪除 dbo 用戶，此用戶始終出現(xiàn)在每個數(shù)據(jù)庫中用戶，此用戶始終出現(xiàn)在每個數(shù)據(jù)庫中 guest 用戶用戶適用于沒有數(shù)據(jù)庫用戶的登錄帳號訪問適用于沒有數(shù)據(jù)庫用戶的登錄帳號訪問每個數(shù)據(jù)庫可有也可刪除每個數(shù)據(jù)庫可有也可刪除向數(shù)據(jù)庫用戶授權(quán) EmployeesEmployeeID123LastNameDavolioFullerLeverling FirstName NancyAndrewJanetReportsTo22 .delete FROM EmployeesSELECT * FR

36、OM Employees權(quán)限的類型授權(quán)的語法為：授權(quán)的語法為：GRANT 權(quán)限權(quán)限 ON 表名表名 TO 數(shù)據(jù)庫用戶數(shù)據(jù)庫用戶USE stuDBGO/*-為為zhangsanDBUser分配對表分配對表stuInfo的的select, insert, update權(quán)限權(quán)限-*/GRANT select, insert, update ON stuInfo TO zhangsanDBUser /*-為為S26301DBUser分配建表的權(quán)限分配建表的權(quán)限-*/GRANT create table TO S26301DBUser權(quán)限的類型沒有授予沒有授予delete權(quán)限權(quán)限共性問題講解n常見調(diào)試問

37、題及解決辦法常見調(diào)試問題及解決辦法n代碼規(guī)范問題代碼規(guī)范問題共性問題集中講解共性問題集中講解什么是索引3-1漢語字典中的漢字按漢語字典中的漢字按頁頁存放，一般都有漢語拼存放，一般都有漢語拼音目錄（音目錄（索引索引）、偏旁部首目錄等）、偏旁部首目錄等我們可以根據(jù)拼音或偏旁部首，我們可以根據(jù)拼音或偏旁部首，快速快速查找某個查找某個字詞字詞什么是索引3-2Indexes Use Key Values to Locate Data（根據(jù)索引鍵查找定位數(shù)據(jù)行）（根據(jù)索引鍵查找定位數(shù)據(jù)行） Data Pages（數(shù)據(jù)頁）（數(shù)據(jù)頁）Index Pages（索引頁）（索引頁） 什么是索引3-3SQL Serv

38、er中的數(shù)據(jù)也是按頁存放中的數(shù)據(jù)也是按頁存放索引：是索引：是SQL Server編排數(shù)據(jù)的內(nèi)部方法。它編排數(shù)據(jù)的內(nèi)部方法。它為為SQL Server提供一種方法來編排查詢數(shù)據(jù)提供一種方法來編排查詢數(shù)據(jù) 索引頁：數(shù)據(jù)庫中存儲索引的數(shù)據(jù)頁；索引頁索引頁：數(shù)據(jù)庫中存儲索引的數(shù)據(jù)頁；索引頁類似于漢語字（詞）典中按拼音或筆畫排序的類似于漢語字（詞）典中按拼音或筆畫排序的目錄頁目錄頁索引的作用：通過使用索引，可以大大提高數(shù)索引的作用：通過使用索引，可以大大提高數(shù)據(jù)庫的檢索速度，改善數(shù)據(jù)庫性能據(jù)庫的檢索速度，改善數(shù)據(jù)庫性能索引類型唯一索引：唯一索引不允許兩行具有相同的索引唯一索引：唯一索引不允許兩行具有相同

39、的索引值值主鍵索引：為表定義一個主鍵將自動創(chuàng)建主鍵索主鍵索引：為表定義一個主鍵將自動創(chuàng)建主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引引，主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每個值是唯一的，并且不能為空要求主鍵中的每個值是唯一的，并且不能為空聚集索引聚集索引(Clustered)：表中各行的物理順序與鍵：表中各行的物理順序與鍵值的邏輯（索引）順序相同，每個表只能有一個值的邏輯（索引）順序相同，每個表只能有一個非聚集索引非聚集索引(Non-clustered)：非聚集索引指定表：非聚集索引指定表的邏輯順序。數(shù)據(jù)存儲在一個位置，索引存儲在的邏輯順序。數(shù)據(jù)存儲在一個位置，索引存儲在另

40、一個位置，索引中包含指向數(shù)據(jù)存儲位置的指另一個位置，索引中包含指向數(shù)據(jù)存儲位置的指針?？梢杂卸鄠€，小于針?？梢杂卸鄠€，小于249個個如何創(chuàng)建索引3-1使用使用SQL Server Management Studio創(chuàng)建索創(chuàng)建索引引操作演示：使用操作演示：使用SQLServ管理器創(chuàng)建索引管理器創(chuàng)建索引如何創(chuàng)建索引3-2使用使用T-SQL語句創(chuàng)建索引語句創(chuàng)建索引CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_name ON table_name (column_name) WITH FILLFACTOR=x唯一索引唯一索引聚集索引或非聚集索引聚集索

41、引或非聚集索引填充因子（系數(shù)）填充因子（系數(shù)）:指定一個指定一個0100之之間的值，表示索引頁填充的百分比間的值，表示索引頁填充的百分比使用使用T-SQL語句刪除索引語句刪除索引DROP INDEX table_name.index_name 刪除表時，該表的所有索引同時會被刪除刪除表時，該表的所有索引同時會被刪除 表名和索引名稱之間，用表名和索引名稱之間，用“.”分隔分隔如何創(chuàng)建索引3-3在在Student表的表的StudentName列創(chuàng)建非聚集索列創(chuàng)建非聚集索引引USE MySchoolGO/*-檢測是否存在該索引檢測是否存在該索引(索引存放在系統(tǒng)表索引存放在系統(tǒng)表sysindexes中

42、中)-*/IF EXISTS (SELECT name FROM sysindexes WHERE name = IX_Student_StudentName)DROP INDEX Student.IX_Student_StudentName -刪除索引刪除索引GO/*-學(xué)生姓名列創(chuàng)建非聚集索引：填充因子為學(xué)生姓名列創(chuàng)建非聚集索引：填充因子為30-*/CREATE NONCLUSTERED INDEX IX_Student_StudentName ON Student(StudentName) WITH FILLFACTOR = 30GO檢測索引是否存在：檢測索引是否存在：索引存放在系統(tǒng)表索引

43、存放在系統(tǒng)表sysindexes中中演示示例演示示例4：使用：使用T-SQL創(chuàng)建索引創(chuàng)建索引按指定的索引查詢數(shù)據(jù)使用索引查詢使用索引查詢“李李”姓的學(xué)生信息姓的學(xué)生信息/*-指定按索引：指定按索引：IX_Student_StudentName查詢查詢-*/SELECT * FROM StudentWITH (INDEX=IX_Student_StudentName)WHERE StudentName LIKE 李李%演示示例演示示例5：使用索引查詢數(shù)據(jù)：使用索引查詢數(shù)據(jù)索引的優(yōu)缺點優(yōu)點優(yōu)點加快訪問速度加快訪問速度加強行的唯一性加強行的唯一性缺點缺點帶索引的表在數(shù)據(jù)庫中需要更多的存儲空間帶索引的

44、表在數(shù)據(jù)庫中需要更多的存儲空間操縱數(shù)據(jù)的命令需要更長的處理時間，因為它們需要操縱數(shù)據(jù)的命令需要更長的處理時間，因為它們需要對索引進行更新對索引進行更新創(chuàng)建索引的指導(dǎo)原則按照下列標準選擇建立索引的列按照下列標準選擇建立索引的列頻繁搜索的列頻繁搜索的列經(jīng)常用作查詢選擇的列經(jīng)常用作查詢選擇的列經(jīng)常排序、分組的列經(jīng)常排序、分組的列經(jīng)常用作聯(lián)接的列（主鍵經(jīng)常用作聯(lián)接的列（主鍵/外鍵）外鍵）請不要使用下面的列創(chuàng)建索引請不要使用下面的列創(chuàng)建索引僅包含幾個不同值的列僅包含幾個不同值的列表中僅包含幾行表中僅包含幾行使用索引時注意事項查詢時減少使用查詢時減少使用*返回全部列，不要返回不需要返回全部列，不要返回不需要的列的列索引應(yīng)該盡量小，在字節(jié)數(shù)小的列上建立索引索引應(yīng)該盡量小，在字節(jié)數(shù)小的列上建立索引WHERE子句中有多個條件表達式時，包含索引子句中有多個條件表達式時，包含索引列的表達式應(yīng)置于其他條件表達式之前列的表達式應(yīng)置于其他條件表達式之前避免在避免在ORDER BY子句中使用表達式子句中使用表達式根據(jù)業(yè)務(wù)數(shù)據(jù)發(fā)生頻率，定期重新生成或重新根據(jù)業(yè)務(wù)數(shù)據(jù)發(fā)生頻率，定期重新生成或重新組織索引，進行碎片整理組織索引，進行碎片整理 查看索引 使用系統(tǒng)存儲過程使用系統(tǒng)