函数在T-SQL中的应用
在T-SQL查询中按用途,主要有四种:
I. 字符串函数
II. 日期函数
III. 数学函数
IV. 系统函数
一、 字符串函数:
n 字符串函数用于控制返回给用户的字符串,这些功能仅用于字符型数据。
例如以下常用的函数:
函数名 | 解释 | 举例 |
CharIndex | 用来寻找一个指定的字符串在另一个字符串中的起始位置 | Select CharIndex (‘dog’,’my dog’, 1) 返回值:4 |
Len | 返回传递给它的字符串长度 | Select len (‘T-SQL 查询’) 返回值:8 |
Upper | 把传递给它的字符串转换为大写 | Select upper (‘T-sql 查询’) 返回值:T-SQL 查询 |
Ltrim | 清除字符左边的空格 | Select Ltrim(‘ 张三 ’) 返回值:张三(后边的空格保存) |
Rtrim | 清除字符右边的空格 | Select Ltrim(‘ 张三 ’) 返回值:张三(前边的空格保存) |
Right | 从字符串的右边返回指定数目的字符串 | Select right (‘冯.诺依曼’, 3) 返回值:诺依曼 |
Replace | 替换一个字符串中的一个字符 | Select replace (‘亚历山大.乔治’,’治’,’大’) 返回值:亚历山大.乔大 |
Stuff | 在一个字符串中删除指定长度的字符,并在该位置插入一个新的字符 | Select stuff (‘ABCDEFG’,2,3,’我的地盘我做主’) 返回值:A我的地盘我做主EFG |
二、 日期函数:
l 在SQL server中不能直接对日期函数运用数学函数,需要使用日期函数操作日期值。那么在使用中有哪些日期函数呢?请看下表中所列的部分常用的日期函数。
函数名 | 解释 | 举例 |
GetDate | 取得当前的系统日期 | Select getdate () 返回值:今天的日期 |
DateAdd | 将指定的数值添加到指定的日期部分后的日期 | Select dateadd (mm,3,’01/01/2010’) 返回值:04/01/2010(当前的日期格式) |
DateDiff | 两个日期之间的指定日期部分的区别 | Select datediff (mm,’04/01/2010’,’05/01/2010’) 返回值:1 |
DateName | 日期中指定日期部分的字符串形式 | Select datename (dw,’05/01/2010’) 返回值:Saturday |
DatePart | 日期中指定日期部分的整数形式 | Select datepart (day,’04/30/2010’) 返回值:30 |
l 例如电信部门查询用户的通话时长、话费等。
例:--显示用户13701418965的2008年6月份的通话记录的通话时长--
Select '用户'+phonenumber+'通话时长'+cast(datediff(mi,starttime,endtime) as datetime)+'分钟' as 通话时长 From "history-call"
Where phonenumber='13701418965'
//在这个语句中我们就可以看到用了Datediff这个日期函数,用来取endtime和starttime之间的差值。
//在这里“+”是字符串拼接
//Cast函数主要是为了将数值型字段转换成字符型字段。
在这里有必要对日期类型单独说明一下
在SQL server中,默认支持字符串到日期类型的转换,所以有以下两种写法
1. Producttime between '2010-4-01 00:00:00' and '2010-4-30 23:59:59'
2. Producttime between cast( '2010-4-01 00:00:00' as datetime) and '2010-4-30 23:59:59'
3. 需要强调的是,无论何种写法,日期字符串写法都要符合“YYYY-MM-DD HH:MM:SS”或者“YYYY-MM-DD”的格式,“YYYY-MM-DD HH:MM:SS”分别代表年-月-日 小时:分钟:秒,书写时一定要严格遵守格式。
三、 数学函数:
u 讲完了日期函数、字符串函数,我们大概了解了这两种函数常见类型和用法。那么下面我们一起了解一下数学函数。
u 数学函数用于对数值进行代数运算,由于数据函数数量众多,所以我列举了以下几种常用到的。
函数名 | 解释 | 举例 |
Abs | 取数值表达式的绝对值 | Select abs(-19) 返回:19 |
Ceiling | 取大于或等于指定数值、表达式的最小整数 | Select ceiling(13.35) 返回:14 |
Floor | 取小于或等于指定数值、表达式的最大整数 | Select floor(13.65) 返回:13 |
Power | 取数值表达式的幂值 | Select power(10,2) 返回:100 |
Round | 将数值表达式四舍五入为指定精度 | Select round(20.513,2) 返回:20.51 |
Sign | 对于正数返回+1,对于负数返回-1,对于0则返回0 | Select sign (-13) 返回:-1 |
Sqrt | 取浮点表达式的平方根 | Select sqrt (16) 返回:4 |
u 了解了以下表格中的常见数学函数,现在我们用一个实例来说明一下。
(与日期函数的实例在同一个背景《电信》)
-----显示用户13701418965的2008年6月份的通话记录的通话时长(通话时长精确到分钟,不到一分钟的按一分钟计算.--
Select '用户'+phonenumber+'通话时长'+cast(ceiling(datediff(ss,starttime,endtime)/60.0) as datetime)+'分钟' as 通话时长
From "history-call"
--查询表名 --
Where phonenumber='13701418965'
--在这个语句中先用日期函数datediff取starttime和endtime的差值再/60.0划成“分钟”然后用数学函数ceiling取整即(不到一分钟的按一分钟计算)。--
--在这里有一个疑问为什么除以60.0而不是60,是这样的因为ceiling函数的参数是带小数的数字类型,所以要将60写成60.0。否则,被系统默认为整数,ceiling的取上限就失效了--。
四、系统函数:
Ø 系统函数是用来获取有关SQL server中对象和设置的系统信息,常用的系统函数有下面几种:
函数名 | 解释 | 举例 |
Convert | 数据类型转换 | Select convert(varcgar(5),12345) 返回:字符串 12345 |
Cast | 数据类型转换,与Convert相比语法简单、功能较少。 | 字符串转换成日期格式,cast(‘2010-05-01’as datetime) |
Current_user | 返回当前用户的名字 | Select Current_user返回:你登录的用户名 |
Datalength | 返回用于指定表达式的字节数 | Select datelength(‘世界杯’) 返回:3 |
Host_name | 返回当前用户所登录的计算机名字 | Select host_name() 返回:你所登录的计算机名字 |
System_user | 返回当前所登录的用户名 | Select system_user返回:你当前所登录的用户名 |
User_name | 从给定的用ID中返回用户名 | Select user_name(1) 返回:从任意数据库中返回“dbo” |
Ø 通过上面的介绍我们了解了四种函数,而这些函数都可以在T-SQL中混合使用,得到符合要求的查询输出。
Ø 那么下面我们还是来举一个实例说明一下吧!
--查询用户的欠费账单记录,要求并显示用户号码和欠费信息
--查询过程是在accountbill表里查询ispaid=0表示未缴费的账单,calltype=0市话,
--calltype=1长话,charge金额,accounttime为账单时间
--显示用户13701418965欠费账单中的市话费用
Select '用户'+phonenumber+'于'+CAST (DATEPART(YEAR,accounttime) as Varchar(10))+'年'+ CAST (DATEPART(MONTH,accounttime) as Varchar(10))+'月发生市话费用'+cast(charge as varchar(10))+'元'
From accountbill
Where phonenumber='13701418965' AND
IsPaid = 0 AND calltype=0 Order By accounttime
---显示用户13701418965欠费账单中的长话费用 ---
Select '用户'+phonenumber+'于'+CAST (DATEPART(YEAR,accounttime) as
Varchar(10))+'年'+ CAST (DATEPART(MONTH,accounttime) as Varchar(10))+'
月发生长话费用'+CAST(charge as varchar(10))+'元'
From accountbill
Where phonenumber ='13701418965' AND IsPaid = 0 AND calltype=1 Order By accounttime
函数的综合应用
实例:假设13701418965用户在2008-10-31 15:20:00激费,计算用户账单欠费的滞纳金。
Select DateDiff(dd,Cast((cast(year(Dateadd(mm,1,accounttime))
as varchar(10))+'-'+cast(MONTH(Dateadd(mm,1,accounttime))
as varchar(10))+'-26 00:00:00')
as datetime),Cast('2008-10-31 15:20:00' as datetime))
as 欠费天数,charge as 金额,accounttime as 账单日期
From accountbill
Where phonenumber='13701418965'
--滞纳金是超期的天数×欠费金额×1%来计算。用户缴费最后期限
--是用户账单时间所在月份的下个月的26日
--滞纳金是超期的天数×欠费金额×1%来计算。用户缴费最后期限
--是用户账单时间所在月份的下个月的26日。
--根据账单日期得到账单缴费最后日期
--计算缴费日期与账单最后日期的超期天数
--得出欠费天数和欠费的金额
--如果超期天数大于0,则滞纳金=超期天数×欠费金额