您好,登錄后才能下訂單哦!
數(shù)據(jù)庫oracle與mysql在語法上區(qū)別是什么,很多新手對此不是很清楚,為了幫助大家解決這個難題,下面小編將為大家詳細講解,有這方面需求的人可以來學(xué)習(xí)下,希望你能有所收獲。
區(qū)別:1、MySQL沒有Number類型,而oracle有;2、MySQL中Date類型僅表示日期,Oracle中Date類型表示日期時間;3、獲取字符串長度,Oracle用length(),MySQL用char_length()等等。
本教程操作環(huán)境:windows7系統(tǒng)、mysql8版本、Dell G3電腦。
最近需要遷移項目,將數(shù)據(jù)庫由Oracle改為MySQL。由于兩者的語法有部分不一樣,所以需要把Oracle中能用但MySQL中不能用的函數(shù)/類型等改為MySQL中能用的,以下是總結(jié)出的部分語法區(qū)別:
一、數(shù)據(jù)類型
1. Number類型
MySQL中是沒有Number類型的,但有int/decimal 類型,Oracle中的Number(5,1)對應(yīng)MySQL中的decimal(5,1),Number(5) 對應(yīng) int(5)。
MySQL中的數(shù)字型類型比較多,分的也比較細,還有tinyint、smallint、mediumint、bigint等類型
2. Varchar2(n)類型
MySQL中對應(yīng)Oracle Varchar2(n)類型的替代類型是varchar(n)類型。
3. Date 類型
MySQL 中的日期時間類型有Date、Time、Datetime等類型,MySQL中Date類型僅表示日期(年-月-日),Time類型僅表示時間(時:分:秒),而Datetime類型表示日期時間(年-月-日 時:分:秒),Oracle中的Date類型和MySQL中的Datetime類型一致。
二、函數(shù)
1. length(str)函數(shù)
Oracle中的length(str)是獲取字符串長度的函數(shù),MySQL 中對應(yīng)的函數(shù)為char_length(str)。
2. sys_guid()函數(shù)
Oracle中可通過sys_guid()函數(shù)是生成隨機序列,MySQL通過UUID()生成隨機序列。
3. 時間格式化函數(shù)
將時間轉(zhuǎn)換為字符串型時間 MySQL date_format(NOW(),'%Y-%m-%d') 對應(yīng)Oracle的 Oracle中的 to_char(sysdate, 'YYYY-MM-DD');
將字符串型時間轉(zhuǎn)換為時間類型 MySQL str_to_date('2019-01-01','%Y-%m-%d') 對應(yīng)Oracle中的 to_date('2019-01-01', 'YYYY-MM-DD');
包括時分秒的函數(shù)轉(zhuǎn)換:DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'),str_to_date('2019-01-01','%Y-%m-%d %H:%i:%s')。
4. 條件函數(shù)(nvl()、nvl2()、decode())
nvl(tab.columnName, 0):如果tab.columnName值為空,則返回值取0,否則取tab.columnName;對應(yīng)的MySQL函數(shù)為:ifnull(tab.columnName, 0)。
nvl2(expr1,expr2,expr3):如果expr1不為null,則返回expr2,否則返回expr3;對應(yīng)的MySQL函數(shù)為:if(expr1,expr2,expr3)。
DECODE(value, val1, val2, val3):如果value等于val1,則返回val2,否則返回val3;MySQL可用IF函數(shù)表示: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()函數(shù)
TRUNC(12.123):返回整數(shù)(12);MySQL對應(yīng)的函數(shù):truncate(12.123, 0);
TRUNC(12.123, 2):返回值保留2為小數(shù)(12.12);MySQL對應(yīng)的函數(shù):truncate(12.123, 2);
TRUNC(SYSDATE):返回值為(2019-07-26 00:00:00);MySQL對應(yīng)的為cast(now() as datetime):返回值為(2019-07-26 14:11:38);
MySQL的cast函數(shù)語法為:CAST(xxx AS 類型) (可用類型為:二進制,同帶binary前綴的效果:BINARY;字符型,可帶參數(shù):CHAR();日期:DATE;時間:TIME;日期時間型: DATETIME;浮點數(shù): DECIMAL;整數(shù):SIGNED;無符號整數(shù):UNSIGNED)
6. to_char() to_number()
to_char(123):將數(shù)字123轉(zhuǎn)換為字符串123;MySQL對應(yīng)的函數(shù)為CAST(123 AS CHAR(3));
to_number('123'):將字符串數(shù)字123轉(zhuǎn)換為數(shù)字類型;MySQL對應(yīng)的函數(shù)為cast('123' as SIGNED);
7. sysdate 當前時間
sysdate:返回當前日期+時間; MySQL對應(yīng)的函數(shù)為 now();
三、其他
1. 引號
MySQL可識別雙引號和單引號,Oracle只能識別單引號。
2. 字符串連接符 ||
Oracle 可用'||'來連接字符串,但MySQL不支持'||'連接,MySQL可通過concat()函數(shù)鏈接字符串。
Oracle的 a.studentname||'【'||a.studentno||'】' 相當于 MySQL的 concat(a.studentname, '【', a.studentno, '】')
3. ROWNUM
Oracle可通過rownum獲取前n條記錄,MySQL通過limit來獲取前n條記錄,但二者的寫法略有不同,在Oracle中rownum作為where條件的一部分,而MySQL中l(wèi)imit不是where條件的一部分。
-- rownum語法如下: SELECT * FROM XJ_STUDENT WHERE ROWNUM = 1; -- 查詢第一條數(shù)據(jù) SELECT * FROM XJ_STUDENT WHERE ROWNUM <= 10; -- 獲取前10條數(shù)據(jù) -- 但rownum不支持查詢后幾條或第n(n>1)條數(shù)據(jù),例如以下sql是不支持的 SELECT * FROM XJ_STUDENT WHERE ROWNUM > 2; SELECT * FROM XJ_STUDENT WHERE ROWNUM = 3; -- limit 語法如下: SELECT * from fw_department limit 3; -- 查詢前3條數(shù)據(jù) SELECT * from fw_department limit 2, 4; -- 從第2(序號從0開始)條開始,查4條記錄
4. 空數(shù)據(jù)排序(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函數(shù)達到相同的效果 -- 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. 表(左/右)關(guān)聯(lián)(+)
Oracle左連接,右連接可以使用(+)來實現(xiàn). MySQL只能使用left join ,right join等關(guān)鍵字。
-- Oracle 左關(guān)聯(lián) select * from taba, tabb where taba.id = tabb.id(+); -- Oracle 右關(guān)聯(lián) select * from taba, tabb where taba.id(+) = tabb.id; -- MySQL 左關(guān)聯(lián) select * from taba left join tabb on taba.id=tabb.id; -- MySQL 右關(guān)聯(lián) select * from taba right join tabb on taba.id=tabb.id;
6. 刪除語法
MySQL的刪除語法沒有Oracle那么隨意,例如下面的sql在Oracle中可以執(zhí)行,但在MySQL中就不可以。
-- Oracle 可執(zhí)行,但MySQL中不能執(zhí)行 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)的這種遞歸查詢,但可以通過自定義函數(shù)來實現(xiàn)。
-- 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 先創(chuàng)建fun_getDepIDList函數(shù),用于查詢部門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可實現(xiàn)相似的功能。
-- 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、未修改/新增的數(shù)據(jù)項,如果必填,則必須有默認值) -- 1、由于是先刪后增,所以需要滿足以下2個條件之一: -- 1.要么必填項有默認值; -- 2.要么插入/更新時為沒有默認值的必填項賦值, 否則新增時會報錯。 -- 2、表中需要有主鍵或唯一索引,否則下面語句如果執(zhí)行多次,表中會出現(xiàn)重復(fù)數(shù)據(jù)。 replace into fw_department(DEPID,PARENTDEPID,DEPNO,DEPNAME) values('1111111', '1234','123', '哈哈'); -- MySQL on duplicate key update (特點:1、插入/更新的表必須有主鍵或唯一索引; -- 2、未修改/新增的數(shù)據(jù)項,如果必填,則必須有默認值) 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來構(gòu)建一個臨時表,但MySQL不支持with,對應(yīng)臨時表,MySQL可通過小括號的方式來處理,但構(gòu)建的臨時表必須設(shè)置臨時表名。
-- Oracle with使用 WITH TMPTAB AS ( SELECT A.DEPID FROM FW_DEPARTMENT A ) SELECT DEPID FROM TMPTAB -- MySQL 構(gòu)建臨時表使用(此處必須給括號中的臨時表設(shè)置表名) select b.depid from ( select depid from fw_department ) b
看完上述內(nèi)容是否對您有幫助呢?如果還想對相關(guān)知識有進一步的了解或閱讀更多相關(guān)文章,請關(guān)注億速云行業(yè)資訊頻道,感謝您對億速云的支持。
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。