엔티티 관계
- O : 있을수도 있고, 없을수도 있다는 의미
성능 데이터 모델링; 성능을 고려한 데이터 모델링
1. 정규화를 수행하여 데이터베이스 모델의 유연성을 확보한다.
2. 데이터베이스의 전체 용량, 월간, 연간 증감율을 예측한다.
3. 애플리케이션의 트랜잭션의 유형(CRUD)을 확인한다.
4. 합계 및 정산 등을 수행하는 반정규화를 수행한다. (성능 향상을 위한 튜닝)
5. 기본키와 외래키, 수퍼타입과 서브타입 등을 조정한다.
6. 성능관점에서 데이터 모델을 검증하고 확인한다.데이터 모델링 과정
데이터 모델링 3종
- 개념적 모델링
- ERD를 만드는 과정
- 사용자 관점에서 데이터 요구사항을 식별함
- 논리적 모델링
- ERD를 통해서 스키마 모델을 만드는 과정
- M:N 관계 해소, 식별자 확정, 정규화, 무결성 정의 등을 수행
- 물리적 모델링
- 관계 스키마 모델의 물리적 구조를 정의하고 구현하는 과정
- 데이터가 물리적으로 저장되는 방법을 정의
정규화의 목적
- 데이터의 정규화
- 함수의 종속성에 따라서 테이블을 분해하는 과정
- 정규화의 목적
- 데이터 중복 제거
- 모델의 독립성 향상
- 갱신 이상 방지(데이터 삽입, 삭제, 수정에 이상 현상이 있는것)
속성
- 기본키 속성
- 엔티티 식별 속성(엔티티를 대표함)
- 최소성(not null)과 유일성(중복 없음)을 만족
- 외래키 속성
- 다른 엔티티의 관계에 포함되는 속성
- 두 개의 테이블 간 연결을 설정하기 위한 key
- 일반 속성
- 다른 엔티티 관계에 포함되지 않는 속성
- 파생 속성
- 다른 특정 데이터를 기반으로 만들어진 데이터
- 예. 가격의 평균 데이터(가격 데이터 기반으로 만듬)
엔티티 특징
- 식별자
- 엔티티는 유일한 식별자가 있어야 한다.
- 인스턴스 집합
- 엔티티는 2개 이상의 인스턴스(데이터)가 있어야 한다.
- 예. 회원 정보는 2명 이상 있어야 한다.
- 속성
- 엔티티는 반드시 속성을 가지고 있다.
- 관계
- 엔티티는 다른 엔티티와 최소 한 개 이상의 관계가 있어야 한다.
- 예. 회원 엔티티 => 회원 배송지 주소 엔티티, 회원 등급 엔티티 등
- 업무
- 엔티티는 업무에서 관리되어야 하는 집합이다.
- 예. 회원, 계좌
IE 표기법
- 1:N 관계에서 N쪽에 새발을 표시
- 필수 참여 관계에서 선택 참여는 O, 필수 참여는 |로 표시
- 다른 표기법: Barker, IE Notation, IDEF1X
식별자의 특징
- 유일성
- 주식별자에 의해 엔티티 내의 모든 인스턴스(데이터)들이 유일하게 구분되어야 한다.
- 데이터의 중복이 없다는 의미
- 최소성
- 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수이어야 한다.
- 주식별자는 하나의 속성만으로 이루어져야 한다는 의미
- 불변성
- 주식별자 값은 자주 변하지 않는 것이어야 함
- 존재성
- 주식별자가 지정이 되면 반드시 값이 들어와야 한다.
- NOT NULL
식별자 분류
CONNECT BY 함수
- CONNECT BY 함수
- 해당 함수는 계층적(상하관계)으로 연결되어 있는 데이터의 관계를 출력할 때 사용한다.
start with로 시작점을 지정하고,connect by로 (아마도 같은 테이블 내에서)연결된 계층 관계를 이어나가며 출력한다.
- 구문 구성
- WHERE
- START WITH : 어떤 데이터로 계층 구조를 지정하는지
- CONNECT BY : 각 행들의 연결 관계를 설정
- LEVEL
- CONNECT BY 절의 결과에 생기는 컬럼명
- PRIOR
- 현재 행과 다른 행을 구분하는 키워드
- 해당 키워드가 붙은 값과 같은 값을 갖는 모든 행을 가져온다
<>
- 같지 않음을 의미하는 부정 연산자
- A <> B 일 때, A를 기준으로 한다.
!=,^=,NOT 컬럼명 =들도 같은 의미의 연산자이다.
DCL; Data Control Language
- GRANT: 권한 부여
- REVOKE: 권한 회수
SQL 명령어
JOIN
NULLIF(A, B)
- A, B 값이 같으면 NULL, 같지 않으면 첫 번쨰 값을 반환함
COUNT 함수
- COUNT()의 인자로 컬럼명을 지정할 경우 NULL인 값을 제외하고 갯수를 카운트함
- COUNT(*)의 경우에는 NULL을 포함한 갯수를 반환함
- 조건절(WHERE)이 거짓이면 0을 반환함
집계 함수 - ROLLUP, CUBE, GROUPING SETS
- GROUP BY와 사용되는 키워드
- ROLLUP(COL1, COL2)
- GROUP BY로 묶은 소그룹 COL1 각각의 합계, 그리고 소그룹 전체(COL1, COL2 모두 포함)의 합계를 모두 구함
- 출력 순서
- 소그룹의 결과들 마지막에 해당 소그룹 합계 출력
- 맨 마지막에 전체 합계 출력
- CUBE
- GROUP BY로 묶은 소그룹 COL1, 그리고 COL@ 각각의 합계, 그리고 소그룹 전체(COL1, COL2 모두 포함)의 합계를 모두 구함
- 출력 순서
- 맨 처음에 전체 합계 출력
- COL2에 대한 합계들 출력
- COL1 소그룹 결과들과 해당 소그룹 결과 마지막에 합계 출력
- GROUPING SETS
- 출력 순서
- COL1에 대한 합계들 출력
- COL2에 대한 합계들 출력
- 출력 순서
- GROUPING SETS 함수와 GROUP BY UNION ALL 절
- 양변의 치환을 자유롭게해서 같은 구문으로 만드는 연습 하기
- 양변의 치환을 자유롭게해서 같은 구문으로 만드는 연습 하기
조건문 - WHEN ~ THEN ~ ELSE ~ END
- END 뒤에는 별칭을 붙일 수 있다.
NVL 함수
- NVL(COL1, VALUE)
- COL1이 NULL인 경우 VALUE로 치환한다.
- NVL2(COL1, VALUE1, VALUE2)
- COL1이 NULL이 아닌 경우 VALUE1로 치환하고, NULL인 경우 VALUE2로 치환한다.
WINDOW FUNCTION(이해 부족)
- OVER()
- ORDER BY, GROUP BY 서브쿼리를 개선하기 위한 함수
- 쿼리 결과 집합을 사용자 지정 정렬하는 느낌
- OVER() 함수 내에 ORDER BY, PARTITION BY를 사용할 수 있음
- OVER 함수 구조
OVER ( [ <PARTITION BY clause> ] [ <ORDER BY clause> ] [ <ROW or RANGE clause> ] ) <PARTITION BY clause> ::= PARTITION BY value_expression , ... [ n ] -- 쿼리 결과 집합을 파티션으로 분할 <ORDER BY clause> ::= ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ ,...n ] -- 각 파티션 내에서 행의 논리적 순서 정의 <ROW or RANGE clause> ::= { ROWS | RANGE } <window frame extent> -- 파티션의 시작점 및 끝점 지정- UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING
- ROW or RANGE clause에 쓰이는 키워드
- UNBOUNDED PRECEDING : 윈도우의 시작 위치가 첫번째 ROW
- UNBOUNDED FOLLOWING : 윈도우의 마지막 위치가 마지막 ROW
PL/SQL
- Oracle에서 사용하는 확장된 SQL 언어
- 절차형 언어
- 내부에서 테이블 생성 가능
- 테이블 생성의 이유는 임시 테이블로 잠깐 사용하는 경우가 많음
- 조건문은 IF ~ THEN ~ ELSE IF ~ END IF와 CASE ~ WHEN을 사용한다.
- 변수에 값을 대입할 경우 ":="을 사용한다.
인덱스 구문
인덱스 생성 / 삭제 구문
-- 생성
CREATE INDEX 인덱스명
ON 테이블명 ( 속성명 , 속성명,…)
-- 삭제
DROP INDEX 인덱스명
ON 테이블명
-- 수정
인덱스 삭제 후 다시 만들어주는 방법을 사용해야 한다
-- 인덱스 조회
SELECT 테이블명, 인덱스명, 컬럼명
FROM ALL_IND_COLUMNS
WHERE TABLE_NAME = '테이블명'COMMIT, ROLLBACK
- COMMIT 시점으로 ROLLBACK
SAVEPOINT, ROLLBACK TO
- SAVEPOINT [세이브포인트 명] : 세이브 포인트로 저장
- ROLLBACK TO [저장된 세이브포인트 명] : 저장된 곳으로 돌아감
DAUL 테이블
- 쿼리 결과를 담는 임시 테이블
MINUS
- 차집합 함수
- 사용 예
SELECT * FROM TABLE1 -- 1,2,3 행이 있는 테이블 MINUS SELECT 1 FROM DUAL -- 1행이 있는 임시 테이블 -- 출력 : 2,3
ANY
- 다수의 비교값 중 하나라도 만족하면 TRUE
인덱스에 대한 연산
- 인덱스에 대해 연산을 하면 인덱스가 변형되어 인덱스를 사용할 수 없음.
분산 데이터베이스
- 정의
- 네트워크를 통해서 여러개의 데이터베이스를 물리적으로 분리한 데이터베이스
- 장단점
- 가용성 : 시스템이 서비스를 정상적으로 제공할 수 있는 상태
실행 계획
- 정의
- SQL을 실행하기 위한 절차와 방법을 의미
- 옵티마이저
- SQL의 실행계획을 수립하고 실행하는 관리 시스템의 소프트웨어
- 설명
- SQL이 데이터베이스에서 실행될 때 실행 절차 및
방법을 표현하여 DBA에게 알려준다. - 옵티마이저의 종류를 확인할 수 있는 RULE, COST가 표현되고 SQL이 내부적으로 어떤 방식으로 실행되었는지 확인 할 수 있다.
- SQL이 데이터베이스에서 실행될 때 실행 절차 및
인덱스 설명
- 인덱스 종류
- 순차 인덱스, 결합 인덱스, 비트맵, 클러스터, 해시 인덱스
- 인덱스 생성 가능 변수 타입
- VARCHAR, CHAR, DATE, NUMBER 가능
- 파티션 인덱스
- 파티션 테이블의 파티션 키에 대해 생성하는 인덱스
- GLOBAL 인덱스라고도 한다.
- 인덱스 단점
- 인덱스 수가 증가하면 입력, 삭제, 수정 등 속도 저하 유발
- 인덱스 시작 숫자
- 인덱스는 0부터 시작
NULL 연산
- 숫자와 NULL 의 덧셈
- 숫자와 NULL을 더하면 NULL
ROWNUM
- ROWNUM은 논리적인 순번을 매기기 위해 사용하는 키워드
- 조건에 부합하면 ROWNUM = 1 상태로 순번이 매겨지기 시작함.
- 때문에 ROWNUM = 1은 가능하지만, 2이상은 등호 연산자로는 호출할 수 없음(부등호를 사용해야한다.)
연산자 우선순위
SELF JOIN
- 동일한 테이블에서 발생하는 JOIN
- 동일 테이블 내에서 두 개의 칼럼 간 JOIN을 수행
- FROM절에 동일한 테이블(본인)을 사용해야하므로 Alias를 사용해야한다.
-- [SELF JOIN] SELECT A.칼럼명, B.칼럼명 FROM 테이블1 A, 테이블2 B WHERE A.칼럼명2 = B.칼럼명1
CROSS JOIN
- CROSS JOIN의 결과는 두 테이블의 행의 개수를 곱한 개수가 된다.
- 테이블의 각각의 행이 다른 테이블의 모든 행을 조인시킨다.
LIKE 활용
--A로 시작하는 문자를 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE 'A%'
--A로 끝나는 문자 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '%A'
--A를 포함하는 문자 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '%A%'
--A로 시작하는 두글자 문자 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE 'A_'
--첫번째 문자가 'A''가 아닌 모든 문자열 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE'[^A]'
--첫번째 문자가 'A'또는'B'또는'C'인 문자열 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '[ABC]'
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '[A-C]'FK 선언
- REFERENCES 참조할_테이블 (참조할_컬럼)
- ON DELETE CASCADE 옵션 : 참조되는 부모 테이블의 행에 대한 DELETE를 허용(같이 삭제)
- ON DELETE SET NULL 옵션 : 참조되는 부모 테이블의 행에 대한 DELETE를 허용(삭제 시, 자식은 NULL로 변환)
SQL 기본 실행 순서
- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
순수 관계 연산자
- 순수 관계 연산자: 관계형 데이터베이스에 적용할 수 있도록 개발한 관계 연산자를 의미
순서 부여 함수
- ROW_NUMBER() : 중복값이 있어서 고유 등수 부여(1, 2, 3, 4)
- RANK() : 중복값에 중복 등수를 매기고 다음 등수는 건너뜀(1, 1, 3, 4)
- DENSE_RANK() : 중복값에 중복 등수를 매기고 다음 등수로 건너뛰지 않음(1, 1, 2, 3)
테이블 복사 키워드
ORACLE : AS
SQL server : INTO
예시
--[ORACLE] CREATE TABLE TEAM_EMP AS SELECT * FROM EMP; --[SQL server] SELECT * ( ) FROM EMP;