理学附加TSQL编程

目标掌握如何定义变量并赋值掌握如何输出显示数据掌握IF、WHILE、CASE逻辑控制语句理解SQL中批处理的概念使用变量变量分为：局部变量：局部变量必须以标记@作为前缀，如@age局部变量的使用也是先声明，再赋值

全局变量：全局变量必须以标记@@作为前缀，如@@version全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值局部变量例如：DECLARE@namevarchar(8)DECLARE@seatint声明局部变量

DECLARE@变量名数据类型赋值

SET@变量名=值

SELECT@变量名=值例如：SET@name=‘张三’SELECT@name=stuNameFROMstuInfoWHEREstuNo=‘s25302’

必须确保筛选出的记录只有1条

或

全局变量全局变量都使用两个@标志作为前缀

变量含义@@ERROR最后一个T-SQL错误的错误号@@IDENTITY最后一次插入的标识值@@LANGUAGE当前使用的语言的名称@@MAX_CONNECTIONS可以创建的同时连接的最大数目@@ROWCOUNT 受上一个SQL语句影响的行数@@SERVERNAME本地服务器的名称@@TRANSCOUNT 当前连接打开的事务数@@VERSION

SQLServer的版本信息SQLServer中的函数相当于Java语言中的内置包字符串函数日期函数数学函数系统函数字符串函数SELECTSTUFF('ABCDEFG',2,3,'我的音乐我的世界')返回：A我的音乐我的世界EFG在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串STUFFSELECTCHARINDEX('ACCP','MyAccpCourse',1)返回：4用来寻找一个指定的字符串在另一个字符串中的起始位置CHARINDEX举例描述函数名SELECTLEN('SQLServer课程')返回：12返回传递给它的字符串长度LENSELECTLOWER('SQLServer课程')返回：sqlserver课程把传递给它的字符串转换为小写LOWERSELECTUPPER('sqlserver课程')返回：SQLSERVER课程把传递给它的字符串转换为大写UPPERSELECTLTRIM('周智宇')返回：周智宇（后面的空格保留）清除字符左边的空格LTRIMSELECTRTRIM('周智宇')返回：周智宇（前面的空格保留）清除字符右边的空格RTRIMSELECTRIGHT('买卖提.吐尔松',3)返回：吐尔松从字符串右边返回指定数目的字符RIGHTSELECTREPLACE('莫乐可切.杨可','可','兰')返回：莫乐兰切.杨兰替换一个字符串中的字符REPLACE日期函数SELECTDATEPART(day,’01/15/2000’)返回：15日期中指定日期部分的整数形式DATEPARTSELECTGETDATE()返回：今天的日期取得当前的系统日期GETDATE举例描述函数名SELECTDATEADD(mm,4,’01/01/99’)返回：以当前的日期格式返回05/01/99将指定的数值添加到指定的日期部分后的日期DATEADDSELECTDATEDIFF(mm,’01/01/99’,’05/01/99’)返回：4两个日期之间的指定日期部分的区别DATEDIFFSELECTDATENAME(dw,’01/01/2000’)返回：Saturday日期中指定日期部分的字符串形式DATENAME数学函数SELECTSQRT(9)返回：3取浮点表达式的平方根SqrtSELECTABS(-43)返回：43取数值表达式的绝对值ABS举例描述函数名SELECTCEILING(43.5)返回：44返回大于或等于所给数字表达式的最小整数CEILINGSELECTPOWER(5,2)返回：25取数值表达式的幂值POWERSELECTROUND(43.543,1)返回：43.5将数值表达式四舍五入为指定精度ROUNDSELECTSIGN(-43)返回：-1对于正数返回+1，对于负数返回-1，对于0则返回0SignSELECTFLOOR(43.5)返回：43取小于或等于指定表达式的最大整数FLOOR系统函数SELECTUSER_NAME(1)返回：从任意数据库中返回“dbo”从给定的用户ID返回用户名USER_NAMESELECTCONVERT(VARCHAR(5)，12345)返回：字符串12345用来转变数据类型CONVERT举例描述函数名SELECTCURRENT_USER返回：你登录的用户名返回当前用户的名字CURRENT_USERSELECTDATALENGTH('中国A盟')返回：7返回用于指定表达式的字节数DATALENGTHSELECTHOST_NAME()返回：你所登录的计算机的名字返回当前用户所登录的计算机名字HOST_NAMESELECTSYSTEM_USER返回：你当前所登录的用户名返回当前所登录的用户名称SYSTEM_USER案例分析1—要求某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题：

卡里面的“O和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“i”都改成“1”； 请编写SQL语句实现以上要求；数据库表名：Card；密码字段名：PassWord；案例分析1—分析这是更新语句，需要使用UPDATE语句；因为牵涉到字符串的替换，需要使用到SQLServer中的函数Replace；案例分析2—要求在数据库表中有以下字符数据，如：

13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2

现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排需，输出要排成这样：

13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2数据库表名：SellRecord；字段名：ListNumber；案例分析2—分析这是查询语句，需要使用SELECT语句需要使用到ORDERBY进行排序，并且在ORDERBY的排序列中，也需要重新计算出排序的数字来前半部分的数字，可以从先找到“-”符号的位置，然后，取其左半部分，最后再使用Convert函数将其转换为数字：

Convert(int,Left(ListNumber,CharIndex('-',ListNumber)-1))后半部分的数字，可以先找到“-”符号的位置，然后把从第一个位置到该位置的全部字符替换为空格，最后再使用Convert函数将其转换为数字：

Convert(int,Stuff(ListNumber,1,Charindex('-',ListNumber),''))用户自定义函数

标量函数：返回单个数据值。表值函数：返回值是一个记录集合——表。在此函数中，return语句包含一条单独的select语句。多语句表值函数：返回值是由选择的结果构成的记录集。在SQLServer中，用户不仅可以使用标准的内置函数，也可以使用自己定义的函数来实现一些特殊的功能。用户自定义函数可以在对象资源管理器中创建，也可以使用CREATEFUNCTION

语句创建。在创建时需要注意：函数名在数据库中必须唯一，其可以有参数，也可以没有参数，其参数只能是输入参数，最多可以有1024参数。1.使用CREATEFUNCTION语句创建用户自定义函数使用CREATEFUNCTION创建用户自定义函数，其语法格式如下：

CREATEFUNCTION[owner_name.]function_name([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])

RETURNSscalar_return_data_type[AS]

BEGINfunction_body

RETURNscalar_expression

ENDfunction_name：指用户自定义函数的名称。其名称必须符合标识符的命名规则，并且对其所有者来说，该名称在数据库中必须唯一。

@parameter_name：用户自定义函数的参数，其可以是一个或多个。每个函数的参数仅用于该函数本身；相同的参数名称可以用在其它函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。如果函数的参数有默认值，在调用该函数时必须指定"default"关键字才能获得默认值。scalar_parameter_data_type：参数的数据类型。scalar_return_data_type：是用户定义函数的返回值。可以是SQLServer支持的任何标量数据类型（text、ntext、image和timestamp除外）。function_body：位于begin和end之间的一系列Transact-SQL语句，其只用于标量函数和多语句表值函数。scalar_expression：用户自定义函数中返回值的表达式。2.表值函数表值函数遵循的原则：RETURNS子句仅包含关键字table。不必定义返回变量的格式，因为它由RETURN子句中的SELECT语句的结果集决定。function_body不由BEGIN和END分隔。RETURN子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表。

例：在stuDB库中创建一个内嵌表值函数XUESHENG，该函数可以根据输入的系部代码返回该系学生的基本信息。其代码如下：CREATEFUNCTIONXUESHENG(@inputdepnvarchar(4))RETURNS

table

AS

RETURN

(SELECT学号,姓名FROM学生WHERE所属院系=@inputdep)GO建立好该内嵌表值函数后，就可以象使用表或视图一样来使用它：SELECT*FROMDBO.XUESHENG(‘100')GO3.多语句表值函数多语句函数的主体中允许使用以下语句：赋值语句DECLARE语句，该语句定义函数局部的数据变量和游标。SELECT语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以INTO子句向局部变量赋值的FETCH语句；不允许使用将数据返回到客户端的FETCH语句。INSERT、UPDATE和DELETE语句，这些语句修改函数的局部table变量。EXECUTE语句调用扩展存储过程。3.多语句表值函数案例：在stuDB库中创建一个多语句表值函数chengji，该函数可以根据输入的课程名称返回选修该课程的学生姓名和成绩。代码如下：USEstuDBGOCREATEFUNCTIONchengji(@inputkmaschar(20))/*为chengji函数定义的表结构，名称变量为@cj*/RETURNS@cjiTABLE(科目编号varchar(10),

姓名varchar(10),

成绩int)ASBEGININSERT@cj/*该变量是上面定义的表名称变量*/

selectb.科目编号,a.学生姓名,(b.笔试成绩+b.上机成绩)/2

from学生信息表asainnerjoin成绩表asb

ona.学号=b.学号whereb.科目编号=@inputkm

RETURNENDGO定义返回表的结构在查询编辑器中输入以下查询命令：SELECT*FROMdbo.chengji(‘9001’)查看、修改和删除自定义函数1.查看用户自定义函数的属性在SQLServer中，根据不同需要，可以使用sp_helptext、sp_help等系统存储过程来查看用户自定义函数的不同信息。每个系统存储过程的具体作用和语法如下：使用sp_helptext查看用户定义函数的文本信息，其语法格式为：

sp_helptext用户自定义函数名使用sp_help查看用户自定义函数的一般信息，其语法格式为：

sp_help用户自定义函数名例：使用有关系统过程查看STUDENT数据库中名为XUEFEN的用户自定义函数的文本信息。其程序代码如下：

SP_HELPTEXTXUEFENGO查看、修改和删除自定义函数2.修改用户自定义函数的属性使用SQL命令修改用户自定义函数，使用ALTERFUNCTION

命令可以修改用户自定义函数。修改由CREATEFUNCTION语句创建的现有用户定义函数，不会更改权限，也不影响相关的函数、存储过程或触发器。其语法格式如下：ALTERFUNCTION[owner_name.]function_name([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])RETURNSscalar_return_data_type[AS]BEGINfunction_bodyRETURNscalar_expressionEND其中的参数与建立用户自定义函数中的参数意义相同。查看、修改和删除自定义函数3.使用T-SQL命令删除用户自定义函数使用DROP命令可以一次删除多个用户自定义函数，其语法格式为：DROPFUNCTION[所有者名称.]函数名称[,…n]案例：删除在student库上建立的xuefen函数。代码如下：

DROPFUNCTIONdbo.xuefenGO逻辑控制语句IF－ELSE语句SQL中的IF-ELSE语句IF

（条件）

BEGIN

语句1

语句2……

ENDELSE

BEGIN

语句1；语句2；

……

ENDELSE是可选部分如果有多条语句，才需要BEGIN-END语句块

IF-ELSE示例问题：统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息。学员成绩表分析：第一步，统计平均成绩存入临时变量；第二步，用IF－ELSE判断；IF-ELSE示例设置输出结果的格式为了文本消息和输出结果显示在同一窗口，需要设置输出结果的格式逻辑控制语句WHILE循环语句SQL中的WHILE语句WHILE

（条件）

BEGIN

语句1

语句2……

BREAK

ENDBREAK表示退出循环如果有多条语句，才需要BEGIN-END语句块

WHILE示例问题：

本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止。学员成绩表分析：

第一步，统计没通过的人数；

第二步，如果有人没通过，加分；

第三步，循环判断。DECLARE@nintWHILE(1=1)--条件永远成立

BEGINSELECT@n=COUNT(*)FROM成绩表

WHEREwrittenExam<60

--统计不及格人数IF(@n>0)UPDATE成绩表--每人加2分

SET

writtenExam=writtenExam+2

ELSEBREAK

--退出循环ENDprint'加分后的成绩如下：'SELECT*FROM成绩表WHILE示例参考语句逻辑控制语句CASE－END多分支语句CASE

WHEN

条件1THEN

结果1

WHEN

条件2THEN

结果2……

ELSE

其他结果ENDCASE-END示例问题：采用美国的ABCDE五级打分制来显示笔试成绩。

A级:90分以上

B级：80－89分

C级:70－79分

D级：60－69分

E级：60分以下学员成绩表print

'ABCDE五级显示成绩如下：'SELECT学号,

成绩=CASE

WHENwrittenExam<60THEN'E'

WHENwrittenExamBETWEEN60AND69THEN'D'

WHENwrittenExamBETWEEN70AND79THEN'C'

WHENwrittenExamBETWEEN80AND89THEN'B'

ElSE

'A'

END

FROM成绩表CASE－END示例参考语句CASE-END练习练习：

请根据平均分和下面的评分规则，编写T-SQL语句查询学员的成绩，如上图所示。优：90分以上良：80－89分中：70－79分差：60－69分不及格：60分以下学员成绩分析CASE-END练习答案USEstuDBGOselecta.学生姓名,a.学号,b.笔试成绩,b.上机成绩,总成绩=(b.笔试成绩+b.上机成绩)/2,等级=casewhen(b.笔试成绩+b.上机成绩)/2<60then'不及格'when(b.笔试成绩+b.上机成绩)/2between60and69then'及格'when(b.笔试成绩+b.上机成绩)/2between70and79then'中'when(b.笔试成绩+b.上机成绩)/2between80and89then'良'else'优'endfrom学生信息表asa,成绩表asbwherea.学号=b.学号批处理语句批处理是包含一个或多个SQL语句的组，从应用程序一次性地发送到SQLServer执行SQLServer将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条客户端应用程序SQLServer服务器批处理语句：语句1语句2……GO批处理语句示例SELECT*FROMstuInfoSELECT*FROMstuMarksUPDATEstuMarksSETwrittenExam=writtenExam+2GOGO是批处理的标志，表示SQLServer将这些T-SQL语句编译为一个执行单元，提高执行效率