MySQL | 일반 함수와 집계 함수

함수

일반 함수는 필요할 때 찾아봐도 되지만, 집계 함수는 기본적인 사용 방법을 익혀두는게 좋다

일반 함수

문자

• CHAR_LENGTH(str) : 문자의 길이 반환
• LENGTH(str) : 문자의 바이트 크기 반환
• CONCAT(str1, str2, ...) : 인자로 들어온 문자열을 연결
• CONCAT_WS(seperator, str1, str2, ...) : 문자열을 연결하나, 구분자를 붙임
  • ex. SELECT CONCAT_WS('-', 'hello', 'world') : 결과 : ‘hello-world’
• LEFT(str, length) : 왼쪽부터 length만큼 문자열을 자름
• RIGHT(str, length) : 오른쪽부터 length만큼 문자열을 자름
• SUBSTR(str, position, length)
  • 시작 위치부터 length만큼 문자열을 자름
  • 문자열의 위치는 1부터 시작이다
• SUBSTRING_INDEX(length, seperator, index) : index번째 seperator를 기준으로 문자열을 분리
  • index가 양수라면 기준점 이후를 제거, 음수라면 기준점 이전을 제거
  • ex. SELECT SUBSTRING_INDEX('hello world mysql', ' ', 1) : 결과 : hello
  • ex. SELECT SUBSTRING_INDEX('hello world mysql', ' ', 2) : 결과 : hello world
  • ex. SELECT SUBSTRING_INDEX('hello world mysql', ' ', -2) : 결과 : mysql
• LPAD(str, length, padding)
• RPAD(str, length, padding)
  • length에서 str을 채우고 남은 길이만큼 padding으로 채움
  • ex. SELECT LPAD('12', 4, '0') : 결과 : 0012
  • ex. SELECT RPAD('12', 4, '0') : 결과 : 1200
• LTRIM(str) : 왼쪽 공백 문자 제거
• RTRIM(str) : 오른쪽 공백 문자 제거
• TRIM(str) : 양쪽 공백 문자 제거
• TRIM(substr FROM str)
  • str에서 양쪽에서 substr을 제거
  • 인자 맨 앞에 키워드를 추가해 삭제 방향을 지정할 수 있다
    • BOTH : 양쪽 방향에서 제거 (기본값)
    • LEADING : 왼쪽 방향만 제거
    • TRAILING : 오른쪽 방향만 제거
  • ex. SELECT TRIM('^' FROM '^hello^') : 결과 : hello
  • ex. SELECT TRIM(LEADING '^' FROM '^hello^') : 결과 : hello^
• FIELD
• FIND_IN_SET
• INSTR
• LOCATE
• ELT
• REPEAT
• REPLACE
• REVERSE

숫자

• CEILING(n), FLOOR(n) : 올림, 내림
• ROUND(n, k), TRUNCATE(n, k) : 지정한 자릿수에서 반올림, 버림을 수행
  • ex. SELECT ROUND(1.45, 0) : 결과 : 1
  • ex. SELECT ROUND(1.45, 1) : 결과 : 1.5
• ABS(n) : 절댓값
• SIGN(n) : 부호 반환
  • 양수라면 1, 음수라면 -1, 0이라면 0 반환
• MOD(n, k) : 나머지 연산 (n % k)
• POWER(n, k) : 지수 연산 ($n^k$)
• SQRT(n) : 제곱근 연산
• RAND(seed)
  • 0과 1 사이 임의의 실수 반환
  • seed 값을 안넣으면 매번 임의의 값 반환
  • 동일한 seed 값에 대해 동일한 값 반환

날짜/시간

현재 날짜/시간

• NOW(), SYSDATE() : 시스템의 현재 날짜와 시간 반환
• CURDATE() : 시스템의 현재 날짜 반환
• CURTIME() : 시스템의 현재 시간 반환

날짜/시간 중 특정 값

• YEAR(dt), MONTH(dt), DAY(dt), HOUR(dt), MINUTE(dt), SECOND(dt) : 인자로 받은 날짜/시간 중 해당하는 값 반환
• QUARTER(dt) : 분기 반환

기간 차

• DATEDIFF()
• TIMESTAMPDIFF()

기간 반영

• ADDDATE(dt, interval)
• SUBDATE(dt, interval)

날짜 변환

• LAST_DAY()
• DAYOFYEAR()
• MONTHNAME()

그외

• CAST(value AS type), CONVERT(value, type) : 데이터타입 변환

• IF(exp, val1, val2) : exp 값이 참이면 val1 반환, 거짓이면 val2 반환
• IFNULL(exp1, exp2) : exp1 이 NULL이 아니면 exp1 반환, 그렇지 않다면 exp2 반환
• NULLIF(exp1, exp2) : exp1와 exp2의 값이 동일하면 NULL 반환, 그렇지 않다면 exp2 반환

CASE문

• 프로그래밍 언어의 case문과 유사
• CASE WHEN 조건1 THEN 값1 WHEN 조건2 THEN 값2... ELSE 값 END 의 형태로 작성
  • CASE로 시작하고 END로 끝남
  • WHEN 다음에 오는 조건이 만족시 THEN 다음에 오는 값이 반환됨
  • 모든 조건이 불일치하면 ELSE 다음에 오는 값이 반환됨

집계 함수

주요 집계함수

집계 함수의 인자는 컬럼이나 수식이 들어간다

• COUNT() : 전체 행 개수
• SUM() : 합
• AVG() : 평균
• MAX() : 최댓값
• MIN() : 최소값
• STDDEV() : 표준편차

예시1

시험 테이블에 컬럼(이름, 점수)이 있을 때, 총 점수의 합, 최댓값, 최솟값, 평균을 구하기

SELECT 
    SUM(점수) AS 합,
    MAX(점수) AS 최대,
    MIN(점수) AS 최소,
    AVG(점수) AS 평균,
    STDDEV(점수),
FROM 시험

GROUP BY

• 칼럼을 그룹으로 묶어 집계 함수를 사용하기 위해 사용한다
• 그룹에 묶인 컬럼은 조회시 한 행만 표시된다
• 그룹에 묶이지 않은 컬럼은 바로 조회할 수 없으며 집계함수 인자로만 넣을 수 있다

예시1

시험 테이블에 칼럼(이름, 반, 점수)가 있을 때, 각 반의 수와 점수의 평균 구하기

SELECT 반, COUNT(*), SUM(점수)  
FROM 시험
GROUP BY 반;

HAVING

• GROUP BY 의 결과에 대해 추가 조건을 걸기 위해 사용한다
• HAVING 내에서도 집계 함수를 사용할 수 있다

WITH ROLLUP

GROUP_CONCAT