[DDL] 명령어를 활용한 테이블 정의

데이터 정의어(DDL, Data Definition Language)

 

• 데이터 정의어는 데이터를 정의하는 언어로서 '데이터를 담는 그릇을 정의하는 언어' 이다.
• 테이블과 같은 데이터 구조를 정의하는 데 사용되는 명령어들로 특정 구조를 생성, 변경, 삭제 , 이름을
  바꾸는 데이터 구조와 관련된 명령어들을 데이터 정의어라고 부른다
• DDL 명령어는 데이터베이스에 즉각 반영되며 데이터 사전(Data Dictionary) 에 기록
• 데이터베이스 객체

 

- DDL 명령어

구분	명령어	설명
생성	CREATE	데이터베이스 오브젝트 생성
수정	ALTER	데이터베이스 오브젝트 변경
삭제	DROP	데이터베이스 오브젝트 테이블 삭제
	TRUNCATE	데이터베이스 오브젝트 내용 삭제

---

- CREATE TABLE

 

• 새로운 테이블 생성

CREATE TABLE 테이블 이름  -- 테이블 이름 중복 불가
( 열 이름 1 데이터 타입,   -- 테이블 내 열 이름 중복 불가
  열 이름 2 데이터 타입,   -- 데이터베이스 예약어 사용 불가
  ...,                   -- 1 ~ 30 바이트 영어이름을 권장
  열 이름 N 데이터 타입);

 

CREATE TABLE customers
(
    customer_id     number          NOT NULL        PRIMARY KEY,
    first_name      varchar2(10)    NOT NULL,
    last_name       varchar2(10)    NOT NULL,
    email           varchar2(10),
    phone_number    varchar2(20),
    regist_date     date
);

SELECT *
FROM customers;

INSERT INTO customers
VALUES (1, 'Minkyu', 'Cheon', 'jake', '010-1234-1234', '23/03/04');

INSERT INTO customers
VALUES (2, 'Elon', 'Musk', 'elon', '010-1111-1111', '23/01/01');

INSERT INTO customers
VALUES (3, 'Steve', 'Jobs', 'steve', '010-2222-2222', '23/02/02');

INSERT INTO customers
VALUES (4, 'Bill', 'Gates', 'bill', '010-3333-3333', '23/03/03');

INSERT INTO customers
VALUES (5, 'Mark', 'Zuckerberg', 'mark', '010-4444-4444', '23/03/04');

---

- ALTER TABLE

 

• 테이블에 열 추가(ALTER TABLE ADD)

ALTER TABLE 테이블 이름              -- 새로운 열 추가 가능
ADD ( 열이름1      데이터 타입,       -- 기존 열 제거 불가능
      열이름2      데이터 타입,       -- 열의 위치 지정 불가능
      ...,                          -- 새로운 열의 데이터 값은 null
      열이름N      데이터 타입
    );

 

ALTER TABLE customers
ADD ( gender varchar2(10) );

ALTER TABLE customers
ADD ( age number );

ALTER TABLE customers
ADD ( dob date );

SELECT *
FROM customers;

 

UPDATE customers
SET gender = 'male', age = 35, dob = '88/02/25'
WHERE customer_id = 1;

UPDATE customers
SET gender = 'male', age = 51, dob = '71/06/28'
WHERE customer_id = 2;

UPDATE customers
SET gender = 'male', age = 56, dob = '55/02/24'
WHERE customer_id = 3;

 

---

•  테이블의 열(Column)에 제약조건 추가 (ALTER TABLE ADD CONSTRAINT)

ALTER TABLE 테이블 이름
ADD CONSTRAINT 제약조건 이름
CHECK ( 조건 지정 );

ALTER TABLE customers
ADD CONSTRAINT AK_email
UNIQUE (email);

ALTER TABLE customers
ADD CONSTRAINT AK_phone
UNIQUE (phone_number);

ALTER TABLE customers
ADD CONSTRAINT CK_age
CHECK (age >= 0);

 

---

• 테이블의 열(Column) 수정 (ALTER TABLE MODIFY)

ALTER TABLE 테이블 이름
MODIFY ( 열이름1   데이터타입,
         열이름2   데이터타입,
         ...,
         열이름N   데이터타입
        );

 

UPDATE customers
SET first_name = 'Steven Paul', gender = 'male', age = 50, dob = '50/01/01'
WHERE customer_id = 3;

--명령의 1 행에서 시작하는 중 오류 발생 -
--UPDATE customers
--SET first_name = 'Steven Paul', gender = 'male', age = 50, dob = '50/01/01'
--WHERE customer_id = 3
--오류 보고 -
--ORA-12899: "HR"."CUSTOMERS"."FIRST_NAME" 열에 대한 값이 너무 큼(실제: 11, 최대값: 10)

 

ALTER TABLE customers
MODIFY ( first_name varchar2(30) );

UPDATE customers
SET first_name = 'Steven Paul', gender = 'male', age = 50, dob = '50/01/01'
WHERE customer_id = 3;

--1 행 이(가) 업데이트되었습니다.

ALTER TABLE customers
MODIFY ( last_name varchar2(30) );

ALTER TABLE customers
MODIFY ( email varchar2(30) );

ALTER TABLE customers
MODIFY age DEFAULT 0;

UPDATE customers
SET first_name = 'William Henry', gender = 'male', age = 40, dob = '89/01/01'
WHERE customer_id = 4;

INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES(6, 'Lawrence Edward', 'Page', 'larry');

 

---

• 테이블의 열(Column) 이름 변경 (ALTER TABLE RENAME)

ALTER TABLE 테이블이름
RENAME COLUMN 열이름 TO 변경할 열이름;

 

ALTER TABLE customers
RENAME COLUMN phone_number TO phone;

ALTER TABLE customers
RENAME COLUMN gender TO sex;

ALTER TABLE customers
RENAME COLUMN dob TO date_of_birth;

SELECT *
FROM customers;

---

• 테이블의 열(Column) 및 제약조건(Constraint) 삭제 (ALTER TABLE DROP COLUMN/CONSTRAINT)

ALTER TABLE 테이블이름
DROP COLUMN 열 이름;

ALTER TABLE 테이블이름
DROP CONSTRAINT 제약조건;

 

ALTER TABLE customers
DROP COLUMN date_of_birth;

ALTER TABLE customers
DROP CONSTRAINT age;

ALTER TABLE customers
DROP COLUMN sex;

SELECT *
FROM customers;

 

---

• 테이블의 모든 데이터 삭제 (테이블 구조 유지) / (TRUNCATE TABLE)

TRUNCATE TABLE 테이블이름;

 

TRUNCATE TABLE customers;

--Table CUSTOMERS이(가) 잘렸습니다.

SELECT *
FROM customers;

---

• 테이블 완전 삭제 (DROP TABLE)
  - 테이블의 모든 데이터 뿐만 아니라 구조도 제거
  - 모든 인덱스와 제약 조건도 삭제

DROP TABLE 테이블이름;

DROP TABLE customers;

--Table CUSTOMERS이(가) 삭제되었습니다.

SELECT *
FROM customers;

--ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
--00942. 00000 -  "table or view does not exist"
--*Cause:    
--*Action:
--6행, 6열에서 오류 발생

---

- 테이블 데이터 삭제 명령어 비교

DELETE TABLE	TRUNCATE TABLE	DROP TABLE
- DML 명령 - 데이터만 삭제 - 디스크 사용량 초기화 불가	- DDL 명령 - 테이블 구조 유지 - 데이터 삭제 - 데이터, 인덱스, 테이블 공간 삭제 - 디스크 사용량 초기화 가능	- DDL 명령 - 테이블 완전 삭제 - 데이터, 인덱스, 테이블 공간 삭제 - 디스크 사용량 초기화 가능

DELETE	TRUNCATE	DROP
DML	DDL	DDL
Commit 이전 Rollback 가능	Rollback 불가	Rollback 불가
사용자 Commit	Auto Commit	Auto Commit
데이터를 모두 Delete해도 사용했던 Storage는 Release 되지 않음	테이블이 사용했던 Storage 중 최초 테이블 생성시 할당된 Storage만 남기고 Release	테이블이 사용했던 Storage를 모두 Release
데이터만 삭제	테이블을 최초 생성된 초기 상태로 만듬	테이블의 정의 자체를 완전히 삭제함

---

Delete(/Modify) Action : Cascade, Set Null, Set Default, Restrict
Cascade	Master 삭제 시 Child 같이 삭제
Set Null	Master 삭제 시 Child 해당 필드 Null
Set Default	Master 삭제 시 Child 해당 필드 Default 값 설정
Restrict	Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용
No Action	참조 무결성을 위반하는 삭제/수정 액션을 취하지 않음

Insert Action : Automatic, Set Null, Set Default, Dependent
Automatic	Master 테이블에 PK가 없는 경우 Master PK를 생성 후 Child 입력
Set Null	Master 테이블에 PK가 없는 경우 Child FK를 Null 값으로 처리
Set Default	Master 테이블에 PK가 없는 경우 Child FK를 지정된 기본값으로 입력
Dependent	Master 테이블에 PK가 존재할 때만 Child 입력 허용
No Action	참조 무결성을 위반하는 입력 액션을 취하지 않음