【SQL Server】数据库开发指南（三）面向数据分析的 T-SQL 编程技巧与实践

T-SQL 指的是 Transact-SQL，是一种针对 Microsoft SQL Server 数据库系统的 SQL 方言。T-SQL 扩展了标准 SQL 语言，提供了更多的功能和特性，包括事务处理、错误处理、游标处理、动态 SQL、存储过程、触发器、用户定义函数等等。

文章目录

一、变量

1.1 局部变量（Local Variable）

T-SQL 中的局部变量是一种只能在当前作用域（存储过程、函数、批处理语句等）中使用的变量。局部变量可以用于存储临时数据，进行计算和处理，以及传递数据到存储过程和函数等。T-SQL 中的局部变量必须以

@

符号开头，并且需要指定数据类型，而且必须用 DECLARE 命令声明后才能使用。

使用语法：

--声明变量DECLARE @变量名 变量类型 [@变量名 变量类型]--为变量赋值SET @变量名 = 变量值;SELECT @变量名 = 变量值;

使用示例：

--局部变量DECLARE@idchar(10)--声明一个长度的变量idDECLARE@ageint--声明一个int类型变量ageSELECT@id=22--赋值操作SET@age=55--赋值操作PRINTconvert(char(10),@age)+'#'+@idSELECT@age,@id
GO
 
--简单hello world示例DECLARE@namevarchar(20);DECLARE@resultvarchar(200);SET@name='jack';SET@result=@name+' say: hello world!';SELECT@result;--查询数据示例DECLARE@idint,@namevarchar(20);SET@id=1;SELECT@name= name FROM student WHERE id =@id;SELECT@name;-- 使用 SELECT 赋值DECLARE@namevarchar(20);SELECT@name='jack';SELECT*FROM student WHERE name =@name;

从上面的示例可以看出，局部变量可用于程序中保存临时数据、传递数据。

• SET 赋值一般用于赋值指定的常量个变量。
• 而 SELECT 多用于查询的结果进行赋值，当然SELECT也可以将常量赋值给变量。

注意：在使用 SELECT 进行赋值的时候，如果查询的结果是多条的情况下，会利用最后一条数据进行赋值，前面的赋值结果将会被覆盖。

在T-SQL 中，如果使用 SELECT 语句进行赋值，并且查询的结果包含多条记录，那么只会使用最后一条记录的值进行赋值。这种行为称为“隐式转换”。

例如，假设我们有一张名为 Employee 的表，其中包含员工的姓名和薪水。如果我们使用 SELECT 语句查询薪水，并将其赋值给一个变量，那么如果查询结果包含多条记录，将只使用最后一条记录的值进行赋值，前面的赋值结果将会被覆盖。以下是一个例子：

DECLARE@Salaryint;SELECT@Salary= Salary
FROM Employee
WHERE Department ='Sales';PRINT@Salary;

在这个例子中，我们声明了一个整型变量 @Salary，并使用 SELECT 语句将查询结果的 Salary 列赋值给@Salary变量。如果 Employee 表中有多个部门为 “Sales” 的员工，那么只有最后一个员工的薪水将被赋值给 @Salary 变量，前面的赋值结果将会被覆盖。

如果想要将查询结果的所有记录都赋值给变量，可以使用类似于聚合函数的方式，将查询结果拼接成一个字符串，然后再将其赋值给变量。例如，可以使用以下语句将所有薪水拼接成一个字符串：

DECLARE@SalaryList NVARCHAR(MAX);SELECT@SalaryList=coalesce(@SalaryList+', ','')+ CAST(Salary AS NVARCHAR(MAX))FROM Employee
WHERE Department ='Sales';PRINT@SalaryList;

在这个例子中，我们声明了一个字符串变量@SalaryList，并使用SELECT语句将所有薪水拼接成一个逗号分隔的字符串。这样就可以将查询结果的所有记录都赋值给@SalaryList变量了。

1.2 全局变量（Global Variable）

全局变量是系统内部使用的变量，其作用范围并不局限于某一程序而是任何程序均可随时调用的，无需创建和配置。这些系统全局变量通常用于返回 SQL Server 实例的元数据信息，如版本号、计算机名称、当前日期时间等。

以下是一些常用的系统全局变量：

-- 常见的全局变量SELECT @@identity;--最后一次自增的值SELECTidentity(int,1,1)AS id INTO tab FROM student;--将studeng表的烈属，以/1自增形式创建一个tabSELECT*FROM tab;SELECT @@rowcount;--影响行数SELECT @@cursor_rows;--返回连接上打开的游标的当前限定行的数目SELECT @@error;--T-SQL的错误号SELECT @@procid;SET datefirst 7;--设置每周的第一天，表示周日SELECT @@datefirstAS'星期的第一天', datepart(dw, getDate())AS'今天是星期';SELECT @@dbts;--返回当前数据库唯一时间戳SETlanguage'Chinese';SELECT @@langIdAS'Language ID';--返回语言idSELECT @@languageAS'Language Name';--返回当前语言名称SELECT @@lock_timeout;--返回当前会话的当前锁定超时设置（毫秒）SELECT @@max_connections;--返回SQL Server 实例允许同时进行的最大用户连接数SELECT @@MAX_PRECISIONAS'Max Precision';--返回decimal 和numeric 数据类型所用的精度级别SELECT @@SERVERNAME;--SQL Server 的本地服务器的名称SELECT @@SERVICENAME;--服务名SELECT @@SPID;--当前会话进程idSELECT @@textSize;--用于指定SQL语句返回结果集的最大长度（以字符数为单位）。其默认值为2147483647，即最大值。SELECT @@version;--当前数据库版本信息

以下是一些系统统计相关的全局变量：

--系统统计相关SELECT @@CONNECTIONS;--连接数SELECT @@PACK_RECEIVED;SELECT @@CPU_BUSY;SELECT @@PACK_SENT;SELECT @@TIMETICKS;SELECT @@IDLE;SELECT @@TOTAL_ERRORS;SELECT @@IO_BUSY;SELECT @@TOTAL_READ;--读取磁盘次数SELECT @@PACKET_ERRORS;--发生的网络数据包错误数SELECT @@TOTAL_WRITE;--sqlserver执行的磁盘写入次数

二、输出打印语句

T-SQL 支持输出语句，用于显示结果。常用输出语句有两种：

使用语法：

PRINT 变量或表达式
SELECT 变量或表达式

使用示例：

SELECT1+2;SELECT @@language;SELECT user_name();PRINT1+2;PRINT @@language;PRINT user_name();

注意：在SQL Server中，PRINT 语句用于在消息窗口中输出一段文本。但是，如果要输出的文本较长（超过8000个字符），则会被截断，只显示前面的一部分内容。此外，如果要输出的内容包含非文本类型的数据（如整数、浮点数等），则需要使用

convert

函数将其转换为字符串类型。

如果要输出的字符串长度超过8000个字符，可以考虑使用多个 PRINT 语句拼接输出，或者使用其他方式输出，如将结果插入到一个临时表中，并使用 SELECT 语句查询。

三、逻辑控制语句

在 SQL Server 中，逻辑控制语句用于控制程序流程，从而根据需要执行特定的代码块。

3.1 if-else 判断语句

使用语法：

IF<表达式>BEGIN-- <命令行或程序块>ENDELSEIF<表达式>BEGIN-- <命令行或程序块>ENDELSEBEGIN-- <命令行或程序块>END

使用示例：

基本使用示例如下：

DECLARE@numINT=3IF@num=1BEGINPRINT'The number is 1.'ENDELSEIF@num=2BEGINPRINT'The number is 2.'ENDELSEBEGINPRINT'The number is not 1 or 2.'END

简单查询判断示例如下：

DECLARE@idchar(10),@pidchar(20),@namevarchar(20);SET@name='广州';SELECT@id= id FROM ab_area WHERE areaName =@name;SELECT@pid= pid FROM ab_area WHERE id =@id;PRINT@id+'#'+@pid;IF@pid>@idBEGINPRINT@id+'%';SELECT*FROM ab_area WHERE pid LIKE@id+'%';ENDELSEBEGINPRINT@id+'%';PRINT@id+'#'+@pid;SELECT*FROM ab_area WHERE pid =@pid;END
GO

3.2 while…continue…break 循环语句

使用语法：

WHILE<表达式>BEGIN<命令行或程序块>[BREAK][CONTINUE]<命令行或程序块>END

使用示例：

--WHILE 循环输出DECLARE@iint;SET@i=1;WHILE(@i<11)BEGINPRINT@i;SET@i=@i+1;END
GO
 
--WHILE CONTINUE 输出到DECLARE@iint;SET@i=1;WHILE(@i<11)BEGINIF(@i<5)BEGINSET@i=@i+1;CONTINUE;ENDPRINT@i;SET@i=@i+1;END
GO
 
--WHILE BREAK 输出到DECLARE@iint;SET@i=1;WHILE(1=1)BEGINPRINT@i;IF(@i>=5)BEGINSET@i=@i+1;BREAK;ENDSET@i=@i+1;END
GO

3.3 case 语句

使用语法：

CASEWHEN<条件表达式>THEN<运算式>WHEN<条件表达式>THEN<运算式>WHEN<条件表达式>THEN<运算式>[ELSE<运算式>]END

使用示例：

SELECT*,CASE sex 
        WHEN1THEN'男'WHEN0THEN'女'ELSE'火星人'ENDAS'性别'FROM student;SELECT areaName,'区域类型'=CASEWHEN areaType ='省'THEN areaName + areaType
        WHEN areaType ='市'THEN'city'WHEN areaType ='区'THEN'area'ELSE'other'ENDFROM ab_area;

五、其他语句

5.1 go 语句

在 SQL Server 中，

GO

是一个批处理语句，它表示当前批处理语句的结束，并执行当前批处理中的所有 SQL 语句。使用 GO 可以将多个批处理语句分隔开来，每个批处理语句独立执行，不受前面语句的影响。

使用 GO 的基本语法如下：

<sql statement 1><sql statement 2>...<sql statement n>
GO

5.2 waitfor delay 延迟执行批处理语句

在 SQL Server 中，waitfor delay 是一个 T-SQL 语句，可以用于延迟执行批处理语句。它会暂停当前批处理语句的执行，等待指定的时间后再继续执行，类似于定时器、休眠等。waitfor delay 语句的示例举例如下：

WAITFOR DELAY '00:00:05'-- 等待 5 秒钟WAITFOR DELAY '10s'-- 等待 10 秒钟WAITFOR DELAY '500ms'-- 等待 500 毫秒