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

1、事務(wù)、視圖和索引事務(wù)、視圖和索引單元單元7 7回顧與作業(yè)點(diǎn)評(píng)3-1下面的下面的SQL語(yǔ)句有錯(cuò)嗎？實(shí)現(xiàn)了什么功能？語(yǔ)句有錯(cuò)嗎？實(shí)現(xiàn)了什么功能？ 上述語(yǔ)句可以用下面的聯(lián)接替換嗎？上述語(yǔ)句可以用下面的聯(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è)點(diǎn)評(píng)3-2下列

2、語(yǔ)句實(shí)現(xiàn)了什么功能？下列語(yǔ)句實(shí)現(xiàn)了什么功能？上述語(yǔ)句可以用下面的聯(lián)接替換嗎？上述語(yǔ)句可以用下面的聯(lián)接替換嗎？ 查看沒(méi)有參加考試的學(xué)生查看沒(méi)有參加考試的學(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è)點(diǎn)評(píng)3-3下列語(yǔ)句實(shí)現(xiàn)了什么

3、功能？下列語(yǔ)句實(shí)現(xiàn)了什么功能？ 請(qǐng)使用請(qǐng)使用IN子查詢改寫(xiě)上述語(yǔ)句？子查詢改寫(xiě)上述語(yǔ)句？請(qǐng)使用聯(lián)接查詢改寫(xiě)上述語(yǔ)句請(qǐng)使用聯(lián)接查詢改寫(xiě)上述語(yǔ)句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(mén)-SQL語(yǔ)句如何提交一個(gè)事務(wù)？語(yǔ)句如何提交一個(gè)事務(wù)？事務(wù)具有的四個(gè)特性是什么？事務(wù)具有的四個(gè)特性是什么？視圖是虛擬表嗎？視圖是虛擬表嗎？索引的作用是什么？索引的作用是什么？本章任務(wù)模擬實(shí)現(xiàn)銀行轉(zhuǎn)帳功能模擬實(shí)現(xiàn)銀行轉(zhuǎn)帳功能批量插入學(xué)生考試成績(jī)批量插入學(xué)生考試成績(jī)辦理畢業(yè)學(xué)生離校手續(xù)辦理畢業(yè)學(xué)生離校手續(xù)查看學(xué)生成績(jī)查看學(xué)生成績(jī)查看學(xué)生檔案查看學(xué)生檔案本章目標(biāo)使用事務(wù)保證操縱數(shù)據(jù)的完整性使用事務(wù)保證操縱數(shù)據(jù)的完整性掌握如何創(chuàng)建并使用視圖掌握如何創(chuàng)建并使用視圖為什么需要事務(wù)5-1銀行轉(zhuǎn)賬問(wèn)題：銀行轉(zhuǎn)賬問(wè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 -當(dāng)前余額當(dāng)前余額)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元，否則視為銷戶元，否則視為銷戶 張三開(kāi)戶，開(kāi)戶金額為張三開(kāi)戶，開(kāi)戶金額為1000元元 ；李四開(kāi)戶，開(kāi)戶金額；李四開(kāi)戶，開(kāi)戶金額1元元 為什么需要事務(wù)5-3目前兩個(gè)賬戶的余額總和為：目前兩個(gè)賬戶的余額總和為：1000+1=1001元元 為什么

7、需要事務(wù)5-4模擬實(shí)現(xiàn)轉(zhuǎn)賬模擬實(shí)現(xiàn)轉(zhuǎn)賬從張三的賬戶轉(zhuǎn)賬從張三的賬戶轉(zhuǎn)賬1000元到李四的賬戶元到李四的賬戶/*-轉(zhuǎn)賬測(cè)試：張三轉(zhuǎn)賬轉(zhuǎn)賬測(cè)試：張三轉(zhuǎn)賬1000元給李四元給李四-*/-我們可能會(huì)這樣編寫(xiě)語(yǔ)句我們可能會(huì)這樣編寫(xiě)語(yǔ)句-張三的賬戶少?gòu)埲馁~戶少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請(qǐng)問(wèn)：請(qǐng)問(wèn)：執(zhí)行轉(zhuǎn)賬語(yǔ)句后，張三、李四的賬戶執(zhí)行轉(zhuǎn)賬語(yǔ)句后，張三、李四的賬戶余額分別是多少？余額分別是多少？張三的賬戶沒(méi)有減少?gòu)埲馁~戶沒(méi)有減少 但李四的賬戶卻多了但李四的賬戶卻多了1000元元 100010012001元元總額多出了總額多出了1000元！元！為什么需要事務(wù)5-5-張三的賬戶減少?gòu)埲馁~戶減少1000元，李四的賬戶增加元，李四的賬戶增加1000元元UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=張三張三

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

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

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

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

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

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

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

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

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

18、向表中插入多條記錄需求說(shuō)明：需求說(shuō)明：批量插入?yún)⒓咏裉炫坎迦雲(yún)⒓咏裉臁癑ava Logic”課程考試的十名課程考試的十名學(xué)生成績(jī)學(xué)生成績(jī)?nèi)绻斎氲某煽?jī)大于如果輸入的成績(jī)大于100分，將違反約束分，將違反約束講解需求說(shuō)明講解需求說(shuō)明指導(dǎo)批量插入學(xué)生考試成績(jī)2-2實(shí)現(xiàn)思路：實(shí)現(xiàn)思路：使用顯式事務(wù)完成批量插入使用顯式事務(wù)完成批量插入10個(gè)學(xué)生考試成績(jī)的操個(gè)學(xué)生考試成績(jī)的操作作使用全局變量使用全局變量ERROR判斷插入操作是否成功判斷插入操作是否成功使用使用IF語(yǔ)句判斷語(yǔ)句判斷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) -分?jǐn)?shù)違反約束分?jǐn)?shù)違反約束SET errorSum=errorSum+ERROR IF(errorSum0) -如果有錯(cuò)誤如果有錯(cuò)誤 BEGIN PRINT 插入失敗，回滾事務(wù)插入失敗，回滾事務(wù) ROLLBACK TRANSACTION END ELSE BEGIN PRINT 插入成功，提交事務(wù)插入成功，提交事務(wù)

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

21、中所有表中所有三年級(jí)學(xué)生記錄三年級(jí)學(xué)生記錄完成時(shí)間：完成時(shí)間：20分鐘分鐘BEGIN TRANSACTIONDECLARE errorSum INTSET errorSum=0/*-查詢查詢Result表中所有表中所有三年級(jí)學(xué)生的考試成績(jī)?nèi)昙?jí)學(xué)生的考試成績(jī)，保存到新表，保存到新表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=三三年級(jí)年級(jí)SET errorSum=errorSumERROR/*-刪除刪除Result表中所有表中所有三年級(jí)學(xué)生的考試成績(jī)?nèi)昙?jí)學(xué)生的考試成績(jī)*/DELETE Result FROM Result JOIN Student ON Result.StudentNo=Student.StudentNoINNER JOIN Grade ON Grade.GradeId=Student.GradeId WHERE GradeName=三年級(jí)三年級(jí)SET errorSum=errorSum+ERROR/*-將將Student表中所有表中所有三年級(jí)的學(xué)

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

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

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

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

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檢測(cè)是否存在檢測(cè)是否存在創(chuàng)建視圖創(chuàng)建視圖查看視圖查看視圖刪除視圖刪除視圖演示案例演示案例3：使用：使用SQL創(chuàng)建、執(zhí)行視圖創(chuàng)建、執(zhí)行視圖使用視圖注意事項(xiàng)視圖中可以使用多個(gè)表視圖中可以使用多個(gè)表一個(gè)視圖可以嵌套另一個(gè)視圖一個(gè)視圖可以嵌套另一個(gè)視圖 視圖定義中的視圖定義中的 SEL

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

29、碼查詢每個(gè)學(xué)生各學(xué)期參加考試的總成績(jī)，每門(mén)課成績(jī)以該生參加的最后一次考試為準(zhǔn)每門(mén)課成績(jī)以該生參加的最后一次考試為準(zhǔn)編譯執(zhí)行視圖，獲得學(xué)生各學(xué)期的總成績(jī)編譯執(zhí)行視圖，獲得學(xué)生各學(xué)期的總成績(jī)CREATE VIEW vw_student_result_infoAS SELECT 姓名姓名=StudentName,學(xué)號(hào)學(xué)號(hào)=Student.StudentNo, 聯(lián)系電話聯(lián)系電話=Phone,學(xué)期學(xué)期=GradeName,成績(jī)成績(jī)=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完成時(shí)間：完成時(shí)間：20分鐘分鐘SQL Server的安全模型數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) 1數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) 2數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) 3表表 1表表 2表表 1表表 2表表 1表表 2增刪增刪改查改查增刪增刪改查改查增

32、刪增刪改查改查增刪增刪改查改查增刪增刪改查改查增刪增刪改查改查數(shù)據(jù)庫(kù)用戶數(shù)據(jù)庫(kù)用戶數(shù)據(jù)庫(kù)用戶數(shù)據(jù)庫(kù)用戶數(shù)據(jù)庫(kù)用戶數(shù)據(jù)庫(kù)用戶登錄帳號(hào)登錄帳號(hào)SQLserver三層安全管理三層安全管理登錄方式登錄驗(yàn)證有兩種方式：登錄驗(yàn)證有兩種方式：SQL身份驗(yàn)證：適合于非身份驗(yàn)證：適合于非windows平臺(tái)的用戶或平臺(tái)的用戶或Internet用戶，用戶， 需要提供帳戶和密碼需要提供帳戶和密碼Windows身份驗(yàn)證：適合于身份驗(yàn)證：適合于windows平臺(tái)用戶，不需平臺(tái)用戶，不需要提供密碼，和要提供密碼，和windows集成驗(yàn)證集成驗(yàn)證登錄帳戶相應(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)用存儲(chǔ)過(guò)程，表示調(diào)用存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程類似存儲(chǔ)過(guò)程類似C語(yǔ)言的函數(shù)語(yǔ)言的函數(shù)內(nèi)置的系統(tǒng)管理員內(nèi)置的系統(tǒng)管理員 帳戶帳戶sa密碼默認(rèn)為空，建議修改密碼密碼默認(rèn)為空，建議修改密碼 域名域名用戶名用戶名用戶名用戶名,密碼密碼創(chuàng)建登錄創(chuàng)建數(shù)據(jù)庫(kù)用戶創(chuàng)建數(shù)據(jù)庫(kù)用戶需要調(diào)用系統(tǒng)存儲(chǔ)過(guò)程創(chuàng)建數(shù)據(jù)庫(kù)用戶需要調(diào)用系統(tǒng)存儲(chǔ)過(guò)程sp_grantdbaccess

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

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

36、OM Employees權(quán)限的類型授權(quán)的語(yǔ)法為：授權(quán)的語(yǔ)法為：GRANT 權(quán)限權(quán)限 ON 表名表名 TO 數(shù)據(jù)庫(kù)用戶數(shù)據(jù)庫(kù)用戶USE stuDBGO/*-為為zhangsanDBUser分配對(duì)表分配對(duì)表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)限的類型沒(méi)有授予沒(méi)有授予delete權(quán)限權(quán)限共性問(wèn)題講解n常見(jiàn)調(diào)試問(wèn)

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

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

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

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

41、引或非聚集索引填充因子（系數(shù)）填充因子（系數(shù)）:指定一個(gè)指定一個(gè)0100之之間的值，表示索引頁(yè)填充的百分比間的值，表示索引頁(yè)填充的百分比使用使用T-SQL語(yǔ)句刪除索引語(yǔ)句刪除索引DROP INDEX table_name.index_name 刪除表時(shí)，該表的所有索引同時(shí)會(huì)被刪除刪除表時(shí)，該表的所有索引同時(shí)會(huì)被刪除 表名和索引名稱之間，用表名和索引名稱之間，用“.”分隔分隔如何創(chuàng)建索引3-3在在Student表的表的StudentName列創(chuàng)建非聚集索列創(chuàng)建非聚集索引引USE MySchoolGO/*-檢測(cè)是否存在該索引檢測(cè)是否存在該索引(索引存放在系統(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檢測(cè)索引是否存在：檢測(cè)索引是否存在：索引存放在系統(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)缺點(diǎn)優(yōu)點(diǎn)優(yōu)點(diǎn)加快訪問(wèn)速度加快訪問(wèn)速度加強(qiáng)行的唯一性加強(qiáng)行的唯一性缺點(diǎn)缺點(diǎn)帶索引的表在數(shù)據(jù)庫(kù)中需要更多的存儲(chǔ)空間帶索引的

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