[SQL] 코딩 테스트를 위한 SQL 정리

관계형 DB관리 시스템인 MySQL을 사용합니다.

 

코딩 테스트 전 알아야 할 SQL 문법들에 대해 정리합니다.

 

간단하게 DB 다루기

기본적으로 DB 안에는 여러 개의 이름이 존재하고, 각 DB 이름 안에는 여러 개의 테이블이 있습니다. 

보통의 코딩 테스트 문제에서는 테이블을 1개에서 많으면 3개까지 제공하여 문제를 해결합니다.

 

DB와 테이블 생성하기

MySQL에서는 CREATE 문을 활용하여 DB와 테이블을 만들 수 있습니다.

CREATE DATABASE {DBNAME}
USE {DBNAME}

DBNAME 공간에 원하는 이름을 넣으면 그 이름을 가진 데이터 베이스가 생성됩니다. 생성한 데이터 베이스 사용을 위해 USE를 사용하여 데이터베이스를 선택할 수 있습니다.

CREATE TABLE Test
(
    ID INT,
    Name VARCHAR(30),
    ReserveDate DATE,
    RoomNum INT
);

다음 코드는 4개의 필드를 갖는 Test 테이블을 생성하는 예제입니다. CREATE TABLE 문을 활용하여 테이블을 생성할 수 있습니다. 

 

기본키와 외래키의 차이점

기본키: 테이블에서 데이터를 유일하게 구분하는 키를 기본키라 합니다.

외래키: 각 테이블 간에 연결을 만들기 위해 테이블에서 다른 테이블의 참조되는 기본키 칼럼을 외래 키라고 합니다.

 

테이블 레코드 선택하기

SELECT 문을 활용하면 테이블의 레코드를 선택할 수 있습니다. 

SELECT * FROM 테이블이름

테이블이름이라는 테이블로 부터 모든 필드를 선택하는 구문입니다. 에스터리스크(*)는 모든 필드를 뜻합니다.

SELECT ID, NAME as SHIP FROM Reservation;

SELECT 문 뒤에 특정 필드 이름을 넣어 해당 필드만 가져올 수도 있습니다. as 문을 활용하여 원래 있던 필드의 이름을 변경할 수도 있습니다. 

https://school.programmers.co.kr/learn/courses/30/lessons/133025

프로그래머스

프로그래머스 코딩테스트 연습 SELECT문 과일로 만든 아이스크림 고르기에서 사용되는 별칭에 대해서 알아봅시다.

SELECT FH.FLAVOR FROM FIRST_HALF FH, ICECREAM_INFO II
WHERE FH.FLAVOR = II.FLAVOR AND FH.TOTAL_ORDER > 3000 AND II.INGREDIENT_TYPE = 'fruit_based'
ORDER BY TOTAL_ORDER desc

모든 SELECT문은 FROM문 뒤에 테이블 명을 작성합니다. 여러 개의 테이블을 선택할 때 테이블 명 뒤에 별칭을 작성해 주면, 다른 SQL 문에서 해당 별칭과 필드 명을 사용하여 두 개의 테이블에 같은 필드 명이 있더라도 분리하여 사용이 가능합니다.

 

WHERE 절 알아보기

SELECT * FROM Reservation
WHERE Name LIKE '장건호';

Reservation 테이블에서 Name 필드에 장건호라는 이름을 찾는 SQL 문입니다. 이렇게 특정 필드의 데이터를 필터링해주는 기능을 가지고 있습니다.

패턴 매칭

WHERE문의 꽃은 패턴 매칭이다. LIKE 연산자를 활용 하여 특정 패턴을 포함하는 데이터를 찾아낼 수 있다. 

SELECT * FROM Reservation
WHERE Name LIKE '장%';

Reservation 테이블에서 Name 필드 내에 장씨로 시작하는 이름을 찾는 SQL 문이다. 이렇게 와일드카드 문자를 활용하여 특정 패턴을 포함하는 데이터를 찾아낼 수 있습니다. 

%	0개 이상의 문자를 대체한다.
_	1개의 문자를 대체한다.

와일드 카드를 활용하여 문자열 내에서 임의의 문자나 문자열을 대체하여 데이터를 찾아낼 수 있습니다.

 

NULL 대체

null 처리를 위해서는 CASE WHEN문 또는 IFNULL 문을 사용하면 좋습니다. 먼저 CASE WHEN문을 알아봅시다.

https://school.programmers.co.kr/learn/courses/30/lessons/132201

프로그래머스

프로그래머스 코딩테스트 연습 SELECT문 12세 이하인 여자 환자 목록 출력하기 문제에서 전화번호 값이 NULL인 경우(없는 경우) 'NONE'으로 출력시켜야 한다는 문장이 있습니다. 

SELECT PT_NAME, PT_NO, GEND_CD, AGE, 
	CASE WHEN TLNO IS NULL THEN 'NONE'
	ELSE TLNO END AS TLNO
FROM PATIENT

다음과 같이 TLNO(전화번호) 가 NULL인 경우엔 THEN을 통해 'NONE'으로 변경시켜 주고 아닌 경우엔 TLNO 그대로 출력시켜 주는 문입니다. 

SELECT PT_NAME, PT_NO, GEND_CD, AGE, 
IFNULL(TLNO, 'NONE') as TLNO
FROM PATIENT

다음과 같이 IFNULL 함수를 통해 CASE WHEN ~ ELSE 문과 같은 효과를 볼 수 있습니다. 

 

GROUP BY

GROUP BY 절은 동일한 값을 가진 컬럼을 기준으로 그룹별 연산을 적용합니다.

나누고자 하는 그룹의 컬럼명을 GROUP BY 절 뒤에 추가해 주면 됩니다. 

SELECT USER_ID, PRODUCT_ID FROM ONLINE_SALE 
GROUP BY USER_ID, PRODUCT_ID

 

HAVING

HAVING은 WHERE 절과 다르게 HAVING 절은 GROUP BY와 함께 사용하는 조건 절이라는 점입니다. 한 마디로 그룹별 집계된 결과 중 원하는 조건의 결과만 필터링하기 위해 사용하는 절인 것입니다.

https://school.programmers.co.kr/learn/courses/30/lessons/131536

프로그래머스

프로그래머스 코딩 테스트 SELECT문 재구매가 일어난 상품과 회원 리스트 구하기 문제에서 이용자가 어떠한 PRODUCT를 재 구매 했을 때 그 USER_ID와 PRODUCT_ID를 출력하라고 나와있습니다.

SELECT USER_ID, PRODUCT_ID FROM ONLINE_SALE 
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(USER_ID) >= 2

HAVING 절과 COUNT() 집계함수를 통해 GROUP BY를 통해 그룹 화 된 테이블에서 같은 값이 2개 이상(count 해서 2개 이상)인 데이터들만 뽑아오는 문입니다.

 

그룹 함수

대표적으로 많이 사용되는 그룹함수는 COUNT(), MIN(), MAX(), SUM(), AVG()가 있습니다. 

대부분의 그룹 함수는 NULL 값을 제외하고 동작한 결과를 반환합니다. 

예로 다음과 같은 PRODUCT 테이블에서 그룹 함수를 사용해 봅시다.

PRODUCT_ID	PRODUCT_CODE	PRICE
1	A10001	10000
2	A20005	9000
3	C10006	22000

먼저 COUNT함수는 특정 조건을 만족하는 레코드의 총 개수를 반환합니다. 

SELECT COUNT(*) as COUNT FROM PRODUCT

결과 값은 3이 될 것입니다. 총 레코드의 개수가 3개이기 때문입니다. 

MIN과 MAX 함수는 특정 필드의 최소와 최대 값을 구하는 함수입니다.

SELECT MAX(PRICE) as MAX_PRICE FROM PRODUCT

다음 코드는 PRODUCT 테이블 내에 PRICE 값 중 최대 값을 찾는 거니 22000이 나오게 될 것입니다. 

SUM함수와 AVG 함수도 COUNT, MAX 함수와 동일하게 사용하면 합과 평균이 나오게 될 것입니다. 

 

중복 제거

DISTINCT를 사용하면 중복 제거를 할 수 있습니다. 

SELECT DISTINCT 필드 FROM 테이블

해당 필드의 중복된 값을 제외하여 출력할 수 있습니다.

 

쿼리문 합치기

UNION 연산자를 통해 하나의 데이터를 2개로 출력 할 수 있습니다.

TABLE1

ID	NAME
1	신종웅
2	최재혁

TABLE2

ID	NAME
2	신종웅
3	강민석

다음과 같은 테이블이 존재 할 때 각각의 테이블을 합치기 위해 UNION ALL을 사용 할 수 있습니다.

 SELECT ID, NAME FROM TABLE1
 UNION ALL
 SELECT ID, NAME FROM TABLE2

ID	NAME
1	신종웅
2	최재혁
2	신종웅
3	강민석

UNION ALL은 쿼리에서 나온 데이터를 하나로 합쳐주고, 중복되는 데이터도 모두 출력된다 하지만 UNION은 중복되는 값은 빼고 출력됩니다.

SELECT ID, NAME FROM TABLE1 
UNION
SELECT ID, NAME FROM TABLE2

ID	NAME
1	신종웅
2	최재혁
3	강민석

 

반올림과 버림

MySQL 쿼리에서 ROUND 함수를 사용하면 반올림을 사용할 수 있습니다. ROUND(데이터, 반올림할 자릿수) 형태로 함수를 사용할 수 있습니다.

SELECT ROUND(1234.56789) FROM MYTABLE
-- 1234

SELECT ROUND(1234.56789, 1) FROM MYTABLE
-- 1234.6 (1의 자리수까지 반올림)

SELECT ROUND(1234.56789, 3) FROM MYTABLE
-- 1234.568

SELECT ROUND(1234.56789, -1) FROM MYTABLE
-- 1230

 

MySQL 쿼리에서 TRUNCATE 함수를 사용하면 버림을 사용할 수 있습니다. TRUNCATE (데이터, 반올림 할 자릿수) 형태로 함수를 사용할 수 있습니다.

SELECT TRUNCATE(1234.56789, 1) FROM MYTABLE;
-- 1234.5

SELECT TRUNCATE(1234.56789, -2) FROM MYTABLE;
-- 1200

 

String 관련 함수

LENGTH 함수는 문자열의 길이를 반환합니다.

SELECT LENGTH('12345678');

-- 8

 

CONCAT 함수는 인자로 들어온 문자들을 모두 결합하여 하나의 문자열로 반환합니다.

인자로 NULL이 끼여있다면 NULL을 반환합니다.

SELECT CONCAT('프론트', '엔드', 'isGood'), 
-- 프론트엔드isGood
CONCAT('프론트', NULL, 'isBad');
-- NULL

 

LOCATE 함수는 인자로 온 문자열중 특정 문자열에서 처음으로 나타나는 위치를 찾아서, 해당 위치를 반환합니다. 없다면 0을 반환합니다.

SELECT LOCATE('cde', 'abcdefg'), 
-- 2

 

문자열 추출 관련 함수로는 SUBSTR, LEFT, RIGHT가 있습니다.

LEFT 함수는 인자로 온 문자열의 왼쪽부터 개수만큼의 문자를 반환합니다.

RIGHT 함수는 인자로 온 문자열의 오른쪽부터 개수만큼의 문자를 반환합니다.

SELECT LEFT('A12345, 2), 
-- A1

SELECT RIGHT('A12345, 2), 
-- 45

SUBSTR 함수는 인자로 받은 문자열을 2번째로 받은 인자의 인덱스부터 3번째로 받은 인자의 인덱스까지 잘라줍니다.

SELECT SUBSTR('A1234', 1, 2)
-- A1

 

날짜와 시간 관련 함수

날짜와 시간의 형식화를 위한 함수가 바로 DATE_FORMAT() 함수입니다. 전달받은 형식에 맞춰서 날짜와 시간 정보를 문자열로 변환해 줍니다. 

DATE_FORMAT(HIRE_YMD, '%Y-%m-%d')

다음과 같은 FOTMAT은 2017-03-01 00:00:00를 2017-03-01 시간을 떼고 출력할 수 있도록 만들어 줍니다.

 

TIMEDIFF 함수는 함수 이름대로 시간 차이를 계산 해주는 역할을 합니다. 계산은 인자1 - 인자2 방식으로 진행됩니다.

SELECT TIMEDIFF("2023-02-22 14:00:00" , "2023-02-22 00:00:00");
-- 14:00:00

 

DATEDIFF 함수도 마찬가지로 함수의 이름대로 날짜 차이를 계산 해주는 역할을 합니다. 계산은 인자1 - 인자2 방식으로 진행됩니다. 

SELECT DATEDIFF("2023-02-22 00:00:00", "2000-02-22 00:00:00");
-- 8401

 

TIMESTAMPDIFF 함수는 모든 시간 관련 차이를 계산 할 수 있는 함수입니다. TIMESTAMPDIFF('형식', '날짜1', '날짜2') 형식으로 사용합니다. 해당 계산은 날짜2 - 날짜1 방식으로 진행 됩니다. 

결과 값으로 받을 수 있는 형식은 8개가 있습니다.

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

Example

1. 두 날짜 간 시간 차이를 초로 표현할 때

SELECT TIMESTAMPDIFF(SECOND,"2023-02-22 14:00:00" , "2023-02-22 15:00:00");
-- 3600

결과가 초 단위로 나오게 됩니다.

 

2. 두 날짜 간 시간 차이를 시로 표현할 때

SELECT TIMESTAMPDIFF(HOUR,"2023-02-22 14:00:00" , "2023-02-22 15:00:00");
-- 1

결과가 시간(Hour) 단위로 나오게 됩니다.

 

재귀 표현으로 쿼리 만들기

https://school.programmers.co.kr/learn/courses/30/lessons/59413

프로그래머스

입양 시각 구하기(2)번 문제는 재귀 표현을 사용하면 편한 문제이다. 
WITH RECURSIVE 문을 통해 사용 할 수 있다. 

WITH RECURSIVE EMPTY_HOUR_TABLE AS (
    SELECT 0 AS HOUR, 0 AS COUNT # 초기값을 설정
    UNION # 위 쿼리와 아래 쿼리의 값을 합친다.
    SELECT HOUR + 1, COUNT #Hour 값을 1씩 증가 시킨다.
    FROM EMPTY_HOUR_TABLE 
    WHERE HOUR < 23 #반복을 멈추는 용도
)

 

HOUR	COUNT
0	0
1	0
2	0
3	0
4	0
5	0
6	0
7	0
...	...
23	0

다음 표와 같은 테이블을 만들 수 있는 문이다.

레퍼런스

• http://www.tcpschool.com/mysql/intro

• https://bramhyun.tistory.com/27

• https://velog.io/@cyanred9/SQL-Recursive