第16章函数.docx
- 文档编号:3881323
- 上传时间:2022-11-26
- 格式:DOCX
- 页数:18
- 大小:33.80KB
第16章函数.docx
《第16章函数.docx》由会员分享,可在线阅读,更多相关《第16章函数.docx(18页珍藏版)》请在冰豆网上搜索。
第16章函数
第16章函数
教学目标
●理解函数的特点、作用和类型
●理解和掌握内置函数的类型和特点
●理解和掌握数学函数、字符串函数、安全函数、加密函数、系统函数的特点和使用方式
●理解和掌握标量函数的管理技术
●理解和掌握表值函数管理技术
教学难点和重点
●理解用户定义函数的特点、作用和类型,这是学习用户定义函数的基础。
理解和掌握标量函数的管理技术,这是最常使用的用户定义函数。
理解和掌握表值函数管理技术,这是实现高效管理数据库操作的技术之一。
教学过程
●概述各种
●内置函数
●创建用户定义函数
●上机实验
内置函数
●MicrosoftSQLServer2005系统提供了许多内置函数,这些函数可以完成许多特殊的操作,大大提高了系统的易用性。
本节分14个部分来介绍这些内置函数。
首先讲述函数的特点和类型。
然后,对于每一种函数类型进行详细的分析和研究。
函数的特点和类型
●可以把MicrosoftSQLServer2005系统提供的内置函数分为13种类型,每一种类型的内置函数都可以完成某种类型的操作,这些类型的函数名称和主要功能如表4-1所示。
【★】聚合函数
●聚合函数可以对一组值执行计算,并且返回单个值。
除了COUNT函数之外,其他的聚合函数都忽略空值。
MicrosoftSQLServer2005系统提供了13个聚合函数。
【★】配置函数
●配置函数用于返回当前配置选项的信息。
例如,如果希望得到当前数据库的时戳值,可以使用@@DBTS函数。
●MicrosoftSQLServer2005系统提供的15个配置函数如表4-3所示。
【★】加密函数
●MicrosoftSQLServer2005系统使用分层加密和密钥管理基础结构来加密数据,每一层都使用证书、非对称密钥和对称密钥的组合对它下面的一层进行加密。
加密函数用于支持加密、解密、数字签名等操作。
MicrosoftSQLServer2005系统提供的18个加密函数的名称和功能描述如表4-4所示。
【★】游标函数
●游标函数可以返回所定义的游标状态信息。
MicrosoftSQLServer2005系统提供的3个游标函数的名称和功能描述如表4-5所示。
【★】日期和时间函数
●很显然,日期和时间函数用于对日期和时间数据的加工。
MicrosoftSQLServer2005系统提供的9个日期和时间函数的名称和功能描述如表4-6所示。
函数
功能
DATEADD
在向指定日期加上一段时间的基础上,返回新的datetime值
DATEDIFF
返回两个日期/时间之间指定部分的差
DATENAME
返回日期的指定日期部分的字符串
DATEPART
返回日期的指定部分的整数
DAY
返回日期的天的日期部分
GETDATE
返回当前系统日期和时间
MONTH
返回代表指定日期月份
YEAR
返回表示指定日期中的年份
(1)常见函数的使用
通过向指定日期添加间隔,返回新的datetime值。
(2)日期处理中常见问题与处理建议
--1.错误的日期
--a.遗漏字符日期边界字符
declare@dtdatetime
SET@dt=2005-3-11
select@dt
解析结果:
sqlserver中日期和字符串是需要字符串边界符的,默认是单引号。
Sqlserver将1900-01-01作为基础日期,结果是1900-01-01之后的1991天。
--b.错误的字符日期边界字符
declare@dtdatetime
SET@dt=#2005-3-11#
--c.错误的日期
declare@dtdatetime
SET@dt='2005-4-31'
--d.超出日期范围
declare@dtdatetime
SET@dt='1700-1-1'
--e.SQLServer不支持的日期格式
declare@dtdatetime
SET@dt='1999-1-1下午2:
30'
GO
--2.与当前会话语言环境不匹配的日期
DECLARE@dtvarchar(50)
--设置当前会话评议环境为英文
SETLANGUAGEus_english
SET@dt=CONVERT(VARCHAR,GETDATE())
SELECT@dt
--设置当前会话评议环境为简体中文
SETLANGUAGE简体中文
SET@dt=CONVERT(VARCHAR,GETDATE())
SELECT@dt
--3.日期在条件处理中的常见错误代码
--a.忽略了日期转换为字符后的格式
SELECT*FROMstudent
WHEREbirthdate='1988-09-27'
SELECT*FROMstudent
WHEREbirthdateLIKE'1988-09-27%'
SELECT*FROMstudent
WHERELEFT(birthdate,10)='1988-09-27'
SELECT*FROMstudent
WHERECONVERT(CHAR(10),birthdate,120)='1988-09-27'
--b.错误的日期区间查询(查询年月至年月的数据)
SELECT*FROMstudent
WHEREYear(出生日期)=1988ANDMonth(出生日期)>=2
ANDYear(出生日期)=1993ANDMonth(出生日期)<=3
--正确的写法
SELECT*FROMstudent
WHERE出生日期between'1988-04-01'and'1993-03-31'
--4推荐的日期时间处理方法
--a.查询当日的数据
SELECT*FROM进货表
WHERE进货日期>=CONVERT(char(10),GETDATE(),120)
AND进货日期 --b.查询最近分钟的记录 SELECT*FROM进货表 WHERE进货日期BETWEENDATEADD(Minute,-5,GETDATE())ANDGETDATE() --c.查询年月的数据 SELECT*FROM进货表 WHERE进货日期>='20080401'AND进货日期<'20080501' --d.查询年月至月的记录 SELECT*FROM进货表 WHERE进货日期>='20080101'AND进货日期<'20080401' GO 【★】数学函数 ●在使用数据库中的数据时,经常需要对数字数据进行数学运算,得到一个数值。 在MicrosoftSQLServer2005系统中可以使用常见的数学函数参加各种数学运算,这些数学函数的名称和功能描述如表4-8所示。 函数 功能 ABS 返回给定数字表达式的绝对值 CEILING 返回大于或等于所给数字表达式的最小整数 FLOOR 返回小于或等于所给数字表达式的最大整数 POWER 返回给定表达式乘指定次方的值 RAND 返回0到1之间的随机float值 ROUND 返回数字表达式并四舍五入为指定的长度或精度 SIGN 返回给定表达式的正(+1)、零(0)或负(-1)号 SQUARE 返回给定表达式的平方 SQRT 返回给定表达式的平方根 selectround(123.05,1,1) 【★】元数据函数 ●通过使用系统提供的元数据函数,可以返回有关数据库和数据库对象的信息。 MicrosoftSQLServer2005系统提供的29个元数据函数的名称和功能描述如表4-9所示。 【★】排名函数 ●在MicrosoftSQLServer2005系统中,可以使用排名函数为分区中的数据进行排名。 这些排名函数如表4-10所示。 使用排名函数可以为分区中的每一行返回一个排名值。 在排名过程中,某些行的排名值有可能相同。 Transact-SQL提供下列排名函数: RANK NTILE DENSE_RANK ROW_NUMBER Row_number()over(orderby列名) 特点: 序号连续,不考虑值相同情况。 【例】按成绩由高到底排序,增加名次列。 selectrow_number()over(orderbyscoredesc)名次,* fromscore 【例】分页,显示5-10行的记录 withrowtalbe as ( selectrow_number()over(orderbyscoredesc)名次,* fromscore ) select*fromrowtalbe where名次between5and10 orderbyscoredesc rank()over(orderby列名) 特点: 考虑值相同情况,序号不连续 【例】执行下列代码,看不同点 selectrank()over(orderbyscoredesc)名次,* fromscore dense_rank()over(orderby列名) 特点: 考虑值相同情况,序号连续。 【例】执行下列代码,看不同点 selectdense_rank()over(orderbyscoredesc)名次,* fromscore ntile(n)over(orderby列名) 特点: 将数据分成n组,进行连续排序 【例】执行下列代码,看不同点 selectntile(3)over(orderbyscoredesc)名次,* fromscore 【★】行集函数 ●行集函数的特点是其返回的结果集,可以像表一样用在Transact-SQL语句中引用表对象的地方。 MicrosoftSQLServer2005系统提供了如表4-11所示的6个行集函数。 【★】安全函数 ●如何检索和查看有关安全性管理的信息呢? 可以使用安全函数。 安全函数可以返回有关用户、架构、角色等信息。 MicrosoftSQLServer2005系统提供的17个安全函数清单和功能描述如表4-12所示。 【★】字符串函数 ●对输入的字符串进行各种操作的函数被称为字符串函数。 就像数学函数一样,字符串函数也是经常使用的一类函数。 MicrosoftSQLServer2005系统提供的27个字符串函数的清单和功能描述如图所示。 函数 功能 ASCII 返回字符表达式最左端字符的ASCII代码值。 CHAR 将intASCII代码转换为字符的字符串函数 CHARINDEX 返回字符串中指定表达式的起始位置 LEFT 返回从字符串左边开始指定个数的字符 LEN 返回给定字符串表达式的字符(而不是字节)个数,其中不包含尾随空格 LOWER 将大写字符数据转换为小写字符数据后返回字符表达式 LTRIM 删除起始空格后返回字符表达式 REPLACE 字符串替换 REPLICATE 以指定的次数重复字符表达式 REVERSE 返回字符表达式的反转 RIGHT 返回字符串中从右边开始指定个数的字符 RTRIM 截断所有尾随空格后返回一个字符串 SPACE 返回由重复的空格组成的字符串 STR 由数字数据转换来的字符数据 STUFF 删除指定长度的字符并在指定的起始点插入另一组字符 SUBSTRING 返回字符、binary、text或image表达式的一部分 UPPER 返回将小写字符数据转换为大写的字符表达式 ●字符串函数使用实例: (1)--将字符串Dumondeentier的每个字符和asc码显示出来。 SETTEXTSIZE0 SETNOCOUNTON DECLARE@positionint,@stringchar(15) SET@position=1 SET@string='Dumondeentier' WHILE@position<=DATALENGTH(@string) BEGIN SELECTASCII(SUBSTRING(@string,@position,1)), SUBSTRING(@string,@position,1) SET@position=@position+1 END SETNOCOUNTOFF GO (2)replace函数 将日期时间格式转换为短格式(去掉多余的零)例如2010-10-01转换为2010-10-1 selectreplace(convert(varchar,getdate(),120),'-0','-') 【★】系统函数 ●在MicrosoftSQLServer2005系统中,对各种选项或对象进行操作或报告的函数被称为系统函数。 这些系统函数的清单和功能描述如表4-14所示。 函数 功能 COALESCE 返回其参数中第一个非空表达式 DATALENGTH 返回任何表达式所占用的字节数 HOST_NAME 返回工作站名称 ISNULL 使用指定的替换值替换NULL NEWID 创建uniqueidentifier类型的唯一值 NULLIF 如果两个指定的表达式相等,则返回空值 USER_NAME 返回给定标识号的用户数据库用户名 isnull的使用 格式isnull(表达式1,表达式2) 含义如果表达式1的值不是null,则返回表达式1的值,否则返回表达式2的值。 【例】显示每个学生的总成绩及所有学生的总成绩。 selectisnull(sno,'总成绩'),sum(score) fromscore groupbysno withcube 【例】如何取出列值为null或者为空格的数据行? createtabletest (uidint, colorvarchar(20) ) insertintotestvalues(1,'') insertintotestvalues(2,null) insertintotestvalues(3,'blue') insertintotestvalues(4,'red') 找出颜色为非空值的数据(是否包含空格) select*fromtest wherecolorisnotnull 找出颜色为空格值的数据(是否包含空值) select*fromtest wherecolor<>'' 查找颜色为空值或空格值的行 select*fromtest whereisnull(color,'')='' 【】系统统计函数 ●可以使用系统统计函数获取系统的各种统计信息。 MicrosoftSQLServer2005系统提供的12个系统统计函数的名称和功能描述如表4-15所示。 文本和图像函数 可以对文本或图像输入值进行操作的函数被称为文本和图像函数。 MicrosoftSQLServer2005系统提供的3个文本和图像函数 函数使用实例 一.随机数 (1) selectR1=rand (1),R2=rand (1) 结果: 生成2个相同的随机数 (2) selecttop3R1=rand(),R2=rand() fromsysobjects 结果: 虽然R1,R2不同,但3条记录相同 (3) 如何实现真正的随机数呢? 认识一下2个函数: Newid(): 创建uniqueidentifier类型的唯一值。 Checksum(): 按照表的某一行或一组表达式计算出来的校验和值,int类型。 --1.生成@@RandMin和@RandMax之间的随机数值 DECLARE @RandMinint,@RandMaxint --设置随机值的最小和最大值 SELECT @RandMin=0, @RandMax=100 --生成随机数 SELECTTOP100 RandValue=ABS(CHECKSUM(NEWID()))%(1+@RandMax-@RandMin)+@RandMin FROMsysobjectsO1,sysobjectsO2 GO --2.生成随机日期 DECLARE @RandMindatetime,@RandMaxdatetime --设置随机值的最小和最大值 SELECT @RandMin='20090101', @RandMax='20091231' --生成随机数 SELECTTOP100 RandValue=DATEADD(Hour, ABS(CHECKSUM(NEWID()))%(1+DATEDIFF(Hour,@RandMax,@RandMin)), @RandMin) FROMsysobjectsO1,sysobjectsO2 GO 二.Round() SELECTROUND(123.9994,3),ROUND(123.9995,3) GO SELECTROUND(123.4545,2); GO SELECTROUND(123.45,-2); GO 16.2用户自定义函数 用户定义函数是接受参数、执行操作并且将运算结果以值的形式返回的例程。 ●用户定义函数的结构和类型 标题和正文。 ●分类 用户定义函数又可以分为两大类,即用户定义标量函数和用户定义表值函数。 16.2.1创建用户定义函数 ●本节主要讲述创建用户定义函数时的考虑、使用CREATEFUNCTION语句创建用户定义函数,查看用户定义函数的信息等。 创建用户定义函数时的考虑 ●在MicrosoftSQLServer2005系统中,可以分别使用CREATEFUNCTION、ALTERFUNCTION、DROPFUNCTION语句来实现用户定义函数的创建、修改和删除操作。 在创建用户定义函数时,每个完全限定的用户函数名称(schema_name.function_name)必须唯一。 ●函数的BEGINEND块中的语句不能有任何副作用。 函数副作用是指对函数作用域之外的资源状态的任何永久性更改(例如修改数据库表)。 函数中的语句唯一能做的更改是对函数局部对象(如局部游标或局部变量)的更改。 不能在函数中执行的操作包括: 对数据库表的修改,对不在函数中的局部游标进行操作,发送电子邮件,尝试修改目录,以及生成返回至用户的结果集等。 使用CREATEFUNCTION语句 ●在MicrosoftSQLServer2005系统中,使用CREATEFUNCTION语句可以创建标量函数、内联表值函数、多语句表值函数。 须要说明的是,如果RETURNS子句指定了一种标量数据类型,则该函数为标量值。 如果RETURNS子句指定了TABLE,则该函数为表值函数。 根据函数主体的定义方式,表值函数可以分为内联函数和多语句函数。 内联函数可以用于获得参数化视图的功能。 --编写函数,根据班级的类型和创建时间计算班 --级的毕业时间 --@type: 班级类型,@crt_time: 创建时间 createfunctiongrad_time(@typevarchar(20),@crt_timedatetime) returnsdatetime as begin if@type='1年制' return(dateadd(year,1,@crt_time)) elseif@type='2年制' return(dateadd(year,2,@crt_time)) returnnull; end createtablecla (idint, namevarchar(20), typevarchar(20)check(typein('1年制','2年制','混合班')), creatdatetime, grdasdbo.grad_time(type,creat) ) insertcla(id,name,type,creat) select1,'1班','1年制',getdate() unionall select2,'2班','2年制',getdate() unionall select3,'3班','1年制','2008-5-1' unionall select4,'4班','2年制',getdate() select*fromcla 生成随机订单 /*订单号为日期时间+流水号*/ --创建表格 createtableorders ( ordernochar(12),--订单号 orderdatedatetimedefaultgetdate(),--订单时间 totalnumint,--总数量 totalmoneymoney--总金额 ) --创建函数,按照规律产生订单号 createfunctionf1(@dtdatetime) returnschar(12) as begin declare@dt1char(8),@strchar(12) set@dt1=convert(char(8),getdate(),112) select@str=@dt1+right(10001+right(isnull(max(orderno),0),4),4) fromorders whereordernolike@dt1+'%' return@str end --测试 insertintoorders(orderno,totalnum,totalmoney) values('201005140001',100,234) insertintoorders(orderno,totalnum,totalmoney) values(dbo.f1(getdate()),110,234) select*fromorders dropfunctionf1 查看用户定义函数的信息 ●MicrosoftSQLServer2005系统提供了几个可以用于查看用户定义函数信息的系统存储过程和目录视图。 使用这些工具,可以查看用户定义函数的定义、获取函数的架构和创建时间、列出指定函数所使用的对象等信息。 ●可以使用sys.sql_modules、OBJECT_DEFINITION、sp_helptext等工具查看用户定义函数的定义,可以使用sys.objects、sys.parameters、sp_h
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第16章 函数 16