19. 테이블 구조 생성, 변경, 제거 : DDL (1)

[ SQL 종류 ]
1. DDL(Data Definition language)
- 자동확정(auto commit)

1) CREATE : 객체(테이블) 생성
2) DROP : 객체(테이블) 삭제(구조를 삭제 -> 아예 없어짐)
3) ALTER : 객체(테이블) 변경(컬럼추가/컬럼삭제/데이터타입변경/사이즈변경,...)
4) TRUNCATE : 구조는 남기고 전체 데이터를 삭제(자동 저장됨, rollback 불가)

2. DML(Data Manipulation language)
- rollback 또는 commit을 사용하여 명령어 수행 완료 처리 필수!

1) INSERT : 한 행씩 입력
2) DELETE : 한 행씩 삭제
3) UPDATE : 셀 단위로 데이터 변경
4) MERGE : 특정 테이블을 참고하여 다른 테이블 변경(병합)

3. TCL(Transaction Control Language)
1) COMMIT
2) ROLLBACK

4. DCL(Data Control Language)
1) GRANT : 권한/롤 부여
2) REVOKE : 권한/롤 회수

5. DQL(Data Query Language)
-) SELECT

 

[ DDL ]
1. CREATE
- 객체(테이블, 인덱스, 유저, 뷰, ...) 생성 언어

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

 

예제) 테스트용 테이블 생성
create table test_1(
no      number,
name    varchar2(10));

 

** 데이터 타입 전달 시 주의사항
- 숫자타입은 데이터 사이즈 생략 가능
- 문자타입은 데이터 사이즈 생략 불가
- 날짜타입은 데이터 사이즈 전달 불가

 

 

2) 테이블 복제(CTAS)
- 이미 있는 테이블의 구조나 데이터의 전체 또는 일부를 복제
- CTAS 시 제약조건, 권한은 복제되지 X(일반 not null 속성은 복제됨)

 

예제) emp 테이블과 구조와 데이터가 동일한 emp_backup 테이블 생성
create table emp_backup
as
select * from emp;

 

 

예제) emp 테이블에서 10번 부서원의 사번, 이름, 입사일, 부서번호를 갖는 emp_backup2 테이블 생성
create table emp_backup2
as
select empno, ename, hiredate, deptno
  from emp
 where deptno = 10;

 

** 데이터 없이 구조만 복사
Create Table emp_backup
As
Select * 
  from emp
 where 1=2;              ----> 구조만 복사할 때 쓰는 방식


예제) student 테이블에서의 1,2학년 학생의 이름, 생년월일, 학년, 
    전화번호를 갖는 student_backup2 테이블 생성
create table student_backup2
as
(select name, birthday, grade, tel from student);

** student backup 테이블 생성
create table student_backup
as
select * from student;

desc student;
desc student_backup;  -- studno만 not null이 복제되지 않음
                      -- studno에 pk가 생성되어 있어 pk로 인해 생긴 not null은 복제되지 X
                      
                      

예제) TAB 테이블을 사용하여 조회되는 모든 테이블에 대해(BIN으로 시작하는 테이블 제외) 백업테이블 생성 스크립트 출력
'create table bonus_backup as select * from bonus;'
select 'create table '||tname||'_backup as select * from '||tname||';'
  from tab
 where tname not like 'BIN%'
   and tname != '100'
   and tname not like '%BACKUP';

2. DROP 
- 객체 삭제(삭제 후에는 조회되지 x)
- AUTO COMMIT(즉시 반영이므로 ROLLBACK으로 돌릴 수 없음)
- 삭제된 테이블은 recyclebin에 보관 -> flashback 명령어로 복원 가능
- purge 옵션을 사용하여 삭제한 테이블은 recyclebin에 보관 X 

 

** 문법
drop table 테이블명 [purge];

 

예제) emp_1, emp_2 생성 후 emp_1 purge 없이, emp_2 purge 옵션으로 테이블 삭제
create table emp_1 as select * from emp;
create table emp_2 as select * from emp;

 

select * from emp_1;
select * from emp_2;

 

drop table emp_1;
drop table emp_2 purge;

 

 recyclebin 조회
select * from user_recyclebin;

 

 recyclebin에 있는 테이블 복원
flashback table "BIN$JRXQ8rdxRQulcJYKpRwxQw==$0" to before drop rename to EMP_1;
select * from emp_1;

 

3. ALTER 
- 객체 변경
- 컬럼추가, 컬럼삭제, 컬럼 데이터타입변경, 컬럼 사이즈 변경, 컬럼 이름 변경, 컬럼 default값 변경, 제약조건 추가 및 삭제

1) 컬럼 추가
 - 맨 뒤에 위치(중간 위치로 추가 불가능)

** 명령어
alter table 테이블명 add (컬럼명 데이터타입 [default값][제약조건]);   -- 괄호 생략 가능
alter table 테이블명 add (컬럼1 데이터타입 [default값][제약조건],    -- 괄호 생략 불가(동시에 여러 컬럼 추가 시 필수)
                       컬럼2 데이터타입 [default값][제약조건]);


예제) emp_1에 col1, col2, col3 등의 컬럼 추가
desc emp_1;
select * from emp_1;

alter table emp_1 add col1 number;                      -- 정상
alter table emp_1 add col2 number, col3 varchar2(10);   -- 에러
alter table emp_1 add (col2 number, col3 varchar2(10)); -- 정상

예제) emp_1에 col4 추가 시 default 값 선언
alter table emp_1 add col4 date default sysdate;