单元单元07用用T-SQL增加测试数增加测试数据和事务控制据和事务控制回顾回顾在班级表中删除在班级表中删除1号班级，报错如图，请问原因是什么？号班级，报错如图，请问原因是什么？创建外键约束的语法？创建外键约束的语法？请列出请列出ComboBox的常用属性的常用属性使用使用DataGridView进行筛选使用其什么属性？进行筛选使用其什么属性？如何使用如何使用DataRelation实现主从表的关联？实现主从表的关联？2全局变量全局变量全局变量都使用两个全局变量都使用两个标志作为前缀标志作为前缀变量变量含义含义ERROR最后一个最后一个T-SQL错误的错误号错误的错误号IDENTITY最后一次插入的标识值最后一次插入的标识值LANGUAGE当前使用的语言的名称当前使用的语言的名称MAX_CONNECTIONS可以创建的同时连接的最大数目可以创建的同时连接的最大数目ROWCOUNT受上一个受上一个SQL语句影响的行数语句影响的行数SERVERNAME本地服务器的名称本地服务器的名称TRANSCOUNT当前连接打开的事务数当前连接打开的事务数VERSIONSQLServer的版本信息的版本信息9printSQLServer的版本的版本+VERSIONprint服务器的名称服务器的名称:+SERVERNAMEINSERTINTOstudent(studentName,studentNo)VALUES(武松武松,s25328)-如果大于如果大于0表示上一条语句执行有错误表示上一条语句执行有错误print当前错误号当前错误号+convert(varchar(5),ERROR)print刚才报名的学员，座位号为刚才报名的学员，座位号为:+convert(varchar(5),IDENTITY)UPDATEstudentSETLoginPWD=aaaWHEREstudentName=武松武松print当前错误号当前错误号+convert(varchar(5),ERROR)GO输出语句输出语句print演示：使用全局变量演示：使用全局变量参考语句参考语句SQLServer的版本的版本服务器名称服务器名称错误号错误号10逻辑控制语句逻辑控制语句IFELSE语句语句SQL中的中的IF-ELSE语句语句IF（条件）（条件）BEGIN语句语句1语句语句2ENDELSEBEGIN语句语句1；语句语句2；ENDlELSE是可选部分是可选部分l如果有多条语句，才需要如果有多条语句，才需要BEGIN-END语句块语句块11IF-ELSE示例示例分析：分析：（1）统计平均成绩存）统计平均成绩存入临时变量。入临时变量。（2）用）用IFELSE判判断。断。【例【例7-3】统计并显示课程号为】统计并显示课程号为1的期末成绩平均分，如果平均分在的期末成绩平均分，如果平均分在80以上，显示以上，显示“成绩优秀成绩优秀”，并查询成绩前，并查询成绩前3名学生的学号；如果在名学生的学号；如果在80以下，显示以下，显示“成绩一般成绩一般”，并查询成绩后，并查询成绩后3名学生的学号名学生的学号declareavgfloatselectavg=avg(score1)fromscorewheresubjectID=1if(avg=80)beginprint成绩优秀成绩优秀selecttop3studentnofromscorewheresubjectID=1orderbyscore1descendelsebeginprint成绩一般成绩一般selecttop3studentnofromscorewheresubjectID=1orderbyscore1end12逻辑控制语句逻辑控制语句CASEEND多分支语句多分支语句CASEWHEN条件条件1THEN结果结果1WHEN条件条件2THEN结果结果2ELSE其他结果其他结果ENDCASE输入值输入值WHEN值值1THEN结果结果1WHEN值值2THEN结果结果2ELSE其他结果其他结果END13CASE示例示例【例【例7-4】学生的性别：】学生的性别：0为男生，为男生，1为女生，要求查询每为女生，要求查询每位学生的学号和性别位学生的学号和性别SelectStudentNo,性别性别=CASESEXWHEN0THEN男生男生WHEN1THEN女生女生ENDFROMStudent14逻辑控制语句逻辑控制语句WHILE循环语句循环语句SQL中的中的WHILE语句语句WHILE（条件）（条件）BEGIN语句语句1语句语句2BREAKENDBREAK表示退出循环表示退出循环如果有多条语句，才需要如果有多条语句，才需要BEGIN-END语句块语句块15WHILE示例示例【例【例7-5】求】求10的阶乘的阶乘DECLAREResultinteger,iintegerSELECTResult=1,i=10WHILEi0BEGINSETResult=Result*iSETi=i-1ENDPRINTResult 16批处理语句批处理语句批处理是包含一个或多个批处理是包含一个或多个SQL语句的组，从应用程序一语句的组，从应用程序一次性地发送到次性地发送到SQLServer执行执行SQLServer将批处理语句编译成一个可执行单元，此单将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条元称为执行计划。执行计划中的语句每次执行一条客户端应用程序客户端应用程序SQLServer服务器服务器批处理语句：批处理语句：语句语句1语句语句2GO17批处理语句示例批处理语句示例SELECT*FROMstudentSELECT*FROMscoreUPDATEscoreSETscore3=score1*0.6+score2*0.4GOlGO是批处理的标志，表示是批处理的标志，表示SQLServer将这些将这些T-SQL语句编译为一个执行语句编译为一个执行单元，提高执行效率单元，提高执行效率l一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定需求和代码编写者决定18批处理语句示例批处理语句示例-例如建表语句的末尾必须添加例如建表语句的末尾必须添加GOCREATETABLEstuInfo(.)GOSQLServer规定：如果是建库、建表语句、以及我们后面规定：如果是建库、建表语句、以及我们后面学习的存储过程和视图等，则必须在语句末尾添加学习的存储过程和视图等，则必须在语句末尾添加GO批批处理标志处理标志19随机函数随机函数RAND()：随机函数，返回从：随机函数，返回从0到到1之间的随机之间的随机float值。值。NEWID()：创建一个：创建一个uniqueidentifier类型的唯一值类型的唯一值selectrand()*100 产生产生0-1000-100内的随机数内的随机数selectLeft(convert(varchar(40),newid(),10) 产生产生1010位随机字符串位随机字符串 20事务事务银行转账银行转账账户账户A账户账户B事务是一个工作单元，是一种要么成功，要么失败的操作。事务是一个工作单元，是一种要么成功，要么失败的操作。例如，银行转账问题：例如，银行转账问题：假定资金从账户假定资金从账户A转到账户转到账户B，至少需要两步：，至少需要两步：账户账户A的资金减少的资金减少然后账户然后账户B的资金相应增加的资金相应增加21假定张三的账户直接转账假定张三的账户直接转账1000元到李四的账户元到李四的账户为什么需要事务为什么需要事务CREATETABLEbank(customerNameCHAR(10),-顾客姓名顾客姓名currentMoneyMONEY-当前余额当前余额)GOALTERTABLEbankADDCONSTRAINTCK_currentMoneyCHECK(currentMoney=1)GOINSERTINTObank(customerName,currentMoney)VALUES(张三张三,1000)INSERTINTObank(customerName,currentMoney)VALUES(李四李四,1)创建账户表，存放用户的账户信息创建账户表，存放用户的账户信息添加约束：根据银行规定，账户添加约束：根据银行规定，账户余额不能少于余额不能少于1元，否则视为销户元，否则视为销户张三开户，开户金额为张三开户，开户金额为1000元元；李四开户，开户金额；李四开户，开户金额1元元22为什么需要事务为什么需要事务目前两个账户的余额总和为：目前两个账户的余额总和为：1000+1=1001元元23为什么需要事务为什么需要事务模拟实现转账模拟实现转账： 从张三的账户转账从张三的账户转账1000元到李四的账户元到李四的账户/*-转账测试：张三转账转账测试：张三转账1000元给李四元给李四-*/-我们可能会这样这样编写语句我们可能会这样这样编写语句-张三的账户少张三的账户少1000元，李四的账户多元，李四的账户多1000元元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=张三张三UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName=李四李四GO-再次查看转账后的结果。再次查看转账后的结果。SELECT*FROMbankGO请问：请问：执行转账语句后，张三、李四的账户执行转账语句后，张三、李四的账户余额为多少？余额为多少？张三的账户没有减少张三的账户没有减少但李四的账户却多了但李四的账户却多了1000元元100010012001元元总额多出了总额多出了1000元！元！24为什么需要事务为什么需要事务-张三的账户减少张三的账户减少1000元，李四的账户增加元，李四的账户增加1000元元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=张三张三UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName=李四李四GO错误原因分析：错误原因分析：执行失败，所以张三还是执行失败，所以张三还是1000元元继续往下执行：执行成功，所以李四变为继续往下执行：执行成功，所以李四变为1001元元如何解决呢？使用如何解决呢？使用事务事务UPDATE语句违反约束：语句违反约束：余额余额=1元元25什么是事务什么是事务事务是作为逻辑工作单元执行的一系列操作。它包含了一组事务是作为逻辑工作单元执行的一系列操作。它包含了一组SQL语句，语句，但整组但整组SQL作为一个整体向系统提交或撤销。对于大型的数据库应用作为一个整体向系统提交或撤销。对于大型的数据库应用系统，如订票系统、银行系统等特别适合。系统，如订票系统、银行系统等特别适合。事务有四个属性，称为事务有四个属性，称为ACID（原子性、一致性、隔离性和持久性）属性，（原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务。只有这样才能成为一个事务。转账过程就是一个事务。转账过程就是一个事务。它需要两条它需要两条UPDATE语句来完成，这两条语句是一个整体，如果其中任一条出语句来完成，这两条语句是一个整体，如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即都是从而确保转账前和转账后的余额不变，即都是1001元。元。26创建事务创建事务T-SQL管理事务的语句有：管理事务的语句有：BEGINTRANSAVTION：开始事务。开始事务。COMMITTRANSACTION：提交事务。提交事务。ROLLBACKTRANSACTION：回滚事务。回滚事务。事务的分类有：事务的分类有：显式事务：通过发出显式事务：通过发出BEGINTRANSACTION语句显式启动事务。语句显式启动事务。自动提交事务：自动提交事务：SQLServer的默认模式。每个单独的的默认模式。每个单独的T-SQL语句作为一语句作为一个事务，成功后提交，错误则自动回滚。个事务，成功后提交，错误则自动回滚。隐性事务：通过设置隐性事务：通过设置SETIMPLICIT_TRANSACTIONSON语句，将隐性语句，将隐性事务模式设置为打开。在隐性事务操作时，提交或回滚事务后自动启动事务模式设置为打开。在隐性事务操作时，提交或回滚事务后自动启动一个新事务。一个新事务。27解决银行转账问题解决银行转账问题BEGINTRANSACTIONDECLAREerrorSumINT-用于累计事务执行过程中的错误用于累计事务执行过程中的错误-*/SETerrorSum=0-初始化为初始化为0，即无错误，即无错误/*-转帐：张三的账户少转帐：张三的账户少1000元，李四的账户多元，李四的账户多1000元元*/UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=张三张三SETerrorSum=errorSum+error-累计是否有错误累计是否有错误UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName=李四李四SETerrorSum=errorSum+error-累计是否有错误累计是否有错误/*-根据是否有错误，确定事务是提交还是撤销根据是否有错误，确定事务是提交还是撤销-*/IFerrorSum0-如果有错误如果有错误BEGINprint交易失败，回滚事务交易失败，回滚事务ROLLBACKTRANSACTIONENDELSEBEGINprint交易成功，提交事务，写入硬盘，永久的保存交易成功，提交事务，写入硬盘，永久的保存COMMITTRANSACTIONEND【例【例7-8】使用显式事务来处理转账业务。】使用显式事务来处理转账业务。28任务实施任务实施1使用使用WHILE循环添加循环添加10万行数据万行数据2使用随机函数使用随机函数3实现隐性事务实现实现隐性事务实现100行提交一次行提交一次29代码分析代码分析关键代码关键代码 setnocountonSETIMPLICIT_TRANSACTIONSON-设置开启隐性事务设置开启隐性事务declareiint,cntint,ddatetimeselectd=getdate(),i=1,cnt=100000while(i=cnt)beginINSERTINTOtstuTestVALUES(i,Left(convert(varchar(40),newid(),10),i%2,convert(varchar(40),newid(),rand()*100,d-i%1000)seti=i+1if(i%100=0)-每每100行提交一次行提交一次committranEndSETIMPLICIT_TRANSACTIONSOFF-设置关闭自动事务设置关闭自动事务committran30任务二实现任务二实现“学籍变动管理学籍变动管理”模块模块任务描述：任务描述：任务分析：任务分析：大宝经理检测学籍变动模块功能，结果发现了大问题：明明有两位学生登记大宝经理检测学籍变动模块功能，结果发现了大问题：明明有两位学生登记了了“休学休学”，可学生信息查询还是，可学生信息查询还是“在读在读”。大宝经理责令张力修改。大宝经理责令张力修改。 1需要登记手续办理情况：学号、学籍变动类型、何时办理、申请理由、审需要登记手续办理情况：学号、学籍变动类型、何时办理、申请理由、审核领导等；如果是转专业，还需要登记转出专业、转入专业、转入方审核核领导等；如果是转专业，还需要登记转出专业、转入专业、转入方审核领导等。领导等。2登记学籍变动信息后，还需要修改该学生的状态，学籍变动造成的学生状登记学籍变动信息后，还需要修改该学生的状态，学籍变动造成的学生状态变化如图所示：态变化如图所示：31ADO.NET控制事务控制事务在在ADO.NET中，可以使用中，可以使用Connection和和Transaction对对象来控制事务。象来控制事务。执行事务：执行事务：调用调用Connection对象的对象的BeginTransaction开始事务。开始事务。将将Transaction对象分配给要执行的对象分配给要执行的Command的的Transaction属属性。性。执行所需的命令。执行所需的命令。调用调用Transaction对象的对象的Commit方法来完成事务，或调用方法来完成事务，或调用Rollback方法来取消事务。方法来取消事务。32ADO.NET控制事务范例控制事务范例【例【例7-9】实现银行转账业务。】实现银行转账业务。privatevoidbtnSave_Click(objectsender,EventArgse)DBHelper.connection.Open();SqlTransactionmyTrans=DBHelper.connection.BeginTransaction();/为事务创建一个命令为事务创建一个命令SqlCommandmyCommand=newSqlCommand();myCommand.Connection=DBHelper.connection;myCommand.Transaction=myTrans;trymyCommand.CommandText=string.Format(UPDATEbankSETcurrentMoney=currentMoney-0WHEREcustomerName=1,txtMoney.Text,txtOldName.Text);myCommand.ExecuteNonQuery();myCommand.CommandText=string.Format(UPDATEbankSETcurrentMoney=currentMoney+0WHEREcustomerName=1,txtMoney.Text,txtNewName.Text);myCommand.ExecuteNonQuery();myTrans.Commit();/提交提交MessageBox.Show(转账成功！转账成功！);catch(Exceptionex)myTrans.Rollback();/回滚回滚MessageBox.Show(转账失败！转账失败！);finallyDBHelper.connection.Close();33任务实施任务实施1设计界面设计界面2创建视图创建视图v_student3窗体打开时获取学籍变动类别信息窗体打开时获取学籍变动类别信息4输入学号后显示该学生信息输入学号后显示该学生信息5转专业，则显示新专业的信息；否则不用填写转专业，则显示新专业的信息；否则不用填写6保存学籍变动信息，用事务控制保存学籍变动信息，用事务控制34代码分析代码分析保存学籍变动信息，用事务控制保存学籍变动信息，用事务控制privatevoidbtnSave_Click(objectsender,EventArgse)stringsqlIns,sqlUpdate=String.Empty;/插入学籍变动表的插入学籍变动表的SQL和修改学生状态和修改学生状态SQLstringagree=rdPass.Checked?1:0; /申请是否通过申请是否通过stringstuStaus=String.Empty;/学生状态变量学生状态变量stringtype=cbxType.SelectedValue.ToString();/学籍变动类别编号学籍变动类别编号/设置学生的目标状态标志设置学生的目标状态标志switch(int.Parse(type)case1:/休学休学stuStaus=2;break;case2:/复学复学stuStaus=1;break;case3:/退学退学stuStaus=3;break;default:/留级、转专业留级、转专业stuStaus=1;break;if(type!=5)/非转专业，插入学籍变动表的非转专业，插入学籍变动表的SQL和修改学生状态和修改学生状态SQLsqlIns=String.Format(INSERTINTOStuStatusChange(StudentNO,TypeID,ChangeDate,OldSpecID,Agree,OldHead,Reason,Memo)Values(0,1,2,3,4,5,6,7),txtStudentNo.Text,type,dtDate.Text,txtSpecOldID.Text,agree,txtHeadOld.Text,txtReason.Text,txtMemo.Text);sqlUpdate=String.Format(UpdateStudentsetStudentStateID=0wherestudentno=1,stuStaus,txtStudentNo.Text);35代码分析代码分析保存学籍变动信息，用事务控制保存学籍变动信息，用事务控制 elsesqlIns=String.Format(INSERTINTOStuStatusChange(StudentNO,TypeID,ChangeDate,OldSpecID,Agree,OldHead,Reason,Memo,NewSpecID,NewHead)Values(0,1,2,3,4,5,6,7,8,9),txtStudentNo.Text,type,dtDate.Text,txtSpecOldID.Text,agree,txtHeadOld.Text,txtReason.Text,txtMemo.Text,cbxSpecNew.SelectedValue,txtHeadNew.Text);DBHelper.connection.Open();SqlTransactionmyTrans=DBHelper.connection.BeginTransaction();/为事务创建一个命令为事务创建一个命令SqlCommandcomm=newSqlCommand(sqlIns,DBHelper.connection);comm.Transaction=myTrans;trycomm.ExecuteNonQuery();if(int.Parse(type)4) comm.CommandText=sqlUpdate;comm.ExecuteNonQuery();myTrans.Commit();/提交提交MessageBox.Show(学籍变动成功！学籍变动成功！);catch(Exceptionex)myTrans.Rollback();/回滚回滚MessageBox.Show(学籍变动失败！学籍变动失败！);finallyDBHelper.connection.Close();36总结总结SQLServer有全局变量和局部变量，变量名称分别以有全局变量和局部变量，变量名称分别以“”和和“”开头。开头。局部变量赋值有两种方式：局部变量赋值有两种方式：SELECT和和SET可以使用可以使用PRINT语句在消息区输出显示数据语句在消息区输出显示数据使用使用IFELSE语句作为条件判断语句语句作为条件判断语句使用使用CASEEND语句作为多分支语句，它有两种使用语语句作为多分支语句，它有两种使用语法，并可放到法，并可放到SELECT中。中。使用使用WHILE语句可使一组语句重复多次语句可使一组语句重复多次GO语句是批处理结束的标志语句是批处理结束的标志事务有事务有4大特性，分别使用大特性，分别使用BEGINTRAN、COMMITTRAN和和ROLLBACKTRAN来显示控制事务来显示控制事务在在ADO.NET中，可以使用中，可以使用Connection和和Transaction对象来控制事务对象来控制事务37作业作业38谢谢！谢谢！39结束语结束语谢谢大家聆听！谢谢大家聆听！40