mysql 관련 내용

•  http://kldp.org/KoreanDoc/html/APM_tip-KLDP/x375.html

Mysql 서버를 어떤 디렉토리에서나 접속이 가능하도록 설정

.bashrc 에서 ALIAS추가 후 source

• alias my='/usr/local/mysql/bin/mysql -uroot -p'

기본

•  버전 알기

  • select version(), current_date();

• 계산

  • select 2+3, 6*7;
• select user(), now()

• 명령 취소하기

  • \c

• 한줄씩 끊어서 보기

  • \G

• SELECT, UPDATE, DISTINCT ORDER BY (DESC, ASC), COUNT(*), GROUP BY

  • order by는  항상 group by 뒤에 사용한다.

• ALTER TABLE 테이블명 변경옵션

•  컬럼 이름 변경

  • ALTER TABLE tableNAME change previousColumnName NewColumName varchar(20);

•  컬럼 삭제

  •  ALTER TABLE tableNAME DROP tobeDELETECOLUMN

• 컬럼 추가

  •  ALTER TABLE tableNAME ADD newoneName datatype

• 컬럼명 수정

  • ALTER TABLE previousColumnName RENAME newoneName

• Table Elimination

  • DROP TABLE tableName

• DATABASE Elimination

  • drop database DatabaseName

    
    
    

• 날짜계산

  • select name, to_days(now())-to_days(birth) /365 from TABLE;
  • SELECT order_num FROM Orders WHERE order_date BETWEEN to_date('01-JAN-2004') AND to_date('31-DEC-2004')

  • birth 라는 컬럼이 미리 존재

    • CREATE TABLE foo (wdate date);
    • INSERT INTO foo values ('1984-01-18');
    • select wdate, floor((to_days(curdate())-to_days(wdate))/365)+1 as age from foo;
    • http://marca.egloos.com/1432634
    • http://kldp.org/KoreanDoc/html/APM_tip-KLDP/x375.html

    • 위에꺼 안되면 특정날짜 검색 이렇게...-_-

      $s 는 for문 변수

      $sdate = "200906".$s."000000";

      $edate = "200906".$s."235959";

      $query1 = "SELECT user_id, write_time FROM tb_my_question_$i WHERE best_flag >0 AND write_time BETWEEN '$sdate' AND '$edate'";

• month(now()) 현재 달 리턴

• 한달씩 뒤로 미루기

  • mod(month(now()),12 ) + 1;

• 패턴매칭

  • 임의의 한 문자
  • % 임의의 수의 문자 , 0개 포함

• 정규식

  •  .  점은 한개의 문자를 말함
  • ... 점 세개는 문자 세개
  • [a-z], [0-9]  다른 언어랑 비슷
  • [a-zA-Z]
  • ^ 는 시작하는 문자와 일치
  • $끝나는 문자와 일치

  • 예)  D나 d로 시작하는 것을 찾아라

    • SELECT * FROM tablename WHERE column01 regexp '[^dD]';

  • at 으로 끝나는 것을 찾아라

    • SELECT * FROM WHERE column01 regexp 'at$';

  • 5개의 문자로 이루어진 것을 찾아라

    • SELECT * FROM tablename WHERE column regexp '^.....$';
    • SELECT * FROM tablename WHERE column regexp '^.{5}$';

• 날짜 검색

  • http://marca.egloos.com/1432634

사용자 생성 및 권한

•  ROOT  , SET PASSWORD

  • mysql -u root -p mysql
  • SET PASSWORD FOR root@localhost=PASSWORD('aaa');
  • (otherwise) UPDATE user SET Password = PASSWORD('aaa');
  • FLUSH PRIVILEGES;

• GRANT [부여할 권한] ON [대상DB명] to [USER] identified by 'PASSWORD';

  • ex) GRANT ALL PRIVILEGES ON SDFSDF.* TO ROOT2@"localhost" identified by 'PASSWORD';

• CONFIRM

  • select user, host, password from user where user='userid';
  • select user, host, db from WHERE user='userid';

• REVOKE - 권한취소

  • REVOKE priv_type [(COLUMN_LIST)], [PRI_Type (columnLIST)] ON {TABLENAME} FROM USERID

TABLE TYPE

1. MyISAM Tables
2. MERGE Tables ( replace 지원 안됨(지원안하는게 더 좋은데),  AUTO_INCREMENT 속성 작동 안함
3. HEAP Tables
4. InnoDB Tables
   

• 디스크가 아닌 메모리에 저장

  • 임시 테이블로 사용할 경우 유용
  • HEAP은 BLOB/TEXT 컬럼 지원 안함
  • AUTO_INCREMENT 지원안함
  • 테이블에 LOCK걸수 있음
  • 생성방법

  • log_table에서 HEAP테이블을 임시로 만들어서 사용하기 위해 log_table을 만드는 예

> CREATE TABLE log_table (

code int not null auto_increment primary key,

session char(50),

ip char(20),

URL char(100)

);

>insert into log_table values('', 'sidsu1','198.11.1.13','index.html');

>SELECT * from log_table;

>SELECT ip, count(ip), AS first FROM log_table GROUP BY ip;

>CREATE TABLE test_heap TYPE=HEAP

-> SELECT ip,count(ip) AS first FROM log_table GROUP BY ip;

물론 재시작 하면 메모리에 생성돼 있던 test_heap 은 사라진다.

InnoDB Tables

ACID  트랜잭션이 지원되는 테이블 타입으로, 외래키도 지원됨

커밋, 롤백, 충돌 복구 가능한 핸들링 제공

오라클처럼 non-locking READ 가 SELECT 문에서 제공

자동복구

SHOW 문법

서버 상태 정보, 데이터베이스, 테이블 ,칼럽 등의 정보 제공

SHOW DATABSES [LIKE wild]

SHOW LOGS

SHOW GRANTS FOR user

SHOW TABLE STATUS [FROM db_name] [LIKE wild]

SHOW INDEX FROM tbl_name [FROM db_name]

SHOW MASTER STATUS

SHOW SLAVE STATUS

SHOW STATUS LIKE 'C%';

SHOW VARIABLES;

SHOW STATUS 를 보고 SHOW VARIABLES  에 나오는 변수를 수정하는 것도 할 수 있다.

SHOW PROCESSLIST;

INSERT Options

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]

[INTO] tbl_name [(col_name,...)] VALUES ((expression|DEFAULT),...)

1. LOW_PRIORITY

   1. 입력을 느리게한다. SELECT 하고 있을경우 SELECT가 모두 끝날 때 까지 기다리는 설정

2. DELAYED

   1. 부하가 심할 경우나 잦은 입력연산을 할 경우, 한번에 일정량의 데이터를 삽입하는 기능으로, ISAM이나 MyISAM 테이블 타입에서 사용 가능하다.

   2. 잘못 사용하면 쿼리 상태가 SLEEP 될 수 있다.

      1. SHOW VARIABLES 에서 delayed_insert_timeout 시간이 지나도 INSERT를 하지 못했을 경우는 데이터를 삽입하지 못하게 될 수 있다.
      2. 자주 입력되는 경우 delayed_queue_size 변수 설정으로 PROCESS의 SLEEP 쿼리 상태를 막을 수 있다.
      3. INSERT delayed into ...

3. IGNORE

   1. Primary key나 UNIQUE 키일 경우 동일한 값이 중복으로 입력될 때 오류발생을 막기 위해 사용한다.

REPLACE

INSERT문과 동일한 기능을 하지만

   INSERT는 PRIMARY KEY나 UNIQUE 인덱스에 중복 데이터가 입력될 경우 입력을 못하지만,

   REPLACE는 동일한 키를 가진 데이타가 들어오면 이전 레코드를 지우고, 현재 입력한 것을 새로 삽입한다.

• 좋은거 같지만 안좋을 때가 더 많음
• http://box.cashdata.com/entry/mysql-replace문으로-데이타를-넣으면-mysqld가-죽더군요
• http://seapy.com/tag/MySQL
• INSERT INTO `wf_word_info` (`word`,`search_count`,`latest_result`) VALUES ('이명박',1,1313) ON DUPLICATE KEY UPDATE `search_count`=`search_count`+1;
  

ALTER TABLE

컬럼 위치 지정하는 after 사용할 수 있음

ALTER table tb1 DROP col1;

ALTER  "      "    ADD    "    ;

ANALYZE TABLE

myisamchk -a 와 동일

DELETE

LOW_PRIORITY 역시 있음

특정 레코드 삭제: where 이용해서 한다

TABLE의 모든 데이터 삭제 : delete from tablename

Data Load to DB  

• LOAD data infile "filename.txt" into table TABLENAME;
• low_priority 가능

1. LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
3.     [REPLACE | IGNORE]
5.     INTO TABLE tbl_name
7.     [FIELDS
9.         [TERMINATED BY 'string']
11.         [[OPTIONALLY] ENCLOSED BY 'char']
13.         [ESCAPED BY 'char']
15.     ]
17.     [LINES
19.         [STARTING BY 'string']
21.         [TERMINATED BY '\n']
23.     ]
25.     [IGNORE number LINES]
27.     [(col_name_or_user_var,...)]
29.     [SET col_name = expr,...)]

\t 칼럼과 칼럼을 구분, \n 레코드와 레코드를 분 등등

참고

• http://radiocom.kunsan.ac.kr/lecture/mysql/prototype/prototype_load_data_infile.html
• http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01&m_no=21494&cat1=13&cat2=380&cat3=399〈=k

OPTIMIZE

OPTIMIZE TABLE tbname

테이블에서 데이터가 삭제 되거나 분리 되었을 때 테이블을 복구한다.

인덱스가 정렬되지 않았을 경우 정렬시킨다

EXPLAIN

EXPLAIN SELECT select_options

SELECT문에서 느린 쿼리 분석에 사용되며, 쿼리가 어떻게 사용되고 있는지, 인덱스에 걸려있는지, 조인을 사용하는지, 퍼포머느가 좋은지에 대해서 말해준다.

ex) EXPLAIN select tcode from 500man_2 order by tcode desc limit 10;

• table : 사용한 테이블, 조인일 경우는 행이 여러개로 나온다.

• type : 조인이 어떻게 사용되었는지 알수 있음

  • 가장 좋은 타입부터 나쁜 타입으로는 system, const, eq_ref, ref, range, index, all임

  • type에 ALL이나 index, range등이 보일 경우는 쿼리가 그다지 빠르지 않다는 것을 의미

    • type : system 1개의 행으로만 이루어진 테이블
    • type:const  조건에 맞는 레코드가 1개인 경우에 나타나는 유형이다. 주로 주키일 경우나 UNIQUE키일 경우 나타난다.
    • type:eq_ref 조인일 경우에 1개 행이 일치하는 경우에 보여짐. 키값이 UNIQUE 나 PRIMARY KEY인 경우임
    • type:ref 인덱스가 걸린 쿼리 유형이며, UNIQUE키나 PRIMARY KEY가 아닌 인덱스일 경우이다.
    • type:range  일정 범위값에 대해서 쿼리를 할 때 나오는 유형이다
    • type: index 테이블 전체를 읽어야하지만, 인덱스를 사용하는 경웅이다. ALL타입이나 마찬가지임
    • type:all 테이블 전체를 검색한다. 가장 좋지 않은 쿼리 상태이다. 
  • range, index, ALL로 나올 경우는 인덱스를 다시 걸어 주거나, 쿼리를 다른 방향으로 할지 생각해야한다.
  • 그러나 type:range로 나오더라도 rows가 적을 경우는 빠름
  • 가장 중요한 것은 적절한 인덱스와 rows를 줄이는 것
• possible_keys : 사용 가능한 키(인덱스)
• key : 사용하고 있는 키 (인덱스)
• key_len : 키에 사용된 길이. 작을 수록 좋음. 
• ref : 칼럼이나 상수가 사용되었는지 말해줌
• rows : 인덱스 범위를 나타냄. 작을수록 빠름
• Extra : 정보 - using temporary 나 using filesort는 가장 좋지 않음

TRUNCATE

TRUNCATE TABLE tbname

테이블을 드랍한 후에 다시 만들기 때문에 1개씩 삭제하는 것보다 훨씬 빠름

• 트랜잭션이나 테이블 락이 걸려 있을 경우 에러가 발생 할 수 있음
• - 롤백등을 할 수 없어 레코드가 삭제되어 이전으로 복구되지 않을 수 있음

그 밖에...

•  데이터형 바꾸기

  • col 2개 동시에 바꿔보기
  • ALTER TABLE tbname modify col1 char(20) not null , modify col2 char (20) not null;

• 테이블명 바꾸기

  •  ALTER TABLE tbname  RENAME tb_name_new;   

• 중복제거

  • UNIQUE INDEX로 만듬

    • ALTER IGNORE TABLE tbname ADD UNIQUE index_name(col1, col2);

• BLOB 이미지 저장

  • INSERT INTO table (description, image ) VALUES ("comment", LOAD_FILE('image.gif'));

JOIN

여러 테이블을 연결하여 수정하는 것. 여러 테이블의 조합으로 데이터를 가공할 때,

• cross join

• equi-join

• UNION

기본 함수

• SELECT USER() :현재 접속하고 있는 MYSQL 사용자 ID를 리턴함
• DATABASE() : 현재 사용중인 데이터베이스 출력
• VERSION() : 디비 서버의 버전
• QUOTE() : PHP의 addslahes()와 같은 기능.
• ABS() : 절대값 리턴
• CHAR() : 아스키 코드에 해당 하는 문자 리턴
• CURDATE() : 현재 날짜를 YYYYMMDD의 형식으로 리턴함
• CURTIME() : 현재 시간을 HHMMSS형태로 리턴함
• NOW() : 날짜와 시간을 리턴함
• DAYOFMONTH() : 몇일인지 리턴함
• DAYOFWEEK() : 무슨요일인지 숫자로 리턴함. 1일 경우 일요일, 2일 경우 월요일 
• DAYOFYEAR()  : 1년 중 며칠이 지났는가를 리턴함
• ENCRYPT() : 주어진 문자열을  UNIX crype()로 암호화한다. SELECT ENCRYPT("hello","85"); SELECT ENCRYPT("hello");
• MD5() : 264 bits이하의 메시지를 분할하여, 여러가지 과정을 거처 128비트로 압축한 메시지를 출력하는 알고리즘이다. 결과는 32자리 문자열이 된다.
• SHA() 비슷함, 40자리의 문자열이 됨
• BENCHMARK() : expr의 표현식을  count번 만큼 실행함. cpu가 얼마나 빠른지 알아볼 수 있음
• IF(test, value1, value2) : 만약test가 참이면 value1 리턴, 아니면  value2리턴
• LOWER() : 소문자로
• UPPER() : 대문자로
• MID(string, position,length) :  문자열에서 포지션에서 시작하여, length만큼의 문자열을 리턴함
• MOD(num1, num2)  
• QUARTER(date) 몇분기에 속하는지 리턴해줌
• REPLACE( string, old, new)
• REVERSE( string) :문자열을 거꾸로 배열한다.
• ROUND(X,D) : 가장 가까운 정수나 실수를 리턴함 , D옵션은 0 일때는 정수를 리턴함
• RTRIM(string) : 문자열 오른쪽에 붙은 공백 제거 

• TRIM([BOTH|LEADING|TRAILING] [remstr]FROM str) : 공백제거, 양쪽, 압, 뒤,

  • SELECT TRIM('  bar ');
• LOAD_FILE(): 파일을 읽어서 파일 내용을 문자열에 리턴함.

• DATE_ADD()

  • SELECT DATE_ADD("1984-01-18",INTERVAL 100000 DAY)

    • 태어난지 100000된 날