Oracle与Mysql数据库SQL语法差异化整理
2023-07-31 本文已影响0人
王大千_xMol_西摩尔
0、数据类型
1.Oracle中有NUMBER；MySQL中有INT/INTEGER/DECIMAL。
其等换写法INT=NUMBER(10)，DECIMAL=NUMBER(10,2)
2.Oracle中有VARCHAR2；MySQL有VARCHAR
3.Oracle中有DATE，包含年月日时分秒；MySQL中有DATE/TIME/DATETIME可任选；
1、字符串截取，substr(“目标字符串”，开始坐标，截取长度)
oracle：
substr( string , 0, 10)，可以从0开始也可以从1开始，效果一样；
mysql：
substr( string , 1, 10)必须从1 开始；substring、mid与substr效果一样；
mysql中left（“目标字符串”，从左侧第一个开始的截取长度）；
mysql中right（“目标字符串”，从右侧最后一个开始的截取长度）;
2、日期-字符串转换
oracle用法 --> mysql用法
to_char(sysdate,'yyyy-mm-dd') --> date_format(now(),'%Y-%m-%d');
to_date('2023-08-01','yyyy-mm-dd') --> str_to_date('2023-08-01','%Y-%m-%d');
3、取时间
oracle：
sysdate关键字，获取系统本地时间。
如：select sysdate from dual;
mysql：
now()函数或者current_date关键字，获取系统本地时间。
如：select now() from dual;
select current_date from dual;
扩展：SYSDATE()函数实际上返回执行时的时间，NOW()函数返回一个常量时间，该语句开始执行。
mysql> SELECT NOW(), SLEEP(5), NOW();
+---------------------+----------+---------------------+
| NOW() | SLEEP(5) | NOW() |
+---------------------+----------+---------------------+
| 2023-08-01 11:36:22 | 0 | 2023-08-01 11:36:22 |
+---------------------+----------+---------------------+
由上执行后可以看出：即便睡眠了5秒，两次now()的值还是一样的；
mysql> SELECT SYSDATE(), SLEEP(5), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE() | SLEEP(5) | SYSDATE() |
+---------------------+----------+---------------------+
| 2023-08-01 11:40:05 | 0 | 2023-08-01 11:40:10 |
+---------------------+----------+---------------------+
由上执行后可以看出：睡眠了5秒后，SYSDATE()的值比前面大了5秒；
4、日期直接加减的含义不同了
oracle：
SELECT sysdate() AS current_time, sysdate()-1 AS yesterday_time;
结果将如下所示：
--------------------- ------------------------
current_time yesterday_time
--------------------- ------------------------
2023-08-02 10:00:00 2023-08-01 10:00:00
--------------------- ------------------------
由上执行后可以看出：返回当前时间前一天对应的时间（减1天）
mysql：
方式1：select sysdate() - 1 from dual;--返回当前时间前一天对应的时间（减1天）
[推荐]方式2：select date_sub(now(), interval 1 DAY) from dual;--返回当前时间前一天对应的时间（减1天）
5、字符串连接（拼接）
oracle：
方式1：CONCAT(char1, char2) 函数； --当其中一个参数为NULL的时候，返回另一个不为NULL的参数，如果所有都为NULL则返回NULL；
方式2："char1 || char2"
mysql：
方式1：CONCAT(char1, char2)可以拼接多个字符串，但只要有一个为NULL，则返回NULL值;
如：
SELECT CONCAT('1,',NULL,'2');
结果为 NULL
SELECT CONCAT('1,','','2');
结果为 1，2
6、分页
oracle分页关键字rownum，需要作为where条件；
如：select * from table as t where t.name='XX' and rownum > 10 and rownum <= 15; --取值11,12,13,14,15
mysql分页limit关键字;
如：select * from table as t where t.name='XX' limit 10, 5;--取值11,12,13,14,15
7、nvl()函数-判断空
oracle：
nvl(expr1,expr2)，如果expr1为空那么返回expr2，如果expr1值不为空，则返回expr1。
扩展：nvl2函数的是nvl函数的拓展，基本语法为nvl2(E1,E2,E3)，意思是E1为null，就返回E3，不为null就返回E2。
mysql：没有nvl()方法;
需要替换为 ifnull(expr1,expr2)；如果expr1为空那么返回expr2，如果expr1值不为空，则返回expr1。
扩展：IF(expr1,expr2,expr3)：如果expr1不为null，则返回expr2，否则返回expr3。
8、decode()函数
oracle：
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,默认值)；
当条件的值等于值1的时候，返回值1，当条件的值等于值2的时候，返回值2，如果条件与值1-值n都不想等，则返回默认值。相当于java中的if-else。
mysql：没有decode函数；
需要替换为 ifnull（elt（filed（条件，值1，值2….值n），返回值1，返回值2…..返回值n），默认值）
扩展：case when then else end，判断when，返回then，都匹配不上时，返回else；
9、结果集拼接
oracle：
wm_concat(字段2)，wm_concat是分组函数，前面查询的字段需要在后面group by一下，如果不group by一个字段的话将会把所有行的字段2都用逗号拼接起来；
mysql：
group_concat(字段2)，group_concat也是分组函数，前面查询的字段同样需要在后面group by一下；group_concat函数是不会留null值的；
10、uuid获取
oracle：sys_guid()
mysql：uuid()
11、字符串转数字，数字转字符串
oracle：
字符串转数字：to_number(字段)
数字转字符串：to_char(123)
mysql：
字符串转数字：Convert(字段,SIGNED) 或者 CAST(字段 as SIGNED)
数字转字符串：CAST(123 as CHAR(3))
以上方法为通用方法，可选类型如下：
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
12、左连接、右连接
oracle：可以使用(+)或者right join、left join；
如：右连接select * from a,b where a.id(+)=b.id;
左连接select * from a,b where a.id=b.id(+);
mysql：只有right join、left join可以实现
13、全连接
oracle：full join 关键字
mysql：union 关键字
14、临时表
oracle：可以用with构建一张临时表；
如：with table_temp as (select * from a where ...);
select * from table_temp;
mysql：不支持构建临时表； 使用(select ...)的子查询语句，就会自动创建临时表；
15、自动增长
oracle：没有自动增长的数据类型，需要创建自增的序列号；
mysql：AUTO_INCREMENT设置字段自动增长；
16、判断空
oracle：空值只有null，通过is null判空；
mysql：空值有null 和 ''，通过is null 和 ='' 同时判空；
17、字符串长度
oracle：length(str)
mysql：char_length(str)
18、数值截取
oracle：trunc()函数
1、截取掉（去掉）小数点后指定位数的数值，如果该位数为负数，则将小数点前的数值对应的位数变成0，如果位数超出数值的整数部分则结果为0；如下例：
-- 首先看对数值进行截取操作
SELECT TRUNC(122.123, 4) from dual; # 122.123
SELECT TRUNC(122.123, 3) from dual; # 122.123
SELECT TRUNC(122.123, 2) from dual; # 122.12
SELECT TRUNC(122.123, 1) from dual; # 122.1
SELECT TRUNC(122.123, 0) from dual; # 122
SELECT TRUNC(122.123, -1) from dual; # 120
SELECT TRUNC(122.123, -2) from dual; # 100
SELECT TRUNC(122.123, -3) from dual; # 0
SELECT TRUNC(122.123, -4) from dual; # 0
2、针对日期的截取，如下例：
-- 首先看对数值进行截取操作（今天的日期是2022年2月24日）
SELECT TRUNC(sysdate, 'YY') from dual; # 01-1月 -22
SELECT TRUNC(sysdate, 'yy') from dual; # 01-1月 -22
SELECT TRUNC(sysdate, 'MM') from dual; # 01-2月 -22
SELECT TRUNC(sysdate, 'mm') from dual; # 01-2月 -22
SELECT TRUNC(sysdate, 'DD') from dual; # 24-2月 -22
SELECT TRUNC(sysdate, 'dd') from dual; # 24-2月 -22
mysql：truncate()函数
1、截取掉（去掉）小数点后指定位数的数值，如果该位数为负数，则将小数点前的数值对应的位数变成0，如果位数超出数值的整数部分则结果为0；如下例：
-- 等同于Oracle的trunc函数。作用是对日期或数值进行截取操作
-- 首先看对数值进行截取操作
SELECT TRUNCATE(122.123, 4) from dual; # 122.123
SELECT TRUNCATE(122.123, 3) from dual; # 122.123
SELECT TRUNCATE(122.123, 2) from dual; # 122.12
SELECT TRUNCATE(122.123, 1) from dual; # 122.1
SELECT TRUNCATE(122.123, 0) from dual; # 122
SELECT TRUNCATE(122.123, -1) from dual; # 120
SELECT TRUNCATE(122.123, -2) from dual; # 100
SELECT TRUNCATE(122.123, -3) from dual; # 0
SELECT TRUNCATE(122.123, -4) from dual; # 0
2、不支持对日期的截取（可以使用date_format函数）；
DATE_FORMAT(NOW(),'%m-%d-%Y') #02-24-2022
参考：[MySQL DATE_FORMAT() 函数](https://www.w3school.com.cn/sql/func_date_format.asp)
19、引号
oracle：sql中只识别英文单引号；
mysql：sql中可识别英文的双引号和英文单引号；
20、merge into
oracle：
支持merge into，判断Ｂ表和Ａ表是否满足ON中条件，如果满足则用B表去更新A表，如果不满足，则将B表数据插入A表；
mysql：
不支持merge into，提供的replace into 和on duplicate key update可实现相似的功能；
