Chapter3T-SQL语言编程在SQLServer数据库中，Transact-SQL语言由以下及部分组成数据定义语言（DDL）。DDL用于对数据库以及数据库中的各种对象进行创建、删除、修改等操作。包括CREATE、ALTER、DROP等语句。■数据操纵语言（DML）。DML用于操纵数据库中的各种对象，检索和修改数据。包括SELECT、INSERT、UPDATE、DELETE等语句。-数据控制语言（DCL）。DCL用于安全管理，确定哪些用户可以查看或修改数据库中的数据。包括GRANT、REVOKE、DENY等语句。■T-SQL增加的语言元素。这部分不是ANSISQL所包含的内容，而是Microsoft为了用户编程的方便而增加的语言元素。这些语言元素包括变量、运算符、表达式、内置函数、流程控制语句等，这些T-SQL语句都可以在查询分析器中交互执行。本章将介绍这部分增加的语言元素。3.1T-SQL程序结构3.1.1批批是一组SQL语句的集合，一个批以结束符GO而终结。批中的所有语句被一次性提交，SQLServer将这些语句编译为一个执行单元。只要其中任意一个SQL语句存在语法错误，SQLServer将取消整个批内所有语句的执行。使用批的基本规则：■CREATE语句应单独构成一个批，不能与其它SQL语句组合使用■使用ALTERTABLE语句修改表结构后，不能在同一个批中使用新定义的列■批命令GO和SQL语句不能在同一行。3.1.2事务事务是一个完整的工作单元，一个事务内的所有语句作为整体执行，要么全部执行，要么全部不执行。遇到错误时，可以回滚事务，取消事务内所做的所有改变，从而保证数据库中数据的一致性和可恢复性。一个事务一般以BEGINTRANSACTION开始，以COMMIT（提交）或ROLLBACK（回滚）结束。事务与批的区别：-批是一组整体编译的SQL语句，事务是一组作为单个逻辑工作单元执行的SQL语句；■批语句的组合发生在编译时刻，事务中语句的组合发生在执行时刻。3.1.3程序结构一个T-SQL程序包含若干个事务，一个事务又包含若干个以GO结束的批处理，一个批处理包含若干条T-SQL语句
Chapter 3 T-SQL 语言编程 在 SQL Server 数据库中，Transact-SQL 语言由以下及部分组成： ◼ 数据定义语言（DDL）。DDL 用于对数据库以及数据库中的各种对象进行创建、删除、 修改等操作。包括 CREATE、ALTER、DROP 等语句。 ◼ 数据操纵语言（DML）。DML 用于操纵数据库中的各种对象，检索和修改数据。包括 SELECT、INSERT、UPDATE、DELETE 等语句。 ◼ 数据控制语言（DCL）。DCL 用于安全管理，确定哪些用户可以查看或修改数据库中的 数据。包括 GRANT、REVOKE、DENY 等语句。 ◼ T-SQL 增加的语言元素。这部分不是 ANSI SQL 所包含的内容，而是 Microsoft 为了用 户编程的方便而增加的语言元素。这些语言元素包括变量、运算符、表达式、内置函数、 流程控制语句等，这些 T-SQL 语句都可以在查询分析器中交互执行。本章将介绍这部 分增加的语言元素。 3.1 T-SQL 程序结构 批 批是一组 SQL 语句的集合，一个批以结束符 GO 而终结。批中的所有语句被一次性提 交，SQL Server 将这些语句编译为一个执行单元。只要其中任意一个 SQL 语句存在语法错 误，SQL Server 将取消整个批内所有语句的执行。 使用批的基本规则： ◼ CREATE 语句应单独构成一个批，不能与其它 SQL 语句组合使用； ◼ 使用 ALTER TABLE 语句修改表结构后，不能在同一个批中使用新定义的列； ◼ 批命令 GO 和 SQL 语句不能在同一行。 事务 事务是一个完整的工作单元，一个事务内的所有语句作为整体执行，要么全部执行，要 么全部不执行。遇到错误时，可以回滚事务，取消事务内所做的所有改变，从而保证数据库 中数据的一致性和可恢复性。一个事务一般以 BEGIN TRANSACTION 开始，以 COMMIT（提 交）或 ROLLBACK（回滚）结束。 事务与批的区别： ◼ 批是一组整体编译的 SQL 语句，事务是一组作为单个逻辑工作单元执行的 SQL 语 句； ◼ 批语句的组合发生在编译时刻，事务中语句的组合发生在执行时刻。 程序结构 一个 T-SQL 程序包含若干个事务，一个事务又包含若干个以 GO 结束的批处理，一个 批处理包含若干条 T-SQL 语句
【例3-1]在以下案例中，整个T-SQL程序包含一个事务，该事务中又包含两个批处理，第一个删除了081101学生的选课成绩记录，第二个查询成绩表CJB（能够查询到删除后的结果），但该事务最终以ROLLBACK回滚结束，并未真正实施删除，所以最后一个批处理的查询仍然能够查询到081101学生的选课成绩记录。BEGIN TRANDELETE FROM CJB WHERE CJB.学号=O811O1GOSELECT*FROM CJBGOROLLBACKSELECT *FROM CJBGO3.2常量与变量3.2.1常量常量是指在程序运行过程中值不变的量。根据常量值的不同类型，常量分为：际字符串常量：用单引号括起来，如CHINA"；图整型常量：二进制、十六进制和十进制；-实型常量：定点表示，如3.1415926；浮点表示，如101.5E5-日期时间常量：用单引号将表示日期时间的字符串括起来构成，如1998-04-21；+货币常量：以“S"作为前缀的一个整型或实型常量数据，如$12；■唯一标识常量：用于表示全局唯一标识符（GUID）值的字符串，可以使用字符串或十六进制字符串格式指定。3.2.2变量变量用于临时存放数据，变量中的数据随着程序的执行而变化。变量有名称及数据类型两个属性。变量名用于标识该变量，必须是一个合法的标识符，不能是T-SQL的保留字；数据类型确定了该变量存放值的格式及允许的运算。（1）变量的分类■全局变量：全局变量由系统提供且预先声明，通过在名称前加两个@来区别于局部变量。例如，@@ERROR返回执行的上一个T-SQL语句的错误号，@@VERSION用于查看当前SQLServer版本。■局部变量：局部变量用于保存单个数据值，首字母为@。（2）局部变量的定义在批处理或存储过程中用DECLARE语句声明局部变量，所有局部变量在声明后均初始化为NULL。DECLARE语法格式如下：DECLARE{@local_variabledata_type[=value]][,n]语法说明：
【例 3-1】在以下案例中，整个 T-SQL 程序包含一个事务，该事务中又包含两个批处理，第 一个删除了 081101 学生的选课成绩记录，第二个查询成绩表 CJB（能够查询到删除后的结 果），但该事务最终以 ROLLBACK 回滚结束，并未真正实施删除，所以最后一个批处理的查 询仍然能够查询到 081101 学生的选课成绩记录。 BEGIN TRAN DELETE FROM CJB WHERE CJB.学号='081101' GO SELECT * FROM CJB GO ROLLBACK SELECT * FROM CJB GO 3.2 常量与变量 常量 常量是指在程序运行过程中值不变的量。根据常量值的不同类型，常量分为： ◼ 字符串常量：用单引号括起来，如‘CHINA’； ◼ 整型常量：二进制、十六进制和十进制； ◼ 实型常量：定点表示，如 3.1415926；浮点表示，如 101.5E5； ◼ 日期时间常量：用单引号将表示日期时间的字符串括起来构成，如‘1998-04-21’； ◼ 货币常量：以“$”作为前缀的一个整型或实型常量数据，如$12； ◼ 唯一标识常量：用于表示全局唯一标识符（GUID）值的字符串，可以使用字符串或十六 进制字符串格式指定。 变量 变量用于临时存放数据，变量中的数据随着程序的执行而变化。变量有名称及数据类型 两个属性。变量名用于标识该变量，必须是一个合法的标识符，不能是 T-SQL 的保留字； 数据类型确定了该变量存放值的格式及允许的运算。 （1）变量的分类 ◼ 全局变量：全局变量由系统提供且预先声明，通过在名称前加两个@来区别于局部变量。 例如，@@ERROR 返回执行的上一个 T-SQL 语句的错误号，@@VERSION 用于查看当 前 SQL Server 版本。 ◼ 局部变量：局部变量用于保存单个数据值，首字母为@。 （2）局部变量的定义 在批处理或存储过程中用 DECLARE 语句声明局部变量，所有局部变量在声明后均初始 化为 NULL。DECLARE 语法格式如下： DECLARE { @local_variable data_type [ = value ] } [ ,.n] 语法说明：
localvariable：局部变量名，用@标识。data_type：数据类型，定义局部变量的数据类型。=value：为变量赋值，值可以使常量或表达式，但必须与变量声明类型匹配。n：表示可定义多个变量，各变量间用逗号隔开(3）局部变量的赋值声明局部变量后，可用SET或SELECT语句为其赋值。最大区别：一个SET语句只能为一个变量赋值，一个SELECT语句可以初始化多个局部变量。SET@local_variable=expressionSELECT(@localvariable=expression)["n]【例3-2】创建局部变量@var1、@var2并赋值，然后输出变量的值。DECLARE @varl char(10),@var2 char(20SET@varl-中国SET@var2=@var1+是一个伟大的国家SELECT@var2AS输出结果-PRINT @var2DECLARE @varlchar(10)@var2char(20SELEcT@var1=中国，@var2-@var1+是一个伟大的国家SELECT @var2 AS输出结果【例3-3】创建一个名为Sex的局部变量，并在SELECT语句中使用该局部变量查找表XSB中所有女同学的学号、姓名。DECLARE @sex bitSET @sex=0SELECTXSB.学号，XSB.姓名FROMXSBWHERE XSB.性别-@sex【例3-4】使用查询结果为变量赋值。DECLARE @name char(8)SET @name=（SELECT XSB.姓名 FROM XSB WHERE XSB.学号='081101)SELECT @name+‘是个好学生(4）表数据类型变量的定义与赋值DECLARE{@table_variable_name[AS]TABLE({<column_definition>|<table_constraint>][..]]【例3-5】声明一个表数据类型变量并向变量中插入数据，--申明表变量DECLARE @var tableAS TABLE(num char(6) NOT NULL PRIMARY KEY,name char(8) NOT NULL,Sex bit NULL)
◼ local_variable：局部变量名，用@标识。 ◼ data_type：数据类型，定义局部变量的数据类型。 ◼ =value：为变量赋值，值可以使常量或表达式，但必须与变量声明类型匹配。 ◼ n：表示可定义多个变量，各变量间用逗号隔开 （3）局部变量的赋值 声明局部变量后，可用 SET 或 SELECT 语句为其赋值。最大区别：一个 SET 语句只能为 一个变量赋值，一个 SELECT 语句可以初始化多个局部变量。 SET @local_variable = expression SELECT {@local_variable = expression} [ ,.n] 【例 3-2】创建局部变量@var1、@var2 并赋值，然后输出变量的值。 DECLARE @var1 char(10),@var2 char(20) SET @var1='中国' SET @var2=@var1+'是一个伟大的国家' SELECT @var2 AS '输出结果' -PRINT @var2 DECLARE @var1 char(10),@var2 char(20) SELECT @var1='中国',@var2=@var1+'是一个伟大的国家' SELECT @var2 AS '输出结果' 【例 3-3】创建一个名为 sex 的局部变量，并在 SELECT 语句中使用该局部变量查找表 XSB 中所有女同学的学号、姓名。 DECLARE @sex bit SET @sex=0 SELECT XSB.学号,XSB.姓名 FROM XSB WHERE XSB.性别=@sex 【例 3-4】使用查询结果为变量赋值。 DECLARE @name char(8) SET @name=(SELECT XSB.姓名 FROM XSB WHERE XSB.学号='081101') SELECT @name+'是个好学生' （4）表数据类型变量的定义与赋值 DECLARE { @table_variable_name [AS] TABLE ( { <column_definition> | <table_constraint> } [ ,. ] ) } 【例 3-5】声明一个表数据类型变量并向变量中插入数据。 -申明表变量 DECLARE @var_table AS TABLE( num char(6) NOT NULL PRIMARY KEY, name char(8) NOT NULL, sex bit NULL)
--向表变量插入数据INSERT INTO @var tableSELECT学号，姓名，性别FROMXSB-查看表变量SELECT * FROM @var table内置函数3.3在程序设计过程中，常常调用系统提供的函数。T-SQL编程语言提供3种系统内置函数5行集函数：返回值为对象的函数，该对象可在T-SQL语句中作为表引用；聚合函数：对一组值操作，返回单一的汇总值，之前SELECT语句中经常与GROUPBY子句一起使用MAX/MIN/AVG/COUNT就属于聚合函数；标量函数：输入参数的类型为基本类型，返回值也为基本类型。3.3.1数学函数数学函数对数值型输入参数值执行计算，并返回一个数值，这些函数都是标量函数。(1）ABS函数：返回数值的绝对值。ABS（numeric_expression）【例3-6】使用ABS函数返回绝对值SELECT ABS(-5),ABS(O),ABS(8)（2）ROUND函数：返回数值表达式并四舍五入为指定的长度或精度。ROUND（numeric_expression,length[.function])numeric_expression为要进行四舍五入的数值表达式，length为四舍五入的精度function是要执行的操作类型，默认值为O，进行四舍五入处理，否则进行截断处理。【例3-7】使用ROUND函数进行四舍五入SELECTROUND(123.4567,2),ROUND(123.4567,-2),ROUND(123.4567,0)（3）CEILING函数VS.FLOOR函数：返回大于或等于所给数值的最小整数（即向上取整）VS.返回小于或等于所给数值的最大整数（即向下取整）。CEILING(numeric_expression)FLOOR（numeric_expression）【例3-8】使用CEILING函数向上取整,FLOOR函数向下取整SELECT CEILING(123.45),FLOOR(123.45),CEILING(-123.45),FLOOR(-123.45),CEILING(0)（4）RAND函数：返回0~1之间的一个随机值。RAND（[seed])参数seed是指定种子值的整型表达式，返回值类型为float。如果未指定seed，则随机分配种子值。【例3-9]使用RAND函数生成一个0-50之间的随机值（每一次执行均会生成一个随机值）。SELECTRAND（)*50AS随机值
-向表变量插入数据 INSERT INTO @var_table SELECT 学号,姓名,性别 FROM XSB -查看表变量 SELECT * FROM @var_table 3.3 内置函数 在程序设计过程中，常常调用系统提供的函数。T-SQL 编程语言提供 3 种系统内置函数 ◼ 行集函数：返回值为对象的函数，该对象可在 T-SQL 语句中作为表引用； ◼ 聚合函数：对一组值操作，返回单一的汇总值，之前 SELECT 语句中经常与 GROUP BY 子句一起使用 MAX/MIN/AVG/COUNT 就属于聚合函数； ◼ 标量函数：输入参数的类型为基本类型，返回值也为基本类型。 数学函数 数学函数对数值型输入参数值执行计算，并返回一个数值，这些函数都是标量函数。 （1）ABS 函数：返回数值的绝对值。 ABS ( numeric_expression ) 【例 3-6】使用 ABS 函数返回绝对值 SELECT ABS(-5),ABS(0),ABS(8) （2）ROUND 函数：返回数值表达式并四舍五入为指定的长度或精度。 ROUND ( numeric_expression, length [,function] ) numeric_expression 为要进行四舍五入的数值表达式，length 为四舍五入的精度， function 是要执行的操作类型，默认值为 0，进行四舍五入处理，否则进行截断处理。 【例 3-7】使用 ROUND 函数进行四舍五入 SELECT ROUND(123.4567,2),ROUND(123.4567,-2),ROUND(123.4567,0) （3）CEILING 函数 VS. FLOOR 函数：返回大于或等于所给数值的最小整数（即向上取整） VS.返回小于或等于所给数值的最大整数（即向下取整）。 CEILING ( numeric_expression ) FLOOR ( numeric_expression ) 【例 3-8】使用 CEILING 函数向上取整,FLOOR 函数向下取整 SELECT CEILING(123.45),FLOOR(123.45), CEILING(-123.45),FLOOR(-123.45),CEILING(0) （4）RAND 函数：返回 0～1 之间的一个随机值。 RAND ( [ seed ] ) 参数 seed 是指定种子值的整型表达式，返回值类型为 float。如果未指定 seed，则随机 分配种子值。 【例3-9】使用RAND函数生成一个0-50之间的随机值（每一次执行均会生成一个随机值）。 SELECT RAND()*50 AS '随机值
3.3.2字符串函数字符串函数对字符串输入值执行操作，返回字符串或数值。这些函数都是标量函数。（1）UPPER函数VS.LOWER函数：将字符串中的大写（小写）字母转换成小写（大写）字母后返回整个字符串。UPPER（character_expression)LOWER（character_expression）【例3-10】使用UPPER/LOWER函数返回aBcDeFgSELECT UPPER("aBcDeFg')LOWER('aBcDeFg')（2）SUBSTRING函数：返回字符串中的一部分。SUBSTRING（character_expression，start，length）返回从字符串character_expression中从位置start开始截取长度为length的子串。【例3-11】返回字符串'abcdefg中从位置3开始的2个字符。SELECT SUBSTRING('abcdefg',3,2)【例3-12】使用SUBSTRING函数返回王姓同学信息SELECT*FROM XSBWHERESUBSTRING（姓名，1,1)=王（3）REPLACE函数：将字符串1中的字符串2部分用字符串3替换。REPLACE（"string_expression1'，string_expression2"，'string_expression3）【例3-13】使用REPLACE函数将专业中的信管替换为信息管理。BEGIN TRANUPDATE XSBSETXSB.专业-REPLACE（XSB，专业，信管，信息管理）SELECT*FROMXSBROLLBACK（4）STUFF函数：将字符串1中从start开始的length个字符用字符串2来替换。STUFF(string_expression1,start,length,string_expression2)【例3-14】使用STUFF函数将学号08改为09。BEGIN TRANUPDATE XSBSETXSB.学号=STUFF（XSB.学号1,2，09）SELECT *FROMXSBROLLBACK3.3.3时间日期函数时间日期函数对日期和时间输入值执行操作，并返回一个字符串、数字值或日期和时间值。这些函数都是标量函数。（1）GETDATE函数：返回当前系统日期和时间，返回值类型为datetime。GETDATEO
字符串函数 字符串函数对字符串输入值执行操作，返回字符串或数值。这些函数都是标量函数。 （1）UPPER 函数 VS. LOWER 函数：将字符串中的大写（小写）字母转换成小写（大写）字 母后返回整个字符串。 UPPER ( character_expression ) LOWER ( character_expression ) 【例 3-10】使用 UPPER/LOWER 函数返回'aBcDeFg' SELECT UPPER('aBcDeFg'),LOWER('aBcDeFg') （2）SUBSTRING 函数：返回字符串中的一部分。 SUBSTRING ( character_expression , start , length ) 返回从字符串 character_expression 中从位置 start 开始截取长度为 length 的子串。 【例 3-11】返回字符串'abcdefg'中从位置 3 开始的 2 个字符。 SELECT SUBSTRING('abcdefg',3,2) 【例 3-12】使用 SUBSTRING 函数返回王姓同学信息 SELECT * FROM XSB WHERE SUBSTRING(姓名,1,1)='王' （3）REPLACE 函数：将字符串 1 中的字符串 2 部分用字符串 3 替换。 REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' ) 【例 3-13】使用 REPLACE 函数将专业中的信管替换为信息管理。 BEGIN TRAN UPDATE XSB SET XSB.专业=REPLACE(XSB.专业,'信管','信息管理') SELECT * FROM XSB ROLLBACK （4）STUFF 函数：将字符串 1 中从 start 开始的 length 个字符用字符串 2 来替换。 STUFF ( string_expression1 , start, length, string_expression2 ) 【例 3-14】使用 STUFF 函数将学号 08 改为 09。 BEGIN TRAN UPDATE XSB SET XSB.学号=STUFF(XSB.学号,1,2,'09') SELECT * FROM XSB ROLLBACK 时间日期函数 时间日期函数对日期和时间输入值执行操作，并返回一个字符串、数字值或日期和时间 值。这些函数都是标量函数。 （1）GETDATE 函数：返回当前系统日期和时间，返回值类型为 datetime。 GETDATE ()