난 기본도 없어서..

• full opt 사용

CREATE DATABASE tbook1_development 
    DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_bin;

TABLE 생성

-- 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...
);

예2 - 인덱스와 함께 사용

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

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

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';

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

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

SELECT NOW();
SELECT CAST(NOW() AS DATETIME);

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

-- STR to unixtime
SELECT UNIX_TIMESTAMP(); -- 1385548154
SELECT UNIX_TIMESTAMP("2013-11-27 00:00:00"); -- 1385478000

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

-- unixtime to STR
SELECT FROM_UNIXTIME(1385690400); -- 2013-11-29 11:00:00

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

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

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

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

SELECT DISTINCT col_a FROM some_table;

• 출처(MySQL-Import)의 내용을 읽어보면서 다시 정리한 것.
  • 다른 참고 링크 : load data infile 문

• 도움말

  mysqlimport --help

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

【사용법】 
   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 

다른 예제

# 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 | 
+------+---------------+ 

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

jijoeDB 의 덤프를 받는다.

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

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

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)

빨리 읽어볼 수 있는 튜토리얼을(Mysql에서의 서브쿼리(subquery)) 정리한 것.

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

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

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

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

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

복잡한 예제

-- 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

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을 사용할 수 있다.

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

SELECT * FROM AAA WHERE evt_type = 1; -- 이 조건에 해당되는 것들만 삭제하려고 하면
 
DELETE FROM AAA WHERE seq = (SELECT seq FROM AAA WHERE evt_type = 1);
-- 이렇게 쓰면 아래 에러가 뜸

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

에러가 뜬다.

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

DELETE a 
  FROM AAA AS a
    JOIN ( SELECT * FROM AAA WHERE evt_type = 1 ) AS b
  ON a.seq = b.seq;

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

!.edu라는 디비의 모든 테이블 스키마를 백업받으려면

mysqldump -u mysql_admin -p -d edu > edu_db.sql 

!.edu라는 디비의 a라는 테이블 스키마를 백업받으려면

mysqldump -u mysql_admin -p -d edu a> edu_a_table.sql

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

키워드

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

START TRANSACTION;
DELETE FROM my_table WHERE idx=2;
SELECT * FROM my_table;
ROLLBACK; -- 되돌리려면
commit; -- 완료하려면

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

주의

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

• 가져온 곳 : http://binalee.tistory.com/66

사용 법은,

SELECT * FROM [TABLE]
  LIMIT [시작번호], [가져올 개수];
-- 또는
SELECT * FROM [TABLE]
  LIMIT [가져올 개수];

limit는 인덱스를 타지 않기 때문에 개수가 많아지면 느려진다고 한다. (Mysql Limit 속도 저하 피하기 )

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

-- 어거지로 인덱스를 사용하는건가?
SELECT * FROM [TABLE] WHERE [TABLE_PK] > [PIVOT_VALUE] LIMIT 10;

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

SELECT @rnum:=@rnum + 1 AS ROWNUM, t.* 
  FROM 
		(SELECT * FROM [TABLE]) t, (SELECT @rnum := 0 ) R;

딜리미터를 바꾸는 명령어

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

따라서,

• 딜리미터 (문장 종결)를 다른 기호 (이번에는 $$)로 바꾸고

  delimiter $$

• SQL 문장을 여러 줄 추가
• 마지막 부분에는

  delimiter ;

  로 원래 기호를 문장 종결자로 선언한다.

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

SHOW PROCESSLIST; 커맨드를 실행

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

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

• 스트링 비교 함수