[Oracle] 조인개요 및 작성법

KIC/ORACLE

[Oracle] 조인개요 및 작성법

octopengj 2020. 10. 12. 10:41

제약조건

pk,uk,ck,not null =>하나의 테이블에 설정이 가능

fk(외래키) =>관계형 데이터베이스의 테이블간 정의를 위해

한개 -> 두개로 만들어서 관리

모든 데이터- > 한테이블에 저장 가능(필드수가 25~)

** 단점=>저장하기 힘들고->검색속도가 느리다.

foreign key(외래키) -> 테이블에 값을 저장할려고 할때

특정 필드와 연결이 되어있는 다른 테이블의 필드값을 검사(존재 체크)

-> 값을 저장을 시켜준다.

-> 존재X-->저장X

참조(reference) 무결성 강화규칙에 위배

==(dept의 deptno)

테이블을 작성할때 제약조건을 한꺼번에 줄수 있다. -> 테이블 지정방식

SQL> create table dept

(deptno number primary key,

dname varchar2(20) not null,

loc varchar2(15) not null);

SQL> insert into dept values(10,'영업부','서울');

insert into dept values(20,'개발부','대전');

insert into dept values(30,'총무부','마산');

SQL> select * from dept;

SQL> create table dept
     (deptno number primary key,
      dname varchar2(20) not null,
      loc varchar2(15) not null);

SQL>insert into dept values(10,'영업부','서울');
     insert into dept values(20,'개발부','대전');
     insert into dept values(30,'총무부','마산');

SQL>select * from dept;

    DEPTNO DNAME                LOC
---------- -------------------- ---------------
        10 영업부               서울
        20 개발부               대전
        30 총무부               마산

sql>create table sawon(
     id number primary key,
     name varchar2(20) not null,
     deptno number);
    
sql>insert into sawon values(1,'홍길동',10);
     insert into sawon values(2,'테스트',40); =>삭제

1 개의 행이 만들어졌습니다.    =>엉터리 데이터저장

<문1>

형식) alter table 수정t명 add constraint 관계설정할 이름

foreign key(자기 테이블의 필드명) references

상대방 테이블명(연결시키고자하는 필드명)

= dept = deptno

sql> alter table sawon add constraint sawon_fk_deptno

foreign key(deptno)

references dept(deptno);

SQL> alter table sawon add constraint sawon_fk_deptno
  2        foreign key(deptno)
  3        references dept(deptno);
alter table sawon add constraint sawon_fk_deptno
                                 *
1행에 오류:
ORA-02298: 제약 (TEST1.SAWON_FK_DEPTNO)을 사용 가능하게 할 수 없음
 - 부모 키가 없습니다 =>40번 부서에 대한 데이터가 없기때문에 
                                fk키 설정을 할 수가 없다는 에러메세지

SQL> delete from sawon where id=2;


1 행이 삭제되었습니다.

SQL> ed
file afiedt.buf(이)가 기록되었습니다

  1* delete from sawon where id=2
SQL> alter table sawon add constraint sawon_fk_deptno
  2        foreign key(deptno)
  3        references dept(deptno);

테이블이 변경되었습니다.

SQL> insert into sawon values(2,'테스트22',20);//정상 OK
     insert into sawon values(3,'테스트3',900); //에러유발

insert into sawon values(3,'테스트3',900)
*
1행에 오류:
ORA-02291: 무결성 제약조건(TEST1.SAWON_FK_DEPTNO)이  위배되었습니다- 부모 키가 없습니다

<문2> 제약조건을 추가,삭제 -> 비활성화(기능정지), 활성화(기능 on)

=> 데이터 딕셔너리(=데이터 사전)에 저장

=> 테이블생성 -> DML -> 오라클의 특정테이블에 자동으로 저장(=시스템테이블)

1) user_XXX => 계정(test1)들이 조회(ex user_constraints에 저장)

2) dba_XXX => 관리자만 조회가 가능

3) all_XXX => 계정 O, 관리자 O

형식) select * from user_constraints=>필드갯수,데이터X

SQL> desc user_constraints

SQL> desc user_constraints

이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 CONSTRAINT_NAME(제약조건이름)                           NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE(제약조건 종류)                                    VARCHAR2(1)
 TABLE_NAME(제약조건이 설정된 테이블 이름)                               NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                   LONG
 R_OWNER                                            VARCHAR2(30)
 R_CONSTRAINT_NAME                                  VARCHAR2(30)
 DELETE_RULE                                        VARCHAR2(9)
 STATUS(제약조건 활성화상태)                                             VARCHAR2(8)
 DEFERRABLE                                         VARCHAR2(14)
 DEFERRED                                           VARCHAR2(9)
 VALIDATED                                          VARCHAR2(13)
 GENERATED                                          VARCHAR2(14)
 BAD                                                VARCHAR2(3)
 RELY                                               VARCHAR2(4)
 LAST_CHANGE                                        DATE
 INDEX_OWNER                                        VARCHAR2(30)
 INDEX_NAME                                         VARCHAR2(30)
 INVALID                                            VARCHAR2(7)
 VIEW_RELATED                                       VARCHAR2(14)

SQL>
  select constraint_name,constraint_type,table_name,
  status from user_constraints
  where table_name=upper('&table_name');
   
table_name의 값을 입력하십시오: b_prtest
구   3:   where table_name=upper('&table_name')
신   3:   where table_name=upper('b_prtest')

CONSTRAINT_NAME                C TABLE_NAME                     STATUS
------------------------------ - ------------------------------ --------
B_PRTEST_CK_AGE                C B_PRTEST                       ENABLED

ENABLED => 제약조건이 지금 작동중임을 표시
DISABLED => 제약조건이 지금 작동 off (임시로 중지)

SQL> save c:\webtest\2.db\print\consearch.txt =>자주 사용되는 SQL

<문3>제약조건이 활성화 <--> 비활성화

add/drop

형식) alter table 수정t명 disable constraint 비활성화시킬 제약조건이름

alter table 수정t명 enable constraint 활성화시킬 제약조건이름

20~65살 입력=>>=65 입력X

SQL> alter table b_prtest disable constraint b_prtest_ck_age;
테이블이 변경되었습니다    //  작동이 멈춘 상태

SQL> @c:\webtest\2.db\print\consearch.txt
table_name의 값을 입력하십시오: b_prtest
구   3:   where table_name=upper('&table_name')
신   3:   where table_name=upper('b_prtest')

CONSTRAINT_NAME                C TABLE_NAME                     STATUS
------------------------------ - ------------------------------ --------
B_PRTEST_CK_AGE                C B_PRTEST                       DISABLED

SQL> insert into b_prtest values(9,'임시22',170,'서울시',sysdate);

1 개의 행이 만들어졌습니다.

foeign key 설정 => 두 테이블 사이의 서로 관련이 있는 필드끼리 설정

sawon(deptno)<----------------dept(deptno)

부서번호

참조(확인해보고 데이터입력을 결정)

deptno(fk)------------------------deptno(pk)

필드명이 같아도 되고 달라된다. (default -> 같게 이름을 부여한다.)
두개의 연결된 필드는 자료형이 같아야 된다.(number <--> number)

<문4>조인? emp테이블에서 부서명을 알고 싶다. ALLEN이 근무한 부서명?

=> 한개 이상의 테이블을 가지고 검색 => 조인

emp(사원) <--> dept(부서 -> 부번, 부서이름, 부서위치)

형식) select 필드명1, 필드명2,,,

from 테이블1(emp), 테이블2(dept),,,

SQL> conn scott/tiger

SQL> select empno,ename,deptno,dname,loc from emp,dept;

SQL> select empno,ename,deptno,dname,loc from emp,dept;
select empno,ename,deptno,dname,loc
                   *
1행에 오류:
ORA-00918: 열의 정의가 애매합니다 // 서로 다른 테이블의 공통된 필드를 
                                  출력=>테이블명.필드명(emp.deptno)

조인(join) => 두 테이블에서 보고자하는 필드를 단순히 나열한다고 조인이되는것이 아니다.

(=>cross join) => 의미가 없다. dept(부서 10~40) 4개=56개, A(20,000) B(35,000)

조인의 개요 => 한 개이상의 테이블을 검색하면서 중복된 데이터가 없게 설정하는 방법(조인)

=>원하는 데이터를 검색

SQL> select empno,ename,dept.deptno,dname,loc from emp,dept;

   EMPNO ENAME          DEPTNO DNAME          LOC
---------- ---------- ---------- -------------- -------------
      7369 SMITH              10 ACCOUNTING     NEW YORK
      7499 ALLEN              10 ACCOUNTING     NEW YORK
      7521 WARD               10 ACCOUNTING     NEW YORK
      7566 JONES              10 ACCOUNTING     NEW YORK
      7654 MARTIN             10 ACCOUNTING     NEW YORK
      7698 BLAKE              10 ACCOUNTING     NEW YORK
      7782 CLARK              10 ACCOUNTING     NEW YORK
      7788 SCOTT              10 ACCOUNTING     NEW YORK
      7839 KING               10 ACCOUNTING     NEW YORK
      7844 TURNER             10 ACCOUNTING     NEW YORK
      7876 ADAMS              10 ACCOUNTING     NEW YORK

     EMPNO ENAME          DEPTNO DNAME          LOC
---------- ---------- ---------- -------------- -------------
      7900 JAMES              10 ACCOUNTING     NEW YORK
      7902 FORD               10 ACCOUNTING     NEW YORK
      7934 MILLER             10 ACCOUNTING     NEW YORK
      7369 SMITH              20 RESEARCH       DALLAS
      7499 ALLEN              20 RESEARCH       DALLAS
      7521 WARD               20 RESEARCH       DALLAS
      7566 JONES              20 RESEARCH       DALLAS
      7654 MARTIN             20 RESEARCH       DALLAS
      7698 BLAKE              20 RESEARCH       DALLAS
      7782 CLARK              20 RESEARCH       DALLAS
      7788 SCOTT              20 RESEARCH       DALLAS

     EMPNO ENAME          DEPTNO DNAME          LOC
---------- ---------- ---------- -------------- -------------
      7839 KING               20 RESEARCH       DALLAS
      7844 TURNER             20 RESEARCH       DALLAS
      7876 ADAMS              20 RESEARCH       DALLAS
      7900 JAMES              20 RESEARCH       DALLAS
      7902 FORD               20 RESEARCH       DALLAS
      7934 MILLER             20 RESEARCH       DALLAS
      7369 SMITH              30 SALES          CHICAGO
      7499 ALLEN              30 SALES          CHICAGO
      7521 WARD               30 SALES          CHICAGO
      7566 JONES              30 SALES          CHICAGO
      7654 MARTIN             30 SALES          CHICAGO

     EMPNO ENAME          DEPTNO DNAME          LOC
---------- ---------- ---------- -------------- -------------
      7698 BLAKE              30 SALES          CHICAGO
      7782 CLARK              30 SALES          CHICAGO
      7788 SCOTT              30 SALES          CHICAGO
      7839 KING               30 SALES          CHICAGO
      7844 TURNER             30 SALES          CHICAGO
      7876 ADAMS              30 SALES          CHICAGO
      7900 JAMES              30 SALES          CHICAGO
      7902 FORD               30 SALES          CHICAGO
      7934 MILLER             30 SALES          CHICAGO
      7369 SMITH              40 OPERATIONS     BOSTON
      7499 ALLEN              40 OPERATIONS     BOSTON

     EMPNO ENAME          DEPTNO DNAME          LOC
---------- ---------- ---------- -------------- -------------
      7521 WARD               40 OPERATIONS     BOSTON
      7566 JONES              40 OPERATIONS     BOSTON
      7654 MARTIN             40 OPERATIONS     BOSTON
      7698 BLAKE              40 OPERATIONS     BOSTON
      7782 CLARK              40 OPERATIONS     BOSTON
      7788 SCOTT              40 OPERATIONS     BOSTON
      7839 KING               40 OPERATIONS     BOSTON
      7844 TURNER             40 OPERATIONS     BOSTON
      7876 ADAMS              40 OPERATIONS     BOSTON
      7900 JAMES              40 OPERATIONS     BOSTON
      7902 FORD               40 OPERATIONS     BOSTON

     EMPNO ENAME          DEPTNO DNAME          LOC
---------- ---------- ---------- -------------- -------------
      7934 MILLER             40 OPERATIONS     BOSTON

56 개의 행이 선택되었습니다.

<문5> 조인의 종류

inner join(default) => 두 검색하고자하는 테이블의 양이 엇비슷할때 사용하는 검색방법

(ex 중소기업,대기업의 기준에 따라 다르다)

형식) select 필드명1,,테이블명1.필드명,,,

from 테이블1,테이블2,,,

where 조건식(emp.deptno=dept.deptno)
outer join => 한쪽의 테이블의 데이터가 많을때 사용하는 방법

=> 많은쪽의 테이블의 데이터를 무조건 다 나오게 설정하고

적은쪽의 테이블은 일치하는 데이터만 출력하게 설정

ed
  1  select empno,ename,dept.deptno,dname,loc
  2       from emp,dept
  3* where emp.deptno=dept.deptno
SQL> /

     EMPNO ENAME          DEPTNO DNAME          LOC
---------- ---------- ---------- -------------- -------------
      7369 SMITH              20 RESEARCH       DALLAS
     =>smith라는 사람이 부서번호가 20번인 research 라는 부서에 근무하고
        있으면 부서의 위치는 dallas다.

      7499 ALLEN              30 SALES          CHICAGO
      7521 WARD               30 SALES          CHICAGO
      7566 JONES              20 RESEARCH       DALLAS
      7654 MARTIN             30 SALES          CHICAGO
      7698 BLAKE              30 SALES          CHICAGO
      7782 CLARK              10 ACCOUNTING     NEW YORK
      7788 SCOTT              20 RESEARCH       DALLAS
      7839 KING               10 ACCOUNTING     NEW YORK
      7844 TURNER             30 SALES          CHICAGO
      7876 ADAMS              20 RESEARCH       DALLAS

     EMPNO ENAME          DEPTNO DNAME          LOC
---------- ---------- ---------- -------------- -------------
      7900 JAMES              30 SALES          CHICAGO
      7902 FORD               20 RESEARCH       DALLAS
      7934 MILLER             10 ACCOUNTING     NEW YORK

14 개의 행이 선택되었습니다.

'KIC > ORACLE' 카테고리의 다른 글

[Oracle] 뷰 (0)	2020.10.12
[ Oracle] 서브쿼리, 집합연산자 (0)	2020.10.12
[Oracle] 그룹함수, DDL, 테이블 제약조건 (0)	2020.10.12
[Oracle] 함수활용 (0)	2020.10.12
[Oracle] select를 이용한 검색방법 및 SQLPlus 명령어 사용법 (0)	2020.10.12

현재글[Oracle] 조인개요 및 작성법

기록하는 노트

패스트캠퍼스 #자바스크립트 #벨로퍼트, 패스트캠퍼스,

Today :
Yesterday :

기록하는 노트