MySQL 문법 정리 (w. 프로그래머스 SQL Lv. 3 문제 풀이)

프로그래머스 SQL 문제를 풀면서 헷갈렸던 문법들을 정리해보자.

 

그 전에, SQL 구문(문법) 순서에 대해서 알아보자

[SQL 구문의 순서]

SELECT 컬럼명 --------------------- (5)
FROM 테이블명 ------------------- (1)
WHERE 테이블 조건 --------------- (2)
GROUP BY 컬럼명 -------------------- (3)
HAVING 그룹 조건 ----------------- (4)
ORDER BY 컬럼명 -------------------- (6)

[실제 원하는 데이터를 찾는 과정]

1. FROM : SQL은 구문이 들어오면 테이블을 가장 먼저 확인한다. 테이블이 없는데 다른 것들을 먼저 조회할 수 없으니까.

2. WHERE : 테이블명을 확인했으니, 테이블에서 주어진 조건에 맞는 데이터들을 추출해준다.

3. GROUP BY : 조건에 맞는 데이터가 추출되었으니, 공통적인 데이터들끼리 묶어 그룹을 만들어준다.

4. HAVING : 공통적인 데이터들이 묶여진 그룹 중, 주어진 조건에 맞는 그룹들을 추출한다.

5. SELECT : 최종적으로 추출된 데이터들을 조회한다.

6. ORDER BY : 추출된 데이터들을 정렬한다.

     * SELECT 다음으로 오는 구문은 ORDER BY 뿐이므로,

       SELECT 에서 만들어진 Alias 는 ORDER BY 구문에서만 사용 가능하다.

 

추후 추가 : EXISTS, TIMESTAMPDIFF, TRIM, REPLACE, ALL, ANY 등

---

[1] WHERE 절에서 두가지 조건으로 검색 + 서브쿼리 사용 + IN절

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

# REST_INFO 테이블에서 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요.
# 이때 결과는 음식 종류를 기준으로 내림차순 정렬해주세요.
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

+ GROUP BY 절이 있는 구문에서 SELECT절에는 GROUP BY의 기준이 되는 컬럼 또는 집계함수만 들어갈 수 있다

( 다른 것을 넣어도 MySQL은 에러를 발생하지 않는다... 그래서 잘못된 부분을 잡기 더 어려울 수 있으니 주의하기 )

---

[2] CONCAT, CONCAT_WS, SUBSTR, LEFT, RIGHT, COUNT 함수

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

# USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 중고 거래 게시물을 3건 이상 등록한 사용자의
# 사용자 ID, 닉네임, 전체주소, 전화번호를 조회하는 SQL문을 작성해주세요.
# 이때, 전체 주소는 시, 도로명 주소, 상세 주소가 함께 출력되도록 해주시고, 전화번호의 경우 xxx-xxxx-xxxx 같은 형태로 하이픈 문자열(-)을 삽입하여 출력해주세요.
# 결과는 회원 ID를 기준으로 내림차순 정렬해주세요.
SELECT U.USER_ID, U.NICKNAME, CONCAT(U.CITY, " ", U.STREET_ADDRESS1, " ", U.STREET_ADDRESS2) AS 젼체주소,
       CONCAT(SUBSTR(TLNO, 1, 3), "-", SUBSTR(TLNO, 4, 4), "-", SUBSTR(TLNO, 8)) AS 전화번호 FROM USED_GOODS_USER U
       JOIN USED_GOODS_BOARD B ON U.USER_ID=B.WRITER_ID
       GROUP BY B.WRITER_ID HAVING COUNT(B.WRITER_ID)>=3
       ORDER BY U.USER_ID DESC

SELECT U.USER_ID, U.NICKNAME, CONCAT_WS(" ", U.CITY, U.STREET_ADDRESS1, U.STREET_ADDRESS2) AS 젼체주소,
       CONCAT_WS("-", LEFT(TLNO, 3), SUBSTR(TLNO, 4, 4), RIGHT(TLNO, 4)) AS 전화번호 FROM USED_GOODS_USER U
       JOIN USED_GOODS_BOARD B ON U.USER_ID=B.WRITER_ID
       GROUP BY B.WRITER_ID HAVING COUNT(B.WRITER_ID)>=3
       ORDER BY U.USER_ID DESC

---

CONCAT(..., ..., ...)	괄호 안의 문자열들을 이어붙임
CONCAT_WS(S, ..., ..., ...)	괄호 안의 문자열들을 S로 이어붙임

숫자가 끼어있어도 문자열로 변환되어 이어진다.

---

SUBSTR, SUBSTRING	주어진 값에 따라 문자열 자름
LEFT	왼쪽부터 N글자
RIGHT	오른쪽부터 N글자

1. SUBSTR(대상 문자열, 시작점(1부터 카운팅), 가져올 글자 수(생략일 경우 끝까지)) : 인수를 3개 받을 수 있다.

• 첫번째 인수를 대상으로 두번째 인수부터 시작하여 세번째 인수의 수 만큼 왼쪽에서 오른쪽으로 센 후 뒤의 문자를 잘라서 가져온다.
• 첫번째 인수 : 대상 문자열 / 두번째 인수 : 시작점(1부터 카운팅) / 세번째 인수 : 가져올 글자 수 (생략일 경우 시작점부터 끝까지 가져옴)
• 두번째 인수가 음수인 경우 문자의 뒤(오른쪽) 끝에서부터 셈을 한다.

2. LEFT(문자열, 개수) : 문자열 왼쪽에서부터 두번째 인자에 적힌 수 만큼 글자를 가져온다.
3. RIGHT(문자열, 개수) : 문자열 오른쪽에서부터 두번째 인자에 적힌 수 만큼 글자를 가져온다.

---

COUNT

갯수 (NULL값 제외)

테이블에 컬럼의 데이터 개수를 가져옴 (NULL인 데이터는 제외)

전체 행 개수를 가져올 때는 컬럼명 대신 * 사용 가능

---

[3] (⭐어려움⭐) MAX, SUM, IF, BETWEEN 함수 (+ CASE, IFNULL 함수)

- 단순 함수 문법이 아닌, 풀이 방법을 잘 기억해둘 것 ! (좋은 방법은 아님, 좀 억지 풀이법 .... 설명은 밑에)

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

# CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요.
# 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.
SELECT CAR_ID, MAX(IF(START_DATE<='2022-10-16' AND '2022-10-16'<=END_DATE, '대여중', '대여 가능')) AS AVAILABILITY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY GROUP BY CAR_ID ORDER BY CAR_ID DESC

SELECT CAR_ID, IF(SUM(IF('2022-10-16' BETWEEN START_DATE AND END_DATE, 1, 0))=0, '대여 가능', '대여중') AS AVAILABILITY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY GROUP BY CAR_ID ORDER BY CAR_ID DESC

 

- (추가) 더 좋은 방법 : LEFT JOIN 사용

SELECT DISTINCT H.CAR_ID, IF(R.CAR_ID IS NULL, '대여 가능', '대여중') AS AVAILABILITY 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H 
LEFT JOIN (SELECT DISTINCT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE) R ON H.CAR_ID=R.CAR_ID 
ORDER BY CAR_ID DESC

 

- (추추가) 가장 좋은 방법 : IN절, DISTINCT 사용 / GROUP BY 사용x

SELECT DISTINCT CAR_ID, 
IF(CAR_ID IN (SELECT DISTINCT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE), '대여중', '대여 가능') AS AVAILABILTIY 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
ORDER BY CAR_ID DESC

---

- 문법 설명

1. MAX() : 최댓값 반환. 괄호 안에 컬럼명 등이 들어갈 수 있음

2. SUM() : 총합 반환. 괄호 안에 컬럼명 등이 들어갈 수 있음

3. IF문 : IF(조건식, 조건식이 참일 때 값, 조건식이 거짓일 때 값) AS ALIAS

4. CASE문 :

CASE 
    WHEN 조건1 THEN '조건1 참일 경우 반환값'
    WHEN 조건2 THEN '조건2 참일 경우 반환값'
    ELSE '충족되는 조건 없을 때 반환값'
END AS ALIAS

5. IFNULL문 : IFNULL(컬럼(데이터), '인수1의 데이터 값이 NULL일 경우 출력할 값')

    - 컬럼이 NULL이 아니면 그대로 출력하고, NULL일 경우 2번째 인자에 적은 값을 출력한다.

6. BETWEEN : 'WHERE 컬럼명 BETWEEN 시작범위 AND 종료범위'과 같이 사용

    - '시작범위<=(컬럼)<= 종료범위'로 검색, 시작범위와 종료범위를 모두 포함한다.

---

- 첫번째 풀이 설명

MAX(IF(START_DATE<='2022-10-16' AND '2022-10-16'<=END_DATE, '대여중', '대여 가능'))

IF절을 통해 대여 중인 경우 '대여중', 대여 중이 아닌 경우 '대여 가능' 이라고 출력될 것이다.

이때 문제에서 요구하는 '대여 가능'이 되려면, 해당 CAR_ID의 모든 값이 '대여 가능'이여야 '대여 가능' 이라고 판단할 수 있다. 만약 IF절에서 나온 값에 '대여중'과 '대여 가능'이 함께 있다면 이 자동차는 '대여중'이라고 출력되어야 한다.

(다시 말해, '대여 가능' 상태라면 모든 데이터가 '대여 가능'일 경우에만 가능하다.)

 

SELECT IF('대여중'>'대여 가능', 1, 0);

로 검색해보면 1이 나온다. 즉, '대여중 > 대여 가능'이다.

 

따라서 IF문에서 나온 값을 MAX() 함수로 처리하면,

모든 데이터가 '대여중'이면 -> '대여중'이,

모든 데이터가 '대여 가능'이면 -> '대여 가능'이,

'대여중'과 '대여 가능'이 함께 존재하면 -> '대여중' 이

출력될 것이다.

 

위와 같이 IF와 MAX 함수를 같이 사용하여 문제에서 원하는 최종 답을 출력할 수 있다.

---

- 두번째 풀이 설명

IF(SUM(IF('2022-10-16' BETWEEN START_DATE AND END_DATE, 1, 0))=0, '대여 가능', '대여중')

안쪽의 IF절 'IF('2022-10-16' BETWEEN START_DATE AND END_DATE, 1, 0)' 을 통해

대여중일 경우 1, 대여 중이 아닐 경우 0으로 표시된다.

이것들을 SUM 함수로 모두 더했을 때, 값이 0이라면(=모든 데이터가 대여 중이 아닐 경우, 0+0+0+0 ..) 대여 가능 상태라고 판단할 수 있다.

따라서 다시 한번 IF절로 감싸서, SUM절이 0일 경우 '대여 가능'으로 표시하고,

대여중인 자동차가 있어 SUM절이 0이 아닐 경우에는 '대여중'으로 표시하여 해결할 수 있다.

( + IF문 안에서 숫자 비교 할 시, == 가 아닌, '=' 을 사용하면  된다)

---

[4] 서브쿼리, LIMIT

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

# USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요.
# 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요.
# 기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요.
# 조회수가 가장 높은 게시물은 하나만 존재합니다.
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 WHERE VIEWS=(SELECT MAX(VIEWS) FROM USED_GOODS_BOARD))
    ORDER BY FILE_ID DESC

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

LIMIT n : n개의 데이터만 가져옴

LIMIT s, n : (s+1)번째 데이터부터 n개의 데이터 가져옴 (offset(첫번째 파라미터)은 0부터 카운팅한다)

ex. LIMIT 10 : 10개의 데이터 가져옴

ex. LIMIT 10, 5 : 11번째 데이터부터 5개 가져옴

ex. LIMIT 0, 3 : 1~3번째 데이터 가져옴 (3개)

---

[5] GROUP BY 2개 이상, MONTH, DATE_FORMAT 함수, IN절

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

# CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서
# 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요.
# 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요.
# 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.
SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(CAR_ID) AS RECORDS FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE CAR_ID IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                    WHERE DATE_FORMAT(START_DATE, "%Y-%m") BETWEEN '2022-08' AND '2022-10' GROUP BY CAR_ID HAVING COUNT(CAR_ID)>=5)
    AND DATE_FORMAT(START_DATE, "%Y-%m") BETWEEN '2022-08' AND '2022-10'
    GROUP BY MONTH(START_DATE), CAR_ID
    HAVING COUNT(CAR_ID)>0
    ORDER BY MONTH ASC, CAR_ID DESC

---

리턴값 : int형

MONTH(날짜) : 해당 날짜가 몇 월인지 리턴. 1~12 사이.

YEAR(날짜) : 년도 리턴. 1000~9999 사이.

HOUR(시간) : 시간 리턴. 0~23 사이.

MINUTE(시간) : 분 리턴. 0~59 사이.

SECOND(시간) : 초 리턴. 0~59 사이.

---

DATE_FORMAT(날짜, format) : format에 설정한 형식대로 날짜를 String으로 만들어 반환

[ format 예시 ]
%Y : 4자리 년도
%y : 2자리 년도
%m : 월(01..12)
%c : 월(1..12)
%d : 일(00..31)
%e : 일(0..31)

ex. '2023-05-21' 처럼 표현하고 싶다면 -> DATE_FORMAT(날짜, "%Y-%m-%d")

---

DATE_FORMAT(START_DATE, "%c") 와 MONTH(START_DATE)의 차이

위의 문제에서 8, 9, 10을 오름차순으로 정렬해야 한다.

하지만

ORDER BY MONTH(START_DATE) ASC -> 8. 9. 10으로 정렬되지만

ORDER BY DATE_FORMAT(START_DATE, "%c") ASC -> 10. 8. 9 로 정렬된다 ..

10. 8. 9는 오름차순도 내림차순도 아니다. 얼핏 보면 "%c"가 월을 1, 5, 12와 같은 형식으로 반환하기 때문에 똑같아 보여서 이 부분에서 실수하여 문제를 틀리는 경우가 있다.

 

결론부터 말하면 리턴 타입의 차이로 인해 이러한 결과가 발생한다. 

MONTH() 함수는 리턴 타입이 int이기 때문에, 8 9 10을 오름차순 정렬하였을 경우 원하는대로 8 9 10이 나온다.

하지만 DATE_FORMAT()은 입력한 날짜를 사용자가 입력한 format에 맞춰 String으로 반환한다.

"8", "9", "10"의 문자열 정렬인 경우, 맨 처음 숫자가 1인 "10"이 가장 먼저 정렬되고, 나머지 8 9 가 정렬되어 10 8 9로 정렬된다.

---

[*] MySQL 문법 정리되어 있는 유용한 글

https://www.yalco.kr/lectures/sql/

MySQL

https://velog.io/@max-sum/MySQL-%EB%AC%B8%EB%B2%952.-%ED%95%A8%EC%88%98-%EA%B7%B8%EB%A3%B9

MySQL | 문법_2. 숫자와 문자열 관련 함수

https://extbrain.tistory.com/50

[MySQL] 연산자 (Operator)

https://redcow77.tistory.com/260

[Mysql] Mysql 조건문 - IF 문, CASE 문

https://velog.io/@12aeun/SQL-mysql%EC%97%90%EC%84%9C-%EB%82%A0%EC%A7%9C-%EC%8B%9C%EA%B0%84-%EA%B3%84%EC%82%B0%ED%95%98%EA%B8%B0

[SQL] mysql 날짜 관련 함수 정리

https://codingspooning.tistory.com/entry/MySQL-%EB%AC%B8%EC%9E%90%EC%97%B4-%EC%9E%90%EB%A5%B4%EA%B8%B0-SUBSTR-SUBSTRING

[MySQL] 문자열 자르기 (SUBSTR, SUBSTRING)

https://jang8584.tistory.com/7

[mysql]날짜 관련 함수 모음