함수

그룹 집계 함수

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

•

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;
SQL
복사

•

ROUND : 반올림

•

CEIL : 올림

•

FLOOR : 내림

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

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

•

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;
SQL
복사

•

GREATEST : () 중 가장 큰 값

•

LEAST : () 중 가장 작은 값

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

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

•

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;
SQL
복사

•

TRUNCATE(N, n) : N을 소숫점 n자리까지 선택

◦

n자리에 양수값을 넣으면 소수점 아래로 주어진 값의 자리만큼 반환한다

◦

n자리에 음수값을 넣으면 소수점 위로 주어진 값의 자리만큼 내림처리된 후 반환한다

◦

n자리에 0을 넣으면 소수점을 떼고 반환한다

SELECT bin(31), hex(31), oct(31);
SQL
복사

•

bin() : 2진수

•

hex() : 16진수

•

oct() : 8진수

SELECT ROUND(RAND()*100, 0)
SQL
복사

•

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

문자열 관련 함수

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

SELECT UCASE(CustomerName), LCASE(ContactName)
FROM Customers;
SQL
복사

•

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;
SQL
복사

•

CONCAT(…) : () 안의 내용을 이어붙임

•

CONCAT_WS(S, …) : () 안의 내용을 S로 이어붙임

◦

NULL이 내용에 있으면 출력되지 않는다.

SELECT
  SUBSTR('ABCDEFG', 3), -- CDEFG
	SUBSTR('ABCDEFG', 3, 2), -- CD
	SUBSTR('ABCDEFG', -4), -- DEFG
	SUBSTR('ABCDEFG', -4, 2); -- DESELECT
  LEFT('ABCDEFG', 3), -- ABC
	RIGHT('ABCDEFG', 3); -- EFG
SQL
복사

•

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'); -- 5SELECT
  LENGTH('안녕하세요'), -- 15
	CHAR_LENGTH('안녕하세요'), -- 5
	CHARACTER_LENGTH('안녕하세요'); -- 5
SQL
복사

•

LENGTH : 문자열 바이트 크기

•

CHAR_LENGTH, CHARACTER_LENGTH : 문자의 개수

•

BIT_LENGTH : 문자열 비트 크기

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

•

TRIM : 양쪽 공백 제거

•

LTRIM : 왼쪽 공백 제거

•

RTRIM : 오른쪽 공백 제거

SELECT
  LPAD('ABC', 5, '-'), -- --ABC
  RPAD('ABC', 5, '-'); -- ABC--
SQL
복사

•

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

•

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

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

•

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
SQL
복사

•

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

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

•

CONVERT(a, t) : a를 t 자료형으로 변환

•

CAST(a AS t)로도 사용 가능

•

t로 올 수 있는 타입

◦

BINARY

◦

DECIMAL, CHAR, NCHAR, SIGNED, UNSIGNED

◦

DATE, DATETIME, TIME

◦

JSON

SELECT
  REPEAT('A', 3);
SQL
복사

•

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

SELECT
  REVERSE('abcdef');
SQL
복사

•

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

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

•

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

시간/날짜 관련 함수

SELECT CURDATE(), CURTIME(), NOW();
SQL
복사

•

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');
SQL
복사

•

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;
SQL
복사

•

YEAR : 주어진 DATETIME값의 년도 반환

•

MONTHNAME : 주어진 DATETIME값의 월(영문) 반환

•

MONTH : 주어진 DATETIME값의 월 반환

•

WEEKDAY : 주어진 DATETIME값의 요일값 반환 (월요일 : 0)

•

DAYNAME : 주어진 DATETIME값의 요일명 반환

•

DAYOFMONTH, DAY : 주어진 DATETIME값의 날짜(일) 반환

SELECT
  HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());
SQL
복사

•

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);
SQL
복사

•

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

•

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

SELECT
  OrderDate,
  NOW(),
  DATEDIFF(OrderDate, NOW())
FROM Orders;
SQL
복사

•

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;
SQL
복사

•

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초');
SQL
복사

•

%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')
  );
SQL
복사

•

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

기타 함수

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

•

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

SELECT
  IFNULL('A', 'B'),
  IFNULL(NULL, 'B');
SQL
복사

•

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

SELECT
	NULLIF('A', 'B');
SQL
복사

•

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

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

•

CASE

◦

WHEN [조건] THEN [참일 때 결과]

◦

ELSE [거짓일 때 결과]

•

END