[SQL] Oracle, 데이터 조작어(DML)

데이터 조작어

:SELECT문으로 조회한 테이블에 데이터를 추가, 변경, 삭제할 때 사용하는 명령어

 

-사전 작업

--1: INSERT
--DEPT 테이블과 동일한 테이블 생성
CREATE TABLE DEPT_TEMP
    AS SELECT * FROM DEPT;
    
SELECT * FROM DEPT_TEMP;

--2: INSERT
CREATE TABLE EMP_TEMP
    AS SELECT *
        FROM EMP
        WHERE 1<>1;
-- 1<>1 조건을 이용해 데이터를 제외하고 열 구조만 가지는 테이블 생성이 가능하다.

--3: UPDATE
CREATE TABLE DEPT_TEMP2
    AS SELECT * FROM DEPT;
    
--4: DELETE
CREATE TABLE EMP_TEMP2
    AS SELECT * FROM EMP;
    
--IF, 테이블 잘못 생성시
DROP TABLE DEPT_TEMP;

 

-INSERT문

INSERT INTO DEPT_TEMP(DEPTNO, DNAME, LOC)
VALUES (50, 'DATABASE', 'SEOUL');

INSERT INTO DEPT_TEMP
VALUES (60, 'NETWORK', 'BUSAN');

INSERT INTO EMP_TEMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES(2111, '이순신', 'MANAGER', 9999, TO_DATE('07/01/2001', 'DD/MM/YYYY'), 4000, NULL, 20);

INSERT INTO EMP_TEMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO
        FROM EMP E, SALGRADE S
        WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
        AND S.GRADE = 1;
--서브쿼리 사용시 VALUES절은 사용하지 않는다. 자료형과 열 개수가 일치해야 한다.

SELECT * FROM EMP_TEMP;

 

-UPDATE문

--데이터 전체 수정
UPDATE DEPT_TEMP2
SET LOC='SEOUL';

--데이터 일부 수정
UPDATE DEPT_TEMP2
SET DNAME='DATABASE',
    LOC='SEOUL'
WHERE DEPTNO=40;

--여러열 동시에 수정
UPDATE DEPT_TEMP2
SET (DNAME, LOC) = (SELECT DNAME, LOC FROM DEPT WHERE DEPTNO = 40)
WHERE DEPTNO=40;

--참고
--ROLLBACK: 수정한 내용 되돌리기(DML 명령어 실행 취소하는 명령어)
ROLLBACK;

 

-DELETE

--데이터 일부 삭제
DELETE FROM EMP_TEMP2
WHERE JOB = 'MANAGER';

--데이터 전체 삭제
DELETE FROM EMP_TEMP2

 

-종합 예제

--1
INSERT INTO CHAP10HW_DEPT(DEPTNO, DNAME, LOC)
VALUES(50, 'ORACLE', 'BUSAN');

INSERT INTO CHAP10HW_DEPT(DEPTNO, DNAME, LOC)
VALUES(60, 'SQL', 'ILSAN');

INSERT INTO CHAP10HW_DEPT(DEPTNO, DNAME, LOC)
VALUES(70, 'SELECT', 'INCHEON');

INSERT INTO CHAP10HW_DEPT(DEPTNO, DNAME, LOC)
VALUES(80, 'DML', 'BUNDANG');

--2
INSERT INTO CHAP10HW_EMP
VALUES(7201, 'TEST_USER1', 'MANAGER', 7788, TO_DATE('2016-01-02', 'YYYY-MM-DD'), 4500, NULL, 50);
 
INSERT INTO CHAP10HW_EMP
VALUES(7202, 'TEST_USER2', 'CLERK', 7201, TO_DATE('2016-02-21', 'YYYY-MM-DD'), 1800, NULL, 50);
 
INSERT INTO CHAP10HW_EMP
VALUES(7203, 'TEST_USER3', 'ANALYST', 7201, TO_DATE('2016-04-11', 'YYYY-MM-DD'), 3400, NULL, 60);
 
INSERT INTO CHAP10HW_EMP
VALUES(7204, 'TEST_USER4', 'SALESMAN', 7201, TO_DATE('2016-05-31', 'YYYY-MM-DD'), 2700, 300, 60);
 
INSERT INTO CHAP10HW_EMP
VALUES(7205, 'TEST_USER5', 'CLERK', 7201, TO_DATE('2016-07-20', 'YYYY-MM-DD'), 2600, NULL, 70);
 
INSERT INTO CHAP10HW_EMP
VALUES(7206, 'TEST_USER6', 'CLERK', 7201, TO_DATE('2016-09-08', 'YYYY-MM-DD'), 2600, NULL, 70);
 
INSERT INTO CHAP10HW_EMP
VALUES(7207, 'TEST_USER7', 'LECTURER', 7201, TO_DATE('2016-10-28', 'YYYY-MM-DD'), 2300, NULL, 80);
 
INSERT INTO CHAP10HW_EMP
VALUES(7208, 'TEST_USER8', 'STUDENT', 7201, TO_DATE('2018-03-09', 'YYYY-MM-DD'), 1200, NULL, 80);

--3
UPDATE CHAP10HW_EMP
SET DEPTNO = 70
WHERE SAL>(SELECT AVG(SAL) FROM CHAP10HW_EMP WHERE DEPTNO=50);

--4
UPDATE CHAP10HW_EMP
SET DEPTNO = 80, SAL = SAL*1.1
WHERE HIREDATE > (SELECT MIN(HIREDATE) FROM CHAP10HW_EMP WHERE DEPTNO=60);

--5
DELETE FROM CHAP10HW_EMP
WHERE SAL BETWEEN (SELECT LOSAL FROM SALGRADE WHERE GRADE = 5) AND (SELECT HISAL FROM SALGRADE WHERE GRADE = 5);

DELETE FROM CHAP10HW_EMP
WHERE EMPNO IN (SELECT E.EMPNO FROM CHAP10HW_EMP E, CHAP10HW_SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND S.GRADE=5);