[MySQL] 자료형, DDL-CREATE, ALTER, DROP

MySQL 자료형

https://www.boostcourse.org/web326/lecture/58936?isDesc=false

https://www.boostcourse.org/web326/lecture/58936?isDesc=false

---

DDL ( Data Definition Language ) : 정의어

CREATE

CREATE TABLE <테이블-이름> (
	<필드-이름1> 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREMENT], 
	<필드-이름2> 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREMENT], 
	<필드-이름3> 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREMENT], 
	...
	PRIMARY KEY(필드-이름)
	);

mysql> CREATE TABLE EMPLOYEE2(
    -> EMPNO      INTEGER NOT NULL PRIMARY KEY,
    -> NAME       VARCHAR(10),
    -> JOB        VARCHAR(9),
    -> BOSS       INTEGER,
    -> HIREDATE   VARCHAR(12),
    -> SALARY     DECIMAL(7, 2),
    -> COMM       DECIMAL(7, 2),
    -> DEPTNO     INTEGER
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> DESC EMPLOYEE2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| EMPNO    | int(11)      | NO   | PRI | NULL    |       |
| NAME     | varchar(10)  | YES  |     | NULL    |       |
| JOB      | varchar(9)   | YES  |     | NULL    |       |
| BOSS     | int(11)      | YES  |     | NULL    |       |
| HIREDATE | varchar(12)  | YES  |     | NULL    |       |
| SALARY   | decimal(7,2) | YES  |     | NULL    |       |
| COMM     | decimal(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int(11)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

---

ALTER

ALTER TABLE <테이블-이름>
	ADD <필드-이름> 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREMENT];

ALTER TABLE <테이블-이름>
	DROP <필드-이름>;
    
ALTER TABLE <테이블-이름>
	CHANGE <필드-이름> <바꿀-필드-이름> 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREMENT];
    
ALTER TABLE <테이블-이름> RENAME <바꿀-테이블-이름>

mysql> ALTER TABLE EMPLOYEE2
    ->
    -> ADD BIRTHDATE VARCHAR(12);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC EMPLOYEE2;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| EMPNO     | int(11)      | NO   | PRI | NULL    |       |
| NAME      | varchar(10)  | YES  |     | NULL    |       |
| JOB       | varchar(9)   | YES  |     | NULL    |       |
| BOSS      | int(11)      | YES  |     | NULL    |       |
| HIREDATE  | varchar(12)  | YES  |     | NULL    |       |
| SALARY    | decimal(7,2) | YES  |     | NULL    |       |
| COMM      | decimal(7,2) | YES  |     | NULL    |       |
| DEPTNO    | int(11)      | YES  |     | NULL    |       |
| BIRTHDATE | varchar(12)  | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql> ALTER TABLE EMPLOYEE2
    ->
    -> DROP BIRTHDATE;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC EMPLOYEE2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| EMPNO    | int(11)      | NO   | PRI | NULL    |       |
| NAME     | varchar(10)  | YES  |     | NULL    |       |
| JOB      | varchar(9)   | YES  |     | NULL    |       |
| BOSS     | int(11)      | YES  |     | NULL    |       |
| HIREDATE | varchar(12)  | YES  |     | NULL    |       |
| SALARY   | decimal(7,2) | YES  |     | NULL    |       |
| COMM     | decimal(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int(11)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> ALTER TABLE EMPLOYEE2
    ->
    -> CHANGE DEPTNO DEPT_NO INT(11);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC EMPLOYEE2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| EMPNO    | int(11)      | NO   | PRI | NULL    |       |
| NAME     | varchar(10)  | YES  |     | NULL    |       |
| JOB      | varchar(9)   | YES  |     | NULL    |       |
| BOSS     | int(11)      | YES  |     | NULL    |       |
| HIREDATE | varchar(12)  | YES  |     | NULL    |       |
| SALARY   | decimal(7,2) | YES  |     | NULL    |       |
| COMM     | decimal(7,2) | YES  |     | NULL    |       |
| DEPT_NO  | int(11)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> ALTER TABLE EMPLOYEE2
    ->
    -> RENAME EMPLOYEE3;
Query OK, 0 rows affected (0.01 sec)

mysql> DESC EMPLOYEE3;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| EMPNO    | int(11)      | NO   | PRI | NULL    |       |
| NAME     | varchar(10)  | YES  |     | NULL    |       |
| JOB      | varchar(9)   | YES  |     | NULL    |       |
| BOSS     | int(11)      | YES  |     | NULL    |       |
| HIREDATE | varchar(12)  | YES  |     | NULL    |       |
| SALARY   | decimal(7,2) | YES  |     | NULL    |       |
| COMM     | decimal(7,2) | YES  |     | NULL    |       |
| DEPT_NO  | int(11)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

---

DROP

DROP TABLE <테이블-이름>;

mysql> DROP TABLE EMPLOYEE3;
Query OK, 0 rows affected (0.01 sec)

mysql> DESC EMPLOYEE3;
ERROR 1146 (42S02): Table 'connectdb.employee3' doesn't exist