Transact-SQL语言

Transact SQL 语言语言 1 Transact SQL 语言概述语言概述 Transact SQL 语言是 Microsoft 公司在关系型数据库管理系统 SQL Server 中实现一种 计算机高级语言 是微软对 SQL 的扩展 Transact SQL 语言具有 SQL 的主要特点 同时增 加了变量 运算符 函数 流程控制和注释等语言元素 使得其功能更加强大 Transact SQL 语言对 SQL Server 十分重要 SQL Server 中使用图形界面能够完成的所有功能 都可 以利用 Transact SQL 语言来实现 使用 Transact SQL 语言操作时 与 SQL Server 通信的 所有应用程序都通过向服务器发送 Transact SQ 语句来进行 而与应用程序的界面无关 在 Transact SQL 语言中 标准的 SQL 语句畅通无阻 Transact SQL 也有类似于 SQL 语言的分类 不过做了许多扩充 2 Transact SQL 语言基础语言基础 2 1 常量与变量常量与变量 2 1 12 1 1 常量常量 常量 也称为文字值文字值或标量值标量值 是表示一个特定数据值的符号 常量的格式取决于它 所表示的值的数据类型 字符串常量字符串常量 字符串常量括在单引号内 如果将 QUOTED IDENTIFIER 选项设置成 OFF 则字符 串也可以使用双引号括起来 但 SQL Server 中 SET QUOTED IDENTIFIER ON 为默认设 置 我们建议使用单引号 如果单引号中的字符串包含一个嵌入的引号 可以使用两个单引号表示嵌入的单引号 对于嵌入在双引号中的字符串则没有必要这样做 以下是字符串的示例 Cincinnati O Brien Process X is 50 complete The level for job id d should be between d and d O Brien 空字符串用中间没有任何字符的两个单引号表示 注意 注意 大于 8000 字节的字符常量为 varchar max 类型的数据 Unicode 字符串字符串 Unicode 字符串的格式与普通字符串相似 但它前面有一个 N 标识符 N 代表 SQL 92 标准中的区域语言 N 前缀必须是大写字母 例如 Mich l 是字符串常量而 N Mich l 则是 Unicode 常量 对于字符数据 存储 Unicode 数据时每个字符使用 2 个 字节 而不是每个字符 1 个字节 注意 注意 大于 8000 字节的 Unicode 常量为 nvarchar max 类型的数据 二进制常量二进制常量 二进制常量具有前辍 0 x 并且是十六进制数字字符串 这些常量不使用引号括起 下面是二进制字符串的示例 0 xAE 0 x12Ef 0 x69048AEFDD010E 0 x empty binary string 例 declare a int set a 0 x10 print a 注意 注意 大于 8000 字节的二进制常量为 varbinary max 类型的数据 bit 常量常量 bit 常量使用数字 0 或 1 表示 并且不括在引号中 如果使用一个大于 1 的数字 则该数字将转换为 1 datetime 常量常量 datetime 常量使用特定格式的字符日期值来表示 并被单引号括起来 下面是 datetime 常量的示例 December 5 1985 5 December 1985 851205 其中的 0 不能省略 12 5 98 下面是时间常量的示例 14 30 24 04 24 PM Integer int 常量常量 integer 常量以没有用引号括起来并且不包含小数点的数字字符串来表示 integer 常 量必须全部为数字 它们不能包含小数 下面是 integer 常量的示例 1894 2 decimal 常量常量 decimal 常量由没有用引号括起来并且包含小数点的数字字符串来表示 下面是 decimal 常量的示例 1894 1204 2 0 float 和和 real 常量常量 float 和 real 常量使用科学记数法来表示 下面是 float 或 real 值的示例 101 5E5 0 5E 2 money 常量常量 money 常量以前缀为可选的小数点和可选的货币符号的数字字符串来表示 money 常 量不使用引号括起 下面是 money 常量的示例 12 542023 14 30 uniqueidentifier 常量常量 uniqueidentifier 常量是表示 GUID 的字符串 可以使用字符或二进制字符串格式指定 以下示例都指定相同的 GUID 6F9619FF 8B86 D011 B42D 00C04FC964FF 0 xff19966f868b11d0b42d00c04fc964ff 2 1 22 1 2 变量变量 变量对于一种语言来说是必不可少的组成部分 Transact SQL 语言允许使用两种变量 一种是用户自己定义的局部变量 Local Variable 另一种是系统提供的全局变量 Global Variable 1 局部变量局部变量 局部变量使用户自己定义的变量 它的作用范围近在程序内部 通常只能在一个批处 理中或存储过程中使用 用来存储从表中查询到的数据 或当作程序执行过程中暂存变量 使用 局部变量使用 DECLARE 语句定义 并且指定变量的数据类型 然后可以使用 SET 或 SELECT 语句为变量初始化 局部变量必须以 开头 而且必须先声明后使用 其 声明格式如下 DECLARE 变量名 变量类型 变量名 变量类型 其中变量类型可以是 SQL Server 支持的所有数据类型 也可以是用户自定义的数据类 型 注意 注意 第一次声明变量时 其值设置为 NULL 局部变量不能使用 变量 变量值 的格式进行初始化 必须使用 SELECT 或 SET 语句来设置其初始值 初始化格式如下 SELECT 局部变量 变量值 SET 局部变量 变量值 如果声明字符型的局部变量 一定要在变量类型中指明其最大长度 否则系统认为 其长度为 1 例 declare find nvarchar 10 set find 李 select from student where sName like find 若要声明多个局部变量 请在定义的第一个局部变量后使用一个逗号 然后指定下 一个局部变量名称和数据类型 例 DECLARE LastName nvarchar 30 FirstName nvarchar 20 StateProvince nchar 2 变量的作用域就是可以引用该变量的 Transact SQL 语句的范围 变量的作用域从 声明变量的地方开始到声明变量的批处理或存储过程的结尾 例如 下面的脚本存在语法 错误 因为在一个批处理中引用了在另一个批处理中声明的变量 USE AdventureWorks GO DECLARE MyVariable int SET MyVariable 1 Terminate the batch by using the GO keyword GO MyVariable has gone out of scope and no longer exists This SELECT statement generates a syntax error because it is no longer legal to reference MyVariable SELECT EmployeeID NationalIDNumber Title FROM HumanResources Employee WHERE EmployeeID MyVariable 变量具有局部作用域 只在定义它们的批处理或过程中可见 在下面的示例中 为 执行 sp executesql 创建的嵌套作用域不能访问在更高作用域中声明的变量 从而返回错 误 DECLARE MyVariable int SET MyVariable 1 EXECUTE sp executesql N SELECT MyVariable this produces an error 变量也可以通过 SELECT 列表中当前所引用的值赋值 例 USE AdventureWorks GO DECLARE EmpIDVariable int SELECT EmpIDVariable MAX EmployeeID 此时就赋该列的最大值 FROM HumanResources Employee SELECT EmpIDVariable GO 如果 SELECT 语句返回多行而且变量引用一个非标量表达式 则变量被设置为结 果集最后一行中表达式的返回值 例如 在此批处理中将 EmpIDVariable 设置为返回的 最后一行的 EmployeeID 值 此值为 1 USE AdventureWorks GO DECLARE EmpIDVariable int SELECT EmpIDVariable EmployeeID 此时返回多行 取最后一行的值 只是赋 值 FROM HumanResources Employee ORDER BY EmployeeID DESC SELECT EmpIDVariable 此句就是将满足的值查询出来 GO 2 全局变量全局变量 全局变量是 SQL Server 2000 系统内部使用的变量 起作用范围并不局限于某一程序 而是任何程序均可随时调用 全局变量通常存储一些 SQL Server 2000 的配置设置值和效能 统计数据 用户可在程序中用全局变量来测试系统的设定值或者 Transact SQL 命令执行后 的状态值 引用全局变量时 全局变量的名字前面要有两个标记符 不能定义与全 局变量同名的局部变量 从 SQL Server 7 0 开始 全局变量就以系统函数的形式使用 全 局变量的符号及其功能如下表所示 全局变量及其功能 全局变量功 能 CONNECTIONS自 SQL Server 2000 最近一次启动以来登录或试图登录的次数 CPU BUSY自 SQL Server 2000 最近一次启动以来 CPU Server 的工作时间 CURRSOR ROWS返回在本次连接最新打开的游标中的行数 DATEFIRST返回 SET DATEFIRST 参数的当前值 DBTS数据库的惟一时间标记值 ERROR系统生成的最后一个错误 若为 0 则成功 FETCH STATUS最近一条 FETCH 语句的标志 IDENTITY保存最近一次的插入身份值 IDLE自 CPU 服务器最近一次启动以来的累计空闲时间 IO BUSY服务器输入输出操作的累计时间 LANGID当前使用的语言的 ID LANGUAGE当前使用语言的名称 LOCK TIMEOUT返回当前锁的超时设置 MAX CONNECTIONS同时与 SQL Server 2000 相连的最大连接数量 MAX PRECISION十进制与数据类型的精度级别 NESTLEVEL当前调用存储过程的嵌套级 范围为 0 16 OPTIONS返回当前 SET 选项的信息 PACK RECEIVED所读的输入包数量 PACKET SENT所写的输出包数量 PACKET ERRORS读与写数据包的错误数 RPOCID当前存储过程的 ID REMSERVER返回远程数据库的名称 ROWCOUNT最近一次查询涉及的行数 SERVERNAME本地服务器名称 SERVICENAME当前运行的服务器名称 SPID当前进程的 ID TEXTSIZE当前最大的文本或图像数据大小 TIMETICKS每一个独立的计算机报时信号的间隔 ms 数 报时信号为31 25ms 或1 32s TOTAL ERRORS读写过程中的错误数量 TOTAL READ读磁盘次数 不是高速缓存 TOTAL WRITE写磁盘次数 TRANCOUNT当前用户的活动事务处理总数 VERSION当前 SQL Server 的版本号 例 delete from student where sScore is null select ROWCOUNT 可见 在 T SQL 中 select 列 是显示列 例 select IDENTITY 2 2 运算符运算符 运算符是一种符号 用来指定要在一个或多个表达式中执行的操作 2 2 12 2 1 算术运算符算术运算符 加 减 乘 除 求余 2 2 22 2 2 赋值运算符赋值运算符 等号 是唯一的 Transact SQL 赋值运算符 只要是 就表示赋值语句 例 DECLARE MyCounter INT SET MyCounter 1 2 2 32 2 3 位运算符位运算符 GO SELECT Name SUBSTRING This is a long string 1 5 AS SampleText ProductNumber ListPrice 1 5 AS NewPrice FROM Production Product 3 语句 语句 3 1 注释语句注释语句 注释是程序代码中不执行的文本字符串 也称为备注 注释可用于对代码进行说明或 暂时禁用正在进行诊断的部分 Transact SQL 语句和批 使用注释对代码进行说明 便于 将来对程序代码进行维护 注释通常用于记录程序名 作者姓名和主要代码更改的日期 注释可用于描述复杂的计算或解释编程方法 SQL Server 支持两种类型的注释字符 双连字符 这些注释字符可与要执行的代码处在同一行 也可另起一行 从 双连字符开始到行尾的内容均为注释 对于多行注释 必须在每个注释行的前面使用双连 字符 正斜杠 星号字符对 这些注释字符可与要执行的代码处在同一行 也可另 起一行 甚至可以在可执行代码内部 开始注释对 与结束注释对 之间的所有内 容均视为注释 对于多行注释 必须使用开始注释字符对 来开始注释 并使用结束注 释字符对 来结束注释 例 USE AdventureWorks GO Single line comment SELECT EmployeeID Title FROM HumanResources Employee GO First line of a multiple line comment Second line of a multiple line comment SELECT Name ProductNumber Color FROM Production Product GO Using a comment in a Transact SQL statement during diagnosis SELECT ContactID FirstName LastName 内部部分注释 FROM Person Contact Using a comment after the code on a line USE AdventureWorks GO UPDATE Production Product SET ListPrice ListPrice 9 Reduce price to build market share GO 3 2 批处理批处理 GO Go 用于向 SQL Server 实用工具发出一批 Transact SQL 语句结束的信号 语法 Go count 其中 count 为一个正整数 表示 GO 之前的批处理将执行指定的次数 注 注 GO 不是 Transact SQL 语句 它是可由 sqlcmd 和 osql 实用工具以及 SQL Server Management Studio 代码编辑器识别的命令 GO 命令和 Transact SQL 语句不能在同一行中 但在 GO 命令行中可包含注释 用户必须遵照使用批处理的规则 例如 在批处理中的第一条语句后执行任何存储 过程必须包含 EXECUTE 关键字 局部 用户定义 变量的作用域限制在一个批处理中 不可在 GO 命令后引用 例 USE AdventureWorks GO DECLARE MyMsg VARCHAR 50 SELECT MyMsg Hello World 或这用set 赋值 GO MyMsg is not valid after this GO ends the batch Yields an error because MyMsg not declared in this batch PRINT MyMsg GO SELECT VERSION Yields an error Must be EXEC sp who if not first statement in batch sp who GO 3 3 语句块语句块 BEGIN END BEGIN END 包括一系列的 Transact SQL 语句 从而可以执行一组 Transact SQL 语句 BEGIN 和 END 是控制流语言的关键字 例 USE AdventureWorks GO BEGIN TRANSACTION GO IF TRANCOUNT 0 BEGIN SELECT from Person Contact WHERE LastName ADAMS ROLLBACK TRANSACTION PRINT N Rolling back the transaction two times would cause an error 也就是说 如果IF下面没有用到语句块Bigin end 则IF ELSE 条件下只会执行最后一天语句 print END ROLLBACK TRANSACTION PRINT N Rolled back the transaction GO 3 4 条件语句条件语句 IF ELSE 指定 Transact SQL 语句的执行条件 语法语法 IF Boolean expression sql statement statement block ELSE sql statement statement block 参数参数 Boolean expression 返回 TRUE 或 FALSE 的表达式 如果布尔表达式中含有 SELECT 语句 则必须用 括号将 SELECT 语句括起来 sql statement statement block 任何 Transact SQL 语句或用语句块定义的语句分组 除非使用语句块 否则 IF 或 ELSE 条件只能执行其后的一条 Transact SQL 语句 注意 T SQL 中 if else 是没有 end 的 在 if 条件中有 select 语句则必须加括号 若要定义语句块 必须使用控制流关键字 BEGIN 和 END 例 declare a int set a 10 if a 0 print a为正数 if执行一行 不加块 else print a 为负数 我们经常利用 if 语句和 exists 或 not exists 关键字来判断 Select 查询结果是否有记录 Exists 是用来判断是否存在返回行的 用 In 的话 只能是同一列 例 判断 002 号课程是否有人选修 用 exists 或 not exists 来判断数据行数 if exists select from SelectRecord where rcID 002 print 002号课程已有人选 else print 002 号课程还没有人选 3 5 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 例 USE AdventureWorks GO SELECT ProductNumber Category CASE ProductLine WHEN R THEN Road WHEN M THEN Mountain WHEN T THEN Touring WHEN S THEN Other sale items ELSE Not for sale END Name FROM Production Product ORDER BY ProductNumber GO 例 USE AdventureWorks GO SELECT ProductNumber Name Price Range CASE WHEN ListPrice 0 THEN Mfg item not for resale WHEN ListPrice 50 and ListPrice 250 and ListPrice 1000 THEN Under 1000 ELSE Over 1000 END FROM Production Product ORDER BY ProductNumber GO 3 6 循环语句循环语句 While While 用于设置重复执行 SQL 语句或语句块的条件 只要指定的条件为真 就重复 执行语句 可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语 句的执行 语法语法 WHILE Boolean expression sql statement statement block BREAK sql statement statement block CONTINUE sql statement statement block 参数参数 Boolean expression 返回 TRUE 或 FALSE 的表达式 如果布尔表达式中含有 SELECT 语句 则必须用 括号将 SELECT 语句括起来 sql statement statement block Transact SQL 语句或用语句块定义的语句分组 若要定义语句块 请使用控制流关键 字 BEGIN 和 END BREAK 导致从最内层的 WHILE 循环中退出 将执行出现在 END 关键字 循环结束的标记 后面的任何语句 CONTINUE 使 WHILE 循环重新开始执行 忽略 CONTINUE 关键字后面的任何语句 例例 在以下示例中 如果产品的平均标价小于 300 则 WHILE 循环将价格乘 2 然后选择最高价格 如果最高价格小于或等于 500 则 WHILE 循环重新开始 并再次 将价格乘 2 该循环不断地将价格乘 2 直到最高价格超过 500 然后退出 WHILE 循 环 并打印一条消息 USE AdventureWorks GO WHILE SELECT AVG ListPrice FROM Production Product 500 BREAK ELSE CONTINUE END PRINT Too much for the market to bear 例 例 Create the table CREATE TABLE TestTable cola int colb char 3 GO SET NOCOUNT ON GO Declare the variable to be used DECLARE MyCounter int Initialize the variable SET MyCounter 0 Test the variable to see if the loop is finished WHILE MyCounter 26 BEGIN Insert a row into the table INSERT INTO TestTable VALUES Use the variable to provide the integer value for cola Also use it to generate a unique letter for each row Use the ASCII function to get the integer value of a Add MyCounter Use CHAR to convert the sum back to the character MyCounter characters after a MyCounter CHAR MyCounter ASCII a Increment the variable to count this iteration of the loop SET MyCounter MyCounter 1 END GO SET NOCOUNT OFF GO View the data SELECT cola colb FROM TestTable GO DROP TABLE TestTable GO 3 7 其它语句其它语句 3 7 13 7 1 PrintPrint 语句语句 Print 语句用于向客户端返回用户定义消息 使用 PRINT 可以帮助我们排除 Transact SQL 代码中的故障 检查数据值或生成报告 例 Print ABCDEFG 3 7 23 7 2 WaitforWaitfor 延迟语句延迟语句 在达到指定时间或时间间隔之前 或者指定语句至少修改或返回一行之前 阻止执行 批处理 存储过程或事务 例 select from student go waitfor Time 19 36 30 select from Student 例 select from student go waitfor delay 00 00 03 select from Student 3 7 33 7 3 Try CatchTry Catch 异常处理语句异常处理语句 例 BEGIN TRY insert into Student values 1001 王小二 d 1990 1 1 500 END TRY BEGIN CATCH print 你插入的重复的学号 END CATCH GO 4 游标 游标 关系数据库中的操作会对整个行集起作用 由 SELECT 语句返回的行集包括满足该 语句的 WHERE 子句中条件的所有行 这种由语句返回的完整行集称为结果集 应用程 序 特别是交互式联机应用程序 并不总能将整个结果集作为一个单元来有效地处理 这 些应用程序需要一种机制以便每次处理一行或一部分行 游标就是提供这种机制的对结果 集的一种扩展 游标的作用 游标的作用 允许定位在结果集的特定行 从结果集的当前位置检索一行或一部分行 支持对结果集中当前位置的行进行数据修改 为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性 支持 提供脚本 存储过程和触发器中用于访问结果集中的数据的 Transact SQL 语句 使用游标的步骤 使用游标的步骤 1 声明游标 2 打开游标 3 检索游标 4 关闭游标 5 释放游标 4 1 声明游标声明游标 声明游标有 ISO 和 Transact SQL 二种方式 ISO Syntax DECLARE cursor name INSENSITIVE SCROLL CURSOR FOR select statement FOR READ ONLY UPDATE OF column name n Transact SQL Extended Syntax DECLARE cursor name CURSOR LOCAL GLOBAL FORWARD ONLY SCROLL STATIC KEYSET DYNAMIC FAST FORWARD READ ONLY SCROLL LOCKS OPTIMISTIC TYPE WARNING FOR select statement FOR UPDATE OF column name n 上述语法中参数含义请参见 SQL Server 帮助文档 例 declare cur cursor for select from student 4 2 打开游标打开游标 例 open cur 注注 打开游标后 可以使用 CURSOR ROWS 函数在上次打开的游标中接收合格 行的数目 4 3 检索游标检索游标 打开游标后 我们可以使用 Fetch 语句检索游标中的数据行 语法 FETCH NEXT PRIOR FIRST LAST ABSOLUTE n nvar RELATIVE n nvar FROM GLOBAL cursor name cursor variable name INTO variable name n 上述语法中参数含义请参见 SQL Server 帮助文档 例 declare cur cursor scroll for select from student open cur fetch next from cur 下一行 fetch absolute 3 from cur 第三行 fetch relative 2 from cur 当前行的前二行 fetch prior from cur 上一行 fetch last from cur 最后一行 close cur deallocate cur 例 fetch next from cur while FETCH STATUS 0 fetch next from cur 上例中的 FETCH STATUS 0 表示上一个 fetch 语句取数据成功 例 declare sID varchar 4 sName nvarchar 4 declare cur cursor scroll for select sId sName from student open cur fetch next from cur into sID sName select sID sName close cur deallocate cur 4 4 关闭游标关闭游标 CLOSE 用于关闭游标 CLOSE 将保留数据结构以便重新打开 但在重新打开游标之 前 不允许提取和定位更新 必须对打开的游标发布 CLOSE 不允许对仅声明或已关闭的游标执行 CLOSE 4 5 释放游标释放游标 DEALLOCATE 用于释放游标引用 当释放最后的游标引用时 组成该游标的数据结 构由 Microsoft SQL Server 释放 4 6 利用游标修改数据利用游标修改数据 利用游标修改数据时 首先在定义游标时要指明修改的字段 然后在 update 语句中指 定通过游标修改 例 declare cur cursor scroll for select from student where sSex 女 for update of sScore open cur fetch next from cur while FETCH STATUS 0 begin update Student set sScore sScore 200 where current of cur fetch next from cur end close cur deallocate cur declare i int Wage money Tax money declare cur cursor for select eID eWage from Employee open cur fetch next from cur into i wage while FETCH STATUS 0 begin set Wage Wage 2000 if Wage0 and Wage1000 and Wage2000 set Tax 150 Wage 2000 0 15 update employee set eTax Tax where eID i fetch next from cur into i wage end close cur deallocate cur 5 常用函数 常用函数 5 1 转换函数转换函数 1 1 CAST CAST CAST 函数将一种数据类型的表达式转换为另一种数据类型的表达式 语法 2 CONVERT CAST expression AS data type length 例 select sName cast sScore as varchar 10 from Student 下列代码将成绩为 Null 值的数据项显示不 0 分 select sName case when sScore IS NULL then 0 else cast sScore as varchar 10 end from Student CONVERT 函数将一种数据类型的表达式转换为另一种数据类型的表达式 语法 CONVERT data type length expression style 例 select sName convert nvarchar 10 sBirthDate 20 from Student 有关参数的 style 具体情况 请参见 SQL Server 帮助文档 ALTER TABLE dm eco ALTER COLUMN ec econame varchar 500 修改字符串的长度 5 2 字符串函数字符串函数 1 1 LEN character expression LEN character expression 返回字符表达式中的字符数 如果字符串中包含前导空格和尾随空格 则函数会将它 们包含在计数内 LEN 对相同的单字节和双字节字符串返回相同的值 例 select max LEN sName from Student 2 2 DATALENGTHDATALENGTH expressionexpression 返回用于表示任何表达式的字节数 例 declare a nvarchar 10 set a abc select DATALENGTH a 显示为 3 3 LEFTLEFT character expressioncharacter expression integer expressioninteger expression 返回字符串中从左边开始指定个数的字符 4 4 RIGHT character expression integer expression RIGHT character expression integer expression 返回字符表达式中从起始位置 从右端开始 到指定字符位置 从右端开始计数 的 部分 5 5 SUBSTRINGSUBSTRING value expressionvalue expression start expression start expression length expressionlength expression 返回字符表达式 二进制表达式 文本表达式或图像表达式的一部分 例 SELECT x SUBSTRING abcdef 2 3 显示 bcd 6 6 UPPER character expression UPPER character expression 返回将小写字符转换为大写字符后得到的字符表达式 例 select UPPER hello 7 7 LOWER character expression LOWER character expression 返回将大写字符转换为小写字符后得到的字符表达式 8 8 SPACESPACE integer expressioninteger expression 返回由重复的空格组成的字符串 例 select sName SPACE 3 sSex from Student 9 9 REPLICATE character expression times REPLICATE character expression times 返回多次复制后的字符表达式 times 参数的计算结果必须为整数 例 declare a int set a 3 print replicate a 例 以 方式输出菱形 declare i int set i 1 while i 4 begin print space 4 i replicate 2 i 1 set i i 1 end set i 1 while i 3 begin print space i replicate 7 2 i set i i 1 end 1010 STUFFSTUFF character expressioncharacter expression startstart lengthlength character expression character expression STUFF 函数将字符串插入另一字符串 它在第一个字符串中从开始位置删除指定长度 的字符 然后将第二个字符串插入第一个字符串的开始位置 例 SELECT STUFF axyzfg 2 3 bcde 结果为 abcdefg 1111 REVERSE character expression REVERSE character expression 按相反顺序返回字符表达式 例 print REVERSE Mountain Bike 输出 ekiB niatnuoM 1212 LTRIM characterLTRIM character expression expression 返回删除了前导空格的字符表达式 1313 RTRIM characterRTRIM character expression expression 返回删除了尾随空格的字符表达式 1414 TRIM character expression TRIM character expression 返回删除了前导空格和尾随空格的字符表达式 convert int 字符串 SELECT MAX CONVERT int bs comment AS Expr1 FROM dm bomstruct WHERE bs bomid 1 由于字符串找最大时值判断首位 这里用此函数将其转换为数字再取 1515 CHARINDEXCHARINDEX expression1expression1 expression2 expression2 start locationstart location 在 expression2 中搜索 expression1 并返回其起始位置 如果找到 搜索的起始位置 为 start location 注 如果 expression1 或 expression2 之一是 Unicode 数据类型 nvarchar 或 nchar 而另一个不是 则将另一个转换为 Unicode 数据类型 CHARINDEX 不 能与 text ntext 和 image 数据类型一起使用 如果 expression1 或 expression2 之一为 NULL 则 CHARINDEX 将返回 NULL 如果在 expression2 内找不到 expression1 则 CHARINDEX 返回 0 例 DECLARE document varchar 64 SELECT document Reflectors are vital safety components of your bicycle SELECT CHARINDEX vital document 5 返回16 GO 注意 CHARINDEX它只要当中有就返回 不管前后是否有值 PATINDEX它可以加 特殊符 要安装特殊字符的规则来 1616 PATINDEXPATINDEX pattern pattern expressionexpression 返回指定表达式中某模式第一次出现的起始位置 如果在全部有效的文本和字符数据 类型中没有找到该模式 则返回零 例 select PATINDEX 李 sName from Student 1717 STRSTR float expressionfloat expression lengthlength decimaldecimal 返回由数字数据转换来的字符数据 其中 float expression 表示带小数点的近似 数字 float 数据类型的表达式 length 表示总长度 它包括小数点 符号 数字以及 空格 默认值为 10 decimal 表示小数点后的位数 例 SELECT STR 123 45 6 1 输出 123 5 1818 CHARCHAR integer expressioninteger expression 将 int ASCII 代码转换为字符 其中 integer expression 介于 0 和 255 之间的整数 如果该整数表达式不在此范围内 将返回 NULL 值 1919 REPLACE character expression searchstring replacementstring REPLACE character expression searchstring replacementstring 将表达式中的一个字符串替换为另一个字符串或空字符串后 返回一个字符表达式 例 print REPLACE Mountain Bike Mountain All Terrain 返回 All Terrain Bike CHARINDEXCHARINDEX 和和 PATINDEXPATINDEX 1 全匹配查找字符串 2 模糊查找字符串 CHARINDEX 和 PATINDEX 函数都返回指定模式的开始位置 PATINDEX 可使用 通配符 而 CHARINDEX 不可以 这两个函数都带有 2 个参数 1 希望获取其位置的模式 使用 PATINDEX 模式是可以包含通配符的字面字符 串 使用 CHARINDEX 模式是字面字符串 不能包含通配符 2 字符串值表达式 通常为列名 例如 查找模式 wonderful 在 titles 表中 notes 列的某一特定行中的开始位置 例如 SELECT TOP 1 ps comment CHARINDEX 借用 ps comment AS Expr1 FROM dm psm WHERE ps nextid 5 AND CHARINDEX 借用 ps comment b set c a else set c b return c end 注 函数的最后一条语句必须为 return 语句 2 2 调用标量函数 调用标量函数 可以在 Transact SQL 语句中允许使用标量表达式的任何位置调用返回标量值