mysql | oracle | |
---|---|---|
时间转换为字符串型时间 | date_format(NOW(),’%Y-%m-%d’) | to_char(sysdate, ‘YYYY-MM-DD’) |
字符串型时间转换为时间类型 | date_format(NOW(),’%Y-%m-%d’) | to_date(‘2020-12-01’, ‘YYYY-MM-DD’) |
mysql | oracle |
---|---|
IFNULL、COALESCE | nvl、nvl2 |
说明:
mysql | oracle |
---|---|
char_length(str) | length(str) |
mysql | oracle |
---|---|
IFNULL | nvl |
IF(expr1,expr2,expr3) | nvl2 |
IF(value=val1, val2, val3)、case when | DECODE |
说明:
SELECT
CASE WHEN STATUS = '1' THEN 'success'
WHEN STATUS = '0' THEN 'faild'
ELSE 'error' END
FROM SYS_SCHEDULER
MySQL | Oracle |
---|---|
truncate(123.123,2) | TRUNC(123.123,2) |
MySQL | Oracle | |
---|---|---|
整型转字符串 | CAST(123 AS CHAR(3)) | to_char(123) |
字符串转整型 | cast(‘123’ as SIGNED) | to_number(‘123’) |
MySQL可识别双引号和单引号,Oracle只能识别单引号。
MySQL | Oracle |
---|---|
concat(studentname, ‘=’, studentno) | studentname||’=’||studentno |
MySQL
select * from USER A order by IF(ISNULL(A.REMARK),1,0),A.REMARK desc
select * from USER A order by IF(ISNULL(A.REMARK),0,1),A.REMARK desc
Oracle
SELECT * FROM USER A ORDER BY A.REMARK DESC NULLS FIRST
SELECT * FROM USER A ORDER BY A.REMARK DESC NULLS LAST
Oracle左连接、右连接可以使用(+)来实现,MySQL只能使用left join ,right join等关键字。 Oracle
select * from ta, tb where ta.id = tb.id(+); #左关联
select * from ta, tb where ta.id(+) = tb.id; #右关联
MySQL
select * from ta left join tb on ta.id=tb.id; #左关联
select * from ta right join tb on ta.id=tb.id; #右关联
MySQL不支持(merge into),但提供的replace into 和on duplicate key update可实现相似的功能。
insert into article(id,title,content)
select '10' id, '今日新闻' title, '北京新闻' content
from article
on duplicate key
update title = values(title),
content=values(content);
replace into article(id,title,content)
values('10', '今日新闻','北京新闻');