DB練習-08

外部キー関係での制約の変更

サブempの外部キーを一時的に無効にする

-- emp1 테이블 생성
create table emp1 (
    empno number(4),
    ename varchar2(10) constraint emp1_ename_nn not null,
    job varchar2(9),
    deptno number(2),
    constraint emp1_empno_pk primary key(empno),
    constraint emp1_job_uk unique(job),
    constraint emp1_deptno_fk foreign key(deptno) references dept1(deptno) );

-- dept1 테이블 생성
create table dept1(
    deptno number(2) constraint dept1_deptno_pk primary key,
    dname varchar2(14),
    loc varchar2(13) );

-- 자료 입력
insert into dept1 values(10, '경리부', '서울');
insert into dept1 values(20, '인사부', '인천');
insert into dept1 values(30, '전산부', '수원');
insert into dept1 values(40, '영업부', '용인');

-- dept1에는 50 값이 없는데, emp1에서 50을 넣으려하니 오류발생
insert into emp1 values(1000, '허준', '사원', 50);

-- 제약 조건을 일시적으로 비활성화 시켜서 허준을 넣고 싶다면?
-- 외래키 관계에 있던 걸(CONSTRAINT emp1_deptno_fk) 비활성화(disable) 하겠다
alter table emp1 disable CONSTRAINT emp1_deptno_fk;

-- 다시 입력해보니 외래키 관계가 없어져서 오류없이 입력된다.
insert into emp1 values(1000, '허준', '사원', 50);
insert into emp1 values(1001, '허영', '대리', 60);

-- emp1의 job 제약조건도 비활성화 시켜보자
alter table emp1 disable constraint emp1_job_uk;

-- job에 걸려있던 유일키 제약조건을 지웠기 때문에, '대리' 가 중복으로 입력 가능하게 되었다.
insert into emp1 values (1002, '허임', '대리', 70);

-- 비활성화 풀어서 다시 제약조건 활성화하기
alter table emp1 enable constraint emp1_deptno_fk;

-- 위처럼 그냥 켜면 이미 값으로 들어가있는 중복 값이 걸려버린다.
delete from emp1; -- 깔끔하게 내용 모두 지워버리고

-- 제약 조건 다시 걸기
alter table emp1 enable constraint emp1_deptno_fk;
alter table emp1 enable constraint emp1_job_uk;

-- 현재 제약조건 확인하기 in ('테이블 명은 대문자로!')
select * from user_constraints where table_name in ('EMP1');

親dept側で無効化

-- dept1 의 구조 확인하기
select * from user_constraints where table_name in ('DEPT1');

-- dept1 쪽에서 비활성화 시키기
-- 1. 무작정 비활성화 시키려 하면 외래키 관계에 있는 자식 때문에 오류난다.
alter table dept1 disable constraint dept1_deptno_pk;
alter table dept1 disable primary key;

-- 2. cascade : 관계에 있는 것이 있을 경우 모두 -해주어라
alter table dept1 disable primary key cascade;
-- 제약 조건을 풀었으니 null 값이 허용되는지 확인
insert into dept1 (dname, loc) values ('개발부', '부산');
-- 제약 조건을 풀었으니 중복이 허용되는 지 확인
insert into dept1 values (10, '총무부', '대전');

サイン

cross join

-- 원래 자료 찾기
select * from emp where ename='감우성';
select dname from dept where deptno=30;

-- cross join
select * from student, member;
-- 이런식으로 하면 김사랑의 부서가 하나로 나올 거 같지만 제대로 안나온다
select empno, ename, dname from emp, dept where ename='김사랑';

equl join

-- equl join
select * from dept, emp where dept.deptno = emp.deptno;
select * from dept d, emp e where d.deptno = e.deptno and ename='김사랑';
-- 처리 순서가 from 을 확인하고 where 조건을 확인해서 select 의 것들을 order by 조건대로 출력할 것이다
select e.ename, d.dname, d.loc from dept d, emp e where d.deptno = e.deptno and loc in ('서울');

non-equl join

-- non-equal join
-- salgrade01 값 변경하기
update salgrade01 set losal = 70, hisal=120 where grade=1;
update salgrade01 set losal = 121, hisal=140 where grade=2;
update salgrade01 set losal = 141, hisal=200 where grade=3;
update salgrade01 set losal = 201, hisal=300 where grade=4;
update salgrade01 set losal = 301, hisal=500 where grade=5;
insert into salgrade01 values(6, 501, 700);
insert into salgrade01 values(7, 701, 1000);

-- 급여 등급 테이블 출력하기
select * from salgrade01;
select * from emp;

-- 검색하고 싶은것 : 사원, 급여, 급여등급
select e.ename, e.sal, s.grade from emp e, salgrade01 s where e.sal between s.losal and s.hisal;
select e.ename, e.sal, s.grade from emp e, salgrade01 s where e.sal >= s.losal and e.sal <= s.hisal;

-- 이름 순서대로 정렬하고 싶다
select e.ename, e.sal, s.grade from emp e, salgrade01 s where e.sal between s.losal and s.hisal order by ename;

-- 검색하고 싶은 것 : 사원 이름, 소속 부서 이름, 사원 급여 등급
select e.ename, d.dname, s.grade from emp e, dept d, salgrade01 s
    where e.deptno = d.deptno and e.sal between s.losal and s.hisal order by grade;
-- 검색하고 싶은 것 : 이름, 급여, 급여등급, 부서명
select e.ename, e.sal, s.grade, d.dname from emp e, dept d, salgrade01 s
    where e.deptno = d.deptno and e.sal between s.losal and s.hisal;

self join

-- 그냥 검색한다면 이런 식으로 검색 가능하다
select mgr, ename from emp where ename='김사랑';
select ename from emp where empno=1013;

-- 모호성을 없애기 위해 애칭 붙이기
select * from emp e1, emp e2;
-- 애칭으로 검색하기
select e1.ename 사원이름, e2.ename 사수이름 from emp e1, emp e2 where e1.mgr = e2.empno and e1.ename = '김사랑';

ANSI join

-- cross join
-- 오라클 sql.98 버젼의 join
select * from emp, dept;
-- anst join
select * from emp cross join dept;


-- 오라클 sql.98 버젼의 equal join
select * from emp e, dept d where e.deptno=d.deptno;
-- inner join
select * from emp e inner join dept d on e.deptno=d.deptno;
-- '이문세'의 부서명 조회해보기
select ename, dname from emp e inner join dept d on e.deptno=d.deptno where ename='이문세';


-- outer join
-- full outer join = 값 모두(합집합)
-- left-right join = 기준을 어디로 삼고 어떻게 join 할 것인가
select e1.ename 사원명, e2.ename 사수명 from emp e1 left outer join emp e2 on e1.mgr = e2.empno;