《SQL Server数据库应用与维护》-第6讲 T-SQL语言基础与应用

1上节回顾5.1数据查询

5.1.1SELECT查询语句

5.1.2简单查询

5.1.3连接查询

5.1.4嵌套查询

5.1.5集合查询5.2数据表的增、删、改等基本操作2第4讲T-SQL语言基础

目的与要求：掌握T-SQL的语法基础，包括变量、运算符、函数、流程控制语句和注解等语言元素。为T-SQL的高级应用奠定基础。重点与难点：1.T-SQL语句结构（重点）2.常量与变量（重点）3.运算符与表达式（重难点）6.程序流程控制语句

（重难点）5.函数（重难点）3主要内容6.1T-SQL语句结构6.2常量与变量6.3运算符与表达式6.4程序流程控制语句6.5函数46.1T-SQL语句结构

在MicrosoftSQLServer2005系统中，根据Transact-SQL语言的功能特点，可以把Transact-SQL语言分为5种类型，即数据定义语言、数据操纵语言、数据控制语言、事务管理语言和附加的语言元素。数据定义语言（DataDefinitionLanguage，DDL）数据操纵语言（DataManipulationLanguage，DML）数据控制语言（DataControlLanguage，DCL）事务管理语言（TransactionManagementLanguage，TML）附加的语言元素主要包括标识符、变量和常量、运算符、表达式、数据类型、函数、控制流语言、错误处理语言、注释等。5介绍SQL批处理批处理单个SQL命令组成一批作为单个

执行计划

进行编译输出结果6批处理示例UsePubsSelect*fromauthorsUpdateauthorssetphone='890451-7366'whereau_lname='White'Go标志着批处理的结束7批处理中的注释1）--（双连字符）

USENorthwind--单行注释

GO

2）/*...*/（正斜线-星号字符对）

SELECT*FROMEmployees/*这是注释*/

注：完整的注释必须包含在一个批处理中。注释是程序代码中不执行的文本字符串。82026年3月16日第8页常量1.字符串常量【例】'Hello''计算机'【例】'Hesay:''Hello!'''

可以在字符串内包含字母和数字字符（a~z、A~Z和0~9）以及特殊的字符，例如感叹号（！）、at字符（@）和数据号（#）。如果字符串中要包含单引号，怎么办？！92.Unicode字符串常量

Uicode字符串使用时前面加上一个N标识符（N必须为大写），字符码均以两个Byte表示。【例】N'Hello'N'计算机'常量10常量3.整型常量

根据整型的进制不同，整型又可以分为十进制常量、二进制常量和十六进制常量。其中十进制常量以普通的整数表示。二进制常量即数字0和1。十六进制常量在使用时加上前缀0x。200/*十进制数-2958/*十进制数0/*十进制数，也可以认为是二进制数，二者在数值上相等。0x60A2/*十六进制数，代表十进制247380xE5f/*十六进制数，代表十进制3679

116.实型常量实型常量是包含有小数点的数字，分为定点表示和浮点表示两种。

32.50/*定点表示的实型常量25.8E4/*浮点表示的实型常量，其值为25.8×10⁴3.2E-2/*浮点表示的实型常量，其值为3.2×10-2

-2E6/*浮点表示的实型常量，其值为-2×106

常量125.日期时间常量

SQLServer可以识别多种格式的日期时间常量。用单引号引起来。'2007-01-01'/*数字日期格式'3/12/1995'/*数字日期格式'February2,2000'/*字母日期格式'20050825'/*未分割的字符串日期格式'12:00:00'/*时间格式'05:30:PM'/*时间格式'2007-10-1008:40:30'/*日期时间格式常量136.货币常量通常采用整型或者实型常量加上'$'前缀构成，例如：

$1236.56-$2007.唯一标识常量唯一标识常量用于表示全局唯一标识符（GUID）的字符串。GUID的格式为“xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx”其中每个x是0-9或a-f范围内的一个4位十六进制数。例如：‘6F9619FF-8B86-D011-B42D-00C04FC964FF’常量14变量有两种形式：系统提供的全局变量全局变量被引用时要在其名称前加上标志“＠＠”。

用户自定义的局部变量局部变量必须先定义，后使用，被引用时要在其名称前加上标志“＠”。变量15全局变量SQLServer中的全局变量都用两个@

标记作为前缀。可以使用简单的SELECT查询语句检索任意全局变量的值。SELECT

@

@VERSIONASSQL_SERVER_VERSION_DETAILS16局部变量使用局部变量可以将数据传递到SQL语句局部变量名必须以

'@'为前缀SET

或SELECT

语句用于给局部变量赋值DECLARE

@cust

VARCHAR(20)SET

@cust=‘FRANK‘--赋值DECLARE

@pub_idchar(4),@hire_datedatetime

数据类型声明变量17为变量赋值SET

语句或

SELECT

语句用于给局部变量赋值。语法：SET

@local_variablename=valueDECLARE

@CUSTVARCHAR(5)–-最大长度为5SET

@CUST='FRANK'SELECT

CUSTOMERID,COMPANYNAMEFROM

CUSTOMERSWHERE

CUSTOMERID=

@CUST局部变量名18SELECT语句：从表中取值赋给变量，但应保证类型一致。若从表中返回的是多个值，则取最后一个值赋给变量。SELECT

@s=price

FROM

titlesWHERE

title_id=‘bu395’19当执行一个简单的变量赋值时,使用SET赋值语句当基于查询进行变量赋值时,使用SELECT赋值语句20变量示例【例1】创建局部变量@var1、@var2，并赋值，然后输出变量的值。DECLARE

@var1,@var2char(20)SET

@var1＝’中国’ /*一个SET语句只能给一个变量赋值*/SET

@var2＝@var1+

’是一个伟大的国家’SELECT

@var1,@var2

/*输出变量的值*/Go21【例2】创建一个名为sex的局部变量，并在SELECT

语句中使用该局部变量查找表学生表中所有女同学的学号、姓名。DECLARE@sexbitSET@sex=0SELECT

学号,姓名FROM

StudentWHERE

性别=@sexGO变量示例22【例3】使用查询给变量赋值。DECLARE@stuchar(8)SET@stu=(SELECT姓名FROM学生)GO/*该语句若学生表中只有一条记录，可正常执行，否则将出错。*/变量示例23【例4】在学生表中不存在符合要求的结果，对该表的查询不返回结果，变量@var1将保留原值。

DECLARE@var1nvarchar(30)

SELECT@var1='刘丰'

SELECT@var1=姓名

/*将姓名列的值赋给变量*/FROM学生

WHERE学号

=‘64122312111’--假设没有该学号

SELECT@var1AS'NAME'/*@var1为‘刘丰’*/变量示例24变量小结1.种类（1）局部变量：@变量名：用户使用（2）全局变量：@@变量名：系统用于记录信息2.声明局部变量:DECLARE

@变量名

类型3.赋值:

SET

@变量名=值|表达式或SELECT

@变量名=值|表达式6.显示变量的值

print

@变量名或SELECT

@变量名25运算符运算符类别所包含运算符赋值运算符=（赋值）算术运算符+(加)、-（减）、*(乖)、/(除)、%（取模）按位运算符&(位与)、|(位或)、^(位异或)字符串串联运算符（连接）比较运算符=(等于)、>(大于)、>=(大于等于)、<(小于)、<=(小于等于)、<>(或!=不等于)、!<(不小于)、!>(不大于)逻辑运算符all（所有）、and(与）、any（任意一个）、between(两者之间)、exists(存在)、in(在范围内)、like(匹配)、not(非)、or(或)、some(任意一个)一元运算符、+（正）、-（负）、~（取反）26简单表达式【例】将“选课表”中各个成绩乘以0.8后输出。SELECT

学号,课程号,grade*0.8FROM

选课表达式SET

NOCOUNTONDECLARE@startdatedatetimeSET@startdate=getdate()

/*当前日期*/SELECT@startdate–出生日期AS年龄FROM

学生【例】求学生的年龄。27复杂表达式表达式【例】多个字符串的联接。SELECT

(学号+‘,’+SPACE(1)+姓名)AS学号及姓名FROM

学生WHERE

Substring(学号,1,2)=‘19’/*取子串*/28LIKE的使用通配符说明示例%代表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任一字符的学号。29【例】查询课程名以C或A开头的情况。SELECT*FROM课程WHERE课程名LIKE'[AC]%'30顺序执行程序控制语句顺序执行流程改变程序流程控制语句

31控制语句说明IF...ELSE条件语句CASE多分支选择语句GOTO无条件转移语句WHILE循环语句CONTINUE用于重新开始下一次循环BREAK用于退出最内层的循环WAITFOR设置语句执行的延迟时间RETURN无条件返回BEGIN…END定义语句块流程控制语句

32IF语句示例IF(SELECTCOUNT(ORDERID)FROMORDERS)>1

BEGIN

RETURN

END

ELSE

BEGIN

SELECT*FROMCUSTOMERS

END332026年3月16日第33页【例】IF语句的使用。

DECLARE

@pingyuchar(10) IF

(SELECTMIN(成绩)FROM选课)>=60 SELECT@pingyu='全部及格' ELSE SELECT@pingyu='存在不及格' PRINT@pingyu流程控制语句

34【例】查询年龄>19的学生人数。

DECLARE@numint SELECT@num=(SELECTCOUNT(*)FROM学生

WHERE年龄>19) IF@num<>0

SELECT@numAS‘年龄>19的人数’35【例】如果“数据库原理课程”的平均成绩高于75分，则显示“平均成绩高于75分”。DECLARE@text1char(20)SET@text1='平均成绩高于75.'IF(SELECTAVG(成绩)FROM选课,课程

WHERE选课.课程号=课程.课程号

AND课程.课程名=’数据库原理’)<75

SELECT@text1='平均成绩低于75.‘SELECT@text136【例】IF...ELSE语句的嵌套使用。

IF(SELECTAVG(成绩)FROMXS_KC,KCWHEREXS_KC.课程号=KC.课程号

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

ANDKC.课程名=’数据库原理’)>75SELECT'平均成绩高于75'37 CASE语句计算条件列表并返回多个可能结果表达式之一，其语法格式有两种。1.简单的CASE语句2.搜索类型的CASE语句流程控制语句

CASE语句38CASE表达式

WHEN表达式的值1THEN返回表达式1WHEN表达式的值2THEN返回表达式2…ELSE返回表达式nEND简单的CASE语句39select

sid,case

cid

when

'1'

then

'数据库'

when

'2'

then

'数学'

when

'3'

then

'信息系统'end

as

cnamefrom

选课简单的CASE语句40CASEWHEN逻辑表达式1THEN返回表达式1WHEN逻辑表达式2THEN返回表达式2… ELSE返回表达式n

END搜索类型的CASE语句41例：调整员工工资，工作级别为1的上调8%，工作级别为2的上调7%，工作级别为3的上调6%，其它上调5%updateemployee

sete_wage=

case

whenjob_level=’1’thene_wage*1.08

whenjob_level=’2’thene_wage*1.07

whenjob_level=’3’thene_wage*1.06

elsee_wage*1.05

end

搜索类型的CASE语句42UPDATEpublishers

SETstate=

CASE

WHENcountry<>"USA"THEN"--"

ELSEstate

END

,

city=

CASE

WHEN

pub_id="9999"THEN"LYON"

ELSEcity

END

WHEREcountry<>"USA“ORpub_id="9999"以这种方式可以用一个命令更新两个或多个列43语法：WHILE

逻辑表达式Begin

T-SQL语句组[break]

/*终止整个语句的执行*/[continue]

/*结束一次循环体的执行*/End

WHILE语句流程控制语句

44【例】简单的while循环示例DECLARE@XintSET@X=0 WHILE@X<3BEGIN

SET@X=@X+1

PRINT'X='+convert(char(1),@X)--类型转换函数convert

ENDGO执行结果：X=1X=2X=3452026年3月16日第45页

WHILE语句【例】利用WHILE语句计算0~100之间所有数的和。

DECLARE@xint,@sumint

SET

@x

=0

SET

@sum

=0 WHILE@x

<=100 BEGIN

SET

@sum=@sum+@x SET

@x=

@x

+1 END

PRINT'1~100所有数的和是：'+str(@sum)流程控制语句

46GOTO语句GOTO语句将执行语句无条件跳转到标签处，并从标签位置继续执行;GOTO语句和标签可以在过程、批处理或语句块中的任何位置使用。【例】利用GOTO语句计算0~100之间所有数的和。

DECLARE

@xint,@sumint

SET

@x=0

SET

@sum=0

xh:SET

@x=@x+1

SET

@sum=@sum+@x if@x<100

GOTO

xh

PRINT'1~100所有数的和是：'+str(@sum)流程控制语句

472026年3月16日第47页BREAK语句 BREAK语句一般都出现在WHILE语句的循环体内，作为WHILE语句的子句。在循环体内使用BREAK语句，会使进程提前跳出循环。

【例】求1～100之间的所有数之和，但是如果和大于1000，立刻跳出循环，输出结果。DECLARE

@xint,@sumintSET

@x=0SET

@sum=0WHILE

@x<100

BEGIN

SET

@x=@x+1

SET

@sum=@sum+@x if@sum>1000

BREAK

ENDPRINT'结果是:‘+str(@sum)流程控制语句

48第48页CONTINUE语句

CONTINUE和BREAK语句一样，一般都出现在WHILE语句的循环体内，作为WHILE语句的子句。在循环体内使用CONTINUE语句，结束本次循环，重新转到下一次循环。【例】计算1～100所有偶数之和，并输出结果。

DECLARE@xint,@sumintSET@x=0SET@sum=0WHILE@x<100 BEGIN

SET@x=@x+1 if@x%2=1

CONTINUE

SET@sum=@sum+@x ENDPRINT'1～100所有偶数之和是:'+str(@sum)流程控制语句

4949WAITFOR语句，称为延迟语句，设定在达到指定时间或时间间隔之前，或者指定语句至少修改或返回一行之前，阻止执行批处理、存储过程或事务。其语法格式为：WAITFOR

{DELAY'time_to_pass'/*设定等待时间*/|TIME'time_to_execute'/*设定等待带某一时刻*/}流程控制语句

WAITFOR语句5050【例】延迟30秒执行查询。USELibraryGOWAITFOR

DELAY’00:00:30’SELECT*FROMReader【例】在时刻21:20:00执行查询。USELibraryGOWAITFOR

TIME'21:20:00'SELECT*FROMReader512026年3月16日第51页RETURN语句

RETURN语句用于结束当前程序的执行，返回到上一个调用它的程序或其他程序。其语法格式为：

RETURN[integer_expression]说明：

integer_expression：要返回的整形值。

RETURN语句通常在存储过程中使用，且不能返回空值。在系统存储过程中，一般情况下返回0值表示成功，返回非0值则表示失败。流程控制语句

522026年3月16日第52页

BEGIN…END语句在IF语句、WHILE语句的程序体内使用BEGIN…END语句表示一次执行一组SQL语句。即将一组语句用BEGIN…END语句封闭起来。BEGIN…END语句允许在使用的过程中嵌套。流程控制语句

53TRY…CATCH错误处理语句

如果TRY块内发生错误，则会将控制传递给CATCH块中包含的另一个语句组。【例】BEGINTRYselectDISTINCT籍贯from学生信息select520/0as'结果'ENDTRYBEGINCATCHselectERROR_LINE()as'错误行数'ENDCATCH流程控制语句

54第54页SQLServer提供强大的函数功能，常用如下：聚合函数日期时间函数字符串函数空值置换函数数学函数数据类型转换函数用户也可以创建自定义函数，对SQLServer对象处理能力进行扩展。函数

55第55页【例】AVG函数的使用。以下语句统计所有学生成绩的平均值。SELECTAVG(分数)as平均成绩FROM选课表GO【例】MAX函数的使用。以下语句返回选课表中学生成绩的最高分数。SELECTMAX(分数)as最高成绩FROM选课表GO【例】COUNT函数的使用。以下语句返回学生表中的记录个数。SELECTCOUNT(学号)as总人数FROM选课表GO1聚合函数COUNT，SUM，AVG，MAX，MIN，DISTINCT56DATEADD()：加上一个时间后的新时间

DECLARE@OLDTimedatetime

SET@OLDTime='24March20063:00PM'

SELECT

DATEADD(hh,4,@OLDTime)执行结果：

2日期时间函数57DATEDIFF()：两时间之差DECLARE@FirstTimedatetime,@SecondTimedatetimeSET@FirstTime='24March20063:00PM'SET@SecondTime='24March20063:33PM'SELECT

DATEDIFF(ms,@FirstTime,@SecondTime)执行结果：2日期时间函数58DATENAME()返回年月日星期等字符串。DECLARE@StatementDatedatetime

SET@StatementDate='24March20063:00PM'SELECTDATENAME(dw,@StatementDate)执行结果：2日期时间函数59DATEPART()返回部分日期DECLARE@WhatsTheDaydatetimeSET@WhatsTheDay='23March20073:00PM'SELECTCAST(DATEPART(yyyy,@WhatsTheDay)ASchar(4))+'年'+CAST(DATEPART(mm,@WhatsTheDay)ASchar(2))+'月'+CAST(DATEPART(dd,@WhatsTheDay)ASvarchar(2))+'日‘执行结果：2日期时间函数60第60页【例】GETDATE的用法。

SELECTGETDATE();【例】YEAR、MONTH和DAY函数的用法。

SELECT

STR(YEAR('03/12/2007'))+'年'+

STR(MONTH('03/12/2007'))+'月'+

STR(DAY('03/12/2007'))+'日'2日期时间函数61ASCII()DECLARE@StringTestchar(10)SET@StringTest=ASCII(‘Robin’)SELECT@StringTestCHAR()DECLARE@StringTestchar(10)SET@StringTest=ASCII(‘Robin’)SELECTCHAR(@StringTest)

3字符函数6262LEFT()DECLARE@StringTestchar(10)SET@StringTest=’Robin’SELECTLEFT(@StringTest,3)LOWER()DECLARE@StringTestchar(10)SET@StringTest=’Robin’SELECTLOWER(LEFT(@StringTest,3))LTRIM()DECLARE@StringTestchar(10)SET@StringTest=’Robin’SELECT‘Start-’+LTRIM(@StringTest),’Start-’+@StringTest3字符函数6363RIGHT()DECLARE@StringTestchar(10)SET@StringTest=’Robin’SELECTRIGHT(@StringTest,3)RTRIM()DECLARE@StringTestchar(10)SET@StringTest=’Robin’SELECT@StringTest+’-End’,RTRIM(@StringTest)+’-End’STR()SELECT‘A’+82SELECT‘A’+STR(82)SELECT‘A’+LTRIM(STR(82))3字符函数6464SUBSTRING()DECLARE@StringTestchar(10)SET@StringTest=’Robin’SELECTSUBSTRING(@StringTest,3,LEN(@StringTest))UPPER()DECLARE@StringTestchar(10)SET@StringTest=’Robin’SELECTUPPER(@StringTest)3字符函数65【例】返回课程名最左边的8个字符，最右边的2个字符，并显示出相应的ASCII码。USEXSCJSELECTLEFT(课程名,8),RIGHT(课程名,2),ASCII(LEFT(课程名,8)),ASCII(RIGHT(课程名,2))FROMKCORDERBY课程号GO3字符函数66【例】使用LTRIM字符删除字符变量中的起始空格。DECLARE@stringvarchar(40)SET@string='中国，一个古老而伟大的国家'SELECTLTRIM(@string)GO3字符函数6767空值置换函数ISNULL（空值，指定的空值），用指定的值代替空值。

SELECTLendnum,ISNULL(Lendnum,0)AS空值置换

FROMReader WHEREISNULL(Lendnum,0)=0 GO

查询结果：4空值置换函数682026年3月16日第68页5数学函数三角函数：SIN：正弦函数。COS：余弦函数。TAN：正切函数。COT：余切函数。反三角函数：ASIN：反正弦函数。ACOS：反余弦函数。ATAN：反正切函数。

ATN2:返回两个值的反正切。角度弧度转换函数：DEGREES：返回弧度值相对应的角度值。RADINANS：返回一个角度的弧度值。取近似值函数：CEILING：返回大于或等于所给数字表达式的最大整数。幂函数：EXP：指数函数。LOG：计算以2为底的自然对数。LOG10：计算以10为底的自然对数。POWER：幂运算。SQRT：平方根函数。SQUARE：平方函数。FLOOR：返回小于等于一个数的最大的整数。ROUND：对一个小数进行四舍五入运算，使其具备特定的精度。符号函数：ABS:返回一个数的绝对值SIGN：根据参数是正还是负，返回-1、+1和0。随机函数：RAND：返回float类型的随机数，该数的值在0～1之间。PI函数：PI：返回以浮点数表示的圆周率。

692026年3月16日第69页【例】ABS函数的使用。

SELECTABS(-8.5)在查询分析器内执行上条语句，返回的结果是8.5，即参数的绝对值。【例】CEILING函数的使用。

SELECTCEILING(25.3),CEILING(-25.3),CEILING(0)返回结果是：26，-25，0。【例】RAND函数的使用。

SELECTFLOOR(RAND()*10),FLOOR(RAND(5)*10) RAND函数返回0～1之间的一个随机数，但是如果其参数（随机数种子）相同的话产生的随机数相同，如果参数不同则随机数不同。上面的语句对产生的随机数乘以10再取整，得到0～10之间的随机整数。706数据类型转换函数通过这类函数可以转换数据的数据类型；默认情况下，系统会自动转换；如果系统没有自动执行数据类型的转换，则需要使用CAST和CONVERT转换函数将一种数据类型的表达式转换为另一种数据类型的表达式【例】SELECTconvert(varchar(20),学号)AS'学号',姓名FROM学生信息71【例】如下程序将检索总学分30～39分的学生姓名，并将总学分转换为char(20)。/*如下例子同时使用CAST和CONVERT*/--使用CAST实现.SELECT姓名,总学分FROMXSWHERECAST(总学分ASchar(20))LIKE'3_'GO--使用