oracle和mysql常用函数对比

mysql:

and device_type like CONCAT('%', #{deviceType}, '%')

oracle:

and device_type like '%' || #{deviceType} || '%'

---------------------------------------------------------------------------
oracle:
####时间转字符串
to_char(t.CREATE_DATETIME,'yyyy-MM-dd HH24:MI:ss')
####字符串转日期格式
to_date(t.CREATE_DATETIME,'yyyy-MM-dd HH24:MI:ss')

mysql:

####时间转字符串（指定格式）
DATE_FORMAT(t.CREATE_DATETIME,'%Y-%m-%d %H:%i:%s')
####字符串转日期格式
str_to_date(t.CREATE_DATETIME,'%Y-%m-%d %H:%i:%s')
-----------------------------------------------------------------------------
oracle:

decode函数：
DECODE函数将比较'Age'列的值与13,14,15 ..并返回适当的字符串值'Thirteen','Fourteen'..如果它与没有匹配,则返回默认值'Adult' .

Select Name, DECODE(Age,
       13,'Thirteen',14,'Fourteen',15,'Fifteen',16,'Sixteen',
       17,'Seventeen',18,'Eighteen',19,'Nineteen',
       'Adult') AS AgeBracket
FROM Person

mysql:

select if(emp_id=1,'X','Y') as test
select if(t.id is null,'X','Y') as test

这只适用于布尔表达式,而不是像`DECODE()`那样的任意长度情况.

Select Name,
       CASE Age
         when 13 then 'Thirteen'
         when 14 then 'Fourteen'
         when 15 then 'Fifteen'
         when 16 then 'Sixteen'
         when 17 then 'Seventeen'
         when 18 then 'Eighteen'
         when 19 then 'Nineteen'
         else         'Adult'
       END AS AgeBracket

--------------------------------------------------------------------------------------

oracle:

select e.deptno,Listagg(e.ename,',') 
within group (order by e.CREATE_DATETIME) as name 
from emp e  group by e.deptno

mysql:
####默认使用,号分隔
SELECT e.deptno,group_concat(e.ename) as name  FROM emp e  group by e.deptno

####使用其他符号分隔
SELECT e.deptno,group_concat(e.ename,'|') as name  FROM emp e  group by e.deptno

-----------------------------------------------------------------------------------------

oracle:

nvl 函数 nvl(expr1,expr2)
如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数不为空，则显示第一个参数的值

mysql:

IFNULL函数 IFNULL(expr1,expr2)
如果mysql第一个参数为空那么显示第二个参数的值，如果第一个参数不为空，则显示第一个参数的值

---------------------------------------------------------------------------------------------

oracle:

TO_NUMBER
to_number() 把 string 转换成 numeric
TO_CHAR(): 

mysql:
cast ( 11 as unsigned int) 整型
cast ( 11 as decimal(10,2)) 浮点型
CAST(123   AS   VARCHAR)
-----------------------------------------------------------------------------------------------

oracle:
系统时间 
sysdate

mysql:
now()

####mysql使用mybatis中转义符
<![CDATA[ >]]>

--------------------------------------------------------------------------------------------------

oracle:

lpad函数，在字符串的左侧添加指定字符串，用法：

lpad(String ,截取长度，添加的字符串)。

说是添加字符串也不准确，比较准确的说法是对String进行截取字符串，如果截取长度大于String的长度，则在

String的左侧添加字符串进行填补，如果第三个参数未指定，则用空格进行填补。例如：

select lpad('test',10) from dual;

将返回“ test”

select lpad('test',10,'ee') from dual;

结果将返回eeeeeetest。

mysql:
####mysql中可以直接使用该函数
select lpad(COUNT(1), 2, 0) from BD_PERIOD_MANAGE BPM where (BPM.DEL_FLAG='N' or BPM.DEL_FLAG is null)

---------------------------------------------------------------------------------------------------------

oracle:
字符串拼接
t.DEVICE_NAME || '(' || t.RUN_NUM || ')' as DEVICE_NAME2

mysql:

concat(t.DEVICE_NAME,'(',t.RUN_NUM,')') as DEVICE_NAME2

----------------------------------------------------------------------------------------------------------

oracle:
add_months()函数
SELECT add_months(SYSDATE,1) from dual; 查询结果 2017/11/12 10:15:18

mysql:
ADDDATE()函数
SELECT ADDDATE(NOW(), INTERVAL 1 MONTH); 查询结果 2017-11-12 10:15:18

------------------------------------------------------------------------------------------------------------

oracle:
递归查询：
select DEPT_ID FROM SYS_DEPT start with DEPT_ID = 'd821ea21c4ea46e28d61DCEGd78f0026' connect by prior DEPT_ID = FID

mysql:
mysql实现递归需要创建函数，再实现，改成直接使用java实现递归然后传参数进来

--------------------------------------------------------------------------------------------------------------

oracle:

(1）substr(expr1,expr2)

expr1指要截取的字符串，expr2指截取字符串开始的位置，默认截取到最后，包含开始位置。

（2）substr(expr1,expr2,expr3)

expr1指要截取的字符串，expr2指截取字符串开始的位置，expr3指截取字符串的长度。

mysql:

substr()和substring()

-------------------------------------------------------------------------------------------------------------------

oracle:

rownum

mysql:

limit

----------------------------------------------------------------------------------------------------------------------

oracle:
ceil()函数 取整 ( 大 )

mysql:
ceiling()函数  取整 ( 大 )

----------------------------------------------------------------------------------------------------------------------
oracle:

extract()函数
Select extract(year from sysdate) year ,extract(month from sysdate) month,extract(day from sysdate) day from dual;

结果：

Year 2019

Month 4

Day 17

mysql同用

----------------------------------------------------------------------------------------------------------------------------

Oracle的trunc函数与MySQL（Maria）的truncate函数都可以对数值进行截取操作，但是Oracle的trunc函数还可以对日期进行截取操作。

对数值进行截取操作两者功能是一样的。

在针对日期的操作中，MySQL的truncate函数无此功能（可以使用date_format函数）

-- 等同于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

----------------------------------------------------------------------------------------------------------------------------------