第5章
TransactSQL基础




TranscatSQL即TSQL，是SQL Server数据管理系统的操作语言。很多管理SQL Server的操作，无论是通过图形界面的SQL Server Management Studio，还是其他应用程序界面执行的操作，其实质基本都是调用了TSQL语句。

TSQL是众多SQL中的一种。SQL在数据库管理系统中如此重要，以至于微软将自己的产品命名为SQL Server，另一款使用面也非常广泛的数据库管理系统MySQL也采用SQL来命名，可以说SQL是关系数据库系统应用的基础。

前面的章节中，在数据库、数据表的创建和管理中已经介绍了部分TSQL的语句，如Create Database、Create Table、Alter Database、Alter Table等，本章将对TSQL语句的相关内容进行更深入的介绍。

本章要点： 

■TSQL概述

■TSQL数据操纵语句

■TSQL数据查询语句

■TSQL附加语言和通用表表达式(CTE)




5.1TSQL概述

5.1TSQL概述
5.1.1TSQL的发展

说起TSQL，不能不先说SQL。而讨论SQL，又不得不说E.F. Codd。SQL是关系数据库的标准语言，而E.F. Codd则是关系数据库理论的奠基者，E.F. Codd对关系数据库的发展，以及对SQL的发展都做出了重大的贡献。

在20世纪70年代初，IBM公司的E.F.Codd发表了A Relation Modal of Data for Large Shared Data Banks，即《大型共享数据库的数据关系模型》，确立了关系数据库的概念。20世纪70年代中期，IBM公司首先使用该模型开发出了结构化英语查询语言（SEQUELStructed English Query Language），作为其关系数据库原型system R系统的操作语言，实现了对关系数据库的信息检索； Oracle公司则在1979年率先推出商用的SQL。由于SQL简单易用，近似于自然语言，因此，自推出之后，受到了数据库软件开发厂商和用户的广泛欢迎。无论是DB2、Oracle，还是Sybase、Informix、SQL Server，以及Visual Foxpro、Access等都支持SQL作为查询语言。

1986年10月，SQL成为美国国家标准组织ANSI的关系数据管理系统操作语言的国家标准。由于SQL使用普遍性且发展迅速，ANSI分别于1989年、1992年、1999年、2003年和2006年推出了SQL的ANSI标准的新版本。国际标准化组织(ISO)也将之确定为关系数据库语言的国际标准。

TSQL是微软公司在遵循SQL标准的基础上，经过进一步发展，应用于SQL Server数据库系统的操作语言。由于市场竞争和客户需求多样化等原因，不同软件厂商的数据库管理系统产品都在SQL国际标准的基础上，添加了各自的特色语句，SQL Server的TSQL如此，Oracle的PL/SQL也是如此。这些带有各自特色的语言，虽然在一定程度上给标准的一致性带来了一些问题，但是不同的特色也进一步丰富了SQL标准的内涵，推动了SQL标准的进一步发展。

对于普通的开发人员来说，由于不同产品的SQL的基本语句是大致相同的，并不会带来太多平台切换的困难。因此，掌握TSQL，同样有利于更深入地去使用其他数据库产品。


第
5
章
TransactSQL基础




5.1.2TSQL的语言分类

TSQL中语句众多，非常丰富，按其功能不同可以大致分为以下4类。

■数据控制语言(Data Control Language，DCL)： 用于安全性管理，可以确定哪些用户能查看或者修改数据，包含GRANT、DENY、REVOKE、COMMIT、ROLLBACK等。

■数据定义语言(Data Definition Language，DDL)： 用于执行数据库管理任务，创建和管理数据库以及数据库中的各种对象，包含CREATE、ALERT、DROP等。

■数据操纵语言(Data Manipulation Language，DML)： 用于在数据库中操纵各种对象、检索和修改数据，包含SELECT、INSERT、UPDATE、DELETE等。本部分语言中，根据对数据的影响情况又可以细分为数据操纵语言和数据查询语言。数据操纵语言是对数据库数据产生变更影响的语言，包含INSERT、UPDATE、DELETE。数据查询语言指从数据库中获取与指定条件相符合的数据，而对原始数据不会产生变更影响的语言，主要是各种SELECT语句。

■附加语言： 包含变量、运算符、函数、流程控制语言和注释等。

数据控制语言将在第10章中进行介绍，数据定义语言已在数据库管理、数据表管理中做了部分介绍，在后续的索引管理、存储过程、视图、触发器等章节中还将介绍相关内容。本章着重介绍数据操纵语言和附加语言。




5.2TSQL数据
操纵语言

5.2TSQL数据操纵语言

往数据表中输入数据、编辑数据和删除数据是数据库系统管理数据的三项最基本的操作。在SQL Server 2022中实现上述三项操作的基本语句是INSERT、UPDATE和DELETE。

5.2.1INSERT插入数据

INSERT是SQL Server插入数据的语句，其基本语法如下：

INSERT 

［ INTO ］ 

{table_name or View_name}

{

［ ( column_list ) ］ 

［ <OUTPUT Clause> ］

{ VALUES ( ( { DEFAULT | NULL | expression } ［ ,…n ］ ) ［ ,…n ］ )  } 

}

各主要参数的含义如下： 

■table_name or View_name，接收插入数据的数据表或者视图名称。

■column_list，插入数据表或视图中的列的列表，是可选项。如果不添加列名的列表，则插入的数据值的个数和顺序要求与列在表或视图中一致。

■OUTPUT Clause，执行INSERT语句后，系统返回值的子句。

■VALUES，插入的数据值的列表，需要使用英文半角逗号分隔。

1. INSERT插入单行数据

使用INSERT插入单行数据的代码较为简单，例如，以下代码为向“产品数据表”中输入一行数据：

INSERT INTO 产品数据表(ProductName,UnitPrice,Unit,Description)  VALUES('联想(Lenovo)小新Pro13.3英寸',5990.00,'台','联想(Lenovo)小新Pro13.3英寸英特尔')  

执行完毕后，“产品数据表”中的数据如图51所示，末行数据即为上述代码执行后新添到数据表中的数据。可以看出，数据值与列之间是根据代码中列的顺序和值的顺序一一对应的，即ProductName对应'联想(Lenovo)小新Pro13.3英寸'，UnitPrice对应5990.00，Unit对应'台'，Description对应'联想(Lenovo)小新Pro13.3英寸英特尔'。



图51INSERT语句执行后数据表的变化


如果表中列的顺序比较明确，INSERT语句中列列表也可以不显式列出，如上述代码，可以改写成以下代码：

INSERT INTO 产品数据表 VALUES('联想Y450A-TSI',4899.00,'台','T6600酷睿2GBDDR320G独立显存' )  

注意： 在使用INSERT输入数据时，必须遵守表结构设计时设置的约束要求。例如主键由系统自动生成，不能手工输入； Not Null约束要求列必须输入值。如果将上述语句改写为以下语句，就会出错，因为ProductID是标识列，不允许赋值。

INSERT INTO 产品数据表(ProductID,ProductName,UnitPrice,Unit,Description)  VALUES(10,'联想Y450A-TSI',4899.00,'台',' T6600酷睿2GBDDR320G独立显存' )  

但是如果确实需要强行向标识列中输入数据时，可以采用SET IDENTITY_INSERT语句，如：
SET IDENTITY_INSERT 产品数据表 on

INSERT INTO 产品数据表(ProductID,ProductName,UnitPrice,Unit,Description)  VALUES(10,'联想Y450A-TSI',4899.00,'台','T6600酷睿2GBDDR320G独立显存' )  

如果对某列设置了默认值约束或允许为NULL，则该列可以不输入，系统会自动取默认值或者将其设置为NULL值，但不输入数据的列不应该出现在列列表中。如下述代码是错误的，因为与原式相比，不需要输入UNIT，因为UNIT设有默认值“台”，但在列列表中出现了UNIT，从而造成列列表与值列表的不匹配(“列列表”有4项，而“值列表”只有3项)。

INSERT INTO 产品数据表(ProductName,UnitPrice,Unit,Description)  VALUES('联想Y450A-TSI',4899.00,'T6600酷睿2GBDDR320G独立显存' )  

2. INSERT插入多行数据

INSERT语句可以一次插入多行数据，插入的数据行可以以数据列表的形式列在VALUES列表中，如以下代码向“产品数据表”中输入了三条数据。

INSERT INTO 产品数据表(ProductName,UnitPrice,［Description］)  

VALUES('联想',4899.00,'T66001'),

('联想',4999.00,'T66002'),

('联想',5099.00,'T66003')  

INSERT语句还可以插入来自另一张表中的数据，这时需使用查询子语句SELECT。如以下代码表示从“产品”数据表中取出数据添加到“产品数据表”中，从代码中可以看到，两个数据表的列名不需要相同，但要求列数相同且第二张表中取出的数据符合第一张表中列出的约束要求。

INSERT INTO 产品数据表(ProductName,UnitPrice,Unit,Description)  SELECT NAME,UPRICE,UNIT,P_DESC from 产品

上述代码是将“产品”表中的所有数据添加到“产品数据表”中，如果只需要将“产品”表中的部分数据添加到“产品数据表”中，则可以使用TOP和PERCENT关键词。如以下代码表示将“产品”表中的10行数据添加到“产品数据表”中； 而PERCENT表示百分比范围内的数据，如TOP (10) PERCENT表示取符合查询条件的数据集中10%的数据。

INSERT TOP(10) INTO 产品数据表(ProductName,UnitPrice,Unit,Description)  SELECT NAME,UPRICE,UNIT,P_DESC from 产品

但是TOP和PERCENT指定的数据是无顺序随机的，如TOP(10)取的并不一定是前10行，TOP (10) PERCENT也并不一定是前10%的数据，如果需要指定顺序，则需要添加ORDER BY子句。例如，以下代码从“产品”表中取出数据并按“P_ID”列的值从小到大排序后，再将前10行数据添加到“产品数据表”中。

INSERT TOP(10)INTO 产品数据表(ProductName,UnitPrice,Unit, Description) SELECT NAME,UPRICE,UNIT,P_DESC from 产品 ORDER BY P_ID

5.2.2UPDATE更新数据

TSQL中更新数据的语句是UPDATE，UPDATE可以一次更新一行数据，也可以一次更新多行数据，还可以一次只更新一列的值，或一次更新多列的值。在UPDATE中可以通过更新列列表指定更新的列及数据值，通过WHERE条件子句可以指定更新的数据行。

UPDATE语句的基本语法如下：

UPDATE table_or_View_name

SET <SET caluse expression> ［{,<SET caluse expression>},…］

［WHERE <search condition>］

各主要参数的含义如下： 

■table_or_View_name，被更改的数据表或视图名称。

■SET caluse expression，被更改的列的表达式，如“unit='PCS'”等。

■search condition，用于行数据筛选的条件表达式。

例如，需要将“产品数据表”中所有行的UNIT的值改为PCS，则代码如下：

UPDATE 产品数据表 SET UNIT='PCS'

如果只需要将ProductID列值为3的数据行的UNIT列的值更新为PCS，ProductName列的值更改为“LENOVO计算机”，则代码如下：

UPDATE 产品数据表 SET UNIT='PCS',ProductName='LENOVO计算机' 

WHERE ProductID=3

其中，UNIT='PCS'，ProductName='LENOVO计算机'是被更新的列列表及数据，使用英文半角逗号分隔； ProductID=3是被更新的行数据筛选条件。

如果一次更新的数据行较多，且只需要更新其中的部分数据行，那么可以配合使用TOP和PERCENT来完成。例如，下例更新产品数据表中满足条件ProductID=3的三行数据。

UPDATE TOP 3 产品数据表 SET UNIT='PCS',ProductName='LENOVO计算机' WHERE ProductID=3


UPDATE语句中被更改列的值可以使用常量，如前述例子； 也可以使用表达式，如需要将“产品数据表”中的单价打9折销售，则可以使用如下代码，此句中UnitPrice =UnitPrice*.9表示将UnitPrice列的值更改为原值的90%。

UPDATE 产品数据表 SET UnitPrice= UnitPrice*.9

注意： 在UPDATE中如果不指定WHERE条件子句，则修改的是数据表中的全部数据。由于更改过程往往是不可逆的，因此为了避免出现数据被误改，除非有确实的需要，否则指定WHERE条件是必需的。同样，修改后的数据必须要符合表所设置的各种约束的要求。


UPDATE还可以借助另外一张表的信息来修改数据，如下例中实现了一个相对较为复杂的更新操作。从Sales表中取qty值来更新titles表的ytd_sales列的值，条件是满足titles.title_id=sales.title_id and sales.ord_date=(select max(sales.ord_date) from sales))。

UPDATE titles SET ytd_sales=titles.ytd_sales+sales.qty From titles,sales where 

titles.title_id=sales.title_id and sales.ord_date=(select max(sales.ord_date) from sales))

5.2.3DELETE删除数据

TSQL中删除数据的语句是DELETE。DELETE可以删除指定表的一行或者多行数据。DELETE的基本语法如下：

 DELETE  FROM  table_or_View_Name  ［WHERE <search condition>］

各主要参数的含义如下： 

■table_or_View_Name，指定要删除数据的表或者视图名称。

■search condition，指定要删除的行数据的条件。

例如，以下代码从“产品数据表”中删除ProductID列值为3的数据行。

DELETE  FROM 产品数据表 WHERE  ProductID=3

如果不指定WHERE条件子句，则指定表中的所有数据都会被删除，如以下代码删除了数据表“产品数据表”中的所有数据。

DELETE FROM 产品数据表

注意：  如果没有明确要求从表中删除所有数据，就应该使用WHERE子句指定要删除的数据行的条件； 否则可能会出现数据被误删的严重后果。

DELETE语句同样支持TOP语句，TOP子句的作用与INSERT、UPDATE语句中的TOP相同，删除时会随机删除TOP指定的数据，由于未对数据排序，删除的数据可能是随机的。

如果需要删除表中的所有数据，除了使用DELETE语句外，还可以使用TRUNCATE Table语句，如以下代码与“DELETE FROM 产品数据表”一样都能把“产品数据表”中的数据删除：

TRUNCATE Table 产品数据表

相对来说，使用TRUNCATE Table语句删除表中的所有数据效率更高，且占用系统资源量要少。原因是TRUNCATE Table删除表中的数据时，并不会把删除操作记录在日志中，而且会立即释放表中数据和索引所占的空间。因此，在需要删除表中所有数据时，建议使用TRUNCATE Table语句。

注意： 当删除表中数据时，需要符合表所设置的约束的要求。如果涉及外键约束，还可能会影响其他表中的数据或受到其他表中数据的影响，如删除的是主键表，外键关系是“级联”，则外键表中相关联的数据也会被删除。

删除表中的全部数据时，并不会删除表结构，对数据表结构的定义还将保留在数据库。要彻底删除数据表需要使用DROP TABLE语句。




5.3.1单表
数据查询

5.3TSQL数据查询语言

数据查询同样是数据库管理系统中最常用的操作之一。在TSQL中，提供了众多功能强大的数据查询方式，可以供用户实现多角度、多条件的灵活的数据查询。


5.3.1单表数据查询

在TSQL中数据查询的基本语句是SELECT。SELECT语句最基本的应用如以下代码所示，表示从“产品数据表”

中查询所有数据，包括所有列，“*”代表将所有列都显示出来。因此，该段代码执行的结果如图52所示。

SELECT * FROM 产品数据表



图52查询结果


1. 查询指定列的数据

在某些列数较多的数据表或者某些只需要显示部分列的应用中，如新闻网站的首页只需显示新闻标题等场合时，可以使用SELECT语句显示部分列查询需要的数据。此时，可以采用列列表来代替“*”，并且列列表中列的顺序可以与表结构中列的顺序不一致。如以下代码表示，从“产品数据表”中查看ProductName、UnitPrice两列的数据，查询结果如图53所示。


SELECT ProductName,UnitPrice FROM产品数据表



图53查询部分列的数据


2. 更改列标题的名称

在图53所示的查询结果中，列标题显示的是数据表中列的名称。在有些场合，需要将标题更改为更易于理解的名称，如将ProductName显示为“产品名称”更易于理解，且不会产生歧义。此时，可以通过更改列标题来实现，如以下代码的查询结果如图54所示。

SELECT ProductName AS '产品名称',UnitPrice AS '单价' FROM 产品数据表



图54更改查询结果的标题


如上代码中使用的AS关键词在SELECT语句中是一个可选项，也可以将其去掉，即以下代码可以完成同样的更改列标题显示的作用。

SELECT ProductName '产品名称', UnitPrice '单价' FROM 产品数据表

3. 数据运算

在SELECT语句中还可以添加各种常量、函数、表达式，对查询数据执行各种运算。如对数值型列可以在查询中执行算术运算，对字符型列可以执行字符串的合并、比较等各种运算。

例如以下代码可在ProductName前添加字符常量，并对UnitPrice列打9折后显示为“9折单价”，其中“+”可以用于连接两个字符型数据列，或者连接字符型数据列和字符型常量。查询执行结果如图55所示。SELECT语句对列的运算不会改变表中的实际数据值。


SELECT '产品名称:'+ProductName,UnitPrice*0.9 '9折单价:' FROM 产品数据表



图55对列进行运算后的查询结果


如果需要对不同数据类型的列或数据执行运算，则需要将数据类型转换成相同的数据类型，在TSQL中可以使用的转换函数有CAST和CONVERT。这两个函数的语法如下：

CAST ( expression AS data_type ［ (length ) ］)

CONVERT ( data_type ［ ( length ) ］ , expression ［ , style ］ )

主要参数含义如下： 

■Expression，需要转换的列或表达式。

■data_type，转换的目标数据类型。

■Length，目标数据类型的长度。

■Style，样式参数。

例如，需要将ProductID列从int数据类型转换成varchar数据类型，可以采用以下代码： 

CAST(productid as varchar)

或

convert(varchar,productid) 

经转换后，可以实现不同数据类型之间的运算，如以下代码将ProductID列转换成为varchar后与ProductName列和常量连接显示为“产品编号与名称”。查询执行结果如图56所示。


SELECT '产品编号'+convert(varchar,productid)+','+'产品名称:' +ProductName as '产品编号与名称',UnitPrice*0.9 '9折单价:'FROM 产品数据表



图56使用数据转换后的查询结果


4. 简单的查询条件

如果不指定WHERE条件子句，SELECT查询显示的是数据表中所有行的数据。但在很多场合，用户所需的往往不是全部数据，而是符合条件的部分数据，如在搜索引擎中用户输入的查询关键词，希望得到的是符合条件的数据； 银行ATM刷卡后，希望得到的是与卡号相关的数据。因此，设置WHERE条件，查询满足要求的数据在现实中的应用比查询全部数据更广泛。

如果只需要设定一个条件，如在“产品数据库”查询ProductID列值为4的数据，可以称为简单的查询条件，SELECT语句可表示为：

SELECT * FROM 产品数据表 WHERE ProductID=4

上述构建的条件ProductID=4，采用“=”构建，属于精确匹配。在有些场合，如查询新闻时，可能不知道新闻标题的完整内容，但知道其中的部分关键词，则可以采用模糊匹配条件。模糊匹配采用的关键词是LIKE，一般用于字符型数据的匹配条件中。如想从“产品数据表”中查询ProductName列中包含“联想”的产品时，条件表达式可以写为ProductName like '%联想%'，则查询语句如下：


SELECT * FROM 产品数据表 WHERE ProductName like '%联想%'

查询结果如图57所示。其中需要说明的是，ProductName like '%联想%'中，两端的“%”是通配符，可以匹配任意字符。因此，该表达式的含义是，只要ProductName列中含有“联想”，不论“联想”两字出现在哪个位置，都是符合条件的。在TSQL中共有4种通配符，除了“%”之外，还有三种通配符，分别为_、［］和［^］，这4个通配符代表的含义请参见表51。



图57模糊查询的结果




表51TSQL的通配符





通配符含义


%代表任意零个或多个字符构成的字符串
_代表一个任意字符
［］代表其间指定的范围或集合中的任意单个字符
［^］代表不在指定范围或集合内的任意单个字符


例如： 

■ProductName like '联想%'，表示检索“联想”两字符开头的ProductName列，如联想计算机、联想笔记本。

■ProductName like '%联想'，表示检索“联想”两字符结尾的ProductName列，如世纪联想、LENOVO联想。

■ProductName like '_ab'，表示检索以ab结尾的三个字符的ProductName，如Mab、cab等。

■ProductName like 'a_b'，表示检索以a开头，b结束的三个字符的ProductName，如agb、aab等。

■ProductName like 'ab_'，表示检索以ab开头的三个字符的ProductName，如abc、abm等。

■ProductName like '［abc］'，表示检索含有a或b或c字符的ProductName，如adfghj、bdefhj、ddckjjd等。

■ProductName like '［^abc］'，表示检索不含有a或b或c中一个的ProductName，如ghk、ttmj等。

例如，以下代码可以从AdventureWorks2017数据库的Person.Address表中查找4个字符的邮政编码，且第一个字符限制在a~e，第二个字符限制在a~z，最后两位为数字。查询执行的结果如图58所示。

SELECT city,postalcode,AddressLine1 FROM Person.Address 

where postalcode like '［a-e］［a-z］［0-9］［0-9］


5. 复合查询条件

如果查询条件中包含的条件不止一个，如要求同时满足两个以上的条件，或者满足几个条件中的一个，这样的复杂条件称为复合查询条件。复合查询条件根据逻辑关系的不同，可以分为“与”条件和“或”条件两种。

“与”条件表示要求同时满足两个以上的条件，使用AND关键词，可以构造“与”条件。“或”条件表示在几个条件中只要满足其中的一个，使用OR关键词，可以构造“或”条件。

例如，以下代码构造了一个“与”条件，即要求从“产品数据表”中查询既满足ProductName列中含有“联想”字符，且UnitPrice列低于5000的产品数据，查询结果如图59所示。

SELECT * FROM 产品数据表 where ProductName like '%联想%' AND UnitPrice<5000



图58使用通配符的查询结果




图59复合“与”条件的产品数据


再如以下代码构造了一个“或”条件，即要求查询ProductName列中含有“联想”字符，或者UnitPrice列低于5000的产品数据，查询结果如图510所示。

SELECT * FROM 产品数据表 where ProductName like '%联想%' OR UnitPrice<5000



图510复合“或”条件的产品数据查询




图511复杂的复合查询语句


“与”和“或”条件还可以组合在一起构成更加复杂的复合查询语句，如以下代码表示在“产品数据表”中查询同时满足ProductName like '%联想%' 和 UnitPrice<4000两个条件，或者满足ProductID<8条件的产品数据，查询结果如图511所示。由于AND运算的优先级高于OR，前一个AND条件中的括号可以不写，但为了使代码容易阅读，还是应该添加合适的括号。

SELECT * FROM 产品数据表 where (ProductName like '%联想%' and UnitPrice <4000) or (ProductID<8)


注意： 如果是字符串、日期时间作为条件值，请使用英文半角单引号，如ProductName='联想'； 如果数值作为条件值，可以直接写数字，如ProductID<8。


AND和OR也被称为逻辑运算符，构造的条件表达式也被称为逻辑表达式，TSQL还有一个逻辑运算符是NOT，即对逻辑运算结果取反。

6. 使用比较运算符

TSQL支持采用=、>、<、>=、<=、<>、!=、!>、!<等比较运算符来构造查询条件，其中“<>”表示“不等于”，与“!=”含义相同。“!>”表示“不大于”，与“<=”含义相同，“!<”表示“不小于”，与“>=”含义相同。

在图511的例子中已介绍了UnitPrice<4000的使用形式，其他比较运算符的使用形式基本相同，不另作介绍。

7. 使用范围和列表

在某些情况下，要求查询的条件是某一个范围，如要求查询在20100101到20100131范围内的订单，或者销售量在100~200范围内的产品等，这时可以使用范围关键词BETWEEN和AND来构建条件。

例如，以下代码要求从Orders表中查询orderdate列在20100101到20100115范围内的订单数据。

Select orderid,customerid,employeeid,orderdate from orders where orderdate between '2010-01-01' and '2010-01-15'

如上代码也可以使用比较运算符来构造，如：

Select orderid,customerid,employeeid,orderdate from orders where orderdate>= '2010-01-01' and orderdate <='2010-01-15'

虽然都能获得相同的结果，但相比较而言，BETWEEN和AND范围语句相对简洁一些。

在另外一些场合，范围不是连续的范围，而是一些离散的值，这时可以采用列表。在TSQL中列表可以使用IN关键词来构造，如以下代码表示要求从authors表查询满足state在“'CA','IN','MD'”三项列表中的数据。

Select * from authors where state in('CA','IN','MD')

如果列表范围是数值，可以使用数字值来构造列表，如以下代码表示从“产品数据表”中查询ProductID列在“1,3,4,8”列表范围中的产品数据。

SELECT * FROM 产品数据表 where ProductID IN (1,3,4,8)




5.3.1.2使用
聚合函数

8. 使用聚合函数

聚合函数包括AVG、MIN、MAX、SUM、COUNT、STDEV、STDEVP、VAR、VARP，这些聚合函数可以在SELECT语句中实现对数据的统计。例如，以下代码分别使用上述聚合函数计算“产品数据表”中UnitPrice列的平均值、最小值、最大值、合计等。查询的执行结果如图512所示。



图512使用聚合函数查询的结果



SELECT COUNT(*) as '表中数据条数', Min(UnitPrice) as '最低单价', MAX(UnitPrice) as '最高单价', SUM(UnitPrice) as '单价合计',AVG(UnitPrice) as '平均单价', 

STDEV(UnitPrice) as '单价标准偏差', STDEVP(UnitPrice) as '单价总体标准偏差',

VAR(UnitPrice) as '单价方差', VARP(UnitPrice) as '单价总体方差' FROM 产品数据表

9. 数据排序

在有些时候，用户要求查询获取的结果数据具有某种顺序。如新闻网站的新闻列表，往往会按时间排序，把最新的新闻排在最前面； 在网络论坛中，很多人气比较高的帖子会被置顶。这些应用都需要对查询结果进行排序，在TSQL中用来排序的语句是ORDER BY。

ORDER BY可以对数据按升序或者降序排列，升序使用的关键词是ASC，降序使用的关键词是DESC，系统默认设置是升序，即ASC可以省略。

例如，以下代码对“产品数据表”中的数据按UnitPrice列从低到高的顺序排列，查询执行的结果如图513所示。

SELECT * FROM 产品数据表 ORDER BY UnitPrice



图513使用排序后的查询结果


ORDER BY可以按多个列进行排序，如ORDER BY UnitPrice,ProductID，排序时会先以第一个指定列的顺序进行排列，如果第一列的值相同，再按第二列的顺序进行排序，以此类推。在对多个列进行排序时，可以对每个列分别指定排列的顺序，如ORDER BY UnitPrice DESC,ProductID，对第一列 UnitPrice按从高到低的降序排列，第二列按默认设定的升序排列。

10. 去除重复数据

虽然在数据表中应用约束，如主键约束可以实现对数据完整性的检验，但并不能完全避免数据重复，如在某些列上出现相同内容。事实上在某些列出现数据相同，有时也是业务所需要的，如“订单细节表”往往会出现orderid列相同的情况，因为有多行订单细节数据是属于同一订单的。但是在有些情况应该避免在查询结果中显示重复的数据，如新闻网站中新闻列表的标题不应该出现重复等。

在TSQL中可以使用DISTINCT关键词来避免在查询结果中显示重复数据，如在不使用DISTINCT时，“产品数据表”的查询结果中出现了重复(第2、3、4行数据)，如图514所示。




图514出现重复的查询结果集


以下代码使用了DISTINCT，则查询结果如图515所示，重复行已经被去除。

SELECT DISTINCT ProductName FROM 产品数据表 ORDER BY ProductName



图515使用DISTINCT去除重复行数据


DISTINCT是以列列表作为重复判断的依据。因此，如果将上述查询语句更改为SELECT DISTINCT ProductName,ProductID FROM 产品数据表 ORDER BY ProductName，由于ProductName,ProductID两列数据组合不重复，因此不会去除任何数据行。

11. 关于NULL

NULL是一个特殊值，如果需要查看数据表中某一列值为NULL的数据，有两种方式可以构造NULL条件： “IS NULL”和“=NULL”，如ProductName IS NULL或者ProductName=NULL。究竟取哪种表达方式，取决于系统的设置： SET ANSI_NULLS {ON|OFF}。

在SQL Server早期的版本中，允许使用where ProductName=null查询ProductName列中是否含有NULL值。但是，这不符合ANSI标准，因为ANSI标准将NULL看成一个完全未知的值，不能等于任何其他值。设置SET ANSI_NULLS ON，将无法使用where ProductName=NULL，此时可以使用where ProductName is null。

例如下列代码中，第一行代码没有查询到数据，而第二行代码有数据产生，如图516所示。

SELECT * FROM 产品数据表 where ProductName=null 

SELECT * FROM 产品数据表 where ProductName is null



图516NULL与IS NULL的区别


如果在查询代码前添加SET ANSI_NULLS OFF，即关闭ANSI_NULLS选项的设置后，则两条语句执行的结果是相同的，如图517所示。

SET ANSI_NULLS OFF

SELECT * FROM 产品数据表 where ProductName is null

SELECT * FROM 产品数据表 where ProductName=null

注意： 对于空值，可以使用where ProductName=''。



图517设置SET ANSI_NULLS OFF后NULL与IS NULL相同


12. 使用GROUP BY分组

在实际业务运行过程中，企业会经常需要对数据进行分组。如需要查看不同产品的销售量、不同业务员的业绩时，需要对查询数据进行分组，然后执行统计。在TSQL中可以利用GROUP BY来实现对数据的分组。

例如，需要对“订单细节表”中的数据按照产品进行分组统计，计算不同产品的销售数量和销售金额，可以用以下代码实现。查询执行的结果如图518所示。

SELECT productid, sum(sales) as '销售量',sum(subtotal) as '销售额' 

from dbo.订单细节表 GROUP BY productid



图518使用GROUP BY分组统计


如果需要从GROUP BY分组的数据中筛选符合特定条件的数据，需要使用HAVING，而不能使用WHERE。如要从上述分组汇总的数据集中筛选出ProductID列值为3的数据，可以使用以下代码：

SELECT productid, sum(sales) as '销售量',sum(subtotal) as '销售额' from dbo.订单细节表 GROUP BY productid HAVING ProductID=3

查询执行后的结果如图519所示。



图519使用HAVING筛选分组数据


但WHERE可以置于GROUP BY子句之前，即先按WHERE条件过滤要用于分组的数据，然后使用HAVING从分组后的数据集中筛选需要的数据，如以下代码可以实现上述要求。如果直接将以下代码中的WHERE替换为HAVING，就会产生语法错误。

SELECT productid,sum(sales) as '销售量',sum(subtotal) as '销售额' 

from dbo.订单细节表 Where ProductID>1 GROUP BY productid HAVING ProductID=3

另外，GROUP BY还可以组合TOP(n) PERCENT、ROLLUP、CUBE等，实现更加复杂的查询分组统计的结果。例如以下代码，添加了WITH ROLLUP，执行结果如图520所示，与图518相比，多一行汇总数据，汇总行汇总了总销售量和销售额。

SELECT productid, sum(sales) as '销售量',sum(subtotal) as '销售额' 

from dbo.订单细节表 Where ProductID>1 GROUP BY productid WITH ROLLUP



图520使用ROLLUP执行分组统计的结果


13. 使用子查询

上述实例在构造where条件表达式时，所采用的表达式值都是某一常量或者一个常量列表。事实上，在实际使用过程中，表达式值可以来自其他数据表中的值。如有两张数据表： 客户信息表(Customer)和订单表(Order)。如果需要从订单表中查阅下单客户的信息，而客户信息保存在客户信息表中。要实现这一类应用，可以通过子查询实现。

子查询指嵌套在查询语句中的查询，子查询可以出现在查询语句的条件表达式中，即在Where 子句中。

例如，以下代码可以查看下订单的客户信息。

select * from ［dbo］.［客户数据表］  where CustomerID in (select ［customerID］ from ［Orders］.［订单表］)

代码中，where子句中“select［customerID］from［Orders］.［订单表］”表示从订单表中获取customerID，返回的是已下过单的客户编号列表，以此列表为范围构造查询的条件，可以进一步获取客户数据表中这些客户编号列表对应的客户的详细信息。代码执行的结果如图521所示。



图521子查询返回值列表


子查询在查询语句中，可以嵌套使用。即在一个子查询中可以包含其他子查询，如下代码使用到了两个子查询，其中，最后一个子查询获取到了“客户名称”中包含“海天”的客户的客户编号值，并将这个返回的结果值作为前一个子查询的条件值。代码执行的结果如图522所示。

select * from ［dbo］.［订单细节表］ where ［orderid］=(select ［orderid］ from ［Orders］.［订单表］ where ［customerID］=(select CustomerID from ［dbo］.［客户数据表］  where ［CustomerName］ like '%海天%'))



图522子查询嵌套




图523子查询获取查询列


子查询也可以出现在查询的列列表中，从而构造复杂的查询列表。如以下代码，在图522所示代码的基础上，应用子查询“(select ［ProductName］ from ［dbo］.［产品数据表］ where productid= d.productid) as 产品名称”从产品数据表中获取到了对应产品编号的产品名称，其中“d”是订单细节表的别名。代码执行的结果如图523所示。

select *,(select ［ProductName］ from ［dbo］.［产品数据表］ where productid= d.productid) as 产品名称 from ［dbo］.［订单细节表］ as d where ［orderid］=

(select ［orderid］ from ［Orders］.［订单表］ where ［customerID］=

(select CustomerID from ［dbo］.［客户数据表］  where ［CustomerName］ like '%海天%'))



子查询在使用时一般需要包含在括号内。

14. ALL、SOME和ANY

ALL、SOME和ANY用于与指定的结果集进行比较。其中ALL表示结果集中的所有值都满足条件时，返回结果为True。而SOME和ANY只要结果集中有满足条件的值，其返回的结果就是True，并不需要所有的值都满足条件； 如果所有的值都不满足条件，则返回的结果为False。

ALL、SOME和ANY使用的基本语法如下： 

scalar_expression { = | <> | != | > | >= | !> | < | <= | !< } { SOME | ANY } ( subquery )

其中，subquery为子查询，子查询返回一个结果集，scalar_expression为表达式，表达式通过各种运算符与子查询结果中的值进行逐一比较。

例如，以下代码为从订单表中查看那些未被处理(Status=0)，并且送货时间(shiptime)早于系统当前时间的订单。由于采用ANY(或SOME)，只要子查询“select shiptime from ［Orders］.［订单表］”返回的送货时间列表中有一项以上的值早于系统时间，则返回结果值为True。代码执行的结果如图524所示。

select * from ［Orders］.［订单表］ where GETDATE()>=ANY(select shiptime from ［Orders］.［订单表］) and Status=0



图524使用ANY或SOME


而当采用ALL构造查询条件时，由于订单数据表中的并不是所有的订单的送货时间都早于系统当前时间，因此查询条件的返回结果为False。代码执行返回结果为空，如图525所示。

select * from ［Orders］.［订单表］ where GETDATE()>=

ALL(select shiptime from ［Orders］.［订单表］) and Status=0



图525使用ALL





5.3.2多表联接
数据查询

5.3.2多表联接数据查询

在SQL Server中根据关系数据库规范化的要求，有时为避免数据的重复冗余，需要将数据表进行水平分隔，以满足规范范式的要求。这样，在进行数据查询时，为了解查询数据的全貌，经常需要将多张表的数据联接到一起来显示。前述介绍的子查询可以实现上述要求，但很多时候子查询并非是最佳选择。

请先来看一下本书的案例数据库NetSale中数据表的情况，如图526所示。



图526NetSale数据库中的数据表


从图526中可见，如果要从“订单表”中查看下单客户的详细信息，必须联接“客户数据表”，要从“订单细节表”中查看订购的产品的详细信息，必须联接“产品数据表”。如果还想知道下单的时间与客户的详细信息，还必须联接“订单表”且需要从“订单表”再去联接“客户数据表”。虽然在数据表的设计过程中，表间已建立了多种外键关系，但这种外键关系只对数据管理起约束作用，对查询不起作用； 用户如果要从多张表中获取数据，必须使用查询联接语句在表间建立联接。

TSQL中，用于联接多表的联接查询语句可以划分为内联接(INNER JOIN)、外联接(OUTER JOIN)和交叉联接(CROSS JOIN)。

1. 内联接

内联接采用INNER JOIN联接两张数据表，通过ON关键词构造两张表之间的关系。内联接可以把两表中符合联接条件的数据抽取出来生成第三张表。因此，内联接生成的表的数据通常会比两源表中任一表的数据量少，或者等于数据量较小的那张表的数据量。


例如，要从表“订单细节表”中查询“购买的产品名称、数量以及金额”，则需要将“订单细节表”与“产品数据表”联接，以下代码可以实现上述要求。

SELECT 产品数据表.ProductName, 订单细节表.sales as '销售量',订单细节表.subtotal as '销售额' from dbo.订单细节表 inner join 产品数据表 on 订单细节表.ProductID=产品数据表.ProductID

如上代码中，“产品数据表.ProductName, 订单细节表.sales as '销售量',订单细节表.subtotal as '销售额'”为查询的列列表，包含从“产品数据表”中提取的ProductName列和“订单细节表”表中的sales和subtotal列； “INNER JOIN 产品数据表”表示从“订单细节表”联接“产品数据表”； “ON订单细节表.ProductID=产品数据表.ProductID”是联接的条件，即要求“订单细节表”中ProductID列的值等于“产品数据表”中ProductID列的值。查询结果如图527所示。



图527联接查询


为简化代码编写，在联接语句中可以使用别名代替表名，如以下代码使用P代表“产品数据表”，使用D代表“订单细节表”，查询执行的结果同图527。

SELECT P.ProductName, D.sales as '销售量',D.subtotal as '销售额' from dbo.订单细节表 AS D inner join 产品数据表 AS P on D.ProductID=P.ProductID



图528联接多张表的查询


如果需要联接两张以上数据表，则可以使用INNER JOIN继续联接，例如以下代码联接了“订单细节表”“订单表”“客户数据表”“产品数据表”，查询结果如图528所示。

SELECT P.ProductName,D.sales as '销售量',D.subtotal as '销售额', C.CustomerName as '客户名称',C.ShipAddress as '送货地址',O.ordertime as '订购时间' from dbo.订单细节表 AS D 

inner join 产品数据表 AS P on D.ProductID=P.ProductID inner join dbo.订单表 as O on D.orderID=O.orderID inner join dbo.客户数据表 as C on O.CustomerID=C.customerID


联接数据表并不要求用作联接条件的两列的名称必须相同，如D.orderID=O.orderID中两端的orderID并不要求名称相同，但是数据类型最好相同或者能够转换为相同的类型。系统默认联接是内联接，因此JOIN等同于INNER JOIN，即当INNER省略时，执行的联接方式是内联接。

2. 外联接

外联接采用OUTER JOIN联接两张数据表，同样通过关键词ON构造联接条件。外联接根据联接方向不同，可以划分为左联接LEFT OUTER JOIN、右联接RIGHT OUTER JOIN和完全外联接FULL OUTER JOIN。与内联接只显示满足连接条件的数据不同，外联接可以分为主表和从表，主表中的数据会被全部显示出来，而从表中只显示满足联接条件的数据，这样外联接结果一般会比两表中数据量小的表中的数据多。

如果是左联接LEFT JOIN，则位于FROM子句左端的表为主表，另一端的表为从表； 右联接则反之。

例如，以下代码构造了一个左连接，“订单细节表”为主表，“产品数据表”为从表，联接条件是D.ProductID=P.ProductID。由于“订单细节表”与“产品数据表”建立了外键关联，即在输入“订单细节表”的ProductID列值时，要求必须已经在“产品数据表”中存在。因此，查询结果与内联接相同，如图529所示。

SELECT P.ProductName, D.sales as '销售量',D.subtotal as '销售额' from dbo.订单细节表 AS D LEFT join 产品数据表 AS P on D.ProductID=P.ProductID



图529左联接查询结果


同样，可以构造上述两张表的右联接，查询结果如图530所示。与图529相比较可见，在第二个联接中，主表“产品数据表”原有7行数据全部显示出来了，另外，产品“联想(Lenovo)小新Pro13.3英寸”在“订单细节表”中有2条对应数据，所以出现了2次。因此，查询结果共计产生8行数据。与从表“订单细节表”有对应关系的3行数据，在“销售量”和“销售额”中填的是“订单细节表”中获取的数据，而另5行在“订单细节表”未找到对应数据，则填充为NULL。

SELECT P.ProductName, D.sales as '销售量',D.subtotal as '销售额' from dbo.订单细节表 AS D RIGHT join 产品数据表 AS P on D.ProductID=P.ProductID



图530右联接查询结果


FULL OUTER JOIN与上述两种外联接不同，FULL OUTER JOIN会从两联接表中获取数据，如果有对应关系的会填上对应数据，没有对应关系的以NULL值填充。因此，FULL OUTER JOIN联接生成的数据量会比两联接表的数据量大，一般会等于左联接与右联接两种方式产生的数据量大的数量。



图531FULL OUTER JOIN联接查询结果


例如，以下代码构造了“订单细节表”与“产品数据表”之间的FULL OUTER JOIN，查询的结果如图531所示。数据量与数据量大的右联接查询的数量相同。

SELECT P.ProductName, D.sales as '销售量',D.subtotal as '销售额' from dbo.订单细节表 AS D FULL join 产品数据表 AS P on D.ProductID=P.ProductID

OUTER JOIN中的OUTER可以省略不写，不会影响查询结果。


3. CROSS JOIN联接

CROSS JOIN联接也被称为交叉联接。与内联接、外联接都不同，交叉联接会罗列所有可能的数据，最终形成的结果是两联接表的笛卡儿乘积，即第一张表的数据行数与第二张表的数据行数的乘积。如果联接的两表分别有10行和20行数据，则CROSS JOIN查询结果的数据行数为200行。

例如，以下代码构造了一个CROSS JOIN联接查询的实例，查询结果如图532所示，生成的数量为“产品数据表”7行和“订单细节表”3行的乘积，共计21行数据。

SELECT P.ProductName, D.sales as '销售量',D.subtotal as '销售额' from dbo.订单细节表 AS D CROSS join 产品数据表 AS P



图532CROSS JOIN查询结果


4. 多表联接查询中使用WHERE子句

与单表查询一样，在多表联接查询中，同样可以使用WHERE来构造数据查询的条件，如以下代码在内联接4张表的基础上添加了数据筛选的条件，查询结果如图533所示。由于参与联接的多张表中可能存在列名相同的情况，因此在构造条件时，应在“列名”前添加表名或者表的别名，如以下代码中C.CustomerID=3表示取Customer表的CustomerID列的值作为筛选条件。




图533多表联接查询中使用WHERE条件



SELECT P.ProductName, D.sales as '销售量',D.subtotal as '销售额', C.CustomerName as '客户名称',C.ShipAddress as '送货地址',O.ordertime as '订购时间' from dbo.订单细节表 AS D inner join 产品数据表 AS P on D.ProductID=P.ProductID inner join dbo.订单表 as O on D.orderID=O.orderID inner join dbo.客户数据表 as C on O.CustomerID=C.customerID where C.CustomerID=3


提示： 在多表联接中，同样可以使用单表联接中的各种关键词，如TOP(n) PERCENT、DISTINCT、ORDER BY、GROUP BY等。




5.3.3使用
SELECT INTO
语句

5.3.3使用SELECT INTO语句

SELECT INTO可以把由SELECT语句中选定的数据保存到一张新数据表中。事实上SELECT INTO语句包含三个子过程： 第一个子过程为SELECT INTO语句根据SELECT选择的列列表及各列的数据类型生成一段创建数据表的代码； 第二个子过程为SELECT INTO语句使用第一过程生成的代码，新建这张数据表； 第三个子过程则是将从源表中选定的数据导入新建的数据表中。

例如，以下代码是图532执行的CROSS JOIN语句，会生成21行数据，以下代码稍作修改，添加了INTO Sales，改造成为一条SELECT INTO语句，执行之后会在当前数据库NetSale中创建数据表Sales，并且会在Sales中添加21行数据。

SELECT P.ProductName, D.sales as '销售量',D.subtotal as '销售额' INTO sales from dbo.订单细节表 AS D CROSS join 产品数据表 AS P 

新生成的Sales表中的数据如图534所示。



图534SELECT INTO生成的新数据表


通过SELECT INTO生成的数据表可以是普通用户表，也可以是临时表。如果在SELECT INTO语句中添加WHERE条件，则生成的数据是满足特定条件的数据。这使SELECT INTO非常适合在一些复杂的数据库应用程序中，通过SELECT INTO生成临时数据以备下一步使用的场合。

以下代码将SELECT INTO生成的数据保存到本地临时数据表#sales中。本地临时数据表在使用完毕后，会自动删除，不会给系统造成负担。

SELECT P.ProductName, D.sales as '销售量',D.subtotal as '销售额' into #sales from dbo.订单细节表 AS D CROSS join 产品数据表 AS P 




5.3.4组合查询

5.3.4组合查询

TSQL提供了UNION、INTERSECT和EXCEPT运算符，可以实现组合查询。组合查询指在某些场合，查询结果需要通过多条SELECT语句从一张或多张表中来获取，查询最终结果是多条SELECT语句查询结果的汇总数据集。组合查询可以看作数据的垂直联接，而前面所介绍的多表联接查询可以看作数据的水平联接。

TSQL提供的三个查询组合运算符可以分别满足三种不同的需要，但是组合查询要求每条SELECT语句生成的数据集中列的个数、列的数据类型和顺序必须相同。

1. UNION

UNION是一种并集运算，可以将两个以上的查询结果合并成一个结果，并在后续的结果集中去除前面结果集中已有的数据行。例如以下代码采用UNION组合了两条SELECT语句，这两条SELECT语句分别从“产品数据表”和“产品”表中查询ProductName数据。

SELECT ProductName from 产品数据表

UNION

SELECT ProductName from 产品

该段代码执行的查询结果如图535所示，共计生成11条数据。图536是“产品数据表”和“产品”中的原始数据情况，“产品数据表”原有7条数据，但3条重复，被去除2条，因此第一个查询从“产品数据表”中获取了5条数据； 而第二个查询原本应该出来6条数据，由于有2条与第一个查询重复，被去除2条，因此共计生成了9条数据。



图535UNION给合查询结果




图536“产品数据表”和“产品”表的原始数据


如果需要保留所有重复数据，可以使用UNION ALL。如以下代码执行后，生成的数据集是两条查询语句产生的数据集的总和，共计13条，执行结果如图537所示。

SELECT ProductName from 产品数据表

UNION ALL

SELECT ProductName from 产品



图537UNION ALL查询结果


2. INTERSECT

INTERSECT可以返回多条查询语句中都包含的非重复数据。例如以下代码，从“产品数据表”和“产品”表中执行INTERSECT组合查询，结果如图538所示。由于两张表都有且不重复的数据只有1条，因此最终结果只有1条。

SELECT ProductName from 产品数据表

INTERSECT

SELECT ProductName from 产品



图538INTERSECT组合查询结果


注意： INTERSECT不支持ALL操作。

3. EXCEPT

EXCEPT可以比较左右两个查询结果集的差异，并从左侧的查询结果集中返回在右侧找不到的数据，即从左侧的结果集中减去与右侧结果集相同的数据后得到的结果。

例如，以下代码使用EXCEPT从“产品数据表”和“产品”表中获取组合查询结果。执行结果如图539所示。因为“产品数据表”中原有5条非重复的数据，经去除“产品”表中的2条与之相同的数据后，最后得到4条非重复的数据。

SELECT ProductName from 产品数据表

EXCEPT

SELECT ProductName from 产品



图539EXCEPT组合查询结果





5.3.5使用FETCH
与OFFSET分页

5.3.5使用FETCH与OFFSET分页

当查询结果数据量较大时，采用分页方式显示数据是一种必然的选择。在TSQL未提供直接的分页语句之前，实现分页功能是一个相对较为复杂的过程。自SQL Server 2012起，TSQL提供了FETCH和OFFSET参数，可以非常简捷和高效地实现大数据集的分页显示。

FETCH和OFFSET可以作为Order By子句的参数，其语法如下：

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

［

FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY

］

其中，OFFSET参数用于指定从结果集中获取记录的起始行，其后的参数可以是整数，也可以是表达式，integer_constant代表整数值，offset_row_count_expression代表表达式。FETCH用于指定在处理OFFSET子句后返回的行数，该值可以是大于或等于1的整数常量或表达式，同样integer_constant代表整数值，offset_row_count_expression代表表达式。参数中ROW和ROWS具有相同含义，FIRST和NEXT也具有相同含义。

以下代码演示了FETCH和OFFSET参数的使用方法。

在SQL Server Management Studio中，单击工具栏中的“新建查询”按钮，在查询编辑器窗口执行以下代码，执行结果如图540所示。第一条SELECT语句显示了产品数据表中的所有数据，第二条SELECT从产品数据表返回从第一行开始的三条记录。

Use NetSale

SELECT  ProductID,ProductName,UnitPrice,Unit,Instocks  FROM 产品数据表  order by ProductID

GO

SELECT  ProductID,ProductName,UnitPrice,Unit,Instocks  FROM 产品数据表  order by ProductID OFFSET 0 ROW  FETCH NEXT 3 ROW ONLY



图540使用确定整数值的FETCH和OFFSET分页


在上述代码中，FETCH和OFFSET参数都使用了确定的整数值。在实际使用过程中，FETCH和OFFSET参数可以是变量值。以下代码演示了变量的使用方法，执行结果如图541所示。

Use NetSale

DECLARE @OffsetRows tinyint=1,@FetchRows tinyint =5

SELECT  ProductID,ProductName,UnitPrice,Unit,Instocks  FROM 产品数据表 Order by ProductID OFFSET @OffsetRows ROWS FETCH NEXT @FetchRows ROWS ONLY



图541使用变量实现分页


上述代码中定义了两tinyint型变量@OffsetRows和@FetchRows，分别用于保存分页起始的记录行和每页返回的行数。根据这两个变量的取值变化可以实现灵活的分页，FETCH和OFFSET参数的分页可以结合存储过程，接收客户端程序传递的参数实现大记录集的分页。

5.4TSQL附加语言元素

TSQL作为数据库操作语言，除了上述语句之外，还包含很多附加的语言元素，如标识符、保留关键字、常量、变量、运算符、流程控制语句、函数、注释等。本节介绍这些内容。




5.4.1标识符

5.4.1标识符

在TSQL中，标识符用于命名各种对象，如数据库名称、数据表名称、存储过程等，以及变量、函数等名称。与其他语言类似，TSQL中的标识符也必须符合标识符命名的规则，这些规则包括以下几项。

(1) 首字符可以是Unicode字符集中的一个字母，包含英文字母A~Z、a~z，以及其他Unicode字符，如汉字等。

(2) 首字符还可以是下画线(_)、位置符号(@)、数字符号(#)。但以这些符号作为首字符时会有不同的含义，如位置符号(@)开头表示定义的标识为局部变量，以两个@开头表示系统内置的某些函数； 以一个#开头表示局部临时表或过程，以两个#开头表示全局临时对象。

(3) 标识符长度限制在128个字符以内，除了局部临时表的名称之外，其他标识长度限制在116个字符以内。

(4) 后续字符可以是Unicode字母、数字、@、$、_、#等符号。

(5) 标识符不能是SQL Server的保留关键字。

(6) 标识符不能嵌入空格或除上述字符以外的其他特殊字符。

例如，Products129、_129、@1G等都是合法的标识符，而Customer Name、1_name等则不是合法标识符。

在有些场合中可以使用双引号("")和中括号(［］)来引用标识符，被称为分隔标识符，如以下语句，因为USER是SQL Server保留关键字，会出现语法错误。

SELECT * from USER

但是将语句改写为以下代码，则不会出现语法错误。

SELECT * from ［user］ 或者 SELECT * from "user"

但是双引号引用的标识符只有在QUOTED_IDENTIFIER选项设为ON时才会有效。默认可以使用中括号(［］)作为分隔标识符。




5.4.2保留
关键字

5.4.2保留关键字

保留关键字是SQL Server预留的用于定义、操作和访问数据库的关键词，是TSQL的组成部分。这些关键词不能直接用于命名标识符，虽然允许通过分隔标识符，如［］或""来引用这些关键词来作为标识符，但为了避免引起不必要的误解，建议不要使用关键字作为标识符。

TSQL中的保留关键字包括ADD、ALL、ALTER、AND、ANY、AS、ASC、AUTHORIZATION、BACKUP、BEGIN、BETWEEN、BREAK、BROWSE、BULK、BY等现在用的180多个关键字，还包括ABSOLUTE、ACTION、ADMIN、AFTER、AGGREGATE、BEFORE、FREE等将来可能使用的关键字190多个。




5.4.3常量与变量

5.4.3常量与变量

常量是表示特定数据值的符号，如'SQL Server 2022'表示一个字符串常量，1表示一个整型常量，1.0表示浮点数常量。在SQL Server 2022中，要求字符串常量需要使用一对单引号(")，数值型常量直接使用数值，日期时间型常量需要使用一对单引号(")。

变量指在TSQL代码执行过程中，其值可变，需要赋值的对象。在SQL Server 2022中，变量可用于批处理和脚本中，例如用来计算循环的次数，也可以保存数据值以供控制流语句测试，还可以用于保存存储过程或函数返回的数据值等。

在TSQL中，定义变量的语句为DECLARE，所定义变量的首字符必须是@，且必须指定数据类型和长度。例如：

DECLARE @SalesCount int

DECLARE语句可以一次指定多个变量，如：

DECLARE @SalesCount int,@saler_name varchar(20)

默认定义的变量其值为NULL，如果需要对变量赋值，可以使用SET语句，如：

DECLARE @saler_name varchar(20)

SET @saler_name='王强'

变量定义后，可以在存储过程、函数或者其他过程中使用。如以下代码定义了变量@id，并将之用于SELECT语句，执行结果如图542所示。

DECLARE @ProductID int 

Set @ProductID =1

select * from 产品数据表 where ProductID>@ProductID



图542使用变量 






5.4.4运算符

5.4.4运算符

运算符是程序设计语言中最重要的元素之一。TSQL提供了算术运算符、比较运算符、逻辑运算符、赋值运算符、字符串连接运算符、位运算符和一元运算符等多种运算符，使TSQL具备完成各种运算的能力。

TSQL运算符的含义见表52。


表52TSQL的运算符





类别运算符含义


算术运算符
+(加)加
-(减)减
*(乘)乘
/(除)除
%(取模)返回一个除法运算的整数余数。例如，13 % 5=3 

赋值运算符=给变量赋值，或其他相关的赋值运算

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

字符串连接运算符+连接两个字符串组成一个新的字符串，如'abc'+'123'，值为'abc123'

位运算符
&位与
|位或
^位异或
+数值为正
-数值为负
~返回数字的非

比较运算符
=等于
>大于
<小于
>=大于或等于
<=小于或等于

比较运算符
<>不等于
!=不等于
!<不小于
!>不大于


当一个表达式中包含上述运算符中的多个时，TSQL会根据不同运算符的优先级来执行运算。这些运算符的优先级顺序如下。

■正、负、非(+、-、~)

■*、/、%

■+(加法或字符串连接)、-(减法)、&

■=(比较)、>、<、>=、<=、<>、!=、!>、!<

■^、|

■NOT

■AND

■ALL、ANY、BETWEEN、IN、LIKE、OR、SOME

■=(赋值)

例如，以下代码中使用了SOME运算符，由于“产品数据表”中的ProductID列有小于3的值，所以下式中的IF条件成立，会继续执行“SELECT * from 订单细节表”查询语句。

if (3>SOME(select Productid from dbo.产品数据表))

SELECT * from 订单细节表

而如果改为以下表达式，因为“产品数据表”中的ProductID列并不全小于3，因此，下式中的IF条件不成立，不会执行“SELECT * from 订单细节表”。

if (3>ALL(select Productid from dbo.产品数据表))

SELECT * from 订单细节表

再如： 

30&12=12,30|12=30,30^12=18,~50=-51,3%4=3




5.4.5控制
流语句

5.4.5控制流语句

TSQL中提供了9种控制流语句，可以实现对程序流程的控制。这些语句包括BEGIN…END、BREAK、CONTINUE、GOTO、IF…ELSE、RETURN、TRY…CATCH、WAITFOR、WHILE等，这些语句的含义如表53所示。


表53TSQL控制流语句的含义





控制流语句含义


BEGIN…END用于定义一组要求连续执行的语句块，语句块可以嵌套定义
BREAK跳出循环语句的循环过程，继续执行循环语句后面的语句
CONTINUE重新开始新的WHILE循环
GOTO跳转到由GOTO后指定的语句，并执行
IF…ELSE条件分支语句，如果条件成立，执行IF后的语句； 条件不成立则执行ELSE后的语句
RETURN从过程、函数中返回，不再执行RETURN后的语句，如果RETURN语句指定有返回值，则将值返回，否则返回值为0
TRY…CATCH错误捕捉语句，程序先执行TRY后的语句，如果出现错误，则执行CATCH后的语句。因此，可以在CATCH中添加错误处理语句，实现对错误的响应
WAITFOR挂起后续语句，直到以下情况发生： 已超过指定的时间间隔、到达一天中指定的时间、指定的RECVEIVE语句至少修改一行数据； 再继续执行挂起的及后续的语句
WHILE为循环语句。当条件成立时，循环执行循环体内的语句，条件不成立时，执行循环体后续的语句


上述控制流语句经组合后，可以实现顺序结构、条件分支结构和循环结构等程序结构，以下对常用的程序结构进行介绍。

1. 使用IF…ELSE实现条件分支结构

IF…ELSE语句是实现条件分支最常用的语句，基本语法如下。当Boolean_expression为真时，执行IF后的语句块； 为假时，执行ELSE后的语句块。

IF Boolean_expression 

{ sql_statement | statement_block } 

［ ELSE 

{ sql_statement | statement_block } ］ 

例如，以下代码配合EXISTS构建了一个条件分支语句。在IF语句中先判断是否存在“产品”表，如果存在，就执行BEGIN…END之间的语句块，不存在则提示“数据表产品不存在。”该段代码执行的结果如图543所示。

USE NetSale

IF EXISTS(SELECT * from INFORMATION_SCHEMA.TABLES where TABLE_NAME='产品')

BEGIN

SELECT '数据表产品存在。'

SELECT * from 产品

END

else

SELECT '数据表产品不存在。'



图543执行IF…ELSE条件分支语句的结果


2. 使用WHILE构造循环结构

WHILE语句可以用来构造循环结构程序。WHILE语句的语法如下，即当Boolean_ex pression表达式为真时，执行循环体内的语句，执行到BREAK时跳出循环，执行循环体后续的语句。

WHILE Boolean_expression 

{ sql_statement | statement_block } 

［ BREAK ］ 

{ sql_statement | statement_block } 

［ CONTINUE ］ 

{ sql_statement | statement_block }

例如以下使用WHILE语句构造了一个循环结构的程序，WHILE循环体执行了三次，当变量@i=4时，退出循环。执行结果如图544所示。

declare @i int

set @i=1

while 0<(SELECT COUNT(*) from 产品)

BEGIN

SELECT * from 产品

set @i=@i+1

if @i>3 

break

END



图544WHILE循环执行结果


3. 使用CASE

在TSQL中还可使用CASE语句来构造条件分支结构。CASE的基本语法如下，input_expression为输入的用于判断的表达式，当when_expression表达式为真时，执行THEN后的语句，WHEN语句可以有多条，当所有的WHEN语句条件都不满足时，执行ELSE后的语句。

CASE input_expression 

WHEN when_expression THEN result_expression 

［ …n ］ 

［ ELSE else_result_expression ］ 

END 

例如以下代码，构造了一个应用CASE语句实现的条件分支结构，根据CategoryID列取值不同，将数据分成三类，即“台式计算机”、“笔记本电脑”和“其他”，执行结果如图545所示。

SELECT ProductID, ProductName, UnitPrice, 产品类别=

CASE

WHEN CategoryID=1 THEN '台式计算机'

WHEN CategoryID=2 THEN '笔记本电脑'

ELSE '其他'

END

FROM  产品数据表 



图545CASE构造的条件分支结构执行结果


TSQL的控制流语句同样可以实现其他程序设计语言的设计功能，如以下代码实现了1+2+…+100的计算。

Declare @i int,@sum int

SET @i=0

SET @sum=0

while @i<=100

BEGIN

SET @sum=@sum+@i

SET @i=@i+1

END

SELECT @sum  

执行结果为5050。




5.4.6函数

5.4.6函数

TSQL提供了大量的系统函数，可用于实现查询语句列运算、查询条件构造、触发器、视图以及各种表达式等。这些系统函数提高了TSQL程序设计的效率，也进一步提升了SQL Server的易用性。

在TSQL中，函数根据执行功能的不同，可以分为聚合函数、配置函数、加密函数、游标函数、日期和时间函数、数学函数、元数据函数、排名函数、行集函数、安全函数、字符串函数、系统函数、系统统计函数、文本函数14类，还可以根据返回值是否能够确定的不同，分为确定和非确定两类。确定指对于一组确定的输入值，函数始终返回相同的结果，如SQUARE(3)的值始终为9，则SQUARE()就是严格确定函数。非确定指针对一组特定的输入值，返回的结果可能会不同，如GETDATE()，会返回系统当前的时间，由于其返回值会根据执行时间的不同产生不同的值，因此是非确定的。

以下介绍几种常用的函数。

1. 聚合函数

聚合函数经常用于数据统计，如统计不同产品的销售量、统计最高成绩等。这类函数包括AVG()、MIN()、MAX()、SUM()、COUNT()、STDEV()、STDEVP()、VAR()、VARP()等，这些函数的使用方法在5.3.1节中已经介绍，此处不再重复介绍。

2. 日期和时间函数

日期和时间函数用于日期和时间的计算，在TSQL查询中经常需要使用到对当月销售数量、本周出勤率等与时间有关的统计应用。使用日期和时间函数可以处理日期和时间数据，生成需要的结果值。

在TSQL中，日期和时间函数主要包括DATEADD()、DATEDIFF()、DATENAME()、DATEPART()、DAY()、GETDATE()、GETUTCDATE()、MONTH()、YEAR()等，这些函数的含义如表54所示。


表54日期时间函数





函 数 名 称语法/含义


DATEADD()
DATEADD(datepart,number,date)
在指定的日期时间值上加上一个时间间隔值，产生一个新值，如Dateadd(dd,1,'20191012')表示在“20191012”时间值上加上1天(dd代表天数)，形成的新日期时间值为“20191013”


DATEDIFF
DATEDIFF(datepart,startdate,enddate)
用于返回两个日期时间值的边界数。如DATEDIFF(year, '20091231' , '20190102')，表示比较两日期的年份(year)的差值

DATENAME
DATENAME(datepart,datetoinspect)
用于返回指定日期时间值中指定部分的值，如DATENAME(month, '20180125')，要求返回月份(MONTH)的值

DATEPART()
DATEPART(datepart,datetoinspect)
用于返回指定日期值指定部分的值。如DATEPART(DAY , '201801 25')，要求返回天(DAY)的部分


DAY()
DAY(date)
用于返回指定日期中的“天”的值，如DAY('20100207')

GETDATE()
GETDATE()
返回系统当前的日期和时间值

GETUTCDATE()
GETUTCDATE()
与GETDATE()一样都能返回系统当前的日期时间，但值是UTC的日期时间值

MONTH()
MONTH(date)
返回指定日期时间值的“月份”的值

YEAR()
YEAR(date)
返回指定日期时间值的“年份”的值

EOMONTH()
EOMONTH(start_date,month_to_add)
返回值为datetime2(7)，是针对指定开始日期start_date的月份的最后一天。month_to_add是可选参数，如果提供这个参数值，函数会将此值添加到开始日期start_date的月数上，然后再返回结果月份的最后一天

DateFromParts()
DateFromParts(year,month,day)
基于给定的year,month,day返回Date值

DateTime2FromParts()
DateTime2FromParts (year,month,day,hour,minute,seconds,fractions,precision)
基于给定的year,month,day,hour,minute,seconds,fractions,precision返回DateTime2值

DateTimeFromParts()
DateTimeFromParts (year,month,day,hour,minute,seconds,fractions,precision)
基于给定的year,month,day,hour,minute,seconds,fractions,precision返回DateTime值

DateTimeOffSetFrom
Parts()


DateTimeOffSetFromParts (year,month,day,hour,minute,seconds,fractions, hour_offset,minute_offset, seconds_offset,fractions_offset, precision)

基于给定的year,month,day,hour,minute,seconds,fractions, hour_offset, minute_offset, seconds_offset,fractions_offset,precision返回DateTime Offset值


SmallDateTimeFrom
Parts()
SmallDateTimeFromParts (year,month,day,hour,minute)
基于给定的year,month,day,hour,minute返回DateTime值

TimeFromParts()
TimeFromParts (year,month,day,hour,minute,seconds,fractions,precision)
基于给定的year,month,day,hour,minute,seconds,fractions,precision返回Time值


表54中，参数datepart的取值与含义如表55所示。


表55datepart的取值及含义





日期部分缩写说明日期部分缩写说明


yearyy，yyyy年weekwk，ww第几星期
quarterqq，q季度weekdaydw周几
monthmm，m月hourhh小时
dayofyeardy，y一年中的第几天minuteMi，n分
daydd,d日期secondss,s秒
millisecondMs毫秒


例如，以下代码可以从“订单表”中返回一个月内的订单，如果今天是“20191207”，则订单的时间范围为“20191207”到“20200108”，执行结果如图546所示。

SELECT * FROM dbo.订单表

WHERE ordertime<DATEADD(m,1,ATEADD(d,1,getdate())) and ordertime>=GETDATE()



图546使用日期函数查询1月的内订单


如果需要查询当月订单，可以使用以下代码，即通过判断年份与月份是否同时相等来获取当月的订单。

SELECT * FROM dbo.订单表

WHERE datePART(yy,ordertime)= datePART(yy,getdate()) and datePART(m,ordertime)= datePART(m,getdate())

3. 数学函数

TSQL中提供了23种数学函数，这些数学函数可以对SQL Server 2022中的各种数值型数据进行运算。这些数学函数包括ABS()、ACOS()、ASIN()、ATAN()、ATN2()、CEILING()、COS()、COT()、DEGREES()、EXP()、FLOOR()、LOG()、LOG10()、PI()、POWER()、RADIANS()、RAND()、ROUND()、SIGN()、SIN()、SQRT()、SQUARE()、TAN()等，这些数学函数的功能与C#、C等编程语言的函数功能相同，请参见相关资料。

4. 字符串函数

字符串函数是对字符串进行各种操作的函数。TSQL提供的字符串函数包括ASCII()、CHAR()、CHARINDEX()、CONCAT（）、DIFFERENCE()、FORMAT（）、LEFT()、LEN()、LOWER()、LTRIM()、NCHAR()、PATINDEX()、QUOTENAME()、REPLACE()、REPLCATE()、REVERSE()、RIGHT()、RTRIM()、SOUNDEX()、SPACE()、STR()、STUFF()、SUBSTRING()、UNICODE()、UPPER()等。

这些函数的具体含义见表56。


表56字符串函数的含义





函数含义


ASCII()将单个字符转换成对应的ASCII码
CHAR()将一个数字值转换成字符
CHARINDEX()返回字符串在另一个字符串中的起始位置
CONCAT()将两个或多个字符串组合为单个字符串
DIFFERENCE()返回一个表示两个字符表达式的SOUNDEX值差异的整数
FORMAT()返回指定格式的值
LEFT()返回字符串中从左边开始到指定长度的字符
LEN()返回字符串的长度
LOWER()返回字符串的小写形式
LTRIM()返回去除字符串左边空格之后的字符串值
NCHAR()返回指定整数代码的UNICODE字符
PATINDEX()返回指定表达式中某模式第一次出现的起始位置； 如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零
QUOTENAME()返回带有分隔符的UNICODE字符
REPLACE()将表达式中的字符串转换成其他字符串或空格
REPLCATE()返回多次复制后的字符串表达式
REVERSE()返回反转后的字符串
RIGHT()返回字符串中从右边开始到指定长度的字符
RTRIM()返回去除字符串右边空格之后的字符串值
SOUNDEX()返回一个由4个字符组成的代码，用于评估两个字符串的相似性
SPACE()返回由指定数量空格组成的字符串
STR()返回由数字数据转换来的字符数据
STUFF()将字符串插入另一个字符串
SUBSTRING()返回整个字符串中指定的部分字符串
UNICODE()返回输入表达式的第一个字符的整数值
UPPER()返回字符串的大写形式


例如以下代码使用了部分字符串函数，执行的结果如图547所示。

SELECT ASCII('SQL Server') as 'ASCII',CHAR(56) AS 'CHAR(56)',

LEFT('SQL Server',5) as 'LEFT5',LOWER('SQL Server') as 'LOWER',

UPPER('SQL Server') AS 'UPPER',RIGHT('SQL Server',5) as 'RIGHT5',

SUBSTRING('SQL Server',3,LEN('SQL Server')) as 'SUMSTRING'



图547字符串函数执行的结果


5. 窗口函数

窗口函数也被称为OLAP(OnLine Analytical Processing)函数，即数据在线分析处理函数。SQL Server提供的窗口函数由两部分组成： 一部分是聚合函数(如SUM、AVG、COUNT、MAX、MIN)； 另一部分是RANK、DENSE_RANK、ROW_NUMBER等专用窗口函数。应用窗口函数结合OVER、PARTITION BY和ORDER BY子句，可以实现对数据进行聚合统计、分组排名等多种实时在线分析。

窗口函数的基本语法如下：

<窗口函数> OVER (［partition_by_clause］ order_by_clause )  

例如以下代码，使专用窗口函数RANK、DENSE_RANK、ROW_NUMBER统计了各产品销售量的排名，执行结果如图548所示。

SELECT p.ProductID, ProductName, o.sumtotal as 销售金额,o.［sales］,产品类别=

CASE

WHEN CategoryID=1 THEN '台式计算机'

WHEN CategoryID=2 THEN '笔记本电脑'

ELSE '其他'

END,

rank() over (order by o.sales desc) as rank,

dense_rank() over (order by o.sales desc) as dense_rank,

row_number() over (order by o.sales desc) as row_num

FROM 产品数据表 p inner join ［dbo］.［订单细节表］ o on p.ProductID=o.productid order by rank



图548使用窗口函数统计销量排名


从图548中可以看出，RANK、DENSE_RANK和ROW_NUMBER三者在生成排序值方面的区别。

■RANK函数在排序时，如果有多行排序值相同，则这些行的排名值相同，但后续行排名值会跳过这些行数再排名，如图548中rank排名值有两行值为2，则后续会从4开始，而不是从3开始。也即RANK函数排名时，排名值并不总是连续的。

■DENSE_RANK函数在排序时，也会出现多行排名值相同的情况，但不会跳过。即如图548中所示，dense_rank列有两行值为2，后续依旧会从3开始。

■ROW_NUMBER函数的作用是给每一行生成一个连续的排序值。

窗口函数可以结合PARTITION BY生成分区的排名，如上述实例中，可以根据需要实现按产品类别分区，生成每一区的销量排名。如以下代码实现上述要求，执行结果如图549所示。

SELECT p.ProductID, ProductName, o.sumtotal as 销售金额,o.［sales］,产品类别=

CASE

WHEN CategoryID=1 THEN '台式计算机'

WHEN CategoryID=2 THEN '笔记本电脑'

ELSE '其他'

END,

rank () over (partition by CategoryID order by o.sales desc) as rank,

dense_rank () over (partition by CategoryID order by o.sales desc) as dense_ranking,row_number () over (partition by CategoryID order by o.sales desc) as row_num FROM产品数据表 p inner join ［dbo］.［订单细节表］ o on p.ProductID=o.productid order by CategoryID,rank



图549使用PARTITION BY分区的窗口函数


从执行结果中可见，使用PARTITION BY的窗口函数，先对数据按CategoryID列进行分组，然后再按ORDER By指定的列排序，再生成各组对应的排序值。

6. 其他常用函数

1) ISDATE()

ISDATE()函数是一个对列、变量或常量进行判断，判别该列、变量或常量是否是一个日期时间值的函数。如果是日期时间，则返回1，否则返回0。这个函数能否正确运算与操作系统的区域和日期格式设置有关。

例如：

ISDATE('20010-01-01')

2) ISNULL()

ISNULL()函数用于判断列或变量的值是否为NULL，如果为NULL，可以用指定的值替换NULL值。该函数的语法如下：

ISNULL(value_to_test,new_value)

例如以下代码使用ISNULL函数对变量进行判断，并使用ISNULL字符串去替换NULL值。

DECLARE @p varchar(20)

select ISNULL(@p,'ISNULL')

由于变量刚定义，未赋值前其值为NULL，所以该代码的输出结果为ISNULL。

3) ISNUMERIC()

ISNUMERIC()函数用于对变量、列或常量进行判断，确定是否是数字值，如果是数字值则返回1，否则返回0。货币符号也会被判定为数字值。

例如以下代码中，@p被赋值为1.2是数字值，因此判定结果为1。

DECLARE @p varchar(20)

SET @p=1.2

select ISNUMERIC(@p)

7. 用户自定义函数

在SQL Server 2022中，除了可以使用由系统提供的上述大量的系统函数之外，用户根据需要还可以构建用户自定义函数(UserDefined Functions，UDF)。用户自定义函数是一组有序的TSQL语句，这些语句被预先优化和编译，可以作为一个整体进行调用。使用用户自定义函数可以提高代码的可重用性，也有助于简化业务系统的复杂程度。

在SQL Server 2022中，根据用户定义函数返回值的不同，可以分成为两大类： 标量值函数和表值函数。用户可以将自己定义的函数分别归类到上述类别中。

1) 标量值函数

标量值函数指执行后返回结果是某种具体数据类型的用户自定义函数，这些数据可以是除BLOB（二进制类型的大对象）、游标(Cursor)和时间戳(Timestamp)之外的任何有效的SQL Server数据类型。

以下代码创建了用于判断产品是否可销售的用户自定义函数，函数的名称为IsCanSale，返回的数据类型为BIT。函数输入两个整型参数，@ProductID用于代表产品编号，@Qty代表产品销售数量，通过判断“产品数据表”中指定的产品是否有足够库存量来确定返回值，返回值为1，表示产品可销售； 为0，表示产品数量不足或产品不存在，不能销售。


USE ［NetSale］

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION ［dbo］.［IsCanSale］(@ProductID int,@Qty int)

RETURNS BIT

AS 

BEGIN

DECLARE @iReturn BIT

SET @iReturn=0

IF(EXISTS(SELECT * FROM ［dbo］.［产品数据表］ WHERE ［ProductID］=@ProductID AND ［Instock］>=@Qty))

SET @iReturn=1

ELSE

SET @iReturn=0

Return @iReturn

END

此函数创建后，可以在当前数据库中作为一个对象，在查询、视图、存储过程等多处调用，甚至可以被其他函数调用，如以下代码演示了函数在查询中调用的方法。

SELECT * FROM ［dbo］.［产品数据表］ WHERE ［dbo］.［IsCanSale］(ProductID,20)=1

执行结果如图550所示。



图550调用标量值用户自定义函数


2) 表值函数

与标量值函数相对应，表值函数指返回值的数据类型为表的用户自定义函数。由于返回结果为表，这使表值函数在需要提取中间数据的场合有很重要的适用性。如在某些场合，需要先了解“产品数据表”中库存不足的产品数据，然后再由这些数据生成对应的采购数据，在这个业务应用中，库存不足的产品数据是中间表，可以由表值函数来实现。


以下代码创建了一个用于获取“产品数据表”库存小于某一指定值的产品数据的用户自定义函数。函数的名称为Get_LowStockProduct，返回值的数据类型为TABLE，使用的参数@Qty用于指定库存的最低数量。

USE ［NetSale］

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION ［dbo］.［Get_LowStockProduct］(@Qty int)

RETURNS TABLE

AS 

RETURN (SELECT * FROM ［dbo］.［产品数据表］ WHERE  ［Instock］<=@Qty)

同样，表值函数也可以在查询、视图、存储过程和函数等多处重复调用。以下代码演示了在查询中调用的方法。

DECLARE @Qty int

set @Qty=20

SELECT * FROM ［dbo］.［Get_LowStockProduct］(@Qty)

执行结果如图551所示。



图551调用表值函数





5.5使用通用
表表达式

5.5使用通用表表达式

通常，使用SELECT可以从表中获取需要的数据，形成一个数据集。在有些场合，需要先生成这个数据集然后在后续的代码中使用，SQL Server可以使用临时表等去生成这个数据集并临时保存，但会降低效率。采用通用表表达式(Common Table Expression，CTE)在需要的时候生成，用完丢弃，也是非常好的一种解决方案。

通用表表达式，一方面可以让代码更加简洁，减少重复代码； 另一方面也可以实现递归调用，实现层次化的数据查询。

5.5.1定义通用表表达式

CTE的定义需要使用WITH关键词，其基本定义语法如下：

WITH expression_name ［(column_name ［,…n］ )］

AS( 

cte_query_definition 

)

各参数含义如下： 

■expression_name，通用表表达式的名称。

■column_name［,…n］，通用表表达式可以返回的列列表，如果返回的是cte_query_definition中定义的所有列，那么此项参数可以省略。

■cte_query_definition，通用表表达式定义的查询语句。

如以下代码定义了一个名称为myCTE的通用表表达式，定义的查询语句为从产品数据表和订单细节表中获取某一产品的销量和销售金额。此处忽略了通用表表达式返回的列列表，即表示返回的是查询语句中的所有列。

WITH myCTE

AS

(

SELECT p.ProductID,ProductName,o.sumtotal as 销售金额,o.sales FROM 产品数据表 p inner join ［dbo］.［订单细节表］ o on p.ProductID=o.productid 

)

当通用表表达式返回的列与定义的不一致时，如以下代码，在列表中Amount和sale_Count列与定义的“销售金额”和sales列是不相同的，需要明确定义，但会顺序对应。

WITH myCTE(ProductID,ProductName,Amount,sale_Count)

AS

(SELECT p.ProductID, ProductName,o.sumtotal as 销售金额,o.sales

FROM 产品数据表 p inner join ［dbo］.［订单细节表］ o on p.ProductID=o.productid)

如果有多个通用表表达式需要定义，可以在同一个WITH关键词下定义，如以下代码定义了两个通用表表达myCTE和myCTE1，其中在myCTE1的查询语句中使用了myCTE。


WITH myCTE(ProductID, ProductName, 销售金额,sales,orderID)

AS

(SELECT p.ProductID, ProductName, od.sumtotal as 销售金额,od.sales,od.orderid

FROM 产品数据表 p inner join ［dbo］.［订单细节表］ od on p.ProductID=od.productid 

),myCTE1

AS(Select m.ProductID,m.ProductName,m.sales,m.销售金额,c.CustomerName from 订单表 o inner join myCTE m on o.orderID=m.orderID inner join  客户数据表 c on o.customerID=c.CustomerID )

在定义通用表表达式时，需要注意的是，如果定义语句在一个代码体中并非处在最前面，即在WITH关键词的前面还有其他语句，则需要在WITH之前添加“;”，表示之前的语句在此WITH前已结束。因为WITH关键词并不仅仅只用于定义CTE，还可用于定义其他选项参数，因此必须做好区分。

5.5.2使用通用表表达式

通用表表达式定义完成后，就可以在其他语句中进行使用，但是需要注意的是，只能在通用表表达式的后续第一条语句中使用。如以下代码，在定义通用表表达式之后，直接在后续的SELECT语句中得到了使用。

WITH myCTE

AS(

SELECT p.ProductID, ProductName, o.sumtotal as 销售金额,o.sales FROM 产品数据表 p inner join ［dbo］.［订单细节表］ o on p.ProductID=o.productid)

select * from  myCTE

执行结果如图552所示。



图552使用通用表表达式


5.6本 章 小 结

TSQL是SQL Server数据管理的基础，几乎所有SQL Server的管理操作都可以通过TSQL完成。TSQL是基于SQL国际标准，应用于SQL Server中的数据操纵语言； 与SQL国际标准相比，增加了很多独具特色的语言，使SQL Server具备更强的功能和特色。

本章介绍了TSQL，包括TSQL的数据操纵语言、数据查询语言，并对TSQL的附加元素，如变量、运算符、控制流语句、函数和通用表达式等进行了深入介绍。掌握TSQL是进一步深入SQL Server 2022应用和管理的重要前提，也有益于深入了解SQL Server 2022的管理机制。

习题与思考

1. 请简述TSQL的发展过程及与ANSI SQL的区别。

2. TSQL语言的分类有哪几种？分别包含哪些语句？

3. 如何使用INSERT 语句在数据表中添加数据？如何使用UPDATE语句修改指定条件数据行的列的值？如何使用DELETE语句删除表中指定条件的数据？

4. 如何使用SELECT语句检索表中指定条件的数据？如何构造模糊匹配条件？如何使用通配符构建检索条件？

5. 如何联接多表检索数据？SQL Server中多表联接检索的种类有哪些？

6. 如何对检索结果数据进行排序和分组？

7. 如何去除检索结果中的重复数据？

8. 如何使用UNION、INTERSECT和EXCEPT运算符实现组合查询？

9. SQL Server 中标识符命名有何要求？

10. 如何使用Select into批量加载数据到数据表中？

11. 如何使用IF…ELSE、WHILE、CASE等语句构造流程控制程序？

12. 如何使用OFFSET和 FETCH 参数实现分页？

13. 通用表表达式如何定义？请说明使用的方法。