chapter 04 SQL 고급

SQL의 함수 : 내장함수와 사용자 정의 함수가 있음.

 

내장 함수 : 상수나 속성이름을 입력으로 받아 단일 값 반환. 최초에 선언될 때 유효한 입력 값을 입력해야 함. 사용빈도가 높은 수학 함수, 문자열 함수, 날짜 함수는 내장 함수로 제공.

 

수학 함수 : 사칙연산(+, -, *, /)과 나머지(%) 연산자는 기호를 그대로 사용. 내장 함수로 제공되는 수학 함수로는 ABS, CEILING, FLOOR, ROUND, LOG, POWER, SQRT, SIGN이 있음. 입력 값으로 열 이름을 사용할 수도 있고, 여러 함수를 복합적으로 사용할 수 있음.

 

ABS : ABS(숫자) 숫자의 절대값을 계산.

 

CEILING : CEILING(숫자) 숫자보다 크거나 같은 최소의 정수.

 

FLOOR : FLOOR(숫자) 숫자보다 작거나 같은 최소의 정수.

 

ROUND : ROUND(숫자, m) 숫자의 반올림, m은 반올림 기준 자릿수.

 

LOG : LOG(숫자) 숫자의 자연로그 값을 반환.

 

POWER : POWER(숫자, n) 숫자의 n승 값을 계산.

 

SQRT : SQRT(숫자) 숫자의 제곱근 값을 계산(숫자는 양수).

 

SIGN : SIGN(숫자) 숫자가 음수면 -1, 0이면 0, 양수면 1.

 

SELECT 문의 FROM 절 생략 : SQL Server에서는 FROM 절이 생략될 경우 내부적으로 가상의 테이블이 존재하는 것으로 간주하고 실행하지만 Oracle의 경우 FROM 절의 생략을 허용하지 않아, dual이라는 가상의 테이블을 지정해주어야 함.

 

문자 함수 : CHAR나 VARCHAR의 데이터형을 대상으로 단일 문자나 문자열을 가공한 결과를 반환 CHAR, NCHAR, CHARINDEX, LEFT, RIGHT, LEN, LOWER, UPPER, LTRIM, RTRIM, PATINDEX, REPLACE, REPLICATE, REVERSE, SPACE, SUBSTRING, ASCII, UNICODE.

 

CHAR : CHAR(n) 정수 아스키 코드를 문자로 반환.

 

NCHAR : NCHAR(n) n 값의 유니코드에 대응하는 문자를 반환.

 

CHARINDEX : CHARINDEX(str1, str2) str2에서 부분 문자열 str1의 시작 위치를 반환.

 

LEFT : LEFT(str, n) str의 왼쪽에서부터 n개 문자열을 반환.

 

RIGHT : RIGHT(str, n) str의 오른쪽에서부터 n개 문자열을 반환.

 

LEN : LEN(str) str의 문자열 길이를 반환.

 

LOWER : LOWER(str) str을 소문자로 변환하여 반환.

 

UPPER : UPPER(str) str을 대문자로 변환하여 반환.

 

LTRIM : LTRIM(str) str의 왼쪽 공백을 제거.

 

RTRIM : RTRIM(str) str의 오른쪽 공백을 제거.

 

PATINDEX : PATINDEX('%str1%', str2) str2에서 str1문자열의 시작 위치를 반환.

 

REPLACE : REPLACE(str1, str2, str3) str1에서 str2를 str3로 변환하여 반환.

 

REPLICATE : REPLICATE(str, n) str을 n만큼 반복.

 

REVERSE : REVERSE(str) str을 역순으로 출력.

 

SPACE : SPACE(n) n 만큼의 공백 문자를 반환.

 

SUBSTRING : SUBSTRING(str, n, m) str에서 n번째부터 m개 문자를 반환.

 

ASCII : ASCII(str) str의 제일 왼쪽 문자의 아스키 코드 값을 반환.

 

UNICODE : UNICODE(str) str의 제일 왼쪽 문자의 유니코드 값을 반환.

 

날짜 함수 : 데이터베이스는 날짜를 단순 문자열로 저장하지 않고 날짜형 데이터로 저장해 관리함. 날짜 부분을 나타내기 위한 특별한 인수를 datepart로 표기. 예를 들어 네 자리 년도는 YYYY로 표기. SYSDATETIME, GETDATE, DATENAME, DATEPART, DAY, MONTH, YEAR, DATEDIFF, DATEADD, ISDATE.

 

SYSDATETIME : SYSDATETIME() SQL Server가 동작하는 컴퓨터의 날짜 및 시간을 출력. GETDATE보다 자세함.

 

GETDATE : GETDATE() SQL Server가 동작하는 컴퓨터의 날짜 및 시간을 출력.

 

DATENAME : DATENAME(datepart, date) date 값 중 datepart에 표시된 값을 문자열로 반환.

 

DATEPART : DATEPART(datepart, date) date 값 중 datepart에 표시된 값을 숫자로 반환.

 

DAY : DAY(date) date 값 중 일을 반환.

 

MONTH : MONTH(date) date 값 중 월을 반환.

 

YEAR : YEAR(date) date 값 중 년을 반환.

 

DATEDIFF : DATEDIFF(datepart, startdate, enddate) datepart에 지정된 부분에 대하여 startdate와 enddate를 비교하여 차이 값을 반환.

 

DATEADD : DATEADD(datepart, number, date) datepart에 지정된 부분에 대하여 date값에서 number만큼 더해서 반환.

 

ISDATE : ISDATE(expression) 정상적인 날짜 값인지 판단하여 거짓이면 0, 정상이면 1을 반환.

 

NULL 값 : 아직 지정되지 않은 값. 알 수도 없고 적용할 수도 없음. '0', ''(빈 문자), ' '(공백)과는 다른 특별한 값으로 =, <, >와 같은 비교연산자로 비교 불가, NULL 값의 연산을 수행하면 결과 역시 NULL 값으로 반환. 'NULL + 숫자'연산의 결과는 NULL이고, 집계 함수 계산 시 NULL이 포함된 행은 집계에서 빠짐, 해당되는 행이 하나도 없을 경우 SUM, AVG 함수의 결과는 NULL이 되고, COUNT 함수의 결과는 0.

 

IS NULL : NULL 값을 찾을 때 사용하는 연산자.

 

IS NOT NULL : NULL이 아닌 값을 찾을 때 사용하는 연산자.

 

ISNULL : IS NULL과 구분해야 하고 ISNULL(속성, 값)으로 사용 NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력함.

 

TOP n : SQL 실행 결과에서 상위 n 개의 행만 반환.

 

부속질의 : 하나의 SQL 문 안에 다른 SQL 문이 중첩된 질의. 일반적으로 데이터가 대량일 경우 조인보다 성능이 좋음. 주질의와 부속질의로 구성. 위치와 역할에 따라 스칼라 부속질의, 인라인 뷰, 중첩질의라 부름. 또한 동작 방식에 따라 상관 부속질의/비상관 부속질의, 반환 결과에 따라 단일행 부속질의, 다중행 부속질의로 구분.

 

상관 부속질의 : 주질의의 특정 열 값을 부속질의가 상속받아 부속질의의 질의에 사용하는 형태.

 

비상관 부속질의 : 독립된 질의를 수행해서 결과 값을 가져오는 형태.

 

단일행 부속질의 : 부속질의의 결과 하나의 행을 반환하여 주질의에 전달. 비교 연산자의 수행이나 스칼라 부속질의 등에 나타남.

 

다중행 부속질의 : 부속질의의 결과 여러 개의 행을 반환. IN 연산자를 사용하여 여러 행 처리.

 

스칼라 부속질의 : 스칼라 값이 들어갈 수 있는 모든 곳에 사용 가능. 일반적으로 SELECT 문과 UPDATE SET 절에 사용 상관/비상관 모두 가능. 부속질의의 결과가 단일행, 단일열의 스칼라 값.

 

인라인 뷰 : FROM 절에서 사용되는 부속질의. 부속질의 결과가 다중행, 다중열이어도 상관 없음. 다만 상관 부속질의로 사용될 수 없음.

 

뷰 : 하나 이상의 테이블을 합하여 만들어진 가상의 테이블. 여기서 합한다는 뜻은 SELECT 문을 통해 얻은 최종 결과를 뜻함. DBMS는 실제 데이터를 디스크에 저장하지 않고 단지 뷰를 생성할 때 사용한 SELECT 문의 정의를 저장. 뷰에는 테이블에 사용할 수 있는 모든 연산을 사용할 수 있으며, 편리성, 재사용성, 보안성의 강점을 가짐. 뷰는 실제 값이 아닌 가상의 테이블이므로 INSERT, UPDATE, DELETE 등의 작업은 경우에 따라 수행되지 않으며, 기본키를 포함하지 않는 수정 요청이나 베이스 테이블 두 개 이상에서 속성을 포함하는 수정 요청은 금지됨.

 

뷰의 생성 : 사용자가 직접 정의하는 과정을 거치는데, 뷰의 정의를 뷰의 생성이라고도 함. CREATE VIEW 문으로 뷰를 생성.

 

뷰의 수정 : ALTER VIEW 문을 통해 이루어지며 뷰의 정의가 명시된 SELECT 문을 변경.

 

뷰의 삭제 : DROP 문을 이용해 삭제.

 

시스템 뷰 : DBMS에서 데이터베이스 개체나 시스템의 통계 정보 등을 사용자가 직접 확인할 수 있도록 제공. 시스템 카탈로그, 딕셔너리 뷰 등의 용어로 불림.

 

중첩질의 : WHERE 절에서 사용되는 부속질의. WHERE 절은 보통 데이터를 선택하는 조건 혹은 술어와 같이 사용. 이용되는 연산자로는 비교연산, 집합연산, 한정연산, 존재연산이 있음.

 

비교 연산자 : 부속질의가 반드시 단일행 단일열을 반환해야 함.

 

집합 연산자 : IN연산자는 주질의의 속성 값이 부속질의에서 제공한 결과 집합에 있는지 확인하는 역할. NOT IN은 이와 반대로 값이 존재하지 않으면 참이 됨.

 

한정 연산자 : ALL과 SOME이 부속질의 대상 범위를 지정하는 역할. ALL은 부속질의의 결과 집합 전체를 대상으로 하므로 결과 집합의 MAX() 값과 같다고 생각할 수 있고, SOME()은 부속질의 결과 집합 중 어떠한 값을 의미하므로 MIN()값과 같음.

 

존재 연산자 : 데이터의 존재 유무를 확인하는 연산자. 반드시 상관 부속질의의 형태로 부속질의에 필요한 값이 발견되면 참 값을 반환.

 

액세스 시간 : 디스크의 입출력 시간으로 탐색 시간, 회전 지연 시간, 데이터 전송 시간의 합. DBMS는 실제 데이터를 보조기억장치에 저장하는데 이 액세스 시간 만큼의 근본적인 속도 문제가 발생. 이러한 속도 문제를 해결하기 위해 DBMS가 사용하는 메모리 공간 중 일부를 DB 버퍼 캐시로 사용함. 이 캐시는 LRU 알고리즘으로 관리됨. 정리하자면 DBMS는 버퍼 캐시에 저장된 데이터를 우선 읽어 들인 후 작업 진행. 데이터의 삽입이나 갱신 작업 후에 버퍼 캐시에 데이터를 갱신하고, 수행 내용을 로그 캐시에 트랜잭션 순서대로 기록한 후 로그파일에 옮김. 버퍼캐시에 저장된 데이터는 최종적으로 일정 주기 혹은 이벤트의 발생에 따라 주 데이터 파일(mdf) 또는 보조 데이터 파일(ndf)에 저장.

 

탐색시간 : 액세스 헤드를 트랙에 이동시키는 시간.

 

회전지연시간 : 액세스 헤드가 섹터에 접근하는 시간.

 

데이터 전송시간 : 데이터를 주기억장치로 읽어오는 시간.

 

주 데이터 파일 : mdf 확장자 파일로 데이터베이스의 시작 정보를 포함하며 데이터베이스의 나머지 파일을 가리킴. 사용자 데이터와 개체를 저장. 모든 데이터베이스에는 하나의 주 데이터 파일이 존재.

 

보조 데이터 파일 : 선택적으로 사용하는 사용자 정의 데이터 파일이며 사용자 데이터 저장. 권장되는 파일 확장명은 ndf임.

 

트랜잭션 로그 파일 : ldf 확장자 파일로 데이터베이스 복구에 사용되는 로그 정보 저장. 데이터베이스마다 최소한 하나의 로그 파일 존재.

 

익스텐트 : DBMS가 디스크에 저장한 데이터 파일은 익스텐트 단위로 공간이 할당되며, 익스텐트는 8개의 페이지로 구성됨.

 

페이지 : SQL Server에서 사용하는 기본 저장 단위이며 8KB의 크기를 가짐. 페이지는 디스크 입출력 및 버퍼 캐시에서도 최소 단위로 사용됨. 각 페이지들은 페이지 번호, 페이지 유형, 해당 페이지의 사용 가능한 크기, 해당 페이지를 소유하고 있는 개체의 할당 단위 ID와 같은 정보를 가짐.

 

인덱스 : 도서의 색인이나 사전에서와 같이 데이터를 쉽고 빠르게 찾을 수 있도록 만든 구조. SQL Server의 인덱스는 B-tree 구조로 구성. 인덱스의 특징을 정리하자면, 인덱스는 테이블에서 한 개 이상의 속성을 이용해 생성함. 빠른 검색과 함께 효율적인 레코드 접근이 가능함. 테이블 보다 작은 공간을 차지. 저장 값들은 테이블의 부분집합으로 특징을 정리할 수 있음. 일반적으로 B-tree 형태의 구조. 데이터의 수정, 삭제 등의 변경이 발생하면 단편화가 일어나기 쉬워 인덱스 재구성 필요.

 

단편화 : B-tree 인덱스의 경우 데이터의 수정, 삭제, 삽입이 잦으면 노드의 갱신이 주기적으로 일어나 단편화 현상이 나타남. 삭제된 레코드의 인덱스 값 자리게 비어지게 되는데 이 상태를 말함. 이로 인해 검색 성능의 저하가 일어남.

 

B-tree : 데이터의 검색시간을 단축시키기 위해 Bayer가 고안. 루트 노드, 내부 노드, 리프 노드로 구성되어 있고 리프 노드가 모두 같은 레벨에 존재하는 균형 트리. 키 값과 포인터를 가져 키 값은 오름 차순이며, 키 값 좌우의 포인터는 각각 키 값보다 작은 값과 큰 값을 가리킴. 키 값이 새로 추가되거나 삭제될 때 노드의 분할 및 통합이 이루어져 트리가 항상 균형을 유지함. 값을 검색하는 경우 검색하고자 하는 값이 노드의 값보다 작으면 왼쪽, 크면 오른쪽으로 이동함. 한번 검색할 때마다 검색 대상이 줄어 접근 시간이 적게 걸림. 100만 개의 튜플을 가진 데이터도 서너번이면 찾을 수 있으니, 주요 DBMS에서 인덱스 기본 구조로 활용. 리프 노드에는 해당 데이터의 저장 위치에 대응하는 레코드 번호(RID)가 존재해 찾고자하는 행 검색 가능.

 

클러스터 인덱스 : 연속된 키 값의 레코드를 묶어서 같은 블록에 저장하는 방법으로 테이블 당 하나만 생성 가능하며, 기본키를 설정하면 자동 생성됨. 리프 노드에 레코드 번호(RID)가 아닌 테이블의 열 자체가 저장됨. 키 값에 의한 동등 검색 및 범위 검색에 모두 유리함. 또한 인덱스 페이지가 단순해 인덱스를 저장하는데 차지하는 공간도 작음.

 

비클러스터 인덱스 : 리프 노드에 실제 데이터가 위치한 테이블 번호가 들어가 있음. 테이블 당 여러 개 생성가능. 테이블과 인덱스를 별도의 페이지에 저장해 많은 저장 공간이 필요함. 특정 값을 찾는 경우 성능을 보장하지만 범위 검색은 미지수.

 

인덱스의 생성 : WHERE절, 조인에 자주 사용되는 속성인지, 속성의 선택도(1/서로 다른 값의 개수)가 낮을 때 유리, 단일 테이블에 인덱스가 많으면 속도가 느려짐, 속성이 가공되는 경우는 사용하지 않는 다는 점을 고려해야 함. CREATE INDEX 명령으로 생성.

 

인덱스의 재구성과 삭제 : 인덱스의 재구성은 ALTER INDEX 명령 사용, 인덱스의 삭제는 DROP INDEX 명령 이용.