DDL , 뷰

DDL

1. unique 제약조건
   하나의 테이블에 unique 제약조건은 여러개 저장할 수 있음. null 값 저장가능.

• 문법: 
  create table department4
  (deptno number(2) constraint department4_deptno_pk primary key,
  dname varchar2(15) costraint department4_dname_uk unique,
  loc varchar2(15));
  
  
• insert into department4 (deptno, dname, loc) values (10, '개발', '서울');
  insert into department4 (deptno, dname, loc) values (20, '개발', '경기');
  -> 개발이 같아서 안들어감 기본키
  
  
• 테이블 레벨형
  create table department5
  (deptno number(2)constraint department5_deptno_pk primary key,
  dname varchar2(15) , loc varchar2(15),
  constraint department5_dname_uk unique(dname));

2. NOT NULL 제약 조건

create table department6

(deptno number(2) constraint department6_deptno_pk primary key,

dname varchar2(15) CONSTRAINT DEPARTMENT6_DNAME_UK UNIQUE,

LOC VARCHAR2(12) constraint6_loc_nn not null);

 

3. CHECK 제약 조건

 

• 조건에 일치하는 데이터만 
  in, and/or, 비교연산자와 함께 사용가능.
• create table into dept7
  (deptno number(2),
  dname number(2),
  dname varchar2(15) constraint dept7_dname_ck_check(dname in ('개발','인사'),
  loc varchar2(15) );
  
  
• insert into department7 (deptno, dname, loc) values (10, '개발','서울');
  insert into department7 (deptno, dname, loc) values (20, '개발','경기');
  → 개발 부로 들어가면 안됨. null 은 됨.
  
  
• create table department7 (deptno number(2) check (deptno > 50), dname varchar2(15) constraint department8_dname_ck check(dname in ('개발', '인사')), loc varchar2(15) );
  → deptno 가 50 미만으로만 가능하게 설정.
  
  

4. FOREGIN

 

• 참조무결성 : 상위테이블에 존재하는 외래키는
  반드시 상위테이블의 기본키가 가지고 있는 기본키 값 또는 널값만 가질 수 있다.
  
  
• insert into emp (empno, ename, deptno) values(9000, 'john',50);
  -> 50번 부서는 존재하지 않아 저장이 안됨.
  
  
• 이때 참조하는 부모테이블은 pk나 uk로 만들어야
  부모테이블을 만들과 자식테이브을 만들기
  삭제할 때도 부모보다 자식먼저
  
  
• create table dept02
  (deptno number(2) constraint dept02_deptno_pk primary key,
  dname varchar2(15),
  loc varchar2(15));
  
  
  
• insert into dept02(deptno, dname, loc) values (10,'인사','서울');
  insert into dept02(deptno, dname, loc) values (20,'개발','광주');
  insert into dept02(deptno, dname, loc) values (30,'관리','부산');
  insert into dept02(deptno, dname, loc) values (40,'영업','경기');
  commit;
• create table emp02
  (empno number(4) constraint emp02_empno_pk primary key,
  ename varchar2(15),
  deptno number(2) constraint dept02_deptno_fk references dept02(deptno));
  
  
• insert into emp02 (empno,ename,deptno)values (1000, 'JOHN', 10);
  insert into emp02 (empno,ename,deptno)values (2000, 'SMITH', 20);
  insert into emp02 (empno,ename,deptno)values (3000, 'SAM', NULL);
  insert into emp02 (empno,ename,deptno)values (4000, 'MIKE', 50);
  commit;
  
  → null 값은 저장되지만 50번 부서는 없는 부서라 저장이 안되고 에러가 남.
  → emp 테이블에 저장되지 않은 부서번호가 있는 30번 부서는 dept02에서 삭제 가능.
  
  
• 테이블 레벨방식 foregin 키 제약조건
  create table emp03
  (empno number(4) constraint emp03_empno_pk primary key,
  ename varchar2(15),
  deptno number(2)
  constraint emp03_deptno_fk foreign key (deptno) references dept02(deptno));
  
  insert into dept02(deptno, dname, loc) values (10,'인사','서울');
  insert into dept02(deptno, dname, loc) values (20,'개발','광주');
  insert into dept02(deptno, dname, loc) values (30,'관리','부산');
  insert into dept02(deptno, dname, loc) values (40,'영업','경기');
  commit;
  
  
• foregin 키 제약조건 추가.
  
  delete from dept02 where deptno =10;
  →삭제안됨.
  
  1) on cascade
  
  create table emp02 (empno number(4) constraint emp02_empno_pk primary key ,
  ename varchar2(15), deptno number(2),
  constraint emp02_deptno_fk foreign key (deptno) references dept02(deptno) on delete cascade );
  → 참조되는 부모 테이블의 행이 삭제되면 해당 행을 참조하는 자식 테이블의 행도 함께 삭제.
  insert into emp02 (empno,ename,deptno)values (9000, 'aa', 10);
  insert into emp02 (empno,ename,deptno)values (9001, 'bb', 50);
  select * from emp02;
  commit;
  
  delete from dept02 where deptno =50;
  
  create table emp02 (empno number(4) constraint emp02_empno_pk primary key,
  ename varchar2(15), deptno number(2), constraint emp02_deptno_fk foreign key (deptno) references dept02(deptno) on delete set null);
  → 참조자식 테이블컬럼값 널.
  
  

테이블 삭제

drop table 테이블명 [cascade constraints];
deop table dept02 [cascade constraints];
-> 참조당하는 부모테이블은 삭제가 안된다 따라서 cascade constraints 옵션을 사용하여 삭제.

테이블 변경

alter
추가삭제 타입길이변경 제약조건추가삭제

create table emp04 as select* from emp;
->구조만 가져옴. 기본키 설정 없음.

1. 컬럼 추가
   
   alter table 테이블명
   add (컬럼명 데이터타입 [, 컬럼명 데이터타입]);
   -> 컬럼은 마지막 테이블에 추가되고 null 값으로 저장됨.
   
   alter table emp04 add (email varchar2(10) , address varchar2(20));
   -> email, address 추가
   
   
2. 컬럼변경 중요 ★ ★ ★ ★ ★
   
   alter table 테이블명
   modify (컬럼명 데이터타입[,컬럼명 데이터타입]);
   alter table emp04 modify (email varchar2(40));
   
   
3. 삭제
   
   alter table emp04 drop (email);
   create table dept03
   (deptno number(2), dname varchar2(15), loc varchar2(15));
   
   alter table dept03 modify (dname varchar2 (15) constraint dept03_dname_nn not null);
   
   select table_name , constraint_type, constraint_name, r_constraint_name
   from user_constraints where table_name in ('DEPT03');
   
   
4. 추가
   
   alter table dept03
   add costraint dept03_deptno_pk primary key(deptno));
   
5. 삭제
   
   alter table dept03
   drop constraint dept03_deptno_pk;
   
   1) cascade 옵션
   
   alter table dept05 drop primary key cascade;
   -> 기본키 삭제
   
   select table_name, constraint_type, constraint_name,
   r_constraint_name from user_constraints
   where table_name in ('DEPT05','EMP05');
   →제약조건이 제거 확인
   
   
6. 제약조건 활성화/비활성화
   
   alter table emp05
   disable constraint emp05_empno_pk;
   -> emp05 테이블 비활성화
   
   select table_name, constraint_type,
   constraint_name, status
   from user_constraints
   where table_name in ('emp05');
   -> 제약조건이 비활성된 것을 확인
   
   alter table emp05
   enable constraint emp05_empno_pk;
   ->재활성화

뷰

가상테이블
뷰의 기반이되는 테이블은 기본테이블
목적: 민감데이터접근막음, 복잡한 질의문을 짧게

문법:
CREATE [OR REPLACE] VIEW 뷰이름 [(alias[,alias] ...)]
AS 서브쿼리
[WITH CHECK OPTION [CONSTRAINT 제약조건명]]
[WITH READ ONLY [CONSTRAINT 제약조건명] ];

create view emp_view
as
select empno, ename,d.dname, d.deptno
from emp e join dept d
on e.deptno= d.deptno
where e.deptno= 20;
-> emp_view 라는 테이블이 만들어짐

 

1. 뷰 수정
   create or replace
   뷰가존재하면 덮어쓰기가 됨
   
   create or replace view emp_view2
   as 
   select empno,ename, job, mgr, comm, deptno
   from emp;
   
   
2. 뷰종류
   1) 단순 뷰
   insert update delete 같은 dml 문 사용가능
   
   create view emp_view3(사원번호, 이름, 월급)
   as 
   select empno, ename, sal
   from emp
   where dept =20;
   → 별칭부여.