文章目录
1 前言
本文主要描述了Oracle和MySQL的语法区别,主要包括数据类型,函数和其他三部分。
后文小标题均将Oralcle的语法写在前面,MySQL在后,形如‘Oralcle’&‘MySQL’。
2 数据类型
常见的数据类型如下。
- Number & int。Oracle的数据类型是Number类型,MySQL中是没有Number类型的,但有int/decimal 等类型。MySQL中的数字型类型比较多,如tinyint、smallint、mediumint、bigint等类型。Oracle中的Number(5,1)对应MySQL中的decimal(5,1),Number(5) 对应 int(5)。
- Varchar2(n) & varchar(n)。Oracle Varchar2(n)类型对应MySQL中的varchar(n)类型。
- Date & Datetime。Oracle中的Date类型和MySQL中的Datetime类型一致。 MySQL 中的日期时间类型有Date、Time、Datetime等类型,MySQL中Date类型仅表示日期(年-月-日),Time类型仅表示时间(时:分:秒),而Datetime类型表示日期时间(年-月-日 时:分:秒)。
表:Oracle & MySQL 常见数据类型对应关系
序号 | Oracle | MySQL | 备注 |
---|---|---|---|
1 | Number | int/decimal | Oracle中的Number(5,1)对应MySQL中的decimal(5,1),Number(5) 对应 int(5) |
2 | Varchar2(n) | varchar(n) | |
3 | Date | Datetime | 详见:【数据库笔记】Oracle & MySQL 日期时间数据类型、日期函数和转换函数の对比 |
4 | Integer | int | |
5 | Exception | SQLEXCEPTION | |
6 | 自动增长的序列 | 自动增长的数据类型 | Oracle 没有自动增长的数据类型,需要建立一个自动增长的序列号,插入记录时要把序列号的下一个值赋于此字段。MySQL 有自动增长的数据类型,插入记录时不用操作此字段,会自动获得数据值。 |
7 | NULL | NULL | 在空字符的处理上,Oracle里定义了非空字段就不容许有空的内容。MySQL 的非空字段也有空的内容。因此,按MySQL 的NOT NULL来定义Oracle表结构, 导数据的时候会产生错误:要对空字符进行判断,如果为NULL或空字符,需要把它改成一个空格的字符串。 |
部分表内容源于:https://blog.csdn.net/huangyinzhao/article/details/80739291
3 函数
1. length(str)函数
Oracle中的`length(str)`是获取字符串长度的函数,MySQL 中对应的函数为`char_length(str)`。
2. sys_guid()函数
Oracle中可通过`sys_guid()`函数是生成随机序列,MySQL通过`UUID()`生成随机序列。
3. 日期函数&转换函数
详见:[【数据库笔记】Oracle & MySQL 日期时间数据类型、日期函数和转换函数の对比](https://blog.csdn.net/qq_36056219/article/details/108977639)
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;来判断,即:
case when value=if1 then val1
when value=if2 then val2
...
when value=ifn then valn
else val
end;
4 其他
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. 表(左/右)关联(+)
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可通过小括号的方式来处理,但构建的临时表必须设置临时表名。
新版MySQL现在也支持with
构建附表,形式同Oracle 。
-- 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
Reference:https://blog.csdn.net/lanmuhhh2015/article/details/97763615