[DB] MySQL / 테이블의 컬럼 통째로 삽입 / INNER JOIN 예제

Onam Kwon·2023년 1월 2일
0

DB

목록 보기
4/11

새 테이블에 다른 테이블의 컬럼값 삽입

INSERT INTO new_table_name (new_table_col1,new_table_col2) 
SELECT (new_table_col1,new_table_col2) FROM old_table_name;
  • 데이터베이스 정규화를 진행하려면 현재 존재하는 테이블을 분리해야 하는 경우가 생긴다.
  • 하나씩 복사하는건 당연히 비효율적이기 때문에 컬럼을 통째로 복사해 삽입하는 방법을 소개한다.

SHOW CREATE TABLE table_name

SHOW CREATE TABLE table_name;
  • 위 쿼리는 이미 존재하는 테이블을 만들었을때 사용한 쿼리를 뽑을때 사용한다.
mysql> CREATE TABLE Population (
    -> name VARCHAR(255) NOT NULL PRIMARY KEY,
    -> city_population INT NOT NULL
    -> );
Query OK, 0 rows affected (0.04 sec)
mysql>DESCRIBE Population;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name            | varchar(255) | NO   | PRI | NULL    |       |
| city_population | int          | NO   |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE Population;
+------------+----------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                           |
+------------+----------------------------------------------------------+
| Population | CREATE TABLE `Population` (
  `name` varchar(255) NOT NULL,
  `city_population` int NOT NULL,
  PRIMARY KEY (`city_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+----------------------------------------------------------+
1 row in set (0.00 sec)
  • 우선 복사한 데이터를 삽입할 새로운 테이블 Population을 위와 같이 만들었다.
mysql> SELECT * FROM Cities;
+----------+------------+-------------+-----------------+
| city_num | state_name | city_name   | city_population |
+----------+------------+-------------+-----------------+
|        1 | KS         | Emporia     |           25000 |
|        2 | KS         | Topeka      |          130000 |
|        3 | KS         | Wichita     |          400000 |
|        4 | KS         | Kansas City |          160000 |
|        5 | TX         | Dallas      |         1300000 |
|        6 | TX         | Houston     |         2300000 |
|        7 | TX         | El Paso     |          680000 |
+----------+------------+-------------+-----------------+
7 rows in set (0.00 sec)
  • 위의 도시 테이블은 기존에 있던 테이블이며 인구 테이블을 따로 만들어 위 도시 테이블의 city_num, city_population만 따로 복사하려고 할 때 아래와 같이 컬럼값을 통째로 삽입할 수 있다.
mysql> INSERT INTO Population (name,city_population) 
    -> SELECT (city_name,city_population) FROM Cities;
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
mysql> select * from population;
+-------------+-----------------+
| name        | city_population |
+-------------+-----------------+
| Dallas      |         1300000 |
| El Paso     |          680000 |
| Emporia     |           25000 |
| Houston     |         2300000 |
| Kansas City |          160000 |
| Topeka      |          130000 |
| Wichita     |          400000 |
+-------------+-----------------+
7 rows in set (0.00 sec)
  • 결과는 위와같이 그대로 복사가 되었다.

ALTER TABLE

ALTER TABLE DROP

ALTER TABLE table_name DROP col_name;
  • 이미 존재하는 테이블의 컬럼을 삭제할때 사용한다.
mysql> SELECT * FROM Cities;
+----------+------------+-------------+-----------------+
| city_num | state_name | city_name   | city_population |
+----------+------------+-------------+-----------------+
|        1 | KS         | Emporia     |           25000 |
|        2 | KS         | Topeka      |          130000 |
|        3 | KS         | Wichita     |          400000 |
|        4 | KS         | Kansas City |          160000 |
|        5 | TX         | Dallas      |         1300000 |
|        6 | TX         | Houston     |         2300000 |
|        7 | TX         | El Paso     |          680000 |
+----------+------------+-------------+-----------------+
7 rows in set (0.00 sec)

mysql> ALTER TABLE Cities DROP city_population;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM Cities;
+----------+------------+-------------+
| city_num | state_name | city_name   |
+----------+------------+-------------+
|        1 | KS         | Emporia     |
|        2 | KS         | Topeka      |
|        3 | KS         | Wichita     |
|        4 | KS         | Kansas City |
|        5 | TX         | Dallas      |
|        6 | TX         | Houston     |
|        7 | TX         | El Paso     |
+----------+------------+-------------+
7 rows in set (0.00 sec)
  • 인구 컬럼을 복사해 새로운 테이블에 넣었으므로, 기존에 존재하던 도시 테이블의 인구 컬럼을 지웠다.

ALTER TABLE MODIFY

ALTER TABLE table_name MODIFY col_name data_type condition1 condition2...;
  • 번외로 이미 존재하는 컬럼의 설정을 바꾸려면 위와같이 재정의 해주면 된다.

JOIN

SELECT * FROM table_A AS a 
INNER JOIN table_b AS b
ON a.col1 = b.col1;
  • 둘 이상의 테이블 사이에 관련 열을 기반으로 행을 결합하는데 사용한다.
  • 기본적으로 INNER를 생략해도 INNER JOIN으로 실행되며, 이 외에도 다양하게 LEFT RIGHT CROSS OUTTER UNION JOIN이 있지만 다음 게시글에서 설명하도록 하겠다.
    • 이번 예제에서는 INNER JOIN 사용.
mysql> SELECT * FROM Cities;
+----------+------------+-------------+
| city_num | state_name | city_name   |
+----------+------------+-------------+
|        1 | KS         | Emporia     |
|        2 | KS         | Topeka      |
|        3 | KS         | Wichita     |
|        4 | KS         | Kansas City |
|        5 | TX         | Dallas      |
|        6 | TX         | Houston     |
|        7 | TX         | El Paso     |
+----------+------------+-------------+
7 rows in set (0.00 sec)

mysql> select * from population;
+-------------+-----------------+
| name        | city_population |
+-------------+-----------------+
| Dallas      |         1300000 |
| El Paso     |          680000 |
| Emporia     |           25000 |
| Houston     |         2300000 |
| Kansas City |          160000 |
| Topeka      |          130000 |
| Wichita     |          400000 |
+-------------+-----------------+
7 rows in set (0.00 sec)
  • 여기까지 JOIN을 사용하기 위한 테이블 분리에 필요한 과정이었고 이제 JOIN을 적용해 위의 도시 테이블과 인구 테이블을 합쳐서 출력해볼 차례이다.
mysql> SELECT c.city_num,c.state_name,c.city_name,p.city_population
    -> FROM Cities AS c INNER JOIN Population AS P
    -> ON c.city_name = p.name;
+----------+------------+-------------+-----------------+
| city_num | state_name | city_name   | city_population |
+----------+------------+-------------+-----------------+
|        1 | KS         | Emporia     |           25000 |
|        2 | KS         | Topeka      |          130000 |
|        3 | KS         | Wichita     |          400000 |
|        4 | KS         | Kansas City |          160000 |
|        5 | TX         | Dallas      |         1300000 |
|        6 | TX         | Houston     |         2300000 |
|        7 | TX         | El Paso     |          680000 |
+----------+------------+-------------+-----------------+
7 rows in set (0.00 sec)
  • Cities테이블과 Population테이블의 공통된 값, 기본키인 city_num을 이용해 두 테이블을 합쳐서 출력했다.
  • INNER JOIN 말고도 더 다양한 JOIN의 종류가 있으며 다음에 더 자세히 알아보도록 하겠습니다.
profile
권오남 / Onam Kwon

0개의 댓글