농부지기

☞ 형식

☞  SELECT 문을 이용한 INSERT

1.  INSERT  INTO TABLE_NAME1
             (   SELECT ...
                 FROM   TBL_NAME2 ) ;

  : Oralce 에서는 Insert 와 Select 문을 같이 사용 할 경우에는 VALUES  문을 사용하지 않는다.

☞ 조건부 INSERT ALL

예시) INSERT ALL
      WHEN   product_id IN ( SELECT product_id FROM promotional_items )
      INTO   promotional_sales
      VALUES (product_id, list_price)
      WHEN   order_mode = 'online'
      INTO   web_orders
      VALUES (product_id, order_total)
      SELECT product_id, list_price, order_total,order_mode
      FROM   orders;

▣ 조건절이 없는 INSERT
        : SQL> INSERT ALL
                 INTO sal_log VALUES (id , salary , title )
                 INTO date_log VALUES (id , start_date , title )
                 SELECT id , salary , start_date , title
                 FROM s_emp
                 WHERE id > 20 ;      

▣ 조건절이 있는 ALL INSERT
      예시) 사번이 20번보다 큰 사원의 사번, 급여, 입사일, 직책을 선택하여 사번, 급여,
            직책은 SAL_LOG 테이블에 입력하되 급여가 850보다큰 직원만 입력하고, 사번, 입사일,
            직책은 DATE_LOG 테이블에 입력하되 단 입사일이 91년 1월 1일 이전인 직원만 입력.

     SQL>    INSERT ALL
             WHEN salary > 850 THEN
             INTO sal_log VALUES (id , salary , title )
             WHEN start_date < '91/01/01' THEN
             INTO date_log VALUES (id , start_date , title )
             SELECT id , salary , start_date , title
             FROM s_emp
             WHERE id > 20 ;

☞ 조건부 INSERT FIRST

INSERT FIRST
WHEN order_total> 10000 THEN
    INTO priority_handling VALUES (id)
WHEN order_total > 5000 THEN
    INTO special_handling VALUES (id)
WHEN total > 3000 THEN
    INTO privilege_handling VALUES (id)
ELSE
    INTO regular_handling VALUES (id)
    SELECT order_total, order_id id  FROM  orders;

※ 무조건 첫번째 조건인 salary>=4000인 조건의 데이터는 DEPT_SAL 테이블에 들어가고,
   나머지 데이터 salary<4000인 데이터 중에서 입사일 조건에 따라 각각의 테이블에 입력
      예시) SQL>  INSERT FIRST
                     WHEN salary >= 4000 THEN
                        INTO dept_sal VALUES (dept_id , salary)
                     WHEN start_date LIKE  '90%' THEN
                        INTO dept_date_90 VALUES (dept_id , start_date)
                     WHEN start_date LIKE  '91%' THEN
                        INTO dept_date_91 VALUES (dept_id , start_date)
                     ELSE
                     INTO dept_date VALUES (dept_id , start_date )
                     SELECT dept_id , SUM(salary) SALARY, MAX(start_date) START_DATE
                     FROM S_EMP
                     GROUP BY dept_id ;

☞ Pivot INSERT

예시)  SQL> INSERT ALL
             INTO sale_data VALUES (id, week_id, sales_mon)
             INTO sale_data VALUES (id, week_id, sales_tue)
             INTO sale_data VALUES (id, week_id, sales_wed)
             INTO sale_data VALUES (id, week_id, sales_thur)
             INTO sale_data VALUES (id, week_id, sales_fri)
             SELECT id,week,sales_mon,sales_tue,sales_wed,sales_thur, sales_fri
             FROM main_sale ;

0. SUB QUERY

1. 정의 : SELECT에서 결과로 나오는 행의 수가 한 행 이상이며, 단 한 컬럼인 경우

2. 예) FROM  TEMP
       WHERE DEPT_CODE IN (SELECT DEPT_CODE FROM TDEPT  WHERE  AREA = '인천');

1. 정의 : SELECT에서 결과로 나오는 행의 수가 한 행 이상이며, 여러 컬럼인 경우

2. 예) FROM  TEMP
       WHERE (DEPT_CODE, EMP_ID) IN (SELECT DEPT_CODE, BOSS_ID FROM TDEPT WHERE AREA = '인천');

1. 정의 : InnerQuery에서 OuterQuery의 어떤 컬럼 값을 사용하는 경우이다.
          InnerQuery(흔히 SUB QUERY라고 함)와 OuterQuery(흔히 MAIN QUERY라고 함)가 서로
          단순히 여관관계가 있는 것이 아니고 서로 값을 주고 받는 연관관계가 성립한다.

2. 참고 : 일반적인 서브쿼리는 SUBQUERY의 결과를 MAIN QUERRY가 이용한다.
          하지만 CORRELATED 서브쿼리는 SUB QUERRY가 MAIN QUERY의 값을 이용하고,
          그렇게 구해진 SUB QUERY의 값을 다시 MAIN QUERY가 다시 이용하게 된다.

3. 예)  FROM   TEMP A
        WHERE  SALARY  >  (SELECT AVG(SALARY)
                           FROM   TEMP B
                           WHERE  B.LEV  =  A.LEV);
  설명) 직원 중 자신의 연봉이 자신과 같은 LEV에 해당하는 직원의 평균 SALARY보다 많은 경우에
        이 사원의 사번과 성명을 읽어 오는 QEURY이다.

4. 예) UPDATE  TDEPT  A
       SET     A.BOSS_ID  =  (SELECT  MIN(B.EMP_ID)
                              FROM    TEMP  B
                              WHERE   B.DEPT_CODE  = A.DEPT_CODE);

1. 정의 : ANY와 ALL 연산자는 서브쿼리와 함께 쓰이거나 값들의 집합과 함께 쓰여 비교된다.

2. 예) SALARY 가 (100, 200, 300)중의 어떤 값 하나보다 큰 값인가를 비교하고 싶을 때
       SALARY > ANY(100, 200, 300)

3. 예) ALL은 집합의 모든 값 보다 어떤가로 비교가 된다.
       SALARY > ALL(100, 200, 300)은  100, 200, 300모두 보다 큰 값인가?로 될 것이다.

4. 예) FROM   TEMP
       WHERE  SALARY > ANY (SELECT  SALARY
                            FROM    TEMP
                            WHERE   LEV = '과장');
   설명) 서브쿼리를 통해 LEV='과장'인 직원의 SALRY를 TEMP에서 읽고 이중의 어떤 값 하나보다
         큰 연봉을 가진 직원의 사번,성명, 연봉을 읽어오는 쿼리이다.

5. 예) FROM   TEMP
       WHERE  SALARY > ALL (SELECT  SALARY
                            FROM    TEMP
                            WHERE   LEV = '과장');
   설명) 어떤 과장보다도 연봉이 많은 직원을 읽어온다.

Join

1. 정의 : JOIN조건 절에 조인 조건이 '='(equal)이 아닌 다른 연산기호로 주어지는 경우

2. 예) FROM  TEMP  A,   EMP_LEVEL B
       WHERE A.BIRTH_DATE  BETWEEN  ADD_MONTHS(SYSDATE, -1 * B.TO_AGE * 12)
                           AND      ADD_MONTHS(SYSDATE, -1 * B.FROM_AGE * 12)

1. 정의 : 두 개 이상의 테이블 조인 시 한쪽 테이블의 행에 대해 다른 쪽 테이블에 일치하는 행이
          없더라도 다른 쪽 테이블의 행을 Null로 하여 행을 Return 하는 조인이다.

2. 추가 : OUTER JOIN 기호(+)는 NULL열이 작성되어야 하는 쪽에 기호가 붙는다.
          하나의 테이블이 OUTER JOIN의 대상으로 삼을 수 있는 테이블의 수가 한 개 뿐이다.

3. 예) FROM  TABLE1,  TABLE2, ....
       WHERE TABLE1.COLUMN = TABLE2.COLUMN(+)

   설명) JOIN된 컬럼 값이 TABLE2에 없더라도 TABLE1에 있으면, TABLE2의 값들 중 TABLE1과
         일치되는 값이 없는 행은 모두 NULL로 RETURN 된다.

1. 정의 : 다른 테이블간의 JOIN이 아닌 같은 테이블을 마치 다른테이블처럼 인식시켜
          JOIN을 하는 것이다.
2. SELF JOIN을 JOINING TABLE ITSELF 라고도 한다.

☞ DML 종류

1. CREATE TABLE : 새로운 데이블을 생성
2. DROP TABLE : 기존 테이블의 구조 및 모든 행을 삭제 
3. ALTER TABLE : 기존 테이블을 변경 - 컬럼의 추가,수정,삭제, 제약조건 추가,삭제,활성화,비활성화
4. TRUNCATE : 기존 테이블의 구조는 남기고 모든 행을 삭제
5. RENAME : object의 이름을 바꿈
6. COMMENT : 테이블이나 컬럼에 주석문 달기

※ 이러한 명령어는 모두 데이터 정의 명령어(DDL)로서 실행 시 자돈 COMMIT되고 ROLLBACK 될 수
   없으므로 주의해서 실행

☞ DROP TABLE

형식 : DROP TABLE 테이블명 [CASCADE CONSTRAINTS] ;
            - CASCADE CONSTRAINTS 옵션은 종속된 제약조건을 삭제

☞ ALTER TABLE

(1) 칼럼에 대한 변경
ㅇ 칼럼의 추가 : ALTER TABLE 테이블명 ADD (컬럼 datatype [DEFAULT 형식][CONSTRAINT 정의]
                                                                  [,컬럼 datatype]...);
ㅇ 칼럼의 수정 : 기존에 존재하는 컬럼의 크기와 데이터 타입의 변경
               : ALTER TABLE 테이블명 MODIFY(컬럼 datatype [DEFAULT 형식]
                                                   [NOT NULL 제약조건][,컬럼datatype]...);
ㅇ 칼럼의 삭제 : ALTER TABLE 테이블명 CROP COLUMN 컬럼
※ 한번에 하나씩, PK 칼럼을 삭제할 경우 CASCADE CONSTRAINTS 옵션을 사용할 것

ㅇ 사용불가 상태로 전환 : ALTER TABLE 테이블명 SET UNUSED [ COLUMN ] 칼럼 ;
    -  SET UNUSED 상태의 칼럼 제거 : ALTER TABLE 테이블명 DROP UNUSED COLUMNS ;
                                   : DROP의 시간 소요를 단축할 수 있음.

(2) Constraint에 대한 변경
ㅇ 제약조건의 추가 : ALTER TABLE 테이블명 ADD [CONSTRAINT 제약 조건명] 제약조건유형 (칼럼명);
   ※ NOT NULL 제약조건은 테이블 레벨 제약조건으로 기술 불가, MODIFY절 활용
ㅇ 제약조건의 삭제 : ALTER TABLE 테이블명 DROP CONSTRAINT 제약 조건명 [CASCADE] ;
ㅇ 제약조건의 활성화{비활성화} : ALTER TABLE 테이블명 ENABLE{DISABLE} CONSTRAINT 제약 조건명;
ㅇ CASCADE 옵션 : 참조하는 CHILD까지도 같이 수정 및 삭제시 활용
ㅇ NOVALIDATE CONSTRAINT 옵션 : 기존에 입력된 데이터에 대해서는 제약 조건을 Check하지 않고
                                향후 수정 혹은 새로 입력되는 데이터에 대해서만 제약 조건을
                                Check하여 수행력 향상
                              : ALTER TABLE 테이블명 ENABLE NOVALIDATE CONSTRAINT 제약 조건명;

☞ TRUNCATE

TRUNCATE TABLE 테이블명 ;

☞ RENAME

RENAME 이전이름 TO 새로운 이름

☞ SQL문장의 종류

1. RETRIEVE(Query) 
   - 데이터 검색 및 조회 SQL 문장
   - SELECT : 원하는 데이터를 검색하기 위해 사용되는 SQL 문

2. DML (Data Manipulation Language )
   - 데이터 조작어
   - 종류 : INSERT / UPDATE / DELETE / MERGE
   - 정의 : 새로운 행을  입력하거나, 기존의 행을 수정하거나 원치않는 데이터를 삭제하는 등
            데이터 조작에 관한 명령어

3. DDL (Data Definition Language)
   - 의미 : 데이터 정의어
   - 종류 : CREATE / ALTER / DROP/ RENAME / TRUNCATE / COMMENT / RENAME 등
   - 정의 : 구조를 만든다거나, 구조변경, 삭제 등 데이터 구조에 관한 명령어

4. TCL (Transaction Control Language)
   - 의미 : 트랜잭션 제어어
   - 종류 : COMMIT/ROLLBACK SAVEPOINT 등
   - 정의 : 논리적인 작업의 단위로 DML에 의해 조작된 결과를 다루는 명령어

5. DCL (Data Control Language)
   - 의미 : 데이터 제어어
   - 종류 : GRANT / REVOKE 등
   - 정의 : 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 받는 명령어