TSQL编程SQLServer高级查询与TSQL编程课件

1、第4章 T-SQL编程SQL Server高级查询与T-SQL编程第1页，共58页。回顾子查询是一个嵌套在 select、insert、update 和 delete 语句或其他子查询中的查询比较运算符 all 关键字用于子查询之前。通过该关键字将一个表达式或列的值，与子查询所返回的一列值中的每一行进行比较，只要有一次比较的结果为 false，则 all 测试返回 false，不执行主查询；否则返回 true，执行主查询exists 用于检查子查询是否至少会返回一行数据子查询不仅可在 select 语句中使用，用于实现需要嵌套的查询功能，还可以维护数据，完成复杂的更新、删除和插入功能第2页，共

2、58页。封装T-SQL 常量和变量流程控制语句系统存储过程和无参存储过程有参存储过程编写 T-SQL 代码查询和处理旅游信息编写 T-SQL 的流程控制代码处理旅游信息利用无参存储过程查询线路信息利用有参存储过程查询给定条件的旅游信息第3页，共58页。T-SQL 常量和变量T-SQL 常量T-SQL 标识符T-SQL 局部变量定义使用 set 语句为局部变量赋值使用 select 语句为局部变量赋值6. T-SQL 全局变量 编写 T-SQL 代码查询和处理旅游信息2520第4页，共58页。4.1.1 T-SQL 常量T-SQL 即 Transact-SQL，是 SQL 在 Microsoft

3、 SQL Server 上的增强版，为应用程序与 SQL Server 进行沟通的主要语言。T-SQL 提供了标准 SQL 的 DDL 和 DML 功能，其延伸的函数、系统预定义程序以及程序设计结构（例如 if、while 等）让程序设计更有弹性第5页，共58页。4.1.1 T-SQL 常量数字常量包裹整数常量、小数常量和浮点常量,例如：12,-37,200.35浮点常量使用符号 e 来指定,例如：1.5e3,-3.14e1,2.5e-7字符串常量包括在单引号内，它包含字母数字字符（a z、A Z 和 0 9）以及特殊字符，如感叹号（！）、at 符（）和数字号（#）,例如：FootballSQ

4、L 规定日期、时间和时间间隔的常量值被指定为日期和时间常量。例如：2017-01-13,03/03/2016SQL 包含几个特有的符号常量，这些常量代表不同的常用数据值。例如：CURRENT_DATE 表示当前的日期，类似的还有 CURRENT_TIME（当前时间）、CURRENT_TIMESTAMP（当前时间戳）等第6页，共58页。4.1.2 T-SQL 标识符变量用于临时存放数据，其中的数据随着程序的运行而变化，变量包括名称及数据类型两个属性。变量名用于标识该变量，数据类型确定了该变量存放值的格式及其允许的运算。SQL Server 中的每一项对象均有一个作为标识用的名称，即 T-SQL

5、标识符，命名规则如下：常规标识符。以 ASCII 字母、Unicode 字母、下划线（_）、 或 # 开头，其后可跟一个或若干个 ASCII 字符、Unicode 字符、下划线（_）、美元符号（$）、 或 #，但不能全为下划线（_）、 或 #。常规标识符不能是 T-SQL保留字分隔标识符。包括在双引号（“”）或方括号（ ）内的常规标识符，或不符合常规标识符规则的标识符第7页，共58页。4.1.3 T-SQL 局部变量定义局部变量是作用域局限在一定范围内的 T-SQL 对象，局部变量是用户自定义的变量，其名称必须以 开始，用于保存单个数据值。局部变量使用 declare 语句声明，所有局部变量在

6、声明后均初始化为 nulldeclare varaible_name datatype ,n第8页，共58页。4.1.3 T-SQL 局部变量定义varaible_name 为局部变量名，并以 开头。datatype 为该局部变量指定的数据类型declare e_mail varchar(50)declare lastname varchar(30),firstname varchar(20),tel varchar(30)第9页，共58页。4.1.4 使用 set 语句为局部变量赋值一个 set 语句仅能为一个变量赋值。其语法格式为：set varaible_name=expressionu

7、se lingjugo declare areaID int, categoryID intset areaID = 4 set categoryID = 7 select title 商品标题 , currentprice 团购价 from productwhere areaID = areaID and categoryID =categoryID第10页，共58页。4.1.4 使用 set 语句为局部变量赋值在 T-SQL 中，go 语句的作用主要表现在以下两个方面：等待 go 语句前的代码执行完毕，再执行 go 之后的语句。如果需要连续执行多条 SQL 语句，并希望它们依次按顺序往下执

8、行，则需要使用 go 进行控制。T-SQL 在执行 go 语句时，SQL Server 的命令窗口和查询窗口将 go 之前已定义的局部变量清除，被 go分开的局部变量不能共享。第11页，共58页。4.1.4 使用 set 语句为局部变量赋值使用 T-SQL 输出“火锅”类商品的数量：use lingjugo declare count int, categoryID intset categoryID = 7set count=(select count(*) from product where categoryID = categoryID)print 火锅类商品数量：+convert(n

9、varchar(50),count)将 count 转换成字符串类型第12页，共58页。4.1.5 使用 select 语句为局部变量赋值一个 select 语句仅能为一个变量赋值。其语法格式为：select varaible_name=expressionselect areaID = 4 select categoryID = 7第13页，共58页。4.1.5 使用 select 语句为局部变量赋值使用子查询执行 select 赋值，如果子查询没有返回值，则将变量赋值为 null：godeclare categoryName1 nvarchar(20),categoryName2 nvar

10、char(20)select categoryName1 = 火锅 select categoryName2 =(select categoryName from category where categoryID=23)select categoryName1, categoryName2在 category 表中并没有 categoryID 为 23 的记录第14页，共58页。4.1.6 T-SQL 全局变量全局变量是 SQL Server 系统内部事先定义好的变量，不用用户参与定义，对用户而言，其作用范围并不局限于某一程序，而是任何程序均可随时调用SQL Server 2018常见的全局

11、变量见表：变量名作用ERROR 返回执行上一条 Transact-SQL 语句所返回的错误号IDENTITY 返回最后插入的标志值MAX_CONNECTIONS 返回 SQL Server 实例所允许同时连接的最大用户数ROWCOUNT 返回上一条语句影响的行数TRANCOUNT返回当前连接的活动事务数第15页，共58页。4.1.7 学生实践练习编写 T-SQL 代码实现以下需求：（1）获取客户“郝琼琼”预订线路的数量。（2）获取预订线路“凤凰古城”的客户姓名。（3）生成每个订单的线路订购人次和订单金额，并更新订单表线路订购人次和订单金额的数据。25第16页，共58页。4.1.7 学生实践练习

12、定义变量存储客户姓名，需要连接订单客户表和客户表。declare customerName nvarchar(20)= 郝琼琼 select 客户姓名 = 郝琼琼 , 预订线路数 =count(*) from oc_detail ocd, customer cwhere CustomerID=c.customerID and =customerName第17页，共58页。4.1.7 学生实践练习定义一个变量，用于存储“凤凰古城”的线路编号，再通过连接客户表、订单客户表和订单线路表，使用子查询技术获取预订线路“凤凰古城”的客户姓名。第18页，共58页。4.1.7

13、学生实践练习（1）连接订单线路表、线路表和订单客户表，根据订单编号分组统计每个订单的订购人次和订单金额select old.ordersID 订单号 , count(*) 线路订购人次 , sum(price) 订单金额 from ol_detail old, line l ,oc_detail ocd where old.lineID=l.lineID and old.ordersID=ocd.ordersID group by old.ordersID第19页，共58页。4.1.7 学生实践练习（2） 将（1）的 SQL 语句作为一个子查询，放置于 update 语句的 from 子句中。

14、update orders set man_times=A. 订购人次 , amount=A. 订单金额 from( select old.ordersID 订单号 , count(*) 线路订购人次 , sum(price) 订单金额 from ol_detail old, line l ,oc_detail ocd where old.lineID=l.lineID and old.ordersID=ocd.ordersID group by old.ordersID) A where ordersID=A. 订单号第20页，共58页。流程控制语句beginend 语句ifelse 条件判

15、断语句while 循环语句case 选择语句编写 T-SQL 的流程控制代码处理旅游信息2520第21页，共58页。4.2.1 beginend 语句T-SQL 的流程控制关键字包括：beginend、ifelse、while、case 等beginend 语句可以将多个 SQL 语句限制在其中，作为一个逻辑执行块。beginend 语句块应至少包含一条SQL 语句，否则将出错在流程控制语句中包含不止一条 SQL 语句时，需要使用 beginend 语句，其类似于 C# 或 Java 语言的大括号“ ”。beginend 主要用于 ifelse 语句、while 循环和 case 语句的执行体

16、第22页，共58页。4.2.2 ifelse 条件判断语句如果要对给定的条件进行判定，当条件为真或假时，分别执行不同的 T-SQL 语句，可用 ifelse语句实现。ifelse 语句的语法格式为：if logical_expressionexpression1 else expression2 第23页，共58页。4.2.2 ifelse 条件判断语句输入某客户的姓名，显示该客户的年龄，如果年龄小于 18，则会显示该客户“还未成年”的信息，否则会显示该客户“已经成年”。use lingjugodeclare years int,name varchar(20)set name= 刘亚蒙 se

17、t years=(select datediff(YYYY,birthday,getdate() from customer wherecustomerName=name)print 客户 +name+ 的年龄是 +convert(nvarchar(10),years)+ 岁，if years=18 print 还未成年 else print 已经成年 第24页，共58页。4.2.2 ifelse 条件判断语句查询商品标题名中含有“KTV”字样的商品，如果有则显示该商品标题和团购价，否则显示“检索商品不存在”。use lingjugodeclare search varchar(50)set

18、search=KTVif exists(select * from product where title like %+search+%)begin select title 商品标题 ,currentprice 团购价 from product where title like%+search+%endelse select 检索商品不存在 第25页，共58页。4.2.3 while 循环语句while 语句的功能是在满足条件的情况下，重复执行同样的语句。其语法格式为：while logical_expressionbeginexpression break continueend第26页

19、，共58页。4.2.3 while 循环语句编写 T-SQL，计算 1+2+3+10 的值declare x int = 1 declare total int = 0while x = 10 begin set total += x set x += 1endprint 结果是 +convert(nvarchar(10),total)go第27页，共58页。4.2.3 while 循环语句查询出“食品”类商品的最低团购价，如该价格低于 5 元，则将所有“食品”类商品的团购价上调 10%，再次查询出加价之后的“食品”类商品的最低团购价，如该价格仍低于 5 元，则将所有“食品”类商品的团购价再次

20、上调 10%，依次反复，直至所有“食品”类商品的最低团购价高于 5 元。use lingjugodeclare price moneydeclare categoryID intset categoryID=(select categoryID from category where categoryName= 食品 )set price=(select min(currentPrice) from product where categoryID=categoryID)while price5 beginupdate product set currentPrice=currentPrice

21、*1.1 where categoryID=categoryIDpriceset price=(select min(currentPrice) from product where categoryID=categoryID)endprint 食品类商品最低团购价：+convert(nvarchar(20),price)第28页，共58页。4.2.4 case 选择语句由于 case 结构提供了比 ifelse 结构更多的选择（switch）和判断的机会，所以使用 case 语句可以很方便地实现多重选择，从而避免了编写繁琐的多重 ifelse 嵌套循环，简单表达式：case input_ex

22、pressionwhen when_expression then result_expression n else else_result_expressionend第29页，共58页。4.2.4 case 选择语句显示所有商品类型信息select categoryName 商品小类名 , p_categoryID 商品大类编号 from category第30页，共58页。4.2.4 case 选择语句通过T-SQL编写分类语句use lingjugoselect categoryName 商品小类名称 , 商品大类名称 = case p_categoryID when 1 then 美食

23、 when 2 then 酒店 when 3 then 电影 when 4 then 购物 when 5 then 休闲娱乐 when 6 then 生活服务 else categoryName endfrom category第31页，共58页。4.2.4 case 选择语句case 选择语句选择表达式的语法：casewhen boolean_expression then result_expression n else else_result_expressionend第32页，共58页。4.2.4 case 选择语句输出每件商品的优惠程度。商品的优惠程度可以由商品的团购价与原价之间的比

24、值反映，比值 =currentPrice/originalPrice，如果该比值小于 0.4（含），则显示“巨优惠”；如果在 0.4 0.6（含）之间，则显示“很优惠”；如果在 0.6 0.8（含）之间，则显示“一般优惠”；其他情况则显示“普通优惠” 。use lingjugoselect title 商品标题 , 优惠程度 =case when currentprice/originalprice0.4 and currentprice/originalprice0.6 and currentprice/originalpriceordersDateorder by o.ordersID,

25、currentPrice第52页，共58页。4.4.2 带输出参数存储过程如果需要存储过程返回一个或多个值，可通过使用输出参数来实现。输出参数必须在创建存储过程时，使用output 关键字进行声明。use lingjugoif exists(select * from sysobjects where name=proc_MaxPriceGivenCategory) drop procedure proc_MaxPriceGivenCategorygocreate procedure proc_MaxPriceGivenCategory ( categoryName nvarchar(20),

26、 maxPrice money output, productName nvarchar(20) output)asselect maxprice=max(currentPrice) from product p, category cwhere p.categoryID=c.categoryID and categoryName=categoryNameselect productName=title from product where currentPrice=maxPricegodeclare categoryName nvarchar(20)declare maxPrice mone

27、ydeclare productName nvarchar(20)set categoryName= 火锅 exec proc_MaxPriceGivenCategory categoryName, maxPrice output, productName outputprint 团购价最贵的 +categoryName+ 类商品是：+productName+，价格是：+convert(nvarchar(20),maxPrice)+ 元。第53页，共58页。4.4.2 带输出参数存储过程创建一个名为 proc_NumsGivenCategory 的存储过程，其将获取指定类型商品的总订购数use lingjugoif exists(select * from sysobjects where name=proc_NumsGivenC