MySQL 내장 함수 53가지 정리 (숫자, 문자, 날짜, 논리, 집계)

MySQL을 사용할 때, 사용할 수 있는 내장 함수 53가지를 준비했다. 함수 종류가 많아서 하니씩 다 찾아보기 힘든 분들을 위해 카테고리 별로 나눠서 표로 정리했다. 숫자, 문자, 날짜, 논리, 집계 5가지 카테고리로 분류된다.

내용은 예시 SQL문, 실행 결과, 데이터를 가져왔을 때 사용하는 예문으로 구성된다. 각 함수에 대한 설명은 예시에 설명해 놓았다.

숫자 관련 함수

1. ABS(number)

-- 예시: 음수의 절대값 계산
SELECT ABS(-15); -- 결과: 15

-- 활용: 테이블의 특정 열에 있는 모든 값의 절대값 계산
SELECT ABS(column_name) FROM table_name;

2. CEILING(number)

-- 예시: 소수점 이하를 올림
SELECT CEILING(15.75); -- 결과: 16

-- 활용: 테이블의 특정 열에 있는 모든 값 올림
SELECT CEILING(column_name) FROM table_name;

3. FLOOR(number)

-- 예시: 소수점 이하를 내림
SELECT FLOOR(15.75) -- 결과: 15

-- 활용: 테이블의 특정 열에 있는 모든 값 내림
SELECT FLOOR(column_name) FROM table_name;

4. ROUND(number, decimals)

-- 예시: 소수점 첫째 자리에서 반올림
SELECT ROUND(15.789, 1); -- 결과: 15.8

 -- 활용: 테이블의 특정 열에 있는 모든 값 소수점 둘째 자리에서 반올림
SELECT ROUND(column_name, 2) FROM table_name;

5. TRUNCATE(number, decimals)

-- 예시: 소수점 첫째 자리에서 절삭
SELECT TRUNCATE(15.789, 1); -- 결과: 15.7

-- 활용: 테이블의 특정 열에 있는 모든 값 소수점 둘째 자리에서 절삭
SELECT TRUNCATE(column_name, 2) FROM table_name;

6. POW(base, exponent)

-- 예시: 2의 3제곱 계산
SELECT POW(2, 3); --결과: 8

-- 활용: 테이블의 특정 열에 있는 모든 값의 제곱 계산
SELECT POW(column_name, 2) FROM table_name;

7. MOD(number, divisor)

-- 예시: 10을 3으로 나눈 나머지 계산
SELECT MOD(10, 3); -- 결과: 1

-- 활용: 테이블의 특정 열에 있는 모든 값 4로 나눈 나머지 계산
SELECT MOD(column_name, 4) FROM table_name;

8. GREATEST(value1, value2, ...)

-- 예시: 주어진 값 중 가장 큰 값 찾기
SELECT GREATEST(1, 2, 3); -- 결과: 3

-- 활용: 테이블의 여러 열 중 각 행에서 가장 큰 값 찾기
SELECT GREATEST(column1, column2, column3) FROM table_name;

9. LEAST(value1, value2, ...)

-- 예시: 주어진 값 중 가장 작은 값 찾기
SELECT LEAST(1, 2, 3); -- 결과: 1

-- 활용: 테이블의 여러 열 중 각 행에서 가장 작은 값 찾기
SELECT LEAST(column1, column2, column3) FROM table_name;

10. INTERVAL(value, vlaue1, value2, ...)

value가 value1~value... 사이 중 몇 번째에 속하는지.

-- 예시: 값이 주어진 범위 내에서 어디에 속하는지 찾기
SELECT INTERVAL(23, 1, 15, 23, 30, 44, 200); -- 결과: 3

-- 활용: 테이블에서 특정 값이 주어진 범위 내에서 어디에 속하는지 찾기
SELECT INTERVAL(column_name, 1, 3, 7, 9) FROM table_name;

문자 관련 함수

11. ASCII(string)

-- 예시: 문자 'A'의 ASCII 값 반환
SELECT ASCII('A'); -- 결과: 65

-- 활용: 테이블의 특정 열에 있는 문자의 ASCII 값 반환
SELECT ASCII(column_name) FROM table_name;

12. CHAR(number1, number2, ...)

-- 예시: ASCII 값 65, 66, 67에 해당하는 문자 반환
SELECT CHAR(65, 66, 67); -- 결과: {"type":"Buffer","data":[65,66,67]}

-- 활용: ASCII 값 77과 테이블의 특정 열의 값에 해당하는 문자 반환
SELECT CHAR(77, column_name) FROM table_name;

13. CONCAT(string1, string2, ...)

-- 예시: 여러 문자열 연결
SELECT CONCAT('Hello', ' ', 'World'); -- 결과: Hello World

-- 활용: 테이블의 두 열을 연결
SELECT CONCAT(column1, ' ', column2) FROM table_name;

14. INSERT(string, position, length, new_string)

아래 예시 7, 5 숫자 의미 : 7번째 문자부터 5개를 지우고, 'MySQL'을 넣는다.

-- 예시: 문자열 중간에 다른 문자열 삽입
SELECT INSERT('Hello World', 7, 5, 'MySQL'); -- 결과: Hello MySQL

-- 활용: 테이블의 특정 열에 있는 문자열 중간에 'Test' 삽입
SELECT INSERT(column_name, 3, 4, 'Test') FROM table_name;

15. REPLACE(string, from_string, mew_string)

-- 예시: 문자열 내의 하위 문자열 교체
SELECT REPLACE('Hello World', 'World', 'MySQL'); -- 결과: Hello MySQL

-- 활용: 테이블의 특정 열에 있는 'old'를 'new'로 교체
SELECT REPLACE(column_name, 'old', 'new') FROM table_name;

16. INSTR(string, substring)

-- 예시: 하위 문자열 'World'의 시작 위치 찾기
SELECT INSTR('Hello World', 'World'); -- 결과: 7

-- 활용: 테이블의 특정 열에서 'pattern'이 시작되는 위치 찾기
SELECT INSTR(column_name, 'pattern') FROM table_name;

17. LEFT(string, length)

-- 예시: 문자열의 왼쪽부터 5번째 문자까지 반환
SELECT LEFT('Hello World', 5); -- 결과: Hello 

-- 활용: 테이블의 특정 열에서 왼쪽 3문자 반환
SELECT LEFT(column_name, 3) FROM table_name;

18. RIGHT(string, length)

-- 예시: 문자열의 오른쪽 부분 반환
SELECT RIGHT('Hello World', 5); --결과: World

-- 활용: 테이블의 특정 열에서 오른쪽 3문자 반환
SELECT RIGHT(column_name, 3) FROM table_name;

19. MID(string, start, length)

-- 예시: 문자열의 중간 부분 반환
SELECT MID('Hello World', 4, 4); -- 결과: lo W

-- 활용: 테이블의 특정 열에서 2번째 위치부터 5문자 반환
SELECT MID(column_name, 2, 5) FROM table_name;

20. SUBSTRING(string, start, lenth)

MID함수와 똑같은 기능을 하며, SUBSTR(string, start, lenth)도 가능하다.

-- 예시: 문자열의 특정 부분 반환
SELECT SUBSTRING('Hello World', 4, 4); -- 결과: lo W

-- 활용: 테이블의 특정 열에서 2번째 위치부터 5문자 반환
SELECT SUBSTRING(column_name, 2, 5) FROM table_name;

21. LTRIM(string)

-- 예시: 문자열 왼쪽 공백 제거
SELECT LTRIM('  Hello World  '); -- 결과: Hello World

-- 활용: 테이블의 특정 열에서 왼쪽 공백 제거
SELECT LTRIM(column_name) FROM table_name;

22. RTRIM(string)

-- 예시: 문자열 오른쪽 공백 제거
SELECT RTRIM('  Hello World  '); -- 결과:   Hello World

-- 활용: 테이블의 특정 열에서 오른쪽 공백 제거
SELECT RTRIM(column_name) FROM table_name;

23. TRIM(string)

-- 예시: 문자열 양쪽 공백 제거
SELECT TRIM('  Hello World  '); -- 결과: Hello World

-- 문제: 테이블의 특정 열에서 양쪽 공백 제거
SELECT TRIM(column_name) FROM table_name;

24. LOWER(string)

-- 예시: 문자열을 소문자로 변환
SELECT LOWER('HELLO WORLD'); -- 결과: hello world

-- 활용: 테이블의 특정 열을 소문자로 변환
SELECT LOWER(column_name) FROM table_name;

25. UPPER(string)

-- 예시: 문자열을 대문자로 변환
SELECT UPPER('hello world'); -- 결과: HELLO WORLD

-- 활용: 테이블의 특정 열을 대문자로 변환
SELECT UPPER(column_name) FROM table_name;

26. REVERSE(string)

-- 예시: 문자열 뒤집기
SELECT REVERSE('Hello World'); -- 결과: dlroW olleH

-- 활용: 테이블의 특정 열의 문자열 뒤집기
SELECT REVERSE(column_name) FROM table_name;

날짜 관련 함수

27. NOW()

-- 예시: 현재 날짜와 시간 반환
SELECT NOW(); -- 결과: 2023-11-22 16:32:26

28. CURDATE()

-- 예시: 현재 날짜 반환
SELECT CURDATE(); -- 결과: 2023-11-22 00:00:00

29. CURTIME()

-- 예시: 현재 시간 반환
SELECT CURTIME(); -- 결과: 16:35:03

30. DATE_ADD(date, INTERVAL expr unit)

-- 예시: 날짜에 일수 추가
SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY); -- 결과: 2023-01-02

-- 활용: 테이블의 특정 열 날짜에 2주 추가
SELECT DATE_ADD(column_name, INTERVAL 2 WEEK) FROM table_name;

31. DATE_SUB(date, INTERVAL expr unit)

-- 예시: 날짜에서 일수 빼기
SELECT DATE_SUB('2023-01-01', INTERVAL 1 DAY); -- 결과: 2022-12-31

-- 활용: 테이블의 특정 열 날짜에서 3개월 빼기
SELECT DATE_SUB(column_name, INTERVAL 3 MONTH) FROM table_name;

32. YEAR(date)

-- 예시: 날짜의 연도 반환
SELECT YEAR('2023-01-01'); -- 결과: 2023

-- 활용: 테이블의 특정 열 날짜의 연도 반환
SELECT YEAR(column_name) FROM table_name;

33. MONTH(date)

-- 예시: 날짜의 월 반환
SELECT MONTH('2023-01-02'); -- 결과: 1

-- 활용: 테이블의 특정 열 날짜의 월 반환
SELECT MONTH(column_name) FROM table_name;

34. DAY(date)

-- 예시: 날짜의 일 반환
SELECT DAY('2023-01-02'); -- 결과: 2

-- 활용: 테이블의 특정 열 날짜의 일 반환
SELECT DAY(column_name) FROM table_name;

35. MONTHNAME(date)

-- 예시: 날짜의 월 이름 반환
SELECT MONTHNAME('2023-01-01'); -- 결과: January

-- 활용: 테이블의 특정 열 날짜의 월 이름 반환
SELECT MONTHNAME(column_name) FROM table_name;

36. DAYNAME(date)

-- 예시: 날짜의 요일 이름 반환
SELECT DAYNAME('2023-01-01'); -- 결과: Sunday

-- 활용: 테이블의 특정 열 날짜의 요일 이름 반환
SELECT DAYNAME(column_name) FROM table_name;

37. DAYOFMONTH(date)

-- 예시: 날짜의 월별 일자 반환
SELECT DAYOFMONTH('2023-01-02'); -- 결과: 2

-- 활용: 테이블의 특정 열 날짜의 월별 일자 반환
SELECT DAYOFMONTH(column_name) FROM table_name;

38. DAYOFWEEK(date)

-- 예시: 날짜의 주별 일자 반환 (1=Sunday, 7=Saturday)
SELECT DAYOFWEEK('2023-01-01'); -- 결과: 1

-- 활용: 테이블의 특정 열 날짜의 주별 요일 번호 반환
SELECT DAYOFWEEK(column_name) FROM table_name;

39. WEEKDAY(date)

-- 예시: 날짜의 주중 요일 번호 반환 (0=월요일, 6=일요일)
SELECT WEEKDAY('2023-01-01'); -- 결과: 6

-- 활용: 테이블의 특정 열 날짜의 주중 요일 번호 반환
SELECT WEEKDAY(column_name) FROM table_name;

40. DAYOFYEAR(date)

-- 예시: 날짜의 연중 일자 반환
SELECT DAYOFYEAR('2023-07-01'); -- 결과: 182

-- 활용: 테이블의 특정 열 날짜의 연중 일자 반환
SELECT DAYOFYEAR(column_name) FROM table_name;

41. WEEK(date)

-- 예시: 날짜가 포함된 주의 연중 주 번호 반환
SELECT WEEK('2023-01-08'); -- 결과: 2

-- 문제: 테이블의 특정 열 날짜의 연중 주 번호 반환
SELECT WEEK(column_name) FROM table_name;

42. FROM_DAYS(number)

-- 예시: 주어진 일 수를 날짜로 변환
SELECT FROM_DAYS(738000); -- 결과: 2020-07-29 00:00:00

-- 활용: 테이블의 특정 열 일 수를 날짜로 변환
SELECT FROM_DAYS(column_name) FROM table_name;

43. TO_DAYS(date)

-- 예시: 날짜를 연속된 일 수로 변환
SELECT TO_DAYS('2023-01-01'); -- 결과; 738886

-- 활용: 테이블의 특정 열 날짜를 연속된 일 수로 변환
SELECT TO_DAYS(column_name) FROM table_name;

44. DATE_FORMAT(date, format)

-- 예시: 날짜를 지정된 형식으로 포맷
SELECT DATE_FORMAT('2023-01-01', '%Y%m%d'); -- 결과: 20230101

-- 활용: 테이블의 특정 열 날짜를 'dd/mm/yyyy' 형식으로 포맷
SELECT DATE_FORMAT(column_name, '%d/%m/%Y') FROM table_name;

논리함수

45. IF(condition, true_value, false_value)

-- 예시: 조건에 따른 값 반환
SELECT IF(1>0, 'True', 'False'); -- 결과: TRUE

-- 활용: 테이블의 특정 열이 10보다 큰 경우 'Greater', 아니면 'Smaller' 반환
SELECT IF(column_name > 10, 'Greater', 'Smaller') FROM table_name;

46. IFNULL(value, defaulf_value)

-- 예시: NULL 값을 'Hello World'로 대체
SELECT IFNULL(NULL, 'Hello World'); -- 결과: Hello World

-- 활용: 테이블의 특정 열에서 NULL 값을 'Default'로 대체
SELECT IFNULL(column_name, 'Default') FROM table_name;

집계 함수

47. COUNT(expression)

-- 예시: 테이블의 전체 행 수 계산
SELECT COUNT(*) AS TotalCount FROM table_name; 

-- 문제: 특정 조건을 만족하는 행의 수 계산
SELECT COUNT(column_name) FROM table_name WHERE condition;

48. SUM(column)

-- 예시: 열 값의 합계 계산
SELECT SUM(column_name) AS TotalSum FROM table_name;

49. AVG(column)

-- 예시: 열 값의 평균 계산
SELECT AVG(column_name) AS AverageValue FROM table_name;

50. MAX(column)

-- 예시: 열 값의 최대값 찾기
SELECT MAX(column_name) AS MaxValue FROM table_name;

51. MIN(column)

-- 예시: 열 값의 최소값 찾기
SELECT MIN(column_name) AS MinValue FROM table_name;

기타 함수

51. DATABASE()

-- 예시: 현재 데이터베이스 이름 반환
SELECT DATABASE();

52. PASSWORD(string)

-- 예시: 문자열을 MySQL 비밀번호 형식으로 암호화
SELECT PASSWORD('my_password');

53. FORMAT(number, decimals)

-- 예시: 숫자를 지정된 소수점 자리수로 포맷
SELECT FORMAT(12345.6789, 2); -- 결과: 12,345.68

-- 활용: 테이블의 특정 열 숫자를 소수점 셋째 자리로 포맷
SELECT FORMAT(column_name, 3) FROM table_name;

저작자표시

'데이터분석 > SQL' 카테고리의 다른 글

[해커랭크] SQL 심화 응용 팁들 (1)	2024.09.24
MySQL 다운로드 및 설치하는 방법 (1)	2024.03.10
[BigQuery] 신규사용자가 첫 접속날짜에 남긴 이벤트만 필터링 (0)	2024.02.25
[BigQuery] event_date 날짜 형식 변경 방법 (0)	2024.02.25
파이썬에서 MySQL 쿼리로 DB 데이터 끌어오는 방법 (0)	2023.12.17