數(shù)據(jù)庫原理與應(yīng)用教程―SQL Server 2019 課件 第10章視圖與索引

數(shù)據(jù)庫原理與應(yīng)用教程

―SQLServer2019第10章視圖和索引第10章視圖和索引

數(shù)據(jù)庫的基本表是按照數(shù)據(jù)庫設(shè)計人員的觀點設(shè)計的，并不一定符合所有用戶的需求。SQLServer可以根據(jù)用戶需求重新定義表的數(shù)據(jù)結(jié)構(gòu)，這種數(shù)據(jù)結(jié)構(gòu)就是視圖（用戶外模式）。數(shù)據(jù)庫中的索引與書籍中的目錄類似，在一本書中，目錄可以方便用戶不必閱讀整本書就能找到需要的內(nèi)容。同樣，在數(shù)據(jù)庫中，索引可以使數(shù)據(jù)庫程序在最短的時間內(nèi)找到所需要的數(shù)據(jù)，而不必查找整個數(shù)據(jù)庫，從而提高查找效率。第10章視圖和索引在數(shù)據(jù)庫的三級模式結(jié)構(gòu)當(dāng)中，索引對應(yīng)的是內(nèi)模式部分，基本表對應(yīng)的是模式部分，而視圖對應(yīng)的是外模式部分。本章主要內(nèi)容：視圖的基本概念、視圖的操作索引的基本概念、索引的操作三級模式結(jié)構(gòu)的一個具體實例視圖1圖書信息出版社名稱字符型20書名字符型30作者姓名字符型20出版日期日期型

視圖2作者著書信息作者姓名字符型20書名字符型30視圖3出版社出書類型出版社名稱字符型20書名字符型30類型字符型1

作者

作品

出版社

數(shù)據(jù)文件數(shù)據(jù)文件數(shù)據(jù)文件

索引文件索引文件索引文件

作者

作品

出版社

作者編號字符型5書號字符型6出版社編號字符型5

作者姓名字符型20書名字符型30出版社名稱字符型20

通信地址字符型50類型字符型1城市字符型10

郵編字符型6價格貨幣

電話字符型11出版日期日期型

外模式模式內(nèi)模式第10章視圖和索引10.1視圖視圖(View，外模式)是從一個或幾個基本表（模式）導(dǎo)出來的表，是一個虛表，并不表示任何物理數(shù)據(jù)。數(shù)據(jù)庫中只存儲視圖的定義，而不存儲視圖對應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存儲在導(dǎo)出視圖的基本表中*。10.1視圖10.1.1視圖概述

數(shù)據(jù)庫的基本表是按照數(shù)據(jù)庫設(shè)計人員的觀點設(shè)計的，并不一定符合所有用戶的需求。SQLServer可以根據(jù)用戶需求重新定義表的數(shù)據(jù)結(jié)構(gòu)，這種數(shù)據(jù)結(jié)構(gòu)就是視圖（用戶外模式）。10.1視圖10.1.1視圖概述視圖的優(yōu)點**：(1)為用戶集中數(shù)據(jù)，簡化用戶的數(shù)據(jù)查詢和處理。(2)保證數(shù)據(jù)的邏輯獨立性。(3)重新定制數(shù)據(jù)，使得數(shù)據(jù)便于共享。(4)提高了數(shù)據(jù)的安全性。

10.1.2創(chuàng)建視圖視圖是數(shù)據(jù)庫中一個獨立的對象，創(chuàng)建時應(yīng)該遵循以下原則。①只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建視圖。②視圖名稱必須遵循標識符的規(guī)則，且對每個用戶必須唯一。③用戶可以在其他視圖之上建立視圖。④如果視圖中的某一列是一個算術(shù)表達式、內(nèi)置函數(shù)或常量派生而來，那么，用戶需要為其指定特定的名稱*。10.1視圖1．在SQLServerManagementStudio使用向?qū)?chuàng)建視圖(例：創(chuàng)建男生視圖male_view)2．使用T-SQL語句創(chuàng)建視圖SQLServer提供了CREATEVIEW語句創(chuàng)建視圖，語法格式如下：CREATEVIEW[schema_name.]view_name[(column_name[,...n])][with<view_attribute>[…n]]ASselect_statement

[WITHCHECKOPTION]10.1.2創(chuàng)建視圖[例10-1]創(chuàng)建視圖s_c_sc

，包括電子信息專業(yè)的學(xué)生的學(xué)號、姓名，和他們選修的課程號、課程名和成績。USEteaching

GOCREATEVIEWs_c_scASSELECTstudent.sno,sname,o,cname,scoreFROMstudent,sc,courseWHEREstudent.sno=sc.snoANDo=oANDspecialty='電子信息'GO10.1.2創(chuàng)建視圖【例10-2】針對教材附錄的實驗中bankcard數(shù)據(jù)庫,創(chuàng)建名為“acc_count”的賬戶統(tǒng)計視圖，求每個儲戶的賬戶個數(shù)，要求包括身份證號和姓名。USEbankcardGOCREATEVIEWacc_countASSELECTdepositor.IDNO,Dname,COUNT(*)ASNumberFROMdepositor,accountWHEREdepositor.IDNO=account.IDNOGROUPBYdepositor.IDNO,DnameGO10.1.2創(chuàng)建視圖10.1.3修改視圖1．在SQLServerManagementStudio中修改2．T-SQL提供了ALTERVIEW語句修改視圖，語法格式如下：ALTERVIEW[schema_name.]view_name[(column_name[,...n])][with<view_attribute>[…n]]ASselect_statement[WITHCHECKOPTION]注：除命令詞與CREATEVIEW不同之外，其他子命令和參數(shù)都相同。10.1視圖【例10-3】修改“acc_count”視圖，求每個儲戶的賬戶個數(shù)和總存款余額，要求包括身份證號和姓名。USEbankcardGOALTERVIEWacc_countASSELECTdepositor.IDNO,Dname,COUNT(*)ASNumber,SUM(Balance)SumBalanceFROMdepositor,accountWHEREdepositor.IDNO=account.IDNOGROUPBYdepositor.IDNO,DnameGO10.1.3修改視圖【例10-4】在視圖上創(chuàng)建視圖：創(chuàng)建“Few_Balance”余額統(tǒng)計視圖，求總存款余額少于5000的儲戶信息，包括身份證號和姓名和手機號。USEbankcardGOCREATEVIEWFew_BalanceASSELECTdepositor.IDNO,depositor.Dname,TelephoneFROMacc_count,depositorWHEREacc_count.IDNO=depositor.IDNOandSumBalance<5000GO10.1.3修改視圖10.1.4使用視圖視圖創(chuàng)建完畢，可以同查詢基本表一樣通過視圖查詢所需要的數(shù)據(jù)，也可以通過視圖更新基表中數(shù)據(jù)。1．使用視圖進行數(shù)據(jù)查詢可以在SQLServerManagementStudio中選中要查詢的視圖并打開，瀏覽該視圖查詢的所有數(shù)據(jù)；也可以在查詢窗口中執(zhí)行T-SQL語句查詢視圖。例如，要查詢各儲戶的賬戶統(tǒng)計信息，可以右擊“acc_count

”，選擇”選擇前1000行”或“編輯前200行”選項；也可以執(zhí)行T-SQL語句：SELECT*FROMacc_count

10.1視圖【例10-5】在查詢窗口中查詢s_c_sc視圖，統(tǒng)計“C++語言”課程的總分和平均分。USEteachingSELECTsumscore=SUM(score),avgscore=AVG(score)FROMs_c_scWHEREcname='C++語言'10.1.4使用視圖【例10-6】查詢“acc_count”視圖中儲戶“張青”的統(tǒng)計信息。USEbankcardSELECT*FROMacc_countWHEREDname='張青'10.1.4使用視圖2．使用視圖更新基本表中數(shù)據(jù)更新視圖的數(shù)據(jù)，其實就是對基本表進行更新，因為真正存放數(shù)據(jù)的地方是基本表而不是視圖。同樣使用INSERT、UPDATE、DELETE語句來完成數(shù)據(jù)的插入、修改和刪除。注意：并不是所有的視圖都可以更新數(shù)據(jù)，只有對滿足可更新條件的視圖才能更新數(shù)據(jù)。10.1.4使用視圖可更新條件*：(1)任何通過視圖的數(shù)據(jù)更新（包括UPDATE、INSERT和DELETE語句）都只能引用一個基本表的列。①如果視圖數(shù)據(jù)為一個表的行、列子集，則此視圖可更新（包括UPDATE、INSERT和DELETE語句）；但如果視圖中沒有包含表中某個不允許取空值又沒有默認值約束的列，則不能利用視圖插入數(shù)據(jù)。②如果視圖所依賴的基本表有多個時，完全不能向該視圖添加（INSERT）數(shù)據(jù)。10.1.4使用視圖③若視圖依賴于多個基本表，那么一次修改只能修改（UPDATE）一個基本表中的數(shù)據(jù)。④若視圖依賴于多個基本表，那么不能通過視圖刪除（DELETE）數(shù)據(jù)。(2)視圖中被修改的列必須直接引用表列中的基礎(chǔ)數(shù)據(jù)。不能是通過任何其他方式對這些列進行派生而來的數(shù)據(jù)，比如通過聚合函數(shù)、計算（如表達式計算）、集合運算等。(3)被修改的列不應(yīng)是在創(chuàng)建視圖時受GROUPBY、HAVING、DISTINCT或TOP子句影響的。10.1.4使用視圖注意：有可能插入并不滿足視圖查詢的WHERE子句條件中的一行。為了進行限制此操作，可以在創(chuàng)建視圖時使用WITHCHECKOPTION**選項。10.1.4使用視圖【例10-7】通過“male_view”視圖向“student”表中插入一個“男”生。INSERTINTOmale_viewVALUES('2023010005','張三','男','2005-6-1','電子信息','2023級')如果通過“male_view”視圖向“student”表中插入一個“女”生，也可以完成。10.1.4使用視圖如果不希望用戶通過“male_view”視圖插入“女”生，在創(chuàng)建“male_view”視圖時應(yīng)該使用WITHCHECKOPTION選項。命令如下：CREATEVIEWmale_viewASSELECTsno,sname,ssex,sbirthday,specialty,gradeFROMstudentWHEREssex='男'WITHCHECKOPTION10.1視圖10.1.5刪除視圖在不需要該視圖的時候，可以刪除該視圖。1．在SQLServerManagementStudio刪除視圖選中要刪除的視圖，右擊選擇“刪除”命令。2．T-SQL提供了視圖刪除語句DROPVIEW。語法格式：DROPVIEWview_name【例10-8】刪除例10-1創(chuàng)建的s_c_sc視圖。USEteachingGODROPVIEWs_c_scGO

10.1視圖第10章視圖和索引10.2索引索引(Index)是對數(shù)據(jù)庫表中一個或多個列的值進行排序的結(jié)構(gòu)，其主要目的是提高SQLServer系統(tǒng)的性能，加快查詢數(shù)據(jù)的速度。10.2.1索引簡介數(shù)據(jù)庫的索引就類似于書籍的目錄，如果想快速查找而不是逐頁查找指定的內(nèi)容，可以通過目錄中章節(jié)的頁號找到其對應(yīng)的內(nèi)容。類似地，索引通過記錄表中的關(guān)鍵值指向表中的記錄，不用掃描整個表而定位到相關(guān)的記錄。10.2索引索引的優(yōu)點**：（1）大大加快數(shù)據(jù)的檢索速度，這是創(chuàng)建索引的最主要的原因。（2）創(chuàng)建唯一性索引，保證表中每一行數(shù)據(jù)的唯一性。（3）加速表和表之間的連接。（4）在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間。（5）查詢優(yōu)化器可以提高系統(tǒng)的性能，但它是依靠索引起作用的。10.2索引10.2.2索引類型SQLServer支持在表中任何列上定義索引。索引可以是唯一的，即索引列不會有兩行記錄相同，這樣的索引稱為唯一索引。例如，如果在表中的“姓名”列上創(chuàng)建了唯一索引，則以后輸入的姓名將不能同名。索引也可以是不唯一的，即索引列上可以有多行記錄相同。如果索引是根據(jù)單列創(chuàng)建的，稱為單列索引，根據(jù)多列組合創(chuàng)建的索引則稱為復(fù)合索引。根據(jù)索引的組織方式的不同，又可以將索引分為聚集索引和非聚集索引。10.2.2索引類型

1.聚集索引聚集索引會對表中數(shù)據(jù)進行物理排序，所以這種索引對查詢非常有效，在表和視圖中只能有一個聚集索引。當(dāng)建立主鍵約束時，如果表中沒有聚集索引，SQLServer會用主鍵列作為聚集索引鍵?？梢栽诒淼娜魏瘟谢蛄械慕M合上建立索引，但實際應(yīng)用中一般為定義成主鍵約束的列建立聚集索引。例如，漢語字典的正文就是一個聚集索引的順序結(jié)構(gòu)（拼音順序）。比如，要查“安”字，就可以翻開字典的前幾頁，因為“安”的拼音是“an”。如果翻完了所有“an”讀音的部分仍然找不到這個字，那么就說明字典中沒有這個字。同樣，如果查“張”字，可以將字典翻到最后部分，因為“張”的拼音是“zhang”。因為字典的正文內(nèi)容本身就是按照音序排列的，而“漢語拼音音節(jié)索引”就可以稱為“聚集索引”。10.2.2索引類型2.非聚集索引非聚集索引不會對表中數(shù)據(jù)進行物理排序。如果表中不存在聚集索引，則表是未排序的。例如，查字典時，不認識的字按照上面的方法很難查找?？梢愿鶕?jù)“偏旁部首”來查。比如查“張”字，在查部首之后的檢字表中“張”字對應(yīng)的頁碼是622頁，檢字表中“張”的上面是“弛”，對應(yīng)的頁碼卻是60頁，“張”的下面是“弟”，對應(yīng)的頁碼是95頁，正文中這些字并不相鄰。所以“偏旁部首”索引中的排序與字典正文不一致。10.2.2索引類型那么，按部首查字就要兩個步驟：先找到部首查字法即“偏旁部首”索引目錄中的結(jié)果，然后再翻到所需要的頁碼。這種目錄純粹是目錄，正文純粹是正文的排序方式就稱為“非聚集索引”。在表或視圖中，最多可以建立250個非聚集索引，或者249個非聚集索引和1個聚集索引。注*：SQLServer中不管聚集還是非聚集索引都采用B+樹的存儲結(jié)構(gòu)。10.2.2索引類型B-樹實例圖10.2.2索引類型B+樹實例圖馬龍

鄭曉娟康劉馬龍齊菲鄭曉娟蔡紅范小麗馬龍宋張鄭曉娟寧潘齊菲戴鄧范小麗安白蔡紅

假定此B+樹為按學(xué)生姓名創(chuàng)建的非聚集索引，下面的指針指向?qū)W生表中相應(yīng)的學(xué)生信息。10.2.2索引類型10.2索引10.2.3創(chuàng)建索引注意：創(chuàng)建時，哪些列適合創(chuàng)建索引，哪些列不適合創(chuàng)建索引，需要進行詳細的考察。

1．創(chuàng)建索引時應(yīng)考慮的問題*（1）對一個表中建大量的索引，應(yīng)進行權(quán)衡**。對于SELECT查詢，大量索引可以提高性能，可以從中選擇最快的查詢方法；但是，會影響INSERT、UPDATE和DELETE語句的性能，因為對表中的數(shù)據(jù)進行修改時，索引也要動態(tài)的維護，所以應(yīng)避免對經(jīng)常更新的表建立過多的索引，而對更新少而且數(shù)據(jù)量大的表創(chuàng)建多個索引，可以大大提高查詢性能。10.2.3創(chuàng)建索引（2）對于小型表（行數(shù)較少）進行索引可能不會產(chǎn)生優(yōu)化效果。（3）對于主鍵和外鍵列應(yīng)考慮建索引，因為經(jīng)常通過主鍵查詢數(shù)據(jù)，而外鍵用于表間的連接。（4）很少在查詢中使用的列以及值很少的列不應(yīng)考慮建索引。（5）視圖中如果包含聚合函數(shù)或連接時，創(chuàng)建視圖的索引可以顯著提升查詢性能。2．通過SQLServerManagementStudio,使用向?qū)?chuàng)建索引是一種圖形界面環(huán)境下最快捷的創(chuàng)建方式3．利用T-SQL語句創(chuàng)建索引基本語法格式：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED（默認）]INDEXindex_name

ON{table_name|view_name}(column[ASC|DESC][,...n])10.2.3創(chuàng)建索引[例10-9]根據(jù)教學(xué)庫中學(xué)生表的姓名列的升序創(chuàng)建一個名為index_sname的普通索引。CREATEINDEXindex_snameONstudent(sname)GO分析受益的語句：如：Select*fromstudentwheresname=‘張強’再如：updatestudentsetspecialty=‘網(wǎng)絡(luò)工程’

wheresname=‘張強’10.2.3創(chuàng)建索引【例10-10】根據(jù)teaching庫中student表的專業(yè)、年級創(chuàng)建一個名為specialty_grade的復(fù)合索引，其中專業(yè)稱為升序，年級為降序。USEteachingGOCREATEINDEXspecialty_gradeONstudent(specialtyASC,gradeDESC)10.2.3創(chuàng)建索引4．間接創(chuàng)建索引在定義表結(jié)構(gòu)或修改表結(jié)構(gòu)時，如果定義了主鍵約束（PRAMARYKEY）或者唯一性約束（UNIQUE），可以間接創(chuàng)建索引。[例10-11]創(chuàng)建一個s3表，并定義了主鍵約束。USEteachingGOCREATETABLEs3(snochar(6)PRIMARYKEY,snamechar(8))此例中，就按sno升序創(chuàng)建了一個聚集索引。10.2.3創(chuàng)建索引[例10-12]創(chuàng)建一個教師表，并定義了主鍵約束和唯一性約束。USEteachingGOCREATETABLEteacher(tnochar(6)PRIMARYKEY