정보처리기사 26일차

3. 데이터베이스

 3.1 SQL 활용

 3.1.3 고급 SQL 작성

---

1. VIEW

 

1.1 뷰의 개념

뷰는 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 테이블로 부터 유도된 이름을 가지는 사상 테이블을 말한다.

기본테이블처럼 행과 열로 구성되지만 다른 테이블에 있는 데이터를 보여줄뿐이지 데이터를 직접 담고 있지는 않다.

 

1.2 뷰의 특징

저장 장치내에 물리적으로 존재하지 않는다.

데이터 보정 등 임시적인 작업을 위한 용도로 사용한다.

기본 테이브로가 같은 형태의 구조로 조작이 거의 비슷하다.

삽입 내용 갱신에 제약이 따른다.

논리적 독립성을 제공한다.

독자적인 인덱스를 가질 수 없다.

Create를 이용하여 뷰를 생성한다.

필요한 데이터만 골라 뷰를 이용하여 처리한다.

 

1.3 뷰 사용

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

SELECT * FROM <View Name>;

즉 FROM 절에 있는 하나의 뷰를 통해 뷰를 구성하는 복수의 테이블 을 대체하는 단순성에 그 의의가 있다.

 

1.4 뷰 생성

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

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

 

 

1.5 뷰 삭제 및 변경

뷰 정의 자체를 변경하는 것은 불가능하다. 일단 뷰를 정의하면 뷰의 물리적 내용은 뷰의 이름과 데이터를 조회하기 위한 쿼리문뿐이다. 이때 뷰의 이름이나 쿼리문을 변경하는 수단은 제공되지 않는다.

이 경우 뷰의 삭제와 재생성을 통해 뷰에 대한 정의 변경이 가능하다.

 

2. 인덱스

 

2.1 인덱스 개념

인덱스는 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여주는 자료구조이다.

테이블에서 자주 사용되는 컬럼 값을 빠르게 검색할 수 있도록 색인을 만들어 놓은 것이다.

과다한 사용은 성능이 떨어진다.

필요없는 인덱스가 많아지면 DB에 차지되는 공간이 많아져 table scan보다 속도가 느려질 수 있다.

 

2.2 인덱스의 구조와 장점

인덱스 구성을 위해 가장 많이 사용하는 검색 트리는 b-tree 구조이다.

인덱스는 데이터베이스의 성능향상을 위한 DB튜닝 수단으로 가장 일반적인 방법이다.

인덱스 사용의 장점

sql 문을 변경하지 않아도 성능을 개선한다.

테이블의 데이터에 영향을 주지 않는다.

일정한 효과를 기대할 수 있다.

 

2.3 인덱스의 사용

인덱스는 테이블의 특정 레코드 위치를 알려주는 용도로 사용한다 이러한 인덱스는 자동으로 생성되지 않는다.

 

2.4 인덱스 종류

순서 인덱스 : 데이터가 정렬된 순서로 인덱스 생성관리 b-tree알고리즘 이용, 오름차순, 내림차순 지정

해시 인덱스 : 해시함수에 의하여 직접 제이터에 키값으로 접근하는 인덱스

비트맵 인덱스 : 각 컬러멩 적은 개수의 값이 저장된 경우 선택

함수기반 인덱스 : 함수 기반으로 사전에 인덱스 설정하면 인덱스 가능 및 속도향상

단일 인덱스 : 하나의 컬럼으로만 인덱스를 지정하는 방식

결합 인덱스 : 복수개의 컬럼을 이용하여 인덱스 지정하는 방식

클러스터드 인덱스 : 저장된 데이터의 물리적 순서에 따라 인덱스가 생성

 

1. 해시 인덱스

데이터의 신속산 검색을 위해 키 값에 해시함수를 적용하여 주소 값을 빠르게 계산하고 레코드가 저장된 위치를 직접 접근하는 방법이다. <키캆, 주소>의 쌍으로 저장하므로 특정 키 값에 대한 검색 방법 중에서 가장 빠르다.

 

2. 인덱스 조작

DB사용자가 인덱스에 대해 조작할 수 있는 방법으로는 생성, 삭제, 변경 조작이 있다. 참고로 인덱스 조작 명령은 SQL 표준화에 포함되지 않아 DBMS 공급사마다 사용법이 약간씩 다르다.

 

3. 집합연산

 

3.1 집합연산 개념

테이블에 집합 개념으로 보고, 두 테이블 연산에 집합 연산자를 사용하는 방식이다.

집합 연산자는 여러 질의 결과를 하나로 결합하는 방식을 사용한다. 즉, 집합 연산자는 2개 이상의 질의 결과를 하나의 결과로 만들어 준다.

 

3.2 집합 연산 유형

테이블 집합의 개념으로 보고 두 테이블 사이에 가능한 집합 연산은 다음과 같은 종류가 있다.

UNION : 여러 SQL문의 결과에 대한 합집합(중복 행 제거)

UNION ALL : 여러 SQL문의 결과에 대한 합집합(중복행 제거하지 않음) 

INTERSECTION : 여러 SQL문의 결과에 대한 교집함(중복행 제거함)

EXCEOT (MINUS) : 앞의 SQL문의 결과와 뒤의 SQL결과 사이의 차집합(중복행 제거)

 

집합 연산을 적용하려면 두 테이블이 합집합 호환성을 가져야 한다. 즉, 집합 연산에 참여하는 두테이블의 컬럼의 개수와 데이터 타입이 일치해야 한다.

 

3.3 UNION

UNION은 합집합을 의미한다. 예를 들어, 두 개의 데이터 집합이 있으면 각 집합 원소를 모두 포함한 결과가 반환된다.

 

3.4 UNION ALL

UNION ALL은 UNION과 비슷하나, 중복된 항복도 모두 조회된다.

 

3.5 INTERSECTION

교집합을 의미하고 공통된 항복만 추출해 낸다.

 

3.6 MINUS

차집합을 의미한다. 즉 한 데이터 집합을 기준으로 다른 데이터 집합과 공통된 항목을 제외한 결과만 추출해 낸다.

 

4. 조인

 

4.1 조인 개요

조인은 두 개 이상의 테이블로부터 연관된 데이터를 결합해서 검색하는 방법을 의미한다.

SELECT 문의 FROM 절에 두개 이상의 테이블을 결거하고 WHERE 절에 조인 조건을 명시하는 방법이 기본적이다.

조인 조건은 두 테이블 사이에 속하는 컬ㄹ럼 값들을 비교 연산자로 연결한 형태이며, 조인 조건을 생략한 경우 또는 조인 조건을 잘 못 작성하기 되면 카티션 프로덕트 연산이 수행되어 원하지 않는 결과를 얻게 된다.

조인 질의에 사용된 두테이블의 컬럼 이름이 동일하다면 반드시 컬럼 이름 앞에 테이블 이름을 명시해야 한다.

 

4.2 조인 유형

조인은 크게 논리적 조인과 물리적 조인으로 구분한다.

논리적 조인 : 사용자의 SQL문에 표현되는 테이블 결합 방식

• 내부조인 : 두 테이블에 존재하는 공통 데이터를 갖는 컬럼을 이용하는 방식
• 외부조인 : 특정 테이블의 모든 데이터를 기준으로 다른 테이블의 정보를 추출
• 셀프조인 : 한 테이블 내에서 조인을 수행

물리적 조인 : 데이터베이스의 옵티마이저에 의해 내부적으로 발생하는 테이블 결합 방식

 

4.3 논리적 조인

논리적 조인은 내부 조인과 외부조인, 셀프 조인으로 구분한다.

내부조인은 조인 조건에 만족하는 데이터를 결과로 산출하는 조인을 말한다.

셀프 조인은 조인에 참여하는 테이블이 1개인 경우를 말한다.

외부 조인은 조인 조건에 만족하지 않는 결과도 같이 보고 싶을 때 사용한다.

 

5. 서브쿼리

 

5.1 서브쿼리 개념

서브쿼리는 다른 SQL문 안에 포함되어 사용된 또 다른 SQL문을 의미한다.

서브쿼리는 WHERE 절 뿐만 아니라 INSERT, DELETE, UPDATE 문에도 사용될 수 있다.

메인쿼리와 서브쿼리 관계는 주종관계로서, 서브쿼리에 사용되는 컬럼 정보는 메인쿼리의 컬럼 정보를 사용할 수 있으나 역으로는 성립하지 않는다

 

5.2 서브쿼리 유형

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