SQLServer数据库技术-05T-SQL

1、SQL Server数据库技术T-SQL2T-SQLT-SQL概述注释批处理数据类型及转换常量变量运算符流程控制常用函数T-SQL概述概述T-SQL是Transact-SQL的简写，是使用SQL Server的核心，所有与SQL Server实例通信的应用程序，都是通过发送T-SQL语句到服务器来完成对数据库的操作T-SQL是在SQL上发展而来的，T-SQL在SQL的基础上添加了流程控制，是SQL语言的扩展SQL是几乎所有的关系型数据库都支持的语言，而T-SQL是Microsoft SQL Server支持的语言T-SQL 提供标准 SQL的DDL 和 DML 功能，加上延伸的函数、系统预存程

2、序以及程式设计结构(例如 IF 和 WHILE)让程式设计更有弹性T-SQL的语法约定的语法约定约定说明大写T-SQL的关键字斜体由用户提供的参数粗体数据库名、表名、列名、索引名、存储过程、实用工具、数据类型名以及必须按显示的原样键入的文本_(下划线)默认值|(竖线)“或”，用来分隔括号或大括号中的语法项，只能选择其中一个(方括号)可选项，使用时不要输入方括号(大括号)必选项，使用时不要输入大括号,n表示前面的项可以重复n次，每一项由逗号分隔n表示前面的项可以重复n次，每一项由空格分隔;表示T-SQL终止的终止符，是可选的，使用时不要输入方括号批处理批处理批处理批处理组成一批组成一批作为作为单

3、个单个执行计划执行计划进行编译进行编译输出结果输出结果批处理示例批处理示例SELECT * FROM CategoriesWHERE CategoryName = N图书UPDATE CategoriesSET Description = N计算机、时尚生活等图书WHERE CategoryName = N图书GOSELECT * FROM CategoriesWHERE CategoryName = N图书GOGO标志着批处理的结束GO标志着批处理的结束GO标志着批处理的结束GO标志着批处理的结束GO标志着批处理的结束注释注释增强程序的可读性- 注释 双连字符-注释的有效范围只到该行结束。-

4、查看插入记录的内容SELECT * FROM 类别 WHERE 类别名称 = N图书/*/注释注释内容从/*开始，到*/结束，可跨越多行注意：完整的注释必须包含在一个批处理中数据类型数据类型精确数字型近似数字型日期时间型字符串型Unicode字符串型二进制字符串型其他数据类型用户自定义型数据类型转换数据类型转换CastConvert隐式数据类型转换数据类型转换数据类型转换-CastCAST ( expression AS data_type (length ) )Expression为任何有效的表达式data_type为要转换的数据类型length为数据类型的长度，一般只针对nchar、nva

5、rchar、char、varchar、binary和varbinary这几种数据类型使用，是可选参数例：转换单价数据查看产品及单价，并在一列中显示出来SELECT ProductName+ 的单价为： + CAST(UnitPrice AS VARCHAR(10) + 元AS 产品介绍FROM Products数据类型转换数据类型转换-ConvertCONVERT ( data_type ( length ) , expression , style )Data_type为要转换的数据类型Length为数据类型的长度Expression为任何有效的表达式Style是样式一般用于讲datetim

6、e或smalldatetime数据类型转换为字符数据的日期格式的样式，或者用于将float/real/money/smallmoney数据类型转换为字符数据如果Style为Null，则返回的结果也为Null例：使用Convert转换列查看订单的订购日期、到货日期和发货日期SELECT OrderID, CONVERT(varchar(20),OrderDate,1) AS 订购日期, CONVERT(varchar(20),RequiredDate,102) AS 到货日期, CONVERT(varchar(20),ShippedDate,103) AS 发货日期FROM OrdersStyl

7、e代码说明代码说明不带世纪位数带世纪位数标准输入或输出样式-0或100默认设置mon dd yyyy hh:miAM(或PM)1101美国mm/dd/yyyy2102ANSIyy.mm.dd3103英国、法国dd/mm/yy4104德国dd.mm.yy5105意大利dd-mm-yy6106-dd mon yy7107-Mon dd,yy8108-Hh:mm:ss-9或109默认设置+毫秒Mon dd yyyy hh:mi:ss:mmmAM(或PM)10110美国Mm-dd-yy11111日本Yy/mm/dd12112ISOYymmdd-13或113欧洲默认设置+毫秒Dd mon yyyy hh

8、:mm:ss:mmm(24h)14114-Hh:mi:ss:mmmm(24h)-20或120ODBC规范Yyyy-mm-dd hh:mi:ss(24h)-21或121带毫秒的ODBC规范Yyyy-mm-dd hh:mi:ss.mmm(24h)隐式数据类型转换隐式数据类型转换例：隐私转换数据类型查看“Products”表中的产品库存量所值的资金SELECT ProductName As 产品名称,UnitPrice*UnitsInStock AS 积压资金FROM ProductsWHERE UnitPrice*UnitsInStock0“UnitPrice”是money数据类型，“UnitsI

9、nStock”是smallint数据类型，隐式转换T-SQL运算符运算符算术运算符赋值运算符位运算符比较运算符逻辑运算符字符串串联运算符一元运算符算术运算符算术运算符对两个表达式执行数学运算，这两个表达式可以是精确数据类型或近似数字型“+”和“-”运算符也可以用datetime和smalldatetime值进行算术运算运算符说明+加法-减法*乘法/除法%取模赋值运算符赋值运算符=给变量赋值在列标题和定义列值的表达式之间建立关系位运算符位运算符用于在两个表达式之间按位进行逻辑运算，这两个表达式可以是整数或二进制数据类型运算符说明&按位进行逻辑与运算|按位进行逻辑或运算按位进行逻辑异或运算

10、，例如00=0,11=0,10=1比较运算符比较运算符用于除text/ntext/image数据类型的表达式运算符说明=等于大于=大于或等于=小于或等于不等于!=不等于!不大于逻辑运算符逻辑运算符逻辑运算符用于对某些条件进行判断，判断其为true或false，与比较运算符一样，返回布尔型数据类型运算符说明ALL如果参与运算的表达式都为True，则返回TrueAND如果两个布尔表达式都为True，则返回TrueANY如果参与运算的表达式中任何一个为True，则返回TrueBETWEEN如果操作数在该范围内，则返回TrueEXISTS如果子查询不为空，则返回TrueIN如果操作数等于表达式列表中的

11、一个，则返回TrueLIKE如果操作数与一种搜索模式匹配，则返回TrueNOT对该布尔运算值取反OR如果两个布尔表达式中的一个为True，则返回 TrueSOME如果参与运算的表达式中，有些为True，则返回True例：查看类别为Seafood和Produce的产品SELECT * FROM Products WHERE CategoryID = ANY ( SELECT CategoryID FROM Categories WHERE CategoryName= NProduce OR CategoryName= NSeafood )例：查询“Products”表中单价大于所有类别为“Sea

12、food”的产品单价的产品SELECT * FROM Products WHERE UnitPrice ALL ( SELECT UnitPrice FROM Products JOIN Categories ON Products.CategoryID = Categories.CategoryID WHERE CategoryName= NSeafood )字符串串联运算符字符串串联运算符+将字符串串联起来一元运算符一元运算符一元运算符只能对一个表达式进行操作运算符说明+数值为正-数值为负返回数字的非，也就是补码。例如1010的补码是0101运算符的优先级运算符的优先级优先级别运算符1(位

13、非)2*(乘) /(除) %(取模)3+(正) (负) +(加) (+ 连接) (减) &(位与) 4= = = != ! !5(位异或) |(位或)6NOT7AND8ALL ANY BETWEEN IN LIKE OR SOME9=(赋值)搞不清楚时，使用括号表示优先级常量常量字符串常量ABC Im back -当字符串中有单引号号，则用两个单引号来表示Unicode常量NABC -使用两个字节存储每个字符二进制常量0 xAEBit常量在T-SQL中可以用1或0表示True或False，如果使用一个大于1的数字来定义bit常量，那么这个数据会被强制转换为1Datetime常量Augu

14、st 3,2006Integer常量Decimal常量Float和real常量Money常量以货币符号开头的数值，如$12Uniqueidentifer常量表示GUID的字符串，可以使用字符或二进制字符串格式指定变量变量局部变量用户自定义的变量以开头全局变量由系统提供的只可使用，不可以自定义全局变量以开头局部变量局部变量存储数值型、字符型等数据，也可以存储函数或存储过程返回的值声明变量DECLARE local_variable AS data_type ,.nlocal_variable:局部变量的名称Data_type:局部变量的数据类型，但不能是text/ntext/image数据类型局

15、部变量局部变量变量赋值SET local_variable = valueSELECT local_variable = value显示变量SELECT local_variablePRINT local_variable局部变量局部变量例：定义局部变量定义局部变量并对其赋值，然后显示其内容DECLARE name varchar(20)DECLARE age int,sex bitSET name = 张三SET age = 20SELECT sex = 1SELECT nameSELECT ageSELECT sexPRINT namePRINT agePRINT sex局部变量局部变量例

16、：使用Select语句对变量赋值输出雇员ID为1的雇员姓名和出生日期DECLARE name varchar(20)DECLARE birthday datetimeSELECT name = LastName+ +FirstName,birthday=BirthDate FROM EmployeesWHERE EmployeeID = 1PRINT 雇员姓名：+namePRINT 雇员生日：+CONVERT(varchar(50),birthday,102)当select返回多个查询结果时，此时只会将select结果集中的最后一条记录的相应字段内容赋值给对应的局部变量中如果返回结果为空，则不

17、对局部变量赋值，局部变量的值保持不变局部变量局部变量局部变量的有效范围为当前批中，从Declare开始，到Go结束。如果没有GO语句，则有效范围可以扩展到所有代码结束DECLARE name varchar(20)DECLARE birthday datetimeSELECT name = LastName+ +FirstName,birthday=BirthDate FROM EmployeesWHERE EmployeeID = 1PRINT 雇员姓名：+namePRINT 雇员生日：+CONVERT(varchar(50),birthday,102)GOPRINT 雇员姓名：+nameP

18、RINT 雇员生日：+CONVERT(varchar(50),birthday,102)全局变量全局变量全局变量是由系统提供的，用于存储一些系统信息只可以使用全局变量，不可以自定义全局变量全局变量全局变量例：使用全局变量查看Select后的记录集里的记录数，并查看SQL Server自启动以来的连接数SELECT * FROM EmployeesPRINT 一共查询了+CAST(ROWCOUNT AS varchar(5)+条记录SELECT SQL Server启动以来尝试的连接数：+CAST(CONNECTIONS AS varchar(10)rowcount记录了上次运行T-SQL所影响

19、的记录数connections记录了SQL Server自上次启动以来尝试的连接数，无论连接是成功还是失败T-SQL常用全局变量常用全局变量全局变量说明LANGUAGE返回当前所用语言的名称VERSION返回当前SQL Server版本、处理器体系结构、生成日期和操作系统版本SERVERNAME返回运行SQL Server的本地服务器的名称SPID返回当前用户进程的会话IDTOTAL_ERRORS返回SQL Server自上次启动之后所遇到的磁盘写入错误数T-SQL的流程控制的流程控制IfWhileCaseGotoWaitforReturnBeginendBeginend通常用来表示一个语句块

20、，其中可以包含一组T-SQL语句，凡是在这个语句块里的所有代码，都是属于同一个流程控制Beginend例：Beginend语句与If语句搭配使用查看“Products”表里的”的单价是否低于20元，如果低于20元，查看其订购量use NorthwindDECLARE price moneyDECLARE productid intDECLARE count intSELECT price = UnitPrice, productid = ProductID FROM Products WHERE ProductName= NChangIF price$20 BEGIN PRINT Chang的

21、单价低于20元 SELECT count = sum(Order Details.quantity) FROM Orders JOIN Order Details ON Orders.OrderID = Order Details.OrderID WHERE Order Details.ProductID = productid PRINT 其订购量为：+ CAST(count AS varchar(5) ENDIfelseIF Boolean_expression sql_statement | statement_block ELSE sql_statement | statement_b

22、lock Ifelse例：查看“Products”表里“Chang”的单价是否低于8元。如果低于8元，查看其订购量，否则查看其库存量。USE NorthwindDECLARE price moneyDECLARE productid intDECLARE count intSELECT price = UnitPrice, productid = ProductID FROM Products WHERE ProductName= NChangIF price$20 BEGIN PRINT Chang的单价低于20元 SELECT count = sum(Order Details.quant

23、ity) FROM Orders JOIN Order Details ON Orders.OrderID = Order Details.OrderID WHERE Order Details.ProductID = productid PRINT 其订购量为： + CAST(count AS varchar(5) ENDELSE BEGIN PRINT Chang的单价高于20元 SELECT count = sum(UnitsInStock) FROM Products WHERE ProductID = productid PRINT 其库存量为： + CAST(count AS va

24、rchar(5) ENDWhile语句语句WHILE Boolean_expression sql_statement | statement_block BREAK sql_statement | statement_block CONTINUE sql_statement | statement_block 当Boolean_expression为True时，执行while语句块的代码，直道boolean_expression为false为止。如果要在中途终止循环，可以使用Break或Continue语句。Break语句用于跳出目前所执行的循环Continue语句用于中止执行代码，跳回到W

25、hile的判断语句重新进行条件判断，再根据判断结果决定是否进入循环。While语句语句例：输出产品编号为10以后的产品名USE NorthwindDECLARE id intDECLARE productname varchar(40)SET id = 1WHILE id10BEGIN SELECT productname = ProductName FROM Products WHERE ProductID = id PRINT productname SET id = id+1ENDWhile语句语句例：输出产品编号小于100并且为奇数的产品名称USE NorthwindDECLARE i

26、d intDECLARE productname varchar(40)DECLARE maxid intSELECT maxid = MAX(ProductID) FROM dbo.Products -查看产品表里最大的编号是多少PRINT 产品表里产品ID最大为 + CAST(maxid AS varchar(10) SET id = 0WHILE id maxid -如果不为1则判断产品编号是否超过产品表中最大编号 BREAK -如果超过产品表中最大编号则跳出整个循环 ELSE PRINT 没有产品ID号为“+CAST(id AS varchar(50)+”的记录 ENDENDCase语

27、句语句简单Case用于将某个表达式与一组简单的表达式进行比较以确定结果CASE input_expression WHEN when_expression THEN result_expression .n ELSE else_result_expression END 搜索Case用于计算一组布尔表示式以确定结果CASE WHEN Boolean_expression THEN result_expression .n ELSE else_result_expression END简单简单Case例：查看类别ID为2的类别名称USE NorthwindDECLARE categoryname

28、 varchar(15)DECLARE outstr varchar(100)SELECT categoryname = CategoryName FROM Categories WHERE CategoryID = 2SET outstr = CASE categoryname WHEN Beverages THEN 类别：饮料 WHEN Condiments THEN 类别：调味品 WHEN Dairy Products THEN 类别：日用品 WHEN Seafood THEN 类别：海鲜 ELSE 其他类别ENDPRINT outstr简单简单Case例：查询”Products”表中的

29、产品名称及类别名称USE NorthwindSELECT ProductName,CASE CategoryID WHEN 1 THEN 饮料WHEN 2 THEN 调味品WHEN 3 THEN 点心WHEN 4 THEN 日用品WHEN 5 THEN 谷类/麦片WHEN 6 THEN 肉/家禽WHEN 7 THEN 特制品WHEN 8 THEN 海鲜ELSE 其他类END AS 类别FROM Products搜索搜索Case搜索Case语句可以用于多条件的判断。用于多条件判断时，在Case关键字后不带任何表达式，而When之后必须为一个逻辑表达式，当表达式为真时返回Then之后的值USE N

30、orthwindDECLARE price moneyDECLARE returnstr varchar(50)SELECT price = UnitPrice FROM Products WHERE ProductName= NChangSET returnstr = CASE WHEN price$20 THEN Chang的单价低于20元 WHEN $20=price and price40 THEN Chang的单价在20元与40元之间 WHEN $40=price and price=80 THEN Chang的单价在40元与80元之间 ELSE Chang的单价大于80元ENDPR

31、INT returnstrGoto语句语句例：输出产品Chang的单价USE NorthwindDECLARE price moneyDECLARE returnstr varchar(50)SELECT price = UnitPriceFROM ProductsWHERE ProductName= NChangIF price$20GOTO print20 -跳转到标签print20IF $20=price and price40GOTO print40 -跳转到标签print40IF $40=price and price=80GOTO print80 -跳转到标签print80GOTO

32、 other -跳转到标签otherprint20:PRINT Chang的单价低于20元GOTO theEnd -跳转到标签theEndprint40:PRINT Chang的单价在20元与40元之间GOTO theEnd -跳转到标签theEndprint80:PRINT Chang的单价在40元与80元之间GOTO theEnd -跳转到标签theEndother:PRINT Chang的单价大于80元theEnd:Waitfor语句语句Waitfor语句用于延迟后续的代码执行，或等到指定的时间后再执行后续的代码WAITFOR DELAY time_to_pass | TIME time

33、_to_execute DELAY：指定延迟时间，在经过该时间之后再继续执行后续的代码，最长可以是24小时Time_to_pass:延迟的时间，必须是datetiem型的数据，但不能指定日期TIME：指定延迟到某个时间后再执行后即的代码Time_to_execute:指定要延迟的时间，必须是datetime型的数据，不能指定日期Waitfor语句语句例：先执行一个查询语句，然后等待10秒后再执行一个查询语句USE NorthwindSELECT UnitPrice FROM Products WHERE ProductName= NChangGOWAITFOR DELAY 00:00:10SELECT UnitPrice FROM Products WHERE ProductName= NChangGO例：先执行一个查询语句，然后在指