區別:1、MySQL沒有Number類型,而oracle有;2、MySQL中Date類型只表示日期,Oracle中Date類型表示日期時間;3、取得字串長度,Oracle用length(), MySQL用char_length()等等。
本教學操作環境:windows7系統、mysql8版本、Dell G3電腦。
最近需要遷移項目,將資料庫由Oracle改為MySQL。由於兩者的語法有部分不一樣,所以需要把Oracle中能用但MySQL中不能用的函數/型別等改為MySQL中能用的,以下是總結出的部分語法區別:
一、資料型別
1. Number型別
MySQL中是沒有Number型的,但有int/ decimal 類型,Oracle中的Number(5,1)對應MySQL中的decimal(5,1),Number(5) 對應int(5)。
MySQL中的數字型別比較多,分的也比較細,還有tinyint、smallint、mediumint、bigint等型別
2. Varchar2(n)型別
MySQL中對應Oracle Varchar2(n)類型的替代類型是varchar(n)類型。
3. Date 類型
MySQL 中的日期時間類型有Date、Time、Datetime等類型,MySQL中Date類型只表示日期(年-月-日),Time類型僅表示時間(時:分:秒),而Datetime類型表示日期時間(年-月-日時:分:秒),Oracle中的Date類型和MySQL中的Datetime類型一致。
二、函數
##1. length(str)函數
Oracle中的length (str)是取得字串長度的函數,MySQL 中對應的函數為char_length(str)。2. sys_guid()函數
Oracle中可透過sys_guid()函數是產生隨機序列,MySQL透過UUID()產生隨機序列。3. 時間格式化函數
將時間轉換為字串型時間MySQL date_format(NOW(),'%Y-%m-%d')對應Oracle的Oracle中的to_char(sysdate, 'YYYY-MM-DD'); 將字串型時間轉換為時間型別時間轉換為時間型別('2019-01-01','%Y-% m-%d') 對應Oracle中的to_date('2019-01-01', 'YYYY-MM-DD'); 包含時分秒的函數轉換:DATE_FORMAT(NOW(),'% Y-%m-%d %H:%i:%s'),str_to_date('2019-01-01','%Y-%m-%d %H:%i:%s')。4. 條件函數(nvl()、nvl2()、decode())
nvl(tab.columnName, 0):若tab.columnName值為空,則回傳值取0,否則取tab.columnName;對應的MySQL函數為:ifnull(tab.columnName, 0)。 nvl2(expr1,expr2,expr3):若expr1不是null,則傳回expr2,否則傳回expr3;對應的MySQL函數為:if(expr1,expr2,expr3)。 DECODE(value, val1, val2, val3):若value等於val1,則傳回val2,否則傳回val3;MySQL可用IF函數表示:if(value=val1, val2, val3);## DECODE(value, if1, val1, if2,val2,...,ifn, valn, val):若value等於if1,則回傳val1,若value等於if2,則回傳value2...若value等於ifn ,則返回valn,否則返回val;MySQL對於這種判斷可以透過case when then else end;l來判斷,即:case when value=if1 then val1 when value=if2 then val2,,,when value=ifn then valn else val end;5. trunc()函數
# TRUNC(12.123):傳回整數(12);MySQL對應的函數:truncate(12.123, 0) ; TRUNC(12.123, 2):回傳值保留2為小數(12.12);MySQL對應的函數:truncate(12.123, 2);#UNC#TR## SYS (2019-07-26 00:00:00);MySQL對應的為cast(now() as datetime):傳回值為(2019-07-26 14:11:38); My 1:38); cast函數語法為:CAST(xxx AS 類型) (可用類型為:二進位,同帶binary前綴的效果:BINARY;字元型,可帶參數:CHAR();日期:DATE;時間:TIME;日期時間型: DATETIME;浮點數: DECIMAL;整數:SIGNED;無符號整數:UNSIGNED)6. to_char() to_number()
# to_char(123 to_char(123):將數字123轉換為字串123;MySQL對應的函數為CAST(123 AS CHAR(3)); to_number('123'):將字串數字123轉換為數字型別;MySQL對應的函數為cast('123' as SIGNED);7. sysdate 目前時間
###sysdate:傳回目前日期時間; MySQL對應的函數為now();
三、其他
##1 . 引號
MySQL可辨識雙引號和單引號,Oracle只能辨識單引號。
2. 字串連接符||
Oracle 可用'||'來連接字串,但MySQL不支援'||'連接,MySQL可透過concat()函數連結字串。
Oracle的a.studentname||'【'||a.studentno||'】' 相當於MySQL的concat(a.studentname, '【', a.studentno, '】')
3. ROWNUM
Oracle可透過rownum取得前n筆記錄,MySQL透過limit取得前n筆記錄,但二者的寫法略有不同,在Oracle中rownum作為where條件的一部分,而MySQL中limit不是where條件的一部分。
-- rownum语法如下: SELECT * FROM XJ_STUDENT WHERE ROWNUM = 1; -- 查询第一条数据 SELECT * FROM XJ_STUDENT WHERE ROWNUM <= 10; -- 获取前10条数据 -- 但rownum不支持查询后几条或第n(n>1)条数据,例如以下sql是不支持的 SELECT * FROM XJ_STUDENT WHERE ROWNUM > 2; SELECT * FROM XJ_STUDENT WHERE ROWNUM = 3; -- limit 语法如下: SELECT * from fw_department limit 3; -- 查询前3条数据 SELECT * from fw_department limit 2, 4; -- 从第2(序号从0开始)条开始,查4条记录
4. 空資料排序(nulls first 和nulls last)
-- null值排在最前 SELECT * FROM FW_DEPARTMENT A ORDER BY A.REMARK DESC NULLS FIRST -- null值排在最后 SELECT * FROM FW_DEPARTMENT A ORDER BY A.REMARK DESC NULLS LAST -- MySQL 可通过IF和ISNULL函数达到相同的效果 -- null值排在最后 select * from FW_DEPARTMENT A order by IF(ISNULL(A.REMARK),1,0),A.REMARK desc -- null值排在最前 select * from FW_DEPARTMENT A order by IF(ISNULL(A.REMARK),0,1),A.REMARK desc
5. 表(左/右)關聯( )
Oracle左連接,右連接可以使用( )來實現. MySQL只能使用left join ,right join等關鍵字。
-- Oracle 左关联 select * from taba, tabb where taba.id = tabb.id(+); -- Oracle 右关联 select * from taba, tabb where taba.id(+) = tabb.id; -- MySQL 左关联 select * from taba left join tabb on taba.id=tabb.id; -- MySQL 右关联 select * from taba right join tabb on taba.id=tabb.id;
6. 刪除語法
MySQL的刪除語法沒有Oracle那麼隨意,例如下面的sql在Oracle中可以執行,但在MySQL中就不行。
-- Oracle 可执行,但MySQL中不能执行 DELETE FROM FW_DEPARTMENT A WHERE A.DEPID = '111'; DELETE FW_DEPARTMENT WHERE DEPID = '111'; -- MySQL中删除语句格式如下: DELETE FROM FW_DEPARTMENT WHERE DEPID = '111';
7. 遞迴查詢(start with connect by prior)
MySQL不支援(start with connect by prior)的這種遞歸查詢,但可以透過自定義函數來實現。
-- Oracle 递归查询 查询部门ID为‘1111’的所有子部门(包含自身) SELECT * FROM FW_DEPARTMENT START WITH DEPID='1111' CONNECT BY PRIOR DEPID = PARENTDEPID; -- Oracle 递归查询 查询部门ID为‘1111’的所有父部门(包含自身) SELECT * FROM FW_DEPARTMENT START WITH DEPID='1111' CONNECT BY PRIOR PARENTDEPID = DEPID; -- MySQL 先创建fun_getDepIDList函数,用于查询部门ID字符串 CREATE FUNCTION fun_getDepIDList(rootId VARCHAR(32)) RETURNS VARCHAR(6000) BEGIN DECLARE pTemp VARCHAR(6000); DECLARE cTemp VARCHAR(6000); SET pTemp='$'; SET cTemp=rootId; WHILE cTemp is not null DO set pTemp=CONCAT(pTemp,',',cTemp); SELECT GROUP_CONCAT(depid) INTO cTemp from fw_department WHERE FIND_IN_SET(PARENTDEPID,cTemp)>0; END WHILE; RETURN pTemp; END; -- 查询部门ID为‘1111’的所有子部门(包含自己) select * from fw_department where FIND_IN_SET(DEPID, fun_getDepIDList('1111')); -- 查询部门ID为‘1111’的所有父部门(包含自己) select * from fw_department where FIND_IN_SET('1111', fun_getDepIDList(DEPID));
8. merge into
MySQL不支援(merge into),但提供的replace into 和on duplicate key update可實現類似的功能。
-- Oracle merge into (有则修改,无则新增) MERGE INTO TMPDEPTAB A USING (SELECT '1111' DEPID, '哈哈' DEPNAME FROM DUAL) B ON (A.DEPID = B.DEPID) WHEN MATCHED THEN UPDATE SET A.DEPNAME = B.DEPNAME WHEN NOT MATCHED THEN INSERT(DEPID, DEPNAME) VALUES(B.DEPID, B.DEPNAME); -- MySQL replace into (特点:1、先删后增; 2、插入/更新的表必须有主键或唯一索引; -- 3、未修改/新增的数据项,如果必填,则必须有默认值) -- 1、由于是先删后增,所以需要满足以下2个条件之一: -- 1.要么必填项有默认值; -- 2.要么插入/更新时为没有默认值的必填项赋值, 否则新增时会报错。 -- 2、表中需要有主键或唯一索引,否则下面语句如果执行多次,表中会出现重复数据。 replace into fw_department(DEPID,PARENTDEPID,DEPNO,DEPNAME) values('1111111', '1234','123', '哈哈'); -- MySQL on duplicate key update (特点:1、插入/更新的表必须有主键或唯一索引; -- 2、未修改/新增的数据项,如果必填,则必须有默认值) insert into fw_department(depid,parentdepid,depno,depname) select '1111111' depid, '123' parentdepid, 'e12' depno, '哈哈哈哈' depname from fw_department on duplicate key update parentdepid = values(parentdepid), depno=values(depno), depname=values(depname);
9. with
Oracle 中可用with來建立一個臨時表,但MySQL不支援with,對應臨時表,MySQL可透過小括號的方式來處理,但建立的臨時表必須設定臨時表名。
-- Oracle with使用 WITH TMPTAB AS ( SELECT A.DEPID FROM FW_DEPARTMENT A ) SELECT DEPID FROM TMPTAB -- MySQL 构建临时表使用(此处必须给括号中的临时表设置表名) select b.depid from ( select depid from fw_department ) b
【相關推薦:mysql影片教學】
以上是資料庫oracle與mysql在語法上有什麼區別的詳細內容。更多資訊請關注PHP中文網其他相關文章!