第63章TSQL语言补充

会计学1第63章TSQL语言补充T-SQL其它语言元素注释变量运算符函数流程控制语句第1页/共67页1.

注释

T-SQL提供两种类型的注释字符：一种是ANSI标准的注释符“--”，用于单行注释；另一种是与C语言相同的程序注释符号，即“/**/”，用于注释多行文字。注释是程序代码中不执行的文本字符串。第2页/共67页2.变量

1.变量变量名必须是一个合法的标识符。1）标识符(1)常规标识符不允许使用T-SQL的保留字，且不允许嵌入空格或其他特殊字符。(2)分隔标识符包含在双引号(“)或者方括号([])内的常规标识符或不符合常规标识符规则的标识符。若标识符中有空格，必须使用双引号或方括号。第3页/共67页（2）变量的分类Transact-SQL语言包括两种形式的变量：用户自己定义的局部变量；系统提供的全局变量。第4页/共67页1.局部变量

局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限在程序内部。

局部变量必须先定义才能被引用。使用DECLARE命令定义局部变量，引用局部变量时必须在其名称前加上标志“＠”。局部变量用于保存单个数据值，供控制流语句测试以及保存由存储过程返回的数据值等。例如，保存运算的中间结果，作为循环变量等。第5页/共67页1)局部变量的定义在批处理或过程中用DECLARE语句声明局部变量。语法格式：DECLARE

{@local_variabledata_type}

[,...n]2.局部变量的使用@local_variable

指定局部变量的名称;datatype设置局部变量的数据类型及大小。局部变量可以为除text，ntext，image类型以外的任何数据类型。所有局部变量在声明后均初始化为NULL，可以使用SELECT或SET设定相应的值。

第6页/共67页2)局部变量赋值（SET或SELECT语句）将DECLARE语句创建的局部变量设置为给定表达式的值。SET语句语法格式：

SET@local_variable＝expression【例1】创建局部变量@var1、@var2，并赋值，然后输出变量的值。DECLARE@var1,@var2char(20)SET@var1＝’中国’ /*一个SET语句只能给一个变量赋值*/SET@var2＝@var1+’是一个伟大的国家’SELECT@var1,@var2/*输出变量的值*/Go第7页/共67页【例3】使用查询给变量赋值。USEXSCJDECLARE@studentchar(8)SET@student=(SELECT姓名FROMXS)GO/*该语句若XS表中只有一条记录，可正常执行，否则将出错。*/【例2】创建一个名为sex的局部变量，并在SELECT语句中使用该局部变量查找表XS中所有女同学的学号、姓名。USEXSCJDECLARE@sexbitSET@sex=0SELECT学号,姓名

FROMXSWHERE性别=@sexGO第8页/共67页SELECT赋值语句的语法格式：SELECT{@local_variable=expression}[,…n]说明如下：1）SELECT语句通常用于将单个值返回到变量中，若有多个值时，将返回的最后一个值赋给变量；2）若无返回行，则变量将保留当前值；3）若expression不返回值，则变量设为NULL；4）一个SELECT语句可以初始化多个局部变量。SELECT语句也可用于局部变量的查看，格式如下：SELECT@local_variable

PRINT@local_variable

也可用于局部变量的查看第9页/共67页【例4】在XS表中不存在符合要求的结果，因此对该表的查询不返回结果，变量@var1将保留原值。USEXSCJDECLARE@var1nvarchar(30)SELECT@var1='刘丰'SELECT@var1=姓名/*将姓名列的值赋给变量*/FROMXSWHERE学号='64122312111'

SELECT@var1AS'NAME‘/*@var1为‘刘丰’*/第10页/共67页3）局部游标变量的定义与赋值(1)局部游标变量的定义语法格式：DECLARE{@cursor_variable_nameCURSOR}[,...n]CURSOR表示该变量为游标变量。(2)游标变量的使用步骤定义游标变量给游标变量赋值打开游标利用游标读取行(记录)使用结束后关闭游标删除游标的引用。第11页/共67页(3)局部游标变量的赋值：利用SET语句赋值语法格式：SET{@cursor_variable={@cursor_variable|/*将一个已存在的并且赋值的游标变量的值赋给另一局部游标变量*/cursor_name|/*将一个已申明的游标名赋给指定的局部游标变量*/{CURSOR子句}/*申明一个游标，同时将其赋给指定的局部游标变量*/}}第12页/共67页【例5】使用游标变量USEXSCJDECLARE@CursorVarCURSOR /*定义游标变量*/SET@CursorVar=CURSORSCROLLDYNAMIC/*给游标变量赋值*/FORSELECT学号,姓名

FROMXSWHERE姓名LIKE'王%'OPEN@CursorVar /*打开游标*/FETCHNEXTFROM@CursorVar/*游标指向第一行记录*/WHILE@@FETCH_STATUS=0/*@@FETCH_STATUS保存FETCH语句的执行状态，其值为0表示上一条执行成功；为-1表示FETCH语句失败或此行不在结果集中；为-2表示被提取的行不存在（已删除）。*/BEGINFETCHNEXTFROM@CursorVar /*通过游标读行记录*/ENDCLOSE@CursorVar/*关闭游标*/DEALLOCATE@CursorVar/*删除对游标的引用*/第13页/共67页3.全局变量的使用

全局变量由系统提供且预先声明，是SQLServer系统内部使用的变量，其作用范围并不仅限于某一程序，而是任何程序均可以随时调用。通常存储SQLServer的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。第14页/共67页使用全局变量时应该注意以下几点：

①全局变量不是由用户的程序定义的，而是在服务器级定义的。②用户只能使用预先定义的全局变量。③引用全局变量时，必须以标记符“@@”开头。④局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。第15页/共67页SQLServer支持的全局变量主要包括：

@@CONNECTIONS：返回自最近一次启动SQLServer以来连接或试图连接的次数。@@ERROR：返回最后执行SQL语句的错误代码。@@ROWCOUNT：返回上一次语句影响的数据行的行数。@@SERVERNAME：返回运行SQLServer的本地服务器的名称。@@VERSION：返回SQLServer当前安装的日期、版本和处理器类型。全局变量的查看语句同局部变量：SELECT@@variable第16页/共67页SQLServer2000的运算符主要有以下六大类：算术运算符位运算符比较运算符逻辑运算符字符串串联运算符赋值运算符2运算符与表达式

第17页/共67页1．算术运算符算术运算符有：+（加）、-（减）、*（乘）、/（除）和%（求模）五种运算。+(加)和–(减)运算符也可用于对datetime及smalldatetime值进行算术运算。【例6】求学生的年龄。USEXSCJSETNOCOUNTONDECLARE@startdatedatetimeSET@startdate=getdate()/*当前日期*/SELECT@startdate–出生时间AS年龄FROMXS第18页/共67页位运算符在两个表达式之间执行位操作，这两个表达式的类型可为整型或与整型兼容的数据类型（如：字符型等，但不能为image类型）。位运算符如表所示。

2．位运算符运算符运算规则&两个位均为1时，结果为1，否则为0|只要一个位为1，结果为1，否则为0^两个位值不同时，结果为1，否则为0第19页/共67页比较运算符(又称关系运算符)如表所示，用于测试两个表达式的值是否相同，其运算结果为逻辑值，可以为三种之一：TRUE、FALSE及UNKNOWN。3.比较运算符运算符含义=相等>大于<小于>=大于等于<=小于等于<>、!=不等于!<不小于!>不大于第20页/共67页逻辑运算符用于对某个条件进行测试，运算结果为TRUE或FALSE。SQLServer提供的逻辑运算符如表所示。4.逻辑运算符运算符运算规则AND如果两个操作数值都为TRUE，运算结果为

TRUE。OR如果两个操作数中有一个为TRUE，运算结果为TRUE。NOT若一个操作数值为TRUE,运算结果为FALSE，否则为TRUE。ALL如果每个操作数值都为TRUE，运算结果为TRUE。ANY在一系列操作数中只要有一个为

TRUE，运算结果为TRUE。BETWEEN如果操作数在指定的范围内，运算结果为TRUE。EXISTS如果子查询包含一些行，运算结果为TRUE。IN如果操作数值等于表达式列表中的一个，运算结果为TRUE。LIKE如果操作数与一种模式相匹配，运算结果为TRUE。SOME如果在一系列操作数中，有些值为TRUE，运算结果为TRUE。第21页/共67页LIKE的使用语法格式：match_expression[NOT]LIKEpattern[ESCAPEescape_character]通配符说明示例%代表0个或多个字符。WHERE姓名

LIKE'刘%'

查询姓刘的学生。_(下划线)代表单个字符。WHERE姓名LIKE'张_'

查询姓张的名为一个汉字的所有人名。[]指定范围

(如：[a-f]、[0-9])或集合

(如[abcdef])中的任何单个字符。WHEREsubstring(学号,1,1)LIKE'[12]%'查询首字符为1、2的学号。[^]指定不属于范围

(如：[^a-f]、[^0-9])或集合

(如：[^abcdef])的任何单个字符。WHEREsubstring(学号,1,1)LIKE‘[^1-9]%’

查询首字符不为1-9任一字符的学号。第22页/共67页【例7】查询课程名以C或A开头的情况。USEXSCJSELECT*FROMKCWHERE课程名LIKE'[AC]%'第23页/共67页通过运算符“+”实现两个字符串的联接运算。将“+”号两边的字符串串联成一个字符串。【例8】多个字符串的联接。USEXSCJSELECT(学号+‘,’+SPACE(1)+姓名)AS学号及姓名FROMXSWHERESUBSTRING(学号,1,2)=‘19’/*取子串*/5.字符串联接运算符第24页/共67页一元运算符有+（正）、-（负）和~（按位取反）。如a的值为12（0000000000001100）则~a的值为：11111111111100116.一元运算符第25页/共67页7.赋值运算符T-SQL只有一个赋值运算符，即等号（=）。赋值运算符可以将数据值指派给特定的对象。如给局部变量赋值的SET和SELECT语句中使用的“=”。利用赋值运算符可以在列标题和为列定义值的表达式之间建立对应关系。第26页/共67页3流程控制语句

控制语句说明IF...ELSE条件语句GOTO无条件转移语句WHILE循环语句CONTINUE用于重新开始下一次循环BREAK用于退出最内层的循环RETURN无条件返回

SQLServer中提供了如表所示的流程控制语句第27页/共67页3.1IF...ELSE语句

在程序中如果要对给定的条件进行判定，当条件为真或假时分别执行不同的T-SQL语句，可用IF...ELSE语句实现。语法格式：IFBoolean_expression/*条件表达式*/{sql_statement|statement_block} /*条件表达式为真时执行*/[ELSE{sql_statement|statement_block}] /*条件表达式为假时执行*/第28页/共67页IF…ELSE语句中，IF子句是必须的，ELSE子句是可选的，最简单的IF语句没有ELSE子句部分。

SQLServer允许嵌套使用IF…ELSE语句，而且嵌套层数没有限制。IF条件表达式

A/*T-SQL语句或语句块*/ELSEB

/*T-SQL语句或语句块*/当条件表达式的值为真时执行A，然后执行IF语句的下一语句；条件表达式的值为假时执行B，然后执行IF语句的下一语句。第29页/共67页

【例8】如下程序用于查询总学分>40的学生人数。USEXSCJDECLARE@numintSELECT@num=(SELECTCOUNT(姓名)FROMXSWHERE总学分>40)IF@num<>0SELECT@numAS‘总学分>40的人数’第30页/共67页【例9】如果“数据库原理课程”的平均成绩高于75分，则显示“平均成绩高于75分”。USEXSCJDECLARE@text1char(20)SET@text1='平均成绩高于75.'IF(SELECTAVG(成绩)FROMXS_KC,KCWHEREXS_KC.课程号=KC.课程号

ANDKC.课程名=’数据库原理’)<75SELECT@text1='平均成绩低于75.'[ELSE]SELECT@text1第31页/共67页【例10】IF...ELSE语句的嵌套使用。USEXSCJIF(SELECTAVG(成绩)FROMXS_KC,KCWHEREXS_KC.课程号=KC.课程号

ANDKC.课程名=’数据库原理’)<75SELECT'平均成绩低于75'ELSEIF(SELECTAVG(成绩)FROMXS_KC,KCWHEREXS_KC.课程号=KC.课程号

ANDKC.课程名=’数据库原理’)>75SELECT'平均成绩高于75'第32页/共67页BEGIN…END语句BEGIN…END语句将多个T-SQL语句组合成一个语句块，并将它们视为一个单元处理。在条件语句和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用BEGIN…END语句。语法形式为：

BEGIN

{sql_statement|statement_block}

END

第33页/共67页3.2GOTO语句

GOTO语句可以使程序直接跳到指定的标有标识符的位置处继续执行，而位于GOTO语句和标识符之间的程序将不会被执行。通常，应尽量少用GOTO语句。

GOTO语句和标识符可以用在语句块、批处理和存储过程中，标识符可以为数字与字符的组合，但必须以“：”结尾。语法形式如下：

GOTOlabel……label:第34页/共67页3.3WHILE…CONTINUE…BREAK语句

WHILE…CONTINUE…BREAK语句用于设置重复执行SQL语句或语句块的条件。只要指定的条件为真，就重复执行语句。

CONTINUE语句可以使程序跳过CONTINUE语句后面的语句，回到WHILE循环的第一行命令。

BREAK语句则使程序退出其所在的这一层循环，结束该层循环语句的执行。第35页/共67页WHILE语句的语法形式为：WHILEBoolean_expression

{sql_statement|statement_block}

[BREAK]

{sql_statement|statement_block}

[CONTINUE]第36页/共67页【例11】显示字符串"China"中每个字符的ASCII值和字符。DECLARE@positionint,@stringchar(8)SET@position=1SET@string='China'WHILE@position<=DATALENGTH(@string) BEGINSELECTASCII(SUBSTRING(@string,@position,1)), CHAR(ASCII(SUBSTRING(@string,@position,1))) SET@position=@position+1 END第37页/共67页RETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行。语法形式为：

RETURN[integer_expression

]其中，参数integer_expression为返回的整型值。存储过程可以给调用过程或应用程序返回整型值。NOTE：除非特别指明，所有系统存储过程返回0值表示成功，返回非零值则表示失败。3.4RETURN语句

第38页/共67页【例12】检查学生的平均成绩，若>75，将返回状态代码1，将返回状态代码2。USEXSCJCREATEPROCEDUREcheckavg@paramvarchar(10)ASIF(SELECTAVG(成绩)FROMXS_KCWHEREXS_KC学号=@paramGROUPBY学号)>75RETURN1ELSERETURN2第39页/共67页[例13]查询课程号为“C01”课程的平均成绩，若平均成绩小于50，则用原成绩*1.2来更新该门课成绩。若最高成绩大于90分，则退出，否则继续执行。第40页/共67页USEXSCJGOWHILE(SELECTAVG(成绩)FROMXS_KCWHERE课程号=‘C01’)<50--若平均成绩小于50，则执行循环BEGINUPDATEXS_KCSET成绩=成绩*1.2WHERE课程号=‘C01’--更新成绩

IF(SELECTMAX(成绩)FROMXS_KCWHERE课程号=‘C01’)>90BREAK--若最高分大于90，则退出循环

ELSECONTINUE--否则，继续执行END第41页/共67页[例14]利用GOTO语句求出从1加到5的总和；

利用WHILE语句求1-50的偶数和。第42页/共67页declare@sumint,@countintselect@sum=0,@count=1label_1:select@sum=@sum+@countselect@count=@count+1if@count<=5gotolabel_1select@count,@sum也可用WHILE语句来实现第43页/共67页declare@sumint,@countintselect@sum=0,@count=2While(@count<=50)beginselect@sum=@sum+@countselect@count=@count+2endPrint‘s=’+str(@sum)第44页/共67页1．数学函数数学函数用于对数字表达式进行数学运算并返回结果。主要包括：

ABS（numeric_expr）：返回绝对值

SQRT（float_expr）：返回给定值的平方根

CEILING（numeric_expr）：返回不小于给定值的最小整数

FLOOR（numeric_expr）：返回不大于给定值的最大整数

ROUND（numeric_expr,length）：将给定的数值四舍五入到指定的长度

SIGN（numeric_expr）：判断给定的数值是否为正（1）、负（-1）或零（0）RAND（[SEED]）：产生随机数函数4.系统内置函数介绍

第45页/共67页2.字符串函数

字符串函数可以对二进制数据、字符串和表达式执行不同的运算。大多数字符串函数只能用于char和varchar数据类型以及明确转换成char和varchar的数据类型，少数几个字符串函数也可以用于binary和varbinary数据类型。某些字符串函数还能处理text、ntext、image数据类型的数据。第46页/共67页字符串函数的分类基本字符串函数：UPPER、LOWER、SPACE、REPLICATE、STUFF、REVERSE、LTRIM、RTRIM。字符串查找函数：CHARINDEX、PATINDEX。长度和分析函数：DATALENGTH、SUBSTRING、RIGHT、LEFT。转换函数：ASCII、CHAR、STR、SOUNDEX、DIFFERENCE。第47页/共67页2)CHAR：将ASCII码转换为字符语法格式：CHAR(integer_expression)

1)ASCII：返回左端第一个字符的ASCII值语法格式：ASCII(character_expression)

3)LEFT：返回从字符串左边开始指定个数的字符语法格式：LEFT(character_expression,integer_expression)RIGHT：返回从字符串右边开始指定个数的字符第48页/共67页【例15】返回课程名最左边的8个字符，最右边的2个字符，并显示出相应的ASCII码。USEXSCJSELECTLEFT(课程名,8),RIGHT(课程名,2),ASCII(LEFT(课程名,8)),ASCII(RIGHT(课程名,2))FROMKCORDERBY课程号GO第49页/共67页【例16】使用LTRIM字符删除字符变量中的起始空格。DECLARE@stringvarchar(40)SET@string='中国，一个古老而伟大的国家'SELECTLTRIM(@string)GO4)LTRIM：删除字符串左边的空格，并返回字符串语法格式：LTRIM(character_expression)RTRIM：删除字符串右边的空格第50页/共67页5)REPLACE：用第三个表达式的值替换第一个表达式中包含第二个表达式的部分。语法格式：REPLACE('string_expression1','string_expression2','string_expression3')例：SELECTREPLACE('abcd','b','bb')6)SUBSTRING：求子串函数语法格式：SUBSTRING(expression,start,length)Expression：指定的字符串Start：子串的起始位置Length：子串的长度，即要返回的字节数第51页/共67页【例17】如下程序在一列中返回XS表中的姓氏，在另一列中返回表中学生的名。USEXSCJSELECTSUBSTRING(姓名,1,1),SUBSTRING(姓名,2,LEN(姓名)-1)FROMXSORDERBY姓名第52页/共67页SPACE(integer_expr)：返回指定长度的空格

UPPER(char_expr)：把给定的字符串变成大写字母

LOWER(char_expr)：把给定的字符串变成小写字母

STUFF(char_expr1,star,length,char_expr2)：在char_expr1中，把从位置star开始，长度为length的字符串用char_expr2代替REVERSE(char_expr)：将指定的字符串反向PATINDEX(‘%pattern%’,expr）CHARINDEX(‘pattern’,expr）在给定的表达式中查找指定字符串的起始位置第53页/共67页在选择价格位于$11和$20之间的书名时，利用SUBSTRING函数从字符串中返回指定的字符串，并使用

LOWER函数、UPPER函数返回大写和小写的书名。其源程序清单为：USEpubsSELECTLOWER(SUBSTRING(title,1,20))ASLower,UPPER(SUBSTRING(title,1,20))ASUpper,FROMtitlesWHEREpricebetween11.00AND20.00第54页/共67页[例18]定义一个局部变量，其类型为VARCHAR，并给该变量输入值为“

thisistest”。利用字符串函数实现以下操作，并显示结果值：1。删除该字符变量起始空格和末尾空格；2。返回该变量右边开始的四个字符；3。将该字符变量取反；4。将该字符变量中的“is”子串用‘“are”字符串替代。第55页/共67页DECLARE@tempstrVARCHAR(20)SET@tempstr='thisistest'SELECTRTRIM(LTRIM(@tempstr)),RIGHT(RTRIM(LTRIM(@tempstr)),4),REVERSE(@tempstr),REPLACE(@tempstr,'is','are')第56页/共67页3.系统函数

系统函数用于返回有关SQLServer系统、用户、数据库和数据库对象的信息。与其它函数一样，可以在SELECT语句的SELECT子句和WHERE子句以及表达式中使用系统函数。

第57页/共67页1）CASE函数

CASE函数可以计算多个条件式，并将其中一个符合条件的结果表达式返回。CASE函数的语法形式CASE

WHENBoolean_expressionTHENresult_expression

[...n]

[

ELSEelse_result_expression

END

第58页/共67页【例19】根据学生的年龄范围显示相应信息。/*使用CASE搜索函数的SELECT语句*/USEXSCJSELECT学号,年龄=CASEWHENgetdate()-出生时间<=20THEN'年龄较小'WHENgetdate()-出生时间>=24THEN'年龄偏大'ELSE'年龄适中'ENDFROMXSGO第59页/共67页2)CAST和CONVERT常用的类型转换有以下几种情况：日期型字符型：如将datetime或smal