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

第7章 使用視圖和索引 優(yōu)化查詢,復(fù)習(xí):,數(shù)據(jù)庫(kù)的基本關(guān)系運(yùn)算 投影(查詢指定的列) 連接(查詢多個(gè)表) 選擇(查詢符合條件的行) 基本查詢 對(duì)一張表的查詢 對(duì)表中的原始數(shù)據(jù)的查詢,分別用什么子句實(shí)現(xiàn)?,select 列表名,from 表名 , n ,where 條件,例：select * from xsqk where 出生日期1981-01-01 and 性別=0,復(fù)習(xí):,匯總查詢 (1) 使用聚合函數(shù)：每個(gè)聚合函數(shù)只返回一個(gè)單值。 select avg ( 成績(jī) ) from xs_kc (2) 分類匯總：每個(gè)組返回一條匯總記錄。 select 學(xué)號(hào), avg (成績(jī)) from xs_kc group by 學(xué)號(hào) (3) (分類)明細(xì)匯總： select * from xs_kc compute avg (成績(jī)),having avg (成績(jī)) 75,by 學(xué)號(hào),order by 學(xué)號(hào),復(fù)習(xí):,連接查詢：同時(shí)查詢多個(gè)表中的信息。 內(nèi)連接：將多表中滿足條件的行連接起來(lái)。 select xsqk.學(xué)號(hào),姓名,課程號(hào), 成績(jī) from xsqk, xs_kc where xsqk.學(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)系電話 from xsqk where ( select avg(成績(jī)) from xs_kc where xsqk.學(xué)號(hào)=xs_kc.學(xué)號(hào) ) 60,select 課程名稱 , 授課教師 from kc where 課程號(hào) = any (select 課程號(hào) from xs_kc where 成績(jī)60),說(shuō)明:子查詢執(zhí)行后, 若返回1個(gè)單值,則可用比較運(yùn)算符來(lái)設(shè)置條件: 若返回一個(gè)列表值,則可用:(not) in、any、all,引：,請(qǐng)思考：查詢結(jié)果集能否被用戶修改？ 在查詢窗口得到的結(jié)果是不能被修改的。 查詢結(jié)果集可以用into子句來(lái)保存。例： select xsqk.學(xué)號(hào), 姓名, 專業(yè)名,課程名, 成績(jī) into cj from xsqk, xs_kc, kc where xsqk.學(xué)號(hào)=xs_kc.學(xué)號(hào) and xs_kc.課程號(hào)=kc.課程號(hào) 再思考： cj表中的數(shù)據(jù)與基表數(shù)據(jù)是否并存于數(shù)據(jù)庫(kù)中？ 用戶對(duì)查詢到的信息(cj表)進(jìn)行修改對(duì)基表是否有影響？ 如果用戶直接對(duì)基表進(jìn)行操作，會(huì)有什么后果？,是。但會(huì)造成：數(shù)據(jù)重復(fù)存儲(chǔ)。,無(wú)影響。,后果是：增加了數(shù)據(jù)的不安全性。,視圖的使用 教學(xué)內(nèi)容,基礎(chǔ)知識(shí)： 視圖概述 創(chuàng)建視圖 修改視圖 刪除視圖 使用視圖管理數(shù)據(jù)表中的數(shù)據(jù), 視圖的使用 學(xué)習(xí)目標(biāo),認(rèn)知目標(biāo)： 了解視圖的概念、使用的目的和好處 理解視圖與基表的關(guān)系 能力目標(biāo): 掌握創(chuàng)建、修改、刪除視圖的方法(重點(diǎn)、難點(diǎn)) 掌握查詢視圖的方法(重點(diǎn)),一、視圖概述,視圖的含義和作用 是基于某個(gè)查詢結(jié)果的虛表。是用戶查看和修改數(shù)據(jù)表中數(shù)據(jù)的一種方式。 每個(gè)視圖都有幾個(gè)被定義的列和多個(gè)數(shù)據(jù)行。 視圖與基表的關(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ù)操作：使用戶不必寫復(fù)雜的查詢語(yǔ)句就可對(duì)數(shù)據(jù)進(jìn)行操作。 定制用戶數(shù)據(jù)：使不同水平的用戶能以不同的方式看到不同的數(shù)據(jù)。 合并分離數(shù)據(jù)：視圖可以從水平和垂直方向上分割數(shù)據(jù)，但原數(shù)據(jù)庫(kù)的結(jié)構(gòu)保持不變。,二、創(chuàng)建視圖,最基本的語(yǔ)句格式： create view 視圖 (列名表) with encryption as select查詢語(yǔ)句 with check option 通過(guò)對(duì)象資源管理器創(chuàng)建視圖,加密視圖的定義，使用戶只能查看，不能修改。,強(qiáng)制所有通過(guò)視圖修改的數(shù)據(jù)滿足select語(yǔ)句中指定的條件。,二、創(chuàng)建視圖,實(shí)例1：創(chuàng)建一個(gè) 名為“v1”的視圖，用于查詢計(jì)算機(jī)網(wǎng)絡(luò)專業(yè)男生的學(xué)號(hào)、姓名、出生日期，并將視圖的列名分別改為：學(xué)生學(xué)號(hào)、男生姓名、生日。,create view v1 (學(xué)生學(xué)號(hào),男生姓名, 生日) as select 學(xué)號(hào), 姓名, 出生日期 from xsqk where 專業(yè)名=計(jì)算機(jī)網(wǎng)絡(luò) and 性別=1 go select * from v1,通過(guò)視圖查詢基表中的數(shù)據(jù)。,二、創(chuàng)建視圖,實(shí)例2：創(chuàng)建一個(gè) 名為“v綜合信息”的視圖，用于查詢學(xué)生的學(xué)號(hào)、姓名、專業(yè)名、課程名、成績(jī)等信息,create view v綜合信息 as select xsqk.學(xué)號(hào),姓名,專業(yè)名,課程名,成績(jī) from xsqk , xs_kc , kc where xsqk.學(xué)號(hào) = xs_kc.學(xué)號(hào) and xs_kc.課程號(hào)= kc.課程號(hào) exec sp_helptext v綜合信息,with encryption,對(duì)視圖的定義進(jìn)行加密,查看視圖的定義,二、創(chuàng)建視圖,課堂練習(xí)1：創(chuàng)建一個(gè)名為“v平均成績(jī)”的視圖，用于分組匯總查詢每個(gè)學(xué)生的平均成績(jī)，將視圖的列名分別改為：學(xué)生學(xué)號(hào)，個(gè)人平均分，并加密視圖的定義。,create view v平均成績(jī)(學(xué)生學(xué)號(hào),個(gè)人平均分) with encryption as select 學(xué)號(hào), avg (成績(jī)) from xs_kc group by 學(xué)號(hào),二、創(chuàng)建視圖,實(shí)例3：創(chuàng)建一個(gè)名為“v單科最高分”的視圖，用于查詢每門課程的最高分的學(xué)生學(xué)號(hào)、姓名、課程號(hào)、成績(jī)信息。,create view v單科最高分 as select xsqk.學(xué)號(hào),姓名,課程號(hào),成績(jī) from xsqk, xs_kc where xsqk.學(xué)號(hào)= xs_kc.學(xué)號(hào) and 成績(jī)= any (select max(成績(jī)) from xs_kc group by 課程號(hào)),二、創(chuàng)建視圖,課堂練習(xí)2：創(chuàng)建一個(gè)名為“v全校最高分”的視圖，用于查詢所有成績(jī)中的最高分的學(xué)生學(xué)號(hào)、姓名、專業(yè)名、課程號(hào)、成績(jī)。,create view v全校最高分 as select xsqk.學(xué)號(hào),姓名,專業(yè)名,課程號(hào),成績(jī) from xsqk, xs_kc where xsqk.學(xué)號(hào)= xs_kc.學(xué)號(hào) and 成績(jī)= (select max(成績(jī)) from xs_kc),成績(jī)= all (select 成績(jī) from xs_kc),三、修改視圖,語(yǔ)法格式： alter view 視圖 (列名表) with encryption as select查詢語(yǔ)句 with check option 通過(guò)對(duì)象資源管理器修改視圖,三、修改視圖,實(shí)例4：在“v1”的視圖中增加兩列：專業(yè)名和所在系。 alter view v1 (學(xué)生學(xué)號(hào),男生姓名,生日,專業(yè),系) as select 學(xué)號(hào),姓名,出生日期,專業(yè)名,所在系 from xsqk where 專業(yè)名=計(jì)算機(jī)網(wǎng)絡(luò) and 性別=1,三、修改視圖,實(shí)例5：修改名為“v綜合信息”的視圖，取消其加密屬性，增加選項(xiàng)檢查屬性。,alter view v綜合信息 as select xsqk.學(xué)號(hào), 姓名, 專業(yè)名, 課程名, 成績(jī) from xsqk, xs_kc, kc where xsqk. 學(xué)號(hào) = xs_kc.學(xué)號(hào) and xs_kc.課程號(hào)= kc.課程號(hào) with check option,四、刪除視圖,語(yǔ)句格式 drop view 視圖名 ,n ,五、使用視圖管理數(shù)據(jù)表中的數(shù)據(jù),說(shuō)明： 視圖可以代替表完成從表中查詢、插入、更新和刪除數(shù)據(jù)的操作。,五、使用視圖管理數(shù)據(jù)表中的數(shù)據(jù) -1.插入數(shù)據(jù),實(shí)例6：向“v1”視圖中添加兩條記錄。 insert into v1 values(020106,李好,1981-04-22,計(jì)算機(jī)網(wǎng)絡(luò),計(jì)算機(jī)) insert into v1 values(020107,周權(quán),1981-07-08,信息安全,計(jì)算機(jī)) select * from xsqk select * from v1,請(qǐng)思考:v1視圖中為什么不能看到周權(quán)的記錄?,因?yàn)橹軝?quán)的記錄不滿足視圖的定義,即視圖中只顯示“計(jì)算機(jī)網(wǎng)絡(luò)”專業(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í)定義了“with check option”選項(xiàng)，則使用視圖向基表中插入數(shù)據(jù)時(shí)，必須保證插入后的數(shù)據(jù)滿足定義視圖的限制條件。 說(shuō)明：如果不想讓不滿足視圖定義的數(shù)據(jù)插入到基表中，可以在定義視圖時(shí)，加上“with check option”。,五、使用視圖管理數(shù)據(jù)表中的數(shù)據(jù) -1.插入數(shù)據(jù),實(shí)例7：假設(shè)“v1”視圖已添加了“with check option”屬性，通過(guò)視圖向基表中添加兩條記錄。 insert into v1 values(020108,趙偉,1980-05-07,計(jì)算機(jī)網(wǎng)絡(luò),計(jì)算機(jī)) insert into v1 values(020109,劉齊,1981-12-02,信息安全,計(jì)算機(jī)) 請(qǐng)思考：這兩條記錄能否插入到基表中？能否在視圖中能否顯示？,“趙偉”的信息既能插入到基表中，也能在視圖中看到。但“劉齊”的信息都不能，且系統(tǒng)會(huì)提示錯(cuò)誤。,五、使用視圖管理數(shù)據(jù)表中的數(shù)據(jù) -2.刪除數(shù)據(jù),語(yǔ)句格式： delete from 視圖名 where 條件 實(shí)例8：刪除“v1”視圖中學(xué)號(hào)為020108的記錄。 delete from v1 where 學(xué)號(hào)=020108 注意事項(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ō)，其搜索的效率非常低。,建立索引。,索引 學(xué)習(xí)目標(biāo),認(rèn)知目標(biāo)： 掌握索引的概念 理解創(chuàng)建索引的必要性 掌握幾種索引類型的特點(diǎn)和它們適合的場(chǎng)合 能力目標(biāo): 掌握用命令方式創(chuàng)建索引的方法(重點(diǎn)、難點(diǎn)) 掌握查看、刪除索引的方法(重點(diǎn)),一、索引的基本概念 1. 索引的含義,索引：是對(duì)表或視圖中的一列或多個(gè)列的值進(jìn)行排序的一種物理結(jié)構(gòu)，它由該表或視圖中的一列或若干列值，以及這些列值的記錄在數(shù)據(jù)表中存儲(chǔ)位置的物理地址所組成。 索引如同書的目錄 索引信息存放在索引頁(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ù)類型的列 當(dāng)修改的性能需求遠(yuǎn)大于查詢的性能需求時(shí)，不要?jiǎng)?chuàng)建索引,一、索引的基本概念 4. 索引的類型,按數(shù)據(jù)的物理存放順序： 聚集索引:會(huì)改變表記錄的物理存儲(chǔ)順序，使之與索引列的順序完全相同（適用于范圍搜索）。 非聚集索引:不改變表記錄的存放順序（適用于直接匹配單個(gè)條件的查詢）。 按索引列取值的唯一性： 唯一索引:要求被索引列不能有重復(fù)值，也不能有兩個(gè)null。 非唯一索引:不存在這一限制。 按索引列的個(gè)數(shù)：?jiǎn)嗡饕?、?fù)合索引,二、創(chuàng)建索引 1. 創(chuàng)建索引的方法,系統(tǒng)自動(dòng)建立索引 人工創(chuàng)建索引 t-sql命令方式（掌握） 對(duì)象資源管理器方式,二、創(chuàng)建索引 2. 系統(tǒng)自動(dòng)建立索引,添加主鍵約束時(shí) 系統(tǒng)會(huì)自動(dòng)在表中生成一個(gè)聚集惟一索引。 在xsqk表的“學(xué)號(hào)”列上已創(chuàng)建了聚集惟一索引(名為pk_xsqk) 在xs_kc表的“學(xué)號(hào)和課程號(hào)”兩列上已創(chuàng)建了聚集惟一索引 在kc表中的“課程號(hào)”列上已創(chuàng)建了聚集惟一索引 添加惟一性約束時(shí) 系統(tǒng)會(huì)自動(dòng)在表中生成一個(gè)非聚集惟一索引。,二、創(chuàng)建索引 2. 系統(tǒng)自動(dòng)建立索引,實(shí)例1：創(chuàng)建一個(gè)名為test的表，其結(jié)構(gòu)定義如下。 use xscj create table test (課程號(hào) smallint constraint pk_test primary key, 課程名 char(14) constraint uq_test unique, 授課教師 char(10) ),二、創(chuàng)建索引 3. 手工建立索引(1)表索引,命令格式 create unique clustered | nonclustered index 索引名 on 表名 | 視圖名 ( 列名 asc | desc , .n ) 說(shuō)明： 一個(gè)表中只能創(chuàng)建1個(gè)聚集索引。 由于系統(tǒng)已自動(dòng)在主鍵上創(chuàng)建了聚集索引，所以用戶不能再創(chuàng)建，除非先刪除已有的。 一個(gè)表中可以創(chuàng)建若干個(gè)非聚集索引。,默認(rèn)值,二、創(chuàng)建索引 3. 手工建立索引(1)表索引,實(shí)例2：在kc表的“課程名”列上創(chuàng)建一個(gè)非聚集索引，索引名為“ix_kcm”，并降序排列。 實(shí)例3：在kc表中，重新創(chuàng)建名為“ix_kcm”的索引，使其成為惟一性的非聚集索引。,create unique index ix_kcm on kc ( 課程名 desc) with drop_existing,create index ix_kcm on kc ( 課程名 desc),刪除同名的原索引，重建新索引。,二、創(chuàng)建索引 3. 手工建立索引(1)表索引,實(shí)例4：已知xs_kc表中的“學(xué)號(hào)”和“課程號(hào)”兩列已設(shè)置為主鍵，主鍵約束名為“pk_xs_kc”，請(qǐng)重新創(chuàng)建這兩列的組合索引，使學(xué)號(hào)降序排列，使索引頁(yè)填滿60%后就換新頁(yè)進(jìn)行填充。,create unique clustered index pk_xs_kc on xs_kc(學(xué)號(hào) desc ,課程號(hào)) with pad_index, fillfactor=60, drop_existing,中間級(jí)索引頁(yè)也按“填充因子”指定的填充度填充,葉級(jí)索引頁(yè)按60%進(jìn)行填充,二、創(chuàng)建索引 3. 手工建立索引(1)表索引,課堂練習(xí)：已知xsqk表中的“學(xué)號(hào)”列已設(shè)置為主鍵，請(qǐng)?jiān)凇靶彰焙汀八谙怠眱闪?