SQL SERVER设计与高级查询
第五章 事务、索引和视图
【事务】
事务是作为单个逻辑工作单元执行的一系列操作.一个逻辑工作单元必须有4个属性:
原子性(Atomicity):事务是一个完整的操作.
一致性(Consistency):事务完成时,数据必须保持一致状态.
隔离性(Isolation):对数据进行修改的所有并发事务是彼隔离.
持久性(Durability):事务完成对系统的影响是永远性的.
--管理事务语句
BEGIN TRANSACTION --开始事务
COMMIT TRANSACTION --提交事务
ROLLBACK TRANSACTION --撤销事务
/*-事务的分类-*/
--显式事务:使用BEGIN TRANSACTION 明确指定事务的开始
--隐式事务:通常设置SET IMPLICIT_TRANSACTION ON 语句,将隐式事务模式设置打开,SQL SEVER将在提交或滚回事务后自动刷新事务
--自动提交事务:默认模式,每条SQL语句都视为一个独立的事务
/*事务实例--模拟银行转帐操作*/
USE BANK
SET NOCOUNT ON --不显示受影响的行数
PRINT '查看转帐事务前的余额'
SELECT * FROM ACCOUNT
GO
--事务开始,后面的SQL语句是一个整体
BEGIN TRANSACTION
--定义一个变量,用于累计事务执行过程中的错误数
DECLARE @errorSum int
SET @errorSum = 0 --初始化为,即没有错误
--转帐,张三帐户-1000,李四帐户+1000
UPDATE ACCOUNT SET currentMoney= currentMoney-1000 where costomerName = '张三'
SET @errorSum = @errorSum + @@error --累计是否有错误
UPDATE ACCOUNT SET currentMoney= currentMoney+1000 where costomerName = '李四'
SET @errorSum = @errorSum + @@error
--判断是否有错误,确定事务是提交还是撤回
PRINT '查看事务过程中余额情况'
SELECT * FROM ACCOUNT
IF @errorSum <> 0
BEGIN
PRINT '交易失败,事务滚回'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT '交易成功,事务提交'
COMMIT TRANSACTION
END
GO
PRINT '查看转帐事务后的余额'
SELECT * FROM ACCOUNT
GO
【问题】
SQL中错误分为两种:语法错、运行时错误
GO批处理语句,在遇到语法错误时,GO以上的语句不会执行.遇到运行时错误时,只会影响到错误语句本身,其他语句会被执行.
【索引】
--唯一索引(unique):创建唯一约束自动创建唯一索引,为了达到高性能,建议使用主键索引
--主键索引:定义一个主键自动建立主键索引
--聚集/非聚集索引(clustered/nonclustered):聚集索引表中各行的物理顺序和键值的逻辑顺序相同,
--创建主键就创建了聚集索引
--可以建立多个非聚集索引,但只能建立一个聚集索引
/*-格式-*/
CREATE indexType INDEX indexName --indexType:唯一索引/聚集/非聚集索引unique/clustered/nonclustered
ON TableName(columnName1,columnName2,.......)
WITH FILLFACTOR = X --FILLFACTOR 表示填充因子,0-100之间,该值指示索引页填满空间所占的百分比
/*-创建索引实例-*/
--笔试成绩经常被查询,为加快查询速度建立索引,因为笔试成绩会重复,所以建立非聚集索引
use student
GO
--检查是否存在该索引,索引存放在系统表sysindexes中
IF EXISTS(SELECT * FROM SYSINDEXES WHERE NAME = 'IX_stuMarks_WrittenExam')
DROP INDEX IX_stuMarks_WrittenExam
--笔试列创建非聚集索引:填充因子为%
CREATE NONCLUSTERED INDEX IX_stuMarks_WrittenExam
ON Student(WrittenExam) WITH FILLFACTOR = 30
GO
【问题】
既然索引可以提高SQL效率,是否索引越多越好呢?否!
建立索引的条件:
该列用于频繁搜索,该列用于队数据进行排序
不能建立索引的条件:
列中包含几个不同值,表中只包含几行数据
【视图】
什么是视图:视图是另一种查看数据库中一个或多个表中的数据方法.视图是一种虚拟表(更新视图,源表数据也会被更改).
--视图通常进行的以下三种操作
--筛选表中的行
--防止未经许可的用户访问敏感数据
--将多个物理数据表抽象为一个逻辑数据表
/*-格式-*/
CREATE VIEW viewName AS SELECT 语句
/*-创建视图实例-*/
USE student
GO
--检测是否存在,视图存放在系统表sysobjects中
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
(如果有不对的或需要补充的地方,还请老师和同学们帮忙指出来,谢谢!)
2010/3/21 整理