数据库系统管理(王岚主编)第六章 PPT课件

数据库基础 1 第六章T SQL语言程序设计 数据库基础 2 本章要点 本章将介绍T SQL Transact SQL 程序设计的基本知识 T SQL作为嵌入在SQLServer中的结构化查询语言 对标准SQL进行了扩展 功能强大 简单易学 同其他程序设计语言一样 T SQL也有自己的数据类型 运算符 表达式以及流程控制语句等相关语法要求 本章将对这些内容进行详细的介绍 数据库基础 3 第六章T SQL语言程序设计 6 1数据类型 常量和变量6 2运算符和表达式6 3流程控制语句6 4函数 数据库基础 4 6 1数据类型 常量和变量 6 1 1数据类型6 1 2常量6 1 3变量 数据库基础 5 6 1 1数据类型 从大的方面来说SQLServer2005中包括系统数据类型和用户自定义数据类型 在前面的章节中对系统数据类型已经进行了详细的介绍 这部分将只介绍用户自定义数据类型 用户自定义数据类型基于系统提供的基本数据类型 并不是一种真正意义上的数据类型 只是提供了一种提高数据库内部元素和基本数据类型之间一致性的机制 通过使用用户自定义数据类型能够简化对常用规则和默认值的管理 数据库基础 6 1 创建用户自定义数据类型 在SQLServer中可利用系统存储过程和使用图形化界面两种方式来创建用户自定义数据类型 1 使用系统存储过程来创建用户自定义数据类型命令格式如下 sp addtype typename type phystype system data type nulltype null type owner owner name 数据库基础 7 1 创建用户自定义数据类型 各参数的含义如下 typename type 用户自定义数据类型的名称 phystype system data type 用户定义的数据类型所基于的系统数据类型或SQLServer提供的数据类型 nulltype null type 用户自定义数据类型处理空值的方式 null type的取值为NULL NOTNULL或NONULL 默认值为NULL owner owner name 指定新数据类型的创建者或所有者 若没有指定 则为当前用户 数据库基础 8 1 创建用户自定义数据类型 例6 1为图书管理数据库创建一个不允许为空值的book code用户自定义数据类型 USE图书管理GOEXECsp addtypebook code char 8 NOTNULL GO需要说明的是 在char 8 上加了单引号 是因为它包含了标点符号 括号 数据库基础 9 1 创建用户自定义数据类型 2 采用图形化界面来创建用户自定义数据类型仍以为图书管理数据库创建一个不允许为空值的book code用户自定义数据类型为例进行讲解 步骤如下 1 打开MicrosoftSQLServerManagementStudio窗口 对象资源管理器窗口 数据库 图书管理 可编程性 类型 右击类型节点 新建 用户定义数据类型 2 如图6 1所示 打开新建用户定义数据类型窗口 在名称字段输入book code 在数据类型下拉列表中选择char数据类型 在长度框中输入8 保留允许空值为取消复选状态 数据库基础 10 1 创建用户自定义数据类型 图6 1新建用户自定义数据类型窗口 数据库基础 11 1 创建用户自定义数据类型 3 设置完毕后 单击确定按钮 则创建了用户定义数据类型book code 数据库基础 12 2 删除用户自定义数据类型 对于不再使用的用户定义数据类型 可以将其删除 同创建时一样 删除用户自定义数据类型也可以采用系统存储过程和图形化界面两种方式 数据库基础 13 2 删除用户自定义数据类型 1 使用系统存储过程删除用户定义数据类型命令格式如下 sp droptype typename typetype用户自定义数据类型的名称 数据库基础 14 2 删除用户自定义数据类型 例6 2删除book code用户定义数据类型 USE图书管理GOEXECsp droptypebook codeGO 数据库基础 15 2 删除用户自定义数据类型 2 使用图形化界面删除用户定义数据类型 步骤如下 1 在MicrosoftSQLServerManagementStudio中找到需要删除的用户定义数据类型 在这里的具体操作为打开MicrosoftSQLServerManagementStudio窗口 对象资源管理器窗口 数据库 图书管理 可编程性 类型 用户定义数据类型 book code 右击选择删除 2 出现如图6 2所示的删除对象窗口 单击确定按钮 则将其删除 数据库基础 16 2 删除用户自定义数据类型 图6 2删除对象窗口 数据库基础 17 6 1数据类型 常量和变量 6 1 1数据类型6 1 2常量6 1 3变量 数据库基础 18 6 1 2常量 在程序运行中值保持不变的数据称为常量 常量实际上是表示特定数据值的符号 格式取决于具体的数据类型 通常分为 字符串常量 整型常量 日期时间常量 实型常量 货币常量和全局唯一标识符 数据库基础 19 1 字符串常量 字符串常量分为ASCII字符串常量和Unicode字符串常量 ASCII字符串常量括在单引号内并包含字母数字字符 a z A Z和0 9 以及特殊字符 如感叹号 at符 和数字号 等 数据库基础 20 1 字符串常量 如果已为某个连接将QUOTED IDENTIFIER选项设置成OFF 则字符串也可以使用双引号括起来 但MicrosoftSQL本机客户端提供程序和ODBC驱动程序将自动使用SETQUOTED IDENTIFIERON 因此建议使用单引号 如果单引号中的字符串包含一个嵌入的引号 可以使用两个单引号表示嵌入的单引号 对于嵌入在双引号中的字符串则不需要这样做 空字符串用中间没有任何字符的两个单引号表示 数据库基础 21 1 字符串常量 引号表示 ASCII字符串常量示例 hello your sbook Xis50 数据库基础 22 1 字符串常量 Unicode字符串的格式与ASCII字符串相似 但它前面有一个N标识符 N代表SQL 92标准中的区域语言 并且N前缀必须是大写字母 将上面的ASCII字符串常量改为Unicode字符串常量 则如下所示 N hello N your sbook N Xis50 数据库基础 23 2 整型常量 按照整型常量的不同表示方式 又可分为二进制整型常量和十进制整型常量 1 二进制整型常量二进制常量具有前辍0 x 通常采用十六进制数字字符串表示 这些常量不使用引号括起 例如 0 xEA0 x23EF0 x 表示空的常量 数据库基础 24 2 整型常量 2 十进制整型常量十进制整型常量即不带小数点的十进制数 且不需要使用引号括起来 例如 2376 1200 3775 数据库基础 25 3 日期时间常量 日期时间常量用单引号将表示日期时间的字符串括起来构成 SQLServer中提供了专门的日期和时间数据类型 可以识别多种格式的日期和时间 字母日期格式 例 July20 2009 20 July 2009 数字日期格式 例 5 12 2008 2008 08 25 未分隔的字符串格式 例 20021207 常见的时间格式如下 10 11 06 08 25AM 数据库基础 26 4 实型常量 实型常量有定点表示和浮点表示两种方式 不需要用单引号括起来 1 定点表示的实型常量由包含小数点的数字字符串组成 例如 1123 6204 23464545 2234 1132456432 10 数据库基础 27 4 实型常量 2 浮点表示的实型常量使用科学记数法来表示 例如 305 5E5 223E 3 42E5 数据库基础 28 5 货币常量 货币常量即为money常量 是以 作为前缀的一个整型或实型常量数据 不需扩在单引号中 例如 642923 370 59 66578 733 数据库基础 29 6 全局唯一标识符 全局唯一标识符是SQLServer根据计算机网络适配器地址和主机CPU时钟产生的唯一号码生成的 可以使用字符或二进制字符串格式指定 例如 6F9619FF 8B86 D011 B42D 00C04FC964FF 0 xff19966f868b11d0b42d00c04fc964ff 数据库基础 30 6 1数据类型 常量和变量 6 1 1数据类型6 1 2常量6 1 3变量 数据库基础 31 6 1 3变量 变量就是在程序执行过程中其值可以改变的量 可以利用变量存储程序执行过程中涉及到的数据 如接收用户输入的数值 计算的结果等 声明变量时需要指定变量名及数据类型 变量名用于标识该变量 数据类型确定了该变量存放值的格式及允许的运算 数据库基础 32 6 1 3变量 为变量命名应使用常规标识符 即以字母 下划线 at符号 或数字符号 开头 后续接字母 数字 at符号 美元符号 下划线的字符序列 不允许嵌入空格或其他特殊字符 数据库基础 33 6 1 3变量 Transact SQL语言中的变量有两种 一种是全局变量 它们是由系统预先定义好的 另外一种是局部变量 是由用户根据需要自己定义的 数据库基础 34 1 全局变量 全局变量以两个 字符开头 由系统定义和维护 用户只能使用全局变量 不能对它们进行修改 实际上他们不是变量 不具备变量的行为 而是系统函数 语法遵循函数的规则 为了和以前的版本兼容 这里仍称为全局变量 表6 1中列出了几个在SQLServer中常用的全局变量 数据库基础 35 1 全局变量 表6 1SQLServer中常用的全局变量 数据库基础 36 1 全局变量 例6 3使用 VERSION查看SQLServer版本信息 结果如图6 3所示 Select VERSION 数据库基础 37 1 全局变量 图6 3查看版本信息 数据库基础 38 1 全局变量 例6 4使用 ROWCOUNT查看前一条命令影响的行数 结果如图6 4所示 Select ROWCOUNT 数据库基础 39 1 全局变量 图6 4使用 ROWCOUNT查看前一条命令影响的行数 数据库基础 40 2 局部变量 局部变量是用户在使用T SQL语言的过程中根据需要定义的 它的作用范围仅限于定义此变量的过程内部 使用DECLARE语句定义局部变量 并在其名称前加上 标志 定义局部变量的语法形式如下 DECLARE local variabledata type n 数据库基础 41 2 局部变量 其中 local variable 用于指定局部变量的名称 data type 用于设置局部变量的数据类型及其大小 可以是由系统提供数据类型 也可以为用户定义的数据类型 但不能是text ntext或image数据类型 数据库基础 42 2 局部变量 创建局部变量之后 初始值为NULL 如果想要设定局部变量的值 必须使用SET命令或者SELECT命令 其语法形式为 SET local variable expression 或者SELECT local variable expression n 其中 参数 local variable是给其赋值并声明的局部变量 参数expression是任何有效的SQLServer2005表达式 数据库基础 43 2 局部变量 例6 5创建局部变量 int s并赋值 然后输出其值 结果如图6 5所示 declare int sintset int s 100select int s 数据库基础 44 2 局部变量 图6 5使用set为局部变量赋值 数据库基础 45 2 局部变量 例6 6查询读者表 将返回的记录数赋给局部变量 M count 并显示结果 结果如图6 6所示 declare M countintselect M count count from读者select M count 数据库基础 46 2 局部变量 图6 6使用select为局部变量赋值 数据库基础 47 第六章T SQL语言程序设计 6 1数据类型 常量和变量6 2运算符和表达式6 3流程控制语句6 4函数 数据库基础 48 6 2运算符和表达式 运算符是一种符号 实现了运算功能 能将数据按照运算符的功能定义进行计算 产生新的结果 在SOLServer2005中运算符可以分为如下几种 算术运算符 赋值运算符 位运算符 比较运算符 逻辑运算符 字符串连接运算符和一元运算符 在SOLServer2005中表达式可以分为简单表达式和复杂表达式两种 简单表达式可以只是一个常量 变量 列名或函数 复杂表达式是由运算符连接起来的两个或多个简答表达式构成的 数据库基础 49 6 2运算符和表达式 6 2 1算数运算符6 2 2赋值运算符6 2 3位运算符6 2 4比较运算符6 2 5逻辑运算符6 2 6字符串连接运算符6 2 7一元运算符6 2 8运算符的优先级 数据库基础 50 6 2 1算数运算符 算术运算符对两个表达式执行数学运算 这两个表达式可以是任何的数值数据类型 算术运算符包括 加 减 乘 除 和 取模 五种运算 其中 加 和 减 运算符也可用于对datetime及smalldatetime值进行算术运算 数据库基础 51 6 2 1算数运算符 例6 7将图书表中单价大于20的书籍按九折重新计算单价 SELECT书名 单价 单价 0 9实际价格FROM图书WHERE单价 20 数据库基础 52 6 2 1算数运算符 运行结果如图6 7所示 本例通过算术运算计算出了单价大于20的书籍的实际价格 如果表达式中有多个算术运算符 则先计算乘 除 然后是取模运算 最后是加 减运算 如果表达式中的算术运算符都有相同的优先顺序 则按从左到右进行计算 括号中的表达式比其他所有运算都要优先进行计算 数据库基础 53 6 2 1算数运算符 图6 7使用乘运算计算实际价格 数据库基础 54 6 2 2赋值运算符 等号 是T SQL中唯一的赋值运算符 在下面的示例中 将创建一个 intNum变量 然后赋值运算符将 intNum设置为表达式返回的值 数据库基础 55 6 2 2赋值运算符 例6 8使用赋值运算符为变量赋值 DECLARE intNumintSET intNum 5 32 7 SELECT intNum执行后 intNum中的值为195 数据库基础 56 6 2 3位运算符 位运算符在两个表达式之间执行位操作 这两个表达式的类型可以是整型数据或二进制数据 image数据类型除外 但要求在位运算符左右两侧的操作数不能同时是二进制数据 位运算符包括 按位与 按位或 按位异或 如表6 2所示 数据库基础 57 6 2 3位运算符 表6 2位运算符 数据库基础 58 6 2 3位运算符 例6 9对两个整型变量 intNum1和 intNum2进行按位运算 DECLARE intNum1int intNum2intSET intNum1 8 SET intNum2 2SELECT intNum1 intNum2与运算 intNum1 intNum2或运算 intNum1 intNum2异或运算在T SQL中先把整形数据转换乘二进制数据 然后再进行按位运算 结果如图6 8 数据库基础 59 6 2 3位运算符 图6 8位运算 数据库基础 60 6 2 4比较运算符 比较运算符 又称关系运算符 用于测试两个表达式的值之间的关系 其运算结果为布尔值 可以为三种之一 TRUE FALSE及UNKNOWN 当SETANSI NULLS为ON时 带有一个或两个NULL表达式的运算符返回UNKNOWN 当SETANSI NULLS为OFF时 上述规则同样适用 但当两个表达式均为NULL时 则等号 运算符返回TRUE 如表6 3所示 位SQLServer2005中可以使用的比较运算符 数据库基础 61 6 2 4比较运算符 表6 3比较运算符 数据库基础 62 6 2 4比较运算符 例6 10查询单价大于15小于30的图书的详细信息 SELECT FROM图书WHERE单价 15and单价 30 数据库基础 63 6 2 5逻辑运算符 逻辑运算符用于对某些条件进行测试 运算结果返回带有TRUE或FALSE的布尔型值 如表6 4所示 为SQLServer2005中提供的逻辑运算符 数据库基础 64 6 2 5逻辑运算符 表6 4逻辑运算符 数据库基础 65 6 2 5逻辑运算符 例6 11挑选出由清华大学出版社或者南开大学出版社出版的图书 SELECT FROM图书WHERE出版社IN 清华大学出版社 南开大学出版社 数据库基础 66 6 2 6字符串连接运算符 在SQLServer2005中 允许使用字符串连接运算符加号 对两个或多个字符串进行串联 例6 11字符串连接 DECLARE vch1varchar 20 vch2varchar 20 SET vch1 hello SET vch2 world SELECT vch1 vch2运行结果显示为 helloworld 数据库基础 67 6 2 7一元运算符 一元运算符只对一个表达式执行操作 该表达式可以是任何数字数据类型 包括 正 负 和 按位取反 三个运算符 其中需要说明的是按位取反运算符返回一个数的补数 正能用于整数数据 数据库基础 68 6 2 7一元运算符 例6 12对变量 num进行一元运算 DECLARE numintSET num 48SELECT num取正 num取负 num取反运行结果如图6 9所示 数据库基础 69 6 2 7一元运算符 图6 9一元运算 数据库基础 70 6 2 8运算符的优先级 当一个复杂的表达式有多个运算符时 运算符优先级决定执行运算的先后次序 执行的顺序会影响到运算的结果 表6 5中给出了运算符的优先顺序 在一个表达式中按先高 优先级数字小 后低 优先级数字大 的顺序进行运算 相同优先级的运算按自左向右的顺序进行 括号可以改变运算符的优先性 表达式中如果有括号 应先对括号内的部分进行计算 数据库基础 71 6 2 8运算符的优先级 表6 5运算符的优先级 数据库基础 72 第六章T SQL语言程序设计 6 1数据类型 常量和变量6 2运算符和表达式6 3流程控制语句6 4函数 数据库基础 73 6 3流程控制语句 与其他的程序设计语言一样 T SQL提供了流程控制语句 可以进行顺序 分支 循环结构的程序设计 数据库基础 74 6 3流程控制语句 6 3 1语句块和注释6 3 2选择控制6 3 3GOTO语句6 3 4RETURN语句6 3 5WAITFOR语句6 3 6循环控制语句 数据库基础 75 6 3 1语句块和注释 1 BEGIN END语句BEGIN END语句能够将多个T SQL语句组合成一个语句块 并将它们视为一个单元来处理 在控制流语句必须执行包含两条或多条T SQL语句时 就要使用BEGIN END语句来构成语句块 BEGIN END语句的语法形式如下 BEGIN sql statement statement block END 数据库基础 76 6 3 1语句块和注释 其中 sql statement statement block 是任和有效的T SQL语句或语句块 BEGIN和END语句必须成对使用 任何一个均不能单独使用 BEGIN语句单独出现在一行中 后跟T SQL语句块 最后END语句单独出现在一行中 指示语句块的结束 数据库基础 77 6 3 1语句块和注释 例6 12显示图书管理数据库中读者表的借书证号为1344005的读者姓名 DECLARE namechar 8 BEGINSELECT name SELECT姓名FROM读者WHERE借书证号 1344005 SELECT nameEND 数据库基础 78 6 3 1语句块和注释 本例中BEGIN END语句将两个SELECT语句组合成一个语句块 实现了给变量 name赋值并显示 虽然BEGIN END语句几乎可以用在程序的任何地方 但最常用的地方是在IF或ELSE子句需要包含语句块 CASE函数的元素需要包含语句块以及WHILE循环需要包含语句块时使用 数据库基础 79 6 3 1语句块和注释 2 注释在程序设计中 注释是必不可少的 加入注释一方面可以有助于对代码的理解 另外也可以方便开发人员调试 在T SQL中使用两个连字符 加入单行注释 使用一对分隔符 加入多行注释 数据库基础 80 6 3流程控制语句 6 3 1语句块和注释6 3 2选择控制6 3 3GOTO语句6 3 4RETURN语句6 3 5WAITFOR语句6 3 6循环控制语句 数据库基础 81 6 3 2选择控制 1 IF ELSE语句2 CASE语句 数据库基础 82 1 IF ELSE语句 IF ELSE语句是条件判断语句 当条件表达式成立时执行某段程序 条件不成立时执行另一段程序 其中 ELSE子句是可选的 最简单的IF语句没有ELSE子句部分 SQLServer允许嵌套使用IF ELSE语句 而且嵌套层数没有限制 IF ELSE语句的语法形式为 IFBoolean expression sql statement statement block ELSE sql statement statement block 数据库基础 83 1 IF ELSE语句 例6 13查询图书管理数据库中图书的平均单价是否大于25 IF SELECTAVG 单价 FROM图书 25SELECT 平均单价大于25元 ELSESELECT 平均单价不大于25元 数据库基础 84 2 CASE语句 CASE语句用于多重选择的情况 可以根据条件表达式的值进行判断 并将其中一个满足条件的结果表达式返回 CASE语句按照使用形式的不同 可以分为简单CASE语句和搜索CASE语句 简单CASE语句的语法形式如下 CASEinput expressionWHENwhen expressionTHENresult expression n ELSEelse result expression END 数据库基础 85 2 CASE语句 执行过程为 先计算CASE后面的表达式的值 然后将其与WHEN后面的表达式逐个进行比较 若相等则返回THEN后面的表达式 否则返回ELSE后面的表达式 数据库基础 86 2 CASE语句 例6 14使用简单CASE将图书管理数据库中部分读者的单位信息重新命名 SELECTCASE单位WHEN 计算机系 THEN 计算机科学与技术系 WHEN 电子系 THEN 电子信息系 WHEN 数学系 THEN 应用数学系 ENDAS单位 姓名FROM读者 数据库基础 87 2 CASE语句 搜索CASE语句的语法形式如下 CASEWHENBoolean expressionTHENresult expression n ELSEelse result expression END执行过程为 如果WHEN后面的逻辑表达式为真 则返回THEN后面的表达式 然后判断下一个逻辑表达式 如果所有的逻辑表达式都为假 则返回ELSE后面的表达式 数据库基础 88 2 CASE语句 例6 15使用搜索CASE根据图书的单价范围 显示相应的信息 SELECT书名 CASEWHEN单价ISNULLTHEN 价钱不确定 WHEN单价 10and单价 20THEN 可以接收 ELSE 太贵了 ENDAS售价满意度FROM图书运行结果如图6 10所示 数据库基础 89 2 CASE语句 图6 10使用搜索CASE 数据库基础 90 6 3流程控制语句 6 3 1语句块和注释6 3 2选择控制6 3 3GOTO语句6 3 4RETURN语句6 3 5WAITFOR语句6 3 6循环控制语句 数据库基础 91 6 3 3GOTO语句 GOTO语句可以使程序直接跳到指定的标识符的位置继续执行 而位于GOTO语句和标识符之间的程序将不会被执行 GOTO语句和标识符可以用在语句块 批处理和存储过程中 标识符可以为数字与字符的组合 但必须以 结尾 如 label 在GOTO语句行 标识符后面不用跟 GOTO语句的语法形式为 GOTOlabel label 数据库基础 92 6 3 3GOTO语句 例6 16利用GOTO语句求出从1到10的总和 DECLARE sumint countintSELECT sum 0 count 1label SELECT sum sum countSELECT count count 1IF count 10GOTOlabelSELECT count sum 数据库基础 93 6 3 3GOTO语句 使用GOTO语句可以增加程序的灵活性 该语句也破坏了程序结构化的特点 使程序结构变得复杂而且难以测试 因此尽量少使用GOTO语句 数据库基础 94 6 3流程控制语句 6 3 1语句块和注释6 3 2选择控制6 3 3GOTO语句6 3 4RETURN语句6 3 5WAITFOR语句6 3 6循环控制语句 数据库基础 95 6 3 4RETURN语句 RETURN语句用于无条件地终止一个查询 存储过程或者批处理 位于RETURN语句之后的程序将不会被执行 RETURN语句的语法形式为 RETURN integer expression 其中 参数integer expression为返回的整型值 数据库基础 96 6 3 4RETURN语句 例6 17创建一个存储过程 用来判断读者现在借阅的图书是否达到规定的最大数量 不到10本的还可以再借阅 CREATEPROCEDUREcheck count paramchar 8 ASIF SELECTCOUNT FROM借阅WHERE借书证号 paramGROUPBY借书证号 10RETURN1ELSERETURN0 数据库基础 97 6 3流程控制语句 6 3 1语句块和注释6 3 2选择控制6 3 3GOTO语句6 3 4RETURN语句6 3 5WAITFOR语句6 3 6循环控制语句 数据库基础 98 6 3 5WAITFOR语句 WAITFOR语句可以指定批处理 存储过程或事务执行的时刻 或需等待的时间间隔 语法形式为 WAITFOR DELAY time TIME time 其中 DELAY用于指定时间间隔 要求时间间隔在24小时之内 TIME用于指定某一时刻 其数据类型为datetime 格式为 hh mm ss 数据库基础 99 6 3 5WAITFOR语句 例16 18在22 00时备份图书管理数据库 WAITFORTIME 22 00 BACKUPDATABASE图书管理TObook bkp 数据库基础 100 6 3流程控制语句 6 3 1语句块和注释6 3 2选择控制6 3 3GOTO语句6 3 4RETURN语句6 3 5WAITFOR语句6 3 6循环控制语句 数据库基础 101 6 3 6循环控制语句 在T SQL中可以使用WHILE语句来进行循环控制 当WHILE后面的条件为真时 就重复执行语句 WHILE可以和CONTINUE以及BREAK配合使用来完成对循环的控制 具体的语法格式如下 WHILEBoolean expression sql statement statement block BREAK sql statement statement block CONTINUE 数据库基础 102 6 3 6循环控制语句 其中Boolean expression表达式 返回TRUE或FALSE 如果布尔表达式中含有SELECT语句 则必须用括号将SELECT语句括起来 sql statement statement block 为T SQL语句或用BEGIN END定义的语句块 BREAK导致从最内层的WHILE循环中退出 将执行出现在END关键字 循环结束的标记 后面的任何语句 CONTINUE使WHILE循环重新开始执行 忽略CONTINUE关键字后面的任何语句 数据库基础 103 6 3 6循环控制语句 例6 19在图书管理数据库中 判断图书单价的平均价格如果少于15 WHILE循环就将价格加倍 然后选择最高价 如果最高价少于或等于40 WHILE循环重新启动并再次将价格加倍 该循环不断地将价格加倍直到最高价格超过40 然后退出WHILE循环并打印一条消息 数据库基础 104 6 3 6循环控制语句 WHILE SELECTAVG 单价 FROM图书 40BREAKELSECONTINUEENDPRINT 可以接受 数据库基础 105 第六章T SQL语言程序设计 6 1数据类型 常量和变量6 2运算符和表达式6 3流程控制语句6 4函数 数据库基础 106 6 4函数 在程序设计中经常会用到函数 SQLServer2005中支持两种函数类型 内置函数和用户定义函数 数据库基础 107 6 4函数 6 4 1内置函数6 4 2用户自定义函数 数据库基础 108 6 4 1内置函数 内置函数是一组预定义的函数 用户只能调用 不能修改 在程序设计中经常会调用一些内置函数 如进行数学计算 实现数据类型的转换 获得系统信息等 下面将介绍一些常用的内置函数 数据库基础 109 1 数学函数 数学函数用于对数字表达式进行数学运算并返回运算结果 数学函数可以对SQLServer2005中提供的数字数据 包括decimal integer float real money smallmoney smallint和tinyint等类型 进行处理 在SQLServer2005中 常用的数学函数如表6 6所示 数据库基础 110 1 数学函数 数据库基础 111 1 数学函数 数据库基础 112 1 数学函数 例6 20用数学函数返回指定数值的绝对值以及大于或等于指定表达式的最小整数和小于或等于指定表达式的最大整数 SELECTABS 13 67 CEILING 13 67 FLOOR 13 67 执行结果如图6 11所示 数据库基础 113 1 数学函数 图6 11使用数学函数 数据库基础 114 2 字符串函数 字符串函数用来实现对字符型数据的查找 分析 转换等操作 在SQLServer2005中字符串函数可分为四大类 基本字符串函数 字符串查找函数 长度和分析函数以及转换函数 具体的函数参照表6 7 数据库基础 115 2 字符串函数 数据库基础 116 2 字符串函数 数据库基础 117 2 字符串函数 数据库基础 118 2 字符串函数 例6 21在第一个字符串 helloworld 中删除从第七个位置 w字符 开始的五个字符 然后在删除的起始位置插入第二个字符串 创建并返回一个字符串 SELECTSTUFF helloworld 7 5 china 运行结果为 hellochina 数据库基础 119 2 字符串函数 例6 22查询图书管理数据库中 读者姓名的第一个字 SELECTSUBSTRING 姓名 1 1 FROM读者运行结果如图6 12所示 数据库基础 120 2 字符串函数 图6 12使用SUBSTRING函数 数据库基础 121 3 日期和时间函数 日期和时间函数用于对日期和时间数据进行各种不同的处理和运算 并返回一个字符串 数字值或日期和时间值 在SQLServer2005中提供的日期和时间函数如表6 8所示 数据库基础 122 3 日期和时间函数 数据库基础 123 3 日期和时间函数 例6 23显示图书管理数据库中读者借书日期到当前日期的天数 SELECT借书证号 图书编号 DATEDIFF day 借阅日期 getdate AS借出天数FROM借阅运行结果如图6 13所示 数据库基础 124 3 日期和时间函数 图6 13使用日期时间函数 数据库基础 125 4 系统函数 系统函数用于返回有关SQLServer2005系统 用户 数据库和数据库对象的信息 用户可以根据系统函数返回的信息进行不同的操作 表6 9给出了常用的系统函数 数据库基础 126 4 系统函数 数据库基础 127 4 系统函数 例6 24使用cast函数将图书管理数据库中图书的单价转换为int型显示 SELECT书名 CAST 单价ASint as单价FROM图书执行结果如图6 14所示 数据库基础 128 4 系统函数 图6 14使用cast函数 数据库基础 129 4 系统函数 例6 25从图书管理数据库中返回图书表的首列名称 SELECTCOL NAME OBJECT ID 图书 1 执行结果如图6 15所示 数据库基础 130 4 系统函数 图6 15使用系统函数返回列名称 数据库基础 131 6 4函数 6 4 1内置函数6 4 2用户自定义函数 数据库基础 132 6 4 2用户自定义函数 虽然SQLServer2005中提供了多种可供用户调用的内置函数 但在实际编程中仍不能完全满足用户的多样化需求 因此还需要使用到用户自定义函数 在用户自定义函数中可以包含0个或多个参数 函数的返回值可以是数值 还可以是一个表 根据用户定义函数返回值的类型 可将用户定义函数分为如下三个类别 数据库基础 133 6 4 2用户自定义函数 标量函数用户自定义函数返回值为一个确定类型的标量值 返回类型可以是除text ntext image cursor和timestamp外的任何系统数据类型 内嵌表值函数用户定义函数返回值为一个表 且函数体中只包含单个SELECT语句 这样的函数称为内嵌表值函数 其功能相当于一个参数化的视图 数据库基础 134 6 4 2用户自定义函数 多语句表值函数用户定义函数的返回值仍是一个表 但在BEGIN END语句块中定义的函数体包含一系列T SQL语句 这些语句可生成行并将其插入到返回的表中 这样的函数称为多语句表值函数 数据库基础 135 1 用户自定义函数的创建与调用 创建用户自定义函数可以在MicrosoftSQLServerManagementStudio中采用有框架提示的方式来实现 也可以使用CREATEFUNCTION语句实现 第一种方法的具体步骤如下 MicrosoftSQLServerManagementStudio 展开要创建自定义函数的数据库 可编程性 函数 右击新建相应类别的函数 如图6 16所示 数据库基础 136 1 用户自定义函数的创建与调用 图6 16创建用户自定义函数 数据库基础 137 1 用户自定义函数的创建与调用 完成上面的操作后 会出现相应的提示框架 用户需要在对应的部分写上代码 下面重点介绍采用CREATEFUNCTION语句的方式 在SQLServer2005中 根据用户自定义函数类型的不同 创建函数的格式也不同 数据库基础 138 1 用户自定义函数的创建与调用 创建标量函数的语法格式如下 CREATEFUNCTION 所有者名 函数名 参数1 AS 类型1 默认值 参数n AS 类型n 默认值 RETURNS返回值类型 WITHENCRYPTION SCHEMABINDING n AS BEGIN函数体RETURN标量表达式END 数据库基础 139 1 用户自定义函数的创建与调用 其中参数可以声明一个或多个 最多可以有1024个参数 函数在执行时 要求指定每个参数的值 对于有默认值的 可以不指定 ENCRYPTION指出系统将加密包含CREATEFUNCTION语句文本的系统表 SCHEMABINDING指明用该选项创建的函数不能更改或删除该函数引用的数据库对象 数据库基础 140 1 用户自定义函数的创建与调用 例6 26创建标量函数以计算图书管理数据库中某个出版社出版的图书的平均价格 CREATEFUNCTIONaverprice cbschar 20 RETURNSdecimalASBEGINDECLARE averdecimalSELECT aver 数据库基础 141 1 用户自定义函数的创建与调用 SELECTavg 单价 FROM图书WHERE出版社 cbsGROUPBY出版社 RETURN averEND 数据库基础 142 1 用户自定义函数的创建与调用 函数创建完成后 可有以下方式调用 在SELECT语句中调用调用形式 所有者名 函数名 实参1 实参n 对于上面创建的averprice函数 采用在SELECT语句中调用语句如下 SELECTdbo Averprice 清华大学出版社 数据库基础 143 1 用户自定义函数的创建与调用 利用EXEC语句执行调用形式 所有者名 函数名实参1 实参n所有者名 函数名形参名1 实参1 形参名n 实参n采用EXEC语句调用averp