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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介: T-SQL 指的是 Transact-SQL,是一种针对 Microsoft SQL Server 数据库系统的 SQL 方言。T-SQL 扩展了标准 SQL 语言,提供了更多的功能和特性,包括事务处理、错误处理、游标处理、动态 SQL、存储过程、触发器、用户定义函数等等。

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

@[toc]

一、变量

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 毫秒
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
11天前
|
数据采集 机器学习/深度学习 数据可视化
使用Jupyter Notebook进行数据分析:入门与实践
【6月更文挑战第5天】Jupyter Notebook是数据科学家青睐的交互式计算环境,用于创建包含代码、方程、可视化和文本的文档。本文介绍了其基本用法和安装配置,通过一个数据分析案例展示了如何使用Notebook进行数据加载、清洗、预处理、探索、可视化以及建模。Notebook支持多种语言,提供直观的交互体验,便于结果呈现和分享。它是高效数据分析的得力工具,初学者可通过本文案例开始探索。
|
4天前
|
SQL 存储 安全
SQL入门与进阶:数据库查询与管理的实用指南
一、引言 在数字化时代,数据库已经成为各行各业存储、管理和分析数据的关键基础设施
|
5天前
|
SQL 安全 数据库
SQL实践指南:从基础到进阶的数据库查询与管理
一、引言 在数据驱动的时代,数据库已成为各行各业不可或缺的一部分
|
5天前
|
SQL 安全 数据库
掌握SQL:数据库操作与查询的精髓
一、引言 在数据驱动的时代,数据库是任何组织或项目不可或缺的一部分
|
5天前
|
SQL 存储 数据库
掌握SQL:数据库查询与管理的关键技能
一、引言 在信息化快速发展的今天,数据库已成为存储、处理和查询数据的重要工具
|
5天前
|
SQL 存储 安全
深入SQL:掌握数据库操作与查询的艺术
一、引言 在信息化高度发展的今天,数据库已成为企业、组织甚至个人管理数据的核心工具
|
5天前
|
SQL 存储 数据库
深入理解SQL:从基础查询到高级数据库管理
一、引言 SQL(结构化查询语言)是数据库管理系统的核心语言,用于存储、检索、更新和管理关系数据库中的数据
|
5天前
|
SQL 存储 监控
精通SQL:数据库查询与管理的全面指南
一、引言 在当今的数据驱动世界中,[数据库](https://www.iyxwzx.com/)技术无处不在,而SQL(结构化查询语言)则是数据库操作的基础
|
5天前
|
SQL 安全 数据库
精通SQL:数据库查询与管理的实战指南
一、引言 在当今数字化时代,[数据库](https://www.iyxwzx.com/)已成为企业、组织和个人不可或缺的数据[管理](https://www.iyxwzx.com/news/)工具
|
6天前
|
SQL 缓存 监控
sql anywhere 数据库优化
SQL Anywhere数据库优化涉及索引、查询、配置、硬件、维护和应用程序多方面。关键策略包括:确保索引有效且定期优化,使用EXPLAIN计划优化查询,调整数据库缓存设置,升级硬件,定期备份,优化SQL语句并减少数据库访问。实时监控性能并据此调优,但需依据具体应用场景和版本进行适配测试。