TSQL编程PPT课件

第6章 T SQL编程 本章内容 2 讲解时间 30分钟 实践时间 60分钟 T SQL流程控制 内容预览 3 T SQL即Transact SQL 是SQL在MicrosoftSQLServer上的增强版 其为使应用程序与SQLServer相沟通的主要语言 T SQL提供了标准SQL的DDL和DML功能 其延伸的函数 系统预定义程序以及程序设计结构 如IF和WHILE 让程序设计更有弹性 Microsoft公司采用T SQL作为SQLServer的核心组件 在T SQL中 命令和语句的书写不区分大小写 SQL概述 4 SQLServer中的每一项对象均有一个作为标识用的名称 该名称即为T SQL标识符 如 数据库名 数据表名 字段名称 存储过程名 视图名 索引名等 T SQL标识符 5 常规标识符以ASCII字母 Unicode字母 下划线 或 开头 其后可跟一个或若干个ASCII字符 Unicode字符 下划线 美元符号 或 但不能全为下划线 或 常规标识符不能是T SQL保留字 如SELECT UPDATE等 分隔标识符包括在双引号 或方括号 内的常规标识符 或不符合常规标识符规则的标识符 如 SELECT EmpSalary 等 T SQL标识符命名规则 6 局部变量全局变量 T SQL变量 7 局部变量是作用域局限在一定范围内的T SQL对象 局部变量在一个批处理或存储过程中被声明或定义 当该批处理结束后 此局部变量的生命周期便随之消亡 局部变量是用户自定义的变量 其名称以 作为前缀 T SQL局部变量 DECLARE e mailvarchar 50 DECLARE lastnamevarchar 30 firstnamevarchar 20 telvarchar 30 8 使用SET语句为局部变量赋值 T SQL局部变量 USEMeitaoGODECLARE countint categoryIDintSET categoryID 7SET count SELECTCOUNT FROMProductWHEREcategoryID categoryID PRINT 火锅类商品数量 CONVERT NVARCHAR 50 count 9 使用SELECT语句为局部变量赋值 T SQL局部变量 SET赋给变量的值通常是给定的 而SELECT赋给变量的值则由查询获得 DECLARE countintSELECT count SELECTCOUNT FROMProductWHEREcatagoryID 13 10 全局变量是用于记录SQLServer服务器活动状态的一组数据 是由SQLServer系统提供并赋值的变量 用户不能建立全局变量 也不能为全局变量赋值或直接更改全局变量的值 全局变量的名称以 开始 全局变量 11 全局变量 12 IF ELSEBEGIN END将多个SQL语句限制在其中 作为一个逻辑执行块 至少包含一条SQL语句 否则将出错 WHILE循环CASE语句 T SQL流程控制语句 13 DECLARE yearsvarchar 2 namevarchar 20 SET name 刘亚蒙 SET years SELECTDATEDIFF YYYY birthday GETDATE FROMCustomerWHEREcustomerName name PRINT 客户 name 的年龄是 years 岁 IF years 18PRINT 还未成年 ELSEPRINT 已经成年 DATEDIFF YYYY birthday GETDATE 为SQLServer的DATE函数 其返回birthday和当前日期之间的时间间隔 其中 YYYY 表示时间间隔单位为 年 流程控制语句 BEGIN END DECLARE searchvarchar 50 SET search 外套 IFEXISTS SELECT FROMProductWHEREtitleLIKE search BEGINSELECT 此商品存在 SELECTtitle currentPriceFROMProductWHEREtitleLIKE search ENDELSESELECT 此商品不存在 查询商品标题名中含有 外套 字样的商品 如果有则显示该商品标题和团购价 并按照团购价升序显示 否则显示 无该商品 流程控制语句 BEGIN END WHILE循环 流程控制语句 WHILE DECLARE xint 1 声明和赋值一体化DECLARE totalint 0WHILE x 10 循环条件BEGIN 开始循环体SET total xSET x 1ENDPRINT 结果是 convert nvarchar 10 total 16 查询出 服装 类商品的最低团购价 如该价格低于100元 则将所有 服装 类商品的团购价上调百分之十 再次查询出加价之后的 服装 类商品的最低团购价 如该价格仍低于100元 则将所有 服装 类商品的团购价再次上调百分之十 依次反复 直至所有 服装 类商品的最低团购价高于100元 流程控制语句 WHILE DECLARE pricemoney 服装类商品最低团购价DECLARE categoryIDint 服装类型编号SET categoryID SELECTcategoryIDFROMCategoryWHEREcategoryName 服装 取出服装类商品最低团购价 赋值给变量 priceSET price SELECTMIN currentPrice FROMProductWHEREcategoryID categoryID WHILE price 100 循环条件BEGIN 将所有服装类商品的团购价上调10 UPDATEProductSETcurrentPrice currentPrice 1 1WHEREcategoryID categoryID 再次取出服装类商品的最低团购价 赋值给变量 priceSET price SELECTMIN currentPrice FROMProductWHEREcategoryID categoryID ENDPRINT 服装类商品最低团购价 CONVERT nvarchar 20 price 流程控制语句 WHILE CASE结构提供了比IF ELSE结构更多的选择 switch 和判断的机会 所以使用CASE语句可以很方便地实现多重选择 从而避免了编写繁琐的多重IF ELSE嵌套循环 CASE语句分为简单表达式和选择表达式 流程控制语句 CASE 19 CASEinput expressionWHENwhen expressionTHENresult expression n ELSEelse result expression END 流程控制语句 CASE 简单表达式 20 输出所有商品类型的小类名称和大类名称 流程控制语句 CASE 输出所有商品类型的小类名称和大类名称 SELECTcategoryName商品小类名称 商品大类名称 CASEp CategoryID 选择大类类型编号WHEN1THEN 美食 WHEN2THEN 酒店 WHEN3THEN 电影 WHEN4THEN 购物 WHEN5THEN 休闲娱乐 WHEN6THEN 生活服务 商品大类类型编号不匹配WHEN中所有的值 则大类名称与小类名称同名ELSEcategoryNameENDFROMCategory 流程控制语句 CASE CASEWHENboolean expressionTHENresult expression n ELSEelse result expression END 流程控制语句 CASE 选择表达式 23 输出每件商品的优惠程度 商品的优惠程度可以由商品的团购价与原价之间的比值反映 比值为 currentPrice originalPrice 如果该比值小于0 4 含 则显示 巨优惠 如果在0 4和0 6 含 之间 则显示 很优惠 如果在0 6和0 8 含 之间 则显示 一般优惠 其他情况则显示 普通优惠 流程控制语句 CASE SELECT优惠情况 CASEWHENcurrentPrice originalPrice0 4ANDcurrentPrice originalPrice0 6ANDcurrentPrice originalPrice 0 8THEN 一般优惠 ELSE 普通优惠 ENDFROMProduct 流程控制语句 CASE 练习巩固上述语法使用T SQL编程获取 国内短线游 线路信息 按照线路价格升序显示线路编号 线路名和价格 使用T SQL编程输出 国内短线游 线路数 最高线路价格和最低线路价格 使用T SQL编程输出价格最高的线路信息 要求显示线路名 行程天数和价格 先使用MAX 取得线路最高价 然后定义一个变量保存该最高价 再以此最高价作为查询条件 获取线路表中价格最高的线路信息 编写T SQL 显示线路价格高于 黄山 的 国内长线游 信息 编写T SQL 计算每个订单的折扣系数 根据每个订单中订购人次的不同 决定每个订单的折扣系数 规则是 一人次不享受折扣 两人次享受九八折 三人次享受九六折 四人次以上 含 享受九四折 使用T SQL的CASE语句即可实现此业务 计算每个订单的实际订单金额 实践练习 实践时间 60分钟 26 实践练习 编写T SQL 获取客户 郝琼琼 预订线路的数量 定义变量存储客户姓名 需要联接订单客户表和客户表 编写T SQL 获取预订 凤凰古城 线路的客户姓名 定义一个变量 用于存储 凤凰古城 的线路编号 再通过联接客户表 订单客户表和订单线路表 使用子查询技术获取预订线路 凤凰古城 的客户姓名 生成每个订单的订购人次和订单金额 并为订单表中相应的数据列赋值 联接订单线路表 线路表和订单客户表 根据订单编号分组统计每个订单的订购人次和订单金额 将订购人次和订单金额 分别赋值给订单表中相应记录的 订购人次 man times 和 订单金额 amount 编写T SQL 删除 境外海岛游 要求先删除线路表中类型为 境外海岛游 线路数据 然后再删除线路类型表中 境外海岛游 数据 在国外旅游旺季时节 所有 出境游 线路的价格均上浮百分之十 要求更新线路价格 27 讲解时间 15分钟 实践时间 30分钟 自定义函数 内容预览 28 用户自定义函数 允许模块化设计函数的天然属性执行速度快效率高 降低代码编译开销 无需反复解析和优化分类Scalarfunctions 标量型函数 返回确定类型的标量值Inlinetable valuedfunctions 内联表值型函数 表函数 返回表类型的结果 29 用户自定义函数 Scalar IFOBJECT ID N dbo FunctionScalar N FN ISNOTNULLDROPFUNCTIONdbo FunctionScalarGOCREATEFUNCTIONdbo FunctionScalar i RankIDvarchar 20 RETURNSvarchar 20 ASBEGINDECLARE o retvarchar 20 SELECT o ret 真 r RankNameFROMRankrWHERERankID i RankID RETURN o retENDGOselectdbo FunctionScalar 103 30 用户自定义函数 内联表 IFOBJECT ID N dbo FunctionTbl N IF ISNOTNULLDROPFUNCTIONdbo FunctionTblGOCREATEFUNCTIONdbo FunctionTbl RETURNSTABLEASRETURN SELECT FROMEmployeeeWHEREe BirthdayISNULL GOselect fromdbo FunctionTbl fcwherefc Address 武汉 31 练习巩固上述语法使用自定义函数输出价格最高的线路信息 要求显示线路名 行程天数和价格 使用自定义函数获取每一种线路类型中价格最高的线路信息 要求显示线路类型名 线路名 行程天数和价格 实践练习 实践时间 30分钟 32 讲解时间 10分钟 实践时间 20分钟 游标 触发器 内容预览 33 游标 对于SELECT返回的结果集 有时需要每次处理一行游标使用五部曲 声明游标打开游标读取游标关闭游标删除游标 34 游标 DECLARECursor1CURSOR 1 创建游标STATICFORSELECTe Name e Gender e Salary e Address e CountryFROMEmployeeeGOOPENCursor1 2 打开游标FETCHNEXTFROMCursor1 3 读取游标FETCHNEXTFROMCursor1FETCHLASTFROMCursor1FETCHFIRSTFROMCursor1CLOSECursor1 4 关闭游标DEALLOCATECursor1 5 删除游标 35 练习巩固上述语法使用游标获取 国内长线游 的信息 实践练习 实践时间 20分钟 36 讲解时间 15分钟 实践时间 30分钟 触发器 内容预览 37 触发器 什么是触发器 定义在特定的表上不由程序调用不能手工调用只由事件触发触发事件 数据插入 更新或删除等为什么使用触发器 触发器通常用于强制业务规则当数据库被更改时 需要进行某种处