MySQL User 메뉴얼 userman.pdf
MySQL Reference 메뉴얼 refman.pdf
MySQL에서는 내장 프로시저, 즉, DB에 저장된 함수를 사용자가 불러다 쓰는 것이죠. 그래서 이를 Stored, "저장된", "내장된" 이름으로 쓰는 것입니다.
이런 내장 프로시저를 만드는데는 몇가지 알아야 할 사항이 있는데,
첫번째가 "delimiter"라는 존재입니다.
이 delimiter는 구분자, 즉, 명령러 단락 구역을 만들 수 있게 도와주는 넘입니다.
SQL 구문은 ';' 문자로 끝나게 됩니다.
근데 프로시저 안에서 한줄 끝날때 마다 ;를 다 넣어주면, 어디가 쿼리문이고 어디가 프로시저 문인지를 헛갈리게 됩니다. 따라서 "여기서 여기 까지가 프로시저야" 라는 걸 알려주기 위해서 delimiter를 씁니다.
이렇게 하여서 ';'을 "$$"로 바꾸어 주게 되는데요.
이렇게 하면, 프로시저를 꾸며줄 때 한줄이 끝났을 때 ';'를 써줘도 함수가 끝나지 않았다는 것을 인식 시켜줄 수 있습니다.
그리고 프로시저를 다 꾸미고 나면, 다시 "$$" 를 ';'로 바꾸어줍니다.
두번째로는 파라미터, "인자 값들의 종류" 입니다.
총 3가지(IN, OUT, INOUT)게 있게 되는데요.
1. IN
IN 파라미터는 "읽기 전용" 이라는 것입니다. 프로서저를 호출할 때 건네주게 되면, 이를 프로시저 안에서 값을 가져다 쓸뿐 바꿀 수 없다는 것이죠.
2. OUT
OUT 파라미터는 "값을 변경 할 수 있는" 속성을 가진 파라미터 입니다.
흔히들 이 파라미터는 프로시저 내부에서 일을 처리한 뒤, 값을 돌려줄때 많이 씁니다.
3. INOUT
INOUT 파라미터는 IN의 속성과 OUT의 속성을 다 가지고 있습니다.
프로시저 호출자로 부터 값을 전달 받아 이를 고쳐서 다시 돌려줄때 사용하게 되는 것이죠.
세번째로는 프로시저의 시작과 끝입니다.
프로시저는 BEGIN 으로 시작해서 END로 끝나게 됩니다.
따라서,
이런 형태로 만드셔야 합니다.
SET은 변수를 만들어주고 변수에 값을 집어 넣는 역할을 하죠. MySQL에서 SET 명령어를 이용해서 변수명 count를 만들게 되면,
SET @count = 0;
이렇게 하시면 됩니다.
이제 간단한 예제는 만들어 볼 수 있습니다.
한번 Stored Procedure를 만들어 보도록 하죠~!
우리가 만들 프로시저는 shop이라는 테이블(뭐.. 가게 정보가 있다고 하죠)이 있으면,
그 shop이라는 테이블의 총 숫자를 위에서 살펴본것 처럼 변수에 다 넣는 예제를 해보도록하죠.
이번 프로시저 이름은 "getshopCount" 라고 짓겠습니다.
일단 프로시저를 만들기 전에는 어떤 역할을 할것인가를 분명하게 세워 놓고,
그 역할에 맞추어서 프로시저이 핵심 쿼리문 정도는 미리 다 마련해 놓고 테스트 하는 것이 중요합니다.
그럼 우리가 만들 "getshopCount" 프로시저에서는 가게의 총 숫자를 가져오는 쿼리문이 필요하겠죠.
그럼 count라는 기본으로 제공되는 함수를 이용해서 해보도록 하겠습니다.
이렇게 하면, 모든 가게의 갯수가 나오게 됩니다.
그럼 핵심 쿼리문이 나왔으니, 프로시저 껍데기를 한번 만들어 보도록 하죠.
이렇게 되겠네요.
그럼, 이번에는 변수를 하나 만들고~ 그 변수에 다가 쿼리 결과 값을 넣어야 하기 때문에,
getshopCount는 OUT 타입의 파라미터를 하나 받아야겠죠.
그래야 나중에 다른 넘이 가져다 쓸 수 있을테니까요.
그리고 위의 쿼리문 ("SELECT COUNT(*) FROM shop;") 을 좀 손을 봐야 하겠네요.
어떻게?? INTO를 집어 넣어서 변수에다 쏘~~ 옥 넣을 겁니다.
//DELIMITER 설정
DELIMITER $$
//SET을 이용한 xcount란느 변수 설정
SET @xcount = 0$$
//처음 보죠?? 아래에서 설명드리겠습니다.
DROP PROCEDURE IF EXISTS getshopCount$$
//이것두~~ 아래에서 설명드리겠습니다.
CREATE PROCEDURE getshopCount(OUT xcount INT)
BEGIN
SELECT count(*) INTO xcount FROM shop;
END $$
//DELIMITER를 다시 ';'로 바꿈.
DELIMITER ;
명령 한줄이 끝날때마다 DELIMITER인 $$를 붙여준다는 것을 잊으시면 안됩니다~!
이것은 프로시저가 생성된 상태에서 또 생성을 하게 되면, 중복 생성이 되지 않기 때문에
기존에 똑같은 이름 즉, getshopCount라는 이름의 프로시저가 있다면 과감하게 버리라는 명령입니다.
"DROP PROCEDURE 프로시저를 버려라, IF 만약 EXISTS 존재한다면, getshopCount getshopCount가"
라고 해석할 수 있겠네요~ ^^ <- 영어시간 같아~
그리고 이번에는 프로시저 선언 부분을 보죠.
움... 파라미터를 선언 할때는 (파라미터타입 파라미터명 파라미터데이터형)
의 형태로 지정해줘야 합니다.
파라미터 타입은 IN, OUT, INOUT 중에서 우리는 값을 수정할 목적으로 끌어오는 놈이기 때문에
이중 OUT을 썼고요. (INOUT을 써도 무방~ ^^)
그리고 파라미터명은
기억나시죠?? 이줄... 여기서 선언한 xcount를 쓰려고 하는 합니다.
그리고 파라미터데이터 형은 숫자기 때문에 INT 형으로 했고요.
그래서 CREATE PROCEDURE getshopCount(OUT xcount INT) 이 완성된겁니다.
이제는 프로시저의 시작이니 BEGIN 을 넣었고요.
shop테이블의 모든 로우값(줄 갯수)를 xcount 변수에 밀어 넣어라~ 라는 말입니다.
이제 다, 된듯 싶습니다.
그럼 이번에는 xcount 값을 보는 프로시저를 간단히 만들어보죠.
이번 보다는 더 쉽습니다~~!
특별한 사항은 없습니다.
단지, SELECT @xcount; 이부분이 신기할 뿐입니다.
이것은 변수를 출력을 할때 쓰는 것이고요. SELECT 문법상 SELECT 글 다음에는 컬럼명이 나와야 하므로
SET @xcount 처럼 @를 붙여주게 된겁니다.
하지만, 일반적으로 변수를 다룰 때는 아래 처럼
변수명 앞에 @를 붙이지 않습니다. 이것이 좀 헛갈리는 부분이라 할 수 있죠.
그리고 조심하셔야 할 부분은 프로시저가 끝나고나서 반드시 END를 쓰실때
$$ 를 빼먹지 마세요~!
주로 MS-SQL에서 작업을 하다, mysql에서 처음으로 stored procedure(이하 sp)를 만들면서, 5분이면 될 것을 수십분은 걸린 것 같다 --;;
에러가 정확한 위치를 알려주질 않는다.
아래의 정리한 내용은 MS-SQL에 익숙하고 mysql을 사용해 보지 않은 분이 보면 무자게 도움이 될 것 같다.
IF문을 쓸 때 END IF문이 있어야 하며, END IF문 뒤에는 ;를 붙여야 한다.
auto increment값을 얻어올 때는 LAST_INSERT_ID()을 이용한다.
CREATE PROCEDURE문 전에 delimeter문을 이용해 delimeter를 ';'가 아닌 다른 걸로 변경하고 뒤에 다시변경할 것
1. 생성...
- 구조
- 예제
2. 삭제
- 구조
- 예제
3. 호출
- 구조
- 예제
MySQL 5.0 에서 가장 주요하게 추가된 기능인 Stored Procedure는 IN 파라미터만 포함하는 경우, 별 문제없이 처리가 가능하지만 OUT 파라미터가 하나라도 있으면 C API로 다루기가 어렵다. 이런 경우 OUT 파라미터의 값을 얻기 위해서는 약간의 workaround가 필요하다. 이 테크노트에서는 그 workaround를 어떻게 구현하면 되는지 살펴보고자 한다
서론
MySQL 5.0이 출시되면서 개발자들에게 가장 환영을 받았던 추가기능이 바로 Stored Procedure였다. 하지만, OUT 파라미터가 포함된 Stored Procedure는 해당 OUT 파라미터의 값을 얻어오는 방법이 API 레벨에서 지원되지 않는 문제가 있다. 이 문제는 현재의 5.0 버전 (5.0.22)에서도 개선되지 않고 있다. 따라서, 이 OUT 파라미터의 값을 얻어오기 위해서는 약간의 workaround가 필요하게 된다.
뭐가 문제인가? MySQL vs. Oracle
mysql 클라이언트 프로그램으로 Stored Procedure의 OUT 파라미터 값을 얻는 방법은 아래와 같다.
오라클 sqlplus로 Stored Procedure의 OUT 파라미터 값을 얻는 방법은 아래와 같다.
얼핏 보기에는 거의 유사한 것 같지만, API 레벨에서 보기에 가장 커다란 차이점은 out 이라는 OUT 파라미터가 오라클의 경우에는 Bind variable 그 차제(:out)이지만, MySQL의 경우에는 API에서 직접 참조할 수 없는 Connection 내부의 user variable (@out) 이라는 점이다. (MySQL C API에서 Stored Procedure의 OUT 파라미터에 일반 bind variable을 bind하면 에러가 발생한다.)
그럼 어떻게 해결하면 되는가?
MySQL C API에는 @out과 같은 user variable을 참조하는 API가 제공되지 않기 때문에, SELECT @out;과 같은 추가적인 SQL 실행이 필요하다. 따라서, MySQL C API에서 Stored Procedure를 수행하기 위해서는 총 3번의 SQL을 실행해야 한다.
위의 경우, 1번 "SET ...;" Statement에 bind한 MYSQL_BIND 구조체 배열을, 마지막 3번 "SELECT @...;" Statement의 result 컬럼으로 bind해도 된다.
대략의 코드는 아래와 같다.(편의상 에러 체크 루틴은 생략한다.)
Multi-statements 기능을 활용하여 한번의 execution으로 OUT 파라미터의 값을 얻을 수도 있다.
MySQL 서버에 접속할때 호출하는 mysql_real_connect() 함수의 마지막 파라미터에 CLIENT_MULTI_STATEMENTS 옵션을 주고,
mysql_query("SET @in = 1, @out = null; CALL proc_test(@in, @out); SELECT @in, @out;");
<<주의>> Multi-statements 기능은 Prepared Statement 기능과 함께 사용할 수 없다.
mysql_more_results() 로 다음 result-set이 있는지 체크하면서, 모든 statement의 result-set을 소비할 때까지 mysql_next_result()를 적절히 호출해준다.
그렇지 않으면, 다음번 Statement 실행시에 "Command out of sync" 오류가 발생한다.
결론
MySQL에서는 Oracle과 같이 bind variable 자체를 Stored Procedure의 OUT 파라미터로 설정할 수가 없는 한계가 있기 때문에, 위에서 나열한대로 3번의 SQL을 실행시켜야만 OUT 파라미터의 값을 얻을 수 있다.
"무슨소리? JDBC를 사용해서 MySQL Stored Procedure를 사용할 때 이런 workaround를 쓸 필요없이 그냥 잘 되던데?"
MySQL JDBC Driver 에서 이런 작업을 내부적으로 수행한다.
◈ winmysqladmin을 이용해서 서버 시작하기
윈도우용 MySQL에서 MySQL 시작과 정지 및 환경설정은 winmysqladmin.exe 를 이용해서 할 수 있음.
C:mysqlbin>winmysqladmin
◈ mysql database 접속
-- root 유저 접속
mysql> mysql -uroot
-- mysql db 접속
mysql> use mysql;
-- database 조회
mysql> show databases;
-- table 조회
mysql> show tables;
-- table 구조 조회
mysql> desc db;
◈ root유저 비밀번호 변경
mysql> UPDATE user
SET password = password('storm')
WHERE user = 'root';
Query OK, 2 rows affected (0.28 sec)
Rows matched: 2 Changed: 2 Warnings: 0
user 테이블상에 root 사용자가 localhost와 host명으로 2개 등록 되어 있으므로
2row 의 비밀번호가 변경이 됩니다.
mysql> exit
Bye
C:mysqlbin>mysqladmin reload
-- 비밀번호를 입력해서 접속 해야 합니다.
C:mysqlbin>mysql -uroot -pstorm mysql
◈ database와 유저의 생성
-- database 생성(mysqladmin이용)
C:>mysqladmin -uroot -p create scott
-- database 생성(root유저로 접속)
C:mysqlbin>mysql -uroot -pstorm mysql
-- database 삭제
mysql> drop database scott;
-- database 생성
mysql> CREATE DATABASE scott;
-- user생성
mysql>insert into user (host,user,password) values('localhost','scott',password('tiger'));
mysql>insert into db values('localhost','scott','scott','y','y','y','y','y','y','y','y','y','y','y','y');
-- 변경사항 적용
mysql>flush privileges;
-- user삭제
mysql>DELETE FROM user WHERE user='scott' AND host='localhost';
-- 변경사항 적용
mysql>flush privileges;
-- grant문을 이용해서 사용자를 추가하는 방법
mysql>grant all on scott.* to scott@'localhost' identified by 'tiger';
-- 새로만든 scott db에 scott유저로 접속
C:mysqlbin>mysql -uscott -ptiger scott
-- script파일 실행(Oracle :start, @)
mysql>source C:scott.sql
◈ 원하는 만큼 데이터 가져오기
-- 앞에서 3개의 데이터를 조회함
mysql>SELECT empno, ename FROM emp LIMIT 3;
-- 2번째 이후의 데이터-부터 2개의 데이터를 조회함
mysql>SELECT empno, ename FROM emp LIMIT 2,2;
◈ Date And Time Functions
◈ String Functions
◈ Numeric Functions
◈ Cast Functions
◈ Other Functions
◈ Group Functions
◈ LEFT OUTER JOIN, RIGHT OUTER JOIN(Oracle : (+) )
mysql>SELECT b.deptno
FROM emp a RIGHT OUTER JOIN dept b
ON a.deptno = b.deptno
◈ SQL실행 결과를 파일로 저장
C:mysqlbin>mysql -uscott -ptiger scott > C:dump.txt
select * from emp;
select * from dept;
exit<!--"<-->
테이블 컬럼 타입
# 날짜 및 시간 관련 컬럼 타입
- DATE
날짜를 표현하는 유형 [YYYY-MM-DD], 1000-01-01 ∼ 9999-12-31까지 나타낼 수 있
- DATETIME
날짜와 시간을 표현하는 유형 [YYYY-MM-DD HH:MM:SS], 1000-01-01 00:00:00 ∼ 9999-12-31 23:59:59
- TIMESTAMP
자동변경 컬럼 타입(4 Byte ), 1970-01-01 00:00:00부터 2037년 까지 표현
- TIME
시간을 표현하는 유형 [HH:MM:SS], 839:59:59 ∼ 833:59:59 까지 표현
- YEAR
년도를 표현하는 유형[기본적으로 4자리로 사용], 1901년 ∼ 2155년
# 문자 컬럼 타입
- CHAR(M)
고정길이 문자열 컬럼, M의 범위는 0 에서 255까지.
- VARCHAR(M)
가변길이 문자열 컬럼, M의 범위는 0 에서 255까지.
- TINYBLOB 또는 TINYTEXT
최대길이 255개의 문자를 저장
- BLOB 또는 TEXT
최대 길이가 63535인 문자를 저장
- MEDIUMBLOB 또는 MEDIUMTEXT
최대 길이가 16777215인 문자를 저장
- LONGBLOB 또는 LONGTEXT
최대 길이가 4294967295(4G)인 문자를 저장
* 참고
- BLOB(Binary Large Object의 약자
- BLOB타입은 대소문자를 구분하고 TEXT타입은 대소문자를 구분하지 않는점이 틀림
- MySQL 3.23.2이번 버전에서는 BLOB와 TEXT컬럼에는 인덱스를 만들수 없다
- BLOB와 TEXT컬럼의 저장시에 문자열 됫부분의 공백이 제거되지 않는다.
- BLOB와 TEXT컬럼은 DEFAULT를 지정할 수 없다.
# 숫자 컬럼 타입
- TINYINT
-128부터 127 까지의 정수형 타입, 부호가 없는 정수 0∼255까지 지원
- SMALLINT
-32768부터 32767 까지의 정수형 타입, 부호가 없는 정수 0∼65535까지 지원
- MEDIUMINT
-8388608부터 8388607 까지의 정수형 타입, 부호가 없는 정수 0∼16777215까지 지원
- INT 또는 INTEGER
-2147483648부터 2147483647까지의 정수형 타입, 부호 없는 정수 0∼4294967295까지 지원
- BIGINT
-9223372036854775808 부터 9223372036854775807 까지의 정수형 타입
부호 없는 정수 0∼18446744073709551615까지 지원
- FLOAT(M,D)
단정도 부동 소수점 실수, -3.402823466E+38 ∼ -1.175494351E-38
그리고 1.175494351E-38 ~ 3.402823466E+38까지 M은 숫자 전체의 길이, D는 소수점 자리수를 의미
- DOUBLE(M,D)
2 배 정밀도를 가진 부동 소수점 실수, -1.79769313486231517E+308 ∼ 2.22507385850720E+308
◈ 제약조건
# AUTO_INCREMENT(Oracle : Sequence)
-- 생성예제
mysql>CREATE TABLE sal(
sal_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL);
- AUTO_INCREMENT로 지정된 컬럼 타입은 숫자형이어야 한다 .
- AUTO_INCREMENT는 하나의 테이블에 하나의 컬럼만 지정할 수 있음
- AUTO_INCREMENT로 지정된 컬럼은 반드시 키 또는 인덱스로 정의되어야 한다.
# NOT NULL : NULL값을 허락하지 않음
# PRIMARY KEY : 중복된 데이터를 허락하지 않음, NOT NULL 조건도 추가
# UNIQUE : 중복돈 데이터를 허락하지 않음, NULL값을 허락한다.
# DEFAULT value : 디폴트 값을 지정함
-- 예제..
mysql>CREATE TABLE emp2(
id INT(3) NOT NULL,
name VARCHAR(30) NOT NULL
sal INT(5) DEFAULT 0,
loc VARCHAR(50),
PRIMARY KEY(id, name));
◈ SELECT 문을 이용하여 테이블 생성하기
-- Syntax
mysql>CREATE TABLE new_table SELECT column_list FROM old_table WHERE condition;
-- 예제
mysql>CREATE TABLE emp3 SELECT * FROM emp WHERE deptno = 10;
◈ 테이블 변경하기(ALTER TABLE)
# 컬럼 추가
mysql>ALTER TABLE table_name ADD COLUMN column_name data_type [FIRST|AFTER column_name];
# 컬럼 삭제
mysql>ALTER TABLE table_name DROP COLUMN column_name;
# 컬럼 변경
mysql>ALTER TABLE table_name CHANGE COLUMN old_column new_column new_column_data_type;
# 테이블명 변경
mysql>ALTER TABLE old_table_name RENAME AS new_table_name;
# Primary Key 변경
mysql>ALTER TABLE table_name ADD PRIMARY KEY (column_list);
mysql>ALTER TABLE table_name DROP PRIMARY KEY;
◈ Database 백업
# mysqldump
mysqldump -uscott -ptiger scott > test.sql
# BACKUP TABLE : 테이블을 데이터 파일로 백업함
mysql>BACKUP TABLE table_name[,tbl_name] TO '/path/directory'
# RESTORE TABLE : BACKUP TABLE로 백업한 데이터를 복구한다.
mysql>RESTORE TABLE table_name[,tbl_name] FROM '/path/directory'
◈ 참고 (Oracle => MySQL 비교)
# NVL => IFNULL
# SELECT SYSDATE FROM DUAL => SELECT NOW();
# TO_CHAR => SELECT CAST(NOW() AS CHAR)
mysql은 CAST(expression! AS data_type)또는 CONVERT(expression!,type)로 형변환
oracle : SELECT TO_CHAR(SYSDATE,'RRRR-MM-DD') credate FROM DUAL
mysql : SELECT CAST(DATE_FORMAT(now(),'%Y-%m-%d') AS CHAR) credate;
# DECODE => CASE 예제..
SELECT CASE week WHEN 1 THEN '일요일' WHEN 2 THEN '월요일'
WHEN 3 THEN '화요일' WHEN 4 THEN '수요일'
WHEN 5 THEN '목요일' WHEN 6 THEN '금요일'
WHEN 7 THEN '토요일' END week
FROM STORM_COUNTER
WHERE year = 2002<!--"<-->
ABS(숫자) : 절대값 출력.
select abs(123);
CEILING(숫자) : 값보다 큰 정수 중 가장 작은 수.
양수일 경우는 소숫점 자리에서 무조건 반올림(4.0과 같은 소숫점 자리 0 값은 제외)
음수일 경우는 소숫점 자리를 무조건 버림
select ceiling(4.0);
select ceiling(4.1);
select ceiling(4.9);
FLOOR(숫자) : 값보다 작은 정수 중 가장 큰 수[실수를 무조건 버림(음수일 경우는 제외)].
음수일 경우는 [.0/.00/.000/...] 을 제외하고 무조건 소숫점을 버리고 반내림(?)
select floor(4.0);
select floor(4.1);
select floor(4.9);
select floor(-4.6789);
ROUND(숫자,자릿수) : 숫자를 소수점 이하 자릿수에서 반올림.(자릿수는 양수,0,음수를 갖을 수 있다.)
자릿수를 생략하면 소숫점이 5 이상일 때 반올림/자릿수를 지정하면 지정한 자리수에서 반올림
select round(4.5);
select round(4.55);
select round(-4.5);
select round(4.556);
select round(4.556,0);
select round(4.556,1);
select round(4.556,2);
select round(45.556,-1);
select round(455.556,-2);
TRUNCATE(숫자,자릿수): 숫자를 소수점 이하 자릿수에서 버림.
만일 자릿수를 소숫점 이전으로 정하면 소숫점이하는 버리고 나머지 값은 0 값으로 처리
예) truncate(9999,-3) --> 9000
또는 자릿수를 소숫점이하로 정하며, 해당숫자가 자릿수보다 소숫점이 모자랄경우 0 값으로 대치
예) truncate(999,3) --> 999.000
반드시 자릿수를 명시해주어야 한다
음수일 경우는 해당자릿수에서 소숫점을 버리면서 무조건 반올림
(자릿수 숫자에서 이후 숫자가 0 일 경우는 제외 / 예)-4.0,0/-400,-2/-4.1230,4)
음수 역시 자릿수를 소숫점이하로 정하며, 해당숫자가 자릿수보다 소숫점이 모자랄경우 0 값으로 대치
또한 자릿수를 소숫점 이전으로 정하면 소숫점이하는 버리고 나머지 값은 역시 0 값으로 처리
POW(X,Y) 또는 POWER(X,Y) : X의 Y승
숫점이 있는 경우도 실행, 단 음수는 양수로 승처리
select pow(-2.5,2);
select pow(1.5,2);
MOD (분자, 분모) : 분자를 분모로 나눈 나머지를 구한다.(연산자 %와 같음)
select mod(12,5); ==> 2
select 12%5; ==> 2
GREATEST(숫자1,숫자2,숫자3...) : 주어진 수 중 제일 큰 수 리턴.
select greatest(100,101,90);
LEAST(숫자1,숫자2,숫자3...) : 주어진 수 중 제일 작은 수 리턴.
select least(100,101,90);
INTERVAL(a,b,c,d.....) : a(숫자)의 위치 반환
두 번째 이후는 오름차순 정렬이 되어야 함
예) INTERVAL(5,2,4,6,8) ==> 2
5는 4와 6사이에 존재, 4~6사이의 위치가 앞에서 2번째
select interval(4,1,2,3,5,6);
ASCII(문자) : 문자의 아스키 코드값 리턴.
SELECT ASCII('문자');
select ascii('A');
CONCAT('문자열1','문자열2','문자열3'...) : 문자열들을 이어준다.
select concat('ASP,','PHP,','SQL',' WEB STUDY');
INSERT('문자열','시작위치','길이','새로운문자열') : 문자열의 시작위치부터 길이만큼 새로운 문자열로 대치
'시작위치' 와 '길이'는 문자열이 아니므로 작은따옴표로 굳이 묶어주지 않아도 된다.
select insert('MySql web study','7','3','offline');
select insert('MySql web study',7,3,'offline');
REPLACE('문자열','기존문자열','바뀔문자열') : 문자열 중 기존문자열을 바뀔 문자열로 바꾼다.
select replace('MySql web study','web','offline');
INSTR('문자열','찾는문자열') : 문자열 중 찾는 문자열의 위치값을 출력
값이 존재하지 않으면 0값 리턴
select instr('MySql web study','s');
select instr('MySql web study','S');
LEFT('문자열',개수) : 문자열 중 왼쪽에서 개수만큼을 추출.
select left('MySql web study',5);
select left('MySql web study','5');
RIGHT('문자열',개수) : 문자열 중 오른쪽에서 개수만큼을 추출.
select right('MySql web study',5);
select right('MySql web study','5');
MID('문자열',시작위치,개수) : 문자열 중 시작위치부터 개수만큼 출력
select mid('MySql web study',7,3);
select mid('MySql web study','7','3');
SUBSTRING('문자열',시작위치,개수) : 문자열 중 시작위치부터 개수만큼 출력
select substring('Mysql web study',11,5);
select substring('Mysql web study','11','5');
LTRIM('문자열') : 문자열 중 왼쪽의 공백을 없앤다.
select ltrim(' web study');
RTRIM('문자열') : 문자열 중 오른쪽의 공백을 없앤다.
select rtrim('web study ');
TRIM('문자열') : 양쪽 모두의 공백을 없앤다.
select trim(' web study ');
LCASE('문자열') 또는 LOWER('문자열') : 소문자로 바꾼다.
select lcase('MYSQL');
select lower('MySQL');
UCASE('문자열') 또는 UPPER('문자열') : 대문자로 바꾼다.
select ucase('mySql');
select upper('mysql');
REVERSE('문자열') : 문자열을 반대로 나열한다.
예) REVERSE('abcde') ==> edcba
select reverse('lqSyM');
IF(논리식,참일 때 값,거짓일 때 값)
논리식이 참이면 참일 때 값을 출력하고 논리식이 거짓이면 거짓일 때 출력한다.
IFNULL(값1,값2)
값1이 NULL 이면 값2로 대치하고 그렇지 않으면 값1을 출력
COUNT(필드명)
NULL 값이 아닌 레코드 수를 구한다.
SUM(필드명)
필드명의 합계를 구한다.
AVG(필드명)
각각의 그룹 안에서 필드명의 평균값을 구한다.
MAX(필드명)
최대값을 구한다.
MIN(필드명)
최소값을 구한다.
NOW() 또는 SYSDATE() 또는 CURRENT_TIMESTAMP()
현재 날짜와 시간 출력
※ 함수의 상황이 숫자인지 문자열인지에 따라
YYYYMMDDHHMMSS 또는
'YYYY-MM-DD HH:MM:SS' 형식으로 반환한다.
예)
select now();
'2001-05-07 09:10:10'
select now() + 0;
20010507091010
CURDATE() 또는 CURRENT_DATE()
현재 날짜 출력
※ 함수의 상황이 숫자인지 문자열인지에 따라
YYYYMMDD 또는
'YYYY-MM-DD 형식으로 반환한다.
예)
select curdate();
'2001-05-07'
select curdate() + 0;
20010507
CURTIME() 또는 CURRENT_TIME()
현재 시간 출력
※ 함수의 상황이 숫자인지 문자열인지에 따라
HHMMSS 또는 'HH:MM:SS' 형식으로 반환한다.
예)
select curtime();
'09:10:10'
select curtime() + 0;
091010
DATE_ADD(날짜,INTERVAL 기준값)
날짜에서 기준값 만큼 더한다.
※ 기준값 : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
예)
select date_add(now(), interval 2 day);
오늘보다 2일 후의 날짜와 시간 출력.
select date_add(curdate(), interval 2 day);
오늘보다 2일 후의 날짜 출력.
DATE_SUB(날짜,INTERVAL 기준값)
날짜에서 기준값 만큼 뺸다.
※ 기준값 : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
select date_sub(now(),interval 2 day);
오늘보다 2일 전의 날짜와 시간 출력.
select date_sub(curdate(), interval 2 day);
오늘보다 2일 전의 날짜 출력.
YEAR(날짜) : 날짜의 연도 출력.
select year('20000101');
select year(20000101);
select year('2000-01-01');
select year(now());
select year(curdate());
select year(date_add(now(),interval 2 year));
select year(date_sub(curdate(),interval 2 year));
MONTH(날짜) : 날짜의 월 출력.
select month('20001231');
select month(20001231);
select month('2000-12-31');
select month(now());
select month(curdate());
select month(date_add(now(),interval 2 month));
select month(date_sub(curdate(),interval 2 month));
MONTHNAME(날짜) : 날짜의 월을 영어로 출력.
select monthname(20021221);
select monthname('20000721');
select monthname('2000-08-10');
select monthname(now());
select monthname(curdate());
select monthname(date_add(now(),interval 17 month));
select monthname(date_sub(curdate(),interval 11 month));
DAYNAME(날짜) : 날짜의 요일일 영어로 출력.
select dayname(20000121);
select dayname('20010123');
select dayname('2001-06-22');
select dayname(now());
select dayname(curdate());
select dayname(date_add(now(),interval 21 day));
select dayname(date_sub(curdate(),interval 333 day));
DAYOFMONTH(날짜) : 날짜의 월별 일자 출력.
select dayofmonth(20030112);
select dayofmonth('20011231');
select dayofmonth('2001-12-23');
select dayofmonth(now());
select dayofmonth(curdate());
select dayofmonth(date_add(now(),interval 56 day));
select dayofmonth(date_sub(curdate(),interval 33 day));
DAYOFWEEK(날짜) : 날짜의 주별 일자 출력(월요일(0),화요일(1)...일요일(6))
select dayofweek(20011209);
select dayofweek('20001212');
select dayofweek('2003-03-21');
select dayofweek(now());
select dayofweek(curdate());
select dayofweek(date_add(now(),interval 23 day));
select dayofweek(date_sub(curdate(),interval 31 day));
WEEKDAY(날짜) : 날짜의 주별 일자 출력(월요일(0),화요일(1)...일요일(6))
select weekday(20000101);
select weekday('20030223');
select weekday('2002-10-26');
select weekday(now());
select weekday(curdate());
select weekday(date_add(now(),interval 23 day));
select weekday(date_sub(curdate(),interval 33 day));
DAYOFYEAR(날짜) : 일년을 기준으로 한 날짜까지의 날 수.
select dayofyear(20020724);
select dayofyear('20001231');
select dayofyear('2002-01-01');
select dayofyear(now());
select dayofyear(curdate());
select dayofyear(date_add(curdate(),interval 44 year));
select dayofyear(date_sub(now(),interval 25 month));
select dayofyear(date_add(now(),interval 55 day));
select dayofyear(date_sub(curdate(),interval 777 hour));
select dayofyear(date_add(now(),interval 999999 minute));
WEEK(날짜) : 일년 중 몇 번쨰 주.
select week(now());
select week(date_sub(curdate(),interval 12 month));
FROM_DAYS(날 수)
00년 00월 00일부터 날 수 만큼 경과한 날의 날짜 출력.
※ 날 수는 366 이상을 입력 그 이하는 무조건 '0000-00-00' 으로 출력.
또한 9999-12-31 [from_days(3652424)] 까지의 날짜가 출력가능 하다고는 하나
정확히 말하면 0000-03-15 [from_days(3652499)] 까지의 날짜가 출력가능함.
따라서 날 수는 366 이상 3652424[3652499] 이하가 되어야 한다.
select from_days(3652424);
select from_days('3652499');
TO_DAYS(날짜)
00 년 00 월 00일 부터 날짜까지의 일자 수 출력.
from_days와 비교해 볼 때 정확한 날짜범위는 3652424 일 수 까지임을 알 수 있다.
select to_days('99991231');
select to_days('0000-03-15');
응용 예제1) 자신이 살아 온 날수
select to_days(now()) - to_days('본인생일자');
select to_days(now()) - to_days('1970-10-10');
응용 예제2) 살아 온 날수를 이용하여 자신의 나이를 만으로 구하기
select (to_days(now())-to_days('1970-10-10'))/365;
select floor((to_days(now())-to_days('19701010'))/365);