SQL 명령문
- 순서 정렬: From - where - group by - having - select - order by
- 종류 정렬:
- DML - select, insert ,delete, update
- DDL - alter, create, modify, drop
- TCL - rollback, commit
- DCL - grant, revoke
다음 중 DDL을 찾아라, 다음중 DML을 찾아라 ..
SELECT
distinct: 집약 기능 -> 원하는 정보를 집약시켜줌
distinct deptno,mgr
group by deptno,mgr
AS ★ ★ ★ ★ ★
- SELECT에서는..
- as 생략가능
- 컬럼명에 띄어쓰기 가능 (ex. "직업 번호")
- FROM에서는..
- as 사용불가
concat
- 인수가 반드시 2개이여야 함
- +: SQLs
- ||: oracle (ex. '문자열 ' || '합치기' )
논리연산자
- and: A and B: 둘 다 가짐
- or: A or B: 둘 중 하나 가짐
- not: A, B: 둘 다 안 가짐
연산순위
- NOT
- AND
- OR
ex. NOT <조건> AND <조건> NOT <조건> OR <조건> (나에오)
SQL 연산자
- Between 1 and 2: 1 <= A <= 2
- A IN (1,2,3): A=1 or A=2 or A=3
- LIKE ★★★
- _: 임의의 한 글자
- %: 0개 이상의 글자
- escape: 와일드카드 인 _와 %를 문자로 취급
- ename like "%@_%" escape "@": escape 문자인 @의 다음 문자를 문자 그대로로 인식
- Rownum (orcale) ★★★
- where절까지 만족 시킨 자료에 1부터 순번이 붙음 (즉, 1인 값을 포함)
- ROWNUM = 2이상의 경우는 데이터 추출 불가
- SELECT empno, sal FROM emp WHERE rownum <=3 order by sal
- order by sal 이 가장 마지막에 시행됨!!!!!! 즉, 정렬 전에 rownum에 의한 조건절 시행
- 3개의 행만 가지고 정렬됨
- where절까지 만족 시킨 자료에 1부터 순번이 붙음 (즉, 1인 값을 포함)
- Top(n) <컬럼명> (sql server)
- SELECT절에서 ORDER BY 후 상위 n개를 가져옴
NULL ★★★★★ (5문제 나옴)
- null의 정의
- 부재, 모르는 값
- null의 산술연산
- null + 2 = null (unknown)
- null + null = null
- null의 비교연산
- where 조건에 결과가 null이면 조건을 알 수 없기 때문에 조건이 맞지 않다고 판단하여 False와 동일
- 정렬 상 의미
- oracle: 무한대
- sql server: 마이너스 무한대
- NVL(컬럼, 0): 컬럼 값이 NULL인 경우 0으로 치환
- NVL2(컬럼, '남', '여'): 컬럼 값이 NULL이 아닌 경우 '남', NULL인 경우 '여'
- ISNULL(컬럼, 0): 컬럼 값이 NULL인 경우 0으로 치환
- NULLIF(ex1, ex2): ex1 == ex2면 NULL, 다르면 ex1 치환
- coalesce (ex1, ex2, ex3, ...): null이 아닌 최초의 표현식으로 치환
정렬 (ORDER BY)
- 정렬의 특징 ★★ (4문제)
- 가장 마지막에 실행
- 성능이 느려질 가능성 있음
- null값과의 관계
- 컬럼 번호(컬럼 순서) 정렬
- 출력되는 컬럼의 수보다 큰 값은 불허
- 인수 두개 정렬
- sal desc, ename asc: sal이 같으면 ename 오름차순
- select ename order by sal: select문에 없어도 정렬 가능
숫자 함수
- Round (n, i): n의 소수점 기준 (i+1)번째에서 반올림한 값을 반환
- ceil(n): n과 같거나 큰 최소 정수 반환 - oracle
- celing(n): n과 같거나 큰 최소 정수 반환 -SQLs
문자열 함수
- UPPER, LOWER: 대소문자 변경
- Lpad, Rpad, LTrim, RTrim, substr, nstr : 실습만 해봐~
날짜 함수
- To char, To_date: 실습만 해봐~, 형변화
- sysdate(orcale), getdate (sql server)
- 날짜 데이터 + 100: 100일 이후, 즉 default는 day값으로 인식
DECODE/CASE
- decode(A, B, '1', null): A가 B일 경우 '1'을, 아닐 경우 null(생략 가능) 반환
- case
Case
when than #1
when than #2
else #가 존재하지 않음
end
-> 1번, 2번 모두 해당이 안 될 경우 null값으로 출력
집계 함수 ★★★ (2~3문제)
- 값들의 집합을 계산해서 하나의 값을 리턴
- COUNT(*)을 제외하고, 집계 함수는 NULL값을 무시
- null과의 관계
- sum(A) + sum(B) + sum(C) vs sum(A+B+C)
GROUP BY
- 집약 기능
- 그룹 수준의 정보를 바꿈
- Having: Group에 대한 조건식
JOIN
- natural join
- 동일한 타입과 이름을 가진 컬럼을 조인 조건으로 이용하는 조인을 간단히 표현하는 방법
중복된 컬럼이 하나만 출력- 중복된 컬럼이 제일 앞에 등장
- alias 사용 안 됨
- using
- 같은 이름을 가진 칼럼들 중에서, using 으로 지정한 컬럼에 대해서만 선택적으로 join 진행
- 중복된 컬럼이 제일 앞에 등장
- alias 사용 안 됨
- left outer join
- ex. A left outer join B = A.col1 = B.col(+) : left 와 +가 같은 의미!! 그리고 반대임!!!!
- 조인 순서
- FROM A,B,C: A와 B를 조인한 후, 조인한 테이블과 C를 조인
서브쿼리 : 들어가는 위치에 따라 다름
- select: 단일 행 서브쿼리 중 하나인 scalar(한 행, 한 컬럼만을 반환하는 서브쿼리)
- from: inline view(메인쿼리에 컬럼 사용 가능, 테이블 명이 올 수 있는 곳에서 사용 가능)
- where: 거의 모든 서브쿼리 다 들어감 = 중첩서브쿼리
- group by: 서브쿼리 안 들어감!!
- having: 거의 모든 서브쿼리 다 들어감 = 중첩서브쿼리
- order by: scalar 서브쿼리만 쓸 수 있음
- in(): 메인 쿼리 한 데이터에 대해 서브쿼리를 다 실행
- any/some(): 하나라도 만족하면 True
- all(): 모두 만족해야 True
- exist(ex. '1', 'x', 'a'): 존재하면 True, 0 rows면 False
집합연산자 - 잘은 안 나오지만 의미는 다 알고 있자
- union: 정렬 작업 O, 느림
- intersect: 정렬 작업 O, 느림
- minus (except): 정렬 작업 O, 느림
- union all: 중복 데이터 존재 가능, 정렬 작업 X, 빠름
- union vs union all 중에 union all 이 더 빠름
DDL (vs TCL과 비교하며 공부하기)
- Truncate: 구조만 남고 데이터 삭제, rollback, commit 불가능
- Drop: 구조, 데이터 전부 삭제, rollback, commit 불가능
- delete: rowe단위로 데이터 삭제, rollback, commit 가능
- Truncate vs Drop
- Truncate vs delete
DML
- insert: TCL commit, rollback과 연관지어 나옴
- update: TCL commit, rollback과 연관지어 나옴
- delete: TCL commit, rollback과 연관지어 나옴
- merge: 신유형 37회~
제약 조건 ★★★
- PK: unique + Not null, 하나만 존재
- FK: 참조하는 테이블에서 존재하는 값만 사용 가능
- UNIQUE: 중복성 배제, 유일한 값으로 존재
- NOT NULL: 필수 입력 사항
- CHECK: 주어진 조건에 해당하는 값만 입력 가능
DCL - 특징 알고 있자
- GRANT
- 데이터베이스 사용자에게 객체에 대한 권한을 부여할 때 사용
- GRANT _ ON _ TO _ (그온투)
- REVOKE
- 데이터베이스 사용자에게 객체에 대한 권한을 회수할 때 사용
- REVOKE _ ON _ TO _(리온투)
- ROLE
- 사용자와 권한 사이에 중개 역할 수행(사용자에게 허가 할 수 있는 권한들의 집합)
- 명령어 아니고 객체(object)의 하나임 특징 다섯개 알아두자
- 권한 부여와 회수를 쉽게 할 수 있음
- CREATE ROLE: 권한을 가진 user에 의해 생성
- 한명의 사용자가 여러 개의 role을 접근 할 수 있고, 여러 사용자에게 같은 role 부여 가능
- 시스템 권한을 부여하고 취소할 때 동일한 명령을 사용해서 사용자에게 부여 및 취소 가능
- 사용자는 role에게 role 부여 가능
VIEW - (독편보)
- 독립성: 기존 테이블의 구조가 변경되면 뷰의 구조도 같이 변경됨
- 편리성: 테이블을 조작할 필요 없음, 쓸 때 마다 나옴
- 보안성: 원하는 정보만 가지고 만들 수 있음 (숨길 수 있음)
* 기존 테이블보다는 용량이 적게 필요함
그룹함수 ★★★★ - 각 비교
- ROLLUP
- 소그룹 간의 소계를 계산
- roll up (A,B) != roll up (B,A)
- CUBE
- GROUPBY 항목들 간 다차원적인 소계를 계산할 수 있음 (모든 그룹케이스를 계산함)
- cube (A,B) == cube (B,A)
- grouping sets
- 특정 항목에 대한 소계를 계산할 수 있음
- 특정 항목을 그룹한 다수의 SQL들을 UNION한 것과 같음
* 빵꾸 뚫어서 어떤 걸 써야 하는지.. 행의 수가 적어보이면 roll up, 행의 수가 많아보이면 cube
TCL
- commit
- rollback
- auto commit off 와 begin transaction commit 기능 없앤 것
윈도우 함수 - 무조건 문제 많이 풀기!!!
- Rows vs. RANGE: 결과값 차이점, 즉 같은 값이 있는가(범위가 같기 때문에 같은 값이 있을 수 있음)
- RANK vs. DENSE RANK: RANK는 중복 건너 뜀, DENSE RANK는 건너 뛰지 않음
- Partition by vs. order by
계층형 질의 ★
- 계층형 데이터: 동일한 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터(ex. 사원테이블의 직급)
- prior 자식 데이터 = 부모 데이터 (프자부)
- '부모데이터 = Prior 자식데이터' 와 같은 의미
- 부모에서 자식으로 가는 경우 순방향 (부자순)
절차형 PL/SQL
- Exception 은 생략 가능
- procedure, trigger, userdefined function 차이점
데이터 모델링
엔터티
- 업무에 필요하고 유용한 정보를 저장하고 관리하기 위한 집합 (관리하고자 하는 대상)
- 엔터티 특징
- 한 개의 엔터티는 두 개 이상의 인스턴스 집합이여야 함
- 한 개의 엔터티는 두 개 이상의 속성을 가짐
- 한 개의 속성은 한 개의 속성값을 가짐
- 관계는 한 개 이상 가짐
- 엔터티 분류
- 유형, 개념, 사건 엔터티 (유개사)
- 기본, 중심, 행동 엔터티 (기중행)
속성
- 관리하고자 하는 대상인 인스턴스의 특징, 고유한 성질
- 분류 정의!!!!! (기설파)
- 기본 속성: 기본속성은 업무로부터 추출한 모든 속성이 해당되며, 가장 일반적이고 많은 속성을 차지함
- 설계 속성: 업무상 필요한 데이터 이외에 데이터 모델링을 위해, 업무를 규칙화 하기 위해 속성을 새로 만들거나 변형하여 정의함
- 파생 속성: 다른 속성에 영향을 받아 발생하는 속성으로, 보통 계산된 값들이 이에 해당됨
도메인 ★★★
- 각 속성이 가질 수 있는 값의 범위
- 데이터 유형, 크기, 제약 조건
- check, primary key 등등 값의 범위..
관계
- IE 표기법
- Bar ker
식별자
- 하나의 엔터티에 구성되어 있는 여러 개의 속성 중에서 엔터티를 대표할 수 있는 속성을 의미함
- 하나의 엔터티는 반드시 하나의 유일한 식별자가 존재해야 함
- 식별/비식별자 한계 (ERD)
- -------: 비식별자
- -------(실선): 식별자
- 주식별자의 특징 (유최불존) ★
- 유일성: 주식별자에 의해 엔터티 내에 모든 인스턴스들은 유일하고 구분함
- 최소성: 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 함
- 불변성: 주식별자가 한 번 특정 엔터티에 지정되면 그 식별자의 값은 변하지 않아야 함
- 존재성: 주식별자가 지정되면 반드시 데이터 값이 존재해야 함 (NULL값 안 됨)
식별자 관계, 비식별자 관계
- 식별자 관계: 자식엔티티의 주식별자로 부모의 주식별자가 상속이 되는 경우
- 비식별자 관계: 부모엔티티로부터 속성을 받았지만 자식엔티티의 주식별자로 사용하지 않고 일반적인 속성으로만 사용하는 경우
식별자 관계: 1. 강한 관계, 2. 단점- SQL 구문 복잡, PK 속성수 증가
비식별자 관계: 약한 관계, 단점- 조인이 많아 느려짐
3. ERD
ERD 서술 규칙
- 시선 좌상단 -> 우하단
- 관계명 반드시 표기 안 해도 됨
- UML은 객체지향 모델에서만 쓰임
성능 데이터 모델링
- 성능 향상을 목적으로 설계 단계의 데이터 모델링 때부터 성능 관련된 사항이 데이터 모델링에 반영될 수 있도록 함
- 아키텍쳐를 바꾸는 방법: 테이블을 자르거나 파티셔닝 등등..
- SQL의 명령문
- 조인 수행 원리 ★★★★
- optimizer
- 실행 계획
정규화 ★★★★★★★★★
- 방법
- 1차 정규화: 테이블의 컬럼이 원자값(하나의 값)을 갖도록 테이블을 분해하는 것
- 2차 정규화: 1정규화를 진행한 테이블에 대해 완전 함수 종속을 만족하도록 테이블 분해하는 것
- 3차 정규화: 2정규화를 진행한 테이블에 대해 이행적 종속을 없애도록 테이블을 분해하는 것
- BCNF 정규화: 3정규화를 진행한 테이블에 대해 모든 결정자가 후보키가 되도록 테이블을 분해하는 것
- 4차 정규화: 다중 값 종속성을 제거하는 것
- 5차 정규화: 조인 종속성을 제거하는 것
- 이상 현상 예시 봐둬!
- 삽입 이상: 원하지 않는 데이터가 삽입되는 현상
- 삭제 이상: 하나의 자료를 삭제하고 싶지만, 그 자료가 포함된 튜플 전체가 삭제되는 현상
- 갱신 이상: 일부만 변경하여 데이터가 불일치하는 현상
- 성능 : select에서 성능이 안 좋아지고(join 때문에), insert, update에서는 성능이 좋아짐
반정규화 ★★
- 데이터 무결성이 해침!!!
- 대량 범위, 범위 처리, 통계처리 (대범한 통조림)
- 응용 시스템 변경, 클러스터링, 뷰, 인덱스 (응클뷰인) -> 를 해보고 안 되면 반정규화 시행
- 테이블(병합, 분할) , 속성(파생, 오류, 이력컬럼, PK->일반속성, 중복속성), 관계(중복관계 추가) (태속관) ★★★ 정의, 특징 암기
- 테이블 반정규화
- 테이블 병합
- 1:1 관계 테이블 병합: 1:1 테이블을 병합하여 성능 향상
- 1:M 관계 테이블 병합: 1:M 테이블을 통합하여 성능 향상
- 슈퍼/서브 테이블 병합: 슈퍼/서브 관계를 통합하여 성능 향상
- 테이블 분할
- 수직 분할: 컬럼 단위의 테이블을 1:1로 분리하여 성능 향상
- 수평 분할: row 단위로 집중 발생되는 트랜잭션을 분석하여 테이블을 분할
- 테이블추가
- 중복테이블 추가: 다른 업무 또는 서버가 틀린경우 동일한 테이블 추가 (원격 조인 제거)
- 통계테이블 추가: 집계함수 등을 미리 수행하여 계산해둔 테이블을 추가 (쿼리 수행시에 계산하지 않음)
- 이력테이블 추가: 마스터 테이블에서 자주 조회되는 레코를 중복하여 테이블 추가 (범위처리 최소화)
- 부분테이블 추가: 자주 이용하는 집중화된 칼럼이 있는경우 디스크 I/O를 줄이기위해 별도의 컬럼만 테이블 추가
- 테이블 병합
- 속성(컬럼) 반정규화
- 중복칼럼 추가: 조인시 성능저하를 예방하기위해, 중복되는 컬럼을 위치시킴
- 파생컬럼 추가: 트랜잭션이 처리되는 시점에 계산에 의해 발생되는 성능저하를 예방하기 위해 미리 계산하여 컬럼에 보관
- 미리 계산한 컬럼 sum , sumtoValue
- 이력테이블 컬럼 추가: 대량의 이력데이터 처리시 기능성컬럼(최근값, 시작일자, 종료일자) 를 추가
- 관계 반정규화
- 중복관계 추가: 여러경로를 거쳐 조인 할 수 있지만, 성능저하를 예방하기위해 추가적인 관계를 맺는 방법(중복 FK)
* 테이블,컬럼 반정규화는 데이터 무결성에 영향을 끼치지만, 관계의 반정규화는 데이터의 무결성을 깨뜨리지 않는다
대량 데이터에 따른 성능
- Row Migration: 수정된 데이터를 해당 데이터 블록에서 저장하지 못 하고 다른 블록의 빈 공간을 찾아 저장
- Row Chaining: row 길이가 너무 길어서 하나의 블록에 저장할 수 없어 여러 블록에 걸쳐 저장
- 해결 방법: partitioning
- 논리적으로는 하나의 테이블이지만 물리적으로는 여러개의 테이블로 분리하는 것
- list partitioning: 특정 값을 기준으로 분할
- range partitioning: 테이블의 컬럼 중에서 값의 범위를 기준으로 여러 개의 파티션으로 데이터를 나누어 저장
- 가장 많이 쓰이고 관리하기 편함
- hash partitioning: 내부적으로 해시 함수를 사용해 데이터를 분할
- 관리하기 어려움
슈퍼/서브타입 - 다시
- 용량 별로 처리 가능
- 큰 경우: 트랜잭션 유형으로 분류 (아래 참고)
- 작은 경우: one to one, 트랜잭션이 개별로 들어감
- 트랜잭션 유형에 따라
- 공통/차이
- 전체 통합
분산 데이터베이스
- 데이터베이스를 여러 개의 서버로 뜯어둠
- 데이터 무결성을 해칠 수 있음
조인 수행 원리 - 문제 많이 풀어 4문제 정도 나옴 ★★★★
- NL join
- 프로그래밍에서 사용하는 중첩된 반복문과 유사한 방식으로 조인 수행
- 랜덤 방식으로 데이터 액세스
- 대용량 sort 작업 시 유리
- 조인 성공 시 조인 결과를 바로 보여줌
- sort merge join
- 조인키 기준으로 정렬, 동기 비동기
- 스캔 방식으로 진행
- hash join과 달리 동등 조인 뿐만 아니라 비동등 조인에 대해서도 조인 작업 가능
- hash join
- 등가 조인에서만 사용 가능
- 선행 테이블 작음
- hash처리 때문에 별도 공간 필요함
인덱스
- 원하는 데이터를 쉽게 찾을 수 있또록 돕는 책의 찾아보기와 유사한 개념
- 언제 사용되는지
- 부정형, LIKE, 묵시적 형변환 일 때 안 쓰임
- 인덱스 사용시 성능 느려짐
- DML(INSERT, UPDATE, DELETE) 성능 나빠짐
실행 계획 ★★
- 사용자가 SQL을 실행하여 데이터를 추출하려고 할 때 옵티마이저가 수립하는 작업절차를 뜻함
- 실행 순서: 들여쓰기 ★★★★★
정규화, pivot, unpivot, merge
반응형