第16章函数Word格式文档下载.docx
《第16章函数Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《第16章函数Word格式文档下载.docx（18页珍藏版）》请在冰豆网上搜索。
功能
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.错误的字符日期边界字符
SET@dt=#2005-3-11#
--c.错误的日期
SET@dt='
2005-4-31'
--d.超出日期范围
1700-1-1'
--e.SQLServer不支持的日期格式
1999-1-1下午2:
30'
GO
--2.与当前会话语言环境不匹配的日期
DECLARE@dtvarchar（50）
--设置当前会话评议环境为英文
SETLANGUAGEus_english
SET@dt=CONVERT（VARCHAR,GETDATE（））
SELECT@dt
--设置当前会话评议环境为简体中文
SETLANGUAGE简体中文
--3.日期在条件处理中的常见错误代码
--a.忽略了日期转换为字符后的格式
SELECT*FROMstudent
WHEREbirthdate='
1988-09-27'
WHEREbirthdateLIKE'
1988-09-27%'
WHERELEFT（birthdate,10）='
WHERECONVERT（CHAR（10）,birthdate,120）='
--b.错误的日期区间查询（查询年月至年月的数据）
WHEREYear（出生日期）=1988ANDMonth（出生日期）>
=2
ANDYear（出生日期）=1993ANDMonth（出生日期）<
=3
--正确的写法
WHERE出生日期between'
1988-04-01'
and'
1993-03-31'
--4推荐的日期时间处理方法
--a.查询当日的数据
SELECT*FROM进货表
WHERE进货日期>
=CONVERT（char（10）,GETDATE（）,120）
AND进货日期<
CONVERT（char（10）,GETDATE（）+1,120）
--b.查询最近分钟的记录
WHERE进货日期BETWEENDATEADD（Minute,-5,GETDATE（））ANDGETDATE（）
--c.查询年月的数据
='
20080401'
'
20080501'
--d.查询年月至月的记录
20080101'
【★】数学函数
●在使用数据库中的数据时，经常需要对数字数据进行数学运算，得到一个数值。
在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
（
）
select*fromrowtalbe
where名次between5and10
orderbyscoredesc
rank（）over（orderby列名）
考虑值相同情况，序号不连续
【例】执行下列代码，看不同点
selectrank（）over（orderbyscoredesc）名次,*
dense_rank（）over（orderby列名）
考虑值相同情况，序号连续。
selectdense_rank（）over（orderbyscoredesc）名次,*
ntile（n）over（orderby列名）
将数据分成n组，进行连续排序
selectntile（3）over（orderbyscoredesc）名次,*
【★】行集函数
●行集函数的特点是其返回的结果集，可以像表一样用在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
（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）
groupbysno
withcube
【例】如何取出列值为null或者为空格的数据行？
createtabletest
（uidint,
colorvarchar（20）
insertintotestvalues（1,'
'
insertintotestvalues（2,null）
insertintotestvalues（3,'
blue'
insertintotestvalues（4,'
red'
找出颜色为非空值的数据（是否包含空格）
select*fromtest
wherecolorisnotnull
找出颜色为空格值的数据（是否包含空值）
wherecolor<
>
查找颜色为空值或空格值的行
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
--2.生成随机日期
@RandMindatetime,@RandMaxdatetime
@RandMin='
20090101'
@RandMax='
20091231'
RandValue=DATEADD（Hour,
ABS（CHECKSUM（NEWID（）））%（1+DATEDIFF（Hour,@RandMax,@RandMin））,
@RandMin）
二．Round（）
SELECTROUND（123.9994,3）,ROUND（123.9995,3）
SELECTROUND（123.4545,2）;
SELECTROUND（123.45,-2）;
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
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（'
混合班'
））,
creatdatetime,
grdasdbo.grad_time（type,creat）
insertcla（id,name,type,creat）
select1,'
1班'
getdate（）
unionall
select2,'
2班'
select3,'
3班'
2008-5-1'
select4,'
4班'
select*fromcla
生成随机订单
/*订单号为日期时间+流水号*/
--创建表格
createtableorders
ordernochar（12）,--订单号
orderdatedatetimedefaultgetdate（）,--订单时间
totalnumint,--总数量
totalmoneymoney--总金额
--创建函数，按照规律产生订单号
createfunctionf1（@dtdatetime）
returnschar（12）
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
--测试
insertintoorders（orderno,totalnum,totalmoney）
values（'
201005140001'
100,234）
values（dbo.f1（getdate（））,110,234）
select*fromorders
dropfunctionf1
查看用户定义函数的信息
●MicrosoftSQLServer2005系统提供了几个可以用于查看用户定义函数信息的系统存储过程和目录视图。
使用这些工具，可以查看用户定义函数的定义、获取函数的架构和创建时间、列出指定函数所使用的对象等信息。
●可以使用sys.sql_modules、OBJECT_DEFINITION、sp_helptext等工具查看用户定义函数的定义，可以使用sys.objects、sys.parameters、sp_h
升级会员 