🌰 [Oracle] SQL 노트정리

이 문서는 SQLD 시험 공부를 위한 노트정리 입니다.

시험 열심히 준비하자

제 51회 SQLD 자격증 취득 완료 (23.12)

SQL 이란?

데이터베이스에 쿼리를 실행해서 원하는 데이터 조회 및 입력, 수정 삭제 등을 할 수 있는 절차형 언어

데이터 모델링의 특징

추상화, 단순화, 명확성

데이터 모델링 단계

개념적 → 논리적 → 물리적

개념적 모델링

• 고객의 비즈니스 프로세스를 분석하고 업무 전체에 대해 데이터 모델링을 수행
• 전사적 관점에서 기업의 데이터를 모델링
• 복잡하지 않으며 중요한 것 위주, 업무측면에서 모델링
• 추상화 수준이 가장 높음
• 엔터티의 속성을 도출하고 ERD 작성

논리적 모델링

• 개념적 모델링을 논리적 모델링으로 반환하는 작업
• 특정 데이터베이스 모델에 종속
• 식별자를 도출하고 필요한 모든 릴레이션 정의
• 식별자를 정의하고 관계 속성등을 모두 표현
• 데이터 정규화를 수행해 모델의 독립성 확보
• 정규화 덕분에 재사용성이 높음

물리적 모델링

• 데이터베이스 실제 구축
• 테이블 인덱스 함수등을 생성
• 성능 보안 가용성을 고려하여 데이터베이스를 구축

데이터 모델링 관점

데이터(사용자적 관점), 프로세스 (작업자 관점), 데이터와 프로세스 (관계적 관점)

ERD 작성 절차

엔터티와 엔터티 간의 관계 정의하는 모델링 방법

1. 엔터티 도출 및 그림
2. 엔터티 배치 중요한건 왼쪽 상단
3. 엔터티 간 관계설정
4. 관계명 서술
5. 관계 참여도 표현 관계 참여도는 관계 수와 비례
6. 관계 필수 여부 표현

3층 스키마

사용자, 설계자, 개발자 가 데이터베이스를 보는 관점에 따라 기술 및 관계를 정의한 ANSI 표

데이터베이스의 독립성 확보

• 각 계층 = 뷰 (View)
• 데이터 복잡도는 낮아지고 중복 제거
• 사용자 요구에 따른 대응력 상승 비용 절감

3층 스키마의 독립성

• 논리적 독립성 → 개념 스키마가 변경되어도 외부 스키마 영향 X
• 물리적 독립성 → 내부 스키마가 변경되어도 개념 스키마가 영향 X

3층 스키마의 구조

• [내부 스키마] - [개념 스키마] - [다수의 외부 스키마]

외부 스키마

사용자 관점 스키마

• 관련 데이터베이스의 뷰 View 표시
• 업무상 관련이 있는 데이터에 접근
• 응용프로그램이 접근하는 데이터베이스 정의

개념 스키마

설계자 관점

• 사용자 전체집단의 데이터베이스 구조
• 통합 DB 구조
• 전체 데이터베이스 내의 규칙과 구조 표현
• 조직 전체적 관점

내부 스키마

개발자 관점

• 데이터베이스의 물리적 저장 구조
• 데이터 저장구조, 레코드, 필드 정의 인덱스 등을 의미

엔터티

업무에서 관리해야 하는 데이터 집합, 저장 및 관리 대상 데이터

• 식별자
• 인스턴스 집합 → 2개 이상의 인스턴스
• 속성 → 반드시 필요 2개 이상 (고객 : 회원 이름, 주소)
• 관계 → 다른 엔터티와는 최소 1개의 관계
• 업무 → 엔터티는 업무에서 관리되어야

엔터티의 종류 (유형과 무형)

• 유형 엔터티
  • 업무에서 도출 지속적으로 사용 (물리적 형태: 고객…)
• 개념 엔터티
  • 개념적으로 사용 (물리적 형태 X: 상품…)
• 사건 엔터티
  • 비즈니스 프로세스 실행하면서 생성 (주문…)

엔터티 종류 (발생 시점)

• 기본 엔터티 (키 엔터티)
  • 독립적 생성
  • 다른 엔터티로부터 영향 받지않음
  • 고객, 상품, 부서
• 중심 엔터티 (메인 엔터티)
  • 기본 엔터티(발생)와 행위 엔터티(생성)의 중간
  • 키 엔터티로부터 파생
• 행위 엔터티
  • 2개 이상의 엔터티로부터 발생, 정보의 추가
  • 데이터가 가장 많음
  • 주문이력, 결제내역

인스턴스

릴레이션이 가질 수 있는 값. 행의 수 릴레이션 + 기본키 (제약조건) → 테이블

속성

더 이상 분리되지 않는 단위 (성질, 본질)

• 업무에 필요한 데이터 저장 (집합)
• 인스턴스의 구성요소
• 엔터티가 가지는 항목
• 하나의 값만 가짐 cf. 유일한 값 X 중복값 O
• 주식별자에게 함수적 종속
• 기본키 변경시 속성의 값도 변경

속성의 종류 (분해여부)

• 단일 속성
  • 하나의 의미 (회원 ID, 이름)
• 복합 속성
  • 여러개의 의미 (주소 → 시 군 동 으로 분해)
• 다중 값 속성
  • 여러개의 값 → 엔터티로 분해 (상품 리스트)

속성의 종류 (특성)

• 기본 속성
  • 비즈니스 프로세스에서 도출되는 본래의 속성
  • ID, 이름, 계좌번호, 주문 일자
• 설계 속성
  • 데이터 모델링 과정에서 발생되는 속성
  • 유일한 값 부여 (상품코드, 지점 코드)
• 파생 속성
  • 다른 속성에 의해서 만들어지는 속성
  • 합계, 평균

도메인

속성이 가질 수 있는 값의 범위 (성별 : 남 여)…

선택도

특정 조건에 의해 선택될 것으로 예상되는 레코드의 비율

관계 (Relationship)

엔터티 간의 관련성 → 동사

1. 존재 관계
   • 두 개의 엔터티가 존재 여부의 관계
   • 엔터티 간의 상태
   • 직원은 관리점에 소속 된다
2. 행위 관계
   • 두 개의 앤터티가 어떤 행위에 의한 관련성이 있는지
   • 엔터티 간의 행위
   • 증권회사는 계좌를 개설한다

관계의 표기법

• 관계명
• 관계차수
• 선택성 (관계선택 사양)

관계 치수 (Relation Condinality)

두 개의 엔터티 간의 관계에 관여하는 수

최대 기수성

• 1 : 1 (일 대 일) → +————+
• 1 : M (일 대 다) → +————<
• M : N (다 대 다) → >————<
  • 다 대 다 관계의 조인
  • 카테시안 곱
  • 1 : N, N : 1 로 찢어서 해소 시켜주어야 함

최소 기수성

• 필수와 선택의 관계 (고객과 계좌)

  → +————o-

카디널리티 (Cadinality)

하나의 릴레이션에서 튜플의 전체 개수 (차수)

식별 관계

고객 엔터티의 기본키인 회원 ID를 계좌 엔터티의 기본키 하나로 공유 (실선 표시)

[ 계좌 ] +———-o< [계좌 입금]

약한 개체

• 개체의 존재가 다른 개체의 존재에 달려있음
• 계좌 입금

강한 개체

• 독립적으로 존재
• 다른 앤터티에게 기본 키 공유
• 식별 관계로 표현
• 강한 개체의 기본키 값이 변경되면 식별관계에 있는 인스턴스도 같이 변경 됨

비식별 관계

• 강한 개체의 기본키를 다른 앤터티의 기본키가 아닌 일반 컬럼으로 관계를 가지는 것 (점선 표시)

• [부서] +- - - - - o< [사원]
• 복잡성이 낮아짐
• ex ) 부모 앤터티에 참조값이 없어도 자식 앤터티의 인스턴스 생성
  • 여러개의 엔터티를 하나로 통합하면서 각각의 앤터티가 가지고 있던 여러개의 개별관계가 통합 됨
  • 자식쪽 앤터티의 주식별자를 부모 앤터티와는 별도로 생성하는 것이 더 유리할 경우

식별자

앤터티를 대표하며 유일성이 만족 되어야 함 주로 대표성 , 생성여부, 속성의 수, 대체 여부 로 판단

주식별자

기본키가 PK

• 최소성
• 대표성 (엔터티의 대표)
• 유일성 (엔터티의 인스턴스를 유일하게 식별)
• 불변성 ( = Not Null )

데이터베이스 키

• 기본 키 : 엔터티 대표
• 후보 키 : 유일성 O, 최소성 O
• 슈퍼 키 : 유일성 O, 최소성 X
• 대체 키 : 후보키 중 기본키를 선정하고 남은 키
• 외래 키 : 다른 테이블의 기본 키 필드 → 참조무결성 확인 → 허용된 데이터 값만 데이터베이스에 저장

식별자의 종류

대표성, 생성 여부, 속성의 수, 대체 여부로 알 수 있음

대표성

주식별자

• 엔티티를 대표 → 다른 엔터티와 참조관게로 연결가능
• 유일성 O, 최소성 O

보조식별자

• 대표성 X → 참조관계 X
• 유일성 O, 최소성 O

생성여부

내부 식별자

• 엔터티 내부에서 스스로 생성
• 부서코드, 주문번호, 종목코드

외부 식별자

• 다른 엔터티의 관계로 인하여 만들어지는 식별자
• 계좌 엔터티의 회원 ID

속성의 수

단일 식별자

• 하나의 속성으로 구성
• 고객 엔터티에 회원 ID

복합 식별자

• 두개 이상의 속성으로 구성

대체여부

본질 식별자

• 비즈니스 프로세스에서 만들어짐

인조 식별자

• 인위적으로 만들어짐 → 순서번호 사용
• 최대한 범용적인 값을 사용해야 하며 유일해야함
• 후보 식별자 중 주식별자로 선정할 것이 없거나 주식별자가 너무 많은 칼럼인 경우

정규화

데이터를 분해하는 과정 → 테이블 수 증가, 조인 수 증가

• 데이터의 일관성
• 최소한의 데이터 중복
  • 중복제거 → 데이터 모델의 독립성 확보
  • 엔터티의 의미해석 명확
• 최대한의 데이터 유연성
  • 비즈니스의 변화가 있어도 데이터 모델의 변경 최소
• 제 3정규화 까지만 수행
• 데이터 모델이 자주 바뀌는 경우 정규화 사용

이상현상

데이터 중복 현상

정규화 수행 X

• → 불필요한 정보 추가가 될 수 있음
• → 테이블 분해 필요 정규화 수행 O
• → 테이블 분해
• → 부서코드로 조인 → 합집합
• → 중복 데이터 제한

제 1 정규화

동일한 유형의 속성이 칼럼 단위로 반복 (속성의 원자성 위배)

• 함수적 종속성 ( X → Y ) Y 는 X 에 함수적 종속
• X 변화 → Y 변화
• 기본키를 결정함

제 2 정규화

부분함수 종속성

기본키가 하나의 칼럼 → 제 2 정규화 생략

기본키가 2개 이상의 칼럼 → 부분함수 종속성

제 3 정규화

이행함수 종속성 제거

→ 기본키를 제외하고 칼럼간에 종속성 발생

함수적 종속성 제거 (이행함수 종속성 제거)

BCNF (Boyce- Codd Normal Form)

복수의 후보키 , 후보키들이 복합속성. 서로 중첩

• 기본키를 선정한 후에도 후보키 존재
• 후보키가 기본키의 속성 중 하나를 함수적으로 종

정규화의 문제점

장점

• 테이블 분해 → 데이터 중복 제거 → 데이터 모델의 유연성이 커짐 단점
• 데이터 조회 (SELECT) 시 JOIN 이 발생
• CPU 와 메모리 사용
• 결국 과부하 유발 해결방법
• 반정규화를 통해 해결
• 하나의 테이블에 저장해 조인을 통한 성능 저하를 해결

반정규화 (De-Normalication)

데이터 베이스의 성능 향상을 위해 데이터 중복 허용, 조인을 줄임 SELECT 문에서 JOIN 을 제거. 따라서 JOIN 속도 향상과 합계로 미리 계산 후 SELECT 사용 데이터 모델의 유연성은 다소 낮아짐

반정규화를 해야 할 경우

• 정규화 시 수행속도가 낮아짐
• 다량의 범위를 자주 처리
• 특정 범위의 데이터
• 요약 / 집계 정보
• 판단 요소 → 반정규화 정보에 대한 재현의 적시성으로 판단 (반정규화 → 적시성 확보)

반 정규화 기법

1. 계산된 칼럼 추가
   • 총 판매 액, 평균 잔고 등 미리 계산 후 칼럼 추가
2. 테이블 수직 분할
   • 테이블 1개를 2개로
3. 테이블 수평 분할
   • 하나의 테이블에 있는 값을 기준으로
4. 테이블 병합
   • 1 : 1, 1 : N, 슈퍼 타입 : 서브 타입 → 성능 향상

슈퍼타입 및 서브 타입 변환 방법

One To One Type

• 슈퍼타입과 서브타입을 개별로 테이블로 도출
• 테이블 수가 많아서 JOIN 발생 → 관리 어려움

Plus Type

• 슈퍼타입과 서브타입 테이블로 도출
• JOIN 발생 → 관리 어려움

Single Type

• 슈퍼타입과 서브타입을 하나의 테이블로 도출
• 도출되는 테이블 수가 적어짐
• JOIN 성능이 올라가 관리가 편해짐
• 대신 하나에 몰려있어 입출력 성능이 낮아짐

클러스터링 인덱스

인덱스 정보 저장시 물리적으로 정렬해서 저장 조회 시 인접블럭을 연속적으로 읽기 떄문에 성능이 좋음

파티션 기법

테이블 분할 가능 논리적으로 하나의 테이블 이지만 여러개의 데이터 파일에 분산되어 저장

• 장점 → 데이터 조회 시 엑세스 범위가 줄고 성능이 오름
• 데이터가 분할 되어 IO 성능 올라감
• 각 파티션을 독립적으로 백업 하고 복구함

중앙 집중형 데이터베이스

한대의 물리적 시스템에 데이터 베이스 관리시스템을 설치 여러명의 사용자가 접속하여 사용

분산 데이터 베이스

물리적으로 떨어진 데이터 베이스에 네트워크로 연결함 작업처리 수행 시스템이 네트워크로 분산되어 있는지 인식 X 데이터 베이스는 투명성 제공

• 분할 투명성
• 위치 투명성
• 지역 사상 투명성
• 중복 투명성
• 장애 투명성
• 병행 투명성

설계 방식

1. 상향식 설계 방식
   • 지역 스키마 → 전역 스키마
2. 하향식 설계 방식
   • 전역 스키마 → 지역 스키마

장 단점

장점

• 데이터 베이스 신뢰성과 가용성이 증가
• 분산 데이터 베이스가 병렬 처리 → 빠른 응답
• 분산 데이터 베이스 추가 → 용량 확장

단점

• 여러 네트워크를 통해 분리 → 관리와 통제 어려움
• 보안관리와 통제 어려움
• 데이터 무결성 관히 어려움
• 설계가 많이 복잡

데이터 중복제거 → 유연성 향상

정규화 3층 스키마 이상현상 제거 데이터 구조화로 해결

좋은 모델링의 요건

중복 배제 비즈니스 룰 완전성

데이터 모델링 고려사항

데이터 중복 비유연성 (데이터 정의를 프로세스와 분리 비일관성

데이터베이스

데이터를 어떠한 형태의 자료구조를 사용하느냐

계층형 데이터베이스

• 트리형태의 자료구조에 데이터 저장 관리 (부모자식)
• 1 : N 관계

네트워크형 데이터베이스

• 오너와 멤버 형태로 데이터 저장
• 1 : N, M : N

관계형 데이터베이스

• 릴레이션에 데이터 저장 및 관리
• 릴레이션 → 집합연산, 관계연산 → 다양한 형태로 데이터조회 → TABLE 로 만들어짐

데이터베이스 관리 시스템

계층형 DB, 네트워크형 DB, 관계형 DB 등을 관리하기 위한 소프트웨어 관계형 데이터베이스 지원

집합 연산

• 합집합
• 차집합
• 교집합
• 곱집합

관계 연산

• 선택 연산 → 조건에 맞는 형 (튜플)
• 투영 연산 → 조건에 맞는 속성
• 결합 연산 → 공통된 속성으로 새로운 릴레이션
• 나누기 연산 → 중복된 행 제거

테이블의 구조

기본키 (PK)

• 유일성
• 최소성
• NOT NULL → 테이블의 대표

외래키 (FK)

• 다른 테이블의 기본키를 참조(JOIN) 결합 연산

테이블

• 행 ⇒ 튜플 : 하나의 테이블에 저장되는 값
• 칼럼 ⇒ 속성 : 데이터를 저장하기 위한 필드

SQL 종류

작업 순서는 DCL → DDL → DML → TCL

DDL (Data Definition Language) - 관계형 데이터베이스의 구조 정의 → 생성 변경 삭제 - CREATE, ALTER, DROP, RENAME, TRUNCATE

DML (Data Manipulation Language) - 데이터 입력, 수정 삭제 조회 → 결과 조작 - INSERT, UPDATE, DELETE, SELECT

DCL (Data Control Language) - 데이터베이스 사용자에게 권한 부여, 회수 - GRANT, REVOKE

TCL (Transaction Control Language) - 트랜잭션 제어 - COMMIT, ROLLBACK, SAVEPOINT

트랜잭션

작업을 처리하는 단위

원자성 - 트랜잭션은 데이터베이스 연산의 전부가 실행되거나 전혀 실행 되지 않아야 함 (ALL or NOT) - 트랜잭션 처리가 완전히 끝나지 X = 실행되지 않은 상태

일관성 - 트랜잭션 실행 결과로 데이터베이스의 상태가 모순 X - 일관성 유지

고립성 - 트랜잭션 실행 중에 연산의 중간 결과는 다른 트랜잭션이 접근 X - 부분적인 실행 결과를 다른 트랜잭션이 볼 수 X

영속성 - 트랜잭션이 실행을 완료하면 결과는 영구적 보장 - 저장됨을 보장

SQL 실행 순서

파싱 → 실행 → 인출

• 파싱 (Porsing) : 문법 검사, 구문 분석
• 실행 (Execution) : 옵티마이저가 SQL 실행
• 인출 (Fetch) : 데이터 읽고 전송

제약 조건 종류

데이터의 무결성을 유지하기 위해 특정 칼럼에 설정하는 제약

• PRIMARY KEY (기본키) ⇒ 테이블 당 1개만, NULL X
• UNIQUE KEY (고유키) ⇒ NULL 입력 가능
• NOT NULL
• CHECK
• FORIGN KEY (외래키) ⇒ 여러개의 제약가능

테이블 변경

• 테이블명 변경

    ALTER TABLE EMP
    	RENAME TO NEW_EMP;
  

• 칼럼 추가

    ALTER TABLE EMP
    	ADD (age number(2) default 1);
  

• 칼럼 변경

    ALTER TABLE EMP
    	MODIFY (ename varchar2(40) not null);
  

• 칼럼 삭제

    ALTER TABLE EMP
    	DROP COLUMN age;
  

• 칼럼 명 변경

    ALTER TABLE EMP
    	RENAME COLUMN ename to new_ename;
  

테이블 삭제

DROP TABLE EMP;
## 테이블의 구조와 데이터 삭제

DROP TABLE EMP CASCADE CONSTRAIT;
## 참조된 제약사항까지 삭제

뷰

테이블로부터 유도된 가상의 테이블

• 실제 데이터를 가지고 있지 않고 테이블을 참조해서 원하는 칼럼만 조회
• 데이터 딕셔너리에 SQL문을 형태로 저장하되 실행 시 참조 됨
• 참조된 테이블이 변경되면 뷰도 변경 → 데이터 관리 간단
• 뷰의 검색은 참조한 테이블과 동일
• 뷰에 대한 입력 수정 삭제 제약
• 특정 칼럼만 조회시켜서 보안성 향상
• 한번 생성된 뷰는 변경 X (삭제 후 재생성)
• ALTER 로도 불가

## 뷰 생성
CREATE VIEW T_EMP AS
	SELECT * FROM EMP;

## 뷰 조회
SELECT * FROM T_EMP;

## 뷰 삭제
DROP VIEW T_EMP;

장점

• 특정 칼럼 조회 → 보안 기능
• 데이터 관리 간단 (응용프로그램 변경X)
• SELECT 문 간단
  • 단지 정의만 있음 실행 시점에서 재작성
• 하나의 테이블 ⇒ 여러개의 뷰 생성

단점

• 뷰는 독자적인 인덱스를 만들 수 없음
• 삽입 수정 삭제 연산이 제약 됨
• 데이터 구조 변경 안됨

SQL 구문

## INSERT
## 테이블에 데이터를 입력하는 DML 문
INSERT INTO EMP (EMPNO, ENAME)
	VALUES (1000, "ABC");

## SELECT
## 데이터를 조회해서 테이블에 바로 삽입
INSERT INTO DEPT_TEST
	SELECT * FROM DEPT;
## DEPT 데이터를 조회해서 모든 데이터를 DEPT_TEST 에 입력

## NOLOGGING
## 로그파일의 기록을 최소화 시켜 입력시 성능 향상
## BUFFER CASH 라는 메모리 영역을 생략하고 기록
ALTER TABLE DEPT NOLOGGING;

## UPDATE
## 입력된 데이터 값 수정
## 조건이 있으면 특정 데이터만, 없으면 모든 데이터
UPDATE EMP
	SET ENAME = "조조"
	WHERE EMPNO = 100; ## 조건절

## DELETE
## 조건 검색 -> 해당 행 삭제
## 조건 X -> 모든 데이터 삭제
## 데이터 용량 초기화 X , 구조 삭제 X
DELETE FROM EMP
	WHERE EMPNO = 100;

## TRUNCATE
## 테이블의 모든 데이터 삭제 (테이블 구조는 삭제 X)
## 테이블의 용량 초기화
## COMMIT 자동
TRUNCATE TABLE TNAME;

## ORDER BY
## 모든 실행이 끝난 후 데이터 출력 전 정렬
## SELECT 문 중 가장 늦게 실행
## 기본적으로 오름차순
## SORT_AREA_SIZE 사용
## 정렬 회피를 위해 인덱스 생성
SELECT * FROM EMP
	ORDER BY ENAME, SAL DESC;
## SAL 만 내림차순

## INDEX 를 이용한 정렬 회피
CREATE TABLE EMP (
	empno number(10) primary key,
	ename varchar2(20),
	sal number(10) );

INSERT INTO EMP VALUES (1000, "임베스트", 20000);
INSERT INTO EMP VALUES (1001, "조조", 20000);
INSERT INTO EMP VALUES (1002, "관우", 20000);

SELECT /* + INDEX_DESC(A) */
FROM EMP A;

## DISTINCT
## 칼럼명 앞에 지정하여 중복 된 데이터를 한번만 조회
## DEPTNO 값이 중복 X
SELECT DEPTNO FROM EMP
ORDER BY DEPTNO; ## 중복해서 조회

SELECT DISTINCT DEPTNO FROM EMP
ORDER BY DEPTNO; ## 중복 제거 후 조회

## ALIAS (별칭)
## 테이블 명이나 칼럼명이 너무 길어 간략하게 함
SELECT ENAME AS "이름" FROM EMP A
WHERE A.EMPNO = 1000;

## WHERE
## = < <= > >=
## 비교연산자

## !=, ^=, <>, NOT CNAME =
## 부정비교연산자 (같지않은 것 조회)

## AND, OR, NOT
## 논리 연산자

## LIKE '%비교 문자열%' => 비교문자열 조회 % 은 모든 값
## BETWEEN A AND B => A 와 B 사이 값
## IN (list) => =OR list 값 중 하나만 일치해도 조회
## IS NULL => NULL 값
## SQL 연산자

## NOT BETWEEN A AND B => A와 B 사이에 해당 되지않은 값
## NOT IN (list) => list 와 불일치
## IS NOT NULL : NULL 이 아닌 값
## 부정 SQL 연산

##LIKE 문의 와일드 카드
## % => 어떤 문자를 포함하는 모든 것
## 조% => 조로 시작하는 모든 문자
## _ => 한개인 단일 문자

SELECT * FROM EMP
WHERE ENAME LIKE '%조'; ## 1조 2조 3조

SELECT * FROM EMP
WHERE ENAME LIKE '1조'; ## 1조

SELECT * FROM EMP
WHERE ENAME LIKE 'test_'; ## test + 한글자

## BETWEEN
SELECT * FROM EMP
WHERE SAL BETWEEN 1000 AND 2000; ## 이상, 이하

SELECT * FROM EMP
WHERE SAL NOT BETWEEN 1000 AND 2000; ## 미만 초과

## IN
## 하나의 조건만 만족해도 O == OR
SELECT * FROM EMP
WHERE JOB IN ("CLERK", "MANAGER"); ##둘 중 하나인 값 조회

SELECT * FROM EMP
WHERE (JOB, ENAME)
IN (("CLERK", "TEST1"), ("MANAGER", "TEST4"));
## 두 조건을 만족하는 값 찾기 JOB 과 ENAME 에서

## NULL
## 모르는 값, 값의 부재
## 숫자나 날짜를 더하면 NULL
## 비교할때 알수 없음이 반환
SELECT * FROM EMP
WHERE MGR IS NULL;

## NVL => NULL 이면 다른값으로 바꿈
NVL(MGR, 0) ## MGR 칼럼이 NULL 이면 0 으로 바꿈

## NVL2 => NVL 함수와 DECODE 함수를 하나로 만든 것
NVL2(MGR, 1, 0) ## MGR 칼럼이 NULL 아니면 1 NULL 이면 0

## NULLIF => 두개의 값이 같으면 NULL 아니면 첫번째 값
NULLIF(exp1, exp2) ## exp = exp2 면 NULL 아니면 exp1

## COALESCE(a, b, c, ...) NULL 이 아닌 최초값 반환
COALESCE(a, b, c, ...)

## ISNULL(a, b) a = null 이면 b 반환
ISNULL(a, b)

## GROUP BY
## 테이블에서 소규모 행을 그룹화 하여 계산
## HAVING 구에 조건을 사용
## ORDER BY 로 정렬
SELECT DEPTNO, SUM(SAL)
	FROM EMP
GROUP BY DEPTNO; ## DEPTNO 별로 그룹화

## HAVING
## GROUP BY 에 조건절을 사용하려면 HAVING 사용
SELECT DEPTNO, SUM(SAL) FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) > 10000;
## GROUP BY 결과에서 급여합게 10000 초과

############# 집계함수 ##############
## COUNT(행수), SUM, AVG, MAX, MIN, STDDEV(표준편차), VARIAN(분산)

##COUNT()
COUNT(*) ## NULL 값을 포함한 모든 행의 수
COUNT(COL_NAME) ## NULL 값을 제외한 행의 수
SELECT COUNT(*) FROM EMP;

## DEPTNO(부서), MGR(관리자) 급여계산 (급여평균)
SELECT DEPTNO, MGR, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, MGR;

## 직업별 급여 합계 중에 급여합계가 1000 이상인 직업
SELECT JOB, SUM(SAL)
FROM EMP
GROUP BY JOB
HAVING SUM(SAL) >= 1000;

## 사원번호 1000 ~ 1003 번의 부서별 급여 합계
SELECT DEPTNO, SUM(SAL)
WHERE EMPNO BETWEEN 1000 AND 1003
GROUP BY DEPTNO;

## SELECT 문 실행 순서
## FROM => WHERE => GROUP BY => HAVING => SELECT => ORDER BY

############# 형변환 함수 ##############

## 형변환
## 두개의 데이터 타입이 일치하도록 변호나
## 명시적 형 변환 - 형변환 함수로 데이터 타입을 일치
## 암시적 형 변환 - 개발자가 형변환을 하지 안ㅇㅎ은 경우 DB 자동변환

## TO_NUMBER(문자열) => 문자열을 숫자로
## TO_CHAR(숫자나 날짜, [FORMAT]) => 숫자나 날짜를 FORMAT 의 문자로
## TO_DATE(문자열, FORMAT) => 문자열을 지정된 FORMAT 으로

## 인덱스와 형변환
SELECT * FROM EMP
WHERE EMPNO = "100" ## 암시적 형 변환 TO_CHAR(EMPNO)

##인덱스 => 데이터를 빠르게 조회하기 위해서 인덱스 키를 기준으로 변형 발생 인덱스 사용 X

SELECT * FROM EMP
WHERE EMPNO = TO_NUMBER("100"); ## 명시적 형 변
## EMPNO 컬럼이 변환 X => 인덱스를 사용함

## DUAL
## 데이터베이스에 의해 자동으로 생성되는 테이블

## 내장형 함수
## 형변환, 문자열 및 숫자형, 날짜형

## 문자열 함수
## ASCII(문자) - 문자나 숫자를 아스키코드 값으로
## CHAR(ASCII코드값) - ASCII 코드값을 문자로
## SUBSTR(문자열, M, N) - 문자열에서 M 위치부터 N 자르고 출력
## CONCAT(문자열1, 문자열 2) - 두 문자열 결합
## LOWER(문자열) : 소문자로
## UPPER(문자열) : 대문자로
## LENGTH / LEN(문자열) : 공백포함 문자열 길이
## LTRIM (문자열, 지정문자) : 왼쪽에서 지정된 문자 삭제, 공백 삭제
## RTRIM (문자열, 지정문자) : 오른쪽에서 지정 될 문자 삭제, 공백 삭제
## TRIM (문자열, 지정문자) : 양쪽에서 ::

## 날짜형 함수
## SYSDATE : 오늘 날짜를 날짜 타입으로
## EXTRACT("YEAR" | "MONTH" | "DAY" from dual) : 년 월 일

SELECT SYSDATE,
	EXTRACT(YEAR FROM SYSDATE),
	TO_CHAR(SYSDATE, "YYYYMMDD") FROM DUAL;

## 숫자형 함수
## ABS(숫자) : 절대값
## SIGN(숫자) : 양수 음수 0 구별
## MOD(숫자1, 숫자2) : 숫자 1 나누기 숫자 2의 나머지
## CEIL / CEILING(숫자) : 숫자보다 크거나 같은 최소의 경우
## FLOOR(숫자) : 숫자보다 작거나 같은 최대의 정수
## ROUND(숫자, M) : 소수점 M 자리에서 반올림 ROUND(10.22, 1) = 10.2
## TRUNC(숫자, M) : 소수점 M 자리에서 절삭

## DECODE
DECODE(EMPNO, 1000, "TRUE", "FALSE") FROM EMP;
## EMPNO = 1000 이면 TRUE, FALSE 3항 연산자 생각

## CASE
## IF ~ THEN ~ ELSE ~ END
SELECT
CASE
	WHEN EMPNO = 1000 THEN "A"
	WHEN EMPNO = 1001 THEN "B"
	ELSE "C"
END
FROM EMP;

## ROWNUM
## SELECT 결과문에 대해 논리적 인련번호 부여
## 조회되는 행 수를 제한 할 때
## 페이지 단위 출력 (인라인 뷰)

## 인라인 뷰
## SELECT 문에서 FROM 절에 사용되는 서브쿼리
## 순차적으로 증가하는 ROWNUM 알고 싶을 때

SELECT * FROM ## 메인쿼리
(SELECT * FROM EMP) a; ## 서브쿼리 (인라인 뷰)

SELECT * FROM EMP
WHERE ROWNUM <= 1;
## 한 행을 조회

SELECT *
FROM (SELECT ROWNUM list, ENAME FROM EMP)
WHERE list <= 5;

SELECT *
FROM (SELECT ROWNUM list, ENAME FROM EMP)
WHERE list BETWEEN 5 AND 10;

## ROWID
## 데이터를 구분할 수 있는 유일한 값
## 데이터가 어떤 데이터 파일 어느 블록에 저장되었는지 알 수 있음
## Reference

## WITH
## 서브쿼리를 사용해서 임시 테이블이나 뷰 처럼 사용할 수 있는 구문
## 서브쿼리 블록에 별칭 지정 가능

WITH viewData AS
	(SELECT * FROM EMP
		UNION ALL
		SELECT * FROM EMP)
SELECT * FROM viewData WHERE EMPNO = 1000;

## EMP 테이블, WITH 구문, 부서번호 DEPTNO 가 30 인 임시테이블 만들고 조회
WITH W_EMP AS
	(SELECT * FROM EMP WHERE DEPTNO = 30)
SELECT * FROM W_EMP;

## GRANT
## 데이터베이스 사용자에게 권한 부여 -> 연결 입력 수정 삭제 조회
## GRANT 권한 ON 테이블명 TO 사용자;
## 권한의 종류
## SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, ALL(모든 권한)

GRANT SELECT, INSERT, UPDATE, DELETE
	ON EMP
	TO USER1;

## WITH GRANT OPTION => 특정사용자 권한 부여
## WITH ADMIN OPTION => 모든 권한 부여

GRANT SELECT, INSERT, DELETE
	ON EMP
	TO USER1 WITH GRANT OPTION;

## REVOKE
## 권한 취소
## REVOKE 권한 ON 테이블 FROM 사용자;

## COMMIT
## INSERT, UPDATE, DELETE 문으로 변경한 데이터 반영
## 변경 전 데이터 잃어버림
## COMMIT 이 완료되면 LOCK 이 해재 되어 다른 사용자가
## 데이터 조작 가능. 트랜잭션 과정 종료

## ROLLBACK
## 데이터에 대한 변경사항을 모두 취소하고 트랜잭션 종료
## INSERT, UPDATE, DELETE 문의 작업을 모두 취소
## 이전 COMMIT 부분까지만 복구
## LOCK 이 해제 -> 다른 사용자도 데이터베이스에 행 조작

## SAVEPOINT
## 트랜잭션을 작게 분할하여 관리하는 것
## 지정된 위치 이후의 트랜잭션만 ROLLBACK 가능
## SAVEPOINT 지정 : SAVEPOINT <SAVEPOINT 명> 실행
## 지정된 SAVEPOINT 까지만 데이터 변경 취소
## : ROLLBACK TO <SAVEPOINT>
## ROLLBACK 실행 -> SAVEPOINT 와 관계없이 모든 변경 사항 저장 X

UPDATE EMP SET ENAME = "임베스트" WHERE EMPNO = 1000;
COMMIT;
SELECT ENAME FROM EMP WHERE EMPNO = 1000;
## 결과 ENAME = "임베스트";
ROLLBACK;
SELECT ENAME FROM EMP WHERE EMPNO = 1000;
## 결과 ENAME = "TEST1";

SAVEPOINT t1;
UPDATE EMP SET ENAME = "관우" WHERE EMPNO = 1003;
SAVEPOINT t2;
UPDATE EMP SET ENAME = "장비" WHERE EMPNO = 1004;
SELECT ENAME FROM EMP WHERE EMPNO IN <1003, 1004>;
## 결과 ENAME "관우" , "장비"
ROLLBACK TO t2; ## t2 시점으로 돌아가기
SELECT ENAME FROM EMP WHERE EMPNO IN <1003, 1004>;
## 결과 ENAME = "관우", "test5"

## EQUI (등가) 조인
## 여러개의 릴레이션을 사용해서 새로운 릴레이션을 만드는 과정
## 교집합. 두개의 테이블 간에 일치하는 것
SELECT * FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
## 두개의 테이블을 연결

## INNER JOIN
## ON 문을 사용해서 테이블을 연결
SELECT * FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
AND EMP.ENAME LIKE "임%"
ORDER BY ENAME;

## 해시조인 (HASH JOIN)
## 테이블을 해시메모리에 적재한 후에 해시함수로써 연결
## 두개의 테이블 연결. EQUI 만 가능

## INTERSECT 연산 (교집합)
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;

## NON-EQUI 조인 (비등가)
## 정확히 일치하지 않는 것
## > < >= <=

## OUTER JOIN
## NULL 인 행도 조회
## LEFT OUTER JOIN + RIGHT OUTER JOIN = FULL OUTER JOIN

SELECT * FROM DEPT, EMP
WHERE EMP.DEPTNO = DEPT.DEPTNO;

SELECT * FROM DEPT LEFT OUTER JOIN EMP
	ON EMP.EMPNO = DEPT.DEPTNO;

## CROSS JOIN
## 조인 조건구 없이 2개의 테이블을 하나로 조인
## 카테시안 곱 => 14개의 행과 4개의 행 테이블 조인 => 56

SELECT * FROM EMP CROSS JOIN DEPT;
SELECT * FROM EMP, DEPT; ## 두개가 같음

## UNION
## 두개의 테이블을 하나로 합침
## 테이블읠 칼럼수, 칼럼의 데이터 형식 모두 일능
## 중복 데이터 제거
## 정렬 과정 발생
SELECT DEPTNO FROM EMP
UNION
SELECT DEPTNO FROM EMP;

## UNION ALL
## 두개의 테이블을 하나로 합침
## 칼럼수 형식이 일치해야 가능
## 중복제거 X 정렬 X
SELECT DEPTNO FROM EMP
UNION ALL
SELECT DEPTNO FROM EMP;

## MINUS (차집합)
## 먼저쓴 SELECT 문 + 뒤에 쓴 SELECT 문 X
## EXCEPT MYSQL
SELECT DEPTNO FROM DEPT
MINUS
SELECT DEPTNO FROM EMP;

## 계층형 조화 (CONNECT BY)
## 계층형으로 데이터 조회 (트리구조로 위 아래 접근)
SELECT MAX(LEVEL)
FROM LIMBEST, EMP
START WITH MGR IS NULL ## 시작 조건
CONNECT BY PRIOR EMPNO = MGR; ## 조인조건

SELECT LEVEL, EMPNO, MGR, ENAME
FROM LIMBEST, EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

## CONNECT BY 키워드
## LEVEL : 검색항목의 깊이 (상위레벨 = 1)
## CONNECT_BY_ROOT : 가장 최상위 값 (ROOT 노드 정보)
## CONNECT_BY_ISLEAF : 가장 최하위 값
## SYS_CONNECT_BY_PATH : 계층구조의 전체 전개 경로 하위 가지
## NOCYCLE : 순환 구조가 발생 지점까지만 전개
## CONNECT_BY_ISCYCLE : 순환 구조 발생 지점 표시 ROOT 까지 경로에 존재하는 데이터

## MAIN-QUERY
## 서브쿼리 밖의 SELECT 문

## SUB-QUERY
## WHERE 절에 사용 된 쿼리 문

## 인라인 뷰
## FROM 절에 사용 된 쿼리문

## 다중형 비교 연산자
## IN(SUB) : 메인쿼리의 비교조건이 서브쿼리의 결과 중 하나만 동일하면 참 OR
## ALL(SUB) : 메인쿼리와 서브쿼리의 결과가 모두 동일해야함
## < ALL 최솟값 반환 > ALL 최댓값 반환
## ANY(SUB) : 메인쿼리의 비교조건이 서브쿼리의 결과중 하나 이상 동일해야 참
## < ANY 하나라도 크게 > ANY 하나라도 작으면 참
##EXIST(SUB) : 메인쿼리와 서브쿼리의 결과가하나라도 존재시 참

## IN
SELECT ENAME, DNAME, SAL
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.DEPTNO
IN (SELECT EMPNO FROM EMP WHERE SAL > 2000)

## ALL
SELECT *
FROM EMP
WHERE DEPTNO <= ALL(20, 30);
## DEPTNO 가 20, 30보다 작거나 같은 것

## EXISTS
## 어떤 데이터 존재 여부 확인
SELECT ENAME, DNAME, SAL
FROM EMP.DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EXISTS (SELECT * FROM EMP
	WHERE SAL > 2000);

## 스칼라 SUB-QUERY
## 한 행과 한 컬럼만 반환
SELECT ENAME AS "이름"
	SAL AS "급여"
	(SELECT AVG(SAL) FROM EMP) AS "평균 급여"
FROM EMP
WHERE EMPNO = 1000;

## ROLLUP
## GROUP BY 의 칼럼에 대해 SUBTOTAL 을 만들어줌
## GROUP BY 구에 칼럼이 두개 이상 => 순서에 따라 결과 다름
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP (DEPTNO, JOB);

## DEPTNO JOB SUM(SAL)
## 10 C 1300 직업별
## 10 M 2450 직업별
## 10   3750 부서별합계 (소계)
## 20 C 500
## 20 A 1800
## 20 M 2400
## ...
## 전체합계 5750

## GROUPING 함수
## ROLLUP CUBE, GROUPING SETS 에서 생성되는 합계값 구현
## 계산 되면 1 계산되지 않았으면 0 반환

## GROUPING SETS 함수
## GROUP BY 에 나오는 칼럼의 순서와 관계 X
## 개별적 처리

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS (DEPTNO, JOB);
## DEPTNO 로 묶은 TABLE 과 JOB 으로 묶은 TABLE 따로
## 소계 없음

## CUBE
## 제시한 칼럼에 대한 결합 가능한 모든 집계
## 다차원 집계
## 조합 가능한 경우의 수가 모두 조합

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB);
## DEPTNO 합계, JOB 합계, DEPTNO 별 JOB 합계, 전체 합계

## 윈도우 함수
## 행과 행간의 관게 정의
## 순위 합계 등 조작 가능

SELECT WINDOW_FUNCTION(ARGUMENTS)
OVER(PARTITION BY "칼럼"
	ORDER BY WINDOWING 절)
FROM "테이블명";

## WINDOWING
## 행 기준의 범위 정함
## ROWS (물리적 결과의 행 수)
## RANGE (논리적인 값에 의한 범위)

## BETWEEN ~ AND : 윈도우 시작과 끝
## UNBOUNDED PRECEDING : 윈도우 시작 위치 = 첫번째 행
## UNBOUNDED FOLLOWING : 윈도우 마지막 위치 = 마지막 행
## CURRENT ROW : 윈도우 시작 위치 = 현재 행, 데이터가 인출 될 행

## 윈도우 함수
## PARTITION 구문이 없으면 전체 집합을 하나의 PARTITION 으로
## 결과 건수 줄어들지 않음
## 적용 범위는 PARTITION 을 넘을 수 없음

## 처음부터 마지막까지 합산
SELECT EMPNO, ENAME, SAL, SUM(SAL)
OVER (ORDER BY SAL
	ROWS BETWEEN UNBOUNDED PRECEDING
	AND UNBOUNDED FOLLOWING) TOTSAL
FROM EMP;

################ 순위 함수 ##################
## RANK : 특정 항목에 대해 순위 계산
## DENSE_RANK : 동일 순위 = 하나의 건수 (공동 등수 있음)
## ROW_NUMBER : 동일 순위 고유 순위 (공동 등수 없음)

################ 집계 함수 ##################
## 윈도우 함수 제공
## SUM, AVG, MAX, MIN (COUNT)

## SUM()
SELECT ENAME, SAL,
	SUM(SAL) OVER(PARTITION BY MGR) SUM_MGR
FROM EMP;

## 행 순서 관련 윈도우 함수
## FIRST_VALUE : 가장 처음 나오는 값 === MIN
## LAST_VALUE : 가장 나중 값 === MAX
## LAG : 이전 행 \
## LEAD : 특정 위치 행 /

## FIRST_VALUE
SELECT DEPTNO, ENAME, SAL, FIRST_VALUE(ENAME)
	OVER (PARTITION BY DEPTNO
	ORDER BY SAL DESC ROWS UNBOUNDED PRECEIDIONG) AS DEPT_A
FROM EMP;