SQL 脚本三

1、事务
2、索引
3、视图
4、存储过程
     错误处理

//事务

事物的提出：

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作
这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行
事务是一个不可分割的工作逻辑单元
转帐过程就是一个事务。
它需要两条UPDATE语句来完成，这两条语句是一个整体，如果其中任一条出现错误，则整个转帐业务也应取消，两个帐户中的余额应恢复到原

来的数据，从而确保转帐前和转帐后的余额不变，即都是1001元。

事物的ACID：
事务必须具备以下四个属性，简称ACID 属性：
原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行
一致性（Consistency）：当事务完成时，数据必须处于一致状态
隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性

事物的理解：
T-SQL使用下列语句来管理事务：
开始事务：BEGIN TRANSACTION
提交事务：COMMIT TRANSACTION
回滚（撤销）事务：ROLLBACK TRANSACTION
一旦事务提交或回滚，则事务结束。

判断某条语句执行是否出错：
使用全局变量@@ERROR；
@@ERROR只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计；
            如： SET @errorSum=@errorSum+@@error

 

事物的实际应用：
CREATE TABLE bank
(
    customerName CHAR(10),  --顾客姓名
    currentMoney MONEY     --当前余额
)
GO
ALTER TABLE bank
   ADD CONSTRAINT CK_currentMoney   
       CHECK(currentMoney>=1)
GO
INSERT INTO bank(customerName,currentMoney)
        VALUES('张三',1000)
INSERT INTO bank(customerName,currentMoney)
        VALUES('李四',1)

begin transaction
declare @myerror int
set @myerror=0
UPDATE bank SET currentMoney=currentMoney-800
     WHERE customerName='张三'
set @myerror=@myerror+@@error
UPDATE bank SET currentMoney=currentMoney+800
     WHERE customerName='李四'
set @myerror=@myerror+@@error
if(@myerror>0)
begin
 print '出错了，事务取消'
 rollback transaction
end
else
begin
 print '没有错误，提交事务'
 commit transaction
end
GO
--再次查看转帐后的结果。
SELECT * FROM bank
GO

 

//数据库索引

 

SQL Server中的数据是按页（ 4KB ）存放
索引：是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据 。
索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页。
索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。
汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等
我们可以根据拼音或偏旁部首，快速查找某个字词

索引分类：
唯一索引：唯一索引不允许两行具有相同的索引值
主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个
非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的

指针。可以有多个，小于249个

索引的理解与应用：
优点 加快访问速度、加强行的唯一性
缺点   带索引的表在数据库中需要更多的存储空间操纵数据的命令需要更长的处理时间，因为需要对索引进行更新
请按照下列标准选择建立索引的列。
该列用于频繁搜索
该列用于对数据进行排序

请不要使用下面的列创建索引：
列中仅包含几个不同的值。
表中仅包含几行。为小型表创建索引可能不太划算，因为SQL Server在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长

索引的创建：
if exists (select * from sysindexes where name='my_index1')
 drop index stuscore.my_index1
create nonclustered index my_index1 on stuscore(score1) with fillfactor=30//nonclustered 是非聚集索引
go --必须要有

应用查询：
select * from stuscore (index=my_index1) where score1 between 60 and 90

 

 

//视图

 

理解：

视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上视图中并不存放数据，而是存放在视图所引用的原始表（基表）中同一张原始表，根据不同用户的不同需求，可以创建不同的视图

视图的用途
筛选表中的行
防止未经许可的用户访问敏感数据
降低数据库的复杂程度
将多个物理数据库抽象为一个逻辑数据库

视图的创建：
if exists (select * from sysobjects where name='my_view1')
 drop view my_view1
go --必须要有，create view 必须是批查询的第一句
create view my_view1 as
select stu.stuname,stu.email,stuscore.score1,stuscore.score2
from stu,stuscore
where stu.id=stuscore.uid
go

应用查询：
select * from my_view1

 

 

//存储过程

 

理解：

存储过程（procedure）类似于C语言中的函数
用来执行管理任务或应用复杂的业务规则
存储过程可以带参数，也可以返回结果
可以包含数据操纵语句、变量、逻辑 控制语句等
执行速度更快
允许模块化程序设计
提高系统安全性
减少网络流通量

分类：
系统存储过程
由系统定义，存放在master数据库中
类似C语言中的系统函数
系统存储过程的名称都以“sp_”开头或”xp_”开头
用户自定义存储过程
由用户在自己的数据库中创建的存储过程
类似C语言中的用户自定义函数

 

系统存储过程：

系统存储过程的调用：
EXEC sp_databases
EXEC  sp_renamedb 'Northwind1','Northwind'
USE classscore
GO
EXEC sp_tables
EXEC sp_columns stu 
EXEC sp_help stu
EXEC sp_helpconstraint stu
EXEC sp_helpindex stu
EXEC sp_helptext 'my_view1'
EXEC sp_stored_procedures 

USE master
GO
EXEC xp_cmdshell 'mkdir d:/bank', NO_OUTPUT

 

自定义存储过程：
if exists (select * from sysobjects where name='my_proc1')
 drop proc my_proc1
go
create proc my_proc1
as
declare @myavg int
select @myavg=avg(score1) from stuscore
print '平均分：'+convert(varchar(5),@myavg)
select * from stuscore where score1<60
go

调用：
exec my_proc1

 

自定义带参数的存储过程：
if exists (select * from sysobjects where name='my_proc1')
 drop proc my_proc1
go
create proc my_proc1
@pass int
as
declare @myavg int
select @myavg=avg(score1) from stuscore
print '平均分：'+convert(varchar(5),@myavg)
select * from stuscore where score1<@pass
go

调用：
exec my_proc1 90

 

自定义带参数默认值的存储过程
if exists (select * from sysobjects where name='my_proc1')
 drop proc my_proc1
go
create proc my_proc1
@pass int=60
as
declare @myavg int
select @myavg=avg(score1) from stuscore
print '平均分：'+convert(varchar(5),@myavg)
select * from stuscore where score1<@pass
go

调用：
exec my_proc1

 

输出参数的存储过程：
if exists (select * from sysobjects where name='my_proc1')
 drop proc my_proc1
go
create proc my_proc1
@passcount int output,
@pass int=60
as
declare @myavg int
select @myavg=avg(score1) from stuscore
print '平均分：'+convert(varchar(5),@myavg)
select * from stuscore where score1<@pass
select @passcount=count(*) from stuscore where score1>@pass
go

调用：
declare @passm int
exec my_proc1 @passm output,85
if(@passm>3)
 print '人数很多'
else
 print '人数很少'

创建输出参数错误处理的存储过程：
if exists (select * from sysobjects where name='my_proc1')
 drop proc my_proc1
go
create proc my_proc1
@passcount int output,
@pass int=60
as
if(@pass>100)
begin
 raiserror ('及格线错误，不能高于100分',16,1)
 return
end
declare @myavg int
select @myavg=avg(score1) from stuscore
print '平均分：'+convert(varchar(5),@myavg)
select * from stuscore where score1<@pass
select @passcount=count(*) from stuscore where score1>@pass
go

调用：
declare @passm int
declare @e int
exec my_proc1 @passm output,850
set @e=@@error
if(@e>0)
begin
 print '错误号：'+convert(varchar(5),@e)
 return
end
if(@passm>3)
 print '人数很多'
else
 print '人数很少'