第十八讲T-SQL编程(一).ppt

第十八讲T-SQL编程（一）,教学目标掌握常用函数的语法格式及功能重点字符函数、日期时间函数及转换函数的使用,一、变量,变量分为：局部变量：局部变量必须以标记作为前缀，如age局部变量须先声明，后赋值，再使用全局变量：全局变量必须以标记作为前缀，如version全局变量由系统定义和维护，只能读取，不能修改,一、变量,变量分为：局部变量：局部变量必须以标记作为前缀，如age局部变量须先声明，后赋值，再使用全局变量：全局变量必须以标记作为前缀，如version全局变量由系统定义和维护，只能读取，不能修改,（一）局部变量：一般在批处理中进行申明、设置、引用，随着批处理的结束而消亡。1、局部变量的声明：DECLAREvariable_namedatatype,.n说明：局部变量名必须以开头，必须符合标识符的命名规则。局部变量的数据类型可以是除text,ntext,image之外的所有类型。如：DECLAREiINT,lastnamevarchar(20),2、为局部变量赋值：初值为NULL。（1）使用select语句：selectvariable_name=expressionfrom表名where条件例1：declaremycounterintselectmycounter=20例2：declaremaxsqldec(4,1),minsqldec(4,1)selectmaxsql=max(sqls),minsql=min(sqls)fromcjprintmaxsqlprintminsql,例3:declarenamevarchar(8)selectname=stunamefromxjglwhereaddr=娄底printname说明：如在一个查询中返回了多个值，只有最后一个查询结果被赋给了变量。注意：select语句的赋值功能和查询功能不能混合使用。如：declaremaxsqldec(4,1)selectmaxsql=max(sqls),avg(sqls)fromcj错误：向变量赋值的SELECT语句不能与数据检索操作结合使用。,2、使用SET语句：setvariable_name=expression例：declarexint,yint,zintsetx=10sety=20setz=x+yprintz例：declarerowsintsetrows=(selectcount(*)fromcj)printrows强调：set语句一次只能给一个变量赋值；select语句一次性可以给多个变量赋值。,课堂练习：计算并显示电子信息工程系学生各科目的平均分。要求使用局部变量分别用select语句、set语句完成。参考答案：使用select语句：usestudentdeclaremathavgdec(4,1),englishavgdec(4,1),sqlavgdec(4,1),javaavgdec(4,1)selectmathavg=avg(maths),englishavg=avg(english),sqlavg=avg(sqls),javaavg=avg(java)fromxjgl,cjwherexjgl.stuno=cj.stunoanddeptname=电子信息工程系printmathavgprintenglishavgprintsqlavgprintjavaavg,declaremathavg,englishavg,sqlavg,javaavgdec(4,1),Printmathavg,englishavg,sqlavg,javaavg,能否在此处插入GO?为什么？,使用set语句：declaremathavgdec(4,1),englishavgdec(4,1),sqlavgdec(4,1),javaavgdec(4,1)setmathavg=(selectavg(maths)fromxjgl,cjwherexjgl.stuno=cj.stunoanddeptname=电子信息工程系)setenglishavg=(selectavg(english)fromxjgl,cjwherexjgl.stuno=cj.stunoanddeptname=电子信息工程系)setsqlavg=(selectavg(sqls)fromxjgl,cjwherexjgl.stuno=cj.stunoanddeptname=电子信息工程系)setjavaavg=(selectavg(java)fromxjgl,cjwherexjgl.stuno=cj.stunoanddeptname=电子信息工程系),（二）全局变量SQLServer2000中支持的全局变量一共有33个，常用的全局变量如下：,二、函数,（一）字符串函数：1、ASCII语法格式：ASCII（字符表达式）功能：返回字符表达式中第一个字符的ASCII码值。2、CHAR语法格式：CHAR（整形表达式）功能：将ASCII码值转化为字符。3、LEFT/RIGHT语法格式：LEFT/RIGHT（字符表达式，长度）功能：返回从字符串左/右边开始指定个数的字符。,4、SUBSTRING语法格式：SUBSTRING（表达式，起始位置，长度）功能：返回指定表达式的一部分。5、STR语法格式：STR（表达式，长度，小数位数）功能：将数字数据转换为字符数据。6、LTRIM语法格式：LTRIM（字符表达式）功能：删除起始空格后返回字符表达式。7、RTRIM语法格式：RTRIM（字符表达式）功能：截断所有尾随空格后返回字符表达式。,8、REVERSE语法格式：REVERSE（字符表达式）功能：返回字符表达式的反转9、LEN语法格式：LEN（字符表达式）功能：返回字符（而不是字节）个数，不包括尾随空格10、STUFF语法格式：STUFF（字符表达式1，起始位置，长度，字符表达式2）功能：从字符表达式中删除指定长度的字符并在指定的起始点插入字符表达式2.,11、LOWER语法格式：LOWER（字符表达式）功能：将大写字母转换为小写字母。12、UPPER-将小写字母转换为大写字母。例：分析下列语句的执行结果selectsubstring(rtrim(ltrim(GOODMORNING),1,4)selectstuff(GOODMORNING,6,7,AFTERNOON),(二）日期和时间函数1、GETDATE（）：返回当前日期和时间。2、DAY（date):返回指定日期的天数。如：selectday(GETDATE()3、MONTH(date):返回指定日期的月份。4、YEAR(date):返回指定日期的年份。5、DATEADD(datepart,number,date):以datepart指定的方式，返回number与date之和。如：selectdateadd(day,4,2008-5-13)selectdateadd(month,4,2008-5-13)selectdateadd(year,4,2008-5-13),6、DATEDIFF(datepart,startdate,enddate):以datepart指定的方式返回enddate与startdate之差。如：selectdatediff(day,getdate(),2008-8-8)7、DATENAME(datepart,date):返回日期date中datepart指定部分所对应的字符串。如：selectdatename(year,2007-10-7)8、DATEPART(datepart,date):返回日期date中datepart指定部分所对应的整数值。如：selectdatepart(month,2007-10-7),(三）数学函数1、ABS(numeric_expression):求绝对值。2、CEILING(numeric_expression):返回大于或等于所给数字表达式的最小整数。3、FLOOR(numeric_expression):返回小于或等于所给数字表达式的最大整数。4、ROUND(numeric_expression，length):返回数字表达式并四舍五入为指定的长度。5、SIGN(numeric_expression):返回表达式的正（+1）、零(0)或负(-1)号。6、RAND(seed)：返回0到1之间的随机float值。7、SIN、COS、TAN、COT、EXP、LOG、LOG10、SQRT、ASIN、ACOS、ATAN例：selectceiling(36.89),floor(36.89),（四）转换函数1、CAST：把一个数据类型强制转换为另一种数据类型。语法格式：CAST(expressionASdata_type)说明：expression:任何有效的表达式。data_type:目标系统所提供的数据类型，但不能使用用户自定义的数据类型。如：selectcast(100aschar(6)2、CONVERT:语法格式：CONVERT(data_type(length),expression,style)功能：将expression的值转换为data_type数据类型。日期或货币数据类型转换为字符串数据时，可使用style参数说明数据转换格式。,例：使用T-SQL语句在xjgl表中找出与“王兰”的学号相邻的学生。declarenamevarchar(8)setname=王兰declarexhchar(6)selectxh=stunofromxjglwherestuname=nameselect*fromxjglwherestuno=0+convert(char(6),convert(int,xh)+1)orst