4. MySQL 의 주요 SQL 문법
▶ CREATE DATABASE
문법 : CREATE DATABASE db_name
주어진 이름의 새로운 데이터베이스를 생성한다. 이때 사용 가능한 이름은 이 장의 첫 부분에서 설명한 바와 같다. 만일 같은 이름의 데이터베이스가 이미 존재 할 경우 에러가 난다. MySQL 은 데이터베이스를 디렉토리로 테이블을 파일로 관리하기 때문에 데이터베이스가 막 생성되었을 경우 테이블을 존재 하지 않으며 실제로 CREATE DATABASE 명령은 주어진 이름의 빈 디렉토리를 생성하는 역할을 한다.
다음과 같이 mysqladmin 유틸리티를 이용해서도 데이터베이스를 생성할 수 있다.
#./mysqladmin create school
▶DROP DATABASE
문법 : DROP DATABASE [IF EXISTS] db_name
DROP DATABASE 은 먼저 주어진 데이터베이스내의 모든 테이블을 삭제하고 데이터베이스를 삭제한다. 일단 삭제된 후에는 다시 복구 시킬 방법이 없으므로 사용에 주의해야 한다. 이 명령은 데이터베이스 디렉토리에 존재하던 파일의 수를 알려주는데 일반적으로 실제 존재하는 테이블 수의 3배이다. 이는 하나의 테이블이 인덱스 파일인`.ISD', 실제 데이터를 저장하는 `.ISM' 파일 그리고 테이블의 스키마(구조)를 저장하는 `.frm' 파일로 구성되어 있기 때문이다..
3.22 이후 버전부터 IF EXISTS 키워드가 추가 되었는데 이는 데이터베이스가 존재하지 않은 경우 에러가 발생하는 것을 막기 위해서 이다. 물론 mysqladmin 유틸리티를 이용해서도 데이터베이스를 삭제할 수 있다.
#./mysqladmin drop school
▶ CREATE TABLE : 테이블 생성
문법 : CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [select_statement]
create_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] KEY(index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or CHECK (expr)
type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
or SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
or INT[(length)] [UNSIGNED] [ZEROFILL]
or INTEGER[(length)] [UNSIGNED] [ZEROFILL]
or BIGINT[(length)] [UNSIGNED] [ZEROFILL]
or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
or CHAR(length) [BINARY]
or VARCHAR(length) [BINARY]
or DATE
or TIME
or TIMESTAMP
or DATETIME
or TINYBLOB
or BLOB
or MEDIUMBLOB
or LONGBLOB
or TINYTEXT
or TEXT
or MEDIUMTEXT
or LONGTEXT
or ENUM(value1,value2,value3,...)
or SET(value1,value2,value3,...)
index_col_name:
col_name [(length)]
reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL | MATCH PARTIAL]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
type = [ISAM | MYISAM | HEAP]
or max_rows = #
or min_rows = #
or avg_row_length = #
or comment = "string"
or auto_increment = #
select_statement:
[ | IGNORE | REPLACE] SELECT ... (Some legal select statement)
|
CREATE TABLE 은 현재 사용중인 데이터베이스에 새로운 테이블을 만든다. 만일 데이터베이스가 선택되지 않았거나 같은 이름의 테이블이 존재하면 에러가 발생한다.
MySQL 3.22 이후 버전부터는 테이블 이름을db_name.tbl_name 과 같이 지정해 줄 수 있게 되어 선택된 데이터베이스가 없어도 된다. 또한 3.23 버전부터는 IF NOT EXISTS 키워드를 제공해 이를 사용할 경우 테이블이 존재 하지 않을 경우만 생성하며 이미 존재하더라고 에러가 발생하지 않는다.
각각의 테이블 tbl_name 은 해당 데이터베이스의 디렉토리에 다음과 같은 3가지 파일로 존재한다.
파일명 기능
tbl_name.frm 테이블 구조 정의 파일
tbl_name.ISD 데이터 파일
tbl_name.ISM 인덱스 파일
- AUTO_INCREMENT 이 아닌 숫자형 컬럼이면 0
- TIMESTAMP 타입이 아닌 날짜형 컬럼이면 해당 타입의 제로 값(zero value)
- 문자형 컬럼은 빈 문자열
▶ ALTER TABLE
문법 : ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_spec:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
or MODIFY [COLUMN] create_definition
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX key_name
or RENAME [AS] new_tbl_name
or table_option
|
ALTER TABLE 명령은 만들어진 테이블의 구조를 변경 할 수 있게 해준다. 예를 들어 컬럼을 추가하거나 삭제, 변경 할 수 있고 인덱스를 생성하거나 삭제할 수도 있다
ALTER TABLE 명령은 원래 테이블을 임시 테이블로 복사해 변경사항을 적용하고 새로운 테이블의 이름을 변경하고 원래 테이블을 삭제하게 된다. 이렇게 함으로서 테이블의 구조를 변경하는 동안에 발생하는 업데이트도 실패 없이 이루어 진다. 새로이 변경된 테이블이 준비될 때까지 쓰기와 갱신은 잠시 지연되게 된다.
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
만일 컬럼의 이름은 변경하지 않고 타입만을 바꾸려면 같은 이름을 두 번 연속해서 써주면 된다, 이는 이전이름과 새로운 이름이 같다는 의미이다.
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
MySQL 3.22.16 이후 버전부터는 MODIFY 구문을 지원해 새로운 이름을 지정하지 않고 다음과 같이 컬럼 명을 변경할 수 있게 되었다.
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
일단 테이블 t1 을 생성한다 .
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
테이블 t1 의 이름을 t2로 변경한다.
mysql> ALTER TABLE t1 RENAME t2;
컬럼a를 INTEGER에서 TINYINT NOT NULL로 컬럼 타입만을 바꾸고 CHAR(10) 컬럼b를 CHAR(20)의 c 라는 이름으로 바꿔본다.
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
d 라는 새로운 TIMESTAMP 컬럼을 추가 한다.
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
컬럼 d 에 인덱스를 생성하고 컬럼 a를 기본 키로 만든다.
mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
컬럼 c 를 제거한다.
mysql> ALTER TABLE t2 DROP COLUMN c;
AUTO_INCREMENT 컬럼c를 추가한다. 이때 AUTO_INCREMENT 컬럼은 NOT NULL 이며 인덱스가 생성되어야만 한다는 사실에 주의 하자.
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);
▶ OPTIMIZE TABLE
문법 : OPTIMIZE TABLE tbl_name
OPTIMZE TABLE 은 테이블에서 상당의 자료를 삭제 했거나 VARCHAR, BLOB , TEXT 등과 같은 가변폭 컬럼에 많은 변화가 생겼을 경우 사용된다. 삭제된 자료는 링크된 공간으로 남고 뒤따른 INSERT 명령에 의해 재사용되므로 저장공간은 불연속 적으로 된다. OPTIMIZE TABLE 명령은 사용되지 않은 공간을 반환하게 한다.
OPTIMIZE TABLE 은 원본 테이블의 복사본을 만들어 작업을 하게 된다. 이전 테이블이 사용되지 않은 공간을 정리해 새로운 테이블이 만들어지면 이전 테이블은 삭제되고 임시 테이블의 이름이 삭제된 테이블과 같이 변경된다. 이런 방식으로 인해 모든 업데이트는 성공적으로 수행되며 OPTIMIZE TABLE 명령이 실행되고 있는 도중에도 원래의 테이블은 읽기가 가능하며 업데이트 및 데이터의 쓰기는 새로 만들어진 테이블이 준비될 때까지 잠시 지연 된다.
▶ DROP TABLE
문법 : DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]
DROP TABLE 은 하나 이상의 테이블을 삭제한다. 테이블 내의 모든 데이터와 테이블의 구조에 대한 정의가 순식간에 사라지니 사용에 주의해야 한다. MySQL 3.22 이후 버전부터 IF EXISTS 키워드가 추가 되어 테이블이 존재 하지 않은 경우 생기는 오류를 막을 수 있게 되었다.
▶ DELETE
문법 : DELETE [LOW_PRIORITY] FROM tbl_name [WHERE where_definition] [LIMIT rows]
DELETE 는 주어진 조건에 맞는 행을 삭제한 후 삭제 된 행의 수를 리턴한다.
만일 WHERE 조건 절을 생략하면 모든 행이 삭제 된다. 이 경우 MySQL 은 먼저 같은 이름의 빈 테이블을 새롭게 만드는데 이 방법이 각각의 행을 지우는 것보다 빠르기 때문이다. 이때 지워진 행의 수는 0 을 리턴한다.
만일 시간이 좀더 걸리더라도 몇 개의 행이 삭제 되었는지 알고 싶다면 WHERE 절을 생략하지 말고 다음과 같이 항상 참인 내용을 적어 주면 된다.
mysql> DELETE FROM tbl_name WHERE 1>0;
이 경우 당연히 WHERE 절을 생략한 채 모두 지우는 것보다 훨씬 느리게 된다.
만일 LOW_PRIORITY 키워드를 사용하면 모든 사용자가 해당 테이블의 사용을 끝낼 때까지 DELETE 의 실행이 지연된다.
지워진 데이터 영역은 새롭게 추가 되는 데이터들에 의해 불연속적으로 채워지게 되므로 OPTIMIZE TABLE 명령과 isamchk 유틸리티를 이용하면 속도가 향상된다. OPTIMIZE TABLE 은 사용이 쉬운 반면 isamchk 유틸리티는 복구 속도가 빠르다 .
MySQL에서만 사용이 가능한 LIMIT 키워드를 사용해 한번에 삭제할 행의 수를 지정해 줄수 있다. 이는 얼마나 많은 자료가 삭제될지 예측할 수 없는 경우 시간절약을 위해 사용될 수 있다. 물론 LIMIT를 반복해서 사용하면 조건에 맞는 모든 자료를 삭제 할 수 있다.
▶SELECT
문법 : SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [DISTINCT | ALL]
select_expression,...
[INTO OUTFILE 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name,...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name] ]
|
SELECT 는 하나 이상의 테이블에서 데이터를 가져올 때 사용 된다.
select_expression 은 데이터를 가져올 컬럼을 나타낸다. SELECT 문을 테이블의 컬럼이 아닌 단순한 연산이나 함수의 결과를 나타낼 때에도 사용된다. 아래의 예를 보자.
mysql> SELECT 1 + 1; -> 2 |
모든 키워드는 위의 사용법에 나온 순서대로 나와야 한다. 예를 들어 HAVING 절은 반드시 GROUP BY 뒤와ORDER BY 사이에 나와야만 한다.
mysql> select concat(last_name,', ',first_name) AS full_name from mytable ORDER BY full_name;
mysql> select college, region, seed from tournament
ORDER BY region, seed;
mysql> select college, region AS r, seed AS s from tournament
ORDER BY r, s;
mysql> select college, region, seed from tournament
ORDER BY 2, 3;
|
내림차순으로 정렬하기 위해서는 DESC 키워드를 사용하다. 오름차순으로 정렬하기 위해서는 ASC 키워드를 사용하는데 디폴트로 되어 있으므로 생략이 가능하다.
mysql> select col_name from tbl_name HAVING col_name > 0;
다음과 같이 써야만 한다.
mysql> select col_name from tbl_name WHERE col_name > 0;
MySQL 3.22.5 이후 버전에서는 다음과 같이 쓸 수도 있다.
mysql> select user,max(salary) from users group by user HAVING max(salary)>10;
구 버전인 경우에는 대신 다음과 같이 써야 한다:
mysql> select user,max(salary) AS sum from users group by user HAVING sum>10;
mysql> select * from table LIMIT 5,10; # 6-15 행까지 If one argument is given, it indicates the maximum number of rows to return. mysql> select * from table LIMIT 5; # 처음 5 행 |
SELECT ... INTO OUTFILE 'file_name' 는 가져온 데이터를 파일에 저장한다, 이때 파일은 서버에 만들어 지며 같은 이름의 파일이 이미 존재해서는 안된다. 또한 사용자는 반드시 file 권한을 가지고 있어야만 한다 .
▶ JOIN
MySQL 은 SELECT 문 내에서 아래와 같은 JOIN 문을 지원한다.
문법 : table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr
table_reference LEFT [OUTER] JOIN table_reference USING (column_list)
table_reference NATURAL LEFT [OUTER] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
마지막의 LEFT OUTER JOIN 구문은 ODBC 호환을 위해존재 한다.
mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
mysql> select table1.* from table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id is NULL; 위의 예는 table1 에 있는 자료 중에 id 값이 table2 에는 존재하지 않는 자료를 모두 가져온다.
A LEFT JOIN B USING (C1,C2,C3,...)
이 문장은 다음과 같이 쓸 수도 있다.
A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...
그 밖에 몇가지 예를 살펴보면 아래와 같다.
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;
▶ INSERT
문법 : 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, ...
INSERT 테이블에 새로운 행을 삽입한다.
The INSERT ... SELECT 구문은 다른 테이블에서 SELECT 된 데이터를 입력할 때 사용된다. tbl_name 은 행이 삽입될 테이블 명이다. 컬럼명이나 SET 절은 데이터가 삽입될 컬럼을 지정해 준다.
mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
그러나 다음의 경우는 오류가 발생한다. 반드시 앞쪽에 나온 값만을 참조 할수 있다는 것을 주의하기 바란다.
mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
DELAYED 옵션은 INSERT 문의 실행을 기다릴 수 없는 사용자가 있을 경우에 매우 유용하다. 이 경우 만일 SELECT 문을 사용하는 사용자가 있을 경우 우선권을 주어 먼저 실행시키고 나서 INSERT 문의 실행을 처리 하게 된다. 이것은 ANSI SQL92 에는 없는 MySQL 의 확장 기능이다.
DELAYED 옵션의 또 다른 장점은 많은 사용자들에 의해 INSERT가 일어날 때 이들에 의해 삽입될 데이터를 모아서 한꺼번에 쓰기 작업을 한다는 것이다. 이 경우 각각의 데이터를 삽입할 때 보다 작업 속도가 빠르다.
▶ REPLACE
문법 : REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] VALUES (expression,...) or REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] SELECT ... or REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name=expression, col_name=expression,... REPLACE 명령은 INSERT 명령과 유사하게 동작하나 한가지 차이점은 인덱스 컬럼에 기존의 값과 같은 행이 입력될 경우 기존의 행을 삭제하고 새로운 행의 입력이 이루어진다.
▶ LOAD DATA INFILE
문법 : LOAD DATA [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,...)] LOAD DATA INFILE 명령은 텍스트 파일에서 데이터를 읽어 매우 빠른 속도로 테이블에 입력해 준다. LOCAL 키워드를 사용할 경우 클라이언트의 호스트에 존재하는 파일을 사용하고 그렇지 않은 경우 MySQL이 구동중인 서버에 존재하는 파일이 사용된다. 서버에 존재하는 파일을 이용 LOAD DATA INFILE 명령을 실행하기 위해서는 file 권한을 가지고 있어야 하며 보안상의 이유로 서버에 존재하는 파일을 읽을 경우 해당 데이베이스의 디렉토리에 존재하거나 아무나 읽을 수 있는 퍼미션을 가지고 있어야 한다.
서버에 존재하는 파일을 이용하기 위해 다음의 규칙이 있다.
REPLACE 및 IGNORE 키워드는 인덱스 컬럼에 입력되는 데이터 행을 제어한다. 만일 REPLACE 를 사용할 경우 기존의 행은 삭제 되고 새로운 값이 입력되며,IGNORE 를 사용 할경우 새로운 값은 무시되고 기존의 값이 보존된다. 만일 키워드가 없으면 오루가 발생한다.
FIELDS 키워드를 사용할 경우 TERMINATED BY, [OPTIONALLY] ENCLOSED BY , ESCAPED BY 선택 사항 중 반드시 한가지 이상 사용해야 하며 만일 FIELDS 키워드를 사용하지 않을 경우 기본값은 다음 명령을 내린 것과 같다.
FIELDS TERMINATED BY '\t' ENCLOSED BY " ESCAPED BY '\\'
LINES 키워드를 사용하지 않을 경우 기본값은 다음 명령을 내린 것과 같다.
LINES TERMINATED BY '\n'
다시 말해 LOAD DATA INFILE 명령의 기본 값은 다음과 같다
문법 : UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1,col_name2=expr2,... WHERE where_definition] UPDATE 명령은 테이블내의 각 컬럼의 값을 새로운 값으로 갱신한다. SET 구문은 변경될 컬럼과 새로운 값을 지정해 준다. 만일 WHERE 구문이 주어질 경우 조건을 만족시키는 행만 갱신되며 생략되면 모든 행이 갱신된다.
LOW_PRIORITY 옵션이 사용되면 다른 모든 클라이언트가 해당 테이블에 대한 읽기 작업이 끝날 때까지 UPDATE 의 실행이 지연된다.
SET 구문에서 테이블의 컬럼명을 사용할 경우 해당 컬럼에 저장되어 있는 값이 사용된다 아래의 age 컬럼을 현재의 값보다 1만큼 증가 시키는 예이다.
mysql> UPDATE persondata SET age=age+1;
UPDATE 문은 각각의 연산을 왼쪽에서 오른쪽으로 실행한다. 아래의 age 컬럼을 현재 값의 2배 한 후 1만큼 증가 시키는 예이다.
mysql> UPDATE persondata SET age=age*2, age=age+1;
잠깐!: 만일 현재 값과 같은 값으로 갱신한다면 MySQL 이를 무시하고 실행하지 않는다.
▶ USE
문법 : USE db_name
USE 명령은 사용할 데이터베이스를 변경한다.
mysql> USE db1;
mysql> SELECT count(*) FROM mytable; # selects from db1.mytable
mysql> USE db2;
mysql> SELECT count(*) FROM mytable; # selects from db2.mytable
USE 명령으로 특정 데이터베이스를 사용하더라도 데이터베이스이름을 지정해 주면 다른 데이터베이스에 존재하는 테이블을 사용할 수 있다.
아래 예는 데이터베이스 db1 에 존재하는 author 테이블과 db2 데이터베이스 내의 editor 테이블을 사용하는 것을 보여준다.
mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor WHERE author.editor_id = db2.editor.editor_id; USE 명령은 Sybase 와의 호환을 위해 제공된다.
▶ FLUSH
문법 : FLUSH flush_option [,flush_option]
MySQL 에 의해 사용되는 내부 캐시를 지우고 새로 읽어 들이기 위해 사용되는 명령이다. 이 명령을 사용하기 위해서는 reload 권한을 가지고 있어야 한다.
flush_option 에는 아래와 같은 옵션이 있다.
| 옵션 | 설명 |
| HOSTS | host 테이블을 다시 읽어 들인다. 접속하려는 호스트의 IP 가 변경 되었거나 새로운 호스트를 추가 했을 경우 그리고 "Host ... is blocked" 에러 메시지나 나타나면 host 테이블을 다시 읽어 들여야만 한다. 최대 접속 가능 수보다 더 많은 호스트들이 접속을 시도할 경우 에러(max_connect_errors)가 발생하고 이 때 MySQL 은 뭔가 잘 못되었음을 판단하고 host 테이블에 등록된 호스트조차 더 이상의 접속을 거부하게 된다. 이러한 에러를 방지하기 위해서는 아래 같이 매우 큰 값을 지정해서 데몬을 띄우면 된다. shell> mysqld -O max_connection_errors=999999999 |
| LOGS | 로그파일을 비우고 새롭게 생성한다. 만일 로그파일 이름을 확장자 없이 지정해 주면 자동적으로 이전 로그파일의 이름에 1 이 증가된 파일명이 생긴다. |
| PRIVILEGES | mysql 데이터베이스내의 권한 관리 테이블을 다시 읽어 들인다. |
| TABLES | 열려진 모든 테이블을 닫는다. |
| STATUS | 모든 상태 변수(status variables)를 0으로 만든다 |
아래와 같이 mysqladmin 유틸리티를 이용해도 같은 작업을 할 수 있다.
shell> mysqladmin flush-hosts, flush-logs, reload , flush-tables
▶ KILL
문법 : KILL thread_id
mysqld 에 의해 각각의 접속은 독립적인 쓰레드로 운영된다. SHOW PROCESSLIST 명령에 의해 동작중인 쓰레드를 볼 수 있고 KILL thread_id 명령에 의해 쓰레드를 죽일 수 있다. PROCESS 권한을 가지고 있을 경우 모든 쓰레드를 볼 수 있고 그렇지 않으면 자신의 쓰레드만을 볼 수 있다.
아래와 같은 방법으로 쓰레드를 점검하고 죽일 수 있다.
shell >mysqladmin processlist 와 shell > mysqladmin kill thread_id
▶ SHOW
테이블 , 컬럼 및 기타 정보를 보여준다.
문법 : SHOW DATABASES [LIKE wild] or SHOW TABLES [FROM db_name] [LIKE wild] or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild] or SHOW INDEX FROM tbl_name [FROM db_name] or SHOW STATUS or SHOW VARIABLES [LIKE wild] or SHOW PROCESSLIST or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
SHOW 명령은 데이터베이스, 테이블 ,컬럼 , MySQL서버 에 대한 정보를 보여 준다. LIKE 문을 사용하면 `%' 와 `_' 와일드 카드 문자를 사용할 수 있다
db_name.tbl_name 을 tbl_name FROM db_name 과 같은 형식으로 사용 할 수 있다. 따라서 아래 두 문장은 같은 기능을 한다.
mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;
SHOW DATABASES - MySQL 서버 호스트에 존재하는 데이터베이스 목록을 보여준다. 쉘 프롬프트 상에서의mysqlshow 명령도 같은 기능을 한다.
SHOW TABLES - 선택된 데이터베이스 내에 존재하는 테이블을 보여준다. 쉘 프롬프트 상에서의 mysqlshow db_name 과 같다.
주의: 만일 테이블에 대한 권한이 아무것도 없다면 결과가 나타나지 않는다.
SHOW COLUMNS 테이블에 있는 컬럼 정보를 보여준다. DESCRIBE 명령도 비슷한 역할 을 한다.
SHOW TABLE STATUS - 3.23 버전에서 추가된 기능이다. SHOW STATUS 와 비슷한 기능을 하지만 각각의 테이블에 대한 상세한 정보를 얻을 수 있다. 쉘 프롬프트 상에서 mysqlshow --status db_name 명령을 통해서도 같은 결과를 얻을 수 있다.
아래와 같은 정보가 보여진다.
| 항 목 | 설 명 |
| Name | 테이블 이름 |
| Type | 테이블 타입 (NISAM, MYISAM, HEAP) |
| Rows | 열의 수 |
| Avg_row_length | 열의 평균 길이 |
| Data_length | 데이터 파일의 크기 |
| Max_data_length | 데이터 파일의 최대 크기 |
| Index_length | 인덱스 파일의 크기 |
| Data_free | 데이터 파일에 할당되었지만 사용되지 않은 공간의 크기 |
| Auto_increment | Auto_increment 컬럼의 다음 값 |
| Create_time | 테이블의 생성 시간 |
| Update_time | 테이블의 마지막 수정 시간 |
| Check_time | 테이블을 마지막 점검한 시간 |
| Create_min_rows | 테이블 생성시 "min_rows" 옵션 사용 여부 |
| Create_max_rows | 테이블 생성시 "max_rows" 옵션 사용 여부 |
| Create_avg_row_length | 테이블 생성시 "avg_row_length" 옵션 사용 여부 |
| Comment | 테이블 생성시 입력한 주석 |
SHOW FIELDS - SHOW COLUMNS 또는 SHOW KEYS 와 같다 테이블의 컬럼이나 인덱스 정보를 보여준다.
SHOW INDEX - 아래와 같은 인덱스 정보를 보여준다
| 항 목 | 설 명 |
| Table | 테이블 이름 |
| Non_unique | 인덱스에 동일한 값이 저장되지 않는다면 0 |
| Key_name | 인덱스 이름 |
| Seq_in_index | 인덱스 내의 몇 번째 컬럼 인가 1부터 시작. |
| Column_name | 컬럼 이름 |
| Collation | 인덱스가 어떻게 저장되나. A (오름차순 정렬) , NULL (정렬안됨). |
| Cardinality | 인덱스 내에 존재하는 유일한 데이터의 수 |
| Sub_part | 컬럼 일부만을 인덱스하는 경우 이 크기. 만일 컬럼 전체가 사용되면 NULL |
SHOW STATUS - 서버의 상태에 대한 정보를 보여준다. 쉡 프롬프트 상태에서의 mysqladmin extended-status 명령과 같다. 결과 값은 아래와 비슷할 것이다.
+--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Created_tmp_tables | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 2 | | Handler_delete | 2 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 0 | | Handler_read_rnd | 35 | | Handler_update | 0 | | Handler_write | 2 | | Key_blocks_used | 0 | | Key_read_requests | 0 | | Key_reads | 0 | | Key_write_requests | 0 | | Key_writes | 0 | | Max_used_connections | 1 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 1 | | Open_files | 2 | | Open_streams | 0 | | Opened_tables | 11 | | Questions | 14 | | Running_threads | 1 | | Slow_queries | 0 | | Uptime | 149111 | +--------------------------+--------+
각각의 항목은 다음과 같은 정보를 나타낸다.
| Aborted_clients | 클라이언트가 정상적인 종료를 하지 못하고 끊어진 접속 수 |
| Aborted_connects | MySQL 서버네 접속을 시도했지만 실패한 접속 수 |
| Created_tmp_tables | SQL 문을 실행하는 동안 생성된 임시 테이블 수 |
| Delayed_insert_threads | 사용중인 지연INSER 문(delayed insert handler) 수 |
| Delayed_writes | INSERT DELAYED에 의해 쓰여진 행수. |
| Delayed_errors | INSERT DELAYED에 의해 오류가 발생한 행수 |
| Flush_commands | FLUSH 명령 실행 횟수 |
| Handler_delete | 테이블내행 삭제명령 수 |
| Handler_read_first | 테이블 우선 읽기 명령의 수 |
| Handler_read_key | 인덱스 기반 테이블 읽기 명령 수 |
| Handler_read_next | 인덱스 정렬 순서에 따라 실행된 다음 행 읽기 명령 수 |
| Handler_read_rnd | 고정폭 테이블에서 실행된 행 읽기 명령 수 |
| Handler_update | 테이블 UPDATE 명령 실행 수 |
| Handler_write | 테이블 INSERT 명령 실행 수 |
| Key_blocks_used | 인덱스 캐시에 의해 사용된 블록 수 |
| Key_read_requests | 인덱스 캐시에 의해 처리된 명령 수 |
| Key_reads | 디스크의 물리적 읽기 횟수 |
| Key_write_requests | 블록이 인덱스 캐시에 저장된 명령 수 |
| Key_writes | 블록이 물리적 디스크에 저장된 명령 수 |
| Max_used_connections | 처리 가능한 최대 동시 접속 수 |
| Not_flushed_key_blocks | 인덱스 캐시에 내용이 변경되었지만 아직 디스크에 저장되지 않은 블록 수 |
| Not_flushed_delayed_rows | INSERT DELAY 에 의해 처리 지연 중인 행 수 |
| Open_tables | 현재 사용을 위해 열려진 테이블 수 |
| Open_files | 열려진 파일 수 |
| Open_streams | 로그인을 위해 연결된 접속 수 |
| Opened_tables | 지금까지 열려진 테이블 수 |
| Questions | 서버에 의해 요청된 질문 수 |
| Running_threads | 현재 연결중인 접속(쓰레드) 수 |
| Slow_queries | long_query_time 에 정의 된 시간 보다 실행시간이 초과된 요청 수 |
| Uptime | 서버 기동 후 경과 시간 |
SHOW VARIABLES
MySQL 의 시스템 변수를 출력한다. 시스템 프롬프트 상태에서 mysqladmin variables 명령을 통해서도 마찬가지 내용을 볼 수 있다. 일부 변수의 디폴트 값을 변경하려면 mysqld 를 명령행 옵션을 주어서 실행 하면 된다.
실행결과는 다음과 비슷할 것이다.
+------------------------+--------------------------+ | Variable_name | Value | +------------------------+--------------------------+ | back_log | 5 | | connect_timeout | 5 | | basedir | /my/monty/ | | datadir | /my/monty/data/ | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | join_buffer_size | 131072 | | flush_time | 0 | | key_buffer_size | 1048540 | | language | /my/monty/share/english/ | | log | OFF | | log_update | OFF | | long_query_time | 10 | | low_priority_updates | OFF | | max_allowed_packet | 1048576 | | max_connections | 100 | | max_connect_errors | 10 | | max_delayed_threads | 20 | | max_heap_table_size | 16777216 | | max_join_size | 4294967295 | | max_sort_length | 1024 | | max_tmp_tables | 32 | | net_buffer_length | 16384 | | port | 3306 | | protocol-version | 10 | | record_buffer | 131072 | | skip_locking | ON | | socket | /tmp/mysql.sock | | sort_buffer | 2097116 | | table_cache | 64 | | thread_stack | 131072 | | tmp_table_size | 1048576 | | tmpdir | /machine/tmp/ | | version | 3.23.0-alpha-debug | | wait_timeout | 28800 | +------------------------+--------------------------+
다음 장 MySQL관리와 튜닝에서 각각의 변수에 대한 설명과 튜닝방법을 다룬다.
SHOW PROCESSLIST
현재 접속중인 쓰레드를 보여준다.
shell>mysqladmin processlist 명령을 이용해도 같은 정보를 얻을 수 있다. 기본적으로 자신의 쓰레드 정보만을 볼 수 있고 PROCESS 권한을 가지고 있을 경우 모든 쓰레드 정보를 보여준다. 이 경우 KILL 명령을 이용 쓰레드를 죽일 수 있다.
▶ LOCK TABLES/UNLOCK TABLES
문법 : LOCK TABLES tbl_name [AS alias] READ | [LOW_PRIORITY] WRITE [, tbl_name READ | [LOW_PRIORITY] WRITE ...] ... UNLOCK TABLES LOCK TABLES 명령은 현재 쓰레드 위해 테이블에 락(lock)을 건다. 쓰레드란 데이터베이스에 연결되는 접속 단위로 생각하면 된다. UNLOCK TABLES은 현재 쓰레드에 의해 걸린 모든 락을 푼다. 하나의 쓰레드에 의해 락이 걸린 모든 테이블은 이 쓰레드가 다른 테이블에 락을 걸 경우나 접속이 끊어질 경우 자동으로 락이 풀린다.
만일 한 쓰레드가 어떤 테이블에 읽기(READ ) 락을 걸면 그 쓰레드의 사용자만이 그 테이블을 읽을 수 있다.
또한 한 쓰레드가 쓰기(WRITE) 락을 걸 경우 해당 쓰레드의 사용자만이 테이블을 읽고 쓸 수 있게 된다. 다른 쓰레드들은 그 테이블에 걸린 락이 풀릴 때가지 대기하게 된다.
일반적으로 쓰기 락이 읽기 락보다 운선 순위가 높은데 이는 가능하면 업데이트가 빨리 진행 되도록 하기 위해서이다. 다시 말해 한 쓰레드가 테이블에 읽기 락을 건 상태에서 다른 쓰레드가 테이블에 쓰기 락을 걸면 이전에 읽기 락을 건 쓰레드는 쓰기 락이 해제 될 때가지 기다리게 된다.
일반적으로 하나의 테이블 만을 업데이트 할 경우에는 락을 걸 필요가 없다. 왜냐하면 하나의 쓰레드에서 SQL 문이 실행 중일 경우 다른 쓰레드는 이 쓰레드를 방해할 수 없기 때문이다. 드러나 때때로 락이 필요한 경우도 있다.
만일 수많은 테이블에서 일련의 연속된 작업을 해야 하는 경우 사용하려는 테이블에 락을 경우 더 빨라질 수 있다. MySQL 은 트랜잭션(transaction) 기능을 제공하지 않으므로 만일 SELECT 와 UPDATE 사이에 다른 쓰레드가 끼어 들지 못하도록 하려면LOCK TABLES 명령을 사용해야만 한다.
이러한 일련을 작업을 안전하게 처리하는 예가 아래에 있다.
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select sum(value) from trans where customer_id= some_id;
mysql> update customer set total_value=sum_from_previous_statement
where customer_id=some_id;
mysql> UNLOCK TABLES;이 경우 만일 LOCK TABLES 명령을 사용하지 않은 경우 SELECT 와 UPDATE 명령 사이에 다른 사용자의 쓰레드가 끼어들 수 있다.
▶ GRANT / REVOKE
문법 :
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON tbl_name | * | *.* | db_name.*
TO user_name [IDENTIFIED BY 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[WITH GRANT OPTION]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON tbl_name | * | *.* | db_name.*
FROM user_name [, user_name ...]
GRANT 와 REVOKE 명령은 시스템 관리자가 데이터베이스 사용자에게 다음과 같은 4단계의 권한을 부여하거나 취소할 수 있도록 한다.
commands allow system administrators to grant and revoke rights to MySQL users at four privilege levels:
Global 레벨
이 권한은 서버에 있는 모든 데이터베이스에 대한 권한으로 mysql 데이터베이스의 user 테이블에 저장된다.
Database 레벨
특정 데이터베이스에 존재하는 모든 테이블에 대한 권한으로 mysql 데이터베이스의 db,host 테이블에 저장된다.
Table level
특정 테이블에 있는 모든 컬럼에 대한 권한으로 mysql 데이터베이스의 tables_priv 테이블에 저장된다.
Column level
특정 테이블에 있는 하나의 컬럼에 대한 권한으로 mysql 데이터베이스의 columns _priv 테이블에 저장된다.
한 유저가 자신의 권한을 다른 유저에게 부여하는(GRANT) 권한을 빼앗기 위해서는 다음과 같이 한다.
REVOKE GRANT OPTION ON priv FROM user ;
테이블에 대해 사용가능한 권한은 SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, INDEX , ALTER. 등이 있다.
▶ CREATE INDEX
인덱스를 생성한다.
문법 : CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name[(length]),... )
CREATE INDEX 구문은 MySQL 3.22. 이전버전에서는 아무런 동작도 하지 않는다. 3.22 이후 버전에서는 CREATE INDEX 는 인덱스를 생성하는ALTER TABLE 구문과 같은 역할을 한다.
일반적으로 모든 인덱스는 CREATE TABLE 구문을 이용해 테이블 생성시에 만들 수 있지만 CREATE INDEX 구문을 이용하면 이미 존재하는 테이블에 인덱스를 추가할 수 있다. 컬럼의 목록을 괄호 안에 (col1,col2,...) 과 같이 열거함으로써 다중 컬럼 인덱스를 만들 수 있다. 이때 생성되는 인덱스 값은 두 컬럼의 조합으로 만들어 진다.
컬럼의 일부만 인덱스로 생성 CHAR 와 VARCHAR 컬럼에서는 col_name(length) 처럼 length를 지정해 줌으로써 컬럼의 일부분만을 사용할 수 있다.
아래의 예를 name 컬럼의 앞부분 10 자리만을 인덱스로 만드는 것을 보여준다.
mysql> CREATE INDEX part_of_name ON customer (name(10));
이 경우 이름 전체를 인덱스로 만드는 것보다 검색속도는 느려지겠지만 많은 디스크 공간을 절약할 수 있고 INSERT 수행 시 속도가 향상된다.
▶ DROP INDEX
문법 : DROP INDEX index_name
인덱스를 삭제한다. DROP INDEX 구문은 MySQL 3.22. 이전버전에서는 아무런 동작도 하지 않는다. 3.22 이후 버전에서는 DROP INDEX 는 인덱스를 삭제하는ALTER TABLE 구문과 같은 역할을 한다.
'닷컴's_열공 > Database' 카테고리의 다른 글
| SVN(SubVersioN) 서버 설치 및 사용방법 (0) | 2008.12.04 |
|---|---|
| 10g에서 group by 사용시 주의사항 (0) | 2008.07.29 |
| ORACLE - Cursor_sharing (0) | 2008.01.14 |
| 프로시져의 내용을 로그 파일에 남기기, 파일 읽고 쓰기 (0) | 2008.01.14 |
| [oracle강좌] 오라클 / PL-SQL 에서 쿼리시 Array Processing (0) | 2008.01.14 |
