SQL課件--十、事務(wù)、索引、視圖.ppt

第十章,事務(wù)、索引和視圖,目標(biāo),理解事務(wù)的概念 掌握如何創(chuàng)建事務(wù) 掌握如何創(chuàng)建索引 掌握如何創(chuàng)建并使用視圖,為什么需要事務(wù),銀行轉(zhuǎn)賬,例如，銀行轉(zhuǎn)賬問題： 假定資金從賬戶A轉(zhuǎn)到賬戶B，至少需要兩步： 賬戶A的資金減少 然后賬戶B的資金相應(yīng)增加,賬戶A,賬戶B,假定張三的賬戶直接轉(zhuǎn)賬1000元到李四的賬戶,為什么需要事務(wù),CREATE TABLE bank ( customerName CHAR(10), -顧客姓名 currentMoney MONEY -當(dāng)前余額 ) GO ALTER TABLE bank ADD CONSTRAINT CK_currentMoney CHECK(currentMoney=1) GO INSERT INTO bank(customerName,currentMoney) VALUES(張三,1000) INSERT INTO bank(customerName,currentMoney) VALUES(李四,1),創(chuàng)建賬戶表，存放用戶的賬戶信息,添加約束：根據(jù)銀行規(guī)定，賬戶余額不能少于1元，否則視為銷戶,張三開戶，開戶金額為1000元 ；李四開戶，開戶金額1元,為什么需要事務(wù),目前兩個賬戶的余額總和為：1000+1=1001元,為什么需要事務(wù),模擬實現(xiàn)轉(zhuǎn)賬 ： 從張三的賬戶轉(zhuǎn)賬1000元到李四的賬戶,/*-轉(zhuǎn)賬測試：張三轉(zhuǎn)賬1000元給李四-*/ -我們可能會這樣這樣編寫語句 -張三的賬戶少1000元，李四的賬戶多1000元 UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=張三 UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四 GO -再次查看轉(zhuǎn)賬后的結(jié)果。 SELECT * FROM bank GO,請問： 執(zhí)行轉(zhuǎn)賬語句后，張三、李四的賬戶余額為多少？,張三的賬戶沒有減少 但李四的賬戶卻多了1000元 100010012001元 總額多出了1000元！,為什么需要事務(wù),-張三的賬戶減少1000元，李四的賬戶增加1000元 UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=張三 UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四 GO,錯誤原因分析：,UPDATE語句違反約束： 余額=1元,執(zhí)行失敗，所以張三還是1000元,繼續(xù)往下執(zhí)行：執(zhí)行成功，所以李四變?yōu)?001元,如何解決呢？使用事務(wù),什么是事務(wù),事務(wù)(TRANSACTION)是作為單個邏輯工作單元執(zhí)行的一系列操作 這些操作作為一個整體一起向系統(tǒng)提交，要么都執(zhí)行、要么都不執(zhí)行 事務(wù)是一個不可分割的工作邏輯單元,轉(zhuǎn)賬過程就是一個事務(wù)。 它需要兩條UPDATE語句來完成，這兩條語句是一個整體，如果其中任一條出現(xiàn)錯誤，則整個轉(zhuǎn)賬業(yè)務(wù)也應(yīng)取消，兩個賬戶中的余額應(yīng)恢復(fù)到原來的數(shù)據(jù)，從而確保轉(zhuǎn)賬前和轉(zhuǎn)賬后的余額不變，即都是1001元。,事務(wù)的分類,了解事務(wù)的分類： 顯示事務(wù)：用BEGIN TRANSACTION明確指定事務(wù)的開始，這是最常用的事務(wù)類型 隱性事務(wù)：在前一個事務(wù)完成時，新事務(wù)隱式啟動，但每個事務(wù)仍以COMMIT或ROLLBACK顯式結(jié)束。 自動提交事務(wù)：這是 SQL Server 的默認(rèn)模式，它將每條單獨(dú)的 T-SQL 語句視為一個事務(wù)，如果成功執(zhí)行，則自動提交；如果錯誤，則自動回滾,事務(wù)的特性,事務(wù)必須具備以下四個屬性，簡稱ACID 屬性： 原子性（Atomicity）：事務(wù)是一個完整的操作。事務(wù)的各步操作是不可分的（原子的）；要么都執(zhí)行，要么都不執(zhí)行 一致性（Consistency）：當(dāng)事務(wù)完成時，數(shù)據(jù)必須處于一致狀態(tài) 隔離性（Isolation）：對數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的，這表明事務(wù)必須是獨(dú)立的，它不應(yīng)以任何方式依賴于或影響其他事務(wù) 永久性（Durability）：事務(wù)完成后，它對數(shù)據(jù)庫的修改被永久保持，事務(wù)日志能夠保持事務(wù)的永久性,原子性(Atomicity),銀行轉(zhuǎn)帳：事務(wù)T從A帳戶過戶50￥到B帳戶。 T：Read(A); A=A-50; Write(A); Read(B); B=B+50; Write(B); Read(X):從數(shù)據(jù)庫傳送數(shù)據(jù)項X到事務(wù)的工作區(qū)中。 Write(X):從事務(wù)的工作區(qū)中將數(shù)據(jù)項X回寫到數(shù)據(jù)庫。這個工作不能只完成一部分，它必須滿足原子性。,一致性(Consistency),事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫從一個一致性狀態(tài)變到另一個一致性狀態(tài)。 當(dāng)數(shù)據(jù)庫只包含成功事務(wù)提交的結(jié)果時，就說明數(shù)據(jù)庫處于一致狀態(tài)。如果數(shù)據(jù)庫系統(tǒng)運(yùn)行中發(fā)生故障，有些事務(wù)尚未完成就被迫中斷，系統(tǒng)將事務(wù)中對數(shù)據(jù)庫的所有已完成的操作全部撤消，滾回到事務(wù)開始的一致狀態(tài)。,隔離性(Isolation),一個事務(wù)的執(zhí)行不能被其他事務(wù)干擾。 事務(wù)查看數(shù)據(jù)時數(shù)據(jù)所處的狀態(tài)，要么是另一并發(fā)事務(wù)修改它以前的狀態(tài)，要么是另一并發(fā)事務(wù)修改它以后的狀態(tài)，事務(wù)不會查看中間狀態(tài)的數(shù)據(jù)。這稱為可串行性。,持續(xù)性/永久性(Durability),一個事務(wù)一旦提交，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就應(yīng)該是永久性的。接下來的其他操作或故障不應(yīng)該對其執(zhí)行結(jié)果有任何影響。,如何創(chuàng)建事務(wù),T-SQL使用下列語句來管理事務(wù)： 開始事務(wù)：BEGIN TRANSACTION 提交事務(wù)：COMMIT TRANSACTION 回滾（撤銷）事務(wù)：ROLLBACK TRANSACTION 一旦事務(wù)提交或回滾，則事務(wù)結(jié)束。 判斷某條語句執(zhí)行是否出錯： 使用全局變量ERROR； ERROR只能判斷當(dāng)前一條T-SQL語句執(zhí)行是否有錯，為了判斷事務(wù)中所有T-SQL語句是否有錯，我們需要對錯誤進(jìn)行累計； 如： SET errorSum=errorSum+error,關(guān)鍵語句講解 BEGIN TRANSACTION /*-定義變量，用于累計事務(wù)執(zhí)行過程中的錯誤-*/ DECLARE errorSum INT SET errorSum=0 -初始化為0，即無錯誤 /*-轉(zhuǎn)賬：張三的賬戶少1000元，李四的賬戶多1000元*/ UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=張三 SET errorSum=errorSum+error UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四 SET errorSum=errorSum+error -累計是否有錯誤,如何創(chuàng)建事務(wù),使用事務(wù)解決銀行轉(zhuǎn)賬問題,開始事務(wù)（指定事務(wù)從此處開始，后續(xù)的T-SQL語句都是一個整體）,累計是否有錯誤,IF errorSum0 -如果有錯誤 BEGIN print 交易失敗，回滾事務(wù) ROLLBACK TRANSACTION END ELSE BEGIN print 交易成功，提交事務(wù)，寫入硬盤，永久的保存 COMMIT TRANSACTION END GO print 查看轉(zhuǎn)賬事務(wù)后的余額 SELECT * FROM bank GO,如何創(chuàng)建事務(wù),根據(jù)是否有錯誤，確定事務(wù)是提交還是撤銷,如果有錯，則回滾操作，事務(wù)結(jié)束,如果成功，則提交操作，事務(wù)結(jié)束,如何創(chuàng)建事務(wù),轉(zhuǎn)賬事務(wù)前,轉(zhuǎn)賬事務(wù)過程中,轉(zhuǎn)賬事務(wù)結(jié)束后,演示：轉(zhuǎn)賬1000，轉(zhuǎn)賬失敗的情況,如何創(chuàng)建事務(wù),轉(zhuǎn)賬事務(wù)前,轉(zhuǎn)賬事務(wù)過程中,轉(zhuǎn)賬事務(wù)結(jié)束后,演示：轉(zhuǎn)賬800，轉(zhuǎn)賬成功的情況,什么是索引,漢語字典中的漢字按頁存放，一般都有漢語拼音目錄（索引）、偏旁部首目錄等 我們可以根據(jù)拼音或偏旁部首，快速查找某個字詞,Indexes Use Key Values to Locate Data （根據(jù)索引鍵查找定位數(shù)據(jù)行）,Index Pages（索引頁）,什么是索引,什么是索引,SQL Server中的數(shù)據(jù)也是按頁（ 4KB ）存放 索引：是SQL Server編排數(shù)據(jù)的內(nèi)部方法。它為SQL Server提供一種方法來編排查詢數(shù)據(jù) 。 索引頁：數(shù)據(jù)庫中存儲索引的數(shù)據(jù)頁；索引頁類似于漢語字（詞）典中按拼音或筆畫排序的目錄頁。 索引的作用：通過使用索引，可以大大提高數(shù)據(jù)庫的檢索速度，改善數(shù)據(jù)庫性能。,索引類型,唯一索引：唯一索引不允許兩行具有相同的索引值 主鍵索引：為表定義一個主鍵將自動創(chuàng)建主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每個值是唯一的，并且不能為空 聚集索引(Clustered)：表中各行的物理順序與鍵值的邏輯（索引）順序相同，每個表只能有一個 非聚集索引(Non-clustered)：非聚集索引指定表的邏輯順序。數(shù)據(jù)存儲在一個位置，索引存儲在另一個位置，索引中包含指向數(shù)據(jù)存儲位置的指針?？梢杂卸鄠€，小于249個,如何創(chuàng)建索引,演示使用企業(yè)管理器創(chuàng)建,如何創(chuàng)建索引,CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_name ON table_name (column_name) WITH FILLFACTOR=x,UNIQUE表示唯一索引，可選 CLUSTERED、NONCLUSTERED表示聚集索引還是 非聚集索引，可選 FILLFACTOR表示填充因子，指定一個0到100之間的值， 該值指示索引頁填滿的空間所占的百分比,使用T-SQL語句創(chuàng)建索引的語法：,唯一索引,聚集索引或非聚集索引,填充因子（系數(shù)）:指定一個0100之間的值，表示索引頁填充的百分比,USE stuDB GO IF EXISTS (SELECT 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= 30 GO /*-指定按索引 IX_writtenExam 查詢-*/ SELECT * FROM stuMarks (INDEX=IX_writtenExam) WHERE writtenExam BETWEEN 60 AND 90,如何創(chuàng)建索引,在stuMarks表的writtenExam列創(chuàng)建索引：,檢測索引是否存在： 索引存放在系統(tǒng)表sysindexes中,索引的優(yōu)缺點,優(yōu)點 加快訪問速度 加強(qiáng)行的唯一性 缺點 帶索引的表在數(shù)據(jù)庫中需要更多的存儲空間 操縱數(shù)據(jù)的命令需要更長的處理時間，因為它們需要對索引進(jìn)行更新,創(chuàng)建索引的指導(dǎo)原則,請按照下列標(biāo)準(zhǔn)選擇建立索引的列。 該列用于頻繁搜索 該列用于對數(shù)據(jù)進(jìn)行排序 請不要使用下面的列創(chuàng)建索引： 列中僅包含幾個不同的值。 表中僅包含幾行。為小型表創(chuàng)建索引可能不太劃算，因為SQL Server在索引中搜索數(shù)據(jù)所花的時間比在表中逐行搜索所花的時間更長,什么是視圖,CREATE VIEW view_stuInfo_stuMarks AS SELECT 姓名=stuName, 學(xué)號=stuInfo.stuNo,基于學(xué)員信息表和成績表創(chuàng)建視圖,教員需要的視圖： 方便查看學(xué)員的成績,班主任需要的視圖： 方便查看學(xué)員的檔案,什么是視圖,視圖是一張?zhí)摂M表，它表示一張表的部分?jǐn)?shù)據(jù)或多張表的綜合數(shù)據(jù)，其結(jié)構(gòu)和數(shù)據(jù)是建立在對表的查詢基礎(chǔ)上 視圖中并不存放數(shù)據(jù)，而是存放在視圖所引用的原始表（基表）中 同一張原始表，根據(jù)不同用戶的不同需求，可以創(chuàng)建不同的視圖,什么是視圖,視圖的用途 篩選表中的行 防止未經(jīng)許可的用戶訪問敏感數(shù)據(jù) 降低數(shù)據(jù)庫的復(fù)雜程度 將多個物理數(shù)據(jù)庫抽象為一個邏輯數(shù)據(jù)庫,如何創(chuàng)建視圖,演示使用企業(yè)管理器創(chuàng)建視圖,如何創(chuàng)建視圖,使用T-SQL語句創(chuàng)建視圖的語法,CREATE VIEW view_name AS with check option,有下列三種情況下必須明確指定視圖的所有列名： 某個目標(biāo)列不是單純的屬性名，而是集函數(shù)或列表達(dá)式 . 多表連接時選出了幾個同名列作為視圖的字段. 需要在視圖中為某個列啟用新的更合適的名字. with check option 用來指明當(dāng)對視圖進(jìn)行數(shù)據(jù)修改時，要檢查是否滿足視圖定義中where后的條件. 建立視圖中的子查詢語句可以是任意復(fù)雜的select語句，但通常不允許含有order by子句和distinct短語.,如何創(chuàng)建視圖,創(chuàng)建方便教員查看成績的視圖,IF EXISTS (SELECT * FROM sysobjects WHERE name = view_stuInfo_stuMarks) DROP VIEW view_stuInfo_stuMarks GO CREATE VIEW view_stuInfo_stuMarks AS SELECT 姓名=stuName,學(xué)號=stuInfo.stuNo, 筆試成績 =writtenExam, 機(jī)試成績=labExam, 平均分=(writtenExam+labExam)/2 FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo GO SELECT * FROM view_stuInfo_stuMarks,檢測是否存在,創(chuàng)建視圖,使用視圖,刪除視圖,如何創(chuàng)建視圖(續(xù)),創(chuàng)建方便教員查看成績的視圖,IF EXISTS (SELECT * FROM sysobjects WHERE name = view_stuInfo_stuMarks) DROP VIEW view_stuInfo_stuMarks GO CREATE VIEW view_stuInfo_stuMarks AS SELECT 姓名=stuName,學(xué)號=stuInfo.stuNo, 筆試成績 =writtenExam, 機(jī)試成績=labExam, 平均分=(writtenExam+labExam)/2 FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam60 WITH CHECK OPTION GO SELECT * FROM view_stuInfo_stuMarks,檢測是否存在,創(chuàng)建視圖,使用視圖,刪除視圖,如何創(chuàng)建視圖(續(xù)),創(chuàng)建方便教員查看成績的視圖,IF EXISTS (SELECT * FROM sysobjects WHERE name = view_Info_Marks) DROP VIEW view_Info_Marks GO CREATE VIEW view_Info_Marks AS SELECT 姓名,學(xué)號,筆試成績, 機(jī)試成績,平均分 FROM view_stuInfo_stuMarks WHERE 平均分60 WITH CHECK OPTI