T-SQL——基础语法

目录

• 0. 定义变量
• 1. 批处理
• 2. 条件逻辑——IF
• 3. 逻辑分支——CASE
• 4. 循环语句——WHILE
• 5. 附录——关于CASE表达式的实现函数
• 6. 参考

shanzm-2020年11月30日 20:47:15

shanzm-2021年10月3日 21:45:47：更新关于全局变量的说明

---

0. 定义变量

• 声明变量：变量以@符号开头，使用DECLARE声明变量(一次可以声明多个变量)，并同时定义变量的数据类型。

• 若是声明变量时没有初始化，则变量值为NULL

• 赋值语法：使用SET或者使用SELECT给变量赋值

DECLARE @Num AS INT ,@Result AS INT
SET @Num=10
SELECT @Result=20
SET @Result=@Num+@Result
SELECT @Result

• 注意1: 声明变量的时候，可以省略AS

DECLARE @Num INT
SET @Num=10
SELECT @Num

• 注意2：SQL Server 2008新增语法特性：声明变量的时候可以同时赋值

DECLARE @i INT=100--声明变量的同时给变量赋值
SELECT @i

• 注意3：使用SET一次只能给一个变量赋值，而使用SELECT可以一次赋值多个变量

SET @Num=10--SET一次只能个一个变量赋值
SELECT @Num=10,@Resutl=100--SELECT一次可以给多个变量赋值

• 注意4：不能使用同一个SELECT即对某个变量使用某个字段赋值，又同时从表中查询另外字段。

DECLARE @Age varchar(50)
SELECT NAME,@AGE=Age FROM T_STUDENTS--这里就是即用Age字段对@Age赋值，又同时查询Name字段

上面的语句就会报错：

向变量赋值的 SELECT 语句不能与数据检索操作结合使用。

但是注意若是SELECT仅是通过查询字段赋值给某个变量，则是完全没有问题的

• 注意5：使用SELECT查询结果给变量赋值，若是查询结果有多个记录，则给变量赋值的是记录的最后一行。若是SELECT无返回值则变量值为NULL

----------------------------------测试数据
DECLARE @studentVar TABLE
(
    Name VARCHAR(10),
    Age INT
);
INSERT INTO @studentVar
(
    Name,
    Age
)
VALUES
('张三', 18),
('李四', 19),
('王五', 18);

----------------------------------测试1,Select查询结果赋值给变量
DECLARE @Age VARCHAR(50),@Name VARCHAR(50)
SELECT @Name=Name ,@Age=Age FROM @studentVar WHERE Name='张三'
SELECT @Name, @Age

-- 结果：
-- ----- ------
-- 张三   18

-----------------------------------测试2，Select查询结果有多个记录
DECLARE @Age2 VARCHAR(50),@Name2 VARCHAR(50)
SELECT @Name2=Name ,@Age2=Age FROM @studentVar WHERE Age=18
SELECT @Name2, @Age2

-- 结果：
-- ----- ------
-- 王五   18

• 【注意6】：使用SELECT和SET将一个查询语句的结果赋值给一个变量，是有区别的。
  若是该查询无结果，使用SELECT赋值则则不会对变量做任何改变，而使用SET赋值则变量变为NULL

DECLARE @subjectStr VARCHAR(100) = '123';
DECLARE @subjectStr2 VARCHAR(100) = '123';
DECLARE @studentVar TABLE
(
    Name VARCHAR(10),
    Age INT
);
INSERT INTO @studentVar
(
    Name,
    Age
)
VALUES
('张三', 18),
('李四', 19),
('王五', 18);
SET @subjectStr =
(
    SELECT Name FROM @studentVar WHERE Name = '赵六'--无
);
SELECT @subjectStr;
--结果：NULL

SELECT @subjectStr =
(
    SELECT Name FROM @studentVar WHERE Name = '赵六'--无
);
SELECT @subjectStr2;
--结果：123

关于T-SQL中的局部变量和全局变量：

• 局部变量：

  • 是由用户自己定义的，只在定义他的批处理和存储过程中使用。
  • 局部变量前用@符号做标记

• 全局变量：

  • 是由系统定义并保留在系统中
  • 变量前用@@标记
  • 用户只能使用系统预定义的全局变量，而不能自己定义全局变量
  • 注意：用户定义局部变量时候，不要与全局变量重名，否则将产生不可预测的后果
  • 常用的全局变量见参考中给出的官方文档链接,这里简单罗列几个常用的：
    • @@ERROR：返回最后一次执行T-SQL语句的错误值，在存储过程中可用于判读是否出现错误
    • @@VERSION：返回当前的SQL SERVER的版本
    • @@SERVERNAME：返回本地运行SQL SERVER的服务器名
    • @@ROWCOUNT:返回最近一次数据库操作所涉及的行数

---

1. 批处理

批处理是作为一个单元而进行分析和执行的一组T-SQL语句。

批处理需要经历的处理阶段：分析（语法检查）-->解析（检查查询的对象和列是否存在，是否具有访问权限）-->优化（作为一个执行单元）

批处理和事务是不一样的：简单的说一个批处理里中可以有多个事务！

在SQL SERVER Management中，可以使用GO命令，表示一个批处理结束标志。但是注意：GO命令是SQL SERVER Management工具的命令，而不是SQL SERVER服务器的命令。

GO命令可以带有参数，实现重复执行同一个批处理（SQL SERVER 2005），GO n,n为执行批处理的次数。

• 简单实例——重复执行批处理

SELECT SYSDATETIME()
GO 3

结果：

开始执行循环
---------------------------
2020-11-24 23:19:39.8413125

---------------------------
2020-11-24 23:19:39.8523131

---------------------------
2020-11-24 23:19:39.8573134

批处理执行已完成 3 次。

在执行批处理的时候首先是进行分析，如果批处理中出现语法错误，则整个批处理就不会执行。

• 简单实例——批处理中出现一个语法错误，则整个批处理中SQL都不会执行

DECLARE @Name NVARCHAR(50) ='Tom'
SELECT @Name
GO--以上是第一个批处理

SELECT GETDATE()
SELECT GETDAT()--有意制造一个错误
GO--以上是第二个批处理

执行结果：

------------
Tom

(1 行受影响)

消息 195，级别 15，状态 10，第 6 行
'GETDAT' 不是可以识别的 内置函数名称。

说明：第一批处理执行完毕，第二批处理中存在语法错误，故第二批处理中的所有SQL都没有执行。

批处理与变量：定义在批处理中的变量是该批处理的局部变量。

• 简单实例——变量作用域为定义其的批处理中

DECLARE @Name NVARCHAR(50) ='Tom'
GO
SELECT @Name

结果：
消息 137，级别 15，状态 2，第 3 行
必须声明标量变量 "@Name"。

说明：SELECT @Name不在定义变量@Name的批处理中，故执行失败

---

2. 条件逻辑——IF

IF……ELSE用于更具条件来控制SQL代码块的执行流程。如果条件取值为TRUE，则执行IF中的的SQL语句；如果条件取值为FALSE或UNKNOWN，则执行ELSE中的SQL语句（ELSE语句为可选）。

• IF……ELSE

DECLARE　@i int =100
IF @i=100
	PRINT 'i=100'
ELSE 
	PRINT 'i!=100'

• BEGIN……END

若是需要在IF或ELSE部分运行多条语句，则可以使用语句块。语句块使用BEGIN……END关键字识别,就相当于“{}”

DECLARE　@i int =100
IF @i=100
	BEGIN
		PRINT 'i=100'
	END
ELSE 
	BEGIN 
		PRINT 'i!=100'
	END

• 流程分支超过两个

在其他语句中，有if……else if……else的语法，在SQL中没有这样的语法，但是可以嵌套使用if……else

DECLARE　@i int =99
IF @i=101
	BEGIN
		PRINT 'i=101'
	END
ELSE 
	IF @i=100
		BEGIN 
			PRINT 'i=100'
		END
	ELSE
		BEGIN 
			PRINT 'i!=101 and i!=100'
		END

• 简单示例

判断当前数据库Test中是否存在Employee表

USE Test
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='Employee') 
    Begin 
        PRINT '当前Test数据库中存在Employee表'
    END
ELSE
    BEGIN
        PRINT '当前Test数据库中不存在Employee表'
    END

另外一种查询当前数据库中是否存在指定的T-SQL编程对象的方法

USE Test
GO
IF OBJECT_ID('Employee','U') IS NOT NULL 
    Begin 
        PRINT '当前Test数据库中存在Employee表'
    END
ELSE
    BEGIN
        PRINT '当前Test数据库中不存在Employee表'
    END

---

3. 逻辑分支——CASE

• CASE表达式是一个标量表达式，返回一个基于条件逻辑的值。CASE是一个表达式，而不是一条语句，也就是说不能用它来控制活的的流程，也不能根据条件逻辑做某些处理，相反，它只是根据条件逻辑来返回某个值，故称其为一个标量表达式。

• 注意：CASE WHEN是列出几个并行的条件，几个情况都是并列的，优先级从前往后；所以如果你的情况条件从小到大，那么遇到前边的，虽然可能符合多个条件，但是优先级是前边的优于后边的。所以遇到第一个符合的，就会取出这个条件需要的数据，而不会再往后判断。

• CASE是一个标量表达式，因此可以在任何允许使用标量表达式的地方使用它。例如：SELECT,WHERE,HAVING和ORDER BY子句中。

• 如果CASE表达式没有ELSE子句，默认为ELSE NULL

• 简单示例1——在SELCET语句中使用--简单格式表达式

常用的场景，将某个缩写词字段中的值替换为完整描述性的值

WITH Temp AS
(
	SELECT 'Tom' AS Name ,14 AS Age ,1 AS Grade
	UNION ALL
	SELECT 'Bob' AS Name ,15 AS Age ,2 AS Grade
	UNION ALL
	SELECT 'Jery' AS Name,16 AS Age,3 AS Grade
)
SELECT Name,Age,
	   CASE Grade 
	   WHEN 1 THEN '一年级'
	   WHEN 2 THEN '二年级'
	   WHEN 3 THEN '三年级'
	   ELSE 'NULL'
	   END AS Grade
FROM Temp

结果：
Name Age    Grade
---- -----  ------
Tom  14     一年级
Bob  15     二年级
Jery 16     三年级

• 简单示例2——在SELECT语句中使用--搜索格式

根据条件在查询结果集中添加一个列（字段）

WITH Temp AS
(
	SELECT 'Tom' AS Name ,13 AS Age 
	UNION ALL
	SELECT 'Bob' AS Name ,15 AS Age 
	UNION ALL
	SELECT 'Jerry' AS Name,16 AS Age
)
SELECT Name,Age ,
	   CASE
	   WHEN Age<=14 THEN '未成年'
	   WHEN Age>14 THEN '成年人'
	   END AS 类型
FROM Temp 

• 简单示例3——在ORDER BY语句中使用

排序的时候，既不是升序也不是降序，而是按照自定义的顺序排序

WITH Temp AS
(
	SELECT 'Tom' AS Name ,14 AS Age 
	UNION ALL
	SELECT 'Bob' AS Name ,15 AS Age 
	UNION ALL
	SELECT 'Jerry' AS Name,16 AS Age
)
SELECT Name,Age
FROM Temp
ORDER BY 
CASE 
WHEN Name='Jerry' THEN 1
WHEN Name='Tom' THEN 2
WHEN Name='Bob' THEN 3
END 

结果：

Name  Age
----- -----
Jerry 16
Tom   14
Bob   15

• 简单示例4——在WHERE语句中使用

使用情形1：根据不同的值选择不同的字段作为筛选条件中使用的字段。

当输入参数@Flag为1的时候，取数学成绩为100的学生，当输入参数@Flag为2的时候，取语文成绩为100的学生
当然这里也可使用使用IF语句判断不同的@Flag值执行不同的SELECT语句，这里使用CASE WHEN语句演示：

DECLARE @Flag INt=1;
WITH Temp AS
(
SELECT '张三' AS Name ,12 AS Age,'男' AS Gender,100 AS MathGrade,80 AS ChineseGrade
UNION ALL
SELECT '李四' AS Name, 14 AS Age,'女' AS Gender,90 AS MathGrade,80 AS ChineseGrade
UNION ALL
SELECT '王五' AS Name, 15 AS Age,'男'AS Gender,80 AS MathGrade,90 AS ChineseGrade
UNION ALL
SELECT '赵六' AS Name, 15 AS Age,'女'AS Gender,80 AS MathGrade,100 AS ChineseGrade
)
--select * from temp where (case when @Flag=1 then MathGrade=100 when @Flag=2  then ChineseGrade=100 end)--错误写法

--select * from temp where (case @Flag when 1 then MathGrade when 2  then ChineseGrade end)=100
--等价于下面写法：
SELECT * FROM Temp WHERE (CASE WHEN  @Flag=1 Then MathGrade WHEN @Flag=2 THEN ChineseGrade END)=100
--更常规，更便于理解的写法
SELECT * FROM temp WHERE (@Flag=1 AND MathGrade=100) OR (@Flag=2 AND ChineseGrade=100)

使用情形2：筛选条件中出现IN(……)，再对某个特定的值，做筛选

首先年龄在12~15岁的人，且15岁年龄的只取女性

WITH Temp AS
(
SELECT '张三' AS Name ,12 AS Age,'男' AS Gender,100 AS MathGrade,80 AS ChineseGrade
UNION ALL
SELECT '李四' AS Name, 14 AS Age,'女' AS Gender,90 AS MathGrade,80 AS ChineseGrade
UNION ALL
SELECT '王五' AS Name, 15 AS Age,'男'AS Gender,80 AS MathGrade,90 AS ChineseGrade
UNION ALL
SELECT '赵六' AS Name, 15 AS Age,'女'AS Gender,80 AS MathGrade,100 AS ChineseGrade
)
SELECT * FROM Temp WHERE Age IN (12,13,14,15) AND (CASE WHEN age=15 AND gender<>'女' THEN 0 ELSE 1 END)=1

结果：
张三   12          男      100         80
李四   14          女      90          80
赵六   15          女      80          100

---

4. 循环语句——WHILE

T-SQL中使用WHILE循环执行代码。只要在WHILE关键字之后指定的表达式为TRUE，WHILE会重复执行一个语句块

• 简单示例1——循环打印

DECLARE @i INT =0;
WHILE @i<10
BEGIN 
	PRINT @i;
	SET @i=@i+1;
END;

• 简单实例——打断循环

DECLARE @i INT =0;
WHILE @i<10
BEGIN 
	IF @i=6 BREAK;--当@i=6的时候，就停止循环
	PRINT @i;
	SET @i=@i+1;
END;

• 简单示例——跳出本次循环

DECLARE @i INT =0;
WHILE @i<10
BEGIN 
    SET @i=@i+1;
	IF @i=6 CONTINUE;--当@i=6的时候，跳出本次循环
	PRINT @i;
END;

• 注意：使用WHILE循环的时候，尤其是在配合IF语句的时候，注意不要进入死循环

---

5. 附录——关于CASE表达式的实现函数

T-SQL支持的某些函数，本质上是可以看作是CASE表达式的缩写:

• ISNULL() :使用指定的替换值替换 NULL。

DECLARE @Name VARCHAR 
SELECT  ISNULL(@Name,'')

• COALESCE() :返回列表中第一个非null表达的值。如果所有表达式求值为null，则返回null

DECLARE @Name VARCHAR 
DECLARE @Age INT
SELECT Coalesce(@Name,@Age,2)

结果：
Result
-------
2

• IFF() :根据布尔表达式计算为 true 还是 false，返回其中一个值。
  语法：IFF(boolean_expression, true_value, false_value)
  其作用就是一个三元运算符

DECLARE @a INT = 45, @b INT = 40;  
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result; 

结果：
Result
------
TRUE

关于IFF()的使用，其实可以替代CASE两个分支的情形，当前CASE分支是两个相互对象的情形的时候，可以使用IFF()简单的实现：

WITH Temp AS
(
	SELECT 'Tom' AS Name ,13 AS Age 
	UNION ALL
	SELECT 'Bob' AS Name ,15 AS Age 
	UNION ALL
	SELECT 'Jerry' AS Name,16 AS Age
)
SELECT Name,Age, IIF(Age<14,'未成年人','成年人') AS AgeType FROM temp

结果：
Name  Age         AgeType
----- ----------- --------
Tom   13          未成年人
Bob   15          成年人
Jerry 16          成年人

• CHOOSE:从值列表返回指定索引处的项。
  语法：CHOOSE ( int_index, val_1, val_2 [, val_n ] )
  第一个参数是后面值列表的索引，后面的参数就是值列表

注意值列表的索引是从1开始的计数的。

SELECT CHOOSE ( 3, '第一名', '第二名', '第三名', '第四名' ) AS Result;

结果：
Result
------
第三名

其作用和CASE的简单表达式的作用一样，将某一列缩写类的值转换为描述性的值

简单示例：根据出生日期，确定出生季节

WITH Temp AS
(
	SELECT 'Tom' AS Name ,'2020-1-11' AS Birthday
	UNION ALL
	SELECT 'Bob' AS Name ,'2020-5-11' AS Birthday 
	UNION ALL
	SELECT 'Jerry' AS Name,'2020-10-11' AS Birthday
)
SELECT Name, Birthday, 
CHOOSE(MONTH(Birthday), 'Spring','Spring','Spring','Summer','Summer', 'Summer','Autumn','Autumn','Autumn','Winter','Winter','Winter') AS Birth_Quarter
FROM Temp

结果：
Name  Birthday   Birth_Quarter
----- ---------- -------------
Tom   2020-1-11  Spring
Bob   2020-5-11  Summer
Jerry 2020-10-11 Winter

• NULLIF():需要两个参数,如果两个指定的表达式等价，则返回null

关于ISNULL函数，还有这样的用处：SQL遇到除零错误，使用NULLIF函数处理为0的分母

--首先SQL中一个数除NULL等于NULL
SELECT 1 / NULL;
WITH temp
AS (SELECT 1 AS a,
           0 AS b)
--NULLIF(b,0) ，是判断 b 是不是等于 0，如果 b = 0，则 a / b 这个算式返回 NULL值
--ISNULL(null,0)，如果 NULLIF 返回 NULL值，那就直接给个默认值 0
SELECT ISNULL((a / NULLIF(b, 0)), 0) [a/b]
FROM temp;

结果：

a/b
------
0

---

6. 参考

• SQL文档：变量

  • Docs/SQL/参考/Transact-SQL (T-SQL)/参考/函数/配置

  • Docs/SQL/参考/Transact-SQL (T-SQL)/参考/函数/系统

• SQL文档

• SQL SERVER 2012 T-SQL基础教程