SQL server学习（四）T-SQL编程之事务、索引和视图

megnlingling

　　今天来分享下T-SQL高级编程中的事务、索引、视图，可以和之前的SQL server系列文章结合起来。

一、事务
　　事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行，事务是一个不可分割的工作逻辑单元
　　这样说可能太笼统，看个实例就会清晰很多，比如银行转账业务，相信大家都办理过。
　　

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)
　　

　　
--模拟银行转账：
　　

　　

　　
UPDATE bank SET currentMoney=currentMoney-1000
　　WHERE customerName='张三'
　　
UPDATE bank SET currentMoney=currentMoney+1000
　　WHERE customerName='李四'
　　
GO
　　

　　
SELECT * FROM bank
　　
GO
　　

　　这样写的SQL代码，实际结果会出现，张三的钱没少，而李四的钱多了1000，是因为添加的约束条件，money要>=1，才导致这样结果。
　　转账那个人的钱没少，而另一方却收到了钱，这样的话，银行岂不亏死，所以，这样的代码明显是不合理的。根据事务概念，是要么执行成功，要是失败，只有这两种结果，而且是同步的。
　　代码改进为如下：
　　

使用事务解决：　　

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  
　　

　　
IF @errorSum<>0  
　　BEGIN
　　print '交易失败，回滚事务'
　　ROLLBACK TRANSACTION
　　END
　　
ELSE
　　BEGIN
　　print '交易成功，提交事务，写入硬盘，永久的保存'
　　COMMIT TRANSACTION  
　　END
　　
GO
　　
print '查看转账事务后的余额'
　　
SELECT * FROM bank
　　
GO
　　

　　添加事务后，就会保证数据的正确性了，到这，我想大家已经明白事务的好处了。

事务的四大属性，简称ACID属性
　　原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行

　　一致性（Consistency）：当事务完成时，数据必须处于一致状态

　　隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务

　　永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性

创建事务
　　

开始事务：BEGIN TRANSACTION　　
提交事务：
COMMIT TRANSACTION　　
回滚（撤销）事务：
ROLLBACK TRANSACTION　　

　　一旦事务提交或回滚，则事务结束。

判断某条语句执行是否出错：@@ERROR的返回值为0表示没错

使用全局变量@@ERROR；

@@ERROR只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计；

            如： SET @errorSum=@errorSum+@@error

事务分类：

显示事务:用BEGIN TRANSACTION明确指定事务的开始，这是最常用的事务类型

隐性事务：通过设置SET IMPLICIT_TRANSACTIONS ON 语句，将隐性事务模式设置为打开，下一个语句自动启动一个新事务。当该事务完成时，再下一个 T-SQL 语句又将启动一个新事务

　　自动提交事务：这是 SQL Server 的默认模式，它将每条单独的 T-SQL 语句视为一个事务，如果成功执行，则自动提交；如果错误，则自动回滚

二、索引
　　索引根据索引键查找定位数据行
　　索引：是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据 。
　　索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页。
　　索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。

索引类型

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

　　主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空

　　聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个，比如：拼音

　　非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个，比如：偏旁

创建索引
　　

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]　　
INDEX   index_name
　　
ON table_name (column_name…)
　　
[WITH FILLFACTOR=x]
　　

　　其中：

UNIQUE表示唯一索引，可选

CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，可选

　　FILLFACTOR表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比
　　创建索引举例：
　　

USE stuDB　　

GO　　
IF EXISTS (SELECT name FROM sysindexes
　　WHERE name = 'IX_writtenExam')
　　DROP INDEX stuMarks.IX_writtenExam  
　　

　　
CREATE NONCLUSTERED INDEX IX_writtenExam
　　ON stuMarks(writtenExam)
　　WITH FILLFACTOR= 30
　　
GO
　　

　　
SELECT * FROM stuMarks   with(INDEX=IX_writtenExam)
　　WHERE writtenExam BETWEEN 60 AND 90
　　


创建索引的指导原则
　　请按照下列标准选择建立索引的列。
　　该列用于频繁搜索
　　该列用于对数据进行排序
　　请不要使用下面的列创建索引：
　　列中仅包含几个不同的值。
　　表中仅包含几行。为小型表创建索引可能不太划算，因为SQL Server在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长

三、视图

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

视图的用途：

　　筛选表中的行

　　防止未经许可的用户访问敏感数据

　　降低数据库的复杂程度

　　将多个物理数据库抽象为一个逻辑数据库

SQL Server中允许用户创建视图，在同一原始数据表的基础上，为不同的用户选择不同的列，从而达到不同用户的需求。

创建视图语法：
　　

CREATE VIEW view_nameAS　　<select语句>
　　

　　举例：
　　

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'view_stuInfo_stuMarks') 检查视图是否存在　　

DROP VIEW view_stuInfo_stuMarks 删除视图　　

GO　　
CREATE VIEW view_stuInfo_stuMarks 创建视图
　　AS
　　SELECT 姓名=stuName,学号=stuInfo.stuNo,笔试成绩 =writtenExam,机试成绩=labExam,平均分=(writtenExam+labExam)/2
　　FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
　　
GO
　　
SELECT * FROM view_stuInfo_stuMarks 使用视图
　　

　　

本文仅代表作者观点，系作者@温一壶清酒发表。　　欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
　　文章出处：http://www.cnblogs.com/hong-fithing/