mysql和oracle sql语法的区别

一、导读

一次需求将oracle数据库迁移到mysql数据库，期间有些注意点这里记录学习一下，不定期更新~~

二、不同点

• 查询数据库中的所有表

1. oracle:

SELECT * FROM USER_TAB_COMMENTS A WHERE A.TABLE_NAME like ‘YW_%’ ORDER BY A.TABLE_NAME

2. mysql:

SELECT TABLE_NAME, TABLE_COMMENT FROM Information_schema.TABLES A WHERE table_schema=‘fxbdb’ AND A.TABLE_NAME like ‘credit_%’ ORDER BY A.TABLE_NAME

• 查询表中的字段

1. oracle:

SELECT * FROM USER_COL_COMMENTS A WHERE A.TABLE_NAME like ‘yv_%’ ORDER BY COLUMN_NAME ASC

2. mysql:

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT FROM Information_schema.COLUMNS WHERE TABLE_NAME like ‘yv_%’ ORDER BY COLUMN_NAME ASC

• 日期和字符相互转换方法

date_format(date,’%Y-%m-%d’) -------------->oracle中的to_char();
str_to_date(date,’%Y-%m-%d’) -------------->oracle中的to_date();

• oracle 的 sysdate 对应mysql 的 current_date
• oracle row_number() 用法 mysql如何替换

oracle:
SELECT
*
FROM
(
SELECT
*
FROM
(
SELECT
t.,
row_number () over ( partition BY t.subject_id ORDER BY t.create_time DESC ) rn
FROM
credit_score t
WHERE
t.score_model_id = ‘402894e56816d7ca0168177d7ab301aa’
)
WHERE
1 = 1
AND rn = 1
)
ORDER BY
score DESC
mysql写法：
SELECT
*
FROM
(
SELECT
base_tmp.,
@rownum := @rownum + 1,
IF
( @puid = base_tmp.subject_id, @rank := @rank + 1, @rank := 1 ) AS NO,
@puid := base_tmp.subject_id
FROM
( SELECT * FROM credit_score WHERE score_model_id = ‘402894e56816d7ca0168177d7ab301aa’ ORDER BY subject_id ASC, create_time DESC ) base_tmp,
( SELECT @rownum := 0, @puid := NULL, @rank := 0 ) a
) result
WHERE
NO = 1
ORDER BY
score DESC