자주 사용하는 mysql syntax

[SQL] 1. 테이블 구조와 내용 복사
CREATE TABLE b AS SELECT * FROM a;

[SQL} 2. 기본 테이블에 컬럼 추가
ALTER TABLE 테이블 명 ADD 컬럼명 타입 

[SQL] 3. mysql 사용자 추가
GRANT ALL PRIVILEGES ON *.* TO him@localhost IDENTIFIED BY 'passwd' WITH GRANT OPTION;

[SQL] 4. 요일 변환
YYYYMMDD -> 무슨 요일인지 확인
SELECT * FROM system_state WHERE to_char(to_date(substr(dt,0,10), 'YYYY-MM-DD'), 'DAY') ='월요일' 

[SQL] 5. 무결성 제약 조건 확인
SELECT * FROM user_cons_columns WHERE costraint_name='R_106' 

[SQL] 6. 컬럼명 변경
ALTER TABLE a RENAME COLUMN col1 TO col2 

[SQL] 7. Dump and Rebuild
[Dump]
$> mysqldump -u[사용자아이디] -p 데이터베이스명 [테이블명] > 저장될 파일명 
$> mysqldump -ukamkami -p mydatabase > kamkami.pe.kr.sql 
이렇게 하면 디비(mydatabase)를 몽땅 덤프를 뜨게된다. 
테이블만 덤프를 뜨고 싶다면 
$> mysqldump -ukamkami -p mydatabase member_table > kamkami.pe.kr.member_table.sql 
이렇게 하면 테이블만 덤프를 뜰 수 있다. 
[Rebuild] 
덤프 파일을 가지고 복구를 하는 방법도 간단하다. 
$> mysql -u[사용자아이디] -p [디비명] < 덤프파일명 
$> mysql -ukamkami -p < kamkami.pe.kr.sql 
$> mysql -ukamkami -p mydatabase < kamkami.pe.kr.member_table.sql 
[특정 db의 특정 table에서 원하는 값만 덤프받기] 
edu라는 디비에 a,b,c라는 테이블이 있다. 여기서 a라는 테이블에서 no가 7번이상이고 10번 
이하인 값만 덤프를 받고자 한다. 어떻게 하겠는가? 
여기서 사용되는 옵션은 -w 이다.그럼 위 질문의 sql문은 아래와 같다 
$> mysqldump -u mysql_admin -p edu a -w'no=>7 and no=<10' > edu_a_cond.sql 
위와같이 하면 no가 7~10번까지가 덤프될것이다. 
위에서 조건문은 -w 다음에 싱글쿼테이션으로 묶어준다. 
[디비 스키마(Schema)만 백업받기] 
초기에 작성해 놓은 테이블 스키마가 없을때 어떻게 하겠는가? : mysqldump옵션 -d 사용. 
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

[SQL] 8. JOIN 문 left outer join
[형식] 
table_reference, table_reference 
table_reference [CROSS] JOIN table_reference 
table_reference INNER JOIN table_reference join_condition 
table_reference STRAIGHT_JOIN table_reference 
table_reference LEFT [OUTER] JOIN table_reference join_condition 
table_reference LEFT [OUTER] JOIN table_reference 
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference 
{ OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr } 
table_reference RIGHT [OUTER] JOIN table_reference join_condition 
table_reference RIGHT [OUTER] JOIN table_reference 
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference 
여기서 
table_reference는 다음과 같이 정의된다. 
table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)] 
또한 join_condition의 정의는 다음과 같다. 
ON conditional_expr | 
USING (column_list) 
LEFT OUTER JOIN 문은 ODBC와 호환하기 위해 쓰인다. 
테이블 참조는 tbl_name AS  alias_name이나 tbl_name alias_name처럼 사용해서 별명을 붙일 수 있다. 
mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name=t2.name; 
ON 조건은 WHERE 절에서 사용되는 형식과 같은 조건이다. 
LEFT JOIN에서 ON이나 USING 부분으로  오른쪽 테이블과 일치하는 레코드가 없으면, 오른쪽 테이블의 모든
컬럼이 담긴 row에 NULL을 넣는다. 그러므로 다른 테이블에서 카운터파트가 없는 레코드를 찾는데 쓰인다. 
mysql> SELECT table1.* FROM table1 
    -> LEFT JOIN table2 ON table1.id=table2.id 
    -> WHERE table2.id IS NULL; 
USING (column_list) 절은 두 테이블에 꼭 존재하는 컬럼 목록의 이름이다. 
다음 두 표현은 같다. 
A LEFT JOIN B USING (C1,C2,C3,...) 
A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,... 
두 테이블의 NATURAL  [LEFT] JOIN는 INNER  JOIN 또는 USING  절을 사용한 LEFT JOIN과 같은 의미이다. 
INNER JOIN이나 ,(comma)는 같은 의미이다. 
RIGHT JOIN는 LEFT JOIN과 유사하다. 
STRAIGHT_JOIN은 JOIN과 같지만, 왼쪽 테이블을 오른쪽 테이블보다 먼저 읽히는 점이 다르다. 
USE INDEX (key_list)를 사용하면, 여러 인덱스 중에서 오직  하나의 인덱스를 지정하는 것이고, 
IGNORE INDEX (key_list)는 지정한 인덱스를 사용하지 않게 한다. 
FORCE INDEX는 USE INDEX (key_list)와 같지만, 테이블 스캔기능이 더 있다. 
USE/IGNORE KEY와 USE/IGNORE INDEX는 동일한 의미이다. 
예 
mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id; 
mysql> SELECT * FROM table1 LEFT JOIN table2 
    -> ON table1.id=table2.id; 
mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id); 
mysql> SELECT * FROM table1 LEFT JOIN table2 
    -> ON table1.id=table2.id LEFT JOIN table3 
    -> ON table2.id=table3.id; 
mysql> SELECT * FROM table1 USE INDEX (key1,key2) 
    -> WHERE key1=1 AND key2=2 AND key3=3; 
mysql> SELECT * FROM table1 IGNORE INDEX (key3) 
    -> WHERE key1=1 AND key2=2 AND key3=3; 

JOIN 문 : 
둘 이상의 테이블의 레코드 조합으로 하나의 테이블 레코드인 것처럼  찾는 것을 join이라 한다. 
이해를 돕기 위하여 다음 테이블을 가정한다. 

mysql> SELECT * FROM test1; 
+--------+------+ 
| a      |   b  | 
+--------+------+ 
| 금강    |   1  | 
| 한강    |   2  | 
| 대동강  |   3  | 
| 두만강  |   4  | 
+--------+------+ 
4 rows in set (0.00 sec) 
mysql> SELECT * FROM test2; 
+------+--------+ 
|   b  |    d   | 
+------+--------+ 
|   1  |  백두산 | 
|   2  |  금강산 | 
|   5  |  지리산 | 
|   6  |  한라산 | 
+------+--------+ 
4 rows in set (0.00 sec) 

1) FULL JOIN :
FULL JOIN은 각 테이블의 레코드 수를 곱한 만큼의 레코드를 출력하며 이를 Cartesian product라 한다. 
from test1, test2에서 사용한 컴마(,)를 join 연산자로 생각하면 된다. 
컴마 대신 join이나 cross join을 사용해도 같은 결과를 보인다. 
[예제] 
mysql> SELECT * FROM test1,test2; 
+--------+------+------+--------+ 
|   a    |   b  |   b  |   d    | 
+--------+------+------+--------+ 
| 금강    |   1  |   1  | 백두산  | 
| 한강    |   2  |   1  | 백두산  | 
| 대동강  |   3  |   1  | 백두산  | 
| 두만강  |   4  |   1  | 백두산  | 
| 금강    |   1  |   2  | 금강산  | 
| 한강    |   2  |   2  | 금강산  | 
| 대동강  |   3  |   2  | 금강산  | 
| 두만강  |   4  |   2  | 금강산  | 
| 금강    |   1  |   5  | 지리산  | 
| 한강    |   2  |   5  | 지리산  | 
| 대동강  |   3  |   5  | 지리산  | 
| 두만강  |   4  |   5  | 지리산  | 
| 금강    |   1  |   6  | 한라산  | 
| 한강    |   2  |   6  | 한라산  | 
| 대동강  |   3  |   6  | 한라산  | 
| 두만강  |   4  |   6  | 한라산  | 
+--------+------+------+--------+ 
16 rows in set (0.00 sec) 

mysql> SELECT * FROM test1 join test2; 
mysql> SELECT * FROM test1 CROSS JOIN test2; 

2) STRAIGHT_JOIN :
straight_join은 컬럼의 순서를 from 절에 나오는 테이블의 순서대로 출력하는 join이다. 
[예제]
mysql> SELECT * FROM test1,test2;
mysql> SELECT * FROM test2,test1; 
mysql> SELECT * FROM test1 STRAIGHT_JOIN test2; 
mysql> SELECT * FROM test2 STRAIGHT_JOIN test1; 
+------+--------+--------+------+ 
|   b  |   d    |   a    |   b  | 
+------+--------+--------+------+ 
|   1  | 백두산  | 금강    |   1  | 
|   2  | 금강산  | 금강    |   1  | 
|   5  | 지리산  | 금강    |   1  | 
|   6  | 한라산  | 금강    |   1  | 
|   1  | 백두산  | 한강    |   2  | 
|   2  | 금강산  | 한강    |   2  | 
|   5  | 지리산  | 한강    |   2  | 
|   6  | 한라산  | 한강    |   2  | 
|   1  | 백두산  | 대동강  |   3  | 
|   2  | 금강산  | 대동강  |   3  | 
|   5  | 지리산  | 대동강  |   3  | 
|   6  | 한라산  | 대동강  |   3  | 
|   1  | 백두산  | 두만강  |   4  | 
|   2  | 금강산  | 두만강  |   4  | 
|   5  | 지리산  | 두만강  |   4  | 
|   6  | 한라산  | 두만강  |   4  | 
+------+--------+--------+------+ 
16 rows in set (0.01 sec) 

3) Theta JOIN :
full join에 where 절로 조건을 제시할 경우 이를 Theta join이라 한다. 
예를 들어, test1과 test2에 b의 값이 동일한 레코드를 선택하는 경우는 다음과 같다. 
[예제]
mysql> SELECT * FROM test1, test2 WHERE test1.b=test2.b; 
+------+------+------+--------+ 
|   a  |   b  |   b  |   d    | 
+------+------+------+--------+ 
| 금강  |   1  |   1  | 백두산  | 
| 한강  |   2  |   2  | 금강산  | 
+------+------+------+--------+ 
2 rows in set (0.00 sec) 

4) INNER JOIN :
Inner join를 이용하여 앞 예제와 동일한 결과를 얻을 수 있는 예는 다음과 같다.  
이는 select 문에서 join에 사용할 컬럼명을 ON test1.b=test2.b와 같이 직접 지정해도 된다. 
[예제] 
mysql> SELECT * FROM test1 INNER JOIN test2 USING(b); 
+------+------+------+--------+ 
|   a  |   b  |   b  |   d    | 
+------+------+------+--------+ 
| 금강  |   1  |   1  | 백두산  | 
| 한강  |   2  |   2  | 금강산  | 
+------+------+------+--------+ 
2 rows in set (0.00 sec) 
mysql> SELECT * FROM test1 INNER JOIN test2 ON test1.b=test2.b; 
+------+------+------+--------+ 
|   a  |   b  |   b  |   d    | 
+------+------+------+--------+ 
| 금강  |   1  |   1  | 백두산  | 
| 한강  |   2  |   2  | 금강산  | 
+------+------+------+--------+ 
2 rows in set (0.00 sec) 
mysql> SELECT * FROM test1, test2 WHERE test1.b=test2.b; 

5) NATURAL JOIN :
두 테이블에 같은 이름을 가진 컬럼이 있고, 이 컬럼을 기준으로 join을 하려면 natural join 을 사용한다. 
[예제] 
mysql> SELECT * FROM test1 NATURAL JOIN test2; 
+------+------+------+--------+ 
|   a  |   b  |   b  |   d    | 
+------+------+------+--------+ 
| 금강  |   1  |   1  | 백두산  | 
| 한강  |   2  |   2  | 금강산  | 
+------+------+------+--------+ 
2 rows in set (0.00 sec) 

6) LEFT OUTER JOIN :
test1에는 4개의 레코드가 있는데 where test1.b=test2.b라는 조건 때문에 두 개의 레코드 밖에 선택되지 
않았다. 이때 test2의 b에 test1의 b 값이 존재하지  않더라도 test1의 레코드를 모두 가져오려면 
left outer join을 사용한다. left outer join에서 outer는 옵션이므로  생략해도 된다. 
[예제]
mysql> SELECT * FROM test1 LEFT JOIN test2 USING(b); 
+--------+------+------+--------+ 
|   a    |   b  |   b  |   d    | 
+--------+------+------+--------+ 
| 금강    |   1  |    1 | 백두산  | 
| 한강    |   2  |    2 | 금강산  | 
| 대동강  |   3  | NULL | NULL   | 
| 두만강  |   4  | NULL | NULL   | 
+--------+------+------+--------+ 
4 rows in set (0.00 sec) 
mysql> SELECT * FROM test1 LEFT JOIN test2 ON test1.b=test2.b; 
+--------+------+------+--------+ 
|   a    |   b  |   b  |   d    | 
+--------+------+------+--------+ 
| 금강    |   1  |    1 | 백두산  | 
| 한강    |   2  |    2 | 금강산  | 
| 대동강  |   3  | NULL | NULL   | 
| 두만강  |   4  | NULL | NULL   | 
+--------+------+------+--------+ 
4 rows in set (0.01 sec) 

7) RIGHT OUTER JOIN :
test2에는 4개의 레코드가 있는데, where test1.b=test2.b라는 조건 때문에 2개의 레코드 밖에 선택되지 
않는다. 이때 test1의  b에 test2의 b값이 존재하지 않더라도  test2의 레코드를 모두 가져오려면 
right outer join을 사용한다. right outer join에서 outer는 옵션이므로 생략할 수 있다. 
[예제]
mysql> SELECT * FROM test1 RIGHT JOIN test2 ON test1.b=test2.b; 
+------+------+------+--------+ 
|   a  |   b  |   b  |   d    | 
+------+------+------+--------+ 
| 금강  |    1 |   1  | 백두산  | 
| 한강  |    2 |   2  | 금강산  | 
| NULL | NULL |   5  | 지리산  | 
| NULL | NULL |   6  | 한라산  | 
+------+------+------+--------+ 
4 rows in set (0.00 sec) 

[SQL] 9. 특정 칼럼을 포함하는 테이블을 search하는 방법
테이블이 수십개가량 있는데요
여기서 특정 칼럼명을 검색해서 column size를 전부 늘려주어야 합니다.
특정 칼럼을 포함하는 테이블을 search하는 좋은 방법이 있으면 알려주시면 감사하겠습니다.
mysql> SELECT a.table_name, b.comments, a.column_name, c.comments, a.data_type,
    -> a.data_length, a.data_precision, a.nullable 
    -> FROM user_tab_columns a, user_tab_comments b, user_col_comments c
    -> WHERE a.column_name = 'column_name'
    -> AND a.table_name = b.table_name
    -> AND b.table_name = c.table_name
    -> AND a.column_name = c.column_name
user_tab_columns : 사용자 테이블명, 컬럼명
user_tab_comments : 사용자 테이블 해설
user_col_comments : 사용자 컬럼 해설

[SQL] 10. 중복찾기  
mysql> SELECT * FROM 테이블명 
     -> WHERE name in( SELECT name FROM 테이블명 GROUP BY id, name having count(*) > 1 ) 

[SQL] 11. mysql 원격접속  
mysql> GRANT ALL PRIVILEGES ON *.* TO username@ip IDENTIFIED BY "password";

* Host IP examples:

[SQL] 12. mysql backup script
백업 위치에 따라,
/home2/DATA/mysqldump/20050701/DB list.....
                   /20050702/DB list.....
                   /20050703/DB list.....
이런 식으로 DB 디렉터리 아래 DB 명 및 테이블이름.sql으로 쌓이게 될 겁니다.
매일 매일 백업한다는 가정 하에 7일 전 데이터는 무조건 삭제합니다.
#!/bin/sh
# 작성일 : 2005/07/01
# 최종수정일 : 2005/09/14
# http://www.rootman.co.kr
# 별도 수정 사항 : db root, 백업위치(변수 : dump_dir)
#---------------------------------------------------------
dump_date=$(date +%Y%m%d-%H);
dump_dir="/home2/DATA/mysqldump";
DB_user="root";
DB_pass="루트패스워드";
DB_optimize_switch=0;   # 만일 1이면 optimizing까지 
for database in `mysqlshow -u ${DB_user} -p${DB_pass} | awk -F" " '{ print $2 }' | grep -
v "^$" | grep -v "Databases"`
do
  echo "*--------------------------------------------------------------------------------- *";
  echo "* ${database} 시작";
  echo "*--------------------------------------------------------------------------------- *";
  if [ ! -d "${dump_dir}/${dump_date}/${database}" ]
  then
       mkdir -p ${dump_dir}/${dump_date}/${database};
  fi
  for table in `mysql -u ${DB_user} -p${DB_pass} -e"show tables" ${database} | grep -
  v "Tables_in_${database}" | grep -v "^$"`
  do
     if [ ${DB_optimize_switch} = "1" ]
     then
         mysql -u ${DB_user} -p${DB_pass} -e"optimize table ${table}" ${database} 
     fi
     mysqldump -u ${DB_user} -p${DB_pass} -n -t "${database}" ${table} > ${dump_dir}/
     ${dump_date}/${database}/${table}.sql
     echo $table;
  done
sleep 1;
done
echo "* ----------------------- 7일 전 디렉터리 삭제 -------------------- *";
Old_Date=`/bin/date -d "7 day ago" +"%Y%m%d-%H"`;
rm -rf ${dump_dir}/${Old_Date};
echo "*--------------------------------------------------------------------------------- *";
echo "* 백업 위치 정보 : ${dump_dir}/${dump_date} "; 
echo "*--------------------------------------------------------------------------------- *";
ls -asl ${dump_dir}/${dump_date};
exit 0;

[SQL] 13. LOAD DATA INFILE syntax
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
  [REPLACE | IGNORE]
  INTO TABLE tbl_name
  [FIELDS
      [TERMINATED BY '\t']
      [[OPTIONALLY] ENCLOSED BY '']
      [ESCAPED BY '\\' ]
  ]
  [LINES TERMINATED BY '\n']
  [IGNORE number LINES]
  [(col_name,...)]