第5章 索引與視圖

第5章索引與視圖

第5章索引與視圖5.1

索引概述5.2索引的操作5.3

視圖概述5.4

視圖的操作5.5

視圖的應用235.1索引45.1.1索引的基本概念

索引是對數(shù)據(jù)庫表中一個或多個字段的值進行排序而創(chuàng)建的一種分散存儲結構。建立索引的目的有以下幾點：

(1)加速數(shù)據(jù)檢索

(2)加速連接、ORDERBY和GROUPBY等操作

(3)查詢優(yōu)化器依賴于索引起作用

(4)強制實行的唯一性什么是索引漢語字典中的漢字按頁存放，一般都有漢語拼音目錄（索引）、偏旁部首目錄等我們可以根據(jù)拼音或偏旁部首，快速查找某個字詞5.1.1索引的基本概念5IndexesUseKeyValuestoLocateData（根據(jù)索引鍵查找定位數(shù)據(jù)行）

DataPages（數(shù)據(jù)頁）…IndexPages（索引頁）

什么是索引5.1.1索引的基本概念6什么是索引SQLServer中的數(shù)據(jù)也是按頁（4KB）存放索引：是SQLServer編排數(shù)據(jù)的內(nèi)部方法。它為SQLServer提供一種方法來編排查詢數(shù)據(jù)。索引頁：數(shù)據(jù)庫中存儲索引的數(shù)據(jù)頁；索引頁類似于漢語字（詞）典中按拼音或筆畫排序的目錄頁。索引的作用：通過使用索引，可以大大提高數(shù)據(jù)庫的檢索速度，改善數(shù)據(jù)庫性能。5.1.1索引的基本概念785.1.2索引的分類根據(jù)數(shù)據(jù)庫的功能，在SQLServer2012中可創(chuàng)建3種類型的索引，即唯一性索引、主鍵索引和聚集索引。

1．唯一性索引在表中建立唯一性索引時，組成該索引的字段或字段組合在表中具有唯一值，也就是說，對于表中的任何兩行記錄來說，索引鍵的值都是各不相同。

唯一性索引要求組成該索引的字段或字段組合不能在多行記錄中具有NULL值。

92．主鍵索引表中通常有一個字段或一些字段組的合，其值用來唯一標識表中的每一行記錄，該字段或字段組合稱為表的主鍵。主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每個值是唯一的，并且不能為空103．聚集索引(Clustered)：在聚集索引中，表中各記錄的物理順序與鍵值的邏輯(索引)順序相同。只有在表中建立了一個聚集索引后，數(shù)據(jù)才會按照索引鍵值指定的順序存儲到表中。由于一個表中的數(shù)據(jù)只能按照一種順序來存儲，所以在一個表中只能建立一個聚集索引。非聚集索引(Non-clustered)：非聚集索引指定表的邏輯順序。數(shù)據(jù)存儲在一個位置，索引存儲在另一個位置，索引中包含指向數(shù)據(jù)存儲位置的指針?？梢杂卸鄠€，小于249個索引分類【例】在學生表中查詢學號sno為“20021003012”的行（在列sno上已建立聚集索引）索引表數(shù)據(jù)表11索引分類【例】在學生表中查詢學號s_no為“20021003012”的行（在列s_no上已建立非聚集索引）索引表數(shù)據(jù)表12135.1.3索引的操作1.創(chuàng)建索引使用企業(yè)管理器向導建立索引使用企業(yè)管理器直接創(chuàng)建索引創(chuàng)建索引使用T-SQL創(chuàng)建基本語句格式：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX

索引名

ON{表|視圖

}（列

[ASC|DESC][,...n]）UNIQUECLUSTERED

NONCLUSTEREDASC|DESC創(chuàng)建唯一索引創(chuàng)建聚集索引創(chuàng)建非聚集索引創(chuàng)建排序方式，默認為升序(ASC)14創(chuàng)建索引例5-1在學院表中，除了學院編號字段是唯一的，學院名稱字段也具有唯一值，對學院表中的學院名稱字段建立唯一索引。創(chuàng)建唯一索引命令為：CREATEUNIQUEINDEXSCHOOLNAMEON

學院(學院名稱)例5-2學院表中已存在一個主鍵，現(xiàn)在學院名稱字段上再創(chuàng)建聚集索引，看看結果如何？CREATEUNIQUECLUSTEREDINDEX學院ON學院(學院名稱)1516例5-3在學院表中，如果會經(jīng)常對學院名稱、學院地址和學院電話進行查詢，可以建立以學院名稱、學院地址和學院電話為關鍵字的索引。

CREATENONCLUSTEREDINDEXSCHOOLON學院(學院名稱,學院地址,學院電話)創(chuàng)建索引5.1.4查看索引使用企業(yè)管理器查看索引使用系統(tǒng)存儲過程查看索引語法格式：sp_helpindex'name'其中'name'子句為指定當前數(shù)據(jù)庫中的表的名稱。例5-4查看學院表的索引，其操作為：use學生成績管理系統(tǒng)數(shù)據(jù)庫gosp_helpindex學院175.1.5刪除索引例5-5

刪除學院表內(nèi)名為SCHOOL的索引。

use學生成績管理系統(tǒng)數(shù)據(jù)庫goIFEXISTS(SELECTnameFROMsysindexesWHEREname='SCHOOL')DROPINDEXSCHOOLON學院

刪除索引語句的語法格式如下：DROPINDEXindex_name[,…n]ON[database_name.[schema_name].|schema_name.]table_or_vlew_name18索引的優(yōu)缺點優(yōu)點加快訪問速度加強行的唯一性缺點帶索引的表在數(shù)據(jù)庫中需要更多的存儲空間操縱數(shù)據(jù)的命令需要更長的處理時間，因為它們需要對索引進行更新19創(chuàng)建索引的指導原則請按照下列標準選擇建立索引的列。該列用于頻繁搜索該列用于對數(shù)據(jù)進行排序請不要使用下面的列創(chuàng)建索引：列中僅包含幾個不同的值。表中僅包含幾行。為小型表創(chuàng)建索引可能不太劃算，因為SQLServer在索引中搜索數(shù)據(jù)所花的時間比在表中逐行搜索所花的時間更長205.2視圖5.2.1視圖的概念CREATEVIEWview_stuInfo_stuMarksASSELECT姓名=stuName,

學號=stuInfo.stuNo,…基于學員信息表和成績表創(chuàng)建視圖教員需要的視圖：

方便查看學員的成績班主任需要的視圖：

方便查看學員的檔案5.2.1視圖的概念1、什么是視圖視圖是一張?zhí)摂M表，它表示一張表的部分數(shù)據(jù)或多張表的綜合數(shù)據(jù)，其結構和數(shù)據(jù)是建立在對表的查詢基礎上視圖中并不存放數(shù)據(jù)，而是存放在視圖所引用的原始表（基表）中同一張原始表，根據(jù)不同用戶的不同需求，可以創(chuàng)建不同的視圖232.視圖的作用

視圖通常用來集中、簡化和自定義每個用戶對數(shù)據(jù)庫的不同認識。視圖可用作安全機制，方法是允許用戶通過視圖訪問數(shù)據(jù)，而不授予用戶直接訪問視圖基礎表的權限。

(1)簡化操作(2)定制數(shù)據(jù)

(3)導出數(shù)據(jù)(4)安全性24視圖的用途篩選表中的行防止未經(jīng)許可的用戶訪問敏感數(shù)據(jù)降低數(shù)據(jù)庫的復雜程度將多個物理數(shù)據(jù)庫抽象為一個邏輯數(shù)據(jù)庫5.2視圖的操作5.2.1創(chuàng)建視圖1.使用企業(yè)管理器創(chuàng)建視圖2.使用Transact-SQL語句建立視圖語法結構：CREATEVIEW[database_name.][owner_name.]view_name[(column[,])][WITH{ENCRYPTION|SCHEMABINDING|VIEW_METADATA}]ASselect_statement[WITHCHECKOPTION]25

1、為定制特定數(shù)據(jù)視圖

例5-6如果經(jīng)常要查詢課程的名稱、學分、學時和課程性質，表中的其他不需要，就只關注這些數(shù)據(jù)，為這些特定的數(shù)據(jù)建立一個視圖。CREATEVIEW課程基本信息

ASSELECT課程名稱,學時數(shù),學分數(shù),課程性質FROM課程;例5-7

在學生成績管理系統(tǒng)數(shù)據(jù)庫中創(chuàng)建學分數(shù)大于等于3分的相關課程信息視圖，該視圖選擇1個基表(課程表)中的數(shù)據(jù)來顯示學分大于等于3分的虛擬表。CREATEVIEW大于等于3學分課程信息

ASSELECT*FROM課程

where學分數(shù)>=3265-8創(chuàng)建視圖：程序設計成績信息，要求能顯示：“學號,姓名,專業(yè)班級,課程名稱,成績”等數(shù)據(jù)CREATEVIEW程序設計成績信息ASSELECT學生.學號,姓名,專業(yè)班級,課程名稱,成績FROM學生innerjoin選課成績on學生.學號=選課成績.學號

innerjoin課堂on選課成績.課堂編號=課堂.課堂編號

innerjoin課程on課堂.課程編號=課程.課程編號WHERE課程名稱='C++程序設計基礎'

2、為簡化SQL語句創(chuàng)建視圖

例5-9

要查詢土木工程和工程力學兩個專業(yè)在2017-2018學年均開過的必修課程，如果不用視圖的話,語句為:

SELECTdistinct課程.課程編號,課程.課程名稱

FROM課程INNERJOIN課堂ON課程.課程編號=課堂.課程編號INNERJOIN

選課成績ON課堂.課堂編號=選課成績.課堂編號INNERJOIN學生

ON選課成績.學號=學生.學號

WHERE課堂.開課年份='2017-2018'AND課程.課程性質='必修'AND學生.專業(yè)班級LIKE'土木工程%'INTERSECTSELECTdistinct課程.課程編號,課程.課程名稱

FROM課程INNERJOIN課堂ON課程.課程編號=課堂.課程編號INNERJOIN

選課成績ON課堂.課堂編號=選課成績.課堂編號INNERJOIN

學生ON選課成績.學號=學生.學號

WHERE課堂.開課年份='2017-2018'AND課程.課程性質='必修'AND學生.專業(yè)班級LIKE'工程力學%'ORDERBY課程.課程編號;

28可以先創(chuàng)建視圖：createview土木工程和工程力學選課信息

as

SELECTdistinct課程.課程編號,課堂.開課年份,課程.課程性質,課程.課程名稱

FROM課程INNERJOIN課堂ON課程.課程編號=課堂.課程編號

INNERJOIN選課成績ON課堂.課堂編號=選課成績.課堂編號

INNERJOIN學生ON選課成績.學號=學生.學號

WHERE學生.專業(yè)班級LIKE'土木工程%'INTERSECTSELECTdistinct課程.課程編號,課堂.開課年份,課程.課程性質,課程.課程名稱

FROM課程INNERJOIN課堂ON課程.課程編號=課堂.課程編號

INNERJOIN選課成績ON課堂.課堂編號=選課成績.課堂編號

INNERJOIN學生ON選課成績.學號=學生.學號

WHERE學生.專業(yè)班級LIKE'工程力學%‘292）再對該視圖查詢并排序SELECT*FROM土木工程和工程力學選課信息WHERE開課年份='2017-2018'AND課程性質='必修'ORDERBY課程編號3031

3、為數(shù)據(jù)安全創(chuàng)建視圖例5-10如果經(jīng)常要查詢學生的學號、姓名和所在的專業(yè)班級，并且不希望學生表中的其他個人隱私信息被一般人查詢，可為這些特定的數(shù)據(jù)建立一個視圖。視圖創(chuàng)建語句為：

CREATEVIEW學生專業(yè)班級信息

ASSELECT學號,姓名,專業(yè)班級

FROM學生

;

4、用WITHCHECKOPTION創(chuàng)建視圖例5-7建立的視圖，如果要對學分數(shù)小于3分的課程信息視圖插入相關的記錄，請思考是否能插入成功？INSERTINTO大于等于3學分課程信息(課程編號,課程名稱,學時數(shù),學分數(shù),課程性質,課程介紹,學院編號)VALUES('0016','大數(shù)據(jù)基礎',32,2,'選修','本課程意在普及大數(shù)據(jù)知識，幫助學生理解大數(shù)據(jù)時代的現(xiàn)實意義，了解大數(shù)據(jù)的處理流程，以及大數(shù)據(jù)采集、存儲、分析、處理和管理的技術，以積極投身于大數(shù)據(jù)的應用。','06')32

4、用WITHCHECKOPTION創(chuàng)建視圖例5-11在學生成績管理系統(tǒng)數(shù)據(jù)庫中創(chuàng)建“大于等于3學分課程信息2”視圖，該視圖選擇1個基表(課程表)中的數(shù)據(jù)來顯示學分數(shù)大于或等于3分的數(shù)據(jù)，并選擇用WITHCHECKOPTION創(chuàng)建視圖

CREATEVIEW大于等于3學分課程信息2ASSELECT*FROM課程

where學分數(shù)>=3WITHCHECKOPTION334、用WITHCHECKOPTION創(chuàng)建視圖例5-11選擇用WITHCHECKOPTION創(chuàng)建視圖，如果要對學分數(shù)小于3分的課程信息視圖插入相關的記錄，請思考是否能插入成功？INSERTINTO大于等于3學分課程信息2(課程編號,課程名稱,學時數(shù),學分數(shù),課程性質,課程介紹,學院編號)VALUES('0016','大數(shù)據(jù)基礎',32,2,'選修','本課程意在普及大數(shù)據(jù)知識，幫助學生理解大數(shù)據(jù)時代的現(xiàn)實意義，了解大數(shù)據(jù)的處理流程，以及大數(shù)據(jù)采集、存儲、分析、處理和管理的技術，以積極投身于大數(shù)據(jù)的應用。','06')345.2.3修改視圖1．使用企業(yè)管理器修改視圖2．使用Transact-SQL語句修改視圖可以使用ALTERVIEW語句來修改視圖，其語法格式如下：ALTERVIEW[<database_name>.][<owner>.]view_name[(column[,...n])][WITH<view_attribute>[,...n]]ASselect_statement[WITHCHECKOPTION]<view_attribute>::={ENCRYPTION|SCHEMABINDING|VIEW_METADATA}35

3、應用案例5-12修改視圖（例5-8視圖）：程序設計成績信息，添加新字段：性別。命令為：ALTERVIEW程序設計成績信息ASSELECT學生.學號,姓名,性別,專業(yè)班級,課程名稱,成績FROM學生innerjoin選課成績on學生.學號=選課成績.學號

innerjoin課堂on選課成績.課堂編號=課堂.課堂編號

innerjoin課程on課堂.課程編號=課程.課程編號WHERE課程名稱='C++程序設計基礎'365.2.4刪除視圖1.使用企業(yè)管理器刪除視圖2．使用Transact-SQL語句刪除視圖可以使用DROPVIEW語句來刪除視圖，其語法格式如下：DROPVIEW{view_name}[,...n]其中View_name是要刪除的視圖名稱，可以刪除多個視圖例5-13

刪除視圖：程序設計成績信息，可執(zhí)行下面的SQL語句。

DROPVIEW程序設計成績信息37385.2.5視圖管理1.用企業(yè)管理器查看視圖屬性2.使用系統(tǒng)存儲過程sp_helptext查看視圖語法格式：sp_helptext'name'例5-14查看視圖“大于等于3學分課程信息”的文本定義信息，可使用以下語句。sp_helptext大于等于3學分課程信息393.使用系統(tǒng)存儲過程重命名視圖語法格式：sp_rename'object_name','new_name'[,[@objtype=]'object_type']例5-15將視圖“學生專業(yè)班級信息”重命名為“學生班級信息”，可執(zhí)行如下SQL語句：

sp_rename學生專業(yè)班級信息,學生班級信息405.2.6視圖的應用1.通過視圖檢索表數(shù)據(jù)在建立視圖后，可以用任一種查詢方式檢索視圖數(shù)據(jù)，對視圖可使用連接、GROUPBY子句、子查詢等以及它們的任意組合。

例5-16創(chuàng)建一個大于等于3學分的視圖，并通過視圖查詢相關數(shù)據(jù)。1）創(chuàng)建視圖CREATEVIEW大于等于3學分課程信息

ASSELECT*FROM課程

where學分數(shù)>=3

2）查看已經(jīng)創(chuàng)建好的大于等于3學分的視圖數(shù)據(jù)。SELECT*FROM大于等于3學分課程信息412.通過視圖添加表數(shù)據(jù)語法格式：INSERTINTO視圖名VALUES(列值1，列值2，列值3，…，列值n)

3.通過視圖修改表數(shù)據(jù)語法格式：UPDATE視圖名SET列1=列值1

列2=列值2…….

列n=列值nWHERE邏輯表達式424.通過視圖刪除表數(shù)據(jù)

盡管視圖不一定包含基礎表的所有列，但可以通過視圖刪除基礎表的數(shù)據(jù)行。語法格式：

DELETEFROM視圖名