[SQL 쿡북/04장] 삽입, 갱신 및 삭제하기

04장. 삽입, 갱신 및 삭제하기

---

🎨 4-1 새로운 레코드 삽입하기

INSERT INTO 테이블명 (컬럼명,컬럼명...) 
              VALUES (값,값...)

EX)

-- 하나의 행 삽입
INSERT INTO DEPT (DEPTNO,DNAME,LOC) 
          VALUES (1,'A','B');
-- 여러 행 삽입
INSERT INTO DEPT (DEPTNO,DNAME,LOC) 
          VALUES (1,'A','B'),
                 (2,'C','D');

---

🎨 4-2 기본값 삽입하기

-- IF 특정 열에 대해 기본값을 사용하도록 테이블을 정의했다면?
CREATE TABLE DEPT (ID INTEGER DEFAULT 0);
-- DEFAULT라는 키워드만 넣어도 0값이 자동으로 들어가게 됩니다.
INSERT INTO DEPT VALUES (DEFAULT);

💎 주의하기!

• 오라클은 Oracle9i버전 부터 지원.
• MySQL은 해당 컬럼값에 값을 지정하지 않아도 default값 설정

🎨 4-3 null로 기본값 오버라이딩하기

💎 Case 1 : 특정열에 기본값을 설정하지 않았다면?

CREATE TABLE DEPT (ID INTEGER, FOO VARCHAR(10));

INSERT INTO DEPT (FOO) 
          VALUES ('테런');
SELECT ID, FOO 
  FROM DEPT;

ID     FOO
-----------
null   테런

💎 Case 2 : 특정열에 기본값을 설정하였다면?

CREATE TABLE DEPT (ID INTEGER DEFAULT 0, FOO VARCHAR(10));
INSERT INTO DEPT (FOO) 
          VALUES ('테런');
SELECT ID, FOO 
  FROM DEPT;

ID     FOO
-----------
0      테런

💎 Case 3 : 특정열에 기본값을 설정했지만, NULL을 넣고 싶다면?

CREATE TABLE DEPT (ID INTEGER DEFAULT 0, FOO VARCHAR(10));
INSERT INTO DEPT (ID, FOO) 
          VALUES (NULL, '테런');
SELECT ID, FOO 
  FROM DEPT;

ID     FOO
-----------
null   테런

---

🎨 4-4 한 테이블에서 다른 테이블로 행 복사하기

INSERT INTO 테이블명A (컬럼1,컬럼2)
               SELECT 컬럼3, 컬럼4
                 FROM 테이블명B

주의 : 같은 열 및 데이터 유형으로 넣어야 합니다.

INSERT INTO DEPT_EAST (DEPTNO, DNAME, LOC)
               SELECT DEPTNO, DNAME, LOC
                 FROM DPT
                WHERE LOC IN ('NEW YORK','BOSTON');

---

🎨 4-5 테이블 정의 복사하기

💎 (1) DB

CREATE TABLE DEPT_2 LIKE DEPT;

💎 (2) ORACLE, MySQL, PostgreSQL

CREATE TABLE DEPT_2 AS
      SELECT *
	      FROM DEPT
	     WHERE 1 = 0

• CTAS (Create Table As Select)문을 사용한다.

💎 (3) SQL Server

SELECT *
  INTO DEPT_2
  FROM DEPT
 WHERE 1 = 0

🎨 4-6 한 번에 여러 테이블에 삽입하기

• 4-4장에서 테이블의 수가 여러개로 확대된 형태

💎 ORACLE

INSERT ALL
  WHEN 조건 THEN
       INTO 테이블명A (컬럼1, 컬럼2...) VALUES (값1, 값2...)
  WHEN 조건 THEN
       INTO 테이블명B (컬럼11, 컬럼21...) VALUES (값11, 값22...)
  ...
  ELSE
       INTO 테이블명F (컬럼61, 컬럼61...) VALUES (값61, 값62...)
SELECT
  FROM 테이블명


INSERT ALL
  WHEN color = '빨강' THEN
       INTO RED_FRUITS (name, color) VALUES (name, color)
  WHEN color = '노랑' THEN
       INTO YELLOW_FRUITS (name, color) VALUES (name, color)
  ELSE
       INTO OTHER_FRUITS (name, color) VALUES (name, color)
SELECT name, color
  FROM FRUITS;

💎 DB2

/*
INSERT INTO (
     SELECT *
	   FROM 테이블명A
	  UNION ALL
     SELECT *
	   FROM 테이블명B
	  UNION ALL
     SELECT *
	   FROM 테이블명C
) SELECT *
    FROM 테이블명
*/

INSERT INTO (
     SELECT *
	     FROM RED_FRUITS
	  UNION ALL
     SELECT *
	     FROM YELLOW_FRUITS
	    UNION ALL
     SELECT *
	     FROM OTHER_FRUITS
) SELECT *
    FROM FRUITS

💎 MySQL, PostgreSQL, SQL Server

• 지원하지 않음

---

🎨 4-7 특정 열에 대한 삽입 차단하기

INSERT INTO 테이블명A (컬럼1,컬럼2)
             SELECT 컬럼3, 컬럼4
               FROM 테이블명B


INSERT INTO DEPT_EAST (LOC) VALUES ("LA");

---

🎨 4-8 테이블에서 레코드 수정하기

/*
모든 행에 데이터를 수정하고자 한다면?
IF 모든 부서의 급여를 10%인상하고자 한다면?
*/
UPDATE EMP
   SET SAL = SAL * 1.1

---

🎨 4-9 일치하는 행이 있을 때 업데이트하기

/*
IF 보너즈 받은 부서의 직원들의 급여만 10%인상하고자 한다면?
*/
UPDATE EMP
   SET SAL = SAL * 1.1
 WHERE EMPNO IN (
	  SELECT EMPNO
        FROM EMP_BONUS)

---

🎨 4-10 다른 테이블 값으로 업데이트하기

💎 DB2, ORACLE, PostgreSQL

UPDATE EMP AS E
   SET (E.SAL, E.COMM) = (SELECT NS.SAL, NS.SAL/2
                            FROM NEW_SAL AS NS
						   WHERE NS.DEPTNO = E.DEPTNO)
 WHERE EXISTS (SELECT *
                 FROM NEW_SAL AS NS
                WHERE NS.DEPTNO = E.DEPTNO)

---

💎 MySQL

UPDATE EMP E, NEW_SAL NS
   SET E.SAL    = NS.SAL,
       E.COMM   = NS.SAL/2
 WHERE E.DEPTNO = NS.DEPTNO

-- 실행전의 EMP 테이블
| EMPNO | ENAME   | SAL  | COMM | DEPTNO |
|-------|---------|------|------|--------|
| 101   | Alice   | 10   | 2    | 10     |
| 102   | Bob     | 12   | 3    | 20     |
| 103   | Charlie | 15   | 4    | 10     |
-- NEW_SAL 테이블
| DEPTNO | SAL   |
|--------|-------|
| 10     | 2000  |
| 20     | 2500  |

-- 실행 후의 EMP 테이블
| EMPNO | ENAME   | SAL   | COMM  | DEPTNO |
|-------|---------|-------|-------|--------|
| 101   | Alice   | 2000  | 1000  | 10     |
| 102   | Bob     | 2500  | 1250  | 20     |
| 103   | Charlie | 2000  | 1000  | 10     |

---

🎨 4-11 레코드 병합하기

💎 DB2, ORACLE, PostgreSQL

MERGE INTO EP_COMMISSION EC
USING (SELECT * FROM EMP) AS EMP  -- 1단계
   ON (EC.EMPNO = EMP.EMPNO)      -- 2단계
 WHEN MATCHED THEN                -- 3단계
      UPDATE
	     SET EC.COMM = 1000	      -- 3-1단계
	  DELETE
	   WHERE (SAL < 2000)	      -- 3-2단계
 WHEN NOT MATCHED THEN			   -- 3단계
      INSERT (EC.EMPNO, EC.ENAME)
	  VALUES (EMP.EMPNO,EMP.ENAME) -- 3-1단계

• MySQL은 제공x

-- 실행전 EP_COMMISION
| EMPNO | ENAME   | COMM  | SAL   |
|-------|---------|-------|-------|
| 101   | Alice   | 500   | 2500  |
| 102   | Bob     | NULL  | 1500  |
-- EMP
| EMPNO | ENAME   | SAL   |
|-------|---------|-------|
| 101   | Alice   | 2500  |
| 102   | Bob     | 1800  |
| 103   | Charlie | 3000  |

-- 실행후 EP_COMMISION
| EMPNO | ENAME   | COMM  | SAL   |
|-------|---------|-------|-------|
| 101   | Alice   | 1000  | 2500  |
| 103   | Charlie | NULL  | NULL  |

---

🎨 4-12 테이블에서 모든 레코드 삭제하기

DELETE
  FROM EMP

---

🎨 4-13 특정레코드 삭제하기

DELETE
  FROM EMP
 WHERE DEPTNO = 10

---

🎨 4-14 단일레코드 삭제하기

• EMPNO는 기본 키라서 가능

DELETE
  FROM EMP
 WHERE EMPNO = 10

---

🎨 4-15 참조 무결성 위반 삭제하기

DELETE
  FROM EMP
 WHERE NOT EXISTS(
           SELECT *
		     FROM DEPT
			WHERE DEPT.DEPTNO = EMP.DEPTNO)

💎 논리 흐름

• EMP 테이블에서 각 레코드의 DEPTNO를 확인.
• DEPT 테이블에서 DEPTNO가 일치하는 레코드를 찾음.
• DEPTNO가 일치하지 않는 EMP 테이블의 레코드를 삭제.

---

🎨 4-16 죽복 레코드 삭제하기

/*
테이블에서 name이 중복되었을때,
가장 작은 id를 제외하고는 다 삭제하고자 하자.
*/
DELETE 
  FROM DUPES
 WHERE ID NOT IN (
    SELECT MIN(id)
      FROM DUPES
  GROUP BY NAME
);

-- 쿼리문 실행전
| id  | name    |
|-----|---------|
| 1   | Alice   |
| 2   | Bob     |
| 3   | Alice   |
| 4   | Charlie |
| 5   | Bob     |
| 6   | Alice   |

-- 쿼리문 실행후
| id  | name    |
|-----|---------|
| 1   | Alice   |
| 2   | Bob     |
| 4   | Charlie |

---

🎨 4-17 다른 테이블에서 참조된 레코드 삭제하기

DELETE
  FROM EMP
 WHERE DEPTNO IN ( SELECT DEPTNO
                     FROM DEPT_ACCIDENTS
                 GROUP BY DEPTNO
                   HAVING COUNT(*) >= 3)