ITPub博客

首页 > 数据库 > Oracle > SQL函数大全

SQL函数大全

Oracle 作者:robinlin2008 时间:2013-08-21 14:53:13 0 删除 编辑


一:时间函数 
1. 当前系统日期、时间
     SQL :select getdate()
     Oracle:select sysdate from dual;
2. dateadd  在向指定日期加上一段时间的基础上,返回新的 datetime 值
  例如:指定日期加上30天
  select dateadd(day,30,CreateTime) from dbo.td_TelUserInfo_Log where PhoneNumber='13309711111'

3. datediff 返回跨两个指定日期的日期和时间边界数。
   select datediff(day, CreateTime , getdate())as 进入平台至今的天数 from dbo.td_TelUserInfo_Log where PhoneNumber='13309711111'
  select PhoneNumber ,max(CreateTime) as 最后订购日期,DateDiff(day,max(CreateTime),getdate()) 距离今天时间 from td_TelUserInfo_Log where ServiceState='1'and Phonenumber='13309711111'
  group by PhoneNumber

4. datepart 返回代表指定日期的指定日期部分的整数。
  SELECT DATEPART(year, CreateTime) as 进入平台年份  from dbo.td_TelUserInfo_Log where PhoneNumber='18997478913'
  SELECT DATEPART(MONTH, CreateTime) as 进入平台月份  from dbo.td_TelUserInfo_Log where PhoneNumber='18997478913'
  SELECT DATEPART(day, CreateTime) as 进入平台月份  from dbo.td_TelUserInfo_Log where PhoneNumber='18997478913'
5. datename 返回代表指定日期的指定日期部分的字符串
 SELECT datename(weekday, '1991-05-01') as 刘芸林生日的星期数

6.convert()函数:
CONVERT(data_type(length),data_to_be_converted,style)data_type(length) 规定目标数据类型(带有可选的长度)。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式。
例如:select 电话进入平台日期=convert(varchar(10),CreateTime),120) from dbo.td_TelUserInfo_Log where PhoneNumber='18997478913'
     select 当前时间=convert(varchar(8),getdate(),114)
Style ID Style 格式
100 或者 0 mon dd yyyy hh:miAM (或者 PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
109 或者 9 mon dd yyyy hh:mi:ss:mmmAM(或者 PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
113 或者 13 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
120 或者 20 yyyy-mm-dd hh:mi:ss(24h)
121 或者 21 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-ddThh:mm:ss.mmm(没有空格)
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM


7:DatePart (interval,date) 返回日期date中,interval指定部分所对应的整数值
  例如:select datepart(d,CreateTime) as 日期天数  from dbo.td_TelUserInfo_Log where PhoneNumber='18997447777'
 参数 interval的设定值如下:
 值缩写(Sql Server) (Access 和 ASP) 说明
 Year Yy yyyy 年 1753 ~ 9999
 Quarter Qq q  季 1 ~ 4
 Month Mm m  月1 ~ 12
 Day of year Dy y  一年的日数,一年中的第几日 1-366
 Day Dd d  日,1-31
 Weekday Dw w 一周的日数,一周中的第几日 1-7
 Week Wk ww  周,一年中的第几周 0 ~ 51
 Hour Hh h  时0 ~ 23
 Minute Mi n  分钟0 ~ 59
 Second Ss s 秒 0 ~ 59
 Millisecond Ms - 毫秒 0 ~ 999

二:统计类函数汇总
1.AVG(column) 返回某列的平均值
 例如: select avg(MessageId) 消息Id的平均值   from dbo.td_LongMessageCache  --超长短信缓存表 MessageId: 消息ID
2.COUNT(*) 语法
 例如:select count(*) as 统计三个运营商的总数 from dbo.td_Dict_Networks where SSID in('中国电信','中国移动','中国联通')
3.ABS(num) 返回该数的绝对值
  例如: select abs(-1.0)
4.语法CEILING (numeric_expression) 参数numeric_expression是精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。返回类型,返回与 numeric_expression 相同的类型。
例如:SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0) 
5.EXP(float_expression)函数返回指定float类型的表达式,返回类型float
  例如:DECLARE @var float
    SET @var = 378.615345498
    SELECT ’The EXP of the variable is: ’ + CONVERT(varchar,EXP(@var))
    GO

9.FLOOR ( numeric_expression )返回小于或等于所给数字表达式的最大整数。
 例如:SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)
10.ROUND (10.22 , length [ , function ] ) 四舍五入指定小数的值
 例如:select ROUND(748.58, -1)
 select ROUND(748.58, -2)
11.Trunc:截断指定小数的值(注:此功能在Oracle中使用)   cast(SQL Server 中)
 TRUNC(column|expression,n)截断列、表达式或值到n位小数,或者,如果n被忽略,那么n默认为0
 例如:ORACLE :SELECT TRUNC(45.923,2), TRUNC(45.923),TRUNC(45.923,-2)FROM DUAL;
    SQL  :select cast(-1.002 as int) ----value
12.取e为底的对数
 例如:S:select log(2.7182818284590451)--- value 1
    O:select ln(2.7182818284590451) value from dual; 1

13.取10为底对数
例如:S:select log10(10) ---value 1
   O:select log(10,10) value from dual;
14.取平方
 例如:S:select SQUARE(4) ---value 16
    :select power(4,2) value from dual 16

15.取平方根

例如:S:select SQRT(4) ---value 2
   O:select SQRT(4) value from dual 2
16.取符号 S:select sign(-8) ---value -1
     O:select sign(-8) value from dual -1
17.CONVERT()函数
对于简单类型转换,CONVERT()函数和CAST()函数的功能相同,只是语法不同。CAST()函数一般更容易使用,其功能也更简单。CONVERT()函数的优点是可以格式化日期和数值,它需要两个参数:第1个是目标数据类型,第2个是源数据。
  lr:SELECT CONVERT(int, '123')
   SELECT CONVERT(decimal(9,2), '123.4')


18;select getdate() as 'wawa_getdate' --当前时间
select getutcdate() as 'wawa_getutcdate' --获取utc时间
select day(getdate()) as 'wawa_day' --取出天
select month(getdate()) as 'wawa_month' --取出月
select year(getdate()) as 'wawa_year' --取出年
select dateadd(d,3,getdate()) as wawa_dateadd --加三天,注意'd'表示天,'m'表示月,'yy'表示年,下面一样
select datediff(d,'2004-07-01','2004-07-15') as wawa_datediff --计算两个时间的差
select datename(d,'2004-07-15') as wawa_datename --取出时间的某一部分
select datepart(d,getdate()) as wawa_datepart  --取出时间的某一部分,和上面的那个差不多

三:字符串函数
1.求字符序号
 例如:S:select ascii('a') ---value 97
    O:select ascii('a') value from dual
2.从序号求字符
 例如:S:select char(97) --value  97
    O:select chr(97) value from dual
3.子串位置 --返回3
 例如:S:select CHARINDEX('s','sdsq',2) value
    O:select INSTR('sdsq','s',2) value from dual
4.模糊子串的位置 --返回2,参数去掉中间%则返回7
  例如 S:select patindex('%d%q%','sdsfasdqe') --value 2
5.求子串
 S:select substring(Phonenumber, 1,3) 前三位 ,count(*) 总数 from
  (select PhoneNumber,substring(Phonenumber, 1 , 3)前三位 from td_TelUserInfo_Log)
   td_TelUserInfo_Log
    Group by substring(Phonenumber, 1,3)
 O:select substr('abcd',2,2) value from dual
6.子串代替 返回aijklmnef
  S:select stuff(Phonenumber, 1,3,555) 替换 ,count(*) 总数 from
  (select PhoneNumber,stuff(Phonenumber, 1 , 3, 555)替换 from td_TelUserInfo_Log)
   td_TelUserInfo_Log
    Group by stuff(Phonenumber, 1,3,555)
  O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual
7.长度
  例如S:select len(PhoneNumber) from dbo.td_TelUserInfo_Log where PhoneNumber='18997450119'   11
     select datalength(PhoneNumber) from dbo.td_TelUserInfo_Log where PhoneNumber='18997450119'   22
    O:length
8大小写转换 lower,upper
   例如:select Upper(AreaName) from  dbo.td_Dict_AreaInfo_Local where beginNum='111111'
      select lower(AreaName) from dbo.td_Dict_AreaInfo_Local where beginNum='111111'
9.左补空格(LPAD的第一个参数为空格则同space函数)
 例如:S:select space(5)+AreaName from dbo.td_Dict_AreaInfo_Local where beginNum='111111'
     select AreaName from dbo.td_Dict_AreaInfo_Local where beginNum='111111'
     O:select LPAD('abcd',14) value from dual

10.删除空格 S:ltrim,rtrim   O:ltrim,rtrim,trim
  例如:select ltrim(AreaName) from  dbo.td_Dict_AreaInfo_Local where beginNum='111111'
11重复字符串
  例如:select REPLICATE(AreaName,4) from  dbo.td_Dict_AreaInfo_Local where beginNum='111111'  ----重复四次
12.left right
   例如:select left(AreaName,2) from dbo.td_Dict_AreaInfo_Local where beginNum='111111'
13.reverse(),字符串逆序
   例如:select reverse(AreaName) from dbo.td_Dict_AreaInfo_Local where beginNum='111111'
14.stuff除指定长度的字符,并在指定的起点处插入另一组字符
 stuff(character_expression , start , length ,character_expression)
 例如:select stuff(AreaName,1,6,'Hello ') from dbo.td_Dict_AreaInfo_Local where beginNum='111111'
15.charindex(expression1 ,expression2  , start_location )或charindex(expression1 ,expression2 )
expression1在expression2 中的开始位置
  例如:select charIndex('h', AreaName) from dbo.td_Dict_AreaInfo_Local where beginNum='2222222'
16.patindex('%pattern%',expression) 返回指定表达式中某模式第一次出现的起始位置;
   如果在全部有效的文本和字符数据类型中没有找到该模式,则返回零。
  例如:select patindex('%ai%',AreaName) from dbo.td_Dict_AreaInfo_Local where beginNum='2222222'

 


     

 

 


   

 

 

 


 

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22109422/viewspace-1116338/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-07-14