[SQL] MySQL & Oracle

★NULL처리★

MySQL : 빈 값과 NULL 구분함

<IF>
IF( expression1, expression2, expression3 )
expression1가 참이면 expression2를, 거짓이면 expression3을 반환합니다.

<IFNULL>
IFNULL( expression1, expression2 )
expression1이 NULL이 아니면 expression1을, NULL이면 expression2를 반환합니다.

<NULLIF>
NULLIF( expression1, expression2 )
expression1과 expression2가 같으면 NULL을, 같지 않으면 expression1를 반환합니다.

SELECT * FROM MEMBER WHERE NAME IS NULL
NAME이 NULL인 경우만 나옴
SELECT * FROM MEMBER WHERE NULLIF(NAME, '') IS NULL
NAME이 NULL과 빈 값인 경우 모두 나옴

Oracle : 빈 값도 NULL로 인식

<IS [NOT] NULL>
expr1 IS [NOT] NULL
테이블에서 expr1 칼럼이 NULL 값이거나 NULL 값이 아닌 행을 반환합니다.

<NVL>
NVL(expr1, expr2)
expr1이 NULL이 아니면 expr1을 반환합니다. expr1이 NULL인 경우 expr2를 반환합니다.

<NVL2>
NVL2(expr1, expr2, expr3)
expr1이 NULL이 아니면 expr2를 반환합니다. expr1이 NULL인 경우 expr3을 반환합니다.

<NULLIF>
NULLIF(expr1, expr2)
NULLIF는 expr1이 expr2와 같으면 NULL을 반환합니다. expr1이 expr2과 다르면, expr1을 반환합니다.

<COALESCE>
COALESCE(expr [, expr ]...)
COALESCE는 목록에 있는 표현식에서 NULL이 아닌 첫 번째 표현식을 반환합니다.

<DECODE>
DECODE(expr1, expr2, expr3, expr4)
expr1이 expr2와 같으면 expr3을, 그 밖의 값이라면 expr4반환
DECODE(expr1, expr2, expr3, expr4, expr5, expr6)
expr1이 expr2와 같으면 expr3을, expr1이 expr4와 같으면 expr5을, 그 밖의 값이라면 expr6반환

<CASE>
CASE NVL(NULL, 'C')
       WHEN 'A' THEN 'A'
       WHEN 'B' THEN 'B'
       WHEN 'C' THEN 'C'
ELSE 'NOT FOUND' END // 'C'
CASE문 표현식에 대해 NVL와 같은 NULL을 체크하는 함수를 이용하여 조건문을 처리할 수 있습니다.

SELECT * FROM MEMBER WHERE NAME IS NULL
NAME이 NULL과 빈 값인 경우 모두 나옴

★조건절★

공통

SELECT * FROM MEMBER WHERE NAME = 'A'
NAME이 'A'인 경우
SELECT * FROM MEMBER WHERE NAME IN ('A', 'B', 'C')
NAME이 'A' 또는 'B' 또는 'C'인 경우
SELECT * FROM MEMBER WHERE NAME != 'A'
NAME이 'A'가 아닌 경우
SELECT * FROM MEMBER WHERE NAME <> 'A'
NAME이 'A'가 아닌 경우
SELECT * FROM MEMBER WHERE NOT NAME = 'A'
NAME이 'A'가 아닌 경우
SELECT * FROM MEMBER WHERE NAME NOT IN ('A', 'B', 'C')
NAME이 'A', 'B', 'C' 모두 아닌 경우

COUNT, MAX와 같은 집계 함수를 조건으로 사용할 경우 WHERE절이 아닌 HAVING절에 사용★

같은 HOST_ID를 가진 데이터끼리 묶고 HOST_ID가 중복(2개 이상)인 HOST_ID 조회
EX) SELECT HOST_ID, COUNT(HOST_ID) FROM PLACES WHERE COUNT(HOST_ID) > 1 GROUP BY HOST_ID ORDER BY HOST_ID (X)
EX) SELECT HOST_ID, COUNT(HOST_ID) FROM PLACES GROUP BY HOST_ID HAVING COUNT(HOST_ID) > 1 ORDER BY HOST_ID (O)

MySQL

SELECT * FROM MEMBER WHERE UPPER(NAME) LIKE CONCAT('%', UPPER('A'), '%')
SELECT * FROM MEMBER WHERE LOWER(NAME) LIKE CONCAT('%', LOWER('A'), '%')
대소문자 구분 없이 NAME이 'A'를 포함하는 경우

SELECT * FROM MEMBER ORDER BY NAME LIMIT 5
MEMBER테이블의 데이터를 NAME 순으로 정렬하고 5개까지만 데이터만 조회

Oracle

SELECT * FROM MEMBER WHERE UPPER(NAME) LIKE '%' || UPPER('A') || '%'
SELECT * FROM MEMBER WHERE LOWER(NAME) LIKE '%' || LOWER('A') || '%'
대소문자 구분 없이 NAME이 'A'를 포함하는 경우

SELECT A.* FROM (SELECT * FROM MEMBER ORDER BY NAME) A WHERE ROWNUM <= 5
MEMBER테이블의 데이터를 NAME 순으로 정렬하고 5개까지만 데이터만 조회
SELECT * FROM MEMBER WHERE ROWNUM <= 5 ORDER BY NAME (잘못된 쿼리) 끝에서부터 5개의 데이터를 조회한 후 NAME순으로 정렬이 됨

★데이터 갯수 제한★

MySQL : 쿼리 마지막에 LIMIT 숫자
Oracle : WHERE절에 ROWNUM <= 숫자

★자르기★

공통

SELECT SUBSTR('내이름은이승엽', 5, 3) AS MY_NAME FROM MEMBER // 5번째 글자부터 3개

MySQL

DATETIME 컬럼의 값이 YYYY-MM-DD HH:MM:SS 형식일 때 바로 SUBSTR 가능

Oracle

DATETIME 컬럼의 값이 YYYY-MM-DD HH:MM:SS 형식일 때
TO_CHAR(DATETIME, 'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(DATETIME, 'HH24:MI:SS') 등의 문자 형태로  꾼 후

SUBSTR 가능하다.

★범위★

공통

SELECT * FROM MEMBER WHERE USER_NO BETWEEN 1 AND 100

★순번 채번★

MySQL

변수 사용
SET @HOUR = -1;
SELECT (@HOUR := @HOUR + 1) AS HOUR, NAME FROM MEMBER WHERE @HOUR < 23

Oracle

계층 쿼리 CONNECT BY LEVEL 사용
SELECT LEVEL-1 AS HOUR FROM DUAL CONNECT BY LEVEL <= 24

★시간★

YYYY-MM-DD hh:mm:ss와 같은 형식으로 반환

MySQL

SELECT NOW() AS '현재 시간'
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') // 대문자Y 소문자m 소문자d 대문자H 소문자i 소문자s // 0000-00-00 00:00:00

DATE_ADD(NOW(), INTERVAL 1 SECOND) // 1초 후
DATE_SUB(NOW(), INTERVAL 1 SECOND) // 1초 전

(SECOND, MINUTE, HOUR, DAY, MONTH, YEAR)

DATEDIFF(날짜1, 날짜2) // 두 날짜의 차

Oracle

SELECT SYSDATE FROM DUAL // AS 사용 불가
TO_CHAR(SYSDATE, 'YYYY/MM/DD') // 0000/00/00

SYSDATE + INTERVAL '1' SECOND // 1초 후
SYSDATE - INTERVAL '1' SECOND // 1초 전
SYSDATE + 1/(24*60*60) // 1초 후
SYSDATE - 1/(24*60*60) // 1초 전

(SECOND, MINUTE, HOUR, DAY, MONTH, YEAR)

TO_DATE(날짜1) - TO_DATE(날짜2) // 두 날짜의 차

TO_DATE('2020-05-02 20:50:50', 'YYYY-MM-DD HH24:MI:SS') // 문자형 -> 날짜형
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') // 날짜형 -> 문자형

★그룹★

공통

GROUP BY NAME을 써서 묶게 되면
NAME 컬럼 중 같은 값들끼리 묶이게 되는데, 이 때 보여지는 NAME들은 각 NAME 값들 중 제일 위에 조회되던 데이터의 값이다.
GROUP BY NAME 후 COUNT(NAME)을 써서 조회하면 같은 NAME으로 묶인 데이터가 몇 개씩인지 보여준다.