데이터조작언어(DML) - 2024.09.11

수호천사임다·2024년 9월 11일

오라클

목록 보기
22/53

데이터 조작 언어란 ?

  • 데이터 조작 언어는 사용자로 하여금 데이터를 처리할 수 있게 하는 도구로서, 사용자(응용 프로그램)와 DMBS 간의 인터페이스를 제공한다.
    • 응용프로그램을 통하여 사용자가 DB의 데이터를 실질적으로 조작할 수 있도록 하기 위해 다양한 언어에 DB기능을 추가해서 만든 언어이다.
    • ROLLBACK이 가능하다.
    데이터 조작 언어 종류
    • INSERT 문 - 테이블에 행(row)을 삽입하기 위한 명령어
    • UPDATE 문 - 테이블의 데이터의 수정하기 위한 명령어
    • DELETE 문 - 테이블의 데이터를 삭젲하기 위한 명령어
    • MERGE 문 - 두 테이블의 데이터를 하나의 테이블로 병합하기 위한 명령어
    • SELECT 문 - SELECT는 가장 일반적으로 사용되는 DQL 명령이며, DML의 구성 요소로 간주되기도 한다.

트랜잭션

  • 데이터베이스의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위 또는 한번에 모두 수행되어야 할 일련의 연산들을 의미한다.

    특징

  • 트랙잭션은 데이터베이스 시스템에서 병행 제어 및 회복 작업 시 처리되는 작업의 논리적 단위
    • 사용자가 시스템에 대한 서비스 요구 시 시스템이 응답하기 위한 상태 변환 과정의 작업단위
    • 하나의 트랜잭션은 COMMIT되거나 ROLLBACK 된다.
    오라클은 INSERT, UPDATE, DELETE 명령을 실행하면 자동으로 트랜잭션이 완료된 상태가 아니므로 다음 중 하나를 이용하여 트랜잭션을 완료 해야 하낟.
  • COMMIT
    • 트랙잭션이 완료(INSERT, UPDATE, DELETE 완료)된 상태로 데이터베이스에 저장 된다. (COMMIT을 안 하면 데이터베이스에 저장이 안 된다.)
  • ROLLBACK
    • 트랙잭션이 취소(INSERT, UPDATE, DELETE 취소)된 상태로 데이터베이스에 저장 된다.

INSERT 구문

  • 테이블에 새로운 행(ROW)를 추가할 때 사용하는 SQL문
  • 테이블에 데이터를 입력하기 위해서는 사용자 소유의 테이블이거나 테이블에 대해 INSERT 권한을 가져야 한다.
  • INSERT문을 사용하여 데이터를 입력 후에 COMMIT 또는 ROLLBACK 명령을 사용하여 트랜잭션을 완료해야 한다.
INSERT INTO 테이블명 VALUES(값, 값);
INSERT INTO 테이블명 (컬럼, 컬럼) VALUES (값, 값);
1. 타입이 안 맞을때 2.가상컬럼일때 , 제약조건을 지키자 
INSERT INTO test1(num, birth, name) VALUES(2, '1999-09-09', '박보검');

CREATE TABLE test1 ( -- test1 테이블 생성 
    num NUMBER PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    birth DATE NOT NULL,
    meno VARCHAR2(1000)
);

-- 모든 컬럼 값을 추가하는 경우에만 컬럼명을 생략할 수 있다.
INSERT INTO test1 VALUES(1, '김자바', '2000-10-10', '조퇴하고싶다');
SELECT * FROM test1;

-- 컬럼명 명시
INSERT INTO test1(num, birth, name) VALUES(2, '1999-09-09', '박보검');
SELECT * FROM test1;

INSERT INTO test1(num, name, birth, meno) 
VALUES(4, '호자바', TO_DATE('05/09/09','MM/DD/RR'), '테스트');

INSERT INTO test1 VALUES(2, '다자바', '2020-10-11'); -- 에러 : 컬럼 개수

INSERT INTO test1 VALUES(2, '2021-11-11', '엄자바', '테스트'); -- 에러 타입 일치X

COMMIT;

-- ORA-01400 : 에러 name은 null이 될 수 없다. 
SQL 오류: ORA-01400: NULL을 ("SKY"."TEST1"."NAME") 안에 삽입할 수 없습니다

INSERT INTO test1(num, name, birth, meno) VALUES(4, null, '1999-10-10', '테스트');

ORA-12899: "SKY"."TEST1"."NAME" 열에 대한 값이 너무 큼(실제: 36, 최대값: 30)하세요

INSERT ALL

  • 기본형식
INSERT ALL
	INTO 테이블명1 [(컬럼명1, 컬럼명2)] VALUES (expr1, expr2, expr_n)
    INTO 테이블명2 [(컬럼명1, 컬럼명2)] VALUES (expr1, expr2, expr_n)
	...
    subquery;
  • 설명
    - 다중 테이블에 다중 행을 입력 한다.
    • INTO 절과 VALUES 절에 기술한 컬럼의 개수와 데이터 타입은 동일해야 한다.
CREATE TABLE emp2 AS
    SELECT empNO, name, dept, pos FROM emp WHERE 1 = 0;

DROP TABLE emp3 PURGE;
CREATE TABLE emp3 AS
    SELECT empNO, sal, bonus FROM emp WHERE 1 = 0;
    
INSERT ALL
    INTO emp2 (empNO, name, dept, pos) VALUES('9999', '나자바', '개발부', '사원')
    INTO emp3 (empNO, sal, bonus) VALUES('9999', 2500000, 100000)
SELECT * FROM dual;

INSERT ALL 조건문

  • 구문 형식
INSERT { ALL | FIRST }
	WHEN 조건 THEN
    	INTO 테이블명1 [(컬럼명1, 컬럼명2, 컬럼_n)] VALUES (expr1, epxr2, expr_n)
	WHEN 조건 THEN
    	INTO 테이블명2 [(컬럼명1, 컬럼명2, 컬럼_n)] VALUES (expr1, epxr2, expr_n)
     ...
     ELSE
    INTO 테이블_N [(컬럼명1, 컬럼명2, 컬럼_n)] VALUES (expr1, epxr2, expr_n)
subquery;

설명

  • subquery를 이용하여 조건 절에 만족하는 하나 이상의 테이블에 여러 행을 입력한다.
  • ALL
    - 조건 절에 만족하는 모든 테이블에 행(row)을 입력한다.
  • FIRST
    - 조건 절에 만족하는 첫 번째 테이블에 행(row)을 입력한다.
CREATE TABLE emp4 AS
    SELECT empNo, name, rrn, dept, pos FROM emp WHERE 1 = 0;
    
CREATE TABLE emp5 AS
    SELECT empNo, name, rrn, dept, pos FROM emp WHERE 1 = 0;
    
INSERT ALL
    WHEN MOD( SUBSTR(rrn, 8,1), 2) = 0 THEN
        INTO emp4 VALUES (empNo, name, rrn, dept, pos)
    WHEN MOD( SUBSTR(rrn, 8,1), 2) = 1 THEN
        INTO emp5 VALUES (empNo, name, rrn, dept, pos)
    SELECT * FROM emp;

UPDATE 구문

개요

  • 테이블에 저장된 데이터를 수정하기 위해서 사용하는 명령문이다.
  • WHERE 절을 사용하지 않을 경우는 테이블에 있는 모든 행이 수정된다.
  • 컬럼에 제약 조건이 설정된 경우, 제약조건에 위반되는 값으로 수정하면 오류가 발생한다.
  • UPDATE 문을 사용하여 데이터를 수정 후에 COMMIT 또는 ROLLBACK 명령을 사용하여 트랜잭션을 완료해야 한다.
  • subquert를 이용한 하나의 컬럼 UPDATE
  • 업데이트 건수가 많을수록 좋지않다..

구문 형식

UPDATE 테이블명 SET 컬럼명1 = expr1 [, 컬럼명2 = expr2, ...] WHERE condition];
수정 할 컬럼과 subquery의 컬럼 개수 및 데이터 타입이 일치해야 하며, subquery의 결과는 하나의 행만 가능하다.
UPDATE emp_score SET excel = 100; -- 이렇게 할 경우 모든 excel 값 변경이 된다.

UPDATE emp_score SET com = 90, excel = 95 WHERE empNo = '1002';

문제1

emp_score: empNo, com, excel, word, tot, avg, grade
tot = com + excel + word
avg = (com + excel + wrod) / 3
grade : 모든 과목의 점수가 40 이상이고 평균 60 이상이면 합격,
평균이 60 이상이고 한과목이라도 40 미만이면 과학 나머지는 불합격
grade를 계산할때는 평균을 반올림 하지 않는다. 

SELECT empNo, com, excel, word, (com + excel + word) tot, ROUND((com + excel + word) / 3, 1) ave,
    CASE
        WHEN com >= 40 AND excel >= 40 AND word >= 40 AND (com + excel + word) / 3 >= 60 THEN '합격'
        WHEN (com + excel + word) / 3 >= 60 THEN '과락'
    ELSE '불합격'
    END grade
FROM emp_score;

문제2

개발부의 empNo, com, excel, word만 출력

SELECT empNo, com, excel, word
FROM emp_score
WHERE empNo IN (SELECT empNo FROM emp WHERE dept = '개발부');

subquery를 이용한 여러 컬럼 UPDATE

기본 형식

UPDATE 테이블명 SET colum = (subquery) [WHERE condition];
수정 할 컬럼과 subquery의 컬럼 개수 및 데이터 타입이 일치해야 하며, subquery의 결과는 하나의 행만 가능 한다.
-- 서브쿼리로 수정
UPDATE emp_score SET (excel, word) = (SELECT 100, 100 FROM dual)
WHERE empNo = '1001';

UPDATE emp_score SET excel = 100, word = 100
WHERE empNo = '1001';

UPDATE emp_score SET empNo = '2001' -- 무겨렁 제약조건 부모키 위반 
WHERE empNo = '1001';
  • 제약조건을 위반하면 수정 할 수 없다.

-- FROM 쓰고 WHERE 쓰고 GROUP BY 쓰고 HAVING 쓰고 SELECT 쓰자 .

DELETE

개요

  • 테이블에 저장된 데이터를 삭제하기 위해서 사용하는 명령문이다.
  • WHERE 절을 사용하지 않을 경우는 테이블에 있는 모든 행이 삭제된다.
  • DELETE 문을 사용하여 데이터를 삭제 후에 COMMIT 또는 ROLLBACK 명령을 사용하여 트랜잭션을 완료해야한다.

구문 형식

DELETE [ FROM ] 테이블명 [WHWERE 조건];

CREATE TABLE emp1 AS
    SELECT * FROM emp;
    
CREATE TABLE emp_score1 AS
    SELECT * FROM emp_score;
    
DELETE FROM emp1 WHERE dept = '영업부';
SELECT * FROM emp1;
COMMIT;

DELETE FROM emp_score1
WHERE empNo IN (SELECT empNo FROM emp1 WHERE dept = '개발부');

-- 모든 레코드 삭제 
DELETE FROM emp1
COMMIT;
SELECT * FROM emp1;

TRUNCATE TABLE emp_score1;
SELECT * FROM emp_score1;

DROP TABLE 한 건 복구 불가능 DELETE는 복구 가능

오라클에서 삭제한 20분전의 테이블을 볼 수 있는 방법

AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '20' MINUTE );

-- 20분 전의 emp 테이블로 삭제된 레코드 복원
INSERT INTO emp (
    SELECT * FROM emp
    AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '20' MINUTE )
    WHERE city = '서울'
);
COMMIT;
-- 레코드의 추가된 순서는 중요하지 않다. 조건을 주고 ODERY BY를 하기 때문.

MERGE 구문(개발자는 사용 x)

개요

  • MERGE문은 조건에 따라서 데이터의 삽입, 갱신, 삭제 작업을 한번에 할 수 있다.
  • MERGE는 특정 조건에 데이터가 존재하는 경우에는 해당 행(row)을 원하는 값으로 UPDATE(DELETE 포함 가능) 하고, 없는 경우에는 새로운 데이터를 INSERT 한다.
  • MERGE문을 사용하면, 여러 개의 INSERT, UPDATE 및 DELETE DML 문을 피할 수 있다.
  • MERGE문을 사용하여 데이터를 병합 후에 COMMIT 또는 ROLLBACK 명령을 사용하여 트랜잭션을 완료해야 한다.

사용법

MERGE INTO emp1 e1 -- 대상
    USING emp2 e2  -- 비교테이블 
    ON (e1.empNo = e2.empNo) -- 조건
    WHEN MATCHED THEN 
        UPDATE SET e1.sal = e1.sal + e2.sal -- sal 값을 합친다
    WHEN NOT MATCHED THEN
        INSERT ( e1.empNo, e1.name, e1.city, e1.dept, e1.sal) 
            VALUES(e2.empNo, e2.name, e2.city, e2.dept, e2.sal);
COMMIT;
profile
수호천사임다
이전 포스트

DROP

다음 포스트

INSERT - 24.09-11

0개의 댓글