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

1. 다중 행 서브쿼리

  - 서브쿼리에서 반환되는 결과 행이 하나 이상일 때 사용하는 서브쿼리

  - 메인쿼리의 where절에서 서브쿼리의 결과와 비교할 경우에는 다중행 비교 연산자를 사용하여 비교

    - 다중행 비교연산자 : IN, ANY, SOME, ALL, EXISTS

    - 다중행 비교연산자는 단일 행 비교 연산자와 결합하여 사용가능

IN은 저번에 배웠던 IN 연산자

ANY(하나), ALL(모든 것)을 집중적으로 보자

 

 1) IN 연산자

 - 메인쿼리의 비교조건에서 서브쿼리의 출력 결과와 하나라도 일치하면 메인쿼리 조건절이 참이 되는 연산자

 - '=' 연산자를 OR로 연결한 것과 같은 의미

예제 ) 정보미디어학부(부서번호:100)에 소속된 모든 학생의 학번, 이름, 학과번호 출력

SELECT NAME, GRADE, DEPTNO
FROM STUDENT
WHERE DEPTNO IN (SELECT DEPTNO
                FROM DEPARTMENT
                WHERE COLLEGE = 100);

 

2) ANY 연산자

 - 메인쿼리의 비교조건에서 서브쿼리의 출력 결과와 하나라도 일치하며너 메인쿼리 조건절이 참이 되는 연산자

 - '>, <' 등과 같은 범위 비교도 가능

예제) 모든학생 중에서 4학년 학생중에서 키가 제일 작은 학생보다 키가 큰 학생의 학번, 이름, 키 출력

SELECT STUDNO, NAME, HEIGHT 
FROM STUDENT
WHERE HEIGHT > ANY (
    SELECT HEIGHT
    FROM STUDENT
    WHERE GRADE = '4'
);

 

3) ALL 연산자

 - 메인쿼리의 비교 조건에서 서브쿼리의 검색 결과와 모두 일치하면 메인쿼리 조건절이 참이되는 연산자

예제)모든 학생중에서 4학년 학색중에서 키가 가장 큰 학생보다 키가 큰 학생의 학번, 이름, 키 출력

SELECT STUDNO, NAME, HEIGHT 
FROM STUDENT
WHERE HEIGHT > ALL (
    SELECT HEIGHT
    FROM STUDENT
    WHERE GRADE = '4'
);

 

4) EXISTS 연산자

 - 서브쿼리에서 검색된 결과가 행개수를 가지고 판단 (공집합이냐, 행이 1개 이상이냐 (BOOLEAN형))

 - 서브쿼레에 검색된 결과가 존재하지않으면 메인쿼리의 조건절은 거짓

예제)보직수당을 받는 교수가 한명이라도 있으면 모든 교수의 교수번호, 이름, 보직수당 그리고 급여와 보직수당의 합을 출력

SELECT PROFNO, NAME, SAL, COMM, SAL, NVL(COMM, 0)+SAL
FROM PROFESSOR
WHERE EXISTS(
    SELECT PROFNO
    FROM PROFESSOR
    WHERE COMM IS NOT NULL
);

 

4) 다중 컬럼 서브쿼리

 - 서브쿼리에서 여러개의 컬럼값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리,

 - 메인쿼리의 조건절에서도 서브쿼리의 컬럼 수만큼 지정해야 함

 

 4-1) 종류

 PAIRWISE : 칼럼을 쌍으로 묶어서 동시에 비교하는 방식

 UNPAIRWISE : 칼럼별로 개별적으로 나누어서 비교한 후, AND연산을 하는 방식 (개별조건)

4-2) PAIRWISE

예제)PAIRWISE 비교방법에 의해 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력

SELECT NAME, GRADE, WEIGHT
FROM STUDENT
WHERE (GRADE, WEIGHT) IN(
    SELECT GRADE, MIN(WEIGHT)
    FROM STUDENT
    GROUP BY GRADE
);

4-3) UNPAIRWISE

예제) UNPAIRWISE 비교방법에 의해 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게 출력

SELECT NAME, GRADE, WEIGHT
FROM STUDENT
WHERE GRADE IN (
    SELECT GRADE 
    FROM STUDENT
    GROUP BY GRADE)
AND WEIGHT IN (
    SELECT MIN(WEIGHT)
    FROM STUDENT
    GROUP BY GRADE
)
ORDER BY 2, 3;

 

5) 상호연관 서브쿼리

 - 메인쿼리절과 서브쿼리간에 검색 결과를 교환하는 서브쿼리

 - 메인쿼리와 서브쿼리간의 결과를 교환하기 위하여 서브쿼리 WHERE 조건절에서 메인쿼리의 테이블과 연결

 

 ※ 주의

  행을 비교할 때마다 결과를 메인으로 반환하는 관계로 처리, 성능이 저하될 수 있음

 

예제) 각 학과 학생의 평균 키보다 키가 큰 학생의 이름, 학과번호, 키를 출력

SELECT NAME, DEPTNO, HEIGHT 
FROM STUDENT S1
WHERE HEIGHT > (
    SELECT AVG(HEIGHT)
    FROM STUDENT S2
    WHERE S2.DEPTNO = S1.DEPTNO
);

2. 실무에서 서브쿼리 사용시 주의사항

 1) 단일행 서브쿼리에서 오류가 발생하는 경우

  - 메인쿼리와 서브쿼리 컬럼의 수가 일치하지 않는경우

  - 서브쿼리 내에서 ORDER BY절 사용하면 오류발생 (WHERE절에서 사용되는 ORDER BY)

  - 서브쿼리의 결과가 NULL(공집합)인 경우 

   ※ 복습 (p.395 실습)

 

 2) Scalar Subquery

※ 데이터 조작어

1. DML(Data Manpulation Language)

 1)정의

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

 

 2) 종류

 - INSERT - 새로운 데이터 입력 명령어

 - UPDATE - 기존 데이터 수정 명령어

 - DELETE - 기존 데이터 삭제 명령어

 - MERGE (INSERT OR UPDATE) - 두개의 테이블을 하나의 테이블로 병합하는 명령어 (사용빈도 ↓)

 

 3) 트랜잭션

http://wiki.hash.kr/index.php/%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98#.EC.9B.90.EC.9E.90.EC.84.B1

 - 데이터의 정합성을 보장

 - 여러개의 명령문을 하나의 논리적인 작업단위로 처리하는 기능

 - 트랜잭션 관리 명령어

   - COMMIT(반영) : 트랜잭션의 정상적인 종료를 위한 명령어

   - ROLLBACK : 트랜잭션의 비정상적인 중단을 위한 명령어

 

 

 4) 데이터 입력 (insert) 

 - 테이블에 데이터를 입력하기 위한 명령인 INSERT 명령문 사용

 - 데이터 입력 방법

    - 단일행 입력 : 한번에 하나의 행을 테이블에 입력하는 방법

    - 다중행 입력 : 서브쿼리를 이용하여 한번에 여러행을 동시에 입력하는 방법

 

 4-1) 단일 행 입력

  - INTO절에 명시한 칼럼에 VALUES절에서 지정한 칼럼값을 입력

  - INTO절에 칼럼을 명시하지 않으면 테이블 생성시 정의한 칼럼순서와 동일한 순서로 입력

  - 입력되는 데이터 타입은 컬럼의 데이터 타입과 동일해야함

  - 입력되는 데이터의 크기는 칼럼의 크기보다 작거나 동일해야함

  - CHAR, VARCHAR2, DATE 타입의 입력 데이터는 단일인용부호(")로 묶어서 입력

예제) 학생테이블에 홍길동 학생의 데이터를 입력

INSERT INTO STUDENT
VALUES(10110, '홍길동', 'hong', '1', '8501011143098', '85/01/01', '041)630-3114', 170,70,101,9903);

SELECT * FROM STUDENT WHERE STUDNO = 10110;

NULL값 입력시 )

INSERT INTO STUDENT
VALUES(10111, '홍길동', 'hong', '1', '8501011143098', '85/01/01', '041)630-3114', 170,70,101,'');

 - VALUES절의 칼럼값에 NULL, ''(빈문자열) 사용

 

5) 묵시적으로 NULL 입력

예제)

INSERT INTO DEPARTMENT(DEPTNO, DNAME) VALUES (300, '생명공학부');

 

6) 명시적으로 NULL입력

예제) 

INSERT INTO DEPARTMENT VALUES (301, '환경보건학과', '', NULL);

 

7) 날짜 데이터 입력방법

 7-1) NULL입력

  - 해당 시스템에서 요구하는 기본 날짜 형식으로 입력

  - UNIX 기본 날짜 형식 : 'DD-MON-YY'

  - 퍼스널 오라클 : 'YY/MM/DD'

  - 필요에 따라서 TO_DATE 함수 사용

 

예제) 교수테이블에서 입사일을 2006년 1월 1일로 입력

INSERT INTO PROFESSOR(PROFNO, NAME, POSITION, HIREDATE, DEPTNO)
VALUES (9920, '최윤식', '조교수'
, TO_DATE('2016/01/01', 'YYYY/MM/DD')
,102);

SELECT * FROM PROFESSOR;

 

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

INSERT INTO PROFESSOR
VALUES (9910, '백미선', 'white', '전임강사', 200, SYSDATE, 10, 101);

SELECT *
FROM PROFESSOR
WHERE PROFNO = 9910;

8) 다중행 입력방법

 - INSERT 명령문에서 서브쿼리 절을 이용

 - INSERT 명령문에 의해 한번에 여러 행을 동시에 입력

8-1) 단일테이블에 다중행 입력

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

 - INSERT 명령문의 VALUES절 대신 서브쿼리에서 검색된 결과 집합을 한꺼번에 입력

 - 서브쿼리의 결과 집합은 INSERT 명령문에 지정된 칼럼 개수와 데이터 타입이 일치해야함

 - 서브쿼리를 이용한 다중행 입력시 테이블에 기본 키, 고유 키 제작조건이 중복되지 않도록 주의

 - 제약조건을 위반할 경우 입력되지 않고 오류발생

CREATE TABLE STU3
AS SELECT * FROM STUDENT
WHERE 1=0; -- 테이블 생성

SELECT * FROM STU3;

INSERT INTO STU3
SELECT * FROM STUDENT; -- 테이블 복사

 

8-2) INSERT ALL

 - 서브쿼리의 결과집합을 조건없이 여러 테이블을 동시에 입력

 - 서브쿼리의 컬럼 이름과 데이터가 입력되는 테이블의 컬럼이 반드시 동일해야함

 - ALL : 서브쿼리의 결과 집합을 해당하는 INSERT절에 모두 입력

 - FIRST : 서브쿼리의 결과집합을 해당하는 첫번째 INSERT절에 입력

 - SUBQUERY : 입력 데이터 집합을 정의하기 위한 서브쿼리

 

예제) 다중행 입력을 위한 HEIGHT_INFO, WEIGHT_INFO 예제테이블 생성

CREATE TABLE WEIGHT_INFO AS SELECT STUDNO, NAME, WEIGHT FROM STUDENT WHERE 1=0;
CREATE TABLE HEIGHT_INFO AS SELECT STUDNO, NAME, HEIGHT FROM STUDENT WHERE 1=0;

예제) 학생테이블에서 2학년 이상의 학생을 검색하여 HEIGHT_INFO 테이블에는 학번, 이름, 키 WEIGHT_INFO 테이블에는 학번, 이름, 몸무게를 각각 입력

INSERT ALL
INTO WEIGHT_INFO VALUES(STUDNO, NAME, WEIGHT)
INTO HEIGHT_INFO VALUES(STUDNO, NAME, HEIGHT)
SELECT * FROM STUDENT WHERE GRADE >= 2;

SELECT * FROM WEIGHT_INFO;
SELECT * FROM HEIGHT_INFO;

9) Conditional INSERT ALL

9-1) 사용법