SQL Server教程 - T-SQL-编程基础

更新记录
转载请注明出处：https://www.cnblogs.com/cqpanda/p/16527406.html
2022年7月29日 发布。
2022年7月2日 从笔记迁移到博客。

T-SQL说明

T-SQL(Transact Structured Query Language)
T-SQL是标准SQL的扩展，专用于SQL Server
用于SQL Server的特定SQL语言
界定符号[]

T-SQL官方示例规范

T-SQL语言组成

过程化编程语言结构 和 SQL语句
过程化编程语言结构主要有：注释、变量、运算符、流程控制。
SQL语句主要有：DDL、DML、DCL、DQL。

DDL组成：

DML组成：

DCL组成：

附加内容：
BEGIN TRANSACTION、COMMIT、ROLLBACK、SET STRASACTION、DECLARE OPEN、FETCH、CLOSE、EXECUTE。

SQL Server标识符

SQL Server标识符分类

常规标识符(Regular Identifier) 和 分隔标识符(Delimited Identifier)

常规标识符(Regular Identifier)

服务器、数据库、数据库对象(表、视图、列、索引、触发器、过程、约束和规则)都需要标识符来定义其名称。

标识符格式：Unicode字符或_(下划线)或@或#开头

注意：
标识符最长128个字符，标识符不区分大小写。
以@开头的标识符一般表示局部变量或函数参数。
以#开头的标识符一般表示临时表或存储过程，比如：#some_table表示临时表。
以##开头的标识符表示全局临时对象，比如：##some_identity 表示全局临时表。
以@@开头的标识符一般表示全局变量。
标识符的字符控制在128个字符内。
如果标识符包含空格，将标识符放在大括号内[]，比如：[some identity]。

分隔标识符(Delimited Identifier)

分隔标识符" [] "
标识符遵守标识符命名规则 "[]"可以被省略
最好不要省略分隔标识符

数据库对象名组成

[服务器名].[数据库名].[拥有者名].[对象名]

server_name .[database_name].[schema_name].object_name
| database_name.[schema_name].object_name
| schema_name.object_name
| object_name

server_name:指定连接的服务器或远程服务器。
database_name:表示如果对象驻留在SQL Server的本地实例中，则指定SQL Server数据库。如果对象在连接的服务器中，则database_name将指定OLEDB目录。
schema_name:表示如果对象在SQL Server 数据库中，则指定包含对象的架构名称。如果对象在连接的服务器中，则schema_name将指定 OLEDB 架构名称。
object_name:表示对象名称。

实例：

批处理和脚本

批处理说明

一条或多条SQL语句的集合，一个批作为一个字符串交给服务器去执行。
使用GO表示批，一个批出现错误，批中的语句都将会被回滚。

批处理定义

GO
    -- SQL语句
GO

注意：
GO必须单独一行，除了注释不可以有其他内容
GO区域内定义的局部变量只在GO区域内有效
限制：
CREATE DEFAULT、CREATE PROCEDURE、CREATE FUNCTION、CREATE RULE
CREATE TRIGGER、CTEATE VIEW不可以再同一个批中

脚本文件说明

一个或多个批组成
使用GO表示批，如果没有GO，则整个文件被视为一个批
外在表现是文件

运算符

算术运算符

注意：+运算符还可以用于连接字符串

SELECT N'Panda' + N'Panda';

注意：如果需要计算时间差可以使用DATEDIFF函数

SELECT DATEDIFF(DAY,CAST('20200101' AS DATE),  CAST('20190101' AS DATE));

比较运算符

注意：比较的结果是true/false/unknown之一
注意：

逻辑运算符

AND 		如果两个逻辑表达式都为TRUE,那么结果为TRUE
OR 			如果两个逻辑表达式中的一个为TRUE,那么结果为TRUE
NOT 		对任何其他逻辑运算符的值取反
BETWEEN 	如果操作数在某个范围之内，那么结果为TRUE
IN 			如果操作数等于表达式列表中的一个，那么结果为TRUE
LIKE  		如果操作数与一种模式相匹配，那么结果为TRUE
ANY/SOME 	如果一组的比较中任何一个为TRUE,那么结果为TRUE
ALL 		如果一组的比较都为TRUE,那么结果为TRUE
EXISTS 		如果子查询包含一些行，那么结果为TRUE

位运算符

注意：位运算符的操作数可以是整型或二进制字符串数据类型分类中的任何数据类型(但 image数据类型除外〉
注意：两个操作数不能同时是二进制字符串数据类型分类中的某种数据类型

连接运算符

加号(＋)是字符串串接运算符，可以将两个或两个以上字符串合并串接成一个字符串。其他所有字符串操作则可以调用字符串函数(如SUBSTRING())进行处理。默认情况下，对于varchar数据类型的数据，在INSERT或赋值语句中，空的字符串将被解释为空字符串。在串接varchar、char或text数据类型的数据时，空的字符串被解释为空字符串。例如：'abc'+"+'def'被存储为'abcdef'

运算符优先级

表达式

根据连接表达式的运算符进行分类，可以将表达式分为算术表达式、比较表达式、逻辑表达式、按位运算表达式和混合表达式等；根据表达式的作用进行分类，可以将表达式分为字段名表达式、目标表达式和条件表达式。

1.字段名表达式

字段名表达式可以是单个字段或几个字段的组合，还可以是由字段、作用于字段的集合函数和常量的任意算术运算(＋、－、＊、／)组成的运算表达式。主要包括数值表达式、字符表达式、逻辑表达式和日期表达式4种。

2.目标表达式

目标表达式有4种构成方式。
(1)*:表示选择相应基表和视图的所有字段。
(2)<表名>.*：表示选择指定的基表和视图的所有字段。
(3)集函数()：表示在相应的表中按集函数操作和运算。
(4)[<表名>].字段名表达式［，［＜表名＞．］＜字段名表达式＞］．．．表示按字段名表达式在多个指定的表中选择。

3.条件表达式

常用的条件表达式有以下6种：
(1)比较大小－应用比较运算符构成表达式，主要的比较运算符有＝、＞、＞＝、＜、＜＝、！＝、、！＞(不大于)、＜(不小于)、NOT(与比较运算符相同，对条件求非)。
(2)指定范围－(NOT)BETWEEN...AND...运算符查找字段值在或者不在指定范围内的记录。BETWEEN后面指定范围的最小值，AND后面指定范围的最大值。
(3)集合(NOT)IN-查询字段值属于或者不属于指定集合内的记录。
(4)字符匹配－(NOT)LIKE<匹配字符串＞＇［ESCAPE'<换码字符＞］查找字段值满足＜匹配字符串＞中指定匹配条件的记录。＜匹配字符串＞可以是一个完整的字符串，也可以包含通配符“”和“％”，“”代表任意单个字符，“％”代表任意长度的字符串。
(5)空值IS(NOT)NULL-查找字段值为空(不为空)的记录。NULL不能用来表示无形值、默认值、不可用值、以及取最低值或取最高值。SQL规定，在含有运算符＋、－、＊、／的算术。表达式中，若有一个值是空值，则该算术表达式的值也是空值；任何一个含有NULL比较操作结果的取值都为FALSE.
(6)多重条件AND和OR-AND表达式用来找出字段值同时满足AND相连接的查询条件的记录。OR表达式用来找出字段值满足OR连接的查询条件中的记录。AND运算符的优先级高于OR运算符。

通配符

注释

单行(ANSI标准注释符)

-- 这是单行注释

多行(C语言类似)

/* 这是多行注释 */

常量与字面量

常量：内存中始终不会变化的量，也称标量值

常见常量与字面量：

123             /*整型*/
12.3            /*浮点型*/
$12.3            /*decimal带货币前缀*/
￥12.3           /*decimal带货币前缀*/
'Panda'         /*字符串类型常量*/	
N'Panda'        /*Unicode字符串，注意N一定要大写*/
'2019-1-1'      /*日期类型，指定时间*/
'6F961-9FF-8B86-DOII-B42D-OOC04-FC964FF'  /*uniqueidentifier*/
CURRENT_TIME    /*符号常量，当前时间*/

变量

说明

SQL Server中变量分为： 局部变量 和 全局变量

局部变量

说明：

局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部。在批处理和脚本中变量可以有如下用途：作为计数器计算循环执行的次数或控制循环执行的次数，保存数据值供控制流语句测试，以及保存由存储过程代码返回的数据值或者函数返回值。

注意：

​ 数据类型不可以是TEXT、NTEXT、IMAGE类型
​ 局部变量被引用时要在其名称前加上标志 "@"
​ 如果不为局部变量赋值，则默认赋值为null

定义局部变量：

DECLARE @panda INT;         /* 定义局部变量 */
DECLARE @panda2 INT = 666;  /* 定义局部变量并赋值 */
DECLARE @panda1 INT, @panda2 INT; /* 定义多个局部变量 */

赋值：

SELECT @panda = 666;
SELECT @panda = (SELECT COUNT(*) FROM Table);
SET @panda = 666;

使用：

SELECT @panda1, @panda2;

注意：使用的时候需要带@符号

局部变量限制：不能在同一个批处理中更改表，然后引用新列。

全局变量

说明：
在全局可用
系统预定义
用户不可以定义全局变量
用户不可以修改全局变量
全局变量以@@开头

常用全局变量：

＠＠CONNECTIONS 	返回SQL Server自上次启动以来尝试的连接数，无论连接是成功还是失败
＠＠CPU_BUSY 		返回SQL Server自上次启动后的工作时间。
				其结果以CPU时间增量或“滴答数”来表示，此值为CPU工作时间的累积值，因此，可能会超出实际占用CPU的时间。
				乘以＠＠TIMETICKS即可转换为微秒。
＠＠CURSOR_ROWS 	返回连接的数据库上打开的上一个游标中的当前限定行的数目，为了提高性能。							
				SQL Server可异步填充大型键集和静态游标。可调用＠＠CURSOR_ROWS以确定当其被调用时检索了游标符合条件的行数。
＠＠DATEFIRST 	针对会话返回 SET DATEFIRST的当前值。
＠＠DBTS 			返回当前数据库的当前 timestamp 数据类型的值。这一时间戳值在数据库中必须是唯一的。
＠＠ERROR 		返回执行的上一个Transact-SQL语句出现错误时对应的错误编号。
＠＠FETCH_STATUS 	返回针对连接的数据库当前打开的任何游标，发出的上一条游标FETCH语句的状态。
＠＠IDENTITY 		返回插入到数据表的IDENTITY列的最后一个值。
＠＠IDLE 			返回SQL Server自上次启动后的空闲时间。结果以CPU时间增量或“时钟周期”来表示，是所有的累积值
				 因此该值可能超过实际经过的时间。乘以＠＠TIMETICKS即可转换为微秒。
＠＠IO_BUSY 		返回自 SQL Server最近一次启动以来，SQL Server已经用于执行输入和输出操作的时间。
				其结果是CPU时间增量(时钟周期)，是CPU执行操作的累积值，这个值可能超过实际消逝的时间。
				乘以＠＠TIMETICKS即可转换为微秒。
＠＠LANGID 		返回当前使用的语言对应的本地语言标识符(ID)。
＠＠LANGUAGE 		返回当前所用语言的名称。
＠＠LOCK_TIMEOUT 	返回当前会话的锁定超时的设置值(单位为毫秒)。
＠＠MAX_CONNECTIONS 	返回 SQL Server 实例允许同时进行的最大用户连接数。返回的数值不一定是当前配置的数值。
＠＠MAX_PRECISION 	按照服务器中的当前设置，返回decimal和numeric 数据类型所用的精度级别。默认情况下，最大精度级别38.
＠＠NESTLEVEL 		返回在本地服务器上执行的当前存储过程的嵌套级别(初始值为0).
＠＠OPTIONS 			返回有关当前SET选项的信息。
＠＠PACK_RECEIVED  	返回 SQL Server 自上次启动后从网络读取的输入数据包数。
＠＠PACK_SENT 		返回SQL Server自上次启动后写入网络的输出数据包个数。
＠＠PACKET_ERRORS 	返回自上次启动 SQL Server后，在SQL Server 连接上发生的网络数据包错误数。
＠＠ROWCOUNT 			返回上一次语句影响的数据行的行数。
＠＠PROCID 			返回 Transact-SQL当前模块的对象标识符(ID).Transact-SQL模块可以是存储过程、用户定义函数或触发器。
					不能在CLR模块或进程内的数据访问接口中指定@@PROCID.
＠＠SERVERNAME 		返回运行SQL Server的本地服务器的名称。
＠＠SERVICENAME 		返回SQL Server正在运行的注册表项的名称。若当前实例为默认实例，则＠＠SERVICENAME返回MSSQLSERVER;
					若当前实例是命名实例，则该函数返回该实例名。
＠＠SPID 				返回当前用户进程的会话ID.
＠＠TEXTSIZE  		返回SET语句的TEXTSIZE选项的当前值，
					它指定 SELECT 语句返回的text或image数据类型的最大长度，其单位为字节。
＠＠TIMETICKS  		返回每个时钟周期的微秒数。
＠＠TOTAL_ERRORS 		返回自上次启动SQL Server之后，SQL Server所遇到的磁盘写入错误数。
＠＠TOTAL_READ  		返回 SQL Server自上次启动后，由SQL Server 读取(非缓存读取)的磁盘的数目。
＠＠TOTAL_WRITE  		返回自上次启动SQL Server以来，SQL Server所执行的磁盘写入数。
＠＠TRANCOUNT  		返回当前连接的活动事务数。
＠＠VERSION  			返回当前安装的日期、版本和处理器类型。

实例：

SELECT @@CONNECTIONS, @@VERSION, @@SERVERNAME;

流程控制

概述

语句块

说明：将多条SQL语句作为一个逻辑快
实现：

实现：

BEGIN
    ....
END

注意：语句块内定义的局部变量在块外还可以使用.

条件语句-IF

说明：一选一

语法：

IF <条件表达式>
语句或语句块

实例：

DECLARE @Age INT = 666;
IF @Age >= 666
BEGIN
    PRINT N'牛逼了！666岁！';
END

条件语句-IF ELSE

说明：二选一
语法：

IF <条件表达式>
语句或语句块
ELSE
语句或语句块

实例：

DECLARE @X INT = 666;
IF @X >= 666
    BEGIN 
        PRINT N'Panda666';
    END
ELSE
    BEGIN
        PRINT N'Panda888';
    END

条件函数-IIF

IIF ( boolean_expression, true_value, false_value )

实例：

SELECT IIF (50 > 20, 'TRUE', 'FALSE') AS RESULT;

条件语句-CASE(简单)

说明：多选一
分类：
分为简单CASE和复杂CASE
区别在于是否在WHEN中进行逻辑判断
THEN必须返回值

简单CASE语法：

CASE <表达式>
    WHEN <值> THEN <T-SQL>
    WHEN <值> THEN <T-SQL>
    WHEN <值> THEN <T-SQL>
    ELSE <T-SQL>
END

实例：

DECLARE @Age int = 18
DECLARE @RESULT CHAR(50) = 
CASE @Age
    WHEN 18
        THEN N'AGE = 18'
    WHEN 19
        THEN N'AGE = 19'
    ELSE
        N'UNKNOWN AGE'
END

实例：

SELECT Title,
    CASE Title
    WHEN 'Mr.' THEN 'Male'
    WHEN 'Ms.' THEN 'Female'
    WHEN 'Mrs.' THEN 'Female'
    WHEN 'Miss' THEN 'Female'
    ELSE 'Unknown' END AS Gender

条件语句-CASE(复杂)

复杂CASE语法：

CASE
    WHEN <条件表达式>   THEN  <T-SQL>
    WHEN <条件表达式>   THEN  <T-SQL>
    WHEN <条件表达式>   THEN  <T-SQL>
    ELSE  <T-SQL>
END

实例：

DECLARE @age int = 18
DECLARE @RESULT CHAR(50) = 
CASE
    WHEN @age = 18
        THEN N'AGE = 18'
    WHEN @age > 18
        THEN N'AGE = 19'
    ELSE
        N'AGE < 18'
END
PRINT @RESULT;

循环语句-WHILE

语法：

WHILE <条件表达式>
BEGIN
    <T-SQL>
END

注意：
WHILE中可以使用CONTINUE和BREAK

实例：

DECLARE @Age int = 20
WHILE @age > 0
    BEGIN
        SET @age = @age-1
        PRINT @age
    END

返回

语法：

RETURN [整数值]  -- 用于从过程、批处理、语句块中退出

注意：RETURN并不是非要带参数，直接返回可以直接使用RETURN

常见返回值的含义：

退出

BREAK;
 
CONTINUE;

注意：除非另有说明，所有系统过程均返回0值。此值表示成功，而非零值则表示失败。RETURN语句不能返回空值。

自定义错误处理

默认使用SQL Server提供的错误处理，如果需要自定义错误处理可以使用以下结构：

BEGIN TRY
    -- SQL
END TRY
BEGIN CATCH
    --SQL
END CATCH

可以在CATCH中使用以下语句抛出错误：

THROW {error_number | @local_variable},
{ message | @local_variable },
{ state | @local_variable };

注意：error_number必须在50000和2147483647之间。

跳转

LABEL:
  <T-SQL>
GOTO LABEL

注意：LABEL也可以在GOTO后面

等待

支持两种等待：延时再执行 或 指定时间执行

延时再执行语法：

WAITFOR DELAY '时间'; -- 注意：是时间不是日期

注意：最多24小时

指定时间执行语法：

WAITFOR TIME '时间'; -- 注意：是时间不是日期

注意：最多24小时

延时再执行实例：

WAITFOR DELAY '00:00:03';
PRINT '1';
PRINT '2';

指定时间执行实例：

WAITFOR TIME '10:40:59';
PRINT '1';
PRINT '2';

常用命令

输出到控制台

语法：

PRINT 内容;

实例：

PRINT 'Panda666';
PRINT 666;

数据库一致性检测程序。

检测指定数据库的磁盘空间分配结构的一致性
语法：

DBCC CHECKALLOC('DataBaseName');

关闭

备份数据库

语法：

BACKUP DATABASE 数据库名 TO disk='文件名.bak';

实例：

BACKUP DATABASE [panda_test] TO disk='D:\test.bak';

恢复数据库

语法：

RESTORE DATABASE [数据库名] from disk='备份文件名.bak' WITH REPLACE;

SET命令

SET语句在不同的场景有不同的作用：

比如：

日期和时间语句

SET DATEFIRST
SET DATEFORMAT

锁定语句

SET DEADLOCK PRIORITY
SET LOCK TlMEOUT