[MySQL] 함수

 

✔️ 함수

 

📌 그룹 집계 함수

SELECT
  MAX(Quantity),
  MIN(Quantity),
  COUNT(Quantity),
  SUM(Quantity),
  AVG(Quantity)
FROM OrderDetails
WHERE OrderDetailID BETWEEN 20 AND 30;

• MAX : 가장 큰 값
• MIN : 가장 작은 값
• COUNT : 갯수 (null 제외)
• SUM : 총합
• AVG : 평균
• SELECT를 이용해 컬럼으로 나타낼 수도 있고 HAVING에서 조건절의 재료로 사용할 수 있습니다.

 

📌 숫자 관련 함수

SELECT ROUND(0.5), CEIL(0.4), FLOOR(0.6);

SELECT Price, ROUND(price), CEIL(price), FLOOR(price) FROM Products;

• ROUND : 반올림
• CEIL : 올림
• FLOOR : 내림

 

SELECT ABS(1), ABS(-1), ABS(3 - 10);

SELECT * FROM OrderDetails WHERE ABS(Quantity - 10) < 5;

• ABS : 절대값

 

SELECT GREATEST(1, 2, 3), LEAST(1, 2, 3, 4, 5);

SELECT
  OrderDetailID, ProductID, Quantity,
  GREATEST(OrderDetailID, ProductID, Quantity),
  LEAST(OrderDetailID, ProductID, Quantity)
FROM OrderDetails;

• GREATEST : () 중 가장 큰 값
• LEAST : () 중 가장 작은 값

 

SELECT POW(2, 3), POWER(5, 2), SQRT(16);

SELECT Price, POW(Price, 1/2) FROM Products WHERE SQRT(Price) < 4;

• POW(a, b), POWER(a, b) : a를 b만큼 제곱
• SQRT : 제곱근
• EXP(n) : e의 거듭제곱
• LOG(n) : 자연로그

 

SELECT
  TRUNCATE(1234.5678, 1),
  TRUNCATE(1234.5678, 2),
  TRUNCATE(1234.5678, 3),
  TRUNCATE(1234.5678, -1),
  TRUNCATE(1234.5678, -2),
  TRUNCATE(1234.5678, -3);

SELECT Price FROM Products
WHERE TRUNCATE(Price, 0) = 12;

• TRUNCATE(N, n) : N을 소숫점 n자리까지 선택
  • n자리에 양수값을 넣으면 소수점 아래로 주어진 값의 자리만큼 반환한다
  • n자리에 음수값을 넣으면 소수점 위로 주어진 값의 자리만큼 내림처리된 후 반환한다
  • n자리에 0을 넣으면 소수점을 떼고 반환한다

 

SELECT bin(31), hex(31), oct(31);

• bin() : 2진수
• hex() : 16진수
• oct() : 8진수

 

SELECT ROUND(RAND()*100, 0)

• ROUND(RAND() * max, min) : min~max 사이의 랜덤값

 

📌 문자열 관련 함수

SELECT UPPER('abcDEF'), LOWER('abcDEF');

SELECT UCASE(CustomerName), LCASE(ContactName) FROM Customers;

• UCASE, UPPER : 대문자로
• LCASE, LOWER : 소문자로

 

SELECT CONCAT('HELLO', ' ', 'THIS IS ', 2021) -- HELLO THIS IS 2021

SELECT CONCAT_WS('-', 2021, 8, 15, 'AM') -- 2021-8-15-AM

SELECT CONCAT('O-ID: ', OrderID) FROM Orders;

SELECT
  CONCAT_WS(' ', FirstName, LastName) AS FullName
FROM Employees;

• CONCAT(…) : () 안의 내용을 이어붙임
• CONCAT_WS(S, …) : () 안의 내용을 S로 이어붙임
  • NULL이 내용에 있으면 출력되지 않는다.

 

SELECT
  SUBSTR('ABCDEFG', 3), -- CDEFG
  SUBSTR('ABCDEFG', 3, 2), -- CD
  SUBSTR('ABCDEFG', -4), -- DEFG
  SUBSTR('ABCDEFG', -4, 2); -- DE

SELECT
  LEFT('ABCDEFG', 3), -- ABC
  RIGHT('ABCDEFG', 3); -- EFG

• SUBSTR, SUBSTRING(’str’, start, n) : ‘str’에서 start위치부터 n만큼 자른다
  • start에 음수값이 들어가면 뒤에서부터 시작한다.
• LEFT, RIGHT(’str’, n) : ‘str’의 왼쪽, 오른쪽부터 n만큼 자른다

 

SELECT
  LENGTH('ABCDE'), -- 5
  CHAR_LENGTH('ABCDE'), -- 5
  CHARACTER_LENGTH('ABCDE'); -- 5

SELECT
  LENGTH('안녕하세요'), -- 15
  CHAR_LENGTH('안녕하세요'), -- 5
  CHARACTER_LENGTH('안녕하세요'); -- 5

• LENGTH : 문자열 바이트 크기
• CHAR_LENGTH, CHARACTER_LENGTH : 문자의 개수
• BIT_LENGTH : 문자열 비트 크기

 

SELECT
  CONCAT('|', ' HELLO ', '|'), -- | HELLO |
  CONCAT('|', LTRIM(' HELLO '), '|'), -- |HELLO |
  CONCAT('|', RTRIM(' HELLO '), '|'), -- | HELLO|
  CONCAT('|', TRIM(' HELLO '), '|'); -- |HELLO|

• TRIM : 양쪽 공백 제거
• LTRIM : 왼쪽 공백 제거
• RTRIM : 오른쪽 공백 제거

 

SELECT
  LPAD('ABC', 5, '-'), -- --ABC
  RPAD('ABC', 5, '-'); -- ABC--

• LPAD(s, n, p) : s가 n개가 될 때까지 왼쪽에 p를 붙인다
• RPAD(s, n, p) : s가 n개가 될 때까지 오른쪽에 p를 붙인다

 

SELECT
  REPLACE('맥도날드에서 맥도날드 햄버거를 먹었다.', '맥도날드', '버거킹');
	-- 버거킹에서 버거킹 햄버거를 먹었다.

• REPLACE(s, a, b) : s에서 a를 b로 치환

 

SELECT
  INSTR('ABCDE', 'ABC'), -- 1
  INSTR('ABCDE', 'BCDE'), -- 2
  INSTR('ABCDE', 'C'), -- 3
  INSTR('ABCDE', 'DE'), -- 4
  INSTR('ABCDE', 'F'); -- 5

• INSTR(S, s) : S 중 s의 첫 위치 반환 (없으면 0)

 

SELECT
  '01' = '1', -- 0
  CONVERT('01', DECIMAL) = CONVERT('1', DECIMAL); -- 1

• CONVERT(a, t) : a를 t 자료형으로 변환
• CAST(a AS t)로도 사용 가능
• t로 올 수 있는 타입
  • BINARY
  • DECIMAL, CHAR, NCHAR, SIGNED, UNSIGNED
  • DATE, DATETIME, TIME
  • JSON

 

SELECT
  REPEAT('A', 3);

• REPEAT(s, n) : s를 n번 반복

 

SELECT
  REVERSE('abcdef');

• REVERSE(s) : s를 뒤집어 반환

 

-- MySQL은 배열 인덱스 시작이 1이다.
LOCATE('abc', 'ababcDEFabc'); -- 3
LOCATE('abc', 'ababcDEFabc', 4); --9

• LOCATE(s, t, i) : t에서 s의 위치를 i부터 찾아서 반환

 

📌 시간/날짜 관련 함수

SELECT CURDATE(), CURTIME(), NOW();

• CURRENT_DATE() , CURDATE() : 현재 날짜 반환
• CURRENT_TIEM() , CURTIME() : 현재 시간 반환
• CURRENT_TIMESTAMP() , SYSDATE() , NOW() : 현재 시간과 날짜 반환

 

SELECT
  '2021-6-1' = '2021-06-01',
  DATE('2021-6-1') = DATE('2021-06-01'),
  '1:2:3' = '01:02:03',
  TIME('1:2:3') = TIME('01:02:03');

• DATE(’yyyy-MM-dd’) : 문자열에 따라 날짜 생성
• TIME(’hh:mm:ss’) : 문자열에 따라 시간 생성

 

SELECT
  OrderDate,
  YEAR(OrderDate) AS YEAR,
  MONTHNAME(OrderDate) AS MONTHNAME,
  MONTH(OrderDate) AS MONTH,
  WEEKDAY(OrderDate) AS WEEKDAY,
  DAYNAME(OrderDate) AS DAYNAME,
  DAY(OrderDate) AS DAY
FROM Orders;

• YEAR : 주어진 DATETIME값의 년도 반환
• MONTHNAME : 주어진 DATETIME값의 월(영문) 반환
• MONTH : 주어진 DATETIME값의 월 반환
• WEEKDAY : 주어진 DATETIME값의 요일값 반환 (월요일 : 0)
• DAYNAME : 주어진 DATETIME값의 요일명 반환
• DAYOFMONTH, DAY : 주어진 DATETIME값의 날짜(일) 반환

 

SELECT
  HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());

• HOUR : 주어진 DATETIME의 시 반환
• MINUTE : 주어진 DATETIME의 분 반환
• SECOND : 주어진 DATETIME의 초 반환

 

SELECT 
  ADDDATE('2021-06-20', INTERVAL 1 YEAR),
  ADDDATE('2021-06-20', INTERVAL -2 MONTH),
  ADDDATE('2021-06-20', INTERVAL 3 WEEK),
  ADDDATE('2021-06-20', INTERVAL -4 DAY),
  ADDDATE('2021-06-20', INTERVAL -5 MINUTE),
  ADDDATE('2021-06-20 13:01:12', INTERVAL 6 SECOND);

• ADDDATE(’yyyy-MM-dd’, INTERVAL n YEAR) : 시간/날짜 더하기
• SUBDATE(’yyyy-MM-dd’, INTERVAL n YEAR) : 시간/날짜 빼기

 

SELECT
  OrderDate,
  NOW(),
  DATEDIFF(OrderDate, NOW())
FROM Orders;

• DATEDIFF(’yyyy-MM-dd’, ‘yyyy-MM-dd’) : 두 시간/날짜 간 일수차
• TIMEDIFF(’hh:mm:ss’, ‘hh:mm:ss’) : 두 시간/날짜 간 시간차

 

SELECT
  OrderDate,
  LAST_DAY(OrderDate),
  DAY(LAST_DAY(OrderDate)),
  DATEDIFF(LAST_DAY(OrderDate), OrderDate)
FROM Orders;

• LAST_DAY(’yyyy-MM-dd’) : 해당 달의 마지막 날짜

 

SELECT
  DATE_FORMAT(NOW(), '%M %D, %Y %T'),
  DATE_FORMAT(NOW(), '%y-%m-%d %h:%i:%s %p'),
  DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %s초');

• %Y : 년도 4자리
• %y : 년도 2자리
• %M : 월 영문
• %m : 월 숫자
• %D : 일 영문(1st, 2nd, 3rd …)
• %d, %e : 일 숫자(01~31)
• %T : hh:mm:ss
• %r : hh:mm:ss AM/PM
• %H, %k : 시(~23)
• %h, %l : 시(~12)
• %i : 분
• %S, %s : 초
• %p : AM/PM

 

SELECT
  DATEDIFF(
    STR_TO_DATE('2021-06-04 07:48:52', '%Y-%m-%d %T'),
    STR_TO_DATE('2021-06-01 12:30:05', '%Y-%m-%d %T')
  ),
  TIMEDIFF(
    STR_TO_DATE('2021-06-04 07:48:52', '%Y-%m-%d %T'),
    STR_TO_DATE('2021-06-01 12:30:05', '%Y-%m-%d %T')
  );

• STR_TO_DATE(S, F) : S를 F형식으로 해석하여 시간/날짜 생성

 

📌 기타 함수

SELECT IF (1 > 2, '1는 2보다 크다.', '1은 2보다 작다.');

• IF(조건, T, F) : 조건이 참이라면 T, 거짓이면 F 반환

 

SELECT
  IFNULL('A', 'B'),
  IFNULL(NULL, 'B');

• IFNULL(A, B) : A가 NULL이 아니면 A 반환, NULL일 시 B 반환

 

SELECT
	NULLIF('A', 'B');

• NULLIF(A, B) : A와 B가 같으면 NULL 반환, 같지 않으면 A 반환

 

SELECT
CASE
  WHEN -1 > 0 THEN '-1은 양수다.'
  WHEN -1 = 0 THEN '-1은 0이다.'
  ELSE '-1은 음수다.'
END;

• CASE
  • WHEN [조건] THEN [참일 때 결과]
  • ELSE [거짓일 때 결과]
• END