SQLD - DDL

1. 데이터 유형

CHARACTER(s), - 고정 길이 문자열 -> 공백을 문자 취급하지 않는다.

VARCHAR(s) - 가변 길이 문자열 -> 공백을 문자 하나로 취급한다.

NUMERIC - 정수, 실수 등 숫자 정보

DATETIME - 날짜와 시각 정보

 

2. CREATE TABLE

mysql> CREATE TABLE STADIUM (
    -> STADIUM_ID CHAR(3) NOT NULL,
    -> CONSTRAINT STADIUM_PK PRIMARY KEY (STADIUM_ID)
    -> );
    
    
    
mysql> DESC STADIUM
    -> ;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| STADIUM_ID | char(3) | NO   | PRI | NULL    |       |
+------------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE TEAM (
    -> TEAM_ID CHAR(3) NOT NULL,
    -> TEAM_NAME VARCHAR(40) NOT NULL,
    -> STADIUM_ID CHAR(3) NOT NULL,
    -> CONSTRAINT TEAK_PK PRIMARY KEY (TEAM_ID),
    -> CONSTRAINT TEAM_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID)
    -> );
    

mysql> DESC TEAM;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| TEAM_ID    | char(3)     | NO   | PRI | NULL    |       |
| TEAM_NAME  | varchar(40) | NO   |     | NULL    |       |
| STADIUM_ID | char(3)     | NO   | MUL | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

 

3. 제약조건(Constraint)

- PRIMARY KEY(기본키)

- NOT NULL

- UNIQUE KEY(고유키)

- CHECK

- FOREIGN KEY(외래키)

 

4. NULL의 의미

- NULL은 공백, 숫자 0, 조건에 맞는 데이터가 없을 때의 공집합과 다르다.

- '아직 정의되지 않은 미지의 값' 이거나 '현재 데이터를 입력하지 못하는 경우'를 의미한다.

 

5. DESC 테이블명

- 테이블 구조 확인

 

6. SELECT 문장을 통한 테이블 생성 사례

- CREATE TABLE ~ AS SELECT ~

- 근데 이 방법 쓰면 NOT NULL 빼고 제약조건 적용이 안되기 때문에 제약조건 추가하려면 ALTER TABLE 기능을 사용해야한다.

mysql> CREATE TABLE TEAM_TEMP3 AS SELECT * FROM TEAM;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC TEAM_TEMP3;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| TEAM_ID      | char(3)     | NO   |     | NULL    |       |
| TEAM_NAME    | varchar(40) | NO   |     | NULL    |       |
| STADIUM_ID   | char(3)     | NO   |     | NULL    |       |
| HOME_ADDRESS | varchar(20) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 

7. ALTER TABLE - ADD COLUMN

ALTER TABLE 테이블명 ADD 추가할 칼럼명 데이터 유형;

mysql> ALTER TABLE TEAM ADD LOVE_HYUNNDY VARCHAR(20) NOT NULL;
Query OK, 0 rows affected (0.02 sec)

mysql> DESC TEAM
    -> ;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| TEAM_ID      | char(3)     | NO   | PRI | NULL    |       |
| TEAM_NAME    | varchar(40) | NO   |     | NULL    |       |
| STADIUM_ID   | char(3)     | NO   | MUL | NULL    |       |
| HOME_ADDRESS | varchar(20) | NO   |     | NULL    |       |
| LOVE_HYUNNDY | varchar(20) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

 

8. ALTER TABLE - DROP COLUMN

mysql> ALTER TABLE TEAM DROP COLUMN LOVE_HYUNNDY;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC TEAM;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| TEAM_ID      | char(3)     | NO   | PRI | NULL    |       |
| TEAM_NAME    | varchar(40) | NO   |     | NULL    |       |
| STADIUM_ID   | char(3)     | NO   | MUL | NULL    |       |
| HOME_ADDRESS | varchar(20) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

- 테이블에서 필요 없는 칼럼을 삭제할 수 있으며, 데이터가 있거나 없거나 모두 삭제 가능하다.

- 한 번에 하나의 칼럼만 삭제 가능하며, 칼럼 삭제 후 최소 하나 이상의 칼럼이 테 이블에 존재해야 한다.

- 주의할 부분은 한 번 삭제된 칼럼은 복구가 불가능하다

9. ALTER TABLE - MODIFY COLUMN

mysql> ALTER TABLE TEAM
    -> MODIFY COLUMN HOME_ADDRESS VARCHAR(20) DEFAULT 'HYUNNDY';
Query OK, 0 rows affected (0.13 sec)

mysql> DESC TEAM;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| TEAM_ID      | char(3)     | NO   | PRI | NULL    |       |
| TEAM_NAME    | varchar(40) | NO   |     | NULL    |       |
| STADIUM_ID   | char(3)     | NO   | MUL | NULL    |       |
| HOME_ADDRESS | varchar(20) | YES  |     | HYUNNDY |       |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 

- 해당 칼럼의 크기를 늘릴 수는 있지만 줄이지는 못한다. 이는 기존의 데이터가 훼손될 수 있기 때 문이다.

- 해당 칼럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 폭을 줄일 수 있다.

- 해당 칼럼이 NULL 값만을 가지고 있으면 데이터 유형을 변경할 수 있다.

- 해당 칼럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.

- 해당 칼럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.

 

10. ALTER TABLE - RENAME COLUMN

mysql> ALTER TABLE TEAM RENAME COLUMN HOME_ADDRESS TO PLAYER_NAME;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC TEAM;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| TEAM_ID     | char(3)     | NO   | PRI | NULL    |       |
| TEAM_NAME   | varchar(40) | NO   |     | NULL    |       |
| STADIUM_ID  | char(3)     | NO   | MUL | NULL    |       |
| PLAYER_NAME | varchar(20) | YES  |     | HYUNNDY |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

11. ALTER TABLE - DROP CONSTRAINT

mysql> ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;

12. ALTER TABLE - ADD CONSTRAINT

mysql> ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC PLAYER
    -> ;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| PLAYER_ID   | char(7)     | NO   | PRI | NULL    |       |
| PLAYER_NAME | varchar(20) | NO   |     | NULL    |       |
| TEAM_ID     | char(3)     | NO   | MUL | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> DESC TEAM;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| TEAM_ID     | char(3)     | NO   | PRI | NULL    |       |
| TEAM_NAME   | varchar(40) | NO   |     | NULL    |       |
| STADIUM_ID  | char(3)     | NO   | MUL | NULL    |       |
| PLAYER_NAME | varchar(20) | YES  |     | HYUNNDY |       |
+-------------+-------------+------+-----+---------+-------+

 

13. RENAME TABLE

mysql> RENAME TABLE TEAM TO TEAM_BACKUP;
Query OK, 0 rows affected (0.02 sec)

mysql> DESC TEAM_BACKUP;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| TEAM_ID     | char(3)     | NO   | PRI | NULL    |       |
| TEAM_NAME   | varchar(40) | NO   |     | NULL    |       |
| STADIUM_ID  | char(3)     | NO   | MUL | NULL    |       |
| PLAYER_NAME | varchar(20) | YES  |     | HYUNNDY |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

 

14. DROP TABLE

mysql> DROP TABLE TEAM_BACKUP;
Query OK, 0 rows affected (0.02 sec)

mysql> DESC TEAM_BACKUP;
ERROR 1146 (42S02): Table 'mysql.team_backup' doesn't exist

 

테이블을 잘못 만들었거나 테이블이 더 이상 필요 없을 경우 해당 테이블을 삭제해야 한다. 다음은 불필요한 테이블을 삭제하는 명령이다.

 

15. TRUNCATE TABLE

mysql> TRUNCATE TABLE TEAM_BACKUP;
Query OK, 0 rows affected (0.05 sec)

TRUNCATE TABLE 은 테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다. 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE 을 실행하면 된다.

 

DROP TABLE 의 경우는 테이블 자체가 없어지기 때문에 테이블 구조를 확인할 수 없다.

반면 TRUNCATE TABLE 의 경우는 테이블 구조는 그대로 유지한 체 데이터만 전부 삭제하는 기능이다.

 

TRUNCATE 는 데이터 구조의 변경 없이 테이블의 데이터를 일괄 삭제하는 명령어로 DML 로 분류 할 수도 있지만 내부 처리 방식이나 Auto Commit 특성 등으로 인해 DDL 로 분류하였다.

 

테이블 에 있는 데이터를 삭제하는 명령어는 TRUNCATE TABLE 명령어 이외에도 다음 DML 절에서 살펴 볼 DELETE 명령어가 있다.

그러나 DELETE 와 TRUNCATE 는 처리하는 방식 자체가 다르다.

테이블의 전체 데이터를 삭제하는 경우, 시스템 활용 측면에서는 DELETE TABLE 보다는 시스템 부하 가 적은 TRUNCATE TABLE 을 권고한다.

단, TRUNCATE TABLE 의 경우 정상적인 복구가 불가 능하므로 주의해야 한다.