SQL 정리 5/13~

Jeong Min·2025년 5월 12일

데이터 모델링의 이해

SQL 기본 용어 정리
엔터티(테이블) : 데이터의 집합체
컬럼(속성) : 열의 제목 (EX. 이름, 나이 등)
인스턴스(행) : 정보가 들어있는 행의 집합

모델링이란?
현실 세계의 비즈니스 프로세스와 데이터 요구 사항을 추상적이고, 구조화된 형태로 표현하는 과정

모델링의 특징
1. 단순화 : 핵심요소에 집중하고 불필요한 세부 사항을 제거.
2. 추상화 : 일정한 형식에 맞추어 간략하게 대략적으로 표현.
3. 명확화 : 애매모호함을 제거하고 정확하게 현상을 기술.

데이터 모델링의 3가지 관점
1. 데이터 관점 : 데이터가 어떻게 저장되고, 접근되고, 관리되는지를 정의.
2. 프로세스 관점 : 시스템이 어떤 작업을 수행하고, 작업들이 어떻게 조직되고 조정되는지 정의하는 단계.
3. 데이터와 프로세스 관점 : 데이터 관점과 프로세스 관점을 결합해, 어떤 데이터가 필요하고, 생성하고, 변경되는지를 정의.

데이터 모델링 유의점
1. 중복 최소화 : 한 테이블 또는 여러 테이블에 같은 정보를 저장하지 않도록 설계.
2. 비유연성 최소화 : 사소한 업무 변화에 대해서 잦은 모델 변경이 되지 않도록 주의.
3. 비일관성 최소화 : 데이터 베이스 내의 정보가 모순되거나 상반된 내용을 갖지 않도록 주의. 데이터의 중복이 없더라도 비일관성이 발생할 수 있음.

데이터 모델링의 3가지 요소.
1. 대상(엔터티) : 업무가 관리하고자하는 대상
2. 속성(Attribute) : 대상이 갖는 속성
3. 관계(Relationship): 대상들 간의 관계

데이터 모델링의 3단계 (아래로 갈수록 구체화)
1. 개념적 모델링 :
업무 중심적이고 포괄적인 수준의 모델링/ 추상화 정도가 가장 높음/ 핵심 엔터티를 추출하는 단계/ 핵심 엔터티 간 관계를 표현하기 위해 ERD 작성.
2. 논리적 모델링 :
개념적 모델링 결과를 토대로 세부속성, 식별자, 관계 등을 표현/ 데이터 정규화 수행
3. 물리적 모델링 :
논리 모델링이 끝나면 직접 물리적으로 생성하는 과정/ 가장 구체적이고 추상화 수준이 가장 낮음.

스키마란?
데이터베이스의 구조와 제약 조건에 관한 전반적인 명세를 기술한 메타데이터의 집합

스키마의 3단계 구조
1. 외부 스키마 : 사용자가 보는 관점에서 데이터베이스 스키마를 정의.
2. 개념 스키마 : 사용자 관점의 데이터베이스 스키마를 통합해 데이터 베이스의 전체 논리적 구조를 정의. (논리적 모델링을 토대로)
3. 내부 스키마 : 데이터가 물리적으로 어떻게 저장되는지를 정의 (물리적 모델링을 토대로)

3단계 스키마의 독립성
-독립성 : 물리적, 논리적 구조를 변경하더라도 사용자가 사용하는 응용 프로그램에 영향 X.
1) 논리적 독립성 : 논리적 데이터 구조가 변경되어도(개념스키마 변경) 응용 프로그램에 영향을 주지 않는 특성.
2) 물리적 독립성 : 물리적 구조가 변경되어도(내부 스키마 변경) 개념/외부 스키마에 영향을 주지 않는 특성.

ERD 란? (Entity Relationship Diagram)
엔터티와 엔터티 간의 관계를 시각적으로 표현한 다이어그램

ERD 작성 절차 6단계
엔터티 도출 후 그림 > 엔터티 배치 > 엔터티 간 관계 설정 > 관계명 서술 > 관계의 참여도 기술 > 관계의 필수 여부 확인

엔터티란?
현실 세계에서 독립적으로 식별 가능한 객체나 사물.
EX)
엔터티 : 학생
속성 : 학번, 이름, 학과 등
식별자 : 학번
인스턴스 : 특정 학생의 데이터 (학번 :0000/ 이름 김OO/ 학과 OO학과)

엔터티의 특징
1. 유일한 식별자에 의해 식별 가능
2. 해당 업무에 필요하고 관리하고자 하는 정보
3. 인스턴스들의 집합 (2개 이상의 인스턴스의 집합)
4. 엔터티는 반드시 속성 보유 (각 엔터티는 2개 이상의 속성 보유)
5. 엔터티는 업무 프로세스에 의해 이용
6. 다른 엔터티와 최소 1개 이상의 관계 성립

엔터티의 분류
1) 유형과 무형에 따라
1. 유형 엔터티 : 물리적 형태가 존재 EX) 사원, 물품 등
2. 개념 엔터티 : 물리적 형태가 없음 EX) 조직, 보험상품 등
3. 사건 엔터티 : 업무 수행에 따라 발생하는 엔터티 EX) 주문, 청구 등

2) 발생 시점에 따라
1. 기본 엔터티 : 업무에 원래 존재하는 정보. 자신의 고유한 주 식별자 보유. EX) 사원,부서 등
2. 중심 엔터티 : 기본 엔터티로부터 발생, 업무의 중심적인 역할 EX) 계약,청구,주문 등
3. 행위 엔터티 : 2개 이상의 부모 엔터티로부터 발생. EX) 주문(고객과 상품) 등

ERD 표기법
주 식별자(PK) = 맨 위에 존재(IE 표기법), # (BARKER 표기법)

속성이란?
업무에서 필요로 하는 고유한 성질, 특징을 의미/ 컬럼으로 표현할 수 있는 단위. 더이상 분리되지 않는 최소의 데이터 단위
EX) 학생 엔터티의 이름,학번 등이 속성이 될 수 있음.

엔터티 - 인스턴스 - 속성 - 속성값의 관계
1. 한 개의 엔터티는 두 개 이상의 인스턴스의 집합 (인스턴스 = 행의 정보)
2. 한 개의 엔터티는 두 개 이상의 속성을 가짐. (속성 = 열의 정보)
3. 한 개의 속성은 1개의 속성값을 가짐. (한 개의 정보)

속성의 특징
1. 정해진 주식별자에 함수적 종속성을 가져야 함.
2. 하나의 속성은 한 개의 값만 가짐. (송성의 원자성)

함수적 종속성이란?
어떤 속성 A의 값에 의해 다른 속성 B도 유일하게 결정된다면, B는 A에 함수적으로 종속되었다 라고 표현. 수식으로는 A->B라고 표현함
1) 완전 함수적 종속
특정 컬럼이 기본키에 대해 완전히 종속 될 때.
PK를 구성하는 컬럼이 2개 이상일 경우 PK 값 모두에 의한 종속 관계를 나타낼 때 완전 함수적 종속.
2) 부분 함수적 종속
PK 일부에 대해 종속 될 때.
PK가 2개여도 1개에 따라서 결정된다면 부분 함수적 종속.

속성의 분류
1) 속성의 특성에 따라
1. 기본 속성 : 업무로 부터 추출된 모든 속성
2. 설계 속성 : 기본 속성 외에 업무를 규칙화하기 위해 새로 만들어지거나 변형해 만들어지는 속성 EX. OO 코드
3. 파생 속성 : 다른 속성에 의해 만들어지는 속성. 일반적으로 계산된 값이 해당
EX. 합계, 평균 등

2) 엔터티 구성 방식에 따라
1. PK(기본키) : 인스턴스를 식별할 수 있는 속성
2. FK(외래키) : 다른 엔터티와의 관계에서 포함된 속성
3. 일반속성 : 엔터티에 포함되어있고, PK/FK에 포함되지 않는 속성

3) 분해 여부에 따라
1. 단일 속성 : 하나의 의미로 구성 EX) 회원 ID, 이름 등
2. 복합 속성 : 여러 개의 의미로 구성 EX) 주소(시,구,동 등 분해 가능) 등
3. 다중값 속성 : 속성에 여러 개의 값을 가질 수 있는 경우 EX) 여러 개의 휴대폰 번호 보유

도메인
각 속성이 가질 수 있는 값의 범위, 타입과 크기 EX) 문자,숫자 1~100자

관계란?
엔터티 간의 연관성으 나타낸 개념

관계의 종류
1. 존재적 관계 : 하나의 엔터티의 존재가 다른 엔터티의 존재에 영향을 미치는 관계
EX) 사원과 부서 엔터티 간의 관계
2. 행위적 관계 : 엔터티 간의 어떤 행위가 있는 것을 의미
EX) 고객 엔터티의 행동에 따라 주문 엔터티 발생

관계의 구성
1. 관계명 (수강이력 등)
2. 차수 (일대다,일대일,다대다)
3. 선택성 (필수냐, 선택이냐)

관계의 차수
1) 1 대 1 관계
1-1. 완전 1대1 관계 : 하나의 엔터티에 관계되는 엔터티가 반드시 하나로 존재하는 경우
1-2. 선택적 1대1 관계 : 하나의 엔터티에 관계되는 엔터티가 하나이거나 없을 수도 있는 경우
2) 1 대 N 관계 : 엔터티에 하나의 행에 다른 엔터티의 값이 여러 개 있는 경우 EX) 한 명의 고객이 여러 개의 계좌 보유 가능
3) M 대 N 관계(다대다) : 두 엔터티가 다대다의 연결관계를 가짐.
이 경우, 조인 시 카테시안 곱 발생이 가능하므로 두 엔터티를 연결하는 연결 엔터티를 추가로 1대 N 관계를 해소해야함.
EX) 한 학생이 여러 강의를 수강할 수 있고, 한 강의 기준으로도 여러학생이 보유 할 수 있음 ->이 두 엔터티읭 연결 엔터티로 수강이력 엔터티가 필요

관계, 차수, 페어링의 차이

학생과 강의 엔터티는 관계를 가짐.
한 학생은 여러 강의를 수강할 수 있고, 한 강의도 여러 학생에게 수강될 수 있으므로 다대다 관계. 이때 차수는 M:N.
인스턴스 관계를 보면 '학생 A가 강의 B를 언제 수강했고 성적은 O이다. 와 같은 페어링ㅇ이 형성.
관계의 차수는 하나의 엔터티와 다른 엔터티 간 레코드 연결방식을 나타냄(1대1,다대다), 페어링은 두 엔터티 간 특정 연결을 설명하고 추가 정보를 제공하는 용도로 사용.

식별자란?
하나의 엔터티에 구성된 속성 중 엔터티를 대표할 수 있는 속성.
하나의 유일한 식별자가 존재.
식별자는 논리 모델링의 용어, 물리 모델링에서는 키라고 표현

주식별자의 특징
1. 유일성 : 주식별자에 의해 모든 인스턴스를 유일하게 구분함.
2. 최소성 : 주식별자를 구성하는 속성은 유일성을 만족하는 최소한의 속성으로 구성
3. 불변성 : 주식별자가 엔터티에 지정되면 식별자의 값은 변화하지 않음 (고유값)
4. 존재성 : 주식별자가 지정되면 반드시 값이 존재해야함.

식별자의 분류
1) 대표성 여부에 따라
1. 주식별자 : 유일성과 최소성을 만족하며 엔터티를 대표하는 식별자 / 엔터티 내에서 각 인스턴스를 유일하게 구분할 수 있음 / 타 엔터티와 참조관계를 연결 가능
2. 보조식별자 : 대표성을 가지지 못해 참조관계 연결 불가능 / 유일성과 최소성은 만족하나 대표성을 만족하지 못하는 식별자
2) 생성 여부에 따라
1. 내부식별자 : 엔터티 내부에서 스스로 생성되는 식별자
2. 외부식별자 : 다른 엔터티와의 관계로 인해 만들어지는 식별자 (외래키,FK)
3) 속성 수에 따라
1. 단일 식별자 : 하나의 속성으로 구성
2. 복합 식별자 : 두개 이상의 속성으로 구성
4) 대체 여부에 따라
1. 본질식별자(원조식별자) : 비즈니스 프로세스에서 만들어지는 식별자
2. 인조식별자 : 인위적으로 만들어지는 식별자 / 자동으로 증가하는 일련번호 같은 형태

주식별자의 도출 기준
1. 해당 업무에서 자주 이용되는 속성을 지정한다.

같은 조건을 만족하더라도 더 많이 사용되는 속성으로 지정.

명칭이나 내역 등과 같은 이름은 피한다.

이름을 주식별자로 사용하는 행위를 피함

속성의 수를 최대한 적게 구성

너무 많은 속성으로 구성 시, 조인으로 인한 성능저하 발생 우려.

관계간 엔터티 구분
1) 강한 개체 : 독립적으로 존재할 수 있는 엔터티. 고객과 계좌 엔터티 중 고객은 강한 개체.
2) 약한 개체 : 독립적으로 존재할 수 없는 엔터티. 고객과 계좌 엔터티 중 계좌는 약한 개체.

식별 관계와 비식별 관계
1) 식별관계 : 하나의 엔터티의 기본키를 다른 엔터티가 기본키의 하나로 공유하는 관계. ERD에서 실선으로 표시.
EX) 사원과 교육이력 엔터티에서 양쪽 모두 사원번호를 기본키로 가진 식별관계.
2) 비식별관계 : 강한 개체의 기본키를 다른 엔터티의 일반속성으로 관계를 갖는 것. ERD에서 점선으로 표시
EX) 부서와 사원 엔터티에서 부서번호는 사원엔터티에서 일반 속성

Key의 종류 (논리 모델링에서의 식별자가 물리 모델링에서는 key라고 불림)
1. 기본키 : 엔터티를 대표할 수 있는 키
2. 후보키 : 유일성과 최소성을 만족하는 키. 이 중 하나가 기본키가 되고, 나머지는 대체키라고 부름.
3. 슈퍼키 : 유일성은 만족하나 최소성은 만족하지 않는 키. EX) 학생 테이블의 PK를 학번만이 아니라 (학번+이름)으로 구성한 것은 슈퍼키
4. 대체키 : 여러 후보키 중 기본키가 아닌 키
5. 외래키 : 다른 테이블의 기본키를 참조하는 키

정규화란?
최소한의 데이터만을 하나의 엔터티에 넣는 식으로 데이터를 분해하는 과정
데이터의 중복을 제거하고 데이터 모델의 독립성을 확보.
논리 데이터 모델링 수행 시점에서 고려 (엔터티를 상세화하는 과정)
1~5 정규화 중 실질적으로 제 3정규화까지만 수행.
EX) 학생 엔터티에 넣을 데이터를 분류하는 것. 학생 엔터티에 지도교사는 필요 X.
이상현상을 줄이기 위한 설계 기법
이상현상?
정규화를 하지 않아 발생하는 현상(삽입이상, 갱신이상, 삭제이상)

정규화의 결과
데이터 입력,수정,삭제 성능 ▲
데이터의 조회 성능 하락 가능성 ▲(JOIN의 횟수가 증가함에 따라)

정규화 단계
1. 제 1 정규화 (1NF)
테이블의 컬럼이 원자성(한 속성이 하나의 값을 갖는 특성) 을 갖도록 테이블을 분해하는 단계

유사한 속성이 반복될 때에도 분리. EX)1개월 수량,2개월 수량 = 수량

하나의 행과 컬럼의 값은 반드시 하나의 값만 입력되도록 행을 분리.
EX. 철수의 구매 물품이 샴푸와 린스여도 각각의 행으로 나누어 주는 것.
2. 제 2 정규화 (2NF)
제 1 정규화를 진행한 테이블에 대해 완전 함수 종속을 만들도록 테이블을 분해하는 단계
완전함수종속이란, 기본키를 구성하는 모든 컬럼의 값이 다른 컬럼을 결정짓는 상태
PK가 두개 이상일 때 발생하며 PK의 일부와 종속되는 관계가 있다면 분리
EX. 기본키 (학생번호+강의명) 중 강의명 하나로만 강의실이 결정될 경우, 완전 함수 종속 위배.
3. 제 3 정규화 (3NF)
제 2 정규화를 진행한 테이블에 대해 이행적 종속을 없애도록 테이블을 분리하는 단계
이행적종속이란, A->B,B->C의 관계가 성립할 때, A->C가 성립되는 것을 말함.
(A,B)와 (B,C)로 분리하는 것.
EX. 고객번호(A)에 의해 상품명(B)이 결정, 상품명(B)에 의해 가격(C)이 결정 될 때, 고객번호(A)에 의해서도 구매가격(C)이 결정됨.
따라서 (고객번호,상품명),(상품명,가격)으로 분리하는 것이 제 3 정규화
4NF의 목적 : 다중값 종속 (MVD) 제거
5NF의 목적 : 조인 종속 제거

조인이란?
두 테이블의 데이터를 동시에 출력하거나 관계가 있는 테이블을 참조하기 위해 데이터를 연결하는 과정.
계층형 데이터 모델 :자기 자신끼리 관계가 발생. 하나의 엔터티 내의 인스턴스끼리 계층 구조를 가지는 경우.(셀프 조인)
EX. 사원 테이블에 사원번호,선임 번호가 함께 존재할 때.

트랜잭션이란?
데이터 베이스 작업의 최소 실행 단위 / 하나의 연속적인 업무 단위.
EX. 100 만원 이체 시, -100과 +100이 동시에 이루어져야함. 모두 실패하거나, 성공하거나.

트랜잭션의 특징 (ACID)
1. 원자성(Atomicity) : 하나의 트랜잭션으로 묶인 연산은 ALL or NOTHING으로 실행된다.
2. 일관성(Consistency) : 트랜잭션 이전에 데이터베이스에 오류가 없다면 이후에도 오류가 없다.
3. 고립성(Isolation) : 독립적으로 수행되며, 다른 트랜잭션이 영향을 미치지 않는다.
4. 영속성(Durability) : 결과는 데이터베이스에 영구적으로 저장되어 유지된다.

트랜잭션 명령어
1. COMMIT : INSERT, UPDATE, DELETE와 같은 DML 명령들을 통한 변경사항을 데이터베이스에 영구적으로 반영하고 락을 해제하여 트랜잭션을 완료. 최종적으로 데이터베이스에 반영
2. ROLLBACK : 트랜잭션에 포함된 변경사항 또는 세이브포인트 이후의 변경 사항을 취소하고 되돌림.
3. SAVEPOINT : 롤백을 하기 위한 저장점을 지정.

SQL SERVER는 자동 커밋모드가 ON이어서 롤백 불가능. 단, BEGIN TRANSACTION에 의한 명시적 트랜잭션은 롤백 가능

필수적, 선택적 관계와 ERD
IE 표기법 : 필수적 관계 = 동그라미X / 선택적 관계 = 동그라미 표기
바커 표기법 : 필수적 관계 = 실선 / 선택적 관계 = 점선

NUUL 이란?
DBMS에서 값이 아직 정해지지 않았을 때.
NULL의 특성
1. NULL을 포함한 사칙연산 결과는 항상 NULL. (NULL은 0이 아니다)
-> 보완하려면? NULL을 치환해야함. "EX. NVL(컬럼,값), 컬럼이 NULL이면 값으로 치환"
2. 그룹 함수는 NULL을 제외하고 연산을 수행

그룹함수(SUM,AVG,MIN,MAX)은 항상 NULL을 무시하고 연산
COUNT()는 NULL 상관 없이 모든 행의 수 리턴
주의할 점!
AVG의 경우 NULL도 포함해서 계산해야하는 경우가 있음.
**이런 경우 SUM(컬럼)/COUNT() = 합계/총 컬럼 수**

NULL은 JOIN 시에 결합 안됨.
EX. A.COUNT=B.COUNT 시 A.COUNT와 B.COUNT의 NULL값은 서로 결합되는 것이 아니고 없는 취급.
NULL 집합(NULL만 모인)에 대한 COUNT 결과는 0 / COUNT를 제외한 집계함수(SUM,AVG) 결과는 NULL
- 공집합(WHERE절 조건에 만족하는 행이 없는 경우)의 집계함수 결과도 위와 동일
NULL그룹 도 GROUP BY 결과로 출력됨 / HAVING 결과에 만족하는 그룹이 없으면 공집합 리턴.
EX) 고객 2명의 번호가 NULL이면 구매 금액은 두 개의 합으로 그룹화

WHERE는 데이터가 없어도 1건으로 요약해주지만 HAVING은 그룹이기에 안보여줌. (집계함수)

NULL의 ERD 표기법
IE 표기법 : 알 수 없음.
바커 표기법 : 속성 앞에 동그라미가 있으면 NULL을 허용하는 속성

식별자 구분 (대체 여부에 따라)
1. 본질 식별자

업무에 의해 만들어지는 필수 식별자

인조 식별자

인위적으로 만들어지는 식별자 (필수는 아니지만 편의를 위해 생성)
EX. 쇼핑몰의 주문상세번호
인조식별자의 단점
1) 중복 데이터 발생 가능 (데이터 품질 저하)
2) 불필요한 인덱스 생성 (저장공간 낭비 및 DML 성능 저하)

관계형 데이터베이스 구성 요소

계정 : 접근 제한을 위한 계정
테이블 : DBMS의 DB 안에서 데이터가 저장되는 형식
스키마 : 테이블의 구성 및 정보에 대한 기본적인 구조

테이블이란?
행과 열의 구조를 갖는 데이터 저장의 최소 단위.
컬럼은 속성(물리 모델링)이라고도 부름
테이블의 특징
1. 하나의 테이블은 반드시 한명의 소유 (여러 유저가 볼 수는 있으나, 소유할 순 없음)
2. 테이블간 관계는 1:1,1:N,M:N의 관계를 가질 수 있음
3. 테이블명은 중복될 수 없으나, 소유자가 다를 경우 같은 이름으로 생성 가능
4. 행 단위(가로)로 데이터가 입력, 삭제됨/ 수정은 특정 컬럼만 수정 가능

SQL이란?

관계형 데이터베이스에서 데이터 조회 및 조작 등을 명령하는 언어.
관계형 데이터베이스의 특징

데이터의 분류, 정렬, 탐색 속도가 빠름
신뢰성이 높고, 데이터 무결성 보장 (오류 가능성 낮음)
기존의 작성된 스키마 수정이 어려움 (엑셀 같은 경우는 열 추가가 쉬움)
데이터베이스의 부하를 분석하기 어려움

데이터 무결성이란?

데이터의 정확성과 일관성을 유지하고, 데이터에 결손이 없는 것.
데이터 무결성의 종류

개체 무결성 : 테이블의 PK를 구성하는 컬럼은 NULL이나 중복값을 가질 수 없음.
참조 무결성 : FK의 값은 NULL이거나 참조 테이블의 PK 값과 동일해야 함.
도메인 무결성 : 속성값은 정해진 범위 내에 속한 값이어야함.
NULL 무결성 : null을 허용하지 않는 속성이라면 null 허용하지 않음.
고유 무결성 : 고유성, 고유키는 중복되지 않음.
키 무결성 : 하나의 관계에 대해서는 적어도 하나의 키가 존재해야함.

ERD

테이블 간 상관관계를 그림으로 표현한 것. (엔터티,관계,속성으로 구성)

SQL의 종류 (기능에 따라)
1) DDL(Definition,정의) : 생성,변경,삭제 등 구조 자체를 변경.
2) DML(Manupulation,조작) : 입력,삭제,수정,변환 등 데이터를 변경
3) DCL(Contrl,제어) : 데이터를 제어, 권한을 부여하고 회수함
4) TCL(Contrl,제어) : 트랜잭션을 제어, 커밋,롤백.
5) DQL(Query) : SELECT문

SELECT문 구조
SELECT - 최종적으로 무엇을 볼 건지?
FROM - 어느 테이블에서 데이터를 가져올 건지?
WHERE - 어떤 조건을 줄 건지?
GROUP BY - 그룹 지을 건지?
HAVING - 그룹 속에서 어떤 조건?
ORDER BY - 어떻게 나열할 건지?

해석 순서 : FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

1. SELECT 절

불러올 컬럼명, 연산 결과를 작성하는 절
별표와 컬럼명 동시 사용 불가
Alias(별명 지정 가능) / SELECT 컬럼명 AS 별명
별명은 SELECT 절보다 늦게 수행되는 ORDER BY 절에서만 사용 가능.
별명에 공백이 포함 OR 특수문자 포함'_제외' OR 대소문자 표현 = " " 필요

2. FROM 절

데이터를 불러올 테이블 명을 작성하는 절
테이블 여러 개 전달 가능 (JOIN)

함수란?
INPUT과 OUTPUT의 관계를 정의한 객체.
FROM 절을 제외한 모든 절에서 사용가능

함수의 종류 (입력값의 수에 따라)
1) 단일행 함수 : 1개의 인풋 > 1개의 아웃풋
2) 복수행 함수 : 여러 개의 인풋 > 1개의 아웃풋 EX. SUM,AVG,MIN,MAX 등

함수의 종류 (입/출력값의 타입에 따라)
1) 문자형 함수 : 문자열 결합,추출,삭제,치환 등 / 단일행 함수 형태

TRANSLATE 함수는 매칭되는 글자끼리 1:1 치환.

2) 숫자형 함수 : 숫자 값을 반환 / 단일행 함수 형태

3) 날짜형 함수 : 날짜와 관련된 함수

4) 변환 함수 : 값의 데이터 타입을 변환 / 문자를 숫자로, 숫자를 문자로, 날짜를 문자로 변경

5) 그룹 함수 : 다중행 함수 / 여러 개의 아웃풋이 하나의 인풋으로 도출 /GROUP BY와 함께 자주 사용

6) 일반 함수 : 기타함수 (널 치환 함수 등)

3. WHERE 절

원하는 조건에 맞는 데이터만 조회하고 싶을 때 사용
여러 조건 동시에 전달 가능 (AND, OR 사용)
NULL 조회시 IS NULL/IS NOT NULL 사용. / = 사용 불가
문자나 날짜 상수 표현 시 홑따옴표 사용 (다른 절에서도 동일)
SUM,AVG 등의 그룹 함수 사용 불가
EX) 셀 중에 남자 셀만 보여줘

4. GROUP BY 절

행을 특정 조건에 따라 그룹으로 분리하여 계산하는 구문식.
제외할 대상이 있다면 WHERE 절에서 해당 행을 제외. EX) 10번 부서는 빼줘
GROUP BY는 요약되기에 이외의 컬럼은 그룹 함수를 사용하지 않으면 표현X.

5. HAVING 절

그룹 함수 결과를 조건으로 사용할 때 사용하는 절.
SUM,AVG 등의 그룹 함수 사용 가능
EX) 그룹 중 2개 이상의 주문을 한 그룹만 보여줘

6. ORDER BY 절

출력되는 행의 순서 변경 (정렬)
오름차순 1,2,3...(디폴트값),내림차순 3,2,1...(DESC)

JOIN 절

여러 테이블의 데이터를 사용해 동시 출력하거나 참조할 경우
FROM 절에 조인할 테이블 나열

JOIN의 종류 (조건의 형태에 따라)
1. EQUI JOIN : JOIN 조건이 동등 조건인 경우 / 조건이 '=' 일 때
2. NON EQUI JOIN :JOIN 조건이 동등 조건인 경우 / 조건이 '>',"BETWEEN' 등 일 때
JOIN의 종류 2 (조인 결과에 따라)
1. INNER JOIN: JOIN 조건에 성립하는 데이터만 출력하는 경우

ANSI 표준의 경우 FROM절에 INNER JOIN 혹은 JOIN을 명시/ USING이나 ON 조건절 필수.
ANSI 표준은 ON 조건절에서 조인 조건 명시, WHERE절에서는 일반 조건 명시
USING 조건절은 조인할 컬럼명이 같은 경우 사용, 괄호 필수
2. OUTER JOIN : JOIN 조건에 성립하지 않는 데이터도 출력하는 경우
(LEFT/RIGHT/FULL 로 나뉨)
LEFT = 왼쪽 테이블 기준으로 오른쪽 테이블 데이터를 채우는 방식 (왼쪽 테이블의 데이터는 생략하지 말아줘) / RIGHT는 반대로
우측 값에서 같은 값이 없을 경우 NULL로 출력 / RIGHT는 반대로
FULL = 양쪽 테이블 모두 값을 보여주되, 연결되지 않는건 NULL 표시
3. NATURAL JOIN : 조인 조건 생략시 두 테이블에 같은 이름으로 자연 연결되는 조인
동일한 이름을 갖는 모든 컬럼끼리 조인
조인 조건 정의 X
같은 이름으로 돼있는 모든 컬럼의 값이 같을 때 조인 됨
EX) order_id 와 address 컬럼이 두 테이블에 존재 할 경우 두 컬럼값이 모두 동일한 행만 추출 !
4. CROSS JOIN : 조인 조건 생략시 두 테이블의 발생 가능한 모든 행을 출력하는 조인
양쪽 테이블 행의 수를 곱한 수의 데이터가 발생 (MxN)
5. SELF JOIN : 하나의 테이블을 두 번이상 참조해서 연결하는 조인 / 같은 테이블끼리 조인해야 하기에 별명 붙이기 필수

서브쿼리

쿼리 안에 있는 쿼리
SELECT 문 중 SELECT,FROM,WHERE,HAVING 절에서 사용 가능(GROUP BY, ORDER BY 불가능)
한 행만 읽었을 때 가져올 수 없는 결과를 더 가져오기 위해서 사용.
EX. 사원 중 급여가 평균 이상인 사원을 뽑고 싶다? -> 급여 평균을 먼저 구해야하기에 서브쿼리 사용.

서브쿼리의 분류 (위치에 따라)
스칼라 서브쿼리 : SELECT문 칼럼 입력 위치
인라인 뷰 : FROM절의 테이블 입력 위치
중첩 서브쿼리 : WHERE절, HAVING절의 칼럼 또는 테이블 입력 위치.
->
1. 단일 행 서브쿼리

서브쿼리 결과가 하나의 행이기에 단순비교연산자(=,>,< 등) 사용 가능
행이 하나 ! 예를 들면 사원의 급여 평균, 합

다중 행 서브쿼리

결과가 둘 이상의 행이기에 단순비교 사용 불가. IN, ANY, ALL 등의 연산자 사용 가능 (ALL은 최대, ANY는 최소, EXISTS는 하나라도 존재, EXIST는 앞에 컬럼 X)
EX) >=ANY(30,40,50) = 30보다 크거나 같아야 함
```
>=ALL(30,40,50) = 50보다 크거나 같아야 함
>=IN(30,40,50) = 30,40,50 중 하나라도 있어야 함.
```
행이 두 개 이상! 예를 들면 급여가 1000 이하인 행의 부서 번호들

다중 컬럼 서브쿼리

컬럼이 두 개 이상! 예를 들면 직업 별 최대 급여

서브쿼리의 분류 (메인쿼리 칼럼 사용 여부에 따라)
연관 서브쿼리 : 메인쿼리의 칼럼을 서브쿼리에서 사용
비연관 서브쿼리 : 메인쿼리의 칼럼을 서브쿼리에서 사용하지 않음

SELECT 절에서의 서브쿼리 목적 : 계산된 값을 표현하기 위해.
EX)
SELECT 이름,
(SELECT COUNT(*)
FROM 프로젝트참여
WHERE 프로젝트참여.직원ID = 직원.직원ID) AS 참여프로젝트수
FROM 직원;
설명: 각 직원이 참여한 프로젝트 수를 함께 보여줌

FROM 절에서의 서브쿼리 목적 : 서브쿼리 결과를 하나의 테이블로 이용하기 위해.

SELECT 부서명, 평균급여
FROM (
SELECT 부서ID, AVG(급여) AS 평균급여
FROM 직원
GROUP BY 부서ID
) AS 부서별급여
JOIN 부서 ON 부서.부서ID = 부서별급여.부서ID;
설명: 부서별 평균 급여를 먼저 구한 뒤, 부서명과 함께 출력

WHERE 절에서의 서브쿼리 목적 : 조건에 맞는 데이터를 찾기위해.

SELECT 이름
FROM 직원
WHERE 부서ID = (
SELECT 부서ID
FROM 부서
WHERE 부서명 = '영업부'
);
설명: ‘영업부’의 부서ID를 서브쿼리로 먼저 찾고, 그 부서에 속한 직원들의 이름을 가져옴

그룹 함수

GROUP BY절의 연산결과에 대해 그룹 별로 연산을 수행하는 함수
집계함수(SUM),ROLLUP,CUBE 등

ROLL UP 함수

GROUP BY절에 들어가는 칼럼을 대상으로 하위 그룹핑 수행
그룹별 소계 & 총계
EX) (A,B) 중 A와 B의 조합, A의 조합, 총계를 그룹핑
CUBE 함수
조합 가능한 모든 경우로 그룹핑 수행
EX) (A,B) 중 A와 B의 조합, A의 조합 ,B의 조합, 전체 총곌르 그룹핑
GROUPING SETS
그룹핑 대상을 지정하는 함수
EX) ((A),(B)) A끼리만 그룹핑, B끼리만 그룹핑
GROUPING
그룹함수와 같이 사용하여 소계에 해당하는 결과 행과 그렇지 않은 부분을 구분
EX) CASE GROUPING(A)
WHEN 1 THEN '총계' ELSE TO_CHAR(A)
END
-> A를 그룹핑 한것 중 결과 행의 경우 '총계'로 표현하고 아니면 문자열로 표현. 문자열로 표현하는 이유는 총계라는 문자가 들어갈 수 있게 하기 위해

윈도우 함수 (순위함수, 집계함수)

하나의 컬럼 내에서 각 행에 대해 연산을 수행하지만, GROUP BY 연산과는 달리 행의 기존 순서를 유지한 상태로 해당 행에 새로운 값을 추가

순위 함수 (등수 매기는 함수)
1) RANK

동일 순위는 같은 순위값을 가짐. 순위값은 앞 순위까지의 누적 개수 +1.
EX) 1등 -> 2등 -> 2등 -> 4등
2) DENSE_RANK
동일 순위는 같은 순위값을 가짐. 순위값은 단순하게 앞 순위 +1
EX) 1등 -> 2등 -> 2등 -> 3등
3) ROW_NUMBER
동일 순위라도 각각의 행이 고유의 순위값을 가짐. 중복 X.
EX) 1등 -> 2등 -> 3등

RANK 함수 예제
SELECT a , COUNT(),**RANK() OVER(ORDER BY COUNT() DESC)** AS RANK -> a별로 그룹핑을 해서 a별 갯수 순위를 내림차 순으로 매김.
FROM A TABLE
GROUP BY a -> 1번으로 실행

DENSE_RANK, ROW_NUMBER의 경우도 위와 같으나 순위값만 다름.

2. 집계 함수
COUNT(*) OVER(PARTITION BY a) -> a별로 그룹핑한 후 파티션별 개수로 나눔.
a가 1인 것이 4개면 각각 4,4,4,4로 표시

TOP N 쿼리 (N-숫자)
1. ROWNUM (행 번호)

저장된 데이터를 그대로 두면서 행에 순차적인 번호 부여.
ROWNUM은 1부터 시작하기에 WHERE ROWNUM = 5 -> 아무것도 출력 X. >=로 표현해야 1~5까지 출력.
ORDER BY를 수행한 후에 ROWNUM 조건을 달고 싶으면 ORDER BY절을 서브쿼리로 해야됨. ORDER BY는 맨 마지막에 수행되기 때문 !

순위 함수 주의점 ! RANK, DENSE_RANK, ROW_NUMBER 등에 따라 >=5를 해도 나오는 결과값이 달라질 수 있음 주의

계층형 질의 = 트리 구조
START WITH -> 조건 (루트 노드 / 뿌리)
CONNECT BY PRIOR 부모컬럼 = 자식컬럼 -> 연결 관계 (프자부순/ PRIOR 자식->부모 = 순방향전개 - 반대로 될 경우 프부자역(방향))
ORDER SIBLINGS BY -> 정렬기준컬럼; 같은 레벨끼리 먼저 순차적으로 하는 것이 아니고 부모->자식 순방향OR 역방향 전개.
!ORDER BY vs ORDER SIBLINGS BY
ORDER BY = 전체 결과를 단순 정렬함 (계층 구조와는 무관하게 정렬)
ORDER SIBLINGS BY = 각 계층 내에서 같은 부모를 가진 노드들끼리만 정렬

계층형 질의 풀이 ! 데이터를 보고 트리 구조 그려보기 -> 시작노드 잡기 -> 순방향,역방향 파악

PIVOT, UNPIVOT
PIVOT : 행을 열로 바꿈 / 보고서 작성에 용이
PIVOT 절

PIVOT (COUNT(*) FOR DNAME IN ('A' 컬럼 AS aa, 'B' 컬럼 AS bb ...))

UNPIVOT : 열을 행으로 바꿈 / 통계치 구하기에 용이
UNPIVOT 절
UNPIVOT (~ FOR ~ IN (aa AS 'A' 컬럼, bb AS 'B' 컬럼 ...))

정규표현식

문자열을 다룰 때 사용하는 만능식
메타문자
문자가 가진 의미가 아니라 다른 의미로 사용되는 문자 (EX. '^' 입력 문자열의 시작을 의미)
주요 메타문자
. : 임의의 한 문자 EX) a.b acb,a-b
? : 선행 문자 0 또는 1개 EX) no? n,no

: 선행 문자 0개 이상 EX) no* n,no,noo,nooo...

: 선행 문자 1개 이상 EX) no+ no,noo,nooo...
ㅣ : 선택적 일치 EX) aㅣb a,b
() : 한 단위로 취급 EX) (ab) ab
% : 0개 이상의 문자 EX) %a% a가 포함된 문자열 모두
_ : 임의의 한 문자 EX) a_b acb,a-b,a1b ...
[ ] : 대괄호 안의 문자 중 하나와 일치 EX) [abc] a, b, c
[-] : 연속 문자의 범위 지정 EX) [a-z] a부터 z까지
[^] : 대괄호 안 문자를 제외한 나머지 중 하나와 일치 EX) [^a-z] a-z를 제외한 나머지 문자

리터럴문자

문자 그 자체가 가진 의미 그대로 사용되는 문자. 패턴 매칭 수행의 최소단위 (알파벳 A,한글 가)

정규표현식 함수 (REGEXP_~)
REGEXP_LIKE : LIKE 연산
REGEXP_REPLACE : 문자열 대체
REGEXP_INSTR : 문자열 검색 후 위치 반환
REGEXP_SUBSTR : 부분 문자열 반환
REGEXP_COUNT : 특정 패턴의 문자열 개수 반환

참조 무결성 제약조건

데이터 입력, 수정, 삭제 과정에서 데이터의 일관성이 깨지는 것을 DBMS 차원에서 방지하기 위한 조건.
참조 대상이 되는 쪽이 부모, 참조하는 쪽을 자식이라 가정.

DELETE

CASCADE : 부모의 값 삭제 시 자식의 값도 같이 삭제.
SET NULL : 부모의 값 삭제 시 자식의 해당 칼럼은 NULL.
SET DEFAULT : 부모의 값 삭제 시 해당 칼럼은 기본값.
RESTRICT : 자식 테이블에 해당 데이터가 PK로 지정되지 않은 경우에만 부모 테이블에서 삭제 및 수정이 가능. (비식별자 관계)
NO ACTION : 제약 조건을 위배한 경우 동작이 실패하며 아무런 액션 X.

INSERT

AUTOMATIC : 부모 테이블에 PK가 없는 경우 PK 생성 후 자식 테이블에 값 입력.
SET NULL : 부모 테이블에 PK가 없는 경우 자식 테이블에 NULL 값.
SET DEFAULT : 부모 테이블에 PK가 없는 경우 기본값 입력.
DEPENDENT : 부모 테이블에 PK가 존재할 때만 자식 테이블에 값 입력을 허용.
NO ACTION : 제약 조건을 위배한 경우 동작이 실패하며 아무런 액션 X.

DDL 코드

문제 풀이.

교차 엔터티?
다대다 관계를 해소하기 위해 중간에 만들어진 엔터티.
다대다 관계는 논리적 모델링 단계에서만 생기고, 물리적 모델링에서는 교차엔터티를 만들어서 다대다 관계를 해소함.

성능 데이터 모델링?
성능을 고려한 데이터 모델링. (SQL 튜닝 X)

인덱스 최적화 순위
1. =이 있는 조건 -> BETWEEN 조건 -> WHERE 절 이외

반정규화 기법

파티셔닝: 여러개의 테이블로 나눔. (주문 테이블을 월별로 분리)
로우체이닝 : 두개 이상의 블록에 걸쳐 저장.
클러스터링 : 여러 데이터베이스 서버를 하나로 묶어서 사용
샤딩 :동일한 스키마를 가진 여러 대의 데이터베이스에 데이털르 분산 저장. (파티셔닝은 동일한 데이터 베이스에 저장, 샤딩은 네트워크에 분산 저장

슈퍼타입, 서브타입

ONE TO ONE : 테이블을 각각 개별 테이블로 구성 = 조인 성능 우수 X.
PULS TYPE : 슈퍼타입 + 서브타입 테이블로 구성 (각각의 서브타입에 슈퍼타입을 합함)
SINGLE TYPE : 전체를 하나의 테이블로 통합 = 조인 성능 우수.

인덱스 생성?

인덱스 : 각 속성을 조회할 때 성능 향상을 위한 것.

성능을 고려한 모델링 순서
정규화 수행 -> 용량 산정 -> 트랜잭션 유형 분석 -> 반정규화 진행 -> 조정 (PK,FK,수퍼타입,서브타입) -> 데이터 모델 검증 및 확인

인조식별자 = 개발의 편의성 향상

DDL, DML, DCL, TCL 암기 !

NULLS FIRST : 정렬시 널이 가장 먼저.
NULLS LAST : 가장 마지막

연산자의 우선 순위
산술 -> 연결 -> 비교 -> NULL,LIKE,IN -> BETWEEN -> NOT -> AND -> OR

ORDER BY의 (CASE WHEN 은 임의의 컬럼을 생성하는 것)

날짜 형식
DD = 01~ 31
D = 일(1) ~ 토(7)
HH or HH12 = 01시~12시
HH24 = 00~23시

서브쿼리
서브쿼리에서는 메인쿼리의 칼럼을 모두 사용할 수 있으나, 메인쿼리에서는 서브쿼리의 칼럼 사용 불가능(FROM절에서는 가능).
서브쿼리에서는 ORDER BY절 사용 불가능.
서브쿼리 가능 한 곳 = SELECT,FROM,WHERE,HAVING,ORDER BY,INSERT문의 VALUE,UPDATE문의 SET
단일행 비교연산자와 사용될 경우 서브쿼리의 결과는 1건이하, 2건 이상의 결과를 반환하는 경우 다중행 비교연산자와(IN,EXIST,ANY,ALL...) 함께 사용.

NTIL(?) = 전체 행을 ?등분으로 나눔.

뷰의 특성 : 편리성, 보안성, 독립성

WHERE NOT EXISTS (서브쿼리) = 서브쿼리의 조건에 부합하지 않는 것을 뽑아냄!

ROWNUM = 각행에 1부터 순차적으로 번호를 부여하는 것. FALSE가 나오면 중단됨.
EX) WHERE ROWNUM <=10 10번까지만 출력/ ROWNUM>=10 1부터 시작하므로 출력 X.

계층형쿼리 ★

UNION = 중복 허용 X. 정렬 수행
UNION ALL = 중복 허용
MINUS = 공통 컬럼을 삭제하고 기존의 중복도 제거
같은 테이블 대상으로 진행 ▲

FROM 절에 여러 테이블을 나열하면 CROSS JOIN 적용
EX) TABLE A, TABLE B (단 뒤에 조건이 명시된 경우 INNER JOIN)
/ = 은 INNER JOIN

GROUP BY CUBE(A, B) = (A),(B)/(A,B)/ 전체

ROLL UP, CUBE, GROUPING SETS 중요!

행 순서함수

FIRST_VALUE : 파티션별로 그룹핑해 가장 처음 값 반환
LAST_VALUE : 파티션별로 그룹핑해 가장 마지막 값 반환
LAG : 입력된 인자의 값만큼 이전 행의 값 반환
LEAD : 입력된 인자의 값만큼 이후 행의 값 반환

SELECT DISTINCT A, B = A와 B 함께 DISTINCT 적용
EX) 1,A / 1,A / 1,B / 1,B 가 있을 경우 1,A와 1,B가 남음

MERGE 함수
MERGE INTO 대상테이블 A
USING 원본테이블 B
ON (조인 조건)
WHEN MATCHED THEN
UPDATE SET ...
WHEN NOT MATCHED THEN
INSERT (...) VALUES (...);

MERGE INTO 변경 대상이 되는 테이블
USING 비교할 기준이 되는 테이블/서브쿼리
ON 매칭 조건 (PK 또는 JOIN 조건)
WHEN MATCHED 조건이 맞는 경우 → UPDATE or DELETE
WHEN NOT MATCHED 조건이 맞지 않는 경우 → INSERT

참조 무결성 제약조건 (삽입시/변경,삭제시)
AUTOMATIC (CASCADE) : 부모테이블에 PK가 없는 경우 PK 생성 후 자식테이블에 값이 자동 입력
SET NULL : PK가 없는 경우 자식 테이블에 NULL 값 입력
SET DEFAULT : PK가 없는 경우 자식 테이블에 기본값 입력
DEPENDENT : PK가 존재할 때만 자식 테이블에 값 입력 허용
NO ACTION (RESTRICT) : 동작이 실패

문자열 길이
CHAR = 고정길이
VARCHAR = 가변길이

CREATE문 작성시 주의점

테이블 이름은 고유성
칼럼의 이름과 데이터 타입은 필수 지정
NULL/NOT NULL 옵션 생략시 NULL이 디폴트값.
이름은 숫자로 시작될 수 없음.

CREATE 구문에서 UNIQUE = NULL 허용
EX) C3 NUMBER UNIQUE = IS NULL

SUBSTR (A, B, C) = C가 음수나 0일 경우 NULL0

WINDOW 함수
MAX() OVER
UNBOUNDED PRECEDING AND 2 FOLLOWING = 제일 윗칸 부터 아래로 두개까지 총 3개 중 최대
SUM() OVER
BETWEEN 1 PRECEDING AND CURRENT ROW = 한칸 앞에서 부터 현재 칸까지의 합
FIRST VALUE() OVER
BETWEEN 200 PRECEDING AND 200 FOLLOWING = 현재 값에서 +- 200 중 첫 번째값

JOIN
INNER JOIN에서는 ON절이 들어감.
오라클에서는 USING절 사용 EX) USING(칼럼명) <-> ON은 조건
NATURAL JOIN은 조건절 필요 X. OWNER 표시 필요 X. EX)A.NAME

를 찾는 구문 : LIKE '%@%' ESCAPE '@' = _를 문자로 해석해라

★ SUM(A+B+C+D) 와 SUM(A)+SUM(B)+SUM(C)+SUM(D) 의 차이