SQL 모르는 것 정리 : 데이터 모델링의 이해 ~ 서브쿼리까지 (1회차)

SQL 문제도 푸는 데에 무슨 핵심이 있나보다.

전체 개념을 정독하는 것이 훨씬 좋지만 전체 개념 정독 시간이 부족하거나 광범위한 개념 중에 핵심 위주로 접근이 필요할 때... 둘이 같은 말이려나? 그럴 때를 위해 종이에 정리한 걸 다시 정리해본다.

 

다시 말하지만 모르는 것을 위주로 적은 것!

 

+ 5/19  5/20  5/21  5/22  5/25  5/28  5/29  계속 업데이트 중!

 

 

 

내가 제일 중요하다고 생각한 개념은

 

🏅count 경우:

null을 비포함한 행의 개수

결과값 일부 null: null아닌 행만 셈

WHERE 등에 의해서 결과가 없다면 : 0 리턴

GROUP BY 결과가 없다면: 공집합 리턴

 

🥇 sum 경우:

행 전부 null: NULL 리턴
행 일부 null: NULL  연산하지 않고 다른 값들만 더함

 

 

오라클 -> ANSI:

ON: 조인조건 => ex) 공통컬럼(key)

WHERE: 일반조건 => ex) 특정 조건 필터링

 

 

where:

where 조건이 없으면 전체가 출력 = 생략되는 데이터가 없음

 

 

NOT IN 결과값이 False면:

sum의 값: NULL

---

 

데이터 모델링의 이해

 

 

데이터 모델링

논리
물리
개념적 데이터 모델링	🏅 추상화 수준이 높고 / 업무 중심적이고 / 포괄적인 수준의 모델링 진행. 전사적 데이터 모델링

 

 

 

 

스키마 3단계

외부	여러 사용자 관점, 뷰 얘기, 논리적 독립성
개념	전체 사용자 관점, 논리적 독립성
내부	물리적
전개 내물 외뷰

 

 

 

 

엔터티의 분류

발생 시점에 따라	기본/중심/행위  (행중기)
유/무형에 따라	개념/유형/사건  (개유사)

 

 

 

속성의 분류

특성에 따라	기본/파생/설계 속성
엔터티 구성방식에 따라	기본/pk/fk

 

 

 

 

속성의 특징

하나의 엔터티  =  두 개 이상의 속성

하나의 엔터티  =  두 개 이상의 인스턴스 집합

하나의 속성     =  1개의 속성값

 

 

 

ERD 읽기

실선: 식별 점선: 비식별

 

 

 

 

식별관계: 부모엔터티의 주식별자를 상속받아 자식엔터티에서 외부식별자면서 주식별자로 사용하는 관계

 

 

 

 

식별자

대표성 여부에 따라	주 보조: 대표성X
스스로 생성 여부에 따라	내부: 스스로 생성되는 식별자 외부: 타 엔터티에서 받아옴 (FK)
대체 여부에 따라	본질: 업무에 의해 만들어짐 인조: 인위적으로 만든 식별자

 

* 주식별자: 속성값이 NULL이 들어갈 수 없음

 

 

 

식별자의 특징

유일성
최소성
불변성	한 번 주식별자가 지정되면 바뀌면 안 됨
존재성	주식별자가 존재하면 값이 꼭 존재해야 함

 

 

 

 

정규화:

모델의 일관성을 확보하고 중복 제거하여 모델의 독립성을 확보하는 과정

 

 

 

3차 정규화 풀이 = 번호를 찾는다

ISBN의 경우 도서 번호가 필요해서 번호가 하나 있다고 생각하면 됨

 

 

 

관계:

서로 다른 엔터티의 인스턴스들 간의 존재하는 논리적인 연관성

존재적관계
행위적관계	엔터티간에 어떤 행위가 있음
존재/행위적 관계는 ERD에서는 구분 안 함. UML에서는 실선: 존재적(소속)  /  점선: 행위적(의존) 근데 내가 IE에서만 그렇고, 바커는 아니라고 생각했던 이유: 바커는 식별,비식별 관계를 실선 점선으로 어느정도 구분이 가능하기 때문.. 어쨌든 구분 안 함으로 생각을 하자..
상호배타적 관계 (둘 중 하나로 주문 가능할 경우)	= 하나 선택하면 다른 하나는 선택 못함

 

* 관계 페어링: 엔터티 안에 인스턴스가 개별적으로 관계를 가지는 것

 

 

 

 

관계 표기법

관계명
관계차수	1:1, 1:M, M:N	하나의 엔터티와 다른 엔터티 간의 레코드 연결 방식
(관계) 선택성

 

 

 

 

트랜잭션:

• 트랜잭션에 의한 관계는 필수적인 관계 형태
• 부분 COMMIT 불가: 동시 COMMIT 또는 ROLLBACK

 

 

 

 

트랜잭션 특징

원자성	모두 성공/실패
일관성	트랜잭션 실행 전 데이터베이스 내용이 잘못되지 않았다면 실행된 이후에도 데이터베이스 내용에 잘못이 있으면 안 된다
고립성
지속성

 

 

 

 

NULL 연산

산술연산의 경우	모른다. => NULL ex) Null + 5 = 모른다  * NULL 출력됨
비교연산의 경우	Unknown(= 알 수 없음) -> False ex) NULL = NULL
집계함수 연산의 경우	0으로 처리 안 되고 계산에서 제외하고 나머지만 계산 수행함
NULL로만 구성된 컬럼을 count	0
count(*)의 경우	null이 있어도 전체 행 개수 출력
count의 경우	null이 아닌 행의 개수 / null을 비포함한 행의 개수 결과값 일부 null: null아닌 행만 셈 WHERE 등에 의해서 결과가 없다면 : 0 리턴 GROUP BY 결과가 없다면: 공집합 리턴
sum의 경우	sum은 0 포함 / null 비포함
	null로만 구성되면 null을 리턴 일부만 null이면 null 말고 다른 애들끼리만 더함 행 전부 null: NULL 리턴 행 일부 null: NULL  연산하지 않고 다른 값들만 더함

 

 

 

SQL 기본

 

관계형 데이터베이스

• 데이터 무결성 보장할 수 있음
• 데이터베이스 부하를 분석하기는 어려움

 

 

테이블

• 하나의 테이블 = 한 계정만 소유

 

 

 

SELECT절

• SELECT절에 DISTINCT는 SELECT 바로 뒤에 위치한다
  • DISTINCT: 중복 제거
• 오라클에서는 group by절 위에 having절 명시해도 된다. (sql server는 안 된다)
• from은 생략 안 된다 (다만 sql server 날짜출력/날짜연산 한정 from 생략된다)
• select절에서 지정한 컬럼 별칭을 ORDER BY절에서 필수 사용할 필요는 없다
• * 와 컬럼명 동시 사용 불가

 

 

 

 

FROM절

• 🏅 여기서 테이블 별칭 지정하면 무조건 별칭 써야 함

 

 

 

 

🏅 별칭 정리 (SELECT, FROM)

SELECT	FROM
컬럼 별칭 필수 사용 ❌ 오더바이에서 꼭 안 사용해도 됨	🏅 테이블 별칭 지정한 순간부터 원래 테이블명 사용 불가      무조건 테이블 별칭 사용해야 함

 

 

 

WHERE절

where 조건이 없으면 전체가 출력 = 생략되는 데이터가 없음

WHERE		HAVING
집계함수 조건 사용 불가		집계함수 사용⭕
연산자 IN	OR 연산자와 같다고 생각하면 됨 ex) NOT IN (Null, 1,2) => 1,2가 널이 아니더라도 이미 null이 있어 거짓이 됨 🏅 in이면 or not in이면 and	NOT IN 뒤의 서브쿼리가 하나라도 거짓이면 전체가 거짓이 됨 => sum값은 NULL
비교연산자 <>	!= 랑 같음 참/거짓 리턴

 

 

 

 

GROUP BY

GROUP BY에서 사용	sum: 그룹바이랑 함께 쓰면 일반 컬럼과 같이 출력 가능 ※ 단, 그 일반컬럼이 그룹바이의 대상이어야 가능
	그룹바이에 명시된 컬럼은 셀렉트절에 단독 올리기 가능
ORDER BY에서 사용	오더바이 사용 경우 그룹바이/셀렉트절 컬럼 중에서 골라야 함

 

 

 

 

ORDER BY

ASC (오름차순 1 - 2 - 3)	null: 맨 뒤
DESC (내림차순 3 - 2 - 1)	null: 맨 앞
NULLS FIRST	null: 맨 앞
NULLS LAST	null: 맨 뒤

 

* 기본적으로 NULL은 맨 뒤

* GROUP BY 사용 시 ORDER BY에서는 SELECT 컬럼 or GROUP BY 컬럼 중에서 골라써야 함

 

 

 

함수

 

문자형함수

substr	문자열 뽑아냄 (추출)	ex) CASE SUBSTR(컬럼명, 7, 1) WHEN 1 일 때 substr값과 when 1의 데이터타입이 불일치 하면 안됨
instr	문자열 위치 (in이 index라고 생각하면 뭔가 외워짐)
Lpad	문자열 왼쪽 삽입
Ltrim	문자열 왼쪽 제거
concat	문자열 결합	고양이들은 더 많이 결합(모여있게)해주고 싶잖아!!!
to_char	숫자나 날짜를 문자열로 바꿔줌 문자열을 원하는 형식으로 바꿀 수 있음	ex) to_char('369', '999.99') or ('369', '000.00') => '369.00' ex) to_char('369', '9,999') => '  369' ex) to_char('369', '0,000') => '0369' 0은 0 9는 공백 * 소수점 자리는 0 or 9 모두 0
initcap	카멜 표기법으로 변경 앞자리만 대문자

 

숫자형함수

ceil	올림	천장 = ◎보다 큰 상수 중 가장 가까운 정수
floor	버림	바닥 = ◎보다 작은 상수 중 가장 가까운 정수
round	반올림 (숫자, 자리수) 그 자리수로 만들어	ex) ROUND(12345.678, -2)는 정수 뒷자리부터 2자리 앞으로 간 10의 자리에서 반올림 => 12300 - 위치는 소수점부터 안 셈 - (-)는 1의자리에서 왼쪽으로 - (+)는 소수점자리 기준으로 오른쪽 - 날짜 반올림 경우: 12시(정오)를 기준으로 반올림
sign	양수 / 음수 / 0 판별	1 / -1 / 0
trunc	특정 자리에서 숫자 삭제 보통) 숫자의 소수점 이하 부분을 제거하고 정수 부분만 남김	ex) trunc(5.47) => 5 floor랑 좀 다르다. 결과값은 같은 경우도 있지만 미카미..
mod	나머지 구하는 함수 (대상1,  대상2) => 대상1 / 대상2의 나머지값
power	제곱 (대상1,2) => 대상1의 2제곱

 

NULL함수

NVL	null 치환
NULLIF 🏅	(A, B) A, B 서로 비교 후 같으면 null 틀리면 A      리턴	ex) NULLIF(NULL, 100) => 첫번째 인수인 NULL???
COALESCE	(대상1,대상2,대상3,...,그외리턴) 대상들 중 null이 아닌 값 출력	ex) COALESCE(NULL, 100) => 대상1이 null이니 그 다음인 100 ex) COALLESCE(100,200,300) => 대상1이 null이 아니니까 100
ISNULL 🏅 (= NVL)	(대상, 치환값) 대상이 null이면 치환값 리턴 NVL이랑 같다보면 됨	ISNULL(대상, 100) => 대상이 null이면 100으로 치환 리턴

 

그외함수

DECODE 🏅	(비교값,조건1,결과1,조건2,결과2,...,기본값) case switch와 같음	ex) DECODE(SIGN(SAL-4000),1,1,0)
CASE WHEN THEN	조건 치환	ex) CASE SUBSTR(컬럼명, 7, 1) WHEN 1 에서는 substr과 when 결과값의 데이터타입이 불일치 하면 안 됨 - 문제에서 count(case when ... ELSE 0) 을 단독으로 쓴 선지가 있을 경우 의심해봐야 함 else 0은 어쨌든 전체 행이 세질 수도 있음(null이 아니라서) => count로 10번 부서(해당조건)만 셀 거면 else가 부여되면 안 됨
		ex) sum의 경우 else 0도 괜찮음 숫자를 더하는 개념이라 괜찮음 sum은 0 포함 / null 비포함
이건 함수는 아닌데 어디에 적어야할지 모르겠어서 씀 || ( =  +, concat)	select 절에서 문자열과 문자열 사이에 사용하면 문자열 합쳐주는 애 연결 연산자	ex) select '하하' || '배고파' * &는 문자열 결합 안 된다고 함
LAST_DAY	(기준이 되는) 속한 달의 마지막 날짜 출력
EXIST	뒤의 조건이 거짓이면 비출력 뒤의 조건이 참이면 출력	- 서브쿼리 결과가 참/거짓인지만 중요 - 행마다 평가
NOT EXIST	뒤에 서브쿼리 조건이 거짓이면 결과 나옴 ∴ 거짓 =출력

 

 

 

 

🏅 날짜 계산

1/24/60: 1분

-30/24/60: -30*1/24/60: -30분

 

1: 하루

24: 시간

60: 분

 

 

 

 

조인

*조인은 유독 헷갈려서 지피티한테 많이 물어보니, 정리가 좋아보여 가져옴

 

* (+)는 기준 조인 테이블 반대편에 붙임

* FULL OUTER JOIN은 left와 right를 UNION한 것과 같음!!! = 중복 한번만 (union all이면 틀림)

* NATURAL JOIN은 조인조건 전달 불가 (ex. using, on)

* CROSS JOIN은 카타시안곱임. => 왼쪽테이블행*오른쪽테이블행

* using 사용 시 반드시 괄호로 전달되어야 함

 

 

이너 vs 레프트/라이트/풀 조인 비교

 

 

 

서브쿼리

서브쿼리 vs 조인

서브쿼리	조인
값 or 행 비교 후 필터된 값 리턴	테이블 연결(합침)	목적
비교 & 선별	연결, 확장	키워드
서브쿼리 종류		결과 값
스칼라	1개의 값만 리턴	1행 1열
IN	여러 개 값 리턴 후 포함 여부 확인	1열 n행
상관	where절에서 양쪽 비교하는 목적 A = B B = A * 내 생각: 서브쿼리 where절에서 왼쪽/오른쪽 순서를 생각해서 문제를 푸는 게 핵심	조건마다 매번 계산하기

 

* 5/22

상관 서브쿼리 풀 때 A=B B=A라고 했지만 선대입해야 하는 애는 서브쿼리 바깥의 WHERE절에 나온 테이블 애 기준이다.

 

ex)

SELECT aaa

FROM T1

WHERE ddd >= ( SELECT sum(ddd) FROM T2 WHERE T2.ccc = T1.ccc)

 

대충 이런 문제가 있을 때, 소괄호 안 상관 서브쿼리에서 where절에 먼저 대입을 해야 할 애는 T1.ccc이다.

T1이 바깥 FROM절 테이블로 선택이 되어있기 때문에

• T1.ccc 값을 먼저 넣고,
• T2.ccc 값을 가져와서 sum해준다음
• ddd랑 비교하고 어쩌고 하면 된다! 내가 지어낸 문제라서 대강 풀이 방법만 이렇다는 걸 정리하고자 했다.

 

동작 방식에 따라		위치에 따라
연관	비연관	스칼라 (SELECT)	인라인뷰 (FROM)	WHERE절
- 서브쿼리가 메인쿼리 컬럼을 가지고 있을 때 - 메인쿼리 먼저 수행 후 서브쿼리 조건이 맞는지 확인할 때 주로 사용함				단일행	다중행
				단일행 비교연산자 사용 (=, <>, >, >=, <, <=)	단일행 비교연산자 사용 가능
				결과값 1개 행	결과 값 여러 행
					최대값: >ALL <ANY 최소값: <ALL >ANY
					EXISTS NOT EXISTS * 서브쿼리 셀렉트절 출력 형태 안 중요(ex. 'X', *, 1)