미완성, 수정 중 . . .

SQL 코딩 테스트를 준비하며 정리한 내용입니다. MySQL을 기준으로 작성하였습니다.

Keywords

Join

조인 방법에는 FROM절에서 JOIN ON으로 할 수 있고, WHERE절에서 조인할 수도 있다. Inner Join일 경우에는 결과가 같지만, Outer Join일 경우에는 다를 수 있기에 주의해야 한다.

INNER JOIN

조건을 만족시키는 경우에만 Join한다.

FROM FIRST_HALF A INNER JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR

SELECT A.FLAVOR
FROM FIRST_HALF A INNER JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR
WHERE TOTAL_ORDER >= 3000 AND INGREDIENT_TYPE = 'fruit_based'
ORDER BY A.TOTAL_ORDER DESC

RIGHT OUTER JOIN

조건을 만족하지 않더라도 오른쪽 테이블은 반드시 Join한다. 이 경우 왼쪽 테이블의 내용은 NULL이 들어간다.

SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_INS I RIGHT OUTER JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID

LIMIT

상위 N개만 출력 하는 키워드이다.
마지막 줄에 LIMIT 구문을 사용한다.

LIMIT N

상위 1개만 출력하는 경우

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

DISTINCT

중복을 제거하는 키워드이다.

다음은 이름(NAME)의 종류의 개수를 새는 명령문이다. DISTINCT를 제거한다면 단순히 NAME이 NULL이 아닌 항목의 개수를 출력하게 된다.

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS
WHERE NAME IS NOT NULL;

CASE

조건에 따라 값을 지정하는 문법이다.

CASE
WHEN [조건] THEN [TURE일 때 값]
ELSE [FALSE일 때 값]
END

아래는 서브 쿼리의 결과를 지정한 문구로 출력하도록 하는 SQL문이다.

SELECT CAR_ID,
    CASE
    WHEN CAR_ID IN (SELECT CAR_ID
                   FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                   WHERE START_DATE <= '2022-10-16' AND END_DATE >= '2022-10-16') THEN '대여중'
    ELSE '대여 가능'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

UNION

SELECT 문...

UNION

SELECT 문..`

SELECT문의 결과를 합칠 때 사용한다. UNION은 중복을 제거하고, UNION ALL은 중복을 포함한다.

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE 
WHERE SALES_DATE >= '2022-03-01' AND SALES_DATE <= '2022-03-31'
UNION 
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE  
WHERE SALES_DATE >= '2022-03-01' AND SALES_DATE <= '2022-03-31'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

NULL AS 컬럼명

UNION 시 컬럼 개수가 일치해야 한다. 만약 특정 컬럼을 끼워 넣기 위해 NULL값으로 채운다면 NULL AS 컬럼명을 사용하면 된다.

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT

USER_ID 컬럼의 내용을 NULL로 채운다.

LIKE

특정 문자열을 포함하고 있는 지 여부로 필터링한다. 정확한 글자 수는 _(언더바)로, 글자 수 상관 없음은 %로 표기한다.

WHERE 컬럼 LIKE '문자열'

'강원도' 가 들어가는 항목 출력

WHERE ADDRESS LIKE '%강원도%'

'강원도' 앞에는 2글자가 있지만, 뒤에는 무엇이 오든 상관 하지 않는 경우

WHERE ADDRESS LIKE '__강원도%'

'강원도' 로 시작하는 항목 출력

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY 
WHERE ADDRESS LIKE '강원도%'
ORDER BY FACTORY_ID

IN

IN 연산자는 주어진 값이 특정 리스트 또는 서브쿼리의 결과에 포함되어 있는지를 확인하는 데 사용된다.
아래는 서브 쿼리 결과에 포함되어 있는지 확인하는 예시 코드이다.

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (
    SELECT FOOD_TYPE, MAX(FAVORITES)
    FROM REST_INFO
    GROUP BY FOOD_TYPE
)
ORDER BY FOOD_TYPE DESC;

위 코드에서 서브 쿼리를 통해 FOOD_TYPE별로 최대 FAVORITES를 가져온다. 그리고 메인 쿼리에서 이와 일치하는 행만을 가져와 출력한다.

기억해야 할 점은, WHERE (FOOD_TYPE, FAVORITES) IN (...)와 같이 여러 개의 컬럼을 비교할 수 있다는 점이다. 이 때 IN 앞과 뒤의 컬럼 수는 일치하여야 한다.

Function

IFNULL

SELECT IFNULL(Column, "대체값") FROM 테이블명;

해당 컬럼에서 NULL값이 있다면 다른 값으로 바꿔주는 함수이다. NULL값이 아니라면 그대로 출력한다.

SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, 'NONE') as TLNO
FROM PATIENT
WHERE AGE <= 12 and GEND_CD = "W"
ORDER BY AGE desc, PT_NAME

DATE_FORMAT

DATE_FORMAT(날짜, '형식')

날짜 데이터를 원하는 형식으로 출력하는 함수이다. 컬럼 명이 달리지기 때문에 일반적으로 AS와 함께 사용한다.

형식	설명	예제
%Y	연도 (네 자리)	2024
%y	연도 (두 자리)	24
%m	월 (숫자, 01-12)	02
%d	일 (01-31)	18

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d')
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH)=3 AND GENDER="W" AND TLNO IS NOT NULL
ORDER BY MEMBER_ID

DATEDIFF

날짜 및 시간의 차이를 계산해 주는 함수이다.

DATEDIFF(구분자, 종료날짜, 시작날짜)

구분자에는 다음과 같은 것들이 있으며, 약어를 사용하여도 동일하게 적용된다.

설명	구분자	약어
년도	year	yy
월	month	mm
일	day	dd

주의: 시작 시점부터 1로 세기를 원한다면 결괏값에 +1을 해 주어야 한다.

SELECT
    HISTORY_ID,
    CAR_ID,
    DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE,
    DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE,
    CASE
        WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '장기 대여'
        ELSE '단기 대여'
    END AS RENT_TYPE
FROM
    CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE
    YEAR(START_DATE) = 2022
    AND MONTH(START_DATE) = 9
ORDER BY
    HISTORY_ID DESC;

당일 대여, 당일 반납이여도 1일로 간주하는 방식이다.

ROUND

소수점 반올림 시 사용하는 함수이다. 자리수가 1이라면 소수점 1번째 자리까지 출력한다(2번째 자리에서 반올림). 0이라면 정수로 출력한다(소수점 1번째 자리에서 반올림).

ROUND(컬럼, 자리수)

자리수를 생략한다면 기본적으로 0이 입력된다.

SELECT ROUND(AVG(DAILY_FEE), 0) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR 
WHERE CAR_TYPE='SUV'

FLOOR, CEIL

소수점 이하 버림과 올림 함수이다.

FLOOR(2.5)
CEIL(2.5)

CONCAT

문자열을 합치는 함수이다.

CONCAT(문자열1, 문자열2 ...)

여러 개의 값을 파라미터로 사용할 수 있으며 하나의 문자열을 반환한다.

SELECT CONCAT('/home/grep/src/', BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE 
WHERE BOARD_ID = (
    SELECT BOARD_ID
    FROM USED_GOODS_BOARD 
    ORDER BY `VIEWS` DESC
    LIMIT 1
)
ORDER BY FILE_ID DESC

SUBSTR

SUBSTR(컬럼, 시작 위치, 길이)

문자열의 일부를 잘라내는 함수이다. SUBSTRING()과 동일하다.
인덱스는 1부터 시작한다. 또한 종료 인덱스가 아닌 시작 위치로부터의 길이를 파라미터로 사용함에 주의한다.

요령 및 팁

날짜범위 사이 필터링

WHERE SALES_DATE >= '2022-03-01' AND SALES_DATE <= '2022-03-31'

3월 판매 기록만 필터링하는 절이다.

다음과 같이 BETWEEN 연산자를 사용할 수도 있다.

WHERE SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'

컬럼명이 예약어와 같을 때

컬럼명이 예약어와 같아 에러가 발생할 땐 ` `(백틱)으로 컬럼명을 감싸주면 된다.

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
       CASE
           WHEN `STATUS` = 'SALE' THEN '판매중'
           WHEN `STATUS` = 'RESERVED' THEN '예약중'
           WHEN `STATUS` = 'DONE' THEN '거래완료'
       END AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE = '2022-10-05'
ORDER BY BOARD_ID DESC;