SQLserver 數據庫的索引，存儲過程和觸發器的使用與概念

前言：索引，存儲過程和觸發器可以對一些高級的數據處理和查詢，從而更好的實現對數據庫的操作，診斷和優化。

一.索引

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

 （1） 索引分類

   1.唯一索引

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

   2.主鍵索引

   在數據庫關系圖中為表定義一個主鍵將自動創建主鍵索引，主鍵索引是唯一索引的特殊類型

   3.聚集索引

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

   4.非聚集索引

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

   5.復合索引

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

 （2）創建和使用索引

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

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

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

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

如下圖所示：

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

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

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

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

6，開始創建索引（唯一索引）

點擊確定，完成創建，再次使用語句查詢第600020行的數據，Select * from t388 Where 學號=600020

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

二，存儲類型

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

    使用存儲過程的好處：

        1.模塊化程序設計

      2.執行速度快，效率高

      3.減少網絡流量

      4.具有很好的安全性

   （2）系統存儲過程

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

           常用的系統存儲過程

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

           sp_helpdb 報告有關指定數據庫或所有數據庫的信息

           sp_renamedb 更改數據庫的名稱

           sp_tables 返回當前環境下可查詢的表和視圖的信息

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

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

           sp_helpconstraint 查看某個表的索引

           sp_stored_procedures 顯示存儲過程的列表

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

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

            使用T-SQL語句調用執行存儲過程的語法如下：

             EXEC 存儲過程名 [參數值]

                 其中EXEC時execute的簡寫

    （3）使用系統存儲過程，以查詢數據庫大小的 sp_database 為例，以上的都是以下格式來執行

 （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盤創建一個bene的文件夾為例

exec xp_cmdshell 'mkdir c:\bene',no_output

（5）自定義存儲過程

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

          1.輸入參數和輸出參數

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

          3.存儲過程的返回值

   自定義存儲過程

   以編寫求網絡管理平均分存儲過程為例

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='網絡管理'

DECLARE @avg decimal (18,2)

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

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

go

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

三，觸發器

  觸發器是一種特殊的存儲過程，當表中的數據發生更新時將自動調用，以響應INSERT，UPDATE，DELETE語句

  （1）觸發器類型
        INSERT觸發器：當向表中插入數據是觸發，自動執行所定義語句

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

        DELETE觸發器：當刪除表時觸發，自動執行所定義語句

 （2）創建觸發器

       使用T-SQL語句創建

語句：CREATE TRIGGER [觸發器名稱]

      ON [需要創建觸發器的表]

     FOR ([DELETE,INSERT,UPDATE)

     AS SQL 語句

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

create trigger reminder --定義觸發器名稱為“reminder”

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

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

as

print '禁止修改，請聯系DBA'   --觸發時顯示的文字

ROLLBACK TRANSACTION

GO