My-SQL 함수 모음

. mysql의 주요 함수



여기서는 mysql의 주요 함수에 대해서 설명한다. 지면관계 상 모든 함수를 설명하지는 못했으며 기초적인 함수와 중요함수 위주로 설명했다. 또한 mysql은 개발 속도가 비교적 빠른 데이터베이스 이므로 가능한 매뉴얼을 참고하는 것이 바람직하다.

편의를 위해 각 함수의 결과는 간략하게 표현했다.

예를 들어 다음 문장의 실행 결과를

mysql> select mod(29,9);

1 rows in set (0.00 sec)

+-----------+

| mod(29,9) |

+-----------+

| 2 |

+-----------+

 



아래와 같이 표현했다.

mysql> select mod(29,9);

-> 2

 



1) 그룹함수(grouping functions)

( ... )

괄호 . 연산의 순서를 명확하게 하기 위해 사용된다

mysql> select 1+2*3;

-> 7

mysql> select (1+2)*3;

-> 9
 




2) 일반적인 산술 연산자

일반적인 연산자들이 그대로 사용된다. 연산에 사용되는 숫자가 모두 정수일 경우 -, + , * 연산의 결과 값은 bigint (64bit)로 표현된다.
+
더하기

mysql> select 3+5;

-> 8

-

빼기 
mysql> select 3-5;

-> -2

* 
곱하기

mysql> select 3*5;

-> 15

mysql> select 18014398509481984*18014398509481984.0;

-> 324518553658426726783156020576256.0

mysql> select 18014398509481984*18014398509481984;

-> 0
 




두 번째 식의 결과값은 제대로 나오지 않았는데 그 이유는 정수형 곱셈의 결과 값이 bigint 형이 나타낼 수 있는64비트의 범위를 초과 했기 때문이다.


/

나누기

mysql> select 3/5;

-> 0.60

0으로 나누는 경우 결과는 null 이다.

mysql> select 102/(1-1);

-> null
 





3) 논리 연산


모든 논리 연산의 결과값은 1 (true,참) 또는 0 (false,거짓) 중의 하나이다..


not

!

부정. not 은 1 이면 0을 0이면 1을 리턴한다.

예외: not null 의 경우는 null을 리턴

mysql> select not 1;

-> 0

mysql> select not null;

-> null

mysql> select ! (1+1);

-> 0

mysql> select ! 1+1;

-> 1
 




마지막 예의 경우는 (!1)+1의 순서로 해석되므로 1이 된다.


or

||

또는. 인수 모두 0 이거나 null 이 아니면 1을 리턴

mysql> select 1 || 0;

-> 1

mysql> select 0 || 0;

-> 0

mysql> select 1 || null;

-> 1
 





and

&&

그리고. 인수중 하나라도 0 또는 null이면 0 을, 아니면1을 리턴.

mysql> select 1 && null;

-> 0

mysql> select 1 && 0; 
-> 0
 





4) 비교 연산자

모든 비교 연산의 결과값은 1 (true,참) 또는 0 (false,거짓) 중의 하나이다..

아래의 함수들은 문자열 혹은 숫자 열에 사용될 수 있으며 필요에 따라 perl 언어에서와 마찬가지로 문자열은 숫자 열로 또는 숫자 열이 문자열로 바뀐다


mysql은 다음의 규칙에 따라 비교 연산을 수행한다


- 인수가 모두null 이면 비교연산의 결과도 null이다.

- 인수가 모두 문자열이면, 문자열로 비교된다.

- 인수가 모두 정수이면 정수로 비교된다.

- 한쪽 인수가 timestamp 또는 datetime 타입이고 다른 쪽이 숫자이면 timestamp 또는 datetime으로 변화되어 비교된다.

- 그 밖의 모든 경우에는 부동소수점 실수로 비교된다.


=

같다

mysql> select 1 = 0;

-> 0

mysql> select '0' = 0;

-> 1

mysql> select '0.0' = 0;

-> 1

mysql> select '0.01' = 0;

-> 0

mysql> select '.01' = 0.01;

-> 1
<> 
!=

같지 않다.

mysql> select '.01' <> '0.01';

-> 1

mysql> select .01 <> '0.01';

-> 0

mysql> select 'zapp' <> 'zappp';

-> 1 
<=

작거나 같다.

mysql> select 0.1 <= 2;

-> 1

<

작다

mysql> select 2 <= 2;

-> 1

>=

크거나 같다

mysql> select 2 >= 2;

-> 1

>

크다

mysql> select 2 > 2;

-> 0

<=>

한쪽이 null 인 경우에만 0 

mysql> select 1 <=> 1, null <=> null, 1<=> null;

-> 1 1 0

isnull(expr)

만일 표현식expr이 null 이면 1을 그렇지않으면 0을 리턴한다.

mysql> select isnull(1+1);

-> 0

mysql> select isnull(1/0);

-> 1
 



※ 주의 =을 이용하여 null 값을 비교하면 항상 0 이 리턴 되므로 isnull() 함수를 이용해야 한다


expr between min and max
 




만일 표현식expr 이 min과 max 값 사이에 존재한다면 1을 그렇지 않으면 0을 리턴한다, expr 이 문자열인지 숫자인지에 따라 비교 방법이 결정된다. 

mysql> select 1 between 2 and 3;

-> 0

mysql> select 'b' between 'a' and 'c';

-> 1

mysql> select 2 between 2 and '3';

-> 1

mysql> select 2 between 2 and 'x-3';

-> 0 
expr in (value,...)
 





만일 표현식 expr 이 괄호 안의 목록에 존재하면 1 아니면 0을 리턴한다.

mysql> select 2 in (0,3,5,'wefwf');

-> 0

mysql> select 'wefwf' in (0,3,5,'wefwf');

-> 1
 





5) 문자열 비교 함수

like 

where 조건 절에서 like 에서는 다음 2가지의 와일드카드 문자(wildcard characters)를 쓸 수 있다.

%
 0 또는 하나 이상의 문자에 해당한다.
 
_
 하나의 문자에 해당한다
 



mysql> select 'david!' like 'david_';

-> 1

mysql> select 'david!' like '%d%v%';

-> 1

mysql> select 'david!' like 'david-';

-> 0

mysql> select 'david_' like 'david-';

-> 1

ansi sql과 달리 확장 기능으로 수치 표현식에도 like 를 사용할 수 있다. 

mysql> select 10 like '1%';

-> 1
 




6) 형 변환 연산자

binary

binary연산자는 다음에 따라오는 문자열을 바이너리 문자열로 바꿔서 연산한다. 바이너리 문자열의 큰 특징은 바로 대소문자를 구별한다는 것이다. 컬럼 자체가 바이너리 문자열로 정의 되어 있지 않더라도 대소문자를 구분해 문자열을 비교해야 할 경우 사용된다.

mysql> select "a" = "a";

-> 1

mysql> select binary "a" = "a";

-> 0
 




binary 는 mysql 3.23.0 에 새롭게 추가된 함수 이다.


7) 흐름 제어(control flow) 함수 


ifnull(expr1,expr2)

만일 expr1 이 null 이 아닐 경우 ifnull() 함수는 expr1 자체의 값을 리턴하고 그렇지 않을 경우 expr2을 리턴한다. 

mysql> select ifnull(1,0);

-> 1

mysql> select ifnull(0,10);

-> 0

mysql> select ifnull(1/0,10);

-> 10

mysql> select ifnull(1/0,'yes');

-> 'yes'
 




위의 두 번째 보기에서 엄연히 0은 null 과는 다르다는 점에 주의하자.

if(expr1,expr2,expr3)

 




만일 expr1 이 참이면 expr2를 리턴하고 그렇지 않으면 expr3을 리턴하다. 

mysql> select if(1>2,2,3);

-> 3

mysql> select if(1<2,'yes','no');

-> 'yes'

mysql> select if(strcmp('test','test1'),'yes','no');

-> 'no'
 





8) 수학 함수

모든 수학 함수는 오류가 생기면 null 을 리턴한다.

-

음수 부호 숫자의 부호를 바꾼다.

mysql> select - 2;

-> -2

abs(x)
 




x의 절대값을 구한다.

mysql> select abs(2);

-> 2

mysql> select abs(-32);

-> 32
 




sign(x)

x 의 부호에 따라 음수일 경우 -1, 0 일 경우 0, 양수일 경우 1을 리턴한다.

mysql> select sign(-32);

-> -1

mysql> select sign(0);

-> 0

mysql> select sign(234);

-> 1

mod(n,m)

%
 




나머지 연산자 n 을 m으로 나눈 나머지를 구한다

mysql> select mod(234, 10);

-> 4

mysql> select 253 % 7;

-> 1

mysql> select mod(29,9);

-> 2
 




floor(x)

x보다 크지 않은 최대 정수를 구한다.

mysql> select floor(1.23);

-> 1

mysql> select floor(-1.23);

-> -2
 




ceiling(x)

x 보다 작지않은 최소 정수를 구한다.

mysql> select ceiling(1.23);

-> 2

mysql> select ceiling(-1.23);

-> -1
 




round(x)

x 의 반올림 값을 구한다

mysql> select round(-1.23);

-> -1

mysql> select round(-1.58);

-> -2

mysql> select round(1.58);

-> 2
 




round(x,d)

x 를 반올림해서 소수점아래 d 자리까지 구한다. 만일 d 가 0 이면 소수점 아래부분을 생략된다.

mysql> select round(1.298, 1);

-> 1.3

mysql> select round(1.298, 0);

-> 1
 




exp(x)

e^x 값을 구한다.

mysql> select exp(2);

-> 2.389056

mysql> select exp(-2);

-> 0.135335
 




log(x)

자연로그 값을 구한다.

mysql> select log(2);

-> 0.693147

mysql> select log(-2);

-> null

if you want the log of a number x to some arbitary base b, use the formula log(x)/log(b).
 




log10(x)

상용로그 값을 구한다

mysql> select log10(2);

-> 0.301030

mysql> select log10(100);

-> 2.000000

mysql> select log10(-100);

-> null

pow(x,y)
 




power(x,y)

x의 y승 값을 구한다.

mysql> select pow(2,2);

-> 4.000000

mysql> select pow(2,-2);

-> 0.250000
 




sqrt(x)

x의 근을 구한다

mysql> select sqrt(4);

-> 2.000000

mysql> select sqrt(20);

-> 4.472136
 




pi()

파이 값을 리턴한다

mysql> select pi();

-> 3.141593
 




cos(x)

x 의 코사인 값을 구한다 ( x 는 라디안)

mysql> select cos(pi());

-> -1.000000<
 


br> 
sin(x)

x 의 사인 값을 구한다 ( x 는 라디안)

mysql> select sin(pi());

-> 0.000000
 




tan(x)

x 의 탄젠트 값을 구한다 ( x 는 라디안)

mysql> select tan(pi()+1);

-> 1.557408
 




acos(x)

x 의 아크 코사인 값을 구한다 ( x 가 -1 ~ 1 사이의 값이 아니면 null 리턴 )

mysql> select acos(1);

-> 0.000000

mysql> select acos(1.0001);

-> null

mysql> select acos(0);

-> 1.570796
 




asin(x)

x 의 아크 사인 값을 구한다 ( x 가 -1 ~ 1 사이의 값이 아니면 null 리턴 )

mysql> select asin(0.2);

-> 0.201358

mysql> select asin('foo');

-> 0.000000
 




atan(x)

x 의 아크 탄젠트 값을 구한다 

mysql> select atan(2);

-> 1.107149

mysql> select atan(-2);

-> -1.107149

atan2(x,y)
 




x 와 y 사이의 아크 탄젠트 값을 구한다. atan(y / x) 와 같다

mysql> select atan(-2,2);

-> -0.785398

mysql> select atan(pi(),0);

-> 1.570796

cot(x)
 




x의 코탄젠트 값을 구한다

mysql> select cot(12);

-> -1.57267341

mysql> select cot(0);

-> null

rand()

rand(n)
 




0 과 1.0 사이의 난수를 만들어 낸다.

n이 주어질 경우 난수 발생을 위한 시드(seed) 로 사용된다.

mysql> select rand();

-> 0.5925

mysql> select rand(20);

-> 0.1811

mysql> select rand(20);

-> 0.1811

mysql> select rand();

-> 0.2079

mysql> select rand();

-> 0.7888
 




least(x,y,...)

주어진 인수 중 가장 작은 것을 찾아낸다. 

mysql> select least(2,0);

-> 0

mysql> select least(34.0,3.0,5.0,762.0);

-> 3.0

mysql> select least("b","a","c");

-> "a"
 




greatest(x,y,...)

주어진 인수 중 가장 작은 큰 것을 찾아낸다. 

mysql> select greatest(2,0);

-> 2

mysql> select greatest(34.0,3.0,5.0,762.0);

-> 762.0

mysql> select greatest("b","a","c");

-> "c"

in mysql versions prior to 3.22.5, you can use max() instead of greatest.

degrees(x)

returns the argument x, converted from radians to degrees.

mysql> select degrees(pi());

-> 180.000000
 




radians(x)

도(degree)를 라디안 값으로 변환한다.

mysql> select radians(90);

-> 1.570796
 




truncate(x,d)

숫자 x를 소수점 아래 d자리까지만 보여준다

mysql> select truncate(1.223,1);

-> 1.2

mysql> select truncate(1.999,1);

-> 1.9

mysql> select truncate(1.999,0);

-> 1
 




9) 문자열 함수

문자열 함수는 결과값의 길이가 max_allowed_packet 에서 정의된 것보다 클 경우 null 을 리턴한다.

문자열의 위치를 다루는 함수에서 첫번째 문자열의 위치는 1로 처리한다.

ascii(str)

문자열 str의 가장 좌측에 위치한 문자의 ascii 코드값을 알려준다. 문자열이 비어있으면 0 을, 인수가 null이면 null을 리턴한다.

mysql> select ascii('2');

-> 50

mysql> select ascii(2);

-> 50

mysql> select ascii('dx');

-> 100
 




conv(n,from_base,to_base)

숫자 n을 서로 다른 진수로 변환시킨 후 문자열로 출력한다. from_base 진수형식으로부터 to_base 진수형식으로 변환된다. 인수 중 하나라도 null 이 있으면 null이 리턴 된다. 2진수부터 36진수까지 지원된다

mysql> select conv("a",16,2);

-> '1010'

mysql> select conv("6e",18,8);

-> '172'

mysql> select conv(-17,10,-18);

-> '-h'

mysql> select conv(10+"10"+'10'+0xa,10,10);

-> '40'
 




bin(n)

10진수 을 2진수 형식으로 바꿔준다. conv(n,10,2) 와 같다.만일 n이 null이면 null을 리턴한다.

mysql> select bin(12);

-> '1100'
 




oct(n)

10진수 을 8진수 형식으로 바꿔준다. conv(n,10,8) 와 같다.만일 n이 null이면 null을 리턴한다.

mysql> select oct(12);

-> '14'
 




hex(n)

10진수 n을 16진수 형식으로 바꿔준다. conv(n,10,2) 와 같다.만일 n이 null이면 null을 리턴한다.

mysql> select hex(255);

-> 'ff'
 




char(n,...)

char() 정수 n 에 해당하는 ascii 코드 값 변환해 출력한다. null 은 무시된다.

mysql> select char(77,121,83,81,'76');

-> 'mysql'

mysql> select char(77,72.3,'72.3');

-> 'mmm'
 




concat(x,y,...)

문자열을 이어준다. 인수가 2개 이상 되어야 하며 하나라도 null 이 있으면 null 이 리턴 된다.

mysql> select concat('my', 's', 'ql');

-> 'mysql'

mysql> select concat('my', null, 'ql');

-> null
 




length(str)

octet_length(str)

char_length(str)

character_length(str)

문자열str의 길이를 리턴한다.

mysql> select length('text');

-> 4

mysql> select octet_length('text');

-> 4
 




locate(substr,str)

position(substr in str)

문자열 str 에서 처음으로 나타나는 문자열 substr 의 위치를 리턴한다. 만일 substr을 찾지 못한 경우 0을 리턴한다.

mysql> select locate('bar', 'foobarbar');

-> 4

mysql> select locate('xbar', 'foobar');

-> 0
 




lpad(str,len,padstr)

len 길이만큼 padstr 문자로 str의 왼쪽을 채워서 보여준다

mysql> select lpad('hi',4,'??');

-> '??hi'
 




rpad(str,len,padstr)

len 길이만큼 padstr 문자로 str의 오른쪽을 채워서 보여준다

mysql> select rpad('hi',5,'?');

-> 'hi???'
 




left(str,len)

문자열str 을 왼쪽에서len 길이만큼만 보여준다.

mysql> select left('foobarbar', 5);

-> 'fooba'
 




right(str,len)

substring(str from len)

문자열str 을 오른쪽에서len 길이만큼만 보여준다.

mysql> select right('foobarbar', 4);

-> 'rbar'

mysql> select substring('foobarbar' from 4);

-> 'rbar'
 




substring(str,pos,len)

substring(str from pos for len)

mid(str,pos,len)

문자열str 을 pos 위치에서len 길이만큼만 보여준다.

mysql> select substring('quadratically',5,6);

-> 'ratica'
 




substring(str,pos)

문자열str 을 pos 위치에서 끝까지 보여준다.

mysql> select substring('quadratically',5);

-> 'ratically'
 




substring_index(str,delim,count)

문자열 str 을 delim 로 구분해서 배열로 만든 후 count 만큼만 보여준다. count 가 양수이면 왼쪽에서 count 수만큼 보여주고 음수이면 오른쪽에서 count 수 만큼 보여준다.

mysql> select substring_index('http://www.mysql.com', '.', 2);

-> 'www.mysql'

mysql> select substring_index('http://www.mysql.com', '.', -2);

-> 'mysql.com'
 




ltrim(str)

문자열 str 의 왼쪽 공백을 제거한다.

mysql> select ltrim(' barbar');

-> 'barbar'
 




rtrim(str)

문자열 str 의 오른쪽 공백을 제거한다.

mysql> select rtrim('barbar ');

-> 'barbar'
 




trim([[both | leading | trailing] [remstr] from] str)

옵션에 따라 문자열에서 공백을 제거 한다.

both 앞뒤 공백제거

leading 앞쪽 공백제거

trailing 뒤쪽 동백제거

mysql> select trim(' bar ');

-> 'bar'

mysql> select trim(leading 'x' from 'xxxbarxxx');

-> 'barxxx'

mysql> select trim(both 'x' from 'xxxbarxxx');

-> 'bar'

mysql> select trim(trailing 'xyz' from 'barxxyz');

-> 'barx'
 




space(n)

n 개의 공백 문자열을 리턴한다.

mysql> select space(6);

-> ' '
 




replace(str,from_str,to_str)

문자열 str 에서 문자열 from_str을 문자열to_str로 치환한다.

mysql> select replace('http://www.mysql.com', 'w', 'ww');

-> 'wwwhttp://www.mysql.com'
 




repeat(str,count)

문자열 str 을 count 수만큼 반복한다.

mysql> select repeat('mysql', 3);

-> 'mysqlmysqlmysql'
 




reverse(str)

문자열의 순서를 뒤집는다.

mysql> select reverse('abc');

-> 'cba'
 




insert(str,pos,len,newstr)

문자열 str 의 특정위치 pos 부터 len 길이만큼 새로운 문자열로 바꿔 넣는다.

mysql> select insert('quadratic', 3, 4, 'what');

-> 'quwhattic'
 




elt(n,str1,str2,str3,...)

문자열의 목록에서 n 번째 문자열을 리턴한다.

mysql> select elt(1, 'ej', 'heja', 'hej', 'foo');

-> 'ej'

mysql> select elt(4, 'ej', 'heja', 'hej', 'foo');

-> 'foo'
 




field(str,str1,str2,str3,...)

문자열 str 이 뒤따라오는 문자열의 목록에서 몇 번째 위치하는지 알려준다.

mysql> select field('ej', 'hej', 'ej', 'heja', 'hej', 'foo');

-> 2

mysql> select field('fo', 'hej', 'ej', 'heja', 'hej', 'foo');

-> 0
 




lcase(str)

lower(str)

문자열을 컴파일 시 지정된 문자 셋에 따라 소문자로 변환한다. (디폴트는 iso-8859-1 latin1 이다).

mysql> select lcase('quadratically');

-> 'quadratically'
 




ucase(str)

upper(str)

문자열을 컴파일 시 지정된 문자 셋에 따라 대문자로 변환한다. (디폴트는 iso-8859-1 latin1 이다).

mysql> select ucase('hej');

-> 'hej'
 




load_file(file_name)

파일을 읽어 파일의 내용을 문자열로 리턴한다. 파일은 같은 서버에 존재해야 하면 반드시 절대 경로를 적어주어야 한다. 또한 max_allowed_packet(디폴트로64kbyte)의 길이보다 작아야 한다. 파일이 존재하지 않거나 크기가 이 보다 크면 null이 리턴된다.

아래는 파일을 읽어 이 내용을 blob 컬럼에 업데이트 하는 보기이다.

mysql> update table_name set blob_column=load_file("/tmp/picture") where id=1;
 




위의 예에서 한가지 재미있는 점은 숫자형을 문자열로 바꿔주는 함수를 사용하지 않은 것인데 이는mysql이 필요에 자동적으로 따라 문자열을 숫자로 혹은 그 반대로 변환하기 때문이다. 다음 예를 보면 쉽게 이해가 될 것이다.

mysql> select 1+"1";

-> 2

mysql> select concat(2,' test');

-> '2 test'
 




10) 날짜 및 시간 함수

- 실제 mysql 에서는 다른 데이터베이스에서 제공하지 않는 매우 편리한 함수를 많이 제공하고 있다. 실제로 프로그래밍을 하다 보면 사소한 것까지 배려한 많은 함수에 저절로 감탄사가 나올 때가 있을 것이다.

dayofweek(date)

날자를 한 주의 몇 번째 요일인지를 나타내는 숫자로 리턴한다. (1 = 일요일, 2 = 월요일, ... 7 = 토요일)

mysql> select dayofweek('1998-02-03');

-> 3
 




weekday(date)

날자를 한 주의 몇 번째 요일인지를 나타내는 숫자로 리턴한다. (0 = 월요일, 1=화요일 ... 6 = 일요일)

mysql> select weekday('1997-10-04 22:23:00');

-> 5

mysql> select weekday('1997-11-05');

-> 2
 




dayofmonth(date)

그 달의 몇 번째 날인지를 알려준다. 리턴 값은 1에서 31 사이이다.

mysql> select dayofmonth('1998-02-03');

-> 3
 




dayofyear(date)

한해의의 몇 번째 날인지를 알려준다. 리턴 값은 1에서 366 사이이다.

mysql> select dayofyear('1998-02-03');

-> 34
 




month(date)

해당 날자가 몇 월인지 알려준다. 리턴 값은 1에서 12 사이이다.

mysql> select month('1998-02-03');

-> 2
 




dayname(date)

해당 날자의 영어식 요일이름을 리턴한다.

mysql> select dayname("1998-02-05");

-> 'thursday'
 




monthname(date)

해당 날자의 영어식 월 이름을 리턴한다.

mysql> select monthname("1998-02-05");

-> 'february'그럼 
 




quarter(date)

분기를 리턴한다 (1~ 4)

mysql> select quarter('98-04-01');

-> 2
 




week(date)

week(date,first)

인수가 하나일 때는 해달 날자가 몇 번째 주일인지(0 ~ 52)를 리턴하고 2개일 때는 주어진 인수로 한 주의 시작일을 정해 줄 수 있다. 0이면 일요일을1이면 월요일을 한 주의 시작일로 계산해 몇 번째 주인가 알려준다.

mysql> select week('1998-02-20');

-> 7

mysql> select week('1998-02-20',0);

-> 7

mysql> select week('1998-02-20',1);

-> 8
 




year(date)

년도를 리턴한다.(1000 ~ 9999)

mysql> select year('98-02-03');

-> 1998
 




hour(time)

시간을 알려준다.(0 ~ 23)

mysql> select hour('10:05:03');

-> 10
 




minute(time)

분을 알려준다(0 ~ 59)

mysql> select minute('98-02-03 10:05:03');

-> 5
 




second(time)

초를 알려준다(0 ~ 59)

mysql> select second('10:05:03');

-> 3
 




period_add(p,n)

yymm 또는 yyyymm 형식으로 주어진 달에n 개월을 더한다. 리턴 값은 yyyymm 의 형식이다.

mysql> select period_add(9801,2);

-> 199803
 




period_diff(p1,p2)


yymm 또는 yyyymm 형식으로 주어진 두 기간사이의 개월을 구한다

mysql> select period_diff(9802,199703);

-> 11

date_add(date,interval expr type)

date_sub(date,interval expr type)

adddate(date,interval expr type)

subdate(date,interval expr type)
 




위의 함수들은 날자 연산을 한다. 잘 만 사용하면 꽤나 편리한 함수 들이다. 모두 mysql 3.22 버전에서 새롭게 추가되었다. adddate() 과 subdate() 는 date_add() 와 date_sub()의 또 다른 이름이다.

인수로 사용되는 date 는 시작일을 나타내는 datetime 또는date 타입이다. expr 는 시작일에 가감하는 일수 또는 시간을 나타내는 표현식이다. 

type 값
 
의미
 
사용 예
 

second
 
초
 
seconds
 

minute
 
분
 
minutes
 

hour
 
시간
 
hours
 

day
 
일
 
days
 

month
 
월
 
months
 

year
 
년
 
years
 

minute_second
 
분:초
 
"minutes:seconds"
 

hour_minute
 
시:분
 
"hours:minutes"
 

day_hour
 
일 시
 
"days hours"
 

year_month
 
년 월
 
"years-months"
 

hour_second
 
시 분
 
"hours:minutes:seconds"
 

day_minute
 
일, 시, 분
 
"days hours:minutes"
 

day_second
 
일, 시, 분, 초
 
"days hours:minutes:seconds"
 




아래 예제를 참고하면 쉽게 이해가 갈 것이다. 

mysql> select date_add("1997-12-31 23:59:59",

interval 1 second);

-> 1998-01-01 00:00:00

mysql> select date_add("1997-12-31 23:59:59",

interval 1 day);

-> 1998-01-01 23:59:59

mysql> select date_add("1997-12-31 23:59:59",

interval "1:1" minute_second);

-> 1998-01-01 00:01:00

mysql> select date_sub("1998-01-01 00:00:00",

interval "1 1:1:1" day_second);

-> 1997-12-30 22:58:59

mysql> select date_add("1998-01-01 00:00:00",

interval "-1 10" day_hour);

-> 1997-12-30 14:00:00

mysql> select date_sub("1998-01-02", interval 31 day);

-> 1997-12-02
 




to_days(date)

주어진 날자를 0000년부터의 일수로 바꾼다.

mysql> select to_days(950501);

-> 728779

mysql> select to_days('1997-10-07');

-> 729669
 




from_days(n)

주어진 일수 n로부터 날자를 구한다

mysql> select from_days(729669);

-> '1997-10-07'
 




date_format(date,format)

format 의 정의에 따라 날자 혹은 시간을 출력한다. 매우 빈번히 쓰이는 함수 이다.

format 에 사용되는 문자는 다음과 같다.



문자
 
의미
 

%m
 
월이름 (january..december)
 

%w
 
요일명 (sunday..saturday)
 

%d
 
영어식 접미사를 붙인 일(1st, 2nd, 3rd, etc.)
 

%y
 
4자리 년도
 

%y
 
2자리 년도
 

%a
 
짧은 요일명(sun..sat)
 

%d
 
일(00..31)
 

%e
 
일(0..31)
 

%m
 
월(01..12)
 

%c
 
월(1..12)
 

%b
 
짧은 월이름 (jan..dec)
 

%j
 
한해의 몇 번째 요일인가 (001..366)
 

%h
 
24시 형식의 시간 (00..23)
 

%k
 
24시 형식의 시간 (0..23)
 

%h
 
12시 형식의 시간 (01..12)
 

%i
 
12시 형식의 시간 (01..12)
 

%l
 
시간 (1..12)
 

%i
 
분 (00..59)
 

%r
 
시분초12시 형식 (hh:mm:ss [ap]m)
 

%t
 
시분초 24시 형식 (hh:mm:ss)
 

%s
 
초 (00..59)
 

%s
 
초 (00..59)
 

%p
 
am 또는 pm 문자
 

%w
 
일주일의 몇 번째 요일인가(0=sunday..6=saturday)
 

%u
 
한해의 몇 번째 주인가(0..52). 일요일이 시작일
 

%u
 
한해의 몇 번째 주인가(0..52). 월요일이 시작일
 

%%
 
`%' 문자를 나타냄
 




위 표에 나와 있는 것들을 제외한 모든 문자는 그냥 그대로 출력된다. 

mysql> select date_format('1997-10-04 22:23:00', '%w %m %y');

-> 'saturday october 1997'

mysql> select date_format('1997-10-04 22:23:00', '%h:%i:%s');

-> '22:23:00'

mysql> select date_format('1997-10-04 22:23:00',

'%d %y %a %d %m %b %j');

-> '4th 97 sat 04 10 oct 277'

mysql> select date_format('1997-10-04 22:23:00',

'%h %k %i %r %t %s %w');

-> '22 22 10 10:23:00 pm 22:23:00 00 6'
 




주의! : mysql 3.23 버전부터 % 기호가 각 형식문자 앞에 필요하게 되었다 그 이전 버전에서는 선택 사항이다.

time_format(time,format)

 



이 함수는 date_format()와 비슷한 역할을 하지만 단지 시,분,초 만을 나타낼 수 있다는 점이다.

curdate()

current_date

 



오늘 날짜를 'yyyy-mm-dd' 또는 yyyymmdd 형식으로 리턴한다, 리턴 값은 이 함수가 문자열로 쓰이느냐 숫자로 쓰이느냐에 따라 달라진다.

mysql> select curdate();

-> '1997-12-15'

mysql> select curdate() + 0;

-> 19971215

 



curtime()

current_time

'hh:mm:ss' 또는 hhmmss 형식으로 현재시간을 나타낸다. 리턴 값은 이 함수가 문자열로 쓰이느냐 숫자로 쓰이느냐에 따라 달라진다.

mysql> select curtime();

-> '23:50:26'

mysql> select curtime() + 0;

-> 235026

 



now()

sysdate()

current_timestamp

오늘 날자와 현재 시간을 'yyyy-mm-dd hh:mm:ss' 또는 yyyymmddhhmmss 형식으로 리턴 한다, 역시 리턴 값은 이 함수가 문자열로 쓰이느냐 숫자로 쓰이느냐에 따라 달라진다. 실제 개발 시 사용자의 등록일시 등을 나타낼 때 유용하게 쓰이는 함수다. 뒷부분의 실전예제에서 보게 될 것이다.

mysql> select now();

-> '1997-12-15 23:50:26'

mysql> select now() + 0;

-> 19971215235026

 



unix_timestamp()

unix_timestamp(date)

인수가 없이 사용될 경우 현재 시간의 유닉스 타임스탬프를 리턴하고 

만일 날짜형식의 date 가 인수로 주어진 경우에는 주어진 날자의 유닉스 타임스탬프를 리턴한다 유닉스 타임스탬프 란 그리니치 표준시로 1970 년 1월 1일 00:00:00 이 후의 시간경과를 초단위로 나타낸 것이다.

mysql> select unix_timestamp();

-> 882226357

mysql> select unix_timestamp('1997-10-04 22:23:00');

-> 875996580

 



주의 : 만일 unix_timestamp함수가 timestamp 컬럼 에서 사용될 경우에는 주어진 시간이 타임스탬프로 바뀌지 않고 그대로 저장된다. 

from_unixtime(unix_timestamp)

주어진 유닉스 타임스탬프 값으로부터 'yyyy-mm-dd hh:mm:ss' 또는 yyyymmddhhmmss 형식의 날짜를 리턴한다.

mysql> select from_unixtime(875996580);

-> '1997-10-04 22:23:00'

mysql> select from_unixtime(875996580) + 0;

-> 19971004222300

 



from_unixtime(unix_timestamp,format)

주어진 유닉스 타임스탬프 값을 주어진 날짜 형식에 맞게 바꿔서 보여준다. 여기서 사용되는 형식문자는 date_format() 함수에서 사용된 것과 같다.

아래 예에서 %x 는 형식문자가 아니므로 그냥 x 가 표시됨에 유의하기 바란다.

mysql> select from_unixtime(unix_timestamp(),

'%y %d %m %h:%i:%s %x');

-> '1997 23rd december 03:43:30 x'

 



sec_to_time(seconds)

주어진 초를 'hh:mm:ss' 또는 hhmmss 형식의 시간단위로 바꿔준다.

mysql> select sec_to_time(2378);

-> '00:39:38'

mysql> select sec_to_time(2378) + 0;

-> 3938

 



time_to_sec(time)

주어진 시간을 초 단위로 바꿔준다.

mysql> select time_to_sec('22:23:00');

-> 80580

mysql> select time_to_sec('00:39:38');

-> 2378

 



11) 기타 함수

database()

현재 사용중인 데이터베이스 이름을 리턴 한다.

mysql> select database();

-> 'test'

 



만일 사용중인 데이터베이스가 없을 경우 빈 문자열이 리턴 된다.

if there is no current database, database() returns the empty string.

 



user()

system_user()

session_user()

현재 접속중인 mysql 사용자 이름을 리턴한다.

mysql> select user();

-> 'davida@localhost'

 



잠깐

3.22.11 이전 버전에서는 사용자 이름과 호스트이름이 함께 리턴되었으며 이경우 다음의 방법으로 사용자 명을 분리 해 낼수 있다.

mysql> select left(user(),instr(user(),"@")-1);

-> 'davida'
 




password(str)

일반 문자열을 암호화 한다. 이 함수는 mysql 서버가 사용자 권한을 설정하는 테이블인 user 테이블에 사용자의 암호를 입력할 때 사용된다,

mysql> select password('badpwd');

-> '7f84554057dd964b'

 



password() 함수를 통한 암호화는 단방향적이다. 즉 한번 password() 함수를 통해 암호화된 문자열을 다시 해독하는 함수는 존재 하지 않는다. 따라서 주어진 사용자의 암호를 암호화 한후 기존의 암호화된 문자열과 비교하는 방법을 통해 비교가 이루어 진다.

encrypt(str[,salt])

이 함수는 유닉스 시스템의 crypt() 시스템 함수를 이용해 주어진 문자열을 암호화 한다. 암호화에 사용되는 salt가 주어질 경우 반드시 2문자가 되어야 한다.

mysql> select encrypt("hello");

-> 'vxufajxvarroc'

 



만일 시스템에 crypt() 함수가 없다면 항상 null 값이 리턴 될 것이다. 또한 encrypt() 함수는 시스템에 따라 주어진 문자열의 앞부분 8 자 까지만 암호화 한다. 

encode(str,pass_str)

주어진 pass_str 문자열을 이용해 문자열str 을 암호화한다. 해독하기 위해서 decode() 함수를 사용하면 된다. 결과 값이 바이너리 문자열로 리턴 되므로 이 값을 저장하기 위해서는 blob 컬럼 타입을 이용해야 한다.

decode(crypt_str,pass_str)

encode()함수를 통해 리턴된 암호화 된 문자열 crypt_str 을 해독한다, 이 때 pass_str 는 반드시 이전에 암호화 할 때에 사용된 문자열 이어야 한다.

last_insert_id([expr])

auto_increment 속성으로 정의된 컬럼에 마지막으로 입력된 값을 리턴한다. 개발 시 상당히 유용하게 사용된다

mysql> select last_insert_id();

-> 195

 



format(x,d)

숫자 x를 '#,###,###.##' 와 같은 형식으로 표현한다.

d 는 소수점 이하 자릿수를 나타낸다. 만일 d가 0 일 경우는 소수점 이하부분을 나타나지 않는다.

mysql> select format(12332.1234, 2);

-> '12,332.12'

mysql> select format(12332.1,4);

-> '12,332.1000'

mysql> select format(12332.2,0);

-> '12,332'

 



version()

mysql 서버의 버전을 표시한다.

mysql> select version();

-> '3.22.19b-log'

 



benchmark(count,expr)

이 함수는 주어진 sql 문이나 연산을 count 에 정의된 수 만큼 반복해서 실행한다. 서버가 얼마나 빠르게 주어진 구문을 처리하는지 알 수 있다. 결과 값은 항상 0 이다. 수행시간은 아래부분에 따로 표시된다.



mysql> select benchmark(1000000,encode("hello","goodbye"));

+----------------------------------------------+

| benchmark(1000000,encode("hello","goodbye")) |

+----------------------------------------------+

| 0 |

+----------------------------------------------+

1 row in set (4.74 sec)

 



수행 시간은 절대적인 cpu 쪽의 시간이 아닌 클라이언트 쪽의 결과로 사용자의 부하의 영향을 받는다. 따라서 여러 차례 반복해서 실행해 볼 것을 권하며 바꿔어 말하면 현재 서버에 걸리는 부하를 측정할 수도 있다.

12) group 함수

다음 함수들은 group by절에서 사용되며 조건에 맞는 모들 행을 대상으로 한다.

count(expr)

select문에서 null이 아닌 행의 개수를 구한다,

mysql> select student.student_name,count(*)

from student,course

where student.student_id=course.student_id

group by student_name;

 



잠깐

count(*) 은 최적화 되어있어 select 문이 자료를 하나의 테이블에서만 가져올 때나 where 조건절이 없을 경우 매우 빠르게 결과 값을 가져온다. 다음 예가 그런 경우이다

mysql> select count(*) from student;


avg(expr)

주어진 컬럼의 평균값을 구한다

mysql> select student_name, avg(test_score)

from student

group by student_name;
 




min(expr)

max(expr)

최소값 혹은 최대값을 구한다. 문자열 컬럼에도 사용될 수 있다.

mysql> select student_name, min(test_score), max(test_score)

from student

group by student_name;
 




sum(expr)

컬럼의 합계를 구한다,

std(expr)
 




stddev(expr)

컬럼의 표준 편차(standard deviation)를 구한다

ansi sql에서 확장된 함수이며stddev()는 오라클과의 호환을 위해 존재한다.

이상으로 mysql 에서 지원하는 다양한 함수들을 살펴 보았다. 앞서 말한 바와 같이 실제 개발에 들어가면 mysql 이 다른 데이터베이스에서는 지원하지 않는 편리하고 유용한 함수들을 상당수 지원하고 있음을 알 수 있다. 한마디로 프로그래머를 위한 데이터베이스라고 할 수 있다. 일부 초보자들의 경우 minisql 과의 비교를 원하는 경우가 있는데 지원하는 함수와 편리한 유틸리티 등으로 인해 당연히 mysql 이 우위에 있다고 말할 수 있겠다.