SELECT 부터 조인·서브쿼리·윈도우 함수·계층형 질의, 그리고 DML·TCL·DDL·DCL 까지.
0 / 42개 학습 · 실제 시험 40문항 80점 · 16문항 미만이면 과락
결과를 머릿속으로만 그리면 NULL 과 빈 문자열, 조인으로 늘어나는 행 수에서 어긋난다. 헷갈리면 직접 돌려 보는 편이 빠르다.
NULL — 값이 아니라 '모른다'
NULL 은 0 도 빈 문자열도 아니다. 어떤 연산을 해도 NULL 이고, 비교하면 참도 거짓도 아니다.
SQL 이 실행되는 순서
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. 쓰는 순서와 실행 순서가 다르다.
WHERE 조건 — BETWEEN·IN·LIKE
BETWEEN 은 양 끝을 포함하고, IN 은 목록 중 하나, LIKE 는 %(여러 글자)와 _(한 글자)로 찾는다.
단일행 함수와 형 변환
단일행 함수는 행마다 하나씩 결과를 내고, 집계 함수는 여러 행을 묶어 하나를 낸다.
GROUP BY 와 집계 함수
GROUP BY 에 없는 열은 SELECT 에 그냥 쓸 수 없다. 집계 함수는 NULL 을 빼고 계산한다.
조인 — INNER 와 OUTER
INNER JOIN 은 양쪽에 다 있는 것만, LEFT OUTER JOIN 은 왼쪽은 다 남기고 오른쪽은 없으면 NULL 이다.
ORDER BY 와 NULL 의 자리
기본은 오름차순이고, NULL 이 어디에 놓이는지는 데이터베이스마다 다르다.
관계형 데이터베이스와 키
기본키는 유일하고 NULL 이 아니며, 외래키는 다른 표의 기본키를 가리키되 NULL 일 수 있다.
서브쿼리 — 단일행·다중행·다중열
한 건이 돌아오면 =·> 로 비교하고, 여러 건이면 IN·ANY·ALL·EXISTS 를 써야 한다.
서브쿼리가 놓이는 자리 — 스칼라·인라인뷰·중첩
SELECT 절에 오면 스칼라 서브쿼리, FROM 절에 오면 인라인 뷰, WHERE 절에 오면 중첩 서브쿼리다.
집합 연산자 — UNION 과 UNION ALL
UNION 은 중복을 없애고 정렬까지 하며, UNION ALL 은 그대로 이어 붙인다. 그래서 UNION ALL 이 빠르다.
윈도우 함수 — 묶지 않고 집계한다
GROUP BY 는 행을 줄이지만, 윈도우 함수는 행을 그대로 두고 옆에 집계 값을 붙인다.
그룹 함수 — ROLLUP·CUBE·GROUPING SETS
ROLLUP 은 오른쪽부터 하나씩 지워 가며 소계를, CUBE 는 모든 조합의 소계를 만든다.
Top N — 정렬한 뒤에 잘라야 한다
행을 자르는 조건이 정렬보다 먼저 적용되면 엉뚱한 행이 남는다. 인라인 뷰에서 정렬한 뒤 바깥에서 자른다.
계층형 질의 — 위아래로 타고 내려가기
START WITH 로 시작 행을 정하고 CONNECT BY 로 이어 간다. PRIOR 가 붙은 쪽이 부모다.
PIVOT 과 UNPIVOT
PIVOT 은 행을 열로 돌려세우고, UNPIVOT 은 열을 다시 행으로 눕힌다.
DML — INSERT·UPDATE·DELETE·MERGE
DML 은 데이터를 바꾸는 명령이고, 아직 확정되지 않아 COMMIT 전까지는 되돌릴 수 있다.
TCL — COMMIT·ROLLBACK·SAVEPOINT
COMMIT 은 확정, ROLLBACK 은 되돌리기, SAVEPOINT 는 중간에 표시를 찍어 거기까지만 되돌리는 것이다.
DDL 과 DCL
DDL 은 구조를 만들고 바꾸는 것(CREATE·ALTER·DROP·TRUNCATE), DCL 은 권한을 주고 뺏는 것(GRANT·REVOKE)이다.
뷰 — 저장된 질의
뷰는 데이터를 갖지 않고 질의만 저장한다. 쓸 때마다 원본을 다시 읽는다.
표준 조인 — NATURAL·USING·CROSS
NATURAL JOIN 은 같은 이름의 열로 알아서 잇고, USING 은 지정한 열로만 이으며, CROSS JOIN 은 조건 없이 모두 짝짓는다.
CASE 와 DECODE — 조건에 따라 값을 바꾸기
CASE 는 위에서부터 맞는 것을 찾아 멈춘다. 그래서 넓은 조건을 위에 두면 아래가 영영 걸리지 않는다.
문자 함수 — SUBSTR·INSTR·REPLACE·TRIM
SUBSTR 은 잘라 내고, INSTR 은 위치를 찾고, REPLACE 는 바꾸고, TRIM 은 양끝을 다듬는다.
날짜 다루기
날짜끼리 빼면 일수가 나오고, 날짜에 숫자를 더하면 그만큼 뒤의 날짜가 된다.
제약조건 — 값이 들어오기 전에 막는다
PRIMARY KEY·UNIQUE·NOT NULL·CHECK·FOREIGN KEY 로 잘못된 값이 아예 들어오지 못하게 한다.
인덱스 — 찾는 것은 빨라지고 넣는 것은 느려진다
인덱스는 찾아보기다. 조회는 빨라지지만 INSERT·UPDATE·DELETE 때마다 함께 고쳐야 해 느려진다.
정규 표현식으로 찾기
LIKE 로는 어려운 형태(숫자 세 자리, 특정 글자 반복)를 REGEXP_LIKE 로 찾는다.
집계 함수의 종류와 DISTINCT
SUM·AVG·MAX·MIN·COUNT 가 있고, 앞에 DISTINCT 를 붙이면 중복을 뺀 값으로 계산한다.
트랜잭션이 겹칠 때 생기는 문제
Dirty Read 는 확정 안 된 값을 읽는 것, Non-Repeatable Read 는 같은 것을 두 번 읽었더니 값이 달라진 것, Phantom Read 는 행 수가 달라진 것이다.
WITH 절 — 이름을 붙여 두고 쓰기
복잡한 인라인 뷰에 이름을 붙여 쿼리 앞으로 빼 두면 읽기 쉬워지고 여러 번 쓸 수 있다.
순수 관계 연산자와 SQL 문장의 대응
셀렉션은 WHERE, 프로젝션은 SELECT 절, 조인은 JOIN, 디비전은 나누기에 해당한다.
NULL 을 다루는 함수 — NVL·NVL2·NULLIF·COALESCE
NVL 은 NULL 일 때 대신 쓸 값을, NVL2 는 NULL 여부에 따라 서로 다른 값을, NULLIF 는 두 값이 같으면 NULL 을, COALESCE 는 여럿 중 처음 만나는 NULL 아닌 값을 돌려준다.
연산자 우선순위와 문자열 붙이기
괄호가 없으면 NOT → AND → OR 순으로 묶인다. 문자열은 || 로 잇는다.
셀프 조인 — 같은 표를 두 번 부르기
한 표 안에서 행끼리 이어야 할 때, 같은 표에 서로 다른 별칭을 붙여 두 표처럼 조인한다.
WHERE 와 HAVING 은 거르는 시점이 다르다
WHERE 는 묶기 전에 행을 거르고, HAVING 은 묶은 뒤에 그룹을 거른다.
EXISTS·ANY·ALL — 있는지만 보거나, 하나라도·전부
EXISTS 는 행이 하나라도 있는지만 보고, ANY 는 여럿 중 하나라도 만족하면, ALL 은 전부 만족해야 참이다.
INTERSECT 와 MINUS — 겹치는 것, 빼는 것
INTERSECT 는 양쪽에 다 있는 행만, MINUS(EXCEPT)는 위쪽에만 있는 행만 남긴다. 둘 다 중복을 없애고 정렬한다.
행 사이를 넘겨다보는 함수 — LAG·LEAD·FIRST_VALUE
LAG 는 앞 행, LEAD 는 뒷 행의 값을 끌어오고, FIRST_VALUE·LAST_VALUE 는 창 안의 첫 행·마지막 행 값을 준다.
몫과 자리를 재는 함수 — NTILE·RATIO_TO_REPORT·비율 순위
NTILE 은 몇 등분한 뒤 몇 번째 통인지, RATIO_TO_REPORT 는 전체 합에서 차지하는 비율, PERCENT_RANK·CUME_DIST 는 순위를 0~1 로 환산한 값이다.
ROWNUM 과 ROW_NUMBER 는 매겨지는 때가 다르다
ROWNUM 은 조건을 통과한 순서대로 먼저 붙고, ROW_NUMBER 는 정렬을 마친 뒤에 붙는다.
DELETE·TRUNCATE·DROP — 지우는 깊이가 다르다
DELETE 는 행만 지우고 되돌릴 수 있으며, TRUNCATE 는 전부 비우되 표는 남기고 되돌릴 수 없으며, DROP 은 표 자체를 없앤다.
트랜잭션의 네 가지 성질
원자성은 전부 되거나 전부 안 되는 것, 일관성은 규칙이 깨지지 않는 것, 고립성은 남의 중간 상태가 보이지 않는 것, 지속성은 커밋한 것이 남는 것이다.