4. SQL 고급

목차

1. 내장함수

2. 부속질의

3. 뷰

4. 인덱스

 

01. 내장함수

1.SQL 내장 함수

2.NULL 값 처리

3.ROWNUM

 

01. 내장함수

SQL에서는 함수의 개념을 사용하는데 수학의 함수와 마찬가지로 특정 값이나 열의 값을 입력받아 그 값을 계산하여 결과 값을 돌려준다.

SQL의 함수는 DBMS가 제공하는 내장함수와 사용자가 필요에 따라 직접 만드는 사용자 정의 함수로 나뉜다.

 

1.1 SQL 내장 함수

SQL 내장 함수는 상수나 속성 이름을 입력 값으로 받아 단일 값을 결과로 반환한다. 모든 내장 함수는 최초에 선언될 때 유효한 입력 값을 받아야 한다.

 

1.1.1 숫자 함수

 

1.1.2 문자 함수

 

 

1.2 NULL 값 처리

NULL 값이란?

아직 지정되지 않은 값

NULL 값은 '0', " (빈 문자), ' '(공백) 등과 다른 특별한 값

NULL 값은 비교 연산자로 비교가 불가능 하다

NULL 값의 연산을 수행하면 결과 역시 NULL 값으로 반환된다.

 

집계 함수를 사용할 때 주의할 점

NULL+숫자 연산의 결과는 NULL 이다.

집계 함수 계산 시 NULL이 포함된 행은 집계에서 빠진다.

해당되는 행이 하나도 없을 경우 SUM, AVG 함수의 결과는 NULL이 되며, COUNT 함수의 결과는 0

 

 

02. 부속질의

1. 스칼라 부속질의 - SELECT 부속질의

2. 인라인 뷰 - FROM 부속질의

3. 중첩질의 - WHERE 부속질의

 

부속질의란?

하나의 SQL문 안에 다른 SQL 문이 중첩된 NESTED 질의를 말한다.

다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공할 때 사용한다.

보통 데이터가 대량일 때 데이터를 모두 합쳐서 연산하는 조인보다 필요한 데이터만 찾아서 공급해주는 부속질의가 성능이 더 좋다.

주 질의(main query, 외부질의)와 부속질의(sub query, 내부질의)로 구성된다.

 

 

부속질의의 종류

 

2.1 스칼라 부속질의 - SELECT 부속질의

스칼라 부속질의란?

SELECT 절에서 사용되는 부속질의로, 부속질의의 결과 값을 단일 행, 단일 열의 스칼라 값으로 반환한다.

스칼라 부속질의는 원칙적으로 스칼라 값이 들어갈 수 있는 모든 곳에 사용 가능하며, 일반적으로 SELECT문과 UPDATE SET절에 사용된다.

주 질의와 부속질의와의 관계는 상관/비상관 모두 가능하다.

 

 

2.2 인라인 뷰 - FROM 부속질의

인라인 뷰(inline view)란?

FROM절에서 사용되는 부속질의

테이블 이름 대신 인라인 뷰 부속질의를 사용하면 보통의 테이블과 같은 형태로 사용할 수 있다.

부속질의 결과 반환되는 데이터는 다중 행, 다중 열이어도 상관없다.

다만 가상의 테이블인 뷰 형태로 제공되어 상관 부속질의로 사용될 수는 없다.

2.3 중첩질의 - WHERE 부속질의

비교 연산자

부속질의가 반드시 단일 행, 단일 열을 반환해야 하며, 아닐경우 질의를 처리할 수 없다.

IN, NOT IN

IN연산자는 주질의 속성 값이 부속질의에서 제공한 결과 집합에 있는지 확인(check)하는 역할을 한다. IN 연산자는 부속질의의 결과 다중 행을 가질 수 있다. 주질의는 WHERE 절에 사용되는 속성 값을 부속질의의 결과 집합과 비교해 하나라도 있으면 참이 된다. NOT IN 은 이와 반대로 값이 존재하지 않으면 참이 된다.

 

ALL, SOME (ANY)

ALL은 모두, SOME은 어떠한(최소한 하나라도)이라는 의미를 가진다.

EXIST, NOT EXISTS

데이터의 존재 유무를 확인하는 연산자이다.

주질의에서 부속질의로 제공된 속성의 값을 가지고 부속질의에 조건을 만족하여 값이 존재하면 참이 되고, 주 질의는 해당 행의 데이터를 출력한다.

NOT EXIST의 경우 이와 반대로 동작한다.

 

03 뷰

뷰는 하나 이상의 테이블을 합하여 만든 가상의 테이블이다.

장점

편리성 및 재사용성: 자주 사용되는 복잡한 질의를 뷰로 미리 정의해 놓을 수 있다.

-> 복잡한 질의를 간단히 작성

 

보안성: 각 사용자별로 필요한 데이터만 선별하여 보여줄 수 있다. 중요한 질의의 경우 질의 내용을 암호화 가능

-> 개인정보나 급여, 건강 같은 민감한 정보를 제외한 테이블을 만들어 사용

 

독립성 제공: 미리 정의된 뷰를 일반 테이블처럼 사용할 수 있기 때문에 편리하다. 또 사용자가 필요한 정보만 요구에 맞게 가공하여 뷰로 만들어 쓸 수 있다.

-> 원본테이블이 구조가 변하여도 응용에 영향을 주지 않도록 하는 논리적 독립성을 제공한다.

 

*뷰의 특징

1. 원본 데이터 값에 따라 같이 변함

2. 독립적인 인덱스 생성이 어렵다

3. 삽입, 삭제, 갱신 연산에 많은 제약이 따른다.

 

뷰

3.1 뷰의 생성

3.2 뷰의 수정, 3.3 뷰의 삭제

 

04. 인덱스

1. 데이터베이스의 물리적 저장

2. 인덱스와 B-tree

3. 오라클 인덱스

4. 인덱스의 생성

5. 인덱스의 재구성과 삭제

 

4.1 데이터베이스의 물리적 저장

실제 데이터가 저장되는 곳은 보조기억장치이다 - 하드디스크, SSD, USB, 메모리 등

 

가장 많이 사용되는 장치는 하드디스크

하드디스크는 원형의 플레이트(plate)로 구성되어 있고, 이 플레이트는 논리적으로 트랙으로 나뉘며 트랙은 다시 몇 개의 섹터로 나뉜다.

원형의 플레이트는 초당 빠른 속도로 회전하고, 회전하는 플레이트를 하드디스크의 엑세스 암(arm)과 헤더(header)가 접근하여 원하는 섹터에서 데이터를 가져온다.

하드디스크에 저장된 데이터를 읽어 오는 데 걸리는 시간은 모터에 의해서 분당 회전하는 속도(RPM), 데이터를 읽을 때 엑세스 암이 이동하는 시간 (latency time), 주기억 장치로 읽어오는 시간 (transfer time)에 영향을 받는다.

4.2 인덱스와 B-tree

인덱스란 도서의 색인이나 사전과 같이 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조이다.

B-tree의 구조

인덱스의 특징

인덱스는 테이블에서 한 개 이상의 속성을 이용하여 생성한다.

빠른 검색과 함께 효율적인 레코드 접근이 가능하다.

순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지한다.

저장된 값들은 테이블의 부분집합이 된다.

일반적으로 B-tree 형태의 구조를 가진다.

데이터의 수정, 삭제 등의 변경이 발생하면 인덱스의 재구성이 필요하다.

 

4.3.1 오라클에서 B-tree 인덱스 사용

인덱스의 예

4.4 인덱스의 생성

인덱스 생성 시 고려사항

인덱스는 WHERE 절에 자주 사용되는 속성이여야 한다.

인덱스는 조인에 자주 사용된ㄴ 속성이여야 한다.

단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다. (테이블 당 4~5개 정도 권장)

속성이 가공되는 경우 사용하지 않는다.

속성의 선택도가 낮을 때 유리하다 (속성의 모든 값이 다른 경우)