/* 선수 정보 */ 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로 분류
'IT > SQLD' 카테고리의 다른 글
[SQLD] SQL기본_GROUP BY, HAVING절 (0) | 2017.11.21 |
---|---|
[SQLD] 함수 (0) | 2017.11.20 |
[SQLD] SQL기본_TCL/WHERE절 (0) | 2017.11.20 |
[SQLD] 엔티티 (0) | 2017.10.20 |
[SQLD]데이터 모델링의 이해 (0) | 2017.06.16 |