數據庫應用技術教程 課件 第5、6章 SQL 2022中視圖和索引的應用、數據庫編程技術基礎

知識回顧查詢沒有選修的1號課程的學生的學號和姓名。第5章SQL2022中視圖和索引的應用學習目標理解視圖的作用；掌握視圖的概念、特點和類型；掌握創建視圖、修改視圖和刪除視圖的方法；掌握查看和加密視圖定義文本；掌握通過視圖修改基表中的數據；掌握使用圖形工具管理視圖口。理解索引的優點和缺點；了解聚集索引和非聚集索引的特點；掌握索引與約束的關系；掌握使用CREATEINDEX語句創建索引的方式；掌握查看、刪除和修改索引；掌握分析和維護索引。5.1.1視圖概述

視圖的定義：視圖是一種常用的數據庫對象，可以把它看成從一個或幾個基本表導出的虛表或存儲在數據庫中的查詢。視圖概述-視圖的作用

簡化操作提高數據安全性屏蔽數據庫的復雜性數據即時更新說明：視圖一經定義后，就可以像基本表一樣可以被查詢、刪除。視圖為查看和存取數據提供了另外一種途徑。5.1.2創建視圖使用ManagementStudio使用CreateView視圖設計器關系圖窗格條件窗格SQL窗格結果窗格

創建視圖--使用ManagementStudio【例5.1】創建視圖Stu_sc1，要求顯示學生的學號、姓名、性別和選課的課程號、成績。

創建視圖--使用CreateView語法格式：CREATEVIEW視圖名[(column[,...n])][WITHENCRYPTION]ASselect_statement[WITHCHECKOPTION]參數說明如下?Column：表示視圖中的列名。WITHENCRYPTION：對包含CREATEVIEW語句文本的條目進行加密。AS：表示視圖要執行的操作。select_statement：定義視圖的SELECT語句。WITHCHECKOPTION：強制針對視圖執行的所有數據修改語句都必須符合在select_statement中設置的條件。創建視圖--使用CreateView（續）【例5.2】在學生選課數據庫中，建立信息系學生的的學號、姓名、性別和年齡視圖。CREATEVIEWIS_Student AS SELECTSno,Sname,Ssex,Sage FROMStudentWHERESdept=‘信息系'創建視圖--使用CreateView（續）【例5.3】在學生選課數據庫中，創建學生選課視圖stu_sc2，視圖包含學生學號、姓名、課程號、成績，并對創建視圖文本進行加密。代碼如下：Createviewstu_sc2(學號,姓名,課程號,成績)WithencryptionasSelectStudent.sno,sname,cno,gradeFromstudentinnerjoinscOnStudent.sno=sc.sno練習1創建一個視圖，使其統計每個學生的考試平均成績和修課總門數。練習2創建一個包含每門課程的課程號，課程名，選課人數，平均成績的視圖。練習3創建每個系的平均年齡的視圖sdept_avg_sage。在創建或使用視圖時的限制情況如果視圖中某一列是函數、數學表達式、常量或來自多個表的列名相同，則必須為列定義名字。當通過視圖操作數據時，SQLServer不僅要檢查視圖引用的表是否存在，是否有效，而且還要驗證對數據的修改是否違反了數據的完整性約束。5.1.3視圖的管理修改視圖刪除視圖查看視圖應用視圖修改視圖

通過ManagementStudio

使用ALTERVIEW語句修改視圖語法格式如下。ALTERVIEW視圖名[(column[,...n])][WITHENCRYPTION]ASselect_statement[WITHCHECKOPTION]修改視圖（續）【例5.4】將例5.1中的視圖Stu_sc1，修改為男學生選課視圖。代碼如下：AlterviewStu_sc1AsSELECTstudent.Sno學號,Sname姓名,Ssex性別,Cno課程號,Grade成績FROMscINNERJOINstudentONsc.Sno=student.SnoWhereSsex='男'刪除視圖使用Managementstudio使用DROPVIEW語句語法格式如下。DROPVIEW視圖名[,…n]【例5.5】刪除視圖sc_count視圖。代碼如下：

DROPVIEWsc_count查看視圖系統存儲過程sp_help

用來返回有關數據庫對象的詳細信息，如果不針對某一特定對象，則返回數據庫中所有對象信息。系統存儲過程sp_depends

返回系統表中存儲的任何信息，該系統表指出該對象所依賴的對象。系統存儲過程sp_helptext（若試圖加密了，就無法看了）檢索出視圖?觸發器?存儲過程的文本。5.1.4視圖的應用1、利用視圖查詢數據【例5.6】在學生選課數據庫中，查詢平均年齡超過22歲的系別和平均年齡。代碼如下：USE學生選課GOSELECT系別名稱,平均年齡Fromsdept_avg_sageWhere平均年齡>22視圖的應用2、利用視圖更新數據【例5.7】在學生選課數據庫中，利用已有視圖IS_Student(sno,sname,ssex,sage,sdept)，增加一個新的女同學“李娜”，年齡22，信息系，學號“95088”。Insertintois_studentvalues(‘95088’,‘李娜’,‘女’,22,‘信息系')5.2索引-索引的作用

索引是一種重要的數據對象，它由一行行的記錄組成，而每一行記錄都包括數據表中一列或若干列值的集合，而不是數據表中的所有記錄，因而能夠提高數據的查詢效率。此外，索引還可以用來確保列的惟一性，從而保證數據的完整性。索引的分類

聚集索引非聚集索引惟一索引包含性列索引索引視圖全文索引XML索引其中，聚集索引和非聚集索引是數據庫引擎最基本的索引索引的分類1、聚集索引（也稱簇索引或簇集索引）在聚集索引中，表中的行的物理存儲順序和索引順序完全相同(類似于圖書目錄和正文內容之間的關系)。聚集索引對表的物理數據頁，按列進行排序，然后再重新存儲到磁盤上。2、非聚集索引（也稱非簇索引或非簇集索引）非簇索引具有與表的數據行完全分離的結構，非聚集索引的葉節點存儲了組成非聚集索引的關鍵字值和一個指針，指針指向數據頁中的數據行，該行具有與索引鍵值相同的列值，非聚集索引不改變數據行的物理存儲順序，因而一個表可以有多個非聚集索引。索引的分類3、惟一索引如果為了保證表或視圖的每一行在某種程度上是惟一的，可以使用惟一索引，也就是說索引值是惟一的。創建數據表時如果設置了主鍵，則SQLServer2022就會默認建立一個惟一索引。4、包含性列索引使用包含性列索引，可以通過將非鍵列添加到非聚集索引的葉級來擴展其功能，創建覆蓋更多查詢的非聚集索引。索引的分類5、視圖索引視圖索引是為視圖創建的索引。其存儲方法與帶聚集索引的表的存儲方法相同。6、全文索引全文索引是一種特殊類型的基于標記的功能性索引，由MicrosoftSQLServer全文引擎(MSFTESQL)服務創建和維護。7、XML索引

XML索引是XML數據關聯的索引形式，是XML二進制BLOB的已拆分持久表示形式，可分為主索引和輔助索引。索引和約束的關系

對列定義PRIMARYKEY約束和UNIQUE約束時，會自動創建索引。1、PRIMARYKEY約束和索引如果創建表時，將一個特定列標識為主鍵，自動對該列創建PRIMARYKEY約束和惟一聚集索引。2、UNIQUE約束和索引默認情況下，創建UNIQUE約束，自動對該列創建惟一非聚集索引。當用戶從表中刪除主鍵約束或惟一約束時，創建在這些約束列上的索引也會被自動刪除。3、獨立索引使用CREATEINDEX語句或SQLServerManagementStudio對象資源管理器中的【新建索引】對話框創建獨立于約束的索引創建索引使用ManagementStudio使用CREATEINDEX語句CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]/*索引的類型*/INDEX索引名ON{表名|視圖名}列名[ASC|DESC][,...n])創建索引【例5.8】在學生表上創建學生學號的聚集索引。操作步驟如下。（1）啟動ManagementStudio。（2）在【對象資源管理器】中，展開【學生選課】|【表】|dbo.Student】|【索引】。在【索引】節點下，可以發現系統已默認依據設置的主鍵自動產生了一個聚集索引“PK_student”。說明：當用戶在Student表中創建主鍵約束，則SQLServer2022數據庫引擎自動對該列創建PRIMARYKEY約束和惟一聚集索引。創建索引【例5.9】在學生選課數據庫中，經常要使用學生的姓名進行查詢，為提高查詢效率，請創建姓名列為非聚集索引。代碼如下：CREATEINDEXIX_snameONstudent(sname)刪除索引使用ManagementStudio刪除獨立于約束的索引使用DROPINDEX語句刪除獨立于約束的索引【例5.10】刪除student表的索引Sname_index。Dropindexstudent.Sname_index說明：由于PK_student聚集索引是由student表在創建主鍵約束時自動創建的索引，所以無法利用DROPINDEX語句刪除索引。查看索引使用ManagementStudio用系統存儲過程sp_helpindex

可以返回表的所有索引信息，它的語法結構如下。

sp_helpindex[@objname=]’name’重命名索引

利用系統存儲過程Sp_rename更改索引的名稱，語法格式如下。

Sp_rename'表名.原索引名稱','新索引名稱'第6章數據庫編程技術基礎上節知識回顧視圖是什么？索引有什么用處？學習目標正確理解和掌握使用SQLServer變量；掌握編寫順序結構、選擇結構和循環結構的程序；掌握SQLServer函數的使用；掌握SQLServer游標的使用。6、數據庫編程技術基礎6.1SQL編程基礎6.2流程控制語句6.3函數6.4游標1.注釋在Transact-SQL中，注釋語句有“--”（雙減號）和“/*…*/”兩種表示方法。（1）嵌入行內的注釋語句（2）塊注釋語句2.變量變量是被賦予一定的值的語言元素。在T-SQL中，變量分為全局變量和局部變量:全局變量：@@開始的變量局部變量：以@開始的變量。全局變量是由系統提供且預先聲明的變量，用戶一般只能查看不能修改全局變量的值。局部變量是用戶用以保存特定類型的單個數據值的對象，它局部于一個語句批。變量的聲明在SQLServer中，局部變量必須先聲明，再使用。聲明變量的語句格式：

DECLARE@局部變量名數據類型變量名最多可以包含128個字符。局部變量的數據類型可以是系統數據類型，也可以是用戶自己定義的數據類型，但不能是text或image類型。使用DECLARE語句聲明一個局部變量后，變量的值將被初始化為NULL。變量的賦值變量的賦值語句為：

SET@局部變量名=值|表達式

SELECT@局部變量名=值|表達式SET語句是對局部變量賦值的首選方法。說明：變量只能出現在使用常數的位置上。在標準的SQL語句中，變量不能用在表、字段或其他數據庫對象的名稱的位置上，也不能用在關鍵字的位置上。示例聲明三個整型變量：@x、@y和@z，并給@x、@y變量分別賦予一個初值，然后將這兩個變量的和值賦給@z，并顯示變量@z的結果。 DECLARE@xint,@yint,@zint SET@x=10 SET@y=20 SET@z=@x+@y Print@z3.PRINT語句作用：將信息顯示在顯示器上。語法格式：PRINT字符串常量|@局部變量名|字符串表達式@局部變量名：是任意有效的字符類型的變量，此變量必須是char（或nchar）或varchar（或nvarchar）型的變量。字符串表達式：返回字符串的表達式。可包含串聯的字面值和變量。消息字符串最多可有8000個字符，超過8000個字節的任何字符均被截斷。6.2流程控制語句用于控制程序的流程，一般分為三類：順序分支循環SQLServer2022也提供對這三種流程控制的支持。T-SQL提供的主要流程控制語句語

句描

述BEGIN…END定義語句塊BREAK退出最內層的

WHILE循環CONTINUE重新開始

WHILE循環GOTO標簽從標簽所定義的標簽之后的語句處繼續進行處理IF…ELSE如果指定條件為真，執行一個分支，否則執行另一個分支RETURN無條件退出WHILE當指定條件為真時重復一些語句1.BEGIN…END語句塊BEGIN語句1語句2…ENDBEGIN…END語句塊通常是與流程控制語句IF…ELSE或WHILE一起使用的2.IF…ELSE語句“布爾表達式”表示一個測試條件，取值為True或False如果布爾表達式中包含SELECT語句，則必須將其用圓括號擴起來。IF布爾表達式語句塊1[ELSE語句塊2]處理過程為：

如果布爾表達式為True，則執行語句塊1；

如果布爾表達式為False，則執行語句塊2，如果有的話。3.WHILE語句用于設置重復執行的一個語句塊。WHILE布爾表達式

語句塊當布爾表達式為真時，重復執行語句塊（稱為循環體）；當布爾表達式為假時退出循環。示例例1：計算1+2+3+…+100的和。DECLARE@iint,@sumintSET@i=1SET@sum=0WHILE@i<=100BEGINSET@sum=@sum+@iSET@i=@i+1ENDPRINT@sum應用舉例例2：查詢選修3號課程的學生的平均成績是否大于60分，輸出相應的提示信息。例3：查詢選課表中是否有數學系的學生的選課信息，輸出相應的提示信息。ifexists(select*fromscwheresnoin(selectsnofromstudentwheresdept='數學系'))print'有數學系的同學的選課信息'elseprint'沒有數學系的同學的選課信息'CASE結構如果對于一個條件來說可能有不同的多種情況，那么對于不同的情況就應該執行不同的操作，在程序設計中，遇到這樣的情況，使用CASE語句就比較簡單。CASE具有兩種格式。1．簡單CASE表達式CASE條件表達式

WHEN表達式值1THEN結果表達式1[WHEN表達式值2THEN結果表達式2[…]][ELSE結果表達式n]END其執行過程是：用條件表達式的值依次與每一個WHEN子句的表達式值比較，直到與一個表達式值完全相同時，便將該WHEN子句指定的結果表達式返回。如果沒有任何一個WHEN子句的表達式值和條件表達式值相同這時，如果存在ELSE子句，便返回ELSE子句之后的結果表達式；如果不存在ELSE子句，便返回一個NULL值。簡單CASE表達式示例例4使用簡單CASE結構實現以下功能：輸出課程號、課程名、開課學期，開課學期用“第幾學期”表示代碼如下：selectcno,cname,開課學期=casesemesterwhen'1'then'第一學期'when'2'then'第二學期'when'3'then'第三學期'when'4'then'第四學期'when'5'then'第五學期'endfromcourse

運行結果如下圖所示。

2．搜索CASE表達式搜索CASE表達式語法格式為：CASEWHEN邏輯表達式1THEN結果表達式1[WHEN邏輯表達式2THEN結果表達式2[…]][ELSE結果表達式n]END其執行過程是：測試每個WHEN子句后的邏輯表達式，如果結果為TRUE，則返回相應的結果表達式，否則檢查是否有ELSE子句，如果存在ELSE子句，便返回ELSE子句之后的結果表達式；如果不存在ELSE子句，便返回一個NULL值。例5使用搜索CASE表達式實現同樣的功能。selectcno,cname,開課學期=casewhensemester='1'then'第一學期'whensemester='2'then'第二學期'whensemester='3'then'第三學期'whensemester='4'then'第四學期'whensemester='5'then'第五學期'endfromcourse練習：請嘗試使用case語句實現以下效果。6.3函數例6輸出當前日期。PRINTGETDATE()例7計算兩個日期之間相關的天數。PRINTDATEDIFF(DAY,'11/11/2021','7/10/2001')輸出結果如下：-7429

例8輸出當前日期，顯示為當前是XXXX年。print'當前是:'+convert(char(4),year(getdate()))+'年'例9顯示當前數據庫的名稱和標識號。Use學生選課

Go SelectDB_Name() SelectDB_id()Go6.4游標6.4.1游標概念6.4.2游標的使用6.4.3游標應用示例6.4.1游標概念如何從某一結果集中逐一地讀取一條記錄？游標實際上是一種能從包括多條數據記錄的結果集中每次提取一條記錄的機制游標總是與一條T_SQL選擇語句相關聯游標把作為面向集合的數據庫管理系統和面向行的程序設計兩者聯系起來，使兩個數據處理方式能夠進行溝通…游標當前行指針游標結果集游標特點允許定位在結果集的特定行。從結果集的當前位置檢索一行或多行。支持對結果集中當前位置的行進行數據修改。游標種類Transact_SQL游標 由DECLARECURSOR語法定義主要用在Transact_SQL腳本，存儲過程和觸發器中API游標 支持在OLEDBODBC以及DB_library中使用游標函數，主要用在服務器上。客戶游標 主要是當在客戶機上緩存結果集時才使用。6.4.2游標的使用是否聲明游標打開游標提取數據處理完成？關閉游標釋放資源聲明游標DECLAREcursor_nameCURSOR[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWARD][READ_ONLY|SCROLL_LOCKS|OPTIMISTIC][TYPE_WARNING]FORselect_statement[FORUPDATE[OFcolumn_name[,…n]]]打開游標OPEN{cursor_name|cursor_variable_name}提取數據

FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{cursor_name|@cursor_variable_name}[INTO@variable_name[,...n]]@@FETCH_STATUS可以使用@@FETCH_STATUS全局變量判斷數據提取的狀態。@@FETCH_STATUS返回FETCH語句執行后的游標最終狀態。返回值含義0FETCH語句成功。-1FETCH語句失敗或此行不在結果集中。-2被提取的行不存在。例9：在學生選課數據庫中逐行讀取數據declare

cur_stu

cursor

forselect*from

studentopen

cur_stufetch

next

from

cur_stuwhile

@@FETCH_STATUS=0beginfetch

next

from

cur_stuend關閉游標CLOSE{cursor_name|cursor_variable_name}在使用CLOSE語句關閉某游標后，系統并沒有完全釋放游標的資源，并且也沒有改變游標的定義，當再次使用OPEN語句時可以重新打開此游標。釋放游標釋放分配給游標的所有資源。

DEALLOCATE{cursor_name| cursor_variable_name}釋放游標就釋放了與該游標有關的一切資源，包括游標的聲明，以后就不能再使用OPEN語句打開此游標了

6.4.3游標應用示例

例10：使用游標處理學生表中姓陳的同學信息。DECLAREname_curCURSORFORSELECTsnameFROMstudentWHEREsnameLIKE'陳%'ORDERBYsnameOPEN

name_cur--首先提取第一行數據FETCHNEXTFROMname_cur例10（續）WHILE@@FETCH_STATUS=0--若讀取成功BEGINFETCHNEXTFROMname_curENDCLOSE

name_curDEALLOCATE

name_cur例11：將FETCH語句的輸出存儲在局部變量--聲明用于存儲FETCH返回結果的局部變量DECLARE@var_snochar(5),

@var_snamechar(20)DECLAREstu_cursorCURSOR