[SQLD] SQL기본_DDL

/* 선수 정보 */
CREATE TABLE PLAYER (
   PLAYER_ID     CHAR (7) NOT NULL
 , PLAYER_NAME   VARCHAR2 (20) NOT NULL
 , TEAM_ID       CHAR (3) NOT NULL
 , BIRTH_DATE    DATE
 , WEIGHT        NUMBER (3)
 , CONSTRAINT PLAYER_ID_PK PRIMARY KEY (PLAYER_ID)
 , CONSTRAINT PLAYER_ID_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM (TEAM_ID) );

/* 팀 정보 */
CREATE TABLE TEAM (
   TEAM_ID       CHAR (3) NOT NULL
 , REGION_NAME   VARCHAR2 (8) NOT NULL
 , TEAM_NAME     VARCHAR2 (40) NOT NULL
 , STADIUM_ID    CHAR (3) NOT NULL
 , ORIG_YYYY    CHAR(4) 
 , CONSTRAINT TEAM_ID_PK PRIMARY KEY (TEAM_ID)
 , CONSTRAINT TEAM_ID_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM (STADIUM_ID) );

/* 운동장 정보 */
CREATE TABLE STADIUM (
   STADIUM_ID     CHAR (3) NOT NULL
 , STADIUM_NAME   VARCHAR2 (10) NOT NULL
 , CONSTRAINT STADIUM_ID_PK PRIMARY KEY (STADIUM_ID) );

CREATE TABLE TEAM_TEMP AS SELECT * FROM TEAM;
/* SELECT 문장을 통한 테이블 생성 */

ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80)); 
/* ADD COLUMN */

ALTER TABLE PLAYER DROP COLUMN ADDRESS;  
/* DROP COLUMN */

ALTER TABLE TEAM MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL); 
/* MODIFY COLUMN */

ALTER TABLE PLAYER RENAME COLUMN BIRTH_DATE TO BIRTH_DT; 
/* RENAME COLUMN */

ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_ID_FK;
/* DROP CONSTRAINT */

ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_ID_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID); 
/* ADD CONSTRAINT */

RENAME PLAYER TO PLAYERS; 
/* RENAME TABLE */

TRUNCATE TABLE PLAYERS; 
/* TRUNCATE TABLE */

 

※테이블에 들어있던 모든 행들을 제거하는 경우

1. DELETE

삭제된 로그 저장

DML로 분류

 

2. TRUNCATE

삭제된 로그 없으므로 ROLLBACK이 불가능

시스템 부하가 적음

Auto Commit 특성으로 인해 DDL로 분류