[SQL] 코딩 테스트 대비 SQL 문법 정리

코딩 테스트 대비 SQL 문법 정리

들어가며

• 코딩 테스트를 하루 앞두고 SQL 문법을 정리해본다.

 

① SQL 쿼리 순서

적는 순서

• 셰프 왜 구해요

SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT

 

실행 순서

• 프리웨어 구하세요

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT

 

② IN

• 파이썬의 @in@과 같은 느낌으로 사용한다.

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE PRICE IN (SELECT MAX(PRICE) FROM FOOD_PRODUCT GROUP BY CATEGORY)
AND CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY PRICE DESC;

 

③ 문자열 & 와일드 카드

• @%@ : 1개 이상의 문자
• @_@ : 1개의 문자 

'공부%' : '공부'로 시작하는 문자열
'%공부' : '공부'로 끝나는 문자열
'%공부%' : '공부'가 들어간 문자열
'_공부' : 첫 글자 하나 있고 그 뒤에 '공부'가 있는 문자열 (3글자)
'공부_' : 처음에 '공부'가 있고 맨 끝에 글자 하나 있는 문자열 (3글자)
'_공부_' : 처음과 끝에 글자가 하나 있고, 그 가운데 '공부'가 있는 문자열 (4글자)

 

④ GROUP BY

• @GROUP BY@를 사용하면 @SELECT@ 문에 집계 함수를 사용할 수 있다.
  • @SUM@, @COUNT@, @MIN@, @MAX@, @AVG@ 등
• 만약 A, B, C라는 속성(Attribute)이 있고, A로 묶었을 경우, @SELECT A, COUNT(B)@처럼 묶은(GROUP BY) 속성만 그대로 추출 가능하고, 나머지 속성은 통계 수치(@COUNT@, @SUM@, @AVG@ 등)만 추출할 수 있다. 

 

⑤ JOIN

• JOIN을 할 때, @LEFT@ 나 @RIGHT@를 쓰지 않고 그냥 @JOIN@만 사용한다.
  
  • 필요한 경우에는 괄호(@()@) 내에 다른 @SELECT@ 문을 쓴다.
• 반드시 @ON@을 이용하여 두 테이블의 키(Key)를 이어준다.

SELECT A.PRODUCT_ID, A.PRODUCT_NAME, (A.PRICE * B.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT A JOIN(
    SELECT PRODUCT_ID, SUM(AMOUNT) AS AMOUNT
    FROM FOOD_ORDER
    WHERE PRODUCE_DATE LIKE '2022-05%'
    GROUP BY PRODUCT_ID
) B
ON A.PRODUCT_ID = B.PRODUCT_ID
ORDER BY TOTAL_SALES DESC;

 

⑥ 조건문

[1] SWITCH

CASE
... -- WHEN과 THEN의 반복
ELSE
END AS 컬럼명

SELECT ORDER_ID, PRODUCT_ID, OUT_DATE,
    CASE
    WHEN OUT_DATE IS NULL
    THEN '출고미정'
    WHEN OUT_DATE <= '2022-05-01'
    THEN '출고완료'
    ELSE '출고대기'
    END AS 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID;

 

[2] IF

• 조건이 맞을 경우 @참값@을, 틀릴 경우 @거짓값@을 입력한다.

SELECT IF(조건, 참값, 거짓값)

 

[3] IFNULL

• 해당 컬럼의 값 중, @NULL@이 있으면 @대체값@을 입력한다.

SELECT IFNULL(COLUMN, 대체값)

 

[4] BETWEEN

• 컬럼 옆에 바로 붙여 써준다.

SELECT ...
FROM ...
WHERE ID BETWEEN 10 AND 40;

 

⑦ 숫자

[1] 소수 : ROUND, CEIL, FLOOR

ROUND(숫자, 반올림할_자릿수)    -- 반올림
CEIL(숫자)    -- 올림
FLOOR(숫자)   -- 내림

• 소수점 첫 번째 자리를 @0@으로 인식하기 때문에, 세 번째 자리에서 반올림하려면 @2@를 쓴다.
• 문제에서 @N@ 번째 자리에서 반올림 하라고 제시되어 있다면, @ROUND@ 함수의 두 번째 인자를 @N-1@로 적어준다.

SELECT A.REST_ID, A.REST_NAME, A.FOOD_TYPE, A.FAVORITES, A.ADDRESS, B.SCORE
FROM REST_INFO A JOIN(
    SELECT REST_ID, ROUND(AVG(REVIEW_SCORE), 2) AS SCORE
    FROM REST_REVIEW
    GROUP BY REST_ID) B
ON A.REST_ID = B.REST_ID
WHERE A.ADDRESS LIKE '서울%'
ORDER BY SCORE DESC, A.FAVORITES DESC;

 

[2] 연산

POW(밑, 지수)    -- 제곱
ABS(숫자)    -- 절댓값

 

⑧ 변수

SET @변수명 = 값;

 

• 위와 같이 변수를 선언하고 값을 할당할 수 있고, 아래와 같이 @＠변수명@으로 변수를 사용할 수 있다.

SET @MX = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT
WHERE PRICE = @MX;

 

• 하지만, 변수에 들어갈 수 있는 타입(Type)이 제한되어 있다.

타입
숫자(Numeric) 타입	INTEGER	정수 타입 (일반적으로 4바이트)
	BIGINT	큰 정수 타입 (일반적으로 8바이트)
	DECIMAL(precision, scale)	- 고정 소수점 숫자 타입 - @precision@은 총 자릿수, @scale@은 소수 자릿수
문자열(String) 타입	CHAR(n)	- 고정 길이 문자열 - @n@은 최대 문자 개수
	VARCHAR(n)	- 가변 길이 문자열 - @n@은 최대 문자 개수
	TEXT	매우 긴 가변 길이 문자열
날짜(Date)와 시간(Time) 타입	DATE	날짜 (년-월-일)
	TIME	시간 (시:분:초)
	DATETIME/TIMESTAMP	날짜와 시간 (년-월-일 시:분:초)
불리언(Boolean) 타입	BOOLEAN/BOOL	참(True) 또는 거짓(False)
이진(Binary) 데이터 타입	BLOB	바이너리 데이터 (이미지, 동영상 등)
기타 타입	JSON	JSON 데이터 타입
	ARRAY	배열 데이터 타입 (몇몇 DB 시스템에서 제공)

 

⑨ DATE

• @DATE@ 관련 단위는 다음과 같다.

초	분	시	일	주	월	분기	연
SECOND	MINUTE	HOUR	DAY	WEEK	MONTH	QUARTER	YEAR

 

[1] 각 연/월/일 가져오기

YEAR(날짜)
MONTH(날짜)
DAY(날짜)
DATE(날짜)

 

[2] 형 변환

DATE_FORMAT

DATE_FORMAT(날짜, 포맷)

• 예를 들어 @2023-08-03@를 표현하고 싶다면, @포맷@을 @%Y-%m-%d@로 지정한다.

 

날짜 데이터 포맷(Format)

• @%Y@: 연도 (4자리)
• @%y@: 연도 (2자리)
• @%m@: 월 (01부터 12까지)
• @%d@: 일 (01부터 31까지)
• @%H@: 시간 (00부터 23까지, 24시간 형식)
• @%h@: 시간 (01부터 12까지, 12시간 형식)
• @%i@: 분 (00부터 59까지)
• @%s@: 초 (00부터 59까지)
• @%p@: 오전/오후 (AM 또는 PM)

 

TO_DATE

TO_DATE(STRING_날짜, 포맷)

• 문자열(String)로 표현된 날짜를 실제 날짜 데이터(DATE)로 변환해주는 함수이다. 

 

[3] 날짜 더하기, 빼기

DATE_ADD(DATE, INTERVAL 숫자 단위)
DATE_SUB(DATE, INTERVAL 숫자 단위)

SELECT DATE_ADD(NOW(), INTERVAL 5 DAY);    -- 현재의 날짜에 5일을 더한다.

 

단위

• @DAY@, @MONTH@, @YEAR@, @HOUR@, @MINUTE@, @SECOND@ 등

 

[4] 날짜 차이 구하기

DATEDIFF(날짜1, 날짜2)    -- 일(DAY) 기준

TIMESTAMPDIFF(단위, 날짜1, 날짜2)    -- 단위 기준

SELECT DATEDIFF('2023-08-15', '2023-08-03');
SELECT TIMESTAMPDIFF(DAY, '2023-08-03', '2023-08-15');

 

⑩ 순위 구하기

SELECT A, B, C, RANK() OVER(ORDER BY salary DESC) "등수"
FROM TABLE;

 

• @RANK@ 관련 함수는 3가지(@RANK@, @DENSE_RANK@, @ROW_RANK@) 있는데, 중복 순위를 어떻게 처리하는지에 따라 그 결과가 달라진다.
  • @RANK@ : 1-2-3-3-6
  • @DENSE_RANK@ : 1-2-3-3-3-4
  • @ROW_NUMBER@ : 1-2-3-4-5-6

 

⑪ 중복 제거

SELECT DISTNCT COLUMN
FROM TABLE

• 중복을 제거하고 싶은 컬럼명 앞에 @DISTINCT@ 키워드를 붙여 사용한다.
• 여기서 주의할 점이 있는데, @DISTINCT@는 함수처럼 사용하는 것이 아니며, @DISTINCT@를 어디에 위치시키는지에 따라 결과가 달라질 수 있다는 것이다. 
  • @DISTINCT@는 개별 컬럼에만 적용되는 것이 아니라, @DISTINCT@ 뒤에 나오는 모든 컬럼에 적용된다.
  • 자세한 내용은 이곳을 참고한다.

 

⑫ 특정 개수 만큼 가져오기

SELECT ...
FROM ...
LIMIT 가져올_개수

 

• 쿼리문의 맨 마지막에 @LIMIT@ 문을 넣어서 특정 개수의 행만 가져올 수 있다.

 

⑬ 문자열

[1] 문자열 길이

CHAR_LENGTH

CHAR_LENGTH(문자열)

• 문자열의 길이를 문자의 수로 반환한다.
• 주로 멀티 바이트 문자셋(UTF-8)에서 문자열의 길이를 구할 때 사용한다.

 

LENGTH

LENGTH(문자열)

• 문자열의 길이를 바이트 단위로 반환한다.
• 문자열의 바이트 수를 반환하며, 멀티 바이트 문자셋에서도 각 문자의 바이트 수를 그대로 세어서 길이를 계산한다.
• UTF-8에서 한글 문자는 3바이트로 표현된다.

 

CHAR_LENGTH('안녕하세요')    -- 5 (문자의 수)
LENGTH('안녕하세요')    -- 15 (UTF-8에서 한글 문자는 3바이트로 표현되므로 총 15바이트)

 

[2] 소문자와 대문자

LOWER

LOWER(문자열)

• 대문자를 소문자로 변환한다.

 

UPPER

UPPER(문자열)

• 소문자를 대문자로 변환한다.

 

[3] 왼쪽/오른쪽/중간 기준 문자열 가져오기

LEFT

LEFT(문자열, 위치)

 

• @문자열@의 첫 번째 문자부터 @위치@번째 문자까지를 반환한다.

 

RIGHT

RIGHT(문자열, 위치)

 

• @문자열@의 @위치@번째 문자부터 마지막 문자까지를 반환한다.

 

SUBSTR

SUBSTR(문자열, 시작위치, 가져올개수)

 

• @문자열@의 @시작위치@에 있는 문자부터 오른쪽으로 @가져올개수@ 만큼의 문자를 반환한다.

 

// STR = 'HELLO'
LEFT(STR, 2)       -- HE
RIGHT(STR, 2)      -- LO
SUBSTR(STR, 2, 2)  -- EL

 

[4] 공백 제거

왼쪽 공백 제거

LTRIM(문자열)
TRIM(LEADING FROM 문자열)

 

오른쪽 공백 제거

RTRIM(문자열)
TRIM(TRAILING FROM 문자열)

 

양쪽 공백 제거

TRIM(문자열)

 

[5] 특정 문자 제거

왼쪽 문자 제거

TRIM(LEADING 문자 FROM 문자열)

 

오른쪽 문자 제거

TRIM(TRAILING 문자 FROM 문자열)

 

양쪽 문자 제거

TRIM(BOTH 문자 FROM 문자열)

 

⑭ 컬럼 합치기

CONCAT

CONCAT(컬럼1, 컬럼2)

 

SELECT DISTINCT CONCAT(A, B) AS ONE