系统内置函数

授课进度1班：第周，第次课（2学时）2班：第周，第次课（2学时）授课日期1班：2015年月日2班：2015年月日授课题目(教学章、节或主题)第七章函数7.1系统内置函数教学目标1.熟悉SQLServer2008中系统内置函数的几种主要分类。2.掌握常用聚合函数的功能并正确使用；3.掌握常用数学函数的功能并正确使用；4.掌握常用字符串函数的功能并正确使用；5.掌握常用日期时间函数的功能并正确使用；6.掌握转换函数的功能并正确使用。教学重点常用系统内置函数的功能及使用。教学难点常用聚合函数的正确使用。教学方法请选择你授课时所采用的教学方法（在括号中画“√”）：讲授法﹝√﹞，讨论法﹝﹞，演示法﹝√﹞，案例法﹝﹞，发现法﹝﹞，探究法﹝﹞，谈话法﹝﹞，实验法﹝﹞，参观法﹝﹞，考察法﹝﹞，自学辅导法﹝﹞，练习法（习题或操作课）﹝﹞，读书指导法﹝﹞，听说法﹝﹞，写生法﹝﹞，视唱法﹝﹞，工序法（技能课）﹝﹞，实习作业法﹝﹞，其他﹝﹞教学手段请选择你授课时所采用的教学手段（在括号中画“√”）：实物﹝﹞，多媒体﹝√﹞，投影﹝﹞，影像﹝﹞，CAI课件﹝﹞，PPT﹝﹞，标本﹝﹞，挂图﹝﹞，模型﹝﹞，其他﹝﹞讨论、思考题、作业参考文献2教学过程及内容复习并导入新课复习：提问BREAK和CONTINUE的区别？BREAK和CONTINUE都可以用于退出循环，但BREAK是退出整个当前该层循环，此层循环不再执行，而CONTINUE只是退出当前该层循环的某一次操作，之后循环继续执行。导入：提问：有没有这样一种方法，它既能像存储过程那样封装一些复杂的T-SQL代码，并根据需要设置参数，同时又能返回程序所需的值呢？在SQLSERVER2005中，函数是用于封装频繁执行的逻辑的例程。任何必须执行的代码都可调用函数，而无需重复所有的函数逻辑。第七章sqlserver函数7.1系统内置函数一、函数函数是—组编译好的Transact-SQL语句，它们可以带一个或一组数值做参数，也可不带参数，它返回一个数值、数值集合，或执行一些操作。函数能够重复执行一些操作，从而避免不断重写代码。函数的组成部分?1.函数名称2.函数体3.参数（可以为：无参数的、单个参数的、多个参数的）4.返回值(注意类型问题)SQLServer2008支持两种函数类型：1.内置函数:是一组预定义的函数，是Transact-SQL语言的一部分，按Transact-SQL参考中定义的方式运行且不能修改。2.用户定义函数:由用户定义的Transact-SQL函数。它将频繁执行的功能语句块封装到一个命名实体中，该实体可以由Transact-SQL语句调用。二、系统内置函数1.标量函数：对单一值操作，返回单一值。2.聚合函数：对一组值进行计算后，向调用者返回单一的值。对于每一个系统函数需要掌握两点：函数功能调用形式参数个数及其作用三、标量函数3标量函数的特点：输入参数的类型为基本类型，返回值也为基本类型。1．转换函数（重点掌握）CAST、CONVERT这两个函数的功能都是实现数据类型的转换，但CONVERT的功能更强一些。常用的类型转换有以下几种情况：日期型→字符型、字符型→日期型、数值型→字符型。函数功能convert(data_type[(length)],expression)[,style])把表达式expression的数据类型转换成data_type类型，style为日期格式样式。cast(expressionasdata_type)把表达式expression的数据类型转换成data_type类型，但格式转换没有convert()灵活转换函数convert()中参数style取值不带世纪带世纪标准输出格式-0或100默认值monddyyyyhh:miAM(或PM)1101美国mm/dd/yyyy2102ANSIyy.mm.dd3103英国/法国dd/mm/yy4104德国dd.mm.yy5105意大利dd-mm-yy61015-ddmonyy7107-mondd,yy8108-hh:mi:ss-9或者109默认值+毫秒monddyyyyhh:mi:msAM或PM10110美国mm-dd-yy11111日本yy/mm/dd12112ISOyymmdd-13或113欧洲+毫秒ddmonyyyyhh:mi:ss:ms(24h)14114-hh:mi:ss:ms(24h)实例1如下程序将检索成绩在50～59分之间的学生姓名，并将成绩转换为char(20)。/*如下例子同时使用CAST和CONVERT*//*使用CAST实现*/SELECT姓名,成绩FROMXS_KCWHERECAST(成绩ASchar(20))LIKE'5_'/*使用CONVERT实现*/SELECT姓名,成绩FROMXS_KC4WHERECONVERT(char(20),成绩)LIKE'5_'2．数学函数（重点掌握）SQLServer的数学函数主要用来对数值表达式进行数学运算并返回运算结果。数学函数可对SQLServer提供的数字数据（decimal、integer、float、real、money、smallmoney、smallint和tinyint）进行数学运算并返回运算结果。在默认情况下，对float数据类型数据的内置运算的精度为6个小数位。常用数学函数实例2求半径为1英寸、高为5英寸的圆柱容积。DECLARE@hfloat,@rfloatSET@h=5SET@r=1SELECTPI()*SQUARE(@r)*@hAS'圆柱容积'实例3求1.00到10.00之间的数字的平方根。DECLARE@myvaluefloatSET@myvalue=1.00WHILE@myvalue10.00BEGINSELECTSQRT(@myvalue)SELECT@myvalue=@myvalue+1ENDGO3．日期时间函数（重点掌握）SELECTSQRT(9)返回：3取浮点表达式的平方根SqrtSELECTABS(-43)返回：43取数值表达式的绝对值ABS举例描述函数名SELECTCEILING(43.5)返回：44返回大于或等于所给数字表达式的最小整数CEILINGSELECTPOWER(5,2)返回：25取数值表达式的幂值POWERSELECTROUND(43.543,1)返回：43.5将数值表达式四舍五入为指定精度ROUNDSELECTPI()返回：3.14159265358979返回圆周率PISELECTFLOOR(43.5)返回：43取小于或等于指定表达式的最大整数FLOOR5日期时间函数用来操作date、datetime和smalldatetime类型的数据，执行算术运算。与其他函数一样，可以在SELECT语句和WHERE子句以及表达式中使用日期时间函数。常用日期时间函数函数名称及格式描述Getdate()返回当前系统的日期和时间Datename(datepart,date_expr)以字符串形式返回date_expr中的指定部分，如果合适的话还将其转换为名称(如June)Datepart(datepart,date_expr)以整数形式返回date_expr中的datepart指定部分Datediff(datepart,date_expr1,date_expr2)以datepart指定的方式，返回date_expr2与date_expr1之差Dateadd(datepart,number,date_expr)返回以datepart指定方式表示的date_expr加上number以后的日期Day(date_expr)返回date_expr中的日期值Month(date_expr)返回date_expr中的月份值Year(date_expr)返回date_expr中的年份值日期部分日期部分写法取值范围Yearyy1753~9999Quarterqq1~4Monthmm1~12Dayofyeardy1~366Daydd1~31Weekwk1~54Weekdaydw1~7(Mon~Sun)Hourhh0~23Minutemi0~59Secondss0~59Millisecondms0~999实例4将地区是大连的学生的入学日期推后5天。updateXSQKset入学日期=dateadd(d,5,入学日期)6where籍贯='大连‘实例5查询出所有学生入学当天的月份数。selectdatename(M,入学日期)fromXSQKselectdatepart(M,入学日期)fromXSQKselectmonth(入学日期)fromXSQK实例6显示系统当前的日期。selectgetdate()printgetdate()实例7求xsqk表中陈伟的年龄，并显示为“年龄是：XX岁。”。declare@avarchar(30)select@a=convert(varchar(4),datediff(yy,出生日期,getdate()))fromxsqkwhere姓名='陈伟'print‘年龄是：’+@a+'岁。'课堂练习：将系统当前的日期显示为“现在的时间是：XX”。print‘现在的时间是：’+convert(varchar(30),getdate())print‘现在的时间是：’+cast(getdate()asvarchar(30))4．字符串函数（重点掌握）字符串函数可以对二进制数据、字符串和表达式执行不同的运算。大多数字符串函数只能用于char和varchar数据类型，少数几个字符串函数也可以用于binary、varbinary数据类型，还有某些字符串函数能够处理text、ntext、image数据类型的数据。常用字符串函数函数功能ascii(char_expr)第一个字符的ASCII值char(int_expr)相同ASCII代码值的字符charindex(expr1,expr2[,start])在expr2中从start位置开始查找expr1第一次出现的位置ltrim(expr)去字符串左边空格lower(expr)将字符串大写字母转换成小写字母replace(expr1,expr2,expr3)将expr1中所有子串expr2替换成expr3len(expr)测字符串长度，返回字符串长度left(expr,n)返回从左边开始n个字符组成的字符串，n=0，返回空串right(expr,n)返回从右边开始n个字符组成的字符串，n=0，返回空串space(n)返回包括n个空格的字符串substring(expr,start,length)取子串str(expr[,length[,decimal])将数字数据转换为字符数据upper(expr)将字符串中小写字母转换成大写字母7reverse(expr)按相反顺序返回字符串rtrim(expr)去字符串右边空格实例8查询出所有学生的名，即不显示姓氏。selectright(rtrim(姓名),len(ltrim(姓名))-1)fromXSQK课堂练习显示kc表中课程号为“006”的课程名称的长度declare@changdusmallintselect@changdu=len(课程名)fromkcwhere课程号=‘006’select@changduas课程名称的长度实例9已知变量@x中的值为“ABCDefghi”，要求将其转换为大写字母并显示。declare@xvarchar(20)--声明变量select@x=‘ABCDefghi’--给变量赋值print@x--显示原字符串printupper(@x)--显示转换后的大写字符串课堂练习将@x中的字符串转换成小写字母输出。5．聚合函数（重点掌握）用来在查询结果集中产生累加和、平均值、记录个数、最大值、最小值等汇总性的数据，并返回一个单值。聚合函数在以下情况下，允许作为表达式使用：SELECT语句的选择列表（子查询或外部查询）。COMPUTE或COMPUTEBY子句。HAVING子句。聚合函数的作用