T-SQL编程
本章目录
一、课程大纲与内容
二、练习
一、课程大纲与内容
3.1变量
3.1.1局部变量
3.1.2T-SQL参数
3.1.3应用程序变量
3.2输出语句
3.3流程控制语句
3.3.1Begin…End语句
3.3.2If…Else语句
3.3.3While语句
3.3.4Case语句
3.3.5Goto语句
3.3.6Return语句
3.3.7Try…Catch语句
3.3.8Execute语句
3.4常用函数
3.4.1结构和类别
3.4.2聚合函数
3.4.3时间和日期函数
3.4.4数学函数
3.4.5系统函数
3.4.7元数据函数
3.4.8字符串函数
3.4.9配置函数
3.4.10排名函数
3.4.11自定义函数
3.4.12常用函数使用示例
3.5T-SQL综合应用
-------------------------------------------------------------------------------------------3.1变量
3.1.1局部变量
局部变量是批处理和脚本中，可以保存数据值的对象。声明或定义此变量后，批处理中的一个语句可以将此变量设置为一个值，该批处理中后面的语句可以从此变量获取这个值。
例如：
use student;
go
declare@var int;
set@var=1234;
select*from student
where sno=@var;
go
说明：declare@var int，生成了数值型的变量@var。
set@var=1234，对变量进行赋值。
where sno=@var，使用变革进行条件的设置。
3.1.2T-SQL参数
T-SQL参数是用于在存储过程中、执行该存储过程的批处理、脚本直接传递数据的对象。参数可以是输入参数也可以是输出参数。
例如：
use student;
go
create procedure sampleproc@parm int as
select sno,sname
from student
where sno=@parm
go
exec sampleproc@parm=105;
说明：@parm int为存储过程的输入参数。
用exec sampleproc@parm=105执行；结果为105。
3.1.3应用程序变量
应用程序编程语言（如C、C++、Basic和Java）使用变量保存数据。变量是可以保存数据值的存储区域。每一个应用程序变量都有数据类型和大小。数值变量还有精度（变量可以保存的数字位数）和小数位数（小数点右边的数字位数）。为了使应用程序可以处理从Transact-SQL语句返回的数据，它必须具有将Transact-SQL数据移至应用程序变量的机制。数据库API支持绑定概念，即将Transact-SQL语句中的结果集列、参数、返回代码或参数标记绑定到应用程序变量。
为了检索结果集中的数据，应用程序使用与下文所示相似的执行步骤：
（1）执行Transact-SQL语句。
（2）调用数据库API函数以确定结果集中的列数。
（3）对于每个结果集列，应用程序可以：
（a）调用返回列的属性（数据类型大小等）的数据库API函数。
（b）分配应用程序变量，其属性与列的属性兼容。
（c）调用数据库API函数将结果集列绑定或映射到应用程序变量。
（4）使用数据库API函数一次提取一行结果集行。每次提取时，每个结果集列的值均放入该列所绑定的应用程序变量中。
应用程序可以改变此执行步骤。例如，如果应用程序正在对已知表执行硬编码Transact-SQL语句，由于预先知道结果集列的属性，因此应用程序不必调用数据库API来获得这些属性。
3.2输出语句
同C语言的PRINTF()函数一样，T-SQL中也支持输出语句，用于输出显示处理的数据结果。
常用的输出语句有两种，他们的语法分别是：
PRINT局部变量或字符串
SELECT局部变量AS自定义列名
其中，第二种方法就是查询语句的特殊应用。
示例：
PRINT‘服务器的名称’+@@SERVERNAME
SELECT@@SERVERNAME AS‘服务器的名称’
用PRINT方法输出的结果将在消息窗口以文本方式显示，用SELECT方法将在网格窗口以表格方式显示。
由于使用PRINT语句要求单个局部变量或字符串表达式作为参数，所以如果我们这样编写SQL语句将出错：PRINT‘当前错误号’+@@ERROR
因为全局变量@@ERROR返回的是整型数值。那如何解决呢？我们可以使用转换函数，把数值转换成为字符串，如下：
PRINT‘当前错误号’+CONVERT(VARCHAR(5),@@ERROR)
@@ERROR用于便是最近一条SQL语句是否有错，如果有错，将返回非零的值。
@@INDENTITY可用来查询最后插入的标识值。
3.3流程控制语句
3.3.1Begin…End语句
使用begin...end...语句将一组T-SQL语句组成语句组，并作为一个单元运行。begin...end允许嵌套。
示例：
use student;
go
begin transaction;
go
if@@trancount=0
begin
select*from student where sno='105';
rollback transaction;
print N'Rolling back ths transaction two times would cause an error.' end;
rollback transaction;
print N'Rolled back the transaction.'
go
执行结果：Rolled back the transaction.
如果不包括BEGIN…END愉快，将执行两个rollback transaction语句，并返回两条print语句。
3.3.2If…Else语句
If…Else用于指导执行条件。Else关键字可选。If…Else可以嵌套使用，
use student;
declare@ErrorCode int;
if(@ErrorCode<>0)
begin
print'Errors encountered,rolling back.';
print'Last error encountered:'+cast(@ErrorCode as varchar(20));
rollback;
end;
else
begin
print'no errors encountered,committiing.';
commit;
end;
return@ErrorCode;
注意：if后跟的表达式必须为布尔表达式。回滚是指退出到执行前的操作。
3.3.3While语句
While用于设置重复执行sql语句或语句块的条件。只要条件为真就重复执行语句。可以使用break和continue关键字，在循环内部控制while循环中语句的执行。
例如：
use student;
select*from student;
go
while(select avg(column_b)from student)>200
begin
update student set column_b=column_b/2;
select min(column_b)from student;
if(select min(column_b)from student)<100
break;
else
continue;
end;
注意：
（a）如果使用两个或多个while循环，内层的break将退出到下一个外层循环。
（b）While后接的表达式，返回true或false。
（c）Break导致从最内层的while循环中退出，continue是while循环重新开始执行，忽略continue关键字后面的任何语句。
（d）示例中，产品的平均价格大于200，则循环将所有价格除以2，然后显示最低价格。如果价格大于等于100，循环重新开始，并再次将价格除以2，直
至最低价格小于100，才退出while循环。
3.3.4Case语句
Case语句用于计算条件列表，并返回对个可能结果表达式。有两种形式：简单case 函数形式，将一个表达式与一组简单表达式进行比较，以得到结果；case搜索函数形式，计算一组表达式，以得到结果。
示例1：简单case函数形式，检查表达式是否相等
use student;
select*from student;
go
select sname as'学生姓名',N'学号'=
case sno
when'103'then N'陆君的学号'
when'101'then N'李军的学号'
when'105'then N'匡明的学号'
when'108'then N'曾华的学号'
when'107'then N'王丽的学号'
else N'其他人的学号'
end
from student
order by sname;
go
示例2：将数据进行分类。将价格分段归类。
select productNumber as N'产品编号',Name as N'名称',N'价格范围'= case
when listprice=0then N'0元'
when listprice<50then N'50元以下'
when listprice>=50and listprice<250then N'250元以下'
when listprice>=250and listprice<1000then N'1000元以下' else N'1000元以上'
end
from product
order by productname;
go
3.3.5Goto语句
Goto语句将执行流更改到其他位置（标签处），跳过Goto后面的语句，并从标签处开始继续处理。可在过程、批处理或语句块中任何位置使用，但是不能跳转到该批处理以为的的标签。Goto也可以嵌套使用。
use student;
go
declare@table0sysname
declare@count int
set@table0=N'student'
set@count=0;
loop0:
if(@@ERROR=0)
BEGIN
EXEC('SELECT'''+@table0+'''=count(*)from'+@table0)
set@count=@count+1;
print str(@count)+N'执行完成!'
END;
if(@@ERROR=0AND@count<2)goto loop0;
go
说明：使用Goto循环代替了While循环。
3.3.6Return语句
Return语句从查询或过程中无条件退出，可以在任何时候使用，其后面的语句不执行。
示例：显示使用return返回存储过程的参考值，该语句经常在存储过程中使用
--1、创建存储过程
create procedure findjobs@nm sysname=null
as
if@nm is null
begin
print'you must give a user name'
return--2返回
end;
else
begin
select https://www.doczj.com/doc/2313445599.html,,o.id,o.uid
from sysobjects o inner join master.syslogins l
on o.uid=l.uid
where https://www.doczj.com/doc/2313445599.html,=@nm;
end;
--3执行存储过程
exec findjobs;
--4执行存储过程
exec findjobs@nm='sa'
如果执行findjobs时没指定用户名作为参数，则return将使过程向用户屏幕发出消息“you must give a user name”然后退出。
3.3.7Try…Catch语句
try...catch语句在SQL Server2005数据库中是新加入的，它用于实现类似于C#和C++语言中的异常处理的错误处理。T-SQL语句组可以包含在try块中。如果try块内部发生错误，则会将控制传递给catch块中包含的另一个语句组。
示例：在临时性过程中使用try...catch语句，使用select语句生成被零除的错误，该错误会导致跳转至相关的catch块的执行，
例如：
use student;
go
begin try
select1/0;--会产生除错误
end try
begin catch
select
ERROR_NUMBER()AS ErrorNumber,
ERROR_SEVERITY()AS ErrorSeverity,
ERROR_STATE()AS ErrorState,
ERROR_PROCEDURE()AS ErrorProcedure,
ERROR_LINE()AS ErrorLine,
ERROR_MESSAGE()AS ErrorMessage;
end catch;
go
由catch块捕获该错误，并以检索错误消息函数显示相关信息，执行结果如下：
(0行受影响)
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage 8134161NULL2遇到以零作除数
错误。
(1行受影响)
使用try...catch...处理事务，在事务出错时进行回滚，正常执行时提交
use student;
select*from student;
go
begin transaction;
begin try
insert into student values('120','张三','猪','2009-09-09','98833',null)
-–这里的性别中我们首先创建约束只能为‘男’或者‘女’
end try
begin catch
select
ERROR_NUMBER()AS ErrorNumber,
ERROR_SEVERITY()AS ErrorSeverity,
ERROR_STATE()AS ErrorState,
ERROR_PROCEDURE()AS ErrorProcedure,
ERROR_LINE()AS ErrorLine,
ERROR_MESSAGE()AS ErrorMessage;
if@@trancount>0
rollback transaction
end catch;
if@@trancount>0
commit transaction
go
执行结果为：
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage 547160NULL4INSERT语句与
CHECK约束
"CK_STUDENT"
冲突。该冲突发生
于数据库
"student"，表
"dbo.STUDENT",
column'SSEX'。
(1行受影响)
3.3.8Execute语句
execute语句执行T-SQL批中的命令字符串、字符串或执行下列模块之一：系统存储
过程，用户定义存储过程，标量值用户定义函数或者扩展存储过程。SQL SERVER2005数据库还提供了扩展的execute语句，可以向链接服务器发送传递命令。
示例：
use student;
go
execute('select*from student');
go
--示例2执行存储过程，带参数
--创建一个存储过程：
USE[student]
GO
/******对象:StoredProcedure[dbo].[sampleproc]脚本日期:01/05/2010 09:34:30******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure[dbo].[sampleproc]@parm int as
select sno,sname
from student
where sno=@parm
-----------------------执行存储过程-----------------------------------
use student;
go
execute sampleproc105;
go
3.4常用函数
3.4.1结构和类别
函数由两部分组成：函数头和函数体。函数可接受0个或多个输入参数，返回数据值或表。
函数头，也称为标题头，一般包含如下结构:有架构、所有者名称的函数名称；输入参数名称和数据类型；可以用于输入参数的选项；返回参数数据类型和可选名称；返回参数数据类型和可选名称；可以用于返回参数的选项。
函数体，也称为正文，它定义了函数将要执行的操作和逻辑，它包含以下两个部分：执行函数逻辑的一个或多个T-SQL语句；.NET程序集的引用。
根据返回值的类型和是否由系统提供，分为标量函数、表值函数和内置函数。标量函数又分为内联标量函数、多语句标量函数；表值函数又分为内联表值函数、多语句表值函数。
内联标量函数：指用户定义标量函数返回在returns子句中定义的类型的“单个数据值”，函数体是单个T-SQL语句。
多语句标量函数：是多语句的标量函数。定义在BEGIN…END块中的函数体，包含一组返回单个值的T-SQL语句。
内联表值函数：返回table数据类型。没函数主体。
多语句表值函数：函数体中，以在BEGIN…END块中包含一组T-SQL语句。
内置函数：也称系统函数，返回标量数据类型或table数据类型的函数，不能修改。
3.4.2聚合函数
聚合函数只能在以下位置作为表达式使用：select语句的选择列表；compute或compute by子句；having子句。
（1）AVG
函数功能：返回组中值的平均值。空值将被忽略。
语法：AVG([ALL|DISTINCT]expression)
（2）MAX
函数功能：返回表达式的最大值。
语法：MAX([ALL|DISTINCT]expression)
（3）MIN
函数功能：返回表达式的最小值。
语法：MIN([ALL|DISTINCT]expression)
（4）SUM
函数功能：返回表达式中所有值的和，或只返回DISTINCT值。SUM只能用于数字列。空值将被忽略。
语法：SUM([ALL|DISTINCT]expression)
（5）COUNT
函数功能：返回组中项目的数量
语法COUNT({[ALL|DISTINCT]expression]|*})
--首先创建一张示例表
create table test
(
sno int primary key identity(1,1)not null,
age int,
salary float
)
插入一些测试数据：
insert into test values(21,81.5);
insert into test values(22,82.5);
insert into test values(23,83.0);
insert into test values(24,86.5);
insert into test values(25,88.5);
insert into test values(26,87.5);
insert into test values(27,89.5);
insert into test values(28,99.5);
使用AVG函数
select avg(salary)from test;--返回结果为87.3125
使用SUM函数
select sum(salary)from test;--返回结果为698.5
T-SQL提供聚合函数
函数名功能说明
AVG返回组中各值的平均值
CHECKSUM返回按照表的某一行或一组表达式计算出来的校验和值
CHECKSUM_AGG返回组中各值的校验和
COUNT返回组中的项数
COUNT_BIG返回组中的项数
GROUPING包含用于对数据进行分组的表达式
MAX返回表达式中的最大值
MIN返回表达式中的最小值
SUM返回表达式中所有值或仅非重复值的和
STDEV返回指定表达式中所有值的标准方差
STDEVP返回指定表达式中所有值的总体标准方差
VAR返回指定表达式中所有值的方差
VARP返回指定表达式中的所有值的总体方差
3.4.3时间和日期函数
（1）DATEADD
函数功能在向指定日期加上一段时间的基础上，返回新的datetime值。
语法：DATEADD(datepart,number,date)
样例：SELECT DATEADD(day,21,pubdate)AS timeframe FROM titles （2）DATEPART
函数功能返回代表指定日期的指定日期部分的整数
语法：DATEPART(datepart,date)
样例：SELECT DATEPART(month,GETDATE())AS'Month Number'
（3）DAY
函数功能返回代表指定日期的天的日期部分的整数。
语法：DAY(date)
（4）GETDATE
函数功能按datetime值的Microsoft?SQL Server?标准内部格式返回当前系统日期和时间。
语法：GETDATE()
（5）GETUTCDATE
函数功能返回表示当前UTC时间（世界时间坐标或格林尼治标准时间）的datetime 值。当前的UTC时间得自当前的本地时间和运行SQL Server的计算机操作系统中的时区设置。
语法：GETUTCDATE()
（6）MONTH
函数功能返回代表指定日期月份的整数。
语法：MONTH(date)
样例：SELECT"Month Number"=MONTH('03/12/1998')
（7）YEAR
函数功能返回表示指定日期中的年份的整数。
语法：YEAR(date)
3.4.4数学函数
（1）ABS
函数功能返回给定数字表达式的绝对值。
语法：ABS(numeric_expression)
（2）RAND