1、第11章 TSQL程序设计,计算中心,本章内容,11.1 数据与表达式 11.2 常用系统函数 11.3 程序控制流语句,11.1 数据与表达式,常量与变量 常量：在程序运行中保持常值的数据，即程序本身不能改变其值的数据，在程序中经常直接使用文字符号表示。 变量：在程序运行过程中可以改变其值的数据。,11.1 数据与表达式,常量 常量表示特定数据值的符号，其格式取决于其数据类型。 (1) 字符串常量：括在单引号内并包含字母数字字符以及特殊字符，若字符串中本身又有单引号字符，则要使用两个单引号表示。如： China ，Im a teacher (2) 日期/时间常量：使用特定格式的字符日期值表示
2、，括在单引号内。如： 2008/08/08 ，输入时可以使用“/”、“.”、“-”作日期/时间常量的分隔符。,表11-1 SQL Server日期时间格式,11.1 数据与表达式,(3) 数值常量 整型常量：没有用引号括起来且不含小数点的一串数字，如：3000，2，-34。 浮点常量：采用科学计数法表示，如：101.5E5。 精确数值常量：没有用引号括起来且包含小数点的一串数字，如100.01。 货币常量：以“$”为前缀的整型或实型常量数据，不使用引号，如$12.5。 逻辑数据常量：使用数字0或1表示，不用引号，非0当作1。 空值：NULL。,11.1 数据与表达式,变量变量用于临时存放数据，
3、变量中的数据随着程序的运行而变化，具有名字与数据类型两个属性。 变量的命名使用常规标识符，即以字母、下划线、开头，后续字母、数字、$、下划线的字符序列，不允许嵌入空格或其他特殊字符。 SQL Server将变量分为全局变量和局部变量两大类。 全局变量：由系统定义并维护，不能由用户定义，以“”开头，如version。 局部变量：由用户来定义，以“”开头。,11.1 数据与表达式,全局变量 全局变量由系统提供，被服务器用来跟踪服务器范围和特定会话期间的信息。 不能由用户定义，但用户需要读取其中的数据。 常用的全局变量 error：返回错误号。在程序运行或SQL语句执行过程中，如果error为非0值
4、，则表明执行过程发生了错误，通过读取错误号，在程序中采取相应的措施加以处理。 rowcount：前一条命令处理的行数。 servername：本地SQL Server的名称。,11.1 数据与表达式,局部变量的定义 使用DECLARE语句，语法： DECLARE local_variable data_type ,.n 提示： 变量名一定是以开头，且符合标识符规则，变量名最大长度为30个字符。声明变量时需指明数据类型。可以同时声明多个局部变量，中间用逗号分隔。 举例：declare name varchar(30),birthday smalldatetime,11.1 数据与表达式,局部变量
5、的赋值（3种赋值方法） (1) 用SELECT为局部变量赋值。在T-SQL中，通常用SELECT语句为变量赋值，语法：SELECT variable_name=expression , nFROM WHERE 例11-1：declare int_var int,birthday smalldatetimeselect int_var=12,birthday= 1990/10/20select int_var提示：赋值时要注意变量的数据类型，如果是字符型和日期/时间型，需要加定界符单引号；SELECT语句中的局部变量没有被赋值时，其作用是输出变量值到屏幕上。,11.1 数据与表达式,例11-2：
6、declare LastName char(8) ,FirstName char(8)select lastname=smith,firstname=david 提示： 在一条SELECT语句中可以同时对几个变量赋值。 由于局部变量没有被赋值前，其值是NULL，而与NULL进行计算后结果也是NULL，因此，需要通过赋值语句初始化局部变量。 例11-3：使用select语句从学生表中查询学号为s2008002的学生姓名，并赋值给变量student。 declare student char(8)select student=姓名 from 学生表 where 学号=s2008002 提示：变量数
7、据类型最好和表中对应字段的数据类型一致；如果返回多行，则赋值最后一行数据；如果没有返回行，值不变。,11.1 数据与表达式,(2) 利用UPDATE为局部变量赋值例11-4：将选课表中学号为s200801课程号为c01的考试成绩赋值给变量score。declare score smallintupdate 选课表 set score=成绩 where 学号=s2008002 and 课程号=c01提示：声明局部变量的数据类型必须与表中对应字段的数据类型相同。,11.1 数据与表达式,(3) 用SET语句给局部变量赋值在为变量赋值时，建议使用SET语句，语法：SET local_variable
8、=expression例11-5：计算学生表中的记录数并赋值给变量rows。declare rows intset rows=(select count(*) from 学生表)提示： expression是任何有效的SQL Server表达式。 赋值方法类似与SELECT语句，但一个SET语句只能为一个变量赋值。,11.1 数据与表达式,运算符与表达式 运算符用来执行数据列之间的数学运算或比较操作。 表达式是符号与运算符的组合。 简单的表达式可以是一个常量、变量、列或函数，复杂表达式是由运算符连接一个或多个简单表达式。 T-SQL运算符共有5类：算术运算符、位运算符、逻辑运算符、比较运算符和
9、连接运算符。,11.1 数据与表达式,(1) 算术运算符与表达式 用于数值型列或变量间的算术运算。算术运算符包括加(+)、减(-)、乘(*)、除(/)和取模(%)运算。 例11-6：select 1000%7 /*求出1000除以7的余数*/ 例11-7：查询学生表中学生年龄。（学生表中有生日列）select 学号,姓名,2009-year(生日) as 年龄 from 学生表,提示：如果表达式中有多个算术运算符，需要考虑运算符的优先级。,11.1 数据与表达式,(2) 位运算符与表达式 对数据进行按位与()、或(|)、异或()、求反()等运算。 与()：同时为1才为1。 或(|)：同时为0才
10、为0。 异或()：不同为1，相同为0。 反()：单目运算符，0为1，1为0。 例11-8：170与75进行&运算。解：170转换成二进制数10101010，75转换成二进制数01001011，按位与运算的结果是00001010，即十进制10。,11.1 数据与表达式,(3) 比较运算符与表达式 用来比较两个表达式的值是否相同，可用于字符、数字或日期数据。 比较运算返回布尔值（逻辑值）TRUE、FALSE、UNKNOWN，通常出现在条件表达式 举例：select *from 选课表where 成绩=90 思考：在SQL语句中，还有什么子句会使用条件表达式？,11.1 数据与表达式,(4) 逻辑运
11、算符与表达式 当进行多个条件判定时，需用逻辑运算符与(AND)、或(OR)、非(NOT)，返回TRUE或FALSE。 逻辑运算符通常和比较运算一起构成更为复杂的表达式，逻辑运算符的操作数都只能是布尔型数据。例11-9：查询学生表中1990年以前出生的男同学。select * from 学生表 where 生日1990-01-01 and 性别=男 SQL Server提供了多个逻辑运算符，以表达真实情况，如下表所示。,11.1 数据与表达式,表11-2 逻辑运算符,11.1 数据与表达式,LIKE运算符：确定给定的字符串是否与指定的模式匹配，限于字符数据类型，模式可以使用通配符。,表11-3
12、LIKE的通配符,11.1 数据与表达式,IN运算符 例11-10：查询选课表中课程c01和c02的考试成绩。select * from 选课表 where 课程号 in(c01,c02) 提示：条件表达式相当于课程号=c01 or 课程号= c02BETWEEN运算符 例11-11：查询选课表中成绩范围在8090分的学生学号。select * from 选课表 where 成绩 between 80 and 90 提示：条件表达式相当于成绩=80 and 成绩=90,11.1 数据与表达式,(5) 连接运算符和表达式 连接运算符(+)用于两个字符串数据的连接，通常也称为字符串运算符。 在SQ
13、L Server中，对字符串的其他操作通过字符串函数进行。字符串连接运算符的操作数类型有char、varchar和text等。 例如：Dr. +Computer的运算结果:Dr. Computer思考：“select 学号+姓名 from 学生表”的运行结果。,11.1 数据与表达式,运算符的优先级别 SQL Server中各种运算符的优先顺序如下： ( ) | *、/、% +、- NOT AND OR 排在前面的运算符的优先级高于其后的运算符。 在一个表达式中，先计算优先级较高的运算，后计算优先级低的运算，相同优先级的运算按自左向右的顺序依次进行。 可以通过小括号强制改变优先级,11.2 常
14、用系统函数,函数是组编译好的Transact-SQL语句，它们可以带一个或一组数值做参数，也可不带参数，它返回一个数值、数值集合，或执行一些操作。 函数能够重复执行一些操作，从而避免重写代码。 函数可以在SELECT和WHERE子句以及表达式中使用。 SQL Server 2000支持两种函数类型： (1) 内置函数:是一组预定义的函数，是Transact-SQL语言的一部分，按Transact-SQL参考中定义的方式运行且不能修改。 (2) 用户定义函数:由用户定义的Transact-SQL函数。,11.2 常用系统函数,在SQL Server中，函数主要用来获得系统的有关信息、执行数学计算
15、和统计、实现数据类型的转换等，SQL Server2000提供的函数包括字符串函数、数学函数、日期函数、系统函数等。 (1) 字符串函数用来实现对字符型数据的转换、查找、分析等操作。常用字符串函数见P195表9-7。例如：select len(abcdef) 结果：6select substring(abcdef,2,3) 结果：bcdselect charindex(-, red-whilte-blue) 结果：4select patindex(%abc%, abc123) 结果：1,11.2 常用系统函数,(2) 数学函数 用来实现各种数学运算，如指数、对数、三角运算等，其操作数为数值型数
16、据（int,float,real,money等）。 常用数学函数见P198表9-8。 例11-12：使用ceiling和floor函数。select ceiling(123),floor(321),ceiling(12.3),ceiling(-32.1),floor(-32.1) 例11-13：使用ROUND函数SELECT ROUND(123.9994, 3), ROUND(123.9995, 3) ,ROUND(123.45, -2), ROUND(748.58, -2), ROUND(748.58, -3) 例11-14：无参数函数 select pi(),rand(),11.2 常用系
17、统函数,(3) 日期函数 用来操作datetime和smalldatetime类型的数据，执行算术运算。 常用日期函数见P199表9-9 例11-15：返回当前系统的日期和时间select getdate() 例11-16：计算学生表中的学生年龄。select 姓名,datediff(year,生日,getdate() as 年龄from 学生表 例11-17：查询学生表中生日的年、月、日信息。select 姓名,datename(mm,生日) from 学生表,11.2 常用系统函数,(4) 系统函数 用于获取有关计算机系统、用户、数据库和数据库对象的信息。 SQL Server系统函数见P
18、200表9-11。 例11-18：select host_name(),db_name()返回服务器端（SQL Server）的计算机名称和数据库名称,11.3 程序控制流语句,语句块和注释 语句块BEGINEND 用来设定一个语句块，将在BEGINEND内的所有语句视为一个逻辑单元执行，在处理时，整个语句块被视为一条语句。语法格式：BEGINsql_statment|statement_blockEND其中， sql_statment|statement_block是任何有效的T-SQL语句或以语句块定义的语句分组。,11.3 程序控制流语句,例11-19：将学生表中学号为s2008001的
19、姓名赋值给变量name，并显示。 declare name char(8) beginselect name=姓名 from 学生表 where 学号=s2008001 select name end 提示：通常和控制流语句组合使用。,11.3 程序控制流语句,注释 在程序代码中有必要加入注释，增强程序的易读性。 注释语句不被执行。 注释没有长度限制。 声明注释有两种方法： 单行注释：以两个连接符“-”开头的语句。 多行注释：用分隔符“/* */”括住的所有语句。 例11-20：阅读如下程序，理解注释。/* 本程序编写时间：2009年2月编写目的：查询学生相关信息作者：朱烨 */select
20、* from 学生表 -查询学生基本信息,11.3 程序控制流语句,选择控制 (1) 条件执行语句IFELSE IF.ELSE结构根据条件表达式的值，以决定执行哪些语句。 IF.ELSE的语法格式为：IF 条件表达式 sql_statement | statement_block -条件表达式为真时执行 ELSE sql_statement | statement_block -条件表达式为假时执行,11.3 程序控制流语句,例11-21：判断选课表中课程号为c01的平均成绩是否在90分以上。 if (select avg(成绩) from 选课表 where 课程号=c01)=90selec
21、t c01课程的平均成绩在90分以上。 elseselect c01课程的平均成绩低于90分。,11.3 程序控制流语句,例11-22：用EXISTS确定学生表中是否存在“李非”。declare name varchar(8),msg varchar(200)set name=李非if exists(select * from 学生表 where 姓名=name)beginselect msg=有学生名为+nameselect msgendelsebeginselect msg=没有学生名为+nameselect msgend,11.3 程序控制流语句,IFELSE语句可以嵌套 例11-23：
22、阅读如下程序，体会嵌套IFELSE的使用。declare name varchar(8),学号 as char(10)set name=李非select 学号=学号 from 学生表 where 姓名=nameif 学号 is nullprint 没有该同学！elsebeginif (select avg(成绩) from 选课表 where 学号=学号)=90print name+同学成绩优秀.elseprint name+同学未达到优秀. end,11.3 程序控制流语句,(2) CASE函数如果有多个条件要判断，通常会使用CASE。CASE函数计算多个条件并为每个条件返回单个值。 简单C
23、ASE函数：将某个表达式与一组简单表达式进行比较以确定结果。CASE input_expressionWHEN when_expression THEN result_expression .n ELSE else_result_expression END CASE搜索函数：CASE计算一组逻辑表达式以确定结果。CASEWHEN Boolean_expression THEN result_expression . n ELSE else_result_expression END,11.3 程序控制流语句,例11-24：查询学生的考试成绩，通过简单CASE函数显示课程名。select 学号
24、,(case 课程号 when c01 then 英语when c02 then 高等数学when c03 then 数据库 end) as 课程名,成绩 from 选课表,11.3 程序控制流语句,例11-25：使用CASE搜索函数将选课表中的成绩按“优”、“良”、“中”、“及”、“未过”来显示。select 学号,课程号,(case when 成绩=90 then 优 when 成绩=80 and 成绩=70 and 成绩=60 and 成绩70 then 及when 成绩60 then 未过 else 成绩有误 end) as 考核from 选课表,11.3 程序控制流语句,(3) 跳转
25、语句GOTO 允许程序的执行转移到标签处,GOTO语句的语法格式：GOTO label其中，label为GOTO语句处理的起点。label必须符合标识符规则。例11-26：使用GOTO语句改变程序流程。DECLARE x intSELECT x=1label_1:SELECT xSELECT x=x+1IF x6GOTO label_1,提示：由于GOTO语句破坏了程序结构化的特点，应尽量避免使用。,11.3 程序控制流语句,(4) RETURN语句 使程序从批处理、存储过程或触发器中无条件退出，不再执行本语句之后的任何语句。语法格式：RETURN integer_expression 例11
26、-27：RETURN语句应用示例。DECLARE x int,y intSELECT x=1,y=2IF xy RETURN,11.3 程序控制流语句,(5) 调度执行语句WAITFOR使用WAITFOR语句可以调度执行批处理或者存储过程，语法格式：WAITFOR DELAY time |TIME time 例如：waitfor delay 00:01:00 select * from 学生表服务器在查询学生表之前暂停1分钟。例如：waitfor time 23:00 backup database studentsdb to studentsdb_bkp在晚上11:00备份数据库studen
27、tsdb提示：指定的时间间隔必须小于24小时；此方法很少使用，推荐使用SQL Server Agent。,11.3 程序控制流语句,循环控制WHILE 根据条件表达式设置T-SQL语句或语句块重复执行的次数。如果条件为真（TRUE），重复执行循环体内T-SQL语句，直到条件为假（FALSE）为止。,11.3 程序控制流语句,循环控制WHILE流程图,11.3 程序控制流语句,在WHILE循环内T-SQL语句的执行可以使用BREAK与CONTINUE关键词来控制。 WHILE循环语句的语法格式如下：WHILE boolean_expression sql_statement | statemen
28、t_block BREAK sql_statement | statement_block CONTINUE ,11.3 程序控制流语句,例11-28：计算1+2+10的值。 declare n tinyint,sum int set n=1 set sum=0 while n=10 beginset sum=sum+nset n=n+1 end select sum,循环执行过程： 第1次：n=1,sum=1 第2次：n=2,sum=1+2 第3次：n=3,sum=1+2+3 第10次：n=10,sum=1+2.+10,11.3 程序控制流语句,例11-29：计算10!。 DECLARE n
29、 tinyint,阶乘 int set n=1 set 阶乘=1 while n=10beginset 阶乘=阶乘*nset n=n+1end select 阶乘,例11-30：计算s=1!+2!+10!。 DECLARE 阶乘和 int,n int,阶乘 int,c int -n为外层（求和）循环变量，c为内层(求阶乘)循环变量 SET 阶乘和=0 SET n=1 WHILE n=10 BEGIN SET c=1SET 阶乘=1WHILE c=nBEGINSET 阶乘=阶乘*cSET c=c+1ENDSET 阶乘和=阶乘和+阶乘SET n=n+1 END SELECT 阶乘和,n,11.3
30、程序控制流语句,例11-31：用while循环绘制如右下图形。 declare i int set i=1 while i=10 beginprint replicate(*,i)set i=i+1 end 思考：如下图形如何用while循环实现？,11.3 程序控制流语句,BREAK或CONTINUE语句 BREAK语句让程序跳出循环，CONTINUE语句让程序跳过CONTINUE命令之后的语句，回到WHILE循环的第一行命令，重新开始循环。 如果嵌套了两个或多个 WHILE 循环，内层的 BREAK 将导致退出到下一个外层循环。首先运行内层循环结束之后的所有语句，然后下一个外层循环重新开始
31、执行。,11.3 程序控制流语句,例11-32：为选课表的成绩进行整体调整，如果最低的平均成绩小于75分，每人每科加1分，但单科成绩不能高于100分。 while (select min(平均成绩) from (select avg(成绩) 平均成绩 from 选课表 group by 学号) a)100breakelsecontinue end print 成绩调整完成。,11.3 程序控制流语句,批处理是包含一个或多个 Transact-SQL 语句的组，从应用程序一次性地发送到 Microsoft SQL Server 执行。SQL Server 将批处理语句编译成一个可执行单元，此单元
32、称为执行计划。,11.3 程序控制流语句,一个批处理是条或多条Transact-SQL语句的集合。 SQL Server服务器把批处理作为一个整体进行处理，过程分为四个阶段： 分析阶段，服务器检查命令的语法，验证表和列的名字的合法性； 优化阶段，服务器确定完成一个查询的最有效的方法； 编译阶段，生成该批处理的执行计划； 运行阶段，条一条地执行该批处理中的语句。 批处理最重要的特征就是它作为一个不可分的实体在服务器上解释和执行。在一些情况下批处理被隐式地设定。例如，用查询分析器来执行一组Transact-SQL语句，这组语句将被视为一个批处理来对待。,11.3 程序控制流语句,SQL Serve
33、r有以下几种指定批处理的方法。 (1) 应用程序作为一个执行单元发出的所有SQL语句构成一个批处理，并生成单个执行计划。 (2) 存储过程或触发器内的所有语句构成一个批处理。每个存储过程或触发器都编译为一个执行计划。 (3) 由EXECUTE语句执行的字符串是一个批处理，并编译为一个执行计划。例如， EXEC (SELECT * FROM 学生表) (4) 由sp_executesql系统存储过程执行的字符串是一个批处理，并编译为一个执行计划。例如， execute sp_executesql NSELECT * from Sales.dbo.employee,11.3 程序控制流语句,批处理
34、的结束和退出 GO是批处理的结束标志。当编译器执行到GO时会把GO前面的所有语句当成一个批处理来执行。 GO命令和Transact-SQL语句不可处在同一行上。但在GO命令行中可以包含注释。 在批处理的第一条语句后执行任何存储过程必须包含EXECUTE关键字。 局部(用户定义)变量的作用域限制在一个批处理中，不可在GO命令后引用。 RETURN可在任何时候从批处理中退出，而不执行位于RETURN之后的语句。,11.3 程序控制流语句,举例：使用GO命令将查询语句与其他语句隔离。 use studentdb go -批处理结束标志 select * from 学生 go -查询语句与其他语句隔离
35、,11.3 程序控制流语句,程序的缩进书写风格（对比以下两段代码）,缩进是保证代码整洁、易读、体现代码层次结构的主要手段。 同层次的代码在同层次的缩进层上。 遇到分支、循环体等需要将它的内容缩进一层。 缩进部分用Tab键，不要使用空格键。,while (最低平均成绩)=100 break Else continue end,while (最低平均成绩)=100breakElsecontinue end,11.3 程序控制流语句,PRINT语句 返回用户的信息，语法： PRINT any ASCII text | local_variable | FUNCTION | string_expr a
36、ny ASCII text一个文本字符串。 local_variable任意有效的字符数据类型变量。local_variable 必须是 char 或 varchar，或者能够隐式转换为这些数据类型。 FUNCTION是返回字符串结果的函数。 string_expr是返回字符串的表达式，消息字符串最长可达 8,000 个字符。,11.3 程序控制流语句,RAISERROR语句 比PRINT语句功能强大，返回用户定义的错误信息并设系统标志，记录发生错误。通过使用RAISERROR语句，客户端可以从sysmessages表中检索条目，或者使用用户指定的严重度和状态信息动态地生成一条消息，这条信息在定义后就作为服务器错误信息返回给客户端。 语法：RAISERROR ( msg_id | msg_str , severity , state , argument ,.n ) WITH option ,.n ,小结与提问,TSQL程序设计 建立程序设计的基本概念和逻辑思维 常量和变量 运算与表达式 程序控制流语句 SQL Server2000常用函数和语句,
