Oracle、MySQL、HQL查询语句的差异

SQL是结构化查询语言，执行Select（选择）、Update（更新）、Delete（删除）和Insert（插入）等数据任务。

关系数据库是指基于关系模型的数据库。在关系模型中，用于存储数据的逻辑结构就是二维表（Table）。表由行和列组成，行也称为记录，代表了单个实体；列也称为字段，代表了实体的某些属性。关系数据库使用 SQL 作为标准语言，执行数据的增删改查以及各种管理操作。关系数据库还定义了三种约束完整性：实体完整性、参照完整性以及用户定义完整性。

大多数主流数据库都属于关系数据库，例如 Oracle、MySQL、SQLServer 以及 PostgreSQL 等。另外，数据库领域还存在一些非关系模型的数据库（NoSQL ），例如 Mongodb、Redis、Cassandra 等。

一、Oracle与MySQL

1.1数据类型

1.1.1Number类型

MySQL中是没有Number类型的，但有int/decimal类型，Oracle中的Number(5,1)对应MySQL中的decimal(5,1)，Number(5)对应 int(5)。MySQL中的数字型类型比较多，分的也比较细，还有tinyint、smallint、mediumint、bigint等类型

1.1.2Varchar2(n)类型

MySQL中对应Oracle Varchar2(n)类型的替代类型是varchar(n)类型。

1.1.3Date类型

MySQL 中的日期时间类型有Date、Time、Datetime等类型，MySQL中Date类型仅表示日期(年-月-日)，Time类型仅表示时间（时:分:秒），而Datetime类型表示日期时间(年-月-日 时:分:秒)；

Oracle中的Date类型和MySQL中的Datetime类型一致。

1.2函数

1.2.1length(str)函数

Oracle中的length(str)是获取字符串长度的函数，MySQL 中对应的函数为char_length(str)。

1.2.2sys_guid()函数

Oracle中可通过sys_guid()函数是生成随机序列，MySQL通过UUID()生成随机序列。

1.2.3时间格式化函数

将时间转换为字符串型时间 MySQL date_format(NOW(),'%Y-%m-%d') 对应Oracle中的 to_char(sysdate, 'YYYY-MM-DD');

将字符串型时间转换为时间类型 MySQL str_to_date('2019-01-01','%Y-%m-%d')对应Oracle中的 to_date('2019-01-01','YYYY-MM-DD');

包括时分秒的函数转换：MySQL中DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')，对应Oracle中的str_to_date('2019-01-01','%Y-%m-%d%H:%i:%s')。

1.2.4条件函数（nvl()、nvl2()、decode()）

（1）nvl(tab.columnName, 0)：如果tab.columnName值为空，则返回值取0，否则取tab.columnName；对应的MySQL函数为：ifnull(tab.columnName, 0)。

（2）nvl2(expr1,expr2,expr3)：如果expr1不为null，则返回expr2，否则返回expr3；对应的MySQL函数为：if(expr1,expr2,expr3)。

（3）DECODE(value, val1, val2, val3)：如果value等于val1，则返回val2，否则返回val3；MySQL可用IF函数表示：if(value=val1,val2, val3)；

（4）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 val1when value=if2 then val2,,,when value=ifn then valn else val end。

1.2.5trunc()函数

（1）trunc(12.123)：返回整数(12)；MySQL对应的函数：truncate(12.123, 0)；

（2）trunc(12.123, 2)：返回值保留2为小数(12.12)；MySQL对应的函数：truncate(12.123,2)；

（3）trunc(SYSDATE)：返回值为(2019-07-2600:00:00)；MySQL对应的为cast(now()as datetime)：返回值为(2019-07-26 14:11:38)；

（4）补充说明：MySQL的cast函数语法为：CAST(xxxAS 类型) （可用类型为：二进制,同带binary前缀的效果:BINARY；字符型,可带参数:CHAR()；日期:DATE；时间:TIME；日期时间型: DATETIME；浮点数: DECIMAL；整数:SIGNED；无符号整数:UNSIGNED）

1.2.6to_char()、to_number()

（1）to_char(123)：将数字123转换为字符串123；MySQL对应的函数为CAST(123 AS CHAR(3))；

（2）to_number('123')：将字符串数字123转换为数字类型；MySQL对应的函数为cast('123' as SIGNED)；

1.2.7sysdate

sysdate：返回当前日期+时间；MySQL对应的函数为 now()；

1.3其他

1.3.1引号

MySQL可识别双引号和单引号，Oracle只能识别单引号。

1.3.2字符串连接符

Oracle 可用'||'来连接字符串，但MySQL不支持'||'连接，MySQL可通过concat()函数链接字符串。

使用字符串的聚合函数将多个字符串合并成一个：MySQL 中使用 GROUP_CONCAT 函数，Oracle 使用 LISTAGG 函数。

1.3.3ROWNUM

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条记录

1.3.4空数据排序（null first和null last）

--Oracle中排序

-- 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

1.3.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 ontaba.id=tabb.id;

-- MySQL 右关联

select * from taba right join tabb ontaba.id=tabb.id;

1.3.6删除语法

MySQL的删除语法没有Oracle那么随意，例如下面的sql在Oracle中可以执行，但在MySQL中就不可以。

-- Oracle 可执行

DELETE FROM FW_DEPARTMENT A WHERE A.DEPID= '111';

DELETE FW_DEPARTMENT WHERE DEPID = '111';

-- MySQL中删除语句格式如下：

DELETE FROM FW_DEPARTMENT WHERE DEPID ='111';

1.3.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(rootIdVARCHAR(32))

RETURNS VARCHAR(6000)

BEGIN

       DECLAREpTemp VARCHAR(6000);

       DECLAREcTemp VARCHAR(6000);

       SETpTemp='$';

       SETcTemp=rootId;

       WHILEcTemp is not null DO

              setpTemp=CONCAT(pTemp,',',cTemp);

              SELECT GROUP_CONCAT(depid) INTO cTemp from fw_department

              WHERE FIND_IN_SET(PARENTDEPID,cTemp)>0;

       ENDWHILE;

       RETURNpTemp;

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));

1.3.8merge 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 UPDATESET 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);

1.3.9with

Oracle 中可用with来构建一个临时表，但MySQL不支持with，对应临时表，MySQL可通过子查询小括号的方式来处理，但构建的临时表必须设置临时表名。

-- Oracle with使用

WITH TMPTAB AS (SELECTA.DEPID FROM FW_DEPARTMENT A)

SELECT DEPID

FROM TMPTAB

-- MySQL 构建临时表使用(此处必须给括号中的临时表设置表名)

select b.depid

from (select depid

      from fw_department

) b

二、HSQL和mysql

2.1hiveSQL特殊字符拼接问题

hiveSQL不能智能识别concat(‘;’,key)，分号要先进行八进制的ASCII码转义。

--MySQL：select concat(key,concat(';',key)) from dual;

--hivesql：select concat(key,concat('\073',key)) from dual;

2.2hivesql不支持等值连接

--MySQL：select * from dual a,dual b where a.key =b.key;

--hivesql：select * from dual a join dual b on a.key= b.key;

2.3空值判断

MySQL中null表示空值，HiveSQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False。

2.4函数使用不同

2.4.1分隔字符串

MySQL：

--select substring_index("1,2,3",",",1); # 返回结果：1     

--select substring_index("1,2,3",",",3); # 返回结果：1,2,3

hivesql：

--select split("1,2,3", ',')[0]; #返回结果：1   

--select split("1,2,3", ',')[2]; # 返回结果：3

2.4.2时间转换函数

MySQL：

--select p_id,from_unixtime(p_create_time/1000,'%y-%m-%d') as times from poi where p_parent_id= 4782760;

hivesql：

--select p_id,from_unixtime(p_create_time div 1000,'YYYY-MM-dd HH:mm:ss') as times from poi where p_parent_id= 4782760;

2.4.3hivesql不支持INSERT INTO 表 Values（）, UPDATE, DELETE等操作

2.4.4hivesql不支持事务

2.4.5hivesql支持分区存储

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• oracle清除日志
• 【LeetCode 2701. 连续递增交易】MySQL用户变量编程得到严格递增连续子序列的开始位置，结束位置，长度等统计信息
• 【Leetcode 2474. 购买量严格增加的客户】MySQL用户变量编程解决严格递增连续子序列问题
• 盘点C#最有价值的10个语法糖
• mysql发生连接异常Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
• 【LeetCode 2494. 合并在同一个大厅重叠的活动】MySQL用户变量编程解决区间合并问题
• 如何处理Mysql死锁
• 【LeetCode1747. 应该被禁止的 Leetflex 账户】MySQL用户变量编程；尝试维护一个multiset

语法 差异 Oracle MySQL HQL语法 差异oracle mysql 语法sqlserver差异 数据库 增量 备份 差异oracle hql ninedata差异 数据mysql mysql差异 性能mysql8 差异mysql tidb sql 马拉松 语法 基础oracle 仓库 数据hql 语法oracle jdbc sid