SQL SERVER设计与高级查询 第五章 学习笔记

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 整理