[MySQL] 주요 함수 및 연산자

프로그래머스 코딩테스트를 풀면서 몰랐거나 문제를 풀기 위해 검색해본 함수를 정리했다.

1. 조건 및 NULL 처리 함수

✅ IFNULL(expr1, expr2)

• 기능 : expr1이 NULL이면 expr2를 반환
• 사용 위치 : SELECT, WHERE, ORDER BY, HAVING 등 값이 들어가는 위치에서 사용 가능

SELECT IFNULL(score, 0) AS final_score FROM students;

2. 문자열 함수

✅ LOCATE(SUBSTR, STR)

• 기능 : STR 문자열 내에 SUBSTR이 처음 당장하는 위치를 반환 (1부터 시작)
• 사용 위치 : SELECT, WHERE, ORDER BY

SELECT LOCATE('world', 'Hello world'); -- 결과: 7

✅ GROUP_CONCAT(EXPR)

• 기능 : 그룹별 데이터를 하나의 문자열로 결합
• 사용 위치 : SELECT 절에서 GROUP BY와 함께 사용

SELECT department, GROUP_CONCAT(employee_name) AS members
FROM employees
GROUP BY department;

3. 날짜/시간 함수

✅ DATE_FORMAT(DATE, FORMAT)

• 기능 : 날짜를 지정한 포맷으로 문자열로 변환
• 사용 위치 : SELECT, WHERE, ORDER BY, GROUP BY

SELECT DATE_FORMAT(birthdate, '%Y-%m-%d') AS formatted_date FROM employees;
-- 출력 결과 : 2025-06-11

SELECT DATE_FORMAT(NOW(), '%H:%i:%s %p');
-- 출력 결과 : 15:30:45 PM

포맷 코드	의미	예시 출력 (2025-06-11 15:30:45 기준)
%Y	4자리 연도	2025
%y	2자리 연도	25
%m	2자리 월 (01~12)	06
%c	월 (숫자, 1~12)	6
%d	2자리 일 (01~31)	11
%e	일 (숫자, 1~31)	11
%H	24시간 형식의 시 (00~23)	15
%h / %I	12시간 형식의 시 (01~12)	03
%i	분 (00~59)	30
%s / %S	초 (00~59)	45
%p	AM 또는 PM	PM
%W	요일 (영어)	Wednesday
%a	요일 (짧은 영어)	Wed

 

✅ DATEDIFF(DATE1, DATE2)

• 기능 : DATE1 - DATE2 결과를 일 수로 반환
• 사용 위치 : SELECT, WHERE

SELECT DATEDIFF('2024-05-05', '2024-05-01'); -- 결과: 4

 

4. 숫자 처리 함수

✅ FLOOR(num) / CEIL(NUM)

• 기능 : FLOOR는 내림, CEIL는 올림
• 사용 위치 : SELECT, WHERE, ORDER BY

SELECT FLOOR(5.7), CEIL(5.2);

 

5. 윈도우 함수 (WINDOW FUNCTIONS)

윈도우 함수는 OVER() 절을 필수로 동반하며, 집계(GROUP BY) 없이도 행별 계산을 수행할 수 있는 함수이다.

OVER() 내부에는 정렬조건(ORDER BY), 파티션(PARTITION BY), 범위 제약(ROWS BETWEEN...)

✅ RANK(), PERCENT_RANK()

• 기능 : 행의 순위를 구하며, 동일값 존재시 동일 순위(순위 건너뜀)
• PERCENT_RANK()는 순위를 백분률로 변환
• 사용 위치 : SELECT 절에서 OVER(ORDER BY)와 함께 사용

SELECT name, score,
       RANK() OVER (ORDER BY score DESC) AS rank,
       PERCENT_RANK() OVER (ORDER BY score DESC) AS percent_rank
FROM exams;

 

✅ LEAD(EXPR, OFFSET, DEFAULT), LAG(EXPR, OFFSET, DEFAULT)

• 기능 : 현재 행을 기준으로 다음(LEAD) 또는 이전(LAG) 행의 값을 가져옴
• 기본값은 생략 가능 (NULL 반환)
• 사용 위치 : SELECT 절에서 OVER(ORDER BY)와 함께 사용

SELECT name,
       score,
       LEAD(score) OVER (ORDER BY exam_date) AS next_score,
       LAG(score) OVER (ORDER BY exam_date) AS prev_score
FROM results;

 

✅ 집계함수 OVER(...) + 범위 지정

• 기능 : 윈도우 함수의 범위 조정

키워드	의미
UNBOUNDED PRECEDING	첫 행부터 현재 행까지 포함
1 PRECEDING	현재 행 기준 앞의 한 행 포함
CURRENT ROW	현재 행만 포함
1 FOLLOWING	현재 행 기준 다음 한 행 포함
UNBOUNDED FOLLOWING	현재 행부터 끝 행까지 포함

 

ROWS BETWEEN과 RANGE BETWEEN의 차이

• ROWS BETWEEN : 정확한 행 수 기준 범위 지정
• RANGE BETWEEN : 값 기준으로 범위 지정 (동일 값 포함 가능, MySQL에서는 RANGE는 제한적 지원)

✅ 누적 합 (처음 ~ 현재 행)

SELECT
    NAME,
    SCORE,
    SUM(SCORE) OVER (ORDER BY EXAM_DATE
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUNNING TOTAL)
FROM RESULTS;

 

✅ 이동 평균

SELECT
    NAME,
    SCORE,
    AVG(SCORE) OVER (ORDER BY EXAM_DATE
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS MOVING_AVG
FROM RESULTS;

 

✅ 부분 합계(그룹별 총합)

SELECT
    DEPARTMENT,
    NAME,
    SUM(SALARY) OVER (PARTITION BY DEPARTMENT) AS DEPT_TOTAL_SALARY
FROM EMPLOYEES;

 

ROWS BETWEEN은 물리적 행 순서 기준이라 ORDER BY가 꼭 필요하다. (기본값은 RANGE BETWEEN)

 

6. 비트 연산자 및 진수 처리 함수

✅ CONV(EXPR, FROM_BASE, TO_BASE)

• 기능 : 진법 변환

SELECT CONV('1010', 2, 10); -- 2진수 → 10진수 변환 → 결과: 10

✅ BIN(NUMBER)

• 기능 : 10진수를 2진수 문자열로 변환

SELECT BIN(10); -- 결과: '1010'

 

✅ 비트 연산자 (Bitwise Operators)

비트 연산은 정수 값을 비트 단위로 조작한다.

연산자	기능	예시
&	대응되는 비트가 모두 1이면 1을 반환 (비트 AND 연산)	5 & 3 → 101 & 011 = 001 → 1
|	대응되는 비트 중 하나라도 1이면 1을 반환 (비트 OR 연산)	5 | 3 → 101 | 011 = 111 → 7
^	대응되는 비트가 서로 다르면 1을 반환 (비트 XOR 연산)	5 ^ 3 → 101 ^ 011 = 110 → 6
~	비트를 1이면 0으로, 0이면 1로 반전시킴 (비트 NOT 연산)	~5 → ~00000101 = 11111010
<<	비트를 왼쪽으로 지정한 수만큼 이동 (LEFT SHIFT 연산)	5 << 1 → 101 → 1010 → 10
>>	부호를 유지하며 비트를 오른쪽으로 이동 (RIGHT SHIFT 연산)	5 >> 1 → 101 → 10 → 2

 

✅ 실용 예시 : 유전자 정보 비교 (부분집합 관계 판단)

(E1.GENOTYPE & E2.GENOTYPE) = E1.GENOTYPE

• E1의 유전자 정보가 E2에 포함되는지(부분집합) 검사
• E1의 비트가 모두 E2에도 존재해야 AND 결과가 E1과 동일함
• 5 & 7 = 5 => 0101 & 0111 = 0101로, 5는 7의 부분집합임을 확인

 

7. 조인 관련 (JOIN)

✅ NATURAL JOIN

• 기능 : 공통 컬럼을 기준으로 자동 조인
• 제약사항 : 공통 컬럼명이 정확히 일치해야 하며, 수동 제어 불가

SELECT * FROM table1 NATURAL JOIN table2;

 

✅ SELF JOIN

• 기능 : 동일 테이블을 자기 자신과 조인
• 사용 목적 : 계층 구조, 상하관계 표현 시 사용

SELECT e1.name, e2.name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;