23-02-07(1) 데이터베이스

INSERT FIRST
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;

※ 데이터 조작어

1. Conditional INSERT ALL

예제) 학생테이블에서 2학년이상의 학생을 검색하여
-- 학생테이블에서2학년이상의학생을검색하여height_info 테이블에는키가170보다큰학생의학번, 이름, 키를입력하고
-- weight_info 테이블에는몸무게가70보다큰학생의학번, 이름, 몸무게를각각입력하여라.

 

 

 

  

2. PIVOTING INSERT

 - OLTP(OnLine Transaction Processing)  업무에서 사용되는 데이터를 데이터 웨어하우스 어무에서 ㅏ용되는 분석용 데이터로 변환하는 경우에 유용

 -  하나의 행을 여러개의 행을 나누어서 입력하는 기능

 - Unconditional INSERT ALL 명령문과 거의 동일

 - INTO절에서 하나의 테이블만 지정

 - 예) 5개의 칼럼으로 구성된 요일별 판매 실적 데이터를 하나의 칼럼으로 통합할때 하나의 칼럼으로 통합된 판매 데이트의 요일을 구분하기 위하여 요일 구분 칼럼을 추가

 

* PIVOTING INSERT를 실습하기 위한 예제 테이블 생성

CREATE TABLE SALES (
    SALES_NO NUMBER(4),
    WEEK_NO NUMBER(2),
    SALES_MON NUMBER(7,2),
    SALES_TUE NUMBER(7,2),
    SALES_WED NUMBER(7,2),
    SALES_THU NUMBER(7,2),
    SALES_FRI NUMBER(7,2));

INSERT INTO SALES VALUES(1101, 4, 100, 150, 80, 60, 120);
INSERT INTO SALES VALUES(1102, 5, 300, 300, 230, 120, 150);

CREATE TABLE SALES_DATA (
    SALE_NO NUMBER(4),
    WEEK_NO NUMBER(2),
    DAY_NO NUMBER(2),
    SALES NUMBER(7,2));

예제)

INSERT ALL 
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, '1', SALES_MON)
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, '2', SALES_TUE)
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, '3', SALES_WED)
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, '4', SALES_THU)
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, '5', SALES_FRI)
SELECT * FROM SALES;

SELECT * FROM SALES_DATA;

3. 데이터 수정

 1) 개요

  - UPDATE 명령문은 테이블에 저장된 데이터 수정을 위한 조작어

 

3-1) 사용법

 WHERE절을 생략하면 테이블의 모든 행을 수정

CONDITION : 칼럼이름, 표현식, 상수, 서브쿼리, 비교연산자

 

예제) 교수번호가 9903인 현재 직급을 '부교수'로 수정

UPDATE PROFESSOR
SET POSITION = '부교수'
WHERE PROFNO = 9903;

3-2) 서브쿼리를 이용한 데이터 수정

 - UPDATE 명령문의 SET절에서 서브쿼리 이용

 - 다른 테이블에 저장된 데이터 검색하여 한꺼번에 여러 칼럼수정

 - SET절의 칼럼 이름은 서브쿼리의 칼럼 이름과 달라도 됨

 - 데이터 타입과 칼럼 수는 반드시 일치

예제) 서브쿼리를 이용하여 학번이 10201인 학생의 학년과 학과번호를 10103학번 학생의 학년과 학과번호를 동일하게 수정

UPDATE STUDENT SET
(GRADE, DEPTNO) = (
    SELECT GRADE, DEPTNO
    FROM STUDENT
    WHERE STUDNO = 10103
)
WHERE STUDNO = 10201;

 

4. 데이터 삭제

 4-1) 개요

  - DELETE 명령문은 테이블에 저장된 데이터 삭제를 위한 조작어

  - WHERE절을 생략하면 테이블의 모든 행 삭제

  - FROM절 생략 가능

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

DELETE STUDENT
WHERE STUDNO = 20103;

 

4-2) 서브쿼리를 이용한 데이터 삭제

 - WHERE절에서 서브쿼리 이용

 - 다른 테이블에 저장된 데이터를 검색하여 한꺼번에 여러행의 내용을 삭제함

 - 데이터 타입과 컬럼수는 일치

 

예제) 학생 테이블에서 컴퓨터공학과에 소속된 학생을 모두 삭제

DELETE 
FROM STUDENT 
WHERE DEPTNO = (
    SELECT DEPTNO 
    FROM DEPARTMENT 
    WHERE DNAME = '컴퓨터공학과'
);

 

5.  MERGE

 5-1) 개요

  - 구조가 같은 두개의 테이블을 비교하여 하나의 테이블로 합치기위한 데이터 조작어

  - WHEN절의 조건절에서 결과 테이블에 해당 행이 존재하면 UPDATE명령문에 의해 새로운 값으로 수정, 그렇지 않으면 ONSERT 명령문으로 새로운 행을 삽입

  - 대량의 데이터를 분석하기 위한 업무에 유용

 

 5-2) 사용법

 

예제1)

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

CREATE TABLE PROFESSOR_TEMP AS
SELECT *
FROM PROFESSOR
WHERE POSITION = '교수';

UPDATE PROFESSOR_TEMP
SET POSITION = '명예교수'
WHERE POSITION = '교수';

INSERT INTO PROFESSOR_TEMP
VALUES(9999, '김도경', 'arom21', '전임강사', 200, SYSDATE, 10, 101);

 

 

6. 트랜잭션 관리

 6-1) 트랜잭션 개요

  - 관계형 데이터베이스에서 실행되는 여러개의 SQL명령문을 하나의 논리적 작업단위로 처리하는 개념

  - COMMIT : 트랜잭션의 정상적인 종료

  - ROLLBACK : 트랜잭션의 전체 취소

 

 6-2) COMMIT 개요

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

  - 처리결과를 디스크에 영구적으로 저장

  - 해당 트랜잭션에 할당된 CPU, 메모리 같은 자원이 해제

  - 서로 다른 트랜잭션을 구분하는 기준

 

 6-3) ROLLBACK 개요

  - 하나의 트랜잭션에서 실행된 SQL명령문의 처리결과를 취소

  - CPU, 메모리 같은 해당 틀내잭션에 할당된 자원을 해제, 트랜잭션을 강제 종료

 

※ SAVE POINT

 

 

 

7. 시퀀스

 7-1) 개요

  - 유일한 식별자

  - 기본 키 값을 자동으로 생성하기 위하여 일련번호 생성해주는 대리객체

  - 예를들면, 웹 게시판에서 글이 등록되는 순서대로 번호를 하나씩 할당하여 기본키로 지정하고자 할때 시퀀스를 편리하게 이용

  - 여러테이블에서 공유 가능

 

 7-2) 사용법

INCREMENT : 시퀀스번호의 증가치

START WITH : 시퀀스 시작번호

MAXVALUE  : 생성 가능한 시퀀스의 최대값

CYCLE : MAXVALUE에 도달한 후 새로 시작하는 시퀀스값

-CHCHE(임시 저장공간) 

 

 7-3) CURRVAL과 NEXTVALUE 함수

  - CURRVAL : 시퀀스에서 생성된 현재 번호를 확인

  - NEXTVAL : 시퀀스에서 다음번호 생성

  - CURRVAL, NEXTVAL

    - INSERT, UPDATE 문에서 사용

    - 서브쿼리, GROUP BY, HAVING, ORDER BY, DISTINCT와 함께 사용할수 없으며, 컬럼의 기본값으로 사용할 수 없음

 

 7-4) 시퀀스 정의 변경

 

 7-5) 시퀀스 삭제

  - DROP SEQUENCE 시퀀스명

 

※ 테이블 관리

 1. 테이블 생성

  1) 개요

    - 테이블 생성은 테이블에 대한 구조를 정리하고, 데이터를 저장하기 위한 공간을 할당하는 과정

    - 테이블에 대한 구조 정의는 테이블을 구성하는 칼럼의 데이터 타입과 무결성 제약조건을 정의하는 방법

 2) 테이블 이름 정의 방법

  - 문자로 시작, 30자 이내

  - 문자, 숫자, 특수문자 사용가능

  - 대소문자 구별 없음, 소문자로 저장하려면 "" 이용

  - 동일 사용자가소유한 다른 객체의 이름과 중복 불가

  - 서로 다른 테이블에서 동일한 데이터를 저장하는 칼럼 이름은 가능하면 같은 이름을 사용

    (JOIN할 때 편함)

  - 필요에따라 언제든지 테이블 생성 가능

  - 완성된 설계도에 따라 테이블을 생성 권장

 

 3) 사용법