SQLD 정리

 

키워드로 문제풀기

 

엔티티 개념

엔티티 -> 테이블(릴레이션)

인스턴스 -> 행(튜플)

속성 -> 열

속성값

 

엔티티는 두 개 이상의 인스턴스의 집합으로 이루어짐

엔티티는 다른 엔티티와 최소 한 개 이상의 관계가 있어야 함

한 개의 엔티티는 두 개 이상의 속성을 갖는다

 

이름에 약어 사용은 자제

속성 명칭은 전체 데이터모델에서 유일성을 확보하는 것이 좋다

하나의 속성은 한 개의 속성값을 갖는다

 

 

엔티티 종류

 

유개사

기중행

 

 

ERD 표기(개속관)

□ 개체	-> 테이블
○ 속성	-> 열
◇ 관계	-> PK, FK

 

 

모델링 순서(개논물)

개념적 데이터 모델링(ERD) -> 논리적 데이터 모델링(표) -> 물리적 데이터 모델링

 

 

도메인 : 제약조건(타입, 길이 등

카디널리티 : M:M, M:1, 1:1 ...

 

역정규화 : 정규화된 테이블에서 컬럼 조작 통해 성능 향상시킴

 

 

 

식별 관계

부모 테이블의 기본키 or 유니크 키를 자식 테이블이 자신의 기본키로 사용하는 관계

강한 연결 관계

 

 

비식별 관계

부모 테이블의 기본키 or 유니크 키를 외래키로 사용하는 관계

약한 연결 관계

 

 

관계의 표기법

-관계명

-관계차수(1:1, 1:M, M:N)

-식별성(필수관계, 선택관계)

 

 

키 종류

1. 슈퍼키

각 행을 유일하게 식별할 수 있는 하나 또는 그 이상의 속성들의 집합

유일성만 만족하면 된다

 

2. 후보키

각 행을 유일하게 식별할 수 있는 최소한의 속성들의 집합

유일성, 최소성을 동시 만족해야한다

 

3. 기본키(unique & not null)

최소성과 유일성을 만족한다

NULL값을 가질 수 없다

테이블당 한 개만 지정 가능하다

 

4. 대체키

기본키로 선정되지 않은 후보키

 

5. 외래키

다른 테이블을 참조하여 테이블간의 관계를 연결
null값 가질 수 있음

한 테이블에 여러개 가능

 

 

 

데이터베이스 스키마 구조 3단계

-개념 스키마 : 전체적인 뷰

-내부 스키마 : 물리적 구조

-외부 스키마(=서브 스키마) : 사용자 뷰

 

 

데이터 모델링 유의점

-중복

-비유연성

-비일관성

 

속성의 특성에 따른 분류

-기본속성

-설계속성 : 코드성 데이터

-파생속성 : 다른 속성에서 계산된 값

 

 

정규화

1차 정규화 : 모든 속성은 반복형태가 있어서는 안 됨

2차 정규화 : 모든 속성은 반드시 기본키에 종속돼야 함

3차 정규화 : 기본키가 아닌 모든 속성 간에는 서로 종속될 수 없음

 

상세설명 blog.naver.com/PostView.nhn?blogId=mjsolar&logNo=130109454313

 

 

DB에는 트랜잭션 개념이 있어 commit 전에는 DB에 반영 안 됨

 

 

SQL 종류

DML(데이터 조작어)	select,insert, update, delete(행단위)
DDL(데이터 정의어)	create, drop, alter, rename(테이블, 열단위)
DCL(데이터 제어어)	grant, revoke(권한)
TCL(트랜잭션 제어어)	commit, rollback

롤백

- 커밋 이전에 변경사항 취소 가능

- savepoint 형성시 savepoint 이전 값으로 돌아간다

 

트랜잭션

데이터베이스의 논리적 연산단위

 

파티셔닝

논리적으로는 하나의 테이블이지만

물리적으로는 여러 개의 테이블로 분리하는 기법

 

트랜잭션 4가지 특성

원자성	트랜잭션이 모두 성공하거나 모두 실패해야한다
일관성	실행 전 잘못된 내용이 없으면 실행 후에도 잘못된 내용이 없다
고립성	실행 도중 다른 트린잭션의 영향 받아 잘못된 결과가 나오면 안 된다
지속성	트랜잭션이 성공하면 영구적으로 저장된다

 

null 찾아내기

is not null

is null

 

 

같지 않다

!=

<>

^=

 

 

오라클 sql 서버 차이

	오라클	sql 서버
''	null로 인식	빈 문자열로 인식
length(' ')	1	0
null	가장 큰 값으로 인식	가장 작은 값으로 인식
기본값	DDL만 오토커밋	DDL, DML 오토커밋
auto commit false	DDL만 오토커밋	오토커밋 x

 

 

 

distinct와 groupby 사용 시 select 절에 있는 값으로만 정렬 가능하다

 

 

select 실행 순서

from -> where -> groupby -> having -> select -> orderby

 

group by 절에 having으로 조건을 걺

 

null관련 함수

NVL=ISNULL=IFNULL(value1, value2)

value1 값이 null이면 value2값 출력

*만약 null이면~

 

NULLIF(value1, value2)

value1=value2면 null 출력

아니면 value1 출력

*null을 출력해라 만약~

 

 

 

순위함수

rank() : 동일 값, 동일순위 중간 비움

SELECT 컬럼1, 컬럼2, 컬럼3,
RANK() OVER (ORDER BY 기준_컬럼 DESC) AS 별명
FROM 테이블;

 

dense_rank() : 동일 값, 동일 순위 중간 안 비움

SELECT 컬럼1, 컬럼2, 컬럼3,
DENSE_RANK() OVER (ORDER BY 기준_컬럼 DESC) AS 별명
FROM 테이블;

 

row_number() : 무조건 순서대로 반환, 중복순위 없음

SELECT 컬럼1, 컬럼2, 컬럼3,
ROW_NUMBER() OVER (ORDER BY 기준_컬럼 DESC) AS RANK
FROM 테이블;

over 뒤의 컬럼을 기준으로 순위를 매긴다

 

 

 

decode(컬럼명, 조건, 참일 때 출력될 값, 거짓일 때 출력될 값)

if-else문과 같다 컬럼이 조건과 같으면 첫번째 값, 다르면 두번째 값을 출력한다

 

 

 

case문

CASE
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  ELSE result
END

if-else구문 여러개와 같다

 

 

 

문자열 합치기(||)

SELECT COUNTRY_ID||COUNTRY_NAME FROM COUNTRIES;

+ 연산과 같다

 

 

 

count(*) - null 포함

count(column_name), sum, avg - null 포함x

 

 

중첩된 그룹함수의 최종결과값은 1건

 

 

 

PK지정하기

CREATE TABLE [테이블명] (
       CONSTRAINT [기본키명] PRIMARY KEY([컬럼명])
)

ALTER TABLE [테이블명] ADD CONSTRAINT [기본키명] PRIMARY KEY ([컬럼명])

 

 

alter 구문

alter table [테이블명] drop column [컬럼명]

alter table [테이블명] add [컬럼명] [테이터형식]

alter table [테이블명] alter [컬럼명] [데이터 유형]

 

 

인덱스 생성

create index [인덱스명] on [테이블명]([컬럼명])

 

 

rename 구문

rename [기존 테이블명] to [새테이블명]

 

 

insert 구문

insert into [테이블명] ([컬럼명]) values ([값])

 

 

truncate	테이블 삭제x. 데이터 삭제. 용량 줄어듦 로그 X
drop	테이블 삭제o 로그 X
delete	테이블 삭제x. 데이터 삭제o. 용량은 그대로 로그 O -> 롤백 가능

 

 

coalesce() : null이 아닌 최초의 값 리턴

 

 

 

 

집합 연산자(union, union all, intersect, minus)

[join과 집합 연산자 차이]

join : 컬럼끼리 합침

집합 연산자 : 레코드끼리 합침, 컬럼 갯수만 같으면 된다

 

union : 중복된 행은 하나만 출력

union all : 중복 제거 x

minus : 왼쪽 결과에서 오른쪽 결과 제외한 값

intersect : 교집합

 

 

 

순수 관계 연산자

select, project, join, divide

 

 

뷰

실제 테이블로부터 작성되는 가상 테이블

실제 테이블처럼 이용 가능

CREATE VIEW 뷰이름[(속성이름[,속성이름])]AS SELECT문;

장점

-독립성

-편리성

-보안성

 

 

도메인

제약조건

 

조인

n개의 테이블 최소 조인 조건 n-1개

 

inner

서로 관계가 있는 레코드

 

outer

서로 관계가 없는 레코드 

 

inner join = join

서로 관계가 있는 레코드 조인

 

left outer join = left join

inner join 후 왼쪽 테이블에 있는 outer를 조인해준다, 빈 값은 null로

 

right outer join = right join

inner join 후 오른쪽 테이블에 있는 outer를 조인해준다, 빈 값은 null로

 

null값이 들어가는 테이블에 (+)값을 붙여준다

 

full outer join

left join

union

right join

left join과 right join의 중복 데이터 한 번만 출력

 

cross join

두 개 테이블을 곱하기로 조인한다

최종 레코드 수는 n*m

카다시안 곱

 

FROM a, b; --> FROM a CROSS JOIN b;
FROM a, b WHERE a.a = b.b; --> FROM a INNER JOIN b ON a.a = b.b;
FROM a, b WHERE a.a = b.b(+); --> FROM a LEFT JOIN b ON a.a = b.b;
FROM a, b WHERE a.a(+) = b.b; --> FROM a RIGHT JOIN b ON a.a = b.b;

 

세미조인

in, exist

 

NL Join = Nested Loop Join	Sort Merge Join	Hash Join
랜덤 액세스	동등, 비동등 조인 가능	등가 조인만 가능
대용량 sort 작업 유리		대량 조인에 유리
인덱스 사용	인덱스 없음	인덱스 없음

 

 

 

단일 행 서브쿼리 
=, <, <=, >, >=, <>

반드시 1건 이하 반환

 

다중 행 서브쿼리
in, all, any, some, exists

2건 이상 반환

 

order by 1,2,3
테이블의 1,2,3번째 칼럼을 기준으로 오름차순 정렬

루트 노드의 level 값은 1이다

cascade
참조하는 값 사라지면 해당 행 사라짐

replace(문자열, 대상문자열, 반환 문자열)
반환 문자열이 없으면 문자열에서 대상문자열을 삭제한다는 것

 

window function

순위, 집계함수

window function으로 결과 건수가 줄진 않음

null 제외 연산

order siblings by
계층 구조는 유지하면서
동일한 부모 둔 자식끼리 정렬하는 것

 

grouping sets(a, b, c)

group by(a) union all

group by(b) union all

group by(c)

 

rollup(a, b)

(a, b) + (a), ()

group by(a, b) union all

group by(a) union all

총집계

 

cube(a, b)

(a, b) + (a), (b), ()

group by(a, b) union all

group by(a) union all

group by(b) union all

총집계

grouping
group by rollup 결과가 null인 행에 1을 반환

group by와 partition by는 용도가 비슷


서브쿼리
1. select절 스칼라 서브쿼리
2. from절 인라인 뷰=다이나믹 뷰
3. where절 중첩 서브쿼리

 

CBO 옵티마이저

규칙기반 옵티마이저는 적절한 인덱스가 존재하면 항상 인덱스 사용함

 

실행계획 = 예상정보

실행계획 읽는 순서

위에서 아래로, 안에서 밖으로

후행 테이블을 먼저 수행한다

 

SQL 처리 흐름도는 SQL 실행 계획을 시각화 한 것

 

인덱스는 조회만을 위한 오브젝트

 

B-TREE 인덱스

루트블록, 브랜치블록, 리프블록

 

BITMAP 인덱스

포인터

 

clustered index

리프 페이지

 

프로시저	트리거
execute 명령어로 실행	자동실행
create procedure	Create Trigger
commit, rollback 가능	commit rollback 불가

 

lag

현재 읽혀진 데이터 위의 값

 

lead

현재 읽혀진 데이터 밑의 값

 

cross join	natural join
where 절에서 조건을 걸 수 있음	where 절에서 조건을 걸 수 없음
join key가 없을 경우 발생	join key는 컬럼명으로 결정
	특정 join 컬럼을 명시적으로 적을 수 없다

 

비교 연산자

=, >, >=, <, <=

 

문자 유형간 비교

 

1. 양쪽 모두 char 타입

-길이가 작은 쪽에 공백 추가 후 비교

-공백수만 다르다면 서로 같은 값

 

2. 한쪽이 char, 한쪽이 varchar

-공백도 하나의 문자 취급

-길이가 다르면 짧은 것이 끝날때까지 비교 후에 긴 것이 크다고 판단

 

row chaining	row migration
하나의 row를 하나의 블럭에 저장할 수 없어 여러 블럭에 걸쳐 저장함	update로 인해 늘어나는 공간을 저장할 공간이 없어  다른 블럭에 row를 옮김

 

prior 자식 = 부모면 순방향

 

 

 

round(10.349, 1) = 10.3

ceil(21.35) = 22

floor(21.35) = 21

해당 위치에서 올리거나 버림

 

select * from emp where sal in(950, 3000, 1250)

-> sal = 950 or sal = 3000 or sal = 1250

 

select * from emp where sal = any(950, 3000, 1250)

-> sal = 950 or sal = 3000 or sal = 1250

 

select * from emp where sal = all(950, 3000, 1250)

-> sal = 950 and sal = 3000 and sal = 1250

 

in, any 하나라도 일치

all 모두 일치

 

in(1,2,null)=in(1,2)

null은 무시