5. MySQL의 column types

이장에서는 다양한 column type들과 그 예를 배운다.

5.1 수치 데이타 타입

6. MySQL에서 SQL( Standed Query Language)

이장에선 MySQL에서 지원하는 SQL과 확장된 SQL에 대해 알아보기로 한다.

6.1 CREATE DATABASE syntax

* CREATE DATABASE db_name

db_name라는 이름을 가지는 데이타베이스를 생성한다.

실제로 MySQL Data 디렉토리내에 이름이 db_name인 디렉토리를 생성한다.

rpm인 경우 /var/lib/mysql/db_name

예)

mysql> CREATE DATABASE school;

또는

shell> mysqladmin CREATE school

실제로 확인을 해보자

shell> ls -l /var/lib/mysql/school

6.2 DROP DATABASE syntax

* DROP DATABASE [IF EXISTS] db_name

db_name라는 이름을 가지는 데이타베이스를 삭제한다.

마찬가지로 /var/lib/mysql/db_name을 삭제한다.

6.3 CREATE TABLE syntax

6.4 ALTER TABLE syntax

6.6 INSERT syntax

* INSERT [LOW_PRIORITY | DELAYED] [IGNORE]

[INTO] tbl_name [(col_name,...)]

VALUES (expression,...),(...),...

or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]

[INTO] tbl_name [(col_name,...)]

SELECT ...

or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]

[INTO] tbl_name

SET col_name=expression, col_name=expression, ...

- LOW_PRIORITY 옵션은 입력하고자하는 테이블이 다른 클라이언트가 접근해있으며

기다렸다가 접근이 없을시에 INSERT을 수행한다.

- DELAYED 옵션은 LOCK을 거는 효과를 낸다.

- IGNORE 옵션은 입력시 PRIMARY, UNIQUE key 가 중복 될때 무시하고 계속 입력을

하게 한다.. 이 옵션이 없는 경우 입력은 중단된다.

예)

mysql> INSERT INTO std VALUES ( \, \길동\, \홍\, \20\, \1980-1-1\, \1\, \);

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM std;

+----+------------+-----------+------+------------+------+------+

| no | first_name | last_name | age | birth | dept | sex |

+----+------------+-----------+------+------------+------+------+

| 1 | 길동 | 홍 | 20 | 1980-01-01 | 1 | |

+----+------------+-----------+------+------------+------+------+

1 row in set (0.00 sec)

[/pre]

- 앞에서 테이블을 만들때 sex의 default는 \x\이지만 디폴트 값은 입력하는 값이 없을 경우에 한해서 적용된다. 즉 \은 empty string을 의미한다.

- 위의 경우와 달리 no 컬럼은 auto_increment로 선언이 되어있기 때문에 \을 사용 하면 자동적으로 맨 마지막의 값에 1을 증가한 값을 더해 저장이 되어진다.

( 뒤장의 select에서 NULL의 의미를 다시 알아본다. )

[ 주의 ]

- 만일 where절에 조건을 적지 않으면 모든 레코드가 삭제 되어버린다.

6.8 SELECT syntax

6.9 LOAD DATA INFILE syntax

6.10 JOIN syntax

table_reference, table_reference

table_reference [CROSS] JOIN table_reference

table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr

table_reference LEFT [OUTER] JOIN table_reference USING (column_list)

- 조인은 두개의 테이블에서 특정한 조건을 만족하는 각각의 컬럼들을 합쳐서 만든다.

- select문에서 사용할수 있다.

- OUT JOIN은 조건을 만족하지 못하더라도 그 값을 필요로 할때 사용할수 있다.

예)

mysql> INSERT INTO std SET First_name= \말자\, last_name = \이\;

mysql> SELECT concat( std.last_name, std.first_name ) AS Name,

-> depart.dept_name AS department FROM std, depart WHERE std.d_no=depart.d_no;

+--------+--------------+

| Name | department |

+--------+--------------+

| 홍길동 | 컴퓨터공학과 |

| 홍기순 | 컴퓨터공학과 |

| 김순이 | 컴퓨터공학과 |

| 홍기철 | 전자계산학과 |

| 홍기자 | 전자계산학과 |

| 박영수 | 전자계산학과 |

| 이철수 | 전자계산학과 |

+--------+--------------+

7 rows in set (0.00 sec)

mysql>SELECT concat( std.last_name, std.first_name ) AS Name,

->depart.dept_name AS department FROM std join depart

->WHERE std.d_no=depart.d_no;

+--------+--------------+

| Name | department |

+--------+--------------+

| 홍길동 | 컴퓨터공학과 |

| 홍기순 | 컴퓨터공학과 |

| 김순이 | 컴퓨터공학과 |

| 홍기철 | 전자계산학과 |

| 홍기자 | 전자계산학과 |

| 박영수 | 전자계산학과 |

| 이철수 | 전자계산학과 |

+--------+--------------+

7 rows in set (0.00 sec)

- OUTER JOIN의 예

mysql> SELECT concat( std.last_name, std.first_name ) AS Name,

-> depart.dept_name AS department

-> FROM std LEFT OUTER JOIN depart USING (d_no);

+--------+--------------+

| Name | department |

+--------+--------------+

| 홍길동 | 컴퓨터공학과 |

| 홍기철 | 전자계산학과 |

| 홍기자 | 전자계산학과 |

| 홍기순 | 컴퓨터공학과 |

| 이말자 | NULL |

| 박영수 | 전자계산학과 |

| 김순이 | 컴퓨터공학과 |

| 이철수 | 전자계산학과 |

+--------+--------------+

8 rows in set (0.01 sec)

mysql> select concat( std.last_name, std.first_name ) AS Name,

-> depart.dept_name AS department

-> from depart left outer join std using (d_no);

+--------+--------------+

| Name | department |

+--------+--------------+

| 홍길동 | 컴퓨터공학과 |

| 홍기순 | 컴퓨터공학과 |

| 김순이 | 컴퓨터공학과 |

| 홍기철 | 전자계산학과 |

| 홍기자 | 전자계산학과 |

| 박영수 | 전자계산학과 |

| 이철수 | 전자계산학과 |

+--------+--------------+

7 rows in set (0.00 sec)

- LEFT의 의미를 파악할수 있을 것이다.

[/pre]

6.11 UPDATE syntax

6.14 DESCRIBE syntax (Get information about columns)

6.15 LOCK TABLES/UNLOCK TABLES syntax

- table을 LOCK, UNLOCK한다.

- mysql은 거의 모든 작업들을 테이블 단위로 이루어진다. ( 아직 컬럼단위는 아니다.)

- 테이블을 READ LOCK을 걸면 자신을 포함한 다른 쓰레드들은 오직 READ만 할수 있다.

- WRITE LOCK을 걸면 다른 쓰레드에게 write와 read도 불가능한다. ( 자신은 가능)

- 다른 쓰레드들은 자신이 참조할려는 테이블이 LOCK이 걸려 있으면 풀리기을 기다린다.

- 정확한 처리를 위해서는 필요한 부분이므로 충분히 이해와 개념을 잡기

바란다.

예제)

가령 예를 들어 은행의 입출금의 경우를 생각해보자.

출금의 경우는 먼저 잔액이 있는 지를 확인하는 작업이 이루어진후, 잔액에 여유가 있는 경우 거래가 이루어진다. 하지만 A라는 구좌에 100만원이 있는 경우 B라는 사람이 먼저 100만원을 출금한다고 하자. 우선 구좌에 현금이 있는 지 확인을 할것이다. 현재는 잔고가 100만원이 있기 때문에 거래가 성사되어진다. 하지만 확인을 하는 시점에서 또라는 C라는 사람이 50만월을 출금할려고 한다면 이 사람도 현재 잔액이 100만원이 있다는 사실을 알고 출금을 할려고 할것이다. 정상적인 거래라고 하면 C라는 사람의 거래는 성사되지 않아야 할것이다. 하지만 위와 같은 경우 150만원이 출금되고도 잔액이 50만원이 남아 있는 불상사가 있어날 확률이 있다.

이런 경우에는 먼저 확인한 프로세스가 write LOCK을 사용함으로써 안정하게 거래가 이루어질 수 있다.

- 다른 쓰레드에서선 SELECT는 되지만 INSERT라던지 UPDATE는 LOCK을 풀리기만을 기다린다.

- LOCK을 해제하기전에 다른 쓰레드에서 쿼리를 주면 LOCK이 풀리기만을 기다리는것을 볼 수 있다.

6.16 GRANT and REVOKE syntax

6.17 CREATE INDEX syntax

CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name[(length]),... )

- 인텍스를 생성한다. 멀티 인텍스도 생성할수 있다.

- CHAR, VARCHAR, TEXT, BLOB등은 자료의 일부분으로 인텍스를 생성할수 있다.

6.19 MySQL에서 바이너리 파일의 저장 ( BLOB)

7. MySQL의 백업 방안

- 데이타 베이스의 생명은 저장된 자료일것이다. 이장에서는 자료를 백업할수있는 방안에 대해 알아보기로 한다.

7.1. 파일

- 앞에서 설명한것처럼 데이타베이스명으로 디렉토리가 생성되며 테이블은 그 안에서 파일로 생성된다. 이 파일을 저장해놓기전에 먼저 LOCK TABLES을 통해 LOCK을 걸어 주는 것이 좋다. 백업본을 만드는 동안에 다른 쓰레드에서 질의가 들어올수있기 때문 이다.

- 이 방법은 dump하는 시간과 공간을 많이 줄여준다. 하지만 문제가 있을수도 있다.

7.2 mysqldump

- SQL문장으로 백업을 해준다. 시간이 많이 걸리며 특정 컬럼은 저장하지 못하는 경우가 있다.