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

男娘i 2023-09-25 22:44 19阅读 0赞

本系列博文还在更新中，收录在专栏：[\#MS-SQL Server][MS-SQL Server] 专栏中。

本系列文章列表如下：

[【SQL Server】 Linux 运维下对 SQL Server 进行安装、升级、回滚、卸载操作][SQL Server_ Linux _ SQL Server]  
[【SQL Server】数据库开发指南（一）数据库设计的核心概念和基本步骤][SQL Server]  
[【SQL Server】数据库开发指南（二）MSSQL数据库开发对于库、表、数据类型、约束等相关操作][SQL Server_MSSQL]  
[【SQL Server】数据库开发指南（三）面向数据分析的 T-SQL 编程技巧与实践][SQL Server_ T-SQL]  
[\[ 云原生 | Docker \] 构建高可用性的 SQL Server：Docker 容器下的主从同步实现指南][_ _ Docker _ _ SQL Server_Docker]  
[【SQL Server】数据库开发指南（五）T-SQL 高级查询综合应用与实战][SQL Server_T-SQL]  
[【SQL Server】数据库开发指南（六）索引和视图的使用技巧、方法与综合应用][SQL Server 1]  
[【SQL Server】数据库开发指南（七）MS-SQL存储过程全面解析：种类、优点和创建方法详解][SQL Server_MS-SQL]  
[【SQL Server】数据库开发指南（八）高级数据处理技术 MS-SQL 事务、异常和游标的深入研究][SQL Server_ MS-SQL]  
[【SQL Server】数据库开发指南（九）详细讲解 MS-SQL 触发器的基础概念与应用场景][SQL Server_ MS-SQL 1]

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

### 文章目录 ###

*   *  一、变量
     *   *  1.1 局部变量（Local Variable）
         *  1.2 全局变量（Global Variable）
     *  二、输出打印语句
     *  三、逻辑控制语句
     *   *  3.1 if-else 判断语句
         *  3.2 while…continue…break 循环语句
         *  3.3 case 语句
     *  五、其他语句
     *   *  5.1 go 语句
         *  5.2 waitfor delay 延迟执行批处理语句

## 一、变量 ##

### 1.1 局部变量（Local Variable） ###

T-SQL 中的局部变量是一种只能在当前作用域（存储过程、函数、批处理语句等）中使用的变量。局部变量可以用于存储临时数据，进行计算和处理，以及传递数据到存储过程和函数等。T-SQL 中的局部变量必须以`@`符号开头，并且需要指定数据类型，而且必须用 DECLARE 命令声明后才能使用。

**使用语法：**

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

**使用示例：**

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

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

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

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

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

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

DECLARE @Salary int;
    
    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;      --最后一次自增的值
    SELECT identity(int, 1, 1) AS id INTO tab FROM student;--将studeng表的烈属，以/1自增形式创建一个tab
    SELECT * FROM tab;
    SELECT @@rowcount;      --影响行数
    SELECT @@cursor_rows;   --返回连接上打开的游标的当前限定行的数目
    SELECT @@error;         --T-SQL的错误号
    SELECT @@procid;
    SET datefirst 7;        --设置每周的第一天，表示周日
    SELECT @@datefirst AS '星期的第一天', datepart(dw, getDate()) AS '今天是星期';
    SELECT @@dbts;          --返回当前数据库唯一时间戳
    SET language 'Chinese';
    SELECT @@langId AS 'Language ID';           --返回语言id
    SELECT @@language AS 'Language Name';       --返回当前语言名称
    SELECT @@lock_timeout;                      --返回当前会话的当前锁定超时设置（毫秒）
    SELECT @@max_connections;                   --返回SQL Server 实例允许同时进行的最大用户连接数
    SELECT @@MAX_PRECISION AS 'Max Precision';  --返回decimal 和numeric 数据类型所用的精度级别
    SELECT @@SERVERNAME;                        --SQL Server 的本地服务器的名称
    SELECT @@SERVICENAME;                       --服务名
    SELECT @@SPID;                              --当前会话进程id
    SELECT @@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 变量或表达式

**使用示例：**

SELECT 1 + 2;
    SELECT @@language;
    SELECT user_name();
     
    PRINT 1 + 2;
    PRINT @@language;
    PRINT user_name();

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

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

## 三、逻辑控制语句 ##

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

### 3.1 if-else 判断语句 ###

**使用语法：**

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

**使用示例：**

基本使用示例如下：

DECLARE @num INT = 3
    
    IF @num = 1
    BEGIN
       PRINT 'The number is 1.'
    END
    ELSE IF @num = 2
    BEGIN
       PRINT 'The number is 2.'
    END
    ELSE
    BEGIN
       PRINT 'The number is not 1 or 2.'
    END

简单查询判断示例如下：

DECLARE @id char(10),
            @pid char(20),
            @name varchar(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 > @id
        BEGIN
            PRINT @id + '%';
            SELECT * FROM ab_area WHERE pid LIKE @id + '%';
        END
    ELSE
        BEGIN
            PRINT @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 @i int;
        SET @i = 1;
    WHILE (@i < 11)
        BEGIN
            PRINT @i;
            SET @i = @i + 1;
        END
    GO
     
    --WHILE CONTINUE 输出到
    DECLARE @i int;
        SET @i = 1;
    WHILE (@i < 11)
        BEGIN
            IF (@i < 5)
                BEGIN
                    SET @i = @i + 1;
                    CONTINUE;
                END
            PRINT @i;
            SET @i = @i + 1;
        END
    GO
     
    --WHILE BREAK 输出到
    DECLARE @i int;
        SET @i = 1;
    WHILE (1 = 1)
        BEGIN
            PRINT @i;
            IF (@i >= 5)
                BEGIN
                    SET @i = @i + 1;
                    BREAK;
                END
            SET @i = @i + 1;
        END
    GO

### 3.3 case 语句 ###

**使用语法：**

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

**使用示例：**

SELECT *,
        CASE sex 
            WHEN 1 THEN '男'
            WHEN 0 THEN '女'    
            ELSE '火星人'
        END AS '性别'
    FROM student;
     
    SELECT areaName, '区域类型' = CASE
            WHEN areaType = '省' THEN areaName + areaType
            WHEN areaType = '市' THEN 'city'
            WHEN areaType = '区' THEN 'area'
            ELSE 'other'
        END
    FROM 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 毫秒

[MS-SQL Server]: https://blog.csdn.net/dietime1943/category_6436988.html
[SQL Server_ Linux _ SQL Server]: https://bluetata.blog.csdn.net/article/details/129864499
[SQL Server]: https://bluetata.blog.csdn.net/article/details/129905010
[SQL Server_MSSQL]: https://bluetata.blog.csdn.net/article/details/129928569
[SQL Server_ T-SQL]: https://bluetata.blog.csdn.net/article/details/130040273
[_ _ Docker _ _ SQL Server_Docker]: https://bluetata.blog.csdn.net/article/details/130124814
[SQL Server_T-SQL]: https://bluetata.blog.csdn.net/article/details/130168306
[SQL Server 1]: https://bluetata.blog.csdn.net/article/details/130899849
[SQL Server_MS-SQL]: https://bluetata.blog.csdn.net/article/details/130907754
[SQL Server_ MS-SQL]: https://bluetata.blog.csdn.net/article/details/131039893
[SQL Server_ MS-SQL 1]: https://bluetata.blog.csdn.net/article/details/131093456