새 테이블에 다른 테이블의 컬럼값 삽입
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: 0mysql> 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으로 실행되며, 이 외에도 다양하게LEFTRIGHTCROSSOUTTERUNIONJOIN이 있지만 다음 게시글에서 설명하도록 하겠다.
- 이번 예제에서는 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의 종류가 있으며 다음에 더 자세히 알아보도록 하겠습니다.
권오남 / Onam Kwon