第05章 Transact-SQL语言基础

SQL语言是关系型数据库的标准语言，能够在SQL Server、Access、Oracle、DB2、MySql等多种数据库上运行。
Transact-SQL语言是SQL Server在SQL语言的基础上增加了一些语言要素后的扩展语言，这些语言要素包括注释、变量、运算符、函数和流程控制语句等。这些附加的语言要素不是标准SQL中的内容。
掌握Transact-SQL语言是进一步学习更多的管理技术和数据库应用开发技术的关键。

Transact-SQL概述

Transact-SQL语言用于处理SQL Server数据库引擎实例的相关操作，主要包括管理数据库对象，检索、插入、修改和删除对象数据。这些都是在程序开发过程中经常用到的功能。
注意：
Transact-SQL语言不是一种标准的编程语言，只能够提供SQL Server 的数据引擎来分析和运行。

Transact-SQL语言的语法约定
Transact-SQL 参考的语法格式中使用的约定及其说明。
image

架构

架构的使用和说明
SQL Server 2016中的架构是形成单个命名空间的数据库实体的集合。
是单个用户所拥有的数据库对象的集合，这些对象形成单个命名空间。

数据库对象由架构所拥有，而架构由数据库用户或角色所拥有。当架构所有者离开单位时，会在删除离开的用户之前将该架构的所有权移交给新的用户或角色。

作用：
利用架构可以简化DBO和开发人员的工作。在 SQL Server 中，架构独立于数据库用户而存在，可以在不更改架构名称的情况下转让架构的所有权，能够在架构中创建具有用户友好名称的对象，明确指示对象的功能。

用户架构分离

架构与数据库用户分离对DBO和开发人员而言有下列好处。

  1. 多个用户可以通过角色成员身份或 Windows 组成员身份拥有一个架构，多个用户可以共享一个默认架构以进行统一名称解析。
  2. 简化了删除数据库用户的操作，删除数据库用户不需要重命名该用户架构所包含的对象。
  3. 开发人员通过共享默认架构可以将共享对象存储在为特定应用程序专门创建的架构中，而不是 DBO 架构中。
  4. 可以用更大的粒度管理架构和架构包含的对象的权限

完全限定的对象名称现在包含4部分：
server.database.schema.object --即服务器.数据库.架构.数据库对象

默认架构。
SQL Server利用默认架构的概念解析未使用其完全限定名称引用的对象的名称。在 SQL Server 2016中，每个用户都有一个默认架构。
如果系统未定义 DEFAULT_SCHEMA，则数据库用户将把 DBO 作为其默认架构。

Transact-SQL语句分类

Transact-SQL语言中的语句，通常根据用途分为5种类型。

  1. 数据定义语言。
    数据定义语言通常是数据库管理系统的一部分，在SQL Server 2016中，数据库对象的数据类型等。
    这些对象的创建、修改和删除等都可以通过使用数据定义语言中的CREATE、ALTER、DROP等语句来完成。

  2. 数据操纵语言。
    数据操作语言 用于检索和操作数据的 SQL 语句的子集。数据操纵语言是指用来查询、添加、修改和删除数据库中数据的语句。
    这些语句包括SELECT、INSERT、UPDATE、DELETE等命令，其中，SELECT是最重要的语句。

  3. 数据控制语言。
    数据控制语言（Data Control Language ，DCL）是用来设置或更改数据库用户或角色权限的语句。
    包括GRANT，DENY，REVOKE等命令。
    在默认状态下，只有sysadmin或db_owner等人员才有权限执行数据控制语言。

  4. 控制流语句（特有）。
    Transact-SQL还为用户提供了控制流语句，用于控制SQL语句、语句块或者存储过程的执行流程。
    在SQL Server中，可以使用的流程控制语句有BEGIN…END、IF…ELSE、WHILE、BREAK、GOTO、WAITFOR、RETURN等主要语句。

Transact-SQL语法要素

常用编码

为了处理世界上各种各样的语言，计算机技术人员需要一种以标准格式来存储一种语言的很多不同字符的方法。Unicode码与ASCⅡ码是最常用的两种编码。
ASCⅡ码
ANSI标准机构制定的ASCⅡ码在使用过程中的存在只能表示256个不同的字符的缺陷。ANSI就建立了许多字符集，指定了一种给定编码的可接受的字符，这就使得对于不同的字母表，需要采用多种编码规格或代码页。

Unicode码
在Unicode标准编码机制下，Unicode具有65,000多个可选的值，Unicode可以包含大多数语言的字符。每个不同的字符都用一种唯一的编码进行表示，不同语言的系统之间传输数据时不需要任何编码转换。

UTF-8（8-bit Unicode Transformation Format）
被称为通用转换格式，是针对Unicode字符的一种变长字符编码。
该字符集是用以解决国际上字符的一种多字节编码，它对英文使用8位（即1个字节），中文使用24位（3个字节）来编码。UTF-8包含全世界所有国家需要用到的字符，是国际编码，通用性强。UTF-8编码的文字可以在各国支持UTF-8字符集的浏览器上显示。

GB 2312和GBK
gb2312是简体中文字符集，GBK是对GB 2312的扩展。GBK是在国家标准GB 2312基础上扩容后兼容GB 2312的标准。GBK包含全部中文字符，是国家编码，通用性比UTF-8差，不过UTF-8占用的数据库比GBK大。

标识符

标识符是用于数据库对象以及常量、变量、自定义函数名称的。
对象标识符可分为常规标识符和分隔标识符。

注意

  • 常规标识符和分隔标识符包含的字符数都必须在1~128之间。
  • 常规标识符可以和分隔符一起使用，也可以不和分隔符一起使用。常规标识符的字母要符合

常规标识符格式规则：

  • 标识符可以以字母开头，也可以符号@(表示局部变量)、#(表示临时变量)或者下划线_开头，后续字符可以是字母、数字和下划线（_）。
  • 标识符不能是Transact-SQL的保留字。
  • 标识符中不允许嵌入空格或特殊字符。

注意：
对于使用分隔标识符，不符合成为常规标识符的格式规则的标识符必须始终使用方括号 “[ ]”进行分隔。

例如，下面给出的示例都是合法的分隔标识符。假设语句中，Sales Volume、Sales Cube 和 select 关键字等都可以使用分隔标识符。

Measures.[Sales Volume]
[Sales Cube]
Product.[select]

实例：

--声明了一个名为Ex_Local的局部变量。
          DECLARE @Ex_Local NCHAR(10)
--声明了一个名为@Ex_Table的表变量。
          DECLARE @Ex_Table  TABLE(col1,CHAR)
--用于创建一个名为TempTable的临时表变量。
          CREATE TABLE #TempTable(itemid,INT)
--定义了一个名为sp_User1的存储过程标识符。
   CREATE PROCEDURE sp_User1 AS
       BEGIN
        …
       END

常量

常量表示一个特定数据值的符号。常量的格式取决于它所表示的值的数据类型。

字符串常量。字符串常量括在单引号内并包含字母数字字符（az、AZ和0~9）以及特殊字符（如!、@和#等）的字符序列。

注意：

  • SQL Server为字符串常量分配当前数据库的默认排序规则，除非使用 COLLATE 子句为其指定了排序规则。

  • 如果单引号中的字符串包含一个嵌入的引号，则可以使用两个单引号表示嵌入的单引号。也可以使用双引号定义字符串常量，则对于嵌入在双引号中的单引号不必作特别处理。

  • 空字符串用中间没有任何字符的两个单引号表示。

  • 对于Unicode 字符串，其前面必须有一个大写字母N前缀。例如，'ABCD' 是字符串常量而 N'ABDC' 则是 Unicode 常量。

示例

'CA123'
'O''Brien'
'Process X is 50%.'
'The level for job_id: %d should be between %d and %d.'
"O'Brien"

二进制常量。二进制常量具有前辍0x并且是十六进制数字字符串。这些常量不使用引号括起。下面是二进制字符串的示例：

0xAA
0x1CE
0x69048AEFBB010E
0x ( 表示空二进制字符)

bit常量。
bit常量使用数字0或1表示，并且不括在引号中。如果使用一个大于 1 的数字，则该数字将转换为1。

日期时间常量。
datetime 常量使用特定格式的字符日期值来表示，并被单引号括起来。

常用的datetime或date常量格式的示例如下：

'April 15, 2017‘              '15 April, 2018'
'170415‘                           '04/15/17'

下面是时间常量的示例：

'16:30:27‘                     '07:27 PM'

整型常量。
integer 常量以没有用引号括起来并且不包含小数点的数字字符序列来表示。integer 常量不能包含小数且必须全部为数字。

数值型常量。decimal 常量由没有用引号括起来并且包含小数点的数字字符串来表示。

下面是 decimal 常量的示例：

3.1415926           9.807

浮点型常量。float 和 real 常量一般使用科学记数法来表示。

下面是 float 或 real 值的示例：

13.76E9              2.77E-3

货币型常量。money 常量以前缀为可选的小数点和可选的货币符号的数字字符串来表示。money 常量不使用引号括起。
下面是 money 常量的示例：

      $20137       $5420437

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

'6F9619FF-8B86-D011-B42D-00C04FC964FF'
0xff19966f868b11d0b42d00c04fc964ff

在Transact-SQL语言中常量的用法主要有两种，即作为表达式中的操作数或用于给变量赋值。

变量

Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。声明变量时需要使用DECLARE命令，为变量赋值时则需要使用SET和SELECT命令。SET命令一次只能为一个变量赋值，而SELECT命令可以同时为多个变量赋值。

局部变量
是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部。

局部变量被引用时要在其名称前加上标志＠，而且必须先用DECLARE命令定义后才可以使用。
定义局部变量的语法形式如下：

DECLAER {@local_variable   data_type}[，…n]

如果想要设定局部变量的值，必须使用SELECT命令或者SET命令。其语法形式为：

SET {{@local_variable = expression }
或者：
SELECT  { @local_variable = expression } [ ,...n ]

全局变量
SQL Server系统本身还提供了一些全局变量。全局变量是SQL Server系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。
全局变量通常存储一些SQL Server的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。

注意

  1. 全局变量不是由用户的程序定义的，它们是在服务器级定义的。
  2. 用户只能使用预先定义的全局变量。
  3. 引用全局变量时，必须以标记符“@@”开头。
  4. 局部变量名称不能与全局变量重名，否则会在应用程序中出现不可预测的结果。

实例

【例5.2】显示到当前日期和时间为止试图登录SQL Server 2016的次数。
SELECT GETDATE()  AS  '当前的时期和时间', 
@@CONNECTIONS AS '试图登录的次数'
运行结果如下：
当前的时期和时间             试图登录的次数
----------------------- -----------
2018-02-22 21:13:26.490 46283
(1 行受影响)

注释

注释是程序代码中非可执行的文本字符串。使用注释对代码进行说明有助于日后的管理和维护。注释通常用于记录程序名称、作者姓名和主要代码更改的日期，还可以用于描述复杂的计算或者解释编程的方法。
在SQL Server中，可以使用两种类型的注释非法方法：

  • --注释。该方式用于单行注释。

  • /*… */注释。“/”用于注释文字的开头，“/”用于注释文字的结尾，利用它们可以在程序中标识多行文字为注释。当然，单行注释也可以使用。

Transact-SQL运算符

在SQL Server中，运算符主要有以下6大类：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符和字符串串联运算符。运算符是用来执行算术运算、字符串连接、赋值以及在字段、常量和变量之间进行比较的操作符。

1.算术运算符

算术运算符主要用于实现数学计算功能，包含的运算符及功能说明如表:
image

2. 比较运算符

比较运算符用于比较两个表达式的值是否相等。Transact-SQL支持的比较运算符有>、=、>= 、< 、<=、 <>、 != 、!> 、!< 等。值得注意的4个比较运算符如表:
image

逻辑运算符

当计算指定的是布尔表达式时需要使用逻辑运算符。逻辑运算符可返回逻辑表达式被执行的最终结果，返回值要么为真(TRUE)，要么为假(FALSE)。

Transact-SQL支持的逻辑运算符，如表:
image

通配符

SQL Server 2016还提供了4种通配符，这些通配符与逻辑运算符一起用于描述一组符合特定条件的表达式。Transact-SQL支持的通配符及其含义如表:

image

字符串连接运算符

字符串连接运算符形式与加号（+）一致，但用于两个字符串地连接。

例如：SELECT ’abc’+’def’+‘123’，其结果为abcdef123。

位运算符

位运算操作符只能用于整数或二进制类型数据，用于在两个整型操作数之间执行位操作运算，

所含运算符如表:(没有左移右移)
image

赋值运算符

Transact-SQL 中只有一个赋值运算符（=）。赋值运算符可以将其右边的表达式值赋给某个特定的对象。另外，还可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。

运算符的优先级

在SQL Server 2016中，当一个复杂的表达式中包含多种运算符时，运算符的优先顺序将决定表达式的计算和比较顺序。

Transact-SQL支持的运算符的优先级按照从高到低的顺序排列如表:(搞不清楚用括号)
image

Transact-SQL函数

SQL Server 2016为Transact-SQL语言提供了大量的功能函数以供编程使用。

如果按照功能对这些函数进行划分，可以将它们大致划分为10类，如下所示。
（1）字符串函数（2）文本/图像管理函数
（3）日期/时间类函数（4）数学计算函数
（5）安全管理函数（6）SQL Server系统配置函数
（7）系统统计函数（8）系统函数
（9）游标函数（10）元数据函数

1 . 数学函数

数学函数用于对数值型字段和表达式进行处理，并返回运算结果。
数学函数可以对SQL Server 2016提供的各种数值型数据进行处理。

常用的数学函数如表：
image

2.聚合函数

聚合函数用于对一组值进行计算并返回一个单一的值。除COUNT 函数之外，聚合函数忽略空值。聚合函数经常与SELECT 语句的GROUP BY 子句一同使用。
作用： 在结果集中通过对被选列值的收集处理，并返回一个数值型的计算结果。常用聚合函数如表：
image

3.时间日期函数

日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间值。

日期时间函数可以在表达式中直接调用，常用的日期时间函数如表：
image

此外，SQL Server 2016还提供了专用于时间函数的常见缩写，如表:
image

4. 转换函数

SQL Server 2016 没有自动执行数据类型的转换，如果需要进行不同类型数据之间的转换，可以使用转换函数CAST 或 CONVERT。

转换函数CONVERT和CAST的语法格式
CAST函数的语法格式。
CAST( expression AS data_type )
CONVERT函数的语法格式。其语法形式为：
CONVERT (data_type[(length)],expression [,style])

转换类型
显式转换。使用 CAST 和 CONVERT 转换函数可以将一种数据类型的表达式强制转换为另一种数据类型的表达式。
利用 CAST 或 CONVERT时，应该注意如下问题。
① 需要提供的信息：要转换的表达式和要将指定的表达式转换为的数据类型。
② 除非将被转换的值存储起来，否则转换仅在CAST 函数或 CONVERT 函数的作用时间范围内有效。
③ 如果转换时没有指定数据类型的长度，则SQL Server 自动将30作为长度值。
转换类型
显式转换。

5. 字符串函数

字符串函数可以对二进制数据、字符串和表达式执行不同的运算，大多数字符串函数只能用于char和varchar数据类型以及明确转换成char和varchar的数据类型，少数几个字符串函数也可以用于binary和varbinary数据类型。常见字符串函数及其功能如表5-12所示。

6.自定义函数

用户根据工作需要，可以创建用户定义函数，以提高程序开发和运行的质量。创建用户定义函数首先要根据业务需要选择函数类型。类型确定后才能使用Transact-SQL或.NET Framework编写函数。
创建自定义函数的有两种方法，即用户利用SQL Server Management Studio中的工具改写模板代码创建函数和使用CREATE FUNCTION语句创建函数。

Transact-SQL表达式
Transact-SQL的表达式 (expression)是指符号和运算符的组合，其计算结果为单个数据值。简单表达式可以是常量、变量、列或标量函数。复杂表达式是由运算符连接的一个或多个简单表达式。
1.复杂表达式
两个表达式可以由一个运算符组合起来，只要它们具有该运算符支持的数据类型，并且满足至少下列一个条件：
两个表达式有相同的数据类型。
优先级低的数据类型可以隐式转换为优先级高的数据类型。
1.复杂表达式
CAST函数能够显式地将优先级低的数据类型转化成优先级高的数据类型，或者转换为一种可以隐式地转化成优先级高的数据类型的过渡数据类型。
如果没有支持的隐式或显式转换，则两个表达式将无法组合。任何计算结果为字符串的表达式的排序规则都应遵循排序优先顺序规则。
2.表达式结果
简单表达式的结果。对于由单个常量、变量、标量函数或列名组成的简单表达式，其数据类型、排序规则、精度、小数位数和值就是它所引用的元素的数据类型、排序规则、精度、小数位数和值。
2.表达式结果
复杂表达式的结果。 用比较运算符或逻辑运算符组合两个表达式时，生成的数据类型为 Boolean，并且值为下列类型之一：TRUE、FALSE 或 UNKNOWN
用算术运算符、位运算符或字符串运算符组合两个表达式时，生成的数据类型取决于运算符。由多个符号和运算符组成的复杂表达式的计算结果为单值结果。生成的表达式的数据类型、排序规则、精度和值由进行组合的两个表达式决定，并按每次两个表达式的顺序递延，直到得出最后结果。表达式中元素组合的顺序由表达式中运算符的优先级决定。

Transact-SQL控制流语句

Transact-SQL为用户提供了控制流语句，用于控制程序的流程，控制流语句是指那些用来控制程序执行和流程分支的语句。在SQL Server 2016中，流程控制语句主要用来控制SQL语句、语句块或者存储过程的执行流程。

下面详细介绍一下主要的控制流程语句。

1. IF…ELSE语句

IF…ELSE语句的语法形式为：

IF Boolean_expression
    { sql_statement | statement_block }
[ ELSE
    { sql_statement | statement_block } ]

2. BEGIN…END语句

BEGIN…END语句能够将多个Transact-SQL语句组合成一个语句块，并将它们视为一个单元处理。

在条件语句和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用BEGIN…END语句。

BEGIN…END语句的语法形式为：

BEGIN
  {   sql_statement | statement_block   }
END

3. WHILE语句

WHILE…CONTINUE…BREAK语句用于设置重复执行 SQL 语句或语句块的条件。

  • CONTINUE语句可以使程序跳过CONTINUE语句后面的语句，回到WHILE循环的第一行命令。
  • BREAK语句则使程序完全跳出循环，结束WHILE语句的执行。

WHILE语句的语法形式为：

 WHILE Boolean_expression
   { sql_statement | statement_block }
 [ BREAK ]
   { sql_statement | statement_block }
 [ CONTINUE ]

【例5.13】 循环控制语句WHILE的使用方法。

DECLARE @count AS INT
SET @count =0
WHILE EXISTS(SELECT * FROM student >WHERE point>800)
 BEGIN
	SET @count = @count+1
   BREAK
 END
IF @count>0
  PRINT '入学成绩有高于800分的学生'
ELSE
  PRINT '入学成绩没有高于800分的学生'

4. CASE语句

CASE语句可以计算多个条件式，并将其中一个符合条件的结果表达式返回。CASE语句按照使用形式的不同，可以分为简单CASE语句和搜索CASE语句。

简单CASE语句的语法格式为：

CASE input_expression
  WHEN when_expression THEN result_expression
   [ ...n ]
  [ELSE else_result_expression ]
END

5. 其他语句

GO 语句
Go 语句是批处理的结束语句。批处理是一起提交并作为一个组执行的若干SQL语句。
PRINT语句
PRINT语句的功能是向客户端返回用户定义消息。
PRINT语句的语法格式：
PRINT @local_variable | string_expr
5. 其他语句
GOTO语句
GOTO语句可以使程序直接跳到指定的标有标识符的位置处继续执行，而位于GOTO语句和标识符之间的程序将不会被执行。
GOTO语句和标识符可以用在语句块、批处理和存储过程中，标识符可以为数字与字符的组合，但必须以“: ”结尾。
GOTO语句的语法形式为：

 label :
 statement | statement_block
 GOTO label

WAITFOR语句：定时执行
WAITFOR用于暂时停止执行SQL语句、语句块或者存储过程等，直到所设定的时间已过或者所设定的时间已到才继续执行。

即在达到指定时间或时间间隔之前，或者指定语句至少修改或返回一行之前，阻止执行批处理、存储过程或事务。

WAITFOR语句的语法形式为：

WAITFOR
{  DELAY 'time_to_pass'
  | TIME 'time_to_execute'   }

RETURN语句
RETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行。RETURN语句的语法形式为：

RETURN [integer_expression ]

其中，参数integer_expression为返回的整型值。存储过程可以给调用过程或应用程序返回整型值。

TRY…CATCH 语句
TRY...CATCH 语句类似于C++ 和C# 语言的异常处理功能。用来处理 Transact-SQL 代码中的错误。TRY…CATCH 构造包括两部分：一个 TRY 块和一个 CATCH 块。如果在 TRY 块中所包含的 Transact-SQL 语句中检测到错误条件，控制将被传递到 CATCH 块中处理该错误。

TRY...CATCH 语句语法格式：

BEGIN TRY
     { sql_statement | statement_block }
END TRY
BEGIN CATCH
     { sql_statement | statement_block }
END CATCH
[;]

CATCH 块处理该异常错误后，控制将被传递到 END CATCH 语句后面的第一个 Transact-SQL 语句。如果 END CATCH 语句是存储过程或触发器中的最后一条语句，控制将返回到调用该存储过程或触发器的代码。将不执行TRY 块中生成错误的语句后面的 Transact-SQL 语句。

Execute语句
Execute语句用于执行 Transact-SQL 批中的命令字符串、字符串或执行下列模块之一：系统存储过程、用户定义存储过程、标量值用户定义函数或扩展存储过程。
最常见的操作如：EXEC sp_help
其中，EXEC是EXECUTE 语句的简写形式，sp_help是一个系统存储过程。其他的存储过程也可以在程序中通过EXECUTE 语句执行。

posted @ 2022-11-02 09:51  kingwzun  阅读(325)  评论(0)  编辑  收藏  举报