데이터베이스 만들기
- 열(Column) 추가, 삭제 등을 해볼 테스트용 데이터베이스 jbTest를 만듭니다.
CREATE DATABASE jbTest;
USE jbTest;
- jbColumn1, jbColumn2, jbColumn3 세 개의 열을 가진 테이블 jbTable1을 만듭니다.
CREATE TABLE jbTable1 (
jbColumn1 INT,
jbColumn2 INT,
jbColumn3 INT
)
;
MariaDB [jbTest]> DESC jbTable1;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| jbColumn1 | int(11) | YES | | NULL | |
| jbColumn2 | int(11) | YES | | NULL | |
| jbColumn3 | int(11) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
열 추가하기
- jbTable1에 jbColumn4 열을 추가합니다.
ALTER TABLE jbTable1
ADD jbColumn4 INT
;
MariaDB [jbTest]> DESC jbTable1;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| jbColumn1 | int(11) | YES | | NULL | |
| jbColumn2 | int(11) | YES | | NULL | |
| jbColumn3 | int(11) | YES | | NULL | |
| jbColumn4 | int(11) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
- 여러 개의 열을 한 번에 추가할 수 있습니다.
ALTER TABLE jbTable1
ADD (
jbColumn5 INT,
jbColumn6 INT
)
;
MariaDB [jbTest]> DESC jbTable1;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| jbColumn1 | int(11) | YES | | NULL | |
| jbColumn2 | int(11) | YES | | NULL | |
| jbColumn3 | int(11) | YES | | NULL | |
| jbColumn4 | int(11) | YES | | NULL | |
| jbColumn5 | int(11) | YES | | NULL | |
| jbColumn6 | int(11) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
- 첫번째 열로 추가하고 싶다면 FIRST를 붙입니다.
ALTER TABLE jbTable1
ADD jbColumn0 INT
FIRST
;
MariaDB [jbTest]> DESC jbTable1;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| jbColumn0 | int(11) | YES | | NULL | |
| jbColumn1 | int(11) | YES | | NULL | |
| jbColumn2 | int(11) | YES | | NULL | |
| jbColumn3 | int(11) | YES | | NULL | |
| jbColumn4 | int(11) | YES | | NULL | |
| jbColumn5 | int(11) | YES | | NULL | |
| jbColumn6 | int(11) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
- 특정 열 뒤에 삽입하고 싶다면 AFTER를 이용합니다.
ALTER TABLE jbTable1
ADD jbColumn7 INT
AFTER jbColumn2
;
MariaDB [jbTest]> DESC jbTable1;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| jbColumn0 | int(11) | YES | | NULL | |
| jbColumn1 | int(11) | YES | | NULL | |
| jbColumn2 | int(11) | YES | | NULL | |
| jbColumn7 | int(11) | YES | | NULL | |
| jbColumn3 | int(11) | YES | | NULL | |
| jbColumn4 | int(11) | YES | | NULL | |
| jbColumn5 | int(11) | YES | | NULL | |
| jbColumn6 | int(11) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
열 삭제하기
- jbTable1에서 jbColumn0을 삭제합니다.
ALTER TABLE jbTable1
DROP jbColumn0
;
MariaDB [jbTest]> DESC jbTable1;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| jbColumn1 | int(11) | YES | | NULL | |
| jbColumn2 | int(11) | YES | | NULL | |
| jbColumn7 | int(11) | YES | | NULL | |
| jbColumn3 | int(11) | YES | | NULL | |
| jbColumn4 | int(11) | YES | | NULL | |
| jbColumn5 | int(11) | YES | | NULL | |
| jbColumn6 | int(11) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
열 순서 변경하기
- jbColumn7을 맨 앞으로 이동합니다. 데이터 타입도 같이 적어야 합니다.
ALTER TABLE jbTable1
MODIFY jbColumn7 INT
FIRST
;
MariaDB [jbTest]> DESC jbTable1;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| jbColumn7 | int(11) | YES | | NULL | |
| jbColumn1 | int(11) | YES | | NULL | |
| jbColumn2 | int(11) | YES | | NULL | |
| jbColumn3 | int(11) | YES | | NULL | |
| jbColumn4 | int(11) | YES | | NULL | |
| jbColumn5 | int(11) | YES | | NULL | |
| jbColumn6 | int(11) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
- 특정 열 뒤로 이동하고 싶다면 AFTER를 이용합니다.
ALTER TABLE jbTable1
MODIFY jbColumn7 INT
AFTER jbColumn6
;
MariaDB [jbTest]> DESC jbTable1;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| jbColumn1 | int(11) | YES | | NULL | |
| jbColumn2 | int(11) | YES | | NULL | |
| jbColumn3 | int(11) | YES | | NULL | |
| jbColumn4 | int(11) | YES | | NULL | |
| jbColumn5 | int(11) | YES | | NULL | |
| jbColumn6 | int(11) | YES | | NULL | |
| jbColumn7 | int(11) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
열 이름 변경하기, 데이터 타입 변경하기
- jbColumn1을 jbColumn0으로 변경합니다.
ALTER TABLE jbTable1
CHANGE jbColumn1 jbColumn0 INT
;
MariaDB [jbTest]> DESC jbTable1;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| jbColumn0 | int(11) | YES | | NULL | |
| jbColumn2 | int(11) | YES | | NULL | |
| jbColumn3 | int(11) | YES | | NULL | |
| jbColumn4 | int(11) | YES | | NULL | |
| jbColumn5 | int(11) | YES | | NULL | |
| jbColumn6 | int(11) | YES | | NULL | |
| jbColumn7 | int(11) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
- jbColumn0의 데이터 타입을 VARCHAR(10)으로 변경합니다.
ALTER TABLE jbTable1
CHANGE jbColumn0 jbColumn0 VARCHAR(10)
;
MariaDB [jbTest]> DESC jbTable1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| jbColumn0 | varchar(10) | YES | | NULL | |
| jbColumn2 | int(11) | YES | | NULL | |
| jbColumn3 | int(11) | YES | | NULL | |
| jbColumn4 | int(11) | YES | | NULL | |
| jbColumn5 | int(11) | YES | | NULL | |
| jbColumn6 | int(11) | YES | | NULL | |
| jbColumn7 | int(11) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
- 열 이름과 데이터 타입을 동시에 변경할 수 있습니다.
ALTER TABLE jbTable1
CHANGE jbColumn0 jbColumn1 INT
;
MariaDB [jbTest]> DESC jbTable1;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| jbColumn1 | int(11) | YES | | NULL | |
| jbColumn2 | int(11) | YES | | NULL | |
| jbColumn3 | int(11) | YES | | NULL | |
| jbColumn4 | int(11) | YES | | NULL | |
| jbColumn5 | int(11) | YES | | NULL | |
| jbColumn6 | int(11) | YES | | NULL | |
| jbColumn7 | int(11) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+