数据库

第8章Transact-SQL程序设计本章导读本章主要介绍了变量、运算符、函数、流程控制语句和注解等语言元素。学习目的与要求（1）理解变量、运算符和流程控制语句。（2）掌握函数的使用方法，能够使用流程控制语句编程。8.1Transact-SQL常用语言元素8.1.1变量变量有两种形式：用户自定义的局部变量局部变量必须先定义，后使用，被引用时要在其名称前加上标志“＠”。系统提供的全局变量全局变量被引用时要在其名称前加上标志“＠＠”。8.1.2DECLARE语句1．格式格式一：DECLARE@变量的名称数据类型[,...n]格式二：SELECT@变量的名称=表达式[,...n]2．功能1）DECLARE语句声明局部变量，变量初始化为NULL。2）格式二，在声明局部变量的同时，给变量赋值。3）局部变量名必须以@开头。定义局部变量@varsex和@varsno，并利用这些变量去查找女同学的姓名与学号。usestudentcourseDeclare@versexchar(2),@versnochar(8)set@varsex=‘女’select学号，姓名fromSwhere性别=@versex8.1.3注释1．格式格式一：/*注释文本*/格式二：--注释文本2．功能多行的注释必须用/*和*/指明。用于多行注释的样式规则是，第一行用/*开始，接下来的注释行用**开始，并且用*/结束注释。--注释可插入到单独行中或嵌套（只限--）在命令行的末端，用--插入的注释由换行字符分界。注释没有最大长度限制。服务器将不运行注释文本。8.1.4函数1．字符函数１）SUBSTRING函数格式：SUBSTRING(字符表达式,m[,n])功能：从字符表达式中的第m个字符开始截取n个字符，形成一个新字符串，m,n都是数值表达式。2）LTRIM函数格式：LTRIM(字符表达式)功能：删除字符串起始空格函数，返回varchar类型数据3）RTRIM函数格式：RTRIM(字符表达式)功能：删除字符串尾随空格函数，返回varchar类型数据。8.1.4函数1．字符函数4）RIGHT函数格式：RIGHT(字符表达式,数据表达式)功能：返回字符串中从右边开始指定个数的字符，返回varchar类型数据。5）LEFT函数格式：LEFT(字符表达式,数据表达式)功能：返回字符串中从左边开始指定个数的字符，返回varchar类型数据。6）UPPER函数格式：UPPER(字符表达式)功能：将小写字符数据转换为大写的字符表达式，返回varchar类型数据。检索所有学生的姓。返回每个电话号码的后四位以及学生的姓。selectdistinctsubstring(姓名,1,1)fromSselectleft(姓名,1)AS姓，right(电话，4)AS电话后四位fromsorderby学号8.1.4函数1．字符函数7）LOWER函数格式：LOWER(字符表达式)功能：将大写字符数据转换为小写的字符表达式，返回varchar类型数据。8）REVERSE函数格式：REVERSE(字符表达式)功能：返回字符表达式的反转。返回varchar类型数据。9）SPACE函数格式：SPACE(整数表达式)功能：返回由重复的空格组成的字符串。整数表达式的值表示空格个数。返回char类型数据。8.1.4函数1．字符函数10）STUFF函数格式：STUFF(字符表达式1,m,n,字符表达式2)功能：删除指定长度的字符并在指定的起始点插入另一组字符。m,n是整数，m指定删除和插入的开始位置,n指定要删除的字符数,最多删除到最后一个字符。如果m或n是负数，则返回空字符串。如果m比字符表达式1长，则返回空字符串。返回char类型数据。11）CHARINDEX函数格式：CHARINDEX(表达式1,表达式2[,m])功能：在表达式2的第m个字符开始查找表达式1起始字符位置。m是整数表达式，如果m是负数或缺省，则将从表达式2的起始位置开始搜索。返回int类型数据。8.1.4函数1．字符函数12）LEN函数格式：LEN(字符表达式)功能：返回给定字符串表达式的字符个数，不包含尾随空格。13）ASCII函数格式：ASCII(字符表达式)功能：返回给定字符串表达式的最左端字符的ASCII码值。返回整型值。14）CHAR函数格式：CHAR(整数表达式)功能：用于将ASCII码转换为字符，整数表达式的取值范围为0到255之间的整数，返回字符型数据值。显示学生的姓名和所在系，之间用逗号和两个空格分隔。将字符串redgreenblue中的green替换成black.判断blue在字符串redgreenblue中的起始位置，判断blue字符长度。selectrtrim(姓名)+’,’+space(2)+ltrim(系)fromsselectstuff(‘redgreenblue’4,5,‘black’)GOselectcharindex(‘blue’,‘redgreenblue’)AS起始位置，len(‘blue’)AS字符长度8.1.4函数2．数学函数1）ABS函数格式：ABS(数字表达式)功能：返回给定数字表达式的绝对值。2）EXP函数格式：EXP(数字表达式)功能：返回给定数字表达式的指数值。参数数字表达式是float类型的表达式。返回类型为float。3）SQRT函数格式：SQRT(数字表达式)功能：返回给定数字表达式的平方根。参数数字表达式是float类型的表达式。返回类型为float。8.1.4函数2．数学函数4）ROUND函数格式：ROUND(数字表达式，m)功能：返回返回数字表达式并四舍五入为指定的长度或精度。5）RAND函数格式：RAND([seed])功能：返回0到1之间的随机float值。参数seed为整型表达式。selectrand()ASRandom_Numberselectexp(1),sqrt(4),abs(-5.3)selectround(123.123456,0),round(123.123456,2),round(123.123456,-2)selectABS(-1.0),ABS(1.0),ABS(1.0)8.1.4函数3．日期和时间函数1）DATEADD函数格式：DATEADD(日期参数,数字,日期)功能：在向指定日期加上一段时间的基础上，返回新的datetime值。日期参数规定了新值的类型。参数有：Year、Month、Day、Week、Hour2）GETDATE函数格式：GETDATE()8.1.4函数3．日期和时间函数3）DAY函数格式：DAY(日期)功能：返回代表指定日期的“日”部分的整数。返回类型为int。4）YEAR函数格式：YEAR(日期)功能：返回表示指定日期中的年份的整数。返回类型为int。5）MONTH函数格式：MONTH(日期)功能：返回表示指定日期中的月份的整数。返回类型为int。8.1.4函数4．系统函数函数,DB_NAME()的功能是返回数据库的名称。函数HOST_NAME()的功能是返回服务器端计算机的名称。函数HOST_ID()的功能是返回服务器端计算机的ID号。函数USER_NAME()的功能是返回用户的数据库用户名。8.1.5PRINT1．格式PRINT文本字符串|@字符数据类型变量|@@返回字符串结果的函数|字符串表达式2．功能将用户定义的消息返回客户端。必须是char或varchar，或者能够隐式转换为这些数据类型。若要打印用户定义的错误信息（该消息中包含可由@@ERROR返回的错误号），请使用RAISERROR而不要使用PRINT。8.2T-SQL控制流语句8.2.1BEGIN...END语句1．格式BEGIN{Transact-SQL语句|语句块}END2．功能BEGIN...END语句将多个SQL语句组合成一组语句块，并将些语句块视为一个单元。BEGIN...END语句块允许嵌套。8.2T-SQL控制流语句8.2.2IF...ELSE语句1．格式IF逻辑表达式〈SQL语句1|语句块1〉[ELSE〈SQL语句2|语句块2〉2．功能IF...ELSE语句是双分支条件判断语句，根据某个条件的成立与否，来决定执行哪组语句。8.2T-SQL控制流语句8.2.3CASE函数1．格式格式1：简单CASE函数CASEInput_表达式WHENwhen_表达式1THENresult_表达式1[WHENwhen_表达式2THENresult_表达式2][...n][ELSEresult_表达式n]END格式2：搜索CASE函数CASEWHEN逻辑表达式1THENresult_表达式1[WHEN逻辑表达式2THENresult_表达式2][...n][ELSEresult_表达式n]END8.1.3注释2．功能１）ELSE参数是可选的。２）简单CASE函数的执行过程：计算Input_表达式的值，按书写顺序计算每个逻辑条件“Input_表达式=when_表达式”。返回第一个使逻辑条件“Input_表达式=when_表达式”为TRUE的result_表达式。如果所有的逻辑条件“Input_表达式=when_表达式”为FLASE，则返回ELSE后的result_表达式n；如果没有指定ELSE子句，则返回NULL值。３）CASE搜索函数的执行过程：按顺序计算每个WHEN子句的逻辑表达式。返回第一个使逻辑表达式为TRUE的result_表达式。如果所有的逻辑表达式为FLASE，则返回ELSE后的result_表达式n；如果没有指定ELSE子句，则返回NULL值。8.2.4GOTO1．格式GOTOlabel--改变执行……label:--定义标签2．功能GOTO语句将程序流程直接跳到指定标签处。标签定义位置可以在GOTO之前或之后。标签符可以为数字与字符的组合，但必须以“：”结尾。在GOTO语句之后的标签不能跟“：”。GOTO语句和标签可在过程、批处理或语句块中的任何位置使用,但不可跳转到批处理之外的标签处。GOTO语句可嵌套使用。8.2.5WHILE…CONTINUE…BREAK语句1．格式WHILE逻辑表达式{SQL语句|语句块}[BREAK]{SQL语句|语句块}[CONTINUE]……2．功能1）当逻辑表达式为真时，重复执行SQL语句或语句块，直到逻辑表达式为假。可以使用BREAK和CONTINUE语句改变WHILE循环的执行。2）END关键字为循环结束标记。BREAK语句可以完全退出本层WHILE循环，执行END后面的语句。3）CONTINUE语句回到循环的第一行命令，重新开始循环至少有一门课程成绩大于80分的学生人数。Declare@numIntSelect@num=(selectcount(distinct学号)fromSCwhere成绩80)If@num0Select@numAS‘成绩80的人数’使用IF语句，如果课程C03的平均成绩低于60,那么显示“不及格”；如果高于90，则显示“优秀”；其他则显示“合格”。USEstudentcoursedeclare@gIntSet@g=(selectavg(成绩)fromscwhere课程号=‘C03’)If(@g60)printcast(@gASchar(3))+’不及格’elseif(@g90)Printcast(@gASchar(3))+’优秀’elseprintcast(@gASchar(3))+’合格’使用CASE函数设置课程号为”C01”的课程的成绩级别，如果学和课程成绩小于60，那么设置类型为“不及格”；如果大于或等于90，则设置类型为“优秀”；其他则设置为合格；并将信息存到“等级”数据表中。usestudentcoursegoselect学号，级别=casewhen成绩60then‘不及格’when成绩90then‘优秀’else‘合格’END成绩INTO等级FROMSCwhere成绩ISNOTNULLAND课程号=‘