数据库SQL编程教程.ppt

第十章 T SQL编程 目标 掌握如何定义变量并赋值掌握如何输出显示数据掌握IF WHILE CASE逻辑控制语句理解SQL中批处理的概念 使用变量 变量分为 局部变量 局部变量必须以标记 作为前缀 如 age局部变量的使用也是先声明 再赋值全局变量 全局变量必须以标记 作为前缀 如 version全局变量由系统定义和维护 我们只能读取 不能修改全局变量的值 局部变量 例如 DECLARE namevarchar 8 DECLARE seatint 声明局部变量DECLARE 变量名数据类型 赋值SET 变量名 值SELECT 变量名 值 例如 SET name 张三 SELECT name stuNameFROMstuInfoWHEREstuNo s25302 必须确保筛选出的记录只有1条 或 局部变量示例 问题 编写T SQL查找李文才的左右同桌 学员信息表 分析 第一步 找出 李文才 的座位号 第二步 李文才的座位号加1或减1 局部变量示例 查找李文才的信息 DECLARE namevarchar 8 学员姓名SET name 李文才 使用SET赋值SELECT FROMstuInfoWHEREstuName name 查找李文才的左右同桌 DECLARE seatint 座位号SELECT seat stuSeatFROMstuInfo 使用SELECT赋值WHEREstuName nameSELECT FROMstuInfoWHERE stuSeat seat 1 OR stuSeat seat 1 GO 演示 使用局部变量 参考语句 全局变量 全局变量都使用两个 标志作为前缀 全局变量示例 print SQLServer的版本 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的版本 服务器名称 座位号 自动编号 错误号 错误号 逻辑控制语句 T SQL语言提供了一些可用于改变语句执行顺序的命令 称为流程控制语句 流程控制语句与常见的程序设计语言类似 主要包括以下几种 begin end if elsecasewhile continue breakgoto 逻辑控制语句 IF ELSE语句 SQL中的IF ELSE语句IF 条件 BEGIN语句1语句2 ENDELSEBEGIN语句1 语句2 END ELSE是可选部分如果有多条语句 才需要BEGIN END语句块 IF ELSE示例 问题 统计并显示本班笔试平均分 如果平均分在70以上 显示 成绩优秀 并显示前三名学员的考试信息 如果在70以下 显示 本班成绩较差 并显示后三名学员的考试信息 学员成绩表 分析 第一步 统计平均成绩存入临时变量 第二步 用IF ELSE判断 IF ELSE示例 设置输出结果的格式 为了文本消息和输出结果显示在同一窗口 需要设置输出结果的格式 逻辑控制语句 WHILE循环语句 SQL中的WHILE语句WHILE 条件 BEGIN语句1语句2 BREAKEND BREAK表示退出循环如果有多条语句 才需要BEGIN END语句块 WHILE示例 问题 本次考试成绩较差 假定要提分 确保每人笔试都通过 提分规则很简单 先每人都加2分 看是否都通过 如果没有全部通过 每人再加2分 再看是否都通过 如此反复提分 直到所有人都通过为止 学员成绩表 分析 第一步 统计没通过的人数 第二步 如果有人没通过 加分 第三步 循环判断 DECLARE nintWHILE 1 1 条件永远成立BEGINSELECT n COUNT FROMstuMarksWHEREwrittenExam0 UPDATEstuMarks 每人加2分SETwrittenExam writtenExam 2ELSEBREAK 退出循环ENDprint 加分后的成绩如下 SELECT FROMstuMarks WHILE示例 参考语句 逻辑控制语句 CASE END多分支语句 CASEWHEN条件1THEN结果1WHEN条件2THEN结果2 ELSE其他结果END CASE END示例 问题 采用美国的ABCDE五级打分制来显示笔试成绩 A级 90分以上B级 80 89分C级 70 79分D级 60 69分E级 60分以下 学员成绩表 print ABCDE五级显示成绩如下 SELECTstuNo 成绩 CASEWHENwrittenExam 60THEN E WHENwrittenExamBETWEEN60AND69THEN D WHENwrittenExamBETWEEN70AND79THEN C WHENwrittenExamBETWEEN80AND89THEN B ElSE A ENDFROMstuMarks CASE END示例 参考语句 CASE END课堂练习 课堂练习 请根据平均分和下面的评分规则 编写T SQL语句查询学员的成绩 如上图所示 优 90分以上良 80 89分中 70 79分差 60 69分不及格 60分以下 学员成绩分析 CASE END练习答案 USEstuDBGOSELECT考号 ExamNo 学号 stuNo 笔试 writtenExam 机试 labExam 平均分 writtenExam labExam 2 等级 CASEWHEN writtenExam labExam 2 60THEN 不及格 WHEN writtenExam labExam 2BETWEEN60AND69THEN 差 WHEN writtenExam labExam 2BETWEEN70AND79THEN 中 WHEN writtenExam labExam 2BETWEEN80AND89THEN 良 ElSE 优 ENDFROMstuMarks 批处理语句 批处理是包含一个或多个SQL语句的组 从应用程序一次性地发送到SQLServer执行SQLServer将批处理语句编译成一个可执行单元 此单元称为执行计划 执行计划中的语句每次执行一条 客户端应用程序 SQLServer服务器 批处理语句 语句1语句2 GO 批处理语句示例 SELECT FROMstuInfoSELECT FROMstuMarksUPDATEstuMarksSETwrittenExam writtenExam 2GO GO是批处理的标志 表示SQLServer将这些T SQL语句编译为一个执行单元 提高执行效率一般是将一些逻辑相关的业务操作语句 放置在同一批中 这完全由业务需求和代码编写者决定 批处理语句示例 例如建表语句的末尾必须添加GOCREATETABLEstuInfo GO SQLServer规定 如果是建库 建表语句 以及我们后面学习的存储过程和视图等 则必须在语句末尾添加GO批处理标志 课堂综合练习 课堂练习 则根据如下规则对机试成绩进行反复加分 直到平均分超过85分为止 请编写T SQL语句实现 90分以上 不加分80 89分 加1分70 79分 加2分60 69分 加3分60分以下 加5分 加分前 加分后 课堂综合练习答案 SELECT FROMstuMarks 原始成绩DECLARE labAvgINTWHILE 1 1 BEGINUPDATEstuMarksSETlabExam CASEWHENlabExam 85BREAKENDSELECT FROMstuMarks 加分后的成绩 声明变量 用户临时存放平均分 循环加分 根据机试成绩酌情加分 整个是一个UPDATE语句 获取目前的平均分 判断是否还继续加分 T SQL语句的综合应用 学员信息表和成绩表 应到人数 5人 实到人数4人 缺考1人 T SQL语句的综合应用 如何实现 本次考试的缺考情况 比较笔试平均分和机试平均分 较低者进行循环提分 但提分后最高分不能超过97分 加分后重新统计通过情况 统计通过率 T SQL语句的综合应用 1 提示 使用子查询统计缺考情况 应到人数 SELECTcount FROMstuInfo实到人数 SELECTcount FROMstuMarks 2 提取学员的成绩信息并保存结果 包括学员姓名 学号 笔试成绩 机试成绩 是否通过1 提取的成绩信息包含两表的数据 所以考虑两表连接 使用左连接 LEFTJOIN SELECTstuName FROMstuInfoLEFTJOINstuMarks 2 要求新加一列 是否通过 isPass 可采用CASE END 为了便于后续的通过率统计 通过则为1 没通过为0SELECT isPass CASEWHENwrittenExam 60 THEN1ELSE0END 3 要求保存提取 查询 的结果 可以使用我们曾学习过的SELECT INTOnewTable语句 生成新表并保存数据 T SQL语句的综合应用 3 比较笔试平均分和机试平均分 对较低者进行循环提分 但提分后最高分不能超过97分 1 使用IF语句判断笔试还是机试偏低 决定对笔试还是机试提分 2 使用WHILE循环给每个学员加分 缺考的除外 当最高分超过97分时退出循环 3 因为给每位学员的笔试或机试提分了 有的学员可能提分后刚好通过了 所以需要更新isPass 是否通过 列 UPDATEnewTableSETisPass CASEWHENwrittenExam 60andlabExam 60THEN1ELSE0END T SQL语句的综合应用 4 提分后 统计学员的成绩和通过情况 1 使用别名实现中文字段名 即SELECT姓名 stuName 学号 stuNo 2 如果某个学员的成绩为NULL 空 则替换为 缺考 否则原样显示 3 isPass列中的1替换为是 0替换为否 SELECT 机试成绩 CASEWHENlabExamISNULLTHEN 缺考 ELSEconvert varchar 5 labExam END 是否通过 CASEWHENisPass 1THEN 是 ELSE 否 END T SQL语句的综合应用 5 提分后统计学员的通过率情况 1 通过人数 因为通过用1表示 没通过用0表示 所以isPass列的累加和即是通过人数 2 通过率 同理 isPass列的平均值 100即是通过率 T SQL参考语句 本次考试的原始数据 SELECT FROMstuInfo SELECT FROMstuMarks 统计考试缺考情况 SELECT应到人数 SELECTcount FROMstuInfo 应到人数为子查询表达式的别名实到人数 SELECTcount FROMstuMarks 缺考人数 SELECTcount FROMstuInfo SELECTcount FROMstuMarks T SQL参考语句 统计考试通过情况 并将结果存放在新表newTable中 IFEXISTS SELECT FROMsysobjectsWHEREname newTable DROPTABLEnewTableSELECTstuName stuInfo stuNo writtenExam labExam isPass CASEWHENwrittenExam 60andlabExam 60THEN1ELSE0ENDINTOnewTableFROMstuInfoLEFTJOINstuMarksONstuInfo stuNo stuMarks stuNo SELECT FROMnewTable 查看统计结果 可用于调试 T SQL参考语句 酌情加分 比较笔试和机试平均分 决定加哪门 DECLARE avgWrittennumeric 4 1 DECLARE avgLabnumeric 4 1 SELECT avgWritten AVG writtenExam FROMnewTableWHEREwrittenExamISNOTNULLSELECT avgLab AVG labExam FROMnewTableWHERElabExamISNOTNULLIF avgWritten 97BREAKENDELSE 略 循环给笔试加分 最高分不能超过97分 T SQL参考语句 因为提分 所以需要更新isPass 是否通过 列的数据UPDATEnewTableSETisPass CASEWHENwrittenExam 60andlabExam 60THEN1ELSE0END SELECT FROMnewTable 可用于调试 显示考试最终通过情况 SELECT姓名 stuName 学号 stuNo 笔试成绩 CASEWHENwrittenExamISNULLTHEN 缺考 ELSEconvert varchar 5 writtenExam END 机试成绩 CASEWHENlabExamISNULLTHEN 缺考 ELSEconvert varchar 5 labExam END 是否通过 CASEWHENisPass 1THEN 是 ELSE 否 ENDFROMnewTable T SQL参考语句 显示通过率及通过人数 SELECT总人数 count 通过人数 SUM isPass 通过率 convert varchar 5 AVG isPass 100 FROMnewTable 总结 变量的赋值有两种方式 使用SET语句或SELECT语句 输出结果也有两种方式 print语句和SELECT语句 控制流语句提供了条件操作所需的顺序和逻辑 语句块使用BEGIN END 批处理可以提高语句执行的效率 批处理结束的标志是 GO