소개

오늘은, 드디어 정보처리기능사 포스팅에 끝인 고급 SQL에 대해 알아보도록 합시다!
여기에선 인덱스, 뷰 등 기본 SQL을 배우지 않은 사람은 이해가 불가능 할 수 있으니, 꼭 이전 포스팅인 기본 SQL을 숙지하고 와주시기 바랍니다!

1. 인덱스 활용

1. 인덱스(INDEX)

1) 인덱스 개념

• 인덱스란?
  • 인젝스란, 데이터를 빠르게 찾을 수 있는 수단이다.
  • 테이블에 대한 조회 속도를 높여주는 자료구조를 의미한다.
• 인덱스는 테이블에 특정 레코드 위치를 알려주는 용도로 사용한다.
• 테이블에서 기본키로 지정할 경우 자동으로 인덱스가 생성된다.

2) 인덱스 활용

1) 인덱스 생성

CREATE [UNIQUE] INDEX <index_name> ON <table_name> (<column(s)>)

• 각각의 파라미터가 의미하는 내용은 다음과 같다.
  • [UNIQUE] - 인덱스가 걸린 컬럼에 중복값을 허용하지 않음. (생략 가능)
  • <index_name> - 생성하고자 하는 인덱스 테이블 이름
  • <table_name> - 인덱스 대상 테이블 이름
  • <column(s)> - 인덱스 대상 테이블의 특정 컬럼 이름(들)

2) 인덱스 변경

ALTER [UNIQUE] INDEX <index_name> ON <table_name> (column(s))

• 한번 생성한 인덱스에 대해 변경이 필요한 경우는 드물다.
• 일부 제품은 인덱스에 대한 변경 SQL문이 없다.
  • 이 경우, 기존 인덱스를 삭제하고, 신규 인덱스를 생성하는 방식으로 사용이 권고되고 있다.

3) 인덱스 삭제

ALTER TABLE <table_name> DROP INDEX <index_name>

• <index_name>은 아까 말했듯이 생성된 인덱스 이름을 의미한다.
• 인덱스 관련 명령어에 대한 SQL 표준이 없다.
  • 그렇기에 제품별 DROP 명령문의 사용법은 약간씩 다르다.
  • 보통 인덱스를 테이블의 종속 구조로 생각하여 인덱스를 삭제하기 위해
    테이블의 변경을 가하는 형식의 명령을 사용한다.
  • 즉, ALTER TABLE 명령 뒤에 DROP INDEX 명령이 추가되는 형태로 사용된다.

2. 뷰 활용

1. 뷰(View)

1) 뷰 개념

• 뷰란?
  • 뷰는 논리 테이블로서, 사용자에게 사용 관점으로는, 테이블과 동일하다.
  • 뷰는 하나의 물리 테이블로부터 생성이 가능하다.
  • 다수의 테이블 또는 다른 뷰를 이용하여 만들 수 있다.
• 뷰와 같은 결과를 만들기 위해 조인(JOIN) 기능을 사용할 수 있다.
  • 하지만, 뷰가 이미 만들어져 있다면 사용자는 조인 없이 하나의 테이블을 대상으로 하는
    단순한 질의어를 사용할 수 있다.

2) 뷰 특징

• 뷰를 사용하는 주된 이유는 다음과 같은 단순한 질의어를 사용할 수 있기 때문이다.

SELECT * FROM <View_Name>

• 즉, FROM 절에 있는 하나의 <뷰>를 통해 뷰를 구성하는 복수의 <테이블>을 대체하는 단순성에
  그 의의가 있다.
• 또 이러한 기능을 통해 테이블의 중요 데이터 일부만을 제공할 수 있다는 장점이 있다.
  

3) 뷰 생성

• 뷰 생성 명령의 일반 형태는 다음과 같다.

CREATE VIEW <뷰이름>(칼럼목록) AS <뷰를 통해 보여줄 데이터 조회용 쿼리문>

• 뷰 생성 방법
  

4) 뷰 변경

• 뷰 정의 자체를 변경하는 것은 불가능하다.
  • 일단 뷰를 정의하면, 뷰의 물리적 내용은 뷰의 이름과 데이터를 조회하기 위한 쿼리문일뿐이다.
  • 이때 뷰의 이름이나 쿼리문을 변경하는 수단은 제공되지 않는다.
  • 이경우, 뷰의 삭제와 재생성을 통해 뷰의 대한 정의 변경이 가능하다.
• 하지만, 뷰를 통해 접근 가능한 데이터에 대한 변경이 가능하다.
  • 하지만 모든 경우에 데이터의 변경이 가능한 것이 아닌, 일부 제약이 존재한다.
  • 이러한 제약은 뷰 자체가 논리적 개념이기 때문에 물리적 상황에 의존적임을 의미한다.
  • 예를 들어, 기본키에 해당하는 컬럼이 뷰에 정의되어 있지 않은 경우, INSERT는 불가능하다.

5) 뷰 삭제

DROP VIEW <View_Name>

3. 다중 테이블

1. 다중 테이블

• 다중 테이블 검색 방법
  • 관계형 DB는 데이터의 중복을 최소화하기 위해 데이터를 분해하여 저장하고, 통합하여 사용한다.
  • 데이터를 분해하는 방법으로 정규화 기법이 사용된다.
  • 통합하는 기법으로는, 다중테이블에 대한 검색이 사용된다.
• 다중 테이블을 이용하는 보다 세부적인 기법은 다음과 같다.

  기법	설명
  조인	두 개의 테이블을 결합하여 데이터를 추출하는 기법
  서브쿼리	SQL문 안에 포함된 SQL문 형태의 사용 기법
  집합 연산	테이블을 집합 개념으로 조작하는 기법

2. 조인(JOIN)

1) 조인 개념

• 조인은 결합을 의미하며, 관계형 DB에서 조인은 교집합 결과를 가지는 결합 방법을 의미한다.
• 조인은 두 테이블의 공통값을 이용하여 컬럼을 조합하는 수단이다.
• 보통, 기본키와 후보키값을 결합하여 사용하는것이 일반적이다.
• 보다 엄밀하게 말하자면, 기본키,후보키와 관계없이 논리적인 값들의 연관을 사용한다.

2) 조인 유형

• 조인은 관계형 DB의 가장 큰 장점이면서 대표적인 핵심 기능이다.
• 크게 논리적 조인과 물리적 조인으로 나뉜다.

1) 논리적 조인

• 사용자의 SQL문에 표현되는 테이블 결합 방식
• 두 테이블에 공통으로 존재하는 컬럼을 이용하는 방식이다.
• 내부 조인 (Inner Join)
  • 동등 조인 (Equi Join)
    • 공통 존재 컬럼의 값이 같은 경우를 추출
  • 자연 조인 (Natural Join)
    • 두 테이블의 모든 컬럼을 비교하여 같은 컬럼명을 가진 모든 컬럼 값이 같은 경우를 추출
  • 교차 조인 (Cross Join)
    • 조인 조건이 없는 모든 데이터의 조합을 추출
• 외부 조인 (Outer Join)
  • 왼쪽 외부 조인 (Left Outer Join)
    • 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출
  • 오른쪽 외부 조인 (Right Outer Join)
    • 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출
  • 완전 외부 조인 (Full Outer Join)
    • 양쪽의 모든 데이터를 추출

2) 물리적 조인

• DB의 옵티마이저에 의해 내부적으로 발생하는 테이블 결합 방식
• 특정 테이블의 모든 데이터를 기준으로 다른 테이블의 정보를 추출한다.
• 다른 테이블의 값이 없어도 출력된다.
• 중첩 반복 조인(Nested Loop Join)
  • 2개 이상의 테이블에서 하나의 집합을 기준으로 바깥 테이블의 처리 범위를 하나에 엑세스하면서 그 추출된 값으로 안쪽 테이블을 조인하는 방식
• 정렬 합병 조인(Sort-Merge Join)
  • 적당한 인덱스가 없을 떄 사용되며 양쪽 테이블의 처리 범위를 각자 실행하여 정렬한 결과를 스캔하고, 연결고리 조건으로 합병하는 방식
• 해시 조인(Hash Join)
  • 조인할 테이블에 대해서 해시 함수를 생성하고 해시 함수의 순서대로 결과를 출력하는 방식

3. 서브쿼리(Sub Query)

1) 서브쿼리의 개념

• 서브쿼리는 다음 그림과 같이 SQL문 안에 포함된 또 다른 SQL문을 의미한다.
  
• 서브쿼리는 알려지지 않은 기준을 검색하기 위해 사용한다.
• 메인쿼리와 서브쿼리 관계는 주종 관계로써, 서브쿼리에 사용되는 컬럼 정보는 메인쿼리의 컬럼 정보를 사용할 수 있으나, 역으로는 성립되지 않는다.

2) 서브쿼리의 유형

• 서브쿼리는 동작하는 방식이나 반환되는 데이터의 형태에 따라 분류할 수 있다.

1) 동작 형태에 따른 서브쿼리

• 비연관(Un-Correlated) 서브쿼리
  • 서브쿼리가 메인쿼리의 컬럼을 가지고 있지 않은 형태
  • 메인쿼리에 서브쿼리에서 실행된 결과값의 제공 용도
• 연관(Correlated) 서브쿼리
  • 서브쿼리가 메인쿼리의 컬럼을 가지고 있는 형태
  • 메인쿼리가 먼저 수행되어 얻은 데이터를 서브쿼리의 조건에 맞는지 확인할때 사용

2) 데이터 형태에 따른 서브쿼리

• Single Row(단일 행)
  • 서브쿼리의 결과가 항상 1건 이하인 서브쿼리
  • 단일 행 비교 연산자(=,<,<=,>,>=,<>)가 사용됨
• Multipie Row(다중 행)
  • 서브쿼리의 결과가 여러 건인 서브쿼리
  • 다중 행 비교 연산자(IN, ALL, ANY, SOME, EXISTS)가 사용됨
• Multipie Column(다중 컬럼)
  • 서브쿼리의 결과가 여러 컬럼으로 반환되는 서브쿼리
  • 메인쿼리의 조건 절의 여러 컬럼을 동시에 비교할 때, 서브쿼리와 메인쿼리에서 비교하는 컬럼 개수와 위치가 동일해야 함.

4. 집합 연산

1) 집합 연산의 개념

• 테이블을 집합 개념으로 보고, 두 테이블 연산에 집합 연산자를 사용하는 방식이다.
• 집합 연산자는 2개 이상의 질의 결과를 하나의 결과로 만들어준다.
• 일반적으로 집합 연산자를 사용하는 경우는 다음과 같다.
  • 서로 다른 테이블에서 유사한 형태의 결과를 반환하는 것을 하나로 합치고자 할때
  • 동일 테이블에서 서로 다른 질의를 수행하여 결과를 합치고자 할때

2) 집합 연산의 유형

• UNION
  • 여러 SQL문의 결과에 대한 합집합(중복 행 제거)
• UNION ALL
  • 여러 SQL문의 결과에 대한 합집합(중복 행 제거하지 않음)
• INTERSECTION
  • 여러 SQL문의 결과에 대한 교집합(중복 행 제거)
• MINUS
  • 앞에 SQL문의 결과와 뒤의 SQL문의 결과 사이의 차집합(중복 행 제거)
  • 일부 제품에 경우는 EXCEPT를 사용한다.

마치며

드디어! 정보처리기능사 실기에 대한 모든 정리가 끝났네요..
시험 보시는분들 참고하셔서 꼭! 붙으시길 바랄게요!
다음 시간엔 어떤 자격증을 포스팅해볼까요?
그럼! 다음시간에 뵙죠!