SQLD 2장 2-3과목

2과목

2장

1. 집합 연산자
   1) 개념 : 두 개 이상의 테이블에서 조인 하지 않고, 연관된 데이터 조회시 사용
   2) 종류
   - UNION : 합집합 (중복 1개로 표시)
   - UNION ALL : 합집합 (중복 행도 표시)
   - INTERSECT : 교집합(INTERSECTION)
   - EXCEPT, MINUS(Oracle) : 차집합(DIFFERENCE)
   - CROSS JOIN : 곱집합(PRODUCT)
2. 순수 관계 연산자
   1) 개념 : 관계형 DB를 새롭게 구현
   2) 종류
   - SELECT → WHERE
   - PROJECT → SELECT
   - NATRUAL JOIN → 다양한 JOIN
   - DIVIDE → 사용x
3. FROM 절 JOIN 형태
   1) 종류
   - INNER JOIN : JOIN 조건에서 동일한 값이 있는 행만 반환, USING이나 ON 절을 필수적으로 사용
   - NATURAL JOIN : 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN 수행, NATURAL JOIN이 명시되면 추가로 USING, ON, WHERE 절에 서 JOIN 조건을 정의불가, SQL Sever는 지원x
   - USING 조건절 : 동일한 이름을 갖는 칼럼들중 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN 수행, JOIN 칼럼에 대해서 ALIAS나 테이블 이름과 같은 접두사 사용 불가, SQL Server 지원x
   - ON 조건절 : ON 조건절과 WHERE 조건절을 분리하여 이해가 쉬우며, 칼럼명이 다르더라도 JOIN 조건을 사용 가능, ALIAS나 테이블명 반드시 사용
   - CROSS JOIN : 양쪽 집합의 M*N건의 데이터 조합 발생
   - 셀프 조인 : 동일 테이블 사이의 조인, FROM 절에 동일 테이블이 2번 이상 표시, 반드시 테이블 별칭을 사용해야 함
4. OUTER JOIN
   1) 개념 : JOIN 조건에서 동일한 값이 없는 행도 반환 가능, USING이나 ON 조건절 반드시 사용해야 함
   2) 종류
   - LEFT OUTER JOIN : 조인 수행시 먼저 표기된 좌측테이블에 해당하는 데이터를 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터 읽음. 우측값에서 같은 값이 없는 경우 NULL 값
   - RIGHT OUTER JOIN : LEFT OUTER JOIN의 반대
   - FULL OUTER JOIN : 조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과 생성. 중복 데이터는 삭제
5. 계층형 질의
   1) 개념 : 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용
   2) 종류
   - START WITH : 계층 구조 전개의 시작 위치 지정
   - CONNECT BY : 다음에 전개될 자식 데이터 지정
   - PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정, PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 부모 데이터에서 자식 데이터(부모-> 자식) 방향으로 전개하는 순방향 전개, 반대는 역방향 전개
   - NOCYCLE : 동일한 데이터가 전개되지 않음
   - ORDER SIBLINGS BY : 형제 노드간의 정렬 수행
   - WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출 (필터링)
   - LEVEL : 루트 데이터이면 1, 그 하위 데이터면 2, 리프 데이터까지 1씩 증가
   - CONNECT_BY_ISLEAF : 리프 데이터 1, 그렇지 않으면 0
   - CONNECT_BY_ISCYCLE : 조상이면 1, 아니면 0 (CYCLE 옵션 사용했을 시만 사용 가능)
   - SYS_CONNECT_BY_PATH : 루트 데이터부터 현재 전개할 데이터까지의 경로 표시
   - CONNECT_BY_ROOT : 전개할 데이터의 루트 데이터를 표시, 단항 연산자
6. 서브쿼리
   1) 개념 : 하나의 SQL문안에 포함되어있는 또다른 SQL문, 알려지지 않은 기준을 이용한 검색에 사용
   2) 주의사항
   - 괄호로 감싸서 사용
   - 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능
   - 단일 행 비교 연산자(=,<,>,<> 등)는 서브쿼리의 결과가 반드시 1건이하
   - 복수 행 비교 연산자(IN, ALL, ANY, SOME, EXISTS 등)는 결과 건수와 상관없음
   - ORDER BY를 사용못함
   - SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에 사용 가능
   - 인라인뷰 : DB에 저장되지 않고 쿼리문 실행시 임시적으로 생성
7. 뷰
   1) 개념 : 실제 데이터를 갖고 있지 않음. 가상 테이블
   2) 장점
   - 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경없음
   - 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성
   - 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재할 때 사용
8. 그룹함수
   1) 종류
   - ROLLUP : Subtotal을 생성하기 위해 사용, Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성. 인수 순서에 주의 ex) Group by ROLLUP(인수1, 인수2..)
   - GROUPING : Subtotal의 total을 생성
   - CUBE : 결합 가능한 모든 값에 대하여 다차원 집계 생성, ROLLUP에 비해 시스템에 부하 심함 ex) Group by CUBE(인수1, 인수2..)
   - GROUPING SETS : 인수들에 대한 개별 집계를 구할 수 있다, 다양한 소계 집합 생성 가능 ex) Group by GROUPING SETS(인수1, 인수2..)
9. 윈도우 함수
   1) 종류
   1-1) 순위
   - RANK : 동일 한 값에 대해서는 동일한 순위를 부여(1,2,2,4)
   - DENSE_RANK : 동일한 순위를 하나의 등수로 간주 (1,2,2,3)
   - ROW_NUMBER : 동일한 값이라도 고유한 순위 부여
   1-2) 집계
   - SUM : 파티션별 윈도우의 합
   - MAX : 파티션별 윈도우의 최대 값
   - MIN : 파티션별 윈도우의 최소 값
   - AVG : 파티션별 윈도우의 평균 값
   1-3) 행 순서(SQL Server는 지원x)
   - FIRST_VALUE : 파티션별 윈도우에서 최초 값
   - LAST_VALUE : 파티션별 윈도우에서 최종 값
   - LAG : 파티션별 윈도우에서 이전 몇 번째 행의 값
   - LEAD : 파티션별 윈도우에서 이후 몇 번째 행의 값
   1-4) 비율 함수(SQL Server는 지원x)
   - RATIO_TO_REPORT : 파티션 내 전체 SUM값에 대한 행별 칼럼 값의 백분율을 소수점으로 반환. 0 < 결과 <= 1
   - PERCENT_RANK : 파티션별 윈도우에서 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 하여 행의 순서별 백분율 반환. 0 <= 결과 <= 1
   - CUME_DIST : 전체건수에 대해 현재 행보다 작거나 같은 건수에 대한 누적백분율. 0 < 결과 <= 1
   - NTILE : 파티션별 전체 건수를 인수 값으로 N등분한 결과
   1-5) 그외
   - ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING (현재 행을 기준으로 파티션 내에서 앞의 1건, 현재 행, 뒤의 1건을 범위로 지정)
10. DCL
    1) 개념 : 유저 생성하고 권한을 제어할 수 있는 명령어
    2) Oracle vs SQL Server의 사용자 차이
    - Oracle : 유저를 통해 DB에 접속. ID와 PW 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 됨
    - SQL Server : 인스턴스에 접속하기 위해 로그인이라는 것을 생성하게 되며, 인스턴스 내에 존재하는 다수의 DB에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑 (Windows 인증 방식/ 혼합 모드 방식)
    3) 시스템권한 : 사용자가 SQL문을 실행하기 위해 필요한 적절한 권한
    - GRANT : 권한 부여
    - REVOKE : 권한 취소
    - ROLE : 유저에게 알맞은 권한들을 한 번에 부여
    - CASCADE : 하위 오브젝트까지 삭제
11. 절차형 SQL
    1) 개념 : SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈 생성
    2) PL/SQL 특징
    - Block 구조로 되어있어 각 기능별로 모듈화 가능
    - 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환
    - IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램
    - DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용
    - PL/SQL은 Oracle에 내장되어 있으므로 호환성 좋음
    - 응용 프로그램의 성능을 향상
    - Block 단위로 처리 → 통신량 감소
    3) PL/SQL 구조
    - DECLARE : BEGIN~END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입 선언
    - BEGIN~END : 개발자가 처리하고자 하는 SQL문, 필요한 로직 처리
    - EXCEPTION : BEGIN~END 절에서 실행되는 SQL문 실행 시 발생하는 에러 처리
    4) T-SQL
    - SQL Server를 제어하는 언어
    5) Trigger
    - 특정한 테이블에 INSERT, UPDATE, DELETE 수행 시, 자동으로 동작
    6) 프로시저와 트리거의 차이점
    - 프로시저는 BEGIN~END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어 사용가능
    - DB 트리거는 BEGIN~END 절 내에 사용 불가
12. 옵티마이저
    1) 개념 : 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할 수행
    2) 종류
    - 규칙기반 옵티마이저 : 우선순위를 가지고 실행계획을 생성, 우선 순위가 높은 규칙이 적은 일량으로 해당 작업을 수행 판단, 인덱스 유무와 SQL문에서 참조하는 객체 참고
    - 비용(SQL문을 처리하기 위해 예상되는 소요시간 또는 자원 사용량을 의미)기반 옵티마이저 : 현재 대부분의 DB에서 사용, SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식, 테이블,인덱스,칼럼 등 다양한 객체 통계정보와 시스템 통계정보 이용
13. 실행계획
    1) 개념 : SQL에서 요구한 사항을 처리하기 위한 절차와 방법
    2) 구성 요소
    - 조인 순서
    - 조인 기법
    - 액세스 기법
    - 최적화 정보 : 실행계획 각 단계마다의 비용 표시(비용, 건수, 메모리 양)
    - 연산
14. 인덱스
    1) 개념 : 원하는 데이터를 쉽게 찾을 수 있도록 도움.검색 성능의 최적화를 목적으로 두고 있지만 느려질 수 있다는 단점이 존재
    2) 종류
    2-1) B-TREE
    - 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽 이동
    - 찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데로 이동
    - 오른쪽에 있는 값보다 크면 오른쪽으로 이동
    2-2) 클러스트형
    - 인덱스의 리프 페이지 = 데이터 페이지
    - 리프 페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장, 한가지 순서로만 정렬 가능
    2-3) 전체 테이블 스캔
    - 테이블에 존재하는 모든 데이터를 읽어 가면서 조건에 맞으면 결과로서 추출하고 조건에 맞지 않으면 버리는 방식으로 검색
    - 조건이 존재하지 않는 경우
    - 주어진 조건에 사용 가능한 인덱스가 존재하지 않는 경우
    - 옵티마이저의 취사 선택
    - 병렬처리 방식으로 처리하는 경우 등
    2-4) 인덱스 스캔
    - 인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출
    - 인덱스 유일 : 유일 인덱스를 사용하여 단 하나의 데이터를 추출(중복X, 구성 칼럼에 대해 모두‘=’로 값이 주어진 경우에만 가능)
    - 인덱스 범위 : 인덱스를 이용하여 한 건 이상의 데이터를 추출
    - 인덱스 역순 범위 : 인덱스의 리프 블록의 양방향 링크를 이용하여 내림차순으로 데이터 리딩
15. 조인 수행
    1) 종류
    - NL Join : 프로그래밍에서 사용하는 중첩된 반복문과 유사한 방식으로 조인을 수행, 랜덤 액세스 방식으로 데이터 읽음
    - Sort Merge Join : 조인 칼럼을 기준으로 데이터를 정렬하여 조인을 수행, 스캔 방식으로 데이터 읽음
    - Hash Join : CPU 작업 위주로 처리, 해슁 기법 이용, NL Join의 랜덤 액세스 문제와 SMJ의 정렬 작업 부담을 해결하기 위한 대안