[MySQL] SQL 기본 문법 정리

SQL

필자는 소마와 기타 등등의 코딩테스트를 위해서 해당 글을 작성하기로 마음 먹었습니다. 

부족한 글이지만 미약하게나마 도움이 되기를..

 

기본 구조

SELECT 컬럼1, 컬럼2, ...
FROM 테이블명
[WHERE 조건]
[GROUP BY 컬럼]
[HAVING 조건]
[ORDER BY 컬럼 [ASC|DESC]]
[LIMIT 갯수];

 

 

예제

SELECT name, age
FROM users
WHERE age > 18
ORDER BY age DESC
LIMIT 10;

 

이 문법의 의미는 users 테이블에서 name, age 컬럼을 출력하되, age가 18살 보다 많은 사람으로 추리고 추린 결과에 내림 차순을 적용한다는 것입니다.

 

SELECT 
    FLAVOR 
FROM
    FIRST_HALF
ORDER BY
    TOTAL_ORDER DESC, SHIPMENT_ID ASC

 

위의 문장의 의미는 TOTAL_ORDER을 기준으로 내림차순으로 정리하고 만약 값이 같을 경우, SHIPMENT_ID로 오름차순 정렬을 한다는 의미입니다.

WHERE 조건

 

주요 조건

• 비교 연산자: =, >, <, >=, <=, <> (같지 않음)
• 논리 연산자: AND, OR, NOT
• 패턴 매칭: LIKE, 와일드카드 %(0개 이상 문자), _(1개 문자)
• 범위 조건: BETWEEN ... AND ... , IN (...)

 

예제

SELECT *
FROM products
WHERE price BETWEEN 100 AND 500
  AND category IN ('Electronics', 'Books')
  AND name LIKE 'A%';

 

해석 -> 가격이 100 이상 500 이하이며 카테고리가 Books와 Electronics이고 이름이 A로 시작해야한다.

 

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

 

집계 함수와 GROUP BY

주요 집계 함수

• COUNT: 행 개수 계산.
• SUM: 합계.
• AVG: 평균.
• MAX, MIN: 최대값, 최소값.

 

GROUP BY

• 특정 컬럼을 기준으로 데이터를 그룹화하여 집계 연산 수행.

SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;

 

• GROUP BY: 부서별로 그룹화.
• HAVING: 그룹화된 결과에서 조건 지정.

 

SELECT 
    USER_ID, 
    PRODUCT_ID
FROM 
    ONLINE_SALE
GROUP BY 
    USER_ID, 
    PRODUCT_ID
HAVING 
    COUNT(*) > 1
ORDER BY 
    USER_ID ASC, 
    PRODUCT_ID DESC;

 

 

 

프로그래머스

 

나의 풀이

SELECT MCDP_CD,COUNT(*)
FROM
    APPOINTMENT
WHERE
    APNT_YMD LIKE "%-05-%"
GROUP BY 
    MCDP_CD
ORDER BY
    COUNT(*) ASC, MCDP_CD ASC

 

GPT 풀이

SELECT 
    MCDP_CD AS "진료과 코드", 
    COUNT(DISTINCT PT_NO) AS "5월예약건수"
FROM 
    APPOINTMENT
WHERE 
    TO_CHAR(APNT_YMD, 'YYYY-MM') = '2022-05'
GROUP BY 
    MCDP_CD
ORDER BY 
    "5월예약건수" ASC, 
    "진료과 코드" ASC;

 

알아야 할 것 ->  TO_CHAR라는 함수도 있다는 것입니다. + as로 바꿔준 컬럼을 ORDER BY에서 쓸 수 있다는 것.

JOIN: 여러 테이블 결합

INNER JOIN

• 두 테이블의 교집합만 조회.

SELECT u.name, o.order_id
FROM users u
JOIN orders o ON u.id = o.user_id;

 

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

프로그래머스

이 문제가 조인을 할 때의 ON이 어떤 역할인 지 확실하게 알려준다

SELECT 
    D.ID, 
    D.EMAIL, 
    D.FIRST_NAME, 
    D.LAST_NAME
FROM DEVELOPERS D
JOIN SKILLCODES S 
ON (D.SKILL_CODE & S.CODE) > 0
WHERE S.NAME IN ('Python', 'C#')
GROUP BY D.ID, D.EMAIL, D.FIRST_NAME, D.LAST_NAME
ORDER BY D.ID ASC;

 

LEFT JOIN

• 왼쪽 테이블의 모든 데이터와 조건에 맞는 오른쪽 데이터.

SELECT u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

 

left join은 과연 무엇일까?에 대해서 단순한 의문을 남기고 문제를 맞닥뜨리니 전혀 쓸 생각을 못 했습니다.

예시 문제를 첨부하겠습니다.

프로그래머스

 

join을 했을 때, 만약 한쪽의 데이터가 없다면 그 데이터를 NULL로 채워줘야 하는 경우도 있을 겁니다.

이때 left join을 사용해야 합니다.

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

 

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

프로그래머스

SELECT 
    H.HISTORY_ID,
    FLOOR(C.DAILY_FEE * (DATEDIFF(H.END_DATE,H.START_DATE)+1) * (1 - COALESCE(P.DISCOUNT_RATE,0) / 100)) AS FEE
FROM
    CAR_RENTAL_COMPANY_CAR C
JOIN 
    CAR_RENTAL_COMPANY_RENTAL_HISTORY H 
ON 
    C.CAR_ID = H.CAR_ID
LEFT JOIN
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN P 
ON
    C.CAR_TYPE = P.CAR_TYPE
AND (
    (DURATION_TYPE = '7일 이상' AND DATEDIFF(H.END_DATE,H.START_DATE)+1 BETWEEN 7 AND 29) OR
    (DURATION_TYPE = '30일 이상' AND DATEDIFF(H.END_DATE,H.START_DATE)+1 BETWEEN 30 AND 89) OR
    (DURATION_TYPE = '90일 이상' AND DATEDIFF(H.END_DATE,H.START_DATE)+1 >= 90)
)
WHERE 
    C.CAR_TYPE = '트럭'
ORDER BY FEE DESC, H.HISTORY_ID DESC;

 

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

프로그래머스

이 문제는 SELF JOIN을 활용하는 문제인 듯

 

SELECT
    E1.ID,
    COUNT(E2.ID) AS CHILD_COUNT
FROM
    ECOLI_DATA E1
LEFT JOIN 
    ECOLI_DATA E2 ON E1.ID = E2.PARENT_ID
GROUP BY
    E1.ID
ORDER BY
    E1.ID ASC

 

서브쿼리

SELECT 안의 서브쿼리

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

 

FROM 안의 서브쿼리

SELECT department, AVG(salary) AS avg_salary
FROM (SELECT * FROM employees WHERE age > 30) AS filtered_employees
GROUP BY department;

 

EXISTS 함수 <-> 서브쿼리와 자주 씀

이 함수는 서브쿼리에서 반환된 행이 있으면 TRUE를 행이 없으면 FALSE를 반환하는 함수임

SELECT column1, column2, ...
FROM table1
WHERE EXISTS (
    SELECT 1
    FROM table2
    WHERE 조건
);

 

포인트: SELECT 1 대신 SELECT *도 가능하지만, 행의 값은 중요하지 않고 존재만 확인하니 1을 자주 사용합니다.

 

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

프로그래머스

 

-- 코드를 입력하세요
# 자동차 종류가 '세단' 또는 'SUV' 인 자동차 
# 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 
# 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차

# 정렬 
# 대여 금액을 기준으로 내림차순 정렬하고
# 자동차 종류를 기준으로 오름차순 정렬
# 자동차 ID를 기준으로 내림차순
WITH POSSIBLE_CAR AS (
    SELECT 
        CAR_ID
    FROM CAR_RENTAL_COMPANY_CAR CC
    WHERE NOT EXISTS (
        SELECT 1
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY RH
        WHERE RH.CAR_ID = CC.CAR_ID
        AND RH.START_DATE <= '2022-11-30' AND RH.END_DATE >= '2022-11-01'
    )
)

SELECT
    CC.CAR_ID,
    CC.CAR_TYPE,
    FLOOR(30 * CC.DAILY_FEE * (1 - CP.DISCOUNT_RATE / 100)) AS FEE
FROM
    CAR_RENTAL_COMPANY_CAR CC
JOIN 
    POSSIBLE_CAR PC ON PC.CAR_ID = CC.CAR_ID
JOIN
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN CP ON CC.CAR_TYPE = CP.CAR_TYPE AND
    CP.DURATION_TYPE = '30일 이상'
WHERE 
    CC.CAR_TYPE IN ('세단','SUV') AND
    FLOOR(30 * CC.DAILY_FEE * (1 - CP.DISCOUNT_RATE / 100)) BETWEEN 500000 AND 1999999
ORDER BY
    FEE DESC, CC.CAR_TYPE ASC, CC.CAR_ID DESC

 

 

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

프로그래머스

SELECT ITEM_ID, ITEM_NAME, RARITY
FROM ITEM_INFO I
WHERE NOT EXISTS(
    SELECT 1
    FROM ITEM_TREE T
    WHERE I.ITEM_ID=T.PARENT_ITEM_ID)
ORDER BY ITEM_ID DESC;

 

문자열 함수

문자열 관련

• CONCAT: 문자열 결합.
• SUBSTRING: 부분 문자열 추출.
• LENGTH: 문자열 길이.
• TRIM: 앞뒤 공백 제거.

 

프로그래머스

SELECT 
    DR_NAME,DR_ID,MCDP_CD,SUBSTRING_INDEX(HIRE_YMD, ' ', 1)
FROM 
    DOCTOR
WHERE 
    MCDP_CD = "CS" OR MCDP_CD = "GS"
ORDER BY
    HIRE_YMD DESC;

SUBSTRING_INDEX(string, delimiter, count)

string: 잘라낼 대상 문자열.

delimiter: 문자열을 나눌 기준이 되는 구분자(예: 공백, 콤마, 슬래시 등).

count:
양수: 문자열의 왼쪽부터 구분자를 기준으로 count개의 부분 문자열을 반환.
음수: 문자열의 오른쪽부터 구분자를 기준으로 count개의 부분 문자열을 반환.

SELECT SUBSTRING_INDEX('apple,banana,cherry,grape', ',', 2) AS result;
-> result = apple,banana

SELECT SUBSTRING_INDEX('apple,banana,cherry,grape', ',', -2) AS result;
-> result = cherry,grape

 

 

 

WITH AS 문법

복잡한 쿼리문을 짤 때 무조건 써야하는 것 같습니다. 약간 서브쿼리와 비슷한 결입니다.

 

프로그래머스

WITH ReviewCount AS(
    SELECT 
        MEMBER_ID, COUNT(*) AS REVIEW_COUNT
    FROM 
        REST_REVIEW
    GROUP BY
        MEMBER_ID
),
MaxReview AS(
    SELECT 
        MEMBER_ID
    FROM 
        ReviewCount
    WHERE  
        REVIEW_COUNT = (SELECT MAX(REVIEW_COUNT) FROM ReviewCount)
)
SELECT MP.MEMBER_NAME, RR.REVIEW_TEXT, SUBSTRING_INDEX(RR.REVIEW_DATE, ' ', 1)
FROM 
    REST_REVIEW AS RR
JOIN 
    MaxReview AS MR
ON 
    RR.MEMBER_ID = MR.MEMBER_ID
JOIN
    MEMBER_PROFILE AS MP
ON
    MR.MEMBER_ID = MP.MEMBER_ID
ORDER BY
    RR.REVIEW_DATE ASC,
    RR.REVIEW_TEXT ASC;

 

임시로 새로운 테이블을 만들어서 그 테이블을 메인에서 사용하는 느낌입니다.

 

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

프로그래머스

이 문제를 두 방법으로 풀 수 있습니다.

WITH BUN AS(
    SELECT 
        CONCAT(EXTRACT(QUARTER FROM DIFFERENTIATION_DATE), 'Q') AS QUARTER
    FROM ECOLI_DATA
)

SELECT QUARTER,COUNT(*) AS ECOLI_COUNT
FROM
    BUN
GROUP BY QUARTER
ORDER BY QUARTER

 

SELECT 
    CONCAT(EXTRACT(QUARTER FROM DIFFERENTIATION_DATE), 'Q') AS QUARTER,
    COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER;