MySQL 내장함수

문자열 함수

MySQL 내장 함수

MySQL은 사용자의 편의를 위해 다양한 기능의 내장 함수를 미리 정의하여 제공하고 있습니다.

 

MySQL에서 미리 정의하여 제공해 주는 대표적인 내장 함수의 종류는 다음과 같습니다.

 

 

 

1. 문자열 함수

 

2. 수학 함수

 

3. 날짜와 시간 함수

 

문자열 길이

LENGTH() 함수는 전달받은 문자열의 길이를 반환합니다.

 

예제

 

 

SELECT LENGTH('12345678');

 

 

문자열 결합

CONCAT() 함수는 전달받은 문자열을 모두 결합하여 하나의 문자열로 반환합니다.

 

만약 전달받은 문자열 중 하나라도 NULL이 존재하면, NULL을 반환합니다.

 

예제

 

 

SELECT CONCAT('Ora', 'cle Cor', 'poration'),

 

CONCAT('Oracle', NULL, 'Corporation');

 

 

특정 문자열의 위치 검색

LOCATE() 함수는 인수로 전달받은 문자열이 특정 문자열에서 처음으로 나타나는 위치를 찾아서, 해당 위치를 반환합니다.

 

만약 전달받은 문자열이 특정 문자열 내에 존재하지 않으면 0을 반환합니다.

 

 

 

다른 대부분의 프로그래밍 언어에서는 문자열의 첫 번째 문자의 인덱스를 0부터 시작하여 그 위치를 계산합니다.

 

하지만 MySQL에서는 문자열의 첫 번째 문자의 인덱스를 언제나 1부터 시작하여 계산하므로, 주의를 기울여야 합니다.

 

 

 

이때 세 번째 인수로 특정 문자열에서 전달받은 문자열을 찾기 시작할 인덱스를 전달할 수도 있습니다.

 

예제

 

 

SELECT LOCATE('abc', 'ababcDEFabc'),

 

LOCATE('abc', 'ababcDEFabc', 4);

 

 

 

 

문자열 추출

LEFT() 함수는 전달받은 문자열의 왼쪽부터 명시한 개수만큼의 문자를 반환합니다.

 

RIGHT() 함수는 전달받은 문자열의 오른쪽부터 명시한 개수만큼의 문자를 반환합니다.

 

예제

 

 

SELECT LEFT('MySQL PHP HTML Java', 5),

 

RIGHT('MySQL PHP HTML Java', 4);

 

 

문자열 대소문자 변경

LOWER() 함수는 전달받은 문자열의 문자를 모두 소문자로 변경합니다.

 

UPPER() 함수는 전달받은 문자열의 문자를 모두 대문자로 변경합니다.

 

예제

 

 

SELECT LOWER('MySQL PHP HTML Java'),

 

UPPER('MySQL PHP HTML Java');

 

 

문자열 대체

REPLACE() 함수는 전달받은 문자열에서 특정 문자열을 찾은 후에, 찾은 문자열을 대체 문자열로 교체합니다.

 

예제

 

 

SELECT REPLACE('MySQL', 'My', 'MS ');

 

 

문자열 다듬기

TRIM() 함수는 전달받은 문자열의 앞이나 뒤, 또는 양쪽 모두에 있는 특정 문자를 제거합니다.

 

 

 

TRIM() 함수에서 사용할 수 있는 지정자는 다음과 같습니다.

 

 

 

1. BOTH : 전달받은 문자열의 양 끝에 존재하는 특정 문자를 제거함. (기본 설정)

 

2. LEADING : 전달받은 문자열 앞에 존재하는 특정 문자를 제거함.

 

3. TRAILING : 전달받은 문자열 뒤에 존재하는 특정 문자를 제거함.

 

 

 

만약 지정자를 명시하지 않으면, 자동으로 BOTH로 설정됩니다.

 

또한, 제거할 문자를 명시하지 않으면, 자동으로 공백을 제거하게 됩니다.

 

예제

 

 

SELECT TRIM(' !!!MySQL PHP HTML Java!!! '),

 

TRIM(LEADING '!' FROM '!!!MySQL PHP HTML Java!!!')

 

 

숫자로 이루어진 문자열의 형식화

FORMAT() 함수는 숫자 타입의 데이터를 세 자리마다 쉼표(,)를 사용하는 '#,###,###.##' 형식으로 변환해 줍니다.

 

하지만 반환되는 데이터의 형식이 숫자 타입이 아닌 문자열 타입이므로, 주의를 기울여야 합니다.

 

이때 두 번째 인수로 반올림할 소수 부분의 자릿수까지 전달할 수 있습니다.

 

예제

 

 

SELECT FORMAT(123456789.123456, 3);

 

 

올림과 내림

FLOOR() 함수는 인수로 전달받은 값과 같거나 작은 수 중에서 가장 큰 정수를 반환합니다.

 

CEIL() 함수는 반대로 인수로 전달받은 값과 같거나 큰 수 중에서 가장 작은 정수를 반환합니다.

 

예제

SELECT FLOOR(10.95),

 

FLOOR(11.01),

 

FLOOR(-10.95),

 

FLOOR(-11.01);

 

예제

SELECT CEIL(10.95),

 

CEIL(11.01),

 

CEIL(11),

 

CEIL(-10.95),

 

CEIL(-11.01);

 

또한, 소수점에서의 반올림은 ROUND() 함수를 사용하여 수행할 수 있습니다.

 

이때 두 번째 인수로 반올림할 소수점을 전달할 수도 있습니다.

 

예제

SELECT ROUND(10.49),

 

ROUND(10.5),

 

ROUND(-10.5),

 

ROUND(-10.49);

 

지수와 로그

SQRT() 함수는 전달받은 수의 제곱근 중 양수값을 반환합니다.

 

POW() 함수는 전달받은 수의 거듭제곱을 반환합니다.

 

POW() 함수의 첫 번째 인수로는 밑수를 전달하고, 두 번째 인수로는 지수를 전달합니다.

 

 

 

EXP() 함수는 인수로 지수를 전달받아, e의 거듭제곱을 계산하여 반환합니다.

 

LOG() 함수는 전달받은 수의 자연로그 값을 계산하여 반환합니다.

 

예제

SELECT SQRT(4),

 

POW(2, 3),

 

EXP(3),

 

LOG(3);

 

삼각 함수

MySQL에서는 다음 함수를 사용하여 쿼리에 간단히 삼각 함수를 사용할 수 있습니다.

 

SIN() 함수는 전달받은 수의 사인값을, COS() 함수는 코사인값을, TAN() 함수는 탄젠트값을 반환합니다.

 

예제

SELECT SIN(PI()/2),

 

COS(PI()),

 

TAN(PI()/4);

 

위의 예제에서 사용된 PI() 함수는 파이(π, 3.14) 값을 반환하는 함수입니다.

 

기타 함수

ABS(X) 함수는 전달받은 수의 절댓값을 반환합니다.

 

RAND() 함수는 0.0보다 크거나 같고 1.0보다 작은 하나의 실수를 무작위로 생성하여 반환합니다.

 

예제

 

 

SELECT ABS(-3),

 

ROUND(RAND()*100, 0);

 

 

위의 예제에서 RAND() 함수는 ROUND() 함수의 인수로 전달되어 0부터 100 사이의 무작위 정수를 하나씩 생성합니다.

 

현재 날짜와 시간 정보

NOW() 함수는 현재 날짜와 시간을 반환합니다.

 

이때 반환되는 값은 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 형태로 반환됩니다.

 

 

 

CURDATE() 함수는 현재 날짜를 반환합니다.

 

이때 반환되는 값은 'YYYY-MM-DD' 또는 YYYYMMDD 형태로 반환됩니다.

 

 

 

CURTIME() 함수는 현재 시각을 반환합니다.

 

이때 반환되는 값은 'HH:MM:SS' 또는 HHMMSS 형태로 반환됩니다.

 

예제

SELECT NOW(),

 

CURDATE(),

 

CURTIME();

 

특정 날짜와 시간 정보

DATE() 함수는 전달받은 값에 해당하는 날짜 정보를 반환합니다.

 

MONTH() 함수는 전달받은 값에서 월에 해당하는 값만을 반환하며, 0부터 12 사이의 값을 가집니다.

 

DAY() 함수는 전달받은 값에서 일자에 해당하는 값만을 반환하며, 0부터 31 사이의 값을 가집니다.

 

 

 

HOUR() 함수는 전달받은 값에서 시간에 해당하는 값만을 반환하며, 0부터 23 사이의 값을 가집니다.

 

MINUTE() 함수는 전달받은 값에서 분에 해당하는 값만을 반환하며, 0부터 59 사이의 값을 가집니다.

 

SECOND() 함수는 전달받은 값에서 초에 해당하는 값만을 반환하며, 0부터 59 사이의 값을 가집니다.

 

예제

SELECT DATE('2016-02-19 12:34:56'),

 

MONTH('2016-01-02 12:34:56'),

 

DAY('2016-01-02 12:34:56'),

 

HOUR('12:34:56'),

 

MINUTE('12:34:56'),

 

SECOND('12:34:56');

 

요일과 월 이름

MONTHNAME() 함수는 전달받은 값의 월에 해당하는 이름을 반환합니다.

 

DAYNAME() 함수는 전달받은 값의 요일에 해당하는 이름을 반환합니다.

 

예제

 

 

SELECT MONTHNAME('2017-02-19'),

 

DAYNAME('2017-02-19');

 

 

연도별, 월별, 주별 일수

DAYOFWEEK() 함수는 전달받은 값의 일자가 해당 주에서 몇 번째 날인지를 반환합니다.

 

이 함수는 1부터 7 사이의 값을 반환합니다. (일요일 = 1, 토요일 = 7)

 

 

 

DAYOFMONTH() 함수는 전달받은 값의 일자가 해당 월에서 몇 번째 날인지를 반환합니다.

 

이 함수는 0부터 31 사이의 값을 반환합니다.

 

 

 

DAYOFYEAR() 함수는 전달받은 값의 일자가 해당 연도에서 몇 번째 날인지를 반환합니다.

 

이 함수는 1부터 366 사이의 값을 반환합니다.

 

예제

SELECT DAYOFMONTH('2017-02-19'),

 

DAYOFWEEK('2017-02-19'),

 

DAYOFYEAR('2017-02-19');

 

날짜와 시간의 형식화

DATE_FORMAT() 함수는 전달받은 형식에 맞춰 날짜와 시간 정보를 문자열로 반환합니다.

예제

 

 

SELECT DATE_FORMAT('2017-02-19 12:34:56', '%D %y %a %d %m %b %j');

 

그룹(aggregate) 함수

MySQL에서는 하나의 값이 아닌 값들의 집합에 대해서 동작하는 그룹(aggregate) 함수를 제공하고 있습니다.

 

 

 

MySQL에서 자주 사용되는 대표적인 그룹 함수는 다음과 같습니다.

 

 

 

1. COUNT()

 

2. MIN()과 MAX()

 

3. SUM()

 

4. AVG()

대부분의 그룹 함수는 NULL 값을 제외하고 동작한 결과를 반환합니다.

 

COUNT()

COUNT() 함수는 선택된 필드에서 특정 조건을 만족하는 레코드의 총 개수를 반환합니다.

 

만약 조건을 만족하는 레코드가 없다면, COUNT() 함수는 0을 반환합니다.

 

이때 중복된 값을 제외하려면, 필드 이름 앞에 DISTINCT 키워드를 사용하면 됩니다.

 

 

 

다음 예제는 Reservation 테이블의 총 레코드 개수를 구하는 예제입니다.

 

예제

 

 

SELECT COUNT(*) AS NumberOfRervation

 

FROM Reservation;

 

 

MIN()과 MAX()

MIN() 함수는 선택된 필드에 저장된 값 중 가장 작은 값을 반환합니다.

 

MAX() 함수는 선택된 필드에 저장된 값 중 가장 큰 값을 반환합니다.

 

 

 

다음 예제는 Customer 테이블의 Age 필드에 저장된 값 중 최솟값을 구하는 예제입니다.

 

예제

 

 

SELECT MIN(Age) AS MinAge

 

FROM Customer;

 

 

다음 예제는 Customer 테이블의 Name 필드에 저장된 값 중 최댓값을 구하는 예제입니다.

 

예제

 

 

SELECT MAX(Name) AS MaxName

 

FROM Customer;

 

 

위의 예제처럼 MIN() 함수와 MAX() 함수는 문자열 타입의 필드에서도 최솟값과 최댓값을 구할 수 있습니다.

 

SUM()

SUM() 함수는 선택된 숫자 타입의 필드에 저장된 값의 총 합을 반환합니다.

 

 

 

다음 예제는 Customer 테이블의 Age 필드에 저장된 값의 총 합을 구하는 예제입니다.

 

예제

 

 

SELECT SUM(Age) AS SumOfAge

 

FROM Customer;

 

 

AVG() 함수는 선택된 숫자 타입의 필드에 저장된 값의 평균값을 반환합니다.

 

 

 

다음 예제는 Customer 테이블의 Age 필드에 저장된 값의 평균값을 구하는 예제입니다.

 

예제

 

 

SELECT AVG(Age) AS AverageOfAge

 

FROM Customer;

 

 

GROUP BY 절

GROUP BY 절은 선택된 레코드의 집합을 필드의 값이나 표현식에 의해 그룹화한 결과 집합을 반환합니다.

 

즉, GROUP BY 절은 하나의 그룹을 하나의 레코드로 반환하므로, 결과 집합의 크기를 줄여주는 역할을 합니다.

 

 

 

이러한 GROUP BY 절은 SELECT 문에서만 사용할 수 있으며, 앞서 살펴 본 그룹 함수를 사용할 때 자주 같이 사용됩니다.

 

문법

SELECT 필드이름, 그룹함수(필드이름)

 

FROM 테이블이름

 

[WHERE 조건]

 

GROUP BY 필드이름;

 

 

 

다음 예제는 Customer 테이블에서 각 나이별 회원수를 구하는 예제입니다.

 

예제

SELECT Age, COUNT(*) AS NumOfCustomer

 

FROM Customer

 

GROUP BY Age;

위의 예제처럼 GROUP BY 절에 사용된 필드의 이름은 반드시 SELECT 문에도 같이 명시되어야 합니다.

 

HAVING 절

HAVING 절은 SELECT 문의 WHERE 절처럼 GROUP BY 절에 의해 반환되는 결과 집합의 조건을 설정할 수 있게 해줍니다.

 

문법

SELECT 필드이름, 그룹함수(필드이름)

 

FROM 테이블이름

 

[WHERE 조건]

 

GROUP BY 필드이름

 

HAVING 조건;

 

 

 

다음 예제는 Customer 테이블에서 각 도시별 가장 나이가 많은 레코드 중 15살 이상의 레코드만을 선택하는 예제입니다.

 

예제

SELECT Address, Name, MAX(Age) AS MaxAge

 

FROM Customer

 

GROUP BY Address

 

HAVING MaxAge > 15;