mysql의 컬럼은 종류가 다양합니다. 그래서 보기 쉽게 아래 표를 만들어 보았습니다.
Type Option (B)-최대표시, (F)-소수점이하자릿수
———————————————————————————————————————
TINYINT 정수형(-128~127), (B), [UNSIGNED]-정수형(0~255)
SMALLINT 정수형(-32768~32767), (B), [UNSIGNED]-정수형(0~65535)
MEDIUMINT 정수형(-8388606~8388607), (B), [UNSIGNED]-정수형(0~16777215)
INT 정수형(-2147483648~2147483647), (B), [UNSIGNED]-정수형(0~4294967295)
INTEGER INT와 동일
BIGINT 정수형(-9223372036854775808~9223372036854775807), (B),
[UNSIGNED]-정수형(0~18446744073709551615)
FLOAT(정밀도) 부동소수점실수, (정밀도)-“(4,8)”, (4)-단정도
부동소수점실수, (8)-배정도 부동소수점실수, 범위는 FLOAT, DOUBLE과 같습니다.
FLOAT(L, F) 단정도 부동소수점 실수, (B,F), (-3.402823466E+38 ~ 1.175494351E-38, 0,
1.175494351E-38 ~ 3.402823466E+38)
DOUBLE 배정도 부동소수점 실수, (B,F), (-1.7976931348623157E+308 ~ -2.2250738585072014E-308,0,
2.2250738585072014E-308 ~ 1.7976931348623157E+308)
DOUBLE PRECISION~REAL PRECISION(B,F), REAL(B,F) DOUBLE와 동일
DECIMAL 부동 소수점 실수 CHAR 형태로 동작, (B,F), F가 0이면 소수점이하는 저장되지 않습니다.
범위는 DOUBLE와 같습니다.
NUMERIC ECIMAL과 동일
DATE 날짜형(1000-01-01 ~ 9999-12-31)
기본타입 – YYYY-MM-DD
DATETIME 날짜와 시간형(1000-01-01 00:00:00 ~ 9999-12-31 23:59:59)
기본타입 – YYYY-MM-DD HH:MM:SS
TIMESTAMP 타임스템프형(1970-01-01 ~ 2037년 임의 시간),
(B) – (14,12,8,6) B 값이 없을 경우 INSERT, UPDATE시 동작된 시간으로 자동적으로 저장됩니다.
기본형식 – YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD,YYMMDD
TIME 시간형(-838:59:59 ~ 838:59:59) 기본형식 – HH:MM:SS
YEAR 년도형(1901 ~ 2155, 0000)
CHAR 고정폭 문자열, (B) – (1~255) B 만큼 오른쪽으로 공백 채워 저장 출력 시 공백은 출력안됨,
[BINARY] – 검색 시 대소문자 구분
VARCHAR 가변폭 문자열, (L) – (1 ~ 255) 문자열 공백이 제거된 후 저장,
[BINARY] – 검색 시 대소문자 구분
TINYBLOB / TINYTEXT BOLB, TEXT형, 최대길이 255문자
BLOB / TEXT BOLB, TEXT형, 최대길이 65535문자
MEDIUMBLOB / MEDIUMTEXT BOLB, TEXT형, 최대길이 16777215문자
LONGBLOB / LONGTEXT BOLB, TEXT형, 최대길이 4294967295문자
ENUM 문자열 목록형, 최대 65535개, 저장된 문자열 목록 중에 오직 한가지만 얻을 수 있습니다.
SET 문자열 목록형, 최대 64개, 저장된 문자열 목록 중에 0, 1개 이상을 얻을 수 있습니다.
———————————————————————————————————————
10. MYSQL 주요함수
여기서는 mysql에 사용되는 중요한 몇 가지 기본 함수들을 소개하겠습니다. 대부분의 함수가 php와 연관하여 비슷한 것들이 많이 있습니다. php에서 만약 이 함수를 사용하려면 mysql query 문을 이용하는 방법도 있습니다. (여기서의 설명은 직접 local에서 접속해서 하는 것보다 클라이언트에서 telnet으로 접속해서 사용한 예를 더 많이 들었습니다.)
10.1 연산자
연산자의 경우 일반적으로 사용하는 연산자들을 사용합니다. “+” , “-” , “*” , “/” 등 입니다. 그럼 각각의 수행 결과를 확인 하겠습니다.
그림. 10-1 + * 연산자 예
위에서 “( )” 사용없이 연산을 했기 때문에 우선 순위가 위인 연산자부터 연산이 이루어 졌습니다.
만약, (1+4)*4로 했다면 결과는 20이 될 것입니다.
그림. 10-2 / – 연산자 예
위와 다르게 (4-2) 가 (4-4)일 경우 “null” 을 출력합니다.
10.2 비교 연산자
비교 연산자는 결과가 참(1) 과 거짓(0)으로 결과를 출력합니다.
그리고 비교 연산자의 경우 몇 가지 규칙이 있습니다.
~ 인수가 모두 문자열이면 문자열로 비교됩니다.
~ 인수가 모두 정수면 정수로 비교됩니다.
~ 인수가 모두 “null” 이면 연산의 결과도 “null”이 됩니다.
~ 한쪽의 인수가 “timestamp” 이면 나머지도 같이 변환되어 비교됩니다.
~ 한쪽의 인수가 “datetime” 이면 나머지도 같이 변환되어 비교됩니다.
~ 나머지의 경우는 부동소수점 실수로 비교됩니다.
기호로는
“=” – 같다
“!= ,<>” – 같지 않다
“<=” – 작거나 같다
“<” – 작다
“>=” – 크거나 같다
“>” – 크다
“<=>” – 둘 중 한쪽이 “null” 일 경우 0을 출력
인자 between (최소값) and (최대값) – 인자가 최소값과 최대값 사이에 존재 하면 “1”을 그렇지
않을 경우 “0”을 출력합니다.
인자 in (값, 값, …) – 인자가 ( ) 안에 존재하면 “1”을 그렇지 않으면 “0”을 출력합니다.
그림. 10-3 비교 연산자 !=
그림. 10-4 비교 연산자 >=
위 그림은 결과가 거짓 이므로 “0”을 출력 합니다.
그림. 10-5 비교 연산자 between
그림. 13-5의 결과는 참이므로 “1”을 리턴합니다.
그림. 10-6 비교 연산자 in
“jun” 항목 안에 없기 때문에 거짓이므로 “0”을 리턴합니다.
10.3 날짜와 시간 관련 함수
날짜와 시간 관련 함수는 여러모로 사용하는 곳이 많은 부분입니다. 대부분의 표현에서 날짜와 시간은 꼭 들어가기 때문에 이 부분을 먼저 다루도록 하겠습니다.
MONTHNAME(“날짜”) – 해당하는 날짜의 월을 영어로 리턴합니다.
그림. 10-7 MONTHNAME
QUARTER(“날짜”) – 해당하는 날짜의 분기를 리턴해 줍니다.
그림. 10-8 QUARTER
YEAR(“날짜”) – 해당 날짜의 년도를 리턴해 줍니다.
그림. 10-9 YEAR
HOUR(“시간”) – 해당하는 시간을 리턴합니다.
그림. 10-10 HOUR
MINUTE(“시간”) – 해당 시간의 분을 리턴합니다.
그림. 10-11 MINUTE
SECOND(“시간”) – 해당 시간의 초를 리턴합니다.
그림. 10-12 SECOND
PERIOD_ADD(날짜, N) – 해당하는 날짜에의 개월에 “N”개월을 더 합니다.
(날짜는 YYMM, YYYYMM형식으로 주어지면 YYYYMM 형식으로 리턴합니다.)
그림. 10-13 PERIOD_ADD
CURDARE( ) – 오늘 날짜를 YYYY-MM-DD 또는 YYYYMMDD 형식으로 리턴해 줍니다.
함수가 문자열 또는 숫자로 사용됨에 따라 리턴 값은 달라 집니다.
그림. 10-14 CURDARE 예 1
그림. 10-15 CURDARE 예 2
CURTIME( ) – 현재 시간을 HH:MM:SS 또는 HHMMSS 형식으로 리턴해 줍니다.
이 함수 역시 함수가 문자열 또는 숫자로 사용됨에 따라 리턴 값이 달라 집니다.
그림. 10-16 CURTIME 예 1
그림. 10-17 CURTIME 예 2
NOW( ) – 현재의 날짜와 시간을 리턴합니다.
그림. 10-18 NOW 예 1
그림. 10-19 NOW 예 2
UNIX_TIMESTAMP( ) – 유닉스 타임스탬프를 리턴합니다.
날짜 인자가 있을 경우 해당 날짜의 유닉스 타임스탬프를 리턴하고, 인자가 없을 경우
현재의 유닉스 타임스탬프를 리턴합니다.(초 단위로 나타냅니다.)
그림. 10-20 UNIX_TIMESTAMP 예 1
그림. 10-21 UNIX_TIMESTAMP 예 2
FROM_UNIXTIME (유닉스 타임스탬프) – 유닉스 타임스탬프 날짜에서 일반 형식의 날짜와 시간으로 리턴합니다.
이 함수를 이용해서 원하는 데이터 형태로도 출력이 가능합니다.
(DATE_FORMAT( ) 함수는 날짜와 시간을 여러 가지 형태로 표현 가능하게
합니다. 다음 함수를 참고 하세요.)
그림. 10-22 FROM_UNIXTIME 예 1
그림. 10-23 FROM_UNIXTIME 예 2
그림. 10-24 FROM_UNIXTIME 예 3
DATE_FORMAT(날짜, 형태) – 형태의 종류에 맞게 여러 가지 양식으로 날짜와 시간을 리턴해 줍니다.
특히 이 함수는 자주 사용 되므로 관심있게 보기 바랍니다.
형태의 종류는….
%M – 월 이름을 영어로 리턴합니다.(January)
%D – 접미사를 사용해 영어로 일을 리턴합니다.(1st , 2nd ..)
%W – 요일을 영어로 리턴합니다. (Monday)
%y – 2자리 연도를 리턴합니다.
%m – 월을 숫자로 리턴합니다.(01 , 02 , 03)
%d – 일을 숫자로 리턴합니다. (00 , 01 ,02 )
%a – 요일을 짧은 영어로 리턴합니다. (Mon)
%e – 일을 숫자로 리턴합니다.(0 , 1 , 2)
%c – 월을 숫자로 리턴합니다. (1 , 2 , 3)
%j – 한해의 몇 번째 요일인지 리턴합니다. (001 ~ 366)
%b – 월을 짧은 영어로 리턴합니다. (Jan)
%H – 24시간 형식의 시간을 리턴합니다. (00 ~ 23 )
%h – 12시간 형식의 시간을 리턴합니다. (01 ~ 12)
%k – 24시간 형식의 시간을 리턴합니다. (1 ~ 23)
%l – 시간을 리턴합니다. (1 ~ 12)
%i – 분을 리턴합니다. (00 ~ 59)
%T – 시분초의 24시간 형식을 리턴합니다. (hh:mm:ss)
%r – 시분초의 12시간 형식을 리턴합니다. (hh:mm:ss)
%s – 초를 리턴합니다. (00 ~ 59)
%p – AM , PM을 리턴합니다.
%w – 일주일 중 몇 번째 요일인지 리턴합니다.(0 – 일요일)
%U – 한해 중 몇 번째 주인지 리턴합니다.(일요일이 시작)
%u – 한해 중 몇 번째 주인지 리턴합니다.(월요일이 시작)
그럼 여러 가지 형태의 데이터 출력을 보겠습니다.
그림. 10-25 DATE_FORMAT 예 1
그림. 10-26 DATE_FORMAT 예 2
TO_DAYS(날짜) – 해당 날짜를 “0000” 부터 일수로 리턴해 줍니다.
그림. 10-27 TO_DAYS 예 1
그림. 10-28 TO_DAYS 예 2
FROM_DAYS(일수) – 해당 일수로부터 날짜를 리턴해 줍니다.
그림. 10-29 FROM_DAYS 예 1
그림. 10-30 FROM_DAYS 예 2
DATE_ADD(날짜,INTERVAL,추가분) – 해당 날짜와 시간에 특정 날짜와 시간을 더해줍니다.
그림. 10-31 DATE_ADD 예 1
위 그림을 보면 하루가 추가된 것을 볼 수 있습니다.
여기에서 추가분의 종류에 대해 알아 보고 계속 예를 보여 드리겠습니다.
second – 초를 추가 합니다(interval 1 second)
minute – 분을 추가 합니다.(interval 1 minute)
hour – 시간을 추가 합니다.(interval 1 hour)
day – 일을 추가 합니다.(interval 1 day)
month – 달을 추가 합니다. .(interval 1 month)
year – 년을 추가 합니다. .(interval 1 year)
minute_second – 분과 초를 추가 합니다. (interval “1:1” minute_second)
hour_minute – 시간과 분을 추가 합니다. (interval “1:1” hour_minute)
day_hour – 일과 시간을 추가 합니다. (interval “1 1” day_hour)
year_month – 년과 월을 추가 합니다. (interval “1-1” year_month)
hour_second – 시간과 분, 초를 추가 합니다. (interval “1:1:1” hour_second)
day_minute – 일과 시간, 분을 추가 합니다.(interval “1 1:1” day_minute)
day_second – 일과 시간, 분, 초를 추가 합니다.(interval “1 1:1:1″ day_second)
그림. 10-32 DATE_ADD 예 2
그림. 10-33 DATE_ADD 예 3
만약 날짜와 시간을 빼기를 원한다면 ” – “를 사용하면 됩니다. 또 다른 방법은 DATE_SUB( ) 함수를
사용하는 것입니다.
그림. 10-34 DATE_ADD 예 4
그림. 10-35 DATE_ADD 예 5
PERIOD_DIFF(날짜1, 날짜2) – 날짜1 과 날짜2 사이의 개월 수를 리턴합니다.
그림.10-36 PERIOD_DIFF
10.4 문자열 관련 함수
php 스크립트 프로그래밍을 하면서 php에서 지원하는 함수를 이용한 방법으로 많은 문자열 처리하게 될 것입니다. Mysql 역시 문자열을 처리하는 여러 가지 함수가 있습니다. 여러분들은 데이터를 데이터베이스에 저장할 때 php 함수에서 지원하는 함수를 사용할 수도 있지만 여기서 설명할 mysql 함수를 사용해도 됩니다. (문자열 함수는 결과 값이 정의된 길이보다 클 경우 NULL을 리턴합니다.)
HEX(n) – 해당 10진수를 16진수로 리턴해 줍니다. (NULL은 NULL로 리턴)
그림. 10-37 HEX
OCT(n) – 해당 10진수를 8진수로 리턴해 줍니다.(NULL -> NULL로 리턴)
그림. 10-38 OCT
BIN(n) – 해당 10진수를 2진수로 리턴합니다. (NULL -> NULL로 리턴)
그림. 10-39 BIN
CONV(n , a , b) – 해당 숫자를 a 형식의 진수에서 b 형식의 진수로 변환해서 리턴합니다.
앞에서 설명한 각 진수별 변환 방법의 기능을 다 가지고 있는 함수입니다.
(인자중 NULL이 있으면 NULL을 리턴합니다. 2 ~36진까지 가능)
그림. 10-40 CONV 예 1
그림. 10-41 CONV 예 2
ASCII(문자열) – 해당 문자열의 처음 위치의 ASCII 코드를 리턴합니다.(NULL -> NULL로 리턴 합니다.)
그림. 10-42 ASCII
FIELD(Nstring , string , …) – Nstring에 해당하는 문자열이 몇 번째 인지 리턴합니다.
그림. 10-43 FIELD
LOWER(문자열) – 해당 문자열을 소문자로 변환해서 리턴합니다.(컴파일 시 선택한 문자 설정)
그림. 10-44 LOWER
UPPER(문자열) – 해당 문자열을 대문자로 변환해서 리턴합니다.
(컴파일 시 선택한 문자 설정)
그림. 10-45 UPPER
LOAD_FILE(파일명) – 64kb 보다 작은 내용의 파일을 읽어 들여 문자열로 리턴합니다.
(64kb보다 클 경우 NULL을 리턴합니다.)
그림. 10-46 LOAD_FILE
SPACE(n) – 해당 인자의 수 만큼 공백을 리턴합니다.
그림. 10-47 SPACE
REVERSE(문자열) – 해당 문자열의 순서를 바꾸어 리턴합니다.
그림. 10-48 REVERSE
INSERT(문자열, a , b , 문자열1) – 해당 문자열을 a 위치부터 b 크기 만큼 문자열1을 넣어 리턴합니다.
그림. 10-49 INSERT
SUBSTRING_INDEX(문자열, a , count ) – 해당 문자열을 a로 구분해서 배열로 만들고 count 수만큼 리턴해
줍니다. 오른쪽부터 출력하길 원하면 음수를 적으면 됩니다.
그림. 10-50 SUBSTRING_INDEX
REPEAT(문자열 , c ) – 해당 문자열을 c 만큼 반복해서 리턴합니다.
그림. 10-51 REPEAT
LTRIM(문자열) – 해당 문자열의 왼쪽 공백을 제거합니다.
그림. 10-52 LTRIM
RTRIM(문자열) – 해당 문자열의 오른쪽 공백을 제거하고 리턴합니다.
그림. 10-53 RTRIM
TRIM(옵션 a FROM 문자열) – 주어진 옵션에 따라 a 문자를 제거하고 리턴합니다.
옵션에는 LEADING | TRAILING | BOTH 등이 있으며 각각 앞쪽 공백제거 | 뒤쪽 공백 제거
| 앞뒤 공백 제거입니다.
그림. 10-54 TRIM 예 1
그림. 10-55 TRIM 예 2
그림. 10-56 TRIM 예 3
CONCAT(문자열, 문자열 …..) – 해당 문자열을 이어 줍니다.
이 함수는 php의 ” . “을 이용한 문자열 연결 방법과 유사 합니다.
(NULL -> NULL로 리턴합니다.)
그림. 10-57 CONCAT
LEFT(문자열,n) – n 만큼 해당 문자열을 왼쪽부터 리턴합니다.
그림. 10-58 LEFT
RIGHT(문자열, n) – n만큼 해당 문자열을 오른쪽부터 리턴합니다.
그림. 10-59 RIGHT
LOCATE(a , b) – a(문자열)가 b(문자열)에서 처음부터 몇 번째 위치인지 리턴합니다.
그림. 10-60 LOCATE
LPAD(문자열 , a , b) – 해당 문자열에 a 길이 만큼 b 문자를 왼쪽부터 넣어 리턴합니다.
그림. 10-61 LPAD
RPAD(문자열, a , b) – 해당 문자열에 a 길이만큼 b 문자를 오른쪽에 넣어 리턴합니다.
그림. 10-62 RPAD
여기까지 문자열 함수의 대부분을 설명했습니다.
몇 가지 빠진 함수들도 있지만 여기 있는 것만으로도 대부분을 표현하기는 충분합니다.
10.5 기타 함수들
mysql의 수학 함수는 여기서 다루지 않도록 하겠습니다. 대부분의 경우 php 함수가 대신 할 수 있기 때문에 구지 mysql 함수를 이용해 수학연산을 할 필요는 없습니다. 하지만 어느 경우든 꼭 사용해야만 할 경우는 mysql 매뉴얼을 보시기 바랍니다. 짧은 영어 실력으로도 충분히 보실 수 있는 매뉴얼이기 때문에 걱정은 필요 없습니다. 그럼, 여기서는 자주 사용되는 나머지 함수들을 설명하겠습니다.
USER() – 현재 mysql에 접속 중인 사용자 이름을 리턴합니다.
그림. 10-63 USER
VERSION( ) – mysql의 버전을 리턴해 줍니다.
그림. 10-64 VERSION
PASSWORD(문자열) – 이 함수는 mysql의 데이터 베이스에 문자열을 암호화 해서 저장해 줍니다.
대부분의 경우 사용자 인증에 이용합니다. 즉, 암호화된 문자열끼리 비교함으로 해서
인증을 하는 것입니다.
뒤에 설명할 admin tool의 사용자 인증 역시 이 방법을 이용했습니다.
그림. 10-65 PASSWORD
이렇게 중요하게 사용되는 몇 가지 함수들을 봤습니다.
대부분의 프로그램과 스크립트들은 거의 비슷한 양식의 함수를 가지고 있습니다.
그래서 다른 곳에서 비슷한 함수를 보면 그 기능이 무엇인지는 어느정도 예상할 수 있습니다.
꼭 필요한 것을 확실히 이해하는 것도 중요합니다.
[필수MySQL 함수 목록]
아래의 MySQL 함수들은 실무에서 쓰이게 되는 꼭 필요한 함수들입니다. 외워둘 필요는 없지만 사용하다 보면 자연스럽게 외워지게 됩니다.
함수명
기능
mysql_pconnect()
데이터베이스에 영구적인 접속을 한다.
mysql_query()
SQL문을 쿼리한다.
mysql_fetch_row()
결과를 숫자 색인 배열로 얻어온다.
mysql_num_rows()
결과셋으로부터 열의 개수를 얻는다.
mysql_insert_id()
최근에 INSERT된 작업에서 생성된 identifier 값을 얻는다.
위에 열거된 MySQL 함수들 이외도 많은 함수들이 있지만 가장 많이 사용하는 함수들만 선별하여 리스트해 보았습니다.