對(duì)于了解掌握SQL的增、刪、改、查的語(yǔ)句操作是最基本的，實(shí)際生產(chǎn)環(huán)境中，我們還會(huì)用到一些比較高級(jí)的數(shù)據(jù)處理和查詢，包括索引、視圖、存儲(chǔ)過(guò)程和觸發(fā)器。本篇博文主要如何更好的實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的操作、診斷及優(yōu)化。

創(chuàng)新互聯(lián)專注于企業(yè)成都全網(wǎng)營(yíng)銷推廣、網(wǎng)站重做改版、秦都網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、html5、成都商城網(wǎng)站開(kāi)發(fā)、集團(tuán)公司官網(wǎng)建設(shè)、外貿(mào)網(wǎng)站制作、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁(yè)設(shè)計(jì)等建站業(yè)務(wù)，價(jià)格優(yōu)惠性價(jià)比高，為秦都等各大城市提供網(wǎng)站開(kāi)發(fā)制作服務(wù)。

博文大綱：
一、索引；
二、視圖；
三、存儲(chǔ)過(guò)程；
四、觸發(fā)器；
五、事務(wù)；

一、索引

索引提供指針以指向存儲(chǔ)在表中指定列的數(shù)據(jù)值，然后根據(jù)指定的次序排列這些指針，再跟隨指針到達(dá)包含該值的列。

1.什么是索引

數(shù)據(jù)庫(kù)中的索引與書(shū)籍中的目錄相似。在一本書(shū)中，無(wú)需閱讀整本書(shū)，利用目錄就可以快速的查找到所需的信息。在數(shù)據(jù)庫(kù)中，索引使數(shù)據(jù)庫(kù)程序無(wú)須對(duì)整個(gè)表進(jìn)行掃描，就可以在其中找到所需的數(shù)據(jù)。書(shū)中的目錄就是一個(gè)詞語(yǔ)列表，其中注明了包含各個(gè)詞的頁(yè)碼。而數(shù)據(jù)庫(kù)中的索引是某個(gè)表中一列或若干列值的集合，以及物理表示這些值得數(shù)據(jù)業(yè)的邏輯指針清單。

索引是SQL Server編排數(shù)據(jù)的內(nèi)部方法，它為SQL Server提供一種方法來(lái)編排查詢數(shù)據(jù)的路由。

索引頁(yè)是數(shù)據(jù)庫(kù)中存儲(chǔ)索引的數(shù)據(jù)頁(yè)。索引頁(yè)存放檢索數(shù)據(jù)行的關(guān)鍵字頁(yè)以及該數(shù)據(jù)行的地址指針。通過(guò)使用索引，可以大大提高數(shù)據(jù)庫(kù)的檢索速度，改善數(shù)據(jù)庫(kù)性能。

2.索引分類

在SQL Server中，常用的索引有：

（1）唯一索引

唯一索引不允許兩行具有相同的索引值。

如果現(xiàn)有數(shù)據(jù)中存在重復(fù)的鍵值，則一般情況下大多數(shù)數(shù)據(jù)庫(kù)不允許創(chuàng)建唯一索引。當(dāng)新數(shù)據(jù)使表中的鍵值重復(fù)時(shí)，數(shù)據(jù)庫(kù)也拒絕接收此數(shù)據(jù)。

創(chuàng)建了唯一約束，將自動(dòng)創(chuàng)建唯一索引。盡管唯一索引有助于找到信息，但是為了獲得最佳性能，建議使用主鍵約束。

（2）主鍵索引

在數(shù)據(jù)庫(kù)關(guān)系圖中為表定義一個(gè)主鍵將自動(dòng)創(chuàng)建主鍵索引，主鍵索引是唯一索引的特殊類型。

主建索引要求主鍵中的每個(gè)值都是唯一的。當(dāng)在查詢使用主鍵索引時(shí)，它還允許快速訪問(wèn)數(shù)據(jù)。

（3）聚集索引

在聚集索引中，表中各行的物理順序與鍵值的邏輯（索引）順序相同。

一個(gè)表中只能包含一個(gè)聚集索引。

（4）非聚集索引

非聚集索引建立在索引頁(yè)上，在查詢數(shù)據(jù)是可以從索引中找到記錄存放的位置。
非聚集索引使表中各行數(shù)據(jù)存放的物理順序與鍵值的邏輯順序不匹配。聚集索引比非聚集索引有更快的數(shù)據(jù)訪問(wèn)速度。

在SQL Server中，一個(gè)表只能創(chuàng)建一個(gè)聚集索引，但可以有多個(gè)非聚集索引。設(shè)置某列為主鍵，該列就默認(rèn)為聚集索引。

（5）復(fù)合索引

在創(chuàng)建索引時(shí)，并不是只能對(duì)其中一列創(chuàng)建索引，與創(chuàng)建主鍵一樣，可以將多個(gè)列組合作為索引，這種索引稱為復(fù)合索引。

注意：只有用到復(fù)合索引的第一列或整個(gè)復(fù)合索引列作為條件完成數(shù)據(jù)查詢時(shí)才會(huì)用到該索引。

（6）全文索引

全文索引是一種特殊類型的基于標(biāo)記的功能性索引，由SQL Server中全文引擎服務(wù)創(chuàng)建和維護(hù)。

全文索引主要用于在大量文本中搜索字符串，此時(shí)使用全文索引的效率將大大高于使用T-SQL的LIKE關(guān)鍵字的效率。

3.創(chuàng)建和使用索引

創(chuàng)建索引的方法有兩種：使用SSMS和T-SQL語(yǔ)句。

使用SSMS創(chuàng)建索引，如下：



使用SSMS創(chuàng)建索引完成！

select * from chengji
WITH (INDEX=IX_chengji)
where name LIKE '孫%'
//使用創(chuàng)建的索引查詢數(shù)據(jù)

雖然可以指定SQL Server按哪個(gè)索引進(jìn)行數(shù)據(jù)查詢，但一般不需要人工指定。SQL Server將會(huì)根據(jù)所創(chuàng)建的索引，自動(dòng)優(yōu)化查詢。

使用索引可加快數(shù)據(jù)檢索速度，但為每個(gè)列都建立索引沒(méi)有必要。因?yàn)樗饕旧硪彩切枰S護(hù)，并占用一定的資源，可以按照以下標(biāo)準(zhǔn)選擇建立索引的列。

• 頻繁搜索的列；
• 經(jīng)常用于查詢選擇的列；
• 經(jīng)常排序、分組的列；
• 經(jīng)常用于連接的列（主鍵/外鍵）；

不要使用下面的列創(chuàng)建索引：

• 僅包含幾個(gè)不同值的列；
• 表中僅包含幾行；

在工作中的經(jīng)驗(yàn)：

• 查詢時(shí)減少使用“*”返回全部列，不要返回不需要的列；
• 索引應(yīng)該盡量小，在字節(jié)小的列上建立索引；
• where子句中有多個(gè)條件表達(dá)式時(shí)，包含索引列的表達(dá)式應(yīng)置于其他條件表達(dá)式之前；
• 避免在order by子句中使用表達(dá)式；
• 根據(jù)業(yè)務(wù)數(shù)據(jù)發(fā)生頻率，定期重新生成或重新組織索引，進(jìn)行碎片整理；

二、視圖

視圖是保存在數(shù)據(jù)庫(kù)中的select查詢。因此，對(duì)查詢指定的大多數(shù)操作也可以在視圖上進(jìn)行。

使用視圖的原因有：

• （1）處于安全考慮，用戶不必看到整個(gè)數(shù)據(jù)庫(kù)結(jié)構(gòu)，而隱藏部分?jǐn)?shù)據(jù)；
• （2）符合用戶日常業(yè)務(wù)邏輯，使其對(duì)數(shù)據(jù)更容易理解；

1.什么是視圖

視圖是另一種查看數(shù)據(jù)庫(kù)中一個(gè)或多個(gè)表中的數(shù)據(jù)的方法。視圖是一種虛擬表，通常是作為來(lái)自一個(gè)或多個(gè)表的行或列的子集創(chuàng)建的。當(dāng)然，視圖也可以包含全部的行和列。但是，視圖并不是數(shù)據(jù)庫(kù)中存儲(chǔ)的數(shù)據(jù)值的集合，它的行和列來(lái)自查詢中引用的表。在執(zhí)行時(shí)，視圖直接顯示來(lái)自表中的數(shù)據(jù)。

視圖充當(dāng)著查詢中指定的表的篩選器。定義視圖的查詢可以基于一個(gè)或多個(gè)表，也可以基于其他視圖、當(dāng)前數(shù)據(jù)庫(kù)或其他數(shù)據(jù)庫(kù)。

視圖通常用來(lái)進(jìn)行以下三種操作：

• 篩選表中的行；
• 防止未經(jīng)許可的用戶訪問(wèn)敏感的信息；
• 將多個(gè)物理數(shù)據(jù)表抽象為一個(gè)邏輯數(shù)據(jù)表。

（1）使用視圖的好處：

（1）對(duì)最終用戶的好處：
結(jié)果更容易理解；
獲得數(shù)據(jù)更容易；
（2）對(duì)開(kāi)發(fā)人員的好處：
限制數(shù)據(jù)檢索更容易；
維護(hù)應(yīng)用程序更方便；

（2）創(chuàng)建視圖

語(yǔ)法：

create view aa
as
SELECT     dbo.基本信息表.學(xué)號(hào), dbo.成績(jī)表.學(xué)號(hào) AS Expr1, dbo.基本信息表.姓名, dbo.成績(jī)表.成績(jī)
FROM         dbo.基本信息表 INNER JOIN
                      dbo.成績(jī)表 ON dbo.基本信息表.學(xué)號(hào) = dbo.成績(jī)表.學(xué)號(hào)

查看視圖

select * from aa

（3）使用視圖的注意事項(xiàng)

（1）每個(gè)視圖可以使用多個(gè)表；
（2）與查詢相似，一個(gè)視圖可以嵌套另一個(gè)視圖，最好不要超過(guò)三層；
（3）視圖定義中的select語(yǔ)句不能包含以下內(nèi)容：

• order by子句，除非子啊select語(yǔ)句的選擇列表中也有一個(gè)TOP子句；
• into關(guān)鍵字；
• 引用臨時(shí)表或表變量；

三、存儲(chǔ)過(guò)程

SQL Server使用存儲(chǔ)過(guò)程來(lái)避免遠(yuǎn)程發(fā)送并執(zhí)行SQL代碼帶來(lái)的安全隱患。

1.為什么需要存儲(chǔ)過(guò)程

當(dāng)今的軟件大多應(yīng)用于網(wǎng)絡(luò)中，而一般應(yīng)用程序所運(yùn)用的數(shù)據(jù)保存在數(shù)據(jù)庫(kù)中。在沒(méi)有使用存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)應(yīng)用程序中，用戶大多從本地極端及客戶端通過(guò)網(wǎng)絡(luò)向服務(wù)器端發(fā)送SQL代碼編寫的請(qǐng)求，服務(wù)器端對(duì)接收到SQL代碼進(jìn)行語(yǔ)法編譯后執(zhí)行，并經(jīng)指定結(jié)果傳送回客戶端，再由客戶端的應(yīng)用軟件處理后輸出。如果開(kāi)發(fā)者對(duì)服務(wù)器的安全性考慮不全面，就會(huì)為非法者提供盜取數(shù)據(jù)的機(jī)會(huì)。如圖：

未經(jīng)授權(quán)的非法者在網(wǎng)絡(luò)中截取用戶想服務(wù)器發(fā)送的SQL代碼，改寫后的惡意SQL代碼提交到服務(wù)器編譯并執(zhí)行，最后非法者就比較容易地獲得他所需的數(shù)據(jù)。

從圖中，我們可以看到應(yīng)用程序執(zhí)行的過(guò)程是不安全的，主要在于以下幾個(gè)方面：

• 數(shù)據(jù)不安全：網(wǎng)絡(luò)傳送SQL代碼，容易被未經(jīng)授權(quán)者截取；
• 每次提交SQL代碼都要經(jīng)過(guò)語(yǔ)法編譯后再執(zhí)行，影響應(yīng)用程序的運(yùn)行性能；
• 網(wǎng)絡(luò)流量大：對(duì)于反復(fù)執(zhí)行的相同SQL代碼，將會(huì)在網(wǎng)絡(luò)上多次傳送，增加網(wǎng)絡(luò)傳輸流量。

為了解決這些問(wèn)題，我們可以采用存儲(chǔ)過(guò)程把對(duì)數(shù)據(jù)庫(kù)操作的SQL代碼預(yù)先編譯好并保存在服務(wù)器端，用戶只需在本機(jī)上輸入要執(zhí)行的存儲(chǔ)過(guò)程名稱和必要的數(shù)據(jù)就可以直接調(diào)用執(zhí)行存儲(chǔ)過(guò)程完成行管的操作。這樣。既減少了網(wǎng)絡(luò)傳輸流量，又能保證應(yīng)用程序的運(yùn)行性能，同時(shí)也防止了未經(jīng)授權(quán)者想截獲SQL代碼的行為。

2.什么是存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程是SQL語(yǔ)句和控制語(yǔ)句的預(yù)編譯集合，保存在數(shù)據(jù)庫(kù)中，可由應(yīng)用程序調(diào)用執(zhí)行，而且允許用戶聲明變量，邏輯控制語(yǔ)句及其他強(qiáng)大的編程功能。

使用存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：

• 1.模塊化程序設(shè)計(jì)；
• 2.執(zhí)行速度快、效率高；減少網(wǎng)絡(luò)流量；
• 3.減少網(wǎng)絡(luò)流量；
• 4.具有良好的安全性；

3.系統(tǒng)存儲(chǔ)過(guò)程

SQL Server提供系統(tǒng)存儲(chǔ)過(guò)程，它們是一組預(yù)編譯的T-SQL語(yǔ)句。系統(tǒng)存儲(chǔ)過(guò)程提供了管理數(shù)據(jù)庫(kù)和更新表的機(jī)制，并充當(dāng)從系統(tǒng)表中檢索信息的快捷方式

（1）常用的系統(tǒng)存儲(chǔ)過(guò)程

SQL Server的系統(tǒng)存儲(chǔ)過(guò)程的名稱以“sp-”開(kāi)頭，并存放在Resource數(shù)據(jù)庫(kù)中。如圖：

比如：

exec sp_databases
#列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫(kù)
exec sp_helptext aa
#查看視圖的語(yǔ)句文本

若xp_cmdshell作為服務(wù)器安全配置的一部分而被關(guān)閉，請(qǐng)使用如下語(yǔ)句啟用：

exec sp_configure 'show advanced options',1
#顯示高級(jí)配置信息
go
reconfigure
#重新配置
go
exec sp_configure 'xp_cmdshell',1
#打開(kāi)xp_cmdshell選項(xiàng)
go
reconfigure
#重新配置
go

（2）常用的擴(kuò)展存儲(chǔ)過(guò)程

比如使用這些語(yǔ)句在系統(tǒng)中創(chuàng)建某些文件：

exec xp_cmdshell 'md c:\bank',no_output
#創(chuàng)建文件夾c:\bank
exec xp_cmdshell 'dir c:\'
#列出c盤下的文件等內(nèi)容

create   proc  oo
as
select 姓名, SUM(成績(jī))  as 總成績(jī)
from   基本信息表  left join  成績(jī)表  on  基本信息表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)
group  by  姓名
#創(chuàng)建存儲(chǔ)過(guò)程qq
exec qq
#查看存儲(chǔ)過(guò)程qq
create   proc  ww
@shuo  varchar(10)
as
select 姓名, SUM(成績(jī))  as 總成績(jī)
from   基本信息表  left join  成績(jī)表  on  基本信息表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)
group  by  姓名
having  姓名=@shuo
#創(chuàng)建針對(duì)每個(gè)同學(xué)查看的記錄
exec ww 張三
#查看ww存儲(chǔ)過(guò)程但是只查看張三

四、觸發(fā)器

觸發(fā)器分為以下幾種
INSERT觸發(fā)器：當(dāng)向表中插入數(shù)據(jù)時(shí)觸發(fā)，自動(dòng)執(zhí)行觸發(fā)器定義的SQL語(yǔ)句；
UPDATE觸發(fā)器：當(dāng)更新表中某列、多列時(shí)觸發(fā)，自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)句；
DELETE觸發(fā)器：當(dāng)刪除表中記錄時(shí)觸發(fā)，自動(dòng)執(zhí)行觸發(fā)器定義的SQL語(yǔ)句。
兩個(gè)特殊的表由系統(tǒng)管理：

創(chuàng)建觸發(fā)器的語(yǔ)句：
第一種

create   trigger   刪除
on  科目表
for  delete
as
begin
delete  from 成績(jī)表  
end
#刪除之后不會(huì)同步從表中的數(shù)據(jù)

第二種

create   trigger   自動(dòng)同步成績(jī)
on  科目表
after delete
as
begin
delete  from 成績(jī)表  where 科目id=(select   科目id  from deleted)
end
#刪除之后自動(dòng)同步成績(jī)

第三種

create  trigger  禁止刪除
on 基本信息表
for  delete
as
print  '禁止刪除'
rollback  transaction
#禁止刪除數(shù)據(jù)，如果刪除數(shù)據(jù)則執(zhí)行回滾、撤回操作

五、事務(wù)

事務(wù)：保證數(shù)據(jù)庫(kù)的原子性、一致性、隔離性、持久性，簡(jiǎn)稱ACID。
一個(gè)小實(shí)例

begin transaction
declare @errorsum int
set @errorsum=0
#定義 內(nèi)部變量，用來(lái)保存前一條的執(zhí)行結(jié)果，執(zhí)行成功為0，執(zhí)行不成功為非0.
/*--轉(zhuǎn)帳：張三的帳戶少1000，李四的帳戶多1000元--*/
update bank set currentmoney=currentmoney-1000
where name='zhangsan'
set @errorsum=@errorsum+@@ERROR 
update bank set currentmoney=currentmoney+1000
where name='lisi'
set @errorsum=@errorsum+@@error
print '查看轉(zhuǎn)賬事務(wù)中的余額'
select * from bank
if @errorsum<>0
 begin
  print '交易失敗，回滾事務(wù)'
  rollback transaction
 end
else
 begin
   print '交易成功，提交事務(wù)，寫入硬盤，永久地保存'
   commit transaction
  end
 go
 print '查看轉(zhuǎn)賬事務(wù)后的余額'
 select * from bank
 go

———————— 本文至此結(jié)束，感謝閱讀 ————————