while (1): study();
핵심 정리 노트 본문
1. 데이터 모델링의 이해
데이터 모델링이란?
1) 업무 분석 기법
2) 약속된 표기법으로 표현
3) DB 구축을 위한 분석/설계 과정
데이터 모델링 유의점
1) 중복
2) 비유연성
3) 비일관성
Peter Chen 표기법
엔터티
- 특징
1. 필요성
2. 유일성 (식별자)
3. 인스턴스
4. 프로세스
5. 속성
6.관계
- 분류
1. 유무형에 따른 분류
유형 엔터티, 개념 엔터티, 사건 엔터티
2. 발생시점에 따른 분류
기본 엔터티, 중심 엔터티, 행위 엔터티
속성
업무에서 필요로 하는 인스턴스에서 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위
한 개의 엔터티는 두 개 이상의 속성을 가짐
관계
1) 관계속성
관계를 정의할 목적으로 생성된 속성, 식별자가 아닌 일반 속성이라면 정상적인 관계 속성이 아님
2) ERD에서는 존재적 관계와 행위에 의한 관계를 구분하지 않음
클래스다이어그램에서는 구분함
2) 관계선택사양 = 데이터 발생규칙
식별자
- 특징
유일성, 최소성, 존재성, 불변성
- 종류
1. 후보 키(Candidate Key): 기본 키가 될 수 있는 자격을 갖춘 후보
2. 기본 키(Primary Key): 테이블 내 행을 유일하게 식별할 수 있는 키
3. 대리 키(Alternate Key): 후보 키 중 기본 키가 아닌 키
4. 슈퍼 키(Super Key): 불필요한 속성이 덧붙여진 후보 키 (최소성 불만족)
- 인조 식별자 사용을 고려해야 하는 경우
1. 본질식별자의 속성 값이 변경될 수 있는 경우
2. 본질식별자의 속성 값이 NULL을 허용하는 경우
3. 데이터 발생 규칙이 변경될 가능성이 있는 경우
식별 관계: 실선, FK 주식별자, UID bar <-> 식별자 수 증가
* 자식 엔터티에 내부식별자가 별도로 존재할 경우 1:N 관계가 성립
비식별 관계: 점선, FK 일반 속성 <-> 조인횟수 증가
2. 데이터 모델과 SQL
다가속성(유사한 속성을 나열한 반복 속성 또한 일종의 다가속성) → 별도의 엔터티로 제1정규화
기준 테이블이 선택관계인 경우(FK에 NULL이 존재하는 경우) 아우터 조인
기준 테이블이 필수관계인 경우(FK가 NOT NULL인 경우) 이너 조인
DBMS 옵티마이저는 테이블을 2개씩 짝지어 조인 수행
1) 부모-자식, 자식-부모가 모두 필수이면 레코드 입력을 하나의 트랜잭션으로 처리
2) 격리성이 낮은 경우 발생하는 문제
1. Dirty Read: 다른 트랜잭션에 의해 수정되었고 아직 커밋되지 않은 데이터를 읽는 것
2. Non-Repeatable Read: 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상
3. Phantom Read: 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫번째 쿼리에 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상
ACID (atomicity, consistency, isolation, durability)
일관성: 데이터 무결성이 일관되게 보장
COUNT(*)를 사용할 시 오류가 발생할 확률이 높음
3. SQL 기본
테이블 별칭
테이블에 별칭을 지정하고 나면, SELECT나 WHERE절에서 반드시 별칭을 사용해야만 한다.
단, 단일 테이블 조회의 경우 별칭으로 칼럼을 한정하지 않아도 된다.
서브 쿼리 내에서 테이블 별칭을 사용하지 않을 경우 서브쿼리 테이블 먼저 확인
CASE 표현식
맨 처음 일치하는 표현식을 반환
1) SEARCHED CASE EXPRESSION
CASE WHEN 칼럼=a THEN b
2) SIMPLE CASE EXPRESSION
CASE 칼럼 WHEN a THEN b
GROUP BY
1) SELECT, WHERE에 GROUP BY 절 or 집계함수 표현식만 기술 가능
2) 집계함수는 GROUP BY가 없으면 항상 결과를 반환
3) HAVING절은 (GROUP BY없이) 집계함수 Filter 용으로도 사용 가능
카티션 곱
조인 시, 조인 조건이 누락되면 카티션 곱 집합이 생성
문자의 대소 비교
처음 달라지는 문자의 크기
아우터 조인
무조건 기준 테이블의 행을 모두 결과에 포함
모든 조인 조건에 (+)가 필요 (테이블 간 비교가 아니어도 마찬가지)
함수
* 단일행 함수: 한 행마다 적용 => 단일값 반환
다중행 함수: 전체 행에 적용 => 단일값 반환
SUBSTR(칼럼명, m, n)
m번째 인덱스부터 n개의 문자
TRUNC(a, b)
a로부터 b까지만 표현하고 나머지는 버린다.
ADD_MONTHS(날짜, a)
날짜 값에 a만큼 개월 수를 더한다.
EXTRACT(form FROM column): 숫자형 / TO_CHAR(column, form): 문자형
DECODE(칼럼, A, a, B, b, ..., c)
칼럼값이 A이면 a, B이면 b, ... 모두 아니면 NULL (혹은 c) 반환
NULLIF(칼럼, a)
칼럼값이 NULL이면 a 반환
MOD
나머지
TRIM
leading: 앞, trailing: 뒤
NTILE
결과 집합을 N개 그룹으로 나눈 뒤 몇번째 그룹에 속하는지 반환
나머지가 1이면 앞쪽 그룹에 행이 하나 더 할당
SIGN: 양수면 1, 음수면 -1, 0이면 0
* 오라클은 NULL과 ''를 동일하게 처리
4. SQL 활용
EXISTS: 서브 쿼리의 결과가 1건이라도 존재하면 True 아니면 False
LPAD(칼럼, a, b): 칼럼값의 길이가 a가 될 때까지 b로 채움
REGEXP(문자열, 패턴, 시작위치, 일치 횟수)
집합 연산자
1. MINUS = DISTINCT + NOT EXISTS
INTERSECT = DISTINCT + EXISTS
2. ORDER BY는 오직 한 번만!
3. UNION은 중복 배제, UNION ALL은 중복 포함 - 헷갈리면 안됨!
그룹 함수
1. ROLLUP(C1, C2) = (C1, C2) → (C1) → ()
CUBE(C1, C2) = () → (C2) → (C1) → (C1, C2)
2. 조합 열은 하나의 단위로 처리
3. GROUPING: 표현식이 행 그룹에 포함되면 0, 그렇지 않으면 1 반환
4. GROUPING_ID: 함수의 결과값을 연결한 값의 비트 벡터의 값 반환
= BIT_TO_NUM(GROUPING(C1), GROUPING(C2))
5. GROUPING SETS: 총계(grand total)까지 표기해주어야 함
윈도우 함수
기본 범위는 RANGE UNBOUNDED PRECEDING
SUM, COUNT 등에 ORDER BY를 기술하면 누적 통계를 구함
KEEP
최고 서열에 대해 집계
Ex)
SELECT 고객번호,
MAX(주문금액) KEEP (DENSE_RANK LAST ORDER BY 주문일자) AS 최대주문금액
FROM 주문
GROUP BY 고객번호;
Top N 쿼리
1. TOP(N) WITH TIES = RANK
2. ROW LIMITING: FETCH & OFFSET
3. ROWNUM: 데이터를 출력하면서 순서대로 번호를 부여 (BETWEEN 사용 시 과부하)
계층형 질의
1. START WITH = CTE 상단 쿼리 = 시작 노드
CONNECT BY = CTE 하단 쿼리 = 반복 조건
2. CTE: WITH ~ UNION ALL
3. WHERE절보다 실행순서가 앞선다!
PIVOT과 UNPIVOT
PIVOT SUM(칼럼) FOR A IN (a, b, c): A를 a, b, c로 pivot
UNPIVOT (A FOR FOR B IN (a, b, c)): a, b, c를 A로 unpivot, B칼럼에는 별칭 없을 시 칼럼명
D10_SAL, D10_CNT, D20_SAL, D20_CNT
정규표현식
1.
%: 0개 이상의 문자와 일치
_: 1개의 문자와 일치
2. PORMS
P: position
O: occurence
R: return_opt (0이면 현재 위치, 1이면 다음 위치)
M: match_params
S: subexpr (0이면 전체 문자열, 1 이상이면 서브표현식 반환)
3. ESCAPE 구문으로 이스케이프 문자 지정 가능
SELECT 쿠폰번호, 할인내용
FROM 쿠폰
WHERE 할인내용 LIKE '1_\%%' ESCAPE '\';
3. 관리 구문
비절차적 데이터 조작어 vs 절차적 데이터 조작어
1) 비절차적 데이터 조작어: 무슨 데이터(What)
2) 절차적 데이터 조작어: 어떻게 데이터에 접근
ex) PL/SQL(Oracle), T-SQL(SQL Server)
트랜잭션
오라클: UPDATE 문이 시작된 시점을 기준으로 갱신 대상을 식별
SQL Server: 레코드에 도달한 시점을 기준으로 갱신 대상을 식별
* 블로킹된 SQL은 사라지는게 아니라 보류되는 것!
DML
INSERT
1) INSERT + NOT EXISTS = 중복 필터링
2) INSERT FIRST: 조건에 맞는 첫 테이블에 행 삽입
3) 칼럼을 명시하지 않을 시 모든 칼럼을 입력해야함
UPDATE
1. UPDATE + EXITSTS = MERGE = NULL 입력 방지
2. 조인뷰를 이용한 업데이트 가능
단, 조인 대상 칼럼이 PK or UNIQUE로 유일성을 보장해야함
DELETE
DELECT는 DML, TRUNCATE는 DDL
* 중복 제거 구문
DELETE
FROM T1
WHERE ROWID IN (SELECT ROWID
FROM (SELECT ROWID,
ROW_NUMBER() OVER (PARTITION BY C1, C2, C3
ORDER BY ROWID) AS RN
FROM T1)
WHERE RN > 1);
MERGE
USING 절 소스테이블의 조인 컬럼 기준 유일성을 보장해야함
매칭 or 매칭되지 않은 행들에 대해서 한번에 수행
WHEN MATCHED THEN UPDATE (+DELETE)
WHEN NOT MATCHED THEN INSERT
DDL
* Oracle은 Auto commit
SQL Server는 사용자 commit
ALTER
1) VARCHAR 타입은 NULL이 아니어도 데이터 크기 변경 가능
* 영어 1byte, 한글 2byte
데이터 크기 디폴트 단위는 byte, char는 length * byte
NUMBER(a, b)
a - 1 자리 정수, b자리 소수
ex) NUMBER(2, 5) = -9.00099 ~ +9.00099
NUMBER(a)
a자리 정수
ex) NUMBER(4) = -9999 ~ + 9999
2) 제약 추가 방법
ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_PK PRIMARY KEY (PROD_ID)
CREATE TABLE PRODUCT(..., CONSTRAINT PRODUCT_PK PRIMARY KEY (PROD_ID));
3) SQL Server는 칼럼 동시 수정을 지원하지 않음
4) ALTER TABLE 구문
1) ADD
2) DROP
3) MODIFY
4) RENAME COLUMN
5) DROP CONSTRAINT
6) ADD CONSTRAINT
5) 참조 무결성 제약
1) DELETE
CASCADE
SET NULL
SET DEFAULT
RESTRICT: Child에 PK가 없을 때 Master 삭제
NO ACTION
2) INSERT
AUTOMATIC: Master에 PK없을 시 생성 후 입력
SET NULL
SET DEFAULT
DEPENDENT: Master에 PK가 존재해야 입력
NO ACTION
DROP
FK 제약 조건에 의해 참조되는 테이블을 제거하려면 먼저 FK 제약 조건을 제거
* Drop vs Truncate vs Delete
DROP: 데이터, 디스크 사용량, 테이블 스키마 삭제(완전 삭제)
TRUNCATE: 데이터, 디스크 사용량 삭제(초기화)
DELETE: 데이터 삭제
* TRUNCATE, DROP은 로그를 남기지 않음
제약 조건
1) PK
오라클: NOT NULL 자동 제약
SQL Server: 명시적으로 NOT NULL 제약
2) UNIQUE
오라클: NULL값 얼마든지 입력
SQL Server: NULL값도 하나만 입력
DCL
1) 로그인: CREATE SESSION
2) 테이블 생성: CREATE TABLE - 자신 스키마에 테이블 생성
CREATE ANY TABLE - 모든 스키마에 테이블 생성
* CTAS는 NOT NULL 제약만 생성
3) 행 삽입: QUOTA 100M - 100MB 한도 설정
QUOTA UNLIMITED - 무제한 권한 설정
UNLIMITED TABLESPACE - 모든 테이블스페이스에 무제한 권한 설정
객체 권한
1.
- GRANT [권한명] ON [객체명] TO ~
- REVOKE [권한명] ON [객체명] FROM ~
2.
Table: EXECUTE 빼고 전부
View: 선택, 갱신, 삽입, 삭제
Sequence: ALTER, SELECT
Procedure: EXECUTE
3. WITH GRANT OPTION = 오브젝트 권한
WITH ADMIN OPTION = 시스템 권한
부여한 권한을 다른 사용자에게도 부여 가능
ROLE
CREATE ROLE
DROP ROLE
PL/SQL
1. 특징
1) 모듈화
2) 변수, 상수 선언
3) 절차형 언어
4) 에러 정의
5) 성능 향상
6) 통신량 절약
2. 저장 모듈
1) Procedure: SQL 로직과 함께 DB 내에 저장해놓은 명령문의 집합
EXECUTE 명령어로 실행
2) User Defined Function: 다른 SQL문을 통해 호출되고 그 결과를 리턴
3) Trigger: 특정 테이블에 DML문이 수행되었을 때 자동으로 동작하도록 작성
COMMIT, ROLLBACK(TCL) 실행 불가
아래 SQL의 실행 결과는?
SELECT '1000' || (40 - NULL) || '0' - 100 AS R1 FROM DUAL;9900
다음 SQL의 실행 결과는?
[EMP_TBL]
| EMPNO | ENAME |
| 1000 | SMITH |
| 1050 | ALLEN |
| 1100 | SCOTT |
[RULE_TBL]
| RULE_NO | RULE |
| 1 | S% |
| 2 | %T% |
SELECT COUNT(*) CNT
FROM EMP_TBL A, RULE_TML B
WHERE A.ENAME LIKE B.RULE;| EMPNO | ENAME |
| SMITH | S% |
| SCOTT | S% |
| SMITH | %T% |
| SCOTT | %T% |