====== SQL 기본 명령어 ======

난 기본도 없어서..

===== DB 생성 =====

  * full opt 사용

<code sql>
create database tbook1_development 
    default character set = utf8 default collate = utf8_bin;
</code>

TABLE 생성

<code sql>
-- Create the table foo.example_02 if it doesn't exist.
CREATE TABLE 
  IF NOT EXISTS `foo`.`example_02`
(
 `field_1` VARCHAR(255) NOT NULL DEFAULT '',
 `field_2` CHAR(20) NOT NULL default 'default_text',
 `field_3` INT(10) DEFAULT NULL,
 `field_4` BIGINT(20),
 `field_5` DATETIME          -- embed a comment in the definition like this...
);
</code>

예2 - 인덱스와 함께 사용

  * 메모용 테이블 (그냥 생각해본 것)
  * 디비 타입 명시
  * 캐릭터셋 명시
  * 주석 추가
  * 프라이머리 키, 키 키워드 사용

CREATE TABLE에서 KEY 키워드를 사용하는 것은 인덱스를 생성하라는 이야긴데,
호환성을 위해서 KEY 명령어도 있다.

<code sql>
CREATE TABLE 
  IF NOT EXISTS example_1
(
  `seq` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '일련번호',
  `orders` bigint(19) NOT NULL COMMENT '순서(0..3)',
  `content` varchar(1000) DEFAULT NULL COMMENT '내용',
  `writer` varchar(20) DEFAULT NULL COMMENT '등록자ip',
  `regdate` datetime DEFAULT NULL COMMENT '등록일시',
  PRIMARY KEY (`seq`),
  KEY `idx` (`orders`,`regdate`),
) ENGINE=InnoDB DEFAULT=utf8 COMMNET='Memo Table';
</code>

기본 값으로 DATETIME에 현재 시간을 입력하게 하는 것은 쉽지 않아서... INSERT 할때 NOW() 함수로 값을 넣는다.

<code sql>
INSERT INTO example_1
( orders, content, startdate, writer, regdate ) VALUES
( 1, 1, 'ABCD', '1.1.1.1', NOW() );
// NOW() 함수로 현재 시간을 입력.
</code>


===== 날짜 - 현재 시간 얻기 =====

<code sql>
SELECT NOW();
select CAST(NOW() AS DATETIME);
</code>

===== 날짜 - 현재 시간 얻기 : UnixTime =====

ref : [[http://blog.bsmind.co.kr/44]]

<code sql>
-- STR to unixtime
select UNIX_TIMESTAMP(); -- 1385548154
select UNIX_TIMESTAMP("2013-11-27 00:00:00"); -- 1385478000
</code>


ref : [[http://www.electrictoolbox.com/mysql-unix-timestamp-to-datetime/]]

<code sql>
-- unixtime to STR
SELECT FROM_UNIXTIME(1385690400); -- 2013-11-29 11:00:00
</code>
===== Create - added =====

Alter에 대해서 추가로 새로운 점,

<code sql>
ALTER TABLE  `etdn_hg_img_list` ADD  `kind` TINYINT NOT NULL DEFAULT  '0' COMMENT  '이미지 종류(취향?)' AFTER  `idx` ;
</code>

  * **AFTER `<컬럼이름>`**으로 원하는 위치에 새로운 컬럼을 추가할 수 있다.
  * DEFAULT는 항상 잘 쓰인다.

=====  DISTINCT ===== 

col_a 컬럼에서 중복은 제거하고 출력

<code sql>
select DISTINCT col_a from some_table;
</code>
===== Import =====

  * [[http://radiocom.kunsan.ac.kr/lecture/mysql/mysqlimport.html|출처(MySQL-Import)]]의 내용을 읽어보면서 다시 정리한 것.
    * 다른 참고 링크 : [[http://radiocom.kunsan.ac.kr/lecture/mysql/load_data_infile.html|load data infile 문]]


  * 도움말 <code>mysqlimport --help</code>
  * csv 파일 형식(콤마, 따옴표가 컬럼 구분자)의 텍스트 형식의 데이터 파일을 읽어서 DB 데이터로 저장한다.
  * 데이터 파일의 확장자는 무시된다.
    * patient.txt, patient.text, patient >> 모두 patient 로 인식 : 확장자를 쓰지말라는 이야긴가?

<code>
【사용법】 
   mysqlimport [OPTIONS] db_name textfile1 [textfile2 ...]

【예제】 
   # cat employees 
   1,조현수,007-123-1234,우리동 우리집,국가대표, 
   2,강선영,007-123-4567,회현면 우리집,대학원생, 
   3,현부금,007-123-7890,나포면 우리집,대학원생, 
   # mysqlimport -u root -p --fields-terminated-by="," test employees 
</code>

다른 예제

<code mysql>
# cat > imptest.txt 
100     Max Sydow 
101     Count Dracula 
^c

# cat imptest.txt 
100     Max Sydow 
101     Count Dracula 

# mysql -e "create table imptest(id int, n varchar(30))" test 

# mysqlimport -u jijoe -p --local test imptest.txt 
test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0 

# mysql -e "select * from imptest" test 
+------+---------------+ 
| id   | n             | 
+------+---------------+ 
|  100 | Max Sydow     | 
|  101 | Count Dracula | 
+------+---------------+ 
</code>

==== 또 다른 예제 ====

일반 사용자(jijoe)가 자신의 데이터베이스(jijoeDB)에 테이블(test)를 생성하고 mysqldump로 데이터만 덤프한 다음 mysqlimport를 이용하여 테이블에 데이터를 복구하는 과정이다.


jijoeDB 의 덤프를 받는다.

<code bash>
shell> mkdir dumpdata
shell> chmod a+w dumpdata
shell> mysqldump -u jijoe -p jijoeDB --no-create-info \
      --tab=/export/home/jijoe/dumpdata \
      --fields-terminated-by=',' \
      --lines-terminated-by='\n' test
Enter password: ******

shell> cat dumpdata/test.txt
     아무개,2008-10-22 17:50:50
     착한이,2008-10-22 17:51:21
</code>

덤프 데이터를 **mysqlimport** 명령어로 데이터를 복사한다.

<code mysql>
mysql> select * from test;
+--------+---------------------+
| name   | login               |
+--------+---------------------+
| 아무개 | 2008-10-22 17:50:50 |
| 착한이 | 2008-10-22 17:51:21 |
+--------+---------------------+
2 rows in set (0.00 sec)

mysql> truncate table test;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

shell> cd dumpdata

shell> cat test.txt
     아무개,2008-10-22 17:50:50
     착한이,2008-10-22 17:51:21

shell> mysqlimport -u jijoe -p \
      --local \
      --fields-terminated-by=',' \
      --lines-terminated-by='\n' jijoeDB test.txt
Enter password: ******* 
jijoeDB.test: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

shell> mysql -u jijoe -p jijoeDB
Enter password: ******* 

mysql> select * from test;
+--------+---------------------+
| name   | login               |
+--------+---------------------+
| 아무개 | 2008-10-22 17:50:50 |
| 착한이 | 2008-10-22 17:51:21 |
+--------+---------------------+
2 rows in set (0.00 sec)
</code>

====== 서브쿼리(SubQuery) ======

빨리 읽어볼 수 있는 튜토리얼을([[http://comefeel.com/tt/comefeel/299|Mysql에서의 서브쿼리(subquery)]]) 정리한 것.

서브쿼리를 넣을 수 있는 위치

  * FROM 구문 다음의 테이블 선택지에서, 특정 테이블을 쿼리 결과로서 전송.
    * 쿼리 결과와 선택한 데이터에 별명을 붙여 사용할 수 있다.
  * WHERE 구문 다음의 조건자 선택시, 조건 항목을 쿼리 결과로서 전송.

from 다음의 서브쿼리가 유용한 것 같다.

간단한 예제 - 삽입할 위치 개념 찾기

<code sql>
-- 쓸모 없지만, 아 이렇구나 하는 감을 보내주는 예제
SELECT name, db FROM country
  WHERE code=(SELECT "SWE");
-- 조금 더 나은 예제
SELECT name, headofstate, population FROM Country 
  WHERE population=(SELECT MAX(population) FROM Country);
</code>

복잡한 예제

<code sql>
-- 1 
SELECT name, population, headofstate, top.nr
  FROM
    Country,
    (
      --2 공식언어(official languages)의 개수에 기반한 국가 코드(country codes)를 비교한다.
      SELECT countrycode, COUNT(*) AS nr
      FROM CountryLanguage
      WHERE isofficial='T'
      GROUP BY countrycode
      HAVING nr=
      (
        -- 3 SELECT #4 번으로부터 nr_official_language의 최대크기를 알아낸다. 
        SELECT MAX(summary.nr_official_languages)
        FROM 
        (
          -- 4 모든 국가와 각각의 공식언어(official languages)의 개수를 찾는다.
          SELECT countrycode, COUNT(*) AS nr_official_languages
          -- CountryLanguage 테이블에서
          FROM CountryLanguage 
          -- 공식언어가 있는 경우에만
          WHERE isofficial='T' 
          -- countrycode 순서로
          GROUP BY countrycode
        -- 원래의 테이블 이름 AS 별칭으로 임시로 쓸 이름
        ) AS summary
      )
    ) as top
  WHERE Country.code=top.countrycode
</code>


4. 이 SELECT 문은 모든 국가와 각각의 공식언어(official languages)의 개수를 찾는다.

3. 이 SELECT 문은 SELECT #4 번으로부터 nr_official_language의 최대크기를 알아낸다. 동시에, 최대크기를 위해 countrycode 컬럼을 얻는 방법은 SQL-표준이 아니다.  

2. 이 SELECT에서, 우리는 공식언어(official languages)의 개수에 기반한 국가 코드(country codes)를 비교한다.

1. 마지막으로 우리는 매칭한(matching) 국가에 관해 국가정보(country information)를 찾기 위한 국가(country) 테이블에 기대어 JOIN을 사용할 수 있다.

====== delete from 같은 테이블의 특정 조건 ======

  - Table AAA 에 seq, evt_type 이 있고
    - seq는 고유 번호
    - evt_type은 두번째 고유 번호라고 하고
  - evt_type 이 1 인 것만 삭제하려고 하면

<code sql>
select * from AAA where evt_type = 1; -- 이 조건에 해당되는 것들만 삭제하려고 하면

delete from AAA where seq = (select seq from AAA where evt_type = 1);
-- 이렇게 쓰면 아래 에러가 뜸
</code>

  SQL Delete: can't specify target table for update in FROM clause

에러가 뜬다.

JOIN을 사용해서 테이블을 다시 결햡 시켜 삭제 해야 한다.

<code sql>
DELETE a 
  FROM AAA AS a
    JOIN ( select * from AAA where evt_type = 1 ) AS b
  ON a.seq = b.seq;
</code>

**evt_type == 1**이고, **seq가 같은 것**만 삭제
====== 덤프 ======

!.edu라는 디비의 모든 테이블 스키마를 백업받으려면 
<code sql>
mysqldump -u mysql_admin -p -d edu > edu_db.sql </code>

!.edu라는 디비의 a라는 테이블 스키마를 백업받으려면 
<code sql>
mysqldump -u mysql_admin -p -d edu a> edu_a_table.sql</code>

====== 트랜잭션 ======

  * 도움말 [[http://dev.mysql.com/doc/refman/5.0/en/commit.html]]

키워드

  * begin (또는 start TRANSACTION) 으로 시작
  * rollback 으로 되돌리기
  * commit으로 완료

<code sql>
start TRANSACTION;
delete from my_table where idx=2;
select * from my_table;
rollback; -- 되돌리려면
commit; -- 완료하려면
</code>

트랜잭션을 걸고 나서 롤백이나 커밋을 해도, 몇개가 되돌려졌는지 로그에는 나오지 않는다.
select로 꼭 되돌려졌는지 확인해야 한다.

주의

  * set autocommit=0 으로 자동 커밋되지 않도록 설정 되어야 한다.



====== 기타 ======

===== limit =====

  * 가져온 곳 : [[http://binalee.tistory.com/66]]

사용 법은,

<code sql>
select * from [TABLE]
  limit [시작번호], [가져올 개수];
-- 또는
select * from [TABLE]
  limit [가져올 개수];
</code>

limit는 인덱스를 타지 않기 때문에 개수가 많아지면 느려진다고 한다. ([[http://theeye.pe.kr/entry/MySQL-MySQL-LIMIT%EC%9D%98-%EC%86%8D%EB%8F%84-%EC%A0%80%ED%95%98-%ED%94%BC%ED%95%98%EA%B8%B0|Mysql Limit 속도 저하 피하기]] )

인덱스를 타게 할 방법이 있는데, PK가 INT 타입일때 효과를 볼 수 있다고,

<code sql>
-- 어거지로 인덱스를 사용하는건가?
select * from [TABLE] where [TABLE_PK] > [PIVOT_VALUE] limit 10;
</code>

PK가 int 와 같은 숫자 타입이 아닌 경우라면, ROWNUM을 임의 생성해서 ( [[http://binalee.tistory.com/47|RowNum 구현]] ) where와 limit를 사용해서 ROWNUM으로부터 얻어오려는 부분을 인덱스를 타서 빨리 가져올 수 있다고 (음.. )

==== RowNum 구현 ====

<code sql>
select @rnum:=@rnum + 1 as ROWNUM, t.* 
  from 
		(select * from [TABLE]) t, (select @rnum := 0 ) R;
</code>

===== delimiter $$ =====

딜리미터를 바꾸는 명령어

  * 그대로 사용한다면, ';'를 만나면 SQL 문장 종료로 인식하고 SQL 실행을 중지한다.

따라서,

  * 딜리미터 (문장 종결)를 다른 기호 (이번에는 $$)로 바꾸고 <code sql>delimiter $$</code>
  * SQL 문장을 여러 줄 추가
  * 마지막 부분에는 <code sql>delimiter ;</code>로 원래 기호를 문장 종결자로 선언한다.

프로시져나 펑션 트리거에 코드를 추가할때 사용한다.

===== Lock 걸려 있는지 확인 하는 방법 =====

SHOW PROCESSLIST; 커맨드를 실행

state 가 **Waiting for table metadata lock** 라면 LOCK 상태. ID 번호를 기록했다가 KILL 한다.
====== Web Link ======

나중에 읽어보면서 정리할 사이트

  * [[http://radiocom.kunsan.ac.kr/lecture/mysql/select_string_compare.html|스트링 비교 함수]]