테이블

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(필드명)
    );
    
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
    );
    
create table book(
	isbn varchar(10) primary key,
    title varchar(20) not null,
    price integer not null
    );

수정 (추가 / 삭제)

alter table 테이블명
	add 필드명 타입 null or not null, default, auto_increment;
    
mysql> alter table book add author varchar(20);
mysql> desc book;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| isbn   | varchar(10) | NO   | PRI | NULL    |       |
| title  | varchar(20) | NO   |     | NULL    |       |
| price  | int         | NO   |     | NULL    |       |
| author | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

alter table 테이블명
	drop 필드명;
mysql> alter table book drop author;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| isbn  | varchar(10) | NO   | PRI | NULL    |       |
| title | varchar(20) | NO   |     | NULL    |       |
| price | int         | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
    

컬럼이름 수정

mysql> desc empolyee2;
ERROR 1146 (42S02): Table 'connectdb.empolyee2' doesn't exist
mysql> desc employee2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int          | NO   | PRI | NULL    |       |
| name     | varchar(10)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| boss     | int          | YES  |     | NULL    |       |
| hiredate | varchar(12)  | YES  |     | NULL    |       |
| salary   | decimal(7,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int          | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> alter table employee2 change deptno dept_no int(11);
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc employee2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int          | NO   | PRI | NULL    |       |
| name     | varchar(10)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| boss     | int          | YES  |     | NULL    |       |
| hiredate | varchar(12)  | YES  |     | NULL    |       |
| salary   | decimal(7,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| dept_no  | int          | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

테이블 이름변경

alter table 테이블명 rename 변경할 테이블명;

테이블 삭제

drop table 테이블이름; 

제약 조건이 있을 경우에는 drop table 명령으로 테이블이 삭제되지 않을 수 있다
생성한 반대 순서로 삭제해야함