《SQL编程和事务》PPT课件.ppt

2,回顾,下列T-SQL语句有错吗？实现了什么功能？SELECT*FROMstuInfoWHEREstuNoIN(SELECT*FROMstuMarks)上述语句可以用下面的连接替换吗？SELECT*FROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNo下列语句实现了什么功能？SELECT*FROMstuInfoWHEREstuNoNOTIN(SELECTstuNoFROMstuMarks)上述语句可以用下面的连接替换吗？SELECT*FROMstuInfoINNERJOINstuMarksONstuInfo.stuNostuMarks.stuNo,SELECTstuNoFROMstuMarks,可以,查看没有参加考试的学员,有的子查询不能用连接替换。这就是典型的例子,3,回顾,下列语句实现了什么功能？SELECT*FROMstuInfoWHEREEXISTS(SELECT*FROMstuMarksWHEREstuNo=stuInfo.stuNo)请使用IN子查询改写上述语句？请使用连接改写上述语句,查询参加考试的学员,SELECT*FROMstuInfoWHEREstuNoIN(SELECTstuNoFROMstuMarks),SELECT*FROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNo,4,目标,掌握如何定义变量并赋值掌握如何输出显示数据掌握IF、WHILE、CASE逻辑控制语句理解SQL中批处理的概念理解事务的概念掌握如何创建事务,5,使用变量,和C语言一样，变量分为：局部变量：局部变量必须以标记作为前缀，如age局部变量的使用也是先声明，再赋值全局变量：全局变量必须以标记作为前缀，如version全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值,6,局部变量,例如：DECLAREnamevarchar(8)DECLAREseatint,声明局部变量DECLARE变量名数据类型,赋值SET变量名=值SELECT变量名=值,例如：SETname=张三SELECTname=stuNameFROMstuInfoWHEREstuNo=s25302,必须确保筛选出的记录只有1条,或,7,局部变量示例2-1,问题：编写T-SQL查找李文才的左右同桌？,学员信息表,分析：第一步，找出“李文才“的座位号；第二步，李文才的座位号加1或减1,8,局部变量示例2-2,/*-查找李文才的信息-*/DECLAREnamevarchar(8)-学员姓名SETname=李文才-使用SET赋值SELECT*FROMstuInfoWHEREstuName=name/*-查找李文才的左右同桌-*/DECLAREseatint-座位号SELECTseat=stuSeatFROMstuInfo-使用SELECT赋值WHEREstuName=nameSELECT*FROMstuInfoWHERE(stuSeat=seat+1)OR(stuSeat=seat-1)GO,演示：使用局部变量,参考语句,9,全局变量,全局变量都使用两个标志作为前缀,10,全局变量示例,printSQLServer的版本+VERSIONprint服务器的名称:+SERVERNAMEINSERTINTOstuInfo(stuName,stuNo,stuSex,stuAge)VALUES(武松,s25328,男,23)-如果大于0表示上一条语句执行有错误print当前错误号+convert(varchar(5),ERROR)print刚才报名的学员，座位号为:+convert(varchar(5),IDENTITY)UPDATEstuinfoSETstuAge=85WHEREstuName=李文才print当前错误号+convert(varchar(5),ERROR)GO,演示：使用全局变量,SQLServer的版本,服务器名称,错误号,座位号（自动编号）,错误号,11,逻辑控制语句,IFELSE语句,C语言中的if-else语句if（条件）语句1；语句2；else语句1；语句2；,SQL中的IF-ELSE语句IF（条件）BEGIN语句1语句2ENDELSEBEGIN语句1；语句2；END,同C语言一样：ELSE是可选部分如果有多条语句，才需要BEGIN-END语句块,12,IF-ELSE示例2-1,问题：统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息。,学员成绩表,分析：第一步，统计平均成绩存入临时变量；第二步，用IFELSE判断；,13,IF-ELSE示例2-2,14,设置输出结果的格式,为了文本消息和输出结果显示在同一窗口，需要设置输出结果的格式,15,逻辑控制语句,WHILE循环语句,C语言中的while语句while（条件）语句1；语句2；break；,SQL中的WHILE语句WHILE（条件）BEGIN语句1语句2BREAKEND,同C语言一样：BREAK表示退出循环如果有多条语句，才需要BEGIN-END语句块,16,WHILE示例2-1,问题：本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止。,学员成绩表,分析：第一步，统计没通过的人数；第二步，如果有人没通过，加分；第三步，循环判断。,17,DECLAREnintWHILE(1=1)-条件永远成立BEGINSELECTn=COUNT(*)FROMstuMarksWHEREwrittenExam0)UPDATEstuMarks-每人加2分SETwrittenExam=writtenExam+2ELSEBREAK-退出循环ENDprint加分后的成绩如下：SELECT*FROMstuMarks,WHILE示例2-2,参考语句,18,逻辑控制语句,CASEEND多分支语句,CASEWHEN条件1THEN结果1WHEN条件2THEN结果2ELSE其他结果END,19,CASE-END示例2-1,问题：采用美国的ABCDE五级打分制来显示笔试成绩。A级:90分以上B级：8089分C级:7079分D级：6069分E级：60分以下,学员成绩表,20,printABCDE五级显示成绩如下：SELECTstuNo,成绩=CASEWHENwrittenExam=1元,执行失败，所以张三还是1000元,继续往下执行：执行成功，所以李四变为1001元,如何解决呢？使用事务,33,什么是事务,事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行事务是一个不可分割的工作逻辑单元,转账过程就是一个事务。它需要两条UPDATE语句来完成，这两条语句是一个整体，如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变,34,事务的特性,事务必须具备以下四个属性，简称ACID属性：原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行一致性（Consistency）：当事务完成时，数据必须处于一致状态隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性,35,如何创建事务,T-SQL使用下列语句来管理事务：开始事务：BEGINTRANSACTION提交事务：COMMITTRANSACTION回滚（撤销）事务：ROLLBACKTRANSACTION一旦事务提交或回滚，则事务结束。判断某条语句执行是否出错：使用全局变量ERROR；ERROR只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计；如：SETerrorSum=errorSum+error,36,如何创建事务,了解事务的分类：显示事务：用BEGINTRANSACTION明确指定事务的开始，这是最常用的事务类型隐性事务：通过设置SETIMPLICIT_TRANSACTIONSON语句，将隐性事务模式设置为打开，下一个语句自动启动一个新事务。当该事务完成时，再下一个T-SQL语句又将启动一个新事务自动提交事务：这是SQLServer的默认模式，它将每条单独的T-SQL语句视为一个事务，如果成功执行，则自动提交；如果错误，则自动回滚,37,关键语句讲解BEGINTRANSACTION/*-定义变量，用于累计事务执行过程中的错误-*/DECLAREerrorSumINTSETerrorSum=0-初始化为0，即无错误/*-转账：张三的账户少1000元，李四的账户多1000元*/UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=张三SETerrorSum=errorSum+errorUPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName=李四SETerrorSum=errorSum+error-累计是否有错误,如何创建事务,使用事务解决银行转账问题,开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体）,累计是否有错误,38,IFerrorSum0-如果有错误BEGINprint交易失败，回滚事务ROLLBACKTRANSACTIONENDELSEBEGINprint交易成功，提交事务，写入硬盘，永久的保存COMMITTRANSACTIONENDGOprint查看转账事务后的余额SELECT*FROMbankGO,如何创建事务,根据是否有错误，确定事务是提交还是撤销,如果有错，则回滚操作，事务结束,如果成功，则提交操作，事务结束,39,如何创建事务,转账事务前,转账事务过程中,转账事务结束后,演示：转账1000，转账失败的情况,40,如何创建事务,转账事务前,转账事务过程中,转账事务结束后,演示：转账800，转账成功的情况,41,总结,变量的赋值有两种方式：使用SET语句或S