shanzm

shanzm

关注
发信
关注(28)粉丝(399)

T-SQL——基础语法

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

0. 定义变量

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

  • 语法：使用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仅是通过查询字段赋值给某个变量，则是完全没有问题的


DECLARE @Age VARCHAR(50),@Name VARCHAR(50)
WITH Temp AS
(
SELECT '张三' AS Name ,12 AS Age
UNION ALL
SELECT '李四' AS Name, 14 AS Age
UNION ALL
SELECT '王五' AS Name, null AS Age
)
SELECT @Name=Name ,@Age=Age FROM Temp WHERE Name='张三'
SELECT @Name, @Age

结果：
----- ------
张三   12

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是一个标量表达式，因此可以在任何允许使用标量表达式的地方使用它。例如：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

6. 参考

12-01 06:13
Powered by LMLPHP ©2024 shanzm 0.070749