DB 연습하기 - 08

외래키 관계일 때 제약조건 변경하기

1. 일시적으로 제약조건 비활성화 시키고 변경 후 제약 조건 활성화시키기
   1-1. cascade 키워드로 관계 맺고 있는 모든 컬럼을 한꺼번에 비활성화시키기
2. 제약 조건을 영구적으로 지우고
   -> 조건을 삽입 / 삭제 후
   -> 제약조건을 재 지정하기

---

하위인 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, equal, non-equal, outer, self join

ex) 사원테이블에 있는 부서 번호에 대해, 정의된 부서 이름이 무엇인지 부서 테이블에서 정보를 얻어와야 할때 사용.

---

cross join

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

equal 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 : 두 테이블에서 -와 - 사이 값을 찾고싶다

-- 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

: 현재 대부분의 상용 데이터베이스 시스템에서 표준 언어이다
1. cross join
2. inner join
: from 다음에 inner join 이라는 단어를 사용하여 조인할 테이블명을 적어주고, on 을 사용하여 조인 조건을 명시
3. outer join
: full, left, right 3가지를 지원한다.

-- 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;