DML(INSERT, UPDATE,DELETE)

▶ DML(Data Manipulation Language)

---

▶ INSERT

---

▶ INSERT ALL

---

▶ UPDATE

---

▶ DELETE

---

▶ TRUNCATE

---

 

-- DML : 데이터 조작 언어 (INSERT, UPDATE, DELETE)
--       데이터를 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)하는 구문
-- INSERT
INSERT INTO EMPLOYEE VALUES(900, '강건강', '990909-1547824', 'jang_ch@kh.or.kr', '01055569512', 'D1', 'J8',
'S3', 4300000, 0.2, '200', SYSDATE, NULL, DEFAULT);

SELECT * FROM EMPLOYEE WHERE EMP_NAME = '강건강';

-- INSERT INTO 테이블명 VALUES
-- INSERT INTO 테이블명 (컬럼명1, 컬럼명2, 컬럼명3, ...) VALUES 
-- 1반은 테이블의 컬럼 순서를 다 외우고 있어야 함

INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, EMP_NO, EMAIL, PHONE, DEPT_CODE,
JOB_CODE, SAL_LEVEL, SALARY, BONUS, MANAGER_ID,
HIRE_DATE, ENT_DATE, ENT_YN)
VALUES(901, '장채현', '901123-1080503', 'jang_ch@kh.or.kr', '01055569512', 'D1', 'J8',
'S3', 4300000, 0.2, '200', SYSDATE, NULL, DEFAULT);

CREATE TABLE EMPLOYEE_COPY AS SELECT * FROM EMPLOYEE;

CREATE TABLE EMPLOYEE_COPY2 AS SELECT EMP_ID, EMP_NAME, SALARY, DEPT_TITLE, JOB_NAME 
    FROM EMPLOYEE
        LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
        JOIN JOB USING(JOB_CODE);
        

-- INSERT ALL
CREATE TABLE EMP_DEPT_D1
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE
    FROM EMPLOYEE
    WHERE 1=0;  --언제나 거짓이다 데이터는 가져오지않는다
        
CREATE TABLE EMP_MANAGER
AS SELECT EMP_ID, EMP_NAME, MANAGER_ID
    FROM EMPLOYEE
    WHERE 1 = 0;        
        
INSERT ALL
    INTO EMP_DEPT_D1 VALUES(EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE) -- 이것도 동일하게 컬럼명 쓸수있음
    INTO EMP_MANAGER VALUES(EMP_ID, EMP_NAME, MANAGER_ID)
    SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE, MANAGER_ID
    FROM EMPLOYEE
    WHERE DEPT_CODE = 'D1';     
   
   
/* - EMPLOYEE테이블의 입사일 기준으로 2000년 1월 1일 이전에 입사한 사원의 사번,
이름, 입사일, 급여를 조회해서 EMP_OLD테이블에 삽입하고
그 후에 입사한 사원의 정보는 EMP_NEW테이블에 삽입  */
 
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;    

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

commit;

-- UPDATE
-- UPDATE 테이블명 SET 컬럼명= 바꿀값 [WHERE 조건];
CREATE TABLE DEPT_COPY
    AS SELECT * FROM DEPARTMENT;
    
UPDATE DEPT_COPY
    SET DEPT_TITLE = '전략기획팀'
    WHERE DEPT_ID = 'D9';

SELECT * FROM EMPLOYEE_COPY WHERE EMP_NAME IN('유재식','방명수');

--▶ DELETE
DELETE FROM EMPLOYEE
WHERE EMP_NAME = '장채현';    

ROLLBACK;

-- CASCADE -- 제약조선 비활성화

SELECT * FROM EMP_DEPT_D1;

DELETE FROM EMP_DEPT_D1;

TRUNCATE TABLE EMP_DEPT_D1; -- 완전 잘라내기 롤백 불가