My Dev Blog

<Oracle>

프로시저(PROCEDURE) 조회 쿼리

SELECT *
  FROM USER_SOURCE
 WHERE TYPE = 'PROCEDURE'
   AND NAME = '프로시저명'

함수(FUNCTION) 조회 쿼리

SELECT *
  FROM USER_SOURCE
 WHERE TYPE = 'FUNCTION'
   AND NAME = '함수명'

프로시저와 함수의 차이

프로시저(PROCEDURE) : 넓은 의미로는 어떤 업무를 수행하기 위한 절차를 뜻한다.

함수(FUNCTION) : 위의 프로시저의 각 프로세스를 수행하기 위해 필요한 기능들을 함수라 한다.

<MySQL>

SQL_MODE 확인

SHOW VARIABLES LIKE 'SQL_MODE'

SQL_MODE 초기화

SET SQL_MODE = '';

SET @변수명 = 초기값 (:= 가능)

SELECT @변수명

또는

SELECT @변수명 := @변수명 + 더해줄 값

등으로 사용 가능

SELECT NAME
     , OCCUPATION
  FROM OCCUPATIONS

SELECT NAME
     , OCCUPATION
     , ROW_NUMBER() OVER (ORDER BY NAME)
  FROM OCCUPATIONS

SELECT NAME
     , OCCUPATION
     , ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME)
  FROM OCCUPATIONS

ANIMAL_INS 테이블의 데이터 중 NAME 컬럼 값이 대소문자 구분 없이

'el'을 포함하면서 ANIMAL_TYPE이 'Dog'인 데이터의 ANIMAL_ID와 NAME을 오름차순으로 정렬

<MySQL>

UPPER(NAME) : NAME 컬럼 값의 대문자

LOWER(NAME) : NAME 컬럼 값의 소문자

LIKE : 같은지 비교

CONCAT('A', 'B', 'C') : 'A'와 'B'와 'C'를 연결

'%' : 임의의 문자열('_' : 하나의 문자)

SELECT ANIMAL_ID
     , NAME
  FROM ANIMAL_INS
 WHERE UPPER(NAME) LIKE UPPER('%el%')
   AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME

SELECT ANIMAL_ID
     , NAME
  FROM ANIMAL_INS
 WHERE UPPER(NAME) LIKE CONCAT('%', UPPER('el'), '%')
   AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME

<Oracle>

UPPER(NAME) : NAME 컬럼 값의 대문자

LOWER(NAME) : NAME 컬럼 값의 소문자

LIKE : 같은지 비교

'A' || 'B' || 'C' : 'A'와 'B'와 'C'를 연결

'%' : 임의의 문자열('_' : 하나의 문자)

SELECT ANIMAL_ID
     , NAME
  FROM ANIMAL_INS
 WHERE UPPER(NAME) LIKE UPPER('%el%')
   AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME

SELECT ANIMAL_ID
     , NAME
  FROM ANIMAL_INS
 WHERE UPPER(NAME) LIKE '%' || UPPER('el') || '%'
   AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME

COUNT나 MAX와 같은 집계 함수를 조건으로 쓸 경우

'WHERE'절이 아닌

'HAVING'절에 쓴다.

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

EX) (X)

SELECT ID

             , NAME

             , HOST_ID

   FROM PLACES

WHERE HOST_ID IN (

                                      SELECT HOST_ID

                                         FROM PLACES

                                      WHERE COUNT(HOST_ID) > 1

                                GROUP BY HOST_ID

                                     )

ORDER BY ID

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

EX) (O)​

SELECT ID

             , NAME

             , HOST_ID

   FROM PLACES

WHERE HOST_ID IN (

                                      SELECT HOST_ID

                                         FROM PLACES

                                 GROUP BY HOST_ID

                                      HAVING COUNT(HOST_ID) > 1

                                    )

ORDER BY ID

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

★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으로 묶인 데이터가 몇 개씩인지 보여준다.