第7章視圖索引游標(biāo)事務(wù).ppt

1、第7章 視圖、索引、游標(biāo)及事務(wù),逯燕玲 戴紅 李志明 主編,2020年8月10日星期一,2,第7章 視圖、索引、游標(biāo)及事務(wù),7.1 視圖 7.2 索引 7.3 游標(biāo) 7.4 事務(wù)的使用,2020年8月10日星期一,3,7.1 視圖,7.1.1 視圖概述 視圖是從一個(gè)表、多個(gè)表或視圖中導(dǎo)出的表，其結(jié)構(gòu)和數(shù)據(jù)是建立在對表的查詢基礎(chǔ)上的。 視圖不是真實(shí)存在的基礎(chǔ)表，而是一個(gè)虛擬表，視圖所對應(yīng)的數(shù)據(jù)并不實(shí)際地以視圖結(jié)構(gòu)存儲在數(shù)據(jù)庫中，而是存儲在視圖所引用的表中。,2020年8月10日星期一,4,7.1.2 創(chuàng)建視圖的方法,1. 使用企業(yè)管理器向?qū)?chuàng)建視圖,【例7-1】在學(xué)籍管理數(shù)據(jù)庫（students

2、）中創(chuàng)建一個(gè)僅包含基礎(chǔ)課的視圖。 啟動SQL Server企業(yè)管理器，在企業(yè)管理器窗口中展開Microsoft SQL Servers和SQL Server組，選擇SQL Server服務(wù)器。 在“工具”菜單中選擇“向?qū)А辈藛雾?xiàng)，選擇工具菜單中的“創(chuàng)建視圖向?qū)А?，在出現(xiàn)的“創(chuàng)建視圖向?qū)А苯缑嬷袉螕簟跋乱徊健薄?2020年8月10日星期一,5,輸入數(shù)據(jù)庫名“學(xué)生學(xué)籍?dāng)?shù)據(jù)庫”，單擊“下一步”；輸入所選數(shù)據(jù)庫的選擇對象對話框名“課程基本信息表”，單擊“下一步”。 為對象中的列選擇視圖所顯示的字段，單擊“下一步”。用“where課程類型=基礎(chǔ)”語句來定義所限制對話框，單擊“下一步”。 將對話框命名為

3、“課程基本信息表_VIEW”，單擊“下一步”。再選擇“完成”，即可成功創(chuàng)建視圖。,2020年8月10日星期一,6,7.1.2 創(chuàng)建視圖的方法,2. 用T-SQL創(chuàng)建視圖 語法： CREATE VIEW view_name(column,n) WITH,n AS sellect_statement,2020年8月10日星期一,7,【例7-2】在學(xué)籍管理數(shù)據(jù)庫（students）中創(chuàng)建一個(gè)視圖，其中內(nèi)容為一門課程的成績單，包含學(xué)生姓名。 在”工具”菜單中選擇”向?qū)А泵?CREATE VIEW SC CourseG WITH,n AS sellect SC.CNo FROM SC,Courses

4、WHERE SC.CNo=Courses.CNo AND Courses.Cname =網(wǎng)絡(luò)數(shù)據(jù)庫）；,2020年8月10日星期一,8,【例7-3】假設(shè)在學(xué)籍管理數(shù)據(jù)庫（students）中有三個(gè)結(jié)構(gòu)相同的學(xué)生基本信息表：S1、S2、S3，分別存放“計(jì)算機(jī)科學(xué)與技術(shù)”、“電子信息科學(xué)與技術(shù)”和“電子信息工程”專業(yè)的學(xué)生基本信息。創(chuàng)建一個(gè)視圖將這三個(gè)表合并在一起。 USE students CREATE VIEW ALL_Students AS SELECT *FROM S1 UNION SELECT *FROM S2 UNION SELECT *FROM S3,2020年8月10日星期一,9,

5、7.1.3 視圖信息的查看和修改,1. 使用企業(yè)管理器查看和修改視圖,啟動SQL Server企業(yè)管理器，登錄到指定的服務(wù)器 。 打開要創(chuàng)建視圖的數(shù)據(jù)庫文件夾，選中view圖標(biāo)，此時(shí)在右面的窗格中顯示當(dāng)前數(shù)據(jù)庫的所有視圖。 右鍵單擊要查看的視圖，在彈出的快捷菜單中選擇“屬性”，打開View Properties對話框 。 在該對話框內(nèi)可瀏覽到該視圖的正文，也可對該視圖進(jìn)行修改，然后單擊“Check Syntax”按鈕對語句合法性進(jìn)行檢查，若對視圖的訪問權(quán)限進(jìn)行設(shè)置，單擊“Permissions”按鈕。,2020年8月10日星期一,10,2使用T-SQL查看和修改視圖 【例7-4】查看在學(xué)籍管理

6、數(shù)據(jù)庫（students）中創(chuàng)建的視圖DB_S_Grades。 SELECT FROM DB_S_Grades,2020年8月10日星期一,11,當(dāng)需要在視圖中對數(shù)據(jù)進(jìn)行插入、修改和刪除等更新操作時(shí)，可與操作表數(shù)據(jù)窗口相似，同樣可以對數(shù)據(jù)進(jìn)行插入、修改和刪除等更新操作。 還可以使用INSERT、UPDATE和DELETE語句對視圖數(shù)據(jù)進(jìn)行更新。,7.1.4 視圖數(shù)據(jù)的更新,2020年8月10日星期一,12,在企業(yè)管理器窗口中單擊“視圖”選項(xiàng)，打開“視圖列表”。在視圖列表中找到要刪除的視圖，右鍵單擊該視圖打開其快捷菜單，選擇“刪除”。在出現(xiàn)的“除去對象”對話框中，選擇“全部除去”即可完成。 還可

7、以使用T-SQL刪除視圖，基本語法格式為： DROP VIEW view_name ,n,7.1.5 刪除視圖,2020年8月10日星期一,13,7.2 索引,7.2.1 索引的分類 聚簇索引（Clustered Index）：行的物理存儲順序與索引順序完全相同，所以每個(gè)表只能有一個(gè)聚簇索引，且在表的主鍵列上自動創(chuàng)建聚簇索引。 非聚簇索引：邏輯順序不等于物理順序，僅僅記錄指針，通過這些指針可以在表中快速準(zhǔn)確的定位數(shù)據(jù)。 唯一性索引（Unique Index）：能夠保證在創(chuàng)建索引的列或多列的組合上不包括重復(fù)的數(shù)據(jù)，聚簇或非聚簇索引都可以是唯一性索引。,2020年8月10日星期一,14,創(chuàng)建索引前

8、考慮的問題,只有表的擁有者才能在表上創(chuàng)建索引 每一個(gè)表上只能創(chuàng)建一個(gè)聚簇索引 每一個(gè)表上最多能創(chuàng)建249個(gè)非聚簇索引 一個(gè)索引最多包含16個(gè)列，最大為900字節(jié),2020年8月10日星期一,15,7.2.2 創(chuàng)建索引,1. 使用企業(yè)管理器直接創(chuàng)建 用鼠標(biāo)右鍵單擊要創(chuàng)建索引的表，選擇”管理索引”菜單項(xiàng)。 2. 使用向?qū)?chuàng)建索引 在”工具”菜單中選擇”向?qū)А泵?3. 使用T-SQL創(chuàng)建索引 CREATE UNIQUECLUSTEREDNONCLUSTERED INDEX index_name ON table|view (columnASCDESC,n) WITH 參數(shù) ,2020年8月10日星

9、期一,16,7.2.3 創(chuàng)建索引視圖,在視圖上創(chuàng)建唯一聚集索引可以提高性能，執(zhí)行該視圖結(jié)果集在數(shù)據(jù)庫中的存儲方式與帶聚集索引的表的存儲方式相同。 在視圖上創(chuàng)建索引的另一個(gè)好處是：查詢優(yōu)化器開始在查詢中使用視圖索引，而不是直接在子句中命名視圖。這樣可從索引視圖檢索數(shù)據(jù)而無需重新編碼，由此帶來的高效率也使現(xiàn)有查詢獲益。,2020年8月10日星期一,17,7.2.4 刪除索引,1. 使用企業(yè)管理器直接刪除索引 在要刪除索引的表上單擊右鍵,選擇”所有任務(wù)”中的”管理索引”命令。 2. 用T-SQL刪除索引 語法： DROP INDEX table.index|view.index,n 例如：DROP

10、INDEX Student. DEPT,2020年8月10日星期一,18,用企業(yè)管理器查看修改索引 選擇要查看的數(shù)據(jù)庫表上單擊右鍵，從快捷菜單中選擇”所有任務(wù)中的“管理索引” 項(xiàng)，出現(xiàn) “索引管理”對話框。 選擇要查看或修改的索引，單擊“編輯”按鈕，出現(xiàn)“修改索引” 對話框。 可修改索引的大部分設(shè)置，還可單擊“編輯SQL”按鈕，在顯示的SQL腳本編輯框直接修改其SQL腳本，但不能修改索引名，程序方式的索引名修改需要使用系統(tǒng)存儲過程 Sp_rename。 要在企業(yè)管理器中修改索引名、改變其所屬文件組等其它信息則需要在表的屬性對話框中進(jìn)行，該屬性對話框是從”設(shè)計(jì)表結(jié)構(gòu)“對話框中調(diào)用，而不是直接通過

11、快捷菜單的 “屬性”菜單項(xiàng)調(diào)用。,7.2.5 查看與修改索引,2020年8月10日星期一,19,7.3 游標(biāo),游標(biāo)是系統(tǒng)為用戶在內(nèi)存中開辟的一個(gè)數(shù)據(jù)緩沖區(qū)，用于存放SQL語句的查詢結(jié)果，每個(gè)游標(biāo)都有一個(gè)名字，通過宿主語言的循環(huán)使SQL逐一從游標(biāo)中讀取記錄，賦給主變量，然后由宿主語言作進(jìn)一步的處理。 游標(biāo)的操作一般分為如下幾個(gè)步驟： 定義游標(biāo) EXEC SQL DECLARE CURSOR FOR 打開游標(biāo) EXEC SQL OPEN 推進(jìn)游標(biāo) EXEC SQL FETCH INTO 關(guān)閉游標(biāo) EXEC SQL CLOSE 刪除游標(biāo) DEALLOCATE ,2020年8月10日星期一,20,【例

12、7-5】查詢各種職稱的教師的名單。,EXEC SQL BEGIN DECLARE SECTION； char xm8； char zc6； EXEC SQL END DECLARE SECTION； printf（”Enter 職稱：”）； scanf（“%s”，zc）； EXEC SQL DECLARE zc_cur CURSOR FOR SELECT Tname，Ttitle FROM TEACHER WHERE Ttitle=：zc； EXEC SQL OPEN zc_cur While(1) EXEC SQL FETCH zc_cur INTO ：xm，：zc； if（sqlca.sq

13、lcode0） break； EXEC SQL CLOSE zc_cur； ,2020年8月10日星期一,21,7.4 事務(wù)的使用,7.4.1 事務(wù)概述 所謂事務(wù)，實(shí)際上就是對于一個(gè)不可分割的操作序列，控制它要么全部執(zhí)行，要么都不執(zhí)行。在每個(gè)事務(wù)結(jié)束時(shí)，數(shù)據(jù)庫中的數(shù)據(jù)應(yīng)該是一致的。 設(shè)計(jì)事務(wù)程序時(shí)應(yīng)保證事務(wù)具有如下特性： 原子性（Atomicity） 一致性（Consistency） 獨(dú)立性（Isolation） 持久性（Durability）,2020年8月10日星期一,22,三種執(zhí)行事務(wù)的模式,顯式事務(wù)：每個(gè)事務(wù)均以BEGIN TRANSACTION語句顯式開始，而以COMMIT TRAN

14、或ROLLBACK TRAN等語句顯式結(jié)束。 隱性事務(wù)：在T-SQL腳本中，執(zhí)行“SET IMPLICIT_TRANSACTIONS ON”語句后，系統(tǒng)即進(jìn)入隱性事務(wù)模式。 自動提交事務(wù)：如果一個(gè)T-SQL語句成功地完成，則自動提交該語句；如果遇到錯(cuò)誤，則自動回滾該語句。,2020年8月10日星期一,23,開始事務(wù)語句 BEGIN TRANSACTIONTRANSACTION_NAMEWITH MARKdescription 提交事務(wù)語句 COMMIT TRANSACTIONTRANSACTION_NAME 回滾事務(wù)語句 ROLLBACK TRANSACTION transaction_nam

15、e| savepoint_name 保存事務(wù)斷點(diǎn)語句 SAVE TRANSACTION SAVEPOINT_NAME,7.4.2 事務(wù)處理語句,2020年8月10日星期一,24,【例7-6】給教工“高山”增加工資100元。 BEGIN TRANSACTION UPDATE 教工登記表 SET 基本工資=基本工資+100 WHERE 姓名=高山 UPDATE 教工工資表 SET 基本工資=基本工資+100 WHERE 姓名=高山 COMMIT,2020年8月10日星期一,25,【例7-7】在“中高級職稱名冊”中修改一條記錄，并插入一條記錄。 BEGIN TRANSACTION UPDATE 中高

16、級職稱名冊 SET 職稱=副教授 WHERE 姓名=王冠 INSERT 中高級職稱名冊 VALUES(SYS010,高山,男,45,1,教授,4800,計(jì)算機(jī)系) SELECT * FROM 中高級職稱名冊 COMMIT,2020年8月10日星期一,26,【例7-8】將當(dāng)前連接設(shè)成隱性事務(wù)狀態(tài)，并進(jìn)行還書。假如在還書時(shí)必須完成修改“租借信息”表和“圖書信息”表兩個(gè)操作，可編寫如下腳本。,DECLARE 借書證號 varchar(5), 圖書編號 varchar(6) SET 借書證號=00006 SET 圖書編號=100006 SET IMPLICIT_TRANSACTIONS ON UPDA

17、TE 租借信息 SET 還書日期= getdate() WHERE 圖書編號=圖書編號 AND 借書證號=借書證號 IF ERROR!=0 AND ROWCOUNT=0 BEGIN ROLLBACK TRAN RETURN END,UPDATE 圖書信息 SET 狀態(tài)=0 WHERE 圖書編號=圖書編號 IF ERROR!=0 AND ROWCOUNT=0 BEGIN ROLLBACK TRAN RETURN END COMMIT PRINT 恭喜你還書成功！ SET IMPLICIT_TRANSACTIONS OFF,2020年8月10日星期一,27,【例7-9】某一新生來借書，假如要完成辦

18、借書證和借書兩個(gè)操作。辦借書證操作可理解為向“學(xué)生信息”表中插入一條辦證記錄；借書操作為了說明方便簡化為只向“租借信息”表中插入一條借書記錄。編寫腳本如下。,DECLARE 學(xué)號 varchar(6),姓名 varchar(10),班級 varchar(10) DECLARE 借書證號 char(5),圖書編號 varchar(6),借閱號 int SET 圖書編號=100002 SELECT 學(xué)號=20030104,姓名=李小軍,班級=2003-01 BEGIN TRAN SELECT 借書證號=借書證號 FROM 學(xué)生信息 ORDER BY 借書證號 SET 借書證號=convert(va

19、rchar,convert(bigint,借書證號)+1) INSERT 學(xué)生信息(借書證號,學(xué)號,姓名,班級) VALUES (借書證號,學(xué)號,姓名,班級) IF error !=0 BEGIN ROLLBACK TRAN PRINT 辦證失敗，不能借書 RETURN END,PRINT 辦證成功，借書證號是：+借書證號 SAVE TRAN AA IF NOT EXISTS(SELECT * FROM 圖書信息 WHERE 圖書編號=圖書編號 AND 狀態(tài)=0) PRINT 該書已經(jīng)借出，借書失敗 ELSE BEGIN SELECT 借閱號=借閱號 FROM 租借信息 ORDER BY 借閱

20、號 INSERT 租借信息(借閱號, 借書證號, 圖書編號, 借書日期) VALUES(借閱號+1, 借書證號, 圖書編號,getdate() IF error !=0 BEGIN ROLLBACK TRAN aa PRINT 借書失敗 END ELSE PRINT 恭喜您借書成功 END COMMIT TRAN,2020年8月10日星期一,28,7.4.3 分布式事務(wù),如果要在事務(wù)中存取多個(gè)數(shù)據(jù)庫服務(wù)器中的數(shù)據(jù)，那么就必須使用“分布式事務(wù)” ，將工作分散到多個(gè)服務(wù)器中進(jìn)行。 執(zhí)行分布式事務(wù)很簡單，只要將 BEGIN TRANSACTION 換成BEGIN DISTRIBUTED TRANSA

21、CTION即可，其他部分都不必改動。 【例7-10】假如向“服務(wù)器1.數(shù)據(jù)庫1.表1.”插入記錄操作和向“服務(wù)器2.數(shù)據(jù)庫2.所有者名.表2.”中插入記錄操作構(gòu)成一個(gè)事務(wù)，那么這個(gè)事務(wù)就是分布式事務(wù)。,2020年8月10日星期一,29,【例7-10】假如向“服務(wù)器1.數(shù)據(jù)庫1.表1.”插入記錄操作和向“服務(wù)器2.數(shù)據(jù)庫2.所有者名.表2.”中插入記錄操作構(gòu)成一個(gè)事務(wù)，那么這個(gè)事務(wù)就是分布式事務(wù)。主要腳本如下：,BEGIN DISTRIBUTED TRAN INSERT 服務(wù)器1.數(shù)據(jù)庫1.表1(a1,a2) VALUES(a1,a2) IF error!=0 BEGIN ROLLBACK TRAN RETURN END INSERT 服務(wù)器2.數(shù)據(jù)庫2.所有者名.表2(b1,b2) VALUES(b