[SQL 첫걸음] 26강. 테이블 작성·삭제·변경

테이블 작성, 삭제, 변경

• 데이터베이스 객체인 테이블을 작성, 삭제, 변경하는 명령을 DDL(Data Definition Language)이라 합니다.

테이블 작성

• CREATE 명령을 사용하여 테이블, 뷰, 인덱스 등의 객체를 작성할 수 있습니다. 그 형태는 간단하게 표현해보면 아래와 같습니다.

CREATE TABLE 테이블명 (
    열 정의1,
    열 정의2,
    ...
)

• 열을 정의할 때는 열명을 붙이고 자료형으로 INTEGER, VARCHAR 등을 지정합니다.
• 특히 CHAR 또는 VARCHAR의 경우 문자열형이기 때문에 최대길이를 괄호(())를 사용하여 함께 지정해줘야 합니다.
• 또한 열을 정의할 때 DEFAULT 키워드를 사용하여 기본값을 설정할 수 있습니다.
• 마지막으로 NULL을 허용할 것인지 지정해야 합니다. 생략했을 때는 NULL을 허용하는 것으로 인지합니다.
• 최대 길이가 8인 문자열형 name열, 기본값으로 현재 시간을 저장하는 날짜시간형 create_at열, 그리고 no열을 가지는 테이블 sample62를 만드는 방법은 아래와 같습니다.

mysql > CREATE TABLE sample62 (
    -> no INTEGER NOT NULL,
    -> name VARCHAR(8) NOT NULL,
    -> created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    -> );

Query OK, 0 rows affected (0.02 sec)

mysql> DESC sample62;

+------------+------------+------+-----+-------------------+-------------------+
| Field      | Type       | Null | Key | Default           | Extra             |
+------------+------------+------+-----+-------------------+-------------------+
| no         | int        | NO   |     | NULL              |                   |
| name       | varchar(8) | NO   |     | NULL              |                   |
| created_at | datetime   | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+------------+------+-----+-------------------+-------------------+
3 rows in set (0.00 sec)

• INSERT 명령을 통해 실제로 데이터를 입력해보고 확인해보면 아래와 같습니다.

mysql > INSERT INTO sample62 (no, name) VALUES (1, '테스트');

Query OK, 1 row affected (0.00 sec)

mysql > SELECT * FROM sample62;

+----+-----------+---------------------+
| no | name      | created_at          |
+----+-----------+---------------------+
|  1 | 테스트    | 2021-12-08 17:52:55 |
+----+-----------+---------------------+
1 row in set (0.00 sec)

날짜시간형의 경우 현재 시간을 기본값으로 사용하고 싶다면 MySQL에서는
CURRENT_TIMESTAMP, Oracle에서는 SYSDATE를 사용합니다.

---

테이블 삭제

• DROP 명령을 통해 필요없는 객체를 삭제할 수 있씁니다.
• 이때 유의할 점은 많은 데이터베이스가 삭제 명령에 따로 확인을 요구하지 않는다는 것입니다. - 따라서 실수로 삭제하지 않게 조심해야 합니다.

앞서 만든 테이블 sample62를 삭제하는 방법은 아래와 같습니다.

mysql > DROP TABLE sample62;

Query OK, 0 rows affected (0.00 sec)

mysql > DESC sample62;

ERROR 1146 (42S02): Table 'sample.sample62' doesn't exist

데이터 행 삭제

테이블 정의는 그대로 둔 채 데이터, 다시 말해 행만 삭제할 때는 DROP이 아닌 DELETE 명령을 사용합니다.

• 이때 WHERE 구를 사용하여 조건을 지정하지 않으면 테이블의 모든 행이 삭제됩니다.

그러나 DELETE 명령의 경우 행 단위로 내부처리가 일어나기 때문에 삭제할 행이 많으면 처리속도가 늦어집니다. 따라서 테이블 내의 모든 행을 삭제해야 할 때 빠른 속도의 작업 처리가 필요하다면 DDL로 분류되는 TRUNCATE TABLE 명령을 사용할 수 있습니다.

mysql > TRUNCATE TABLE sample62;

Query OK, 0 rows affected (0.01 sec)

mysql > SELECT * FROM sample62;

Empty set (0.00 sec)

---

테이블 변경

• 테이블을 작성한 뒤에도 열 구성은 변경 가능합니다.
• ALTER 명령을 통해 객체를 변경할 수 있습니다.

이전에 만들었던 테이블 sample62에 최대길이가 16이고 VARCHAR형인 열 nickname을 추가하는 방법은 아래와 같습니다.

mysql > ALTER TABLE sample62 ADD (
    -> nickname VARCHAR(16) NOT NULL
    -> );

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC sample62;

+------------+-------------+------+-----+-------------------+-------------------+
| Field      | Type        | Null | Key | Default           | Extra             |
+------------+-------------+------+-----+-------------------+-------------------+
| no         | int         | NO   |     | NULL              |                   |
| name       | varchar(8)  | NO   |     | NULL              |                   |
| created_at | datetime    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| nickname   | varchar(16) | NO   |     | NULL              |                   |
+------------+-------------+------+-----+-------------------+-------------------+
4 rows in set (0.01 sec)

열 속성 변경

ALTER TABLE 명령에서 열 속성을 변경하라면 MODIFY 하부명령을 사용합니다.

• 이때 열을 정의하는 방법은 CREATE TABLE 때와 동일합니다.

테이블 sample62에 존재하는 nickname 열의 제약조건을 NULL 값을 허용하는 걸로 변경하고 최대길이 또한 8로 줄이는 방법은 아래와 같습니다.

mysql > ALTER TABLE sample62 MODIFY nickname VARCHAR(8) NULL;

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > DESC sample62;

+------------+------------+------+-----+-------------------+-------------------+
| Field      | Type       | Null | Key | Default           | Extra             |
+------------+------------+------+-----+-------------------+-------------------+
| no         | int        | NO   |     | NULL              |                   |
| name       | varchar(8) | NO   |     | NULL              |                   |
| created_at | datetime   | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| nickname   | varchar(8) | YES  |     | NULL              |                   |
+------------+------------+------+-----+-------------------+-------------------+
4 rows in set (0.00 sec)

열 이름 변경

ALTER TABLE 명령에서 열의 이름을 변경하려면 CHANGE 하부명령을 사용합니다.

• 첫 번째로 기존 열 이름을 입력하고 뒤이어 신규 열 이름을 입력하면 기존 열 이름이 해당 신규 열 이름으로 변경됩니다.

CHANGE 하부명령은 열 이름 뿐만 아니라 열 속성도 변경할 수 있습니다.
Oracle에서는 열 이름을 변경할 경우 RENAME TO 하부명령을 사용합니다.

mysql > ALTER TABLE sample62 CHANGE name real_name VARCHAR(16);
// 기존 sample62 테이블에서 name 컬럼을 real_name 컬럼명으로 변경 데이터타입도 varchar(8)에서 16으로 변경
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > DESC sample62;

+------------+-------------+------+-----+-------------------+-------------------+
| Field      | Type        | Null | Key | Default           | Extra             |
+------------+-------------+------+-----+-------------------+-------------------+
| no         | int         | NO   |     | NULL              |                   |
| real_name  | varchar(16) | YES  |     | NULL              |                   |
| created_at | datetime    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| nickname   | varchar(8)  | YES  |     | NULL              |                   |
+------------+-------------+------+-----+-------------------+-------------------+
4 rows in set (0.00 sec)

열 삭제

ALTER TABLE 명령에서 열을 삭제하려면 DROP 하부명령을 사용합니다. 뒤에 삭제하고 싶은 열명을 지정하면 됩니다.

mysql > ALTER TABLE sample62 DROP real_name;

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > DESC sample62;

+------------+------------+------+-----+-------------------+-------------------+
| Field      | Type       | Null | Key | Default           | Extra             |
+------------+------------+------+-----+-------------------+-------------------+
| no         | int        | NO   |     | NULL              |                   |
| created_at | datetime   | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| nickname   | varchar(8) | YES  |     | NULL              |                   |
+------------+------------+------+-----+-------------------+-------------------+
3 rows in set (0.00 sec)

---

ALTER TABLE로 테이블 관리

최대길이 연장

대규모 데이터베이스에서는 데이터의 크기가 매우 커질 때가 많습니다.
특히 행 개수가 많은 테이블에서는 데이터 하나의 크기만을 최적화하더라도 저장공간을 효율적으로 관리할 수 있습니다.
이럴 때 ALTER TABLE을 활용하여 해당 열의 자료형만 변경하거나 문자열형의 경우 최대길이를 조절하여 저장공간을 관리할 수 있습니다.

이때 유의할 점은 데이터가 이미 저장되어 있을 때 해당 데이터가 변경하려는 자료형에 알맞지 않은 경우 오류가 발생한다는 것과 또한 최대길이도 마찬가지로 저장되어 있는 데이터의 길이가 만약 변경하려는 최대길이보다 클 경우 오류가 발생한다는 것입니다.

먼저 아래와 같이 데이터가 저장되어 있는 테이블 sample62가 있다고 가정해봅시다.

+----+---------------------+------------------------+
| no | created_at          | nickname               |
+----+---------------------+------------------------+
|  1 | 2021-12-08 19:42:07 | 테스트용 닉네임           |
+----+---------------------+------------------------+

• 만약 nickname열의 자료형을 INTEGER형으로 변결하려면 아래와 같은 오류가 발생합니다.

mysql > ALTER TABLE sample62 MODIFY nickname INTEGER;

ERROR 1366 (HY000): Incorrect integer value: '테스트용 닉네임' for column 'nickname' at row 1

• 또한 최대길이를 변경했을 때 이미 저장된 데이터의 길이가 변경하려는 길이보다 클 경우에 아래와 같은 오류가 발생합니다.

mysql > ALTER TABLE sample62 MODIFY nickname VARCHAR(4);

ERROR 1265 (01000): Data truncated for column 'nickname' at row 1

열 추가

ALTER TABLE ADD ... 명령문을 통해 열을 추가하면 행을 추가하는 INSERT 명령을 꼭 확인해야 합니다.

추가된 열이 NULL 값을 허용하거나 DEFAULT 값이 존재하지 않는 이상 해당 열에 데이터 값을 지정해줘야 하기 때문입니다.

기존에 데이터가 존재하는 테이블 sample62에 아래와 같이 NOT NULL 제약조건을 걸어 name열을 추가한다고 가정해봅시다.

ALTER TABLE sample62 ADD name VARCHAR(4) NOT NULL;

기존 존재하던 행의 새로 추가된 name열 값과 DESC 명령을 통해 테이블 구조를 확인해보면 아래와 같습니다.

mysql > SELECT * FROM sample62;

+----+---------------------+------------------------+------+
| no | created_at          | nickname               | name |
+----+---------------------+------------------------+------+
|  1 | 2021-12-08 19:42:07 | 테스트용 닉네임          |      |
+----+---------------------+------------------------+------+
1 row in set (0.00 sec)

mysql > DESC sample62;

+------------+------------+------+-----+-------------------+-------------------+
| Field      | Type       | Null | Key | Default           | Extra             |
+------------+------------+------+-----+-------------------+-------------------+
| no         | int        | NO   |     | NULL              |                   |
| created_at | datetime   | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| nickname   | varchar(8) | YES  |     | NULL              |                   |
| name       | varchar(4) | NO   |     | NULL              |                   |
+------------+------------+------+-----+-------------------+-------------------+
4 rows in set (0.01 sec)

• 추가된 name열의 Default 값이 NULL로 되어 있음에도 제약조건이 NOT NULL로 되어 있기 때문에 기본값이 빈 문자열, 다시 말해 ''(빈 스트링)로 추가된 것을 확인할 수 있습니다.
• 따라서 WHERE 구와 함께 IS NULL과 = ''를 조건으로 검색(SELECT)하면 결과는 아래와 같습니다.

mysql > SELECT * FROM sample62 WHERE name IS NULL;

Empty set (0.00 sec)

mysql > SELECT * FROM sample62 WHERE name = '';

+----+---------------------+------------------------+------+
| no | created_at          | nickname               | name |
+----+---------------------+------------------------+------+
|  1 | 2021-12-08 19:42:07 | 테스트용 닉네임        |      |
+----+---------------------+------------------------+------+
1 row in set (0.00 sec)

INTEGER형의 경우는 아래와 같이 제약조건이 NOT NULL일 때 기본값으로 0이 입력되는 것을 확인할 수 있습니다.

mysql > ALTER TABLE sample62 ADD test INTEGER NOT NULL;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > SELECT * FROM sample62;

+----+---------------------+------------------------+------+------+
| no | created_at          | nickname               | name | test |
+----+---------------------+------------------------+------+------+
|  1 | 2021-12-08 19:42:07 | 테스트용 닉네임          |      |    0 |
+----+---------------------+------------------------+------+------+
1 row in set (0.01 sec)

제약조건이 NULL값을 허용하고 별다른 DEFAULT 키워드를 통한 기본값 설정이 없을 경우에는 자료형에 상관없이 기본값이 전부 NULL로 입력됩니다.