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

1. with문
- 쿼리 내 임시테이블을 만드는 문법(해당 쿼리에서만 유효, 실제 저장되지는 X)
- 가독성을 높이기 위해 사용
- select 문에서 주로 사용(dml에서도 사용 가능)


** 문법
with 임시테이블명(컬럼1, 컬럼2, ...)
as
select ....;      -- 저장하고자 하는 데이터 형태 정의
sql 문장...         -- 임시테이블을 사용하여 처리할 sql문 전달



예제)
with std_test(name, grade, height)
as
(select name, grade, height
  from student
 where grade =4)
 
 
select * from std_test;
;

 

예제) student; exam_01을 조인하여 학번, 이름, 학년, 시험성적에 대한 데이터를 with문을 사용하여 
임시테이블로 설정, 임시테이블을 사용하여 각 학년별 최고시험성적 출력

with std_exam(studno, name, grade, total)
as
(select s.studno, s.name, s.grade, e.total
   from student s join exam_01 e 
     on e.studno = s.studno)
     
select grade,max(total) 최고성적 
  from std_exam
 group by grade;





2. ALTER
1) 컬럼 추가
- 맨 마지막에 추가
- 기존에 데이터가 있을경우 새로 추가된 컬럼의 데이터는 null로 입력됨
- 기존에 데이터의 새로 추가된 컬럼의 데이터를 null이 아닌 값으로 설정 시 default 값 선언 필요
- 동시에 여러 컬럼 추가 가능*(반드시 괄호 사용)

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

2) 컬럼 삭제
- 여러 컬럼 동시 삭제 불가
- 즉시 확정(rollback 불가)
- recyclebin에 보관 X -> 복원 불가
- 데이터 유무 상관없이 항상 삭제 가능
** 문법
alter table 테이블명 drop column 컬럼명;

 

예제)
select * from test1;
alter table test1 drop column 상호명, 매출;      --error
alter table test1 drop column (상호명, 매출);    --error
alter table test1 drop column 매출;            -- 정상

select * from user_recyclebin;

 

3) 컬럼 변경 ***
- 사이즈, 데이터타입, default값

** 문법
alter table 테이블명 modify (컬럼명 데이터타입 [default값]);    --괄호 생략 가능
alter table 테이블명 modify (컬럼1 데이터타입 [default값],
                         (컬럼2 데이터타입 [default값],
                          ....);                      -- 괄호 생략 불가

3-1) 사이즈 변경
- 데이터 사이즈를 크게 변경하는 것은 항상 가능
- 저장된 데이터보다 작은 사이즈로의 축소 불가!

 

예) test1 테이블의 지역1,지역2 컬럼 사이즈 10 -> 15
desc test1;
alter table test1 modify 상호명 VARCHAR2(15);     --정상
alter table test1 modify (상호명 VARCHAR2(18));   --정상
alter table test1 modify 지역1 CHAR(10), 지역2 VARCHAR2(10);     --에러
alter table test1 modify (지역1 CHAR(10), 지역2 VARCHAR2(10));   --정상

예) test1 테이블의 지역1 컬럼 사이즈 5로 변경(축소)
alter table test1 modify 지역1 CHAR(5);     --에러
 => char(10) 이었던 지역1 컬럼의 데이터는 뒤에 공백이 붙어 총 10byte로 저장되어 있으므로 저장된 데이터보다 작은 사이즈로의 축소 불가!

select * from test1;서울



3-2) 데이터 타입 변경
- 비어있지 않은 컬럼의 데이터 타입 변경(비어있는 컬럼에 대해 데이터 타입 변경 가능)
- 비어있지 않아도 char <-> varchar2로의 변경 가능

예제) test1에 기존 데이터에 100의 값을 갖는 숫자 타입의 CODE 컬럼 추가
alter table test1 add code number(5) default 100;
alter table test1 add code2 number(5);


desc test1;
select * from test1;

 

예제) test1 테이블의 code, code 컬럼의 데이터타입을 varchar2로 변경
alter table test1 modify code varchar2(10);         -- error(데이터 유형을 변경할 열은 비어 있어야 한다.)  

alter table test1 modify2 code varchar2(10);        -- 가능

 

 

예제) test1 테이블의 지역1 컬럼을 varchar2로, 지역2 컬럼의 데이터타입을 char로 동시 변경
alter table test1 modify (지역1 varchar2(10) , 지역2 char(10));  -- 가능(비어있지 않아도 char <-> varchar로의 변경 가능)




3-3) default값 변경
- default 값을 변경한 시점 이후에 입력되는 데이터에 대해서만 반영(기존 데이터는 변경되지 X)
- null값 삽입 시 default 값으로 입력되지 X(null은 null 자체로의 데이터로 인정되므로)

 

 

예제) test1의 code2 컬럼의 default 값을 200으로 변경
alter table test1 modify (code2 default 200);
select * from test1;

 => default 값을 변경한 시점 이후에 입력되는 데이터에 대해서만 반영, 이미 있는 데이터에 대해서는 적용되지 X 

 

 

예제) test1 테이블에 한 행의 데이터 삽입(삽입 시 code2 입력X)
insert into test1 values('강원','강원','AA',100);       --error (값의 수가 충분하지 않습니다.)
insert into test1 (지역1,지역2,상호명,code) values('강원','강원','AA',100);  -- 정상 / code2의 값이 200으로 입력됨.

 

 

예제) test1 테이블에 한 행의 데이터 삽입(code2 값은 null로 입력)
insert into test1 values('강원2','강원2','AAA',100,null); 

 

 

예제) test1 테이블의 code, code2 컬럼의 default값 삭제

** 각 컬럼의 default 값 확인
select table_name, column_name, data_default
  from user_tab_columns
 where table_name = 'TEST1';


alter table test1 modify (code default null, code2 default null);

 

 

3-4) 컬럼명 변경
- 항상 가능
- 괄호 전달 불가
- 동시에 여러 컬럼 이름 변경 불가

** 문법
alter table 테이블명 rename column old_name to new_name;

예제) test1 테이블의 상호명 컬럼 이름을 상점명으로 변경

alter table test1 rename column 상호명 to 상점명;
select * from test1;

 

 

** 테이블명(객체) 변경
rename old_name to new_name;


rename test1 to test10;
select * from test1;
select * from test10;

 

3. truncate
- 테이블 구조는 남기고 데이터만 모두 삭제
- 일부 데이터 삭제 불가

예제) delete 와 truncate의 차이
select * from test10;
delete from test10;
rollback;
select * from test10;      --데이터 다시 복구됨

 

 

truncate table test10;
rollback;
select * from test10;      --데이터 영구 삭제됨