mysql 테이블 분리하기

mysql 테이블 분리하기 

기존에 있던 테이블 이름 바꿔서 백업테이블로 만들기 

mysql 테이블 이름 바꾸기 명령어: rename 

MariaDB [opentutorials]> rename table topic to topic_backup;

Query OK, 0 rows affected (0.02 sec)

다시 새로운 topic 테이블 생성한다. 

MariaDB [opentutorials]> create table topic(

    -> id int(11) not null auto_increment,

    -> title varchar(30) not null,

    -> description text null,

    -> created datetime not null,

    -> author_id int(11) null,

    -> primary key(id));

Query OK, 0 rows affected (0.06 sec)

테이블이 잘 만들어졌는지 확인하기.

MariaDB [opentutorials]> desc topic;

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

| Field       | Type        | Null | Key | Default | Extra          |

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

| id          | int(11)     | NO   | PRI | NULL    | auto_increment |

| title       | varchar(30) | NO   |     | NULL    |                |

| description | text        | YES  |     | NULL    |                |

| created     | datetime    | NO   |     | NULL    |                |

| author_id   | int(11)     | YES  |     | NULL    |                |

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

5 rows in set (0.00 sec)

author 테이블 생성하기 

MariaDB [opentutorials]> create table author(

    -> id int(11) not null auto_increment,

    -> name varchar(30) not null,

    -> profile varchar(100) null,

    -> primary key(id));

Query OK, 0 rows affected (0.06 sec)

MariaDB [opentutorials]> insert into topic(id, title, description, created, author_id) values(1,'my sql','mysql is...',now(),1);

Query OK, 1 row affected (0.01 sec)

MariaDB [opentutorials]> insert into topic(id, title, description, created, author_id) values(2,'oracle','oracle is...',now(),1);

Query OK, 1 row affected (0.01 sec)

MariaDB [opentutorials]> insert into topic(id, title, description, created, author_id) values(3,'sql server','sql server is...',now(),2);

Query OK, 1 row affected (0.00 sec)

MariaDB [opentutorials]> select * from topic;

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

| id | title      | description      | created             | author_id |

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

|  1 | my sql     | mysql is...      | 2018-09-22 00:12:27 |         1 |

|  2 | oracle     | oracle is...     | 2018-09-22 00:13:38 |         1 |

|  3 | sql server | sql server is... | 2018-09-22 00:14:37 |         2 |

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

3 rows in set (0.01 sec)

MariaDB [opentutorials]> insert into author(id,name,profile) values(3,'taeho','data scientist, developer');

Query OK, 1 row affected (0.01 sec)

MariaDB [opentutorials]> insert into topic(id, title, description, created, author_id) values(4,'postgresql','postgresql is...',now(),3);

Query OK, 1 row affected (0.01 sec)

MariaDB [opentutorials]> select * from topic;

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

| id | title      | description      | created             | author_id |

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

|  1 | my sql     | mysql is...      | 2018-09-22 00:12:27 |         1 |

|  2 | oracle     | oracle is...     | 2018-09-22 00:13:38 |         1 |

|  3 | sql server | sql server is... | 2018-09-22 00:14:37 |         2 |

|  4 | postgresql | postgresql is... | 2018-09-22 00:17:48 |         3 |

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

4 rows in set (0.00 sec)

MariaDB [opentutorials]> insert into topic(id, title, description, created, author_id) values(5,'mongodb','mongodb is...',now(),1);

Query OK, 1 row affected (0.02 sec)

MariaDB [opentutorials]> select * from author;

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

| id | name  | profile                   |

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

|  3 | taeho | data scientist, developer |

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

1 row in set (0.00 sec)

MariaDB [opentutorials]> insert into author(id,name,profile) values(1,'egoing','developer');

Query OK, 1 row affected (0.01 sec)

MariaDB [opentutorials]> insert into author(id,name,profile) values(2,'duru','data administrator');

Query OK, 1 row affected (0.01 sec)

MariaDB [opentutorials]> select * from author;

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

| id | name   | profile                   |

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

|  1 | egoing | developer                 |

|  2 | duru   | data administrator        |

|  3 | taeho  | data scientist, developer |

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

3 rows in set (0.00 sec)

MariaDB [opentutorials]> select * from topic;

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

| id | title      | description      | created             | author_id |

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

|  1 | my sql     | mysql is...      | 2018-09-22 00:12:27 |         1 |

|  2 | oracle     | oracle is...     | 2018-09-22 00:13:38 |         1 |

|  3 | sql server | sql server is... | 2018-09-22 00:14:37 |         2 |

|  4 | postgresql | postgresql is... | 2018-09-22 00:17:48 |         3 |

|  5 | mongodb    | mongodb is...    | 2018-09-22 00:18:51 |         1 |

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

5 rows in set (0.00 sec)

MariaDB [opentutorials]>