系统内置函数

1、授课进度1班：第 周，第 次课（ 2 学时）2班：第 周，第 次课（ 2 学时）授课日期1班：2015年 月 日2班：2015年 月 日授课题目(教学章、节或主题)第七章 函数 7.1系统内置函数教学目标1. 熟悉SQLServer2008中系统内置函数的几种主要分类。2. 掌握常用聚合函数的功能并正确使用；3. 掌握常用数学函数的功能并正确使用；4. 掌握常用字符串函数的功能并正确使用；5. 掌握常用日期时间函数的功能并正确使用；6. 掌握转换函数的功能并正确使用。教学重点常用系统内置函数的功能及使用。教学难点常用聚合函数的正确使用。教学方法请选择你授课时所采用的教学方法（在括号中画“”）：

2、讲授法，讨论法，演示法，案例法，发现法，探究法，谈话法，实验法，参观法，考察法，自学辅导法，练习法（习题或操作课），读书指导法，听说法，写生法，视唱法，工序法（技能课），实习作业法，其他教学手段请选择你授课时所采用的教学手段（在括号中画“”）：实物，多媒体，投影，影像，CAI课件，PPT，标本，挂图，模型，其他讨论、思考题、作业参考文献9教学过程及内容复习并导入新课复习：Ø 提问BREAK和CONTINUE的区别？BREAK和CONTINUE都可以用于退出循环，但BREAK是退出整个当前该层循环，此层循环不再执行，而CONTINUE只是退出当前该层循环的某一次操作，之后循环继续执行。

3、 导入：Ø 提问： 有没有这样一种方法，它既能像存储过程那样封装一些复杂的T-SQL 代码，并根据需要设置参数，同时又能返回程序所需的值呢？ 在SQL SERVER 2005 中，函数是用于封装频繁执行的逻辑的例程。任何必须执行的代码都可调用函数，而无需重复所有的函数逻辑。第七章sqlserver函数7.1系统内置函数一、 函数 函数是组编译好的Transact-SQL语句，它们可以带一个或一组数值做参数，也可不带参数，它返回一个数值、数值集合，或执行一些操作。函数能够重复执行一些操作，从而避免不断重写代码。Ø 函数的组成部分?1. 函数名称2. 函数体3. 参数（可以为：

4、无参数的、单个参数的、多个参数的）4. 返回值(注意类型问题) SQL Server 2008支持两种函数类型：1. 内置函数:是一组预定义的函数，是Transact-SQL语言的一部分，按Transact-SQL参考中定义的方式运行且不能修改。2. 用户定义函数:由用户定义的Transact-SQL函数。它将频繁执行的功能语句块封装到一个命名实体中，该实体可以由Transact-SQL语句调用。 二、 系统内置函数 1. 标量函数：对单一值操作，返回单一值。 2. 聚合函数：对一组值进行计算后，向调用者返回单一的值。对于每一个系统函数需要掌握两点：Ø 函数功能Ø 调用形式

5、<参数个数及其作用>三、标量函数标量函数的特点：输入参数的类型为基本类型，返回值也为基本类型。1转换函数（重点掌握）CAST、CONVERT这两个函数的功能都是实现数据类型的转换，但CONVERT的功能更强一些。常用的类型转换有以下几种情况：日期型字符型、字符型日期型、数值型字符型。函数功能convert(data_type(length),expression),style)把表达式expression的数据类型转换成data_type类型，style为日期格式样式。cast(expression as data_type)把表达式expression的数据类型转换成data_t

6、ype类型，但格式转换没有convert()灵活转换函数convert()中参数style取值不带世纪带世纪标准输出格式-0或100默认值mon dd yyyy hh:miAM(或PM)1101美国mm/dd/yyyy2102ANSIyy.mm.dd3103英国/法国dd/mm/yy4104德国dd.mm.yy5105意大利dd-mm-yy61015-dd mon yy7107-mon dd,yy8108-hh:mi:ss-9或者109默认值+毫秒mon dd yyyy hh:mi:msAM或PM10110美国mm-dd-yy11111日本yy/mm/dd12112ISOyymmdd-13或1

7、13欧洲+毫秒dd mon yyyy hh:mi:ss:ms(24h)14114-hh:mi:ss:ms(24h)实例1 如下程序将检索成绩在5059分之间的学生姓名，并将成绩转换为char(20)。/*如下例子同时使用 CAST 和 CONVERT*/*使用CAST实现*/ SELECT 姓名,成绩FROM XS_KCWHERE CAST(成绩 AS char(20) LIKE '5_' /*使用 CONVERT实现*/ SELECT 姓名,成绩FROM XS_KCWHERE CONVERT(char(20),成绩) LIKE '5_' 2数学函数（重点掌握）

8、SQL Server的数学函数主要用来对数值表达式进行数学运算并返回运算结果。数学函数可对SQL Server提供的数字数据（decimal、integer、float、real、money、smallmoney、smallint 和 tinyint）进行数学运算并返回运算结果。在默认情况下，对float数据类型数据的内置运算的精度为6个小数位。常用数学函数SELECT SQRT(9)返回：3取浮点表达式的平方根SqrtSELECT ABS(-43)返回：43取数值表达式的绝对值ABS举例描述函数名SELECT CEILING(43.5)返回：44返回大于或等于所给数字表达式的最小整数CEIL

9、INGSELECT POWER(5,2)返回：25取数值表达式的幂值POWERSELECT ROUND(43.543,1)返回：43.5将数值表达式四舍五入为指定精度ROUNDSELECT PI()返回：3.14159265358979返回圆周率PISELECT FLOOR(43.5)返回：43取小于或等于指定表达式的最大整数FLOOR实例2 求半径为 1 英寸、高为 5 英寸的圆柱容积。 DECLARE h float, r float SET h = 5 SET r = 1 SELECT PI()* SQUARE(r)* h AS '圆柱容积'实例3 求 1.00 到 10

10、.00 之间的数字的平方根。 DECLARE myvalue float SET myvalue = 1.00 WHILE myvalue < 10.00 BEGIN SELECT SQRT(myvalue) SELECT myvalue = myvalue + 1 END GO3日期时间函数（重点掌握）日期时间函数用来操作date、datetime和smalldatetime类型的数据，执行算术运算。与其他函数一样，可以在SELECT语句和WHERE子句以及表达式中使用日期时间函数。常用日期时间函数函数名称及格式描 述Getdate()返回当前系统的日期和时间Datename(date

11、part, 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_exp

12、r中的日期值Month(date_expr)返回date_expr中的月份值Year(date_expr)返回date_expr中的年份值日期部分日期部分写 法取值范围Yearyy17539999Quarterqq14Monthmm112Dayofyeardy1366Daydd131Weekwk154Weekdaydw17(MonSun)Hourhh023Minutemi059Secondss059Millisecondms0999实例4 将地区是大连的学生的入学日期推后5天。 update XSQK set 入学日期 =dateadd(d,5,入学日期 ) where 籍贯='大连实

13、例5 查询出所有学生入学当天的月份数。 select datename(M,入学日期 ) from XSQK select datepart(M,入学日期 ) from XSQKØ select month(入学日期 ) from XSQK 实例6 显示系统当前的日期。select getdate( )print getdate( )实例7 求xsqk表中陈伟的年龄，并显示为“年龄是：XX岁。”。declare a varchar(30)select a = convert(varchar(4), datediff (yy, 出生日期 , getdate() ) )from xsqk

14、 where 姓名='陈伟'print 年龄是： + a + '岁。'课堂练习：将系统当前的日期显示为“现在的时间是：XX”。print 现在的时间是：+ convert(varchar(30), getdate( )print 现在的时间是：+ cast(getdate( ) as varchar(30)4字符串函数（重点掌握）字符串函数可以对二进制数据、字符串和表达式执行不同的运算。大多数字符串函数只能用于char和varchar数据类型，少数几个字符串函数也可以用于binary、varbinary数据类型，还有某些字符串函数能够处理text、ntext、i

15、mage数据类型的数据。常用字符串函数函数功能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

16、(expr,n)返回从右边开始n个字符组成的字符串，n=0，返回空串space(n)返回包括n个空格的字符串substring(expr,start,length)取子串str(expr,length,decimal)将数字数据转换为字符数据upper(expr)将字符串中小写字母转换成大写字母reverse(expr)按相反顺序返回字符串rtrim(expr)去字符串右边空格实例8 查询出所有学生的名，即不显示姓氏。 select right(rtrim(姓名), len(ltrim(姓名)-1) from XSQK课堂练习 显示kc表中课程号为“006”的课程名称的长度declare ch

17、angdu smallintselect changdu=len(课程名) from kc where 课程号=006select changdu as 课程名称的长度实例9 已知变量x中的值为“ABCDefghi”，要求将其转换为大写字母并显示。declare x varchar(20) -声明变量select x=ABCDefghi -给变量赋值print x -显示原字符串print upper(x) -显示转换后的大写字符串课堂练习 将x中的字符串转换成小写字母输出。5聚合函数（重点掌握）用来在查询结果集中产生累加和、平均值、记录个数、最大值、最小值等汇总性的数据，并返回一个单值。 聚

18、合函数在以下情况下，允许作为表达式使用：Ø SELECT语句的选择列表（子查询或外部查询）。Ø COMPUTE或COMPUTE BY子句。Ø HAVING子句。聚合函数的作用范围：既可以是一个表中的全部记录，也可以是由where子句指定的该表的一个子集。Ø 语句格式：select 聚合函数(列名) from 表名 where 条件 Ø 说明：当select子句中有聚合函数且没有group by子句时，就不能再指定其它的列名。SQL Server提供的常用聚合函数有：ü AVG函数： avg ( all | distinct 列名) ü SUM函数： sum ( all | distinct 列名) ü MAX函数： max ( all | distinct 列名) ü MIN函数： min ( all | distinct 列名)ü Count函数：count ( * | all | distinct 列名)Ø 说明：All ：计算该列值非空的记录的个数，默认为all。Dis