第第3 3章章 Transact-SQL语言基础语言基础教学内容：教学内容：uT-SQL的基本数据类型的基本数据类型uT-SQL的语句结构的语句结构uT-SQL支持的流程支持的流程uT-SQL的常用函数的常用函数2009年3月 第1页3.1SQL概述概述StructuredQueryLanguage，结构化查，结构化查询语言询语言。SQL语言的功能包括语言的功能包括查询、操纵、查询、操纵、定义定义和和控制控制，是一个综合的、通用的关系，是一个综合的、通用的关系数据库语言，同时又是一种高度非过程化数据库语言，同时又是一种高度非过程化的语言，只要求用户指出做什么而不需要的语言，只要求用户指出做什么而不需要指出怎么做。指出怎么做。uSQL-89标准标准uSQL-92标准标准uSQL3标准标准2009年3月 第2页最早最早1986年年10月由美国月由美国ANSI公布。公布。SQL-89标准标准:1989年年4月，月，ISO提出。提出。SQL-92标准：标准：1992年年8月月ISO和和ANSI公布。公布。SQL-99标准：标准：SQL3。2009年3月 第3页3.1.2SQL语言的组成语言的组成数据定义语言（数据定义语言（DDL），实现定义、删除），实现定义、删除和修改数据库对象的功能。和修改数据库对象的功能。数据查询语言（数据查询语言（DQL），实现查询数据的），实现查询数据的功能。功能。数据操纵语言（数据操纵语言（DML）实现对数据库数据）实现对数据库数据的增加、删除和修改功能。的增加、删除和修改功能。数据控制语言（数据控制语言（DCL）；实现控制用户对）；实现控制用户对数据库的操作权限的功能。数据库的操作权限的功能。2009年3月 第4页常见常见SQL语句语句语句语句功能功能语句语句功能功能数据操作数据操作SELECT从数据库表中检索数据从数据库表中检索数据ALTERDOMAIN改变域定义改变域定义INSERT向数据库表中添加数据行向数据库表中添加数据行DROPDOMAIN从数据库中删除域从数据库中删除域DELETE从数据库表中删除数据行从数据库表中删除数据行数据控制数据控制UPDATE更新数据库表中的数据更新数据库表中的数据GRANT授予用户访问权限授予用户访问权限数据定义数据定义DENY拒绝用户访问拒绝用户访问CREATETABLE创建一个数据库表创建一个数据库表REVOKE解除用户访问权限解除用户访问权限DROPTABLE从数据库中删除表从数据库中删除表事务控制事务控制ALTERTABLE修改数据库表结构修改数据库表结构COMMIT结束当前事务结束当前事务CREATEVIEW创建一个视图创建一个视图ROLLBACK回滚当前事务回滚当前事务DROPVIEW从数据库中删除视图从数据库中删除视图SAVETRANSACTION在事务内设置保存点在事务内设置保存点CREATEINDEX为数据库表创建一个索引为数据库表创建一个索引程序化程序化SQLDROPINDEX从数据库中删除索引从数据库中删除索引DECLARE设定游标设定游标CREATEPROCEDURE创建一个存储过程创建一个存储过程OPEN打开一个游标打开一个游标DROPPROCEDURE从数据库中删除存储过程从数据库中删除存储过程FETCH检索一行查询结果检索一行查询结果CREATETRIGGER创建一个触发器创建一个触发器CLOSE关闭游标关闭游标DROPTRIGGER从数据库中删除触发器从数据库中删除触发器PREPARE为动态执行准备为动态执行准备SQL语句语句CREATEDOMAIN创建一个数据值域创建一个数据值域EXECUTE动态执行动态执行SQL语句语句2009年3月 第5页3.1.3SQL语句的结构语句的结构每条每条SQL语句均由一个谓词开始，该谓词语句均由一个谓词开始，该谓词描述这条语句要产生的动作描述这条语句要产生的动作。SELECT vToyName FROM Toys WHERE mToyRate 30表名表名字段名字段名谓词谓词常量常量2009年3月 第6页3.1.3SQL语句的结构语句的结构例例3-1：查询所有玩具的玩具号和玩具名：查询所有玩具的玩具号和玩具名USEToyUniverseSELECTcToyId,vToyNameFROMToys运行结果如下：运行结果如下：cToyIdvToyName-000001RobbytheWhale例例3-2：查询玩具表的所有数据：查询玩具表的所有数据USEToyUniverseSELECT*FROMToys2009年3月 第7页3.1.3SQL语句的结构语句的结构例例3-3：查询玩具表中玩具价格大于：查询玩具表中玩具价格大于30元的所有玩元的所有玩具，并按照升序排列具，并按照升序排列USEToyUniverseSELECTvToyName,mToyRateFROMToysWHEREmToyRate30ORDERBYmToyRate运行结果如下：运行结果如下：vToyNamemToyRate-WaterChannelSystem33.9900SuperDeluge35.99002009年3月 第8页3.2Transact-SQL数据类型数据类型Transact-SQL语言的分类语言的分类:u变量说明：变量说明：用来说明变量的命令。用来说明变量的命令。u数据定义语言（数据定义语言（DDL)：用来建立数据库、数据库对象和定义其：用来建立数据库、数据库对象和定义其列，大部分是以列，大部分是以CREATE开头的命令，如：开头的命令，如：CREATETABLE、CREATEVIEW、DROPTABLE等。等。u数据操纵语言（数据操纵语言（DML）：用来操纵数据库中的数据的命令，如：）：用来操纵数据库中的数据的命令，如：SELECT、INSERT、UPDATE、DELETE等。等。u数据控制语言（数据控制语言（DCL）：用来控制数据库组件的存取许可、存）：用来控制数据库组件的存取许可、存取权限等的命令。如取权限等的命令。如GRANT、REVOKE等。等。u流程控制语言（流程控制语言（FlowControlLanguage）：用于设计应用程）：用于设计应用程序的语句如序的语句如IFWHILECASE等。等。u内嵌函数：说明变量的命令。内嵌函数：说明变量的命令。u其它命令：嵌于命令中使用的标准函数。其它命令：嵌于命令中使用的标准函数。2009年3月 第9页3.2Transact-SQL数据类型数据类型SQL Server 2000SQL Server 2000提供的数据提供的数据类类型分型分类类分分类类数据数据类类型型整数数据整数数据类类型型INTINT或或INTEGERINTEGER、SMALLINTSMALLINT、TINYINTTINYINT、BIGINTBIGINT浮点数据浮点数据类类型型REALREAL、FLOATFLOAT、DECIMALDECIMAL、NUMERICNUMERIC二二进进制数据制数据类类型型BINARYBINARY、VARBINARYVARBINARY逻辑逻辑数据数据类类型型BITBIT字符数据字符数据类类型型CHARCHAR、NCHARNCHAR、VARCHATVARCHAT、NVARCHARNVARCHAR文本和文本和图图形数据形数据类类型型TEXTTEXT、NTEXTNTEXT、IMAGEIMAGE日期和日期和时间时间数据数据类类型型DATETIMEDATETIME、SMALLDATETIMESMALLDATETIME货币货币数据数据类类型型MONEYMONEY、SMALLMONEYSMALLMONEY特定数据特定数据类类型型TIMESTAMPTIMESTAMP、UNIQUEIDENTIFIERUNIQUEIDENTIFIER用用户户自定自定义义数据数据类类型型SYSNAMESYSNAME新数据新数据类类型型SQL_VARIANTSQL_VARIANT、TABLETABLE2009年3月 第10页3.2.5字符数据类型字符数据类型CHAR（n）与）与VARCHAR（n）：）：un的取值为的取值为1到到8000；uCHAR（n），若输入数据的字符数小于），若输入数据的字符数小于n，则系统自，则系统自动在其后添加空格来填满设定好的空间。若输入的数动在其后添加空格来填满设定好的空间。若输入的数据过长，将会截掉其超出部分。据过长，将会截掉其超出部分。uVARCHAR（n），），VARCHAR数据类型具有变动长数据类型具有变动长度的特性，因为度的特性，因为VARCHAR数据类型的存储长度为实数据类型的存储长度为实际数值长度，若输入数据的字符数小于际数值长度，若输入数据的字符数小于n，则系统不，则系统不会在其后添加空格来填满设定好的空间。会在其后添加空格来填满设定好的空间。uCHAR数据类型长度固定，它比数据类型长度固定，它比VARCHAR类型的类型的处理速度快。处理速度快。2009年3月 第11页3.2.11新数据类型新数据类型BIGINTu-263263-1SQL_VARIANTu可以存储除文本、图形数据（可以存储除文本、图形数据（TEXT、NTEXT、IMAGE）和）和TIMESTAMP类型数据外的其它任何合法类型数据外的其它任何合法的的SQLServer数据数据TABLEuTABLE数据类型用于存储对表或视图处理后的结果数据类型用于存储对表或视图处理后的结果集。这一新类型使得变量可以存储一个表，从而使函集。这一新类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。数或过程返回查询结果更加方便、快捷。2009年3月 第12页3.3Transact-SQL变量变量局部变量（局部变量（LocalVariable）全局变量（全局变量（GlobalVariable）2009年3月 第13页3.3.1局部变量局部变量局部变量是用户可自定义的变量，它的作用范围局部变量是用户可自定义的变量，它的作用范围仅在程序内部。仅在程序内部。uDECLARE变量名变量名变量类型变量类型变量名变量名变量类型变量类型nSELECT局部变量局部变量=变量值变量值nSET局部变量局部变量=变量值变量值例例3-5：声明一个长度为：声明一个长度为10个字符变量个字符变量“id”并赋并赋值值DECLAREidchar（10）SELECTid=10010001注意：可以在注意：可以在Select命令查询数据时，在命令查询数据时，在Select命令中命令中直接将列值赋给变量直接将列值赋给变量2009年3月 第14页3.3.1局部变量局部变量例例3-6:查询编号为查询编号为“000001”的玩具名和价格，将其分别赋予变量的玩具名和价格，将其分别赋予变量ToyName和和Price。USEToyUniverseDECLAREToyNameVARCHAR(30)DECLAREPriceMONEYSELECTToyName=vToyName,Price=mToyRateFROMToysWHEREcToyId=000001SELECTToyNameASToyName,PriceASToyRate运行结果如下：运行结果如下：ToyNameToyRate-RobbytheWhale8.9900注意：数据库语言和编程语言有一些关键字，为避免冲突和产生错注意：数据库语言和编程语言有一些关键字，为避免冲突和产生错误，在命令表、列、变量以及其它对象时应避免使用关键字。误，在命令表、列、变量以及其它对象时应避免使用关键字。2009年3月 第15页3.3.2全局变量全局变量全局变量是全局变量是SQLServer系统内部使用的变量，其作用范系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用全围并不局限于某一程序，而是任何程序均可随时调用全局变量通常存储一些局变量通常存储一些SQLServer的配置设定值和效能统的配置设定值和效能统计数据。用户可在程序中用全局变量来测试系统的设定计数据。用户可在程序中用全局变量来测试系统的设定值或值或Transact-SQL命令执行后的状态值。命令执行后的状态值。注意：全局变量不是由用户的程序定义的，它们是在服注意：全局变量不是由用户的程序定义的，它们是在服务器级定义的。只能使用预先说明及定义的变局变量。务器级定义的。只能使用预先说明及定义的变局变量。引用全局变量时，必须以引用全局变量时，必须以“”开头。局部变量的名称开头。局部变量的名称不能与全局变量的名称相同、否则会在应用中出错。例不能与全局变量的名称相同、否则会在应用中出错。例如：如：SELECTServerName显示服务器名。显示服务器名。2009年3月 第16页3.4Transact-SQL编程基础编程基础注释符注释符u-：用于单行注释；：用于单行注释；u/*/：标识多行文字为注释：标识多行文字为注释运算符运算符u算术运算符算术运算符：+/u比较运算符比较运算符：!=u逻辑运算符：逻辑运算符：AND、OR、NOTu位运算符位运算符：&|u连接运算符：连接运算符：+2009年3月 第17页例例4-8：USEToyUniverseSELECT价格最高的玩具是：价格最高的玩具是：+vToyName+价格为：价格为：+CONVERT(VARCHAR(10),mToyRate)FROMToysWHEREmToyRate=(SELECTMAX(mToyRate)FROMToys)运行结果如下：运行结果如下：价格最高的玩具是：价格最高的玩具是：FlowerLovingDoll价格为：价格为：49.99（所影响的行数为（所影响的行数为1行）行）2009年3月 第18页3.4.3通配符通配符Transact-SQL的通配符的通配符通配符通配符功功能能实实例例%代表零个或多个字符代表零个或多个字符ab%，ab后可接任意字后可接任意字符串符串_(下划线下划线)代表一个字符代表一个字符a_b，a与与b之间可之间可以有一个字符以有一个字符表示在某一范围的字符表示在某一范围的字符0-9，0到到9之间的字符之间的字符表示不在某一范围的字表示不在某一范围的字符符0-9，不在，不在0到到9之间的字符之间的字符2009年3月 第19页3.5Transact-SQL流程控制语句流程控制语句1.IFELSE2.BEGINEND3.CASE4.WHILECONTINUEBREAK5.WAITFOR6.GOTO7.RETURN2009年3月 第20页3.5.1IFELSE语法如下：语法如下：IFELSE条件表达式条件表达式其中其中可以是各种表达式的组合，但表达式可以是各种表达式的组合，但表达式的值必须是逻辑值的值必须是逻辑值“真真”或或“假假”。ELSE子句是可选子句是可选的，最简单的的，最简单的IF语句没有语句没有ELSE子句部分。子句部分。IFELSE用用来判断当某一条件成立时执行某段程序，条件不成立时来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。如果不使用程序块，执行另一段程序。如果不使用程序块，IF或或ELSE只能执只能执行一条命令。行一条命令。IFELSE可以进行嵌套。可以进行嵌套。2009年3月 第21页例例4-9DECLARExINT,yINT,zINTSELECTx=1,y=2,z=3IFxyPRINTxy-打印字符串打印字符串xyELSEIFyzPRINTyzELSEPRINTzy运行结果如下运行结果如下zy注意：在注意：在Transact-SQL中最多可嵌套中最多可嵌套32级。级。2009年3月 第22页3.5.2BEGINEND语法如下：语法如下：BEGINENDBEGINEND用来设定一个程序块，将在用来设定一个程序块，将在BEGINEND内的所有程序视为一个单元执行内的所有程序视为一个单元执行BEGINEND经常在条件语句，如经常在条件语句，如IFELSE中使用。中使用。在在BEGINEND中可嵌套另外的中可嵌套另外的BEGINEND来定义来定义另一程序块。另一程序块。2009年3月 第23页3.5.3CASECASE语句的两种格式语句的两种格式格格式式1格格式式2CASECASEWHENTHENWHEN THEN WHENTHENWHEN THEN ELSEELSEENDEND2009年3月 第24页例例4-10调整玩具价格，原价格小于调整玩具价格，原价格小于10元上调元上调8%，原价，原价格大于于格大于于30元上调元上调6%，其它上调，其它上调7%。USEToyUniverseUPDATEToysSETmToyRate=CASEWHENmToyRate30THENmToyRate*1.06ELSEmToyRate*1.07END2009年3月 第25页3.5.4WHILECONTINUEBREAK语法：语法：WHILEBEGINBREAKCONTINUE命令行或程序块命令行或程序块ENDWHILE命令在设定的条件成立时会重复执行命令行或程序块。命令在设定的条件成立时会重复执行命令行或程序块。CONTINUE命令可以让程序跳过命令可以让程序跳过CONTINUE命令之后的语句，回到命令之后的语句，回到WHILE循环的第一行命令。循环的第一行命令。BREAK命令则让程序完全跳出循环，结束命令则让程序完全跳出循环，结束WHILE命令的执行。命令的执行。WHILE语句也可以嵌套。语句也可以嵌套。2009年3月 第26页例例4-11：DECLARExINT,yINT,cINTSELECTx=1,y=1WHILEx3BEGINPRINTx-打印变量打印变量x的值的值WHILEy3BEGINSELECTc=100*x+yPRINTc-打印变量打印变量c的值的值SELECTy=y+1ENDSELECTx=x+1SELECTy=1END运行结果如下：运行结果如下：1 10110222012022009年3月 第27页3.5.5WAITFOR语法如下语法如下:WAITFORDELAY|TIME|ERROREXIT|PROCESSEXIT|MIRROREXITWAITFOR命令用来暂时停止程序执行，直到所设定的命令用来暂时停止程序执行，直到所设定的等待时间已过或所设定的时间已到才继续往下执行。其等待时间已过或所设定的时间已到才继续往下执行。其中中时间时间必须为必须为DATETIME类型的数据，如：类型的数据，如：11:15:27，但不能包括日期各关键字含义如下：，但不能包括日期各关键字含义如下：nDELAY用来设定等待的时间最多可达用来设定等待的时间最多可达24小时；小时；nTIME用来设定等待结束的时间点；用来设定等待结束的时间点；nERROREXIT直到处理非正常中断；直到处理非正常中断；nPROCESSEXIT直到处理正常或非正常中断；直到处理正常或非正常中断；nMIRROREXIT直到镜像设备失败。直到镜像设备失败。2009年3月 第28页例例4-12/例例4-13等待等待1小时小时2分零分零3秒后才执行秒后才执行SELECT语句语句WAITFORDELAY01:02:03SELECT*FROMToys等到晚上等到晚上11点零点零8分后才执行分后才执行SELECT语语句句WAITFORTIME23:08:00SELECT*FROMToys2009年3月 第29页3.5.6GOTO语法如下：语法如下：GOTO标识符标识符uGOTO命令用来改变程序执行的流程，使程命令用来改变程序执行的流程，使程序跳到标有标识符的指定的程序行再继续往序跳到标有标识符的指定的程序行再继续往下执行。作为跳转目标的标识符可为数字与下执行。作为跳转目标的标识符可为数字与字符的组合，但必须以字符的组合，但必须以“：”结尾，如结尾，如12：或或a_1：。在。在GOTO命令行，标识符命令行，标识符后不必跟后不必跟“：”。2009年3月 第30页例例4-14分行打印字符分行打印字符1、2、3、4、5。DECLARExINTSELECTx=1lab_1: PRINTxSELECTx=x+1WHILEx6GOTOlab_12009年3月 第31页3.5.7 RETURN语法如下：语法如下：RETURN整数值整数值uRETURN命令用于结束当前程序的执行，返命令用于结束当前程序的执行，返回到上一个调用它的程序或其它程序。在括回到上一个调用它的程序或其它程序。在括号内可指定一个返回值号内可指定一个返回值2009年3月 第32页例例4-15USEpubsGOCREATEPROCEDUREcheckstateparamvarchar(11)ASIF(SELECTstateFROMauthorsWHEREau_id=param)=CARETURN1ELSERETURN2GODECLAREreturn_statusintEXECreturn_status=checkstate172-32-1176SELECTReturnStatus=return_status2009年3月 第33页3.6 小 结Transact-SQL支持的数据类型；支持的数据类型；Transact-SQL局部变量和全局变量局部变量和全局变量Transact-SQL编程的基础知识，如注释编程的基础知识，如注释符、运算符和通配符；符、运算符和通配符；Transact-SQL的流程控制语句的流程控制语句uIFELSEuBEGINENDuWHILE)uCASE2009年3月 第34页思考题思考题在查询分析器中编写实现如下功能的脚本。在查询分析器中编写实现如下功能的脚本。声明两个整型的局部变量：声明两个整型的局部变量：i1和和i2，对对i1赋初值：赋初值：10，i2的值为：的值为：i1乘以乘以5，再显示，再显示i2的结果值。的结果值。用用WHILE语句实现计算语句实现计算10000减减1、减、减2、减减3，一直减到，一直减到50的结果，并显示最的结果，并显示最终结果。终结果。2009年3月 第35页思考题思考题用用CASE语句实现下述功能：语句实现下述功能：声明变量声明变量x，y为字符型，长度均为为字符型，长度均为6，为，为x赋初值赋初值abc，分情况判断：分情况判断：当当xa时，时，yabc：+1当当xb时，时，ybc：+2当当xabc时，时，yabc：+3否则，否则，yno声明变量声明变量i为整型，为整型，s为字符型为字符型,长度为长度为6,为为i赋初始值赋初始值85，分情，分情况判断：况判断：当当i在在90到到100范围内时，范围内时，s优优当当i在在80到到89范围内时，范围内时，s良良当当i在在70到到79范围内时，范围内时，s中中当当i在在60到到69范围内时，范围内时，s及格及格其他：其他：s不及格不及格2009年3月 第36页