반응형
1.1. 데이터 타입 비교
Oracle / MariaDB Data Type Mapping
1.2. Function / Statements
Oracle / MariaDB Function 비교
| Oracle | Maria | Example ( Oracle ) | Example ( Maria ) |
| NVL | IFNULL | SELECT NVL( MAX( logging_time ) , SYSDATE ) last_time FROM xapm_server_time | SELECT IFNULL( MAX( logging_time ) , now() ) last_time FROM mjlee9.xapm_server_time |
| SYSDATE | NOW() | SELECT SYSDATE FROM DUAL; | SELECT now(); |
| || | CONCAT() | A||B | CONCAT(A, B) |
| TO_DATE | STR_TO_DATE | SELECT TO_DATE('2021-02-11', 'YYYY-MM-DD') FROM dual; | SELECT STR_TO_DATE('2021-02-11', '%Y-%m-%d'); |
| TO_CHAR | DATE_FORMAT | SELECT TO_CHAR(SYSDATE,‘YYYY-MM-DD') credate FROM DUAL | SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') |
| TO_CHAR() | CAST | select to_char(1111) from dual | select cast(1111 as char) |
| TO_NUMBER() | CAST | select to_number(1111) from dual | select cast(1111 as unsigned) |
| TO_DATE() | CAST | select to_date(‘2015’) from dual | select cast(‘2015’ as datetime) |
| DECODE | CASE THEN | SELECT DECODE(foods,'한식',1,'중식',2,' 양식',3,4) |
SELECT CASE foods WHEN '한식' THEN 1 WHEN '중식' THEN 2 WHEN '양식' THEN 3 ELSE 4 END |
| JOIN(+) | OUTER JOIN | SELECT t1.*, t2.* FROM t1, t2 where t1.i1 = t2.i2(+) ; SELECT t1.*, t2.* FROM t1, t2 where t1.i1(+) = t2.i2 ; |
SELECT t1.*, t2.* FROM t1 LEFT OUTER JOIN t2 ON t1.i1 = t2.i2 ; SELECT t1.*, t2.* FROM t1 RIGHT OUTER JOIN t2 ON t1.i1 = t2.i2 ; |
| ROWNUM | LIMIT | SELECT * FROM TABLE WHERE ROWNUM >=1 AND ROWNUM <=5 |
SELECT * FROM TABLE LIMIT 1,10 ; |
| REGEXP_SUBSTR | REGEXP_SUBSTR | SELECT REGEXP_SUBSTR('ab12cd','[0- 9]+') FROM DUAL; |
SELECT REGEXP_SUBSTR('ab12cd','[0- 9]+'); |
| REGEXP_INSTR | REGEXP_INSTR | SELECT REGEXP_INSTR('abc','b') FROM DUAL; |
SELECT REGEXP_INSTR('abc','b'); |
| REGEXP_LIKE | REGEXP | select REGEXP_LIKE(ERROR, '\[JEF.*\].*|\[HI.*\].*') |
select (ERROR REGEXP '\[JEF.*\].*' AND ERROR REGEXP '\[HI.*\].*') |
| TRUNC | TRUNCATE | TRUNC(TO_NUMBER(b.playtime)/60) | TRUNCATE(cast(b.playtime as unsigned)/60,0) |
| CHR() | CHAR() | select chr(100) from dual | select char(100 using ascii) |
Oracle / MariaDB 구문 비교
| Oracle | Maria | Example ( Oracle ) | Example ( Maria ) |
| OVER | N/A | SQL> select deptno, ename, sal, sum(sal) over (partition by deptno) from emp order by 1, 3 DEPTNO ENAME SAL SUM(SAL)OVER(PARTITIONBYDEPTNO) |
mysql> select a.DEPTNO, a.ENAME, a.SAL, b.TOTSAL from EMP as a inner join ( select DEPTNO, sum(SAL) TOTSAL from EMP group by DEPTNO ) as b on a.DEPTNO = b.DEPTNO order by 1, 3; |
| RANK() OVER | N/A | SQL> select * from (select deptno, empno, lastname, firstname, sal, rank() over (partition by deptno order by sal desc) "RANK" from employees) where "RANK" <= 5 order by deptno, "RANK" |
mysql> select * from (select a.DEPTNO, a.EMPNO, a.LASTNAME, a.FIRSTNAME, a.SAL, (select 1 + count(*) from EMPLOYEES b where b.DEPTNO = a.DEPTNO and b.SAL > a.SAL) RANK from EMPLOYEES as a) as x where x.RANK <= 5 order by x.DEPTNO, x.RANK; |
| COUNT() OVER() | COUNT() | select id, count(1) over() from tb_access_log |
select id, (select count(1) from tb_access_log) from tb_access_log; |
| ROW_NUMBER() OVER |
N/A | SELECT empno, ename, job, sal, ROW_NUMBER() OVER (PARTITION BY job ORDER BY sal) AS rnum FROM scott.emp; |
SELECT empno, ename, job, sal, rnum FROM ( SELECT a.*, (CASE @vjob WHEN a.job THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rnum, (@vjob:=a.job) vjob FROM emp a, (SELECT @vjob:='', @rownum:=0 FROM DUAL) b ORDER BY a.job, a.sal ) c; |
| NLSSORT | BINARY | select * from test order by NLSSORT(name); | select * from test order by BINARY(name); |
| ROWNUM | select t.* from ( select ROWNUM rnum , a.* from tb a ) t; |
select t.* from ( select @ROWNUM := @ROWNUM + 1 as rnum , a.* from tb a , (select @ROWNUM := 0) R ) t; |
|
| WITH ~ AS | WITH tab1 AS ( select * from tb1 ) , tab2 as ( select * from tb2 ) select * from tab1 , tab2 where tab1.key = tab2.key; |
select * from tb1 inner join tb2 on tb1.key = tb2.key; |
|
| 서브쿼리 | alias 반드시 필요 | AND a.fi_status = 1 AND a.fi_mt_id = 99 AND a.fi_u_id = 99 ORDER BY fKind ASC , reg_dt desc ) originalquery ) |
AND a.fi_status = 1 AND a.fi_mt_id = 99 AND a.fi_u_id = 99 ORDER BY fKind ASC , reg_dt desc ) originalquery ) tt |
| HINT | 1. /*+ 힌트 */ 2. --+ 힌트 |
# 인텍스 생성 create index index_name on table_name(col); # 인텍스 SCAN select * from table_name USE INDEX(index_name) where col = 1; |
|
| RECURSIVE SQL | N/A | select deptno, ltrim(sys_connect_by_path(ename,','),',') ename from ( select deptno, ename, row_number() over (partition by deptno order by empno) rn, count(*) over (partition by deptno) cnt from emp ) where level = cnt start with rn = 1 connect by prior deptno = deptno and prior rn = rn-1 ; DEPTNO ENAME ------- ------------------------------------ 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
select deptno, group_concat(ename order by empno separator ',') as ename from emp group by deptno; DEPTNO ENAME ------- ------------------------------------ 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 14 rows in set (0.00 sec) |
반응형
댓글