[풀스택과정] 데이터베이스 9. 데이터 조작어

1. 데이터 조작어

데이터 조작어

• 데이터 조작어 (DML:Data Manpulation Language)란?

  – 테이블에 새로운 데이터를 입력하거나 기존 데이터를 수정 또는 삭제하기 위한 명령어

• 종류

  – INSERT : 새로운 데이터 입력 명령어
  – UPDATE : 기존 데이터 수정 명령어
  – DELETE : 기존 데이터 삭제 명령어
  – MERGE : 두개의 테이블을 하나의 테이블로 병합하는 명령어

• 트랜잭션

  – 여러 개의 명령문을 하나의 논리적인 작업단위로 처리하는 기능
  – 트랜잭션 관리 명령어

    • COMMIT : 트랜잭션의 정상적인 종료를 위한 명령어
    • ROLLBACK : 트랜잭션의 비정상적인 중단을 위한 명령어

 

2. 데이터 입력

데이터 입력

• 개요

  – 테이블에 데이터를 입력하기 위한 명령인 INSERT 명령 사용
  – 데이터 입력 방법
    • 단일 행 입력 : 한번에 하나의 행을 테이블에 입력하는 방법
    • 다중 행 입력 : 서브쿼리를 이용하여 한번에 여러 행을 동시에 입력하는 방법

 

단일행 입력

• 단일 행 입력 방법

  – INTO 절에 명시한 칼럼에 VALUES 절에서 지정한 칼럼 값을 입력
  – INTO 절에 칼럼을 명시하지 않으면 테이블 생성시 정의한 칼럼 순서와 동일한 순서로 입력
  – 입력되는 데이터 타입은 칼럼의 데이터 타입과 동일해야 함
  – 입력되는 데이터의 크기는 칼럼의 크기보다 작거나 동일해야 함
  – CHAR, VARCHAR2, DATE 타입의 입력 데이터는 단일인용부호(' ')로 묶어서 입력

• 사용법

• 사용 예

– 학생 테이블에 홍길동 학생의 데이터를 입력하여라

NULL의 입력

• NULL 입력

  – 데이터를 입력하는 시점에서 해당 컬럼 값을 모르거나, 미확정
  – 묵시적인 방법

    • INSERT INTO 절에 해당 칼럼 이름과 값을 생략
    • 해당 칼럼에 NOT NULL 제약조건이 지정된 경우 불가능

  – 명시적 방법

    • VALUES 절의 칼럼 값에 NULL , ' ' 사용

 

 

묵시적으로 NULL을 입력하는 예

• 사용 예

– INSERT 명령문에서 묵시적인 방법을 이용하여 부서 테이블의 부서번호와 부서 이름을 입력하고 나머지

칼럼은 NULL을 입력하여라.

명시적으로 NULL을 입력하는 예

• 사용 예

– INSERT 명령문에서 명시적인 방법을 이용하여 부서 테이블의 부서번호와 부서 이름을 입력하고 나머지

칼럼은 NULL을 입력하여라.

날짜 데이터 입력 방법

• NULL 입력

  – 해당 시스템에서 요구하는 기본 날짜 형식으로 입력
  – UNIX 기본 날짜 형식 : "DD-MON-YY"
  – 퍼스널 오라클 : "YY/MM/DD"
  – 필요에 따라서 TO_DATE 함수 사용

 

• 사용 예

– 교수 테이블에서 입사일을 2006년 1월1일로 입력하여라.

SYSDATE 함수를 이용한 현재 날짜 입력

• SYSDATE 함수

  – 현재 시점의 날짜 값을 자동적으로 입력
  – 시스템에 저장된 현재 날짜 데이터를 반환하는 함수

• 사용 예

– 교수 테이블에서 새로운 행을 입력할 때 입사일을 현재 날짜로 입력하여라.

다중 행 입력

• 다중 행 입력 방법

  – INSERT 명령문에서 서브쿼리 절을 이용
  – INSERT 명령문에 의해 한번에 여러 행을 동시에 입력
  – 9i 버전 이후 부터

    • unconditional INSERT ALL
    • conditional INSERT ALL
    • conditional FIRST INSERT
    • pivoting INSERT지원

 

단일 테이블에 다중 행 입력

• 단일 테이블에 다중 행 입력 방법

  – INSERT 명령문에서 서브쿼리 절을 이용하여 자신이나 다른 테이블에 데이터를 복사하여 여러 행

    동시 입력
  – INSERT 명령문의 VALUES절 대신 서브쿼리에서 검색된 결과 집합을 한꺼번에 입력
  – 서브쿼리의 결과 집합은 INSERT 명령문에 지정된 칼럼 개수와 데이터 타입이 일치해야 함
  – 서브쿼리를 이용한 다중 행 입력시 테이블에 기본 키, 고유 키 제약조건이 중복되지 않도록 주의
  – 제약 조건을 위반할 경우 입력되지 않고 오류 발생

• 사용법

단일 테이블에 다중 행 입력 예

• 사용법(테이블의 데이터를 복사할 경우)

다중 행 입력 - INSERT ALL

• INSERT ALL(unconditional INSERT ALL) 명령문

– 서브쿼리의 결과 집합을 조건없이 여러 테이블에 동시에 입력
– 서브쿼리의 컬럼 이름과 데이터가 입력되는 테이블의 칼럼이 반드시 동일해야 함

• 사용법

– ALL : 서브쿼리의 결과 집합을 해당하는 INSERT절에 모두 입력
– FIRST : 서브쿼리의 결과 집합을 해당하는 첫번째 INSERT절에 입력
– subquery : 입력 데이터 집합을 정의하기 위한 서브쿼리

 

다중 행 입력 - INSERT ALL 예

• 다중 행 입력을 위한 height_info, weight_info 예제 테이블 생성

• 사용 예

– 학생 테이블에서 2학년 이상의 학생을 검색하여 height_info 테이블에는 학번, 이름, 키, weight_info

테이블에는 학번, 이름, 몸무게를 각각 입력하여라.

• 결과확인

 

 

다중행 입력 - Conditional INSERT ALL

• Conditional INSERT ALL 명령문

  – 서브쿼리의 결과 집합에 대해 WHEN 조건절에서 지정한 조건을 만족하는 행을 해당되는 테이블에 

    각각 입력
  – 서브쿼리에서 검색된 행을 만 조건이 여러 개 일 경우 해당 테이블에 모두 입력
  – ALL : WHEN~THEN~ELSE의 조건을 만족하는 서브쿼리의 모든 검색 결과를 입력하기 위한 옵션
  – WHEN 조건절 THEN : 서브쿼리의 결과 집합에 대한 비교 조건

 

• 사용법

 

  – 서브쿼리의 결과 집합 중에서 조건절 1을 만족하는 결과 행은 table1에 입력, 조건절 2를 만족하는

결과 행은 table2에 입력, 그리고 어느 조건절도 만족하지 않는 행은 table3에 입력

 

• weight_info, height_info 테이블 데이터 모두 삭제

 

다중 행 입력 - Conditional INSERT ALL 예

• 사용 예

– 학생 테이블에서 2학년 이상의 학생을 검색하여 height_info 테이블에는 키가 170보다 큰 학생의 학번,

이름, 키를 입력하고 weight_info 테이블에는 몸무게가 70보다 큰 학생의 학번, 이름, 몸무게를 각각 

입력하여라.

 

 

 

-- 425 페이지
-- 학생 테이블에서 2학년 이상의 학생을 검색하여 HEIGHT_INFO 테이블에서는 키가
-- 170보다는 큰 학생의 학번, 이름, 키를 입력하고 WEIGHT_INFO 테이블에서는
-- 몸무게가 70보다 큰 학생의 학번, 이름, 몸무게를 각각 입력하여라
INSERT ALL
WHEN HEIGHT > 170 THEN INTO HEIGHT_INFO VALUES(STUDNO, NAME, HEIGHT)
WHEN WEIGHT > 70 THEN INTO WEIGHT_INFO VALUES(STUDNO, NAME, WEIGHT)
SELECT * 
FROM STUDENT
WHERE GRADE >=2;

SELECT * FROM HEIGHT_INFO;
SELECT * FROM WEIGHT_INFO;

TRUNCATE TABLE WEIGHT_INFO;
TRUNCATE TABLE HEIGHT_INFO;

 

• 결과 확인

 

 

다중 행 입력 – Conditional-First INSERT

• Conditional-First INSERT 명령문

  – 서브쿼리의 결과 집합에 대해 WHEN 조건절에서 지정한 조건을 만족하는 첫 번째 테이블에 우선적으로

     입력하기 위한 명령문
  – 서브쿼리의 결과 집합 중에서 조건을 만족하는 첫 번째 WHEN절에서 지정한 테이블에만 입력하고

     그 외의 결과집합에서 나머지 WHEN절에 조건이 만족하면 첫 번째 조건에 INSERT한 행을 제외하고

     INSERT, 마지막에는 ELSE절에 정의된 TABLE에 INSERT

 

• 사용법

다중 행 입력 – Conditional-First INSERT 예

• weight_info, height_info 테이블 데이터 모두 삭제

 

• 사용 예

– 학생 테이블에서 2학년 이상의 학생을 검색하여 height_info 테이블에는 키가 170보다 큰 학생의 학번,

이름, 키를 입력하고 weight_info 테이블에는 몸무게가 70보다 큰 학생의 학번, 이름, 몸무게를 각각

입력하여라. 단, 키가 170보다 작고, 몸무게가 70보다 큰 학생은 weight_info 테이블만 입력한다.

다중 행 입력 – PIVOTING INSERT

• PIVOTING INSERT 명령문

  – OLTP(OnLine Transaction Processing) 업무에서 사용되는 데이터를 데이터웨어하우스 업무에서

    사용되는 분석용 데이터로 변화하는 경우에 유용
  – 하나의 행을 여러 개의 행으로 나누어서 입력하는 기능
  – Unconditional INSERT ALL 명령문과 거의 동일
  – INTO 절에서 하나의 테이블만 지정
  – 예를 들면, 5개의 칼럼으로 구성된 요일별 판매 실적 데이터를 하나의 칼럼으로 통합할 때 하나의

     칼럼으로 통합된 판매 데이트의 요일을 구분하기 위하여 요일 구분 칼럼을 추가

• 사용 예

– PIVOTING INSERT를 실습하기 위한 예제 테이블

• 사용 예

– PIVOTING INSERT 명령문을 사용하여 SALES 테이블의 요일별 데이터를 통합하여 SALES_DATA

테이블에 하나의 행으로 입력하여라.

 

 

3. 데이터 수정

• 데이터 수정 개요

  – UPDATE 명령문은 테이블에 저장된 데이터 수정을 위한 조작어
  – WHERE 절을 생략하면 테이블의 모든 행을 수정

• 사용법

  – WHERE 절을 생략하면 테이블의 모든 행을 수정
  – Condition : 칼럼이름, 표현식, 상수, 서브쿼리, 비교 연산자

 

• 사용 예

– 교수 번호가 9903인 교수의 현재 직급을 "부교수"로 수정하여라

서브쿼리를 이용한 데이터 수정

• 서브쿼리를 이용한 데이터 수정 개요

  – UPDATE 명령문의 SET절에서 서브쿼리를 이용
  – 다른 테이블에 저장된 데이터 검색하여 한꺼번에 여러 칼럼 수정
  – SET절의 칼럼 이름은 서브쿼리의 칼럼 이름과 달라도 됨
  – 데이터 타입과 칼럼 수는 반드시 일치

• 사용법

 

• 사용 예

– 서브쿼리를 이용하여 학번이 10201인 학생의 학년과 학과 번호를 10103 학번 학생의 학년과 학과 

번호와 동일하게 수정하여라.

 

• 사용 예

– 서브쿼리를 이용하여 학번이 10201인 학생의 학년과 학과 번호를 10103 학번 학생의 학년과 학과 

번호와 동일하게 수정하여라.

 

4. 데이터 삭제

데이터 삭제

• 데이터 삭제 개요

  – DELETE 명령문은 테이블에 저장된 데이터 삭제를 위한 조작어
  – WHERE 절을 생략하면 테이블의 모든 행 삭제

• 사용법

• 사용 예

– 학생 테이블에서 학번이 20103인 학생의 데이터를 삭제하여라.

서브쿼리를 이용한 데이터 삭제

• 서브쿼리를 이용한 데이터 삭제 개요

  – WHERE 절에서 서브쿼리 이용
  – 다른 테이블에 저장된 데이터를 검색하여 한꺼번에 여러 행의 내용을 삭제함
  – WHERE 절의 칼럼 이름은 서브쿼리의 칼럼 이름과 달라도 됨
  – 데이터 타입과 칼럼 수는 일치

 

• 사용법

• 사용 예

– 학생 테이블에서 컴퓨터공학과에 소속된 학생을 모두 삭제하여라.

5. MERGE

MERGE

• MERGE 개요

  – 구조가 같은 두 개의 테이블을 비교하여 하나의 테이블로 합치기 위한 데이터 조작어
  – WHEN 절의 조건절에서 결과 테이블에 해당 행이 존재하면 UPDATE 명령문에 의해 새로운 값으로 수정,      그렇지 않으면 INSERT 명령문으로 새로운 행을 삽입

  – 대량의 데이터를 분석하기 위한 업무에 유용
  – 예를 들면, 

      • 전자상거래 회사에서 하루 수만 건의 데이터를 평소에는 판매 데이터를 월 단위로 분리하여 별도의

        테이블에서 관리하다가 연말에 판매 실적 분석을 위해 하나의 테이블로 합치는 경우

 

• 사용법

– MERGE INTO : 하나의 테이블로 합치기 위한 결과 테이블
– USING : 테이블, 뷰, 서브쿼리에 대한 별명 지정
– ON : 조인 조건 지정
– WHEN MATCHED THEN : ON 절의 조인 조건을 만족하는 행 존재하면 지정된 값으로 행을 UPDATE
– WHEN NOT MATCHED THEN:ON 절의 조인 조건을 만족하지 않을 경우 새로운 행으로 INSERT
– WHEN MATCHED THEN 절과 WHEN NOT MATCHED THEN 절에서는 테이블이나 뷰 이름 대신에

   USING 절에서 지정한 별명 사용

 

• 사용 예

– professor 테이블과 professor_temp 테이블을 비교하여 professor 테이블에 있는 기존 데이터는 professor_temp 테이블의 데이터에 의해 수정하고, professor 테이블에 없는 데이터는 신규로 입력한다.

6. 트랜잭션 관리

트랙잭션 관리

• 트랜잭션 개요

  – 관계형 데이터베이스에서 실행되는 여러 개의 SQL명령문을 하나의 논리적 작업 단위로 처리하는 개념
  – COMMIT : 트랜잭션의 정상적인 종료
  – ROLLBACK : 트랜잭션의 전체 취소

COMMIT

• COMMIT 개요

  – 하나의 트랜잭션에서 실행되는 모든 SQL 명령문의 처리 결과가 하드디스크에 안전하게 보장되는 것을

     보장
  – 처리 결과를 디스크에 영구적으로 저장
  – 해당 트랜잭션에 할당된 CPU, 메모리 같은 자원이 해제
  – 서로 다른 트랜잭션을 구분하는 기준
  – COMMIT 명령문 실행하기 전에 하나의 트랜잭션 변경한 결과를 다른 트랜잭션에서 접근할 수 없도록

     방지하여 일관성 유지

 

ROLLBACK

• ROLLBACK 개요

  – 하나의 트랜잭션에서 실행된 SQL 명령문의 처리결과를 취소
  – CPU, 메모리 같은 해당 트랜잭션에 할당된 자원을 해제, 트랜잭션을 강제 종료

 

7. 시퀀스

시퀀스

• 시퀀스 개요

  – 유일한 식별자
  – 기본 키 값을 자동으로 생성하기 위하여 일련번호 생성 객체
  – 예를 들면, 웹 게시판에서 글이 등록되는 순서대로 번호를 하나씩 할당하여 기본키로 지정하고자 할 때 

     시퀀스를 편리하게 이용
  – 여러 테이블에서 공유 가능

 

• 사용법

– INCREMENT BY n : 시퀀스 번호의 증가치로 기본은 1, 일반적으로 –1 사용
– START WITH n : 시퀀스 시작번호, 기본값은 1
– MAXVALUE n : 생성 가능한 시퀀스의 최댓값
– MAXVALUE n : 시퀀스 번호를 순환적으로 사용하는 cycle로 지정한 경우, MAXVALUE에 도달한 후 

새로 시작하는 시퀀스값
– CYCLE | NOCYCLE : MAXVALUE 또는 MINVALUE에 도달한 후 시퀀스의 순환적인 시퀀스 번호의 

생성 여부 지정
– CACHE n | NOCACHE : 시퀀스 생성 속도 개선을 위해 메모리에 캐쉬하는 시퀀스 개수, 기본값은 20

 

• 사용 예

– 시작번호는 1, 증가치는 1, 최대 값은 2인 s_seq 시퀀스를 생성하여라

 

CURRVAL 과 NEXTVALUE 함수

• CURRVAL

  – 시퀀스에서 생성된 현재 번호를 확인

• NEXTVAL

  – 시퀀스에서 다음 번호 생성

• CURRVAL, NEXTVAL

  – INSERT, UPDATE 문에서 사용
  – 서브쿼리, GROUP BY, HAVING, ORDER BY, DISTINCT와 함께 사용할 수 없으며, 컬럼의 기본값으로

    사용할 수 없음

• 사용법

시퀀스를 이용한 기본 키 생성

• 개요

  – 기본키로 사용할 수 있는 적절한 칼럼이 없거나 다수의 칼럼을 결합해야 식별이 가능한 경우에는 

    시퀀스를 이용
  – NEXTVAL 함수를 사용

 

시퀀스 정의 변경

• 시퀀스 정의 변경

  – 시퀀스 생성 후 증가치, 최소 값, 최대 값 등의 정의를 수정
  – 변경된 시퀀스 정의는 새로 생성되는 시퀀스 값부터 적용
  – ALTER SEQUENCE 명령문을 사용하여 변경
  – START WITH절은 생성 직후의 시작 값을 의미, 변경 불가

• 사용법

• 시퀀스 정의 변경 시에는

– ALTER SEQUENCE 명령문 사용

• 사용 예

– s_seq 시퀀스의 최대 값을 200으로 변경하여라

• 시퀀스 삭제 시에는

– DROP SEQUENCE 명령문 사용

 

• 사용법

• 사용예

– s_seq 시퀀스를 삭제하여라.