《SQLServer数据库应用与维护》-第15讲 T-SQL语言及应用5

上节内容回顾6.3函数6.3.1聚合函数6.3.2日期和时间函数6.3.3字符串函数6.3.4数学函数6.3.5数据类型转换函数6.3.5用户自定义函数…….上节内容提问1、T-SQL中介绍了哪些常见函数，各举例1-2个？2、聚合函数包括哪些？3、日期时间函数包括哪些？4、最常见的数据类型转换函数是？5、用户自定义函数有哪两种常见的，语法格式是？

第六章T-SQL语言基础及应用第六章T-SQL语言基础及应用目的与要求：掌握T-SQL的语法基础，包括变量、运算符、函数、流程控制语句和注解等语言元素。掌握数据库的并法控制：事务、锁。重点与难点：常量与变量（重点）运算符与表达式（重点）程序流程控制语句（重难点）函数（难点）事务、锁（难点）第15讲T-SQL语言基础及应用56.4高级操作6.4.1事务6.4.2锁第6章复习6.4高级操作6.4高级操作事务和锁是灵活应用T-SQL语句的关键，在程序设计和开发中起着重要的作用。数据库是可供多个用户共享的信息资源。当多个用户并发地存取数据库时，就会产生多个事务同时存取同一数据的情况。数据库的并发控制就是控制数据库，防止多用户并发使用数据库时造成数据错误和程序运行错误，保证数据的完整性。事务是多用户系统的一个数据操作基本单元。封锁是使事务对它要操作的数据有一定的控制能力。6.4高级操作高级操作事务锁事务基本概念事务操作并发问题SQLServer中的锁死锁6.4.1事务6.4.1事务用华仔无间道中的一句来诠释：去不了终点，回到原点。举例说明：在一个事务中，你写了2条sql语句，一条是修改订单表状态，一条是修改库存表库存-1。如果在修改订单表状态的时候出错，事务能够回滚，数据将恢复到没修改之前的数据状态，下面的修改库存也就不执行，这样确保你关系逻辑的一致，安全。。事务就是这个样子，倔脾气，要么全部执行，要么全部不执行，回到原数据状态。书面解释：事务具有原子性，一致性，隔离性，持久性。故事导入：6.4.1事务事务是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。事务是程序的逻辑运行单位事务必须保持数据库的一致性在事务执行过程中,数据库可能处于不一致状态当事务提交时,数据库必须处于一致状态一、事务的定义6.4.1事务二、事务的ACID性质Atomicity。原子性，事务的操作要么完全执行,要么全不做。Consistency。一致性，事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。当数据库中只包含成功事务提交的结果时，就称数据库处于一致性状态。Isolation。隔离性，一个事务内部各操作的执行不会被其他事务干扰。Durability。持久性，事务成功结束后,对数据库所做的更新将永久化,即使再发生系统故障也不受影响。6.4.1事务例1：转账。从账户A转移$50到账户B:1. read(A)2. A:=A–503. write(A)4. read(B)5. B:=B+506. write(B)一致性要求：事务执行前后A与B之和保持不变。原子性要求：若事务在第3步之后及第6步之前失败,系统应确保事务所做更新不被反映到数据库中,否则会出现不一致。持久性要求：一旦用户被告知事务已经完成(即,$50转账已经发生),则即使发生故障,事务对数据库的更新也必须持久化。隔离性要求：若在第3步与第6步之间允许另一事务存取部分更新的数据库,该事务将看到不一致的数据库(A+B小于正确值)。6.4.1事务三、事务控制语句在SQL中，定义事务的语句有三条：BEGINTRANSACTION;事务开始COMMIT;提交，事务正常结束ROLLBACK;回滚，事务被撤销，事务中已完成的操作全部撤销，回滚到事务开始时的状态。6.4.1事务（1）事务控制语句BEGINTRANSACTION[tran_name]：标识一个用户定义的事务的开始。tran_name为事务的名字，标识一个事务开始。COMMITTRANSACTION[tran_name]：表示提交事务中的一切操作，结束一个用户定义的事务，使得对数据库的改变生效。ROLLBACKTRANSACTION[tran_name|save_name]：回退一个事务到事务的开头或一个保存点。表示要撤销该事务已做的操作，回滚到事务开始前或保存点前的状态。SAVETRANSACTIONsave_name：在事务中设置一个保存点，名字为save_name，它可以使一个事务内的部分操作回退。其中，TRANSACTION可简写为TRAN。6.4.1事务（2）两个可用于事务管理的全局变量两个可用于事务管理的全局变量是@@error和@@rowcount。@@error：给出最近一次执行的出错语句引发的错误号，@@error为0表示未出错。@@rowcount：给出受事务中已执行语句所影响的数据行数。6.4.1事务事务控制语句的使用BEGIN

TRAN

A组语句序列

SAVE

TRAN

save_point

B组语句序列

IF

@@error<>0

ROLLBACK

TRAN

save_point

/*仅回退B组语句序列*/COMMIT

TRAN

/*提交A组语句，且若未回退B组语句，则提交B组语句*/6.4.1事务例2：使用事务向表物料表中插入数据。USE

POGOBEGIN

TRAN

AAA

INSERT

INTO

物料(物料代码,物料名称,物料类别)VALUES('1005','鼠标','2')SAVE

TRAN

BBB

INSERT

INTO

物料(物料代码,物料名称,物料类别)VALUES('1006','键盘','2')GOINSERT

INTO

物料(物料代码,物料名称,物料类别)VALUES('3003','沙发','3')GOIF

@@ERROR<>0ROLLBACK

TRAN

BBB

GOCOMMIT

TRAN

AAA

GO6.4.1事务例3：使用事务向表物料表中插入数据。USE

POGOBEGIN

TRAN

INSERT

INTO

物料(物料代码,物料名称,物料类别)VALUES('1005','鼠标','2')INSERT

INTO

物料(物料代码,物料名称,物料类别)VALUES('1006','键盘','2')INSERT

INTO

物料(物料代码,物料名称,物料类别)VALUES('3003','沙发','3')IF

@@ERROR<>0ROLLBACK

TRAN

GOCOMMIT

TRAN

GO6.4.1事务例3：使用事务向表物料表中插入数据。USE

POGOBEGIN

TRAN

AAA

INSERT

INTO

物料(物料代码,物料名称,物料类别)VALUES('1007',鼠标,2)SAVE

TRAN

BBB

INSERT

INTO

物料(物料代码,物料名称,物料类别)VALUES('1008','键盘','2')GOINSERT

INTO

物料(物料代码,物料名称,物料类别)VALUES('3004','沙发','3')GOIF

@@ERROR<>0ROLLBACK

TRAN

BBB

GOCOMMIT

TRAN

AAA

GO6.4.1事务（3）事务中不可使用的语句相关操作语句创建数据库CREATEDATABASE修改数据库ALTERDATABASE删除数据库DROPDATABASE备份数据库DUMPDATABASE、BACKUPDATABASE还原数据库LOADDATABASE、RESTOREDATABASE日志备份DUMPTRANSACTION、BACKUPLOG日志还原LOADTRANSACTION、RESTORELOG配置LOADTRANSACTION、RESTORELOG磁盘初始化DISKINIT统计UPDATESTATISTICS显示或设置数据库选项SP_DBOPTION在事务中不能使用以下T-SQL语句，也就是如果使用了这些语句，也不能够进行事务的回滚。6.4.1事务（4）事务回滚机制如果服务器错误使事务无法成功完成，Server将自动回滚该事务，并释放该事务占用的所有资源。如果客户端与Server的网络连接中断了，则网络会通知Server并回滚该连接的所有未完成事务。如果客户端应用程序失败或客户计算机崩溃或重启，也会中断该连接，同Server回滚该连接的所有未完成事务。如果客户从应用程序注销，所有未完成的事务也会被回滚。如果批处理中出现运行时语句错误，那么Server中默认的行为将是只回滚产生该错误的语句。6.4高级操作高级操作事务锁事务基本概念事务操作并发问题SQLServer中的锁死锁6.4.2锁6.4.2锁1.从何而来？在多用户都用事务同时访问同一个数据资源的情况下，就会造成以下几种数据错误。更新丢失：多个用户同时对一个数据资源进行更新，必定会产生被覆盖的数据，造成数据读写异常。不可重复读：如果一个用户在一个事务中多次读取一条数据，而另外一个用户则同时更新了这条数据，造成第一个用户多次读取数据不一致。脏读：第一个事务读取第二个事务正在更新的数据表，如果第二个事务还没有更新完成，那么第一个事务读取的数据将是一半为更新过的，一半还没更新过的数据，这样的数据毫无意义。幻读：第一个事务读取一个结果集后，第二个事务，对这个结果集经行增删操作，然而第一个事务中再次对这个结果集进行查询时，数据发现丢失或新增。然而锁定，就是为解决这些问题所生的，他的存在使得一个事务对他自己的数据块进行操作的时候，而另外一个事务则不能插足这些数据块。这就是所谓的锁定。6.4.2锁2.3种常用的锁模式（1）共享锁ShareLock（S锁）还可以叫他读锁。可以并发读取数据，但不能修改数据。也就是说当数据资源上存在共享锁的时候，所有的事务都不能对这个资源进行修改，直到数据读取完成，共享锁释放。（2）排他锁ExclusiveLock（X锁）还可以叫他独占锁、写锁。就是如果你对数据资源进行增删改操作时，不允许其它任何事务操作这块资源，直到排它锁被释放，防止同时对同一资源进行多重操作。（3）更新锁UpdateLock（U锁）防止出现死锁的锁模式，两个事务对一个数据资源进行先读取在修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁则可以避免死锁的出现。资源的更新锁一次只能分配给一个事务，如果需要对资源进行修改，更新锁会变成排他锁，否则变为共享锁。6.4.2锁3.什么是死锁？什么是死锁，为什么会产生死锁。我们用“事务把死锁给整出来啦”标题下的两个事务产生的死锁来解释应该会更加生动形象点。例子是这样的：第一个事务（称为A）：先