单元07用T-SQL增加测试数据和事务控制ppt课件

1、单元单元7用用T-SQL添加测试数据添加测试数据和事务控制和事务控制回想回想 在班级表中删除在班级表中删除1号班级，报错如图，请问缘由是什么？号班级，报错如图，请问缘由是什么？创建外键约束的语法？创建外键约束的语法？请列出请列出ComboBox的常用属性的常用属性运用运用DataGridView进展挑选运用其什么属性？进展挑选运用其什么属性？如何运用如何运用DataRelation实现主从表的关联？实现主从表的关联？本单元义务本单元义务 义务一义务一 为学生表插入十万行数据为学生表插入十万行数据义务二义务二 完成完成“转专业模块转专业模块本单元目的本单元目的掌握如何定义变量并赋值掌握如何定义变

2、量并赋值掌握如何输出数据掌握如何输出数据掌握逻辑控制语句掌握逻辑控制语句了解批处置的概念了解批处置的概念了解事务的概念了解事务的概念掌握掌握ADO.NET中控制事务的方法中控制事务的方法义务一为学生表插入十万行数据义务一为学生表插入十万行数据 义务描画：义务描画：义务分析：义务分析：为了进展系统的压力测试，为学生表添加十万行数据为了进展系统的压力测试，为学生表添加十万行数据 。 1运用运用INSERT语句可为学生表添加一行数据。语句可为学生表添加一行数据。2运用循环控制可添加运用循环控制可添加n。3假设十万行数据都一样，那么意思不大。因此，要运用随机数，产生不假设十万行数据都一样，那么意思不大

3、。因此，要运用随机数，产生不同的数据。同的数据。4思索系统性能，应每思索系统性能，应每100行提交一次。行提交一次。运用变量运用变量 变量分为：变量分为：部分变量：部分变量：部分变量必需以标志作为前缀部分变量必需以标志作为前缀 ，如，如age部分变量的运用也是先声明，再赋值部分变量的运用也是先声明，再赋值 全局变量：全局变量：全局变量必需以标志全局变量必需以标志 作为前缀，如作为前缀，如version全局变量由系统定义和维护，我们只能读取，不能修正全全局变量由系统定义和维护，我们只能读取，不能修正全局变量的值局变量的值 部分变量部分变量 例如：例如：DECLARE name varchar(8

4、)DECLARE seat int声明部分变量声明部分变量 DECLARE 变量名变量名 数据类型数据类型赋值赋值 SET 变量名变量名 =值值 SELECT 变量名变量名 = 值值例如：例如：SET name=张三张三SELECT name=studentName FROM student WHERE studentNo=2019010101 必需确保挑选出的记录只需必需确保挑选出的记录只需1条条 或 部分变量例如部分变量例如分析：分析：1查找张亮同窗的班级号查找张亮同窗的班级号2查找班级号是查找班级号是1结果的学生信结果的学生信 【例【例7-1】查找与张亮同班的学生信息】查找与张亮同班的学

5、生信息-声明存放学生姓名的变量，最多可存贮声明存放学生姓名的变量，最多可存贮10个字符个字符DECLARE name varchar(10)-声明存放班级号声明存放班级号DECLARE classID intSet name = 张亮张亮Select classID = classID From Student Where StudentName = nameSelect * from Student Where ClassID = classID全局变量全局变量 全局变量都运用两个标志作为前缀全局变量都运用两个标志作为前缀 变量变量含义含义ERROR最后一个最后一个T-SQL错误的错误号错误

6、的错误号IDENTITY最后一次插入的标识值最后一次插入的标识值LANGUAGE当前使用的语言的名称当前使用的语言的名称MAX_CONNECTIONS可以创建的同时连接的最大数目可以创建的同时连接的最大数目ROWCOUNT受上一个受上一个SQL语句影响的行数语句影响的行数SERVERNAME本地服务器的名称本地服务器的名称TRANSCOUNT当前连接打开的事务数当前连接打开的事务数VERSIONSQL Server的版本信息的版本信息print SQL Server的版本的版本+VERSION print 效力器的称号效力器的称号: +SERVERNAME INSERT INTO studen

7、t(studentName,studentNo) VALUES(武松武松,s25328)-假设大于假设大于0表示上一条语句执行有错误表示上一条语句执行有错误print 当前错误号当前错误号+convert(varchar(5),ERROR) print 刚刚报名的学员，座位号为刚刚报名的学员，座位号为: +convert(varchar(5),IDENTITY )UPDATE student SET LoginPWD=aaa WHERE studentName=武松武松print 当前错误号当前错误号+convert(varchar(5),ERROR) GO输出语句输出语句 print演示：运

8、用全局变量演示：运用全局变量 参考语句参考语句SQL Server的版本的版本 效力器称号效力器称号 错误错误号号 逻辑控制语句逻辑控制语句IFELSE语句语句SQL中的中的IF-ELSE语句语句IF 条件条件 BEGIN 语句语句1 语句语句2 ENDELSE BEGIN 语句语句1； 语句语句2； ENDlELSE是可选部分是可选部分l假设有多条语句，才需求假设有多条语句，才需求BEGIN-END语句块语句块 IF-ELSE例如例如分析：分析：1统计平均统计平均成果存入暂时变成果存入暂时变量。量。2用用IFELSE判别。判别。 【例【例7-3】统计并显示课程号为】统计并显示课程号为1的期末

9、成果平均分，假设平均分的期末成果平均分，假设平均分在在80以上，显示以上，显示“成果优秀，并查询成果前成果优秀，并查询成果前3名学生的学号；假设名学生的学号；假设在在80以下，显示以下，显示“成果普通，并查询成果后成果普通，并查询成果后3名学生的学号名学生的学号 declare avg floatselect avg=avg(score1)from scorewhere subjectID=1if (avg = 80) begin print 成果优秀成果优秀 select top 3 studentno from score where subjectID=1 order by score1

10、 desc endelse begin print 成果普通成果普通 select top 3 studentno from score where subjectID=1 order by score1 end逻辑控制语句逻辑控制语句CASEEND多分支语句多分支语句CASE WHEN 条件条件1 THEN 结果结果1 WHEN 条件条件2 THEN 结果结果2 ELSE 其他结果其他结果ENDCASE 输入值输入值 WHEN 值值1 THEN 结果结果1 WHEN 值值2 THEN 结果结果2 ELSE 其他结果其他结果ENDCASE例如例如 【例【例7-4】学生的性别：】学生的性别：0为

11、男生，为男生，1为女生，要求查询每为女生，要求查询每位学生的学号和性别位学生的学号和性别 Select StudentNo, 性别性别 = CASE SEX WHEN 0 THEN 男生男生 WHEN 1 THEN 女生女生 ENDFROM Student逻辑控制语句逻辑控制语句WHILE循环语句循环语句SQL中的中的WHILE语句语句WHILE 条件条件 BEGIN 语句语句1 语句语句2 BREAK ENDBREAK表示退出循环表示退出循环假设有多条语句，才需求假设有多条语句，才需求BEGIN-END语句块语句块 WHILE例如例如 【例【例7-5】求】求10的阶乘的阶乘 DECLARE

12、Result integer, i integerSELECT Result=1, i=10WHILE i0 BEGIN SET Result=Result*i SET i=i-1 ENDPRINT Result 批处置语句批处置语句批处置是包含一个或多个批处置是包含一个或多个 SQL 语句的组，从运用程序一语句的组，从运用程序一次性地发送到次性地发送到SQL Server执行执行SQL Server 将批处置语句编译成一个可执行单元，此单将批处置语句编译成一个可执行单元，此单元称为执行方案。执行方案中的语句每次执行一条元称为执行方案。执行方案中的语句每次执行一条客户端运用程序客户端运用程序S

13、QL Server效力器效力器批处置语句：批处置语句：语句语句1语句语句2GO批处置语句例如批处置语句例如SELECT * FROM studentSELECT * FROM scoreUPDATE score SET score3=score1*0.6+score2*0.4 GOlGO是批处置的标志，表示是批处置的标志，表示SQL Server将这些将这些T-SQL语句编译为一个执行语句编译为一个执行单元，提高执行效率单元，提高执行效率l普通是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务普通是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决议需求和代

14、码编写者决议 批处置语句例如批处置语句例如-例如建表语句的末尾必需添加例如建表语句的末尾必需添加GOCREATE TABLE stuInfo (.) GOSQLServer规定：假设是建库、建表语句、以及我们后规定：假设是建库、建表语句、以及我们后面学习的存储过程和视图等，那么必需在语句末尾添加面学习的存储过程和视图等，那么必需在语句末尾添加 GO 批处置标志批处置标志随机函数随机函数 RAND ( )：随机函数，前往从：随机函数，前往从0 到到1之间的随机之间的随机float值。值。NEWID ( )：创建一个：创建一个uniqueidentifier类型的独一值类型的独一值select r

15、and()*100 产生产生0-1000-100内的随机数内的随机数select Left(convert(varchar(40),newid(),10) 产生产生1010位随机字符串位随机字符串 事务事务账户账户A账户账户B事务是一个任务单元，是一种要么胜利，要么失败的操作。事务是一个任务单元，是一种要么胜利，要么失败的操作。 例如，银行转账问题：例如，银行转账问题： 假定资金从账户假定资金从账户A转到账户转到账户B，至少需求两步：，至少需求两步：账户账户A的资金减少的资金减少然后账户然后账户B的资金相应添加的资金相应添加 假定张三的账户直接转账假定张三的账户直接转账1000元到李四的账户元

16、到李四的账户为什么需求事务为什么需求事务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) VALUES(李四李四,1)创建账户表，存

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

18、句我们能够会这样这样编写语句-张三的账户少张三的账户少1000元，李四的账户多元，李四的账户多1000元元UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三张三UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四李四GO-再次查看转账后的结果。再次查看转账后的结果。 SELECT * FROM bankGO请问：请问：执行转账语句后，张三、李四的账执行转账语句后，张三、李四的账户余额为多少？户余额为多少？张三的账户没有减少张三

19、的账户没有减少 但李四的账户却多了但李四的账户却多了1000元元 100010012019元元 总额多出了总额多出了1000元！元！为什么需求事务为什么需求事务-张三的账户减少张三的账户减少1000元，李四的账户添加元，李四的账户添加1000元元UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三张三UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四李四GO错误缘由分析：错误缘由分析：执行失败，所以张三还是执行失败，所以张三还

20、是1000元元继续往下执行：执行胜利，所以李四变为继续往下执行：执行胜利，所以李四变为1001元元如何处理呢？运用事务如何处理呢？运用事务UPDATE语句违反约束：语句违反约束：余额余额=1元元什么是事务什么是事务事务是作为逻辑任务单元执行的一系列操作。它包含了一组事务是作为逻辑任务单元执行的一系列操作。它包含了一组SQL语句，语句，但整组但整组SQL作为一个整体向系统提交或撤销。对于大型的数据库运用作为一个整体向系统提交或撤销。对于大型的数据库运用系统，如订票系统、银行系统等特别适宜。系统，如订票系统、银行系统等特别适宜。事务有四个属性，称为事务有四个属性，称为 ACID原子性、一致性、隔离

21、性和耐久性原子性、一致性、隔离性和耐久性属性，只需这样才干成为一个事务。属性，只需这样才干成为一个事务。转账过程就是一个事务。转账过程就是一个事务。它需求两条它需求两条UPDATE语句来完成，这两条语句是一个整体，假设其中任一条语句来完成，这两条语句是一个整体，假设其中任一条出现错误，那么整个转账业务也应取消，两个账户中的余额应恢复到原来的出现错误，那么整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即都是数据，从而确保转账前和转账后的余额不变，即都是1001元。元。创建事务创建事务T-SQL管理事务的语句有：管理事务的语句有：BEGIN TRANS

22、AVTION：开场事务。开场事务。COMMIT TRANSACTION：提交事务。提交事务。ROLLBACK TRANSACTION：回滚事务。回滚事务。事务的分类有：事务的分类有：显式事务：经过发出显式事务：经过发出BEGIN TRANSACTION语句显式启动事务。语句显式启动事务。自动提交事务：自动提交事务：SQL Server的默许方式。每个单独的的默许方式。每个单独的T-SQL语句作语句作为一个事务，胜利后提交，错误那么自动回滚。为一个事务，胜利后提交，错误那么自动回滚。隐性事务：经过设置隐性事务：经过设置SET IMPLICIT_TRANSACTIONS ON语句，将语句，将隐性事

23、务方式设置为翻开。在隐性事务操作时，提交或回滚事务后自隐性事务方式设置为翻开。在隐性事务操作时，提交或回滚事务后自动启动一个新事务。动启动一个新事务。处理银行转账问题处理银行转账问题BEGIN TRANSACTION DECLARE errorSum INT -用于累计事务执行过程中的错误用于累计事务执行过程中的错误-*/SET errorSum=0 -初始化为初始化为0，即无错误，即无错误/*-转帐：张三的账户少转帐：张三的账户少1000元，李四的账户多元，李四的账户多1000元元*/UPDATE bank SET currentMoney=currentMoney-1000 WHERE c

24、ustomerName=张三张三SET errorSum=errorSum+error -累计能否有错误累计能否有错误UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四李四SET errorSum=errorSum+error -累计能否有错误累计能否有错误/*-根据能否有错误，确定事务是提交还是撤销根据能否有错误，确定事务是提交还是撤销-*/IF errorSum0 -假设有错误假设有错误 BEGIN print 买卖失败，回滚事务买卖失败，回滚事务 ROLLBACK TRANSACTION END ELS

25、E BEGIN print 买卖胜利，提交事务，写入硬盘，永久的保管买卖胜利，提交事务，写入硬盘，永久的保管 COMMIT TRANSACTION END 【例【例7-8】运用显式事务来处置转账业务。】运用显式事务来处置转账业务。 义务虚施义务虚施1运用运用WHILE循环添加循环添加10万行数据万行数据 2运用随机函数运用随机函数 3实现隐性事务虚现实现隐性事务虚现100行提交一次行提交一次 代码分析代码分析关键代码关键代码 set nocount onSET IMPLICIT_TRANSACTIONS ON- 设置开启隐性事务declare i int, cnt int, d datetim

26、eselect d=getdate(), i=1, cnt=100000while (i=cnt)begin INSERT INTO tstuTest VALUES(i,Left(convert(varchar(40),newid(),10),i%2, convert(varchar(40), newid(), rand()*100, d-i%1000) set i = i + 1 if (i%100=0)- 每100行提交一次 commit tranEndSET IMPLICIT_TRANSACTIONS OFF- 设置封锁自动事务commit tran义务二实现义务二实现“学籍变动管理模块

27、学籍变动管理模块 义务描画：义务描画：义务分析：义务分析：大宝经理检测学籍变动模块功能，结果发现了大问题：明明有两位学生登记大宝经理检测学籍变动模块功能，结果发现了大问题：明明有两位学生登记了了“休学，可学生信息查询还是休学，可学生信息查询还是“在读。大宝经理责令张力修正。在读。大宝经理责令张力修正。 1需求登记手续办理情况：学号、学籍变动类型、何时办理、恳求理由、需求登记手续办理情况：学号、学籍变动类型、何时办理、恳求理由、审核指点等；假设是转专业，还需求登记转出专业、转入专业、转入方审审核指点等；假设是转专业，还需求登记转出专业、转入专业、转入方审核指点等。核指点等。2登记学籍变动信息后，

28、还需求修正该学生的形状，学籍变动呵斥的学生登记学籍变动信息后，还需求修正该学生的形状，学籍变动呵斥的学生形状变化如下图：形状变化如下图：休学 复学 退学 留级，转专业 退学 在读 休学 退学 毕业( 未就业) 毕业( 已就业) ADO.NET控制事务控制事务在在ADO.NET中，可以运用中，可以运用Connection和和Transaction对对象来控制事务。象来控制事务。执行事务：执行事务：调用调用Connection对象的对象的BeginTransaction开场事务。开场事务。将将Transaction对象分配给要执行的对象分配给要执行的Command的的Transaction属性。属

29、性。执行所需的命令。执行所需的命令。 调用调用Transaction对象的对象的Commit方法来完成事务，或调方法来完成事务，或调用用Rollback方法来取消事务。方法来取消事务。 ADO.NET控制事务范例控制事务范例【例【例7-9】实现银行转账业务。】实现银行转账业务。private void btnSave_Click(object sender, EventArgs e) DBHelper.connection.Open(); SqlTransaction myTrans = DBHelper.connection.BeginTransaction(); /为事务创建一个命令为事务

30、创建一个命令 SqlCommand myCommand = new SqlCommand(); myCommand.Connection = DBHelper.connection; myCommand.Transaction = myTrans; try myCommandmandText = string.Format(UPDATE bank SET currentMoney=currentMoney-0 WHERE customerName=1, txtMoney.Text, txtOldName.Text);myCommand.ExecuteNonQuery();myCommandma

31、ndText = string.Format( UPDATE bank SET currentMoney=currentMoney+0 WHERE customerName=1, txtMoney.Text, txtNewName.Text);myCommand.ExecuteNonQuery();myTransmit();/提交提交MessageBox.Show(转账胜利！转账胜利！); catch (Exception ex) myTrans.Rollback();/回滚回滚MessageBox.Show(转账失败！转账失败！); finally DBHelper.connection.C

32、lose(); 义务虚施义务虚施1设计界面设计界面 2创建视图创建视图v_student 3窗体翻开时获取学籍变动类别信息窗体翻开时获取学籍变动类别信息 4输入学号后显示该学生信息输入学号后显示该学生信息 5转专业，那么显示新专业的信息；否那么不用转专业，那么显示新专业的信息；否那么不用填写填写 6保管学籍变动信息，用事务控制保管学籍变动信息，用事务控制 代码分析代码分析保管学籍变动信息，用事务控制保管学籍变动信息，用事务控制 private void btnSave_Click(object sender, EventArgs e) string sqlIns, sqlUpdate = St

33、ring.Empty; /插入学籍变动表的插入学籍变动表的SQL和修正学生形状和修正学生形状SQL string agree = rdPass.Checked?1:0; /恳求能否经过恳求能否经过 string stuStaus = String.Empty; /学生形状变量学生形状变量 string type = cbxType.SelectedValue.ToString();/学籍变动类别编号学籍变动类别编号 /设置学生的目的形状标志设置学生的目的形状标志 switch (int.Parse(type) case 1:/休学休学 stuStaus = 2; break; case 2:/

34、复学复学 stuStaus = 1;break; case 3:/退学退学 stuStaus = 3;break; default:/留级、转专业留级、转专业 stuStaus = 1;break; if (type != 5)/ 非转专业，插入学籍变动表的非转专业，插入学籍变动表的SQL和修正学生形状和修正学生形状SQL sqlIns = String.Format(INSERT INTO StuStatusChange ( StudentNO,TypeID, ChangeDate,OldSpecID, Agree,OldHead,Reason,Memo) Values(0,1,2,3, 4

35、,5,6,7), txtStudentNo.Text, type, dtDate.Text, txtSpecOldID. Text, agree, txtHeadOld.Text, txtReason.Text, txtMemo.Text);sqlUpdate = String.Format(Update Student set StudentStateID=0 where studentno=1, stuStaus, txtStudentNo.Text); 代码分析代码分析保管学籍变动信息，用事务控制保管学籍变动信息，用事务控制 elsesqlIns = String.Format(INSERT I