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