오라클(MySQL) DDL (CREATE, ALTER, DROP, TRUNCATE) 총정리

CREATE 

CREATE TABLE TB_NAME(
    COL_NAME1 VARCHAR(10) NOT NULL PRIMARY KEY,
    COL_2NAME INT NOT NULL  AUTO_INCREMENT UNIQUE,
    COL_NAME3 CHAR(10) DEFAULT NULL,
    COL_NAME4 VARCHAR(5) DEFAULT 'Y',
    CONSTRAINT test_check CHECK(COL_NAME4 BETWEEN 'Y' AND 'N'),
    PRIMARY KEY (COL_2NAME)
);

 

ALTER

ALTER TABLE TB_NAME ADD COL_NAME5 INT;
ALTER TABLE TB_NAME DROP COLUMN COL_NAME3;
ALTER TABLE TB_NAME MODIFY COL_NAME4 VARCHAR(3) DEFAULT 'N';
ALTER TABLE TB_NAME RENAME COLUMN COL_2NAME TO COL_NAME2;
ALTER TABLE TB_NAME ADD CONSTRAINT CONST_NAME1 PRIMARY KEY(COL_NAME4);
ALTER TABLE TB_NAME ADD CONSTRAINT CONST_NAME2 FOREIGN KEY(COL_NAME5) REFERENCES TB_NAME2(COL_NAME5);
ALTER TABLE TB_NAME ADD CONSTRAINT CONST_NAME3 CHECK (COL_NAME5 > 1);
ALTER TABLE TB_NAME ADD CONSTRAINT CONST_NAME4 CHECK (COL_NAME4 IN ('F','M'));
ALTER TABLE TB_NAME DROP CONSTRAINT CONST_NAME3;
 

 

DROP, RENAME, TRUNCATE

RENAME TB_NAME TO TB_NEW_NAME;  -- 오라클
RENAME TABLE TB_NAME TO TB_NEW_NAME;  -- MYSQL
TRUNCATE TABLE TB_NEW_NAME;
DROP TABLE TB_NEW_NAME;