sql 函数

本文主要是介绍sql 函数，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

表：create table T_Person (FidNumber varchar(20),Fname varchar(20),FbrithDay datetime,
    FregDay datetime,Fweight decimal(10,2));

求绝对值：select Fweight-50,abs(Fweight-50),abs(-5.03) from t_person;

求幂函数：select Fweight,power(Fweight,-0.5),power(Fweight,2) from t_person;第二个参数为幂。

求平方根：select fweight,sqrt(fweight) from t_person;

求随机：mysql：select rand();

sqlserver：select rand(1234);

oracle：select dbms_random.value(10,50) from dual;>=10,<50;

select dbms_random.normal from dual;

select dbms_random.string(opt,len) from dual;

DB2:select SYSFUN.rand() from SYSIBM.SYSDUMMY1;

舍入最大值：select Fname,fweight,ceiling(fweight),ceil(fweight *-1) from t_person;

舍入最小值：select Fname,fweight,floor(fweight),floor(fweight *-1)from t_person;

四舍五入：select Fname,fweight,round(fweight,1),round(fweight *-1,0),round(fweight,-1) from t_person;后面一位是精度，可为负。

弧度转换为角度：mysql，server：select fname ,fweight,degrees(fweight) from t_person;

Oracle，db2：select fname ,fweight,(Fweight*180)/acos(-1) from t_person;
角度转弧度：select fname ,fweight,(Fweight*acos(-1))/180 from t_person;     select fname ,fweight,radians(fweight) from t_person;

求符号：select fname ,fweight,sign(Fweight) from t_person;大于0得1，<0=-1,=0=0;

 求发音差异：select soundex('jack'),soundex('jeck') from dual;
select fname,soundex(fname) from t_person;
db2，server有函数（1到4）来判断发音区别：select difference (fname,'merry') from t_person;

mysql求时间：select now(),sysdate(),current_date(),current_timestamp(),current_time();

server：求时间：select convert(varchar(50),getdate(),101) as riqi;
select convert(varchar(50),getdate(),108) as sj;

oracle：select to_char(sysdate(),'YYYYMMDD') from dual;

db2：select current timestamp/date/time from sysibm.sysdummyl;

日期加法：mysql：select fbrithday,date_add(fbrithday,interval 1 week) as wk,
date_add(fbrithday,interval 2 month) as mon,
date_add(fbrithday,interval 5 quarter) as week from t_person；

 server：dateadd(datepart,number,date)；

Oracle：用+，-计算时间天数，addmonths（date，number）计算月

db2：date+length   unit(单位：day，month，year)

类型转换：mysql：select cast('-30' as signed) as sig,
　　　　　　　　　　convert('36',unsigned) as usig；

sqlserver：convert（类型，转换值）与上面相反；Oracle与db2有专门类型转换函数。o：to_date(exp,format),to_char(exp,format)...;2:date(),int()。

空值转换：select fbrithday,fregday, coalesce(fbrithday,fregday,'2018-08-08') from t_person；从第一个参数开始判断是否为空，不为空则赋这个值。

简化版：M:IFNULL(EXP,VALUE),S:ISNULL(E,V),O:NVL(E,V);

这篇关于sql 函数的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！