第7章使用視圖和索引優(yōu)化查詢

第7章使用視圖和索引

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

列表名from

表名[,…n]where

條件例：select*fromxsqk

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

selectavg(成績)fromxs_kc(2)分類匯總：每個組返回一條匯總記錄。select學號,

avg(成績)fromxs_kcgroupby

學號(3)(分類)明細匯總：select*fromxs_kc

computeavg(成績)having

avg(成績)

>75by

學號orderby學號復習:連接查詢：同時查詢多個表中的信息。內(nèi)連接：將多表中滿足條件的行連接起來。

selectxsqk.學號,姓名,課程號,成績

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

學號,姓名,聯(lián)系電話fromxsqkwhere(select

avg(成績)fromxs_kc

wherexsqk.學號=xs_kc.學號)>60select

課程名稱,授課教師fromkcwhere

課程號=ANY(select

課程號

fromxs_kcwhere

成績<60)說明:子查詢執(zhí)行后,若返回1個單值,則可用比較運算符來設(shè)置條件:若返回一個列表值,則可用:(not)in、any、allin引：請思考：查詢結(jié)果集能否被用戶修改？在查詢窗口得到的結(jié)果是不能被修改的。查詢結(jié)果集可以用into子句來保存。例：selectxsqk.學號,姓名,專業(yè)名,課程名,成績

intocj

fromxsqk,xs_kc,kcwherexsqk.學號=xs_kc.學號and

xs_kc.課程號=kc.課程號再思考：cj表中的數(shù)據(jù)與基表數(shù)據(jù)是否并存于數(shù)據(jù)庫中？用戶對查詢到的信息(cj表)進行修改對基表是否有影響？如果用戶直接對基表進行操作，會有什么后果？是。但會造成：數(shù)據(jù)重復存儲。無影響。后果是：增加了數(shù)據(jù)的不安全性。生成一張新表，其中保存的是查詢結(jié)果的數(shù)據(jù)

——視圖的使用

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

——視圖的使用

學習目標認知目標：了解視圖的概念、使用的目的和好處理解視圖與基表的關(guān)系能力目標:掌握創(chuàng)建、修改、刪除視圖的方法(重點、難點)掌握查詢視圖的方法(重點)一、視圖概述視圖的含義和作用是基于某個查詢結(jié)果的虛表。是用戶查看和修改數(shù)據(jù)表中數(shù)據(jù)的一種方式。

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

視圖

[(列名表)][with

encryption]as

select查詢語句

[withcheckoption]通過對象資源管理器創(chuàng)建視圖加密視圖的定義，使用戶只能查看，不能修改。強制所有通過視圖修改的數(shù)據(jù)滿足select語句中指定的條件。二、創(chuàng)建視圖實例1：創(chuàng)建一個名為“v1”的視圖,包含10702班的學生學號，姓名，性別，出生日期，班級編號。

createviewv1asselectstudentid，studentname，sex，birthday，classidfromstudentwhereclassid='10702‘通過視圖查詢基表中的數(shù)據(jù)。select*fromv1二、創(chuàng)建視圖實例2：創(chuàng)建一個名為“v綜合信息”的視圖，用于查詢學生的學號、姓名、專業(yè)名、課程名、成績等信息createviewV綜合信息

as

selectstudent.studentid,studentname,coursename,scorefromstudent,course,scorewherestudent.studentid=score.studentidandcourse.courseid=score.courseidwithencryption對視圖的定義進行加密查看視圖的定義execsp_helptext

v綜合信息二、創(chuàng)建視圖課堂練習1：創(chuàng)建一個名為“V平均成績”的視圖，用于分組匯總查詢每個學生的平均成績，將視圖的列名分別改為：學生學號，個人平均分，并加密視圖的定義。createviewV平均成績(學生學號,個人平均分)withencryptionasselectstudentid,avg(score)fromscoregroupbystudentid二、創(chuàng)建視圖實例3：創(chuàng)建一個名為“V單科最高分”的視圖，用于查詢每門課程的最高分的課程號、成績信息。createviewV單科最高分asselectcourseid,max(score)fromscoregroupbycourseid)二、創(chuàng)建視圖課堂練習2：創(chuàng)建一個名為“V全校最高分”的視圖，用于查詢所有成績中的最高分的學生學號、姓名、專業(yè)名、課程號、成績。createviewV全校最高分asselectstudent.studentid,studentname,courseid,scorefromstudent,scorewherestudent.studentid=score.studentidandscore=(selectmax(score)fromscore)三、修改視圖語法格式：

alterview

視圖

[(列名表)][with

encryption]as

select查詢語句

[withcheckoption]通過對象資源管理器修改視圖三、修改視圖實例4：在“v1”的視圖中增加兩列：地址和電話。alterviewv1

(學生學號，姓名，性別，出生日期，班級編號,地址,電話)as

selectstudentid，studentname，sex，birthday，classid,address,telfromstudentwhereclassid='10702'三、修改視圖實例5：修改名為“v綜合信息”的視圖，取消其加密屬性，增加選項檢查屬性。alterviewv綜合信息asselectstudent.studentid,studentname,coursename,scorefromstudent,course,scorewherestudent.studentid=score.studentidandcourse.courseid=score.courseidwithcheckoption四、刪除視圖語句格式drop

view

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

----1.插入數(shù)據(jù)實例6：向“V1”視圖中添加兩條記錄。insertintov1values('020188','李敏','女','1981-04-22','10702')insertintov1values ('020199','周明','男','1981-07-08','10701')select*fromstudentselect*fromv1請思考:V1視圖中為什么不能看到‘周明’的記錄?因為‘周明’的記錄不滿足視圖的定義,即視圖中只顯示‘10702’

的學生信息，所以‘周明’的信息只能在基表中看到,而不能在視圖中看到。五、使用視圖管理數(shù)據(jù)表中的數(shù)據(jù)

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

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

values

(‘020108’,‘趙偉’,男,‘1980-05-07’,‘10702’)insertintov1

values(‘020109’,‘劉齊’,男,‘1981-12-02’,‘20801’)請思考：這兩條記錄能否插入到基表中？能否在視圖中能否顯示？“趙偉”的信息既能插入到基表中，也能在視圖中看到。但“劉齊”的信息都不能，且系統(tǒng)會提示錯誤。五、使用視圖管理數(shù)據(jù)表中的數(shù)據(jù)

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

deletefrom

視圖名[where

條件]實例8：刪除“V1”視圖中學號為‘020108’的記錄。deletefromV1where

學號=‘020108’注意事項：要刪除的數(shù)據(jù)必須包含在視圖的結(jié)果集中。如果視圖引用了多個表時，無法用delete命令刪除數(shù)據(jù)。引:請思考:在一個數(shù)據(jù)表中檢索數(shù)據(jù)時是如何進行的?如果采取逐行掃描的方式進行檢索,可行嗎?有什么方法可提高檢索效率？對于小表來說，是可行的。但對于有成千上萬行的表來說，其搜索的效率非常低。建立索引。索引

學習目標認知目標：掌握索引的概念理解創(chuàng)建索引的必要性掌握幾種索引類型的特點和它們適合的場合能力目標:掌握用命令方式創(chuàng)建索引的方法(重點、難點)掌握查看、刪除索引的方法(重點)一、索引的基本概念

1.

索引的含義索引：是對表或視圖中的一列或多個列的值進行排序的一種物理結(jié)構(gòu)，它由該表或視圖中的一列或若干列值，以及這些列值的記錄在數(shù)據(jù)表中存儲位置的物理地址所組成。索引如同書的目錄索引信息存放在索引頁中，表中的數(shù)據(jù)存放在數(shù)據(jù)頁中。一、索引的基本概念

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

3.

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

3.

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

4.

索引的類型按數(shù)據(jù)的物理存放順序：聚集索引:會改變表記錄的物理存儲順序，使之與索引列的順序完全相同（適用于范圍搜索）。非聚集索引:不改變表記錄的存放順序（適用于直接匹配單個條件的查詢）。按索引列取值的唯一性：唯一索引:要求被索引列不能有重復值，也不能有兩個NULL。非唯一索引:不存在這一限制。按索引列的個數(shù)：單索引、復合索引

二、創(chuàng)建索引

1.

創(chuàng)建索引的方法系統(tǒng)自動建立索引人工創(chuàng)建索引T-SQL命令方式（掌握）對象資源管理器方式二、創(chuàng)建索引

2.

系統(tǒng)自動建立索引添加主鍵約束時系統(tǒng)會自動在表中生成一個聚集惟一索引。在xsqk表的“學號”列上已創(chuàng)建了聚集惟一索引(名為PK_xsqk)在xs_kc表的“學號和課程號”兩列上已創(chuàng)建了聚集惟一索引在kc表中的“課程號”列上已創(chuàng)建了聚集惟一索引添加惟一性約束時系統(tǒng)會自動在表中生成一個非聚集惟一索引。二、創(chuàng)建索引

2.

系統(tǒng)自動建立索引實例1：創(chuàng)建一個名為test的表，其結(jié)構(gòu)定義如下。usexscjcreatetabletest(課程號smallintconstraintpk_testprimarykey,

課程名

char(14)constraintuq_testunique,

授課教師char(10))生成惟一性聚集索引生成惟一性非聚集索引二、創(chuàng)建索引

3.

手工建立索引—(1)表索引命令格式create[unique][clustered|nonclustered]

index

索引名

on{表名

|

視圖名

}(列名

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

說明：一個表中只能創(chuàng)建1個聚集索引。由于系統(tǒng)已自動在主鍵上創(chuàng)建了聚集索引，所以用戶不能再創(chuàng)建，除非先刪除已有的。一個表中可以創(chuàng)建若干個非聚集索引。默認值二、創(chuàng)建索引

3.

手工建立索引—(1)表索引實例2：在kc表的“課程名”列上創(chuàng)建一個非聚集索引，索引名為“ix_kcm”，并降序排列。實例3：在kc表中，重新創(chuàng)建名為“ix_kcm”的索引，使其成為惟一性的非聚集索引。createuniqueindexix_kcmonkc(課程名desc)withdrop_existingcreateindexix_kcmonkc(課程名desc)刪除同名的原索引，重建新索引。二、創(chuàng)建索引

3.

手工建立索引—(1)表索引實例4：已知xs_kc表中的“學號”和“課程號”兩列已設(shè)置為主鍵，主鍵約束名為“PK_xs_kc”，請重新創(chuàng)建這兩列的組合索引，使學號降序排列，使索引頁填滿60%后就換新頁進行填充。createuniqueclusteredindexpk_xs_kconxs_kc(學號desc,課程號)withpad_index,fillfactor=60,drop_existing中間級索引頁也按“填充因子”指定的填充度填充葉級索引頁按60%進行填充二、創(chuàng)建索引

3.

手工建立索引—(1)表索引課堂練習：已知xsqk表中的“學號”列已設(shè)置為主鍵，請在“姓