KH 49일차 - INSERT 문 (***)

-- + 외래키 제약 조건

-- (1) CASCADE ( 연쇄삭제 )

-- (2) RESTRICTED ( 삭제를 거부 )

-- (3) DEFAULT ( 부모 테이블 기본키 삭제시, 자식 참조키에 이미 지정한 디폴트값 지정 )

-- (4) SET NULL( NULL값 지정 )

---

-- ------------------------------------------------------

-- 1. INSERT 문 ( DML )

-- ------------------------------------------------------

--  가. 테이블에 데이터를 저장하기 위한 문장.

--  나. 구분:

--      (1) 단일 행 INSERT문: 한 번에 하나의 행을 테이블에 저장

--      (2) 다중 행 INSERT문: 한 번에 여러 행을 테이블에 저장

--  다. 테이블에 새로운 레코드(행)이 삽입 됨. (*)

--  라. INTO 절에 명시한 컬럼은, VALUES 절에서 지정한 컬럼값과

--      일대일 대응되도록, 순서대로 입력해야 함.

--      So, INTO 절에 명시된 컬럼과 VALUES 절에 명시된 값의

--      개수와 타입은 같아야 함.

--  마. INTO 절의 컬럼목록은 생략가능.

--      if 생략하면, 테이블 생성시 정의한 컬럼순서와 동일한 순서로

---     모든 컬럼값을 VALUES 절에 지정해야 한다.

--  바. 저장되는 데이터의 타입은, 컬럼의 데이터 타입과 일치해야 함

--  사. 저장되는 데이터의 크기는, 컬럼의 크기보다 같거나 작아야 함

--  아. 기본키(PK) 또는 UNIQUE컬럼은, 동일한 값을 저장할 수 없음.

--  자. INTO 절에서 생략된 컬럼은, 자동으로 널(NULL) 값이 저장됨.

--      따라서, NOT NULL 제약조건이 아닌 컬럼만 INTO절에서 생략가능.

-- ------------------------------------------------------

-- Basic Syntax) 단일 행 INSERT문

--  INSERT INTO 테이블명 [ (컬럼명1, 컬럼명2, ...) ] ( 지정한 컬럼에만 넣을때는 ()를 활용하여 컬럼을 지정한다. )

--  VALUES ( 값1, 값2, ... );

-- ------------------------------------------------------

DELETE FROM dept

    -- dept는 부서 테이블이다. (SCOTT)

WHERE

    deptno IN (50,60,70,80);

SELECT

    *

FROM

    dept;

-- dept 릴레이션에서 where 절에 적인 부서의 번호(deptno)를 지워버린다.

-- 그 후에 *를 통해서 dept 릴레이션을 출력하였다.

-- 현재로는 부서번호가 10에서 40까지이기에 실제로는 지워지지는 않는다.

 

---

-- ------------------------------------------------------

-- 1-1. 단일 행 INSERT 문 (******)

-- ------------------------------------------------------

BEGIN

    -- 테이블명 오른쪽에 명시적으로 소괄호() 안에 컬럼명을 명시한 INSERT문

    INSERT INTO dept(deptno, dname, loc)

    VALUES (50, '개발','서울'); -- 각각을 트랜젝션 유닛이라고 한다.

    -- 테이블명 오른쪽에 컬럼명을 생략한 INSERT문 -> 이때에는 테이블의 스키마에 맞게 값 지정

    INSERT INTO dept

    VALUES (60,'인사','경기');

    -- 묵시적으로 널(NULL) 값 저장

    --1 ) 묵시적 방법 : 칼럼목록에 loc 칼럼이 빠져있음 ( NOT NULL일 경우에는 오류가 발생할 수 있음 )

    INSERT INTO dept(deptno, dname)

    VALUES  (70,'인사');

    -- 명시적으로 널값 저장

    -- 2 ) 명시적 방법 : 특정 칼럼에 결측치(NULL)을 명시적으로 삽입

    INSERT INTO dept(deptno, dname, loc)

    VALUES (80,'인사',NULL);

    -- ROLLBACK;

    COMMIT;

END;    -- TO end a transaction

 

-- BEGIN은 END와 함께 사용해야 한다.

-- 이 과정은 ALL or Nothing으로 하나라도 실패하면 아무것도 변화시키지 않는다.

-- ROLLBACK은 되돌리는 것이고, COMMIT은 완전히 기록해버리는 것이다.

-- 즉, ROLLBACK을 작성하면 저장되지 않고 다시 전의 상태로 되돌아가는 것이다.

-- TCL ( 트레젝션 제어 언어 ) : 1. COMMIT(변경내역을 영구적으로 데이터베이스 테이블에 기록) 2. ROLLBACK(변경이전상태로 되돌림) 3. SAVEPOINT(중간 세이브포인트)

-- 주로 COMMIT과 ROLLBACK이 자주 사용된다.

-- 이들은 하나의 트랜잭션으로 ALL OR NOTHING으로 처리된다.(**)

-- DBMS 입장에서는 1번에 받은 SQL문장이 1개이든 100개이든 모두 TX로 처리

-- TX : 하나의 '거래' 안에 거래되는 물품이 1개 이상(100개도 1000000개도 가능)

-- TX 처리결과 : 하나의 거래 안에 포함된 모든 물품을 받아들어던가, 1개라도 불량이 발견되어 전부 취소하던가.

-- 데이터베이스의 트랜잭션 처리 : ALL OR NOTHING ( 하나라도 DML문장이 실패하면, 모든 DML처리를 취소 : 이전 상태로 되돌린다. ( ROLLBACK ) ) (****)

-- BEGIN ~ END; : 데이터베이스에서 이 범위를 하나의 TX(트랜젝션)로 처리해달라는 것이다.

 

---

-- ------------------------------------------------------

-- **(주의)** INSERT 문장 수행 시, 오류 발생 예시

-- ------------------------------------------------------

-- 1 ) INTO절에 명시된 컬럼의 갯수와 VALUES절에 명시된 개수가 틀린 경우

INSERT INTO dept(deptno, dname, loc)

VALUES (11, '인사');

-- 2 ) INTO 절에서 컬럼목록을 생략한 경우 : VALUES절에서 테이블의 모든 컬럼값을 누락하지 말아야 한다. (*)

INSERT INTO dept

VALUES (12, '인사');

-- 3 ) INTO 절의 컬럼 데이터 타입과 VALUES 절의 값의 데이터 타입이 일치하지 않는 경우

INSERT INTO dept (deptno, dname, loc)

VALUES ('개발', 13, '인사');

-- 4 ) VALUES 절의 컬럼값 지정시, 반드시 리터럴 형식에 맞게 설정해야 한다.

-- ------------------------------------------------------

--   * 리터럴(Literal) 규칙:

-- ------------------------------------------------------

--      a. 문자와 날짜 리터럴: 반드시, '' 로 묶어야 함. (***)

--         만일, ''를 누락하면, 리터럴(값)이 아닌, 식별자로 인식

--      b. 수치 리터럴: '' 없이 사용해야 함.

-- ------------------------------------------------------

INSERT INTO dept (deptno, dname, loc)

VALUES ('개발',14,인사);

---

-- ------------------------------------------------------

-- 1-2. 복수 행 INSERT 문 ( = INSERT SELECT문 ) (******)

-- ------------------------------------------------------

-- 가. 하나의 INSERT 문장으로, 한번에 여러 행 저장

-- 나. VALUES 절 대신에, 서브쿼리(= 부속질의) 사용 (***)

-- 다. 서브쿼리를 사용하여, 기존 테이블의 데이터를 복사한 후에,

--     INSERT 문으로 새로운 행 생성.

-- 라. (*주의할 점*)

--     INTO 절에서 지정한 컬럼의 개수와 타입에 맞추어,

--     서브쿼리의 수행결과가 반드시 동일해야 함.

-- ------------------------------------------------------

-- Basic Syntax)

-- INSERT INTO 테이블명 [ (컬럼명1, 컬럼명2, ..., 컬럼n) ]

-- Subquery;

-- ------------------------------------------------------

---

-- ------------------------------------------------------

-- (1) 기존 테이블을 이용하여, 새로운 테이블 생성 (CTAS) (***)

-- ------------------------------------------------------

-- * CTAS: 기존 테이블 스키마 복사 시, NULL 제약조건을 제외

--         한, 그 외 제약조건은 복사되지 않음. (***)

-- ------------------------------------------------------

-- Basic Syntax)

--  CREATE TABLE 테이블명 [(컬럼명,컬럼명2)]

--  AS

--  Subquery;

-- ------------------------------------------------------

DROP TABLE mydept PURGE;

CREATE TABLE mydept

AS

SELECT * FROM dept;

-- WHERE 1 = 2;        -- 조건이 항상 FALSE이기에 투플은 복사되지 않는다. (**)

-- 기존 테이블은 dept 테이블에서 스키마만 복사해서 왔다. (***)

-- 전부 복사하기 위해서는 where 조건을 항상 TRUE로 하던지, 없애던지 하면 된다.

---

-- ------------------------------------------------------

-- (2) 복수 행 INSERT 문 수행

-- ------------------------------------------------------

INSERT INTO mydept

SELECT

    deptno,

    dname,

    loc

FROM

    dept;

-- mydept 테이블에 dept테이블의 모든 투플의 deptno, dname, loc를 넣는다는 것이다.

DESC mydept;

SELECT * FROM mydept;

 

---

-- ------------------------------------------------------

-- 1-3. 다중 테이블, 복수 행 INSERT 문 (= INSERT ALL 문) (*****)

-- ------------------------------------------------------

--  가. 하나의 INSERT 문장으로, 한번에 한 개 이상의 테이블에,

--      여러 개의 행을 저장하는 문장.

--  나. INSERT ALL 문장이라고 부름.

--  다. 서브쿼리의 실행결과가 INTO절에 지정한 테이블(1..N)에

--      자동으로 INSERT 됨.

--  라. ** WHEN 절은 생략가능 ** (= 무조건 INSERT ALL 문)

--  마. WHEN절이 있는 경우, 조건식이 참일 때에만, 지정된 테이블에

--      서브쿼리의 결과가 INSERT 됨.

--      (이땐, "조건 INSERT ALL 문" 이라고 부름)

--  바. (**주의사항**) VALUES 절에 사용된 컬럼명과 Subquery에서

--      사용된 컬럼명이 반드시 동일해야 함.

--  사. 구분:

--      (1) 무조건 INSERT ALL문 : WHEN 절이 생략된 경우.

--      (2) 조건 INSERT ALL문   :

--          WHEN 절의 조건식의 참일 경우에만, INSERT 수행.

--          여러 WHEN 절이 중복되어 참인 경우에는,

--          각 테이블에 모두 해당 행이 INSERT 됨.

--      (3) 조건 INSERT FIRST문 :

--          WHEN 절에 지정된 조건이 중복되어 참인 경우에,

--          처음조건에 일치하는 테이블에만 해당 행이 저장되고,

--          이후 조건이 일치해도, 해당 테이블에 저장하지 않는 문장.

-- ------------------------------------------------------

-- Basic Syntax) (***)

-- INSERT ALL

--  [WHEN 조건식 THEN]

--  INTO 테이블1 VALUES (컬럼명1, 컬럼명2, ..., 컬럼명n)

--  [WHEN 조건식2 THEN]

--  INTO 테이블2 VALUES (컬럼명1, 컬럼명2, ..., 컬럼명n)

--  ..

--  [WHEN 조건식n THEN]

--  INTO 테이블n VALUES (컬럼명1, 컬럼명2, ..., 컬럼명n)

-- Subquery;

-- ------------------------------------------------------

---

-- ------------------------------------------------------

-- 1-3-1. 무조건 INSERT ALL 문 (*****)

-- ------------------------------------------------------

-- (1) 기존 테이블을 이용하여, 새로운 테이블 생성 (CTAS)

-- ------------------------------------------------------

-- * CTAS: 기존 테이블 스키마 복사 시, NOT NULL 제약조건을 제외

--         한, 그 외 제약조건은 복사되지 않음.

-- ------------------------------------------------------

-- Basic Syntax)

--  CREATE TABLE 테이블명 [(컬럼명,컬럼명2)]

--  AS

--  Subquery; ( = CTAS = CREATE TABLE AS Subquery )

-- ------------------------------------------------------

DROP TABLE myemp_hire;

CREATE TABLE myemp_hire AS

SELECT

    empno,

    ename,

    hiredate,

    sal

FROM

    emp

WHERE

    1 = 2;

-- 이 경우에는 WHERE 조건이 항상 거짓이기에 컬럼(스키마)만 복사해간다. ( 데이터는 제외된다. )

DROP TABLE myemp_mgr;

CREATE TABLE myemp_mgr AS

SELECT

    empno,

    ename,

    mgr

FROM

    emp

WHERE

    1 = 3;      -- 기존 테이블의 스키마만 복사 ( 데이터 제외 )

-- 이번에는 emp의 일부 칼럼들을 myemp_mgr에 복사하고 있다. ( WHERE 조건이 항상 false이기에 데이터는 제외된다. )

DESC myemp_mgr;

---

-- ------------------------------------------------------

-- (2) 무조건 INSERT ALL 문장 수행 (*****)

-- ------------------------------------------------------

-- (**주의사항**) VALUES 절에 사용된 컬럼명과 Subquery에서

--      사용된 컬럼명이 반드시 동일해야 함.

-- ------------------------------------------------------

INSERT ALL

    INTO myemp_hire VALUES (empno, ename, hiredate, sal)

    INTO myemp_mgr VALUES (empno,ename,mgr)

    SELECT

        empno,

        ename,

        hiredate,

        sal,

        mgr

    FROM

        emp;

-- 이번에는 조건으로 필터링하지 않기에 각각 스키마에 맞게 모든 데이터가 복사해서 들어가게 된다.

DROP TABLE myemp_hire2;

CREATE TABLE myemp_hire2 AS

SELECT

    empno,

    ename,

    hiredate,

    sal

FROM

    emp

WHERE

    1 = 2;

-- WHERE에서 항상 거짓으로 되어있기에, 기존 테이블에서 스키마만 복사해 온다. ( 데이터는 복사하지 않는다. )

DESC myemp_hire2;

DROP TABLE myemp_mgr2;

CREATE TABLE myemp_mgr2 AS

SELECT

    empno,

    ename,

    mgr

FROM

    emp

WHERE

    1 = 2;

--

DESC myemp_mgr2;

INSERT ALL

    WHEN sal > 3000 THEN

        INTO myemp_hire2 VALUES (empno,ename,hiredate,sal)

    WHEN mgr = 7698 THEN

        INTO myemp_mgr2 VALUES (empno,ename,mgr)

   

    SELECT

        empno,

        ename,

        hiredate,

        sal,

        mgr

    FROM

        emp;

-- WHEN 절을 활용하여 조건에 맞는 투플들만 각각의 테이블에 넣었다.

SELECT

    *

FROM

    myemp_hire2;

-- when 조건대로 sal이 3000이상인 투플만 출력된다.

SELECT

    *

FROM

    myemp_mgr2;

-- when 조건대로 mgr이 7698인 투플만 출력된다.

CREATE TABLE myemp_hire3 AS

SELECT

    empno,

    ename,

    mgr

FROM

    emp

WHERE

    1 = 2;