6.1 리터럴
리터럴은 고정되거나 변경할 수 없는 값으로 상수라고도 한다.리터럴은 SELECT 명령문에서 행을 선택하는 조건에 사용되거나 INSERT 명령문에서 새로운 행에 값을 지정하기 위해서 사용된다.
6.1.1 정수리터럴
정수는 숫자 (digit)의 시퀀스 형태로 표현된다. 부동 소수점은 10진법 구분자 (separater)로 ‘.’을 사용한다. 두 가지 숫자 모두 음수 또는 양수를 표시하기 위해서 ‘-’ 또는 ‘+’를 각각 앞에 사용할 수도 있다.
유효한 정수 값의 예를 보면:
12210-32유효한 부동 소수점 숫자의 예를 보면:
294.42-32032.6809e+10148.00
6.1.2 십진 리터럴
소수점을 가지고 있거나 가지지 않는 수로서 필요하다면 양 또는 음의 부호를 사용할 수 있다. 각 정수 리터럴은 십진 리터럴 정의에 따른다.
49
18.47
-3400
-16
0.83459
-349
18.47
-3400
-16
0.83459
-349
소수점 앞에 있는 숫자의 수를 정밀도라 하며, 소수점 뒤에 있는 숫자의 수를 크기라한다.
6.1.3 부동 소수점 리터럴
지수를 가지고 있는 십진 리터럴 이다.
부동 소수점 리터럴
-34E2 → -3400
0.16E4 → 1600
6.1.4 영수치 리터럴
영수치 리터럴은 인용부호(')로 감싼 0 또는 그 이상의 영수치 문자로 수성된 문자열이다. 여기서 인용부호는 리터럴에 포함되지 않고 문자열의 시작과 끝을 나타낸다.
- 모든 영문자의 소문자 (a-z)
- 모든 영문자의 대문자 (A-Z)
- 모든 숫자 (0-9)
- 특수 기호 (+,=,?,_)
영수치 리터럴
'collins' → collins
'"tis' → "tis
아래의 SELECT 명령문은 인용 부호 및 이스케이핑을 어떻게 사용하는지를 보여 주는 예문이다:
mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+
mysql> SELECT 'This\nIs\nFour\nLines';
+--------------------+
| This
Is
Four
Lines |
+--------------------+
mysql> SELECT 'disappearing\ backslash';
+------------------------+
| disappearing backslash |
+------------------------+
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+
mysql> SELECT 'This\nIs\nFour\nLines';
+--------------------+
| This
Is
Four
Lines |
+--------------------+
mysql> SELECT 'disappearing\ backslash';
+------------------------+
| disappearing backslash |
+------------------------+
6.1.5 날짜 리터럴
연도, 달, 일로 구성하여 날짜를 표현한다. 이와 같은 날짜를 구서하는 3개의 요소는 슬래쉬'/'로 구분 되어 있는데,
생략 가능하다.
날짜 리터럴
1980/12/08 → 80/12/08 → 9 December 1980
6.2 수식
6.2.1 수치 수식과 숫자 처리 함수
6.2.1 수치 수식과 숫자 처리 함수
수치 수식에서 열 명세가 NULL 값을 가진다면 전체 수식의 값은 NULL 값으로 정의된다.
수식 수식에서 사용된 열 명세와 함수 그리고 시스템 변수는 수치 자료형을 가져야 한다.
6+4 *25 → 106
6+4*TOTAL(열의 값은 25) → 106
NULL *30 → NULL
수식 수식에서 사용된 열 명세와 함수 그리고 시스템 변수는 수치 자료형을 가져야 한다.
6+4 *25 → 106
6+4*TOTAL(열의 값은 25) → 106
NULL *30 → NULL
예제6-1) 1학년 신입생의 경우에만 입학금을 내고 재학생의 경우에는 입학금을 내지 않으므로 등록금 총액(fee_total)은
"입학금(fee_enter) + 수업료(fee_price)"가 된다. 그러나 입학금이 NULL인 경우에는 가산할 수 없으므로 ifnull(fee_
enter,0)와 같이 ifnull() 함수를 사용하여 NULL을 0으로 변환하여 가산할 수 있다.
mysql > update fee
> set fee_total = ifnull(fee_enter,0) +fee_price;
예제 6-2) 납입금 총액은 "등록금 총액- 장학금 총액"이다. 납입금 총액을 변경하라
mysql > update fee
> set fee_pay = fee_total - ifnull(jang_total,0)
6.2.2 영수치 수식
수치 수식이 수치 값을 갖는 거처럼 영수치 수식은 영수치 값을 갖는다.
예제 6-3) 우편번호 150-051인 동이름과 지역전화 번호를 출력
mysql> select post_no, concat(post_dong, ddd)
> from post
> where post_no = '150-051'
6.2.3 숫자 처리 함수
ROUND, TRUNCATE 함수
ROUND 함수 : 숫자를 소수점 이하 자릿수에서 반올림한다. 자릿수를 생략하면 소숫점이 5이상일 때 반올림/자릿수를 지정하면 지정한 자리수에서 반올림한다.
TRUNCATE 함수 : 숫자를 소수점 이하 자리수에서 버린다.
Round (column_name or value, n)
Truncate (clumn_name or value, n)
FLOOR 함수 : 소수점 아래의 수를 무조건 절삭하여 정수를 반환
CEIL 함수 : 소수점 아래의 수는 무시하고 무조건 올림하는 수
FLOOR (column_name or value, n)
CEIL (column_name or value, n)
MOD 함수 : 첫번 째 인수를 두번째 인수로 나누어 나머지를 반환한다.
MOD(column_name1 or value1(분자),column_name2 or value2(분모))
ABS함수 : 절대값을 출력하는 함수
POW 함수 : 제곱의 값을 구하는 함수이며 소숫점이 있는 경우에는 실행이 된다.
GREATEST 함수 : 주어진 숫자 중 가장 큰 수를 반환
LEAST 함수 : 반대로 가장 작은 수를 반환
6.3 스칼라 함수
스카랄 함수는 연산을 수행하기 위해서 사용된다. 스칼라 함수는 전달 인수를 사용하지 않거나 하나 이상의 전달 인수를 사용한다. 문자 처리 함수는 문자열 조작에 관한 함수들이 있다.
CONCAT 함수 : 두문자열을 연결시켜 합쳐주고
SUBSTRING 함수 : 지정된 위치에서 지정된 길이만큼의 문자열을 추출한다.
LENGTH 함수 : 문자열의 길이를 정수 값으로 반환한다.
INSTR 함수 : 문자열에서 특정 문자의 위치를 반환하고
LPAD 함수 : 왼쪽에 지정된 문자를 지정된 길이만큼 채워준다.
RPAD 함수 : 오른쪽에서 지정된 문자를 지정된 길이만큼 채워준다
LOWER 함수 : 문자열을 모두 소문자로 바꾸어 준다.
UPPER 함수 : 문자열 모두 대문자로 바꿔준다.
INITCAP 함수 : 문자열에 속한 각 단어 별로 첫 글자를 대문자로 나머지 부분은 소문자로 바꾸어준다.
mysql> select stu_no, stu_name, upper(stu_ename)
> from student
2학년 학생의 번호와 이름, 영문이름 그리고 영문이름의 길이를 나타내라
mysql> select stu_no, stu_name, stu_ename, length(rtrim(stu_ename))
> from student
> where grade =2;
이 함수의 전달 인수 자체는 RTRIM이라는 함수이다. RTRIM 함수는 영수치 값에서 우측에 있는 불필요한 공백을 모두 제거한다. 예제에서 무낮의 수가 계산 되기 전에 이름으로부터 모든 공백은 제거 된다.
영문 이름의 길이가 정확히 12자인 각 학생의 번호와 영문이름을 출력하라
mysql> select stu_no, stu_name
> from student
> where length(rtrim(stu_ename)_=12;
현주소의 우편번호가 "550"으로 시작하는 전남 여수시에 거주하는 학생의 학번과이름, 우편번호를 나타내라
mysql> select stu_no, stu_name, post_no
> from student
> where substring(post_no,1,3) ='550';
학벅인 '20001021'인 학생의 학번과 이름, 우편번호, 주소를 출력하라. 단 주소출력에는 공백 부분을 삭제하여 출력하고 CONCAT함수를 사용하라
mysql> select s.stu_no, s.stu_name, s.post_no,
> concat(rtrim(p.post_address), rtrim(s.address)) "주소"
> from student s, post p
> where s.post_no = p.post_no
> and stu_no = '20001021';
우편번호 테이블에서 451-800 에 해당하는 주소 값(경기도 평택시 팽성읍)과 학생 신상 테이블에서 '20001021'인 학생의 주소값(안정리 주공 APT 107동 504호)을 조합하여 이상길 학생의 주소를 완성하여 출력한다.
6.4 날짜 및 시간 처리
MySQL은 표준 시간 이외에도 다양한 날짜 및 시간 관련 칼럼 타입과 함수를 지원한다.
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[(M)] : 날짜 및 시간 타입으로 자동변경칼럼타입이다. 어떤 칼럼의 값을 수정하면 자동으로 수정날짜가나온다. TIMESTAMP는 기본 14 이며 12,8,6을 지정할수 있다.
mysql> select name,telephone,modifydate
> from customers
> where name='장영실';
(결과)
+--------+-------------+----------------+
| name | telephone | modifydate |
+--------+-------------+----------------+
| 장영실 | 051-65-1234 | 20030706224546 |
+--------+-------------+----------------+
1 row in set (0.00 sec)
일단 수정
mysql> update customers set telephone='051-665-2345'
> where name='장영실';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select name,telephone,modifydate
> from customers
> where name='장영실';
+--------+--------------+----------------+
| name | telephone | modifydate |
+--------+--------------+----------------+
| 장영실 | 051-665-2345 | 20030710185204 |
+--------+--------------+----------------+
1 row in set (0.00 sec)
modifydate부분을 보면 수정날짜로 변경되었음을 알수있다.
TIME : 시간타입이다. '-838:55:59'에서 '838:59:59'까지 나타낼수있으며,기본적으로 형태는 'HH:MM:SS'이다.
YEAR[(2|4)] : 연도를 나타내는 타입이다. 2자리 혹은 4자리로 나타낼 수 있음 자리수를 지정하지 않으면 기본적으로 4자리로 나타낸다. 4자리로 사용할 때는 1091에서 2155년까지 지원하며 2자리로 사용할때는 1970에서 2069년까지 지원한다(70-69).
6.4.2 날짜 및 시간 관련 함수
NOW() 또는 SYSDATE() : 현재 날짜와 시간 반환
CURDATE() 또는 CURRENT_DATE() : 현재 날짜 반환한다.
CURTIME() 또는 CURRENT_TIME() : 현재 시간을 반환
DAYOFWEEK(date) : 지정한 날짜가 그 주의 몇 번째 요일인가를 가져온다.(1=일요일,2=월요일..)
(예제)
mysql> select DAYOFWEEK('2003-07-10');
+-------------------------+
| DAYOFWEEK('2003-07-10') |
+-------------------------+
| 5 |
+-------------------------+
1 row in set (0.00 sec)
WEEKDAY(date) : DAYOFMONTH(date)와 같으나 0은 월요일, 1은화요일..이다.
DAYOFMONTH(date) :1에서 31까지 중 해당 달의 몇번째 날인지를 가져온다.
DAYOFYEAR(date) : 1에서 366까지 중 해당 연도의 몇번째인지 가져온다.
mysql> select weekday('2003-07-10'),dayofmonth('2003-07-10'),
-> dayofyear('2003-07-10');
+-----------------------+--------------------------+-------------------------+
| weekday('2003-07-10') | dayofmonth('2003-07-10') | dayofyear('2003-07-10') |
+-----------------------+--------------------------+-------------------------+
| 3 | 10 | 191 |
+-----------------------+--------------------------+-------------------------+
1 row in set (0.00 sec)
MONTH(date) : 1에서 12까지 해당연도의 몇번째 달인지를 가져옴.
DAYNAME(date) : 해당 날짜의 요일명을 가져온다.
MONTHNAME(date) : 해당 달의 이름을 가져온다.
mysql> select month('2003-07-10'),dayname('2003-07-10'),
-> monthname('2003-07-10');
+---------------------+-----------------------+-------------------------+
| month('2003-07-10') | dayname('2003-07-10') | monthname('2003-07-10') |
+---------------------+-----------------------+-------------------------+
| 7 | Thursday | July |
+---------------------+-----------------------+-------------------------+
1 row in set (0.00 sec)
QUARTER(date) : 분기계산할때 유용함수
mysql> select quarter('2003-07-10');
+-----------------------+
| quarter('2003-07-10') |
+-----------------------+
| 3 |
+-----------------------+
1 row in set (0.00 sec)
WEEK(date) : 해당 연도의 몇번째 주인가를 가져온다.
WEEK(date,first) : WEEK(date)와 동일하나 한주의 기준을 정할수 있다.
first에 0이 오면 일요일기준으로 1이 오면 월요일기준으로 순서를 가져온다.
mysql> select week('2003-07-10'),week('2003-01-01',0),week('2003-01-01',1);
+--------------------+----------------------+----------------------+
| week('2003-07-10') | week('2003-01-01',0) | week('2003-01-01',1) |
+--------------------+----------------------+----------------------+
| 28 | 1 | 1 |
+--------------------+----------------------+----------------------+
1 row in set (0.00 sec)
YEAR(date) : 1000에서 9999까지의 연도를 가져온다.
YEARWEEK(date) : 연도와 몇번째 주인가를 동시에 가져온다.
YEARWEEK(date) : YEARWEEK(date) 와 동일하며 first에 해당하는 인자가 0이고 해당 날짜가 전년도의 마지막 주에 해당하면 전년도와 전년도의 마지막 주에 해당하는 순서를 가져온다.
mysql> select year('2003-07-10'),yearweek('2003-07-10'),yearweek('2003-07-10',0
);
+--------------------+------------------------+--------------------------+
| year('2003-07-10') | yearweek('2003-07-10') | yearweek('2003-07-10',0) |
+--------------------+------------------------+--------------------------+
| 2003 | 200328 | 200328 |
+--------------------+------------------------+--------------------------+
1 row in set (0.00 sec)
HOUR(time) : 0에서 23까지 중 해당시간을 가져온다.
MINUTE(time) : 0에서 59까지 숫자중 해당분을 가져온다.
SECOND(time) : 0에서 59까지 숫자중 해당초을 가져온다.
mysql> select hour(now()),minute(now()),second(now());
+-------------+---------------+---------------+
| hour(now()) | minute(now()) | second(now()) |
+-------------+---------------+---------------+
| 22 | 23 | 40 |
+-------------+---------------+---------------+
1 row in set (0.00 sec)
DATE_ADD(date,INTERVAL expr type) 혹은 ADDDATE(date,INTERVAL expr type) : date로 부터 expr만큼 type 단위로 더한 결과를 가져온다.
DATE_SUB(date,INTERVAL expr type) 혹은 SUBDATE(date,INTERVAL expr type) : date로 부터 expr만큼 type 단위로 뺀 결과를 가져온다.
* expr 과 type 관계를 알아보자.
=====================================
type expr
=====================================
SECOND 초
MINUTE 분
HOUR 시
DAY 날
MONTH 달
YEAR 년
MINUTE_SECOND "분:초"
HOUR_MINUTE "시:분"
DAY_HOUR "날 시"
YEAR_MONTH "년-달"
HOUR_SECOND "시:분초"
DAY_MINUTE "날 시:분"
DAY_SECOND "날 시:분:초"
=====================================
예제)
mysql> select date_add("2002-12-02 23:59:34",interval 1 day);
+------------------------------------------------+
| date_add("2002-12-02 23:59:34",interval 1 day) |
+------------------------------------------------+
| 2002-12-03 23:59:34 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2002-12-02 23:59:34',interval '1:1' minute_second);
+--------------------------------------------------------------+
| date_add('2002-12-02 23:59:34',interval '1:1' minute_second) |
+--------------------------------------------------------------+
| 2002-12-03 00:00:35 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2002-12-02 23:59:34',interval '-1 10' day_hour);
+-----------------------------------------------------------+
| date_add('2002-12-02 23:59:34',interval '-1 10' day_hour) |
+-----------------------------------------------------------+
| 2002-12-01 13:59:34 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_sub('1998-01-02',interval 31 day);
+----------------------------------------+
| date_sub('1998-01-02',interval 31 day) |
+----------------------------------------+
| 1997-12-02 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('1998-01-30',interval 1 month);
+-----------------------------------------+
| date_add('1998-01-30',interval 1 month) |
+-----------------------------------------+
| 1998-02-28 |
+-----------------------------------------+
1 row in set (0.00 sec)
EXTRACT(type FROM date) : date로부터 type 형태로 결과를 추출하여 가져온다. 이 때 type은 DATE_ADD(),DATE_SUB()에서 사용하는 type과 같은 형태이다.
TO_DAYS(date) : 0년 부터 계산한 날짜수를 가져온다. 참고로 TO_DAYS()는 그레고리력(1582)을 따르지는 않는다.
FORM_DAYS(N) : 0년부터 계산된 날짜수로부터 날짜를 가져온다.
mysql> select date_add('1998-01-30',interval 1 month);
+-----------------------------------------+
| date_add('1998-01-30',interval 1 month) |
+-----------------------------------------+
| 1998-02-28 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql > update fee
> set fee_pay = fee_total - ifnull(jang_total,0)
6.2.2 영수치 수식
수치 수식이 수치 값을 갖는 거처럼 영수치 수식은 영수치 값을 갖는다.
예제 6-3) 우편번호 150-051인 동이름과 지역전화 번호를 출력
mysql> select post_no, concat(post_dong, ddd)
> from post
> where post_no = '150-051'
6.2.3 숫자 처리 함수
ROUND, TRUNCATE 함수
ROUND 함수 : 숫자를 소수점 이하 자릿수에서 반올림한다. 자릿수를 생략하면 소숫점이 5이상일 때 반올림/자릿수를 지정하면 지정한 자리수에서 반올림한다.
TRUNCATE 함수 : 숫자를 소수점 이하 자리수에서 버린다.
Round (column_name or value, n)
Truncate (clumn_name or value, n)
FLOOR 함수 : 소수점 아래의 수를 무조건 절삭하여 정수를 반환
CEIL 함수 : 소수점 아래의 수는 무시하고 무조건 올림하는 수
FLOOR (column_name or value, n)
CEIL (column_name or value, n)
MOD 함수 : 첫번 째 인수를 두번째 인수로 나누어 나머지를 반환한다.
MOD(column_name1 or value1(분자),column_name2 or value2(분모))
ABS함수 : 절대값을 출력하는 함수
POW 함수 : 제곱의 값을 구하는 함수이며 소숫점이 있는 경우에는 실행이 된다.
GREATEST 함수 : 주어진 숫자 중 가장 큰 수를 반환
LEAST 함수 : 반대로 가장 작은 수를 반환
6.3 스칼라 함수
스카랄 함수는 연산을 수행하기 위해서 사용된다. 스칼라 함수는 전달 인수를 사용하지 않거나 하나 이상의 전달 인수를 사용한다. 문자 처리 함수는 문자열 조작에 관한 함수들이 있다.
CONCAT 함수 : 두문자열을 연결시켜 합쳐주고
SUBSTRING 함수 : 지정된 위치에서 지정된 길이만큼의 문자열을 추출한다.
LENGTH 함수 : 문자열의 길이를 정수 값으로 반환한다.
INSTR 함수 : 문자열에서 특정 문자의 위치를 반환하고
LPAD 함수 : 왼쪽에 지정된 문자를 지정된 길이만큼 채워준다.
RPAD 함수 : 오른쪽에서 지정된 문자를 지정된 길이만큼 채워준다
LOWER 함수 : 문자열을 모두 소문자로 바꾸어 준다.
UPPER 함수 : 문자열 모두 대문자로 바꿔준다.
INITCAP 함수 : 문자열에 속한 각 단어 별로 첫 글자를 대문자로 나머지 부분은 소문자로 바꾸어준다.
mysql> select stu_no, stu_name, upper(stu_ename)
> from student
2학년 학생의 번호와 이름, 영문이름 그리고 영문이름의 길이를 나타내라
mysql> select stu_no, stu_name, stu_ename, length(rtrim(stu_ename))
> from student
> where grade =2;
이 함수의 전달 인수 자체는 RTRIM이라는 함수이다. RTRIM 함수는 영수치 값에서 우측에 있는 불필요한 공백을 모두 제거한다. 예제에서 무낮의 수가 계산 되기 전에 이름으로부터 모든 공백은 제거 된다.
영문 이름의 길이가 정확히 12자인 각 학생의 번호와 영문이름을 출력하라
mysql> select stu_no, stu_name
> from student
> where length(rtrim(stu_ename)_=12;
현주소의 우편번호가 "550"으로 시작하는 전남 여수시에 거주하는 학생의 학번과이름, 우편번호를 나타내라
mysql> select stu_no, stu_name, post_no
> from student
> where substring(post_no,1,3) ='550';
학벅인 '20001021'인 학생의 학번과 이름, 우편번호, 주소를 출력하라. 단 주소출력에는 공백 부분을 삭제하여 출력하고 CONCAT함수를 사용하라
mysql> select s.stu_no, s.stu_name, s.post_no,
> concat(rtrim(p.post_address), rtrim(s.address)) "주소"
> from student s, post p
> where s.post_no = p.post_no
> and stu_no = '20001021';
우편번호 테이블에서 451-800 에 해당하는 주소 값(경기도 평택시 팽성읍)과 학생 신상 테이블에서 '20001021'인 학생의 주소값(안정리 주공 APT 107동 504호)을 조합하여 이상길 학생의 주소를 완성하여 출력한다.
6.4 날짜 및 시간 처리
MySQL은 표준 시간 이외에도 다양한 날짜 및 시간 관련 칼럼 타입과 함수를 지원한다.
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[(M)] : 날짜 및 시간 타입으로 자동변경칼럼타입이다. 어떤 칼럼의 값을 수정하면 자동으로 수정날짜가나온다. TIMESTAMP는 기본 14 이며 12,8,6을 지정할수 있다.
mysql> select name,telephone,modifydate
> from customers
> where name='장영실';
(결과)
+--------+-------------+----------------+
| name | telephone | modifydate |
+--------+-------------+----------------+
| 장영실 | 051-65-1234 | 20030706224546 |
+--------+-------------+----------------+
1 row in set (0.00 sec)
일단 수정
mysql> update customers set telephone='051-665-2345'
> where name='장영실';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select name,telephone,modifydate
> from customers
> where name='장영실';
+--------+--------------+----------------+
| name | telephone | modifydate |
+--------+--------------+----------------+
| 장영실 | 051-665-2345 | 20030710185204 |
+--------+--------------+----------------+
1 row in set (0.00 sec)
modifydate부분을 보면 수정날짜로 변경되었음을 알수있다.
TIME : 시간타입이다. '-838:55:59'에서 '838:59:59'까지 나타낼수있으며,기본적으로 형태는 'HH:MM:SS'이다.
YEAR[(2|4)] : 연도를 나타내는 타입이다. 2자리 혹은 4자리로 나타낼 수 있음 자리수를 지정하지 않으면 기본적으로 4자리로 나타낸다. 4자리로 사용할 때는 1091에서 2155년까지 지원하며 2자리로 사용할때는 1970에서 2069년까지 지원한다(70-69).
6.4.2 날짜 및 시간 관련 함수
NOW() 또는 SYSDATE() : 현재 날짜와 시간 반환
CURDATE() 또는 CURRENT_DATE() : 현재 날짜 반환한다.
CURTIME() 또는 CURRENT_TIME() : 현재 시간을 반환
DAYOFWEEK(date) : 지정한 날짜가 그 주의 몇 번째 요일인가를 가져온다.(1=일요일,2=월요일..)
(예제)
mysql> select DAYOFWEEK('2003-07-10');
+-------------------------+
| DAYOFWEEK('2003-07-10') |
+-------------------------+
| 5 |
+-------------------------+
1 row in set (0.00 sec)
WEEKDAY(date) : DAYOFMONTH(date)와 같으나 0은 월요일, 1은화요일..이다.
DAYOFMONTH(date) :1에서 31까지 중 해당 달의 몇번째 날인지를 가져온다.
DAYOFYEAR(date) : 1에서 366까지 중 해당 연도의 몇번째인지 가져온다.
mysql> select weekday('2003-07-10'),dayofmonth('2003-07-10'),
-> dayofyear('2003-07-10');
+-----------------------+--------------------------+-------------------------+
| weekday('2003-07-10') | dayofmonth('2003-07-10') | dayofyear('2003-07-10') |
+-----------------------+--------------------------+-------------------------+
| 3 | 10 | 191 |
+-----------------------+--------------------------+-------------------------+
1 row in set (0.00 sec)
MONTH(date) : 1에서 12까지 해당연도의 몇번째 달인지를 가져옴.
DAYNAME(date) : 해당 날짜의 요일명을 가져온다.
MONTHNAME(date) : 해당 달의 이름을 가져온다.
mysql> select month('2003-07-10'),dayname('2003-07-10'),
-> monthname('2003-07-10');
+---------------------+-----------------------+-------------------------+
| month('2003-07-10') | dayname('2003-07-10') | monthname('2003-07-10') |
+---------------------+-----------------------+-------------------------+
| 7 | Thursday | July |
+---------------------+-----------------------+-------------------------+
1 row in set (0.00 sec)
QUARTER(date) : 분기계산할때 유용함수
mysql> select quarter('2003-07-10');
+-----------------------+
| quarter('2003-07-10') |
+-----------------------+
| 3 |
+-----------------------+
1 row in set (0.00 sec)
WEEK(date) : 해당 연도의 몇번째 주인가를 가져온다.
WEEK(date,first) : WEEK(date)와 동일하나 한주의 기준을 정할수 있다.
first에 0이 오면 일요일기준으로 1이 오면 월요일기준으로 순서를 가져온다.
mysql> select week('2003-07-10'),week('2003-01-01',0),week('2003-01-01',1);
+--------------------+----------------------+----------------------+
| week('2003-07-10') | week('2003-01-01',0) | week('2003-01-01',1) |
+--------------------+----------------------+----------------------+
| 28 | 1 | 1 |
+--------------------+----------------------+----------------------+
1 row in set (0.00 sec)
YEAR(date) : 1000에서 9999까지의 연도를 가져온다.
YEARWEEK(date) : 연도와 몇번째 주인가를 동시에 가져온다.
YEARWEEK(date) : YEARWEEK(date) 와 동일하며 first에 해당하는 인자가 0이고 해당 날짜가 전년도의 마지막 주에 해당하면 전년도와 전년도의 마지막 주에 해당하는 순서를 가져온다.
mysql> select year('2003-07-10'),yearweek('2003-07-10'),yearweek('2003-07-10',0
);
+--------------------+------------------------+--------------------------+
| year('2003-07-10') | yearweek('2003-07-10') | yearweek('2003-07-10',0) |
+--------------------+------------------------+--------------------------+
| 2003 | 200328 | 200328 |
+--------------------+------------------------+--------------------------+
1 row in set (0.00 sec)
HOUR(time) : 0에서 23까지 중 해당시간을 가져온다.
MINUTE(time) : 0에서 59까지 숫자중 해당분을 가져온다.
SECOND(time) : 0에서 59까지 숫자중 해당초을 가져온다.
mysql> select hour(now()),minute(now()),second(now());
+-------------+---------------+---------------+
| hour(now()) | minute(now()) | second(now()) |
+-------------+---------------+---------------+
| 22 | 23 | 40 |
+-------------+---------------+---------------+
1 row in set (0.00 sec)
DATE_ADD(date,INTERVAL expr type) 혹은 ADDDATE(date,INTERVAL expr type) : date로 부터 expr만큼 type 단위로 더한 결과를 가져온다.
DATE_SUB(date,INTERVAL expr type) 혹은 SUBDATE(date,INTERVAL expr type) : date로 부터 expr만큼 type 단위로 뺀 결과를 가져온다.
* expr 과 type 관계를 알아보자.
=====================================
type expr
=====================================
SECOND 초
MINUTE 분
HOUR 시
DAY 날
MONTH 달
YEAR 년
MINUTE_SECOND "분:초"
HOUR_MINUTE "시:분"
DAY_HOUR "날 시"
YEAR_MONTH "년-달"
HOUR_SECOND "시:분초"
DAY_MINUTE "날 시:분"
DAY_SECOND "날 시:분:초"
=====================================
예제)
mysql> select date_add("2002-12-02 23:59:34",interval 1 day);
+------------------------------------------------+
| date_add("2002-12-02 23:59:34",interval 1 day) |
+------------------------------------------------+
| 2002-12-03 23:59:34 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2002-12-02 23:59:34',interval '1:1' minute_second);
+--------------------------------------------------------------+
| date_add('2002-12-02 23:59:34',interval '1:1' minute_second) |
+--------------------------------------------------------------+
| 2002-12-03 00:00:35 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2002-12-02 23:59:34',interval '-1 10' day_hour);
+-----------------------------------------------------------+
| date_add('2002-12-02 23:59:34',interval '-1 10' day_hour) |
+-----------------------------------------------------------+
| 2002-12-01 13:59:34 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_sub('1998-01-02',interval 31 day);
+----------------------------------------+
| date_sub('1998-01-02',interval 31 day) |
+----------------------------------------+
| 1997-12-02 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('1998-01-30',interval 1 month);
+-----------------------------------------+
| date_add('1998-01-30',interval 1 month) |
+-----------------------------------------+
| 1998-02-28 |
+-----------------------------------------+
1 row in set (0.00 sec)
EXTRACT(type FROM date) : date로부터 type 형태로 결과를 추출하여 가져온다. 이 때 type은 DATE_ADD(),DATE_SUB()에서 사용하는 type과 같은 형태이다.
TO_DAYS(date) : 0년 부터 계산한 날짜수를 가져온다. 참고로 TO_DAYS()는 그레고리력(1582)을 따르지는 않는다.
FORM_DAYS(N) : 0년부터 계산된 날짜수로부터 날짜를 가져온다.
mysql> select date_add('1998-01-30',interval 1 month);
+-----------------------------------------+
| date_add('1998-01-30',interval 1 month) |
+-----------------------------------------+
| 1998-02-28 |
+-----------------------------------------+
1 row in set (0.00 sec)
DATE_FROMAT(날짜,'형식') : 날짜를 형식에 맞게 출력, 날짜/시간 포맷
|
format 값 |
의미 |
|---|---|
|
%a |
Weekday, 영문 약어 (Sun, …, Sat) |
|
%b |
Month, 영문 약어 (Jan, …, Dec) |
|
%c |
Month(1, …, 12) |
|
%D |
Day of the month, 서수 영문 스트링(1st, 2nd, 3rd, …) |
|
%d |
Day of the month, 두 자리 숫자(01, …, 31) |
|
%e |
Day of the month (1, …, 31) |
|
%f |
Milliseconds, 세 자리 숫자 (000, …, 999) |
|
%H |
Hour, 24시간 기준, 두 자리 수 이상 (00, …, 23, …, 100, …) |
|
%h |
Hour, 12시간 기준 두 자리 숫자 (01, …, 12) |
|
%I |
Hour, 12시간 기준 두 자리 숫자 (01, …, 12) |
|
%i |
Minutes , 두 자리 숫자(00, …, 59) |
|
%j |
Day of year, 세 자리 숫자 (001, …, 366) |
|
%k |
Hour, 24시간 기준, 한 자리 수 이상 (0, …, 23, …, 100, …) |
|
%l |
Hour , 12시간 기준 (1, …, 12) |
|
%M |
Month, 영문 스트링 (January, …, December) |
|
%m |
Month, 두 자리 숫자 (01, …, 12) |
|
%p |
AM or PM |
|
%r |
Time, 12 시간 기준, 시:분:초 (hh:mm:ss AM or hh:mm:ss PM) |
|
%S |
Seconds, 두 자리 숫자 (00, …, 59) |
|
%s |
Seconds , 두 자리 숫자(00, …, 59) |
|
%T |
Time, 24시간 기준, 시:분:초 (hh:mm:ss) |
|
%U |
Week, 두 자리 숫자, 일요일이 첫날인 주 단위(00, …, 53) |
|
%u |
Week, 두 자리 숫자, 월요일이 첫날인 주 단위(00, …, 53) |
|
%V |
Week, 두 자리 숫자, 일요일이 첫날인 주 단위(01, …, 53) |
|
%v |
Week, 두 자리 숫자, 월요일이 첫날인 주 단위(01, …, 53) |
|
%W |
Weekday, 영문 스트링 (Sunday, …, Saturday) |
|
%w |
Day of the week, 숫자 인덱스 (0=Sunday, …, 6=Saturday) |
|
%X |
Year, 네 자리 숫자, 일요일이 첫날인 주 단위로 계산(0000, …, 9999) |
|
%x |
Year, 네 자리 숫자, 월요일이 첫날인 주 단위로 계산(0000, …, 9999) |
|
%Y |
Year, 네 자리 숫자(0001, …, 9999) |
|
%y |
Year, 두 자리 숫자(00, 01, …, 99) |
|
%% |
특수문자 "%"를 그대로 출력하는 경우 |
|
%x |
포맷 지정자로 쓰이지 않는 영문자 중 임의의 문자 x를 그대로 출력하는 경우 |
예제)
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
======================
'Sunday October 2009'
SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
======================
'22:23:00'
SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
======================
'4th 00 Thu 04 10 Oct 277'
SELECT DATE_FORMAT('1999-01-01', '%X %V');
======================
'1998 52'
6.5 데이터형 변환함수
프로그래머던, 아니면 DB를 작업하는 사람이던 MySQL에서의 형변환을 항상 기대해 왔을 것이다.(Oracle과 Mssql의 이 기능이 부러웠던 것은 사실이다.) 완전하지는 않지만 꽤 쓸만한 기능들이 4.0이상 부터 추가되기 시작했다. 아래의 글은 ysql.com의 매뉴얼에서 발견하고 소개하는 것이다.
형변환 함수(Cast Functions)
MySQL 4.0.2부터 추가된 함수로 CAST()와 CONVERT() 함수는 한 타입의 값을 취해서 다른 타입의 값으로 사용된다. 구문은 아래와 같다.
CAST(expression! AS type)
CONVERT(expression!,type)
CONVERT(expr USING transcoding_name)
타입값은 아래의 것들 중 하나가 될 것이다.
• BINARY
• CHAR
• DATE
• DATETIME
• SIGNED :부호가 있는 데이터형
• TIME
• UNSIGNED : 부호가 없는 데이터형
CAST 함수,CONVERT
CAST 함수는 type을 변경(지정)하는데 유용하다.
CAST 함수의 사용법은 다음과 같다.
CAST(expression AS type) 또는 CONVERT(expression,type)
타입은 다음중 하나이다.
BINARY
CHAR
DATE
DATETIME
SIGNED - 양수만 저장 가능
TIME
UNSIGNED - 양수만 저장 가능
CAST()은 SQL-99에 쓰이는 문장이고
CONVERT()은 ODBC에 쓰이는 문장이다.
【예제】
mysql> select cast(now() as date);
+---------------------+
| cast(now() as date) |
+---------------------+
| 2003-09-25 |
+---------------------+
1 row in set (0.00 sec)
mysql> select cast(1-2 as unsigned);
+-----------------------+
| cast(1-2 as unsigned) |
+-----------------------+
| 18446744073709551615 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select cast(cast(1-2 as unsigned) as signed);
+---------------------------------------+
| cast(cast(1-2 as unsigned) as signed) |
+---------------------------------------+
| -1 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select cast(1 as unsigned) -2.0;
+--------------------------+
| cast(1 as unsigned) -2.0 |
+--------------------------+
| -1.0 |
+--------------------------+
1 row in set (0.02 sec)
6.6 사용자 정의 함수
MySQL에서는 두 가지 방법으로 사용자가 정의한 변수를 지원한다. 변수이름은
alphanumeric 문자와 '_', '$', '.'로 구성된다. 변수에 초기값이 지정되지 않으면, NULL이 디
폴트이며, integer, real, string 값을 저장할 수 있다. 변수이름은 버전 5.0부터는 대·소문자
구분이 없다.
방법1)
SET 문을 사용하여 변수를 설정
SET @variable={integer expression | real expression | string expression }
[,@variable=...]
(예제)
mysql> set @t3=5;
mysql> select @t3;
+------+
| @t3 |
+------+
| 5 |
+------+
mysql>
SET 문을 사용하여 변수를 설정
SET @variable={integer expression | real expression | string expression }
[,@variable=...]
(예제)
mysql> set @t3=5;
mysql> select @t3;
+------+
| @t3 |
+------+
| 5 |
+------+
mysql>
방법2)
@variable:=expr 문을 사용하여 설정
【예제】
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 |
+----------------------+------+------+------+
| 5 | 5 | 1 | 4 |
+----------------------+------+------+------+
@variable:=expr 문을 사용하여 설정
【예제】
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 |
+----------------------+------+------+------+
| 5 | 5 | 1 | 4 |
+----------------------+------+------+------+
6.7 시스템 변수
시스템 변수는 thread-specific 변수와 global 변수가 있다.
시스템 변수는 thread-specific 변수와 global 변수가 있다.
global 변수 : SET GLOBAL 명령으로 변경
session 변수 : SET SESSION 명령으로 변경
시스템 변수를 변경하는 방법은 다음과 같다.(예는 sort_buffer_size이다)
1) global 변수 변경
SET GLOBAL sort_buffer_size=value;
또는 SET @@global.sort_buffer_size=value;
SET GLOBAL sort_buffer_size=value;
또는 SET @@global.sort_buffer_size=value;
2) session 변수 변경 (여기서 session=LOCAL을 의미함)
SET SESSION sort_buffer_size=value;
또는 SET @@global.sort_buffer_size=value;
또는 SET sort_buffer_size=value;
SET SESSION sort_buffer_size=value;
또는 SET @@global.sort_buffer_size=value;
또는 SET sort_buffer_size=value;
변수를 확인해 볼 수 있는 명령은 다음과 같다.
1) global 변수인 경우
SELECT @@global.sort_buffer_size;
또는 SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';
2) session 변수인 경우
SELECT @@session.sort_buffer_size;
또는 SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
1) global 변수인 경우
SELECT @@global.sort_buffer_size;
또는 SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';
2) session 변수인 경우
SELECT @@session.sort_buffer_size;
또는 SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
참조 : show variables
【예제】
mysql> set global sort_buffer_size=530000;
Query OK, 0 rows affected (0.01 sec)
mysql> set global sort_buffer_size=530000;
Query OK, 0 rows affected (0.01 sec)
mysql> set session sort_buffer_size=524280;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.sort_buffer_size;
+---------------------------+
| @@global.sort_buffer_size |
+---------------------------+
| 530000 |
+---------------------------+
1 row in set (0.01 sec)
+---------------------------+
| @@global.sort_buffer_size |
+---------------------------+
| 530000 |
+---------------------------+
1 row in set (0.01 sec)
mysql> select @@session.sort_buffer_size;
+----------------------------+
| @@session.sort_buffer_size |
+----------------------------+
| 524280 |
+----------------------------+
1 row in set (0.00 sec)
+----------------------------+
| @@session.sort_buffer_size |
+----------------------------+
| 524280 |
+----------------------------+
1 row in set (0.00 sec)
mysql>
현재의 autocommit 상태를 확인하는 방법은 다음 예제와 같다.
【예제】
mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.03 sec)
【예제】
mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.03 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.01 sec)
+----------------------+
| @@session.autocommit |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.01 sec)
mysql>
다음은 global과 session 변수를 정리하였다.
다음은 global과 session 변수를 정리하였다.
| 변수 이름 | 변수값 타입 | 타입 |
|---|---|---|
| autocommit | bool | SESSION |
| big_tables | bool | SESSION |
| binlog_cache_size | num | GLOBAL |
| bulk_insert_buffer_size | num | GLOBAL ¦ SESSION |
| concurrent_insert | bool | GLOBAL |
| connect_timeout | num | GLOBAL |
| convert_character_set | string | SESSION |
| delay_key_write | OFF|ON|ALL | GLOBAL |
| delayed_insert_limit | num | GLOBAL |
| delayed_insert_timeout | num | GLOBAL |
| delayed_queue_size | num | GLOBAL |
| error_count | num | LOCAL |
| flush | bool | GLOBAL |
| flush_time | num | GLOBAL |
| foreign_key_checks | bool | SESSION |
| identity | num | SESSION |
| insert_id | bool | SESSION |
| interactive_timeout | num | GLOBAL ¦ SESSION |
| join_buffer_size | num | GLOBAL ¦ SESSION |
| key_buffer_size | num | GLOBAL |
| last_insert_id | bool | SESSION |
| local_infile | bool | GLOBAL |
| log_warnings | bool | GLOBAL |
| long_query_time | num | GLOBAL ¦ SESSION |
| low_priority_updates | bool | GLOBAL ¦ SESSION |
| max_allowed_packet | num | GLOBAL ¦ SESSION |
| max_binlog_cache_size | num | GLOBAL |
| max_binlog_size | num | GLOBAL |
| max_connect_errors | num | GLOBAL |
| max_connections | num | GLOBAL |
| max_error_count | num | GLOBAL ¦ SESSION |
| max_delayed_threads | num | GLOBAL |
| max_heap_table_size | num | GLOBAL ¦ SESSION |
| max_join_size | num | GLOBAL ¦ SESSION |
| max_sort_length | num | GLOBAL ¦ SESSION |
| max_tmp_tables | num | GLOBAL |
| max_user_connections | num | GLOBAL |
| max_write_lock_count | num | GLOBAL |
| myisam_max_extra_sort_file_size | num | GLOBAL ¦ SESSION |
| myisam_repair_threads | num | GLOBAL ¦ SESSION |
| myisam_max_sort_file_size | num | GLOBAL ¦ SESSION |
| myisam_sort_buffer_size | num | GLOBAL ¦ SESSION |
| net_buffer_size | num | GLOBAL ¦ SESSION |
| net_read_timeout | num | GLOBAL ¦ SESSION |
| net_retry_count | num | GLOBAL ¦ SESSION |
| net_write_timeout | num | GLOBAL ¦ SESSION |
| query_cache_limit | num | GLOBAL |
| query_cache_size | num | GLOBAL |
| query_cache_type | enum | GLOBAL |
| read_buffer_size | num | GLOBAL ¦ SESSION |
| read_rnd_buffer_size | num | GLOBAL ¦ SESSION |
| rpl_recovery_rank | num | GLOBAL |
| safe_show_database | bool | GLOBAL |
| server_id | num | GLOBAL |
| slave_compressed_protocol | bool | GLOBAL |
| slave_net_timeout | num | GLOBAL |
| slow_launch_time | num | GLOBAL |
| sort_buffer_size | num | GLOBAL ¦ SESSION |
| sql_auto_is_null | bool | SESSION |
| sql_big_selects | bool | SESSION |
| sql_big_tables | bool | SESSION |
| sql_buffer_results | bool | SESSION |
| sql_log_binlog | bool | SESSION |
| sql_log_off | bool | SESSION |
| sql_log_update | bool | SESSION |
| sql_low_priority_updates | bool | GLOBAL ¦ SESSION |
| sql_max_join_size | num | GLOBAL ¦ SESSION |
| sql_quote_slow_create | bool | SESSION |
| sql_safe_updates | bool | SESSION |
| sql_select_limit | bool | SESSION |
| sql_slave_skip_counter | num | GLOBAL |
| sql_warnings | bool | SESSION |
| table_cache | num | GLOBAL |
| table_type | enum | GLOBAL ¦ SESSION |
| thread_cache_size | num | GLOBAL |
| timestamp | bool | SESSION |
| tmp_table_size | enum | GLOBAL ¦ SESSION |
| tx_isolation | enum | GLOBAL ¦ SESSION |
| version | string | GLOBAL |
| wait_timeout | num | GLOBAL ¦ SESSION |
| warning_count | num | LOCAL |
| unique_checks | bool | SESSION |
'DB > MySQL' 카테고리의 다른 글
| 7장 SELECT 명령문의 절 (0) | 2012.03.05 |
|---|---|
| 테이블 생성 및 삭제하기 (0) | 2012.03.02 |
| BNF (0) | 2012.02.28 |
| 5장 테이블 생성 (0) | 2012.02.28 |
| 4장 연습 문제 (0) | 2012.02.23 |
댓글