DML (INSERT, UPDATE, DELETE)

DML(Data Manipulation Language)

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

INSERT INTO employee
VALUES(1, ‘홍길동’, ‘820114-1010101’, ‘hong_kd@codeit.com’, ‘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 문

테이블에 새로운 행을 추가하여 테이블의 행 개수를 증가시키는 구문
컬럼명을 기입하거나 생략하는 방법이 존재한다. 생략하면 CREATE 순서대로 값을 매칭해야 한다.
→ 가급적이 컬럼명을 기입하는 것을 권장, 보통 CREATE 순을 모른다.

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(900, ‘홍길동', '901123-1080503', 'hong_gd@codeit.com', '01055569512', 'D1', 'J8', 'S3', 4300000, 0.2, '200', SYSDATE, NULL, DEFAULT);

-- 또는

INSERT INTO employee
VALUES(900, ‘홍길동', '901123-1080503', 'hong_gd@codeit.com', '01055569512', 'D1', 'J8', 'S3', 4300000, 0.2, '200', SYSDATE, NULL, DEFAULT);
-- * 모든 컬럼에 INSERT 하고 싶은 경우 컬럼 명 생략 가능. 단, 컬럼의 순서를 지켜서 VALUES에 값 기입)

INSERT - 여러 줄 삽입(대량 삽입)

INSERT를 여러 번 반복하는 경우 이를 묶어서 한번에 삽입하는 구문이 존재, 이는 대량으로 삽입할 경우 유효하며 만일 유효하지 않은 값이 존재하면 모든 INSERT가 취소된다.
만일 모두 취소를 원하지 않고 일부만 들어가는 것을 원하는 경우 ‘ON CONFLICT’ 구문 활용

INSERT INTO employee (emp_id, emp_name, emp_no, email)
VALUES(900, '홍길동', '901123-1180503', 'test@codeit.com'),
(901, '박길동', '901124-1180503', 'test@codeit.com'),
(902, '최길동', '901126-1280503', 'test@codeit.com’);

-- 또는

INSERT INTO employee (emp_id, emp_name, emp_no, email)
VALUES (900, '홍길동', '901123-1180503', 'test@codeit.com'),
(901, '박길동', '901124-1180503', 'test@codeit.com'),
(902, '최길동', '901126-1280503', 'test@codeit.com’);
ON CONFLICT (emp_id) DO NOTHING;
-- * 모든 컬럼에 INSERT 하고 싶은 경우 컬럼 명 생략 가능. 단, 컬럼의 순서를 지켜서 VALUES에 값 기입)

INSERT 서브 쿼리 활용

INSERT문의 서브 쿼리를 활용하여 SELECT문과 결합이 가능하다.

CREATE TABLE emp_01(emp_id INT, emp_name VARCHAR(30), dept_title VARCHAR(20));
INSERT INTO emp_01(
	SELECT emp_id,
		emp_name,
		dept_title
	FROM employee
	LEFT JOIN department ON (dept_code = dept_id)
);
-- * INSERT 시 VALUES 대신 서브쿼리 이용 가능

INSERT ALL

INSERT 시 서브쿼리가 사용하는 테이블이 같은 경우 두 개 이상의 테이블에 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_dept_d2
AS SELECT emp_id, emp_name, dept_code, hire_date
  FROM employee
  WHERE dept_code = 'D1';

• EMP_DEPT_D1테이블에 EMPLOYEE테이블의 부서코드가 D1인 직원의 사번, 이름, 소속부서, 입사일 삽입

INSERT INTO emp_dept_d3 (emp_id, emp_name, dept_code, hire_date)
SELECT emp_id, emp_name, dept_code, hire_date
FROM employee
WHERE dept_code = 'D1';

UPDATE문

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

CREATE TABLE dept_copy
AS SELECT * FROM department;

UPDATE dept_copy
SET dept_title = ‘전략기획팀’
WHERE dept_id = ‘D9’;

-- 방명수 사원의 급여와 보너스율을 유재식 사원과 동일하게 변경
CREATE TABLE emp_salary
AS SELECT emp_id,
		  emp_name,
		  dept_code,
		  salary,
		  bonus
   FROM employee;

SELECT * FROM emp_salary
WHERE emp_name IN ('유재속', '방명수');

UPDATE emp_salary SET
salary = (SELECT * FROM (SELECT salary FROM
emp_salary WHERE emp_name ='유재속') as a),
BONUS = (SELECT * FROM (SELECT bonus FROM
  emp_salary WHERE
  emp_name = '유재속') as b)
WHERE emp_name = '방명수';

-- EMP_SALARY테이블에서 아시아 지역에 근무하는 직원의 보너스 포인트 0.3으로 변경
UPDATE emp_salary
SET bonus = 0.3
WHERE emp_id IN (SELECT emp_id
	FROM employee
	JOIN department ON(dept_id = dept_code)
	JOIN location ON(location_id = local_code)
	WHERE local_name LIKE ‘ASIA%’);

DELETE문

테이블의 행을 삭제하는 구문으로 테이블의 행 개수가 줄어든다.

DELETE FROM employee -- 모든 EMPLOYEE 삭제 구문

DELETE FROM employee
WHERE emp_id = 200;
-- * 가능하면 Key 값으로 삭제 권장

DELETE FROM department
WHERE dept_id = 'D1';
-- * FOREIGN KEY 제약조건이 설정되어 있는 경우 참조되고 있는 값에 대해서는 삭제 불가능

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

DELETE FROM department
WHERE dept_id = 'D1';

ALTER TABLE employee DROP CONSTRAINT employee_dept_id_fkey;
-- 외래키 제약 해제

DELETE FROM department
WHERE dept_id = 'D1';

ALTER TABLE employee
ADD CONSTRAINT employee_dept_id_fkey
  FOREIGN KEY (dept_id) REFERENCES department(dept_id);
-- 외래키 제약 다시 설정

TRUNCATE

테이블 전체 행 삭제 시 사용하며 DELETE보다 수행 속도가 빠르고 ROLLBACK을 통해 복구가 불가능하다. 또한 DELETE와 마찬가지로 FOREIGN KEY 제약조건일 때는 적용 불가능하기 때문에 제약 조건을 비활성화 해야 삭제할 수 있음.

TRUNCATE TABLE emp_salary;
SELECT * FROM emp_salary;
-- * 모든 컬럼이 삭제되긴 하지만 테이블의 구조는 남아있음

ROLLBACK;
-- * ROLLBACK 후에도 컬럼이 복구되지 않음