Oracle SQL - DML – INSERT, UPDATE, DELETE

Data Manipulation Language

"C"REATE  - INSERT

"R"EAD     - SELECT

"U"PDATE  - UPDATE

"D"ELETE   - DELETE

--> "CRUD"

 

종류
Add New Row(s)
   - INSERT INTO 테이블명 [(컬럼 리스트)] VALUES (값 리스트);
Modify Exsiting Row(s)
   -UPDATE 테이블명 SET 변경내용 [WHERE 조건];
Remove Existing Row(s)
   -DELETE FROM 테이블명 [WHERE 조건];

 

트랜잭션의 대상  - > 복구 할 수 있다.
   -트랜잭션은 DML의 집합으로 이루어짐
   -ALL or NOTHING

 

INSERT

테이블에 새로운 튜플을 삽입할 때 사용하는 명령
Syntax

INSERT INTO table_name [{column [, column ...]}]
{VALUES (value[, value...])|Subquery};

• 대응하는 colum과 value는 개수와 타입이 일치해야 한다
• 테이블 내 모든 컬럼의 내용을 삽입할 때는 column 명을 생략할 수 있지만, 이때는 CREATE TABLE 문에 기술된 컬럼 순으로 value 값들을 지정해야 한다
• Subquery를 이용하여 다른 테이블의 검색 결과를 삽입할 수 있다

묵시적 방법: 컬럼 이름, 순서 지정하지 않음. 테이블 생성시 정의한 순서에 따라 값 지정

INSERT INTO author
VALUES (1, '박경리', '토지 작가 ' );

명시적 방법: 컬럼 이름 명시적 사용. 지정되지 않은 컬럼은 NULL 자동 입력

INSERT INTO author( author_id, author_name )
VALUES (2, ‘이문열‘ );

Subquery 이용: 타 테이블로부터 데이터 복사 (테이블은 미리 존재해야 함)

INSERT INTO department_usa
  SELECT department_id, department_name
    FROM deptments
    WHERE department_anme = 'IT';

참고로 Subquery 결과를 없는 테이블을 생성하고 데이터를 복사하고자 할 때는 CREATE TABLE AS SELECT 이용

 

-- INSERT : 묵시적 방법
DESC author;
INSERT into author
values (1,'박경리','토지작가');

-- INSERT : 명시적 방법
INSERT INTO author(author_id, author_name)
values(2,'김영하');

-- 확인
SELECT * FROM author;

-- DML은 트랜젝션의 대상
-- 취소 : ROLLBACK
-- 변경사항 반영 : COMMIT

rollback;  -- 취소
commit;  -- 변경사항 반영

---

UPDATE

테이블에 있는 튜플들 중에서 특정 튜플의 내용을 갱신할 때 사용하는 명령
Syntax

UPDATE table_name
SET column=value[, column=value ...]
[WHERE condition];

※ WHERE 절이 생략된 UPDATE 문장은 해당 테이블 내의 모든 Row를 변경하므로 주의해서 사용해야 한다.

 

조건을 만족하는 레코드를 변경
  - 10번 부서원의 금여를 100 인상 & 수수료를 0으로 변경

UPDATE emp
SET sal = sal + 100, comm = 0
WHERE deptno = 10;

WHERE 절이 생략되면 모든 레코드에 적용
  - 모든 직원의 급여를 10% 인상

UPDATE emp SET sal = sal * 1.1

Subquery를 이용한 변경
   - 담당 업무가 'SCOTT'과 같은 사람들의 월급을 부서 최고액으로 변경

UPDATE emp SET sal = (SELECT MAX(sal) FROM emp)
WHERE job =
(SELECT job FROM emp WHERE ename='SCOTT');

조건절이 없으면 이런 상황이 발생할 수 있다.

DELETE

테이블에 있는 튜플들 중에서 특정 튜플을 삭제할 때 사용하는 명령

Syntax

DELETE FROM table_name
[WHERE condition];

※ WHERE 절이 생략된 DELETE 문장은 해당 테이블 내의 모든 Row를 삭제하므로 주의해서 사용해야 한다.

 

조건을 만족하는 레코드 삭제
  - 이름이 'SCOTT'인 사원 삭제

DELETE FROM emp
WHERE ename = 'SCOTT';

조건이 없으면 모든 레코드 삭제 (주의!)
  - 모든 직원 정보 삭제

DELETE FROM emp;

Subquery를 이용한 DELETE
  - 'SALES' 부서의 직원 모두 삭제

DELETE FROM emp
WHERE deptno = (SELECT deptno FROM dept
			WHERE dname = 'SALES');

데이터 입력, 수정시 자주 사용되는 Pseudo 컬럼
  - USER : 현재 사용자명
  - SYSDATE : 현재 날짜와 시간
  - ROWID : 열의 위치 정보

INSERT INTO emp(eno, hiredate) VALUES (200, SYSDATE);

DEFAULT : default 값이 정의된 컬럼에 기본 값을 입력할 경우 사용

INSERT INTO book VALUES (200,'Gems', DEFAULT);

DELETE와 TRUNCATE의 차이
  - Delete는 Rollback 가능. 그러나 대량의 log 등을 유발하므로 Truncate보다 느림

모든 DML 문은 Integrity Constraint를 어길 경우 에러 발생

 

-- delete : 테이블에서 레코드 삭제
SELECT * FROM emp123;


-- job_id가 mk_로 시작하는 사원 삭제
DELETE FROM emp123
WHERE job_ID LIKE 'MK_%';

-- 현재 급여 평균보다 높은 사람을 모두 삭제
DELETE FROM emp123
WHERE salary > (SELECT AVG(salary) FROM emp123);

Select * FROM emp123;
commit;

-- TRUNCATE 와 DELETE
-- DELETE는 ROLLBACK의 대상
-- TRUNCATE 는 ROLLBACK의 대상이 아님

DELETE from emp123;
select * from emp123;
rollback;

TRUNCATE table emp123;
rollback;
select * from emp123;