T-SQ与高级子查询

1、第二章第二章 使用使用T-SQL编程编程本章内容本章内容uT-SQLT-SQL中的变量中的变量uT-SQLT-SQL中的运算符中的运算符uT-SQLT-SQL中的批处理语句中的批处理语句uT-SQLT-SQL中的流程控制语句中的流程控制语句1. T-SQL1. T-SQL中的变量中的变量 uT-SQLT-SQL中的变量分为：中的变量分为：n局部变量局部变量局部变量必须以标记局部变量必须以标记 作为前缀，如作为前缀，如numnum局部变量的使用是先声明，再赋值局部变量的使用是先声明，再赋值 n全局变量全局变量全局变量必须以标记全局变量必须以标记作为前缀，如作为前缀，如versionversion

2、全局变量由系统定义和维护，只能读取，不能修改全局变量的值全局变量由系统定义和维护，只能读取，不能修改全局变量的值1.1 1.1 T-SQL变量概述变量概述1.1 T-SQL1.1 T-SQL变量概述变量概述u全局变量全局变量变量名变量名作用作用ERROR返回执行的上一个语句的错误号返回执行的上一个语句的错误号IDENTITY返回最后插入的标识值返回最后插入的标识值MAX_CONNECTIONS返回允许同时进行的最大用户连接数返回允许同时进行的最大用户连接数ROWCOUNT返回受上一语句影响的行数返回受上一语句影响的行数SERVERNAME返回运行返回运行 SQL Server 的本地服务器的名

3、称的本地服务器的名称SERVICENAME返回返回 SQL Server 正在其下运行的注册表项的名称正在其下运行的注册表项的名称TRANCOUNT返回当前连接的活动事务数返回当前连接的活动事务数LOCK_TIMEOUT返回当前会话的当前锁定超时设置（毫秒）返回当前会话的当前锁定超时设置（毫秒）1.2 1.2 定义局部变量并赋值定义局部变量并赋值 u声明局部变量声明局部变量DECLARE varname DataType DECLARE DeptNo NVARCHAR(10) u为局部变量赋值为局部变量赋值SET varname=VALUE 或或 SELECT varname=VALUESET

4、 DeptNo=D02 PRINT 错误号码错误号码+CONVERT(NVARCHAR(225),ERROR)1.2 1.2 定义局部变量并赋值定义局部变量并赋值u局部变量的使用局部变量的使用要求查询要求查询“陆逊陆逊”的账户余额信息的账户余额信息 USE BankGOdeclare accountId varchar(20)-声明变量声明变量select accountId = ACCOUNT_ID from dbo.ACCOUNTwhere ACCOUNT_NAME = 陆逊陆逊select BALANCE from ALL_PURPOSE_CARD whereACCOUNT_ID = a

5、ccountId GO使用变量和为使用变量和为变量赋值变量赋值1.3 GO1.3 GO语句语句全局变量的作用域全局变量的作用域GO局部变量的作用域局部变量的作用域GO整个整个DBMSGO语句分开的批语句分开的批处理语句块内处理语句块内uGOGO语句的作用语句的作用n等待等待GOGO语句前的代码执行完毕后，再执行语句前的代码执行完毕后，再执行GOGO后面的语句后面的语句n作为批处理语句的结束标记作为批处理语句的结束标记1.4 1.4 输出语句输出语句 u输出语句包括输出语句包括PRINTPRINT和和SELECTSELECTPRINT 局部变量或字符串局部变量或字符串PRINT 错误号码错误号码

6、+CONVERT(NVARCHAR(225),ERROR)SELECT 局部变量或全局变量局部变量或全局变量 AS 别名别名SELECT VERSION AS 版本号为：版本号为：2.T-SQL2.T-SQL中中的的运算符运算符 uT-SQLT-SQL中使用的运算符分为中使用的运算符分为7 7种种n算数运算符：加（算数运算符：加（+ +）、减（）、减（- -）、乘（）、乘（* *）、除（）、除（/ /）、模（）、模（% %） n逻辑运算符：逻辑运算符：ALLALL、ANYANY、EXISTSEXISTS、ININ、LIKELIKE、ANDAND、NOTNOT、OROR、BETWEEN BETW

7、EEN n赋值运算符：赋值运算符：= = n字符串运算符：字符串运算符：+ + n比较运算符：比较运算符：= =、 、 =、=、 n位运算符：位运算符：| |、& &、 n复合运算符：复合运算符：+=+=、-=-=、/=/=、%=%=、* *= = 2.1 T-SQL运算符概述运算符概述 2.1 T-SQL 2.1 T-SQL运算符概述运算符概述 u算数运算符示例算数运算符示例DECLARE OptNumber int=124PRINT 转换前：转换前：+CONVERT(VARCHAR(3),OptNumber)DECLARE Reverse VARCHAR(3)DECLARE Unit in

8、t=OptNumber % 10SET Reverse=CONVERT(VARCHAR(1),Unit)SET OptNumber=OptNumber/10SET Unit=OptNumber % 10SET Reverse=Reverse+CONVERT(VARCHAR(1),Unit)SET OptNumber=OptNumber/10PRINT 转换后：转换后：+Reverse+CONVERT(VARCHAR(1),OptNumber)2.2 2.2 逻辑运算符逻辑运算符uALLALLn用于比较特定值与结果集的所有值之间的逻辑关系，结果为布尔值。一般情用于比较特定值与结果集的所有值之间的

9、逻辑关系，结果为布尔值。一般情况下与比较运算符一起使用。况下与比较运算符一起使用。特定值特定值 =|!=|=|=|! ALL(SELECT语句语句) IF 5000 = ALL(SELECT BALANCE FROM ALL_PURPOSE_CARD)PRINT 所有账户余额都超过所有账户余额都超过5000ELSEPRINT 还有余额未超过还有余额未超过5000的用户的用户 2.2 2.2 逻辑运算符逻辑运算符uANYANYn用于比较特定值与结果集的所有值之间的逻辑关系，结果为布尔值。用于比较特定值与结果集的所有值之间的逻辑关系，结果为布尔值。 一般一般情况下与比较运算符一起使用。情况下与比较

10、运算符一起使用。特定值特定值 =|!=|=|=|! ANY(SELECT语句语句) IF 5000 300BEGININSERT INTOALL_CARD_EXCHANGE(CARD_ID,EXGET,BALANCE,EXTIME,EXADDRESS) VALUES(3,300,BALANCE-300,2011-12-18,ATM取款取款)UPDATE ALL_PURPOSE_CARD SET BALANCE = BALANCE-300 , LAST_USED_DATE = 2011-12-18 WHERE CARD_NO = 6225643823656648END 3.2 IF ELSE3.

11、2 IF ELSE条件语句条件语句 IF (条件条件)语句语句ELSE语句语句实现实现“陆逊陆逊”取款金额为取款金额为1500015000，判断余额是否大于取款金额，如果小于，判断余额是否大于取款金额，如果小于1500015000则显示余额不足。则显示余额不足。 DECLARE BALANCEDECLARE BALANCEFLOATFLOATSELECT BALANCE = BALANCE FROM ALL_PURPOSE_CARD WHERE CARD_NO = SELECT BALANCE = BALANCE FROM ALL_PURPOSE_CARD WHERE CARD_NO = 62

12、258877065490116225887706549011IF BALANCE 15000IF BALANCE 15000BEGINBEGININSERT INTO ALL_CARD_EXCHANGE(CARD_ID,EXGET,BALANCE,EXTIME,EXADDRESS)INSERT INTO ALL_CARD_EXCHANGE(CARD_ID,EXGET,BALANCE,EXTIME,EXADDRESS) VALUES(1, 15000,BALANCE-15000,2011-12-25,ATM VALUES(1, 15000,BALANCE-15000,2011-12-25,ATM

13、取款取款)UPDATE ALL_PURPOSE_CARD SET BALANCE = BALANCE-15000 WHERE CARD_NO = UPDATE ALL_PURPOSE_CARD SET BALANCE = BALANCE-15000 WHERE CARD_NO = 62258877065490116225887706549011ENDENDELSEELSEPRINT PRINT 余额不足余额不足!3.3 WHILE3.3 WHILE循环语句循环语句 uWHILEWHILE语句可以根据条件重复执行一条语句可以根据条件重复执行一条SQLSQL语句或一个语句块。语句或一个语句块。WH

14、ILEWHILE的条件的条件是布尔表达式，只要条件为是布尔表达式，只要条件为TRUETRUE，就会重复执行，就会重复执行WHILEWHILE块内的语句。在循环块内的语句。在循环过程中，可以与过程中，可以与CONTINUECONTINUE、BREAKBREAK关键字一起使用，控制语句的执行。关键字一起使用，控制语句的执行。 循环打印循环打印1-10declare nI intset nI = 1while nI 10breakprint nIset nI += 1ENDGO3.4 CASE3.4 CASE多分支语句多分支语句 CASEWHEN 条件1 THEN 结果1WHEN 条件2 THEN

15、结果2ELSE 其他结果ENDu当满足条件当满足条件1 1时，返回结果时，返回结果1 1；满足条件；满足条件2 2时，返回结果时，返回结果2 2；如果两个条；如果两个条件都不满足，则返回其他结果。件都不满足，则返回其他结果。ELSEELSE是可选的。是可选的。3.4 CASE3.4 CASE多分支语句多分支语句 u根据卡上的金额，评定用户类型，依次判定是否属于“VIP客户”、“重点客户”或“普通用户”。 SELECT CARD_NO,BALANCE,等级等级=CASE WHEN BALANCE 500000 THEN VIP客户客户WHEN BALANCE BETWEEN 100000 AND

16、 500000 THEN 重点客户重点客户ElSE 普通用户普通用户ENDFROM ALL_PURPOSE_CARD 本章总结本章总结u变量的使用是先声明、后赋值。其作用域有全局和局部两种。全局变量变量的使用是先声明、后赋值。其作用域有全局和局部两种。全局变量由系统提供，以由系统提供，以为前缀，局部变量以为前缀，局部变量以 为前缀，并且只在批处理块内有为前缀，并且只在批处理块内有效。效。u变量的赋值有两种方式：变量的赋值有两种方式：SETSET和和SELECTSELECT。SETSET赋值一般用于将已经给定的赋值一般用于将已经给定的值赋给变量；值赋给变量；SELECTSELECT赋值的数据来源

17、于查询。赋值的数据来源于查询。uT-SQLT-SQL输出结果有两种方式：输出结果有两种方式：PRINTPRINT语句和语句和SELECTSELECT语句。语句。PRINTPRINT语句输出的语句输出的变量要求是字符串类型的，如果不是，则需要使用变量要求是字符串类型的，如果不是，则需要使用CONVERTCONVERT函数进行转换。函数进行转换。SELECTSELECT通常用于输出局部变量或全局变量的值。通常用于输出局部变量或全局变量的值。uT-SQLT-SQL中使用的运算符有算术运算符、逻辑运算符、比较运算符、字符串中使用的运算符有算术运算符、逻辑运算符、比较运算符、字符串运算符和赋值运算符。运

18、算符和赋值运算符。u逻辑运算符有：逻辑运算符有：ALLALL、ANYANY、EXISTSEXISTS、ININ、LIKELIKE、ANDAND、NOTNOT、OROR、BETWEENBETWEEN等。等。uT-SQLT-SQL可以通过可以通过IF ELSEIF ELSE、WHILEWHILE、CASECASE、BREAKBREAK、CONTINUECONTINUE语句来控制程语句来控制程序流程。序流程。u批处理可以提高批处理可以提高T-SQLT-SQL程序的执行效率，批处理结束的标志是程序的执行效率，批处理结束的标志是GOGO。uBEGIN ENDBEGIN END表示一个语句块，其作用类似于

19、表示一个语句块，其作用类似于JavaJava中的中的。第三章第三章 深入深入SQL高级子查询高级子查询课程回顾课程回顾uT-SQL变量包括局部变量和全局变量变量包括局部变量和全局变量n局部变量由用户定义，以局部变量由用户定义，以为前缀。为前缀。n全局变量由系统提供，以全局变量由系统提供，以为前缀。用户只能获取全局变量的值，不能自己定为前缀。用户只能获取全局变量的值，不能自己定义，使用义，使用IF ELSE语句可以根据条件决定执行的语句可以根据条件决定执行的T-SQL语句。语句。u使用使用WHILEWHILE语句可以循环执行语句可以循环执行T-SQL语句。语句。u使用使用CASE WHEN TH

20、EN END多路分支能够依据多种条件选择执行的多路分支能够依据多种条件选择执行的T-SQL语句。语句。u在在T-SQL编程中，逻辑运算符编程中，逻辑运算符ALL、ANY与关系运算符可以一起实现特定值与关系运算符可以一起实现特定值与结果集的比较。与结果集的比较。u在在T-SQL编程中，逻辑运算符编程中，逻辑运算符EXISTS可以进行结果集的存在检查，通常与可以进行结果集的存在检查，通常与IF一起使用，进行存在测试。一起使用，进行存在测试。本章内容本章内容u简单子查询的用法简单子查询的用法u子查询与子查询与IN、ANY、ALLu子查询与子查询与EXISTSu相关子查询相关子查询u子查询和多表联接查

21、询子查询和多表联接查询1.1.简单子查询简单子查询 u基本子查询分为三类基本子查询分为三类n通过通过 IN 、ANY 、ALL 修改的比较运算符引入的列表上操作的子查询修改的比较运算符引入的列表上操作的子查询n通过未修改的比较运算符引入的必须返回单个值的子查询通过未修改的比较运算符引入的必须返回单个值的子查询n通过通过 EXISTS 引入的存在测试的子查询引入的存在测试的子查询1.1 1.1 子查询概述子查询概述u子查询是一个嵌套于子查询是一个嵌套于SELECT、INSERT、UPDATE 或或 DELETE 语句的语句的其他子查询中的查询。其他子查询中的查询。1.1 1.1 子查询概述子查询

22、概述 u子查询的简单使用子查询的简单使用任务：查询出比账号“6225887706549011”存款多的账户信息 1.1 1.1 子查询概述子查询概述u方法一：使用方法一：使用T-SQL中的变量中的变量 USE BankGOdeclare balance intselect balance = BAlance from ALL_PURPOSE_CARD where CARD_NO= 6225887706549011select * from ALL_PURPOSE_CARD where BALANCE balanceGO查询出账户查询出账户“622588770654901162258877065

23、49011”的余额的余额并将其保存到变量中并将其保存到变量中在查询中使在查询中使用变量用变量1.1 1.1 子查询概述子查询概述u方法二：使用子查询方法二：使用子查询select * from ALL_PURPOSE_CARD where BALANCE (select BAlance from ALL_PURPOSE_CARD where CARD_NO = 6225887706549011) 子查询：将查询嵌套在子查询：将查询嵌套在另一个查询之中另一个查询之中 数据库引擎将子查询作为虚表用于执行查询操作。子查询可作为联接语句数据库引擎将子查询作为虚表用于执行查询操作。子查询可作为联接语句中

24、的一个表，也可作为选择语句中的一个值。子查询的执行依赖于嵌套查中的一个表，也可作为选择语句中的一个值。子查询的执行依赖于嵌套查询。顺序从最内层开始，逐层向外执行，外层的嵌套查询可以访问内层嵌询。顺序从最内层开始，逐层向外执行，外层的嵌套查询可以访问内层嵌套查询的结果。套查询的结果。1.2 1.2 如何编写子查询如何编写子查询 u编写复杂的子查询（尤其是多层嵌套的子查询），最简单的方法是编写复杂的子查询（尤其是多层嵌套的子查询），最简单的方法是分解查询。从最内层的子查询开始分解，将嵌套的分解查询。从最内层的子查询开始分解，将嵌套的SQL语句拆分为语句拆分为独立的独立的SQL语句分别执行；当确认结

25、果无误后，再重新组合在一个语句分别执行；当确认结果无误后，再重新组合在一个语句中。语句中。 n任务任务从所有账户信息中查询出存款最高的交易明细从所有账户信息中查询出存款最高的交易明细 n问题分析问题分析查询涉及到两张数据表：查询涉及到两张数据表：ALL_PURPOSE_CARD和和ALL_CARD_EXCHANGE 1.2 1.2 如何编写子查询如何编写子查询u第一步第一步从从ALL_PURPOSE_CARD表中查询出最高的存款表中查询出最高的存款 SQL1= select MAX(BALANCE) 最高存款最高存款 from ALL_PURPOSE_CARD SQL1是伪代码，是伪代码，作用

26、类似于变量作用类似于变量1.2 1.2 如何编写子查询如何编写子查询u第二步第二步在第一步的基础上，进一步查找出存款最高的账户卡号在第一步的基础上，进一步查找出存款最高的账户卡号 ，将，将SQL1代入代入到到SQL2语句中。语句中。 SQL2= select Card_ID from ALL_PURPOSE_CARD where BALANCE = (SQL1) 1.2 1.2 如何编写子查询如何编写子查询u第三步第三步交易详情表交易详情表（ALL_CARD_EXCHANGE）需要得到的对应的卡需要得到的对应的卡号，可以在号，可以在SQL2中查询出来，将中查询出来，将SQL2代入代入SQL3语

27、句中。语句中。 SQL3= (select * from ALL_CARD_EXCHANGE where CARD_ID = (SQL2) 2.2.高级子查询高级子查询 2.1 2.1 使用使用IN、NOT IN的子查询的子查询uIN后面的子查询允许返回多行记录，用于从一个范围限制主查询的条件。后面的子查询允许返回多行记录，用于从一个范围限制主查询的条件。 select ACCOUNT_NAME from ACCOUNT where ACCOUNT_ID in ( select ACCOUNT_ID from ALL_PURPOSE_CARD whereCARD_NO in (select T

28、F_NO from TRANSFER)主查询主查询 WHERE 表达式表达式 IN (子查询）子查询）查找有转账历史的查找有转账历史的账户名称账户名称 2.1 2.1 使用使用IN、NOT IN的子查询的子查询 u使用使用NOT IN 查询信用卡交易记录中没有交易信息的信用卡信息查询信用卡交易记录中没有交易信息的信用卡信息 select * from CREDIT_CARD where CREDIT_CARD_ID not in ( select CREDIT_CARD_ID from EXCHANGE ) 2.2 2.2 使用使用EXISTS、NOT EXISTS的子查询的子查询 uEXIS

29、TS用于对子查询的查询结果进行存在测试。只要子查询的结果有一用于对子查询的查询结果进行存在测试。只要子查询的结果有一行或一行以上的数据，就返回真；否，则返回假。其结果只取决于是否会行或一行以上的数据，就返回真；否，则返回假。其结果只取决于是否会返回行。返回行。 在网银上查询在网银上查询“赵云赵云”的卡号（的卡号（6225546378534297）是否收到转账，如果收到）是否收到转账，如果收到则显示信息则显示信息“转账款已收到转账款已收到” 。if EXISTS (select * from TRANSFER where TF_NO = 6225546378534297)BEGINprint 转

30、账款已收到转账款已收到END2.2 2.2 使用使用EXISTS、NOT EXISTS的子查询的子查询 uNOT EXISTS用于对子查询的查询结果进行不存在测试，如果一行数据也用于对子查询的查询结果进行不存在测试，如果一行数据也不存在，则整个不存在，则整个NOT EXISTS( (子查询子查询) )返回真返回真。查询账户余额大于查询账户余额大于500000的用户是否拥有信用卡，如果没有则提示的用户是否拥有信用卡，如果没有则提示“你可以尽快申请我行你可以尽快申请我行白金信用卡白金信用卡” 。if NOT EXISTS (select * from ACCOUNT where ACCOUNT_I

31、D in (select ACCOUNT_ID from ALL_PURPOSE_CARD where BALANCE 500000) and ACCOUNT_ID in (select ACCOUNT_ID from CREDIT_CARD)BEGINprint 你可以尽快申请我行白金信用卡你可以尽快申请我行白金信用卡END 2.3 2.3 使用使用ALL的子查询的子查询 u通过比较运算符将一个表达式的值或列值与子查询返回的一列值中通过比较运算符将一个表达式的值或列值与子查询返回的一列值中的每一行进行比较，只要有一次比较的结果为的每一行进行比较，只要有一次比较的结果为FALSE，则，则ALL

32、ALL测试返测试返回回FALSE。 表达式或字段表达式或字段 比较运算符比较运算符 ALL（子查询）（子查询） select * from ALL_PURPOSE_CARD where ACCOUNT_ID ALL(select ACCOUNT_ID from CREDIT_CARD)查询使用一卡通表比查询使用一卡通表比信用卡表多的人数信信用卡表多的人数信息息 2.4 2.4 使用使用ANYANY的子查询的子查询 u按照比较运算符、表达式或字段对子查询的结果每一行进行一次计算和比较。只要有一次满足条件，那么 ANY 的结果就是真；当子查询每行结果与ANY前面的表达式或字段比较结果全为假时，则结

33、果为假 表达式或字段 比较运算符 ANY（子查询） select * from ALL_PURPOSE_CARD where ACCOUNT_ID ANY (select ACCOUNT_ID from CREDIT_CARD)查询一卡通中账户编号比信用卡账户编号大的人数 3. 3. 子查询的综合应用子查询的综合应用u案例：案例：Bank数据库中有一卡通表数据库中有一卡通表（ALL_PURPOSE_CARD）、一卡通交易、一卡通交易表表（ALL_CARD_EXCHANGE），），要求查询出交易次数最多的一卡通信息。要求查询出交易次数最多的一卡通信息。 3. 3. 子查询的综合应用子查询的综合应

34、用u分析分析n一卡通交易表中保存了账户的历史交易记录，所以应该首先使用一卡通交易表中保存了账户的历史交易记录，所以应该首先使用count函函数统计出每个账户的交易次数。数统计出每个账户的交易次数。n查询出账户卡号上的消费次数，但在一卡通交易表中只有账户的交易信息，查询出账户卡号上的消费次数，但在一卡通交易表中只有账户的交易信息，没有一卡通的信息，所以必须将过滤后的数据作为一个子查询（虚表）与没有一卡通的信息，所以必须将过滤后的数据作为一个子查询（虚表）与一卡通表进行相关子查询。一卡通表进行相关子查询。 3. 3. 子查询的综合应用子查询的综合应用u相关子查询相关子查询n第一步：第一步：统计出每一个账户卡号上的消费次数统计出每一个账户卡号上的消费次数 方法：使用统计函数，分组查询方法：使用统计函数，分组查询 select CARD_ID , COUNT(*) as 次数次数 from ALL_CARD_EXCHANGE group by CARD_ID 3. 3. 子查询的综合应用子查询的综合应用u相关子查询相关子查询n第二步：将过滤后的数据作为一个子查询（虚