數(shù)據(jù)庫(kù)應(yīng)用技術(shù)教程 課件 第5章 SQL 2022中視圖和索引的應(yīng)用

知識(shí)回顧查詢沒有選修的1號(hào)課程的學(xué)生的學(xué)號(hào)和姓名。第5章SQL2022中視圖和索引的應(yīng)用學(xué)習(xí)目標(biāo)理解視圖的作用；掌握視圖的概念、特點(diǎn)和類型；掌握創(chuàng)建視圖、修改視圖和刪除視圖的方法；掌握查看和加密視圖定義文本；掌握通過視圖修改基表中的數(shù)據(jù)；掌握使用圖形工具管理視圖口。理解索引的優(yōu)點(diǎn)和缺點(diǎn)；了解聚集索引和非聚集索引的特點(diǎn)；掌握索引與約束的關(guān)系；掌握使用CREATEINDEX語(yǔ)句創(chuàng)建索引的方式；掌握查看、刪除和修改索引；掌握分析和維護(hù)索引。5.1.1視圖概述

視圖的定義：視圖是一種常用的數(shù)據(jù)庫(kù)對(duì)象，可以把它看成從一個(gè)或幾個(gè)基本表導(dǎo)出的虛表或存儲(chǔ)在數(shù)據(jù)庫(kù)中的查詢。視圖概述-視圖的作用

簡(jiǎn)化操作提高數(shù)據(jù)安全性屏蔽數(shù)據(jù)庫(kù)的復(fù)雜性數(shù)據(jù)即時(shí)更新說明：視圖一經(jīng)定義后，就可以像基本表一樣可以被查詢、刪除。視圖為查看和存取數(shù)據(jù)提供了另外一種途徑。5.1.2創(chuàng)建視圖使用ManagementStudio使用CreateView視圖設(shè)計(jì)器關(guān)系圖窗格條件窗格SQL窗格結(jié)果窗格

創(chuàng)建視圖--使用ManagementStudio【例5.1】創(chuàng)建視圖Stu_sc1，要求顯示學(xué)生的學(xué)號(hào)、姓名、性別和選課的課程號(hào)、成績(jī)。

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

通過ManagementStudio

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

DROPVIEWsc_count查看視圖系統(tǒng)存儲(chǔ)過程sp_help

用來返回有關(guān)數(shù)據(jù)庫(kù)對(duì)象的詳細(xì)信息，如果不針對(duì)某一特定對(duì)象，則返回?cái)?shù)據(jù)庫(kù)中所有對(duì)象信息。系統(tǒng)存儲(chǔ)過程sp_depends

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

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

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

XML索引是XML數(shù)據(jù)關(guān)聯(lián)的索引形式，是XML二進(jìn)制BLOB的已拆分持久表示形式，可分為主索引和輔助索引。索引和約束的關(guān)系

對(duì)列定義PRIMARYKEY約束和UNIQUE約束時(shí)，會(huì)自動(dòng)創(chuàng)建索引。1、PRIMARYKEY約束和索引如果創(chuàng)建表時(shí)，將一個(gè)特定列標(biāo)識(shí)為主鍵，自動(dòng)對(duì)該列創(chuàng)建PRIMARYKEY約束和惟一聚集索引。2、UNIQUE約束和索引默認(rèn)情況下，創(chuàng)建UNIQUE約束，自動(dòng)對(duì)該列創(chuàng)建惟一非聚集索引。當(dāng)用戶從表中刪除主鍵約束或惟一約束時(shí)，創(chuàng)建在這些約束列上的索引也會(huì)被自動(dòng)刪除。3、獨(dú)立索引使用CREATEINDEX語(yǔ)句或SQLServerManagementStudio對(duì)象資源管理器中的【新建索引】對(duì)話框創(chuàng)建獨(dú)立于約束的索引創(chuàng)建索引使用ManagementStudio使用CREATEINDEX語(yǔ)句CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]/*索引的類型*/INDEX索引名ON{表名|視圖名}列名[ASC|DESC][,...n])創(chuàng)建索引【例5.8】在學(xué)生表上創(chuàng)建學(xué)生學(xué)號(hào)的聚集索引。操作步驟如下。（1）啟動(dòng)ManagementStudio。（2）在【對(duì)象資源管理器】中，展開【學(xué)生選課】|【表】|dbo.Student】|【索引】。在【索引】節(jié)點(diǎn)下，可以發(fā)現(xiàn)系統(tǒng)已默認(rèn)依據(jù)設(shè)置的主鍵自動(dòng)產(chǎn)生了一個(gè)聚集索引“PK_student”。說明：當(dāng)用戶在Student表中創(chuàng)建主鍵約束，則SQLServer2022數(shù)據(jù)庫(kù)引擎自動(dòng)對(duì)該列創(chuàng)建PRIMARYKEY約束和惟一聚集索引。創(chuàng)建索引【例5.9】在學(xué)生選課數(shù)據(jù)庫(kù)中，經(jīng)常要使用學(xué)生的姓名進(jìn)行查詢，為提高查詢效率，請(qǐng)創(chuàng)建姓名列為非聚集索引。代碼如下：CREATEINDEXIX_snameONstudent(sname)刪除索引使用ManagementStudio刪除獨(dú)立于約束的索引使用DROPINDEX語(yǔ)句刪除獨(dú)立于約束的索引【例5.10】刪除student表的索引Sname_index。Dropindexstudent.Sname_index說明：由于PK_student聚集索引是由student表在創(chuàng)建主鍵約