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

单元7用T-SQL增加测试数据 和事务控制1回顾 • 在班级表中删除1号班级，报错如图，请问原因是什么 ？ • 创建外键约束的语法？ • 请列出ComboBox的常用属性 • 使用DataGridView进行筛选使用其什么属性？ • 如何使用DataRelation实现主从表的关联？2本单元任务• 任务一 为学生表插入十万行数据 • 任务二 完成“转专业”模块新增管理员新增教师新增学生3本单元目标• 掌握如何定义变量并赋值 • 掌握如何输出数据 • 掌握逻辑控制语句 • 理解批处理的概念 • 理解事务的概念 • 掌握ADO.NET中控制事务的方法4任务一为学生表插入十万行数 据 任务描述：任务分析：为了进行系统的压力测试，为学生表添加十万行数据 1．使用INSERT语句可为学生表添加一行数据 2．使用循环控制可添加n 3．如果十万行数据都一样，则意思不大因此，要使用随机数，产生不同的 数据 4．考虑系统性能，应每100行提交一次5使用变量 变量分为： • 局部变量：– 局部变量必须以标记@作为前缀 ，如@age– 局部变量的使用也是先声明，再赋值 • 全局变量： – 全局变量必须以标记@ @作为前缀，如@@version – 全局变量由系统定义和维护，我们只能读取，不能修改全局变量 的值 6局部变量 例如： DECLARE @name varchar(8) DECLARE @seat int•声明局部变量DECLARE @变量名 数据类型•赋值 SET @变量名 =值 SELECT @变量名 = 值例如： SET @name=‘张三’ SELECT @name=studentName FROM student WHERE studentNo=‘2003010101’ 必须确保筛选出的记录只有1条 或 7局部变量示例分析： （1）查找张亮同学的班级号 （2）查找班级号是（1）结果的学生信 • 【例7-1】查找与张亮同班的学生信息--声明存放学生姓名的变量，最多可存贮10个字符 DECLARE @name varchar(10) --声明存放班级号 DECLARE @classID int Set @name = '张亮' Select @classID = classID From Student Where StudentName = @name Select * from Student Where ClassID = @classID8全局变量 • 全局变量都使用两个@标志作为前缀变量含义@@ERROR最后一个T-SQL错误的错误号@@IDENTITY最后一次插入的标识值@@LANGUAGE当前使用的语言的名称@@MAX_CONNECTIONS可以创建的同时连接的最大数目@@ROWCOUNT受上一个SQL语句影响的行数@@SERVERNAME本地服务器的名称@@TRANSCOUNT当前连接打开的事务数@@VERSIONSQL Server的版本信息9输出语句 printprint 'SQL Server的版本'+@@VERSION print '服务器的名称: '+@@SERVERNAME INSERT INTO stuInfo(stuName,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的版本 服务器名称 座位号（自动编号） 错误号 错误号 10逻辑控制语句• IF－ELSE语句SQL中的IF-ELSE语句 IF （条件）BEGIN语句1语句2……END ELSEBEGIN语句1；语句2；……ENDlELSE是可选部分l如果有多条语句，才需要BEGIN-END语句块 11IF-ELSE示例分析： （1）统计平均成绩存 入临时变量。

（2）用IF－ELSE判 断• 【【例7-3】统计并显示课程号为1的期末成绩平均分，如果平均分 在80以上，显示“成绩优秀”，并查询成绩前3名学生的学号；如果 在80以下，显示“成绩一般”，并查询成绩后3名学生的学号 declare @avg float select @avg=avg(score1) from score where subjectID=1 if (@avg >= 80)beginprint '成绩优秀'select top 3 studentno from score where subjectID=1 order by score1 descend elsebeginprint '成绩一般'select top 3 studentno from score where subjectID=1 order by score1end12逻辑控制语句• CASE－END多分支语句1CASE 输入值WHEN 值1 THEN 结果1WHEN 值2 THEN 结果2……ELSE 其他结果END13CASE示例1• 【例7-4】学生的性别：0为男生，1为女生，要求查询 每位学生的学号和性别 Select StudentNo, 性别 = CASE SEXWHEN 0 THEN '男生'WHEN 1 THEN '女生'END FROM Student14逻辑控制语句• CASE－END多分支语句2CASE WHEN 条件1 THEN 结果1WHEN 条件2 THEN 结果2……ELSE 其他结果END15CASE示例2问题：采用美国的ABCDE五级打分制来显示总评成绩。

A级: 90分以上B级： 80－89分C级: 70－79分D级： 60－69分E级： 60分以下Select StudentNo, 分数 = CASE WHEN score1=60 and score1=70 and score1=80 and score10BEGINSET @Result=@Result*@iSET @i=@i-1END PRINT @Result 18WHILE示 例2问题： 本次考试成绩较差，假定要提分，确保每人笔试都通过提分规则很简 单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分 ，再看是否都通过，如此反复提分，直到所有人都通过为止 学员成绩表分析： 第一步，统计没通过的人数 ； 第二步，如果有人没通过，加分； 第三步，循环判断 19DECLARE @n int WHILE(1=1) --条件永远成立BEGINSELECT @n=COUNT(*) FROM stuMarks WHERE writtenExam0)UPDATE stuMarks --每人加2分SET writtenExam=writtenExam+2 ELSEBREAK --退出循环END print '加分后的成绩如下：' SELECT * FROM stuMarksWHILE示例• 参考语句20批处理语句• 批处理是包含一个或多个 SQL 语句的组，从应用程序一 次性地发送到SQL Server执行 • SQL Server 将批处理语句编译成一个可执行单元，此单 元称为执行计划。

执行计划中的语句每次执行一条客户端应用程序SQL Server服务器批处理语句： 语句1 语句2 …… GO21批处理语句 示例SELECT * FROM student SELECT * FROM score UPDATE score SET score3=score1*0.6+score2*0.4 GOlGO是批处理的标志，表示SQL Server将这些T-SQL语句编译为一个执行 单元，提高执行效率l一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务 需求和代码编写者决定 22批处理语句示例--例如建表语句的末尾必须添加GO CREATE TABLE stuInfo ( .... ) GO• SQLServer规定：如果是建库、建表语句、以及我们后 面学习的存储过程和视图等，则必须在语句末尾添加 GO 批处理标志23随机函数 • RAND ( )：随机函数，返回从0 到1之间的随机float值 • NEWID ( )：创建一个uniqueidentifier类型的唯一值select rand()*100 产生0-100内的随机数select Left(convert(varchar(40),newid()),10) 产生10位随机字符串 24事务银行转账 账户A账户B• 事务是一个工作单元，是一种要么成功，要么失败的操 作。

• 例如，银行转账问题：假定资金从账户A转到账户B，至少需要两步： – 账户A的资金减少 – 然后账户B的资金相应增加 25• 假定张三的账户直接转账1000元到李四的账户为什么需要事务CREATE TABLE bank (customerName CHAR(10), --顾客姓名currentMoney MONEY --当前余额 ) GO ALTER TABLE bankADD CONSTRAINT CK_currentMoney CHECK(currentMoney>=1) GO INSERT INTO bank(customerName,currentMoney)VALUES('张三',1000) INSERT INTO bank(customerName,currentMoney)VALUES('李四',1)创建账户表，存放用户的账户信息添加约束：根据银行规定，账户 余额不能少于1元，否则视为销户 张三开户，开户金额为1000元 ；李四开户，开户金额1元 26为什么需要事务• 目前两个账户的余额总和为：1000+1=1001元 27为什么需要事务• 模拟实现转账 ：从张三的账户转账1000元到李四的账户/*--转账测试：张三转账1000元给李四--*/ --我们可能会这样这样编写语句 --张三的账户少1000元，李四的账户多1000元 UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='张三' UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName='李四' GO --再次查看转账后的结果。

SELECT * FROM bank GO请问： 执行转账语句后，张三、李四的账户 余额为多少？张三的账户没有减少 但李四的账户却多了1000元 1000＋1001＝2001元总额多出了1000元！28为什么需要事务--张三的账户减少1000元，李四的账户增加1000元 UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='张三' UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName='李四' GO• 错误原因分析：执行失败，所以张三还是1000元继续往下执行：执行成功，所以李四变为1001元如何解决呢？使用事务UPDATE语句违反约束： 余额>=1元29什么是事务•事务是作为逻辑工作单元执行的一系列操作它包含了一组SQL语 句，但整组SQL作为一个整体向系统提交或撤销对于大型的数据 库应用。