通過之前的學(xué)習(xí)，已經(jīng)能夠熟練的操作數(shù)據(jù)庫數(shù)據(jù)表，并使用相關(guān)數(shù)據(jù)表來查詢自己想要的信息。由于在實際業(yè)務(wù)中，數(shù)據(jù)表的記錄很多，隨著時間的推移數(shù)據(jù)量會更多，會造成查詢速度越來慢。因此給表創(chuàng)建索引就是一個能夠提高速度，優(yōu)化查詢的有效方法。

就像是給厚厚的字典添加索引可以幫助盡快查找字詞一樣。SQLserver數(shù)據(jù)庫可以通過適當(dāng)?shù)乃饕龓椭?strong>減少查詢工作量，提高查詢特定信息的速度。

查詢數(shù)據(jù)信息是數(shù)據(jù)庫應(yīng)用系統(tǒng)的主要功能，可以利用索引和視圖對查詢進行優(yōu)化。

一，創(chuàng)建和管理索引

1，索引的概念

索引是一個數(shù)據(jù)列表，這個列表包含某張表中的一列或若干列值（也叫鍵值）的有序集合，并記錄與這些值相對應(yīng)的數(shù)據(jù)行在表中存儲的物理地址。

一張表的存儲是由兩部分組成的：

• 數(shù)據(jù)頁：存放表
• 索引頁：存放索引鍵值和指向?qū)?yīng)記錄的指針

通常索引頁相對于數(shù)據(jù)頁來說小得多。

在進行數(shù)據(jù)檢索時，系統(tǒng)首先檢索索引頁，從而找到所需記錄的指針，然后通過指針從數(shù)據(jù)頁中讀取數(shù)據(jù)，從而提高查詢速度。

例如，由于姓名索引中的姓名按照字母順序（或其它定義的順序）排列，是一個有序集合，從有序集合中查找信息一定比從無序集合中查找信息快，因此可以將學(xué)生表students中的姓名sne列作為一個索引，即基于學(xué)生表中學(xué)生姓名字段創(chuàng)建一個姓名索引。

2，索引的分類 

索引鍵值是索引中用于標識和定位記錄的字段值。

例如，在一張課程表中，如果“課程名”列被用作索引，那么該列對應(yīng)的值就是該索引的鍵值。

根據(jù)索引鍵值有無重復(fù)，分為唯一索引和非唯一索引。

 唯一索引（UNIQUE INDEX）：

索引列中的每個值都是唯一的，即沒有重復(fù)值?？纱_保數(shù)據(jù)的完整性，防止插入重復(fù)數(shù)據(jù)。

由于每個NULL值在唯一索引中只被視為一個實例，因此可以有多個NULL值。

常用于需要確保數(shù)據(jù)唯一性的列，例如電子郵件地址、用戶名、身份證號等

非唯一索引：

允許索引列中的值重復(fù)。因為主要用于提高查詢性能，所以不強制要求列中的值唯一。

索引鍵值可以重復(fù)，允許多個記錄具有相同的值。也可以包含NULL值。

常用于提高查詢性能的列，例如用于頻繁查詢的外鍵列或其他常用的搜索字段

根據(jù)存儲結(jié)構(gòu)的不同，將索引分為聚集索引和非聚集索引。

聚集索引：

根據(jù)數(shù)據(jù)行的鍵值，在表或視圖中排序和存儲這些數(shù)據(jù)行，即，表中數(shù)據(jù)頁會按照該索引的

順序來存放，索引順序和記錄的物理順序一致。

每張表只有一個聚集索引，看起來，似乎和之前學(xué)的主鍵約束一樣？其實不一樣：

1）主鍵約束用來唯一標識表中的記錄，而聚集索引則決定表中記錄的存儲順序 。

2）主鍵約束不能為空且不能有重復(fù)值，而聚集索引可以有重復(fù)值。
 非聚集索引：

具有獨立于數(shù)據(jù)行的結(jié)構(gòu)，索引中包含索引鍵值，指向包含該鍵值的數(shù)據(jù)行的指針。

非聚集索引的數(shù)據(jù)表中記錄的實際存儲順序可以不一致，每張表可以有多個非聚集索引。 

根據(jù)索引建立在一列上還是多列上，分為單列索引和復(fù)合索引。

如下語句基于學(xué)生表students中的學(xué)生姓名sne字段創(chuàng)建了一個姓名索引（單列索引）：

CREATE INDEX ix_students_sne ON students(sne)

接著看復(fù)合索引 。由于復(fù)合索引的列順序會影響查詢性能，因此通常將選擇性高的列放在前面。

如下語句基于學(xué)生表students中的學(xué)生姓名sne和班級編號cno字段創(chuàng)建了一個復(fù)合索引：

CREATE INDEX ix_students_sne_cno ON students(sne,cno)

與單列索引相比，復(fù)合索引在某些情況下能夠顯著提高查詢性能 ，如果一個查詢同時使用了多個列作為條件，復(fù)合索引可以加速這些查詢，而不需要分別對每個列進行索引查找。

3，創(chuàng)建索引的原則

雖然索引可以加快查詢，但是索引并不是創(chuàng)建得越多越好，因為創(chuàng)建和維護索引需要時間和資源。因此知道知道創(chuàng)建索引的原則很重要。如下：

可以創(chuàng)建索引的列

1）在主鍵列創(chuàng)建聚集索引

2）外鍵或在表連接操作中經(jīng)常用到的列

3）經(jīng)常查詢的數(shù)據(jù)列
 不創(chuàng)建索引的列

1）很少在查詢中被引用的列

2） 重復(fù)值較多的列

3） 定義為text，ntext或image數(shù)據(jù)類型的列
 系統(tǒng)會自動為下列字段創(chuàng)建索引

1） 為唯一性約束字段創(chuàng)建唯一索引

2） 為主鍵約束字段創(chuàng)建聚集索引

例如students表中在主鍵列學(xué)號sno，唯一列聯(lián)系電話spe：

知道了創(chuàng)建索引的相關(guān)原則之后， 就可以開始創(chuàng)建索引。

4，創(chuàng)建索引 

任務(wù)：在課程表courses上對課程名稱cne列創(chuàng)建索引。

可以通過SSMS的圖形化界面創(chuàng)建索引，也可以使用T-SQL語句創(chuàng)建索引（推薦）。

1）使用SSMS的圖形化界面 

由于任務(wù)要求在課程表courses上操作，因此，這里需要先展開課程表courses的表節(jié)點 ，如下??

彈出如下界面后，指定索引名，之后點擊添加，勾選指定的列，按照任務(wù)要求這里是課程名cne。由于課程名cne既不是主鍵列，也不是唯一列，因此我將該列設(shè)置成了不唯一，非聚集索引。

 再點擊“確定”，之后刷新courses表，就可以看到索引欄下多了一個非聚集索引，如下??： 

 2） 使用T-SQL

 使用T-SQL創(chuàng)建索引的語句為：

CREATE [UNIQUE][CLUSTERED | NONCLUSTERED] INDEX 索引名
ON {表名 | 視圖名} (索引列[ASC|DESC][,...n])

依舊是之前的任務(wù)：在課程表courses上對課程名稱cne列創(chuàng)建索引。

由于之前通過SSMS創(chuàng)建了一個單列索引cne，因此如果想要再創(chuàng)建一個cne的單列索引，就需要將之前創(chuàng)建在索引刪除，語法如下：

DROP INDEX 索引名 ON 數(shù)據(jù)表名

刪除索引：

創(chuàng)建索引：

上面的任務(wù)是創(chuàng)建單列索引，接下來開始創(chuàng)建復(fù)合索引。

任務(wù)：在學(xué)生表上對姓名列和班級列創(chuàng)建復(fù)合索引（姓名列在前）

編寫語句如下：

USE StuScore
CREATE INDEX ix_students_sne_cno ON students(sne,cno)

可以看到，如果不指定索引類型，創(chuàng)建的索引默認為不唯一，非聚集索引。 

如果想要刪除索引，可以使用如下語句：

--刪除索引DROP INDEX 表名.索引名

 二，創(chuàng)建和使用視圖  

1，視圖概念

視圖是從一個多多個表中導(dǎo)出的虛擬表，由一組查詢語言定義，數(shù)據(jù)庫僅存有它的定義（索引視圖除外），數(shù)據(jù)由引用視圖時動態(tài)生成，視圖的特點如下（4個）：

視圖是查看數(shù)據(jù)庫表中數(shù)據(jù)的一種方法。視圖存儲了預(yù)定義的查詢語句，可以重復(fù)使用。視圖是一種邏輯對象，并不存儲數(shù)據(jù)。視圖中被引用的表稱為視圖的基表。

創(chuàng)建視圖，是為了保證數(shù)據(jù)的安全性及簡化查詢。

例如，某單位的員工表包括：員工號，姓名，性別，出生日期，身份證號碼，部門，家庭住址，聯(lián)系電話，工資賬號，薪資待遇等信息。

現(xiàn)在有兩個兩個人：張三和王五，他們兩個人分別管理不同的數(shù)據(jù)。

鑒于張三有前科，出于安全考慮，要求張三只能瀏覽員工的基本信息：

員工號，姓名，性別，出生日期，部門等。

李四只比張三多了員工薪資這一列的信息。

可以看到，針對不同的用戶，同一張表，所展示的內(nèi)容會有所不同。如果每次展示的時候都需要重新從表格中獲取，無疑是重復(fù)且沒意義的。由于每個視圖只有限定的內(nèi)容且可以像數(shù)據(jù)表一樣分配權(quán)限，因此可以通過視圖來實現(xiàn)上述要求。

視圖的作用如下：

集中數(shù)據(jù)。

將數(shù)據(jù)集中于視圖中，用戶可以著重于所負責(zé)的特定事物數(shù)據(jù)。
 對數(shù)據(jù)提供保護。

對不同的用戶定義不同的視圖，使機密數(shù)據(jù)不出現(xiàn)在不應(yīng)看到這些數(shù)據(jù)的用戶視圖上，這種機制提供了對機密數(shù)據(jù)的自動安全保護功能。
 簡化用戶操作。

簡化復(fù)雜的結(jié)構(gòu)，方便對數(shù)據(jù)的操作。
 為數(shù)據(jù)庫重構(gòu)提供了一定程度的邏輯獨立性。

若用戶通過視圖訪問數(shù)據(jù)庫，當(dāng)數(shù)據(jù)庫的邏輯結(jié)構(gòu)發(fā)生改變時，只需要改變視圖的定義，而基于視圖的查詢不需改變，用戶程序不必改變。

知道了視圖的作用及好處之后，接下來通過任務(wù)來創(chuàng)建視圖。

 2，創(chuàng)建視圖

任務(wù)如下：

創(chuàng)建包含students表的學(xué)號，姓名，性別，班級的視圖。

除了這個使用SSMS在圖形化界面創(chuàng)建外，其他任務(wù)都使用T-SQL語句。
 創(chuàng)建包含學(xué)生信息，課程信息和成績信息的視圖。查詢1班學(xué)生的名單（學(xué)號，姓名）并按照學(xué)號升序排序。查詢每門課程的平均成績（課程號，課程名稱，平均成績）。查詢“SQL Server數(shù)據(jù)庫應(yīng)用技術(shù)”課程不及格的學(xué)生信息。  1）使用SSMS在圖形化界面 

任務(wù)1：創(chuàng)建包含學(xué)生表students的學(xué)號，姓名，性別，班級的視圖。

在SSMS下，選擇學(xué)生成績管理數(shù)據(jù)庫節(jié)點下的“視圖”節(jié)點，右擊，選擇“新建視圖”：

 由于任務(wù)要求是在學(xué)生表students上操作，因此，這里的視圖基表為students??

添加結(jié)束后，左擊“關(guān)閉”，進入創(chuàng)建視圖的界面??： 

關(guān)系圖窗格，選擇和條件窗格和SQL窗格用于創(chuàng)建查詢語句，這三個窗格保持同步，即在任一窗格中所完成的操作都會同時反映在其他兩個窗格中。如下，我按照題目，勾選students的學(xué)號sno，姓名sne，性別ssx，班級cno：

就可以看到，關(guān)系和選擇窗格，SQL窗格都發(fā)生了變化。 

構(gòu)建好創(chuàng)建視圖的查詢語句后，單擊工具欄上的“執(zhí)行”按鈕：

就可以在結(jié)果窗格中看到對應(yīng)的結(jié)果：

使用快捷鍵“Ctrl+s”或者是點擊工具欄上的“保持”按鈕，在彈出“選擇名稱”對話框中輸入視圖名稱：

之后點擊“確定”保存。 回到學(xué)生成績管理數(shù)據(jù)節(jié)點下的“視圖”，可以看到視圖創(chuàng)建及保存成功 ：

 2）使用T-SQL語句創(chuàng)建視圖

語法如下：

CREATE VIEW[數(shù)據(jù)庫名.][<架構(gòu)名>.]視圖名[(列名1[,...n])]
[WITH ENCRYPTION] --encryption：加密，加密術(shù)
AS
SQL
[WITH CHECK OPTION]

說明如下：

列名：視圖中的列名（要么全部省略，要么全部指定）。
如果未指定列名表，則視圖列將獲得與SELECT 語句中的列相同的名稱。
如果SELECT 語句中的列有別名，視圖中就取列的別名作為視圖中的列名。

任務(wù)2：創(chuàng)建包含學(xué)生信息，課程信息和成績信息的視圖（v_students_score），該視圖包含學(xué)號，姓名，班級編號，課程號，課程名和成績。（指定列名）

USE StuScore
GO
CREATE VIEW  v_students_score (sno,sne,cla,cno,cne,gets) --cla:class班級
AS 
SELECT st.sno AS 學(xué)號,sne AS 學(xué)生姓名,st.cno AS 班級編號,co.cno AS 課程編號,cne AS 課程名,gets AS 成績得分
FROM students AS st,score AS sc,courses AS co
WHERE st.sno=sc.sno AND co.cno=sc.cno

可以看到，即使我在SELECT 語句中給相應(yīng)的列取了別名，但是，創(chuàng)建視圖時，依舊使用列表名里面的列名。

創(chuàng)建視圖之后，也可以刪除視圖，語法如下：

DROP VIEW 視圖名

DROP VIEW v_students_score

接下來我將列表里面的列名全部省略，并在SELECT語句中給列起別名：

USE StuScore
GO
CREATE VIEW  v_students_score 
AS 
SELECT st.sno AS 學(xué)號,sne AS 學(xué)生姓名,st.cno AS 班級編號,co.cno AS 課程編號,cne AS 課程名,gets AS 成績得分
FROM students AS st,score AS sc,courses AS co
WHERE st.sno=sc.sno AND co.cno=sc.cno

只要在下列情況下，才必須命名CREATE VIEW 中的列（或在SELECT 語句中給列起別名）
1)  當(dāng)列是從算術(shù)表達式，函數(shù)或常量派生來的。
2）兩個或更多的列可能會具有相同名稱（通常是因為連接）。
3）視圖中的某列被賦予了不同于派生來源列的名稱。

定義視圖的SELECT 語句
可以用具有任意復(fù)雜性SELECT 子句，使用多張表或其他視圖來創(chuàng)建視圖。在視圖中被查詢的表稱為基表。
對于視圖定義中的SELECT子句，有以下幾個限制|：
1）不能包含COMPUTE 或 COMPUTE BY 子句。
2）不能包含ORDER BY 子句，除非在SELECT語句的選擇列表中有TOP子句。
3）不能包含SELECT INTO關(guān)鍵字。

WITH CHECK OPTION
強制視圖上執(zhí)行的所有數(shù)據(jù)修改語句，都必須符合定義視圖的WHERE子句設(shè)置的條件。

WITH ENCRYPTION
表示對CREATE VIEW語句文本的項進行加密，加密后無法瀏覽視圖的定義。

上面已經(jīng)創(chuàng)建好視圖，之后使用SSMS打開視圖： 

可以看到視圖中的三張表：學(xué)生表students st，成績表 score sc 和 課程表courses co 中，成績表有學(xué)生表和課程表的外鍵??：

 3，通過視圖查詢數(shù)據(jù) 

創(chuàng)建好視圖之后，可以應(yīng)用視圖進行數(shù)據(jù)查詢，就像對表的查詢一樣，同事在滿足一定條件下，可以應(yīng)用視圖進行數(shù)據(jù)添加，更新和刪除。最終所有對視圖的操作都轉(zhuǎn)換成對基表的操作。

使用視圖查詢數(shù)據(jù)和使用表進行查詢一樣，其實是轉(zhuǎn)換成對基表的查詢。 

下面利用前面創(chuàng)建好的視圖查詢數(shù)據(jù)。

任務(wù)3：查詢1班學(xué)生的名單（學(xué)號，姓名）并按照學(xué)號升序排序。

USE StuScore
GO
SELECT sno AS 學(xué)號,sne AS 學(xué)生名 FROM v_students_list WHERE cno='1' ORDER BY sno
SELECT sno AS 學(xué)號,sne AS 學(xué)生名 FROM v_students_list  -- 包含2班的songjiang

 如果要查詢學(xué)生的聯(lián)系電話spe，就會報錯，如下：

 可以看到，視圖v_students_list中沒有聯(lián)系電話spe列，用戶無法訪問該信息。

這樣就能保證用戶只能看到學(xué)號和姓名，而不能看到其他列，所以使用視圖能起到一定的數(shù)據(jù)保密作用。

任務(wù)4：查詢每門課程的平均成績（課程號，課程名稱，平均成績）

USE StuScore
GO
SELECT 課程編號,課程名,Avg(成績得分) AS 平均成績 FROM v_students_score 
GROUP BY 課程編號,課程名

任務(wù)5：查詢“SQLserver DataBase Application principle”課程不及格的學(xué)生信息。 

USE StuScore
GO
SELECT 學(xué)號,學(xué)生姓名,課程編號,課程名,成績得分
FROM v_students_score
WHERE 課程名='SQLserver DataBase Application principle' 
AND 成績得分<60

 4，通過視圖修改數(shù)據(jù)

 用戶可以通過視圖修改基表的數(shù)據(jù)，其方法與使用UPDATE，INSERT,DELETE語句在表中修改數(shù)據(jù)一樣，實質(zhì)都是轉(zhuǎn)換為對基表的操作。

需要注意：

任何通過視圖的數(shù)據(jù)修改都只能修改一張基表的列，不能同時影響多張表。
 通過視圖修改的列必須是直接引用基表中的列。

對于通過使用集合函數(shù)得到或使用表達式由多個字段得到的列，不能進行修改操作。
 如果在視圖定義中使用WITH CHECK OPTION字句，則所有在視圖上執(zhí)行的修改操作都必須符合定義視圖的SELECT 語句中所設(shè)置的檢索條件。

任務(wù)1：建立一個包含女生信息的視圖（包含學(xué)號，學(xué)生姓名，性別，所屬班級），并要求通過視圖修改的數(shù)據(jù)仍是女生。

編寫語句：

USE StuScore
GO
CREATE VIEW v_female
AS
SELECT sno AS 學(xué)號,sne AS 學(xué)生姓名,ssx AS 性別,cno AS 所屬班級
FROM students
WHERE ssx='female'
WITH CHECK OPTION

 結(jié)果圖：

 任務(wù)2：利用任務(wù)1建立的視圖v_female，插入一條記錄：

學(xué)號 7 ，姓名  紅果果 hongguoguo，性別 女，所屬班級 2 班。

編寫語句如下：

USE StuScore
GO
INSERT INTO v_female(學(xué)號,學(xué)生姓名,性別,所屬班級)
VALUES('7','hongguoguo','female','2')

查看視圖的數(shù)據(jù)：

查看學(xué)生表students：

可以看到， hongguoguo紅果果的出生日期和聯(lián)系電話都為空NULL，由于國籍snn（student nation）設(shè)置了默認約束，所以會自動填充。

如果我再插入一條聯(lián)系電話為空的女生信息，就會報錯：

究其原因是因為聯(lián)系電話spe設(shè)置了唯一約束，只能有一個空值，如果再添加另外一條記錄的電話spe為空，就會違反唯一約束的規(guī)定。 

 有兩種方法：

刪除聯(lián)系電話spe的唯一約束。為視圖v_female添加一個列“聯(lián)系電話”spe。（推薦）

使用第2中方法，修改視圖：

USE StuScore
GO
ALTER VIEW v_female 
AS 
SELECT sno AS 學(xué)號,sne AS 學(xué)生姓名,ssx AS 性別,cno AS 所屬班級,spe AS 聯(lián)系電話
FROM students WHERE ssx='female'
WITH CHECK OPTION

修改之后，記得刷新視圖。 

接著將劉艷liuyan的信息添加進去：

INSERT INTO v_female(學(xué)號,學(xué)生姓名,性別,所屬班級,聯(lián)系電話)
VALUES('8','liuyan','female','2','163xxxxxxxx')

如果我想要在存放了女生信息的視圖v_female中，插入一條男生記錄，就會報錯：

是因為在我在創(chuàng)建v_female視圖時，帶有WITH CHECK OPTION 選項，因此在利用視圖修改數(shù)據(jù)時，任必須滿足性別ssx='female'的檢索條件。 

查看視圖v_female及學(xué)生表students的信息，可以看到，沒有將不符合檢索條件的記錄添加進去。 

 5，視圖的優(yōu)缺點

通過以上對視圖的學(xué)習(xí)和使用，可以總結(jié)出視圖的優(yōu)缺點，如下。
優(yōu)點：

• 數(shù)據(jù)保密。對不同的用戶定義不同的視圖，使其只能看到與自己有關(guān)的數(shù)據(jù)。
• 簡化查詢操作。為復(fù)雜的查詢建立一個視圖，針對此視圖做簡單的查詢。
• 保證數(shù)據(jù)的邏輯獨立性。構(gòu)成視圖的基本表改變時，只需改變視圖的定義，而基于視圖的查詢不需改變。

缺點：

• 性能降低。
• 修改受限。

6，使用T-SQL管理視圖

 1）查看視圖定義

使用系統(tǒng)存儲過程SP_HELPTEXT(個人記憶：store process help text：存儲過程幫助文本)

 語法如下：

SP_HELPTEXT 視圖名

 例如，查看視圖v_students_score的定義：

ALTER VIEW 視圖名[列表名]
[WITH ENCRYPTION]
AS
SQL 語句
[WITH CHECK OPTION]

2）修改視圖定義

 語法如下：

ALTER VIEW 視圖名[列表名]
[WITH ENCRYPTION]
AS
SQL 語句
[WITH CHECK OPTION]

 例如前面在向視圖v_female插入輸入時，對視圖進行的修改

 3）重命名視圖

盡管可以使用 SP_RENAME 更改視圖的名稱，但是建議刪除現(xiàn)有視圖，然后使用新名稱重新創(chuàng)建視圖。使用 SP_RENAME重命名存儲過程、函數(shù)、視圖或觸發(fā)器時，sys.sql_modules 目錄視圖的定義列中相應(yīng)對象的名稱不會更改。 這可能會在以后造成混淆。 因此，不建議使用 SP_RENAME重命名對象。 而是刪除對象，然后使用新名稱重新創(chuàng)建該對象。具體詳情可點擊下面鏈接查看：

 重命名視圖 - SQL Server | Microsoft Learn有關(guān)如何重命名視圖的教程。

https://learn.microsoft.com/zh-cn/SQL/relational-databases/views/rename-views?view=sql-server-linux-ver16

 4）刪除視圖

 語法如下：

DROP VIEW 視圖名

例如在創(chuàng)建視圖部分，我就已經(jīng)學(xué)會了刪除視圖：

到此這篇關(guān)于SQL server 使用索引和視圖優(yōu)化查詢的文章就介紹到這了,更多相關(guān)SQL server 索引和視圖內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論