[SQL] DML - INSERT, INSERT ALL, UPDATE, MERGE, DELETE

DML(Data Multipulation Language)

o  데이터 조작 언어로 테이블에 값을 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)하는 구문을 말한다.

o  DML은 커밋하기 전이면 ROLLBACK 명령어로 수행 명령을 되돌릴 수 있다.

INSERT INTO EMPLOYEE VALUES(1, ‘홍길동’, ‘820114-1010101’, ‘hong_kd@kh.or.kr’, ‘01099998888’, ‘D5’, ‘J2’, ‘S4’, 3800000, NULL, ‘200’, SYSDATE, NULL, DEFAULT);   UPDATE EMPLOYEE SET EMP_ID = 290 WHERE EMP_NAME = ‘홍길동’;   DELTE FROM EMPLOYEE WHERE EMP_NAME = ‘홍길동’;

 

INSERT

o  테이블에 새로운 행을 추가하여 테이블의 행 개수를 증가시키는 구문

o  특정테이블에 한개 ROW를 추가하는 구문으로 한 번에 한개 데이터만 삽입이 가능하다.

o  INSERT로 데이터를 추가하는 방법은 2가지 방법이 있다.

1.  지정한 컬럼에만 데이터를 추가하는 방법

    : 지정되지 않은 컬럼의 값은 NULL로 된다.

    : INSERT INTO 테이블명 (지정컬럼1, 지정컬럼2, 지정컬럼3,....) VALUES(값1, 값2, 값3, ....);

2.  전체 컬럼에 데이터를 추가하는 방법

    : 테이블에 있는 전체 컬럼의 값을 모두 작성해야 한다.

                                 -> 작성 값이 존재하는 컬럼 개수 보다 부족하거나 많을 경우 에러가 발생한다.

    : INSERT INTO 테이블명 VALUES(컬럼1값, 컬럼2값 ,컬럼3값....);

 o 예시

INSERT INTO DEPARTMENT(DEPT_ID,LOCATION_ID) VALUES('A1','L3'); SELECT * FROM DEPARTMENT; - 지정하지 않은 컬럼은 자동으로 NULL로 된다. (NOT NULL 제약조건 주의한다) INSERT INTO DEPARTMENT VALUES('A2','오라클개발부','L4'); -- 모든 컬럼의 값을 입력한다. NSERT INTO DEPARTMENT VALUES('A3'); -- 에러, 방법 2의 경우 비어 있는 컬럼 값에 자동으로 NULL이 들어가지 않는다. -- 값을 지정해주거나 NULL을 작성해준다.

 

o  INSERT문에 서브 쿼리를 이용해서 테이블을 복사할 수도 있다.

INSERT INTO INSERT_TEST(SELECT EMP_ID,EMP_NAME,DEPT_TITLE                         FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID                         WHERE DEPT_CODE='D5'); SELECT * FROM INSERT_TEST;   * INSERT 시 VALUES 대신 서브쿼리 사용

  

INSERT ALL

o  INSERT 시 서브 쿼리가 사용하는 테이블이 같은 경우 두 개 이상의 테이블에 INSERT ALL을 이용하여 한 번에 삽입 가능

o  단, 각 서브 쿼리의 조건절이 같아야 한다.

CREATE TABLE EMP_HIRE_DATE AS SELECT EMP_ID,EMP_NAME, HIRE_DATE     FROM EMPLOYEE WHERE 1=0;   CREATE TABLE EMP_MANAGE AS SELECT EMP_ID,EMP_NAME, MANAGER_ID     FROM EMPLOYEE WHERE 1=0;   INSERT ALL INTO EMP_HIRE_DATE VALUES(EMP_ID, EMP_NAME, HIRE_DATE) INTO EMP_MANAGE VALUES(EMP_ID, EMP_NAME, MANAGER_ID) -- 각 각의 테이블에 하나의 결과가 각각 입력된다. SELECT EMP_ID, EMP_NAME, HIRE_DATE, MANAGER_ID FROM EMPLOYEE;

 

EMPLOYEE테이블의 구조를 복사하여 사번, 이름, 입사일, 급여를 기록할 수 있는 테이블 EMP_OLD와 EMP_NEW 생성  CREATE TABLE EMP_OLD AS SELECT EMP_ID, EMP_NAME, SALARY, HIRE_DATE FROM EMPLOYEE WHERE 1=0;   CREATE TABLE EMP_NEW AS SELECT EMP_ID, EMP_NAME, SALARY, HIRE_DATE FROM EMPLOYEE WHERE 1=0;   - EMPLOYEE테이블의 입사일 기준으로 2000년 1월 1일 이전에 입사한 사원의 사번, 이름, 입사일, 급여를 조회해서 EMP_OLD테이블에 삽입하고 그 후에 입사한 사원의 정보는 EMP_NEW테이블에 삽입 INSERT ALL WHEN HIRE_DATE < ‘2000/01/01’ THEN INTO EMP_OLD VALUES(EMP_ID, EMP_NAME, SALARY, HIRE_DATE) --2000년 1월 1일 이전 입사자 WHEN HIRE_DATE >= ‘2000/01/01’ THEN INTO EMP_NEW VALUES(EMP_ID, EMP_NAME, SALARY, HIRE_DATE) --2000년 1월 1일 이후 입사자 SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY FROM EMPLOYEE;

SELECT * FROM EMP_OLD;

SELECT * FROM EMP_NEW;

 

UPDATE

o  테이블에 기록된 컬럼 값을 수정하는 구문으로 테이블 전체 행 개수는 변화 없음

o  한개 또는 그이상의 ROW의 값을 수정하는 명령어

o  사용 형식 : UPDATE 테이블명 SET 수정할 컬럼명=수정할 값,수정할 컬럼명=수정할 값...[WHERE 조건식]

* 가능하면 WHERE은 사용한다. WHERE 미사용 시 모든 데이터가 일괄 수정된다…

o  테이블에 컬럼을 수정 시 WHERE절을 사용해서 특정 값만 필터링 후 수정한다.

CREATE TABLE DEPT_COPY AS SELECT * FROM DEPARTMENT;   'A1'부서의 부서명을 자바개발부로 변경 UPDATE DEPT_COPY SET DEPT_TITLE='자바 개발부' WHERE DEPT_ID='A1';   급여가 200만원 미만인 사원의 보너스를 0.4변경하기 UPDATE EMP_COPY SET BONUS=0.4 WHERE SALARY < 2000000;   * WHERE 조건을 설정하지 않으면 모든 행의 컬럼 값이 변경됨

 

o  UPDATE문에서도 서브 쿼리를 사용하여 값을 수정할 수 있다.

방명수의 월급을 심봉선의 월급과 동일하게 변경하기 UPDATE EMP_COPY SET SALARY=(SELECT SALARY FROM EMP_COPY WHERE EMP_NAME='심봉선') WHERE EMP_NAME='방명수'; SELECT * FROM EMP_COPY WHERE EMP_NAME='방명수' OR EMP_NAME='심봉선';

  

MERGE

o  구조가 같은 두 개의 테이블을 하나의 테이블로 합치는 기능 제공

o  두 테이블에서 지정하는 조건의 값이 존재하면 UPDATE되고 조건의 값이 없으면 INSERT함

o  사용 형식 : MERGE INTO 기준이되는 테이블 USING 합칠 테이블명 ON (동일여부 판단 컬럼)

WHEN MATCHED THEN DML구문

WHEN NOT MATCHED THEN DML구문

CREATE TABLE TBL_MERGE01(   -- 같은 내용의 중복 테이블 생성     ID VARCHAR2(20),     NAME VARCHAR2(20) ); CREATE TABLE TBL_MERGE02(     ID VARCHAR2(20),     NAME VARCHAR2(20) ); INSERT INTO TBL_MERGE01 VALUES ('USER01','CHAN'); INSERT INTO TBL_MERGE01 VALUES ('USER02',' CHAN '); INSERT INTO TBL_MERGE02 VALUES ('USER02',' CHAN '); INSERT INTO TBL_MERGE02 VALUES ('USER03',' CHAN ');   MERGE INTO TBl_MERGE01 USING TBL_MERGE02 ON (TBL_MERGE01.ID=TBL_MERGE02.ID)    -- ID값이 같은 경우 WHEN 구문 수행 WHEN MATCHED THEN UPDATE SET TBL_MERGE01.NAME=TBL_MERGE02.NAME -- ID값이 같은 경우 TBL_MERGE01.NAME를 지우고 TBL_MERGE02를 합친다.                  -- UPDATE SET 앞에 테이블명이 기재되어 있기에 SET앞에 테이블명을 작성하지 않아도 된다. WHEN NOT MATCHED THEN INSERT VALUES(TBL_MERGE02.ID,TBL_MERGE02.NAME);

  

DELETE

o  테이블의 행을 삭제하는 구문으로 테이블의 행 개수가 줄어듦

o  사용 형식 : DELETE FROM 테이블 [WHERE 조건식]

    * WHERE을 생략하면 전체를 지우는 것

o  DELETE사용 시 주의사항 : 자신을 참조하고 있는 자식테이블이 있는 경우 삭제 불가능(FOREIGN KEY)

o  FOREIGN KEY 설정 시 ON DELETE 옵션을 설정했을 경우에는 가능

DELETE FROM EMPLOYEE WHERE EMP_NAME = ‘장채현’; * WHERE조건을 설정하지 않으면 모든 행 삭제   DELETE FROM DEPARTMENT WHERE DEPT_ID = ‘D1’; * FOREIGN KEY 제약조건이 설정되어 있는 경우 참조되고 있는 값에 대해서는 삭제 불가능

o  삭제 시 FOREIGN KEY 제약조건으로 컬럼 삭제가 불가능한 경우 제약조건을 비활성화 할 수 있음

DELETE FROM DEPARTMENT WHERE DEPT_ID = ‘D1’; ALTER TABLE EMPLOYEE DISABLE CONSTRAINT EMP_DEPTCODE_FK CASCADE;   DELETE FROM DEPARTMENT WHERE DEPT_ID = ‘D1’; ALTER TABLE EMPLOYEE ENABLE CONSTRAINT EMP_DEPTCODE_FK; * 비활성화 된 제약 조건을 다시 활성화 시킬 수 있음