MySQL / 명령어 정리

* root 암호설정 (root로 로그인하여 해야함)

mysqladmin -u root password '변경암호'
mysqladmin -u root -p password '변경암호'

* DB작업

DB생성: mysql> create database DB명 ( or % mysqladmin -u root -p create DB명 )
DB삭제: mysql> drop database DB명

DB사용: mysql> use DB명 (엄밀히 말하자면, 사용할 'default database'를 선택하는 것이다.)
DB변경: mysql> alter database db명 DEFAULT CHARACTER SET charset

* MySQL 연결

mysql -u 사용자 -p DB명 ( or % mysqladmin -u root -p drop DB명 )

 

* 데이터파일 실행(sql*loader기능, 대용량 파일을 DB에 Insert 하고 싶을 때)

mysql>
load data infile "데이터파일" into table 테이블명
fileds terminated by ','
lines terminated by '\n' 
ignore 1 lines;

- 데이터파일의 위치는 /home/kang/load.txt 와 같이 절대경로로 지정
- fileds terminated by ','  구분자를 ','로 지정
- lines terminated by '\n' 한줄의 마지막 구분자를 '\n'으로 지정
- ignore 1 lines 첫번째 라인은 무시한다.(보통 첫번째라인에 컬럼의 이름명이 적혀있을 경우)

 

* 질의 파일 실행

[root@snubi]$ mysql -u 사용자 -p DB명 < 질의파일

- mysql프롬프트상에서
mysql> source 질의파일

 

* 여러가지 명령정리

-서버의 variables(설정사항)출력
mysql> show variables;

 

-조건에 맞는 variables만 출력
mysql> show variables like 'have_inno%'

 

-database목록
mysql> show databases;

 

-현재DB의 테이블목록(temporary table은 출력하지 않음)

mysql> show tables;

-지정된 db명이 소유한 테이블목록
mysql> show tables from db명;

 

-조건에 맞는 테이블목록만 출력

mysql> show tables like 'mem%';

 

-인덱스 보기

mysql> show index from 테이블명;

 

-테이블구조(describe 테이블명, explain 테이블명)

mysql> show columns from 테이블명;

 

-현재 DB의 테이블들의 상태(row수,table type,row길이,..)
mysql> show table status;

-지정된 DB의 테이블들의 상태(row수,table type,row길이,..)

mysql> show table status from db명;

-해당 테이블 생성 SQL문 출력
mysql> show create table 테이블명;

 

-테이블명 변경(ALTER TABLE 테이블1 RENAME TO 테이블2)

mysql> rename table 테이블1 to 테이블2;

-rename multiple tables
mysql> rename table 테이블1 to 테이블2, 테이블3 to 테이블4;

 

-테이블을 다른 DB로 이동
mysql> rename table db1명.테이블명 to db2명.테이블명;

 

-컬럼추가

mysql> alter table 테이블명 add 컬럼명 데이터타입;

-컬럼제거

mysql> alter table 테이블명 del 컬럼명;

 

-컬럼명에 지정된 컬럼타입의 변경

mysql> alter table 테이블명 modify 컬럼명 컬럼타입;

-컬럼명 변경

mysql> alter table 테이블명 change old컬럼명 new컬럼명 컬럼타입

 

-테이블type변경
mysql> alter table 테이블명 type=innodb;

 

-10000row를 수용할 수 있을 만큼 메모리할당(heap type이므로)

mysql> create table 테이블명(..) type=heap min_rows=10000;

-MySQL서버버전 출력

mysql> select version();

-테이블1과 동일한 테이블 생성(with 데이터, as는 생략가능)

mysql> create table 테이블2 as select * from 테이블1;

-테이블1과 동일한 구조의 테이블 생성(without 데이터, 1=2는 0으로 할수도 있다.)

mysql> create table 테이블2 as select * from 테이블1 where 1=2;

-테이블1의 데이터를 테이블2에 insert

mysql> insert into 테이블2 select * from 테이블1;

 

* 테이블이 존재여부 파악

DROP TABLE IF EXISTS 테이블명;
CREATE TABLE 테이블명 (...);

프로그래밍 언어에서 COUNT(*)를 사용하여 질의가 성공하면 테이블이 존재함을

파악할 수 있다.

ISAM, MyISAM의 경우 COUNT(*)가 최적화되어 상관없으나, BDB, InnoDB의 경우

full scan이 발생하므로 사용하지 마라.
대신 select * from 테이블명 where 0; 을 사용하라.

질의가 성공하면 테이블이 존재하는 것이고, 아니면 존재하지 않는 것이다.

* 검색조건(where)

mysql> select * from work where 열명 regexp "정규표현식";

 

* replace

replace into test values('maddog','kang myung gyu')'

해당 레코드 존재하면 update하고, 존재하지 않는다면 insert한다.(insert문법과 동일)