본문 바로가기
DataBase/MySQL

MySQL CREATE의 종류와 사용법

by Dev. Jkun 2011. 9. 21.
반응형

1. CREATE DATABASE


 CREATE DATABASE는 주어진 이름으로 database를 만든다. database 생성 권한이 있어야 하며, 동일한 이름이 존재하고 IF NOT EXISTS 조건을 걸지 않으면 에러가 발생한다.

아래의 간단한 예를 보자!

먼저 현재 생성되어 있는 DATABASE들의 목록은 SHOW DATABASES 명령으로 확인 할 수 있다.

mysql> show databases;
+--------------------+
| Database                |
+--------------------+
| information_schema |
| mysql                     |
| nforge                     |
| test                         |
+--------------------+
4 rows in set (0.00 sec)
현재 4개의 database가 존재하고 있음을 알 수 있다.
이때 동일한 이름의 database를 생성하고자 한다면 아래와 같이 에러가 발생한다.

mysql> create database test;
ERROR 1007 (HY000): Can't create database 'test'; database exists
이럴경우 에러메시지를 발생시키지 않고자 한다면 IF NOT EXISTS를 사용하면 된다.

mysql> create database if not exists test;
Query OK, 1 row affected, 1 warning (0.00 sec)
에러가 발생하지는 않았지만, warning이 1개 있음을 알 수 있다.

정상적으로 생성하고 다시 show를 통해 생성된 database를 확인 해 보자!

mysql> create database if not exists test2;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database                |
+--------------------+
| information_schema |
| mysql                     |
| nforge                     |
| test                         |
| test2                       |
+--------------------+
5 rows in set (0.00 sec)
에러나 워닝없이 정상적으로 실행되었으며, database의 목록이 추가 된 것을 알 수 있다.


2. CREATE EVENT

 아래는 이벤트를 생성하고 스케쥴하는 문장이다. 이벤트는 이벤트 스케쥴이 enabled되기전까지 작동하지 않는다. 이벤트 스케쥴에 대한 정보는 다음의 링크를 참고하기 바란다.(Section 19.4.2, “Event Scheduler Configuration”)

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
이벤트를 생성하기 위해서는 EVENT 권한 이 있어야 한다. 

아래는 간단한 EVENT생성 예이다.
CREATE EVENT myevent 
 ON SCHEDULE 
    AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR 
 DO 
    UPDATE myschema.mytable SET mycol = mycol + 1;
CREATE EVENT 문장 다음에 유일한 이름의 event이름을 적어 주고,
ON SCHEDULE에는 언제, 얼마나 자주 이벤트가 실행될 것인지를 결정한다.
DO에는 실행될 SQL문장을 적으면 된다.
이것이 최소한의 CREATE EVENT문장이다.

위의 문장은 myevent라는 이벤트를 생성하고 생성된 1시간 후 한번 실행되며, 실행되는 내용은 myschema.mytable의 mycol 컬럼의 값을 1증가 시킨다.

이벤트 이름은 최대 64글자 이내 이어야 하고, 대소문자 구분을 하지 않으므로 myevent와 MyEvent같은 이름으로 이벤트가 함께 존재 할 수 없다.

AT대신 EVERY를 사용해 반복 설정이 가능하며, event_scheduler가 ON인 상태에서 작동한다.
이벤트에 대한 자세한 내용은 다른 글에서 소개 하고자 한다.


 CREATE INDEX는 이미 존재하는 Table에 index들을 생성하는데 사용되며, PRIMARY KEY를 생성하는데 사용될 수는 없다. 이때는 대신 ALTER TABLE을 사용한다.

형식은 아래와 같다.
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]
index_type: USING {BTREE | HASH}

index_option: KEY_BLOCK_SIZE [=] value
    | index_type
    | WITH PARSER parser_name
    | COMMENT 'string'

인덱스에 대한 내용는 다른 글에서 상세히 다루도록 하겠다.


4. CREATE PROCEDURE and CREATE FUNCTION

 Procedure와 Function에 대한 내용도 다른 글에서 다룰 것이며, 여기에서는 생성에 대한 형식과 예를 통해 생성하는 방법만을 설명하고자 한다.

형식은 아래와 같다.
CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement
 위 형식에서 주의 할 것은 파라미터의 형식이 PROCEDURE에서는 IN, OUT, INOUT 모두 가능하지만, FUNCTION의 경우는 항상 IN으로만 간주된다는 것이다.

간단한 예를 보면서 생성법과 사용법을 보도록 하자.

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3      |
+------+
1 row in set (0.00 sec)
간단한 PROCEDURE를 만드는 예제이다.
테이블 t의 레코드수를 param1이라는 OUT 파라미터로 넘기는 예제이다.
SELECT문에서 INTO를 사용해서 값을 넣는 것을 볼 수 있다.
그리고 호출시(CALL simpleproc(@a)) @a라는 변수로 전달하여 값을 출력 하고 있다.

만들어진 PROCEDURE는 SHOW CREATE PROCEDURE로 확인 할 수 있다.
mysql> SHOW CREATE PROCEDURE simpleproc\G
*************************** 1. row ***************************
           Procedure: simpleproc
            sql_mode: 
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc`(OUT param1 INT)
BEGIN
  SELECT COUNT(*) INTO param1 FROM t;
END
1 row in set (0.00 sec)

다음은 CREATE FUNCTION에 대한 예를 보자.
mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world')    |
+----------------+
| Hello, world!      |
+----------------+
1 row in set (0.00 sec)
간단히 설명하면 입력받은 문자 앞에 'Hello, '를 뒤쪽에 '!'를 붙여서 반환하는 함수 이다.
쉽게 이해가 될 것이다.

이 또한 SHOW CREATE FUNCTION를 이용해 생성된 것을 확인 할 수 있다.
mysql> SHOW CREATE FUNCTION hello\G
*************************** 1. row ***************************
            Function: hello
            sql_mode: 
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(s CHAR(20)) RETURNS char(50) CHARSET latin1
    DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!')
1 row in set (0.00 sec)


5. CREATE TABLE

 CREATE TABLE에 대한 가장 간단한 형식은 아래와 같다.
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
OR
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement

두 형식의 차이는 아래의 마지막 부분이다. select_statement 즉, 다른 테이블의 필드와 값을 추가 할 수 있다는 것이다.

아래 예들을 보면 이해가 쉬울 것이다.
mysql> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| idx   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> create table test2(idx int(11)) select idx from test;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| test            |
| test2           |
+-----------------+
2 rows in set (0.00 sec)

mysql> desc test2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| idx   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> create table test3(t_idx int(11)) select idx from test;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| t_idx | int(11) | YES  |     | NULL    |       |
| idx   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table test4(t_idx int(11)) select idx AS test_idx from test;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test4;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| t_idx    | int(11) | YES  |     | NULL    |       |
| test_idx | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 Table의 생성은 Database의 가장 기본적이면서 가장 중요한 작업이라 할 수 있다. 그래서 굉장히 많은 옵션과 생성 방법이 존재 한다. 이 글에서 모두를 다 다루는 것은 어려울 것 같아 다른 글을 통해 소개 하고자 한다.


6. CREATE TRIGGER




7. CREATE VIEW

반응형

댓글