数据与表达式

  和其他程序设计语言一样，SQL Server也提供了用于编写结构化程序的数据类型、常量、变量、运算符和表达式等语法。理解和掌握这些语法是Transact-SQL程序设计的基础。

一。用户定义数据类型

 SQL Server有4种基本数据类型：字符和二进制数据、日期/时间数据、逻辑数据和数值数据。SQL Server也支持用户定义的数据类型，但这只是使用户能够限定已有的数据类型，方便用户对数据的操作，而不是定义具有新的存储和检索特点的新类型。

 SQL Server允许在系统数据类型的基础上建立用户定义的数据类型。用户定义数据类型可以用在CREATE DATABASE 和ALTER DATABASE 语句中定义数据表列，并且可以将默认和规则关联于用户定义数据类型，为用户定义数据类型的列提供默认值和完整性约束。

  例如，创建员工表的编号列时，可能将员工编号设置为char(4)，而出勤表的员工编号可能设置为char（3），在创建数据库关系时，会出现员工编号列的长度输入错误，这种错误的解决办法是使用用户定义类型来为经常使用的列设置一种类型。

  创建用户定义数据类型时，必须提供3个参数：名称、作为新数据类型基础的系统数据类型和NULL值属性（数据类型是否允许NULL值）。

  在SQL Server中，提供了两种方式来创建用户定义数据类型：系统存储过程和SQL Server管理平台。

1.使用系统存储过程来创建用户定义数据类型

命令格式如下：

sp_addtype [@typename=] type,

[@phystype=] system_data_type

[,[@nulltype=]'null_type']

[,[@owner=]'owner_name']

各选项的含义如下：

（1）[@typename=]type:用户定义数据类型名称。

（2）[@phystype=]system_data_type：用户定义的数据类型所基于的物理数据类型或SQL Server提供的数据类型（如decimal、int等）。

（3）[@nulltype=]'null_type':指明用户定义的数据类型处理空值的方式，默认值为NULL，还可以为NOT NULL或NONULL。

（4）[@owner=]'owner_name'：指定新类型的创建者或所有者。若没有指定，则为当前用户。

  执行系统存储过程sp_addtype后，如果执行成功，则会返回数值0，否则返回数值1.

 例如，为Sales数据库创建一个不允许为NULL值的test_add用户定义数据类型。

EXEC sp_addtype test_add,'Varchar(10)','NOT NULL'

此后，test_add可用为数据列或变量的数据类型。

2.使用SQL Server管理平台创建用户定义数据类型

   在SQL Server管理平台中，为Sales数据库创建一个不允许为NULL值的test_add用户定义数据类型，操作步骤如下：

（1）选择Sales数据库，并展开，然后选择“可编程性”结点并展开，选择“类型”并展开，在“用户定义数据类型”结点上右击，在出现的快捷菜单中选择“新建用户定义数据类型”命令。弹出“新建用户定义数据类型”窗口。

（2）在“用户定义的数据类型属性”对话框中的名称文本框内输入test_add，在“数据类型”下拉列表框中，选择char，在“长度”文本框中输入10，选中“允许空值”复选框。

（3）单击“确定”按钮完成创建用户自定义数据类型。

二。常量与变量

在程序运行中保持常值的数据，即程序本身不能改变其值的数据，称为常量，在程序中经常直接使用文字符号表示。相应地，在程序运行过程中可以改变其值的数据，称为变量。

1.常量

常量是表示特定数据值的符号，其格式取决于其数据类型，具有以下几种类型：字符串和二进制常量、日期/时间常量、数值常量、逻辑数据常量。

1）字符串和二进制常量

字符串常量括在单引号内并包含字母数字字符（a~z,A~Z和0~9）以及特殊字符，如感叹号（！）、at符（@）和数字号（#）等。若字符串中本身又有单引号字符，则单引号字符要用两个单引号来表示。例如：

'Cincinnati'、'0''Brien'、'Process X is 50% complete.'为字符串常量。

 SQL Server中，字符串常量还可以采用Unicode字符串的格式，即在字符串前面用N标识（N标识SQL -92标准中的国际语言，National Language），如N'A SQL Server string'，表示字符串'A SQL Server string'为Unicode字符串。

二级制常量具有前缀0x并且是十六进制数字字符串，它们不适用引号。

例如，0xAE、0x12Ef\0x69048AEFDD010E、0x(空串)为二进制常量。

2）日期/时间常量

datetime常量使用特定格式的字符日期值表示，用单引号括起来。下表概况了几种日期时间格式

SQL Server日期时间格式

输入格式	datetime值	Smalldatetime
Sep 3,2008 1:34:34.122	2008-09-03 01:34:34.123	2008-09-03 01:35:00
9/3/2008 1PM	2008-09-09 13:00:00.000	2008-09-03 13:00:00
9.3.2008 13:00	2008-09-03 13:00:00.000	2008-09-03 13:00:00
13:25:19	1900-01-01 13:25:19.000	1900-01-01 12:25:00
9/3/2008	2008-09-03 00:00:00.000	2008-09-03 00:00:00

输入时，可以使用“/”、“.”、“-”作为日期/时间常量的分隔符。默认情况下，服务器按照mm/dd/yy的格式（即月/日/年的顺序）来处理日期类型数据。SQL Server支持的日期格式有mdy、dmy、ymd、myd、dym，用SET DATEFORMAT命令来设定格式。

对于没有日期的时间值，服务器将其日期值指定为1900年1月1日。

3）数值常量

 数值常量包括整型常量、浮点常量、货币常量、uniqueidentifier常量。

（1）整型常量由没有用引号括起来且不含小数点的一串数字表示。例如，1894、2为整型常量。

（2）浮点常量主要采用科学记数法表示，例如，101、5E5、0.5E-2为浮点常量。

（3）精确数值常量由没有用引号括起来且包含小数点的一串数字表示。例如，1984.1204、2.0为精确数值常量。

（4）货币常量是以$为前缀的一个整型或实型常量数据，不适用引号。例如，$12.5、$542023.14为货币常量。

（5）uniqueidentifier常量是表示全局唯一标识符GUID值的字符串。可以使用字符或二进制字符串格式指定。

4）逻辑数据常量

逻辑数据常量使用数字0或1表示，并且不适用引号。非0的数字当作1处理。

5）空值

在数据列定义之后，还需确定该列是否允许空值（NULL)。允许空值意味着用户在向表中插入数据时可以忽略该列值。空值可以表示整型、实型、字符型数据。

2.变量

变量用于临时存放数据，变量中的数据随着程序的运行而变化，变量由名字与数据类型两个属性。

变量的命名使用常规标识符，即以字母、下划线（_）、at符号（@）、数字符号（#）开头，后续字母、数字、at符号、美元符号（$)、下划线的字符序列。不允许嵌入空格或其他特殊字符。

SQL Server将变量分为全局变量和局部变量两类，其中全局变量由系统定义并维护，通过在名称前面加@@符号区别于局部变量，局部变量的首字母为单个@。

1）局部变量

 局部变量使用DECLARE语句定义，仅存在于声明它的批处理、存储过程或触发器中，处理结束后，存储在局部变量中的信息丢失。

DECLARE 语句的语法格式如下：

DECLARE {@local_variable data_type}[,...n]

其中，@local_variable是变量的名称。局部变量名必须以@符号开头，且必须符合标识符规则。data_type是任何由系统提供或用户定义的数据类型。用DECLARE 定义的变量不能是text、ntext或image数据类型。

在使用DECLARE语句来声明局部变量时，必须提供变量名称及它的数据类型。变量名前必须由一个@符号，其最大长度为30个字符。一条DECLARE语句可以定义多个变量，各变量之间使用逗号隔开。例如：

DECLARE @name varchar(30),@type int

局部变量的值使用SELECT 或者PRINT语句显示。局部变量的赋值可以通过SELECT、UPDATE和SET语句进行。

（1）用SELECT为局部变量赋值。

在Transact-SQL中，通常用SELECT语句为变量赋值，格式如下：

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

FROM 

WHERE

例如：

DECLARE @int_var int

SELECT @int_var=12                                                               /*给@int_var赋值*/

SELECT @int_var                                                                     /*将@int_var的值输出到屏幕上*/

在一条语句中可以同时对几个变量进行赋值，例如：

DECLARE @LastName char(8),@Firstname char(8),@BirthDate datetime

SELECT @LastName ='Smith',@Firstname='David',@BirthDate='1985-2-20'

SELECT @LastName,@Firstname,@BirthDate

局部变量没有被赋值前，其值是NULL，若要在程序中引用它，必须先赋值。当SELECT语句中的局部变量没有被赋值时，其作用是将局部变量的值输出到屏幕。

说明：若表达式含有NULL值，则该表达式的计算结果也是NULL，因此必须使用赋值语句来初始化变量。

SELECT赋值语句通过FROM子句可以从一个表中检索出数据并赋值给局部变量。

举例：使用SELECT 语句从customer表中检索出顾客编号为C0002的行，再将顾客的名字赋给变量@customer。

DECLARE @customer varchar(40),@curdate datetime

SELECT @customer =customer_name ,@curdate=getdate()

FROM customer

WHERE customer_id='C0002'

通常情况下，一条SELECT 赋值语句自还能返回一行。若一条SELECT赋值语句在检索数据后返回了多行，则只将返回的最后一行的值赋给局部变量。

如果检索结果为空，则此局部变量的值保持不变。

（2）利用UPDATE为局部变量赋值。

在SQL Server中，还可以使用UPDATE语句来为变量赋值。

举例：将sell_order表中的transporter_id列值为T001、goods_id列值为G0003的order_num列的值赋给局部变量@order_num.

DECLARE @order_num float

UPDATE sell_order

SET @order_num =order_num*2

/*@order_num为局部变量，order_num为sell_order表中的列名称*/

WHERE transporter_id='T001'AND goods_id='G00003'

说明：SQL Server会在SELECT赋值语句中自动地执行许多隐式的数据转换，但在UPDATE语句中不会执行这些自动转换。在上例中，如果将变量@order_num声明为整数（int），则会因为所创建的数据类型与表中数据类型不匹配而出错。

（3）用SET给局部变量赋值

  在为变量赋值时，建议使用SET语句，其语法格式如下：

SET {@local_variable =expression}

其中，expression是任何有效的SQL Server表达式。

使用SET初始化变量的方法与SELECT语句相同，但一个SET语句只能为一个变量赋值。SET也可以使用查询给变量赋值。

举例：计算employee表的记录数并赋值给局部变量@rows。

DECLARE @rows int

SET @rows =(SELECT COUNT(*) FROM employee)

SELECT @rows

2）全局变量

  全局变量通常被服务器用来跟踪服务器范围和特定会话期间的信息，不能显式地被赋值或声明。全局变量不能由用户定义，也不能被应用程序用来在处理器之间交叉传递信息。

全局变量由系统提供，在某个给定的时刻，各用户的变量值将肯定互不相同。下表中列出了SQL Server中常用的全局变量。

SQL Server中常用的全局变量

变量	说明
@@rowcount	前一条命令处理的行数
@@error	前一条SQL 语句报告的错误号
@@trancount	事务嵌套的级别
@@transtate	事务的当前状态
@@tranchained	当前事务的模式（链接的、非链接的）
@@servername	本地SQL Server的名称
@@version	SQL Server和O/S版本级别
@@spid	当前进程ID
@@identity	上次INSERT操作中使用的identity值
@@nestlevel	存储过程/触发器中的嵌套层
@@fetch_status	游标中上条FETCH语句的状态

下面介绍这些常用的全局变量。

（1）@@rowcount.

@@rowcount 存储前一条命令影响到的记录总数，除了DECLARE语句之外，其他任何语句都可以影响

@@rowcount的值。如果需要重复使用此值，或者在执行某些中间处理后再回过头引用该值，则需要声明一个整型局部变量，利用该变量存储@@rowcount的值。

例如：

DECLARE @rows int 

SELECT @rows=@@rowcount

在触发器中，这项技术特别有用，因为用户可能要经常访问@@rowcount，以确保表中的所有行都是有效的。

（2）@@error。

  一般情况下，为了保证自己编写的代码运行起来更为流畅，以及对数据的操作更为稳妥，用户应当在执行完每条SQL 语句后都检查一遍@@error，尤其是在存储过程和触发器中。如果@@error为非0值，则表明执行过程中产生了错误，此时应当在程序中国采取相应的措施加以处理。

@@error的值与@@rowcount一样，会随着每一条SQL Server语句 的变化而改变。

举例：使服务器产生服务，并显示错误号。

raiserror('miscellaneous error message',16,1)                             /*产生一个错误*/

if @@error<>0

SELECT @@error as 'last error'

运行结果如下：

消息50000，级别 16，状态 1，行 1

miscellaneous error message

last error

0

第一行输出信息表明实际的错误号为50000，这是错误陷阱起了作用，指出前一个语句执行后@@error不为0.接着IF语句成功，输出的错误号为0。可见@@error会随着执行语句的变化而报告不同的内容。

用户要根据不同的错误号采用不同的操作，或在错误记录表中记录错误，需要在语句执行后立即捕捉@error的值。

举例：捕捉上例中服务器产生的错误号，并显示出来。

DECLARE @my_error int

RAISERROR ('miscellaneous error message',16,1)

SELECT @my_error=@@error

IF @my_error <>0

SELECT @my_error as 'last error'

运行结果如下：

消息50000，级别16，状态 1，行 2

miscellaneous error message

last error

50000

本例通过定义局部变量@my_error保存当前@@error的值，SELECT 语句显示错误号为50000.

（3）@@trancount

@@trancount记录当前的事务数量，当某个事物当前并没有结束会话过程时，@@trancount的值大于0.

（4）@@version

@@version 的值代表服务器的当前版本和当前操作系统版本，是SQL Server中一项较实用的技术支持，通常对识别网络中某个未命名的服务器非常有用。

（5）@@spid

@@spid返回当前用户进程的服务器进程ID，可以用来识别sp_who输出中的当前用户进程。

举例：使用@@spid返回当前用户进程的ID。

SELECT @@spid as 'ID',SYSTEM_USER AS 'Login Name',USER AS 'User Name'

运行i间而过如下：

ID Login Name User Name

53      sa              dbo

(6)@@identity

当INSERT、SELECT INTO或批复制语句完成后，@@identity等于由语句产生的最后一个identity值。如果语句没有影响任何表的identity列，@@identity返回NULL；如果插入了多行，产生了多个identity值，@@identity返回最后产生的identity值；如果语句激发了一个或多个触发器，而这些触发器执行了产生identity的值插入操作，那么在语句执行结束后就hi立即调用@@identity来返回由触发器产生的最后一个identity值。如果INSERT 、SELECT INTO 语句或批复制失效，或者事物撤销了，@@identity值并不恢复到以前的设置。

举例：在Sales数据库中创建jobs表，并在表中插入带有identity列的行，并且使用@@iedentity来显示新行中所用的identity值。

CREATE TABLE jobs(

[job_id][varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,

[min_1v1][tinyint] NOT NULL,

[max_1v1][tinyint] NOT NULL

) ON [PRIMARY]

INSERT INTO jobs(job_desc,min_1v1,max_1v1)

VALUES('Accountant',12,125)

SELECT @@IDENTITY AS 'Identity'

运行结果如下：

Identity

1

（7）@@nestlevel.

@@nestlevl 返回当前存储过程执行的嵌套层（初始值为0）。

一个存储过程每次调用另一个存储过程时，嵌套层就增加，当超过最大值32时，事物就被终止。

举例：创建两个过程:innerproc 和outerproc。outerproc过程用来调用innerproc过程，innerproc过程用来显示@@nestlevel的设置。

①定义innerproc为内嵌过程。

CREATE PROCEDURE innerproc as 

SELECT @@nestlevel AS 'Inner LEVEl'

GO

②定义outerproc为外层过程。

CREATE PROCEDURE outerproc as 

SELECT @@nestlevel AS 'Outer Level'

EXEC innerproc

GO

③执行outerproc

EXECUTE outerproc

GO

运行结果如下：

Outer Level

1

Inner Level

2

(8) @@fetch_status.

@@fetch_status返回最后一个游标语句FETCH的状态，返回值有0、-1和-2。

因为@@fetch_status对于连接的所有游标是一个全局变量，所以使用它时要格外谨慎。当一个FETCH语句执行后，对@@fetch_status的检测必须在另一个游标执行任何其他FETCH语句之前进行。在任何取数据操作发生之前，@@fetch_status的值是不确定的。

三。运算符与表达式

运算符用来执行数据列之间的数学运算或比较操作。Transact-SQL运算符共有5类，即算术运算符、位运算符、逻辑运算符、比较运算符、连接运算符。

表达式是符号与运算符的组合。简单的表达式可以是一个常量、变量、列或函数，复杂的表达式是由运算符连接一个或多个简单表达式。

1.算术运算符与表达式

  算术运算符用于数值型列或变量间的算术运算。算术运算符包括加（+）、减（-）、乘（*）、除（/）和取模（%）运算等。如下表列出了所有的算术运算符及其可操作的数据类型。

算术运算符及其可操作的数据类型

算术运算符	数据类型
+、-、*、/	int、smallint、tinyint、numeric、decimal、float、real、money、smallmoney
%	int、smallint、tinyint

举例：使用“+”将goods表中高于9000元的商品价格增加15元。

SELECT goods_name,unit_price,(unit_price+15) AS nowprice

FROM goods

WHERE unit_price >9000

运行结果如下：

运行结果

	goods_name	unit_price	nowprice
1	IBM R51	9999.00	10014.00
2	旭日160-D1.7G	9499.00	9514.00
3	NEC S3000	9900.00	9915.00

本例创建一个虚拟列，并通过“+”修改该列的值。

如果表达式中有多个算术运算符，则先计算乘、除和求余，然后计算加减法。如果表达式中所有算术运算符都具有相同的优先顺序，则执行顺序为从左到右。括号中的表达式比所有其他运算都要优先。算术运算的结果为优先级较高的参数的数据类型。

2.位运算符与表达式

 位运算符用以对数据进行按位与（&）、或（|）、异或（^）、求反（~）等运算。在Transact-SQL语句中，进行整型数据的位运算时，SQL Server先将它们转换为二进制数，然后再进行计算。其中与、或、异或运算符需要两个操作数，求反运算符仅需要一个操作数。如下表列出了位运算符及其可操作的数据类型。

位运算符及其可操作的数据类型

位运算符	左操作数	右操作数
&	int、smallint、tinyint	int、smallint、tinyint、bigint
|	int、smallint、tinyint	int、smallint、tinyint、binary
^	binary、varbinary、int	int、samllint、tinyint、bit
~	无左操作数	int、smallint、tinyint、bit

• & 运算只有当两个表达式中的两个位值都为1时，结果中的位才被设置为1，否则结果中的位被设置为0.
• | 运算时，如果在两个表达式的任一位为1或者两个位均为1，那么结果的对应位被设置为1；如果表达式中的两个位都不为1，则结果中该位的值被设置为0.
• ^ 运算时，如果在两个表达式中，只有一位的值为1，则结果中位的值被设置为1；如果两个位的值都为0或者都为1，则结果中该位的值被清除为0.
• ~ 运算时，如果表达式某位为1，则结果中的该位为0，否则相反。

例如，170与75进行&运算，先将170和75转换为二进制数0000 0000 1010 1010和0000 0000 0100 1011，再进行&运算的结果是 0000 0000 0000 1010，即十进制数10.

同样，表达式5^2，~1，5|2的运算结果为：7，0，7.

3。比较运算符与表达式

比较运算符用来比较两个表达式的值是否相同，可用于字符、数字或日期数据。SQL Server中的比较运算符有大于（>）、小于（<）、大于等于（>=）、小于等于（<=）和不等于（！=）等，比较运算返回布尔值，通常出现在条件表达式中。

比较运算符的结果为布尔数据类型，其值为TRUE、FALSE及UNKNOWN。

例如，表达式2=3的运算结果为FALSE。

一般情况下，带有一个或两个NULL表达式的运算符返回UNKNOWN。当SET ANSI_NULL为OFF且两个表达式都为NULL，那么“=”运算符返回TRUE。

4.逻辑运算符与表达式

  逻辑运算符与（AND）、或（OR）、非（NOT）等，用于对某个条件进行测试，以获得其真实情况。逻辑运算符和比较运算符一样，返回TRUE或FALSE的布尔数据值。如下表所示列出了逻辑运算符及其运算情况。

逻辑运算符

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

例如，NOT TRUE为假；TRUE AND FALSE 为假；TRUE OR FALSE为真。

逻辑运算符通常和比较运算一起构成更为复杂的表达式。与比较运算符不同的是，逻辑运算符的操作数都只能是布尔型数据。

例如，在表employee中查找1973年以前与1980年以后出生的男员工的表达式为：

（year(birth_date)<1973 or year(birth_date)>1980）AND sex ='男'

LIKE运算符确定给定的字符串是否与指定的模式匹配，通常只限于字符数据类型。模式可以使用通配符字符，如下表所示，它们使LIKE更加灵活。例如：

SELECT employee_name,address

FROM employee

WHERE employee_name LIKE'钱%'

本例查找所有姓“钱”的员工及住址。

LIKE的通配符

运算符	描述	示例
%	包含零个或多个字符的任意字符串	address LIKE'%公司%'将查找地址任意位置包含公司的所有职员
_	下划线，对应任何单个字符	employee_name LIKE'_海燕'将查找以”海燕“结尾的所有6个字符的名字
[]	指定范围（a~f）或集合（[abcdef]）中的任何单个字符	employee_name LIKE'[张李王]海燕'将查找张海燕、李海燕、王海燕等
[^]	不属于指定范围（a~f）或集合（[abcdef]）的任何单个字符	employee_name LIKE’[^张李]海燕‘将查找不姓张、李的名为海燕的职员

5.连接运算符于表达

 连接式运算符（|）用于两个字符串数据的连接，通常也称为字符串运算符。在SQL Server中，对字符串的其他操作通过字符串函数进行。字符串连接运算符的操作数类型有char、varchar和text等。例如，’Dr.‘+'Computer'中的”+“运算符将两个字符串连接成一个字符串’Dr.Computer‘。

6.运算符的优先级别

 不同运算符具有不同的运算优先级，在一个表达式中，运算符的优先级决定了运算的顺序。SQL Server中各种运算符的优先顺序如下：

（）→~→^→&→  |  → *、/、%→+、- →NOT →AND → OR

排在前面的运算符的优先级高于其后的运算符。在一个表达式中，先计算优先级较高的运算，后计算优先级低的运算，相同优先级的运算按自左向右的顺序依次进行。