Oracle 8일차 - OUTER JOIN, DDL(Data Definition Language)

2020.08.13(목)

같은 테이블에서 JOIN가능 (SELF JOIN)

다른 컬럼으로 JOIN (동일한 값이 존재) mgr=empno

예)

    게시판

       게시물번호(중복이 없는 값) => MAX()+1

    댓글

       댓글번호 no

       게시물번호 board_no

1. JOIN

    INNER JOIN (같은 값일때 데이터 연결 => 교집합) = (NULL값을 제외)

       = EQUI_JOIN

       = NON_EQUI_JOIN

    OUTER JOIN

       = LEFT OUTER JOIN => INTERSECT + MINUS

          형식)

          = 오라클 조인

          SELECT A.col, B.col

          FROM A, B

          WHERE A.col = B.col(+) ==> INNER JOIN + (A-B)

          = ANSI 조인

          SELECT A.col, B.col

          FROM A LEFT OUTER JOIN B

          ON A.col = B.col

       = RIGHT OUTER JOIN => INTERSECT + MINUS

          = 오라클 조인

          SELECT A.col, B.col

          FROM A, B

          WHERE A.col(+) = B.col ==> INNER JOIN + (B-A)

          = ANSI 조인

          SELECT A.col, B.col

          FROM A RIGHT OUTER JOIN B

          ON A.col = B.col

       = FULL OUTER JOIN => UNION

          = ANSI 조인

          SELECT A.col, B.col

          FROM A FULL OUTER JOIN B

          ON A.col = B.col

 

INNER JOIN => 2,3,4 (bbb|eee, ccc|fff. ddd|ggg)

SQL> SELECT A.no, A.text, B.no, B.text FROM A, B WHERE A.no=B.no;

        NO TEXT                         NO TEXT
---------- -------------------- ---------- --------------------
         2 bbb                           2 eee
         3 ccc                           3 fff
         4 ddd                           4 ggg

SQL> SELECT e1.ename "본인", e2.ename "사수" FROM emp e1, emp e2 WHERE e1.mgr=e2.empno;

본인                 사수
-------------------- --------------------
FORD                 JONES
SCOTT                JONES
JAMES                BLAKE
TURNER               BLAKE
MARTIN               BLAKE
WARD                 BLAKE
ALLEN                BLAKE
MILLER               CLARK
ADAMS                SCOTT
CLARK                KING
BLAKE                KING
JONES                KING
SMITH                FORD

LEFT OUTER JOIN => 2,3,4 (bbb|eee, ccc|fff, ddd|ggg)+ 1(aaa|null)

SQL> SELECT A.no, A.text, B.no, B.text FROM A, B WHERE A.no=B.no(+);

        NO TEXT                         NO TEXT
---------- -------------------- ---------- --------------------
         2 bbb                           2 eee
         3 ccc                           3 fff
         4 ddd                           4 ggg
         1 aaa

SQL> SELECT e1.ename "본인", e2.ename "사수" FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.mgr=e2.empno;

본인                 사수
-------------------- --------------------
FORD                 JONES
SCOTT                JONES
JAMES                BLAKE
TURNER               BLAKE
MARTIN               BLAKE
WARD                 BLAKE
ALLEN                BLAKE
MILLER               CLARK
ADAMS                SCOTT
CLARK                KING
BLAKE                KING
JONES                KING
SMITH                FORD
KING

RIGHT OUTER JOIN => 2,3,4 (bbb|eee, ccc|fff, ddd|ggg) + 5(null|hhh)

SQL> SELECT A.no, A.text, B.no, B.text FROM A, B WHERE A.no(+)=B.no;

        NO TEXT                         NO TEXT
---------- -------------------- ---------- --------------------
         2 bbb                           2 eee
         3 ccc                           3 fff
         4 ddd                           4 ggg
                                         5 hhh

SQL> SELECT e1.ename "본인", e2.ename "사수" FROM emp e1 RIGHT OUTER JOIN emp e2 ON e1.mgr=e2.empno;

본인                 사수
-------------------- --------------------
SMITH                FORD
ALLEN                BLAKE
WARD                 BLAKE
JONES                KING
MARTIN               BLAKE
BLAKE                KING
CLARK                KING
SCOTT                JONES
TURNER               BLAKE
ADAMS                SCOTT
JAMES                BLAKE
FORD                 JONES
MILLER               CLARK
                     TURNER
                     Hong
                     WARD
                     MARTIN
                     ALLEN
                     MILLER
                     SMITH
                     ADAMS
                     JAMES

FULL OUTER JOIN => 2,3,4 (bbb|eee, ccc|fff, ddd|ggg) 1(aaa|null) 5(null|hhh)

(UNION ALL이 아닌 UNION)

SQL> SELECT A.no, A.text, B.no, B.text FROM A FULL OUTER JOIN B ON A.no=B.no;

        NO TEXT                         NO TEXT
---------- -------------------- ---------- --------------------
         2 bbb                           2 eee
         3 ccc                           3 fff
         4 ddd                           4 ggg
                                         5 hhh
         1 aaa

SQL> SELECT e1.ename "본인", e2.ename "사수" FROM emp e1 FULL OUTER JOIN emp e2 ON e1.mgr=e2.empno;

본인                 사수
-------------------- --------------------
                     SMITH
                     ALLEN
                     WARD
FORD                 JONES
SCOTT                JONES
                     MARTIN
JAMES                BLAKE
TURNER               BLAKE
MARTIN               BLAKE
WARD                 BLAKE
ALLEN                BLAKE
MILLER               CLARK
ADAMS                SCOTT
CLARK                KING
BLAKE                KING
JONES                KING
                     TURNER
                     ADAMS
                     JAMES
SMITH                FORD
                     MILLER
KING

 

 

2. SQL

  = DML (데이터 조작)

     1) SELECT (데이터 검색)

        = 형식 (순서)

        = 조건 검색 (연산자)

        = 조작 (함수)

        = JOIN : 데이터를 묶어준다

        = SUBQUERY : VIEW (SQL문장을 묶어준다)

SQL> SELECT ename, sal FROM emp WHERE sal<(SELECT AVG(sal) FROM emp);

ENAME                       SAL
-------------------- ----------
SMITH                       800
ALLEN                      1600
WARD                       1250
MARTIN                     1250
TURNER                     1500
ADAMS                      1100
JAMES                       950
MILLER                     1300

     =================== 테이블이 존재

     2) INSERT (데이터 추가)

     3) UPDATE (데이터 수정)

     4) DELETE (데이터 삭제)

     =================== CURD

  = DDL (데이터 선언)

     1) CREATE : 데이터를 저장하는 공간 제작 (TABLE:파일)

        TABLE, VIEW, SEQUENCE, INDEX, PL/SQL

        PL/SQL => PROCEDURE, FUNCTION, TRIGGER

         형식)

             1. CATS : 복사(기존에 존재하는 테이블을 복사)

                CREATE TABLE table_name

                AS

                    SELECT ~

             2. 새로운 테이블 제작 (데이터 저장하는 공간)

                CREATE TABLE table_name(

                         컬럼명 데이터형,

                         컬럼명 데이터형,

                         컬럼명 데이터형,

                         컬럼명 데이터형,

                         ==

                         ==

                );

     2) DROP : 삭제(table 자체 삭제)

         형식)

                DROP TABLE 삭제할 테이블명; ==> 백업

     3) ALTER : 수정, 삭제, 추가 (컬럼관련)

         형식)

                ALTER TABLE table_name ADD 컬럼명 데이터형(컬럼 추가)

                ALTER TABLE table_name DROP COLUMN 컬럼명(컬럼 삭제)

                ALTER TABLE table_name MODIFY COLUMN 컬럼명 데이터형

                ALTER TABLE table_name RENAME COLUMN 이전컬럼명 TO 변경컬럼명

     4) TRUNCATE : 데이터만 삭제 => 복구안됨

         형식)

                TRUNCATE TABLE table_name;

     5) RENAME : 테이블 이름 변경

         형식)

                RENAME 이전테이블명 TO 변경할 테이블명(테이블 이름 변경)

              형식)

                ALTER TABLE table_name read only (읽기 전용)

                 => SELECT문장만 사용이 가능

  = DCL (데이터 제어)

     1) GRANT : 권한 부여

     2) REVOKE : 권한 해제

  = TCL (트랜잭션)

     1) COMMIT

     2) ROLLBACK

 

 

3. 테이블 제작

 1) 테이블 복사

    CREATE TABLE myDept

    AS SELECT * FROM dept;

SQL> CREATE TABLE myDept AS SELECT * FROM dept;

Table created.

SQL> SELECT * FROM myDept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        
        
SQL> CREATE TABLE myEmpDept AS SELECT empno, ename, job, hiredate, sal, emp.deptno, dname, loc
     FROM emp, dept WHERE emp.deptno = dept.deptno;

Table created.

SQL> SELECT * FROM myEmpDept;

     EMPNO ENAME                JOB                HIREDATE        SAL     DEPTNO DNAME                        LOC
---------- -------------------- ------------------ -------- ---------- ---------- ---------------------------- --------------------------
      7369 SMITH                CLERK              80/12/17        800         20 RESEARCH                     DALLAS
      7499 ALLEN                SALESMAN           81/02/20       1600         30 SALES                        CHICAGO
      7521 WARD                 SALESMAN           81/02/22       1250         30 SALES                        CHICAGO
      7566 JONES                MANAGER            81/04/02       2975         20 RESEARCH                     DALLAS
      7654 MARTIN               SALESMAN           81/09/28       1250         30 SALES                        CHICAGO
      7698 BLAKE                MANAGER            81/05/01       2850         30 SALES                        CHICAGO
      7782 CLARK                MANAGER            81/06/09       2450         10 ACCOUNTING                   NEW YORK
      7788 SCOTT                ANALYST            82/12/09       3000         20 RESEARCH                     DALLAS
      7839 KING                 PRESIDENT          81/11/17       5000         10 ACCOUNTING                   NEW YORK
      7844 TURNER               SALESMAN           81/09/08       1500         30 SALES                        CHICAGO
      7876 ADAMS                CLERK              83/01/12       1100         20 RESEARCH                     DALLAS
      7900 JAMES                CLERK              81/12/03        950         30 SALES                        CHICAGO
      7902 FORD                 ANALYST            81/12/03       3000         20 RESEARCH                     DALLAS
      7934 MILLER               CLERK              82/01/23       1300         10 ACCOUNTING                   NEW YORK
      7935 Hong                                    20/08/11                    10 ACCOUNTING                   NEW YORK

 

 2) 사용자 정의

   오라클에서 제공하는 데이터형

   = 문자

      CHAR (1byte~2000byte) : 고정 바이트

        CHAR(10) => 'A' => 10byte (글자수 상관없이 같아서 메모리 차지를 많이함)

      VARCHAR2 (1byte~4000byte) : 가변 바이트

        VARCHAR2(10) => 'A' => 1byte

      CLOB : 가변 바이트

        4기가 저장이 가능 => 내용, 줄거리, 자기소개

   = 숫자

      NUMBER (정수, 실수) => 실수 => NUMBER(10,2)

      NUMBER (정수) => NUMBER(1~38)

      NUMBER => 14자리

   = 날짜

      DATE : 일반 날짜, 시간

      TIMESTAMP : 기록 경기(올림픽..)

   = 기타 : 동영상, 사진..

      BLOB : 4G => 사진, 동영상... 바이러리로 저장

      BFILE : 4G => 사진, 동영상... File형태로 저장

 

테이블

 컬럼명 데이터형 

 name VARCHAR2(34)

 

emp

 = 사번 => 정수

 = 이름 => 문자

 = 직위 => 문자

 = 입사일 => 날짜형

 = 급여 => 실수

 = 부서번호 => 정수

 

 VO

 CREATE TABLE emp(

   empno NUMBER(4) -- 1~9999

   ename VARCHAR2(34) -- 17자 (한글은 2byte)

   job VARCHAR2(10)

   hiredate DATE,

   sal NUMBER(7,2) -- 실수, 정수

   deptno NUMBER(2)

 );

CREATE TABLE 테이블명 (컬럼명 + 데이터명);

 

* 테이블, 컬럼명 제작 (의미를 부여)

 1) 테이블명은 반드시 문자로 시작

 2) 숫자는 사용이 가능 => 시작하면 안된다

    1emp, 2emp => emp1, emp2

 3) 알파벳, 한글도 가능 => 알파벳(한글 => 깨질 수 있다)

 4) 대소문자 구분은 없다 => 오라클에 저장시에 대문자로 저장된다

SQL> SELECT column_name, data_type FROM user_tab_columns WHERE table_name='emp';

no rows selected

SQL> SELECT column_name, data_type FROM user_tab_columns WHERE table_name='EMP';

COLUMN_NAME
--------------------------------------
DATA_TYPE
--------------------------------------
EMPNO
NUMBER

ENAME
VARCHAR2

JOB
VARCHAR2

MGR
NUMBER

HIREDATE
DATE

SAL
NUMBER

COMM
NUMBER

DEPTNO
NUMBER

 5) 테이블, 컬럼명 => 저장길이 30byte, 한글(15)

 6) 테이블명은 중복해서 만들 수 없다 (XE)(유일)

 7) 키워드는 사용할 수 없다 SELECT, FROM

    * 테이블명, 컬럼명이 동일할 수 있다

 

-- 필요없는 컬럼 삭제

ALTER TABLE movie DROP COLUMN address;

 

-- 필요한 컬럼 추가

ALTER TABLE movie ADD cno NUMBER;

 

-- 컬럼 수정

ALTER TABLE movie MODIFY genre VARCHAR2(100);

 

-- 컬럼 이름 변경

ALTER TABLE movie RENAME COLUMN showUser TO su;

 

-- 컬럼 레코드 추가

INSERT INTO movie(mno, title_ko, time) VALUES(1, '인셉션', 227);

 

-- 레코드 내용 수정

UPDATE movie SET title_ko = 테넷 WHERE mno = 1;

 

-- 레코드 삭제

DELETE FROM movie WHERE mno = 1;

 

-- 테이블 이름 변경

RENAME movie TO daum_movie;

 

-- 테이블 복사

CREATE TABLE movie2 AS SELECT * FROM movie1;

CREATE TABLE movie2 AS SELECT * FROM movie1 WHERE 1=2;

=>(조건이 false면 테이블 컬럼만 가져오고 데이터는 가져오지 않는다)

 

-- 컬럼은 남겨 놓고 데이터만 지우는 쿼리

TRUNCATE TABLE movie;

 

-- 테이블을 완전히 삭제하는 쿼리

DROP TABLE movie;