🗃️나의 SQL : 관계형 데이터의 시작-테이블 분리하기

---

우리가 관계형 데이터를 보여주기 위해서는 TABLE을 분리해야한다. 그래서 기존의 TABLE의 명칭을 변경해서 새로 저장할 것이다. 아래 코드를 살펴보자.

RENAME TABLE topic TO topic_backup;

MariaDB [opentutorials]> RENAME TABLE topic TO topic_backup;
Query OK, 0 rows affected (0.018 sec)

MariaDB [opentutorials]> SHOW TABLES;
+-------------------------+
| Tables_in_opentutorials |
+-------------------------+
| topic_backup            |
+-------------------------+
1 row in set (0.001 sec)

 

이후 우리는 전에 사용했던 topic이란 TABLE을 새로운 형태로 생성할 것이다. 아래 코드를 살펴보며 기존 TABLE을 비교해보자.

CREATE TABLE topic(-);

MariaDB [opentutorials]> CREATE TABLE topic (
    ->   id int(11) NOT NULL AUTO_INCREMENT,
    ->   title varchar(30) NOT NULL,
    ->   description text,
    ->   created datetime NOT NULL,
    ->   author_id int(11) DEFAULT NULL,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.017 sec)

 

topic_backup TABLE 과 topic TABLE 비교

MariaDB [opentutorials]> DESC topic_backup;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| title       | varchar(100) | NO   |     | NULL    |                |
| description | text         | YES  |     | NULL    |                |
| created     | datetime     | NO   |     | NULL    |                |
| author      | varchar(30)  | YES  |     | NULL    |                |
| profile     | varchar(100) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.025 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.024 sec)

마지막으로 우리가 topic에서 author_id가 참조할 TABLE을 생성할 것이다. 아래 코드를 살펴보자

 

CREATE TABLE author(-);

MariaDB [opentutorials]> CREATE TABLE author (
    ->   id int(11) NOT NULL AUTO_INCREMENT,
    ->   name varchar(20) NOT NULL,
    ->   profile varchar(200) DEFAULT NULL,
    ->   PRIMARY KEY (id)
    -> );
    
 MariaDB [opentutorials]> DESC author;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)  | NO   |     | NULL    |                |
| profile | varchar(200) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.023 sec)

 

왼쪽은 SELECT * FROM author; 오른쪽은 위에서 SELECT * FROM topic; DESC topic;

우리가 이제 데이터를 참조하기 위해서는 위와 같이 인터페이스를 설정해놔야 한다. 

데이터 값

INSERT INTO topic(-) VALUES(-);

MariaDB [opentutorials]> INSERT INTO topic(id, title, description, created, author_id) 
VALUES(1, 'MySQL', 'MySQL is...', '2018-1-1 12:10:11', 1);
Query OK, 1 row affected (0.004 sec)

MariaDB [opentutorials]> INSERT INTO topic(id, title, description, created, author_id) 
VALUES(2, 'Oracle', 'Oracle is...', '2018-1-03 13:01:10', 1);
Query OK, 1 row affected (0.002 sec)

MariaDB [opentutorials]> INSERT INTO topic(id, title, description, created, author_id) 
VALUES(3, 'SQL Server', 'SQL Server is...', '2018-01-20 11:01:10', 2);
Query OK, 1 row affected (0.004 sec)

MariaDB [opentutorials]> INSERT INTO topic(id, title, description, created, author_id) 
VALUES(4, 'PostgreSQL', 'PostgereSQL is...', '2018-01-23 1:3:3', 3);
Query OK, 1 row affected (0.005 sec)

MariaDB [opentutorials]> INSERT INTO topic(id, title, description, created, author_id) 
VALUES(5, 'MongoDB', 'MongoDB is...', '2018-01-30 12:31:3', 1);
Query OK, 1 row affected (0.004 sec)

MariaDB [opentutorials]> SELECT * FROM topic;
+----+------------+-------------------+---------------------+-----------+
| id | title      | description       | created             | author_id |
+----+------------+-------------------+---------------------+-----------+
|  1 | MySQL      | MySQL is...       | 2018-01-01 12:10:11 |         1 |
|  2 | Oracle     | Oracle is...      | 2018-01-03 13:01:10 |         1 |
|  3 | SQL Server | SQL Server is...  | 2018-01-20 11:01:10 |         2 |
|  4 | PostgreSQL | PostgereSQL is... | 2018-01-23 01:03:03 |         3 |
|  5 | MongoDB    | MongoDB is...     | 2018-01-30 12:31:03 |         1 |
+----+------------+-------------------+---------------------+-----------+
5 rows in set (0.001 sec)

INSERT INTO author (-) VALUES(-);

MariaDB [opentutorials]> INSERT INTO author (id, name, profile) 
VALUES(1, 'egoing', 'developer');
Query OK, 1 row affected (0.003 sec)

MariaDB [opentutorials]> INSERT INTO author (id,name,profile) 
VALUES(2, 'duru', 'data administrator');
Query OK, 1 row affected (0.003 sec)

MariaDB [opentutorials]> INSERT INTO author (id,name,profile) 
VALUES(3, 'taeho', 'data scientist, developer');
Query OK, 1 row affected (0.003 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.000 sec)

테이블 분리하기 - 생활코딩