数据库原理与应用：第7章 Transact-SQL程序设计

1、数据库原理与应用数据库原理与应用第七讲第七讲 Transact-SQLTransact-SQL程序设计程序设计1 注释注释2 变量变量3 运算符运算符4 函数函数5 流程控制语句流程控制语句 1 注释注释 注释是程序代码中不执行的文本字符串（也注释是程序代码中不执行的文本字符串（也称为注解）。在称为注解）。在SQL Server中，可以使用两种类型中，可以使用两种类型的注释字符：一种是的注释字符：一种是ANSI标准的注释符标准的注释符“-”，它用，它用于单行注释；另一种是与于单行注释；另一种是与C语言相同的程序注释符语言相同的程序注释符号，即号，即“/* */”。 2 变量变量变量是一种语言中

2、必不可少的组成部分。变量是一种语言中必不可少的组成部分。Transact-SQL语言中有两种形式的变量，一种语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提是用户自己定义的局部变量，另外一种是系统提供的全局变量。供的全局变量。1. 局部变量局部变量利用局部变量可以保存程序执行过程中的中间结果，保存由存储过程返回的数据值等。（1）局部变量的定义 必须先用DECLARE命令定义后才可以使用。DECLAER local_variable data_type n （2）局部变量的赋值方法 SET local_variable = expression 或者SELECT local

3、_variable = expression ,.n 2. 全局变量全局变量 全局变量是全局变量是SQL Server系统内部使用的变量，系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。全局变量通常存储一些程序均可以随时调用。全局变量通常存储一些SQL Server的配置设定值和统计数据。用户可的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者以在程序中用全局变量来测试系统的设定值或者是是Transact-SQL命令执行后的状态值。命令执行后的状态值。 使用全局变量时应该注意以下几点：使用全局变量

4、时应该注意以下几点： 全局变量不是由用户的程序定义的，它们是在服务全局变量不是由用户的程序定义的，它们是在服务器级定义的。器级定义的。用户只能使用预先定义的全局变量。用户只能使用预先定义的全局变量。引用全局变量时，必须以标记符引用全局变量时，必须以标记符“”开头。开头。局部变量的名称不能与全局变量的名称相同，否则局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。会在应用程序中出现不可预测的结果。 常用的全局变量常用的全局变量ErrorRowCount Identity3 运算符运算符 运算符是一些符号，它们能够用来执行算术运算、运算符是一些符号，它们能够用来执行算术

5、运算、字符串连接、赋值以及在字段、常量和变量之间进字符串连接、赋值以及在字段、常量和变量之间进行比较。在行比较。在SQL Server 2000中，运算符主要有以中，运算符主要有以下六大类：算术运算符、赋值运算符、位运算符、下六大类：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符以及字符串串联运算符。比较运算符、逻辑运算符以及字符串串联运算符。1算术运算符算术运算符 算术运算符可以在两个表达式上执行数学运算术运算符可以在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何算，这两个表达式可以是数字数据类型分类的任何数据类型。算术运算符包括加（数据类型。算术运算符包括加（

6、+）、减（）、减（）、）、乘（乘（*）、除（）、除（/）和取模（）和取模（%）。）。 2赋值运算符赋值运算符Transact-SQL 中只有一个赋值运算符，中只有一个赋值运算符，即等号（即等号（=）。赋值运算符使我们能够将数据）。赋值运算符使我们能够将数据值指派给特定的对象。另外，还可以使用赋值指派给特定的对象。另外，还可以使用赋值运算符在列标题和为列定义值的表达式之值运算符在列标题和为列定义值的表达式之间建立关系。间建立关系。3位运算符位运算符位运算符使我们能够在整型位运算符使我们能够在整型数据或者二进制数据（数据或者二进制数据（image 数数据类型除外）之间执行位操作。据类型除外）之间执

7、行位操作。此外，在位运算符左右两侧的操此外，在位运算符左右两侧的操作数不能同时是二进制数据。作数不能同时是二进制数据。 表表2-1 位运算符位运算符 运运 算算 符符 含含 义义 &（按位（按位 AND）按位按位 AND（两个操作数）。（两个操作数）。|（按位（按位 OR）按位按位 OR（两个操作数）。（两个操作数）。（按位互斥（按位互斥 OR）按位互斥按位互斥 OR（两个操作数）。（两个操作数）。4比较运算符比较运算符比较运算符用于比较两个表达式的大小或比较运算符用于比较两个表达式的大小或是否相同，其比较的结果是布尔值，即是否相同，其比较的结果是布尔值，即TRUE（表示表达式的结果为

8、真）、（表示表达式的结果为真）、FALSE（表示表（表示表达式的结果为假）以及达式的结果为假）以及UNKNOWN。除了。除了 text、ntext 或或 image 数据类型的表达式外，比较运算数据类型的表达式外，比较运算符可以用于所有的表达式。符可以用于所有的表达式。 5逻辑运算符逻辑运算符逻辑运算符可以把多个逻辑表逻辑运算符可以把多个逻辑表达式连接起来。逻辑运算符包括达式连接起来。逻辑运算符包括AND、OR和和NOT等运算符。逻辑运等运算符。逻辑运算符和比较运算符一样，返回带有算符和比较运算符一样，返回带有 TRUE 或或 FALSE 值的布尔数据类型。值的布尔数据类型。6字符串串联运算符

9、字符串串联运算符字符串串联运算符允许通字符串串联运算符允许通过加号过加号 (+) 进行字符串串联，进行字符串串联，这个加号即被称为字符串串联这个加号即被称为字符串串联运算符。例如对于语句运算符。例如对于语句SELECT abc+def，其结果，其结果为为abcdef。运算符的优先等级从高到低如下所示运算符的优先等级从高到低如下所示 括号：（）；括号：（）；乘、除、求模运算符：乘、除、求模运算符：*、/、%；加减运算符：加减运算符：+、- ；比较运算符：比较运算符：=、=、=、!=、!、!；位运算符：位运算符：、&、|；逻辑运算符：逻辑运算符：NOT；逻辑运算符：逻辑运算符：AND；逻辑

10、运算符：逻辑运算符：OR。4 函数函数在在Transact-SQL语言中，函数被用来执行一些特殊的运算以语言中，函数被用来执行一些特殊的运算以支持支持SQL Server的标准命令。的标准命令。Transact-SQL 编程语言提供了三种编程语言提供了三种函数：函数： 行集函数：行集函数可以在行集函数：行集函数可以在Transact-SQL语句中当作表引语句中当作表引用。用。 聚合函数：聚合函数用于对一组值执行计算并返回一个单一聚合函数：聚合函数用于对一组值执行计算并返回一个单一的值。的值。 标量函数：标量函数用于对传递给它的一个或者多个参数值标量函数：标量函数用于对传递给它的一个或者多个参数

11、值进行处理和计算，并返回一个单一的值。进行处理和计算，并返回一个单一的值。 SQL Server中最常用的几种函数中最常用的几种函数 字符串函数字符串函数日期和时间函数日期和时间函数数学函数数学函数转换函数转换函数系统函数系统函数聚合函数聚合函数其它函数其它函数 字符串函数字符串函数字符串函数可以对二进制数据、字符串和表达式字符串函数可以对二进制数据、字符串和表达式执行不同的运算，大多数字符串函数只能用于执行不同的运算，大多数字符串函数只能用于char和和varchar数据类型以及明确转换成数据类型以及明确转换成char和和varchar的数据类型，少数几个字符串函数也可以用于的数据类型，少数

12、几个字符串函数也可以用于binary和和varbinary数据类型。此外，某些字符串函数据类型。此外，某些字符串函数还能够处理数还能够处理text、ntext、image数据类型的数据。数据类型的数据。 字符串函数的分类：字符串函数的分类：基本字符串函数：基本字符串函数：UPPER、LOWER、SPACE、REPLICATE、STUFF、REVERSE、LTRIM、RTRIM。字符串查找函数：字符串查找函数：CHARINDEX、PATINDEX。长度和分析函数：长度和分析函数：DATALENGTH、SUBSTRING、RIGHT。转换函数：转换函数：ASCH、CHAR、STR、SOUNDEX、

13、DIFFERENCE。 2日期和时间函数日期和时间函数日期和时间函数用于对日期和时间日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期并返回一个字符串、数字值或日期和时间值。在和时间值。在SQL Server 2000中，中，日期和时间函数的类型如表日期和时间函数的类型如表2-5所所示；另外，表示；另外，表2-6列出了日期类型列出了日期类型的名称、缩写形式以及可接受的值。的名称、缩写形式以及可接受的值。表表2-5 日期和时间函数的类型日期和时间函数的类型函函 数数参参 数数DATEADD(datepart,number,da

14、te)DATEDIFF(datepart,date1,date2)DATENAME(datepart,date)DATEPART(datepart,date)DAY(date)GETDATE（）MONTH(date)YEAR( date )从从GETDATE函数返回的日期中函数返回的日期中提取月份数。提取月份数。 SELECT DATEPART(month, GETDATE() AS Month Number运行结果为：运行结果为：Month Number-4 从日期从日期 04/12/2006 中返回月份数、天数和年份中返回月份数、天数和年份数。数。SELECT MONTH(04/12/20

15、06), DAY(04/12/2006),YEAR(04/12/2006)运行结果为：运行结果为：- - -4 12 20063数学函数数学函数数学函数用于对数字表达式进行数学函数用于对数字表达式进行数学运算并返回运算结果。数学数学运算并返回运算结果。数学函数可以对函数可以对SQL Server提供的数提供的数字数据（字数据（decimal、integer、float、real、money、smallmoney、smallint 和和 tinyint）进行处理。）进行处理。 在同一表达式中使用在同一表达式中使用CEILING（）、（）、FLOOR（）、（）、ROUND（）函数。（）函数。sel

16、ect ceiling(13.4), floor(13.4), round(13.4567,3)运行结果为：运行结果为：- - -14 13 13.45704转换函数转换函数一般情况下，一般情况下，SQL Server会自动处理某些数据类型会自动处理某些数据类型的转换。例如，如果比较的转换。例如，如果比较 char 和和 datetime 表达式、表达式、smallint 和和 int 表达式、或不同长度的表达式、或不同长度的 char 表达式，表达式，SQL Server 可以将它们自动转换，这种转换被称为可以将它们自动转换，这种转换被称为隐性转换。但是，无法由隐性转换。但是，无法由SQL

17、Server自动转换的或自动转换的或者是者是SQL Server自动转换的结果不符合预期结果的，自动转换的结果不符合预期结果的，就需要使用转换函数做显示转换。转换函数有两个：就需要使用转换函数做显示转换。转换函数有两个：CONVERT和和CAST。CONVERT和和CAST函数函数C A S T ( e x p r e s s i o n A S data_type )CONVERT函数允许用户把表达式从一函数允许用户把表达式从一种数据类型转换成另一种数据类型，还种数据类型转换成另一种数据类型，还允许把日期转换成不同的样式。其语法允许把日期转换成不同的样式。其语法形式为：形式为：CONVERT

18、 (data_type(length),expression ,style)USE pubsSELECT title, ytd_salesFROM titlesWHERE CAST(ytd_sales AS char(20) LIKE 15% AND type = trad_cook运行结果为：运行结果为：Title ytd_sales-Fifty Years in Buckingham Palace Kitchens 150965系统函数系统函数系统函数用于返回有关系统函数用于返回有关SQL Server系统、用户、数据库和数据库对象的系统、用户、数据库和数据库对象的信息。系统函数可以让用户

19、在得到信信息。系统函数可以让用户在得到信息后，使用条件语句，根据返回的信息后，使用条件语句，根据返回的信息进行不同的操作。与其它函数一样，息进行不同的操作。与其它函数一样，可以在可以在SELECT语句的语句的SELECT和和WHERE子句以及表达式中使用系统子句以及表达式中使用系统函数。函数。 返回返回 Northwind 数据库的数据库的 Employees 表表中的首列的名称。中的首列的名称。USE NorthwindSELECT COL_NAME(OBJECT_ID(Employees), 1)运行结果为：运行结果为：EmployeeID6聚合函数聚合函数 聚合函数可以返回整个或者几个列

20、聚合函数可以返回整个或者几个列或者一个列的汇总数据，它常用来或者一个列的汇总数据，它常用来计算计算SELECT语句查询的统计值。语句查询的统计值。聚合函数经常与聚合函数经常与 SELECT 语句的语句的 GROUP BY 子句一同使用。子句一同使用。用户自定义函数CREATE FUNCTION 创建用户定义函数，它是返回值的已保存的创建用户定义函数，它是返回值的已保存的 Transact-SQL 例程。用户定义函数不例程。用户定义函数不能用于执行一组修改全局数据库状态的操作。与系统函数一样，用户定义函数可以从能用于执行一组修改全局数据库状态的操作。与系统函数一样，用户定义函数可以从查询中唤醒调

21、用。也可以像存储过程一样，通过查询中唤醒调用。也可以像存储过程一样，通过 EXECUTE 语句执行。语句执行。用户定义函数用用户定义函数用 ALTER FUNCTION 修改，用修改，用 DROP FUNCTION 除去。除去。CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS scalar_return_data_type WITH , .n AS BEGIN function_body RETURN scalar_ex

22、pressionEND5 流程控制语句流程控制语句流程控制语句是指那些用来控制程序流程控制语句是指那些用来控制程序执行和流程分支的命令，在执行和流程分支的命令，在SQL Server 2000中，流程控制语句主要中，流程控制语句主要用来控制用来控制SQL语句、语句块或者存储语句、语句块或者存储过程的执行流程。过程的执行流程。 使用BEGINEND可以将多条T-SQL语句封装起来，构成一个独立的语句块。BEGIN关键字表示语句块的开始，END关键字表示语句块的结束，它们必须成对出现。BEGINEND的语法格式如下：BEGIN sql_statement | statement_block END

23、Begin EndIFELSE IFELSE语句可以控制程序按条件执行。当IF关键字后的条件满足（布尔表达式返回TRUE 时），则在执行IF关键字及其条件之后的T-SQL 语句。否则，就执行ELSE关键字后的T-SQL语句（若ELSE部分存在）。IFELSE语句的语法格式如下：IF Boolean_expression sql_statement | statement_block ELSE sql_statement | statement_block 查询课程号为“105237”的课程的平均分是否超过了85分，若超过，则输出考出了高分的信息。USE EducationalGODECLARE Course_ID varchar(10)SELECT Course_ID = 105237 /* 查询的课程号 */ IF ( SELECT AVG(Grade) FROM Grade WHERE CourseID = Course_ID ) 85/* 平均分大于85分 */BEGIN SELECT Course_ID = CourseName /* 从课程信息表中取课程名 */ FRO