[oracle] 무결성 제약조건, JOIN, 서브쿼리

제약 조건

PRIMARY KEY

FOREIGN KEY

 

컬럼 레벨 방식으로 제약 조건 지정하기

//CONSTRAINT [테이블명]_[컬럼명]_[제약 조건 유형]

CREATE TABLE EMP05(

 EMPNO NUMBER(4) CONSTRAINT EMP05_EMPNO_PK PRIMARY KEY

);

 

테이블 레벨 방식으로 제약 조건 지정하기

1, 복합 키로 기본 키를 지정할 경우

 - 2개 이상의 컬럼이 하나의 기본 키를 구성하는 경우

2. 복합 키는 반드시 테이블 레벨 방식

3. NOT NULL 조건은 반드시 컬럼 레벨 방식

 

//CONSTRAINT를 사용하여 제약 조건명 지정하기
CREATE TABLE EMP04(
 EMPNO NUMBER(4),
 ENAME VARCHAR2(10) CONSTRAINT EMP04_ENAME_NN NOT NULL,
 JOB VARCHAR2(9),
 DEPTNO NUMBER(4),

 CONSTRAINT EMP04_EMPNO_PK PRIMARY KEY(EMPNO),
 CONSTRAINT EMP04_JOB_UK UNIQUE(JOB),
 CONSTRAINT EMP04_DEPTNO_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
);

//테이블 딕셔너리
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP04';

 

제약 조건 추가하기

NOT NULL은 ADD대신 MODIFY키워드를 사용한다.

 

제약 조건 제거하기

 

제약 조건의 비활성화

- 임시적으로 제약 조건을 사용하지 않게 하는 것

1. 상위 테이블을 삭제한 후, 하위 테이블을 삭제한다

2. 테이블의 외래 키 제약 조건을 제거 후 삭제한다,

 

제약 조건의 활성화

1. DISABLE

2. ROLLBACK

 

CASECADE

- 상위 테이블과 하위 테이블 간의 참조 설정이 되어 있을 때,

상위 테이블이 비활성화이면, 하위 테이블도 비활성화를 만들어 주는 옵션

 

1. 부모 테이블의 기본 키를 참조하는 자식 테이블의 외래 키에 대한 제약 조건을 비활성화 해야 함

2. 부모 테이블의 기본 키에 대한 제약조건을 비활성

 

중복되는 데이터는 제1정규화를 무시하는 일..

소스도 가독성이 있어야 한다.

 

JOIN

- 여러 개의 테이블 중에서 필요한 데이터들을 가져오기 위해서 필요한 것

 

JOIN의 규칙

1. PRIMARY KEY와 FOREIGN KEY열을 통한 다른 테이블의 행과 연결

2. 연결 KEY 사용으로 테이블과 테이블이 결합한다.

3. WHERE 절에서 조인 조건을 사용한다.(조인 조건 갯수=연결 테이블 수 -1)

4. 명확성을 위해 컬럼 이름 앞에 테이블 명 또는 테이블 별칭을 붙인다.

->SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND E.ENAME='SCOTT';

 

Equi Join

- 공통적으로 존재하는 컬럼의 값 일치되는 행을 연결하여 결과를 생성하는 조인 방법

- FOREIGN KEY로 관계를 설정해 공통적인 컬럼으로 만든다.

->SELECT ENAME, DNAME
FROM EMP, DEPT
WHERE EMP, DEPTNO=DEPT.DEPTNO;

Non-Equi Join

- 조인 테이블 사이에 컬럼 값이 직접적으로 일치하지 않을 때

Outer Join

- 조인 조건에 만족하지 않는 행들도 나타내기 위한 조인

Self Join

- 자기 자신과 조인을 맺는 것

->SELECT WORK.ENAME ||'의 매니저는' || MANAGER.ENAME || '이다' AS "그 사원의 매니저"
FROM EMP WORK, EMP MANAGER
WHERE WORK.MGR=MANAGER.EMPNO;

 

ANSI Join

- CROSS JOIN

->SELECT * FROM EMP, DEPT;

==>SELECT * FROM EMP CROSS JOIN DEPT;

 

- INNER JOIN : 공통 컬럼을 '=' 통해 같은 값을 가지는 로우를 연결하여 결과를 구하는 형태

->SELECT ENAME, DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO

AND ENAME='SCOTT';

==>SELECT ENAME, DNAME

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO

WHERE ENAME = 'SCOTT';

 

- OUTER JOIN : 다른 테이블에 있는 것을 출력

[LEFT/RIGHT/FULL ]

-> 왼쪽에 데이터가 없으면 RIGHT으로 JOIN, 오른쪽에 데이터가 없으면 LEFT로 JOIN

->SELECT *

FROM DEPT01, DEPT02

WHERE DEPT01.DEPTNO = DEPT02.DEPTNO(+);

//왼쪽의 DEPT01이 출력된다

==>SELECT *

FROM DEPT01 LEFT OUTER JOIN DEPT02

ON DEPT01.DEPTNO=DEPT02.DEPTNO;

//오른쪽의 DEPT02가 출력된다

->SELECT *

FROM DEPT01 RIGHT OUTER JOIN DEPT02

USING(DEPTNO);

 

JOIN하는 과정

1. 출력하고자 하는 내용 확인

2. 각각의 컬럼들이 어느 테이블에 소속되어 있는 지 알아야 한다.

3. 관계 설정 확인 -> 공동 컬럼이 무엇인지 알아야 한다.

4. INNER/OUTER 설정

5. WHERE절, ANSI JOIN

HR계정에서 3개 이상의 테이블 JOIN 하기

- 두 SQL문은 결과가 똑같다. (D -> E <- J)

SELECT E.EMPLOYEE_ID, JOB_TITLE, DEPARTMENT_NAME
FROM EMPLOYEES E, JOBS J, DEPARTMENTS D
WHERE E.JOB_ID = J.JOB_ID
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID 
AND LAST_NAME='KING';

 

SELECT E.EMPLOYEE_ID, JOB_TITLE, DEPARTMENT_NAME
FROM EMPLOYEES E JOIN JOBS J
ON E.JOB_ID = J.JOB_ID
JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID 
AND LAST_NAME='KING';

 

서브 쿼리

- WHERE 절에 대한 결과값을 또 다른

단일 행 서브 쿼리

- 오직 하나의 행만을 반환, 단일 행 비교 연산자 사용

다중 행 서브 쿼리

- 결과가 2개 이상 구해지는 쿼리문을 서브 쿼리로 기술할 경우

IN 연산자

- 서브 쿼리의 결과 중에서 하나라도 일치하면 참인 결과를 구한다.

ALL 연산자

- 서브 쿼리의 검색 결과값 모두에 대해서 커야만 한다.(최대값 구하기) -> MAX 사용한다.

ANY 연산자

- ALL연산자의 반대 최소값을 구한다. -> MIN 사용한다

 

테이블 구조만 복사하기

CREATE TABLE EMP03

AS SELECT * FROM EMP WHERE 1=0;

 

서브 쿼리를 이용한 두 개 이상의 컬럼에 대한 값 변경

ex>UPDATE DEPT01

SET DNAME=(SELECT DNAME FROM DEPT01 WHERE DEPTNO=30), LOC=(SELECT LOC FROM DEPT01 WHERE DEPTNO=30) WHERE DEPTNO=20;

 

서브 쿼리를 이용한 데이터 삭제하기

ex>DELETE FROM EMP01

WHERE DEPTNO