第5章 T-SQL语言 5.1 SQL语言与T-SQL语言 5.2 常量、变量与数据类型 5.3 运算符与表达式 5.4 流程控制语句

Presentation on theme: "第5章 T-SQL语言 5.1 SQL语言与T-SQL语言 5.2 常量、变量与数据类型 5.3 运算符与表达式 5.4 流程控制语句"— Presentation transcript:

1 第5章 T-SQL语言 5.1 SQL语言与T-SQL语言 5.2 常量、变量与数据类型 5.3 运算符与表达式 5.4 流程控制语句
5.2 常量、变量与数据类型 5.3 运算符与表达式 5.4 流程控制语句 5.5 系统内置函数 5.6 用户定义函数

2 5.1 SQL语言与T-SQL语言 1．什么是SQL语言
SQL语言：结构化查询语言（Structured Query Language），是用于数据库中的标准数据查询语言，IBM公司最早使用。1986年10月，美国ANSI对 SQL进行规范后，以此作为关系数据库管理系统的标准语言。 作为关系数据库的标准语言，它已被众多商用数据库管理系统产品所采用，不过，不同的数据库管理系统在其实践过程中都对SQL规范做了某些改变和扩充。所以，实际上，不同数据库管理系统之间的SQL语言不能完全通用。例如，微软公司的MS SQL-Server支持的是T-SQL，而甲骨文公司的Oracle 数据库所使用的SQL语言则是PL-SQL。

3 5.1 SQL语言与T-SQL语言 2．什么是T-SQL语言
T-SQL是SQL语言的一种版本，且只能在微软MS SQL-Server以及Sybase Adaptive Server系列数据库上使用。 T-SQL是ANSI SQL的扩展加强版语言，除了提供标准的SQL命令之外，T-SQL还对SQL做了许多补充，提供了类似C、BASIC和Pascal的基本功能，如变量说明、流控制语言、功能函数等。

4 5.1 SQL语言与T-SQL语言 3．T-SQL语言的构成 在SQL Server数据库中，T-SQL语言由四部分组成。
（1）数据定义语言（DDL）。DDL用于执行数据库的任务，对数据库以及数据库中的各种对象进行创建、删除、修改等操作。 数据库对象主要包括表、默认约束、规则、视图、触发器、存储过程。 表5.1 DDL主要语句及功能 语 句 功 能 说 明 CREATE 创建数据库或数据库对象 不同数据库对象，其CREATE语句的语法形式不同 ALTER 对数据库或数据库对象进行修改 不同数据库对象，其ALTER语句的语法形式不同 DROP 删除数据库或数据库对象 不同数据库对象，其DROP语句的语法形式不同

5 5.1 SQL语言与T-SQL语言 （2）数据操纵语言（DML）。DML用于操纵数据库中的各种对象，检索和修改数据。DML包括的主要语句及功能如表5.2所示。 表5.2 DML主要语句及功能 语 句 功 能 说 明 SELECT 从表或视图中检索数据 是使用最频繁的SQL语句之一 INSERT 将数据插入到表或视图中 UPDATE 修改表或视图中的数据 既可修改表或视图的一行数据，也可修改一组或全部数据 DELETE 从表或视图中删除数据 可根据条件删除指定的数据

6 5.1 SQL语言与T-SQL语言 （3）数据控制语言（DCL）。DCL用于安全管理，确定哪些用户可以查看或修改数据库中的数据。
语 句 功 能 说 明 GRANT 授予权限 可把语句许可或对象许可的权限授予其他用户和角色 REVOKE 收回权限 与GRANT的功能相反，但不影响该用户或角色从其他角色中作为成员继承许可权限 DENY 收回权限，并禁止从其他角色继承许可权限 功能与REVOKE相似，不同之处是，除收回权限外，还禁止从其他角色继承许可权限

7 5.1 SQL语言与T-SQL语言 （4）T-SQL增加的语言元素。这部分不是ANSI SQL所包含的内容，而是微软为了用户编程的方便而增加的语言元素。这些语言元素包括变量、运算符、流程控制语句、函数等。这些T-SQL语句都可以在查询分析器中交互执行。

8 5.2 常量、变量与数据类型 5.2.1 常量 根据常量值的不同类型，常量分为： 字符串常量 整型常量 实型常量 日期时间常量 货币常量
5.2 常量、变量与数据类型 常量 根据常量值的不同类型，常量分为： 字符串常量 整型常量 实型常量 日期时间常量 货币常量 唯一标识常量

9 5.2 常量、变量与数据类型 1．字符串常量 字符串常量分为ASCII字符串常量和Unicode字符串常量。 （1）ASCII字符串常量。
5.2 常量、变量与数据类型 1．字符串常量 字符串常量分为ASCII字符串常量和Unicode字符串常量。 （1）ASCII字符串常量。 ASCII字符串常量是用单引号括起来，由ASCII字符构成的符号串。 ASCII字符串常量举例如下： 'China' 'How do you!' 'O''Bbaar' /*如果单引号中的字符串包含引号，可以使用两个单引号来表示嵌入的单引号。*/

10 常量 （2）Unicode 字符串常量。Unicode 字符串常量与ASCII字符串常量相似，但它前面有一个N标识符（N代表 SQL-92标准中的国际语言National Language），N前缀必须为大写字母。 Unicode字符串常量举例如下： N'China ' N'How do you!' Unicode 数据中的每个字符用两个字节存储，而每个ASCII字符用一个字节存储。

11 5.2.1 常量 2．整型常量 按照不同表示方式，整型常量又分为二进制整型常量、十六进制整型常量和十进制整型常量。
常量 2．整型常量 按照不同表示方式，整型常量又分为二进制整型常量、十六进制整型常量和十进制整型常量。 十六进制整型常量的表示：前辍 0x 后跟十六进制数字串。 十六进制常量举例：0xEBF x69048AEFDD010E 0x /*空十六进制常量*/ 二进制整型常量的表示：即数字 0 或1，并且不使用引号。如果使用一个大于 1 的数字，它将被转换为1。 十进制整型常量即不带小数点的十进制数， 例如，

12 5.2.1 常量 3．实型常量 实型常量有定点表示和浮点表示两种方式。 定点表示： 1894.1204 2.0
常量 3．实型常量 实型常量有定点表示和浮点表示两种方式。 定点表示： 2.0 浮点表示： 101.5E5 0.5E-2 +123E-3 -12E5

13 常量 4．日期时间常量 日期时间常量：用单引号将表示日期时间的字符串括起来构成。SQL Server可以识别如下格式的日期和时间。 字母日期格式，如'April 20, 2000'； 数字日期格式，如'4/15/1998'，' '； 未分隔的字符串格式，如' '； 以下是时间常量的例子： '14:30:24' '04:24:PM' 以下是日期时间常量的例子： 'April 20, :30:24'

14 5.2.1 常量 5．money常量 money常量是以“$”作为前缀的一个整型或实型常量数据。下面是money常量的例子： $12
常量 5．money常量 money常量是以“$”作为前缀的一个整型或实型常量数据。下面是money常量的例子： $12 $542023 -$45.56 +$ 6．uniqueidentifier常量 uniqueidentifier 常量是用于表示全局唯一标识符（GUID）值的字符串。可以使用字符串或十六进制字符串格式指定。例如， '6F9619FF-8A86-D011-B42D-00004FC964FF' 0xff19966f868b11d0b42d00c04fc964ff

15 5.2.2 数据类型 1．系统数据类型 系统数据类型又称为基本数据类型。在第3章已详细地介绍了。 2．用户自定义数据类型
数据类型 1．系统数据类型 系统数据类型又称为基本数据类型。在第3章已详细地介绍了。 2．用户自定义数据类型 用户自定义数据类型可看做系统数据类型的别名。 在多表操作的情况下，当多个表中的列要存储相同类型的数据时，往往要确保这些列具有完全相同的数据类型、长度和为空性（数据类型是否允许空值）。用户自定义数据类型并不是真正的数据类型，它只是提供了一种提高数据库内部元素和基本数据类型之间一致性的机制。

16 数据类型 用户自定义数据类型student_num后，可以重新设计学生成绩管理数据库表XSB、CJB结构中的学号字段，如表5.4、表5.5和表5.6所示。 表5.4 自定义类型student_num 依赖的系统类型 值允许的长度 为 空 性 char 6 NOT NULL 表5.5 表XSB中学号字段的重新设计 表5.6 表CJB中学号字段的重新设计 字 段 名 类 型 学号 student_num

17 5.2.2 数据类型 要使用用户自定义类型，首先应定义该类型，然后用这种类型来定义字段或变量。
数据类型 要使用用户自定义类型，首先应定义该类型，然后用这种类型来定义字段或变量。 在创建用户自定义数据类型时首先应考虑如下三个属性： 数据类型名称； 新数据类型所依据的系统数据类型（又称为基类型）； 为空性。 如果为空性未明确定义，系统将依据数据库或连接的ANSI Null 默认设置进行指派。

18 5.2.2 数据类型 创建用户自定义数据类型的方法如下。 （1）使用对象资源管理器定义。 步骤如下。
数据类型 创建用户自定义数据类型的方法如下。 （1）使用对象资源管理器定义。 步骤如下。 第1步：启动“SQL Server Management Studio”，在“对象资源管理器”中展开“数据库→PXSCJ→可编程性”，右击“类型”，选择“新建”选项，再选择“新建用户定义数据类型”，弹出“新建用户定义数据类型”窗口。

19 数据类型 第2步：在“名称”文本框中输入自定义的数据类型名称，如student_num。在“数据类型”下拉框中选择自定义数据类型所基于的系统数据类型，如char。在“长度”栏中填写要定义的数据类型的长度，如6。其他选项使用默认值，如图5.1所示，单击“确定”按钮即可完成创建。 图5.1 “新建用户定义数据类型属性”窗口

20 数据类型 （2）使用命令定义。 在SQL Server 2008中，使用CREATE TYPE语句来实现用户数据类型的定义。语法格式： CREATE TYPE [ schema_name. ] type_name FROM base_type [ ( precision [ , scale ] ) ] [ NULL | NOT NULL ] [ ; ]  例如，定义描述学号字段的数据类型的语句如下： CREATE TYPE student_num FROM char(6) NOT NULL

21 5.2.2 数据类型 （3）删除用户自定义数据类型。 在SSMS中删除用户自定义数据类型的主要步骤如下：
数据类型 （3）删除用户自定义数据类型。 在SSMS中删除用户自定义数据类型的主要步骤如下： 在对象资源管理器中展开数据库“PXSCJ→可编程性→类型”，在“用户定义数据类型”中选择类型“dbo.student_num”，右击鼠标，在弹出的快捷菜单中选择“删除”菜单项，打开“删除对象”窗口后单击“确定”按钮即可（实际不做操作）。

22 5.2.2 数据类型 （4）使用命令删除用户自定义数据类型。 使用命令方式删除自定义数据类型可以使用DROP TYPE语句。语法格式：
数据类型 （4）使用命令删除用户自定义数据类型。 使用命令方式删除自定义数据类型可以使用DROP TYPE语句。语法格式： DROP TYPE [ schema_name. ] type_name [ ; ] 例如，删除前面定义的student_num类型的语句为 DROP TYPE student_num 以上语句实际不做操作。

23 5.2.2 数据类型 （5）利用用户自定义数据类型定义字段。
数据类型 （5）利用用户自定义数据类型定义字段。 在定义类型后，接着应考虑定义这种类型的字段，同样可以利用对象资源管理器和T-SQL命令两种方式实现。读者可以参照第2章进行定义，不同点只是数据类型为用户自定义类型，而不是系统类型。 例如，在对象资源管理器中对于XSB表学号字段的定义如图5.2所示。 图5.2 使用用户自定义数据类型定义XSB表

24 5.2.2 数据类型 利用命令方式定义XSB表结构： CREATE TABLE XSB (
数据类型 利用命令方式定义XSB表结构： CREATE TABLE XSB ( 学号 student_num NOT NULL PRIMARY KEY, /*将学号定义为student_num类型*/ 姓名 char(8) NOT NULL, 性别 bit NULL DEFAULT 1, 出生时间 datetime NULL, 专业 char(12) NULL, 总学分 int NULL, 备注 varchar(500) NULL )

25 数据类型 3．用户自定义表数据类型 SQL Server 2008还提供了一种新的用户自定义数据类型，称为用户自定义表数据类型（User-defined Table Types）。 这种数据类型也由用户自行定义，可以作为参数提供给语句、存储过程或者函数。 创建自定义表数据类型语法格式如下： CREATE TYPE [ schema_name. ] type_name AS TABLE ( <column_definition> [ <table_constraint> ] [ ,...n ] ) [ ; ]

26 5.2.2 数据类型 【例5.1】 创建用户自定义表数据类型，包含CJB表中的所有列。 CREATE TYPE CJB_tabletype
数据类型 【例5.1】 创建用户自定义表数据类型，包含CJB表中的所有列。 CREATE TYPE CJB_tabletype AS TABLE ( 学号 char(6) NOT NULL, 课程号 char(3) NOT NULL, 成绩 int NOT NULL, PRIMARY KEY(学号, 课程号) )

27 5.2.3 变量 1．变量 变量名必须是一个合法的标识符。 （1）标识符。在SQL Server中标识符分为两类。
变量 1．变量 变量名必须是一个合法的标识符。 （1）标识符。在SQL Server中标识符分为两类。 注意：常规标识符不能是T-SQL的保留字。常规标识符中不允许嵌入空格或其他特殊字符。 分隔标识符：包含在双引号（"）或者方括号（[ ]）内的常规标识符或不符合常规标识符规则的标识符。 标识符允许的最大长度为128个字符。符合常规标识符格式规则的标识符可以分隔，也可以不分隔。对不符合标识符规则的标识符必须进行分隔。

28 5.2.3 变量 （2）变量的分类。SQL Server中变量可分为两类。
变量 （2）变量的分类。SQL Server中变量可分为两类。 Server以来连接或试图连接的次数。 局部变量：局部变量用于保存单个数据值。例如，保存运算的中间结果，作为循环变量等。

29 5.2.3 变量 2．局部变量的使用 （1）局部变量的定义与赋值。
变量 2．局部变量的使用 （1）局部变量的定义与赋值。 ① 局部变量的定义。在批处理或过程中用DECLARE语句声明局部变量，所有局部变量在声明后均初始化为NULL。语法格式： data_type [= value]}  [ ,...n] 说明如下。 data_type：数据类型，用于定义局部变量的类型，可为系统类型或自定义类型。 =value：为变量赋值，值可以是常量或表达式，但它必须与变量声明类型匹配。 n：表示可定义多个变量，各变量间用逗号隔开。

30 5.2.3 变量 ② 局部变量的赋值。可用SET或SELECT语句为其赋值。 用SET语句赋值：
变量 ② 局部变量的赋值。可用SET或SELECT语句为其赋值。 用SET语句赋值： 将DECLARE语句创建的局部变量设置为给定表达式的值。语法格式： SET @local_variable＝expression

31 5.2.3 变量 【例5.2】 创建局部变量@var1、@var2并赋值，然后输出变量的值。
变量 【例5.2】 char(10) char(30) SET @var1='中国' /*一个SET语句只能为一个变量赋值*/ SET @var2 GO 执行结果如下：

32 5.2.3 变量 【例5.3】 创建一个名为sex的局部变量，并在SELECT语句中使用该局部变量查找表XSB中所有女同学的学号、姓名。
变量 【例5.3】 创建一个名为sex的局部变量，并在SELECT语句中使用该局部变量查找表XSB中所有女同学的学号、姓名。 USE PXSCJ GO bit SELECT 学号, 姓名 FROM XSB WHERE 执行结果如下：

33 5.2.3 变量 【例5.4】 使用查询为变量赋值。 DECLARE @student char(8)
变量 【例5.4】 使用查询为变量赋值。 char(8) 姓名 FROM XSB WHERE 学号= '081101')

34 5.2.3 变量 用SELECT语句赋值： 语法格式： SELECT {@local_variable=expression} [,…n]
变量 用SELECT语句赋值： 语法格式： SELECT [,…n] 关于SELECT，需说明以下几点： 通常用于将单个值返回到变量中。如果expression为列名，则返回多个值，此时将返回的最后一个值赋给变量。 如果SELECT 语句没有返回行，变量将保留当前值。 如果expression是不返回值的标量子查询，则将变量设为 NULL。 一个SELECT语句可以初始化多个局部变量。

35 5.2.3 变量 【例5.5】 使用SELECT语句为局部变量赋值。 DECLARE @var1 nvarchar(30)
变量 【例5.5】 使用SELECT语句为局部变量赋值。 nvarchar(30) ='刘丰' SELECT @var1 AS 'NAME' 执行结果如下：

36 5.2.3 变量 【例5.6】 为局部变量赋空值。 DECLARE @var1 nvarchar(30)
变量 【例5.6】 为局部变量赋空值。 nvarchar(30) = '刘丰' = ( SELECT 姓名 FROM XSB WHERE 学号= '089999' ) AS 'NAME' 执行结果如下：

37 5.2.3 变量 （2）局部游标变量的定义与赋值。 ① 局部游标变量的定义。 语法格式：
变量 （2）局部游标变量的定义与赋值。 ① 局部游标变量的定义。 语法格式： DECLARE CURSOR } [ ,...n] ② 局部游标变量的赋值。利用SET语句为一个游标变量赋值，有三种情况： 将一个已存在的并且赋值的游标变量的值赋给另一个局部游标变量。 将一个已声明的游标名赋给指定的局部游标变量。 声明一个游标，同时将其赋给指定的局部游标变量。

38 5.2.3 变量 上述三种情况的语法描述如下。 语法格式： SET { @cursor_variable =
变量 上述三种情况的语法描述如下。 语法格式： SET  = { @cursor_variable /*将一个已赋值的游标变量的值赋给一个目标游标变量*/ | cursor_name  /*将一个已声明的游标名赋给游标变量*/ | { CURSOR 子句 } /*游标声明*/ }

39 变量 ③ 游标变量的使用步骤如下。 定义游标变量→给游标变量赋值→打开游标→利用游标读取行（记录）→使用结束后关闭游标→删除游标的引用。 【例5.7】 使用游标变量。 USE PXSCJ GO DECLARE @CursorVar CURSOR /*定义游标变量*/ SET @CursorVar = CURSOR SCROLL DYNAMIC /*为游标变量赋值*/ FOR SELECT 学号, 姓名 FROM XSB WHERE 姓名 LIKE '王%' /*打开游标*/ FETCH NEXT FETCH NEXT /*通过游标读行记录*/ /*删除对游标的引用*/

40 5.2.3 变量 （3）表数据类型变量的定义与赋值。 语法格式： DECLARE
变量 （3）表数据类型变量的定义与赋值。 语法格式： DECLARE [AS] TABLE ( { <column_definition> | <table_constraint> } [ ,... ] ) } 【例5.8】 声明一个表数据类型变量并向变量中插入数据。 AS TABLE ( num char(6) NOT NULL PRIMARY KEY, name char(8) NOT NULL, sex bit NULL ) /*声明变量*/ INSERT SELECT 学号,姓名,性别 FROM XSB /*插入数据*/ SELECT * /*查看内容*/

41 5.3 运算符与表达式 1．算术运算符 算术运算符在两个表达式上执行数学运算，这两个表达式可以是任何数字数据类型 。
5.3 运算符与表达式 1．算术运算符 算术运算符在两个表达式上执行数学运算，这两个表达式可以是任何数字数据类型 。 算术运算符有+（加）、-（减）、*（乘）、/（除）和%（求模）5种运算。 +（加）和-（减）运算符还可用于对日期时间类型的值进行算术运算。

42 5.3 运算符与表达式 2．位运算符 位运算符在两个表达式之间执行位操作，这两个表达式的类型可为整型或与整型兼容的数据类型（如字符型等，但不能为image类型）。位运算符如表5.7所示。 表5.7 位运算符 运 算 符 运 算 规 则 & 两个位均为1时，结果为1，否则为0 | 只要一个位为1，则结果为1，否则为0 ^ 两个位值不同时，结果为1，否则为0

43 5.3 运算符与表达式 【例5.9】 在master数据库中，建立表bitop，并插入一行，然后将a字段和 b字段列上值进行按位与运算。
5.3 运算符与表达式 【例5.9】 在master数据库中，建立表bitop，并插入一行，然后将a字段和 b字段列上值进行按位与运算。 USE master GO CREATE TABLE bitop ( a int NOT NULL, b int NOT NULL ) INSERT bitop VALUES (168, 73) SELECT a & b, a | b, a ^ b FROM bitop 执行结果如下：

44 5.3 运算符与表达式 说明：a(168)的二进制表示为 ；b(73)的二进制表示为 。在这两个值之间进行的位运算如下。 (a &b)： ————————————— （十进制值为8） (a | b)： （十进制值为233） (a ^ b)： （十进制值为225）

45 5.3 运算符与表达式 3．比较运算符 比较运算符（又称关系运算符）如表5.8所示，用于测试两个表达式的值是否相同，
5.3 运算符与表达式 3．比较运算符 比较运算符（又称关系运算符）如表5.8所示，用于测试两个表达式的值是否相同， 其运算结果为逻辑值，可以为TRUE、FALSE 及 UNKNOWN三者之一。 表5.8 比较运算符 运 算 符 含 义 = 相等 <= 小于等于 > 大于 <>、!= 不等于 < 小于 !< 不小于 >= 大于等于 !> 不大于

46 5.3 运算符与表达式 除text、ntext或image类型的数据外，比较运算符可以用于所有的表达式。
5.3 运算符与表达式 除text、ntext或image类型的数据外，比较运算符可以用于所有的表达式。 下面的例子用于查询指定学号的学生在XSB表中的信息，其中，IF语句为条件判断语句。 USE PXSCJ GO char(10) = '081101' IF <> 0) SELECT * FROM XSB WHERE 执行结果如下：

47 5.3 运算符与表达式 4．逻辑运算符 逻辑运算符用于对某个条件进行测试，运算结果为TRUE或FALSE。 表5.9 逻辑运算符 运 算 符
5.3 运算符与表达式 4．逻辑运算符 逻辑运算符用于对某个条件进行测试，运算结果为TRUE或FALSE。 表5.9 逻辑运算符 运 算 符 运 算 规 则 AND 如果两个操作数值都为TRUE，则运算结果为 TRUE OR 如果两个操作数中有一个为TRUE，则运算结果为TRUE NOT 若一个操作数值为TRUE，则运算结果为FALSE，否则为TRUE ALL 如果每个操作数值都为TRUE，则运算结果为TRUE ANY 在一系列操作数中只要有一个为 TRUE，则运算结果为TRUE BETWEEN 如果操作数在指定的范围内，则运算结果为TRUE EXISTS 如果子查询包含一些行，则运算结果为TRUE IN 如果操作数值等于表达式列表中的一个，则运算结果为TRUE LIKE 如果操作数与一种模式相匹配，则运算结果为TRUE SOME 如果在一系列操作数中，有些值为TRUE，则运算结果为TRUE

48 5.3 运算符与表达式 （1）ANY、SOME、ALL、IN的使用。
5.3 运算符与表达式 （1）ANY、SOME、ALL、IN的使用。 可以将ALL或ANY关键字与比较运算符组合进行子查询。SOME的用法与ANY相同。以 > 比较运算符为例： >ALL表示大于每一个值，即大于最大值。例如，>ALL(5, 2, 3)表示大于5。因此，使用>ALL的子查询也可用MAX集函数实现。 >ANY表示至少大于一个值，即大于最小值。例如，>ANY (7, 2, 3)表示大于2。因此，使用>ANY的子查询也可用MIN集函数实现。 =ANY运算符与IN等效。 <>ALL与NOT IN等效。

49 5.3 运算符与表达式 【例5.10】 查询成绩高于“林一帆”最高成绩的学生姓名、课程名及成绩。 USE PXSCJ GO
5.3 运算符与表达式 【例5.10】 查询成绩高于“林一帆”最高成绩的学生姓名、课程名及成绩。 USE PXSCJ GO SELECT 姓名, 课程名, 成绩 FROM XSB, CJB, KCB WHERE 成绩> ALL ( SELECT b.成绩 FROM XSB a, CJB b WHERE a.学号= b.学号 AND a.姓名= '林一帆' ) AND XSB.学号=CJB.学号 AND KCB.课程号=CJB.课程号 AND 姓名<>'林一帆'

50 5.3 运算符与表达式 （2）BETWEEN的使用。 语法格式：
5.3 运算符与表达式 （2）BETWEEN的使用。 语法格式： test_expression [ NOT ] BETWEEN begin_expression AND end_expression 如果test_expression的值大于或等于begin_expression的值并且小于或等于 end_expression的值，则运算结果为TRUE，否则为FALSE。 NOT关键字表示对谓词BETWEEN的运算结果取反。

51 5.3 运算符与表达式 【例5.11】 查询总学分在 40～50之间的学生学号和姓名。 SELECT 学号, 姓名, 总学分
5.3 运算符与表达式 【例5.11】 查询总学分在 40～50之间的学生学号和姓名。 SELECT 学号, 姓名, 总学分 FROM XSB WHERE 总学分 BETWEEN 40 AND 50 使用 >= 和 <=代替 BETWEEN实现与上例相同的功能： FROM XSB WHERE 总学分>= 40 AND 总学分<=50 【例5.12】 查询总学分在范围40～50之外的所有学生的学号和姓名。 WHERE 总学分 NOT BETWEEN 40 AND 50

52 5.3 运算符与表达式 （3）LIKE的使用。 语法格式：
5.3 运算符与表达式 （3）LIKE的使用。 语法格式： match_expression [ NOT ] LIKE pattern [ ESCAPE Escape_character ] 确定给定的字符串是否与指定的模式匹配，若匹配，则运算结果为TRUE，否则为FALSE。模式可以包含普通字符和通配字符。 【例5.13】 查询课程名以“计”或C开头的情况。 SELECT * FROM KCB WHERE 课程名 LIKE '[计C]%'

53 5.3 运算符与表达式 （4）EXISTS与NOT EXISTS的使用。 语法格式： EXISTS subquery
5.3 运算符与表达式 （4）EXISTS与NOT EXISTS的使用。 语法格式： EXISTS subquery 用于检测一个子查询的结果是否不为空，若是则运算结果为真，否则为假。subquery用于代表一个受限的SELECT语句（不允许有COMPUTE子句和INTO关键字）。EXISTS子句的功能有时可用IN或= ANY运算符实现，而NOT EXISTS 的作用与EXISTS正相反。 【例5.14】 查询所有选课学生的姓名。 SELECT DISTINCT 姓名 FROM XSB WHERE EXISTS ( SELECT * FROM CJB WHERE XSB.学号= CJB.学号 )

54 5.3 运算符与表达式 5．字符串连接运算符 通过运算符“+”实现两个字符串的连接运算。 【例5.15】 多个字符串的连接。
5.3 运算符与表达式 5．字符串连接运算符 通过运算符“+”实现两个字符串的连接运算。 【例5.15】 多个字符串的连接。 SELECT (学号+ ',' + 姓名) AS 学号及姓名 FROM XSB WHERE 学号= '081101' 执行结果如下：

55 5.3 运算符与表达式 6．一元运算符 一元运算符有+（正）、-（负）和～（按位取反）三个。对于按位取反运算符举例如下：
5.3 运算符与表达式 6．一元运算符 一元运算符有+（正）、-（负）和～（按位取反）三个。对于按位取反运算符举例如下： 设a的值为12（ ），则～a的值为 。 7．赋值运算符 指给局部变量赋值的SET和SELECT语句中使用的“=”。

56 5.3 运算符与表达式 8．运算符的优先顺序 在一个表达式中按先高（优先级数字小）后低（优先级数字大）的顺序进行运算。
5.3 运算符与表达式 8．运算符的优先顺序 在一个表达式中按先高（优先级数字小）后低（优先级数字大）的顺序进行运算。 表5.10 运算符优先级表 运 算 符 优 先 级 +（正）、-（负）、～（按位 NOT） 1 NOT 6 *（乘）、/（除）、%（模） 2 AND 7 +（加）、+（串联）、-（减） 3 ALL、ANY、BETWEEN、IN、LIKE、OR、SOME 8 =,  >,  <,  >=,  <=,  <>,  !=,  !>,  !< 比较运算符 4 =（赋值） 9 ^（位异或）、&（位与）、|（位或） 5

57 5.3 运算符与表达式 9．表达式 一个表达式就是常量、变量、列名、复杂计算、运算符和函数的组合。表达式可分为字符型表达式、数值型表达式和日期时间型表达式。 表达式还可以根据值的复杂性来分类。 标量表达式：若表达式的结果只是一个值，如一个数值、一个单词或一个日期，如1+2，'a'>'b'。 行表达式：若表达式的结果是由不同类型数据组成的一行值，例如，（学号，'王林'，'计算机'，50*10）；当学号列的值为081101时，这个行表达式的值就为（'081101'，'王 林'，'计算机'，500）。 表表达式：若表达式的结果为0个、1个或多个行表达式的集合。 表达式一般用在SELECT以及SELECT语句的WHERE子句中。

58 5.4 流程控制语句 在设计程序时，常常需要利用各种流程控制语句，改变计算机的执行流程以满足程序设计的需要。在SQL Server中提供了如表5.11所示的流程控制语句。 表5.11 SQL Server流程控制语句 控 制 语 句 说 明 BEGIN…END 语句块 CONTINUE 用于重新开始下一次循环 IF…ELSE 条件语句 BREAK 用于退出最内层的循环 CASE 分支语句 RETURN 无条件返回 GOTO 无条件转移语句 WAITFOR 为语句的执行设置延迟 WHILE 循环语句

59 5.4 流程控制语句 【例5.16】 查询总学分大于42的学生人数。 USE PXSCJ GO DECLARE @num int
5.4 流程控制语句 【例5.16】 查询总学分大于42的学生人数。 USE PXSCJ GO int COUNT(姓名) FROM XSB WHERE 总学分>42) AS '总学分>42的人数'

60 BEGIN…END语句块 在T-SQL中可以定义BEGIN…END语句块。当要执行多条T-SQL语句时，就需要使用BEGIN…END将这些语句定义成一个语句块，作为一组语句来执行。语法格式如下： BEGIN { sql_statement | statement_block } END 例： USE PXSCJ GO SELECT * FROM XSB SELECT * FROM KCB

61 5.4.2 条件语句 在程序中如果要对给定的条件进行判定，当条件为真或假时分别执行不同的 T-SQL 语句，可用IF…ELSE语句实现。
条件语句 在程序中如果要对给定的条件进行判定，当条件为真或假时分别执行不同的 T-SQL 语句，可用IF…ELSE语句实现。 语法格式： IF Boolean_expression /*条件表达式*/ { sql_statement | statement_block } /*条件表达式为真时执行*/ [ ELSE { sql_statement | statement_block } ] /*条件表达式为假时执行*/

62 5.4.2 条件语句 由上述语法格式可看出，条件语句分带ELSE部分和不带ELSE部分两种使用形式。 （1）带ELSE部分：
条件语句 由上述语法格式可看出，条件语句分带ELSE部分和不带ELSE部分两种使用形式。 （1）带ELSE部分： IF 条件表达式 A /* T-SQL语句或语句块*/ ELSE B /*T-SQL语句或语句块*/ 当条件表达式的值为真时执行A，然后执行IF语句的下一语句；条件表达式的值为假时执行B，然后执行IF语句的下一语句。

63 5.4.2 条件语句 （2）不带ELSE部分： IF 条件表达式 A /*T-SQL语句或语句块*/
条件语句 （2）不带ELSE部分： IF 条件表达式 A /*T-SQL语句或语句块*/ 当条件表达式的值为真时执行A，然后执行IF语句的下一条语句；当条件表达式的值为假时直接执行IF语句的下一条语句。 图5.3 IF语句的执行流程

64 5.4.2 条件语句 【例5.17】 如果“计算机基础”课程的平均成绩高于75分，则显示“平均成绩高于75分”。 IF (
条件语句 【例5.17】 如果“计算机基础”课程的平均成绩高于75分，则显示“平均成绩高于75分”。 IF ( SELECT AVG(成绩) FROM XSB, CJB, KCB WHERE XSB.学号= CJB.学号 AND CJB.课程号=KCB.课程号 AND KCB.课程名='计算机基础' ) <75 SELECT '平均成绩低于75' ELSE SELECT '平均成绩高于75'

65 5.4.2 条件语句 【例5.18】 IF…ELSE语句的嵌套使用。 IF ( SELECT AVG(成绩)
条件语句 【例5.18】 IF…ELSE语句的嵌套使用。 IF ( SELECT AVG(成绩) FROM XSB, CJB, KCB WHERE XSB.学号= CJB.学号 AND CJB.课程号=KCB.课程号 AND KCB.课程名='计算机基础' ) <75 SELECT '平均成绩低于75' ELSE ) >75 SELECT '平均成绩高于75'

66 5.4.3 CASE语句 语法格式： CASE input_expression
WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END 或者 CASE WHEN Boolean_expression THEN result_expression [ ELSE else_result_expression ]

67 5.4.3 CASE语句 【例5.19】 使用第一种格式的CASE语句，根据性别值输出“男”或“女”。
SELECT 学号, 姓名, 专业, SEX= CASE 性别 WHEN 1 THEN '男' WHEN 0 THEN '女' ELSE '无' END FROM XSB WHERE 总学分>48

68 5.4.3 CASE语句 若使用第二种格式的CASE语句则可以使用以下T-SQL语句。 SELECT 学号, 姓名, 专业, SEX=
WHEN 性别=1 THEN '男' WHEN 性别=0 THEN '女' ELSE '无' END FROM XSB WHERE 总学分>48

69 无条件转移语句 无条件转移语句将执行流程转移到标号指定的位置。 语法格式： GOTO label label : 语句

70 5.4.5 循环语句 1．WHILE循环语句 如果需要重复执行程序中的一部分语句，则可使用WHILE循环语句实现。 语法格式：
循环语句 1．WHILE循环语句 如果需要重复执行程序中的一部分语句，则可使用WHILE循环语句实现。 语法格式： WHILE Boolean_expression /*条件表达式*/ { sql_statement | statement_block } /*T-SQL语句序列构成的循环体*/ WHILE语句的执行流程如图5.4所示。 图5.4 WHILE语句的执行流程

71 5.4.5 循环语句 【例5.20】 将学号为081101的学生的总学分使用循环修改到60，每次只加2，并判断循环了多少次。
循环语句 【例5.20】 将学号为081101的学生的总学分使用循环修改到60，每次只加2，并判断循环了多少次。 USE PXSCJ GO INT WHILE (SELECT 总学分 FROM XSB WHERE 学号='081101')<60 BEGIN UPDATE XSB SET 总学分=总学分+2 WHERE 学号= '081101' SET END AS 循环次数 执行结果如下：

72 5.4.5 循环语句 2．BREAK语句 语法格式： BREAK
循环语句 2．BREAK语句 语法格式： BREAK BREAK语句一般用在循环语句中，用于退出本层循环。当程序中有多层循环嵌套时，使用BREAK语句只能退出其所在的这一层循环。 3．CONTINUE语句 CONTINUE CONTINUE语句一般用在循环语句中，用于结束本次循环，重新转到下一次循环条件的判断。

73 5.4.6 返回语句 用于从存储过程、批处理或语句块中无条件退出，不执行位于RETURN之后的语句。 语法格式：
返回语句 用于从存储过程、批处理或语句块中无条件退出，不执行位于RETURN之后的语句。 语法格式： RETURN [ integer_expression ] 如果不提供integer_expression，则退出程序并返回一个空值；如果用在存储过程中，则可以返回整型值integer_expression。 【例5.21】 判断是否存在学号为081128的学生，如果存在则返回，不存在则插入081128号学生的信息。 IF EXISTS(SELECT * FROM XSB WHERE 学号='081128') RETURN ELSE INSERT INTO XSB VALUES('081128', '张可', 1, ' ', '计算机',52, NULL)

74 5.4.7 等待语句 等待语句指定触发语句块、存储过程或事务执行的时刻或需等待的时间间隔。 语法格式： WAITFOR {
等待语句 等待语句指定触发语句块、存储过程或事务执行的时刻或需等待的时间间隔。 语法格式： WAITFOR { DELAY 'time_to_pass' | TIME 'time_to_execute' } 【例5.22】 如下语句设定在早上8点执行查询语句。 BEGIN WAIT FOR TIME '8:00' SELECT * FROM XSB END

75 5.5 系统内置函数 系统内置函数介绍 T-SQL编程语言提供三个系统内置函数： 行集函数 聚合函数 标量函数

76 5.5 系统内置函数 1．行集函数 行集函数是返回值为对象的函数，该对象可在T-SQL语句中作为表引用。所有行集函数都是非确定性的，即每次用一组特定参数调用它们时，所返回的结果不总是相同的。 SQL Server 2008主要提供了如下行集函数： （1）CONTAINSTABLE：对于基于字符类型的列，按照一定的搜索条件进行精确或模糊匹配，然后返回一个表，该表可能为空。 （2）FREETEXTTABLE：为基于字符类型的列返回一个表，其中的值符合指定文本的含义，但不符合确切的表达方式。 （3）OPENDATASOURCE：提供与数据源的连接。

77 5.5 系统内置函数 （4）OPENQUERY：在指定数据源上执行查询。可以在查询的 FROM 子句中像引用基本表一样引用OPENQUERY函数，虽然查询可能返回多个记录，但 OPENQUERY 只返回第一个记录。 （5）OPENROWSET：包含访问OLE DB数据源中远程数据所需的全部连接信息。可在查询的FROM子句中像引用基本表一样引用OPENROWSET函数，虽然查询可能返回多个记录，但OPENROWSET只返回第一个记录。 （6）OPENXML函数：通过XML文档提供行集视图。

78 5.5.1 系统内置函数介绍 2．聚合函数 聚合函数对一组值操作，返回单一的汇总值。聚合函数在以下情况下，允许作为表达式使用：
系统内置函数介绍 2．聚合函数 聚合函数对一组值操作，返回单一的汇总值。聚合函数在以下情况下，允许作为表达式使用： （1）SELECT语句的选择列表（子查询或外部查询）。 （2）COMPUTE或COMPUTE BY子句。 （3）HAVING子句。

79 系统内置函数介绍 3．标量函数 标量函数的特点：输入参数的类型为基本类型，返回值也为基本类型。SQL Server包含如下几类标量函数： （1）配置函数； （2）系统函数； （3）系统统计函数； （4）数学函数； （5）字符串函数； （6）日期和时间函数； （7）游标函数； （8）文本和图像函数； （9）元数据函数； （10）安全函数。

80 常用系统标量函数 1．配置函数 配置函数用于返回当前配置选项设置的信息。全局变量是以函数形式使用的，配置函数一般都是全局变量名。

81 常用系统标量函数 2．数学函数 数学函数可对SQL Server提供的数字数据（decimal、integer、float、real、money、smallmoney、smallint 和 tinyint）进行数学运算并返回运算结果。 （1）ABS函数。语法格式： ABS(numeric_expression) 返回给定数字表达式的绝对值。 【例5.23】 显示ABS 函数对三个不同数字的效果。 SELECT ABS(-5.0), ABS(0.0), ABS(8.0) 运行结果如下：

82 5.5.2 常用系统标量函数 （2）RAND函数。语法格式： RAND ([ seed ] ) 返回0～1之间的一个随机值。
常用系统标量函数 （2）RAND函数。语法格式： RAND ([ seed ] ) 返回0～1之间的一个随机值。 如果未指定seed，则随机分配种子值。对于指定的种子值，返回的结果始终相同。 【例5.24】 以下程序通过RAND函数返回随机值。 int = 5 SELECT

83 5.5.2 常用系统标量函数 3．字符串处理函数 （1）ASCII函数。语法格式：
常用系统标量函数 3．字符串处理函数 （1）ASCII函数。语法格式： ASCII ( character_expression ) 返回字符表达式最左端字符的ASCII值。 【例5.25】 查找字符串'sql'最左端字符的ASCII值。 SELECT ASCII('sql') 执行结果如下：

84 5.5.2 常用系统标量函数 （2）CHAR函数。语法格式： CHAR ( integer_expression )
常用系统标量函数 （2）CHAR函数。语法格式： CHAR ( integer_expression ) 将ASCII码转换为字符。integer_expression为介于0～255之间的整数，返回值为字符型。 （3）LEFT函数。语法格式： LEFT ( character_expression , integer_expression ) 返回从字符串左边开始指定个数的字符。参数character_expression为字符型表达式，integer_expression为整型表达式，返回值为varchar型。 【例5.26】 返回课程名最左边的4个字符。 SELECT LEFT(课程名, 4) FROM KCB ORDER BY 课程号

85 5.5.2 常用系统标量函数 （4）LTRIM函数。语法格式： LTRIM ( character_expression )
常用系统标量函数 （4）LTRIM函数。语法格式： LTRIM ( character_expression ) 删除character_expression字符串中的前导空格，并返回字符串。 【例5.27】 使用LTRIM字符删除字符变量中的起始空格。 varchar(40) = ' 中国，一个古老而伟大的国家 ' SELECT

86 5.5.2 常用系统标量函数 （5）REPLACE函数。语法格式：
常用系统标量函数 （5）REPLACE函数。语法格式： REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' ) 用第三个字符串表达式替换第一个字符串表达式中包含的第二个字符串表达式，并返回替换后的表达式。参数string_expression1、string_expression2和string_expression3均为字符串表达式。返回值为字符型。

87 5.5.2 常用系统标量函数 （6）SUBSTRING函数。语法格式：
常用系统标量函数 （6）SUBSTRING函数。语法格式： SUBSTRING ( expression , start , length ) 返回expression中指定的部分数据。参数expression可为字符串、二进制串、text、image字段或表达式；start、length均为整型，前者指定子串的开始位置，后者指定子串的长度（要返回字节数）。如果expression是字符类型和二进制类型，则返回值类型与expression的类型相同。其他情况参考表5.12。 表5.12 SUBSTRING函数返回值不同于给定表达式的情况 给定的表达式 返回值类型 text varchar image varbinary ntext nvarchar

88 5.5.2 常用系统标量函数 【例5.28】 如下程序在一列中返回XSB表中的姓氏，在另一列中返回表中学生姓名。
常用系统标量函数 【例5.28】 如下程序在一列中返回XSB表中的姓氏，在另一列中返回表中学生姓名。 SELECT SUBSTRING(姓名, 1,1), SUBSTRING(姓名, 2, LEN(姓名)-1) FROM XSB ORDER BY 姓名

89 5.5.2 常用系统标量函数 【例5.29】 显示字符串"China"中每个字符的ASCII值和字符。
常用系统标量函数 【例5.29】 显示字符串"China"中每个字符的ASCII值和字符。 char(8) = 1 <= BEGIN 1)) AS ASCII码, @position, 1))) AS 字符 + 1 END

90 5.5.2 常用系统标量函数 4．系统函数 系统函数用于对SQL Server中的值、对象和设置进行操作并返回有关信息。
常用系统标量函数 4．系统函数 系统函数用于对SQL Server中的值、对象和设置进行操作并返回有关信息。 （1）CAST和CONVERT函数。 CAST、CONVERT这两个函数的功能都是实现数据类型的转换，但CONVERT的功能更强一些。常用的类型转换有以下几种情况： 日期型→字符型 字符型→日期型 数值型→字符型。

91 5.5.2 常用系统标量函数 语法格式： CAST ( expression AS data_type[(length)])
常用系统标量函数 语法格式： CAST ( expression AS data_type[(length)]) CONVERT (data_type[(length)], expression [, style]) 说明：这两个函数将expression表达式的类型转换为data_type所指定的类型。 参数expression可为任何有效的表达式，data_type可为系统提供的基本类型，不能为用户自定义类型，当data_type 为nchar、nvarchar、char、varchar、binary或varbinary 等数据类型时，可以通过length参数指定长度。 对于不同的表达式类型转换，参数style的取值不同。

92 5.5.2 常用系统标量函数 style的常用取值及其作用如表5.13～表5.15所示。
常用系统标量函数 style的常用取值及其作用如表5.13～表5.15所示。 表5.13 日期型与字符型转换时style的常用取值及其作用 不带世纪数位（yy） 带世纪数位（yyyy） 标 准 输入/输出 0或100 默认值 mon dd yyyy hh:miAM(或 PM) 1 101 美国 mm/dd/yyyy 2 102 ANSI yy.mm.dd 9或109 默认值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM(或 PM) 10 110 mm-dd-yy 12 112 ISO Yymmdd

93 5.5.2 常用系统标量函数 表5.14 float 或 real转换为字符型数据时style的取值
常用系统标量函数 表5.14 float 或 real转换为字符型数据时style的取值 style值 输 出 0（默认值） 根据需要使用科学记数法，长度最多为6 1 使用科学记数法，长度为8 2 使用科学记数法，长度为16 表5.15 从money或smallmoney转换为字符数据时style的取值 值 输 出 0（默认值） 小数点左侧每3位数字之间不以逗号分隔，小数点右侧取两位数，例如 1 小数点左侧每3位数字之间以逗号分隔，小数点右侧取两位数，例如 3,510.92 2 小数点左侧每3位数字之间不以逗号分隔，小数点右侧取四位数，例如

94 5.5.2 常用系统标量函数 【例5.30】 如下程序将检索总学分在50～59分之间的学生姓名，并将总学分转换为char(20)。
常用系统标量函数 【例5.30】 如下程序将检索总学分在50～59分之间的学生姓名，并将总学分转换为char(20)。 /*使用CAST实现*/ USE PXSCJ GO SELECT 姓名, 总学分 FROM XSB WHERE CAST(总学分AS char(20)) LIKE '5_' AND 总学分>=50 /*使用 CONVERT实现*/ FROM XSB WHERE CONVERT(char(20), 总学分) LIKE '5_' AND总学分>=50

95 5.5.2 常用系统标量函数 （2）COALESCE函数。 语法格式： COALESCE ( expression [ ,...n ] )
常用系统标量函数 （2）COALESCE函数。 语法格式： COALESCE ( expression [ ,...n ] ) 返回参数表中第一个非空表达式的值，如果所有自变量均为NULL，则COALESCE返回NULL值。 所有表达式必须是相同类型的，或者可以隐性转换为相同的类型。 COALESCE(expression1,...n) 与如下形式的CASE 语句等价： CASE WHEN (expression1 IS NOT NULL) THEN expression1 ... WHEN (expressioN IS NOT NULL) THEN expressionN ELSE NULL

96 5.5.2 常用系统标量函数 （3）ISNUMBRIC函数。 ISNUMBRIC函数用于判断一个表达式是否为数值类型。 语法格式：
常用系统标量函数 （3）ISNUMBRIC函数。 ISNUMBRIC函数用于判断一个表达式是否为数值类型。 语法格式： ISNUMBRIC(expression)

97 5.5.2 常用系统标量函数 5．日期时间函数 （1）GETDATE函数。语法格式： GETDATE ()
常用系统标量函数 5．日期时间函数 （1）GETDATE函数。语法格式： GETDATE () 返回当前系统日期和时间。返回值类型为datetime。 （2）YEAR、MONTH、DAY函数。这三个函数分别返回指定日期的年、月、天部分，返回值都为整数。语法格式： YEAR(date) MONTH(date) DAY(date)

98 5.5.2 常用系统标量函数 6．游标函数 游标函数用于返回有关游标的信息。主要的游标函数如下。
常用系统标量函数 6．游标函数 游标函数用于返回有关游标的信息。主要的游标函数如下。 返回最后打开的游标中当前存在的满足条件的行数。 返回值为0表示游标未打开； 为-1表示游标为动态游标； 为-m表示游标被异步填充，返回值（-m）是键集中当前的行数； 为n表示游标已完全填充，返回值（n）是游标中的总行数。

99 5.5.2 常用系统标量函数 【例5.31】 声明一个游标，并用SELECT显示@@CURSOR_ROWS 的值。 USE PXSCJ GO
常用系统标量函数 【例5.31】 的值。 USE PXSCJ GO SELECT DECLARE student_cursor CURSOR FOR SELECT 姓名 FROM XSB OPEN student_cursor FETCH NEXT FROM student_cursor CLOSE student_cursor DEALLOCATE student_cursor

100 5.5.2 常用系统标量函数 （2）CURSOR_STATUS函数。语法格式： CURSOR_STATUS
常用系统标量函数 （2）CURSOR_STATUS函数。语法格式： CURSOR_STATUS (  { 'local' , 'cursor_name' } /*指明数据源为本地游标*/ | { 'global' , 'cursor_name' } /*指明数据源为全局游标*/ | { 'variable' , 'cursor_variable' } /*指明数据源为游标变量*/ ) 结果显示游标状态是打开还是关闭。 常量字符串local、global用于指定游标的类型，local表示为本地游标名，global表示为全局游标名。参数cursor_name用于指定游标名，常量字符串variable用于说明其后的游标变量为一个本地变量，参数cursor_variable为本地游标变量名称，返回值类型为smallint。

101 5.5.2 常用系统标量函数 CURSOR_STATUS()函数返回值如表5.16所示。 表5.16 CURSOR_STATUS返回值列表
常用系统标量函数 CURSOR_STATUS()函数返回值如表5.16所示。 表5.16 CURSOR_STATUS返回值列表 返 回 值 游标名或游标变量 1 游标的结果集至少有一行 -2 游标不可用 游标的结果集为空* -3 指定的游标不存在 -1 游标被关闭

102 5.5.2 常用系统标量函数 （3）@@FETCH_STATUS函数。语法格式： @@FETCH_STATUS
常用系统标量函数 表5.17 返 回 值 说 明 FETCH 语句执行成功 -2 被读取的记录不存在 -1 FETCH 语句执行失败

103 5.5.2 常用系统标量函数 【例5.32】 用@@FETCH_STATUS控制在一个WHILE循环中的游标活动。 USE PXSCJ GO
常用系统标量函数 【例5.32】 USE PXSCJ GO char(6) DECLARE Student_Cursor CURSOR FOR SELECT 姓名,学号 FROM PXSCJ.dbo.XSB OPEN Student_Cursor FETCH NEXT FROM @st_id WHILE = 0 BEGIN FETCH NEXT FROM Student_Cursor END CLOSE Student_Cursor DEALLOCATE Student_Cursor

104 5.5.2 常用系统标量函数 7．元数据函数 元数据是用于描述数据库和数据库对象的。元数据函数用于返回有关数据库和数据库对象的信息。
常用系统标量函数 7．元数据函数 元数据是用于描述数据库和数据库对象的。元数据函数用于返回有关数据库和数据库对象的信息。 （1）DB_ID函数。语法格式： DB_ID ( [ 'database_name' ] ) 系统在创建数据库时，自动为其创建一个标识号。 函数DB_ID根据database_name指定的数据库名，返回其数据库标识（ID）号。 如果参数database_name不指定，则返回当前数据库ID，返回值类型为smallint。

105 5.5.2 常用系统标量函数 （2）DB_NAME函数。语法格式： DB_NAME ( database_id )
常用系统标量函数 （2）DB_NAME函数。语法格式： DB_NAME ( database_id ) 根据参数database_id所给的数据库标识号，返回数据库名。 参数database_id 类型为 smallint，如果没有指定数据库标识号，则返回当前数据库名。返回值类型为nvarchar(128)。

106 5.6 用户定义函数 根据用户定义函数返回值的类型，可将用户定义函数分为如下两个类别。
5.6 用户定义函数 根据用户定义函数返回值的类型，可将用户定义函数分为如下两个类别。 （1）标量函数：用户定义函数返回值为标量值，这样的函数称为标量函数。 （2）表值函数：返回值为整个表的用户定义函数为表值函数。 根据函数主体的定义方式，表值函数又可分为: 内嵌表值函数:若用户定义函数包含单个SELECT语句且该语句可更新，则该函数返回的表也可更新，这样的函数称为内嵌表值函数； 多语句表值函数:若用户定义函数包含多个SELECT语句，则该函数返回的表不可更新，这样的函数称为多语句表值函数。

107 5.6.1 用户函数的定义与调用 1．标量函数 （1）标量函数的定义。语法格式：
用户函数的定义与调用 1．标量函数 （1）标量函数的定义。语法格式： CREATE FUNCTION [ schema_name. ] function_name /*函数名部分*/ ( [ [ AS ][ type_schema_name. ] parameter_data_type /*形参定义部分*/ [ = default ] [ READONLY ] } [ ,...n ] ]) RETURNS return_data_type /*返回参数的类型*/ [ WITH <function_option> [ ,...n ] ] /*函数选项定义*/ [ AS ] BEGIN function_body /*函数体部分*/ RETURN scalar_expression /*返回语句*/ END [ ; ] 其中， <function_option>::= { [ ENCRYPTION ] | [ SCHEMABINDING ] | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] }

108 5.6.1 用户函数的定义与调用 从上述语法形式可归纳出标量函数的一般定义形式如下： CREATE FUNCTION [所有者名.] 函数名
用户函数的定义与调用 从上述语法形式可归纳出标量函数的一般定义形式如下： CREATE FUNCTION [所有者名.] 函数名 ( 参数1 [AS] 类型1 [ = 默认值 ] ) [ ,...参数n [AS] 类型n [ = 默认值 ] ] ] ) RETURNS 返回值类型 [ WITH 选项 ] [ AS ] BEGIN 函数体 RETURN 标量表达式 END

109 5.6.1 用户函数的定义与调用 【例5.33】 创建用户定义函数，实现计算全体学生某门功课平均成绩的功能。 USE PXSCJ GO
用户函数的定义与调用 【例5.33】 创建用户定义函数，实现计算全体学生某门功课平均成绩的功能。 USE PXSCJ GO CREATE FUNCTION char(20)) RETURNS int AS BEGIN int ( SELECT avg(成绩) FROM CJB WHERE GROUP BY 课程号 ) END

110 5.6.1 用户函数的定义与调用 （2）标量函数的调用。当调用用户定义的标量函数时，必须提供至少由两部分组成的名称（架构名.函数名）。
用户函数的定义与调用 （2）标量函数的调用。当调用用户定义的标量函数时，必须提供至少由两部分组成的名称（架构名.函数名）。 可用以下方式调用标量函数。 ① 在SELECT语句中调用。 调用形式：架构名.函数名(实参1,…,实参n)。 实参可为已赋值的局部变量或表达式。

111 5.6.1 用户函数的定义与调用 【例5.34】 调用例5.33定义的函数average。
用户函数的定义与调用 【例5.34】 调用例5.33定义的函数average。 USE PXSCJ /*假设用户函数average在此数据库中已定义*/ GO char(20) /*定义局部变量*/ int SELECT @course1 = '101‘ /*给局部变量赋值*/ SELECT /*调用用户函数，并将返回值赋给局部变量*/ SELECT @aver1 AS '101课程的平均成绩'/*显示局部变量的值*/ 执行结果如下：

112 5.6.1 用户函数的定义与调用 ② 利用EXEC语句执行。 在用T-SQL EXECUTE（EXEC）语句调用用户函数时，调用形式：
用户函数的定义与调用 ② 利用EXEC语句执行。 在用T-SQL EXECUTE（EXEC）语句调用用户函数时，调用形式： 架构名.函数名 实参1,…,实参n 或 架构名.函数名 形参名1=实参1,…, 形参名n=实参n 【例5.35】 调用例5.33中计算平均成绩的函数。 char(20) int = dbo.average @cnum = '101' /*通过EXEC调用函数，将返回值赋给局部变量*/ AS '101课程的平均成绩‘ *显示局部变量的值*/

113 5.6.1 用户函数的定义与调用 【例5.36】 在PXSCJ中建立一个course表，并将一个字段定义为计算列。
用户函数的定义与调用 【例5.36】 在PXSCJ中建立一个course表，并将一个字段定义为计算列。 USE PXSCJ /*用户函数average在此数据库中已定义*/ GO CREATE TABLE course ( cno int, /*课程号*/ cname nchar(20), /*课程名*/ credit int, /*学分*/ aver AS /*将此列定义为计算列*/ dbo.average(cno) )

114 5.6.1 用户函数的定义与调用 2．内嵌表值函数 内嵌函数可用于实现参数化视图。例如，有如下视图：
用户函数的定义与调用 2．内嵌表值函数 内嵌函数可用于实现参数化视图。例如，有如下视图： CREATE VIEW View1 AS SELECT 学号, 姓名 FROM PXSCJ.dbo.XSB WHERE 专业= '计算机' 若希望设计更通用的程序，让用户能指定感兴趣的查询内容，可将“WHERE 专业= '计算机' ”替换为“WHERE 专业=

115 5.6.1 用户函数的定义与调用 由于视图不支持在WHERE子句中指定搜索条件参数，为解决这一问题，我们可使用内嵌用户定义函数，脚本如下：
用户函数的定义与调用 由于视图不支持在WHERE子句中指定搜索条件参数，为解决这一问题，我们可使用内嵌用户定义函数，脚本如下： /*内嵌函数的定义*/ CREATE FUNCTION nvarchar(30) ) RETURNS table AS RETURN ( SELECT 学号, 姓名 FROM PXSCJ.dbo.XSB WHERE ) GO /*内嵌函数的调用*/ SELECT * FROM fn_View1 ('计算机')

116 用户函数的定义与调用 执行结果如下：

117 5.6.1 用户函数的定义与调用 （1）内嵌表值函数的定义。语法格式如下：
用户函数的定义与调用 （1）内嵌表值函数的定义。语法格式如下： CREATE FUNCTION [ schema_name. ] function_name /*定义函数名部分*/ ( [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ]) /*定义参数部分*/ RETURNS TABLE /*返回值为表类型*/ [ WITH <function_option> [ ,...n ] ] /*定义函数的可选项*/ [ AS ] RETURN [ ( ] select_stmt [ ) ] /*通过SELECT语句返回内嵌表*/ [ ; ] RETURNS子句仅包含关键字TABLE，表示此函数返回一个表。

118 用户函数的定义与调用 【例5.37】 对于PXSCJ数据库，为了让学生查询其各科成绩及学分，可以利用XSB、KCB、CJB三个表创建视图。程序如下：  CREATE VIEW ST_VIEW AS SELECT dbo.XSB.学号, dbo.XSB.姓名, dbo.KCB.课程名, dbo.CJB.成绩 FROM dbo.KCB INNER JOIN dbo.CJB ON dbo.KCB.课程号 = dbo.CJB.课程号 INNER JOIN dbo.XSB ON dbo.CJB.学号 = dbo.XSB.学号 然后在此基础上定义如下内嵌函数： CREATE FUNCTION char(6)) RETURNS table AS RETURN ( SELECT * FROM PXSCJ.dbo.ST_VIEW WHERE dbo. )

119 5.6.1 用户函数的定义与调用 （2）内嵌表值函数的调用。内嵌表值函数只能通过SELECT语句调用，内嵌表值函数调用时，可以仅使用函数名。
用户函数的定义与调用 （2）内嵌表值函数的调用。内嵌表值函数只能通过SELECT语句调用，内嵌表值函数调用时，可以仅使用函数名。 在此，以前面定义的st_score()内嵌表值函数的调用作为应用举例，学生通过输入学号调用内嵌函数查询其成绩。 【例5.38】 调用st_score()函数，查询学号为081101的学生的各科成绩及学分。 SELECT * FROM PXSCJ.[dbo].st_score('081101') 执行结果如下：

120 5.6.1 用户函数的定义与调用 3．多语句表值函数 （1）多语句表值函数定义。 语法格式： 其中，
用户函数的定义与调用 3．多语句表值函数 （1）多语句表值函数定义。 语法格式： CREATE FUNCTION [ schema_name. ] function_name /*定义函数名部分*/ ( [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ]) /*定义函数参数部分*/ TABLE < table_type_definition > /*定义作为返回值的表*/ [ WITH <function_option> [ ,...n ] ] /*定义函数的可选项*/ [ AS ] BEGIN function_body /*定义函数体*/ RETURN END [ ; ] 其中， <table_type_definition>:: = /*定义表，参考第3章*/ ( { <column_definition> <column_constraint> } [ <table_constraint> ] [ ,...n ] )

121 用户函数的定义与调用 【例5.39】 在PXSCJ数据库中创建返回表的函数，通过以学号作为实参调用该函数，可显示该学生各门功课的成绩和学分。 CREATE FUNCTION char(6)) TABLE ( xs_ID char(6), xs_Name char(8), kc_Name char(16), cj tinyint, xf tinyint ) AS BEGIN SELECT S.学号, S.姓名,P.课程名,O.成绩, P.学分 FROM PXSCJ.[dbo].XSB AS S INNER JOIN PXSCJ.[dbo].CJB AS O ON (S.学号= O.学号) INNER JOIN PXSCJ.[dbo].KCB AS P ON (O.课程号= P.课程号) WHERE RETURN END

122 用户函数的定义与调用 （2）多语句表值函数的调用。多语句表值函数的调用与内嵌表值函数的调用方法相同。以下例子是上述多语句表值函数score_table()的调用。 【例5.40】 以下语句查询学号为081101的学生的各科成绩和学分。 SELECT * FROM PXSCJ.[dbo].score_table('081101') 执行结果如下：

123 5.6.2 用户定义函数的删除 对于一个已创建的用户定义函数，可有两种方法删除： （1）通过对象资源管理器删除。
用户定义函数的删除 对于一个已创建的用户定义函数，可有两种方法删除： （1）通过对象资源管理器删除。 （2）利用T-SQL语句DROP FUNCTION删除，下面介绍其语法格式。 语法格式： DROP FUNCTION { [ schema_name. ] function_name } [ ,...n ]