第19講 視圖與索引

第6章視圖與索引

第19講復(fù)習(xí):數(shù)據(jù)庫(kù)的基本關(guān)系運(yùn)算投影(查詢指定的列)連接(查詢多個(gè)表)選擇(查詢符合條件的行)基本查詢對(duì)一張表的查詢對(duì)表中的原始數(shù)據(jù)的查詢分別用什么子句實(shí)現(xiàn)?select

列表名from

表名[,…n]where

條件例：select*fromxsqk

where出生日期<‘1997-01-01’and性別=0復(fù)習(xí):匯總查詢(1)使用聚合函數(shù)：每個(gè)聚合函數(shù)只返回一個(gè)單值。

selectavg(成績(jī))fromxs_kc(2)分類(lèi)匯總：每個(gè)組返回一條匯總記錄。select學(xué)號(hào),

avg(成績(jī))fromxs_kcgroupby

學(xué)號(hào)(3)(分類(lèi))明細(xì)匯總：select*fromxs_kc

computeavg(成績(jī))having

avg(成績(jī))

>75by

學(xué)號(hào)orderby學(xué)號(hào)復(fù)習(xí):連接查詢：同時(shí)查詢多個(gè)表中的信息。內(nèi)連接：將多表中滿足條件的行連接起來(lái)。

selectxsqk.學(xué)號(hào),姓名,課程號(hào),成績(jī)

fromxsqk,xs_kcwherexsqk.學(xué)號(hào)=xs_kc.學(xué)號(hào)and成績(jī)<60外連接：將滿足條件的行連接，不匹配的行也保留。自連接：將同一個(gè)表中滿足條件的行連接起來(lái)。復(fù)習(xí):子查詢：一個(gè)select語(yǔ)句(內(nèi)查詢)作為另一條select語(yǔ)句(外查詢)的一部分。相關(guān)子查詢：內(nèi)查詢的執(zhí)行依賴外查詢。嵌套子查詢：（不依賴）select

學(xué)號(hào),姓名,聯(lián)系電話fromxsqkwhere(select

avg(成績(jī))fromxs_kc

wherexsqk.學(xué)號(hào)=xs_kc.學(xué)號(hào))>60select

課程名稱(chēng),授課教師fromkcwhere

課程號(hào)=ANY(select

課程號(hào)

fromxs_kcwhere

成績(jī)<60)說(shuō)明:子查詢執(zhí)行后,若返回1個(gè)單值,則可用比較運(yùn)算符來(lái)設(shè)置條件:若返回一個(gè)列表值,則可用:(not)in、any、allin第6章視圖與索引

教學(xué)內(nèi)容基礎(chǔ)知識(shí)：視圖概述創(chuàng)建、修改、刪除視圖使用視圖管理數(shù)據(jù)表中的數(shù)據(jù)索引概述創(chuàng)建、刪除索引第6章視圖與索引

學(xué)習(xí)目標(biāo)認(rèn)知目標(biāo)：了解視圖的概念、使用的目的和好處理解索引的概念、創(chuàng)建索引的必要性、索引的類(lèi)型

能力目標(biāo):掌握創(chuàng)建、修改、刪除視圖的方法(重點(diǎn))掌握創(chuàng)建、刪除索引的方法（重點(diǎn)、難點(diǎn)）一、視圖概述視圖的含義和作用是基于某個(gè)查詢結(jié)果的虛表。是用戶查看和修改數(shù)據(jù)表中數(shù)據(jù)的一種方式。

每個(gè)視圖都有若干被定義的列和行。視圖與基表的關(guān)系視圖中的數(shù)據(jù)列和行來(lái)源于是其引用的基表。視圖所對(duì)應(yīng)的數(shù)據(jù)并不實(shí)際存儲(chǔ)在數(shù)據(jù)庫(kù)中，而是仍存儲(chǔ)在視圖所引用的基表中。數(shù)據(jù)庫(kù)中只存儲(chǔ)視圖的定義。一、視圖概述使用視圖的目的與好處聚焦特定數(shù)據(jù)：使用戶只能看到和操作與他們有關(guān)的數(shù)據(jù)，提高了數(shù)據(jù)的安全性。簡(jiǎn)化數(shù)據(jù)操作：使用戶不必寫(xiě)復(fù)雜的查詢語(yǔ)句就可對(duì)數(shù)據(jù)進(jìn)行操作。定制用戶數(shù)據(jù)：使不同水平的用戶能以不同的方式看到不同的數(shù)據(jù)。合并分離數(shù)據(jù)：視圖可以從水平和垂直方向上分割數(shù)據(jù)，但原數(shù)據(jù)庫(kù)的結(jié)構(gòu)保持不變。二、創(chuàng)建視圖最基本的語(yǔ)句格式：createview

視圖

as

select查詢語(yǔ)句

[withcheckoption]通過(guò)SSMS創(chuàng)建視圖強(qiáng)制所有通過(guò)視圖修改的數(shù)據(jù)滿足select語(yǔ)句中指定的條件。二、創(chuàng)建視圖實(shí)例1：創(chuàng)建一個(gè)名為“v1”的視圖，用于查詢成績(jī)?cè)?0~80之間的學(xué)生學(xué)號(hào)、姓名、專(zhuān)業(yè)名、課程號(hào)、成績(jī)信息。createviewv1

asselectxsqk.學(xué)號(hào),姓名,專(zhuān)業(yè)名,課程號(hào),成績(jī)

fromxsqk,xs_kcwherexsqk.學(xué)號(hào)=xs_kc.學(xué)號(hào)and

成績(jī)>=60and

成績(jī)<80goselect

*

from

v1通過(guò)視圖查詢基表中的數(shù)據(jù)。三、修改視圖語(yǔ)法格式：

alterview

視圖

asselect查詢語(yǔ)句

[withcheckoption]通過(guò)SSMS修改視圖三、修改視圖實(shí)例2：在“v1”的視圖中增加一列：所在系。alterviewv1asselectxsqk.學(xué)號(hào),姓名,專(zhuān)業(yè)名,課程號(hào),成績(jī),所在系fromxsqk,xs_kcwherexsqk.學(xué)號(hào)=xs_kc.學(xué)號(hào)and成績(jī)>=60and成績(jī)<80四、刪除視圖語(yǔ)句格式drop

view

視圖名[,…n]五、使用視圖管理數(shù)據(jù)表中的數(shù)據(jù)說(shuō)明：視圖可以代替表完成從表中查詢、插入、更新和刪除數(shù)據(jù)的操作。五、使用視圖管理數(shù)據(jù)表中的數(shù)據(jù)

----1.插入數(shù)據(jù)實(shí)例3：創(chuàng)建了一個(gè)“V2”的視圖，用于查詢網(wǎng)絡(luò)工程專(zhuān)業(yè)的學(xué)生信息，包括學(xué)號(hào)、姓名、性別、出生日期、專(zhuān)業(yè)名、所在系。createviewv2

asselect

學(xué)號(hào),姓名,性別,出生日期,專(zhuān)業(yè)名,所在系

fromxsqkwhere專(zhuān)業(yè)名=‘網(wǎng)絡(luò)工程’五、使用視圖管理數(shù)據(jù)表中的數(shù)據(jù)

----1.插入數(shù)據(jù)向“V2”視圖中添加兩條記錄。insertintov2

values(‘2012130206’,‘李好’,1,‘1996-04-22’,‘網(wǎng)絡(luò)工程’,‘計(jì)算機(jī)應(yīng)用’)insertintov2

values(‘2012130106’,‘周權(quán)’,1,‘1986-07-08’,‘信息安全’,‘計(jì)算機(jī)應(yīng)用’)select*fromxsqkselect*fromv2請(qǐng)思考:V2視圖中能不能看到‘周權(quán)’的記錄，為什么?因?yàn)椤軝?quán)’的記錄不滿足視圖的定義,即視圖中只顯示“網(wǎng)絡(luò)工程”專(zhuān)業(yè)的學(xué)生信息，所以‘周權(quán)’的信息只能在基表中看到,而不能在視圖中看到。五、使用視圖管理數(shù)據(jù)表中的數(shù)據(jù)

----1.插入數(shù)據(jù)插入數(shù)據(jù)的注意事項(xiàng)可通過(guò)視圖向基表中插入數(shù)據(jù)，但插入的數(shù)據(jù)實(shí)際上存放在基表中，而不是存放在視圖中。如果視圖引用了多個(gè)表，使用insert語(yǔ)句插入的列必須屬于同一個(gè)表。若創(chuàng)建視圖時(shí)定義了“withcheckoption”選項(xiàng)，則使用視圖向基表中插入數(shù)據(jù)時(shí)，必須保證插入后的數(shù)據(jù)滿足定義視圖的限制條件。說(shuō)明：如果不想讓不滿足視圖定義的數(shù)據(jù)插入到基表中，可以在定義視圖時(shí)，加上“withcheckoption”。五、使用視圖管理數(shù)據(jù)表中的數(shù)據(jù)

----1.插入數(shù)據(jù)實(shí)例4：假設(shè)“V2”視圖已添加了“withcheckoption”屬性，通過(guò)視圖向基表中添加兩條記錄。insertintov2

values(‘2012130207’,‘趙偉’,1,‘1996-05-07’,‘網(wǎng)絡(luò)工程’,‘計(jì)算機(jī)應(yīng)用’)insertintov2

values(‘2012130107’,‘劉齊’,1,‘1996-12-02’,‘信息安全’,‘計(jì)算機(jī)應(yīng)用’)請(qǐng)思考：這兩條記錄能否插入到基表中？能否在視圖中能否顯示？“趙偉”的信息既能插入到基表中，也能在視圖中看到。但“劉齊”的信息都不能，且系統(tǒng)會(huì)提示錯(cuò)誤。五、使用視圖管理數(shù)據(jù)表中的數(shù)據(jù)

----2.刪除數(shù)據(jù)語(yǔ)句格式：

deletefrom

視圖名[where

條件]實(shí)例5：刪除“V2”視圖中學(xué)號(hào)為‘2012130206’的記錄。deletefromV2where

學(xué)號(hào)=‘2012130206’注意事項(xiàng)：要?jiǎng)h除的數(shù)據(jù)必須包含在視圖的結(jié)果集中。如果視圖引用了多個(gè)表時(shí)，無(wú)法用delete命令刪除數(shù)據(jù)。引:請(qǐng)思考:在一個(gè)數(shù)據(jù)表中檢索數(shù)據(jù)時(shí)是如何進(jìn)行的?如果采取逐行掃描的方式進(jìn)行檢索,可行嗎?有什么方法可提高檢索效率？對(duì)于小表來(lái)說(shuō)，是可行的。但對(duì)于有成千上萬(wàn)行的表來(lái)說(shuō)，其搜索的效率非常低。建立索引。六、索引的基本概念

1.

索引的含義索引：是對(duì)表的一列或多個(gè)列的值進(jìn)行排序的一種物理結(jié)構(gòu)，它由該表的一列或多個(gè)列的值，以及指向這些列值對(duì)應(yīng)記錄存儲(chǔ)位置的指針?biāo)M成。索引如同書(shū)的目錄索引信息存放在索引頁(yè)中，表中的數(shù)據(jù)存放在數(shù)據(jù)頁(yè)中。六、索引的基本概念

2.建立索引的優(yōu)缺點(diǎn)建立索引的優(yōu)點(diǎn)建立了索引的列作為查詢條件時(shí)，數(shù)據(jù)的檢索速度能大大地提高。增加索引的不利方面創(chuàng)建索引也要花費(fèi)時(shí)間和占用物理空間。雖然索引加快了檢索速度，但減慢了數(shù)據(jù)修改的速度(因?yàn)槊繄?zhí)行一次數(shù)據(jù)修改，就需要對(duì)索引進(jìn)行維護(hù))。六、索引的基本概念

3.

使用索引的準(zhǔn)則何時(shí)創(chuàng)建索引？在經(jīng)常用作查詢條件的列上在經(jīng)常要排序的列上在主鍵上在用于連接的列(外鍵)上六、索引的基本概念

3.

使用索引的準(zhǔn)則何時(shí)可不考慮建立索引？很少或從來(lái)不作為查詢條件的列在小表中的任何列屬于text、image、長(zhǎng)度較大的char、varchar、binary等數(shù)據(jù)類(lèi)型的列當(dāng)修改的性能需求遠(yuǎn)大于查詢的性能需求時(shí)，不要?jiǎng)?chuàng)建索引六、索引的基本概念

4.

索引的類(lèi)型按數(shù)據(jù)的物理存放順序：聚集索引:會(huì)改變表記錄的物理存儲(chǔ)順序，使之與索引列的順序完全相同非聚集索引:不改變表記錄的存放順序七、創(chuàng)建索引

1.

創(chuàng)建索引的方法系統(tǒng)自動(dòng)建立索引人工創(chuàng)建索引T-SQL命令方式（掌握）SSMS七、創(chuàng)建索引

2.

系統(tǒng)自動(dòng)建立索引添加主鍵約束時(shí)系統(tǒng)會(huì)自動(dòng)在表中生成一個(gè)唯一聚集索引。在xsqk表的“學(xué)號(hào)”列上已創(chuàng)建了唯一聚集索引在xs_kc表的“學(xué)號(hào)和課程號(hào)”兩列上已創(chuàng)建了唯一聚集索引在kc表中的“課程號(hào)”列上已創(chuàng)建了唯一聚集索引添加唯一性約束時(shí)系統(tǒng)會(huì)自動(dòng)在表中生成一個(gè)唯一非聚集索引。七、創(chuàng)建索引

3.

手工建立索引命令格式create[unique][clustered|nonclustered]

index

索引名

on{表名

|

視圖名

}(列名

[asc|desc][,...n])

with<索引選項(xiàng)>說(shuō)明：一個(gè)表中只能創(chuàng)建1個(gè)聚集索引。由于系統(tǒng)已自動(dòng)在主鍵上創(chuàng)建了聚集索引，所以用戶不能再創(chuàng)建，除非先刪除已有的。一個(gè)表中可以創(chuàng)建若干個(gè)非聚集索引。默認(rèn)值七、創(chuàng)建索引

3.

手工建立索引實(shí)例6：在kc表的“課程名”列上創(chuàng)建一個(gè)非聚集索引，索引名為“ix_kcm”，并降序排列。思考：在kc表中，每個(gè)索引頁(yè)是否要填滿？要如何重新創(chuàng)建名為“ix_kcm”的索引，使其成為唯一性的非聚集索引？createindexix_kcmonkc(課程名desc