TP10事務(wù)索引視圖

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

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

3、語句？請使用連接改寫上述語句 查詢參加考試的學(xué)員SELECT * FROM stuInfo WHERE stuNo IN (SELECT stuNo FROM stuMarks)SELECT * FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo目標(biāo)目標(biāo)理解理解事務(wù)事務(wù)的概念的概念1掌握如何創(chuàng)建事務(wù)掌握如何創(chuàng)建事務(wù)2掌握如何創(chuàng)建索引掌握如何創(chuàng)建索引3掌握如何創(chuàng)建并使用視圖掌握如何創(chuàng)建并使用視圖45為什么需要事務(wù)5-1銀行轉(zhuǎn)帳 例如，銀行轉(zhuǎn)帳問題：例如，銀行轉(zhuǎn)帳問題： 假定資金從帳戶假定資金從帳戶A轉(zhuǎn)到帳戶轉(zhuǎn)到帳戶B，

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

5、(customerName,currentMoney) VALUES(李四,1)創(chuàng)建帳戶表，存放用戶的帳戶信息添加約束：根據(jù)銀行規(guī)定，帳戶余額不能少于1元，否則視為銷戶 張三開戶，開戶金額為1000元 ；李四開戶，開戶金額1元 7為什么需要事務(wù)5-3 目前兩個帳戶的余額總和為：1000+1=1001元 8為什么需要事務(wù)5-4 模擬實(shí)現(xiàn)轉(zhuǎn)帳 ： 從張三的帳戶轉(zhuǎn)帳從張三的帳戶轉(zhuǎn)帳1000元到李四的帳戶元到李四的帳戶/*-轉(zhuǎn)帳測試：張三轉(zhuǎn)賬1000元給李四-*/-我們可能會這樣這樣編寫語句-張三的帳戶少1000元，李四的帳戶多1000元UPDATE bank SET currentMoney=cur

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

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

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

9、行分的（原子的）；要么都執(zhí)行，要么都不執(zhí)行一致性（一致性（Consistency）：當(dāng)事務(wù)完成時(shí)，數(shù)據(jù)必須處于一致狀態(tài)當(dāng)事務(wù)完成時(shí)，數(shù)據(jù)必須處于一致狀態(tài)隔離性隔離性（Isolation）：對數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的，這對數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的，這表明事務(wù)必須是獨(dú)立的，它不應(yīng)以任何方式依賴于或影響其他事務(wù)表明事務(wù)必須是獨(dú)立的，它不應(yīng)以任何方式依賴于或影響其他事務(wù)永久性（永久性（Durability）：）：事務(wù)完成后，它對數(shù)據(jù)庫的修改被永久保持，事事務(wù)完成后，它對數(shù)據(jù)庫的修改被永久保持，事務(wù)日志能夠保持事務(wù)的永久性務(wù)日志能夠保持事務(wù)的永久性12如何創(chuàng)建事務(wù)T-SQL使

10、用下列語句來管理事務(wù)：使用下列語句來管理事務(wù)：開始事務(wù)：BEGIN TRANSACTION提交事務(wù)：COMMIT TRANSACTION回滾（撤銷）事務(wù)：ROLLBACK TRANSACTION一旦事務(wù)提交或回滾，則事務(wù)結(jié)束。一旦事務(wù)提交或回滾，則事務(wù)結(jié)束。判斷某條語句執(zhí)行是否出錯：判斷某條語句執(zhí)行是否出錯：使用全局變量ERROR；ERROR只能判斷當(dāng)前一條T-SQL語句執(zhí)行是否有錯，為了判斷事務(wù)中所有T-SQL語句是否有錯，我們需要對錯誤進(jìn)行累計(jì)； 如：如： SET errorSum=errorSum+error13如何創(chuàng)建事務(wù)了解事務(wù)的分類：了解事務(wù)的分類：顯示事務(wù)：顯示事務(wù)：用用BEGI

11、N TRANSACTION明確指定事務(wù)的開始，這是明確指定事務(wù)的開始，這是最常用的事務(wù)類型最常用的事務(wù)類型隱性事務(wù)：通過設(shè)置隱性事務(wù)：通過設(shè)置SET IMPLICIT_TRANSACTIONS ON 語語句，將隱性事務(wù)模式設(shè)置為打開，下一個語句自動啟動一個新事務(wù)。句，將隱性事務(wù)模式設(shè)置為打開，下一個語句自動啟動一個新事務(wù)。當(dāng)該事務(wù)完成時(shí)，再下一個當(dāng)該事務(wù)完成時(shí)，再下一個 T-SQL 語句又將啟動一個新事務(wù)語句又將啟動一個新事務(wù)自動提交事務(wù)：這是自動提交事務(wù)：這是 SQL Server 的默認(rèn)模式，它將每條單獨(dú)的的默認(rèn)模式，它將每條單獨(dú)的 T-SQL 語句視為一個事務(wù)，如果成功執(zhí)行，則自動提交；

12、如果錯誤，語句視為一個事務(wù)，如果成功執(zhí)行，則自動提交；如果錯誤，則自動回滾則自動回滾14關(guān)鍵語句講解BEGIN TRANSACTION /*-定義變量，用于累計(jì)事務(wù)執(zhí)行過程中的錯誤-*/DECLARE errorSum INT SET errorSum=0 -初始化為0，即無錯誤/*-轉(zhuǎn)帳：張三的帳戶少1000元，李四的帳戶多1000元*/UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=張三SET errorSum=errorSum+errorUPDATE bank SET currentMoney=curre

13、ntMoney+1000 WHERE customerName=李四SET errorSum=errorSum+error -累計(jì)是否有錯誤如何創(chuàng)建事務(wù) 使用事務(wù)解決銀行轉(zhuǎn)帳問題使用事務(wù)解決銀行轉(zhuǎn)帳問題開始事務(wù)（指定事務(wù)從此處開始，后續(xù)的T-SQL語句都是一個整體）累計(jì)是否有錯誤15IF errorSum0 -如果有錯誤 BEGIN print 交易失敗，回滾事務(wù) ROLLBACK TRANSACTION END ELSE BEGIN print 交易成功，提交事務(wù)，寫入硬盤，永久的保存 COMMIT TRANSACTION ENDGOprint 查看轉(zhuǎn)帳事務(wù)后的余額SELECT * FROM

14、 bank GO 如何創(chuàng)建事務(wù)根據(jù)是否有錯誤，確定事務(wù)是提交還是撤銷如果有錯，則回滾操作，事務(wù)結(jié)束如果成功，則提交操作，事務(wù)結(jié)束16如何創(chuàng)建事務(wù)轉(zhuǎn)帳事務(wù)前轉(zhuǎn)帳事務(wù)過程中轉(zhuǎn)帳事務(wù)結(jié)束后 演示：轉(zhuǎn)帳演示：轉(zhuǎn)帳1000，轉(zhuǎn)帳失敗的情況，轉(zhuǎn)帳失敗的情況17如何創(chuàng)建事務(wù)轉(zhuǎn)帳事務(wù)前轉(zhuǎn)帳事務(wù)過程中轉(zhuǎn)帳事務(wù)結(jié)束后 演示：轉(zhuǎn)帳演示：轉(zhuǎn)帳800，轉(zhuǎn)帳成功的情況，轉(zhuǎn)帳成功的情況18什么是索引什么是索引v 漢語字典中的漢字按漢語字典中的漢字按頁頁存放，一般都有漢語拼音目錄（存放，一般都有漢語拼音目錄（索引索引）、偏旁部）、偏旁部首目錄等首目錄等v 我們可以根據(jù)拼音或偏旁部首，我們可以根據(jù)拼音或偏旁部首，快速快速查找某

15、個字詞查找某個字詞19Indexes Use Key Values to Locate Data（根據(jù)索引鍵查找定位數(shù)據(jù)行） Data Pages（數(shù)據(jù)頁）Index Pages（索引頁） 什么是索引什么是索引20什么是索引什么是索引 SQL Server中的數(shù)據(jù)也是按頁（中的數(shù)據(jù)也是按頁（ 4KB ）存放）存放 索引：是索引：是SQL Server編排數(shù)據(jù)的內(nèi)部方法。它為編排數(shù)據(jù)的內(nèi)部方法。它為SQL Server提供一種方法來編排查詢數(shù)據(jù)提供一種方法來編排查詢數(shù)據(jù) 。 索引頁：數(shù)據(jù)庫中存儲索引的數(shù)據(jù)頁；索引頁類似索引頁：數(shù)據(jù)庫中存儲索引的數(shù)據(jù)頁；索引頁類似于漢語字（詞）典中按拼音或筆畫排序

16、的目錄頁。于漢語字（詞）典中按拼音或筆畫排序的目錄頁。 索引的作用：通過使用索引，可以大大提高數(shù)據(jù)庫索引的作用：通過使用索引，可以大大提高數(shù)據(jù)庫的檢索速度，改善數(shù)據(jù)庫性能。的檢索速度，改善數(shù)據(jù)庫性能。21索引類型 唯一索引唯一索引：唯一索引不允許兩行具有相同的索引值：唯一索引不允許兩行具有相同的索引值 主鍵索引主鍵索引：為表定義一個主鍵將自動創(chuàng)建主鍵索引，主鍵索引是唯：為表定義一個主鍵將自動創(chuàng)建主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每個值是唯一的，并且不一索引的特殊類型。主鍵索引要求主鍵中的每個值是唯一的，并且不能為空能為空 聚集索引聚集索引(Clustered)：表中各

17、行的物理順序與鍵值的邏輯（索引）：表中各行的物理順序與鍵值的邏輯（索引）順序相同，每個表只能有一個順序相同，每個表只能有一個 非聚集索引非聚集索引(Non-clustered)：非聚集索引指定表的邏輯順序。數(shù)：非聚集索引指定表的邏輯順序。數(shù)據(jù)存儲在一個位置，索引存儲在另一個位置，索引中包含指向數(shù)據(jù)存據(jù)存儲在一個位置，索引存儲在另一個位置，索引中包含指向數(shù)據(jù)存儲位置的指針。可以有多個，小于儲位置的指針?？梢杂卸鄠€，小于249個個22如何創(chuàng)建索引3-1v 演示使用企業(yè)管理器創(chuàng)建演示使用企業(yè)管理器創(chuàng)建23如何創(chuàng)建索引 3-2CREATE UNIQUE CLUSTERED|NONCLUSTERED I

18、NDEX index_name ON table_name (column_name) WITH FILLFACTOR=xq UNIQUE表示唯一索引，可選q CLUSTERED、NONCLUSTERED表示聚集索引還是 非聚集索引，可選q FILLFACTOR表示填充因子，指定一個0到100之間的 值，該值指示索引頁填滿的空間所占的百分比v 使用使用T-SQL語句創(chuàng)建索引的語法：語句創(chuàng)建索引的語法：唯一索引聚集索引或非聚集索引填充因子（系數(shù)）:指定一個0100之間的值，表示索引頁填充的百分比24USE stuDBGOIF EXISTS (SELECT name FROM sysindexes

19、 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 查詢-*/SELECT * FROM WHERE writtenExam BETWEEN 60 AND 90如何創(chuàng)建索引3-3v 在在stuMarks表的表的writtenExam列創(chuàng)建索引：列創(chuàng)建索

20、引：檢測索引是否存在：索引存放在系統(tǒng)表sysindexes中25索引的優(yōu)缺點(diǎn) 優(yōu)點(diǎn)優(yōu)點(diǎn)加快訪問速度加快訪問速度加強(qiáng)行的唯一性加強(qiáng)行的唯一性 缺點(diǎn)缺點(diǎn)帶索引的表在數(shù)據(jù)庫中需要更多的存儲空間帶索引的表在數(shù)據(jù)庫中需要更多的存儲空間操縱數(shù)據(jù)的命令需要更長的處理時(shí)間，因?yàn)樗鼈冃枰獙λ饕M(jìn)行更新操縱數(shù)據(jù)的命令需要更長的處理時(shí)間，因?yàn)樗鼈冃枰獙λ饕M(jìn)行更新26創(chuàng)建索引的指導(dǎo)原則 請按照下列標(biāo)準(zhǔn)選擇建立索引的列。請按照下列標(biāo)準(zhǔn)選擇建立索引的列。該列用于頻繁搜索該列用于頻繁搜索該列用于對數(shù)據(jù)進(jìn)行排序該列用于對數(shù)據(jù)進(jìn)行排序 請不要使用下面的列創(chuàng)建索引：請不要使用下面的列創(chuàng)建索引：列中僅包含幾個不同的值。列中僅包含

21、幾個不同的值。表中僅包含幾行。為小型表創(chuàng)建索引可能不太劃算表中僅包含幾行。為小型表創(chuàng)建索引可能不太劃算，因?yàn)?，因?yàn)镾QL Server在索引中搜索數(shù)據(jù)所花的時(shí)間比在表中逐行搜索所花的時(shí)間在索引中搜索數(shù)據(jù)所花的時(shí)間比在表中逐行搜索所花的時(shí)間更長更長 27什么是視圖CREATE VIEW view_stuInfo_stuMarks AS SELECT 姓名姓名=stuName, 學(xué)號學(xué)號=stuInfo.stuNo,基于學(xué)員信息表和成績表創(chuàng)建視圖教員需要的視圖：方便查看學(xué)員的成績班主任需要的視圖：方便查看學(xué)員的檔案28什么是視圖 視圖是一視圖是一張張?zhí)摂M表虛擬表，它表示一張表的部分?jǐn)?shù)據(jù)它表示一張表

22、的部分?jǐn)?shù)據(jù)或多張表的綜合數(shù)據(jù)或多張表的綜合數(shù)據(jù)，其結(jié)構(gòu)和數(shù)據(jù)是建立在，其結(jié)構(gòu)和數(shù)據(jù)是建立在對表的查詢基礎(chǔ)上對表的查詢基礎(chǔ)上 視圖中并不存放數(shù)據(jù)，而是存放在視圖所引用視圖中并不存放數(shù)據(jù)，而是存放在視圖所引用的原始表（基表）中的原始表（基表）中 同一張?jiān)急?，根?jù)不同用戶的不同需求，可同一張?jiān)急?，根?jù)不同用戶的不同需求，可以創(chuàng)建不同的以創(chuàng)建不同的視圖視圖29什么是視圖 視圖的用途視圖的用途篩選表中的行防止未經(jīng)許可的用戶訪問敏感數(shù)據(jù)降低數(shù)據(jù)庫的復(fù)雜程度將多個物理數(shù)據(jù)庫抽象為一個邏輯數(shù)據(jù)庫30如何創(chuàng)建視圖 演示使用企業(yè)管理器創(chuàng)建視圖演示使用企業(yè)管理器創(chuàng)建視圖31如何創(chuàng)建視圖 使用使用T-SQL語句創(chuàng)建視圖的語法語句創(chuàng)建視圖的語法CREATE VIEW view_name AS 32如何創(chuàng)建視圖 創(chuàng)建方便教員查看