SQLD 자격검정 간단 개념 요약

2021-11-28 21:43:23

 

칼럼 = 세로, 열, 필드, 속성 (같은 도메인)

로우 = 가로, 행, 레코드, 튜플 (셀 수 있는 수량의 순서 or 열거)

---

NULL은 아직 정의되지 않은 미지의 값, 현재 데이터를 입력하지 못한 경우,

값의 부재, 모르는 값 (공백 아님, 숫자 0 아님)

---

함수에는 내장함수(BUILT-IN FUNCTION): 벤더 제공

               사용자 정의 함수 (USER DEFINED FUNCTION)  있고

 

 

내장함수에는 단일행함수(SINGLE ROW)

                     다중행함수(MULTI ROW)가 있다

 

 

다중행 함수에는 집계함수(AGGREGATE), 그룹함수(GROUP), 윈도우함수(WINDOW) 있음

 

 

 단일행 함수

 -그룹함수 제외 거의 다

 -추출되는 각 행마다 작업 수행

 -각 행마다 하나의 결과 반환

 -SELECT, WHERE, ORDER BY, UPDATE SET 절 사용 가능

 -데이터 타입 변경 가능

 -중첩 사용 가능

 

 

 다중행 함수

 -여러 개 행 입력되어 하나의 값 반환

 -그룹(집계)함수 ex) SUM, AVG, MAX, MIN, COUNT..

---

SELECT 구문 처리

FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY

 

인라인 뷰에는 WHRER 조건 올 수 없음

인라인뷰 메인테이블의 칼럼을 조건절 사용 시 에러

 

 

GROUP BY, HAVING절 특성

-GROUP BY절 통해 소그룹별 기준 정한 후 SELECT절 집계 함수 사용

-집계함수 통계정보는 NULL값 가진 행 제외하고 수행

-GROUP BY 절은 SELECT절과 달리 ALIAS(별칭) 사용 못함

-GROUP BY절 사용으로 인해 SELECT절에만 있는 칼럼을 ORDER BY절에 써야 함

(GROUP BY절에 집계함수 사용 경우 ORDER BY절에도 집계함수 사용 가능)

-집계함수는 WHERE절에 올 수 없음

(집계함수 사용할 수 있는 GROUP BY절보다 WHERE절이 먼저 수행됨)

-GROUP BY절에 의한 소그룹별 만들어진 집계 데이터 중 

HAVING절에서 제한 조건을 두어 조건 만족하는 내용만 출력

-HAVING절은 일반적으로 GROUP BY 뒤에 위치

-HAVING절 집계함수 GROUP BY 소그룹 정하지 않고도 사용 가능

 

JOIN : 두 개 이상 테이블 연결 또는 결합해 데이터 출력

PK, FK 값의 연관에 의해 JOIN 성립이 일반적이지만 어떤 경우는 PK,FK 관계없어도

논리적인 값들의 연관만으로도 JOIN 성립 가능하다

---

PRIOR : CONNECT BY 절에 사용, 현재 읽은 칼럼 지정

PRIOR자식=부모(프자부)  순방향 전개(부모→자식)

PRIOR부모=자식 역방향 전개(자식→부모)

 

 

ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이 정렬 수행

ORACLE 계층형 질의 루트 노드 LEVEL 값은 1

---

계층형 질의문

-SQL Server에서의 계층형 질의문은 CTE를 재귀 호출함으로써 계층 구조 전개

-SQL Server에서의 계층형 질의문은 앵커 멤버를 실행해 기본 결과 집합을 만들고

이후 재귀 멤버를 지속적으로 실행

-오라클의 계층 질의문에서 WHERE 절은 모든 전개를 진행한 이후 필터 조건으로서

조건을 만족하는 데이터만 추출하는데 활용

-오라클 계층형 질의문에서 PRIOR 키워드는 SELECT, WHERE절에서도 사용 가능

(CONNECT BY 절 당연하고)

 

-테이블에 계층형 데이터 존재하는 경우 데이터 조회 위해 '계층형 질의'사용

---

셀프 조인 SELF JOIN

동일 테이블 사이의 조인, FROM절에 동일 테이블이 두 번 이상 나타남

동일 테이블 사이 조인 시 테이블과 칼럼 이름 동일하기 때문에 식별을 위해

반드시 테이블 별칭(ALIAS) 사용

---

반환되는 데이터 형태에 따른 서브 쿼리 분류

SINGLE ROW 서브쿼리(단일행) 

-실행결과 항상 1건 이하

-단일행 비교 연산자와 함께 사용(=,<, <=,>,>=,<>)

 

MULTI ROW 서브 쿼리(다중행)

-실행결과 여러 건

-다중행 비교 연산자 함께 사용(IN, ALL, ANY, SOME, EXISTS)

 

MULTI COLUMN 서브 쿼리(다중 칼럼)

-실행결과 여러 칼럼 반환

-메인 쿼리 조건절에 여러 칼럼 동시 비교 가능

-서브 쿼리와 메인 쿼리에서 비교하고자 하는 칼럼 개수, 칼럼 위치 동일해야 함)

-SQL Server에서 지원하지 않음

 

 

서브 쿼리

-다중행 서브 쿼리 비교연산자는 단일행 서브쿼리 비교 연산자로도 사용 가능

-비연관 서브 쿼리는 주로 메인 쿼리에 값을 제공하기 위한 목적 사용

-메인 쿼리의 결과가 서브 쿼리로 제공될 수도 있고

서브 쿼리 결과가 메인쿼리로 제공될 수 있으므로 실행순서는 상황에 따라 달라짐

 

 

FROM절에 사용되는 서브쿼리 = 인라인 뷰 (Inline View)

-서브 쿼리 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있음

  =동적 뷰(Dynamic View)

-SQL 문장 중 테이블 명이 올 수 있는 곳에서 사용 가능

-SQL문 실행 시에만 임시 생성되는 동적인 뷰라서 데이터베이스에 해당 정보가 저장되지 않음

---

뷰(VIEW) - 독립성/편리성/보안성

-뷰는 단지 정의만 가지고 있으며 실행 시점에 질의를 재작성해 수행

-독립성:테이블 구조 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 됨

-보안성:뷰는 보안 강화하기 위한 목적으로 활용 가능

-편리성:복잡한 질의를 뷰로 생성해 단순하게 작성 가능, 해당 형태의 SQL문을 자주 사용할 때 

뷰를 이용하면 편리하게 사용할 수 있다.

---

-ROLLUP은 계층 구조를 가진 SUBTOTAL을 생성하는 함수로 나열된 칼럼의 순서가 변경되면 수행 결과도 변경됨

-GROUPING SETS 함수는 전체 합계 구하지 않음

-CUBE, GROUPING SETS, ROLLUP 세 가지 그룹 함수 모두 일반그룹 함수로 동일한 결과 추출 가능

-CUBE, GROUPING SETS, ROLLUP 함수들에 의해 집계된 레코드에서 집계 대상 칼럼 이외

GROUP 대상 칼럼의 값은 NULL을 반환한다

-CUBE 그룹 함수는 인자로 주어진 칼럼의 결합 가능한 모든 조합에 대해 집계 수행하므로

다른 그룹 함수에 비해 시스템에 대한 부하가 큼

가질 수 있는 모든 경우에 대한 SUBTOTAL 생성, 경우에 바람직하나 시스템 부하 때문에 사용주의

정렬 필요한 경우 ORDER BY절 명시 필요

-GROUPING SETS은 다양한 소계 집합 만들 수 있는데 표시된 인수들에 대한 개별 집계 구할 수 있으며,

이때 표시된 인수들 간 계층 구조인 ROLLUP과 달리 평등한 관계, 인수 순서 바뀌어도 결과 같음

정렬 필요시 ORDER BY절 명시

---

-CUBE 가능한 모든 조합 합계, GROUPING SETS 지정한 조합별 합계, ROLLUP 단계별 합계

ROLLUP(A, B, C)

(A, B, C)

(A, B)

(A)

() 전체 총합 묶음

CUBE(A, B)

(A, B)

(A)

(B)

()

GROUPING SETS(A, (B, C))

(A)

(B, C)

---

RANK

1 2 2 4 연속 등수 X

 

DENSE_RANK

1 2 2 3 다음연속 등수 (동일한 순위 하나의 건수 취급)

 

ROW-NUMBER

1 2 3 4 연속등수

---

RARIO_TO_REPORT

값의 비율을 나타내는 함수로 윈도 함수가 적용되는 칼럼 공간의

값의 합 중 해당 행이 차지하는 비율

전체에 해당 행 칼럼 값 비율, 합계에서 나누기

 

PERCENT_RANK

제일 먼저 나오는 것 0, 제일 늦게 나오는 것 1 처리해

값이 아닌 행의 순서별 백분율 구하기 

잘리는 횟수로 나누기

 

CUME_DIST(CUMULATIVE누적)

현재 행보다 작거나 같은 건수에 대해 누적 백분율  

0이 나올 수 없음

 

NTILE

전체 그룹 N등분

ex) NTILE(4) - 14개 행일 경우 14/4는 3에 나머지 2, 나머지 2를 상위 테이블에 하나씩 나눠줌

---

PL/SQL 개념 특징

-SQL에서도 절차적인 프로그래밍 가능하도록 지원

-BLOCK 구조로 설계되어 각 기능별 모듈화 가능

여러 SQL 문장 BLOCK 한 번에 서버로- 통신량 감소

-변수, 상수 등 선언해 SQL 문장 값 교환 가능

-DBMS 정의 에러, 사용자 정의 에러 사용 가능

-유형 : 프로시저, 사용자 정의 함수, 트리거

 

 

사용자정의함수

-프로시저처럼 절차형 SQL 로직과 함께 저장한 명령문 집합

-내장 함수 벤더에 의해 정의된 함수

-프로시저와 달리 반드시 수행 결과 리턴해야 함

 

트리거

-DML 수행 시 이와 연결된 동작 자동 수행하도록 작성하는 프로그램

-사용자가 명시적 호출 X , 조건 맞으면 자동 수행

-주로 데이터 무결성 보장 위해 FK처럼 동작하거나 실시간 집 계성 테이블 생성에 사용됨

-보안 적용, 유효하지 않은 트랜잭션 예방, 업무규칙 적용, 감시 제공 등 사용

-OLTP시스템에서는 부하로 인해 성능 저하될 수 있음

-롤백 시 원 트랜잭션뿐 아니라 트리거에 의해 실행된 연산 모두 취소됨

TRIGGER는 INSERT, DELETE, UPDATE문과 연결된 하나의 트랜잭션 내에 수행되는 작업으로 이해해야 함

 

 

프로시저 - EXECUTE 명령어로 실행( EXECUTE 프로시저 이름)

사용자 정의 함수 - 함수 이름으로 실행( SELECT 함수)

 

트리거 - 생성된 후 DML에 의해 자동실행

 

 

ROW TRIGGER - FOR EACH ROW 각행마다

 

STATE TRIGGER - FOR EACH ROW 생략, SQL 한 문장에 한 번만 TRIGGER

 

ROW와 STATE 레벨은 트리거 실행되는 시점과 횟수가 다르다

 

 

PL/SQL에서는 동적 SQL 또는 DDL문장 실행 시 EXECUTEIMMEDIATE 사용

 

비용 기반 - cost 단어

규칙 기반 - cost 단어 x 

---

비용 기반 옵티마이저 실행계획

-조인 기법

-액세스 기법

-최적화 정보는 실제 실행 결과 아닌 통계 정보 바탕의 예측치

cost 상대적 비용정보

card 주어진 조건 만족하는 행의 수

bytes 결과 집합이 차지하는 메모리의 양

전부 숫자가 낮을수록 적을수록 유리하다

---

NL(NESTED LOOP) 조인 - 소규모

-소규모 데이터 인덱스 사용해 작업하는 OLTP 업무 적합

-연결 조건 인덱스 및 드라이빙 테이블의 우선순위에 따라 성능 결정

 

SORT MERGE 조인 - 정렬

-인덱스 사용 안 함, NON-EQUI JOIN 가능

-정렬 수행에 비용 소요됨

 

HASH JOIN -해시함수 만들어내고 데이터 많을 경우

-EQUI JOIN만 가능, 데이터 많을 경우 유리

-해시함수 계산에 비용이 소모

-결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 경우 성능 향상

(NL 조인도 마찬가지)

 

 

단계별로 다른 조인 적용 가능

ex) A와 B 조인에 NL 조인, 그 결과와 C 조인은 해시 조인