SQL常用语句及函数方法.doc

1、通常用到的字符串转日期格式 select * from a except( select a.mail,a.name,a.pass,a.time from a inner join c on a.mail=c.mail and a.name=c.name and a.pass=c.pass) Oracle中minus差集，intersect交集，union并集 select * from a minus( select a.mail,a.name,a.pass,a.time from a inner join c on a.mail=c.mail and a.name=c.name and a.pass=c.pass) merge用法： Merge into table1 Using table2 on (table1.id=table2.id and table1.name=table2.name) When matched then Update set table1.name=table2.name,table1.mail=table2.mail When not matched then Insert (id,name,mail) values(table2.id,table2.name,table2.mail) 3、数学函数 序号 函数功能 SQL Server用法 Oracle用法 1 绝对值 select abs(-1) value select abs(-1) value from dual 2 取整(大) select ceiling(-1.001) value select ceil(-1.001) value from dual 3 取整(小) select floor(-1.001) value select floor(-1.001) value from dual 4 取整（截取） select cast(-1.002 as int) value select trunc(-1.002) value from dual 5 四舍五入 select round(1.23456,4) value select round(1.23456,4) value from dual 6 为底的幂 select Exp(1) value select Exp(1) value from dual 7 取e为底的对数 select log(2.7182818284590451) select ln(2.7182818284590451) 8 取10为底对数 select log10(10) select log(10,10) 9 取平方 select SQUARE(4) select power(4,2) 10 取平方根 select SQRT(4) select SQRT(4) 11 求任意数为底的幂 select power(3,4) select power(3,4) 12 取随机数 select rand() select sys.dbms_random.value(0,1) 13 取符号 select sign(-8) select sign(-8) 14 圆周率 SELECT PI() ? 15 sin,cos,tan select sin(PI()/2) select sin(PI()/2) 16 求集合最大值 select max(value) value from　　(select 1 value　　union　　select -2 value　　union　　select 4 value　　union　　select 3 value)a select greatest(1,-2,4,3) value from dual 17 求集合最小值 select min(value) value from　　(select 1 value　　union　　select -2 value　　union　　select 4 value　　union　　select 3 value)a select least(1,-2,4,3) value from dual 18 处理null值(F2中的null以10代替) select F1,IsNull(F2,10) value from Tbl select F1,nvl(F2,10) value from Tbl ? 4、数值间比较 序号 函数功能 SQL Server用法 Oracle用法 1 求字符序号 select ascii(a) select ascii(