sql server數(shù)據(jù)庫(kù)設(shè)計(jì)和高級(jí)查詢(xún)advance理論部分教學(xué)tp5v1

1、第五章事務(wù)、索引和視圖回顧下列T-SQL語(yǔ)句有錯(cuò)嗎？實(shí)現(xiàn)了什么功能？ SELECT * FROM stuInfo WHERE stuNo IN (SELECT * FROM stuMarks) 上述語(yǔ)句可以用下面的連接替換嗎？ SELECT * FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo下列語(yǔ)句實(shí)現(xiàn)了什么功能？SELECT * FROM stuInfo WHERE stuNo NOT IN (SELECT stuNo FROM stuMarks) 上述語(yǔ)句可以用下面的連接替換嗎？SELECT * FROM s

2、tuInfo INNER JOIN stuMarks ON stuInfo.stuNostuMarks.stuNo SELECT stuNo FROM stuMarks 可 以 查看沒(méi)有參加考試的學(xué)員 有的子查詢(xún)不能用連接替換。這就是典型的例子回顧下列語(yǔ)句實(shí)現(xiàn)了什么功能？ SELECT * FROM stuInfo WHERE EXISTS(SELECT * FROM stuMarks WHERE stuNo=stuInfo.stuNo)請(qǐng)使用IN子查詢(xún)改寫(xiě)上述語(yǔ)句？請(qǐng)使用連接改寫(xiě)上述語(yǔ)句 查詢(xún)參加考試的學(xué)員SELECT * FROM stuInfo WHERE stuNo IN (SELEC

3、T stuNo FROM stuMarks)SELECT * FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo目標(biāo)理解事務(wù)的概念掌握如何創(chuàng)建事務(wù)掌握如何創(chuàng)建索引掌握如何創(chuàng)建并使用視圖為什么需要事務(wù)銀行轉(zhuǎn)賬 例如，銀行轉(zhuǎn)賬問(wèn)題： 假定資金從賬戶(hù)A轉(zhuǎn)到賬戶(hù)B，至少需要兩步：賬戶(hù)A的資金減少然后賬戶(hù)B的資金相應(yīng)增加 賬戶(hù)A賬戶(hù)B假定張三的賬戶(hù)直接轉(zhuǎn)賬1000元到李四的賬戶(hù)為什么需要事務(wù)CREATE TABLE bank( customerName CHAR(10), -顧客姓名 currentMoney MONEY -當(dāng)

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

5、什么需要事務(wù)模擬實(shí)現(xiàn)轉(zhuǎn)賬 ： 從張三的賬戶(hù)轉(zhuǎn)賬1000元到李四的賬戶(hù)/*-轉(zhuǎn)賬測(cè)試：張三轉(zhuǎn)賬1000元給李四-*/-我們可能會(huì)這樣這樣編寫(xiě)語(yǔ)句-張三的賬戶(hù)少1000元，李四的賬戶(hù)多1000元UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=張三UPDATE bank SET currentMoney=currentMoney WHERE customerName=李四GO-再次查看轉(zhuǎn)賬后的結(jié)果。 SELECT * FROM bankGO請(qǐng)問(wèn)：執(zhí)行轉(zhuǎn)賬語(yǔ)句后，張三、李四的賬戶(hù)余額為多少？張三的賬戶(hù)沒(méi)有減少 但李四的

6、賬戶(hù)卻多了1000元 100010012001元 總額多出了1000元！為什么需要事務(wù)-張三的賬戶(hù)減少1000元，李四的賬戶(hù)增加1000元UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=張三UPDATE bank SET currentMoney=currentMoney WHERE customerName=李四GO錯(cuò)誤原因分析：UPDATE語(yǔ)句違反約束：余額=1元執(zhí)行失敗，所以張三還是1000元繼續(xù)往下執(zhí)行：執(zhí)行成功，所以李四變?yōu)?001元如何解決呢？使用事務(wù)什么是事務(wù)事務(wù)(TRANSACTION)是作為單

7、個(gè)邏輯工作單元執(zhí)行的一系列操作這些操作作為一個(gè)整體一起向系統(tǒng)提交，要么都執(zhí)行、要么都不執(zhí)行 事務(wù)是一個(gè)不可分割的工作邏輯單元 轉(zhuǎn)賬過(guò)程就是一個(gè)事務(wù)。它需要兩條UPDATE語(yǔ)句來(lái)完成，這兩條語(yǔ)句是一個(gè)整體，如果其中任一條出現(xiàn)錯(cuò)誤，則整個(gè)轉(zhuǎn)賬業(yè)務(wù)也應(yīng)取消，兩個(gè)賬戶(hù)中的余額應(yīng)恢復(fù)到原來(lái)的數(shù)據(jù)，從而確保轉(zhuǎn)賬前和轉(zhuǎn)賬后的余額不變，即都是1001元。事務(wù)的特性事務(wù)必須具備以下四個(gè)屬性，簡(jiǎn)稱(chēng)ACID 屬性：原子性（Atomicity）：事務(wù)是一個(gè)完整的操作。事務(wù)的各步操作是不可分的（原子的）；要么都執(zhí)行，要么都不執(zhí)行一致性（Consistency）：當(dāng)事務(wù)完成時(shí)，數(shù)據(jù)必須處于一致?tīng)顟B(tài)隔離性（Isolatio

8、n）：對(duì)數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的，這表明事務(wù)必須是獨(dú)立的，它不應(yīng)以任何方式依賴(lài)于或影響其他事務(wù)永久性（Durability）：事務(wù)完成后，它對(duì)數(shù)據(jù)庫(kù)的修改被永久保持，事務(wù)日志能夠保持事務(wù)的永久性如何創(chuàng)建事務(wù)T-SQL使用下列語(yǔ)句來(lái)管理事務(wù)：開(kāi)始事務(wù)：BEGIN TRANSACTION提交事務(wù)：COMMIT TRANSACTION回滾（撤銷(xiāo)）事務(wù)：ROLLBACK TRANSACTION一旦事務(wù)提交或回滾，則事務(wù)結(jié)束。判斷某條語(yǔ)句執(zhí)行是否出錯(cuò)：使用全局變量ERROR；ERROR只能判斷當(dāng)前一條T-SQL語(yǔ)句執(zhí)行是否有錯(cuò)，為了判斷事務(wù)中所有T-SQL語(yǔ)句是否有錯(cuò)，我們需要對(duì)錯(cuò)誤進(jìn)行累

9、計(jì)； 如： SET errorSum=errorSum+error如何創(chuàng)建事務(wù)了解事務(wù)的分類(lèi)：顯示事務(wù)：用BEGIN TRANSACTION明確指定事務(wù)的開(kāi)始，這是最常用的事務(wù)類(lèi)型隱性事務(wù)：通過(guò)設(shè)置SET IMPLICIT_TRANSACTIONS ON 語(yǔ)句，將隱性事務(wù)模式設(shè)置為打開(kāi)，下一個(gè)語(yǔ)句自動(dòng)啟動(dòng)一個(gè)新事務(wù)。當(dāng)該事務(wù)完成時(shí)，再下一個(gè) T-SQL 語(yǔ)句又將啟動(dòng)一個(gè)新事務(wù)自動(dòng)提交事務(wù)：這是 SQL Server 的默認(rèn)模式，它將每條單獨(dú)的 T-SQL 語(yǔ)句視為一個(gè)事務(wù)，如果成功執(zhí)行，則自動(dòng)提交；如果錯(cuò)誤，則自動(dòng)回滾關(guān)鍵語(yǔ)句講解BEGIN TRANSACTION /*-定義變量，用于累計(jì)事務(wù)

10、執(zhí)行過(guò)程中的錯(cuò)誤-*/DECLARE errorSum INT SET errorSum=0 -初始化為0，即無(wú)錯(cuò)誤/*-轉(zhuǎn)賬：張三的賬戶(hù)少1000元，李四的賬戶(hù)多1000元*/UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=張三SET errorSum=errorSum+errorUPDATE bank SET currentMoney=currentMoney WHERE customerName=李四SET errorSum=errorSum+error -累計(jì)是否有錯(cuò)誤如何創(chuàng)建事務(wù)使用事務(wù)解決銀行轉(zhuǎn)賬問(wèn)

11、題開(kāi)始事務(wù)（指定事務(wù)從此處開(kāi)始，后續(xù)的T-SQL語(yǔ)句都是一個(gè)整體）累計(jì)是否有錯(cuò)誤IF errorSum0 -如果有錯(cuò)誤 BEGIN print 交易失敗，回滾事務(wù) ROLLBACK TRANSACTION END ELSE BEGIN print 交易成功，提交事務(wù)，寫(xiě)入硬盤(pán)，永久的保存 COMMIT TRANSACTION ENDGOprint 查看轉(zhuǎn)賬事務(wù)后的余額SELECT * FROM bank GO 如何創(chuàng)建事務(wù)根據(jù)是否有錯(cuò)誤，確定事務(wù)是提交還是撤銷(xiāo)如果有錯(cuò)，則回滾操作，事務(wù)結(jié)束如果成功，則提交操作，事務(wù)結(jié)束如何創(chuàng)建事務(wù)轉(zhuǎn)賬事務(wù)前轉(zhuǎn)賬事務(wù)過(guò)程中轉(zhuǎn)賬事務(wù)結(jié)束后演示：轉(zhuǎn)賬1000，轉(zhuǎn)賬失

12、敗的情況如何創(chuàng)建事務(wù)轉(zhuǎn)賬事務(wù)前轉(zhuǎn)賬事務(wù)過(guò)程中轉(zhuǎn)賬事務(wù)結(jié)束后演示：轉(zhuǎn)賬800，轉(zhuǎn)賬成功的情況什么是索引漢語(yǔ)字典中的漢字按頁(yè)存放，一般都有漢語(yǔ)拼音目錄（索引）、偏旁部首目錄等我們可以根據(jù)拼音或偏旁部首，快速查找某個(gè)字詞Indexes Use Key Values to Locate Data（根據(jù)索引鍵查找定位數(shù)據(jù)行） Data Pages（數(shù)據(jù)頁(yè)）Index Pages（索引頁(yè)） 什么是索引什么是索引SQL Server中的數(shù)據(jù)也是按頁(yè)（ 4KB ）存放索引：是SQL Server編排數(shù)據(jù)的內(nèi)部方法。它為SQL Server提供一種方法來(lái)編排查詢(xún)數(shù)據(jù) 。索引頁(yè)：數(shù)據(jù)庫(kù)中存儲(chǔ)索引的數(shù)據(jù)頁(yè)；索引頁(yè)類(lèi)

13、似于漢語(yǔ)字（詞）典中按拼音或筆畫(huà)排序的目錄頁(yè)。索引的作用：通過(guò)使用索引，可以大大提高數(shù)據(jù)庫(kù)的檢索速度，改善數(shù)據(jù)庫(kù)性能。索引類(lèi)型唯一索引：唯一索引不允許兩行具有相同的索引值主鍵索引：為表定義一個(gè)主鍵將自動(dòng)創(chuàng)建主鍵索引，主鍵索引是唯一索引的特殊類(lèi)型。主鍵索引要求主鍵中的每個(gè)值是唯一的，并且不能為空聚集索引(Clustered)：表中各行的物理順序與鍵值的邏輯（索引）順序相同，每個(gè)表只能有一個(gè)非聚集索引(Non-clustered)：非聚集索引指定表的邏輯順序。數(shù)據(jù)存儲(chǔ)在一個(gè)位置，索引存儲(chǔ)在另一個(gè)位置，索引中包含指向數(shù)據(jù)存儲(chǔ)位置的指針。可以有多個(gè)，小于249個(gè)如何創(chuàng)建索引 演示使用企業(yè)管理器創(chuàng)建如何

14、創(chuàng)建索引CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_name ON table_name (column_name) WITH FILLFACTOR=xUNIQUE表示唯一索引，可選CLUSTERED、NONCLUSTERED表示聚集索引還是 非聚集索引，可選FILLFACTOR表示填充因子，指定一個(gè)0到100之間的值，該值指示索引頁(yè)填滿(mǎn)的空間所占的百分比 使用T-SQL語(yǔ)句創(chuàng)建索引的語(yǔ)法：唯一索引聚集索引或非聚集索引填充因子（系數(shù)）:指定一個(gè)0100之間的值，表示索引頁(yè)填充的百分比USE stuDBGOIF EXISTS (SELECT

15、name FROM sysindexes WHERE name = IX_writtenExam) DROP INDEX stuMarks.IX_writtenExam /*-筆試列創(chuàng)建非聚集索引：填充因子為30-*/CREATE NONCLUSTERED INDEX IX_writtenExam ON stuMarks(writtenExam) WITH FILLFACTOR= 30GO/*指定按索引 IX_writtenExam 查詢(xún)*/SELECT * FROM stuMarks (INDEX=IX_writtenExam) WHERE writtenExam BETWEEN 60 AN

16、D 90如何創(chuàng)建索引 在stuMarks表的writtenExam列創(chuàng)建索引：檢測(cè)索引是否存在：索引存放在系統(tǒng)表sysindexes中索引的優(yōu)缺點(diǎn)優(yōu)點(diǎn)加快訪(fǎng)問(wèn)速度加強(qiáng)行的唯一性缺點(diǎn)帶索引的表在數(shù)據(jù)庫(kù)中需要更多的存儲(chǔ)空間操縱數(shù)據(jù)的命令需要更長(zhǎng)的處理時(shí)間，因?yàn)樗鼈冃枰獙?duì)索引進(jìn)行更新創(chuàng)建索引的指導(dǎo)原則請(qǐng)按照下列標(biāo)準(zhǔn)選擇建立索引的列。該列用于頻繁搜索該列用于對(duì)數(shù)據(jù)進(jìn)行排序請(qǐng)不要使用下面的列創(chuàng)建索引：列中僅包含幾個(gè)不同的值。表中僅包含幾行。為小型表創(chuàng)建索引可能不太劃算，因?yàn)镾QL Server在索引中搜索數(shù)據(jù)所花的時(shí)間比在表中逐行搜索所花的時(shí)間更長(zhǎng) 什么是視圖CREATE VIEW view_stuIn

17、fo_stuMarks AS SELECT 姓名=stuName, 學(xué)號(hào)=stuInfo.stuNo,基于學(xué)員信息表和成績(jī)表創(chuàng)建視圖教員需要的視圖：方便查看學(xué)員的成績(jī)班主任需要的視圖：方便查看學(xué)員的檔案什么是視圖視圖是一張?zhí)摂M表，它表示一張表的部分?jǐn)?shù)據(jù)或多張表的綜合數(shù)據(jù)，其結(jié)構(gòu)和數(shù)據(jù)是建立在對(duì)表的查詢(xún)基礎(chǔ)上視圖中并不存放數(shù)據(jù)，而是存放在視圖所引用的原始表（基表）中同一張?jiān)急?，根?jù)不同用戶(hù)的不同需求，可以創(chuàng)建不同的視圖什么是視圖視圖的用途篩選表中的行防止未經(jīng)許可的用戶(hù)訪(fǎng)問(wèn)敏感數(shù)據(jù)降低數(shù)據(jù)庫(kù)的復(fù)雜程度將多個(gè)物理數(shù)據(jù)庫(kù)抽象為一個(gè)邏輯數(shù)據(jù)庫(kù)如何創(chuàng)建視圖演示使用企業(yè)管理器創(chuàng)建視圖如何創(chuàng)建視圖使用T-S

18、QL語(yǔ)句創(chuàng)建視圖的語(yǔ)法CREATE VIEW view_name AS 如何創(chuàng)建視圖創(chuàng)建方便教員查看成績(jī)的視圖IF EXISTS (SELECT * FROM sysobjects WHERE name = view_stuInfo_stuMarks) DROP VIEW view_stuInfo_stuMarksGOCREATE VIEW view_stuInfo_stuMarks AS SELECT 姓名=stuName,學(xué)號(hào)=stuInfo.stuNo, 筆試成績(jī) =writtenExam, 機(jī)試成績(jī)=labExam, 平均分=(writtenExam+labExam)/2 FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNoG