GINS WIKI 긴스 위키

사용자 도구

사이트 도구

현재 위치: 긴스위키 GINS wiki » 디비 DB » SQL 기본 명령어
추적:
db:mysql

차이

문서의 선택한 두 판 사이의 차이를 보여줍니다.

차이 보기로 링크

양쪽 이전 판이전 판
다음 판		이전 판
db:mysql [2013/11/27 19:29] – [날짜 - 현재 시간 얻기 : UnixTime] kieunsdb:mysql [2024/04/23 22:43] (현재) – 바깥 편집 127.0.0.1
줄 1: 줄 1:
  
 +====== 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|스트링 비교 함수]]