如何使用T-SQL编程

第二章使用T-SQL编程课程回顾数据库设计的四个步骤收集信息标识实体标识实体的属性标识实体之间的关系E-R图中矩形表示实体，椭圆表示属性，菱形表示实体之间的关系。实体之间的映射基数有四种：1：1、1：N、N：1、M：N数据库规范化设计使用三大范式理论1NF：要求保证列的原子性，列不可再分2NF：要求保证所有的列都必须依赖于主键3NF：要求保证所有的列都必须直接依赖于主键，不能传递函数依赖本章内容T-SQL中的变量T-SQL中的运算符T-SQL中的批处理语句T-SQL中的流程控制语句本章目标掌握变量的作用域和使用掌握常用的运算符掌握T-SQL流程控制语句1.T-SQL中的变量T-SQL中的变量分为：局部变量局部变量必须以标记@作为前缀，如@num局部变量的使用是先声明，再赋值全局变量全局变量必须以标记@@作为前缀，如@@version全局变量由系统定义和维护，只能读取，不能修改全局变量的值1.1T-SQL变量概述1.1T-SQL变量概述全局变量变量名作用@@ERROR返回执行的上一个语句的错误号@@IDENTITY返回最后插入的标识值@@MAX_CONNECTIONS返回允许同时进行的最大用户连接数@@ROWCOUNT返回受上一语句影响的行数@@SERVERNAME返回运行SQLServer的本地服务器的名称@@SERVICENAME返回SQLServer正在其下运行的注册表项的名称@@TRANCOUNT返回当前连接的活动事务数@@LOCK_TIMEOUT返回当前会话的当前锁定超时设置（毫秒）1.2定义局部变量并赋值声明局部变量DECLARE@varnameDataTypeDECLARE@DeptNoNVARCHAR(10)为局部变量赋值SET@varname=VALUE或SELECT@varname=VALUESET@DeptNo='D02'PRINT'错误号码'+CONVERT(NVARCHAR(225),@@ERROR)1.2定义局部变量并赋值局部变量的使用要求查询“陆逊”的账户余额信息USEBankGOdeclare@accountIdvarchar(20)--声明变量select@accountId=ACCOUNT_IDfromdbo.ACCOUNTwhereACCOUNT_NAME=‘陆逊’selectBALANCEfromALL_PURPOSE_CARDwhereACCOUNT_ID=@accountIdGO使用变量和为变量赋值1.3GO语句全局变量的作用域GO局部变量的作用域GO整个DBMSGO语句分开的批处理语句块内GO语句的作用等待GO语句前的代码执行完毕后，再执行GO后面的语句作为批处理语句的结束标记1.4输出语句输出语句包括PRINT和SELECTPRINT局部变量或字符串PRINT'错误号码'+CONVERT(NVARCHAR(225),@@ERROR)SELECT局部变量或全局变量[AS别名]SELECT@@VERSIONAS'版本号为：'小结1局部变量的声明使用什么关键字？局部变量和全局变量有什么区别？T-SQL局部变量的方式有哪两种？它们的区别是什么？2.T-SQL中的运算符T-SQL中使用的运算符分为7种算数运算符：加（+）、减（-）、乘（*）、除（/）、模（%）逻辑运算符：ALL、ANY、EXISTS、IN、LIKE、AND、NOT、OR、BETWEEN赋值运算符：=字符串运算符：+比较运算符：=、、、=、=、位运算符：|、&、^复合运算符：+=、-=、/=、%=、*=2.1T-SQL运算符概述2.1T-SQL运算符概述算数运算符示例DECLARE@OptNumberint=124PRINT'转换前：'+CONVERT(VARCHAR(3),@OptNumber)DECLARE@ReverseVARCHAR(3)DECLARE@Unitint=@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逻辑运算符ALL用于比较特定值与结果集的所有值之间的逻辑关系，结果为布尔值。一般情况下与比较运算符一起使用。特定值{=||!=|||=|=|!|!}ALL(SELECT语句)IF5000=ALL(SELECTBALANCEFROMALL_PURPOSE_CARD)PRINT'所有账户余额都超过5000'ELSEPRINT'还有余额未超过5000的用户'2.2逻辑运算符ANY用于比较特定值与结果集的所有值之间的逻辑关系，结果为布尔值。一般情况下与比较运算符一起使用。特定值{=||!=|||=|=|!|!}ANY(SELECT语句)IF5000=ANY(SELECTBALANCEFROMALL_PURPOSE_CARD)PRINT'已经有用户的余额超过5000'ELSEPRINT'没有一个用户的余额超过5000'2.2逻辑运算符EXISTSEXISTS测试用于判断查询的结果集是否有指定数据存在，如果有，则返回TRUE；否，则返回FALSE。--sysObjects是系统表;Object_Id和ObjectProperty都是系统函数IFEXISTS(SELECT*FROMdbo.sysObjectsWHEREid=Object_Id(N'ACCOUNT')andObjectProperty(id,N'IsUserTable')=1)PRINT'ACCOUNT是表'ELSEPRINT'ACCOUNT不是表'小结2逻辑运算符ALL、ANY、EXISTS的区别是什么？NOTEXISTS与NOTIN的区别是什么？3.T-SQL中的流程控制在没有流程控制语句的情况下，SQL语句只能顺序执行。流程控制语句是结构化程序设计的保障，为编写复杂的SQL结构化程序提供了支持。在T-SQL中，流程控制主要指一些关键字的使用，如IFELSE、WHILE、CASEWHENTHENEND、RETURN、BREAK、BEGINEND、GOTO。流程控制语句支持嵌套，但一个语句不能跨多个批处理用户定义的函数和存储过程。3.1BEGINEND语句BEGINEND语句可以将多个SQL语句限制在其中，作为一个逻辑执行块，它至少包含一条SQL语句，否则将出错。实现“孙尚香”取出金额300元，需要判断“孙尚香”的余额是否大于300，如果大于300则可以取钱，同时，账户余额也要减去300元。DECLARE@BALANCEFLOATSELECT@BALANCE=BALANCEFROMALL_PURPOSE_CARDWHERECARD_NO='6225643823656648'IF@BALANCE300BEGININSERTINTOALL_CARD_EXCHANGE(CARD_ID,EXGET,BALANCE,EXTIME,EXADDRESS)VALUES(3,300,@BALANCE-300,'2011-12-18','ATM取款')UPDATEALL_PURPOSE_CARDSETBALANCE=@BALANCE-300,LAST_USED_DATE='2011-12-18'WHERECARD_NO='6225643823656648‘END3.2IFELSE条件语句IF(条件)语句ELSE语句实现“陆逊”取款金额为15000，判断余额是否大于取款金额，如果小于15000则显示余额不足。DECLARE@BALANCEFLOATSELECT@BALANCE=BALANCEFROMALL_PURPOSE_CARDWHERECARD_NO='6225887706549011'IF@BALANCE15000BEGININSERTINTOALL_CARD_EXCHANGE(CARD_ID,EXGET,BALANCE,EXTIME,EXADDRESS)VALUES(1,15000,@BALANCE-15000,'2011-12-25','ATM取款')UPDATEALL_PURPOSE_CARDSETBALANCE=@BALANCE-15000WHERECARD_NO='6225887706549011'ENDELSEPRINT'余额不足!'3.3WHILE循环语句WHILE语句可以根据条件重复执行一条SQL语句或一个语句块。WHILE的条件是布尔表达式，只要条件为TRUE，就会重复执行WHILE块内的语句。在循环过程中，可以与CONTINUE、BREAK关键字一起使用，控制语句的执行。循环打印1-10declare@nIintset@nI=1while@nI100BEGINif@nI10breakprint@nIset@nI+=1ENDGO3.4CASE多分支语句CASEWHEN条件1THEN结果1WHEN条件2THEN结果2[ELSE其他结果]END当满足条件1时，返回结果1；满足条件2时，返回结果2；如果两个条件都不满足，则返回其他结果。ELSE是可选的。3.4CASE多分支语句根据卡上的金额，评定用户类型，依次判定是否属于“VIP客户”、“重点客户”或“普通用户”。SELECTCARD_NO,BALANCE,等级=CASEWHENBALANCE500000THEN'VIP客户'WHENBALANCEBETWEEN100000AND500000THEN'重点客户'ElSE'普通用户'ENDFROMALL_PURPOSE_CARD小结3如何使用CASE语句？本章总结变量的使用是先声明、后赋值。其作用域有全局和局部两种。全局变量由系统提供，以@@为前缀，局部变量以@为前缀，并且只在批处理块内有效。变量的赋值有两种方式：SET和SELECT。SET赋值一般用于将已经给定的值赋给变量；SELECT赋值的数据来源于查询。T-SQL输出结果有两种方式：PRINT语句和SELECT语句。PRINT语句输出的变量要求是字符串类型的，如果不是，则需要使用CONVERT函数进行转换。SELECT通常用于输出局部变量或全局变量的值。T-SQL中使用的运算符有算术运算符、逻辑运算符、比较运算符、字符串运算符和赋值运算符。逻辑运算符有：ALL、ANY、EXISTS、IN、LIKE、AND、NOT、OR、BETWEEN等。T-SQL可以通过IFELSE、WHILE、CASE、BREAK、CONTINUE语句来控制程序流程。批处理可以提高T-SQL程序的执行效率，批处理结束的标志是GO。BEGINEND表示一个语句块，其作用类似于Java中的{}。