MySQL ALTER TABLE 테이블 수정 하기

▶쿼리 (Query)

컬럼 추가 (Add)

ALTER TABLE table_name ADD COLUMN ex_column varchar(32) NOT NULL;

1-1: 맨 뒤에 추가

ALTER TABLE `테이블명` ADD `컬럼명` 자료형

mysql> DESC employee;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id            | int(11)     | NO   | PRI | NULL    |       |
| name          | varchar(16) | NO   |     | NULL    |       |
| department_id | int(11)     | NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE `employee` ADD `comments` VARCHAR(200) NOT NULL;
Query OK, 4 rows affected (0.41 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> DESC employee;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| id            | int(11)      | NO   | PRI | NULL    |       |
| name          | varchar(16)  | NO   |     | NULL    |       |
| department_id | int(11)      | NO   |     | NULL    |       |
| comments      | varchar(200) | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

1-2: 맨 앞에 추가

ALTER TABLE `테이블명` ADD `새컬럼명` 자료형 FIRST

1-3: 지정 컬럼 다음에 추가

ALTER TABLE `테이블명` ADD `새컬럼명` 자료형 AFTER `앞컬럼명`

mysql> DESC employee;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id            | int(11)     | NO   | PRI | NULL    |       |
| name          | varchar(16) | NO   |     | NULL    |       |
| department_id | int(11)     | NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE `employee` ADD `comments` VARCHAR(200) NOT NULL AFTER `name`;
Query OK, 4 rows affected (0.10 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> DESC employee;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| id            | int(11)      | NO   | PRI | NULL    |       |
| name          | varchar(16)  | NO   |     | NULL    |       |
| comments      | varchar(200) | NO   |     | NULL    |       |
| department_id | int(11)      | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

→ 새 컬럼 comments가 name 컬럼의 바로 다음에 생성되었다.

컬럼 변경 (Modify)

ALTER TABLE table_name MODIFY COLUMN ex_column varchar(16) NULL;

다른 컬럼 다음으로 이동

ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 자료형 AFTER 다른컬럼;

첫번째 위치로 이동

ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 자료형 FIRST;

컬럼 이름까지 변경 (Change)

ALTER TABLE table_name CHANGE COLUMN ex_column ex_column2 varchar(16) NULL;

명령어

• 컬럼명 변경

ALTER TABLE `테이블명` CHANGE `기존컬럼명` `새컬럼명` 기존자료형

→ 컬럼명을 변경할 때도 자료형을 기입해야 한다. 기존 자료형을 확인하고 그대로 써주자.

• 컬럼 자료형 변경

ALTER TABLE `테이블명` CHANGE `컬럼명` `컬럼명` 새자료형

자료형변경 ex)

mysql> DESC employee;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id            | int(11)     | NO   | PRI | NULL    |       |
| name          | varchar(16) | NO   |     | NULL    |       |
| department_id | int(11)     | NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE `employee` CHANGE `department_id` `department_id` VARCHAR(16) NOT NULL;
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> DESC employee;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id            | int(11)     | NO   | PRI | NULL    |       |
| name          | varchar(16) | NO   |     | NULL    |       |
| department_id | varchar(16) | NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

컬럼 삭제 (Drop)

ALTER TABLE table_name DROP COLUMN ex_column;

테이블 이름 변경 (RENAME)

ALTER TABLE table_name1 RENAME table_name2;

MySQL 컬럼 삭제

ALTER TABLE `테이블명` DROP `컬럼명`;

ex)

mysql> DESC employee;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| id            | int(11)      | NO   | PRI | NULL    |       |
| name          | varchar(16)  | NO   |     | NULL    |       |
| department_id | int(11)      | NO   |     | NULL    |       |
| comments      | varchar(200) | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE `employee` DROP `comments`;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> DESC employee;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id            | int(11)     | NO   | PRI | NULL    |       |
| name          | varchar(16) | NO   |     | NULL    |       |
| department_id | int(11)     | NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)