[MySQL] 테이블 분리와 JOIN

[강의 출처] opentutorials.org/course/3161

DATABASE2 - MySQL - 생활코딩

---

관계형 데이터베이스의 쓸모

아래와 같은 데이터가 있다고 할 때,

id	종	과	목	계
1	고양이	고양이과	식육목	동물계
2	장미	장미과	장미목	식물계
3	퓨마	고양이과	식육목	동물계
4	보르네오구름표범	고양이과	식육목	동물계

여기서 '식육목', '동물계'는 '고양이과'의 상위 항목이므로, '고양이과'에 따라오는 데이터이고,

'장미목', '식물계'는 '장미과'에 따라오는 데이터다.

이런 상황에서는 '목' 컬럼의 데이터를 영어 명칭으로 바꿔야 되는 경우, 모든 '목' 데이터를 다 건드려야하므로 대참사가 일어날 수 있다.

따라서 이렇게 따라오는 데이터, 즉 종속 데이터가 있는 경우, 아래처럼 표를 분리하는 편이 바람직하다.

id	종	과_id
1	고양이	1
2	장미	2
3	퓨마	1
4	보르네오구름표범	1

과_id	과	목	계
1	고양이과	식육목	동물계
2	장미과	장미목	식물계

이 경우 '목' 컬럼의 데이터를 변경해야 하는 경우가 있더라도 아래 표에서만 변경하면 되고, 위 표에서는 과_id를 참조해 처리하면 된다.

분리는 이렇게 하면 된다.

 

기존 테이블 생성 구문

mysql> CREATE TABLE gray(id int(11) NOT NULL AUTO_INCREMENT, species VARCHAR(30) NOT NULL
, family VARCHAR(30) NOT NULL, _order VARCHAR(30) NOT NULL, kingdom VARCHAR(30) NOT NULL,
 PRIMARY KEY(id));
 
mysql> INSERT INTO gray (species, family, _order, kingdom) 
VALUES ('cat', 'Felidae', 'Carnivora', 'Animalia'),
('rose', 'Rosaceae', 'Rosales', 'Plantae'),
('puma', 'Felidae', 'Carnivora', 'Animalia'),
('Sunda clouded leopard', 'Felidae', 'Carnivora', 'Animalia');

+----+-----------------------+----------+-----------+----------+
| id | species               | family   | _order    | kingdom  |
+----+-----------------------+----------+-----------+----------+
|  1 | cat                   | Felidae  | Carnivora | Animalia |
|  2 | rose                  | Rosaceae | Rosales   | Plantae  |
|  3 | puma                  | Felidae  | Carnivora | Animalia |
|  4 | Sunda clouded leopard | Felidae  | Carnivora | Animalia |
+----+-----------------------+----------+-----------+----------+

기존 테이블을 분리하는 구문

mysql> CREATE TABLE green1 SELECT id, species, family FROM gray;      -- 위의 표 green1 생성

mysql> CREATE TABLE green2 SELECT id, family, _order, kingdom FROM gray;  -- 아래 표 green2 생성
mysql> DELETE tb1 FROM green2 tb1, green2 tb2              
WHERE tb1.family = tb2.family AND tb1._order = tb2._order
AND tb1.kingdom = tb2.kingdom AND tb1.id < tb2.id;   -- green2 중복 제거  

mysql> UPDATE green1 INNER JOIN green2 ON green1.family = green2.family
SET green1.family = green2.id;               -- green1의 family를 green2의 id값으로 치환

ALTER TABLE green1 CHANGE family familyID INT(11);   -- 헷갈리니까 green1 family컬럼명도 변경

mysql> SELECT * FROM green1;
+----+-----------------------+----------+
| id | species               | familyID |
+----+-----------------------+----------+
|  1 | cat                   |        4 |
|  2 | rose                  |        2 |
|  3 | puma                  |        4 |
|  4 | Sunda clouded leopard |        4 |
+----+-----------------------+----------+

mysql> SELECT * FROM green2;
+----+----------+-----------+----------+
| id | family   | _order    | kingdom  |
+----+----------+-----------+----------+
|  2 | Rosaceae | Rosales   | Plantae  |
|  4 | Felidae  | Carnivora | Animalia |
+----+----------+-----------+----------+

 

표를 분리해둔 상태에서, 맨위의 회색 표처럼 출력하고 싶다면 JOIN문을 이용하면 된다.

 

JOIN

mysql> SELECT * FROM green1 LEFT JOIN green2 ON green1.familyID = green2.id;
+----+-----------------------+----------+------+----------+-----------+----------+
| id | species               | familyID | id   | family   | _order    | kingdom  |
+----+-----------------------+----------+------+----------+-----------+----------+
|  2 | rose                  |        2 |    2 | Rosaceae | Rosales   | Plantae  |
|  1 | cat                   |        4 |    4 | Felidae  | Carnivora | Animalia |
|  3 | puma                  |        4 |    4 | Felidae  | Carnivora | Animalia |
|  4 | Sunda clouded leopard |        4 |    4 | Felidae  | Carnivora | Animalia |
+----+-----------------------+----------+------+----------+-----------+----------+

id가 출력되는 것이 보기 싫다면 직접 원하는 컬럼을 지정해줄 수도 있다.

mysql> SELECT green1.id, green1.species, green2.family, green2._order, green2.kingdom
FROM green1 LEFT JOIN green2 ON green1.familyID = green2.id;
+----+-----------------------+----------+-----------+----------+
| id | species               | family   | _order    | kingdom  |
+----+-----------------------+----------+-----------+----------+
|  2 | rose                  | Rosaceae | Rosales   | Plantae  |
|  1 | cat                   | Felidae  | Carnivora | Animalia |
|  3 | puma                  | Felidae  | Carnivora | Animalia |
|  4 | Sunda clouded leopard | Felidae  | Carnivora | Animalia |
+----+-----------------------+----------+-----------+----------+

출력 컬럼명을 바꾸고 싶다면 AS, 정렬이 필요하다면 ORDER BY를 쓰면 된다.

ysql> SELECT green1.id, green1.species, family, _order AS orders, kingdom
FROM green1 LEFT JOIN green2 ON green1.familyID = green2.id ORDER BY id ASC;
+----+-----------------------+----------+-----------+----------+
| id | species               | family   | orders    | kingdom  |
+----+-----------------------+----------+-----------+----------+
|  1 | cat                   | Felidae  | Carnivora | Animalia |
|  2 | rose                  | Rosaceae | Rosales   | Plantae  |
|  3 | puma                  | Felidae  | Carnivora | Animalia |
|  4 | Sunda clouded leopard | Felidae  | Carnivora | Animalia |
+----+-----------------------+----------+-----------+----------+