T-SQL精简笔记总结

以下为精心总结的学习笔记，内容有点多，喜欢的朋友建议点赞收藏哦！


  虽然工作中用了很多关于SQL方面的技术，但没有静下心来看一本完整的关于数据库方面的系统书籍，正好最近在看关于这方面的书，所以就对于自己学习的过程，对于一些重点的知识点记录下来。
  T-SQL(Transact Structured Query Language)时标准的 Microsoft SQL Server 的扩展，是标准的SQL程序设计语言的增强版，是用来让程序与SQL Server沟通的主要语言。

概述

T-SQL主要由以下3部分组成：

  数据定义语言(Data Definition Language,DDL)：用于在数据库系统中对数据库、表、视图、索引等数据库对象进行创建和管理；
  数据控制语言(Data Control Language,DCL)：用于实现对数据库中数据的完整性、安全性等的控制；
  数据操纵语言(Data Manipolation Language,DML)：用于增删查改数据库中的数据。

T-SQL语句分类：

  变量说明语句： 用来说明变量的命令；
  数据定义语句： 用来建立数据库、数据库对象和定义列，大部分是以CREATE开头的命令，如CREATE TABLE、CREATE VIEW、DROP TABLE等；
   数据操纵语句： 用来操纵数据库中数据的命令，如SELECT、INSERT、UPDATE、DELETE和CURSOP等；
  数据控制语句： 用来控制数据库组件的存取许可、存取权限等命令，如GRANT、REVOKE等；
  流程控制语句： 用于设计应用程序流程的语句，如IF WHILE和CASE等；
  内嵌函数： 说明变量的命令；
  其他命令： 嵌于命令中使用的标准函数。

SQL 基础

局部变量

  局部变量是用户自定义的变量，其作用范围仅在程序内部。局部变量采用 @ 作为标记前缀，用 DECLARE 申明定义局部变量。

-- 定义局部变量“localVar”
DECLARE @localVar NVARCHAR(50)
-- 给局部变量赋值
SET @localVar='这是一个局部变量'
-- 打印输出局部变量
PRINT @localVar
-- 查询“StuName”为“张三”的“Age”并赋值给临时变量
SELECT @localVar=Age FROM tableName WHERE StuName='张三'
PRINT @localVar
-- 申明多个变量
DECLARE @num1 int,@num2 int
SET @num1=3
SET @num2=2
PRINT @num1+@num2

全局变量

  全局变量是SQLServer系统内部事先定义好的变量，不用用户参与定义，其作用范围不限于某一个程序，而是任何程序均可随时调用。以两个 at 符号 (@@) 开头进行调用。
  其实，全局变量并不能完全叫做全局变量。对于全局变量，微软有自己的说法：

微软说明
一些 Transact-SQL 系统函数的名称以两个 at 符号 (@@) 开头。 尽管在旧版 SQL Server 中，@@ 函数称为全局变量，但 @@ 函数不是变量，不具有等同于变量的行为。 @@函数是系统函数，语法遵循函数规则。   不能在视图中使用变量。   事务回滚不影响对变量所做的更改。

序号	全局变量	说明
1	@@CONNECTIONS	记录自最后一次服务器启动以来，所有针对这台服务器进行的连接数目，包括没有链接成功的尝试。使用 @@CONNECTIONS 可以让系统管理员很容易地得到今天所有尝试连接本服务器的连接数目。
2	@@CPU_BUSY	记录自上次启动以来尝试的连接数，无论连接成功与否，都以ms为单位的CPU工作时间。
3	@@CURSOR_ROWS	返回在本次服务器连接中，打开游标1 取出数据行的数目。
4	@@DATEFIRST
5	@@DBTS	返回当前数据库中timestamp数据类型的当前值。
6	@@ERROR	返回执行上一条T-SQL语句所返回的错误代码。在SQL Server服务器执行完一条语句后，如果该语句执行成功，则将返回 @@ERROR 的值为0，如果该语句执行过程中发生错误，则返回错误的信息，而 @@ERROR 将返回响应的错误编码，该标号将一直保持下去，直到下一条语句得到执行位置。由于 @@ERROR 在每一条语句执行后被清除并且重置，应在语句验证后立即检查它，或将其保存到一个局部变量中以备事后查看。
7	@@FETCH_STATUS	返回上一次使用游标FETCH操作所返回的状态值，且返回值为整型。以下为返回值示例：   0：FETCH语句成功；   -1：FETCH语句失败或此行不在结果集中；   -2：被提取的行不存在。
8	@@IDENTITY	返回最近一次插入的identity列的数值，返回值是numeric。
9	@@IDLE	返回以ms为单位计算SQL Server服务器自最近一次启动以来处于停顿状态的时间。
10	@@IO_BUSY	返回以ms为单位计算的SQL Server服务器自最近一次启动以来华仔输入和输出上的时间。
11	@@LANGID	返回当前使用的语言的本地语言标识符（ID）。
12	@@LANGUAGE	返回当前所有语言的名称。
13	@@LOCK_TIMEOUT	返回当前对数据锁定的超时设置。
14	@@MAX_CONNECTIONS	返回SQL Server实例允许同时进行的最大用户连接数，返回的数值不一定是当前配置的数值。
15	@@MAX_PRECISION	按照服务器当前设置，返回decimal和numeric数据类型所用的精度级别。
16	@@NESTLEVEL	返回本地服务器上执行的当前存储过程的嵌套级别（初始值为0）。
17	@@OPTIONS	返回有关当前SET选项的信息。
18	@@PACK_RECEIVED	返回SQL Server服务器自最近一次启动以来一共从网络上接受数据数目。
19	@@PACK_SENT	返回SQL Server自上次启动后写入网络读取的输入数据包数。
20	@@PACKET_ERRORS	返回自上次启动SQL Server后，在SQL Server连接上的网络数据报错误数。
21	@@PROCID	返回当前存储过程的ID标识。
22	@@REMSERVER	返回在登录记录中记载远程SQL Server服务器的名称。
23	@@ROWCOUNT	返回上一条SQL语句所影响的数据行的数目。对所有不影响数据库数据的SQL语句，这个全局变量返回的结果是0。在进行数据库编程时，经常要检测 @@ROWCOUNT 的返回值，以便明确所执行的操作是否达到了目标。
24	@@SERVERNAME	返回运行SQL Server的本地服务器的名称 。
25	@@SERVICENAME	返回SQL Server正在其下运行的注册表项的名称，若当前实例为默认实例，则@@SERVICENAME返回MSSQLSERVER。
26	@@SPID	返回当前服务器进程的ID标识。
27	@@TEXTSIZE	返回SET语句中的TEXTSIZE选项的当前值 。
28	@@TIMETICKS	返回每个时钟周期的微秒数。
29	@@TOTAL_ERRORS	返回自SQL Server服务器启动以来，所遇到读写错误的总数。
30	@@TOTAL_READ	返回自SQL Server服务器启动以来，读磁盘的次数。
31	@@TOTAL_WRITE	返回自SQL Server服务器启动以来，写磁盘的次数。
32	@@TRANCOUNT	返回当前连接中，处于活动状态事务的数目。
33	@@VERSION	返回当前SQL Server服务器安装日期、版本，以及处理器的类型。

注释符、运算符、通配符

  注释符： 注释符是对代码给出解释或者说明。注释语句不是可执行语句，不参与程序的编译，通常是一些说明性的文字，对代码或者功能的实现方式给出和简要的解释和提示。

-- 这是行注释，不参与程序的编译与执行
/* 这是块注释，在注释符内的内容不参与程序的编译与执行 */

  运算符： 运算符是一种符号，用来进行常量、变量或者列之间的数学运算和比较操作，包括算术运算符、赋值运算符、比较运算符、逻辑运算符、位运算符、连接运算符等。
   1. 算数运算符： 算数运算符在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何数据类型。算数运算符包括：＋(加)、-(减)、*(乘)、/(除)、%(取余)等。

-- 申明变量
DECLARE @num1 int,@num2 int
-- 给变量赋值
SET @num1=3
SET @num2=2
-- 加
PRINT @num1+@num2
-- 减
PRINT @num1-@num2
-- 乘
PRINT @num1*@num2
-- 除
PRINT @num1/@num2
-- 取余
PRINT @num1%@num2

   2. 赋值运算符： 即（=），在算数运算符处介绍展示的给变量赋值便是赋值运算符的应用。
   3. 比较运算符： 比较运算符是对两个表达式作比较。除了text、ntext或image数据类型的表达式外，比较运算符可以用于所有的表达式。比较运算符包括：>(大于)、<(小于)、=(等于)、>=(大于或等于)、<=(小于或等于)、<>(不等于)、!=(不等于)、!>(不大于)、!<(不小于)，其中!=、!>、!<不是ANSI标准的运算符。比较运算符的结果为布尔数据类型，有3各值：TRUE、FALSE、UNKNOWN。
   4. 逻辑运算符： 逻辑运算符对某个条件进行测试，以获得其真实情况。逻辑运算符和比较运算符一样，返回带有TRUE或FALSE值的布尔数据类型。下面这个表格，说明列为记录的笔记，讲的比较抽象，建议还是直接看后面的 Demo 吧。

运算符	说明	Demo
ALL	如果一个比较集中全部都是TRUE，则值为TRUE。	查询出表1中，“Age”字段大于表2中所有数据的“Age”字段的数据： SELECT * FROM table1 WHERE Age > ALL (SELECT Age FROM table2); 类似于： SELECT * FROM table1 WHERE Age > (SELECT MAX(Age) FROM table2);
AND	如果两个布尔表达式均为TRUE，则值为TRUE。	查询出表1中“StuName”字段等于“张三”，并且“Age”字段大于12的数据： SELECT * FROM table1 WHERE StuName='张三' AND Age>12
ANY	如果一个比较集中任何一个为TRUE，则值为TRUE。	查询出表1中，“Age”字段大于表2中任意一条数据的“Age”字段的数据 SELECT * FROM table1 WHERE Age > ANY (SELECT Age FROM table2); 类似于： SELECT * FROM table1 WHERE Age > (SELECT MIN(Age) FROM table2);
BETWEEN	如果操作数是在某个范围内，则值为TRUE。	查询处表1中，“Age”字段数据在8到12之间的数据： SELECT * FROM table1 WHERE Age BETWEEN 8 AND 12;
EXISTS	如果子查询包含任何行，则值为TRUE。	查询表1中，存在“Age”字段的值为16，则输出全部数据，否则为空表： SELECT * FROM tableName WHERE EXISTS (SELECT * FROM tableName WHERE Age=16);
IN	如果操作数与一个表达式列表中的某个相等的话，则值为TRUE。	查询表1中，“StuName”字段在表2中的“StuName”字段存在的数据： SELECT * FROM tableName WHERE StuName IN (SELECT StuName FROM table2)
LIKE	如果操作数匹配某个模式的话，则值为TRUE。	查询表1中，“StuName”字段姓张的所有数据： SELECT * FROM tableName WHERE StuName LIKE '张%'
NOT	对任何其他布尔运算符的值取反。	查询表1中，“StuName”字段不为空的数据： SELECT * FROM table1 WHERE StuName IS NOT NULL
OR	如果任何一个布尔表达式时TRUE，则值为TRUE。	查询出表1中“StuName”字段等于“张三”，或者“Age”字段大于12的数据： SELECT * FROM table1 WHERE StuName='张三' OR Age>12
SOME	如果一个比较集中的某些为TRUE，则值为TRUE。	查询表1中，“Age”字段大于或者等于表2中的“Age”字段的值： SELECT * FROM tableName WHERE Age>= SOME (SELECT * FROM table2)

   5. 位运算符： 位运算符的操作数可以时整数（将整数转为二进制数据后在进行运算，运算结束后将二进制结果转成十进制输出）数据类型或者二进制串数据类型（image数据类型除外）范畴的。

运算符	说明	Demo
&	按位AND	示例：PRINT 1&2; 输出：0
^	按位互斥OR	示例：PRINT 1^2; 输出：3
~	按位NOT	示例：PRINT ~2; 输出：-3
|	按位OR	示例：PRINT 1|2; 输出：3

   6. 字符串连接运算符： 连接运算符“＋”用于连接两个或两个以上的字符或二进制串、列名或者串和列的混合体，将一个串加入另一个串的末尾。例如：PRINT '这是一个字符串：'+'String';
   7. 通配符： 匹配指定范围内或属于方括号所指定的集合中的任意单个字符。

通配符	描述	示例
%	包含零个或更多字符的任意字符	loving% 可以表示：“lovingc”、“loving you”、“loving”等。
_（下划线）	任何单个字符	loving% 可以表示：“lovingc”，后面只能再接一个字符。
[]	指定范围（[a~f]）或集合（[abcdef]）中的任何单个字符	[0~9]123 表示以0~9任意一个字符开头，以“123”结尾的字符串。
[^]	不属于指定范围（[a~f]）或集合（[abcdef]）中的任何单个字符	[0~5]123 表示不以0~5任意一个字符开头，却以“123”结尾的字符串。

流程控制

  BEGIN…END——用于将多个T-SQL语句组合为一个逻辑块。当流程控制语句必须执行一个包含两条或两条以上的T-SQL语句的语句块时，使用BEGIN…END语句。

BEGIN
	{SQL语句块}
END

  IF——条件判断语句，当 IF 条件成立的情况下执行条件判断块里面的语句。

IF <条件表达式>
	-- 当条件表达式为 TRUE 时，执行此语句块
	BEGIN
		{SQL语句块}
	END

  IF…ELSE——选择结构语句。

IF <条件表达式>
	-- 当条件表达式为 TRUE 时，执行此语句块
	BEGIN
		{SQL语句块}
	END
ELSE
	-- 当条件表达式为 FALSE 时，执行此语句块
	BEGIN
		{SQL语句块}
	END

  CASE——解决多种选择的情况。

SELECT *,
CASE
	WHEN Age<12 THEN '小学'
	WHEN Age>=12 AND Age<15 THEN '初中'
	WHEN Age>15 THEN '高中'
END AS 年级
FROM table1

  WHILE——循环结构，在条件表达式成立的情况下，执行循环体内的语句。

WHERE <条件表达式>
	BEGIN
		{SQL语句块}
	END

  WHILE…CONTINUE…BREAK——带终止循环的循环语句。CONTINUE 和 BREAK 在循环体内使用，CONTINUE 表示跳出此次循环进入下一次循环，BREAK 表示跳出循环体。

WHILE <条件表达式>
	BEGIN
		-- CONTINUE 表示跳出此次循环继续进入下一次循环
		CONTINUE
		-- BREAK 表示跳出整个循环体
		BREAK
	END

  RETURN——表示从查询或者存储过程中无条件退出。RETURN 可以在任何地方用于从存储过程、批处理或语句中退出，位于 RETURN 之后的语句不会继续执行。

RETURN [返回值]

  在括号中指定一个返回值。如果没有指定返回值，SQL Server 系统会根据程序执行的结果返回一个内定值，内定值结果如下表所示：

返回值	含义		返回值	含义
F	程序执行成功		-7	资源错误，如磁盘空间不足
-1	找不到对象		-8	非致命的内部错误
-2	数据类型错误		-9	以达到系统的极限
-3	死锁		-10或-11	致命的内部不一致性错误
-4	违反权限原则		-12	表或指针破坏
-5	语法错误		-13	数据库破坏
-6	用户造成的一般错误		-14	硬件错误

  GOTO——用来改变程序执行的流程，使程序跳到标识符指定的程序行再继续向下执行。标识符需要在其后加上一个冒号 :。

DECLARE @x INT
SELECT @x=1
loving:
	PRINT @x
	SELECT @x=@x+1
WHILE @x<=3 GOTO loving

  WAITFOR——WAITFOR 指定触发器、存储过程或事务执行的时间²、时间间隔或时间；还可以用来暂时停止程序的执行，直到所设定的等待时间已过才继续往下执行。

-- DELAY：用来设定等待的时间，最多可达24小时。
-- TIME：用来设定等待结束的时间点。
WAITFOR DELAY<'时间'>|TIME<'时间'>

常用命令

  DBCC——用于验证数据库完整新、查找错误和分析系统使用情况等，有两种命令：
  1. DBCC CHECKALLOC ：检查指定数据库的磁盘空间分配结构的一致性：

DBCC CHECKALLOC ('数据库名称')

  2. DBCC SHOWCONTIG ：显示指定表的数据和索引的碎片信息：

DBCC SHOWCONTIG ('数据表名称')

  CHECKPOINT——用于检查当前工作的数据库中被更改的数据页或日志页，并将这些数据从数据缓冲器中强制写入硬盘。

-- 参数：表示以秒为单位指定检查点完成所需要的时间。
CHECKPOINT [参数]

  DECLARE——用于声明一个或多个局部变量、游标变量或表变量，案例如声明变量的案例。
  PRINT——向客户端返回一个用户自定义的信息，即显示一个字符串³、局部变量或全局变量的内容。
  RAISERROR——用于在 SQL Server 系统中返回错误信息时返回用户指定的信息。
  READTEXT——用于读取text、ntext或image列中的值，从指定的位置开始读取指定的字符数。
  BACKUP——用于将数据库内容或其事务处理日志备份到存储介质上⁴。计算机在操作过程中难免出现意外，为了保证用户数据的安全性，防止数据库中的数据意外丢失，应对数据库即使进行备份。
  RESTORE——用来将数据库或其事务处理日志备份文件由存储介质还原到 SQL Server 系统中。如果数据库中的数据发生丢失或被破坏，操作员应该及时还原数据库，尽可能地减少损失。注意：执行还原操作时，需要将使用的数据库更改为 master。
  SELECT——SELECT 语句除了具有强大的查询功能外，还可用于给变量赋值，用法同SET一样。
  SET——用于给局部变量赋值、执行SQL命令时SQL Server 的语出选项设定等。

DECLARE @a INT 
SET @a=1

  SHUTDOWN——用于停止SQL Server的执行。

SHUTDOWN [WITH HOWAIT]

  WITH HOWAIT 当使用NOWAIT参数时，SHUTDOWN命令立即终止所有的用户过程，并在对每一现行的事务发生一个回滚后退出SQL Server。当没有用NOWAIT参数时，SHUTDOWN命令将按一下步骤执行：
  1. 终止任何用户登录SQL Server。
  2. 等待尚未完成的T-SQL命令或存储过程执行完毕。
  3. 在每个数据库中执行CHECKPOINT命令。
  4. 停止SQL Server的执行。
  WRITETEXT——允许数据类型为text、ntext、image的列进行行交互式更新。WRITETEXT 语句不能用在视图中的text、ntext、image的列上。
  SUSE——用于选择当前需要进入的工作区数据库。

---

1. 游标是获取一组数据并能够一次与一个单独的数据进行交互的方法。 ↩︎

2. 这里的时间必须为 DATETIME 类型的数据。 ↩︎

3. 最长为255个字符。 ↩︎

4. 硬盘或磁带等。 ↩︎