介绍
SQL Server是由微软公司开发的关系型数据库管理系统,现在是全世界主流数据库之一。它具备方便使用、可伸缩性好、相关软件集成程度高等优势,能够从单一的笔记本上运行或以高倍云服务器集群为基础,或在这两者之间任何一种方式上运行。
一.索引
1.索引是什么
索引是SQL Server编排数据内部方法,通过索引可以快速查找数据,而无需扫描整个表。
类似于字典的音节索引页似的,可以快速查找到,你所想要找到的字而无需一页一页翻。
2.索引的分类
- 唯一索引:不允许两行具有相同的索引值,如果尝试插入一个已经存在的值,数据库将会拒绝这个操作。
- 主键索引:要求主键中的每个值是唯一的,主键索引通常用于唯一标识每一行,以便于快速查找和连接数据。
- 聚集索引:数据存放的物理顺序与索引顺序相同,聚集索引可以加快基于索引列的数据检索操作
- 非聚集索引:数据存放的物理顺序与索引顺序不相同
- 复合索引:将多个列组合而成的索引
- 全文索引:一种特殊类型的基于标记的功能性索引,全文索引通常用于处理大量文本数据,如文章、博客帖子或文档
3.唯一索引和主键索引的区别:
主键索引一定是唯一索引,唯一索引不一定是主键索引。主键索引一定是聚集索引。唯一索引不一定是聚集索引。
4.选择索引的标准
- 频繁搜索的列
- 经常用作查询选择的列
- 经常排序、分组的列
- 经常用作连接的列(主键/外键)
- 大量数据
5.创建索引
1.选中一张表,展开表,找到索引,右击索引——新建索引
2.在新建索引对话框中,指定索引名称,可以选择给那一列创建索引,创建什么类型的索引,完成后点击确定,就创建好了一个新的索引。
二.视图
1.什么是视图
- 视图是一种虚拟表,通常是作为来自一个或多个表的行或列的子集创建的。
- 视图直接显示来自表中的数据,只供查看,无法修改。
视图的作用
- 筛选表中的数据
- 防止未经许可的用户访问敏感数据
- 将多个物理数据表抽象
使用视图的好处
- 对于最终的用户:结果更容易理解,获取数据更容易。
- 对于开发:限制数据检索更容易,维护应用程序更方便
创建视图:
1.展开数据库——找到视图,右击视图——新建视图
2.选择要添加到视图中的表,选择表然后点击添加。可以添加多张表。添加完成后点击关闭。
手动选择表之间的连接条件,并选择最终要在视图中显示的列。(在要显示的列前面打钩)
配置完后,点击保存
三.存储过程
1.什么是存储过程
存储过程是SQL语句和控制语句的预编译集合,保存在数据库里,可由应用程序调用执行。
2.为什么需要存储过程
- 数据不安全
- 网络流量大
- 影响应用程序的运行性能
- 从客户端到网络服务器发送SQL代码并执行不妥当
3.使用存储过程的优点
- 模块化程序设计
- 执行速度快,效率高
- 减少网络流量
- 具有良好的安全性
4.存储过程分类
(1)系统存储过程
常用的系统存储过程(以“sp_”开头,存放在Resource数据库中)
2.常用的扩展存储过程(以“xp_”开头,以DLL形式单独存在)
扩展存储过程默认被关闭,需要启用
(2)用户自定义的存储过程
调用执行存储过程:
EXEC[UTE] 存储过程名 [参数值]
创建存储过程: (使用T-SQL语句创建) Create proc 存储过程名称 [@参数1 数据类型] [@参数1 数据类型] As SQL 语句
使用SSMS创建
四. 触发器
1.什么是触发器
- 是在对表进行插入、更新或删除操作时自动执行的存储过程
- 用于强制业务规则,可以定义比用 CHECK 约束更为复杂的约束
- 通过事件触发而被执行的
2.分类
INSERT触发器:当向表中插入数据时触发
UPDATE触发器:当更新表中某列、多列时触发
DELETE触发器:当删除表中记录时触发
触发器涉及到两张表(delete表和inserted表)(由系统管理,用户不可以修改,仅做了解)
修改操作 |
inserted表 |
deleted表 |
增加(INSERT)记录时 |
存放新增的记录 |
—— |
删除(DELETE)时 |
—— |
存放被删除的记录 |
修改(UPDATE)时 |
存放用来更新的新记录 |
存放更新前的记录 |
3.触发器的作用
强化约束(实现比CHECK语句更为复杂的约束)
跟踪变化(侦测数据库内的操作,从而不允许数据库中未经许可的指定更新和变化)
级联运行(侦测数据库内的操作,并自动地级联影响整个数据库的各项内容)
4.创建触发器
(1)使用SSMS创建
(2)使用T-SQL语句创建触发器
Create trigger 触发器名称 On 执行触发器的表 [with encryption] #加密syscomments 表中包含create trigger语句文本的条目。 From [delete,insert,update] As SQL 语句
实战案例
创建视图
方法一:在图形界面下创建视图(以Myschool数据库为例)
创建一个视图,分别来自三个表的三个列,并重命名列,生成的视图名为student_info,如下图所示:
实验案例一:验证索引的作用
- 创建数据库test,再创建一个数据量大的表,名称为“学生表”,分别有三列,学号,姓名和班级,如下图所示,学号为自动编号,班级为默认值“一班”。
- 向表中插入大量数据,数据越多,验证索引的效果越好。
使用语句完成:While 1>0 Insert into 学生表(姓名) values(‘张三’)
上面语句是一个死循环,除非强制结束,如果1大于0就会一直向表中插入姓名
如下图所示:
等待5分钟左右,打开表的属性,查看表的行数,当前为1032363,如下图所示:
使用语句查询第900000行的数据,Select * from 学生表 Where 学号=900000
4、打开“sql server 2016 profiler ”工具进行跟踪,如下图所示:
打开“sql server profiler ”工具查看跟踪的信息,发现查询时间很长,cpu工作了265毫秒,reads:读了8649次,writes:写了10次,duration:总计花费2336毫秒完成查询。
为了下面分析文件更准确,多执行几次Select * from 学生表 Where 学号=900000
然后把跟踪的结果保存在桌面上:
- 打开“SQL server 2016数据库引擎优化顾问”,添加跟踪文件,进行分析,发现索引建议,需要建立索引。
注意选择test数据库中的学生表,然后点击“开始分析”
索引类型为clusterd(聚集索引),索引列为“学号”。
- 按照“数据库引擎优化顾问”的索引建议建立聚集索引,并且选择“唯一”
- 再次执行Select * from 学生表Where 学号=900000
- 打开sql server profiler查看跟踪的时间,发现查询时间大幅提升,说明索引可以提高查询速度。
发现总计时间为1毫秒,几乎忽略不计
实验案例二:分别练习创建各种索引
- 创建聚集索引
目前tstudent表中没有任何索引也没有主键
为tstudent表创建聚集索引
选中studentID,单击左上侧的主键按钮
为Tstuden表的studentID创建主键就同时创建了聚集索引
2、创建组合索引
为成绩表创建组合索引,因为一个学生不能为一门学科录入两次成绩,所以将成绩表中的studentID和subjectID创建组合索引
解决办法:
菜单栏----工具----选项
找到设计器(designers),将标记处的勾去掉,单击“确定”
这样组合索引就创建成功了。
3创建唯一索引
创建唯一性约束的时候就会创建唯一性索引,不能有重复值
为Tstudent表创建唯一非聚集索引
createuniquenonclusteredindexU_cardIDonTStudent(cardID)
4、创建非聚集索引---可以有重复值
为Tstudent表的姓名列创建非聚集索引
create nonclustered index non_sname on tstudent(sname)
使用命令查看表上的索引
Select * from sys.sysindexes where id=(select object_id from sys.all_objects where name='Tstudent')
Indid中1代表聚集索引
Indid中2代表唯一非聚集索引
Indidz中3代表非聚集索引
使用sp_help Tstudent也可以查看到相关表的信息
sp_help Tstudent
实验案例一:验证索引的作用
- 创建数据库test,再创建一个数据量大的表,名称为“学生表”,分别有三列,学号,姓名和班级,如下图所示,学号为自动编号,班级为默认值“一班”。
- 向表中插入大量数据,数据越多,验证索引的效果越好。
使用语句完成:While 1>0 Insert into 学生表(姓名) values(‘张三’)
上面语句是一个死循环,除非强制结束,如果1大于0就会一直向表中插入姓名
如下图所示:
等待5分钟左右,打开表的属性,查看表的行数,当前为1032363,如下图所示:
使用语句查询第900000行的数据,Select * from 学生表 Where 学号=900000
4、打开“sql server 2016 profiler ”工具进行跟踪,如下图所示:
打开“sql server profiler ”工具查看跟踪的信息,发现查询时间很长,cpu工作了265毫秒,reads:读了8649次,writes:写了10次,duration:总计花费2336毫秒完成查询。
为了下面分析文件更准确,多执行几次Select * from 学生表 Where 学号=900000
然后把跟踪的结果保存在桌面上:
- 打开“SQL server 2016数据库引擎优化顾问”,添加跟踪文件,进行分析,发现索引建议,需要建立索引。
注意选择test数据库中的学生表,然后点击“开始分析”
索引类型为clusterd(聚集索引),索引列为“学号”。
- 按照“数据库引擎优化顾问”的索引建议建立聚集索引,并且选择“唯一”
- 再次执行Select * from 学生表Where 学号=900000
- 打开sql server profiler查看跟踪的时间,发现查询时间大幅提升,说明索引可以提高查询速度。
发现总计时间为1毫秒,几乎忽略不计
实验案例二:分别练习创建各种索引
- 创建聚集索引
目前tstudent表中没有任何索引也没有主键
为tstudent表创建聚集索引
选中studentID,单击左上侧的主键按钮
为Tstuden表的studentID创建主键就同时创建了聚集索引
2、创建组合索引
为成绩表创建组合索引,因为一个学生不能为一门学科录入两次成绩,所以将成绩表中的studentID和subjectID创建组合索引
解决办法:
菜单栏----工具----选项
找到设计器(designers),将标记处的勾去掉,单击“确定”
这样组合索引就创建成功了。
3创建唯一索引
创建唯一性约束的时候就会创建唯一性索引,不能有重复值
为Tstudent表创建唯一非聚集索引
createuniquenonclusteredindexU_cardIDonTStudent(cardID)
4、创建非聚集索引---可以有重复值
为Tstudent表的姓名列创建非聚集索引
create nonclustered index non_sname on tstudent(sname)
使用命令查看表上的索引
Select * from sys.sysindexes where id=(select object_id from sys.all_objects where name='Tstudent')
Indid中1代表聚集索引
Indid中2代表唯一非聚集索引
Indidz中3代表非聚集索引
使用sp_help Tstudent也可以查看到相关表的信息
sp_help Tstudent
实验案例三:创建视图
方法一:在图形界面下创建视图(以Myschool数据库为例)
创建一个视图,分别来自三个表的三个列,并重命名列,生成的视图名为student_info,如下图所示:
通过查询语句查看视图:select * from student_info
方法二:使用语句创建视图(以schoolDB数据库为例)
进行数据库设计的时候,一个表有很多列,我们可以在表上创建视图,只显示指定的列。
Select语句可以作为一个视图
selectSname,sex,ClassfromTStudentwhereClass='网络班'
- 创建视图,筛选行和列
(1)创建视图
createviewnetstudent
as
selectSname,sex,Classfromdbo.TStudentwhereClass='网络班'
(2)从视图中查找数据:
select*fromnetstudentwheresex='男'
2、创建视图,更改列的表头
(1)创建
createviewV_Tstudent1
as
selectStudentID 学号,Sname姓名,sex 性别,cardID 身份证号码,Birthday 生日,Class 班级fromTStudent
(2)从视图中查找数据:
select*fromV_Tstudent1
以后再去查询的时候就非常方便了。
实验案例四:存储过程
1、常用的系统存储过程
exec sp_databases --列出当前系统中的数据库
use MySchool
go
exec sp_tables --当前数据库中可查询对象的列表
exec sp_columns student --查看表student中列的信息
exec sp_help student --查看表student的所有信息
exec sp_helpconstraint student --查看表student表的约束
exec sp_stored_procedures --返回当前数据库中的存储过程列表
2、常用的扩展存储过程(在C盘下创建一个文件夹bank)
exec xp_cmdshell 'mkdir c:\bank',no_output --创建文件夹c:\bank
exec xp_cmdshell 'dir c:\bank\' --查看文件
如果执行不了上面的语句,请开启下面的功能。然后再次执行上面的两条语句。
若xp_cmdshell作为服务器安全配置的一部分而被关闭,请使用如下语句启用:
exec sp_configure 'show advanced options', 1 --显示高级配置选项(单引号中的只能一个空格隔开)
go
reconfigure --重新配置
go
exec sp_configure 'xp_cmdshell',1 --打开xp_cmdshell选项
go
reconfigure --重新配置
go
3、用户自定义的存储过程(以schoolDB数据库为例,计算网络管理专业的平均分)
use schoolDB
go
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
执行验证效果:
exec usp_getaverageresult
实验案例五:触发器
(Myschool数据库为例)
创建触发器(禁止修改admin表中数据):
create trigger reminder
on admin
for update
as
print '禁止修改,请联系DBA'
rollback transaction
go
执行语句,查看错误信息:
update Admin set LoginPwd='123' where LoginId='benet'
select * from Admin
实验案例六:事务
案例:完成转账的过程,如果转账1000会回滚,提示失败,如果转账800,提示成功。(参看书140页)
要求:
- 创建表名为bank,如图所示:
- Currentmoney列的Check约束:
(2)插入两条数据:
INSERT INTO bank(customerName,currentMoney) VALUES('张三',1000)
INSERT INTO bank(customerName,currentMoney) VALUES('李四',1)
- 转账的过程代码参考书140页。(先执行转账1000,查看结果;之后转账800,查看结果)
print '查看转账事务前的余额'
select * from bank
go
begin transaction
declare @errorsum int
set @errorsum=0
update bank set currentmoney=currentmoney-1000
where customername='张三'
set @errorsum=@errorsum+@@ERROR
update bank set currentmoney=currentmoney+1000
where customername='李四'
set @errorsum=@errorsum+@@ERROR
print ' 查看转账事务过程中的余额'
select * from bank
if @errorsum <>0
begin
print '交易失败'
rollback transaction
end
else
begin
print '交易成功'
commit transaction
end
go
print ' 查看转账事务后的余额'
select * from bank
go