[MySQL] 기본 SQL 문법 총정리 & 모음

 

✔️ 주의
 - SELECT 절에서 생성한 별칭을 Where 절에서 사용할 수 없음
- Partition과 같은 윈도우 함수는 Where 절에서 사용할 수 없음 
- 서브쿼리 만들고, 별칭 안 사용해도 만들어야 함 

 

⭐️ SQL 쿼리 순서
1. SELECT
2. FROM
3. JOIN
4. WHERE
5. GROUP BY
6. HAVING
7. ORDER BY
8. LIMIT

 

 

📍 NULL 처리 관련 함수 

NULL 처리 

: 컬럼의 값이 NULL인 경우 처리해주는 함수 
데이터베이스 쿼리에서 누락되거나 정의되지 않은 값을 처리하는 데 활용 

• NULL 값 확인
  • `IS NULL` : 값이 NULL인지 확인 
  • `IS NOT NULL` : 값이 NULL이 아닌지 확인 
  • `ISNULL(expr)` : NULL이면 1 반환, 아니면 0을 반환 
• NULL값 대체
  • `IFNULL` : 컬럼 값이 NULL을 반환할 때 다른 값으로 출력할 수 있도록 하는 함수
    • ex) SELECT IFNULL(칼럼명, 'null일 경우 대체 값') FROM 테이블명;
  • `COALESCE` : 지정한 표현식들 중에 NULL이 아닌 첫 번째 값을 반환
    • ex) SELECT COALESCE(칼럼명1, 칼럼명2, 칼럼명3, 0) : 이런식으로 순서대로 NULL이 아닌거 반환
  • `NULLIF`
    • NULL(expr1, expr2) : 두 표현식이 같으면 NULL 반환, 다르면 expr1 반환 
    • 주로 두 값이 같을 때 NULL 처리를 위해 사용
    • ex)SELECT product_id, NULLIF(discount, price) AS adjusted_discount FROM products;
  • `CASE` 구문 사용
    • case 구문으로 NULL 값을 원하는 방식으로 처리 가능 
    • ex) CASE WHEN salary IS NULL THEN ~~~

 

 

📍 조건 연산자 

BETWEEN 

: WHERE 절 내 특정 값이 주어진 범위 내에 포함되는지 확인할 때 사용
숫자, 날짜, 시간, 문자열 등 다양한 데이터 타입에서 사용 가능 

• ex) WHERE age BETWEEN 20 AND 29;
• 왜 사용? : WHERE ~ AND 20<= AGE <= 29; 이런 식으로 기호로는 연속적인 조건 비교가 안됨 

 

IN

: 특정 값이 주어진 값 목록 안에 있는지 확인하는 데 사용

• SELECT 칼럼명 `IN` (val1, val2, val3, ..., val N)

 

* 활용 : 아래처럼 괄호를 사용해서 두가지 칼럼에 대해 조건을 설정할 수도 있음 

SELECT * FOOD_TYPE
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

 

IF 

: 조건을 평가하고, 해당 조건이 참인지 거짓인지에 따라 다른 값을 반환

• IF(condition, true_value, false_value) : condition이 true 면 2번째 값, false면 3번째 값
• ex) SELECT IF(1 > 0, 'Yes', 'No') AS result;
  • IF(salary > 5000, 'High', 'Low')

SELECT
    ANIMAL_ID
    , NAME
    , IF(SEX_UPON_INTAKE LIKE 'Neutered%' 
         OR SEX_UPON_INTAKE LIKE 'Spayed%', 'O', 'X') AS 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

 

📍 집계 & 윈도우 함수 

MAX, MIN, ABS, AVG, SUM

* 최댓값에 해당하는 수가 1개로 보장될 때 
SELECT MAX(DATETIME) AS 시간
FROM ANIMAL_INS

* 최댓값에 해당하는 수가 여러개 있을 수 있을 때 
SELECT DATETIME AS 시간
FROM ANIMAL_INS
WHERE DATETIME = (SELECT MAX(DATETIME)
                  FROM ANIMAL_INS);

* MAX 함수 활용 예제 (좋으니까 참고)
SELECT
    CAR_ID 
    , CASE
        WHEN MAX('2022-10-16' BETWEEN START_DATE AND END_DATE) = 1 THEN '대여중'
        ELSE '대여 가능'
        END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

group by를 수행하면 car_id 끼리 묶이게 되는데 이때 when의 max 절 안에 조건을 통해 아래처럼 출력
- 참이면 1 
- 거짓이면 0
따라서 max 함수에 의해 대여중이 한번이라도 있었으면 1을 출력하게 되며
그것이 =1 에 조건에 맞아 then '대여중'으로 나오게 되는 것

 

COUNT

: 행의 개수를 계산하는 데 사용

• `COUNT(*)` : 테이블의 모든 행을 계산 (NULL의 여부에 상관없이 모든 행을 포함해서 계산)
• `COUNT(칼럼명)` : 해당 열에 NULL 값이 있는 경우 제외 
• `COUNT(DISTINCT 칼럼명)` : 특정 열의 고유값 개수를 계산, 중복된 값은 제외하고 계산 

* 열의 NULL 유뮤를 고려해서 COUNT(*)와 COUNT(칼럼명) 중 적절하게 고려해야 한다 

 

OVER

: 윈도우 함수의 동작 범위를 지정 
- 윈도우 함수를 사용하기 위해 필요함 구문

 

* 참고하기 좋은 쿼리 예제 : Group by 무시하고 전체 집계하기 

* 각 직원이 담당한 매출의 총합을 전체 매출 대비 얼마인지 비율 계산 
SELECT 
    E.EmployeeId
    , SUM(I.Total) / SUM(SUM(I.Total)) OVER() AS TOTAL_SALES
FROM 
    Invoice AS I
    JOIN Customer AS C
    ON I.CustomerId = C.CustomerId
    JOIN Employee AS E
    ON C.SupportRepId = E.EmployeeId
GROUP BY E.EmployeeId;

 

• GROUP BY 에 의해 직원 아이디를 기준으로 집계 되지만, `SUM(~) OVER()`를 수행하면 전체 합계를 구할 수 있음
• 주의 ) SUM(I.Total) OVER () ~ 이렇게 수행하면 오류남 -> `SUM(SUM(~)) OVER()` 이렇게 해야 함 

 

PARTITON BY

: 윈도우 함수의 일부로, 데이터를 특정 기준으로 나누고 각 파티션 내에서 계산을 수행

* PARTITION BY는 WHERE절에 사용할 수 없음 

💡 PARTITION BY와 GROUP BY의 차이
- `GROUP BY` : 데이터를 그룹화하고, 그룹당 하나의 결과를 반환
- `PARTITION BY` : 데이터를 파티션으로 나누지만, 모든 행을 유지하며 추가 계산을 수행 

 

* 좋은 퀴리 예시 코드 모음

-- 예제. 음식 종류별로 가장 많은 즐겨찾기를 받은 음식점을 조회하는 쿼리
SELECT
    FOOD_TYPE
    , REST_ID
    , REST_NAME
    , FAVORITES
FROM (
    SELECT *
        , MAX(FAVORITES) OVER (PARTITION BY FOOD_TYPE) AS MAX_FAV
    FROM REST_INFO
    ) AS M
WHERE FAVORITES = MAX_FAV
ORDER BY FOOD_TYPE DESC

* GROUP BY가 있는 쿼리의 경우 

-- 예제 1. 각 연도별로 트랙별 판매량과 총 매출 중에 가장 많이 판매된 트랙
SELECT *
FROM (
	SELECT 
		YEAR(InvoiceDate) AS year
		, TrackId AS track_id
		, COUNT(Quantity) AS sales_cnt
		, SUM(Quantity * UnitPrice) AS total_sales
		, MAX(COUNT(Quantity)) OVER (PARTITION BY YEAR(InvoiceDate)) AS max_cnt
	FROM data
	GROUP BY 1, 2
    ) AS z
WHERE sales_cnt = max_cnt;

-- 예제 2. 각 직원별 고객의 총매출 중에 최대 매출을 기록한 고객 
SELECT *
FROM (
	SELECT 
		SupportRepId
        	, CustomerId AS cust_id
		, SUM(Total) AS sales_sum
		, MAX(SUM(Total)) OVER (PARTITION BY SupportRepId) AS sales_max
	FROM data
	GROUP BY CustomerId
	) AS sub
WHERE sales_sum = sales_max

 

RANK

: 순위 계산 관련 함수 

• `ROW_NUMBER()`: 고유 순위 계산
• `RANK()`: 순위를 부여하며 중복 순위를 허용(다음 순위를 건너뜀)
• `DENSE_RANK()`: 중복 순위를 허용하되 다음 순위를 건너뛰지 않음
• `PERCENT_RANK()`: 특정 행의 순위를 백분율 (%)로 계산 * 0.0~1.0 값 반환 

 

* 참고하기 좋은 예제 

* 퍼센트로 등급 분류하기 
SELECT 
    ID
    , (CASE 
        WHEN PER_RANK <= 0.25 THEN 'CRITICAL'
        WHEN PER_RANK <= 0.5 THEN 'HIGH'
        WHEN PER_RANK <= 0.75 THEN 'MEDIUM'
        ELSE 'LOW'
      END) AS COLONY_NAME
FROM (SELECT 
        ID, 
        PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS PER_RANK
     FROM ECOLI_DATA
     ) AS D
ORDER BY ID

SELECT *
FROM (
	SELECT 
		YEAR(InvoiceDate) AS year
		, t.TrackId AS track_id
		, t.Name AS track_name
		, art.Name AS artist_name
		, COUNT(il.Quantity) AS sales_cnt
		, SUM(il.Quantity * il.UnitPrice) AS total_sales
		, MAX(COUNT(il.Quantity)) OVER (PARTITION BY YEAR(InvoiceDate)) AS max_cnt
	FROM data
	GROUP BY 1, 2
    ) AS z
WHERE sales_cnt = max_cnt;

 

LAG / LEAD

: 이전 / 다음 행의 값을 가져오는 윈도우 함수 
- 현재 행을 기준으로 특정 열의 이전 / 다음 값을 참조할 수 있도록 함 

• `LAG(칼럼명, offset, 기본값)` 
  • offset : 몇 번째 이전의 값을 가져올지 (default: 1)
  • 기본값 : 이전 값이 없을 때 반환할 기본 값 (default: NULL)
• 활용 : LAG(칼럼명, offset, 기본값) OVER (PARTITION BY 칼럼명 ORDER BY 칼럼명)

 

* 기본 예제 : 증감율 구하기

SELECT (현재매출 - 과거매출) / 과거매출 * 100
FROM (SELECT 
	*
        , LAG(현재매출, 1) OVER (ORDER BY 날짜열) AS 과거매출
	FROM data
    ) AS sub

 

 

* 어려운 활용 예제 

WITH genre_sale AS (
SELECT 
    g.GenreId
    , YEAR(InvoiceDate) AS year
    , SUM(il.UnitPrice * il.Quantity) AS cur_sales
    , LAG(SUM(il.UnitPrice * il.Quantity)) OVER (PARTITION BY g.GenreId ORDER BY YEAR(InvoiceDate)) AS pre_sales
FROM data
GROUP BY 1, 2
)

SELECT
    GenreId
    , year
    , cur_sales
    , ROUND((cur_sales - pre_sales) / pre_sales * 100, 2) AS growth_rate
FROM genre_sale;

 

 

📍 숫자 처리 관련 함수 

TRUNCATE()

: 숫자를 버리를 자릿수 아래로 버림 
* 버림이란 반올림하지 안호, 지정한 자리수 이후의 값을 단순히 제거하는 것을 의미 != ROUND()

• SELECT TRUNCATE(12345.1234567 ,1)  >>  12345.1
• SELECT TRUNCATE(12345.1234567 ,4)  >>  12345.1234
• SELECT TRUNCATE(12345.1234567 ,-1)  >>  12340
• SELECT TRUNCATE(12345.1234567 ,-2)   >>  12300 

* 정수 자리를 버릴 때는 - 기호 활용 

• `ROUND(숫자, 자릿수)`
  • SELECT TRUNCATE(123.567, 2) >> 123.57

 

 

📍 문자열 처리 관련 함수 

LIKE

: 문자열 패턴을 검색, 비교할 때 사용하는 연산자 
주로 검색 조건에서 특정 패턴과 일치하는 값을 찾기 위해 사용됨

• `%` 활용법
  • '손'으로 시작하는 4글자 데이터 조회 : ~ LIKE '손___'
  • '손'으로 시작하는 모든 데이터 조회 : ~ LIKE '손%'
  • '블'이 들어가는 모든 데이터 조회 : ~ LIKE '%손%'
• `_` 활용법
  • %와 같지만, 정확히 1개의 문자를 대체하고 싶을 때 사용

 

REGEXP

: 정규 표현식을 활용해 문자열 패턴을 검색하는 데 활용
기존 연산자보다 복잡한 문자열 조건 가능 

기호	설명	예시
.	임의의 한 문자와 일치	'a.b'	acb, a_b
^	문자열의 시작을 의미	'^hello'	hello world, hello hi
$	문자열의 끝을 의미	'world$'	hello word, hi word
*	앞의 문자가 0번 이상 반복됨	'ab*c'	ac, abc, abbc, abbbc
+	앞의 문자가 1번 이상 반복됨	'ab+c'	abc, abbc, ac (x)
?	앞의 문자가 0번 또는 1번 나타남	'colou?r'	color, colour
[abc]	'a', 'b', 'c' 중 하나와 일치	'[abc]'	apple, banna, cherry
[a-z]	소문자 알파벳 중 하나와 일치
[A-Z]	대문자 알파벳 중 하나와 일치
[0-9]	숫자 중 하나와 일치
|	OR 연산자 역할	'cat|dog'	i have a cat, dog is good

* 자주 사용하는 방식 
SELECT CAR_TYPE
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS REGEXP '열선시트|가죽시트|통풍시트'

 

SUBSTR

: 문자열에서 특정 부분을 추출하는 사용

• `SUBSTR(str, 시작 위치, 길이)`
  • SELECT SUBSTR('Hello World', 1, 5) = Hello
  • SELECT SUBSTR('Hello World',7 ) = 7번 위치부터 끝까지 
• `SUBSTR(str, - 시작 위치, 길이)`
  • 음수 사용 : 뒤에서부터 문자열 자르기 
  • SELECT SUBSTR('Hello World', -5, 3) = wor

 

SUBSTRING_INDEX

: 구분자를 기준으로 문자열을 분리하여 특정 부분을 추출하는 함수 

• `SUBSTRING_INDEX(str, 구분자, 개수)`
• SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', 2) = apple,banana (앞에서 2개 추출)
• SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', -1) =  cherry (마지막 추출) 

 

CONCAT

: 두 개 이상의 문자열을 하나로 연결 (합치기) 하는 데 사용

• `CONCAT`
  • 여러 문자열을 단순히 붙여서 사용
  • 위의 ex) CONCAT(CITY, '-', STREET_ADDRESS1, '-', STREET_ADDRESS2)

• `CONCAT_WS`
  
  • 구분자를 지정하여 여러 문자열을 결합
  • 위의 ex) CONCAT_WS(' ', CITY, STREET_ADDRESS1, STREET_ADDRESS2)
• 하나라도 NULL 값이 포함되면 결과는 NULL이 반환됨 <- `IFNULL` 이나 `COALESCE` 활용해서 처리

* 3개 이상의 문자열을 결합할 때는 concat_ws가 더 간편하다.

 

 

📍 날짜 관련 함수 

DATE_FORMAT

: 날짜와 시간을 지정된 형식으로 변환하는 데 사용 

• 일반적으로 가장 많이 사용하는 형식 : `DATE_FORMAT(날짜 컬럼, '%Y-%m-%d')`
• 특정 연도의 행만 ~~ 이렇게 있을 때 아래 3가지 방식 모두 활용 가능 (date ex: 2021-02-01)
  • WHERE date `LIKE` '2021%'
  • `DATE_FORMAT`(date, '%Y') = 2021
  • `YEAR`(date)

포맷 코드	설명	예시	결과
%Y	4자리 연도	'2025-01-02 15:30:45'	2025
%y	2자리 연도	'2025-01-02 15:30:45'	25
%M	월 이름 (전체)	'2025-01-02 15:30:45'	January
%m	월 (2자리 숫자)	'2025-01-02 15:30:45'	01
%b	월 이름 (축약형)	'2025-01-02 15:30:45'	Jan
%d	일 (2자리 숫자)	'2025-01-02 15:30:45'	02
%e	일 (숫자, 앞에 0 없음)	'2025-01-02 15:30:45'	2
%W	요일 이름 (전체)	'2025-01-02 15:30:45'	Thursday
%a	요일 이름 (축약형)	'2025-01-02 15:30:45'	Thu
%H	시간 (24시간, 2자리)	'2025-01-02 15:30:45'	15
%h	시간 (12시간, 2자리)	'2025-01-02 15:30:45'	03
%i	분 (2자리 숫자)	'2025-01-02 15:30:45'	30
%s	초 (2자리 숫자)	'2025-01-02 15:30:45'	45
%p	오전 / 오후 (AM / PM)	'2025-01-02 15:30:45'	PM
%%	% 문자 출력	'2025-01-02 15:30:45'	%

 

 

추가 함수 

: DATE_FORMAT을 쓰지 않고, 날짜 데이터를 특정 요소로 바로 변환할 수 있는 함수 

함수	설명	예시	결과
YEAR(date)	연도	YEAR('2025-01-02')	2025
MONTH(date)	월	MONTH('2025-01-02')	1
DAY(date)	일	DAY('2025-01-02')	2
HOUR(time)	시간	HOUR('15:30:00')	15
MINUTE(time)	분	MINUTE('15:30:00')	30
SECOND(time)	초	SECOND('15:30:45')	45
DAYNAME(date)	요일 이름	DAYNAME('2025-01-02')	Thursday
MONTHNAME(date)	월 이름	MONTHNAME('2025-01-02')	January
QUARTER(date)	분기 (1~4)	QUARTER('2025-04-15')	2
WEEK(date)	주 번호	WEEK('2025-01-02')	1
DAYOFYEAR(date)	연도 기준 몇 번째 날인지	DAYOFYEAR('2025-01-02')	2
TO_DAYS(date)	날짜일 일 (day)로 변환	TO_DAYS('2025-01-02')	738850
TIME_TO_SEC(time)	시간을 초(second)로 변환	TIME_TO_SEC('01:02:03')	3723
SEC_TO_TIME(sec)	초를 시간 형식으로 변환	SEC_TO_TIME(3723)	01:02:03

 

날짜 활용 함수

: 자주 쓰이는 날짜 활용 함수 정리 

함수	설명	예시	결과
DATEDIFF	두 날짜 간의 차이 반환	DATEDIFF('2023-12-31', '2023-01-01')	364
TIMESTAMPDIFF	지정된 단위의 차이 반환	TIMESTAMPDIFF(DAY, '2023-01-01', '2023-12-31'); TIMESTAMPDIFF(MONTH, '2023-01-01', '2023-12-31');	364; 11;
CURDATE	현재 날짜 반환 (시간 제외)	SELECT CURDATE(); SELECT CURRENT_DATE();	2025-01-02

 

날짜 비교 연산

: 기호를 사용해서 두 날짜를 비교할 수 있음 

• 큰 값일수록 더 나중의 시간이다 !

SELECT
    I.ANIMAL_ID
    , I.NAME
FROM ANIMAL_INS AS I
    JOIN ANIMAL_OUTS AS O
    ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.DATETIME > O.DATETIME -- 보호시작일 > 입양일 (보호 시작일보다 입양일이 더 빠름)
ORDER BY I.DATETIME