[SQL] DML - INSERT/ UPDATE /DELETE

DML(DATA MANIPULATION LANGUAGE): 데이터 조작 언어

테이블에 새로운 데이터를 삽입, 수정, 삭제하는 구문 (DELETE, UPDATE, DELETE)

 

1.INSERT

테이블에 행을 추가하는 구문

1) 테이블의 모든 컬럼에 값 추가시

 INSERT INTO 테이블명 VALUES (값1, 값2, 값3....)
    => 해당  테이블의 모든 컬럼에 값 추가시 사용
      **유의할 점: 컬럼 순번을 지켜야함

SELECT * FROM EMPLOYEE;
INSERT INTO EMPLOYEE 
VALUES (10, '샐러리맨', , 'moneyisall@gg.or.kr', '0101263158', 'D1', 'J1', 'S4', 80000000, 0.1, NULL, SYSDATE, NULL, DEFAULT);

2) 테이블에 특정 컬럼만 선택하여 값 추가시

 INSERT INTO 테이블명 (컬럼명1, 컬럼명2, 컬럼명3) VALUES (값1, 값2, 값3) 
  == > 해당 테이블에 특정 컬럼만 선택하여 추가할 때
  ==> INSERT는 행 단위로 추가가 되므로 선택이 되지 않은 컬럼은 NULL값이 들어감 
        (DEFAULT가 지정되어 있을 경우 DEFAULT값이 들어감)
        주의사항: NOT NULL 제약조건이 걸린 컬럼은 반드시 값을 제시하여야 함

INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, EMP_NO, DEPT_CODE, JOB_CODE, SAL_LEVEL, HIRE_DATE)
VALUES ( 107, '자바전문가', '153244-5446789', 'D2','J2', 'S3', SYSDATE);

3)  INSERT INTO 테이블명(서브쿼리)

VALUES 내부 값을 기입하는 대신,
서브쿼리 조회 결과값을 INSERT하는 구문

INSERT INTO 테이블명
서브쿼리;

CREATE TABLE EMP_01 (
    EMP_ID NUMBER,
    EMP_NAME VARCHAR2(30),
    DEPT_TITLE VARCHAR2(20)
);

SELECT * FROM EMP_01;

INSERT INTO EMP_01
	(SELECT EMP_ID, EMP_NAME, DEPT_TITLE
	   FROM EMPLOYEE, DEPARTMENT
	  WHERE DEPT_CODE =DEPT_ID(+)
	) ;

4) INSERT ALL

동일한 하나의 SUBQUERY의 결과값을
두 개 이상의 테이블에 각각 INSERT할 때 사용 

INSERT ALL
INTO 테이블명1 VALUES (컬럼명, 컬럼명 ,컬럼명)
INTO 테이블명2 VALUES (컬럼명, 컬럼명 ,컬럼명)
서브쿼리;

--- EMP_JOB 테이블에는 급여가 300만원 이상인 사원들의 정보 삽입
--- EMP_DEPT테이블에는 급여가 300만원 이상인 사원들의 정보 삽입

INSERT ALL 
    INTO EMP_JOB 
VALUES (EMP_ID, EMP_NAME, JOB_NAME) 
    INTO EMP_DEPT
VALUES (EMP_ID, EMP_NAME, JOB_NAME) 
SELECT EMP_ID, EMP_NAME, JOB_NAME
  FROM EMPLOYEE 
   LEFT JOIN JOB
 USING (JOB_CODE)
   LEFT JOIN DEPARTMENT
      ON DEPT_CODE = DEPT_ID
    WHERE SALARY >= 3000000;

 

CREATE TABLE EMP_OLD 
       AS SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
       FROM EMPLOYEE 
       WHERE 1 = 0 ; 
       
CREATE TABLE EMP_NEW 
       AS SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
       FROM EMPLOYEE 
       WHERE 1 = 0 ; 

SELECT * FROM EMP_OLD;
SELECT * FROM EMP_NEW;


SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE HIRE_DATE >= '2010/01/01';

5)   INSERT ALL  - WHEN THEN

INSERT ALL
	WHEN 조건1 THEN
              INTO 테이블명1 VALUES( 컬럼명1, 컬럼명,,,,)
  	  WHEN 조건2 THEN
               INTO 테이블명2 VALUES (컬럼명..)
    서브쿼리;

  

	INSERT ALL
	  WHEN HIRE_DATE  < '2010/01/01' THEN
	  INTO EMP_OLD VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
	  WHEN HIRE_DATE >= '2010/01/01' THEN
	INTO EMP_NEW VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
	SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
	FROM EMPLOYEE;

 

2. UPDATE

테이블에 기록된 데이터를 수정하는 구문

UPDATE 테이블명 
     SET 컬럼명 = 바꿀값
       , 컬럼명 = 바꿀값
          ....
          ==> 여러개 컬럼 동시변경 가능 
WHERE 조건절 ; => 생략이 가능하나, 생략시 전체 모든 행 데이터가 변경됨

--테이블복사
CREATE TABLE DEPT_COPY 
AS SELECT * FROM DEPARTMENT;

SELECT * FROM DEPT_COPY;
---D9부서명을 을 전략기획부로 수정

UPDATE DEPT_COPY
SET DEPT_TITLE = '전략기획부';

ROLLBACK;

UPDATE DEPT_COPY
SET DEPT_TITLE = '전략기획부'
WHERE DEPT_TITLE = '총무부';

-- 테이블 복사2
CREATE TABLE EMP_SALARY
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY, BONUS
FROM EMPLOYEE;

DROP TABLE EMP_SALARY;

ROLLBACK;

UPDATE시 서브쿼리 활용
서브쿼리 수행 결과값으로 UPDATE

UPDATE 테이블명 
SET 컬럼명 = (서브쿼리) 
WHERE 조건 ; (생략가능)

SELECT * FROM EMP_SALARY;
--- EMP_SALARY테이블에서 자바전문가 사원의 부서코드를 샐러리맨 사원의 부서코드로 변경해보자

UPDATE EMP_SALARY
      SET DEPT_CODE = (           
                                      SELECT DEPT_CODE
                                      FROM EMPLOYEE
                                      WHERE EMP_NAME = '샐러리맨')
WHERE EMP_NAME = '자바전문가' ;
      

--방명수 사원의 급여와 보너스를 유재식사원의 급여와 보너스값으로 변경

UPDATE EMP_SALARY
       SET (SALARY, BONUS) = (   SELECT SALARY, BONUS
                                FROM EMPLOYEE
                                WHERE EMP_NAME = '유재식')                                                   
    WHERE EMP_NAME = '방명수';    
    

SELECT * FROM EMP_SALARY;

UPDATE EMPLOYEE
   SET EMP_ID = 200
   WHERE EMP_NAME = '송종기'; ---ORA-00001: unique constraint (KH.EMPLOYEE_PK) violated  --PRIMARY KEY제약조건 위배 (중복)
   
UPDATE EMPLOYEE
SET EMP_NAME = NULL
WHERE EMP_NAME = '송종기'; --ORA-01407: cannot update ("KH"."EMPLOYEE"."EMP_NAME") to NULL , NOT NULL 제약조건 위배


COMMIT;

3. DELETE

테이블에 기록된 데이터를 삭제하는 구문

 DELETE FROM 테이블명
    WHERE 조건; (WHERE 절은 생략가능, 생략시 해당  테이블 전체행삭제)

DELETE FROM EMPLOYEE;
SELECT * FROM EMPLOYEE;
ROLLBACK;--- 롤백시 마지막 커밋시점으로 돌아감


---샐러리맨, 자바전문가 데이터 지우기
DELETE FROM EMPLOYEE
WHERE EMP_NAME IN ( '샐러리맨', '자바전문가');


COMMIT; ---모든 변경사항을 확정하겠다

-------DEPARTMENT테이블로부터 DEPT_ID가 D1인 부서 삭제

DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D1';  ---ORA-02292: integrity constraint (KH.SYS_C007166) violated - child record found

DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D3';

ROLLBACK;

 

 

 * TRUNCATE

- 테이블 전체 행을 삭제할 때 사용(절삭) 
- DELETE보다 실행속도가 빠름
- 별도 조건 부여 불가 
- ROLLBACK불가

 

 

TRUNCATE TABLE 테이블명;	DELETE FROM 테이블명
별도 조건제시 불가	특정 조건제시 가능
수행속도 빠름	수행속도 느림
ROLLBACK불가	ROLLBACK 가능

SELECT * FROM EMP_SALARY;
DELETE FROM EMP_SALARY; -- 0.03초
ROLLBACK;

TRUNCATE TABLE EMP_SALARY; --0.15초 --- 테이블 데이터가 많아야 의미있음,,,

--TABLE EMP_SALARY 가 잘렸습니다 --ROLLBACK 불가!