第章Tsql编程ppt课件.ppt

T SQL编程 2 回顾2 1 建库的语法 补充下列语句 IFEXISTS SELECT FROM WHEREname stuDB DROPDATABASEstuDB建表的语法 补充下列语句 IFEXISTS SELECT FROM WHEREname stuDB DROPTABLEstuInfo解释下列语句的含义 EXECsp addlogin zhangsan 1234 EXECsp grantdbaccess zhangsan zhangsanDBUser 3 回顾2 2 补充下列语句 1 ALTERTABLEstuInfoADDCONSTRAINT PRIMARYKEY stuNo 2 ALTERTABLEstuInfoADDCONSTRAINTUQ stuID 3 ALTERTABLEstuInfoADDCONSTRAINTDF stuAddress 4 ALTERTABLEstuInfoADDCONSTRAINTCK stuAgeCHECK 5 ALTERTABLEstuMarksADDCONSTRAINTFK stuNoFOREIGNKEY REFERENCES PK stuNo UNIQUE stuID DEFAULT 地址不详 FORstuAddress stuAgeBETWEEN15AND40 stuNostuInfo stuNo 4 目标 掌握如何定义变量并赋值掌握如何输出显示数据掌握IF WHILE CASE逻辑控制语句理解SQL中批处理的概念 5 使用变量 和C语言一样 变量分为 局部变量 局部变量必须以标记 作为前缀 如 age局部变量的使用也是先声明 再赋值全局变量 全局变量必须以标记 作为前缀 如 version全局变量由系统定义和维护 我们只能读取 不能修改全局变量的值如当前SQL语句是否执行有错误 ERROR 6 局部变量 例如 DECLARE namevarchar 8 DECLARE seatint 声明局部变量DECLARE 变量名数据类型 赋值SET 变量名 值SELECT 变量名 值 例如 SET name 张三 SELECT name stuNameFROMstuInfoWHEREstuNo s25302 必须确保筛选出的记录只有1条 或 用于普通的赋值 用于从表中查询数据并赋值 7 局部变量示例2 1 问题 编写T SQL查找李文才的左右同桌 学员信息表 分析 第一步 找出 李文才 的座位号 第二步 李文才的座位号加1或减1 8 局部变量示例2 2 查找李文才的信息 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 演示 使用局部变量 参考语句 9 全局变量 全局变量都使用两个 标志作为前缀 10 全局变量的使用 print SQLServer的版本 VERSIONprint 服务器的名称 SERVERNAMEprint 最后学号为 convert varchar 5 IDENTITY 11 在使用全局变量时应该注意以下几点 用户只能使用预先定义的全局变量 引用全局变量时 必须以标记符 开头 局部变量名称不能与全局变量的名称相同 否则会在应用程序中出现不可预测的结果 例显示到当前日期和时间为止试图登录SQLServer2005的次数 程序代码如下 SELECTGETDATE AS 当前的时期和时间 CONNECTIONSAS 试图登录的次数 12 输出语句 T SQL支持输出语句 用于输出显示处理的数据结果常用的输出语句有两种 Print局部变量或字符串SELECT局部变量as自定义列名示例 使用print语句要求单个局部变量或字符串表达式作为参数print 当前错误号 ERROR全局变量 ERROR返回的是整数值 所以必须使用转换函数 把数据转换为字符串 如下所示 Print 当前错误号 convert varchar 5 ERROR print 服务器的名称 SERVERNAMESELECT SERVERNAMEAS 服务器名称 演示select和print语句的输出 出错 13 批处理 客户端应用程序 SQLServer服务器 批处理语句 语句1语句2 GO 批处理是包含一个或多个SQL语句的组 从应用程序一次性地发送到SQLServer执行SQLServer将批处理语句编译成一个可执行单元 此单元称为执行计划 执行计划中的语句每次执行一条 14 批处理语句示例 SELECT FROMstuInfoSELECT FROMstuMarksUPDATEstuMarksSETwrittenExam writtenExam 2GO GO是批处理的标志 表示SQLServer将这些T SQL语句编译为一个执行单元 提高执行效率一般是将一些逻辑相关的业务操作语句 放置在同一批中 这完全由业务需求和代码编写者决定 15 SQLServer规定 如果是建库 建表语句 以及后面将要学到的存储过程和视图等 则必须在语句末尾添加 GO 批处理标志其他常用的DDL语句主要有 CREATEDEFAULTCREATEFUNCTIONCREATEPROCEDURECREATERULECREATETRIGGERCREATEVIEW这些语句不能在批处理中与其他语句组合使用 CREATETABLEDEPT GO 16 在SQL中使用注释 注释是程序代码中的描述性的文本字符串 也称为注解 编译器会忽略这些内容注释常用来记录程序名称 作者名称以及对代码进行重要修改的日期 注释可以用来描述复杂的计算或用来解释程序设计的方SQLServer支持下列两种注释方式 双连字符 单行注释 这些注释字符可以用在要执行的代码的同一行 或者单独作为一行 正斜线 星号字符对 多行注释 从打开注释对 到关闭注释对 的所有内容都认为是注释部分 多行注释 不能跨批处理 17 逻辑控制语句 IF ELSE语句 C语言中的if else语句if 条件 语句1 语句2 else 语句1 语句2 SQL中的IF ELSE语句IF 条件 BEGIN语句1语句2 ENDELSEBEGIN语句1 语句2 END 同C语言一样 ELSE是可选部分如果有多条语句 才需要BEGIN END语句块 18 IF语句示例 例在Transact SQL中使用IF语句 程序代码如下 DECLARE pointASintSet point 87IF point 60PRINT pass verygood ELSEPRINT nopass tryagain 19 例用BEGIN END语句可使IF语句在计算结果为FALSE时跳过语句块 程序代码如下 DECLARE MyVarfloatSet MyVar 5 7If MyVar 10 8BEGINSET MyVar 123 456PRINT 变量 MyVar的值为 PRINTCAST MyVarASvarchar 12 ENDELSEPRINTCAST MyVarASvarchar 12 20 IF ELSE示例2 1 问题 统计并显示本班笔试平均分 如果平均分在70以上 显示 成绩优秀 并显示前三名学员的考试信息 如果在70以下 显示 本班成绩较差 并显示后三名学员的考试信息 学员成绩表 分析 第一步 统计平均成绩存入临时变量 第二步 用IF ELSE判断 21 IF ELSE示例2 2 22 逻辑控制语句 WHILE循环语句 C语言中的while语句while 条件 语句1 语句2 break SQL中的WHILE语句WHILE 条件 BEGIN语句1语句2 BREAKEND 同C语言一样 BREAK表示退出循环如果有多条语句 才需要BEGIN END语句块 23 WHILE示例2 1 问题 本次考试成绩较差 假定要提分 确保每人笔试都通过 提分规则很简单 先每人都加2分 看是否都通过 如果没有全部通过 每人再加2分 再看是否都通过 如此反复提分 直到所有人都通过为止 学员成绩表 分析 第一步 统计没通过的人数 第二步 如果有人没通过 加分 第三步 循环判断 统计函数selectcount fromstuMarkswherewrittenExam 60 24 DECLARE nintWHILE 1 1 条件永远成立BEGINSELECT n COUNT FROMstuMarksWHEREwrittenExam0 UPDATEstuMarks 每人加2分SETwrittenExam writtenExam 2ELSEBREAK 退出循环ENDprint 加分后的成绩如下 SELECT FROMstuMarks WHILE示例2 2 参考语句 25 逻辑控制语句 CASE END多分支语句 CASEWHEN条件1THEN结果1WHEN条件2THEN结果2 ELSE其他结果END 26 CASE END示例2 1 问题 采用美国的ABCDE五级打分制来显示笔试成绩 A级 90分以上B级 80 89分C级 70 79分D级 60 69分E级 60分以下 学员成绩表 根据分数 一条条记录进行判断 使用CASE END 27 print ABCDE五级显示成绩如下 SELECTstuNo 成绩 CASEWHENwrittenExam 60THEN E WHENwrittenExamBETWEEN60AND69THEN D WHENwrittenExamBETWEEN70AND79THEN C WHENwrittenExamBETWEEN80AND89THEN B ElSE A ENDFROMstuMarks CASE END示例2 2 参考语句 1 多分支部分整体是一条非常简单的语句 SELECTstuNo 成绩 某个值FROMstuMarks2 成绩是别名 等价使用AS 3 成绩后的值不确定 它的值由CASE END多分支决定 28 CASE END课堂练习 课堂练习 请根据平均分和下面的评分规则 编写T SQL语句查询学员的成绩 如上图所示 优 90分以上良 80 89分中 70 79分差 60 69分不及格 60分以下 学员成绩分析 求平均分 writtenExam labExam 2 60 29 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 30 课堂综合练习 课堂练习 则根据如下规则对机试成绩进行反复加分 直到平均分超过85分为止 请编写T SQL语句实现 90分以上 不加分80 89分 加1分70 79分 加2分60 69分 加3分60分以下 加5分 加分前 加分后 31 课堂综合练习答案 SELECT FROMstuMarks 原始成绩DECLARE labAvgINTWHILE 1 1 BEGINUPDATEstuMarksSETlabExam CASEWHENlabExam 85BREAKENDSELECT FROMstuMarks 加分后的成绩 声明变量 用户临时存放平均分 循环加分 根据机试成绩酌情加分 整个是一个UPDATE语句 获取目前的平均分 判断是否还继续加分 32 其他语句 GOTO语句 GOTO语句可以使程序直接跳到指定的标有标识符的位置处继续执行 而位于GOTO语句和标识符之间的程序将不会被执行 标识符可以为数字与字符的组合 但必须以 结尾 GOTO语句的语法形式为 label statement statement blockGOTOlabel 例利用GOTO语句求出从1加到7的总和 程序代码如下 declare sumint countintselect sum 0 count 0label 1 select count count 1select sum sum countif count 6gotolabel 1select countas 数值 sumas 1 7累加和 33 WAITFOR语句 WAITFOR用于暂时停止执行SQL语句 语句块或者存储过程等 直到所设定的时间已过或者所设定的时间已到才继续执行 WAITFOR语句的语法形式为 WAITFOR DELAY time to pass TIME time to execute 例使用WAITFORTIME语句 以便在指定时间执行存储过程sp help 程序代码如下 BEGINWAITFORTIME 22 20 17 EXECUTEsp helpEND 34 TRY CATCH语句 类似于C 语言的异常处理功能 用来处理Transact SQL代码中的错误 TRY CATCH构造包括两部分 一个TRY块和一个CATCH块 如果在TRY块的T SQL语句中检测到错误条件 控制将被传递到CATCH块处理该错误 1 TRY CATCH语句语法格式 BEGINTRY sql statement statement block ENDTRYBEGINCATCH sql statement statement block ENDCATCH 2 主