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

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

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

2 5.1 T-SQL语言简介 1．什么是T-SQL语言
T-SQL是SQL语言的一种版本，且只能在微软MS SQL-Server以及Sybase Adaptive Server系列数据库上使用。 T-SQL是ANSI SQL的扩展加强版语言，除了提供标准的SQL命令之外，T-SQL还对SQL做了许多补充，提供了类似C、BASIC和Pascal的基本功能，如变量说明、流控制语言、功能函数等。 2．T-SQL语言的构成 T-SQL增加的语言元素。这部分不是ANSI SQL所包含的内容，而是微软为了用户编程的方便而增加的语言元素。这些语言元素包括

3 5.2 常量、变量与数据类型 5.2.1 常量 1．字符串常量 字符串常量分为ASCII字符串常量和Unicode字符串常量。
5.2 常量、变量与数据类型 常量 1．字符串常量 字符串常量分为ASCII字符串常量和Unicode字符串常量。 （1）ASCII字符串常量。ASCII字符串常量是用单引号括起来，由ASCII字符构成的符号串。 ASCII字符串常量举例如下： 'China' 'How do you!' 'O''Bbaar' /*如果单引号中的字符串包含引号，可以使用两个单引号来表示嵌入的单引号。*/ （2）Unicode 字符串常量。Unicode 字符串常量与ASCII字符串常量相似，但它前面有一个N标识符（N代表 SQL-92标准中的国际语言National Language），N前缀必须为大写字母。 Unicode字符串常量举例如下： N'China ' N'How do you!'

4 5.2.1 常量 2．整型常量 十六进制整型常量的表示：前辍 0x 后跟十六进制数字串。 十六进制常量举例： 0xEBF
常量 2．整型常量 十六进制整型常量的表示：前辍 0x 后跟十六进制数字串。 十六进制常量举例： 0xEBF 0x69048AEFDD010E 0x /*空十六进制常量*/ 十进制整型常量即不带小数点的十进制数，例如， 1894 2

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

6 5.2.1 常量 4．日期时间常量 字母日期格式，如'April 20, 2000'；
常量 4．日期时间常量 字母日期格式，如'April 20, 2000'； 数字日期格式，如'4/15/1998'，' '； 未分隔的字符串格式，如' '； 以下是时间常量的例子： '14:30:24' '04:24:PM' 以下是日期时间常量的例子： 'April 20, :30:24'

7 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

8 5.2.2 数据类型 1．系统数据类型 2．用户自定义数据类型
数据类型 1．系统数据类型 2．用户自定义数据类型 用户自定义数据类型student_num后，可以重新设计学生成绩管理数据库表XSB、CJB结构中的学号字段，如表5.1、表5.2和表5.3所示。

9 数据类型 （1）使用对象资源管理器定义。 （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

10 5.2.2 数据类型 （3）删除用户自定义数据类型。在SSMS中删除用户自定义数据类型的主要步骤如下：
数据类型 （3）删除用户自定义数据类型。在SSMS中删除用户自定义数据类型的主要步骤如下： 在对象资源管理器中展开数据库“PXSCJ→可编程性→类型”，在“用户定义数据类型”中选择类型“dbo.student_num”，右击鼠标，在弹出的快捷菜单中选择“删除”菜单项，打开“删除对象”窗口后单击“确定”按钮即可。 （4）使用命令删除用户自定义数据类型。使用命令方式删除自定义数据类型可以使用DROP TYPE语句。语法格式： DROP TYPE [ schema_name. ] type_name [ ; ] 例如，删除前面定义的student_num类型的语句为 DROP TYPE student_num

11 数据类型 （5）利用用户自定义数据类型定义字段。 例如，在对象资源管理器中对于XSB表学号字段的定义如图5.2所示。

12 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 )

13 5.2.2 数据类型 3．用户自定义表数据类型 创建自定义表数据类型也使用CREATE TYPE语句，语法格式如下：
数据类型 3．用户自定义表数据类型 创建自定义表数据类型也使用CREATE TYPE语句，语法格式如下： CREATE TYPE [ schema_name. ] type_name AS TABLE ( <column_definition> [ <table_constraint> ] [ ,...n ] ) [ ; ] 【例5.1】 创建用户自定义表数据类型，包含CJB表中的所有列。 CREATE TYPE CJB_tabletype AS TABLE ( 学号 char(6) NOT NULL, 课程号 char(3) NOT NULL, 成绩 int NOT NULL, PRIMARY KEY(学号, 课程号) )

14 5.2.3 变量 1．变量 变量名必须是一个合法的标识符。 （1）标识符。 （2）变量的分类。 2．局部变量的使用
变量 1．变量 变量名必须是一个合法的标识符。 （1）标识符。 （2）变量的分类。 2．局部变量的使用 （1）局部变量的定义与赋值。 【例5.2】 char(10) char(30) SET @var1='中国' /*一个SET语句只能为一个变量赋值*/ SET @var2 执行结果如下：

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

16 5.2.3 变量 【例5.4】 使用查询为变量赋值。 DECLARE @student char(8)
变量 【例5.4】 使用查询为变量赋值。 char(8) 姓名 FROM XSB WHERE 学号= '081101') 用SELECT语句赋值： 语法格式： SELECT [,…n] 【例5.5】 使用SELECT语句为局部变量赋值。 nvarchar(30) ='刘丰' SELECT @var1 AS 'NAME' 执行结果如下：

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

18 5.2.3 变量 （2）局部游标变量的定义与赋值。 【例5.7】 使用游标变量。 USE PXSCJ GO
变量 （2）局部游标变量的定义与赋值。 【例5.7】 使用游标变量。 USE PXSCJ GO DECLARE @CursorVar CURSOR /*定义游标变量*/ SET @CursorVar = CURSOR SCROLL DYNAMIC /*为游标变量赋值*/ FOR SELECT 学号, 姓名 FROM XSB WHERE 姓名 LIKE '王%' /*打开游标*/ FETCH NEXT FETCH NEXT /*通过游标读行记录*/ /*删除对游标的引用*/

19 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 * /*查看内容*/

20 5.3 运算符与表达式 1．算术运算符 算术运算符在两个表达式上执行数学运算，这两个表达式可以是任何数字数据类型。
5.3 运算符与表达式 1．算术运算符 算术运算符在两个表达式上执行数学运算，这两个表达式可以是任何数字数据类型。 算术运算符有+（加）、-（减）、*（乘）、/（除）和%（求模）5种运算。+（加）和-（减）运算符还可用于对日期时间类型的值进行算术运算。 2．位运算符 位运算符在两个表达式之间执行位操作，这两个表达式的类型可为整型或与整型兼容的数据类型（如字符型等，但不能为image类型）。位运算符如表5.4所示。 运 算 符 运 算 规 则 & 两个位均为1时，结果为1，否则为0 | 只要一个位为1，则结果为1，否则为0 ^ 两个位值不同时，结果为1，否则为0

21 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 执行结果如下：

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

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

24 5.3 运算符与表达式 4．逻辑运算符 逻辑运算符用于对某个条件进行测试，运算结果为TRUE或FALSE。SQL Server提供的逻辑运算符如表5.6所示。 运 算 符 运 算 规 则 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

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

26 5.3 运算符与表达式 6．一元运算符 一元运算符有+（正）、-（负）和～（按位取反）三个。对于按位取反运算符举例如下：
5.3 运算符与表达式 6．一元运算符 一元运算符有+（正）、-（负）和～（按位取反）三个。对于按位取反运算符举例如下： 设a的值为12（ ），则～a的值为 。 7．赋值运算符 指给局部变量赋值的SET和SELECT语句中使用的“=”。 8．运算符的优先顺序 运算符优先级如表5.7所示。在一个表达式中按先高（优先级数字小）后低（优先级数字大）的顺序进行运算。 运 算 符 优 先 级 +（正）、-（负）、～（按位 NOT） 1 NOT 6 *（乘）、/（除）、%（模） 2 AND 7 +（加）、+（串联）、-（减） 3 ALL、ANY、BETWEEN、IN、LIKE、OR、SOME 8 =,  >,  <,  >=,  <=,  <>,  !=,  !>,  !< 比较运算符 4 =（赋值） 9 ^（位异或）、&（位与）、|（位或） 5

27 5.3 运算符与表达式 9．表达式 一个表达式就是常量、变量、列名、复杂计算、运算符和函数的组合。一个表达式通常可以得到一个值。与常量和变量一样，一个表达式的值也具有某种数据类型，可能的数据类型有字符类型、数值类型、日期时间类型。这样根据表达式的值的类型，表达式可分为字符型表达式、数值型表达式和日期时间型表达式。

28 5.4 流程控制语句 在SQL Server中提供了如表5.8所示的流程控制语句。 控 制 语 句 说 明 BEGIN…END 语句块
5.4 流程控制语句 在SQL Server中提供了如表5.8所示的流程控制语句。 控 制 语 句 说 明 BEGIN…END 语句块 CONTINUE 用于重新开始下一次循环 IF…ELSE 条件语句 BREAK 用于退出最内层的循环 CASE 分支语句 RETURN 无条件返回 GOTO 无条件转移语句 WAITFOR 为语句的执行设置延迟 WHILE 循环语句

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

30 BEGIN…END语句块 在T-SQL中可以定义BEGIN…END语句块。当要执行多条T-SQL语句时，就需要使用BEGIN…END将这些语句定义成一个语句块，作为一组语句来执行。语法格式如下： BEGIN { sql_statement | statement_block } END 关键字BEGIN是T-SQL语句块的起始位置，END标识同一个T-SQL语句块的结尾。sql_statement是语句块中的T-SQL语句。BEGIN…END可以嵌套使用，statement_block表示使用BEGIN…END定义的另一个语句块。例如， USE PXSCJ GO SELECT * FROM XSB SELECT * FROM KCB

31 5.4.2 条件语句 语法格式： IF Boolean_expression /*条件表达式*/
条件语句 语法格式： IF Boolean_expression /*条件表达式*/ { sql_statement | statement_block } /*条件表达式为真时执行*/ [ ELSE { sql_statement | statement_block } ] /*条件表达式为假时执行*/ （1）带ELSE部分： IF 条件表达式 A /* T-SQL语句或语句块*/ ELSE B /*T-SQL语句或语句块*/ （2）不带ELSE部分： A /*T-SQL语句或语句块*/

32 条件语句 IF语句的执行流程如图5.3所示。如果在IF…ELSE语句的IF区和ELSE区都使用了 CREATE TABLE 语句或SELECT INTO 语句，那么CREATE TABLE 语句或SELECT INTO语句必须使用相同的表名。

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

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

35 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 ]

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

37 5.4.4 无条件转移语句 语法格式： GOTO label label是指向的语句标号，标号必须符合标识符规则。标号的定义形式为
无条件转移语句 语法格式： GOTO label label是指向的语句标号，标号必须符合标识符规则。标号的定义形式为 label : 语句

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

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

40 循环语句 2．BREAK语句 语法格式： BREAK 3．CONTINUE语句 CONTINUE

41 5.4.6 返回语句 语法格式： RETURN [ integer_expression ]
返回语句 语法格式： RETURN [ integer_expression ] 【例5.16】 判断是否存在学号为081128的学生，如果存在则返回，不存在则插入081128号学生的信息。 IF EXISTS(SELECT * FROM XSB WHERE 学号='081128') RETURN ELSE INSERT INTO XSB VALUES('081128', '张可', 1, ' ', '计算机',52, NULL)

42 5.4.7 等待语句 语法格式： WAITFOR { DELAY 'time_to_pass'
等待语句 语法格式： WAITFOR { DELAY 'time_to_pass' | TIME 'time_to_execute' } 【例5.17】 如下语句设定在早上8点执行查询语句。 BEGIN WAITFOR TIME '8:00' SELECT * FROM XSB END

43 5.4.8 错误处理语句 语法格式： BEGIN TRY { sql_statement | statement_block }
错误处理语句 语法格式： BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH [ { sql_statement | statement_block } ] END CATCH [ ; ]

44 5.5 系统内置函数 5.5.1 数学函数 SQL Server中常用的数学函数如表5.9所示。 下面给出几个例子说明数学函数的使用。
5.5 系统内置函数 数学函数 SQL Server中常用的数学函数如表5.9所示。 下面给出几个例子说明数学函数的使用。 （1）ABS函数。语法格式： ABS(numeric_expression) 返回给定数字表达式的绝对值。参数numeric_expression为数字型表达式（bit数据类型除外），返回值类型与numeric_expression相同。 【例5.18】 显示ABS 函数对三个不同数字的效果。 SELECT ABS(5.0), ABS(0.0), ABS(8.0) 运行结果如下：

45 5.5.1 数学函数 （2）RAND函数。语法格式： RAND ([ seed ] )
数学函数 （2）RAND函数。语法格式： RAND ([ seed ] ) 返回0～1之间的一个随机值。参数seed是指定种子值的整型表达式，返回值类型为float。如果未指定seed，则随机分配种子值。对于指定的种子值，返回的结果始终相同。 【例5.19】 以下程序通过RAND函数返回随机值。 int = 5 SELECT

46 5.5.2 字符串处理函数 SQL Server中常用的字符串函数如表5.10所示。 （1）ASCII函数。语法格式：
字符串处理函数 SQL Server中常用的字符串函数如表5.10所示。 （1）ASCII函数。语法格式： ASCII ( character_expression ) 返回字符表达式最左端字符的ASCII值。参数character_expression的类型为字符型的表达式，返回值为整型。 【例5.20】 查找字符串'sql'最左端字符的ASCII值。 SELECT ASCII('sql') 执行结果如下： （2）CHAR函数。语法格式： CHAR ( integer_expression ) 将ASCII码转换为字符。integer_expression为介于0～255之间的整数，返回值为字符型。

47 5.5.2 字符串处理函数 （3）LEFT函数。语法格式：
字符串处理函数 （3）LEFT函数。语法格式： LEFT ( character_expression , integer_expression ) 返回从字符串左边开始指定个数的字符。参数character_expression为字符型表达式，integer_expression为整型表达式，返回值为varchar型。 【例5.21】 返回课程名最左边的4个字符。 SELECT LEFT(课程名, 4) FROM KCB ORDER BY 课程号

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

49 5.5.2 字符串处理函数 （5）REPLACE函数。语法格式：
字符串处理函数 （5）REPLACE函数。语法格式： REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' ) 用第三个字符串表达式替换第一个字符串表达式中包含的第二个字符串表达式，并返回替换后的表达式。参数string_expression1、string_expression2和string_expression3均为字符串表达式。返回值为字符型。 （6）SUBSTRING函数。语法格式： SUBSTRING ( expression , start , length ) 返回expression中指定的部分数据。参数expression可为字符串、二进制串、text、image字段或表达式；start、length均为整型，前者指定子串的开始位置，后者指定子串的长度（要返回字节数）。如果expression是字符类型和二进制类型，则返回值类型与expression的类型相同。其他情况参考表5.11。 给定的表达式 返回值类型 text varchar image varbinary ntext nvarchar

50 5.5.2 字符串处理函数 【例5.23】 如下程序在一列中返回XSB表中的姓氏，在另一列中返回表中学生姓名。
字符串处理函数 【例5.23】 如下程序在一列中返回XSB表中的姓氏，在另一列中返回表中学生姓名。 SELECT SUBSTRING(姓名, 1,1), SUBSTRING(姓名, 2, LEN(姓名)-1) FROM XSB ORDER BY 姓名 【例5.24】 显示字符串"China"中每个字符的ASCII值和字符。 char(8) = 1 <= BEGIN 1)) AS ASCII码, @position, 1))) AS 字符 + 1 END

51 5.5.3 系统函数 （1）CAST和CONVERT函数。
系统函数 （1）CAST和CONVERT函数。 CAST、CONVERT这两个函数的功能都是实现数据类型的转换，但CONVERT的功能更强一些。常用的类型转换有以下几种情况：日期型→字符型、字符型→日期型、数值型→字符型。 语法格式： CAST ( expression AS data_type[(length)]) CONVERT (data_type[(length)], expression)

52 5.5.3 系统函数 【例5.25】 如下程序将检索总学分在50～59分之间的学生姓名，并将总学分转换为char(20)。
系统函数 【例5.25】 如下程序将检索总学分在50～59分之间的学生姓名，并将总学分转换为char(20)。 /*如下例子同时使用 CAST 和 CONVERT*/ /*使用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

53 5.5.3 系统函数 （2）COALESCE函数。 语法格式： COALESCE ( expression [ ,...n ] )
系统函数 （2）COALESCE函数。 语法格式： COALESCE ( expression [ ,...n ] ) COALESCE(expression1,...n) 与如下形式的CASE 语句等价： CASE WHEN (expression1 IS NOT NULL) THEN expression1 ... WHEN (expressioN IS NOT NULL) THEN expressionN ELSE NULL （3）ISNUMBRIC函数。 ISNUMBRIC函数用于判断一个表达式是否为数值类型。 ISNUMBRIC(expression)

54 日期时间函数 日期时间函数可用在SELECT语句的选择列表或用在查询的WHERE子句中。表5.12中列出了SQL Server中常用的日期时间函数。 函数名称 语法格式 说 明 DATEADD DATEADD(<日期格式>,<相加时间 >,<日期>) 在向指定日期加上一段时间的基础 上，返回新的日期时间值。 DATEDIFF DATEDIFF(<日期格式>,<开始日期 >,<结束日期>) 返回跨两个指定日期的日期和时间 边界数。 DATENAME DATENAME(<日期格式>,<日期>) 返回代表指定日期的指定日期部分 的字符串。 DATEPART DATEPART(<日期格式>,<日期>) 返回代表指定日期的指定日期部分 的整数。 DAY DAY(<日期>) 返回代表指定日期的天的日期部分 的整数。 GETDATE GETDATE() 按datetime值的SQL Server标准内部 格式返回当前系统日期和时间。 GETUTCDATE GETUTCDATE() 返回表示当前UTC时间（世界时间 坐标或格林威治标准时间）的datetime 值。当前的UTC时间应从当前的本地时 间和运行SQL Server的计算机操作系统 的时区设置。 MONTH MONTH(<日期>) 返回表示指定日期中的月份的整数。 YEAR YEAR(<日期>) 返回表示指定日期中的年份的整数。

55 5.5.4 日期时间函数 （1）GETDATE函数。语法格式： GETDATE ()
日期时间函数 （1）GETDATE函数。语法格式： GETDATE () 按SQL Server标准内部格式返回当前系统日期和时间。返回值类型为datetime。 （2）YEAR、MONTH、DAY函数。这三个函数分别返回指定日期的年、月、天部分，返回值都为整数。语法格式： YEAR(date) MONTH(date) DAY(date)

56 5.5.5 游标函数 （1）@@CURSOR_ROWS函数。语法格式： @@CURSOR_ROWS
游标函数 【例5.26】 的值。 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

57 5.5.5 游标函数 （2）CURSOR_STATUS函数。语法格式： CURSOR_STATUS
游标函数 （2）CURSOR_STATUS函数。语法格式： CURSOR_STATUS (  { 'local' , 'cursor_name' } /*指明数据源为本地游标*/ | { 'global' , 'cursor_name' } /*指明数据源为全局游标*/ | { 'variable' , 'cursor_variable' } /*指明数据源为游标变量*/ ) CURSOR_STATUS()函数返回值如表5.13所示。 返 回 值 游标名或游标变量 1 游标的结果集至少有一行 -2 游标不可用 游标的结果集为空* -3 指定的游标不存在 -1 游标被关闭

58 5.5.5 游标函数 （3）@@FETCH_STATUS函数。语法格式： @@FETCH_STATUS
游标函数 返 回 值 说 明 FETCH 语句执行成功 -2 被读取的记录不存在 -1 FETCH 语句执行失败

59 5.5.5 游标函数 【例5.27】 用@@FETCH_STATUS控制在一个WHILE循环中的游标活动。 USE PXSCJ GO
游标函数 【例5.27】 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

60 5.5.6 元数据函数 （1）DB_ID函数。语法格式： DB_ID ( [ 'database_name' ] )
元数据函数 （1）DB_ID函数。语法格式： DB_ID ( [ 'database_name' ] ) 系统在创建数据库时，自动为其创建一个标识号。函数DB_ID根据database_name指定的数据库名，返回其数据库标识（ID）号。 （2）DB_NAME函数。语法格式： DB_NAME ( database_id ) 根据参数database_id所给的数据库标识号，返回数据库名。参数database_id 类型为 smallint，如果没有指定数据库标识号，则返回当前数据库名。

61 5.6 用户定义函数 5.6.1 用户函数的定义与调用 1．标量函数 （1）标量函数的定义。语法格式：
5.6 用户定义函数 用户函数的定义与调用 1．标量函数 （1）标量函数的定义。语法格式： CREATE FUNCTION [ schema_name. ] function_name /*函数名部分*/ ( [ [ AS ][ type_schema_name. ] parameter_data_type /*形参定义部分*/ [ = default ] [ READONLY ] } [ ,...n ] ]) RETURNS return_data_type /*返回参数的类型*/ [ WITH ENCRYPTION ] /*函数选项定义*/ [ AS ] BEGIN function_body /*函数体部分*/ RETURN scalar_expression /*返回语句*/ END [ ; ]

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

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

64 5.6.1 用户函数的定义与调用 （2）标量函数的调用。当调用用户定义的标量函数时，必须提供至少由两部分组成的名称（架构名.函数名）。
用户函数的定义与调用 （2）标量函数的调用。当调用用户定义的标量函数时，必须提供至少由两部分组成的名称（架构名.函数名）。 【例5.29】 调用例5.38定义的函数average。 USE PXSCJ /*假设用户函数average在此数据库中已定义*/ GO char(20) /*定义局部变量*/ int SELECT @course1 = '101' /*给局部变量赋值*/ SELECT /*调用用户函数，并将返回值赋给局部变量*/ SELECT @aver1 AS '101课程的平均成绩' /*显示局部变量的值*/ 执行结果如下：

65 5.6.1 用户函数的定义与调用 【例5.30】 调用例5.28中计算平均成绩的函数。 DECLARE @course1 char(20)
用户函数的定义与调用 【例5.30】 调用例5.28中计算平均成绩的函数。 char(20) int = dbo.average @cnum = '101' /*显示局部变量的值*/ AS '101课程的平均成绩' /*通过EXEC调用函数，将返回值赋给局部变量*/ 【例5.31】 在PXSCJ中建立一个course表，并将一个字段定义为计算列。 USE PXSCJ /*用户函数average在此数据库中已定义*/ GO CREATE TABLE course ( cno int, /*课程号*/ cname nchar(20), /*课程名*/ credit int, /*学分*/ aver AS /*将此列定义为计算列*/ dbo.average(cno) )

66 5.6.1 用户函数的定义与调用 2．内嵌表值函数 内嵌函数可用于实现参数化视图。例如，有如下视图：
用户函数的定义与调用 2．内嵌表值函数 内嵌函数可用于实现参数化视图。例如，有如下视图： CREATE VIEW View1 AS SELECT 学号, 姓名 FROM PXSCJ.dbo.XSB WHERE 专业= '计算机'

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

68 5.6.1 用户函数的定义与调用 （1）内嵌表值函数的定义。语法格式如下：
用户函数的定义与调用 （1）内嵌表值函数的定义。语法格式如下： CREATE FUNCTION [ schema_name. ] function_name /*定义函数名部分*/ ( [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ]) /*定义参数部分*/ RETURNS TABLE /*返回值为表类型*/ [ AS ] RETURN [ ( ] select_stmt [ ) ] /*通过SELECT语句返回内嵌表*/ [ ; ]

69 用户函数的定义与调用 【例5.32】 对于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. )

70 5.6.1 用户函数的定义与调用 （2）内嵌表值函数的调用。内嵌表值函数只能通过SELECT语句调用，内嵌表值函数调用时，可以仅使用函数名。
用户函数的定义与调用 （2）内嵌表值函数的调用。内嵌表值函数只能通过SELECT语句调用，内嵌表值函数调用时，可以仅使用函数名。 【例5.33】 调用st_score()函数，查询学号为081101的学生的各科成绩及学分。 SELECT * FROM PXSCJ.[dbo].st_score('081101')

71 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 > /*定义作为返回值的表*/ [ AS ] BEGIN function_body /*定义函数体*/ RETURN END [ ; ] 其中， <table_type_definition>:: = /*定义表，参考第3章*/ ( { <column_definition> <column_constraint> } [ <table_constraint> ] [ ,...n ] )

72 用户函数的定义与调用 【例5.34】 在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

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

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