1、第十章,T-SQL编程,目标,掌握如何定义变量并赋值掌握如何输出显示数据掌握IF、WHILE、CASE逻辑控制语句理解SQL中批处理的概念,使用变量,变量分为： 局部变量： 局部变量必须以标记作为前缀 ，如age 局部变量的使用也是先声明，再赋值 全局变量： 全局变量必须以标记 作为前缀，如version 全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值,局部变量,例如： DECLARE name varchar(8) DECLARE seat int,声明局部变量DECLARE 变量名 数据类型,赋值 SET 变量名 =值 SELECT 变量名 = 值,例如： SET name=
2、张三 SELECT name=stuName FROM stuInfo WHERE stuNo=s25302,必须确保筛选出的记录只有1条,或,局部变量示例,问题： 编写T-SQL查找李文才的左右同桌 ？,学员信息表,分析： 第一步，找出“李文才“的座位号； 第二步，李文才的座位号加1或减1,局部变量示例,/*-查找李文才的信息-*/ DECLARE name varchar(8) -学员姓名 SET name=李文才 -使用SET赋值 SELECT * FROM stuInfo WHERE stuName = name /*-查找李文才的左右同桌-*/ DECLARE seat int -座
3、位号 SELECT seat=stuSeat FROM stuInfo -使用SELECT赋值WHERE stuName=name SELECT * FROM stuInfo WHERE (stuSeat = seat+1) OR (stuSeat = seat-1) GO,演示：使用局部变量,参考语句,全局变量,全局变量都使用两个标志作为前缀,全局变量示例,print SQL Server的版本+VERSION print 服务器的名称: +SERVERNAME INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES(武松,s25328
4、,男,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的版本,服务器名称,座位号（自动编号）,错误号,错误号,逻辑控制语句,T-SQL语言提供了一些可用于改变语句执行顺序的命令,称为流程控制语句.流程
5、控制语句与常见的程序设计语言类似,主要包括以下几种:,beginend ifelse case while . continue.break goto,逻辑控制语句,IFELSE语句,SQL中的IF-ELSE语句 IF （条件）BEGIN语句1语句2END ELSEBEGIN语句1；语句2；END,ELSE是可选部分 如果有多条语句，才需要BEGIN-END语句块,IF-ELSE示例,问题： 统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息 。,学员成绩表,分析： 第一步，统计平均成绩
6、存入临时变量； 第二步，用IFELSE判断；,IF-ELSE示例,设置输出结果的格式,为了文本消息和输出结果显示在同一窗口，需要设置输出结果的格式,逻辑控制语句,WHILE循环语句,SQL中的WHILE语句 WHILE （条件）BEGIN语句1语句2BREAKEND,BREAK表示退出循环 如果有多条语句，才需要BEGIN-END语句块,WHILE示例,问题： 本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止 。,学员成绩表,分析： 第一步，统计没通过的人数 ； 第
7、二步，如果有人没通过，加分； 第三步，循环判断。,DECLARE n int WHILE(1=1) -条件永远成立BEGINSELECT n=COUNT(*) FROM stuMarks WHERE writtenExam0)UPDATE stuMarks -每人加2分SET writtenExam=writtenExam+2 ELSEBREAK -退出循环END print 加分后的成绩如下： SELECT * FROM stuMarks,WHILE示例,参考语句,逻辑控制语句,CASEEND多分支语句,CASE WHEN 条件1 THEN 结果1WHEN 条件2 THEN 结果2ELSE
8、其他结果 END,CASE-END示例,问题： 采用美国的ABCDE五级打分制来显示笔试成绩。A级: 90分以上B级： 8089分C级: 7079分D级： 6069分E级： 60分以下,学员成绩表,print ABCDE五级显示成绩如下： SELECT stuNo,成绩= CASE WHEN writtenExam60 THEN EWHEN writtenExam BETWEEN 60 AND 69 THEN DWHEN writtenExam BETWEEN 70 AND 79 THEN CWHEN writtenExam BETWEEN 80 AND 89 THEN BElSE AENDF
9、ROM stuMarks,CASEEND示例,参考语句,CASE-END课堂练习,课堂练习： 请根据平均分和下面的评分规则，编写T-SQL语句查询学员的成 绩，如上图所示。优 ：90分以上良 ：8089分中 ：7079分差 ：6069分不及格 ：60分以下,学员成绩分析,CASE-END练习答案,USE stuDB GO SELECT 考号=ExamNo,学号=stuNo,笔试=writtenExam,机试=labExam,平均分=(writtenExam+labExam)/2,等级=CASE WHEN (writtenExam+labExam)/260 THEN 不及格WHEN (writt
10、enExam+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 优ENDFROM stuMarks,批处理语句,批处理是包含一个或多个 SQL 语句的组，从应用程序一次性地发送到SQL Server执行 SQL Server 将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条,客户端应用程序,SQL Server服务器,批处
11、理语句： 语句1 语句2 GO,批处理语句示例,SELECT * FROM stuInfo SELECT * FROM stuMarks UPDATE stuMarks SET writtenExam=writtenExam+2 GO,GO是批处理的标志，表示SQL Server将这些T-SQL语句编译为一个执行单元，提高执行效率 一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定,批处理语句示例,-例如建表语句的末尾必须添加GO CREATE TABLE stuInfo () GO,SQLServer规定：如果是建库、建表语句、以及我们后面学习的存储过程和视
12、图等，则必须在语句末尾添加 GO 批处理标志,课堂综合练习,课堂练习： 则根据如下规则对机试成绩进行反复加分，直到平均分超过85分为止。请编写T-SQL语句实现。 90分以上： 不加分 8089分： 加1分 7079分： 加2分 6069分： 加3分 60分以下： 加5分,加分前,加分后,课堂综合练习答案,SELECT * FROM stuMarks -原始成绩 DECLARE labAvg INT WHILE(1=1)BEGINUPDATE stuMarks SET labExam=CASEWHEN labExam=85BREAKEND SELECT * FROM stuMarks -加分后
13、的成绩,声明变量，用户临时存放平均分,循环加分,根据机试成绩酌情加分，整个是一个UPDATE语句,获取目前的平均分，判断是否还继续加分,T-SQL语句的综合应用,学员信息表和成绩表,应到人数：5人,实到人数4人，缺考1人,T-SQL语句的综合应用,如何实现？,本次考试的缺考情况,比较笔试平均分和机试平均分，较低者进行循环提分，但提分后最高分不能超过97分 。加分后重新统计通过情况,统计通过率,T-SQL语句的综合应用,1.提示: 使用子查询统计缺考情况： 应到人数：SELECT count(*) FROM stuInfo 实到人数：SELECT count(*) FROM stuMarks,2
14、.提取学员的成绩信息并保存结果，包括学员姓名、学号、笔试成绩、机试成绩、是否通过 1)提取的成绩信息包含两表的数据，所以考虑两表连接，使用左连接（ LEFT JOIN ）；SELECT stuNameFROM stuInfo LEFT JOIN stuMarks 2)要求新加一列“是否通过（isPass）”，可采用CASE END。为了便于后续的通过率统计，通过则为1，没通过为0SELECT isPass=CASE WHEN writtenExam=60 THEN 1ELSE 0 END 3)要求保存提取（查询）的结果，可以使用我们曾学习过的SELECT INTO newTable语句，生成新
15、表并保存数据,T-SQL语句的综合应用,3.比较笔试平均分和机试平均分，对较低者进行循环提分，但提分后最高分不能超过97分： 1) 使用IF语句判断笔试还是机试偏低，决定对笔试还是机试提分； 2) 使用WHILE循环给每个学员加分，缺考的除外，当最高分超过97分时退出循环； 3)因为给每位学员的笔试或机试提分了，有的学员可能提分后刚好通过了，所以需要更新isPass（是否通过）列。UPDATE newTable SET isPass=CASEWHEN writtenExam=60 and labExam=60 THEN 1ELSE 0END,T-SQL语句的综合应用,4.提分后，统计学员的成绩
16、和通过情况： 1）使用别名实现中文字段名，即SELECT 姓名=stuName,学号=stuNo 2）如果某个学员的成绩为NULL(空)，则替换为”缺考”，否则原样显示； 3）isPass列中的1替换为是，0替换为否；SELECT ,机试成绩=CASEWHEN labExam IS NULL THEN 缺考ELSE convert(varchar(5),labExam)END,是否通过=CASE WHEN isPass=1 THEN 是ELSE 否END,T-SQL语句的综合应用,5.提分后统计学员的通过率情况： 1）通过人数：因为通过用1表示，没通过用0表示，所以isPass列的累加和即是通
17、过人数； 2）通过率：同理，isPass列的平均值*100即是通过率；,T-SQL参考语句,/*-本次考试的原始数据-*/ -SELECT * FROM stuInfo -SELECT * FROM stuMarks /*-统计考试缺考情况-*/ SELECT 应到人数=(SELECT count(*) FROM stuInfo) , -应到人数为子查询表达式的别名实到人数=(SELECT count(*) FROM stuMarks) ,缺考人数=(SELECT count(*) FROM stuInfo)-(SELECT count(*) FROM stuMarks),T-SQL参考语句,
18、/*-统计考试通过情况，并将结果存放在新表newTable中-*/ IF EXISTS(SELECT * FROM sysobjects WHERE name=newTable)DROP TABLE newTable SELECT stuName,stuInfo.stuNo,writtenExam ,labExam ,isPass=CASE WHEN writtenExam=60 and labExam=60 THEN 1ELSE 0ENDINTO newTable FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
19、-SELECT * FROM newTable -查看统计结果，可用于调试,T-SQL参考语句,/*-酌情加分：比较笔试和机试平均分，决定加哪门-*/ DECLARE avgWritten numeric(4,1) DECLARE avgLab numeric(4,1) SELECT avgWritten=AVG(writtenExam) FROM newTableWHERE writtenExam IS NOT NULL SELECT avgLab=AVG(labExam)FROM newTable WHERE labExam IS NOT NULL IF avgWritten=97BREA
20、KEND ELSE 略 -循环给笔试加分，最高分不能超过97分,T-SQL参考语句,-因为提分，所以需要更新isPass（是否通过）列的数据 UPDATE newTable SET isPass=CASEWHEN writtenExam=60 and labExam=60 THEN 1ELSE 0END -SELECT * FROM newTable -可用于调试,/*-显示考试最终通过情况-*/ SELECT 姓名=stuName,学号=stuNo ,笔试成绩=CASE WHEN writtenExam IS NULL THEN 缺考ELSE convert(varchar(5),writt
21、enExam)END,机试成绩=CASEWHEN labExam IS NULL THEN 缺考ELSE convert(varchar(5),labExam)END,是否通过=CASE WHEN isPass=1 THEN 是ELSE 否ENDFROM newTable,T-SQL参考语句,/*-显示通过率及通过人数-*/ SELECT 总人数=count(*) ,通过人数=SUM(isPass),通过率=(convert(varchar(5),AVG(isPass*100)+%) FROM newTable,总结,变量的赋值有两种方式：使用SET语句或SELECT语句。 输出结果也有两种方式：print语句和SELECT语句。 控制流语句提供了条件操作所需的顺序和逻辑。 语句块使用BEGINEND。 批处理可以提高语句执行的效率，批处理结束的标志是“GO”。,
