MySQL和Oracle中函数的区别
MySQL | Oracle |
---|---|
concat() 可以拼接多个字符串 | concat() 只能拼接两个字符串,拼接多个用“||” |
IFNULL(a,b) 如果a为空,就显示b的值 | nvl(x,value) 如果x为空,就返回value值,否则返回x本身 |
substring(‘abcd’,2,2) | SUBSTR(‘abcd’,2,2) |
用IF或者CASE代替 | decode(要判断的值,值1, 翻译值1,值2,翻译值2,…,缺省值) |
date_format/time_format select date_format(now(),’%Y-%m-%d’); | TO_CHAR(date,format) select to_char(sysdate,’yyyy-mm-dd’) from dual; |
STR_TO_DATE(str,format) SELECT STR_TO_DATE(‘2004-03-01’, ‘%Y-%m-%d’) VAULE | to_date(str,format) SELECT to_date(‘2009-3-6’,’yyyy-mm-dd’) VAULE FROM DUAL |
CAST(“123” AS SIGNED INTEGER) SELECT CAST(“123” AS SIGNED INTEGER) as value; SIGNED INTEGER:带符号的整形 | TO_NUMBER(str) SELECT TO_NUMBER(‘123’) AS VALUE FROM DUAL; |
now()/sysdate() | sysdate |