SQL--Transact-SQL

常量类型与常量表示
常量类型	常量表示说明
字符串常量	包括在单引号或双引号中，由字母、数字及特殊字符组成。如：'Cint','40%'
二进制常量	由0或1构成的串，并且不使用引号。如果使用一个大于1的数字，它将被转换为1.如：100011，111101
十进制整型常量	使用不带小数点的十进制数据表示。如：1946，-1299，+345
十六进制整型常量	使用前缀0X后跟十六进制数字串表示。如：0XEFD,0X123EFD
日期常量	使用单引号将日期时间字符串括起来。如：字母日期格式：'July 25,2008','25-July-2008' 数字日期格式：’08/25/1993','1988-08-30','08-23-88','1988年8月1日' 未分隔的字符格式：'1920624'
实型常量	有定点表示和浮点表示两种方式。如：定点表示：1984.21，4.0，+4.5，-6.5 浮点表示：10E2, 0.34E-6, -86E6
货币常量	以前缀为可选的小数点和可选的货币符号的数字字符串来表示。如： $4452, $74.11

变量

变量是指在程序运行过程中，其值可以改变的量，可以利用变量存储程序执行过程中涉及的数据。

变量由变量名和变量值组成，类型与常量相同，但变量名不允许与函数名或命令名相同。

变量可以分为全局变量和局部变量。

全局变量是SQL SERVER提供的。它的特征是有@@在变量名前。用来提供一些SQL SERVER相关信息。

全局变量及其说明
@@CONNECTIONS	返回自上次启动以来连接或试图连接的次数。
@@CURSOR_ROWS	返回连接上最后打开的游标中当前存在的合格行的数量（返回被打开的游标中还未被读取的有效数据行的行数）
@@ERROR	返回最后执行的SQL语句的错误代码
@@FETCH_STATUS	返回被FETCH语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
@@IDENTITY	返回最后插入的标识值
@@LANGUAGE	返回当前使用的语言名
@@LOCK_TIMEOUT	返回当前会话的等待锁的时间长短，单位为毫秒
@@PROCID	返回当前过程的存储过程标识符ID
@@ROWCOUNT	返回受上一语句影响的行数
@@SERVERNAME	返回运行的本地服务器名称
@@SPID	返回当前用户进程的服务器进程标识符ID
@@TRANCOUNT	返回当前连接中处于激活状态的事务数目
@@VERSION	返回当前安装的日期、版本和处理器类型
@@CPU_BUSY	返回自SQL Server最近一次启动以来CPU的工作时间其单位为毫秒
@@DATEFIRST	返回使用SET DATEFIRST命令被赋值的DATEFIRST参数值。 SET DATEFIRST命令用来指定每周的第一天是星期几。
@@DBTS	返回当前数据库的时间戳值必须保证数据库中时间戳的值是唯一的。
@@IDLE	返回自SQL Server最近一次启动以来CPU处于空闲状态的时间长短单位为毫秒
@@ID_BUSY	返回自SQL Server最近一次启动以来CPU执行输入输出操作所花费的时间其单位为毫秒
@@LANGID	返回当前所使用的语言ID值
@@MAX_CONNECTIONS	返回允许连接到SQL Server的最大连接数目
@@MAX_PRECISION	返回decimal和numeric数据类型的精确度
@@NESTLEVEL	返回当前执行的存储过程的嵌套级数初始值为0
@@OPTIONS	返回当前SET选项的信息
@@PACK_RECEIVED	返回SQL Server通过网络读取的输入包的数目
@@PACK_SENT	返回SQL Server写给网络的输出包的数目
@@PACKET_ERRORS	返回网络包的错误数目
@@TEXTSIZE	返回SET语句的TEXTSIZE选项SET语句定义了SELECT语句中text或image数据类型的最大长度基本单位为字节
@@TIMETICKS	返回每一时钟的微秒数
@@TOTAL_ERRORS	返回磁盘读写错误数目
@@TOTAL_READ	返回磁盘读操作的数目
@@TOTAL_WRITE	返回磁盘写操作的数目

局部变量

局部变量是指作用域局限在一定范围内的变量，相对于全局变量，局部变量需要先声明后使用，并且在声明时其变量名称前必须加上标识符@。

创建局部变量

DECLARE

{

{@local_variable data_type}|{@cursor_variable CURSOR}|{table_type_definition}

}[,...n]

变量不能是text、ntext或image数据类型。

给变量赋值

SET @local_variable = expression

SELECT @local_variable = expression[,...n]

SELECT @local_variable通常用于将单个值返回到变量中，如果expression为列名，则返回多个。如果SELECT语句返回多个值，将返回的最后一个值赋值变量。如果没有返回行，变量将保留当前值。如果expression是不返回值的子查询，则将变量设为NULL，因此，通常情况下使用SET而不是SELECT给变量赋值。

表达式

表达式是由变量、常量、运算符、函数等组成的。

简单表达式

简单表达式是指仅由变量、常量、运算符、函数等组成的表达式。简单表达式的结构比较单一，通常用来描述一个简单的条件。

复杂表达式

复杂表达式是指由两个或多个简单表达式通过运算符连接起来的表达式。

注释

注释是对语句做说明，增强语句的可读性

多行注释/*.....*/

单行注释--...

运算符

算术运算符

算术运算符是指在两个表达式上执行数学运算，这两个表达式可以是任意的数据类型。

算术运算符说明
运算符	运算符说明
+	加，将两个值相加
-	减，将两个值相减
*	乘，将两个值相乘
/	除，将两个值相除。当其中有一个为浮点数时，求的结果和正常除法结果相同。当两个数都为整数时，结果为商。
%	取余，取得两个数相除时的余数。

加和减运算符也可以用于对datetime及smalldatetime值执行算术运算。

比较运算符

比较运算符也称为关系运算符，用于比较两个表达式的值之间的关系。计算结果为布尔数据，而返回布尔数据类型的表达式称为布尔表达式。比较运算符根据布尔表达式的输出结果，返回True，False或Unknow值。

比较运算符
运算符	运算符说明
=	等于，SQL中赋值运算符和等于比较运算符相同
>	大于
<	小于
>=	大于等于
<=	小于等于
!= 或<>	不等于

赋值运算符

赋值运算符是指将表达式的值赋给一个变量。通常使用"="进行赋值。

位运算符

位运算符是指对两个表达式进行位操作。位操作的前提，将两个表达式的值转换为二进制表示，然后对这两个二进制数值进行位操作。

位运算符
运算符	运算符说明
&	两个位均为1时，结果为1，否则为0
\|	只要一个位为1，结果为1，否则为0
^	两个位值不同时，结果为1，否则为0

两个操作数不能同时是二进制字符串数据类型中的某种数据类型。

逻辑运算符

逻辑运算符是指对某些条件进行测试，返回最终结果。返回带有True或False的数据类型。

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

字符串连接运算符

字符串连接运算符是指运用连接符"+"将字符串串联起来。在串联varchar、char或text数据类型的数据时，空的字符串被解释为空字符串。但是，如果兼容级别设置为65，则空常量将作为单个空白字符处理。

一元运算符

一元运算符只对一个表达式执行操作。

一元运算符
运算符	运算符说明
+	正，可以用于数字数据类型中的任一数据类型的表达式
-	负，可以用于数字数据类型中的任一数据类型的表达式
~	位反。只能用于整数数据类型类别中任一数据类型的表达式

运算符优先级

运算符优先级用于指定执行运算的先后顺序，当一个复杂的表达式含有多个运算符时，需要根据运算符的优先级对表达式进行求值。在一个表达式中按先高后低的顺序进行运算（即数字越小其优先级越高），当一个表达式中的两个运算符有相同的运算符优先级别时，按照它们在表达式中的位置，一元运算符按从右向左的顺序运算，二元运算符按从左到右进行求值。如果表达式中带有括号，则括号中的表达式优先级最高，所以应先对括号中的内容进行求值，从而产生一个值，然后括号外的运算符才可以使用这个值。如果括号嵌套括号，则应对最内部的括号求值，然后次层括号的运算符才可以使用该值，依次类推。

运算符优先级
优先级	运算符
1	~（位非）
2	*（乘）, /（除）, %（取模）
3	+ （正），-（负）,+（加）,+（连接）,-（减）,&（位与）
4	=,>,<,>=,<=,!=,!>,!<
5	^（位异或）,\|（位或）
6	NOT
7	AND
8	ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
9	=(赋值）

控制语句

流程控制语句就是用来控制程序执行流程的语句，也称流控制语句或控制流语句。

BEGIN END语句块

BEGIN END定义了一个语句块，这些语句块作为一组语句执行，并且允许语句嵌套。

BEGIN

{

sql_statement|statement_block

}

END

BEGIN 和END是成对出现的，分别语句块的起始位置和结束位置。

IF条件语句

IF条件语句用于指定Transact-SQL语句的执行条件。如果条件为真，则执行条件表达式后面的语句。当条件为假时，可以使用ELSE关键字指定要执行的语句。

IF Boolean_expression

{sql_statement | statement_block}

ELSE

{sql_statement | statement_block}

其中，Boolean_expression是指返回True或False的表达式。如果布尔表达式中包含有SELECT 语句，必须用圆括号将SELECT语句括起来。

CASE分支语句

CASE关键字可根据表达式的真假来确定是否返回某个值，可在允许使用表达式的任何位置使用这个关键字。使用CASE语句可以进行多个分支的选择，CASE语句具有两种格式：

简单格式将某个表达式与一组简单表达式进行比较以确定结果

CASE input_expression

WHEN when_expression THEN result_expression

[...n]

[ELSE else_result_expression]

END

搜索格式计算一组布尔表达式以确定结果

CASE

WHEN Boolean_expression THEN result_expression

[...n]

[ELSE else_result_expression]

END

WHILE循环语句

WHILE循环语句用于设置重复执行Transact-SQL语句或语句块的条件。当指定的条件为真时，重复执行循环语句。可以在循环体内设置BREAK和CONTINUE关键字，以便控制循环语句的执行过程。

WHILE Boolean_expression

{sql_statement | statement_block}

[BREAK]

{sql_statement | statement_block}

[CONTINUE]

{sql_statement | statement_block}

TRY错误处理语句

如果TRY块内部发生错误，则会将控制传递给CATCH块中包含的另一个语句组。TRY CATCH构造捕捉所有严重级别大于10但不终止数据库连接的错误。

BEGIN TRY

{sql_statement | statment_block}

END TRY

BEGIN CATCH

{sql_statement | statment_block}

END CATCH

函数

聚合函数

聚合函数是指对一组值执行计算并返回单个值。通常与SELECT语句的GROUP BY、HAVING子句一起使用。所有聚合函数均为确定性函数，也就是说，只要使用一组特定输入值调用聚合函数，该函数总是返回相同的值。

除了COUNT函数外，聚合函数都会忽略空值。

聚合函数
函数名称	函数说明	函数语法
AVG	返回组中各值的平均值，如果为空将被忽略。	AVG([ALL \| DISTINCT] expression)
CHECKSUM	用于生成哈希索引，返回按照表的某一行或一组表达式计算出来的校验和值。	CHECKSUM(* \| expression [...n])
CHECKSUM_AGG	返回组中各值的校验和，如果为空将被忽略。	CHECKSUM_AGG([ALL \| DISTINCT] expression)
COUNT	返回组中项值的数量，如果为空也将计数	COUNT({[[ALL \| DISTINCT] expression] \| *})
COUNT_BIG	返回组中项值的数量。与COUNT函数唯一的差别是它们的返回值。COUNT_BIG始终返回bigint数据类型。COUNT始终返回int数据类型值。	COUNT_BIG({[[ALL \|DISTINCT] expression] \| *})
GROUPING	当行由CUBE或ROLLUP运算符添加时，该函数将导致附加列的输出值为1；当行不由CUBE或ROLLUP运算符添加时，将导致附加列的输出值为0。	GROUPING(<column_expression>
MAX	返回组中值列表的最大值	MAX([ALL \| DISTINCT] expression)
MIN	返回组中值列表的最小值	MIN([ALL \| DISTINCT] expression)
SUM	返回组中各值的总和	SUM([ALL \| DISTINCT] expression)
STDEV	返回指定表达式中所有值的标准偏差	STDEV([ALL \| DISTINCT] expression)
STDEVP	返回指定表达式中所有值的总体标准偏差	STDEVP([ALL \| DISTINCT] expression)
VAR	返回指定表达式中所有值的方差	VAR( [ALL \| DISTINCT] expression)
VARP	返回指定表达式中所有值的总体方差	VARP([ALL \| DISTINCT] expression)

数学函数

数学函数
函数	函数说明	语法
ABS	返回数值表达式的绝对值	ABS(numeric_expression)
EXP	返回指定表达式以e为底的指数	EXP(float_expression)
CEILING	返回大于或等于数值表达式的最小整数	CEILING(numeric_expression)
FLOOR	返回小于或等于数值表达式的最大整数	FLOOR(numeric_expression)
LN	返回数值表达式的自然对数	LN(float_expression)
LOG	返回数值表达式以10为底的对数	LOG(float_expression)
POWER	返回对数值表达式进行幂运算的结果	POWER(float_expression,y)
ROUND	返回舍入到指定长度或精度的数值表达式	ROUND(numeric_expression,length[,function])
SIGN	返回数值表达式的正号、负号或零	SIGN(numeric_expression)
SQUARE	返回数值表达式的平方	SQUARE(float_expression)
SQRT	返回数值表达式的平方根	SQRT(float_expression)

配置函数

配置函数即前面讲述的全局变量，用来返回系统的配置信息。

字符串函数

字符串函数
函数	函数说明	函数语法
ASCII	返回字符表达式中最左侧的字符的ASCII代码值	ASCII(character_expression)
CHAR	返回指定ASCII代码的字符	CHAR(integer_expression)
LEFT	返回字符串中从左边开始指定个数的字符	LEFT(character_expression,integer_expression)
LEN	返回指定字符串表达式的字符数，其中不包含尾随空格	LEN(string_expression)
LOWER	将大写字符数据转换为小写字符数据后返回字符表达式	LOWER(character_expression)
LTRIM	返回删除了前导空格之后的字符表达式	LTRIM(character_expression)
REPLACE	用第三个表达式替换第一个字符串表达式中出现的所有第二个指定字符串表达式的匹配项	REPLACE(string1_expression,string2_expression,string3_expression)
REPLICATE	复制函数，以指定的次数重复字符表达式	REPLICATE(string1_expression,int_expression)
RIGHT	返回字符串中从右边开始指定个数的字符	RIGHT(character_expression,integer_expression)
RTRIM	删除所有尾随空格后返回一个字符串	RTRIM(character_expression)
SPACE	返回由重复的空格组成的字符串	SPACE(integer_expression)
STR	返回由数字数据转换来的字符数据	STR(float_expression[,length[,decimal]])
SUBSTRING	返回字符表达式、二进制表达式、文本表达式或图像表达式的一部分	SUBSTRING(value_expression,start_expression,length_expression)
UPPER	返回小写字符数据转换为大写的字符表达式	UPPER(character_expression)

数据类型转换函数

数据类型的转换有两种方式：隐式转换和显式转换。

隐式转换是指在默认情况下，系统会对一些表达式进行自动转换。

显示转换是指需要使用CAST和CONVERT转换函数，将一种数据类型的表达式转换为另一种数据类型的表达式。

CAST(expression AS data_type [length])

CONVERT( data_type[ length], expression[, style])

日期和时间函数

日期和时间函数
函数	函数说明	语法
DATEADD	返回给指定日期加上一个时间间隔后的新datetime值	DATEADD(datepart,number,date)
DATEDIFF	返回跨两个指定日期的日期边界数和时间边界数	DATEDIFF(datepart,startdate,enddate)
DATENAME	返回表示指定日期的指定日期部分的字符串	DATENAME(datepart,date)
DATEPART	返回表示指定日期的指定日期部分的整数	DATEPART(datepart,date)
DAY	返回一个整数，表示指定日期的天DATEPART部分	DAY(date)
GETDATE	以datetime值的SQL SERVER2008标准内部格式返回当前系统日期和时间	GETDATE()
GETUTCDATE	返回表示当前的UTC时间的datetime值	GETUTCDATE()
MONTH	返回表示指定日期的“月”部分的整数	MONTH(date)
YEAR	返回表示指定日期的年份的整数	YEAR(date)

文本和图像函数

文本和图像函数可对文本或图像输入的值或列进行操作，并返回有关该值的信息。

文本和图像函数
函数	函数说明	语法
PATINDEX	返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回0	PATINDEX('%pattern%,expression)
TEXTPTP	返回对应于varbinary格式的text、ntext或image列的文本指针值	TEXTPTP(column)
TEXTVALID	检查特定文本指针是否有效的text、ntext或image函数	TEXTVALID('table.column',text_ptr)

用户自定义函数

根据函数返回值形式的不同，可以创建3类自定义函数，分别是标量值自定义函数、内联表值自定义函数和多语句表值自定义函数。

自定义函数可以接受零个或多个输入参数，其返回值可以是一个数值，也可以是一个表，但是自定义函数不支持输出参数。

标量自定义函数语法

CREATE FUNCTION function_name

([{@parameter_name scalar_parameter_data_type [= default]}[,...n]])

RETURNS scalar_return_data_type

[WITH ENCRYPTION]

[AS]

BEGIN

function_body

RETURN scalar_expression

END

内联表值自定义函数

内联表值函数以表的形式返回一个返回值，即返回的是一个表。内联表值自定义函数没有由BEGIN...END语句块包含的函数体，而是直接使用RETURN子句，其中包含的SELECT语句将数据从数据库中筛选出来形成一个表。使用内联表值自定义函数可以提供参数化的视图功能。

CREATE FUNCTION function_name

([{@parameter_name scalar_parameter_data_type [= default]}[,...n]])

RETURNS TABLE

[WITH ENCRYPTION]

[AS]

RETURN (select_statement)

多语句表值函数

多语句表值自定义函数可以看作标量型和内联表值型函数的结合体。该类函数的返回值是一个表，但它和标量值自定义函数一样，有一个用BEGIN...END语句块包含起来的函数体，返回值的表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询，对数据进行多次筛选与合并，弥补了内联表值自定义函数的不足。

CREATE FUNCTION function_name
([{@parameter_name scalar_parameter_data_type [= default]}[,...n]])
RETURNS
@OutputTable TABLE
(
        @Column1 DataTypeForColumn1 ,
        @Column2 DataTypeForColumn2
)
AS
BEGIN
  --FunctionBody
RETURN
END

其中@outputtable是需要返回的结果表，functionbody中用于创建相应的结果表。

事务与锁

SQL Server 2008数据库系统以事务为处理单元，使用锁保证数据的一致性，并解决数据库操作时的并发问题。

事务

事务是指用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。

在SQL Server 2008数据系统中，包括自动提交事务、显式事务、隐式事务和批处理级事务4种形式。

事务类型
类型	含义
自动提交事务	指每条单独的语句都是一个事务
显式事务	指每个事务均以BEGIN TRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束
隐式事务	指在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显式完成
批处理级事务	指只能应用于多个活动结果集

一组行为要成为一个事务，必须通过ACID检验。ACID是4个事务属性的每个单词的头一个字母的组合。

原子性ATOMICITY

这是指事务的全有或全无性质。一个事务要么所有的操作都执行，要么一个都不执行。

一致性CONSISTENCY

事务在完成时，必须使所有的数据都保持一致性状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构都必须是正确的。

孤立性ISOLATION

事务过程中暂时不一致的数据都不能被其他事务应用，直到数据再次一致。

持久性DURABILITY

一旦完成了事务提交，它们就变成永久的。事务所完成的工作得到永久保存。

BEGIN TRANSACTION

body

COMMIT | ROLLBACK

事务以BEGIN TRANSACTION开始，以COMMIT 或ROLLBACK结束。COMMIT表示提交事务的所有操作，事务正常结束。而ROLLBACK表示回滚，对数据库的所有已完成的操作全部撤销，回滚到事务开始的状态。

锁

SQL Server 2008的关键特性之一是支持多用户共享同一数据库，但是，当多个用户同时对同一个数据进行修改时，会产生一定的并发问题。使用事务便可以解决用户存取数据的这个问题，从而保证数据库的完整性和一致性。然而如果防止其他用户修改另一个还没有完成的事务中的数据，就必须在事务中使用锁。

SQL Server 2008中提供了多种锁模式，主要包括：排他锁、共享锁、更新锁、意向锁、键范围锁、架构锁和大容量更新锁。

锁模式
锁类型	说明
排他锁	如果事务T1获得了数据项R上的排他锁，则T1对数据项既可读又可写。事务T1对数据项R加上排他锁，则其他事务对数据项R的任何封锁请求都不会成功，直至事务T1释放数据项R上的排他锁。
共享锁	如果事务T1获得了数据项R上的共享锁，则T1对数据项R可以读但不可以写。事务T1对数据项R加上共享锁，则其他事务对数据项R的排他锁请求不会成功，而对数据项R的共享锁请求可以成功。
更新锁	更新锁可以防止死锁情况出现。当一个事务查询数据以便进行修改时，可以对数据项施加更新锁，如果事务修改资源，则更新锁会转换成排他锁。否则会转换成共享锁。一次只有一个事务可以获得资源上的更新锁，它允许其他事务对资源的共享式访问，但阻止排他式的访问。
意向锁	意向锁用来保护共享锁或排他锁放置在锁层次结构的底层资源上。之所以命名为意向锁，是因为在较低级别锁前可获取它们，因此会通知意向将锁放置在较低级别上。
键范围锁	键范围锁可以防止幻读。通过保护行之间键的范围，它还防止对事务访问的记录集进行幻象插入或删除。
架构锁	执行表的DDL操作时使用架构修改锁。在架构修改锁起作用的期间，会防止对表的并发访问。这意味着在释放架构修改锁之前，该锁之外的所有操作都将被阻止。
大容量更新锁	大容量更新锁允许多个进程将数据并行地大容量复制到同一表，同时防止其他不进行大容量复制的进程访问该表。