SQLD 이론 요약 및 정리

1장. 데이터 모델링의 이해

 

데이터 모델링의 중요성 및 유의점

• 중복 : 같은 시간 같은 데이터 제공

• 비유연성 : 사소한 업무 변화에, 데이터 모델이 수시로 변경되면 안된다.

• 비일관성 : 신용 상태에 대한 갱신없이, 고객의 납부 이력 정보 갱신 안됨.

 

데이터 모델링

• 개념, 논리, 물리적 데이터 모델링

 

데이터 독립성 요소

• 외부 스키마 : 개개인 사용자가 보는 개인적 DB스키마

• 내부 스키마 : 모든 사용자 관점을 통합한 전체 DB

• 개념 스키마 : 물리적 장치에서 데이터가 실제적 저장됨

 

데이터 독립성

• 논리적 독립성 : 개념 스키마가 변경되어도, 외부 스키마에 영향을 주지않음

• 물리적 독립성 : 내부스키마가 변경되어도, 외부/개념 스키마는 영향이없음

 

MAPPING ( 사상 ) : 상호 독립적인 개념을 연결시켜주는 다리

 

데이터 모델링의 3요소

• THINGS

• ATTRIBUTES

• RELATIONSHIPS

 

데이터 모델링은 프로젝트에 참여한 모두가 알아야함

 

엔터티 : 집합

인스턴스 : 단수

 

데이터 모델 표기법

• 1976년 피터첸이 ENTITY RELATIONSHIP MODEL 개발

 

모델링의 특징

추상화 단순화 정확화

 

ERD 작업순서

엔터티 그림 → 엔터티 배치 → 관계 설정 → 관계명 기술 → 관계의 참여도 기술 → 관계필수여부

 

좋은 데이터 모델링 요소

1. 안전성 : 어붐에 필요한 모든 데이터가 모델에 정의

2. 중복배제 : 하나의 DB내에 동일한 사실은 한번만

3. 업무 규칙 : 많은 규칙을 사용자가 공유하도록 제공

4. 데이터의 재사용 : 데이터가 독립적으로 설계되어야함

5. 의사소통 : 업무규칙은 엔터티,서브타입,속성,관계 등 형태로 최대한 자세히 표현한다.

6. 통합성 : 동일한 데이터는 한번만 정의한다. 참조등으로 이용

 

---

 

엔터티 : 업무에 필요하고, 유용한 정보를 저장하고 관리하기 위한 집합적인 것. 보이지 않는 개념 포함

엔터티 특징

1. 반드시 해당업무에서 필요하고, 관리하고자 함

2. 유일한 식별자에 의해 식별 가능

3. 두개 이상의 인스턴스의 집합

4. 업무 프로세스에 의해 이용되어야함

5. 반드시 속성이 있어야함

6. 다른 엔터티와 최소 1개이상의 관계가 있어야함

 

엔터티 분류

• 유무형에 따른 분류 : 유형, 개념, 사건 엔터티
  • 유형 : 물리적 형태 → 사원, 물품, 강사
  • 개념 : 개념적 정보 → 조직, 보험상품
  • 사건 : 업무 수행 시 발생 → 주문, 청구, 미납

• 발생시점에 따른 분류 : 기본/키, 중심, 행위 엔터티
  • 기본/키 : 그 업무에 존재하는 정보 , 타 엔터티의 부모 역할, 자신의 고유한 주식별자를 가짐 → 사원, 부서
  • 중심 : 기본 엔터티로부터 발생, 다른 엔터티와의 관계로 많은 행위 엔터티 생성 → 계약, 사고, 주문
  • 행위 : 2개 이상의 부모로부터 발생 자주 바뀌거나 양이 증가 → 주문목록, 사원변경이력

 

엔터티 명명

• 한 업무에서 사용하는 용어 사용

• 약어 사용 금지

• 단수 명사 사용

• 고유한 이름 사용

• 생성의미대로 부여

---

 

속성

: 업무에서 필요로 하는 인스턴스로 관리하고자 하는 의미상 분리되지 않는 최소의 데이터 단위

• 한개의 엔터티는 2개이상의 인스턴스 집합

• 한개의 엔터티는 2개이상의 속성을 가짐

• 한개의 속성은 1개의 속성값을 가짐

 

속성의 분류 : 기본, 설계, 파생

• 기본 속성 : 업무로부터 추출한 모든 일반적인 속성

• 설계 속성 : 업무를 규칙화하기 위해 새로 만들거나 변형, 정의하는 속성 → 일련번호

• 파생속성 : 다른 속성에 영향을 받아 발생하는 속성, 빠른 성능을 낼수 있도록 원래 속성 값을 계산 → 합

 

도메인 : 각, 속성이 가질 수 있는 값의 범위

 

속성의 명명

1. 해당 업무에서 사용하는 이름 부여

2. 서술식, 약어는 금지

3. 전체 데이터모델에서 유일성 확보

 

---

 

관계

: 엔터티의 인스턴스 사이의 논리적인 연관성으로서, 존재의 형태나 행위로서 서로에게 연관성이 부여된 상태

 

페어링 : 엔터티 안에 인스턴스가 개별적으로 관계를 가지는것

 

UML의 관계 표현

• 연관 관계 : 항상 이용하는 관계 , ( 존재적 ) , 실선 표현

• 의존 관계 : 상대방의 행위에 의해 발생하는 관계 ( 행위적 ) , 점선 표현

 

관계의 표기법

• 관계명 : 관계의 이름

• 관계 차수 : 1;1, 1;M , M;N

• 관계선택성 : 필수, 선택

 

관계 체크사항

1. 2개의 엔터티 사이에 관심있는 연관규칙 가능

2. 2개의 엔터티 사이에 정보의 조합 발생 가능

3. 업무기술서, 장표에 관계 연결에 대한 규칙 서술 가능

4. 업무기술서, 장표에 관계연결을 가능케하는 동사 가능

 

---

 

식별자

• 엔터티 내에서 인스턴스를 구분하는 구분자

• 식별자는 논리데이터 모델링 단계에서 사용된다

• KEY 는 물리데이터 모델링 단계에서 사용한다.

 

식별자 특징 : 유일성, 최소성, 불변성, 존재성

1. 주식별자에 의해 모든 인스턴스들이 유일하게 구분됨

2. 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야함

3. 지정된 주식별자의 값은 자주 변하지 않아야함

4. 주식별자가 지정이 되면, 반드시 값이 들어와야함

 

식별자 분류

• 대표성 여부 : 주식별자, 보조 식별자

• 스스로 생성여부 : 내부식별자, 외부 식별자

• 속성의 수 : 단일 식별자, 복합식별자

• 대체 여부 : 본질식별자, 인조식별자

 

주식별자 도출 기준

1. 해당 업무에서 자주 이용되는 속성임

2. 명칭, 내역 등과 같이 이름으로 기술되는것은 불가능

3. 복합으로 주식별자를 구성할 경우 너무 많은 속성 가질수없음

 

식별자 관계

• 주식별자
  • 자식의 주식별자로 부모의 주식별자 상속
  • 부모로부터 받은 식별자를 자식엔터티의 주식별자로 이용하는경우
  • 강한 연결관계 표현 , 실선 표기

• 비식별자
  • 부모 속성을 자식의 일반속성으로 사용
  • 부모없는 자식이 생성될 수 있는 경우
  • 부모와 자식의 생명주기가 다른 경우
  • 여러개의 엔터티가 하나의 엔터티로 통합되어 표현되었는데, 각각의 엔터티가 별도의 관계를 가진 경우
  • SQL 문장이 길어져 복잡성이 증가되는것 방지
  • 약한 연결관계 표현, 점선 표기

 

---

 

2장 데이터 모델과 성능

 

성능 데이터 모델링

• DB 성능향상을 목적으로 설계 단계의 데이터 모델링때부터 정규화, 반정규화, 테이블 통합, 테이블 분할, 조인구조, PK,FK등 여러가지 성능과 관련된 사항이 데이터 모델링에 반영될수 있도록 하는것

• 분석/설계 단계에서 데이터 모델에 성능을 고려한 데이터 모델링을 수행할 경우, 성능저하에 따른 재업무 비용을 최소화 할 수 있는 기회를 가지게 된다. 데이터의 증가가 빠를수록 성능저하에 따른 성능개선 비용은 기하급수적으로 증가하게 된다.

 

성능 데이터 모델링 고려사항

1. 데이터 모델링할떄 정규화 정확하게 수행

2. DB 용량 산정

3. DB 트랜잭션 유형 파악

4. 용량과 트랜잭션의 유형에 따라 반정규화 수행

5. 이력 모델의 조정, PK,,FK 조정, 슈퍼/서브 타입 조정

6. 성능관점 데이터 모델 검증

 

기본적으로 데이터는 속성간의 함수종속성에 근거해 정규화 되어야 한다.

정규화는 선택이 아니라 필수사항이다.

 

함수적 종속성

• 데이터들이 어떤 기준 값에 의해 종속되는 현상

 

정규화

• 반복적인 데이터를 분리하고, 각 데이터가 종속된 테이블에 적절하게 배치하는것

• 일반적으로 정규화 시, 입력/수정/삭제 성능이 향상되며, 반정규화시 조인성능 향상된다.

 

반정규화 절차

1. 대상 조사 ( 처리빈도수, 범위, 통계성 )

2. 다른 방법 유도 검토 ( 뷰, 클러스터링, 인덱스 조정 )

3. 반정규화 적용 ( 테이블, 속성, 관계 반정규화 )

 

반정규화 대상조사

1. 자주 사용되는 테이블에 접근하는 프로세스 수가 많고, 항상 일정한 범위만을 조회하는 경우

2. 테이블에 대량의 데이터가 있고, 대량이 데이터 범위를 자주 처리하는 경우에 처리범위를 일정하게 줄이지 않으면 성능을 보장할 수 없는 경우

3. 통계성 프로세스에 의해 통계 정보를 필요로 할때, 별도의 통계 테이블을 생성한다.

4. 테이블에 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우

 

다른 방법 유도 검토

1. 지나치게 많은 조인이 걸려, 데이터 조회하는 작업이 기술적으로 어려울경우 뷰를 사용한다.

2. 대량의 데이터 처리나 부분처리에 의해 성능이저하되는 경우, 클러스터링을 적용하거나 인덱스를 조정함

3. 대량의 데이터는 pk의 성격에 따라 부분적인 테이블로 분리할 수 있다. ( 파티셔닝 기법 )

4. 응용 애플리케이션에서 로직을 구사하는 방법을 변경함으로써 성능을 향상시킬수 있다.

 

반정규화 기법 ( 테이블, 칼럼, 관계 )

 

테이블 반정규화

테이블 병합 ( 1;1, 1;M, 슈퍼/서브 타입 )

1. 1;1 관계를 통합해 성능향상

2. 1;M 관계를 통합해 성능향상

3. 슈퍼/서브 타입 관계를 통합해 성능향상

 

테이블 분할(수직, 수평)

1. 칼럼단위 테이블을 디스크 IO를 분산처리하기위해, 테이블을 1;1 로 분리해 성능향상

2. 로우단위로 집중발생되는 트랜잭션을 분석해 디스크 IO 및 데이터 접근의 효율성을 높여 성능 향상을 위해 로우단위로 테이블을 쪼갠다.

 

테이블 추가 (중복, 통계, 이력, 부분 테이블 추가 )

1. 다른 업무이거나 서버가 다른 경우 동일한 테이블 구조를 중복해 원격조인을 제거해 성능향상

2. SUM,AVG 등을 미리 수행해 계산해둠으로 조회시 성능향상

3. 이력 테이블 중 마스터 테이블에 존재하는 레코드를 중복하여 이력테이블에 존재시켜 성능향상

4. 하나의 테이블의 전체칼럼중 자주 이용하는 집중화된 칼럼들이 있을 때 디스크 IO를 줄이기 위해 해당 칼럼들을 모아놓은 별도의 반정규화된 테이블을 생성

 

칼럼 반정규화

1. 중복 칼럼 추가 : 조인에 의해 처리할떄 성능 저하를 예방하기 위해 중복된 칼럼을 위치시킴

2. 파생칼럼 추가 : 트랜잭션이 처리되는 시점에 계산에 의해 발생되는 성능저하를 예방하기 위해 미리 값을 계산해 칼럼에 보관

3. 이력테이블 칼럼추가 : 대량의 이력데이터를 처리시 불특정 날 조회나 최근 값조회시 나타날수있는 성능저하를 예방하기 위해 이력테이블에 기능성 칼럼( 최근 값여부, 시작과 종료일자 등)을 추가함

4. 응용시스템 오작동을 위한 칼럼 추가 : 업무적으로는 의미가 없지만, 사용자의 실수로 원래 값을오 복구하기 원하는 경우, 이전데이터를 임시적으로 중복해 보관하는 기법

 

관계 반정규화

중복 관계 추가 : 데이터를 처리하기위한 여러 경로를 거쳐 조인이 가능하지만, 이때 발생할 수 있는 성능 저하를 예방하기 위해 , 추가적인 관계를 맺는 방법

 

---

 

로우 체이닝 : 로우의 길이가 너무 길어서 데이터 블록 하나에 데이터가 모두 저장되지 않고, 두개 이상의 블록에 걸쳐 하나의 로우가 저장되어 있는 형태

 

로우 마이그레이션 : 데이터 블록에서 수정이 발생 시 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고, 다른 블록의 빈공간을찾아 저장하는 방식

 

로우 체이닝, 로우마이그레이션이 발생해 많은 블록에 데이터가 저장되면, DB메모리에서 디스크 IO가 발생 할때 많은 IO가 발생하여, 성능저하 발생한다.

트랜잭션을 분석해 적절하게 1;1 관계로 분리함으로써 성능향상이 가능하도록 해야한다.

 

PK에 의해 테이블을 분할하는 방법 ( 파티셔닝 )

1. RANGE : 대상 테이블이 날짜 또는 숫자값으로 분리가 가능하고, 각 영역별로 트랜잭션이 분리되는 경우

2. LIST : 지점, 사업소등 핵심적인 코드값으로 PK가 구성되고, 대량의 데이터가 있는 테이블의 경우

3. HASH : 지정된 HASH 조건에 따라 해시 알고리즘이 적용되어 테이블이 분리됨

 

테이블에 대한 수평/수직 절차

1. 데이터 모델링 완료

2. DB 용량산정

3. 트랜잭션 패턴 분석

4. 칼럼단위로 발생하는지, 로우단위로 발생하는지 분석해 집중화된 단위로 테이블을 분리하는것 검토

 

---

 

슈퍼/서브 타입 모델 : 업무를 구성하는데 데이터의 특징을 공통, 차이점의 특징을 고려하여 효과적 표현

 

슈퍼/서브 타입 데이터 모델의 변환 기술

1. 개별로 발생되는 트랜잭션에 대해서는 개별 테이블로 구성 ( ONE TO ONE TYPE )

2. 슈퍼 + 서브 타입에 대해 발생되는 트랜잭션에 대해서 슈퍼+서브타입 테이블로 구성 ( PLUS TYPE )

3. 전체를 하나로 묶어 트랜잭션이 발생할때는 하나의 테이블로 구성 ( SINGLE, ALL IN ONE TYPE )

 

인덱스의 특성을 고려한 PK/FK DB 성능향상

인덱스의 특징은 여러개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성의 값이 비교자로 있어야 좋은 효율을 나타낸다.

앞쪽에 위치한 속성의 값이 가급적 = 아니면 최소범위 BETWEEN, <> 가 들어와야 효율적이다.

 

---

 

분산 DB

1. 여러곳으로 분산된 DB를 하나의 가상시트메으로 사용할수있도록 한 DB

2. 논리적으로 동일한 시스템에 속하지만 컴퓨터 네트워크를 통해 물리적으로 분산되어 있는 집합

 

분산 DB를 만족하기위한 투명성

1. 분할 투명성(단편화) : 하나의 논리적 릴레이션이 여러 단편으로 분할되어 각 사본이 여러 사이트에 저장

2. 위치 투명성 : 저장장소 명시 불필요, 위치정보가 시스템 카탈로그에 유지

3. 지역 사상 투명성 : 지역 DBMS와 물리적 DB사이에 MAPPING 보장

4. 중복 투명성 : DB 객체가 여러 사이트에 중복되어 있는지 알 필요가 없는 성질

5. 장애 투명성 : 구성요소의 장애에 무관한 트랜잭션의 원자성 유지

6. 병행 투명성 : 다수 트랜잭션 동시 수행시 결과의 일관성 유지, TIMESTAMP 분산 2단계 LOCKING 이용

 

분산 DB 장단점

장점 : 지역 자치성, 신뢰성, 가용성, 효용성, 융통성, 빠른 응답속도, 비용절감, 각 지역 사용자 요구 수용

단점 : 비용증가, 오류의 잠재성 증대, 설계 관리의 복잡성, 불규칙한 응답속도, 통제의 어려움, 데이터의 무결성 위협

 

분산 DB 적용 기법

1. 테이블 위치 분산 → 설계된 테이블을 본사와 지사단위로 분산

2. 테이블 분할 분산 → 수평분할 ( 로우단위로 분리 ) , 수직분할 ( 칼럼 단위로 분리 )

3. 테이블 복제 분산 → 부분복제, 광역복제

4. 테이블 요약 분산 → 분석요약, 통합요약

 

분산 DB 설계를 고려해야 하는 경우

1. 성능이 중요한 사이트

2. 공통코드, 기준정보, 마스터 데이터의 성능향상

3. 실시간 동기화가 요구되지 않는 경우, 거의 실시간

4. 특정 서버에 부하가 집중되어 부하를 분산

5. 백업 사이트 구성하는 경우

 

---

---

 

SQL 기본

 

DB : 특정 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장해 놓은 것을 의미한다.

DBMS : 효율적인 데이터 관리 뿐만 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고, 필요시 필요한 데이터를 복구하기 위한 강력한 기능의 SW

SQL : 관계형 DB에서 데이터 정의, 조작, 제어를 위해 사용하는 언어

 

SQL 문장의 종류

DML : SELECT, INSERT, DELETE, UPDATE

DDL : CREATE, ALTER, DROP, RENAME

DCL : GRANT, REVOKE

TCL : COMMIT, ROLLBACK

 

테이블 : 데이터를 저장하는 객체, 로우와 칼럼으로 구성

정규화 : 데이터의 정합성 확보와 데이터 입력/수정/삭제 시 발생할 수 있는 [ 이상현상 ] 을 방지하기 위함

기본키 : 테이블에 존재하는 각 행을 한가지 의미로 특정할 수 있는 한개이상의 칼럼

외부키 : 다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 칼럼

---

 

데이터의 유형

CHAR : 고정길이 문자열 → 'AA' = 'AA '

VARCHAR : 가변길이 문자열 정보 → 'AA' ≠ 'AA '

NUMERIC : 정수,실수 등 숫자정보

DATE → 날짜와 시각정보

 

제약조건

1. PRIMARY KEY 기본키

2. FOREIGN KEY 참조키 외래키

3. UNIQUE KEY 고유키

4. NOT NULL

5. CHECK

 

DESC(RIBE) 테이블명; → 테이블 구조확인 (오라클)

exec sp_help 'db0.테이블명' → 테이블 구조확인(SQL Server)

---

DML

DML의 경우 실행시 , AUTO COMMIT

DML의 경우 실행시 수동 COMMIT(SQL SERVER의 경우 DML도 AUTO COMMIT )

 

와일드카드

* 모든

% 모든

- 한 글자

 

합성 연산자

+ ||

 

---

TCL

트랜잭션 : 밀접히 관련되어 분리될 수 없는 1개이상의 DB조직

1. 원자성 : 트랜잭션에 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않아야 함

2. 일관성 : 트랜잭션 실행전 DB 내용이 잘못 되지 않으면 실행 후도 잘못되지 않아야 함

3. 고립성 : 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.

4. 지속성 : 트랜잭션이 성공적으로 수행되면 DB의 내용은 영구적으로 저장된다.

---

연산자의 종류

BETWEEN A AND B

IN ( LIST )

LIKE '비교문자열'

IS NULL

NOT IN

IS NOT NULL

 

연산자 우선순위 : () → NOT → 비교연산자 → AND → OR

 

NULL 값과의 수치연산은 NULL 값을 리턴

NULL 값과의 비교연산은 거짓(FALSE)를 리턴한다.

 

ROWNUM : 원하는 만큼의 행을 가져올때 사용 ( ORACLE )

TOP : 원하는 만큼의 행을 가져올때 사용 ( SQL SERVER )

---

 

문자형 함수

LOWER : 소문자로

UPPER : 대문자로

ASCII : 문자의 ASCII값 반환

CHR / CHAR : ASCII 값에 해당하는 문자 반환

CONCAT : 문자열 연결

SUBSTR : 문자열중 M개위치의 N개의 문자 반환

LENGTH : 문자열 길이를 숫자값으로 반환

 

CONCAT('OR',' ACLE')→ 'OR ACLE'

SUBSTR('SQL ORACLE',5,3) → ORA

LTRIM('xxxYYZZxYZ','x') → YYZZxYZ 왼쪽에 x제거

RTRIM('XXYYzzXYzz','z') → XXYYzzXY 오른쪽에 z제거

TRIM('x','xx123xx) → 123 좌우 x제거

 

숫자형 함수

SIGN(n) 숫자가 양수면 1 음수면 -1 , 0이면 0 반환

MOD(n,m) : 숫자 1을 숫자2로 나누어 나머지 반환

CEIL(n) : 크거나 같은 최소 정수 반환

FLOOR(n) : 작거나 같은 최대 정수 반환

 

ROUND(38.5235,3) → 38.524

ROUND(38.5235,1) → 38.5

ROUND(38.5235) → 39 반올림계산

TRUNC(38.5235,3) → 38.523

TRUNC(38.5235,1) → 38.5

TRUNC(38.5235) → 38 그냥 없앰

 

날짜형 함수

SYSDATE/GETDATE() → 현재날짜, 시각 출력

EXTRACT/DATEPART → 날짜에서 데이터 출력

 

NULL 관련 함수

NVL(식1, 식2) / ISNULL(식1,식2) : 식1의 값이 NULL 이면 식2출력

NULLIF(식1, 식2) : 식1,식2가 같으으면 NULL 다르면 식1 출력

COALESCE (식1,식2) : NULL이아닌 최초의 표현식 출력

 

---

 

집계함수

1. 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수이다.

2. GROUP BY 절은 행들을 소그룹화 한다.

3. SELECT, HAVING, ORDER BY 절에 사용 가능

- ALL : Defualt 옵션

- DISTINCT : 같은 값을 하나의 데이터로 간주 옵션

COUNT(*) : NULL 포함 행의 수

COUNT(표현식) : NULL 제외 행의 수

SUM, AVG : NULL 제외 합계, 평균 인산

STDDEV : 표준편차

VARIAN : 분산

MAX, MIN : 최대, 최소값

 

GROUP BY, HAVING 절의 특징

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

2. 집계함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.

3. GROUP BY 절에 대해 ALIAS 사용 불가

4. 집계함수는 WHERE 절에 올수없다.

5. HAVING 절에는 집계함수를 이용해 조건 표시 가능

6. HAVING 절은 일반적으로 GROUP BY 뒤에 위치함

 

SEARCH CASE : CASE WHEN LOC = 'a' THEN 'b'

SIMPLE CASE : WHEN CASE LOC = 'a' THEN 'b'

 

ORDER BY 절의 특징

1. SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정한 칼럼을 기준으로 정렬하여 출력하는데 사용한다.

2. ORDER BY 절에 칼럼명 대신 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능하다.

3. DEFAULT 값으로 오름차순, DESC 내림차순

4. SQL 문장의 제일 마지막에 위치한다.

5. SELECT 절에 정의하지 않은 칼럼 사용 가능

 

오라클에서는 NULL을 가장 큰 값으로 취급

SQL 서버에서는 NULL을 가장 작은 값으로 취급

문장 수행 순서 : FROM - WHERE -GROUP BY - HAVING - SELECT - ORDER BY

 

---

 

JOIN

• 두개 이상의 테이블들을 연결 또는 결합해 데이터를 출력하는 것

• 일반적으로 행들은 PK나 FK 값의 연관에 의해 JOIN 이 성립된다.

• 어떤 경우에는 PK,FK 관계가 없어도 논리적인 값들의 연관만으로도 조인 성립

5가지 테이블을 JOIN 하기 위해 최소 4개의 조인이 필요함 ( N-1 )

 

EQUI JOIN

2개의 테이블간 칼럼들값이 정확히 일치하는 경우 사용

 

NON EQUI JOIN

2개의 테이블간 칼럼 값들이 서로 정확하게 일치하지 않는 경우 사용

= 연산자가 아닌 BETWEEN, < ≤ 등 연산자 사용

 

---

 

집합 연산자

두개이상의 테이블에서 조인을 사용하지않고, 연관된 데이터를 조회시 사용

SELECT 절에 칼럼수가 동일하고, SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환시 사용

 

일반집합 연산자

1. UNION

2. UNION ALL

3. INTERSECT

4. EXCEPT, MINUS

5. DIVIDE

 

FROM 절의 JOIN 형태

1. INNER JOIN

2. NATURAL JOIN

3. USING 조건절

4. ON 조건절

5. CROSS JOIN

6. OUTER JOIN

 

INNER JOIN

• JOIN 조건에서 동일한 값이 있는 행만 반환 , USING ON을 필수사용

NATURAL JOIN

• 두 테이블 간의 동일한 이름을 갖는 모든 칼럼에대해 EQUI JOIN 수행, NATURAL JOIN 이 명시되면, 추가로 USING ON WHERE 절에서 JOIN 조건을 정의할수 없다. SQL SERVER 는 지원하지않음

USING 조건절

• 같은 이름을 가진 칼럼 중 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN 가능

• JOIN 칼럼에 대해 ALIAS 나 테이블 이름과 같은 접두사를 붙일 수없다. SQL SERVER 지원하지않음

ON 조건절

• ON 조건절과 WHERE 조건절을 분리해 이해가 쉬우며, 칼럼명이 다르더라도 JOIN 조건을 사용할수있는 장점이 있다.

• ALIAS 나 테이블명 반드시 사용

CROSS JOIN

• 양쪽 집합의 M* N 건의 데이터 조합이 발생

OUTER JOIN

• JOIN 조건에서 동일한 값이 없는 행도 반환 가능

• USING, ON 조건절 반드시 사용해야함

LEFT OUTER JOIN

• 조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터 읽은 후 , 나중에 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어온다. 우측 값에서 같은 값이 없는 경우 NULL 로 채운다

RIGHT OUTER JOIN

FULL OUTER JOIN

• 조인 수행시 좌,우 테이블의 모든 데이터를 읽어 JOIN 해 결과 생성, 중복데이터 삭제함

---

계층형 질의

• 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용한다.

 

START WITH : 게층구조 전개의 시작 위치 지정

CONNECT BY : 다음에 전개될 자식 데이터 지정

PRIOR : CONNECT BY 절에 사용되며, 현재읽은 칼럼을 지정한다. PRIOR 자식 = 부모 는 순방향, PRIOR 부모 = 자식은 역방향

NOCYCLE : 동일한 데이터가 전개되지 않음

ORDER SIBLINGS BY : 형제 노드간 정렬을 수행

WHERE : 모든 전개를 수행한 후 지정된 조건을 만족하는 데이터만 추출한다.

 

LEVEL : 루트 데이터면 1, 그 하위데이터면 2 , 리프데이터까지 1씩 증가

CONNECT_BY_ISLEAF : 해당 데이터가 리프데이터면 1, 아니면 0

CONNECT_BY_ISCYCLE : 해당 데이터가 조상이면 1, 아니면 0

 

SYS_CONNECT_BY_PATH : 루트데이터로부터 현재 전개할 데이터까지의 경로 표시

CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터 표시, 단항연산자임

 

셀프 조인 : 동일 테이블 사이의 조인, FROM 절에 동일 테이블이 2번 이상 나타난다. 반드시 테이블 별 별칭을 사용해야함.

 

서브쿼리 : 하나의 SQL문안에 포함된 또다른 SQL문 , 알려지지않은 기준을 이용한 검색에 사용

서브쿼리 사용시 주의사항

1. 괄호로 감싸서 사용

2. 단일행, 복수행 비교연산자와 함께 사용가능 , 단일행 비교연산자는 서브쿼리 결과가 반드시 1건 이하여야하고 복수행 비교연산자는 결과 건수와 상관없다.

3. 서브쿼리는 ORDER BY 불가능

4. SELCT,FROM, WHERE, HAVING GROUP BY , INSERT VALUES , UPDATE SET 절에 사용 가능

단일행 비교연산자 : = < , > , <> 등

다중행 비교연산자 : IN ALL ANY SOME 등

스칼라 서브쿼리 : 테이블 명이 올수있는곳에 사용 오더바이 사용가능

 

뷰 : 테이블은 데이터를 실제로 갖지만 , 뷰는 실제 데이터 X, 가상 테이블이라고함

장점

1. 독립성 : 구조 변경되어도 응용프로그램 변경 X

2. 편리성 : 복잡한 질의를 뷰로 생성해 관련 질의를 단순하게 작성 가능

3. 보안성 : 숨기고싶은 정보 숨길수잇으

 

---

 

ROLLUP : SUBTOTAL을 생성하기 위해 사용, GROUPING COLUMN의 수를 N이라고 했을 때, N+1 의 LEVEL의 SUBTOTAL이 생성된다. 인수 순서에 주의

 

GROUPING : SUBTOTAL의 TOTAL을 생성

 

CUBE : 결합 가능한 모든 값에 대해, 다차원 집계 생성 , 롤업에 비해 시스템 부하 심하다.

 

GROUPING SETS : 인수들에대한 개별집계를 구할 수 있다. 다양한 소계 집합 생성 가능

 

---

 

윈도우 함수 : 행과 행간의 관계를 정의하거나 , 행과 행간을 비교, 연산하는 함수

 

RANK : 틍정항목에 대한 순위 , 동일 값에대해 동일한 순위 부여 → 1,2,2,4

DENSE_RANK : 동일한 순위를 하나의 등수로 간주 → 1,2,2,3

ROW_NUMBER : 동일한 값이라도 고유한 순위번호 부여 → 1,2,3,4

 

SUM : 파티션별 윈도우의 합

COUNT : 조건에 맞는 데이터에 대한 통계값

FIRST_VALUE : 가장 먼저나온 값 ( SQL X )

LAST_VALUE : 가장 나중에 나온 값

LAG : 이전 몇번째 행의 값을 가져올수 있다.

LEAD : 이후 몇번째 행의 값을 가져올수 있다.

 

RATIO_TO_REPORT : 파티션 내 전체 SUM값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다. 결과는 0 < X ≤ 1

PERSENT_RANK : 파티션별 윈도우에서 제일 먼저나오는것을 0 , 늦게나오는것을 1로 행의 순서별 백분율을 구한다. 0≤ X ≤ 1

CUME_DIST : 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구한다. 0 < X ≤ 1

NTILE : 파티션별 전체 건수를 인수값으로 N등분한 결과

 

---

DCL

 

ORACLE 과 SQL Server의 사용자 아키텍처 차이

ORACLE : 유저를 통해, DB에 접속을 하는 형태, ID와 PW방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여 받게 됨

SQL Server : 인스턴스에 접속하기 위해 로그인이라는 것을 생성하게 되며, 인스턴스 내에 존재하는 다수의 DB에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑해 주어야한다. 윈도우인증, 혼합모드 방식이 존재함

 

GRANT : 권한부여,

REVOKE : 권한회수

모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면, 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야한다.

 

ROLE : 유저에게 알맞은 권한들을 한번에 부여하기위해 사용하는 것.

CASECADE : 하위 오브젝트까지 삭제

 

---

 

절차형 SQL

• SQLD 문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다.

• 프로시져, 유저정의기능, 트리거 등이 있음

 

저장 모듈 : PL/SQL 문장을 DB 서버에 저장하여, 사용자와 애플리케이션 사이에서 공유할수 있도록 만든 일종의 SQL 컴포넌트 프로그램, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램

 

PL/SQL 의 특징

1. BLOCK 구조로 되어있어, 각 기능별로 모듈화 기능

2. 변수 상수 등을 선언하여, SQL 문장간 값 교환

3. IF, LOOP 등의 절차형 언어를 사용하여 절차적 프로그램이 가능하게함

4. DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.

5. PL/SQL 은 ORACLE에 내장되어 있으므로, 호환성 굳

6. BLOCK 단위로 데이터를 처리함 → 통신량을 줄일수있다

 

DECLARE : BEGIN - END 절에서 사용될 변수와 인수에대한 정의 및 데이터 타입 선언부

BEGIN - END : 개발자가 처리하고자 하는 SQL 문과 여러가지 비교문 , 제어문을 이용 필요한 로직 처리

EXCEPTION : BEGIN - END 절에 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할지 정의하는 예외처리부

 

T-SQL : 근본적으로 SQL Server 제어하는 언어

TRIGGER : 특정한 테이블에 INSERT, UPDATE,DELETE 와 같은 DML 문이 수행되었을때, DB에서 자동으로 동작하도록 작성된 프로그램, 사용자 호출이 아닌 DB 자동 수행

 

프로시저와 트리거의 차이점

프로시저는 BEGIN - END 절 내에 COMMIT ,ROLLBACK 같은 명령어 사용가능

트리거는 BEGIN - END절내에 사용불가

 

---

 

옵티마이저 : 사용자가 질의한 SQL문에 최적의 실행방법을 결정하는 역할 수행

 

비용기반 옵티마이저 : 대부분 DB에서 사용, SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식, 비용이란 SQL문을 처리하기 위해 예상되는 소요시간 또는 자원 사용량을 의미한다. 테이블, 인덱스, 칼럼 등 다양한 객체 통계정보와 시스템 통계정보등을 이용한다.

 

규칙기반 옵티마이저 : 우선순위를 가지고 실행계획 생성, 우선순위가 높은 규칙이 적은 일량으로 해당 작업을 수행한다고 판단함, 인덱스 유무와 SQL문에서 참조하는 객체등을 참고

 

실행 계획 : SQL 에서 요구사항을 처리하기 위한 절차와 방법을 의미, 실행계획을 구성하는 요소에는 조인순서, 조인기법, 액세스 기법 , 최적화 정보, 연산 등이 있다.

 

---

 

인덱스

• 원하는 데이터를 쉽게 찾도록 돕는 책의 찾아보기와 유사한 개념이다.

• 검색성능의 최적화를 목적으로 함

• 느려질 수 있음 .

 

B-TREE INDEX에서 원하는 값 찾는 과정

1. 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나, 같으면 왼쪽 포인터로 이동됨

2. 찾고자 하는 값이 브랜치 왼쪽 값 사이에 존재하면 , 가운데 포인터로 이동

3. 오른쪽에 있는 값보다 크면, 오른쪽 포인터로 이동

 

전체 테이블 스캔

• 테이블에 존재하는 모든 데이터를 읽어 가면서, 조건에 맞으면 결과로서 추출하고 조건에 맞지 않으면 버리는 방식

 

전체 테이블 스캔 하는 경우

1. SQL문에 조건이 존재하지 않는 경우

2. SQL문에 주어진 조건에 사용가능한 인덱스가 없을때

3. 옵티마이저의 취사 선택

4. 병렬처리 방식으로 처리하는 경우 등

 

인덱스 스캔 : 인덱스를 구성하는, 칼럼의 값을 기반으로 데이터를 추출하는 액세스 기법

인덱스 유일 스캔 : 유일 인덱스를 사용해, 단하나의 데이터를 추출하는 방식 ( 중복 X , 구성 칼럼에 대해 모두 = 로 값이 주어진 경우에만 사용 가능 )

인덱스 범위 스캔 : 인덱스를 이용해 한건이상의 데이터 추출 방식

인덱스 역순 범위 스캔 : 인덱스의 리프블록의 양방향 링크를 이용해, 내림차순으로 데이터를 읽는다

 

---

 

NESTED LOOP JOIN

• 프로그래밍에서 사용하는 중첩된 반복문과 유사한 방식으로 조인 수행

• 랜덤 액세스 방식으로 데이터를 읽는다.

 

SORT MERGE JOIN

• 조인 칼럼을 기준으로 데이터를 정렬하여 조인 수행

• 스캔 방식으로 데이터를 읽음

 

HASH JOIN

• CPU 작업 위주로 처리

• 해쉬 기법이용

• NL JOIN 의 랜덤액세스 문제와 SMJ의 정렬 작업 부담을 해결하기 위한 대안