SQL第04章 T-SQL编程与事务

《SQL第04章 T-SQL编程与事务》由会员分享，可在线阅读，更多相关《SQL第04章 T-SQL编程与事务（41页珍藏版）》请在装配图网上搜索。

1、T-SQL编程与事务编程与事务SQL数据库设计与实现数据库设计与实现第第4 4章章2回顾回顾下列T-SQL语句有错吗？实现了什么功能？SELECT*FROM stuInfo WHERE stuNo IN (SELECT*FROM stuMarks)上述语句可以用下面的连接替换吗？SELECT*FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo下列语句实现了什么功能？SELECT*FROM stuInfo WHERE stuNo NOT IN (SELECT stuNo FROM stuMarks)上述语句可以用下面的

2、连接替换吗？SELECT*FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNostuMarks.stuNo SELECT stuNo FROM stuMarks 可可 以以 查看没有参加考试的学员查看没有参加考试的学员 有的子查询不有的子查询不能用连接替换。能用连接替换。这就是典型的例子这就是典型的例子3回顾回顾下列语句实现了什么功能？SELECT*FROM stuInfo WHERE EXISTS(SELECT*FROM stuMarks WHERE stuNo=stuInfo.stuNo)请使用IN子查询改写上述语句？请使用连接改写上述语句 查

3、询参加考试的学员查询参加考试的学员SELECT*FROM stuInfo WHERE stuNo IN(SELECT stuNo FROM stuMarks)SELECT*FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo4目标目标 掌握如何定义变量并赋值 掌握如何输出显示数据 掌握IF、WHILE、CASE逻辑控制语句 理解SQL中批处理的概念 理解事务的概念 掌握如何创建事务5使用变量使用变量 和C语言一样，变量分为：局部变量：局部变量必须以标记作为前缀，如age局部变量的使用也是先声明，再赋值 全局变量：全局变

4、量必须以标记 作为前缀，如version全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值 6局部变量局部变量 例如：DECLARE name varchar(8)DECLARE seat int声明局部变量 DECLARE 变量名 数据类型赋值 SET 变量名=值 SELECT 变量名=值例如：SET name=张三SELECT name=stuName FROM stuInfo WHERE stuNo=s25302 必须确保筛选出的记录只有1条 或 7局部变量示例局部变量示例 2-1问题：问题：编写T-SQL查找李文才的左右同桌？学员信息表分析：分析：第一步，找出“李文才“的座位

5、号；第二步，李文才的座位号加1或减1 8局部变量示例局部变量示例 2-2/*-查找李文才的信息-*/DECLARE name varchar(8)-学员姓名SET name=李文才 -使用SET赋值SELECT*FROM stuInfo WHERE stuName=name/*-查找李文才的左右同桌-*/DECLARE seat int -座位号SELECT seat=stuSeat FROM stuInfo -使用SELECT赋值 WHERE stuName=nameSELECT*FROM stuInfo WHERE(stuSeat=seat+1)OR(stuSeat=seat-1)GO演示

6、：使用局部变量 参考语句9全局变量全局变量 全局变量都使用两个标志作为前缀 变量含义ERROR最后一个T-SQL错误的错误号IDENTITY最后一次插入的标识值LANGUAGE当前使用的语言的名称MAX_CONNECTIONS可以创建的同时连接的最大数目ROWCOUNT受上一个SQL语句影响的行数SERVERNAME本地服务器的名称TRANSCOUNT当前连接打开的事务数VERSIONSQL Server的版本信息10全局变量示例全局变量示例print SQL Server的版本+VERSION print 服务器的名称:+SERVERNAME INSERT INTO stuInfo(stuN

7、ame,stuNo,stuSex,stuAge)VALUES(武松,s25328,男,23)-如果大于0表示上一条语句执行有错误print 当前错误号+convert(varchar(5),ERROR)print 刚才报名的学员，座位号为:+convert(varchar(5),IDENTITY)UPDATE stuinfo SET stuAge=85 WHERE stuName=李文才print 当前错误号+convert(varchar(5),ERROR)GO演示：使用全局变量 参考语句SQL Server的版本 服务器名称 错误号 座位号（自动编号）错误号 11逻辑控制语句逻辑控制语句I

8、FELSE语句C语言中的if-else语句if（条件）语句1；语句2；else 语句1；语句2；SQL中的IF-ELSE语句IF（条件）BEGIN 语句1 语句2 ENDELSE BEGIN 语句1；语句2；END 同C语言一样：lELSE是可选部分l如果有多条语句，才需要BEGIN-END语句块 12IF-ELSE示例示例 2-1问题：问题：统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息。学员成绩表分析：分析：第一步，统计平均成绩存入临时变量；第二步，用IFELSE判断；13IF-

9、ELSE示例示例 2-214设置输出结果的格式设置输出结果的格式为了文本消息和输出结果显示在同一窗口，需要设置输出结果的格式15逻辑控制语句逻辑控制语句WHILE循环语句C语言中的while语句while（条件）语句1；语句2；break；SQL中的WHILE语句WHILE（条件）BEGIN 语句1 语句2 BREAK END 同C语言一样：lBREAK表示退出循环l如果有多条语句，才需要BEGIN-END语句块 16WHILE示例示例 2-1问题：问题：本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通

10、过，如此反复提分，直到所有人都通过为止。学员成绩表分析：分析：第一步，统计没通过的人数；第二步，如果有人没通过，加分；第三步，循环判断。17DECLARE n intWHILE(1=1)-条件永远成立 BEGIN SELECT n=COUNT(*)FROM stuMarks WHERE writtenExam0)UPDATE stuMarks -每人加2分 SET writtenExam=writtenExam+2 ELSE BREAK -退出循环 ENDprint 加分后的成绩如下：SELECT*FROM stuMarksWHILE示例示例 2-2参考语句18逻辑控制语句逻辑控制语句CASE

11、END多分支语句CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 其他结果END19CASE-END示例示例 2-1问题：问题：采用美国的ABCDE五级打分制来显示笔试成绩。A级:90分以上 B级：8089分 C级:7079分 D级：6069分 E级：60分以下学员成绩表20print ABCDE五级显示成绩如下：SELECT stuNo,成绩=CASE WHEN writtenExam60 THEN E WHEN writtenExam BETWEEN 60 AND 69 THEN D WHEN writtenExam BETWEEN 70 AND

12、79 THEN C WHEN writtenExam BETWEEN 80 AND 89 THEN B ElSE A END FROM stuMarksCASEEND示例示例 2-2参考语句21CASE-END课堂练习课堂练习课堂练习：课堂练习：请根据平均分和下面的评分规则，编写T-SQL语句查询学员的成 绩，如上图所示。优：90分以上 良：8089分 中：7079分 差：6069分 不及格：60分以下学员成绩分析22CASE-END练习答案练习答案USE stuDBGOSELECT 考号=ExamNo,学号=stuNo,笔试=writtenExam,机试=labExam,平均分=(writt

13、enExam+labExam)/2,等级=CASE WHEN(writtenExam+labExam)/260 THEN 不及格 WHEN(writtenExam+labExam)/2 BETWEEN 60 AND 69 THEN 差 WHEN(writtenExam+labExam)/2 BETWEEN 70 AND 79 THEN 中 WHEN(writtenExam+labExam)/2 BETWEEN 80 AND 89 THEN 良 ElSE 优 END FROM stuMarks23批处理语句批处理语句批处理是包含一个或多个 SQL 语句的组，从应用程序一次性地发送到SQL Ser

14、ver执行SQL Server 将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条客户端应用程序SQL Server服务器批处理语句：语句1语句2GO24批处理语句示例批处理语句示例SELECT*FROM stuInfoSELECT*FROM stuMarksUPDATE stuMarks SET writtenExam=writtenExam+2 GOlGO是批处理的标志，表示SQL Server将这些T-SQL语句编译为一个执行单元，提高执行效率l一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定 25批处理语句示例批处理语

15、句示例-例如建表语句的末尾必须添加GOCREATE TABLE stuInfo (.)GOSQLServer规定：如果是建库、建表语句、以及我们后面学习的存储过程和视图等，则必须在语句末尾添加 GO 批处理标志26课堂综合练习课堂综合练习课堂练习：则根据如下规则对机试成绩进行反复加分，直到平均分超过则根据如下规则对机试成绩进行反复加分，直到平均分超过85分为止。请编分为止。请编写写T-SQL语句实现。语句实现。90分以上：分以上：不加分不加分8089分：分：加加1分分7079分：分：加加2分分6069分：分：加加3分分60分以下：分以下：加加5分分加分前加分后27课堂综合练习答案课堂综合练习答

16、案SELECT*FROM stuMarks -原始成绩原始成绩DECLARE labAvg INTWHILE(1=1)BEGIN UPDATE stuMarks SET labExam=CASE WHEN labExam=85 BREAK ENDSELECT*FROM stuMarks -加分后的成绩加分后的成绩声明变量，用户临时存放平均分循环加分根据机试成绩酌情加分，整个是一个UPDATE语句获取目前的平均分，判断是否还继续加分28为什么需要事务为什么需要事务银行转账银行转账 例如，银行转账问题：假定资金从账户A转到账户B，至少需要两步：账户A的资金减少然后账户B的资金相应增加 账户账户A账

17、户账户B29假定张三的账户直接转账1000元到李四的账户为什么需要事务为什么需要事务CREATE TABLE bank(customerName CHAR(10),-顾客姓名顾客姓名 currentMoney MONEY -当前余额当前余额)GOALTER TABLE bank ADD CONSTRAINT CK_currentMoney CHECK(currentMoney=1)GOINSERT INTO bank(customerName,currentMoney)VALUES(张三张三,1000)INSERT INTO bank(customerName,currentMoney)VAL

18、UES(李四李四,1)创建账户表，存放用户的账户信息创建账户表，存放用户的账户信息添加约束：根据银行规定，账户添加约束：根据银行规定，账户余额不能少于余额不能少于1元，否则视为销元，否则视为销户户 张三开户，开户金额为张三开户，开户金额为1000元元；李四开户，开户金额；李四开户，开户金额1元元 30为什么需要事务为什么需要事务目前两个账户的余额总和为：1000+1=1001元 31为什么需要事务为什么需要事务模拟实现转账：从张三的账户转账1000元到李四的账户/*-转账测试：张三转账转账测试：张三转账1000元给李四元给李四-*/-我们可能会这样这样编写语句我们可能会这样这样编写语句-张三的

19、账户少张三的账户少1000元，李四的账户多元，李四的账户多1000元元UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三张三UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四李四GO-再次查看转账后的结果。再次查看转账后的结果。SELECT*FROM bankGO请问：请问：执行转账语句后，张三、李四的账户执行转账语句后，张三、李四的账户余额为多少？余额为多少？张三的账户没有减少张三的账户没有减少 但李四的账户却多了但李四的

20、账户却多了1000元元 100010012001元元 总额多出了总额多出了1000元！元！32为什么需要事务为什么需要事务-张三的账户减少张三的账户减少1000元，李四的账户增加元，李四的账户增加1000元元UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三张三UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四李四GO错误原因分析：UPDATE语句违反约束：语句违反约束：余额余额=1元元执行失败，所以张三还是执行失败，所以张

21、三还是1000元元继续往下执行：执行成功，所以李四变为继续往下执行：执行成功，所以李四变为1001元元如何解决呢？使用如何解决呢？使用事务事务33什么是事务什么是事务事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行 事务是一个不可分割的工作逻辑单元 转账过程就是一个事务。转账过程就是一个事务。它需要两条它需要两条UPDATE语句来完成，这两条语句是一个整体，如果其中任一条出语句来完成，这两条语句是一个整体，如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，现错误，则整个转账业务也应

22、取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即都是从而确保转账前和转账后的余额不变，即都是1001元。元。34事务的特性事务的特性事务必须具备以下四个属性，简称事务必须具备以下四个属性，简称ACID ACID 属性：属性：原子性（原子性（AtomicityAtomicity）：）：事务是一个完整的操作。事务的事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都各步操作是不可分的（原子的）；要么都执行，要么都不执行不执行 一致性（一致性（ConsistencyConsistency）：当事务完成时，数据必须处于当事务完成时，数据必须处于一致

23、状态一致状态 隔离性隔离性（IsolationIsolation）：对数据进行修改的所有并发事务对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务何方式依赖于或影响其他事务 永久性（永久性（DurabilityDurability）：）：事务完成后，它对数据库的修事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性改被永久保持，事务日志能够保持事务的永久性35如何创建事务如何创建事务T-SQL使用下列语句来管理事务：开始事务：BEGIN TRANSACTION提交事务：COMMI

24、T TRANSACTION回滚（撤销）事务：ROLLBACK TRANSACTION 一旦事务提交或回滚，则事务结束。一旦事务提交或回滚，则事务结束。判断某条语句执行是否出错：判断某条语句执行是否出错：使用全局变量ERROR；ERROR只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计；如：SET errorSum=errorSum+error36如何创建事务如何创建事务了解事务的分类：显示事务：用BEGIN TRANSACTION明确指定事务的开始，这是最常用的事务类型隐性事务：通过设置SET IMPLICIT_TRANSACTION

25、S ON 语句，将隐性事务模式设置为打开，下一个语句自动启动一个新事务。当该事务完成时，再下一个 T-SQL 语句又将启动一个新事务自动提交事务：这是 SQL Server 的默认模式，它将每条单独的 T-SQL 语句视为一个事务，如果成功执行，则自动提交；如果错误，则自动回滚37关键语句讲解关键语句讲解BEGIN TRANSACTION/*-定义变量，用于累计事务执行过程中的错误定义变量，用于累计事务执行过程中的错误-*/DECLARE errorSum INT SET errorSum=0 -初始化为初始化为0，即无错误，即无错误/*-转账：张三的账户少转账：张三的账户少1000元，李四的

26、账户多元，李四的账户多1000元元*/UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三张三SET errorSum=errorSum+errorUPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四李四SET errorSum=errorSum+error -累计是否有错误累计是否有错误如何创建事务如何创建事务使用事务解决银行转账问题开始事务（指定事务从此处开始，后续开始事务（指定事务从此处开始，后续的的T-SQL语句都是一个

27、整体）语句都是一个整体）累计是否有错误累计是否有错误38IF errorSum0 -如果有错误如果有错误 BEGIN print 交易失败，回滚事务交易失败，回滚事务 ROLLBACK TRANSACTION END ELSE BEGIN print 交易成功，提交事务，写入硬盘，永久的保存交易成功，提交事务，写入硬盘，永久的保存 COMMIT TRANSACTION ENDGOprint 查看转账事务后的余额查看转账事务后的余额SELECT*FROM bank GO 如何创建事务如何创建事务根据是否有错误，确定根据是否有错误，确定事务是提交还是撤销事务是提交还是撤销如果有错，则回滚操作，事务

28、结束如果有错，则回滚操作，事务结束如果成功，则提交操作，事务结束如果成功，则提交操作，事务结束39如何创建事务如何创建事务转账事务前转账事务前转账事务过程中转账事务过程中转账事务结束后转账事务结束后演示：转账1000，转账失败的情况40如何创建事务如何创建事务转账事务前转账事务前转账事务过程中转账事务过程中转账事务结束后转账事务结束后演示：转账800，转账成功的情况41总结总结变量的赋值有两种方式：使用SET语句或SELECT语句。输出结果也有两种方式：print语句和SELECT语句。控制流语句提供了条件操作所需的顺序和逻辑。语句块使用BEGINEND。批处理可以提高语句执行的效率，批处理结束的标志是“GO”。数据库事务具有如下特性：原子性、一致性、隔离性、持久性事务可以分为如下类型：显式事务、隐性事务、自动提交事务Transact-SQL使用下列语句来管理事务：BEGIN TRANSACTIONCOMMIT TRANSACTIONROLLBACK TRANSACTION