查看: 1383|回复: 0

[SQLServer] SQLserver 数据库的索引,存储过程和触发器的使用与概念

发表于 2018-3-10 08:00:01

前言:索引,存储过程和触发器可以对一些高级的数据处理和查询,从而更好的实现对数据库的操作,诊断和优化。


一.索引

索引提供指针以指向存储在表中指定的数据值,数据库的索引,就好比一本书中的目录类似,无需阅读整个一本书,利用目录就可以快速的查找所需信息。在数据库中,索引使数据库程序无需对整个表进行扫描,就可以在其中找到所需数据。通过使用索引,可以大大提高数据库的查询速度

(1) 索引分类

1.唯一索引

唯一索引不允许两行具有相同索引值

2.主键索引

在数据库关系图中为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型

3.聚集索引

在聚集索引中,表中各行的物理顺序与键值的逻辑(索引)顺序相同

4.非聚集索引

非聚集索引建立在索引页上,在查询数据时可以从索引中找到记录存放位置

5.复合索引

在创建索引时,并不是只能对其中一列创建索引,与创建主键一样,可以将多个列组合作为索引,这种索引称为复合索引

(2)创建和使用索引


1、首先创建一个数据量大的表,名称为“学生表”,分别有三列,学号,姓名和班级,如下图所示,学号为自动编号,班级为默认值“一班”。

1.png


2、向表中插入大量数据,数据越多,验证索引的效果越好。

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

上面语句是一个死循环,除非强制结束,如果1大于0就会一直向表中插入姓名

如下图所示:


2.png

3,等待5分钟左右,打开表的属性,查看表的行数,当前为1,如下图所示:713179


3.png


4,使用语句查询第600000行的数据,Select * from t388 Where 学号=600000

6.png


5、打开“sql server profiler ”工具进行跟踪,如下图所示:

打开“sql server profiler ”工具查看跟踪的信息,发现查询时间很长,cpu工作了188毫秒,reads:读了6614次,writes:写了9次,duration:总计花费2977毫秒完成查询。

4.png5.png7.png


6,开始创建索引(唯一索引)

8.png

9.png10.png


点击确定,完成创建,再次使用语句查询第600020行的数据,Select * from t388 Where 学号=600020

11.png

这是唯一索引创建。主键索引不用创建,把列设置为表的主键,自动生成主键索引

12.jpg13.png



二,存储类型

(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 为例,以上的都是以下格式来执行

01.png

(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

02.png


以在C盘创建一个bene的文件夹为例

exec xp_cmdshell 'mkdir c:\bene',no_output

02.png

03.png

(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

05.png06.png

07.png

上述代码主要理解创建存储过程的语法,其中涉及的变量及判断语句等无需深究,只要能根据语句理解就可,有兴趣可以查看资料自学



三,触发器

触发器是一种特殊的存储过程,当表中的数据发生更新时将自动调用,以响应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

111.png112.png





回复

使用道具 举报