[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