[SQL] DDL - CREATE/ ALTER/ DROP

DDL(DATA DEFINITION LANGUAGE): 데이터 정의 언어

오라클에서 제공하는 객체(OBJECT)를
새로 만들고(CREATE) 구조를 변경하고 (ALTER) 구조자체를 삭제하는(DROP) 명령문

CREATE, ALTER, DROP!

객체의 값이 아닌,
구조자체를 정의하는 언어로 주로 DB관리자, 설계자가 사용

오라클에서의 객체(구조):
테이블(TALBE) , 뷰 (VIEW), 시퀀스(SEQUENCE), 인덱스, 패키지, 트리거, 프로시져, 함수, 동의어, 사용자 등..

 CREATE 

1. CREATE TABLE

테이블이란? : 행, 열로 구성되는 가장 기본적인 데이터베이스 객체 ( 자바의 클래스)
모든 데이터는 테이블을 통하여 저장됨 (데이터를 보관하고자 한다면 테이블을만들어야 함)

CREATE TABLE 테이블명 (
컬럼명 자료형,
컬럼명 자료형,
컬럼명 자료형
....
);

 

2. 오라클의 데이터타입 (자료형)

CHAR(바이트수)	-고정길이 문자  (아무리 적은 값이 들어와도 공백으로 채워서 처음 할당된 크기 유지)  - 최대 2000byte / 디폴트 값은 1byte  - 주로 들어올 값의 글자수가 정해져있는 경우 사용     (예: 성별: 남 / 여, M/F)  - 숫자, 영문자, 특수문자 => 1글자당 1BYTE    한글 => 1글자당 3BYTE
VARCHAR2(바이트수)	- 가변길이 문자 (지정 바이트보다 적은 값이 들어오면 그 값에 맞춰서 크기가 줄어듦)  - 최대 4000BYTE / 디폴트 값은 1byte
LONG	최대 2GB 크기의 가변길이 문자형
NUMBER	- 가변숫자 - 최대 22BYTE - 값의 범위 : 1.0e - 130 ~ 9.99999..9e125 -- 10^-130~ 10^126 - 정밀도 : 전체 자리수(1~38) - 스케일 : 소숫점이하의 자리수   (생략시 DEFALUT 0, 스케일 음수시 정수부분 반올림)
FLOAT	- NUMBER의 하위타입
DATE	연, 월, 일, 시, 분, 초 까지 입력 가능 '+', '-' 연산의 대상 * SYSDATE : 시스템이 제공하는 현 날짜정보를 제공하는 함수

< ALTER >

객체 구조를 수정하는 구문
수정내용: 
  [1] 컬럼 추가, 수정, 삭제
  [2] 제약조건 추가OR 삭제(제약조건 수정은 불가함, 삭제 후 새롭게 추가해야함),
  [3] 테이블명/컬럼명/제약조건명 수정

  [1] 컬럼 추가, 수정, 삭제

 ALTER TABLE  테이블명 수정할내용;
 
1)컬럼 추가 (ADD)
    ALTER TABLE 테이블명 ADD 추가할컬럼명 데이터타입 [DEFAULT] (생략가능)
    
2) 컬럼수정 (MODIFY) 
  - 데이터타입 수정 : 
    ALTER TABLE 테이블명 MODIFY 수정할컬럼명 바꾸고자하는 데이터타입; 
  - DEFAULT값 수정 : 
	ALTER TABLE 테이블명 MODIFY 수정할컬럼명  DEFAULT 바꾸고자하는 기본값;

3)컬럼삭제 
    ALTER TABLE 테이블명 DROP COLUMN 삭제하고자 하는 컬럼명;

SELECT * FROM DEPT_COPY;
--CNAME컬럼 추가

ALTER TABLE DEPT_COPY ADD CNAME VARCHAR2(20);
-- 새로 컬럼이 만들어지고 기본값 NULL로 채워짐

-- DEPT ID컬럼의 데이터타입을 CHAR(3)으로 변경 
ALTER TABLE DEPT_COPY MODIFY DEPT_ID CHAR(3);
--현재 변경하고자하는 컬럼에 이미 담겨있는 값과 완전히 다른타입으로는 변경불가
-- (문자 -> 숫자 불가, 문자열 사이즈 축소(x) :큰값을 작은값에 넣을 수 없어서, 문자열사이즈 확대 O) 

ALTER TABLE DEPT_COPY MODIFY DEPT_ID NUMBER; 
----column to be modified must be empty to change datatype 오류!
ALTER TABLE DEPT_COPY MODIFY DEPT_TITLE VARCHAR2(10); 
---cannot decrease column length because some value is too big 오류!

-- DEPT_TITLE컬럼의 데이터타입을 VARCHAR2(40)로, 
-- LOCATION_ID컬럼의 데이터타입을 VARCHAR2(4)로,
--LNAME 컬럼의 기본값을 미국으로 변경


ALTER TABLE DEPT_COPY MODIFY DEPT_TITLE VARCHAR2(40);
ALTER TABLE DEPT_COPY MODIFY LOCATION_ID VARCHAR2(4);
ALTER TABLE DEPT_COPY MODIFY LNAME DEFAULT '미국';

ALTER TABLE DEPT_COPY
MODIFY DEPT_TITLE VARCHAR2(40)
MODIFY LOCATION_ID VARCHAR2(4)
MODIFY LNAME DEFAULT '미국';

 

 * ALTER 테이블을 이용해 값을 바꿔도 실제 테이블 내 값(기존 값)은 바뀌지 않는다. 구조만 바뀔뿐!
CREATE TABLE DEPT_COPY3
AS SELECT * FROM DEPT_COPY;

ALTER TABLE DEPT_COPY3 DROP COLUMN DEPT_ID;
ROLLBACK;   * DDL 구문은 복구가 불가능하다
ALTER TABLE DEPT_COPY3 DROP COLUMN DEPT_ID;
ALTER TABLE DEPT_COPY3 DROP COLUMN DEPT_TITLE;
ALTER TABLE DEPT_COPY3 DROP COLUMN CNAME;
ALTER TABLE DEPT_COPY3 DROP COLUMN LNAME;

* 마지막 컬럼 삭제시도시 오류발생!: cannot drop all columns in a table
 테이블에 최소 한개의 컬럼은 남아있어야 한다!

[2] 제약조건 추가OR 삭제

<제약조건 추가>
NOT NULL : ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL;
UNIQUE:  ALTER TABLE 테이블명 ADD UNIQUE (ZJFFJAAUD)
CHECK: ALTER TABLE 테이블명 ADD CHECK (컬럼명)
PRIMARY KEY: ALTER TABLE 테이블명 ADD PRIMARY KEY(컬럼명)
FOREIGN KEY: ALTER TABLE 테이블명 ADD FOREIGN KEY(컬럼명) REFERENCES 참조테이블명(컬럼명

<제약조건 삭제>
PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK: ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명
NOT NULL : ALTER TABLE 테이블명 MODIFY 컬럼명 NULL 
   (NULL제약조건은 DROP 대신 NULL로 수정해야함)

 

<제약조건 추가>
ALTER TABLE DEPT_COPY
ADD CONSTRAINT DCOPY_PK PRIMARY KEY(DEPT_ID)
ADD CONSTRAINT DCOPY_DQ UNIQUE (DEPT_TITLE)
MODIFY LNAME CONSTRAINT DCOPY_NN NOT NULL;


<제약조건 삭제>
---DCOPY_PK 제약조건 지우기

ALTER TABLE DEPT_COPY DROP CONSTRAINT DCOPY_PK; -- 해당 제약조건명가진 친구가 날라감
ALTER TABLE DEPT_COPY MODIFY LNAME NULL; -- NULL제약조건은 DROP 대신 NULL로 수정해야함

 [3] 테이블명/컬럼명/제약조건명 수정(RENAME)

 

--- 컬럼명, 제약조건명, 테이블명 변경 (RENAME)
-- 3-1 컬럼명 변경: RENAME COLUMN 기존컬럼명 TO 바꿀컬럼명
ALTER TABLE DEPT_COPY RENAME COLUMN DEPT_TITLE TO DEPT_NAME; 

SELECT * FROM DEPT_COPY;
-- 3-2 제약조건명 변경: RENAME CONSTRAINT 기존제약조건명 TO 바꿀제약조건명
ALTER TABLE DEPT_COPY RENAME CONSTRAINT SYS_C0010798 TO DCOPY_98;

--3-3 테이블명 변경 RENAME TO 바꿀테이블명
ALTER TABLE DEPT_COPY RENAME TO DEPT_TEST;

< DROP>

객체를 삭제하는 구문
참조되는 자식객체를 가진 부모테이블은 삭제가 불가함
==> FOREIGN KEY로 참조되는 부모테이블을 지우려면,
      자식테이블을 먼저 삭제해야 부모테이블 삭제가능

-- 방법1: 자식, 부모테이블 순차삭제
DROP TABLE 자식테이블;
DROP TABLE 부모테이블;

-- 방법2: 부모테이블만 삭제하되 맞물려있는 제약조건도 함께 삭제
DROP TABLE 부모테이블 CASCADE CONSTRAINT;