6. SQL(3)

데이터 검색(2)

그룹 질의

집계 함수

• 특점 컬럼에통계 연산 수행
• SELECT절 or HAVING절에서 사용
• 집계 함수의 종류
  -> COUNT, SUM, AVG, MAX ,MIN

DSITINCT 중복제거

그룹 질의

• 특정 기준으로 레코드를 그룹화, 각 레코드별 집계 함수 사용

SELECT 질의 GROUP BY 컬럼;
// SELECT 절에  집계 함수에 사용되는 컬럼의 값이 존재해야함

SELECT 학과, COUNT *)AS 학생 수 FROM 학생 GROUP BY 학과;

H

HAVING 절

• GROUP BY(그룹 질의) 결과 레코드에 조건 적용
• WHERE : 레코드에 대한 조건 기술, HAVING:집계 결과 레코드에 대한 조건 기술

중첩 질의 (서브 쿼리)

• SELECT문 안에 SELECT문
• 내부 질의의 처리결과를 외부 질의에서 재사용
• 중첩 질의의 종류
  1. from 절에서의 중첩 질의 -> FROM 절의 결과 집합을 SELECT에서 재검색
  2. where 절에서의 중첩 질의 -> WHERE절의 결과 집합을 활용하여 외부 질의에서 레코드의 출력 여부 결정
     -> IN, NOT iN, EXISTS, NOT EXSISTS 사용

SELECT 컬럼  
    FROM (SELECT 컬럼 FROM 테이블 WHERE 조건)
    WHERE 조건;

SELECTG 컬럼
    FROM 테이블
    WHERE 테이블1.컬럼 연산자 (SELECT 테이블2.컬럼
                              FROM 테이블
                            WHERE 조건);

// from 절에서의 서브쿼리
SELECT MAX(b.평균연봉) AS 평균연봉 
    FROM (SELECT 부서, AVG(연봉) AS 평균연봉
        FROM 사원
        GROUP BY 부서) AS b
    WHERE b.평균연봉 < 50000000

부서별 사원의 평균 연봉 서브쿼리에서 5천만원 미만의 평균 연봉중 가장 높은 부서의 연봉을 평균 연봉으로 가져옴

SELECT A.사원번호
    FROM 부서 AS A
    WHERE A.부서이름 = '영업팀' AND
        EXISTS (SELECT B.사원번호 FROM 관리자 B
            WHERE A.사원번호 = B.사원번호)

부서가 영업팀인 사원번호가 관리자의 사원번호에 포함되어있는 경우에만 출력

join

• 테이블 간의 관련성을 이용하여 두개 이상의 테이블에서 데이터를 검색하는 질의
• ER 모델링 및 정규화 기법으로 여러 테이블로 분리된 정보를 통합하여 검색 시 유용

1. 내부 조인 (많이 사용됨)

• 조인 조건에 만족하는 레코드만 결합하여 출력 (만족하지 않는 정보의 손실 발생)
• 조인 조건은 ON절에 기록 (같은 레코드가 1개 뿐이면 생략 가능)
• ANSI SQL표준과 오라클 문법이 제안한 조인 형식 두가지가 사용됨

//ANSI ISO 표준
SELECT 컬럼1, 컬럼2
    FROM 테이블1 INNER JOIN 테이블2
    ON 조인조건1  // 조인 조건 후에 WHERE절을 사용할 수 있다
    [WHERE 조건]
    [ORDER BY]


// Oracle (ON절을 따로 사용하지 않음)
SELECT 테이블1.컬럼1, 테이블1.컬럼2
       테이블2.컬럼1, 테이블2.컬럼1
    FROM 테이블1, 테이블2
    WHERE 테이블1.컬럼1 = 테이블2.컬럼1
        AND 테이블.컬럼 조건
    [ORDER BY]

조인, 중첩 질의에서는 컬럼 앞에 테이블명을 명시해주는 것이 좋다

2. 자연조인 

• 두개 이상의 테이블을 하나의 테이블로 결합 (내부 조인과 유사함)
• 동일한 이름의 컬럼에 대해 값이 같은 레코드 끼리만 결합하는 내부 조인
  -> 동일한 이름이라는 조건이 있기 때문에 ON절 사용 안함 (조인 조건이 자연적이라서 자연조인)
• SELECT 컬럼1, 컬럼2 FROM 테이블1 NATURAL JOIN 테이블2 [WHERE 조건] [ORDER BY]

3. 외부조인

• 조인 조건에 맞지 않는 레코드들도 질의 결과에 포함시킨다

1. left outer join - 왼쪽 정보는 전부 출력
2. right outer join - 오른쪽 정보는 전부 출력
3. full outer join - 양쪽 모두 전부 출력

SELECT A.컬럼1, A.컬럼2
       B.컬럼1, B.컬럼2
    FROM 테이블1 as A LEFT|RIGHT [OUTER] JOIN 테이블2 as B
    ON A.컬럼1 = B.컬럼1
    [WHERE 조건]
    [ORDER BY]

4. 셀프조인

• 힌 테이블이 자기 자신과 조인되는 형태 (재귀적)
• 동일한 이름의 테이블에 대한 조인이기 때문에 반드시 별칭(Alias)을 의무적으로 사용
• 한 테이블 안에 기본키와 외래키가 있는 경우?

SELECT 별칭1.컬럼1, 별칭1.컬럼2
       별칭2.컬럼1, 별칭2.컬럼2
       FROM 테이블 AS 별칭1
               INNER|OUTER JOUN 테이블 AS 별칭2
       ON 조인 조건
       [WHERE 절]
       [ORDER BY]

VIEW

뷰의 개념

• 데이터를 저장하고 있는 하나 이상의 테이블을 유도하여 생성하는 가상의 테이블(virtual table)

뷰를 사용하는 이유

• 데이터 독립성: 원본 테이블 구조가 바뀌어도 뷰를 이용한 작업은 정의만 변경되어 응용 프로그램에 영향이 없음
• 데이터 보안: 사용자에게 원본 테이블의 일부 컬럼만 접근을 허용하여 보안 향상
• 다양한 구조의 테이블 : 사용자 요구사항에 맞는 테이블 구조를 생성 가능
• 작업의 단순화 : 복잡한 질의문을 뷰로 단순화
• 데이터 무결성 : WITH CHECK OPTION을 이용하여 뷰 생성에 위배되는 수정 작업 거부 가능

뷰의 생성

• 생성되는 뷰의 구조는 SELECT문의 결과로 결정된다

CREATE VIEW 이름 AS 
    ( SELECT 컬럼1, 컬럼2
        FROM 테이블
        [WHERE])
[WITH CHECK POINT]

뷰의 수정과 삭제

• 뷰의 수정은 생성과 동일하게 SELECT문의 결과로 변경 (컬럼의 list가 동일해야만 한다)

// 수정
ALTER VIEW 이름(컬럼1,컬럼2) AS
    (SELECT 컬럼1, 컬럼2
        FROM 테이블
        [WHERE])

//삭제
DROP VIEW 이름

뷰 생성 예

• SELECT문을 CREATE VIEW 이름 AS 해주면 됨

CREATE VIEW 이름 AS
    (SELECT A.컬럼1, A.컬럼2
       B.컬럼1, B.컬럼2
    FROM 테이블1 as A LEFT|RIGHT [OUTER] JOIN 테이블2 as B
    ON A.컬럼1 = B.컬럼1
    [WHERE 조건]
    [ORDER BY]
    )

뷰를 이용한 데이터 검색

• 뷰는 가상 테이블이지만 조작은 테이블과 동일하게 수행

SELECT * FROM 이름 WHERE 조건 // 구문 형식

SELECT * FROM 이름 WHERE 조건 AND 뷰 정의조건  // 실행형식

뷰를 이용한 데이터 삽입

• 뷰에 대한 INSERT문은 원본 테이블에서 실행
• PRIMARY KEY 등의 제약사항이 위배되는 경우 삽입 불가
• 원본 테이블에 존재하는 컬럼이지만 뷰에는 없는 컬럼에 삽입하려할 때 실행 불가
• 조인 질의 또는 그룹 질의가 적용된 뷰는 데이터 삽입 및 수정 불가
• WITH CHECK OPTION이 적용된 뷰는 위배되는 사항은 없어도 뷰에 맞지 않는 조건일 경우 실행 불가