前言:索引,存储过程和触发器可以对一些高级的数据处理和查询,从而更好的实现对数据库的操作,诊断和优化。
成都创新互联公司是一家专业从事成都做网站、网站设计、网页设计的品牌网络公司。如今是成都地区具影响力的网站设计公司,作为专业的成都网站建设公司,成都创新互联公司依托强大的技术实力、以及多年的网站运营经验,为您提供专业的成都网站建设、营销型网站建设及网站设计开发服务!
一.索引
索引提供指针以指向存储在表中指定的数据值,数据库的索引,就好比一本书中的目录类似,无需阅读整个一本书,利用目录就可以快速的查找所需信息。在数据库中,索引使数据库程序无需对整个表进行扫描,就可以在其中找到所需数据。通过使用索引,可以大大提高数据库的查询速度
(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