JPA Domain 을 디비에 자동으로 생성되게 해놓았는데


AWS RDS 에 DB 를 새로 세팅하고 MySQL 에서 Foreign key 걸때 아래와 같이 에러가 났다.


Error Code: 1215. Cannot add foreign key constraint


이것저것 보다보니 Dump 떠서 생성했던 기존 Table.Column 의 Collation 과 JPA 가 생성한 Collation 이 다르네


하나는 utf8_general_ci, 다른 하나는 utf8_bin 


두개가 서로 맞지않으면서 foreign key 생성이 안되던 문제


해결책은 아래



http://confluence.goldpitcher.co.kr/pages/viewpage.action?pageId=138838813

YOUR COMMENT IS THE CRITICAL SUCCESS FACTOR FOR THE QUALITY OF BLOG POST



DB에 저장되어있는 컬럼 중에 DATE 타입에 대한 연산이 필요할 때가 있다.

예를 들어 지금시간으로부터 12시간안의 모든 레코드를 필요로 한다던지,

지금시간으로부터 12시간 이전의 자료를 모두 삭제한다던지 할때 DATE 형이 시간이 기록되어 있다면

시간까지 연산가능하고, 만약 시간이 기록되지 않은 DATE 타입이면 날짜연산만 가능하다. 




UPDATE sample SET state = 'done' WHERE date < DATE_ADD(now(), INTERVAL -6 hour)

 ↑ 해석 : sample 테이블의 date 컬럼의 시간이

             현재(now()) 시간으로부터 6시간전의 시간을 기준으로 그 이전시간 레코드의
             state 컬럼을 'done'으로 다 입력한다. 




DATE_ADD(now(), INTERVAL -6 hour)

↑ 이 문장에서 hour 대신에 year, day, minute, second 로 바꿀 수 있다.

맨위 코드에서 '<' 를 '>' 로 바꾸게되면

6시간전을 기준으로 이전레코드가 아니라, 현재시간으로부터 6시간내의 자료만 가져올 것이다.


헷갈린다면 한번 해보면 확실할 것.

YOUR COMMENT IS THE CRITICAL SUCCESS FACTOR FOR THE QUALITY OF BLOG POST



제목 그대로다

 PreparedStatement 에서 LIKE 를 쓰고 싶을때, '?' 와 '%' 합치는 법

출처 :  http://mainia.tistory.com/557 녹두장군님



웹 어플리케이션 작업시 sql 쿼리를 보통 properties xml 로 문자열을 만들어 빼놓는다.

예전처럼 무식하게 쿼리를 문자열 합치기로 소스상에서 사용하지는 않는다여러가지 문제가

많기 때문이다.

 

쿼리에서 동적으로 변하는 데이타는 ? 를 써서 문자열을 만들고 PreparedStatement 사용해

파라미터 값을 맵핑시킨다아래는 스프링의 예이다스프링은 framework 내부에 PreparedStatement

사용해서 맵핑을 구현해 놓았고 우리가 사용하는 부분은 아래와 같이 함수에 파라미터로

갯수에 맞게 Objec 배열로 넘기면 된다.

 

쿼리문자열

test.srch.select = \n\

           select * from board where id = ? \n\

 

String sql = message.getMessage("test.srch.select");

getJdbcTemplate().queryForList(sql, new String[]{srchText});

 

일반적인 쿼리문자열은 이렇게 적용을 하면 되는데 문제는 like 를 사용할때 이다.

보통 사용하는 것처럼 like %?% 로 하면 안된다. % 문자와 ? 맵핑할 문자를 합쳐야 된다.

RDBMS 프로그램에 따라 차이가 있다. Oracle 일때에는  like '%' || ? || '%' 사용하면 된다.

중간에 ORM framework 인 iBatis 를 사용할 경우에는 like '%' || #?# || '%' 이다.

 

 

하지만 MySql 일 경우에는 이것이 통하지 않는다그래서 문자열을 합치는 함수를 사용해

해결하였다일반적인 경우는 like concat ('%', ?, '%') 이며 

iBatis 
라고 한다면

like concat ('%', #?#, '%') 사용해서 처리 하면된다.



 
YOUR COMMENT IS THE CRITICAL SUCCESS FACTOR FOR THE QUALITY OF BLOG POST


mysql-인덱스를 생성하는 방법]

 

인덱스 만들기

1. 추가하여 만들기

    CREATE INDEX <인덱스명> ON <테이블명> ( 칼럼명1, 칼럼명2, ... );

 

2. 테이블 생성시 만들기

    끝에....

    INDEX <인덱스명> ( 칼럼명1, 칼럼명2 )

    UNIQUE INDEX <인덱스명> ( 칼럼명 )  --> 항상 유일해야 함.

 

3. 이렇게도 생성한다

    ALTER TABLE <테이블명> ADD INDEX <인덱스명> ( 칼럼명1, 칼럼명2, ... );

 

4. 인덱스 보기

    SHOW INDEX FROM <테이블명>;

 

5. 인덱스 삭제

    ALTER TABLE <테이블명> DROP INDEX <인덱스명>;

 

///////////////////////////////////////////////////////////////////

 

인덱스 파일은 "테이블명.MYI"파일로 DB 디렉토리 아래 저장..

 

mysql> create table test1( 
    -> var1 int not null auto_increment primary key,
    -> var2 int not null,
    -> var3 char(30) not null,
    -> index idx1(var1) <-- 인덱스지정
    -> );
Query OK, 0 rows affected (0.06 sec)

 

mysql> desc test1 <-- index는 MUL로 표시된다.

mysql> show keys from test1; <-- key 볼 수 있다.
====================================================

 

mysql> CREATE INDEX idx1 ON test1(var1);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

제거는..DROP INDEX or Alter table

 

YOUR COMMENT IS THE CRITICAL SUCCESS FACTOR FOR THE QUALITY OF BLOG POST


많은 분들이 mysql 테이블을 생성할 때 index를 주는데, 실제로 보니까 index를 잘못주는 경우가 많더군요. 

1. 인덱스를 검사하는 법. 
explain select * from Table_Name where A='a' and B='b' order by C,D,E ; 
해당 쿼리문이 인덱스를 타는지 안타는지 알기 위해서는 쿼리문 앞에 explain을 붙여주면 인덱스를 타는지 안타는지 알 수 있습니다. 
type의 결과값이 ALL일 경우 인덱스를 타지 않고 있습니다. range,index등일 때 인덱스를 타고 있습니다.(system,const,eq_ref,range,index,ALL,fulltext) 
key의 값이 해당 쿼리문이 타고 있는 인덱스입니다. 

2. 경우의 수가 작은 것은 인덱스를 타지 않습니다. 
가령 특정 테이블에서 성별을 구분하는 테이블이 있고, 이 테이블에서 성별로 구분해서 보여주는 경우가 많다고 해서 성별을 나타내는 필드에 인덱스를 걸어주어도 인덱스를 타지 않습니다. 
인덱스는 결과값이 1/3미만일 경우에만 인덱스를 탑니다. 그러므로 성별같은 경우에는 인덱스를 걸어주는 것이 테이블의 용량만 키우는 결과이기 때문에 인덱스를 걸어주지 않는 것이 좋습니다. 
이것은 PRIMARY KEY에도 적용이 됩니다. 
Table_Name 테이블에서 No 필드가 Primary Key, A 필드에 Index가 걸려있고, 데이타수가 30000개일 경우. 
select * from Table_Name where No>0 and A>0; // 인덱스 안탐. 
select * from Table_Name where No>20000 and A>0; // Primary 인덱스를 탐. 
select * from Table_Name where No>0 and A>20000; // A 인덱스를 탐. 

3. 인덱스는 하나만 탑니다. 
오라클등에서는 인덱스를 여러개 걸어주면 그것을 다 타지만 mysql에서는 인덱스를 하나밖에 타지 않습니다. 
가령 Table_Name 에서 A필드와 B필드, C필드에 인덱스를 걸어주고, 아래와 같은 쿼리문을 날립니다. 
select * from Table_Name where A='a' and B='b' and C='c'; 
이럴경우 세개의 인덱스중 하나의 인덱스만 탑니다. 

4. 결합인덱스 사용법. 
특정 테이블에서 동일한 쿼리문을 주로 사용하고 그 쿼리문이 아래와 같을 경우... 
select * from Table_Name where A='a' and B='b' and C='c'; 
이경우 이 세개의 인덱스를 전부 타기 위해서는 결합인덱스를 걸어줘야 됩니다. 
ALTER TABLE Table_Name ADD INDEX (A,B,C); 
이렇게 인덱스를 줄 경우에 세개의 필드를 정렬해서 하나의 인덱스를 생성하기 때문에 해당인덱스를 탈 수 있습니다. 

5. order by 에서 사용하는 인덱스. 
select * from Table_Name order by A; -> 이경우에 A인덱스를 탑니다. 
select * from Table_Name where B='b' order by A; -> 이경우 B인덱스만 탑니다. 
이경우에 두개의 인덱스를 다 타게 하려면 결합인덱스로 (A,B)를 생성시키고, A의 조건을 where 문에 지정을 해줍니다.(이부분은 사실 저도 조금 헷갈리는 부분입니다. 어쨌든 며칠동안 인덱스를 살펴본 결과 이렇게 해줘야 될거 같아서 일단 적습니다. ㅠ.ㅠ) 

6. 검색에서 사용하는 인덱스. 
보통 본문검색을 할 때 [where A like '%a%' ]와 같은 방식으로 조건을 주는데, 이경우 인덱스를 타지 않습니다. 가끔보면 검색에 주로 사용되는 필드에 인덱스를 거는 경우를 볼 수 있는데, 쓸데없는 짓입니다. 
단, [where A like 'a%']와 같이 검색을 하는 경우에는 인덱스를 탑니다. 

7. join문에서의 인덱스. 
개인적으로 join문을 잘 사용하지 않는데, 그 이유가 join문을 사용할 경우 속도문제 때문이었습니다. 지금은 join문에서 인덱스를 타는 경우에 대해서 테스트해보고, 이것은 나중에 결과를 보고 글을 올려보도록 하겠습니다.

 

www.nuno21.net


YOUR COMMENT IS THE CRITICAL SUCCESS FACTOR FOR THE QUALITY OF BLOG POST


1. 테이블 이름 바꾸기

ALTER TABLE `원래 테이블 이름` RENAME `수정할 테이블 이름`


2. 구조만 복사

CREATE TABLE `생성될 테이블명` LIKE `구조를 복사할 테이블`;

 

3. 구조와 내용복사

CREATE TABLE `생성될 테이블명` AS SELECT * FROM `구조를 복사할 테이블`;

YOUR COMMENT IS THE CRITICAL SUCCESS FACTOR FOR THE QUALITY OF BLOG POST



MySQL 관련 10가지 TIP
작성자: albumbang.com/ 어라님    작성일: 2009-07-07 10:47   조회: 1049   추천: 0   댓글: 0
처음으로 MySQL을 배우려는 사람에게 이 소프트웨어는 당황스럽게 보일 수도 있다. 본기사에서는 MySQL 관리자, 설계자, 개발자에게 MySQL 설치와 관련된 퍼포먼스, 보안, 유지에 도움을 줄 수 있는 좋은 예제들을 설명할 것이다. 

1. "root" 사용자 패스워드를 설정하고 사용자 명을 바꿔라. 
MySQL을 설치한 후 가장 먼저 해야 할 일은 루트 사용자 패스워드를 설정하는 것이다. 
[01:19:00] george@firenze$ mysqladmin -u root password 'somepassword' 
일단 패스워드를 설정했다면 '루트' 사용자의 이름을 다른 것으로 바꾸자. MySQL 서버를 공격하려는 해커는 수퍼유적의 권한을 갖고 있고 잘 알려진 사용자이기 때문에 대부분의 시스템에 존재하는 루트를 목표로 한다. '루트' 사용자의 이름을 바꾸면 해커일 수도 있는 공격자들이 야만스러운 공격을 시도하기 여렵게 되기 때문이다. 아래 명령은 '루트' 사용자 이름을 바꾸어 준다. 

[01:25:29] george@firenze$ mysql -u root -p mysql
Enter password: 
Welcome to the MySQL monitor. Commands end with ;or \g.
Your MySQL connection id is 72 to server version: 4.0.0-alpha-log 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
mysql> UPDATE user set user = 'admin' where user = 'root'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 
mysql> quit; Bye [01:25:51] george@firenze$ mysqladmin -u root -p reload Enter password: 
물론, 'admin' 보다 더 창의적인 이름을 택할 수도 있다. 

2. 인터넷에서 MySQL을 숨겨라. 
MySQL은 네트워크 서비스의 보안을 위한 매우 구체적인 레코드가 있다. 그럼에도 불구하고 인터넷에 직접적으로 MySQL을 노출하지 않는 것이 좋다. 방화벽 뒤에 MySQL을 숨기고 운영중인 애플리케이션 서버나 웹서버와 통신할 수 있게 될 때, 해커일 수도 있는 공격자가 공격할 수도 있는 경로를 저지할 수 있기 때문이다. 

3. 다른 사용자들에 의해 MySQL 설치 디렉토리가 액세스되는 것을 보호하라. 
첫째, MySQL은 'mysql' 같은 특정 사용자 아이디로 설치되어야 한다. 둘째, MySQL이외에 어느 사용자도 시스템의 MySQL 데이터 디렉토리에 접근할 수 없어야 한다. 다른 사용자가 당연스럽게 디렉토리에 접근할 수 있게 되면 MySQL 내부 보안과 절충하는 경로를 시작해야 한다. 데이터베이스 관리자라도 'mysql' 그룹에 가입되어서는 안된다. 대신, 대부분의 DBA 기능은 MySQL 자체를 통과하도록 한다. 파일 시스템 단계에서 일어날 필요가 있는 몇몇 과정에 한해서 DBA가 MySQL 사용자로 로그인 해야 한다. 

4. MySQL에 바이너리 데이터를 저장하지 말아라 
MySQL이 바이너리 타입을 지원하는 것은 사실이나, 그럴 수 있어도 포함시키지 않는게 좋다. MySQL은 결과를 한꺼번에 클라이언트로 보낸다. 따라서 바이너리 데이터를 결과값으로 파싱하는 애플리케이션은 모두 다 처리되기 전에 도착하는 각 결과값을 기다려야 한다. 게다가 MySQL에 바이너리를 저장해도 이득이 될 게 없다. 
바이너리 데이터에 접근하는 좀더 나은 방법은 데이터를 파일 시스템에 저장하고 MySQL에 있는 이러한 파일들의 포인터를 저장하는 것이다. 이런 접근으로 여러분은 실제적으로 결과값을 처리하는 동안 백그라운드 스레드에 있는 바이너리 데이터를 스트림할 수 있다. 
이런 팁은 단지 바이너리 데이터에만 적용되는 것이 아니다. 큰 데이터 오브젝트 어느 것이나 적용될 수 있다. 바이너리 데이터를 귀찮게 하는 성능 문제는 역시 문자 데이터도 귀찮게 한다. 다시 말해 결과 값의 일부분을 읽는 것은 연속적으로 완료된다. 대개 그 값이 크기 때문에 바이너리 데이터에서는 쉽게 알 수 있을 것이다. 큰 문자 데이터를 처리할 때와 마찬가지로 쉽게 알 수 있다. 파일 시스템에 대규모 문자 데이터를 저장하는 것과 데이터베이스에 저장하는 것에 반해 탐색할 수 있는 성능상의 이익으로는 어떤 것이 더 좋은지 확인해봐야 한다. 

5. ANSI SQL과 밀착 
MySQL은 ANSI 표준보다 프로그래머에게 더 매력적인 편리한 부가물을 제공한다. 이런 부가물들은 멀티테이블 삭제나 멀티로우 삽입과 같은 시간을 절약해 주는 도구들을 포함한다. 이와 같은 MySQL 사양에 의존하면 애플리케이션이 다른 데이터베이스 엔진으로 이식하는 능력을 제한하게 된다. 사실 다시 심각하게 작성하지 않고 다른 데이터베이스로 이식하는 것이 불가능할 지도 모른다. 따라서 최대한의 이식성을 위해 애플리케이션을 ANSI SQL에 밀착되도록 작성해야 한다. 
다른 한 편으로는 이런 도구들을 무시하라는 인상은 주고 싶지 않다. 도구들도 MySQL 유지와 관련하여 나름대로의 역할이 있기 때문이다. MySQL 명령줄에서 작업하거나 MySQL 설치 유지를 위해 구체적으로 스크립트를 만들 때 이런 도구가 가져다주는 편리함을 무시하는 것은 어리석은 일이다. 

6. 자신의 시퀀스 생성 스키마를 만들어라. 
이 지침은 위의 5번째 항목과 관련되어 있다. MySQL은 auto_increment 키워드를 사용하여 독특한 식별자를 만들 수 있는 MySQL용 도구을 제공한다. 구체적으로 MySQL은 한 줄을 삽입할 때마다 각 테이블마다 한 컬럼에 자동으로 생성되는 값을 정의할 수 있다. 이 기능은 안타깝게도 MySQL에만 있으며 다음과 같은 약간의 제약이 있다. 
한 테이블에 오직 하나의 auto_increment를 갖는다. 
여러 테이블을 위해 연속된 유일값을 가질 수 없다. 예를 들어 분리된 테이블에서 컬럼들이 유일성을 보장받기 위해 auto_increment를 사용할 수 없기 때문에 한 테이블에 유일한 값은 다른 테이블에서도 나타낼 수 없다. 
애플리케이션을 위해 MySQL이 자동으로 생성하는 값들을 쉽게 바꿀 수 없다. 
자동 생성값으로 접근은 자신만의 크로스 플랫폼으로 개발하는 것이 좋다. 오라일리에서 발간될 『Java Best Practices:J2EE Edition』에서 필자는 그런 접근 방식으로 설명했다. 한마디로 이런 접근은 다른 연속값을 줄 수 있는 데이터베이스의 특별한 테이블 생성과 관련있다. 원하는 만큼 많은 연속값을 가질 수 있으며 여러 컬럼에 걸쳐 연속값들을 공유할 수도 있다. 단순하게 이름으로 연속값에 접근하면 된다. 그러면 애플리케이션은 메모리에 유일값을 생성하기 위해 이런 특별한 테이블로부터 시드(seed)를 사용한다. 시드로 인해 가능한 연속값들의 리스트가 고갈될 때까지 데이터베이스로 되돌아갈 필요가 없다. 

7. 출력 코드와 데이터베이스 코드를 섞지 마라 
출력 코드와 데이터베이스 코드가 섞여 있는 애플리케이션은 유지하기가 힘들다. 그런 특이한 한 예는 JDBC 코드를 포함하는 JSP 페이지이다. 이런 상황이 발생해서는 안된다. 
대신 model-view-controller(MVC) 디자인 패턴에 따라 애플리케이션 로직을 나눌 때 애플리케이션은 훨씬 유지하기 수월하다. 이런 좋은 예는 웹 프로그래밍과 GUI 애플리케이션 둘다 적용된다. 한마디로 MVC는 코드를 모델(데이터베이스 요소 하우징)과 뷰(사용자 인터페이스를 표현하는 요소), 컨트롤러(사용자 행동을 컨트롤하는 객체)로 나누게 한다. 

8. 열정적으로 정규화하고, 비정규화를 적게하라. 
정규화는 데이터베이스 디자인에서 중복된 데이터를 제거하는 과정이다. 중복성을 제거해서 데이터 일치성을 유지하기 위한 공간을 최소화해야 한다. 결과적으로 시스템 유지가능성이 늘어나기 때문이다. 데이터 모델을 제3정규화(3NF)형태로 항상 유지해라. 
어떤 경우에는 정규화가 성능 저하를 초래하기도 한다. 그러나 이런 성능 저하는 사람들이 믿을 수 있게 될 정도로 자주 일어나지는 않는다. 이와 같이 성능 향상을 위해 정규화를 절충해 데이터 모델을 최적화 과정을 비정규화라고 부른다. 정규화를 거치지 않은 데이터 모델이 비정규화되지 않은 데이터베이스라는 것도 알아두어야 한다. 대신 그것은 unmormalized라 불린다. 
비정규화는 구체적인 성능 향상을 위해 정규화에 의해 제거된 데이터 모델로의 매우 의도적이고 신중한 과정이다. 데이터 모델이 완전히 정규화된 후에 일어나야 하며 장점이 쉽게 증명될 수 있을 때에만 발생해야 한다. 
9. 웹 서버와 애플리케이션 서버에서 connection pooling을 사용해라. 
데이터베이스로의 연결은 돈이 많이 드는 작업이다. 클라이언트/서버 애플리케이션에서는 이런 비용이 대부분 알려지지 않고 발생한다. 그러나 웹 서버나 애플리케이션 서버는 지속적으로 데이터베이스와 사용자 세션을 시작했다 끊기 때문에 새로운 연결이 생성되면 바빠진다. 다행히도 대부분의 프로그래밍 언어들은 요즘 데이터베이스에 pooling을 위한 도구를 제공한다. 다시 말해 connecting pool은 계속해서 연결을 재시작할 필요없이 수많은 사용자 세션을 위해 같은 연결을 재사용할 수 있게 해준다. 어떤 프로그래밍 언어를 선택하든지 이런 지원사항을 미리 알아서 챙기는 것도 이득이 될 것이다. 
10. EXPLAIN SELECT로 퀴리를 튜닝해라. 
EXPLAIN SELECT는 알아 두어야 할 중요 명령어이다. 이것의 결과값은 단일 쿼리를 실행하기 전에 어떻게 SQL이 작동할 것인가를 이해하는데 도움을 준다. 역시 인덱스 생성과 같이 변경할 필요가 있는 곳을 알려줄 수 있다.
YOUR COMMENT IS THE CRITICAL SUCCESS FACTOR FOR THE QUALITY OF BLOG POST


MySQL 함수
숫자 관련 함수
▶ 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);

▶ DATE_FORMAT(날짜,'형식') : 날짜를 형식에 맞게 출력
DATE타입 구분기호 설명 구분기호 설명
년도 %Y 4자리 연도 %y 2자리 년도
%M
%b
긴 월 이름 (January, ...)
짧은 월 이름(Jan, ...)
%m
%c
숫자의 월 (01...12)
숫자의 월 (1...12)
요일 %W 긴 요일 이름 (Sunday, ...) %a 짧은 요일 이름 (Sun, ...)
%D
%w
월 내에서 서수 형식의 일(1th, ...)
숫자의 요일 (0=Sunday, ...)
%d
%e
%j
월 내의 일자 (01...31)
월 내의 일자 (1...31)
일년 중의 날수 (001...366)
%l
%h
%I
12시간제의 시 (1...12)
12시간제의 시 (01...12)
12시간제의 시 (01...12)
%k
%H
12시간제의 시 (0...23)
12시간제의 시 (00...23)
%i 숫자의 분 (00...59)
%S 숫자의 초 (00...59) %s 숫자의 초 (00...59)
시간 %r 12시간제의 시간 (hh:mm:ss AM 또는 PM) %T 24시간제의 시간 (hh:mm:ss)
%U 일요일을 기준으로 한 주 (0...52) %u 월요일을 기준으로 한 주 (0...52)
기타 %% 문자 '%' %p AM 또는 PM

   ☞ 예)
   select date_format(now(),'%Y:%M:%p');
   ==> 2001:May:PM

▶ DATABASE() : 현재의 데이터베이스 이름을 출력한다.

▶ PASSWORD('문자열')
: 문자열을 암호화한다.

▶ FORMAT(숫자,소수이하자리수) : 숫자를 #,###,###.## 형식으로 출력
   --임의의 소수점자릿수를 생성한다./소숫점을 필요한 만큼 취한다.
   --소숫점을 만들어 같은 길이로 한다음 동일하게 프로그램에서 불러와서 소숫점을 버리고
      필요한 곳에 출력하는 등에 응용할 수 있다.
   select format(123,5);
   select format(123.12345600123,9);
   select format(123.123,-3);
   ※ 소숫점이하자리수가 0 이나 음수값은 해당이 안됨
YOUR COMMENT IS THE CRITICAL SUCCESS FACTOR FOR THE QUALITY OF BLOG POST


MySQL Query(쿼리)문 모음

출처 : 하로기님의 블로그 ( http://harogipro.tistory.com/57 )


1. show databases; 는 데이터베이스들을 보여준다. 
     create database 데이터베이스명 ; 은 데이터베이스를 생성한다. 
     그러나 실제 mysql 관리자(서버관리자)가 아닌 이상 이 명령어를 사용할 수가 없다. 
     호스팅업체에서 대개는 자신의 계정아이디와 동일한 DB하나만 서비스해주기 때문에 
     직접 이 명령어를 사용하진 못한다. 

2. use 데이터베이스 : 사용할 데이터 베이스를 선택한다. 실제 호스팅인 경우 바로
    바로 데이터베이스 안으로 접속되는 경우가 많다. 
    show tables ;  테이블의 목록 출력 
     - DB는 테이블 형태로 데이터가 저장된다.

테이블 생성 
   create table 테이블 명 ( 컬럼명 데이터형식 널값여부 기타옵션);
  auto_increment 는 자동으로 번호를 증가시켜준다. 
  primary key 는 고유값 설정으로 똑같은 값은 절대 받지 않는다는 뜻.

  *** mysql 각종 데이터형들 
 tinyint 부호 있는 정수 -128 ~ 127 
부호 없는 정수 0 ~255 
1 Byte 

SMALLINT 부호 있는 정수 -32768 ~ 32767 
부호 없는 정수 0 ~65535 
2 Byte 

MEDIUMINT 부호 있는 정수 -8388608 ~ 8388607 
부호 없는 정수 0 ~16777215 
3 Byte 

INT 또는 INTEGER 부호 있는 정수 -2147483648 ~ 2147483647 
부호 없는 정수 0 ~4294967295 
4 Byte 

BIGINT 부호 있는 정수 -9223372036854775808 ~ 9223372036854775807 
부호 없는 정수 0 ~18446744073709551615 
8 Byte 

FLOAT 단일 정밀도를 가진 부동 소수점 
-3.402823466E+38 ~3.402823466E+38 

DOUBLE 2 배 정밀도를 가진 부동 소수점 
-1.79769313486231517E+308 ~ 1.79769313486231517E+308 

DATE 날짜를 표현하는 유형 
1000-01-01 ~ 9999-12-31 

DATETIME 날짜와 시간을 표현하는 유형 
1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 

TIMESTAMP 1970-01-01 00:00:00 부터 2037년 까지 표현 
4 Byte 

TIME 시간을 표현하는 유형 
-839:59:59 ~ 838:59:59 

YEAR 년도를 표현하는 유형 
1901 년 ~ 2155년 

CHAR(M) 고정길이 문자열을 표현하는 유형 
M = 1 ~255 

VARCHAR(M) 가변길이 문자열을 표현하는 유형 
M = 1 ~ 255 

TINYBLOB
TINYTEXT 255개의 문자를 저장 
BLOB : BINARY LARGE OBJECT의 약자 

BLOB
TEXT 63535개의 문자를 저장 

MEDIUMBLOB
MEDIUMTEXT 16777215개의 문자를 저장 

LONGBLOB
LONGTEXT 4294967295(4Giga)개의 문자를 저장


 3. desc 테이블 명 ; 테이블의 각 컬럼 형식 보기



4. 데이터 입력하기 

5.한꺼번에 데이터 입력하기


6. no 필드에 값을 입력하지 않아도 자동적으로 증가하는 것을 볼 수 있다.


7. 원하는 필드만 선택할때...


8. 조건으로 검색하기



9. 내림차순 정렬하기


10.오름차순정렬


11. 조건절과 정렬 함께 사용하기


12.데이터 수정하기(조건절이 없으면 전부 바뀐다.)


13. 데이터 삭제(조건이 없으면 전부 삭제된다)


14. 컬럼(필드) 추가해보기


15. 컬럼 삭제해보기


16. 컬럼 수정해보기



17. 테이블 삭제해보기


18. 합계 연습을 위해 임시 테이블 만들었음


19. 필드의 최대, 최소, 평균, 합계구해보기 
    as 임시필드명 해주면 임시로 필드명이 생성된다.


20. 필드의 총 개수 구해보기


21. 한꺼번에 최대값과 합산값, 평균구하기.
     between 으로 범위값 내에 있는 필드 구하기
    in 으로 지정한 필드만 뽑아내기


22. not in 은 그것을 제외한 필드를 구한다.
     %는 like 와 함께 쓰이며 '%강%'은 강을 기준으로 강을 포함한 앞뒤문자검색을 해준다.


23. a 이후에 문자열 검색
     b 이전에 문자열 검색


24. limit는 레코드 처음부터 2개만 뽑아온다. 범위와 함께 쓰일 수도 있다.


25. limit 시작레코드번호, 뽑아올 레코드 갯수


26. 컬럼명 바꾸기(컬럼명을 바꿀땐 데이터도 같이 바꿔줘야 한다.)
     테이블 명 바꾸기....(아래참고)



27. 날짜형 데이터넣기
     now() 함수는 날짜를 가지고 있는 내장합수인데 선언한 데이터형에 따라 들어가는 값이 
     아래처럼 다르게 들어간다.


'♨ DataBase > MySQL' 카테고리의 다른 글

MySQL 관련 10가지 TIP  (0) 2011.07.22
Mysql 함수 모음  (0) 2011.07.22
MySQL Query(쿼리)문 모음  (0) 2011.07.22
MYSQL 날짜 데이터 타입  (0) 2011.07.22
mysql 컬럼 삭제, 추가, 변경  (0) 2011.07.22
mysql에서 LIMIT의 사용법  (0) 2011.07.22
YOUR COMMENT IS THE CRITICAL SUCCESS FACTOR FOR THE QUALITY OF BLOG POST


출처 나는 운명보다 살아가는 힘에 더 큰 존애를 느낀다. | 아설
원문 http://famlilia.blog.me/100038815352
  • DATE_FORMAT(date,format)

format 스트링에 따라서date 값을 포맷한다.

아래에 나와 있는 지정자 (specifier)들은 format 스트링안에서 사용할 수 있다. ‘%’ 문자는 지정자 문자를 포맷하기 전에 필요한 것이다.

Specifier

Description

%a

Abbreviated weekday name (Sun..Sat)

%b

Abbreviated month name (Jan..Dec)

%c

Month, numeric (0..12)

%D

Day of the month with English suffix (0th1st2nd3rd, …)

%d

Day of the month, numeric (00..31)

%e

Day of the month, numeric (0..31)

%f

Microseconds (000000..999999)

%H

Hour (00..23)

%h

Hour (01..12)

%I

Hour (01..12)

%i

Minutes, numeric (00..59)

%j

Day of year (001..366)

%k

Hour (0..23)

%l

Hour (1..12)

%M

Month name (January..December)

%m

Month, numeric (00..12)

%p

AM or PM

%r

Time, 12-hour (hh:mm:ss followed by AM or PM)

%S

Seconds (00..59)

%s

Seconds (00..59)

%T

Time, 24-hour (hh:mm:ss)

%U

Week (00..53), where Sunday is the first day of the week

%u

Week (00..53), where Monday is the first day of the week

%V

Week (01..53), where Sunday is the first day of the week; used with %X

%v

Week (01..53), where Monday is the first day of the week; used with %x

%W

Weekday name (Sunday..Saturday)

%w

Day of the week (0=Sunday..6=Saturday)

%X

Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V

%x

Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v

%Y

Year, numeric, four digits

%y

Year, numeric (two digits)

%%

A literal ‘%’ character

%x

x, for any ‘x’ not listed above

달 및 날짜 지정자를 위한 범위는 0에서부터 시작을 한다..

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> SELECT DATE_FORMAT('1999-01-01', '%X %V');

        -> '1998 52'

mysql> SELECT DATE_FORMAT('2006-06-00', '%d');

        -> '00'

  • DAY(date)

DAY() is a synonym for DAYOFMONTH().

  • DAYNAME(date)

date에 대한 주간 요일 이름을 리턴한다.

mysql> SELECT DAYNAME('1998-02-05');

        -> 'Thursday'

  • DAYOFMONTH(date)

0에서 31 사이의 달별 날짜를 리턴한다.

mysql> SELECT DAYOFMONTH('1998-02-03');

        -> 3

  • DAYOFWEEK(date)

date에 대한 주간 요일 인덱스를 리턴한다 (1 = Sunday, 2 = Monday, …, 7 = Saturday). 이러한 인덱스들은 ODBC 표준을 따른다.

mysql> SELECT DAYOFWEEK('1998-02-03');

        -> 3

  • DAYOFYEAR(date)

1에서 366 사이의 date에 해당하는 일수를 리턴한다.

mysql> SELECT DAYOFYEAR('1998-02-03');

        -> 34

  • EXTRACT(unit FROM date)

EXTRACT() 함수는 DATE_ADD() 또는 DATE_SUB()과 같은 종류의 유닛 지정자를 사용하지만날짜 산술식을 실행하는 것이 아닌 날짜에서 부분을 추출하는 기능을 수행한다..

mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');

       -> 1999

mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');

       -> 199907

mysql> SELECT EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03');

       -> 20102

mysql> SELECT EXTRACT(MICROSECOND

    ->                FROM '2003-01-02 10:30:00.00123');

        -> 123

  • FROM_DAYS(N)

주어진 날짜 숫자 N에 대해서 DATE 값을 리턴한다.

mysql> SELECT FROM_DAYS(729669);

        -> '1997-10-07'

  • FROM_UNIXTIME(unix_timestamp)FROM_UNIXTIME(unix_timestamp,format)

unix_timestamp 인수에 대한 표현식을 이 함수가 사용된 문장에 따라서 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 포맷으로 리턴한다unix_timestamp는 UNIX_TIMESTAMP() 함수가 만들어 내는 것과 같은 내부 타임 스탬프 값이다.

만일 format 을 주게 되면그 결과는 format 스트링에 따라서 포맷이 되는데이것은 DATE_FORMAT() 함수에 대한 엔트리에 목록화 되는 방식과 같은 방식을 사용한다.

mysql> SELECT FROM_UNIXTIME(875996580);

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

mysql> SELECT FROM_UNIXTIME(875996580) + 0;

        -> 19971004222300

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),

    ->                      '%Y %D %M %h:%i:%s %x');

        -> '2003 6th August 06:22:58 2003'

  • GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL')

포맷 스트링을 리턴한다이 함수는 DATE_FORMAT() 및 STR_TO_DATE() 함수를 결합하는데 있어서 매우 유용한 것이다.

첫 번째 인수와 두 번째 인수에 대해서 몇 가지 포맷 스트링 값이 사용 가능하다이 값들은 ISO 9075를 참조한다.

Function Call

Result

GET_FORMAT(DATE,'USA')

'%m.%d.%Y'

GET_FORMAT(DATE,'JIS')

'%Y-%m-%d'

GET_FORMAT(DATE,'ISO')

'%Y-%m-%d'

GET_FORMAT(DATE,'EUR')

'%d.%m.%Y'

GET_FORMAT(DATE,'INTERNAL')

'%Y%m%d'

GET_FORMAT(DATETIME,'USA')

'%Y-%m-%d-%H.%i.%s'

GET_FORMAT(DATETIME,'JIS')

'%Y-%m-%d %H:%i:%s'

GET_FORMAT(DATETIME,'ISO')

'%Y-%m-%d %H:%i:%s'

GET_FORMAT(DATETIME,'EUR')

'%Y-%m-%d-%H.%i.%s'

GET_FORMAT(DATETIME,'INTERNAL')

'%Y%m%d%H%i%s'

GET_FORMAT(TIME,'USA')

'%h:%i:%s %p'

GET_FORMAT(TIME,'JIS')

'%H:%i:%s'

GET_FORMAT(TIME,'ISO')

'%H:%i:%s'

GET_FORMAT(TIME,'EUR')

'%H.%i.%S'

GET_FORMAT(TIME,'INTERNAL')

'%H%i%s'

TIMESTAMP는 또한 GET_FORMAT() 함수의 첫 번째 인수로 사용이 가능한데이와 같은 경우함수는 DATETIME 함수와 같은 값을 리턴한다.

mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));

        -> '03.10.2003'

mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));

        -> '2003-10-31'

  • HOUR(time)

time에 대해서 시간 (hour)를 리턴한다일별 시간에 대해서는 0에서 23 사이의 값을 가진다하지만TIME 값의 실제 범위는 이것보다 훨씬 크기 때문에HOUR 23 보다 큰 값을 리턴할 수 있다.

mysql> SELECT HOUR('10:05:03');

        -> 10

mysql> SELECT HOUR('272:59:59');

        -> 272

  • LAST_DAY(date)

날짜 또는 데이트타임 값을 가져와서 그 달의 가장 마지막 날짜에 해당하는 값을 리턴한다만일 인수가 올바르지 않으면 NULL을 리턴한다.

mysql> SELECT LAST_DAY('2003-02-05');

        -> '2003-02-28'

mysql> SELECT LAST_DAY('2004-02-05');

        -> '2004-02-29'

mysql> SELECT LAST_DAY('2004-01-01 01:01:01');

        -> '2004-01-31'

mysql> SELECT LAST_DAY('2003-03-32');

        -> NULL

  • LOCALTIMELOCALTIME()

LOCALTIME 및 LOCALTIME()는 NOW()과 동일한 것이다.

  • LOCALTIMESTAMPLOCALTIMESTAMP()

LOCALTIMESTAMP 및 LOCALTIMESTAMP()는 NOW()과 동일하다.

  • MAKEDATE(year,dayofyear)

주어진 연도 및 연도별 날짜 값을 가지고서해당하는 날짜를 리턴한다dayofyear 인수는 0 보다 커야 하며그렇지 않을 경우에는NULL을 리턴한다.

mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);

        -> '2001-01-31', '2001-02-01'

mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);

        -> '2001-12-31', '2004-12-30'

mysql> SELECT MAKEDATE(2001,0);

        -> NULL

  • MAKETIME(hour,minute,second)

hourminute그리고 second 인수를 가지고 계산된 시간 값을 리턴한다.

mysql> SELECT MAKETIME(12,15,30);

        -> '12:15:30'

  • MICROSECOND(expr)

시간 또는 데이트타임 수식 expr 에서 마이크로 세컨드(microsecond) 값을 리턴하는데그 범위는0에서 999999 사이가 된다.

mysql> SELECT MICROSECOND('12:00:00.123456');

        -> 123456

mysql> SELECT MICROSECOND('1997-12-31 23:59:59.000010');

        -> 10

  • MINUTE(time)

time에서 분 (minute)에 해당하는 값을 리턴하는데그 범위는 0에서 59 사이가 된다.

mysql> SELECT MINUTE('98-02-03 10:05:03');

        -> 5

  • MONTH(date)

date에서 월에 해당하는 값을 리턴하는데그 범위는 0에서 12 사이가 된다.

mysql> SELECT MONTH('1998-02-03');

        -> 2

  • MONTHNAME(date)

date에 해당하는 월을 전체 이름으로 표시한다.

mysql> SELECT MONTHNAME('1998-02-05');

        -> 'February'

  • NOW()

이 함수가 사용되는 문장에 따라서 그 형태를 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS로 해서 현재의 날짜 및 시간을 리턴한다.

mysql> SELECT NOW();

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

mysql> SELECT NOW() + 0;

        -> 19971215235026

NOW()는 이 명령문이 실행을 시작하는 시점을 나타내는 시간을 상수 값으로 리턴한다. (스토어드 루틴 또는 트리거 안에서는,NOW()는 루틴 또는 트리거링 명령문이 실행되는 시점 값을 리턴한다.) 이 함수는 SYSDATE()와는 차이점을 가지는데후자의 경우는 5.0.13 이후에는 함수가 실행된 정확한 시간을 리턴한다.

mysql> SELECT NOW(), SLEEP(2), NOW();

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

| NOW()               | SLEEP(2) | NOW()               |

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

2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |

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

 

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();

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

| SYSDATE()           | SLEEP(2) | SYSDATE()           |

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

2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |

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

이 두 함수간의 차이점에 대해서는 SYSDATE() 함수 설명을 참고로 하기 바란다.

  • PERIOD_ADD(P,N)

N  (month)을 기간 P 에 추가한다 (YYMM 또는 YYYYMM 포맷). 리턴되는 값은 YYYYMM 포맷을 가진다기간 인수 P 는 날짜 값이 아니라는 점을 유의한다.

mysql> SELECT PERIOD_ADD(9801,2);

        -> 199803

  • PERIOD_DIFF(P1,P2)

P1 P2 사이의 월별 간격을 리턴하는데여기에서 P1 과 P2 는 YYMM 또는 YYYYMM 형태가 되어야 한다인수 P1 과 P2 는 날짜 값이 아니라는 점을 유의한다.

mysql> SELECT PERIOD_DIFF(9802,199703);

        -> 11

  • QUARTER(date)

Date에 해당하는 분기를 리턴하는데범위는에서 4 사이가 된다

mysql> SELECT QUARTER('98-04-01');

        -> 2

  • SECOND(time)

time에서 초 부분을 리턴하는데범위는 0 에서 59 사이가 된다.

mysql> SELECT SECOND('10:05:03');

        -> 3

  • SEC_TO_TIME(seconds)

seconds 인수를 리턴하는데이 함수가 사용되는 문장에 따라서 'HH:MM:SS' 또는 HHMMSS 포맷의 값으로 시간초로 변환을 시킨다.

mysql> SELECT SEC_TO_TIME(2378);

        -> '00:39:38'

mysql> SELECT SEC_TO_TIME(2378) + 0;

        -> 3938

  • STR_TO_DATE(str,format)

이 함수는 DATE_FORMAT() 함수와는 정 반대 값을 리턴한다이 함수는 스트링 str 을 가져와서 스트링 format형태로 포맷을 한다.STR_TO_DATE()포맷 스트링이 날짜 및 시간 부분을 모두 가지고 있는 경우에는 DATETIME 값을또는 스트링이 날짜 또는 시간 부분만을 가지고 있는 경우에는 DATE 또는 TIME 값을 리턴한다.

Str에 포함되어 있는 날짜시간또는 데이트타임 값은 ormat에 의해 지정되는 포맷으로 주어져야 한다format에서 사용될 수 있는 지정자의 경우는 DATE_FORMAT() 함수 설명을 참조하기 바란다만일 str이 올바르지 않은 날짜시간또는 데이트타임 값을 가지고 있다면STR_TO_DATE()는 NULL을 리턴한다. MySQL 5.0.3 이후에서는 경고문도 함께 발생한다.

날짜 값의 범위 검사는 Section 11.3.1, “DATETIMEDATE그리고 TIMESTAMP 타입에서 설명을 하고 있다예를 들면이것은, “제로 (zero)” 날짜 또는 날짜 부분에 0을 가지고 있는 날짜는 SQL 모드가 이러한 값을 허용하지 않도록 설정되지 않는 한 사용 가능하다는 것을 의미한다.

mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');

        -> '0000-00-00'

mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');

        -> '2004-04-31'

  • SUBDATE(date,INTERVAL expr unit)SUBDATE(expr,days)

두 번째 인수에 대해서 INTERVAL 폼을 사용해서 호출을 한다면SUBDATE()는 DATE_SUB()과 동일한 값을 리턴하게 된다.

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);

        -> '1997-12-02'

mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);

        -> '1997-12-02'

두 번째 형태는 days에 대해서 정수 값을 사용할 수 있도록 하고 있다이와 같은 경우날짜 또는 데이트타입 수식 expr에서 빼기가 되어야 하는 날짜 수로 해석이 된다.

mysql> SELECT SUBDATE('1998-01-02 12:00:00', 31);

        -> '1997-12-02 12:00:00'

Note"%X%V" 형태를 사용해서 년-주간 스트링을 날짜로 변환 시킬수는 없는데그 이유는 연도와 주간의 결합은 해당 주간이 두 개의 달에 걸쳐 있을 경우에는 연도 및 달을 구분할 수 없기 때문이다연도-주간 값을 날짜로 변환하기 위해서는주간 요일(weekday)을 함께 지정해 주어야 한다:

mysql> SELECT STR_TO_DATE('200442 Monday', '%X%V %W');

        -> '2004-10-18'

  • SUBTIME(expr1,expr2)

SUBTIME() 함수는 expr1 – expr2 수식의 결과 값을 리턴하느데그 포맷은 expr1을 따른다 expr1 은 시간 또는 데이트 타임 수식을 사용할 수 있으며expr2은 시간 수식이 된다.

mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');

        -> '1997-12-30 22:58:58.999997'

mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');

        -> '-00:59:59.999999'

  • SYSDATE()

함수가 사용된 문장에 따라서 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 포맷으로 현재 날짜 및 시간을 리턴한다.

MySQL 5.0.13 이후부터는SYSDATE()은 이것이 실행된 시간을 리턴한다이 함수는 NOW()과 차이를 가지는데후자의 경우는 명령문이 실행을 시작한 상수 시간을 리턴한다.

mysql> SELECT NOW(), SLEEP(2), NOW();

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

| NOW()               | SLEEP(2) | NOW()               |

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

2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |

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

 

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();

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

| SYSDATE()           | SLEEP(2) | SYSDATE()           |

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

2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |

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

또한SET TIMESTAMP 명령문은 NOW()가 리턴하는 값에는 영향을 주지만SYSDATE()가 리턴하는 값에는 영향을 주지 않는다.이것이 의미하는 것은 바이너리 로그에서 설정된 타임 스탬프가 SYSDATE() 함수 호출에는 영향을 주지 않는다는 것을 나타내는 것이다.

SYSDATE()는 동일한 명령문 안에서도 서로 다른 값을 리턴하고SET TIMESTAMP에 의해서도 영향을 받지 않기 때문에이 함수는 논-디터미니스틱 (non-deterministic)이며 따라서 리플리케이션에서는 안전하지가 못하게 된다만일 이것이 문제가 된다면서버를--sysdate-is-now 옵션과 함께 구동 시킴으로서 SYSDATE()가 NOW()의 별칭으로 동작하도록 할 수는 있다.

  • TIME(expr)

시간 또는 데이트 타임 수식 expr 에서 시간 부분을 추출하고 그 값을 스트링으로 리턴한다.

mysql> SELECT TIME('2003-12-31 01:02:03');

        -> '01:02:03'

mysql> SELECT TIME('2003-12-31 01:02:03.000123');

        -> '01:02:03.000123'

  • TIMEDIFF(expr1,expr2)

TIMEDIFF()은 expr1 – expr2 수식의 결과를 시간 값으로 리턴한다expr1 및 expr2 는 시간 또는 날짜-시간 수식이 될 수 있지만양쪽 모두 동일한 타입이어야 한다.

mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',

    ->                 '2000:01:01 00:00:00.000001');

        -> '-00:00:00.000001'

mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001',

    ->                 '1997-12-30 01:01:01.000002');

        -> '46:58:57.999999'

  • TIMESTAMP(expr)TIMESTAMP(expr1,expr2)

단일 인수를 사용하게 되면이 함수는 날짜 또는 데이트 타임 수식 expr 을 데이트 타임 값으로 리턴한다두 개의 인수를 사용하게 되면이 함수는 시간 수식 expr2 를 날짜 또는 데이트 타임 수식 expr1 에 추가를 하게 되고 그 결과를 데이트타임 값 형태로 리턴한다.

mysql> SELECT TIMESTAMP('2003-12-31');

        -> '2003-12-31 00:00:00'

mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');

        -> '2004-01-01 00:00:00'

  • TIMESTAMPADD(unit,interval,datetime_expr)

정수 수식 interval 를 날짜 또는 데이트타임 수식 datetime_expr에 추가를 한다interval 에 대한 유닛은 unit 인수에 의해 주어지는데이 인수는 다음의 값 중에 하나가 되어야 한다FRAC_SECONDSECONDMINUTEHOURDAYWEEKMONTH,QUARTER, or YEAR.

unit 값은 위에 나와 있는 키워드중의 하나를 사용하거나 또는 지정될 수 있다SQL_TSI_의 접두어를 사용해서 지정할 수가 있다예를 들면DAY 및 SQL_TSI_DAY 는 모두 유효하다.

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');

        -> '2003-01-02 00:01:00'

mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');

        -> '2003-01-09'

TIMESTAMPADD() 5.0.0 이후에 사용 가능해졌다.

  • TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

날짜 또는 데이트 타임 수식 datetime_expr1 및 datetime_expr2간의 정수 차이를 리턴한다그 결과에 대한 유닛은 unit 인수에 의해 주어진다unit 에 대한 유효 값은 TIMESTAMPADD() 함수에서 설명된 리스트 값과 같다.

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');

        -> 3

mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');

        -> -1

TIMESTAMPDIFF() 5.0.0 이후에 사용 가능해졌다.

  • TIME_FORMAT(time,format)

이 함수는 DATE_FORMAT() 함수와 비슷하게 사용되지만format 스트링은 시간그리고 초에만 해당하는 지정자를 가질 수도 있다다른 지정자들을 사용하면 NULL 값 또는 0이 나오게 된다.

만일 time 값이 23 보다 큰 시간 부분을 가진다면%H 및 %k 시간 포맷 지정자 0에서 23 보다 큰 값을 만들게 된다다른 시간 포맷 지정자는 시간 값 모듈로 12를 만든다.

mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');

        -> '100 100 04 04 4'

  • TIME_TO_SEC(time)

time 인수를 초로 변환해서 리턴한다.

mysql> SELECT TIME_TO_SEC('22:23:00');

        -> 80580

mysql> SELECT TIME_TO_SEC('00:39:38');

        -> 2378

  • TO_DAYS(date)

주어진 날짜 date에 대해서연도 0에서부터 계산된 날짜 숫자를 리턴한다.

mysql> SELECT TO_DAYS(950501);

        -> 728779

mysql> SELECT TO_DAYS('1997-10-07');

        -> 729669

  • UNIX_TIMESTAMP()UNIX_TIMESTAMP(date)

만일 아무런 인수를 지정하지 않고 호출을 한다면부호를 사용하지 않은 유닉스 타임 스탬프를 리턴한다 ('1970-01-01 00:00:00'UTC 이후의 초). 만일 UNIX_TIMESTAMP()가 date 인수를 사용해서 호출되었다면이 함수는 '1970-01-01 00:00:00' UTC 이후의 초 형태로 인수 값을 리턴한다Date는 DATE 스트링DATETIME 스트링TIMESTAMP또는 YYMMDD 또는 YYYYMMDD에 있는 숫자가 될 수도 있다서버는 date를 현재의 타임 존에 있는 값으로 해석을 해서 UTC의 내부 값으로 변환을 시킨다클라이언트는 자신의 타임 존을 Section 5.11.8, “MySQL 서버 타임  지원에서 설명한 방식으로 설정을 한다.

mysql> SELECT UNIX_TIMESTAMP();

        -> 882226357

mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');

        -> 875996580

UNIX_TIMESTAMP를 TIMESTAMP 컬럼에서 사용하는 경우이 함수는 내부 타임 스탬프 값을 직접 리턴하고의미적인 (implicit) “스트링에서 유닉스 타임 스탬프로 변환을 실행하지 않는다만일 날짜 범위를 벗어난 값을 UNIX_TIMESTAMP()에 주게 되면함수는 0을 리턴한다.

  • UTC_DATEUTC_DATE()

이 함수가 사용된 문장에 따라서현재의 UTC 날짜를 'YYYY-MM-DD' 또는 YYYYMMDD 포맷으로 리턴한다.

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;

        -> '2003-08-14', 20030814

  • UTC_TIMEUTC_TIME()

이 함수가 사용된 문장에 따라서현재의 UTC 시간을 'HH:MM:SS' 또는 HHMMSS 포맷으로 리턴한다.

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;

        -> '18:07:53', 180753

  • UTC_TIMESTAMPUTC_TIMESTAMP()

이 함수가 사용된 문장에 따라서현재의 UTC 날짜를 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 포맷으로 리턴한다.

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;

        -> '2003-08-14 18:08:04', 20030814180804

  • WEEK(date[,mode])

이 함수는 date에 해당하는 주간 숫자를 리턴한다WEEK() 함수에 두 개의 인수를 사용하면 해당 주가 일요일 또는 월요일에 시작을 하는지를 지정할 수 있으며 또한 리턴되는 값이 0에서 53 사이 또는 1에서 53 사이에 있는지를 지정할 수가 있게 된다만일 mode인수를 생략한다면default_week_format 시스템 변수가 사용된다. Section 5.2.2, “서버 시스템 변수를 참조할 것.

아래의 테이블은 mode 인수가 어떻게 동작을 하는지를 보여주는 것이다.

 

First day

 

 

Mode

of week

Range

Week 1 is the first week …

0

Sunday

0-53

with a Sunday in this year

1

Monday

0-53

with more than 3 days this year

2

Sunday

1-53

with a Sunday in this year

3

Monday

1-53

with more than 3 days this year

4

Sunday

0-53

with more than 3 days this year

5

Monday

0-53

with a Monday in this year

6

Sunday

1-53

with more than 3 days this year

7

Monday

1-53

with a Monday in this year

mysql> SELECT WEEK('1998-02-20');

        -> 7

mysql> SELECT WEEK('1998-02-20',0);

        -> 7

mysql> SELECT WEEK('1998-02-20',1);

        -> 8

mysql> SELECT WEEK('1998-12-31',1);

        -> 53

만일 어떤 날짜가 바로 전년도의 마지막 주에 있다면여러분이 옵션 인수MySQL은 0을 리턴한다:

mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);

        -> 2000, 0

  • WEEKDAY(date)

Date에 해당하는 주간 요일 인덱스를 리턴한다 (0 = Monday, 1 = Tuesday, … 6 = Sunday).

mysql> SELECT WEEKDAY('1998-02-03 22:23:00');

        -> 1

mysql> SELECT WEEKDAY('1997-11-05');

        -> 2

  • WEEKOFYEAR(date)

1에서 53 사이의 달력 주간 숫자를 리턴한다.

mysql> SELECT WEEKOFYEAR('1998-02-20');

        -> 8

  • YEAR(date)

1000에서 9999 사이의 date에 해당하는 연도를 리턴하거나또는 제로” 날짜일 경우에는 0을 리턴한다.

mysql> SELECT YEAR('98-02-03');

        -> 1998

  • YEARWEEK(date)YEARWEEK(date,start)

해당되는 연도 및 주를 리턴한다start 인수는 WEEK() 함수에서 사용되는 것과 동일하게 동작을 한다결과에 나오는 연도는 날짜 인수에 표시되어 있는 연도와 다르게 나올 수도 있다.

mysql> SELECT YEARWEEK('1987-01-01');

        -> 198653

'♨ DataBase > MySQL' 카테고리의 다른 글

MySQL 관련 10가지 TIP  (0) 2011.07.22
Mysql 함수 모음  (0) 2011.07.22
MySQL Query(쿼리)문 모음  (0) 2011.07.22
MYSQL 날짜 데이터 타입  (0) 2011.07.22
mysql 컬럼 삭제, 추가, 변경  (0) 2011.07.22
mysql에서 LIMIT의 사용법  (0) 2011.07.22
YOUR COMMENT IS THE CRITICAL SUCCESS FACTOR FOR THE QUALITY OF BLOG POST