[데이터베이스] SQL 고급 (내장함수, subquery, 뷰)

내장함수

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

SQL 내장 함수

SQL 내장 함수는 상수나 속성 이름을 입력 값으로 받아 단일 값을 결과로 반환한다.

모든 내장 함수는 최초에 선언될 때 유효한 입력값을 받아야한다. 만약 선언에 위배된 값이 입력되면 질의는 실행을 중지하고 에러 메세지를 출력한다.

SQL 내장 함수는 SELECT, WHERE, UPDATE, SET 절 등에 모두 사용 가능하다.

 

수학 함수

 

예시

SELECT ROUND(4.875, 1)

답
4.900

 

문자 함수

 

날짜 함수

날짜형 데이터로 저장하면 날짜를 더하거나 차이를 구하는 등의 연산을 손쉽게 처리할 수 있다.

 

NULL 값 처리

NULL값이란 아직 지정되지 않은 값을 의미한다.
'0', '(빈 문자)',, ' '(공백) 등과 다른 특별한 값이며 비교 연산자로 비교 불가능하다.
NULL값의 연산을 수행하면 결과 역시 NULL값으로 반환된다.

 

NULL값에 대한 연산 주의사항

• NULL+숫자 연산의 결과는 NULL이다.
• 집계 함수 계산시 NULL이 포함된 행은 집계에서 빠진다.

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

 

IS NULL, IS NOT NULL

• NULL값을 찾을 때는 = 연산자가 아닌 IS NULL을 사용하고, NULL값이 아닌 값을 찾을 때는 IS NOT NULL을 사용한다.

  SELECT *
  FROM Mybook
  WHERE price IS NULL

 

TOP n 질의

SQL 실행 결과에서 상위 n개의 행을 반환한다.

SELECT TOP 2 custid, name, phone
FROM Customer
ORDER BY name;

 

부속 질의(subquery)

하나의 SQL문 안에 다른 SQL 문이 중첩된 질의
다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공할 때 사용한다.

 

조인 vs 부속 질의

상황 : 고객 이름과 주문 내역을 같이 보고 싶다.

1. Customer 테이블과 Orders 테이블의 고객 번호를 사용하여 조인한후 데이터 추출
2. Customer 테이블에서 고객의 고객 번호를 찾고, 찾은 고객 번호를 바탕으로 Orders 테이블에서 확인한다.(subquery)

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

 

부속 질의의 종류

1. 스칼라 부속 질의 : SELECT, UPDATE 절에서 사용되는 부속 질의, 단일 값 반환

UPDATE Orders
SET bookname=(SELECT bookname
             FROM Book
             WHERE Book.bookid=Orders.bookid);

한번에 업데이트 가능

 

2. 인라인 뷰 : FROM절에서 사용되는 부속 질의, 가상의 테이블인 뷰 형태로 제공

SELECT cs.name, SUM(od.saleprice) "total"
FROM (SELECT custid, name
     FROM Customer
     WHERE custid <= 2) cs,
     Orders od
WHERE cs.custid = od.custid
GROUP BY cs.name;

인라인 뷰를 사용하면 조인에 참여하기 직전 Customer 테이블에서 필요한 데이터만 뽑아 조인시킬 수 있으므로 성능의 손실을 줄일 수 있다.

 

3. 중첩질의 : WHERE절에서 사용되는 부속 질의
주질의의 자료 집합에서 한 행씩 가져와 부속 질의를 수행하며, 연산 결과에 따라 조건이 참일 경우 주질의의 해당 행을 출력한다.

 

 

평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 보이시오

SELECT orderid, saleprice
FROM Orders
WHERE saleprice<= (SELECT AVG(saleprice)
                    FROM Orders);

ALL : MAX값과 같음
SOME : MIN값과 같음

 

뷰(view)

하나 이상의 테이블을 합하여 만든 가상의 테이블

• 뷰는 실제 테이블처럼 사용가능하지만 실제 데이터를 디스크에 저장하지 않고, 단지 뷰를 생성할 때 사용한 SELECT문의 정의를 DBMS가 저장한다.
• 뷰를 사용하여 질의를 처리하면 DBMS는 뷰의 정의를 참조하여 질의를 수행하고 그 결과를 사용자에게 반환한다.
• 뷰는 테이블에 사용할 수 있는 모든 연산을 사용할 수 있다.

 

뷰의 장점

• 편리성 : 미리 정의된 뷰를 일반 테이블처럼 사용가능하므로 편리하다. 사용자가 필요한 정보만 요구에 맞게 가공하여 뷰로 만들어 쓸 수 있다.
• 재사용성: 자주 사용되는 질의를 뷰로 미리 정의할 수 있다.
• 보안성: 각 사용자별로 필요한 데이터만 선별하여 보여줄 수 있다. 중요한 질의의 경우 질의 내용을 암호화할 수 있다.

 

뷰의 생성

CREATE VIEW 뷰이름 [(열이름 [,...n])]
AS SELECT 문

 

예시

주소에 대한민국을 포함하는 고객들로 구성된 뷰를 만들고 조회하시오

CREATE VIEW vw_Customer
AS SELECT *
    FROM Customer
    WHERE address LIKE '%대한민국%';

 

여러 테이블에 같은 속성이 있을 경우 테이블의 속성으로 지정

 

뷰 사용하기

SELECT *
FROM vw_Customer

 

뷰의 수정

ALTER VIEW 뷰이름 [(열이름 [,..n])]
AS SELECT 문

뷰 vw_Customer를 영국 주소로 가진 고객으로 변경

ALTER VIEW vw_Customer (custid, name, address)
AS SELECT custid, name, address
    FROM Customer
    WHERE address LIKE '%영국%';

 

뷰의 삭제

DROP VIEW 뷰이름 [,...n];

예시

DROP VEW vw_Customer;

 

 

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

SQL 작업 도구를 통해 SQL문을 작성하여 실행하면 DBMS에 의해 처리 방법이 결정되고, 운영체제를 통해 각 장치에 명령이 내려져 작업이 처리된다.

실제 데이터가 저장되는 곳은 보조기억장치이다.

 

액세스 시간

디스크의 입출력 시간을 액세스 시간이라고 한다.

액세스 시간 = 탐색 시간 + 회전 지연시간 + 데이터 전송 시간

• 탐색 시간 : 액세스 헤드를 트랙에 이동시키는 시간
• 회전 지연 시간 : 섹터가 액세스 헤드에 접근하는 시간
• 데이터 전송 시간 : 데이터를 주기억장치로 읽어오는 시간

DB 버퍼캐시

DBMS가 하드디스크에 데이터를 저장하고 읽어올때는 근본적인 속도 문제가 발생할 수 밖에 없다.
컴퓨터 시스템에서 처리되는 연산 속도는 빠른데, 디스크의 액세스 속도는 느리기 때문이다.
=> 이러한 속도 문제를 줄이기 위해 주기억장치에 DBMS가 사용되는 공간 중 일부를 DB 버퍼캐시로 만들어 사용한다.

DB 버퍼캐시에는 자주 사용하는 데이터를 저장해두는데, LRU 알고리즘에 의해 사용빈도가 높은 데이터 위주로 저장, 관리한다.

 

데이터 검색 과정

• 데이터 검색시 DBMS는 버퍼 캐시에 저장된 데이터를 우선 읽어들인 후 작업을 진행한다.
• 삽입이나 갱신 작업 후에도 버퍼캐시에 데이터를 갱신하고, 수행 내용을 로그캐시에 트랜잭션 순서대로 기록한 후 로그파일(LDF)로 옮긴다.
• 버퍼캐시에 저장된 데이터는 최종적으로 일정 주기 혹은 이벤트의 발생에 따라 주 데이터 파일 또는 보조 데이터 파일에 저장된다.

 

SQL Server의 내부 구조

• DBMS는 데이터베이스별로 하나의 데이터 파일(MDF, NDF)이 생성된다.
• 테이블은 생성시 정의된 내용에 따라 논리적으로 구분되어 각각의 데이터 파일에 저장된다.파일 종류
• 주 데이터 파일(mdf) : 사용자 데이터와 개체를 저장한다.
• 보조 데이터 파일(ndf) : 선택적으로 사용하는 사용자 정의 데이터 파일이며 사용자 데이터를 저장한다.
• 트랜잭션 로그 파일(LDF) : 데이터베이스 복구에 사용되는 로그 정보를 저장하며, 데이터베이스마다 최소한 하나의 로그 파일이 있어야한다.인덱스와 B-Tree
• 인덱스 : 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조
  SQL 서버의 인덱스는 B-tree 구조로 되어 있다.

 

Reference

책 : SQL Server로 배우는 데이터베이스 개론과 실습

https://m.yes24.com/Goods/Detail/97538787

SQL Server로 배우는 데이터베이스 개론과 실습 - 예스24