#11 11일차_DDL : CREATE, DROP, TRUNCATE, ALTER | 제약조건

11일차에 배운 내용을 정리하였다.

목차는 다음과 같다. :

 

01. DDL / DML / TCL / DCL

02. CREATE

03. DROP

04. TRUNCATE

05. ALTER

 -01 컬럼 추가

 -02 컬럼 변경

06. 제약조건

 -01 PRIMARY KEY

 -02 UNIQUE

 -03 NOT NULL

 -04 CHECK

 -05 FOREIGN KEY

 -06 제약조건의 생성

---

 

01. DDL / DML / TCL / DCL

 -01 ddl(data definition language) : create, drop, alter, truncate(전체데이터삭제)

       - 데이터 정의 언어

       - 객체(테이블, 인덱스, 제약조건, 뷰 등) 생성/변경/삭제

       - 자동 저장되므로 취소할 수 없음 (이전 작업 내용들도 함께 저장)
       - auto commit (자동저장)
 -02 dml(data manage language) : insert, update, delete, merge
 -03 tcl(transaction control language) : commit, rollback
 -04 dcl(data control language) : grant, revoke

---

02. CREATE

create table [소유자.]테이블명(
  컬럼1 데이터타입 [제약조건],
  컬럼2 데이터타입 [제약조건],
  컬럼3 데이터타입 [제약조건],
  ...);

 

 테이블 생성에 관련된 기능이며, 내가 아닌 다른 소유자의 테이블을 만들 시 소유자를 지정해야 한다. ( 이 경우 권한이 있어야 한다.)

 

 또한 테이블 명명 규칙은 다음과 같다. :

 - 숫자로 시작 불가

 - 띄어쓰기 불가

 - 특수기호삽입 불가 (_, $, #는 예외)

 - 예약어(create, table, from, where, order...) 사용 불가

 

예제) 

▶ scott 소유 테이블 생성

create table test1(no number, name varchar2(10));      -- scott 소유

insert into test1 values(1, '홍길동');
commit;

select * from test1;

 

▶ hr 소유 테이블 생성(scott계정으로 실행)

create table hr.test2(no number, name varchar2(10));    -- error

▶ hr 소유 테이블 생성(system 계정으로 실행)
create table hr.test2(no number, name varchar2(10));  -- 정상

▶ 올바른 테이블명 생성
create table 2test(no number, name varchar2(10));    -- error
create table te   s t(no number, name varchar2(10)); -- error
create table t@#$est(no number, name varchar2(10));  -- error
create table create(no number, name varchar2(10));   -- error
create table select(no number, name varchar2(10));   -- error
create table test_(no number, name varchar2(10));    -- 정상
create table test#(no number, name varchar2(10));    -- 정상
create table tesssssssssssssssssssssddddddst(no number, name varchar2(10));    -- error
create table aaaaaaaaaa(no number, name varchar2(10));    -- 정상 (10)
create table 아아아아아아아아아아(no number, name varchar2(10));    -- 정상 (20)
create table aaaaaaaaaaaaaaa(no number, name varchar2(10));    -- 정상 (15)
create table aaaaaaaaaaaaaaaaaaaa(no number, name varchar2(10));    -- 정상 (20)
create table aaaaaaaaaaaaaaaaaaaaaaaaa(no number, name varchar2(10));    -- 정상 (25)
create table "order"(no number, name varchar2(10));    -- 정상
create table aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa(no number, name varchar2(10));    -- 정상 (30)
create table aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa(no number, name varchar2(10));    -- error (31)

---

 

테이블 생성 시 데이터 타입에 따른 생성 문법은 다음과 같다.;

(1) 숫자
number : 자리수 제한 없음
number(n) : n자리 초과 삽입 불가
number(p,s) : 총 p자리수 중 소수점 자리 s (예 : number(5,2) -> 123.45)

(2) 문자
char(n) : 고정형 문자타입(입력하는 데이터 길이와 상관없이 항상 nbyte로 할당)
varchar2(n) : 가변형 문자타입(입력하는 데이터 길이만큼만 저장)

(3) 날짜
date : 시분초 출력
timestamp : 시분초 + 타임존 출력

---

 

테이블을 복제할 시 쿼리는 다음과 같다. :

 기존 테이블의 컬럼명, 컬럼순서, 데이터타입, 컬럼길이, NOT NULL 속성을 모두 복제하나, 제약조건은 복제되지 않는다.

*CTAS

create table 새 테이블명
as
select * from 원본테이블명;

create table emp_backup as select * from emp;

 

 

desc emp;

 

desc emp_backup; (복제된 테이블)

 

 

 복제된 테이블의 empno의 not null 조건은 복제되지 않았음을 확인할 수 있다.

이는 원본 테이블인 emp 테이블의 empno 열이 primary key로 지정되어 있기 때문에 null이 허용되지 않기 때문이며, CTAS를 통해 복제된 테이블은 primary key까지 복제하지 않기 때문에 제약조건을 복제하지 않는다.

---

 

 테이블 복제 시 데이터가 아닌 레이아웃만 복제하기를 원한다면 '항상 거짓' 조건을 통해 복제를 진행할 수 있다.

 

create table std_backup as select * from student where 1=2;

---

03. DROP

drop table 테이블명;

 

객체의 구조 및 데이터를 모두 삭제한다.

auto commit으로 명령어 실행 시 즉시 삭제가 진행되며, 삭제된 테이블은 recyclebin에서 복구 가능하다.

(만약 영구적인 삭제를 수행하고자 할 시 => drop table 테이블명 [purge];)

 

 - recyclebin 조회

select *
  from user_recyclebindrop;

 

 - recyclebin 내 테이블 복원

flashback table "bin$81wwstwjtkuokcakmxzn2w==$0" to before drop rename to test1;

 

 - 복구된 테이블 확인

select * from tab;

---

04. TRUNCATE

 truncate table 테이블명;

 

 구조는 삭제하지 않고 데이터만 전체 삭제한다.

auto commit으로 commit 없이 바로 삭제가 진행되며, rollback이 불가능하다.

drop에 비하여 recyclebin 내 기록이 남지 않는다.

 

	TRUNCATE	DELETE
데이터 삭제	데이터 전체	선택 가능
COMMIT	자동	수동
복원가능여부	불가	가능
구조	존재	존재
속도	매우 빠름	느림

---

05. ALTER

 -01 컬럼 추가

 alter table 테이블명 add (컬럼1 데이터타입 [제약조건], 컬럼2 데이터타입 [제약조건] ...);

 

 컬럼 추가, 컬럼 삭제, 데이터 타입 변경, 길이 변경 등 구조 변경을 수행한다.

단 하나의 컬럼만을 수정 시 괄호는 생략 가능하다.

컬럼 추가 시 새로 추가하는 컬럼의 위치는 지정할 수 없으며 가장 오른쪽에 추가된다.

 * 기존 데이터가 있는 테이블에 컬럼을 추가할 시 null이 삽입된다.

 

▶ 데이터가 없는 테이블에 새로운 컬럼 추가
alter table emp_backup add (컬럼1 number, 컬럼2 varchar2(10));

select * from emp_backup;

 

▶ 데이터가 있는 테이블에 새로운 컬럼 추가

alter table pro_backup add (컬럼1 number, 컬럼2 varchar2(10));

 

select *  from pro_backup;

 

 

 새로 추가된 컬럼1, 컬럼2에 null이 추가되었음을 확인할 수 있다.

---

 -02 컬럼 변경

alter table 테이블명 modify (컬럼1 데이터타입);

 

 (1) 데이터 타입 변경

alter table 테이블명 modify (컬럼명 데이터타입);

alter table emp_backup modify (empno varchar2(4));

- 데이터가 없는 경우(열이 비어있는 경우)는 모든 타입 변환 가능
- 데이터가 있는 경우(열이 비어있지 않은 경우)는 서로 다른 유형의 타입 변환은 항상 불가( 숫자 <-> 문자 <-> 날짜 등...)
- 데이터가 있는 경우(열이 비어있지 않은 경우)는 서로 호환되는 유형의 타입 변환은 가능(char <-> varchar2)

 

 (2) 데이터 길이 변경

alter table 테이블명 modify (컬럼명 데이터타입, 컬럼명 데이터타입...);

alter table emp_backup modify (empno varchar2(6), ename varchar2(5));

 

- 길이를 늘리는 것은 항상 가능
- 길이를 줄이는 것은 제약(기존 데이터가 있는 경우 데이터의 최대 길이보다 작게는 축소 불가)

 

 (3) 이름 변경

alter table table_name rename column old_name to new_name;

alter table pro_backup rename column pay to sal;

 

- 데이터 유무에 관계없이 언제나 가능

- 하나 번에 한 컬럼만 변경 가능

 

 (4) 컬럼 삭제

alter table 테이블명 drop column 컬럼명;

alter table test_sum1 drop column col2;

 

- 데이터 유무에 관계없이 항상 가능
- 한 번에 한 컬럼만 삭제 가능
- 즉시 저장, recyclebin에 남지 않는다 (위험한 명령어)

 

 (5) 테이블 이름 변경

raname a to b;

rename pro_backup to professor_backup; 

 

 (6) 기본값 설정

- 기본값 : 테이블의 테이터를 입력할 때, 값이 지정되지 않은 컬럼에 대해 자동으로 부여되는 값
- 기본값 설정 이전에 입력한 데이터에 대해서는 변경되지 않음 (기본값 설정 이후에 입력된 데이터에 대해서만 적용)
- 값을 null로 지정하는 경우는 기본값이 적용되지 않음

 

 ▶ 기본값 선언

alter table test_member modify (jdate date default sysdate);
select * from test_member;

 

 ▶ 기본값 변경

* 'null'로 하는 경우 무조건 null로 출력하겠다고 '지정'하는 것이므로 null이 된다.

 

alter table test_member modify (jdate date default sysdate-1);
insert INTO TEST_MEMBER VALUES(1002, '최길동', 'a4','02)333-3333','서울시',null);
insert INTO TEST_MEMBER(mid, mname) VALUES(1004, '김길동');

 

 ▶ 기본값 삭제

alter table test_member modify (jdate date default null);

---

06. 제약조건

데이터 무결성을 위해 각 컬럼마다 데이터의 입력, 수정, 삭제 등의 제약을 두는 객체

 

 -01 PRIMARY KEY (PK : 기본키)

**모델링 규칙: 각 테이블에는 각 행을 식별할 수 있는 유일한 식별자가 존재하며, 이것이 기본키이다.

 

  한 테이블에는 하나의 기본키가 존재하며, 최대한 변경되지 않고(불변성) 각 행을 식별할 수 있어야하고, 최소한의 컬럼으로 구성해야 한다(최소성).

 또한 하나의 pk를 구성하는 컬럼이 여러개일 수 있다 (pk가 두 개라는 것이 아님).

 primary key 설정 시 자동으로 index가 생성된다.

---

 -02 UNIQUE

 중복값이 삽입될 수 없도록 관리하는 제약조건으로 null의 삽입이 가능하다.

설정 시 자동으로 index가 생성된다.

---

 -03 NOT NULL

 null을 허용하지 않는 제약조건인 동시에 속성이다.

 유일하게 CTAS로 복사 가능한 조건이며, CTAS로 복제된 테이블의 경우 NOT NULL 속성이 그대로 따라간다.

 (다른 제약조건의 경우 복사되지 ㅇ낳는다.)

---

 -04 CHECK

각 컬럼의 허용 범위(도메인)를 설정하는 제약조건이다.

---

 -05 FOREIGN KEY

부모-자식간의 참조 관계를 형성하기 위한 제약조건이다.

자식의 매핑키에 fk를 설정하며, 부모에 존재하는 데이터로만 입력, 수정이 가능하다.

---

 -06 제약조건의 생성

(1) 테이블 생성 시 선언 가능
create table emp_backup2(
empno    number(4)      primary key,
ename    varchar2(10)   not null,
sal      number         check (sal >= 0),
hiredate date           default sysdate not null,
deptno   number(4));

select * from emp_backup2;

(2) 컬럼 추가 시 선언 가능
alter table emp_backup2 add (comm number check(comm >= 0));

(3) 이미 생성된 컬럼에 제약조건만 추가
create table dept_backup2(
deptno number(4),
dname  varchar2(10));

select * from dept_backup2;
-- 이 경우에만 컬럼명이 뒤에 명시됨
alter table dept_backup2 add primary key(deptno);
alter table dept_backup2 add not null(dname); -- error
alter table dept_backup2 modify (dname varchar2(10) not null); -- not null 제약조건만 컬럼수정형태로 처리

(4) foreign key 추가
alter table emp_backup2 add foreign key (deptno) references dept_backup2(deptno);

---