SQLserver 數(shù)據(jù)庫的索引，存儲過程和觸發(fā)器的使用與概念

前言：索引，存儲過程和觸發(fā)器可以對一些高級的數(shù)據(jù)處理和查詢，從而更好的實現(xiàn)對數(shù)據(jù)庫的操作，診斷和優(yōu)化。

一.索引

 索引提供指針以指向存儲在表中指定的數(shù)據(jù)值，數(shù)據(jù)庫的索引，就好比一本書中的目錄類似，無需閱讀整個一本書，利用目錄就可以快速的查找所需信息。在數(shù)據(jù)庫中，索引使數(shù)據(jù)庫程序無需對整個表進行掃描，就可以在其中找到所需數(shù)據(jù)。通過使用索引，可以大大提高數(shù)據(jù)庫的查詢速度

 （1） 索引分類

   1.唯一索引

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

   2.主鍵索引

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

   3.聚集索引

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

   4.非聚集索引

   非聚集索引建立在索引頁上，在查詢數(shù)據(jù)時可以從索引中找到記錄存放位置

   5.復合索引

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

 （2）創(chuàng)建和使用索引

1、首先創(chuàng)建一個數(shù)據(jù)量大的表，名稱為“學生表”，分別有三列，學號，姓名和班級，如下圖所示，學號為自動編號，班級為默認值“一班”。

2、向表中插入大量數(shù)據(jù)，數(shù)據(jù)越多，驗證索引的效果越好。

使用語句完成：While 1>0  Insert into t388(姓名)  values(‘小明’)

上面語句是一個死循環(huán)，除非強制結(jié)束，如果1大于0就會一直向表中插入姓名

如下圖所示：

3，等待5分鐘左右，打開表的屬性，查看表的行數(shù)，當前為1，如下圖所示：713179

4，使用語句查詢第600000行的數(shù)據(jù)，Select * from t388 Where 學號=600000

5、打開“sql server profiler ”工具進行跟蹤，如下圖所示：

打開“sql server profiler ”工具查看跟蹤的信息，發(fā)現(xiàn)查詢時間很長，cpu工作了188毫秒，reads：讀了6614次，writes：寫了9次，duration：總計花費2977毫秒完成查詢。

6，開始創(chuàng)建索引（唯一索引）

點擊確定，完成創(chuàng)建，再次使用語句查詢第600020行的數(shù)據(jù)，Select * from t388 Where 學號=600020

這是唯一索引創(chuàng)建。主鍵索引不用創(chuàng)建，把列設置為表的主鍵，自動生成主鍵索引

二，存儲類型

   （1） 存儲過程是sql語句和控制語句的預編譯集合，保存在數(shù)據(jù)庫中，可由應用程序執(zhí)行，而且允許用戶聲明變量，邏輯控制語句和強大的編程功能

    使用存儲過程的好處：

        1.模塊化程序設計

      2.執(zhí)行速度快，效率高

      3.減少網(wǎng)絡流量

      4.具有很好的安全性

   （2）系統(tǒng)存儲過程

      sql-server提供了很多的系統(tǒng)存儲過程，他們是一組預編譯的T-SQL語句，系統(tǒng)存儲過程提供了管理數(shù)據(jù)庫和更新表的機制，并充當從系統(tǒng)表中檢索信        息的快捷方式。

           常用的系統(tǒng)存儲過程

           sp_database 列出服務器上的所有數(shù)據(jù)庫的信息，包括數(shù)據(jù)庫名和數(shù)據(jù)大小

           sp_helpdb 報告有關指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息

           sp_renamedb 更改數(shù)據(jù)庫的名稱

           sp_tables 返回當前環(huán)境下可查詢的表和視圖的信息

           sp_columns 返回某個表和視圖的列信息，包括列的數(shù)據(jù)類型和長度等

           sp_help 查看某個數(shù)據(jù)庫對象的信息：如列名，主鍵，約束，外鍵，索引等

           sp_helpconstraint 查看某個表的索引

           sp_stored_procedures 顯示存儲過程的列表

           sp_password 添加或修改登錄賬戶的密碼

           sp_helptext 顯示默認值，未加密的存儲過程，用戶定義的存儲過程，觸發(fā)器或視圖的實際文本

            使用T-SQL語句調(diào)用執(zhí)行存儲過程的語法如下：

             EXEC 存儲過程名 [參數(shù)值]

                 其中EXEC時execute的簡寫

    （3）使用系統(tǒng)存儲過程，以查詢數(shù)據(jù)庫大小的 sp_database 為例，以上的都是以下格式來執(zhí)行

 （4）擴展存儲過程

    語法：EXEC xp_cmdshell DOS命令 {no_output}

    若xp_cmdshell作為服務器安全配置的一部分而被關閉，請用以下語句開啟：

   exec sp_configure 'show advanced options',1  --顯示高級配置信息

   go 

   reconfigure   --重新配置

   go

    exec sp_configure 'xp_cmdshell',1

   go

   reconfigure   -- 重新配置

   go

以在C盤創(chuàng)建一個bene的文件夾為例

exec xp_cmdshell 'mkdir c:\bene',no_output

（5）自定義存儲過程

          在sql-server中，用于創(chuàng)建處處過程的sql語句為create procedure,所有的存儲過程都存放在當前數(shù)據(jù)庫中。一個完整的存儲過程都包含以下三        部分

          1.輸入?yún)?shù)和輸出參數(shù)

          2.在存儲過程中執(zhí)行的T-SQL語句

          3.存儲過程的返回值

   自定義存儲過程

   以編寫求網(wǎng)絡管理平均分存儲過程為例

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'usp_GetAverageResult') 

DROP PROCEDURE usp_GetAverageResult

GO

CREATE PROCEDURE usp_GetAverageResult

AS

DECLARE @subJectID nvarchar(4)

SELECT @subJectID=subJectID FROM dbo.TSubject WHERE subJectName='網(wǎng)絡管理'

DECLARE @avg decimal (18,2)

SELECT @avg=avg(mark) from dbo.TScore where subJectID=@subJectID

PRINT '網(wǎng)絡管理專業(yè)平均分是:'+CONVERT(VARCHAR(5),@avg)

go

上述代碼主要理解創(chuàng)建存儲過程的語法，其中涉及的變量及判斷語句等無需深究，只要能根據(jù)語句理解就可，有興趣可以查看資料自學

三，觸發(fā)器

  觸發(fā)器是一種特殊的存儲過程，當表中的數(shù)據(jù)發(fā)生更新時將自動調(diào)用，以響應INSERT，UPDATE，DELETE語句

  （1）觸發(fā)器類型
        INSERT觸發(fā)器：當向表中插入數(shù)據(jù)是觸發(fā)，自動執(zhí)行所定義語句

        UPDATE觸發(fā)器：當更新表中某列，多列時觸發(fā)，自動執(zhí)行所定義語句

        DELETE觸發(fā)器：當刪除表時觸發(fā)，自動執(zhí)行所定義語句

 （2）創(chuàng)建觸發(fā)器

       使用T-SQL語句創(chuàng)建

語句：CREATE TRIGGER [觸發(fā)器名稱]

      ON [需要創(chuàng)建觸發(fā)器的表]

     FOR ([DELETE,INSERT,UPDATE)

     AS SQL 語句

      以當有人試圖在表中更改數(shù)據(jù)時，將提示一條消息并阻止操作為例，以下語句可實現(xiàn)：

create trigger reminder --定義觸發(fā)器名稱為“reminder”

on dbo.TScore --在哪個表執(zhí)行，此例在“dbo.Tscore”表中

for UPDATE --指定在表中執(zhí)行那些數(shù)據(jù)修改語句時激活觸發(fā)器，可以指定多個，用，分隔。此例為“UPDATE”

as

print '禁止修改，請聯(lián)系DBA'   --觸發(fā)時顯示的文字

ROLLBACK TRANSACTION

GO