第19講 視圖與索引

第6章視圖與索引

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

列表名from

表名[,…n]where

條件例：select*fromxsqk

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

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

avg(成績)fromxs_kcgroupby

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

computeavg(成績)having

avg(成績)

>75by

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

selectxsqk.學(xué)號,姓名,課程號,成績

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

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

avg(成績)fromxs_kc

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

課程名稱,授課教師fromkcwhere

課程號=ANY(select

課程號

fromxs_kcwhere

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

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

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

能力目標:掌握創(chuàng)建、修改、刪除視圖的方法(重點)掌握創(chuàng)建、刪除索引的方法（重點、難點）一、視圖概述視圖的含義和作用是基于某個查詢結(jié)果的虛表。是用戶查看和修改數(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ù)操作：使用戶不必寫復(fù)雜的查詢語句就可對數(shù)據(jù)進行操作。定制用戶數(shù)據(jù)：使不同水平的用戶能以不同的方式看到不同的數(shù)據(jù)。合并分離數(shù)據(jù)：視圖可以從水平和垂直方向上分割數(shù)據(jù)，但原數(shù)據(jù)庫的結(jié)構(gòu)保持不變。二、創(chuàng)建視圖最基本的語句格式：createview

視圖

as

select查詢語句

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

asselectxsqk.學(xué)號,姓名,專業(yè)名,課程號,成績

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

成績>=60and

成績<80goselect

*

from

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

alterview

視圖

asselect查詢語句

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

view

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

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

asselect

學(xué)號,姓名,性別,出生日期,專業(yè)名,所在系

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

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

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

values(‘2012130106’,‘周權(quán)’,1,‘1986-07-08’,‘信息安全’,‘計算機應(yīng)用’)select*fromxsqkselect*fromv2請思考:V2視圖中能不能看到‘周權(quán)’的記錄，為什么?因為‘周權(quán)’的記錄不滿足視圖的定義,即視圖中只顯示“網(wǎng)絡(luò)工程”專業(yè)的學(xué)生信息，所以‘周權(quán)’的信息只能在基表中看到,而不能在視圖中看到。五、使用視圖管理數(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ù)實例4：假設(shè)“V2”視圖已添加了“withcheckoption”屬性，通過視圖向基表中添加兩條記錄。insertintov2

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

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

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

deletefrom

視圖名[where

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

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

1.

索引的含義索引：是對表的一列或多個列的值進行排序的一種物理結(jié)構(gòu)，它由該表的一列或多個列的值，以及指向這些列值對應(yīng)記錄存儲位置的指針所組成。索引如同書的目錄索引信息存放在索引頁中，表中的數(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ù)的物理存放順序：聚集索引:會改變表記錄的物理存儲順序，使之與索引列的順序完全相同非聚集索引:不改變表記錄的存放順序七、創(chuàng)建索引

1.

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

2.

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

3.

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

index

索引名

on{表名

|

視圖名

}(列名

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

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

3.

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