mysql 명령어 정리

1. 해당 디렉토리 찾아가기

$ cd /

$ cd usr/local/mysql

2. mysql 처음 사용시 해야할 것

mysql의 root사용자의 패스워드를 다음과 같이 설정하고 mysql를 재기동하도록 합니다.

$ mysql -u root

mysql> use mysql;
mysql> show databases;
mysql> show tables;
(여기서 db, user 테이블을 주목해 두어야 함.)
mysql> select * from db;
mysql> select * from user;

mysql> update user set password=password('******') where user='root';

mysql>\q;

$ mysqladmin -u root reload

3. mysql 재기동

$ mysqladmin -uroot -p reload
Enter password : ******

위의 패스워드는 MySql에서 사용하는 패스워드입니다.



4. 데이터베이스의 생성

$ mysqladmin -uroot -p create db명
Enter password : ******



5. 데이터베이스의 삭제

$ mysqladmin -uroot -p drop db명
Enter password : ******



6. 특정 db 사용자 권한 설정 및 새로운 사용자 등록

$ mysql -u root -p
Enter password : ******

mysql> use mysql;
mysql> insert into db values ('%','db명', '사용자ID', 'y','y','y','y','y','y','y','y','y','y');
mysql> select host, db, user from db;

mysql> insert into user (host, user, password) values ('localhost', '사용자ID', password('패스워드'));
mysql> select host, user, password from user;

mysql> \q



※ 지금부터는 linux3s1 사용자(패스워드는 111111으로 가정)가 linux3 데이터베이스를 사용한다는 조건하에서 예시를 들어 설명합니다.

7. mysql 의 실행

$ mysql -u linux3s1 -p
Enter password : ******

8. 어떠한 데이터베이스가 있는지 확인

mysql> show databases;

9. 사용할 데이터베이스를 선택

mysql> use linux3s;

10. 특정 데이터베이스안에 어떠한 테이블들이 있는지 확인

mysql> show tables;



11. table 생성/수정/삭제

⑴ linux3s 데이터베이스에 imsi 테이블을 CREATE TABLE을 이용하여 생성한다.

mysql> create table imsi(
-> name CHAR(8),
-> id VARCHAR(10) NOT NULL,
-> url VARCHAR(40),
-> email VARCHAR(40),
-> milage INT(11),
-> level CHAR(2),
-> PRIMARY KEY(id) );

⑵ desc 명령으로 imsi 테이블의 구조 명세를 살펴보자.

mysql> desc imsi;

⑶ 테이블 내용을 수정하려면 ALTER TABLE 문을 이용한다.

mysql> alter table imsi change column id id varchar(12) NOT NULL;

mysql> desc imsi;

⑷ imsi 테이블을 삭제하려면 DROP TABLE문을 사용한다.

mysql> drop table imsi;

mysql> show tables;

⑸ mysql client를 이용하지 않고 데이터베이스내에 테이블을 생성하는 방법

아래의 SQL문을 imsi.sql이라는 파일로 저장한다.

create table imsi(
name CHAR(8),
id VARCHAR(10) NOT NULL,
url VARCHAR(40),
email VARCHAR(40),
milage INT(11),
level CHAR(2),
PRIMARY KEY(id) );

이제 계정의 커맨드 라인에서 아래와 같은 명령을 내리면 mysql client를 통해 linux3s 데이터베이스에 연결하지 않고도 데이터베이스내에 imsi 테이블을 생성.

아래의 명령은 linux3s1라는 사용자로 linux3s 데이터베이스에 연결하여 imsi.sql 파일에 담긴 sql문을 실행한다는 의미이다.

$ mysql -u linux3s1 -p linux3s < imsi.sql
Enter password : 111111

12. INSERT 명령문으로 5개의 데이터를 imsi 테이블에 삽입해 보자.

mysql> insert into imsi (name, id, url, email, milage, level)
-> values ('이상군', 'server', '815server.net', 'master@815server.net', '1000', 'A');

mysql> insert into imsi (name, id, url, email, milage, level)
-> values ('김재현', 'regist', 'dosa.com', 'master@dosa.com', '1500', 'B');

mysql> insert into imsi (name, id, url, email, milage, level)
-> values ('마준석', 'you', 'youiwe.co.kr', 'master@youiwe.co.kr', '2000', 'C');

mysql> insert into imsi (name, id, url, email, milage, level)
-> values ('김대수', 'iwe', 'youiwe.co.kr', 'master@youiwe.co.kr', '1500', 'B');

mysql> insert into imsi (name, id, url, email, milage, level)
-> values ('한용수', 'yshan', 'hanserver.com', 'master@hanserver.com', '2500', 'E');

13. SELECT 문으로 확인해 보자.

mysql> select * from imsi;

14. SELECT 문으로 level이 B인 사용자의 이름과 아이디를 출력해 보자.

mysql> select name, id from imsi where level='B';

15. DISTINCT 옵션을 이용하여 imsi 테이블에 총 몇 종류의 사용자 level이 존재하는지 알아보자.

mysql> select distinct (level) from imsi;

16. WHERE 문을 이용하여 milage가 1500 이상이면서 level이 'B'인 사람의 이름과 아이디, url을 출력해 보자

mysql> select name, id, url from imsi where milage>1500 AND level='B';

17. ORDER BY문을 이용하여 검색 결과를 정렬하여 보자

milage가 2000 이상인 사람의 이름과 아이디, milage를 milage가 높은 순서대로 출력

mysql> select name, id, milage from imsi where milage > 2000 order by milage DESC;

18. 문자열을 포함하여 검색

mysql> select name, '님의 milage는 ', milage, '점입니다.' from imsi?? where milage >= 2000 order by milage;

19. 산술식을 이용하여 출력

mysql> select name, id, email, milage+200 from imsi where id='yshan';

20. 그룹함수를 이용

그룹함수를 이용하면 필드 값의 평균이나 최대, 최소, 또는 해당 조건을 만족하는 레코드가 몇 개 인지 알 수 있으므로 수치를 값으로 갖는 필드에 대한 통계치를 구할 때 유용하다.

count(필드명) //조건을 만족하는 레코드의 개수
sum(필드명) //해당 필드의 합
min(필드명) //해당 필드의 값 중 최소값
max(필드명) //해당 필드의 값 중 최대값
avg(필드명) //해당 필드의 평균값

mysql> select count(*) from imsi where level='B';
mysql> select count(*) from imsi where name like '%박%';
'박%';
'전_';
'전__';
where date between '2001-07-10' and '2001-08-30';

21. 자료의 갱신

mysql> update imsi set name='전세정' where name='마준석';

22. 특정 레코드의 삭제

mysql> delete from imsi where name='전세정';



백업과 복구

· 데이터베이스의 백업(backup)과 복구(restore)는 매우 중요하다.

MySQL에서는 mysqldump라는 파일을 실행하여 데이터베이스내의 데이터를 외부 파일로 저장할 수 있다. mysqldump는 mysql 디렉토리 밑의 bin 디렉토리에 존재하며 아래와 같은 명령어를 통해 백업을 수행한다.

mysqldump -u [user_name] -p [백업할 db_name] > [외부로 저장할 파일명]

23. linux3s? 데이터베이스를 백업해 보자.

$ mysqldump -u linux3s1 -p linux3s > linux3s_backup.sql
Enter password : ******

24. 백업된 파일의 확인

$ ls -l linux3s_backup.sql

25. 위 명령의 실행 결과 생성된 linux3s_backup.sql 파일의 내용을 확인한다.

$ cat linux3s_backup.sql

mysqldump 명령으로 백업된 데이터베이스는 해당 데이터베이스내에 있는 테이블의 구조를 정의한 데이터 정의문과 INSERT 문과 같은 데이터 조작문의 SQL 형태로 저장되어 있다.

이렇게 SQL 형태로 자장되면 아래와 같은 명령으로 쉽게 데이터를 복구할 수 있다. 또한 SQL은 데이터베이스의 표준언어이므로 MySQL이 아닌 다른 database server에 해당 database를 이식하려 할 때에도 호환성에 의한 문제가 거의 발생치 않는 장점이 있다.

26. 필요에 따라 linux3s_backup.sql 파일의 내용을 수정한 후 다음과 같은 방법으로 복구한다.

$ mysql -u linux3s1 -p linux3s < linux3s_backup.sql
Enter password : ******



※ 컬럼의 유형

○ 컬럼(Column)

- 데이터베이스를 구성하는 최소단위
- 컬럼들이 모여서 테이블(Table)을 구성

○ 컬럼의 유형

TINYINT
·부호 있는 정수 -128 ∼ 127
·부호 없는 정수 0 ∼ 255
·1 Byte

SMALLINT
·부호 있는 정수 -32768 ∼ 32767
·부호 없는 정수 0 ∼ 65535
·2 Byte

MEDIUMINT
·부호 있는 정수 -8388608 ∼ 8388607
·부호 없는 정수 0 ∼ 16777215
·3 Byte

INT 또는
INTEGER
·부호 있는 정수 -2147483648 ∼ 2147483647
·부호 없는 정수 0 ∼ 4294967295
·4 Byte

BIGINT
·부호 있는 정수 -9223372036854775808 ∼ -9223372036854775807
·부호 없는 정수 0 ∼ 18446744073709551615
·8 Byte

FLOAT
·단일 정밀도를 가진 부동 소수점
·-3.402823466E+38 ∼ 3.402823466E+38

DOUBLE
·2 배 정밀도를 가진 부동 소수점
·-1.79769313486231517E+308 ∼ 1.79769313486231517E+308

DATE
·날짜를 표현하는 유형
·1000-01-01 ∼ 9999-12-31

DATETIME
·날짜와 시간을 표현하는 유형
·1000-01-01 00:00:00 ∼ 9999-12-31 23:59:59

TIMESTAMP
·1970-01-01 00:00:00부터 2037년 까지 표현
·4 Byte

TIME
·시간을 표현하는 유형
·-839:59:59 ∼ 833:59:59

YEAR
·년도를 표현하는 유형
·1901년 ∼ 2155년

CHAR(M)
·고정길이 문자열을 표현하는 유형
·M = 1 ∼ 255

VARCHAR(M)
·가변길이 문자열을 표현하는 유형
·M = 1 ∼ 255

TINYBLOB
TINYTRXT
·255개의 문자를 저장
·BLOB : Binary Large Object의 약자

BLOB
TEXT
·63535개의 문자를 저장

MEDIUMBLOB
MEDIUMTEXT
·16777215개의 문자를 저장

LONGBLOB
LONGTEXT
·4294967295(4Giga)개의 문자를 저장