SQLD 헷갈리는 이론 정리

연산 순서 정렬하는 문제

from - where - group by - having - select - order by

​

​

dml> select, insert, delete, update

ddl> alter , create, modify, drop (암묵적 commit됨 그래서 rollback 못함)

tcl> rollback, commit

dcl> grant, revoke

​

as

select

1. as 생략 가능

2. 컬럼명에 띄어쓰기 ex. 직원 번호> "직원 번호"

from

1. as 사용불가

​

​

DML

insert

update

delete

marge

​

DCL

grant

revoke

​

view

독립성 편리성 보안성

​

SUM(A)// 열 계산,

SUM(A+B+C)// 새로운 열 생성함. 행 계산. NULL도 계산해줘서 null이 있으면 무조건 null.

SUM(A)+SUM(B)+SUM(C) VS SUM(A+B+C) 차이 기억

​

​

​

​

컬럼 = 속성

행 = 인스턴스

테이블 = 엔터티

​

모델링 특징

단순화

추상화

명확화

​

데이터 모델링 유의점

중복

비유연성

비일관성

​

데이터 모델링 3요소

대상

속성

관계

​

데이터 모델링 3단계

개념적 - 추상화 수준이 높음

핵심엔터티 추출하는 단계

erd

논리적 모델링

세부속성 식별자 관계 표현하는 단계

정규화 단계

재사용가능

물리적 모델링

데이터베이스 성능 디스크 저장구조 하드웨어 보안성 가용성 등을 고려

​

erd란

멘터티와 엔터티간의 관계를 시각적으로 표현한 다이어그램

​

erd 작성 절차

엔터티 도출

엔터티 배치

엔터티 간의 관계설정

관계명 서술

관계 참여도 기술

관계 필수 여부 확인

​

엔터티란

독립적으로 식별 가능한 객체나 사물

관리하고자 하는 대상

식별가능

2개이상의 인스턴스 집합

2개 이상의 속성을 가짐

하나이상의 관계 가짐

​

​

유형과 무형에 따른 분류

1 유형엔터티

물리적 형태가 있음

2개념엔터티

물리적인 형태 없으나 개념적 정보로부터 구분

3사건엔터티

업무를 수행에 따라 발생

​

발생시점에 따른 분류(발.기중(요)행)ㅋㅋㅈㅅ

1기본엔터티

그 업무에 원래 존재하는 정보

독립적으로 생성

고유한 주식별자 가짐

2중심엔터티

기본 엔터티로부터 발생되고 업무에서 중심적인 역할

3행위엔터티

2개 이상의 부모엔터티로부터 발생

​

​

속성

관리하고 하는 대상(인스턴스)의 특성들

속성은 열, 인스턴스는 행

속성은 인스턴스들의 집합

​

분류

- 기본 속성

- 설계 속성

- 파생 속성

​

1. 식별/ 비식별자

관계 표기법

- ERD

점선> 비식별자

실선> 식별자

​

2. 주식별자

특징

유 최 불 존

- 유일성? 인스턴스를 유일하게 구분

- 최소성? 여러가지 속성을 묶어서도 식별자 가능 , 그게 최소여야 함

- 불변성? 한 번 만들면 바뀌지 않아야 한다

- 존재성? NOT NULL

네 가지 다 만족하면 후보키

, 후보키에서 대표로 선정> 기본키/ 대체키

​

정규화

1. 방법

1차? 원자성 확보

한 속성안에 값은 무조건 하나의 값만 들어가야함 ,사용해서 여러 값 불가능

​

2차? 부분함수 종속성 제거

기본키 전체가 아니라 기본키 일부에 대해 종속될때

예를 들어, 주문 테이블에 '주문 ID'와 '상품 ID'가 기본 키로 구성되어 있고, '상품 이름'이 '상품 ID'에만 종속된다면, 이는 2NF를 위반하는 것입니다.

​

3차? 이행함수 ~

비주요 속성이 다른 비주요 속성에 의해 결정되면 안 됨

예를 들어, '고객 ID'가 기본 키이고, '고객 주소'가 '고객 ID'에 종속되며, '배송 지역'이 '고객 주소'에 종속된다면, '배송 지역'은 '고객 ID'에 이행적으로 종속된 것이므로 3NF를 위반합니다.

​

함수적 종속성

어떤 속성 A의 값에 의해 다른 속성 B도 유일하게 결정되는것

A->B

​

완전 함수적 종속

특정 컬럼이 기본키에 대해 완전히 종속되는것

PK를 구성하는 컬럼이 2개 이상일 경우 pk값 모두에 의한 종속관계를 나타낼때 완전 함수 종속성 만족

​

​

부분 함수적 종속

기본키 전체가 아니라 기본키 일부에 대해 종속될때

​

​

sql 서버는 여러컬럼을 동시에 변경불가,괄호 불가.

​

​

계층형 질의

​

SELECT 컬럼....

FROM 테이블

WHERE 조건 ....

START WITH 조건

CONNECT BY PRIOR 조건 ....

[ORDER BY SIBLINGS BY 컬럼,....]

​

START WITH 절

계층구조 전개의 시작위치를 지정하는 구문

​

CONNECT BY PRIOR

다음에 전개될 자식 데이터를 지정하는 구문

​

PRIOR

CONNECT BY 절에 사용되며, 부모노드를 가리킴

PRIOR 자식 = 부모

부모 ->자식 방향으로 전개되는 순방향 전개

=부모데이터 = PRIOR 자식데이터

PRIOR 부모 = 자식

자식 ->부모 방향으로 전개되는 역방향 전개

 

ORDER SIBLINGS BY

동일한 레벨 사이에서의 정렬 지정

​

LEVEL

계층 구조의 깊이를 나타냄 최상위 노드 1, 아래 단계로 내려 갈수록 1씩 증가.

​

​

SELECT 윈도우함수[(대상)] OVER ([PARTITION BY 칼럼]

[ORDER BY 컬럼 ASC[DESC]

[ROWS/RANGE BETWEEN A AND B])

FROM 테이블 명;

​

PARTITION BY

데이터를 타피션으로 나눔.

GROUP BY와 유사하지만, 데이터를 그룹화하지 않고 모든 행을 유지.

​

ORDER BY 컬럼 ASC|DESC

데이터의 순서를 정해줌.

윈도우함수가 ORDER BY와 함께쓰이면 누적값을 반환함.

​

ROWS BETWEEN

물리적인 행.

현재 행을 기준으로 몇개의 행을 포함시킬지 지정

​

RANGE BETWEEN

현재 행의 값을 기준으로 범위를 지정.

​

​

UNBOUNDED PRECEDING

시작점으로 사용. 파티션 첫줄

​

n PRECEDING

현재 행을 기준으로 n개의 이전 행

​

CURRENT ROW

현재 행

​

n FOLLOWING

현재 행을 기준으로 n개의 다음 행

​

UNBOUNDED FOLLOWING

끝점에 사용. 파티션의 끝줄

​

시작점만 적은 경우 현재행(CURRENT ROW)까지임

​

​

​

​

​

​

​

​

​

​

​

​

​

UNION

합집합이지만 중복되는 데이터는 한번만 나타냄

UNION ALL

중복되는 데이터를 그대로 나타내는 합집합

​

INTERSECT

교집합으로 중복되어지는 데이터만 한번만 나타냄.

​

 

MINUS/EXCEPT(차집합)

순서중요

A MINUS B

= A테이블에서 B테이블에 있는 두테이블의 중복되는 애들을 빼고 A에 남아있는 데이터들만 출력

​

​

​

​

​

​

WHERE 절

​

IN연산자

SELECT NAME, NO, COMM

FROM EMP

WHERE NAME = 'HAJI',

OR NAME = 'GANGHAJI';

​

는

​

SELECT NAME, NO, COMM

FROM EMP

WHERE NAME IN('HAJI','GANGHAJI');

(NAME이 'HAJI'이거나 'GANGHAJI'인 것을 조회해라)

와 같다.

​

HAVING절

SELECT절보다 먼저 연산되므로 SELECT절에서 선언된 alias 사용 불가.

ORDER BY절

SELECT절보다 늦게 연산되므로 SELECT절에서 선언된 alias 사용 가능.

​

​

JOIN

• 동일한 열 이름이 여러 터이블에 존재할 경우 열 이름 앞에 테이블개 이름이나 alias 붙임.
• FROM절에서 alias 사용가능하지만 'as'를 붙이지않고 바로 붙임
• N개의 테이블을 조인하려면 최소 N-1개의 조인 조건이 필요함

​

ANSI표준의 경우 USING,ON 사용

• USING(칼럼명) : 두 테이블의 동일한 칼럼명으로 조인,alias나 테이블 이름 같은 접두사 붙이기 불가.
• ON(조건절): 두 테이블의 동일하지 않은 컬럼명을 비교식으로 조인

​

​

NATURAL JOIN

• 두 테이블 사이에 동일한 이름을 가지면서 동일한 데이터타입을 가진 컬럼들을 자동으로 EQUI JOIN 수행.
• USING,ON,WHERE 절에서 조건 정의 불가

​

​

OUTER JOIN

컬럼명1 = 컬럼명2(+) : LEFT OUTER JOIN

컬럼명1(+) = 컬럼명2: RIGHT OUTER JOIN

​

RANK()

중복되는 값이 있는 행은 공동 등수

다음 등수는 제거.

1,1,3

DENCE_RANK()

중복되는 값이 있는 행은 다음 등수

1,1,2

ROW_NUMBER()

우리가 아는 일반 연속 등수

1,2,3,4

​

​

NULL 계산

집계함수는 null제외한 나머지를 계산.즉 값이 null이 아님

행끼리 계산시 null이 존재하면 결과값은 null

컬럼끼리 계산시 null이 존재해도 결과값은 null이 아님(집계함수와 비슷)

​

​

그룹함수들(COUNT, SUM, AVG, MIN, MAX 등)

• NULL을 제외하고 카운트한다.
• NULL인 열도 계산하고 싶으면 NVL과 같은 함수로 NULL을 0으로 바꿔주든지해서 계산하면 됨

​

GROUPING SETS

• 만약 2개 컬럼 입력하면 각 컬럼의 집계만 나옴.
• 만약 1개 컬럼 입력하면 그 컬럼의 집계만 나옴

그래서 컬럼입력순서 상관없음 어찌됐든 따로따로 나오니까.

​

GROUPING SETS(A,B)

A,B

GROUPING SETS((A,B),A,())

(A,B),A,() 즉 ROLLUP과 동일한 값

GROUPING SETS(A,ROLLUP(B))

A,B,()

​

​

​

ROLLUP

• 만약 (A) 컬럼 입력하면

A칼럼의 집계,전체 합계

• 만약 (A,B) 컬럼 입력하면

A컬럼과 B컬럼의 집계. A컬럼의 집계, A와B의 합계 가 나옴.

• 근데 (B,A)를 입력하면

B컬럼과 A컬럼의 집계. B컬럼의 집계, B와A의 합계 가 나옴.

즉 컬럼입력순서 중요함. 뭐가 앞에 오냐에 따라 결과값이 달라짐.

​

GROUP BY A, ROLLUP(B)

• A로 파트를 나눈후 그 파트 안에서 b별로 집계함수, 한 파트당 합계, (즉 전체합계는 없음)

​

​

CUBE 함수

CUBE(A)

전체 합계, 칼럼 A의 소계

CUBE(A,B)

전체 합계, 칼럼 A의 소계,칼럼 B의 소계,칼럼 A,B의 소계

*가능한 모든 조합의 소계 및 합계를 생성하기 때문에 시스템에 무리가 갈수있음

​

​

COUNT(*),COUNT(1),COUNT(2)...

전체 테이블의 행의 수를 출력.

-(COUNT(1)의 경우 1값으로 가득 찬 파생 칼럼의 행의 수를 구하는것. null값이 존재할수 없으므로 COUNT(*)와 동일)

null값을 포함해서 행의 수를 계산

COUNT(칼럼명)

null을 제외한 행의 수를 계산

COUNT(DISTINCT 칼럼)

null을 제외한 칼럼이 가지고 있는 고유의 종류의 수를 계산

​

NVL(A,B)

NULL VALUE의 약자

a의 값이 설마 null value인가? 그렇다면 b를 출력하자

​

ISNULL(A,B)

IS NULL?의 질문형식

a의 값이 설마 null value인가? 그렇다면 b를 출력하자

​

NULLIF(A,B)

NULL IF( A = B ) BUT THEN A

NULL값을 가져라 만약 ,a와 b가 같다면. / 그러나 같지 않다면 a의 값을 가져라

​

CALESCE(A,B,C...)

ABC 순서대로 검사해서 NULL이 아닌 최초의 값 출력

​

​

set operator

위치기반 결합을 실시함(즉 컬럼명이 달라도 안에 내용만 같으면 결합가능)

결과테이블의 변수 명은 제 1 기준 테이블의 변수를 그대로 따른다.

​

--- 중복을 인정하지 않는다. DISTINCT한 결과만 출력한다

UNION : 합집합

INTERSECT : 교집합

MINUS(오라클)/EXCEPT(sql): 차집합

----------------------------------

--- 중복을 인정한다

UNION ALL : 합집합

------------------------------------

​

서브쿼리

group by를 제외한 모든곳에 사용

select *

from emp

where sal > (select avg(sal) from emp);

종류

단일행

결과가 단 1건 이하인 서브 쿼리를 의미

=,<,<=,>,>=,<>

다중행

결과가 여러개인것

IN(존재하는 값과 일치하는 값), ALL(모든 값들에 대해 만족하는 조건), ANY(어느하나라도 만족하는 조건), EXISTS(만족하는 값이 존재하는지 여부)

​

비연관 서브쿼리

서브쿼리가 메인 쿼리 칼럼을 갖고 있지 않은 형태. (정적)

단순히 간단한 값(서브쿼리 결과)을 제공하기 위한 목적

연관 서브쿼리

서브쿼리가 메인쿼리 칼럼을 포함하는 서브쿼리 (동적)

​

INDEX

CREATE INDEX 인덱스이름 ON 컬럼명

​

TCL정리

트랜잭션

"현실 세계"에서 어떤 행위가 일어날 때 이를 데이터 베이스에 반영하기 위해 필수적 발생 데이터 베이스의 조작들(DML)

특징 3가지

원자성....뭐지?

COMMIT -- 트랜잭션의 완료, 즉 계약완료 수정 불가

ROLLBACK --최신 commit 까지 복귀 또는 지정 savepoint 까지 복귀

SAVEPOINT --복귀지점지정

DDL문장 실행시 auto commit이 수행된다

​

​

EXISTS

서브쿼리 내부의 where을 만족하는 어떤 임의의 개체가 존재하는가?

존재(EXISTS)하면 해당 개체는 where 조건 절을 만족 시킬 수 있다.

​

NOT EXISTS는 "서브쿼리에 의해 특정 조건을 만족하는 데이터가 존재하지 않을 경우, 나머지 데이터를 반환하라"는 의미

​

​

​

1/24 여기까지 1시간이라고 외우고 뒤에 붙는걸 봐

60으로 나누면 1분

(60/10)으로 나누면 6으로 나누니까 10분

​

​

대표성 여부

- 주식별자 : 엔터티 내에서 각 어커런스를 구분할 수 있는 구분자. 타 엔터티와 참조 관계를 연결할 수 있는 식별자.

ex) 부서번호, 사번, 주문번호

- 보조식별자 : 엔터티 내에서 각 어커런스를 구분할 수 있으나, 대표성을 가지지 못해 참조관계를 연결하지 못한다.

ex) 주민등록번호, (사원 엔터티에서의 주식별자는 주민번호보다 사번이 더 알맞다.)

스스로 생성 여부

- 내부식별자 : 엔터티 내부에서 스스로 만들어지는 식별자

ex) 부서번호, 사번, 주문번호 (각각은 부서에서, 사원, 구매신청 엔터티에서 스스로 만들어진다.)

- 외부식별자 : 타 엔터티와의 관계를 통해 타 엔터티로부터 받아오는 식별자

ex) 사원 엔터티의 부서번호 (사원 엔터티와 부서 엔터티는 부서번호로 관계되어 있다.)

속성의 수

- 단일식별자 : 하나의 속성으로 구성된 식별자

ex) 부서번호, 사번, 주문번호

- 복합식별자 : 둘 이상의 속성으로 구성된 식별자

ex) 교육이력 엔터티에서 사번과 수강일자 두 속성을 이용해 식별자 역할을 한다.

대체 여부

- 본질식별자 : 업무에 의해 만들어지는 식별자

ex) 부서번호, 사번,

- 인조식별자 : 업무적으로 만들어지지는 않았지만, 원조식별자가 복잡한 구성을 가지고 있어 인위적으로 만들어짐.

ex) 주문번호 (기존에는 사번 + 주문일자 + 순번으로 불러와서 처리해야 해서, 인위적으로 주문번호만 처리하게 함)

​