MySQL 데이터베이스 완전 정복
CHAPTER 1. DB와 SQL 기초
DB가 뭐야?
데이터를 모아두는 창고!
- 네이버 → 회원 정보
- 쿠팡 → 주문 내역
- 배달의민족 → 메뉴 정보
테이블이 뭐야?
엑셀 표랑 똑같아!
empno ename job sal deptno
| 7839 | KING | PRESIDENT | 5000 | 10 |
| 7902 | FORD | ANALYST | 3000 | 20 |
| 7788 | SCOTT | ANALYST | 3000 | 20 |
| 7566 | JONES | MANAGER | 2975 | 20 |
| 7698 | BLAKE | MANAGER | 2850 | 30 |
| 7369 | SMITH | CLERK | 800 | 20 |
행(row) = 한 명의 데이터 열(column) = 번호, 이름, 직업, 급여, 부서번호
CHAPTER 2. 기본 SELECT
sql
-- 전체 보기
SELECT * FROM emp;
-- 특정 컬럼만
SELECT ename, sal FROM emp;
-- 조건 걸기
SELECT * FROM emp WHERE sal >= 3000;
-- 정렬
SELECT * FROM emp ORDER BY sal DESC; -- 높은 순
SELECT * FROM emp ORDER BY sal ASC; -- 낮은 순
-- 중복 제거
SELECT DISTINCT job FROM emp;
-- 별칭(이름 바꾸기)
SELECT ename, sal AS 급여 FROM emp;WHERE 조건 종류
sql
WHERE sal >= 3000 -- 이상
WHERE sal BETWEEN 2000 AND 4000 -- 범위
WHERE ename LIKE 'K%' -- K로 시작
WHERE ename LIKE '%S' -- S로 끝
WHERE ename LIKE '%O%' -- O 포함
WHERE deptno IN (10, 20) -- 목록 중 하나
WHERE sal IS NULL -- NULL인 것
WHERE sal IS NOT NULL -- NULL 아닌 것
WHERE sal >= 3000 AND deptno = 10 -- 둘 다
WHERE sal >= 3000 OR deptno = 10 -- 하나라도
WHERE NOT sal >= 3000 -- 아닌 것CHAPTER 3. GROUP BY / HAVING
실행 순서 - 필수 암기!
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY집계함수
함수설명
| COUNT(*) | 개수 세기 |
| SUM(sal) | 합계 |
| AVG(sal) | 평균 |
| MAX(sal) | 최댓값 |
| MIN(sal) | 최솟값 |
sql
-- 부서별 통계
SELECT deptno,
COUNT(*) 인원수,
SUM(sal) 급여합계,
AVG(sal) 급여평균,
MAX(sal) 최고급여,
MIN(sal) 최저급여
FROM emp
GROUP BY deptno;
-- 전부 같이 쓰기
SELECT deptno, AVG(sal)
FROM emp
WHERE job != 'CLERK' -- 개인 필터 먼저!
GROUP BY deptno
HAVING AVG(sal) >= 2000 -- 그룹 필터 나중에!
ORDER BY AVG(sal) DESC;WHERE vs HAVING
WHEREHAVING
| 시점 | 그룹 묶기 전 | 그룹 묶은 후 |
| 집계함수 | 못 씀 | 가능 |
| 대상 | 개인 필터 | 그룹 필터 |
CHAPTER 4. JOIN
JOIN이 뭐야?
두 개 이상의 테이블을 합쳐서 보는 것!
sql
-- INNER JOIN: 둘 다 있는 것만
SELECT e.ename, d.dname, e.sal
FROM emp e
JOIN dept d ON e.deptno = d.deptno;
-- LEFT JOIN: 왼쪽 전부 (오른쪽 없으면 NULL)
SELECT e.ename, d.dname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno;
-- RIGHT JOIN: 오른쪽 전부 (왼쪽 없으면 NULL)
SELECT e.ename, d.dname
FROM emp e
RIGHT JOIN dept d ON e.deptno = d.deptno;JOIN 종류설명비유
| INNER JOIN | 둘 다 있는 것만 | 교집합 |
| LEFT JOIN | 왼쪽 전부 | 왼쪽 기준 |
| RIGHT JOIN | 오른쪽 전부 | 오른쪽 기준 |
JOIN 실전 예제
sql
-- 부서별 평균 급여
SELECT d.dname, AVG(e.sal) 평균급여
FROM emp e
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname
HAVING AVG(e.sal) >= 2000;
-- 직원 없는 부서 찾기
SELECT d.dname
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
WHERE e.empno IS NULL;CHAPTER 5. 서브쿼리
서브쿼리가 뭐야?
질문 안에 또 질문! 반드시 () 괄호 안에!
sql
SELECT * FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp);
-- ↑ 먼저 실행! → 결과를 바깥에서 사용단일행 서브쿼리 (결과 1개)
sql
-- 사용 연산자: = != > >= < <=
WHERE sal = (SELECT MAX(sal) FROM emp) -- 최고급여자
WHERE sal > (SELECT AVG(sal) FROM emp) -- 평균 이상
WHERE sal = (SELECT MIN(sal) FROM emp) -- 최저급여자다중행 서브쿼리 (결과 여러개)
sql
-- IN: 목록 중에 있으면 OK
WHERE deptno IN (10, 20)
-- ANY: 하나라도 이기면 OK (제일 작은 값 기준)
WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30)
-- ALL: 전부 이겨야 OK (제일 큰 값 기준)
WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 30)값이 10, 20, 30이면:
- > ANY → 10보다만 크면 됨 (쉬움)
- > ALL → 30보다 커야 함 (어려움)
인라인 뷰 (FROM절)
sql
-- 데이터 먼저 줄이고 사용 → 속도 빠름!
SELECT * FROM
(SELECT * FROM emp WHERE deptno = 10) 임시
WHERE sal >= 3000;스칼라 서브쿼리 (SELECT절)
반드시 1행 1열!
sql
SELECT ename, sal,
(SELECT AVG(sal) FROM emp) 전체평균,
sal - (SELECT AVG(sal) FROM emp) 차이
FROM emp;VIEW
sql
CREATE VIEW 고연봉직원 AS
SELECT * FROM emp WHERE sal > 3000;
SELECT * FROM 고연봉직원; -- 테이블처럼 사용!서브쿼리 위치 정리
위치종류조건
| SELECT절 | 스칼라 | 반드시 1행 1열 |
| FROM절 | 인라인 뷰 | 임시 테이블처럼 |
| WHERE절 | 단일행/다중행 | 결과 개수에 따라 |
| HAVING절 | 단일행/다중행 | 그룹 조건 |
| GROUP BY절 | ❌ 불가 |
CHAPTER 6. 윈도우 함수
GROUP BY vs 윈도우 함수
GROUP BY → 행이 합쳐져서 사라짐!
윈도우 함수 → 행 유지하면서 통계 추가!기본 구조
sql
함수() OVER (
PARTITION BY 컬럼 -- 그룹 나누기 (부서별, 반별)
ORDER BY 컬럼 -- 계산 순서
)순위 함수
급여: KING(5000), FORD(3000), SCOTT(3000), JONES(2975)
함수결과설명사용
| ROW_NUMBER() | 1,2,3,4 | 무조건 다른 번호 | 페이지 번호 |
| RANK() | 1,2,2,4 | 공동순위, 번호 건너뜀 | 올림픽 순위 |
| DENSE_RANK() | 1,2,2,3 | 공동순위, 안 건너뜀 | 학교 석차 |
sql
SELECT ename, sal,
ROW_NUMBER() OVER(ORDER BY sal DESC) 행번호,
RANK() OVER(ORDER BY sal DESC) 순위,
DENSE_RANK() OVER(ORDER BY sal DESC) 촘촘순위
FROM emp;통계 함수
sql
AVG(sal) OVER() -- 전체 평균
AVG(sal) OVER(PARTITION BY deptno) -- 부서별 평균
sal - AVG(sal) OVER(PARTITION BY deptno) -- 나와 부서 평균 차이
ROUND(AVG(sal) OVER(PARTITION BY deptno), 1) -- 소수점 1자리
SUM(sal) OVER(PARTITION BY deptno ORDER BY sal DESC) -- 부서별 누적합
MIN(sal) OVER(PARTITION BY deptno) -- 부서별 최솟값
MAX(sal) OVER(PARTITION BY deptno) -- 부서별 최댓값LAG / LEAD
sql
LAG(sal) OVER(ORDER BY hiredate) -- 이전 사람 값
LEAD(sal) OVER(ORDER BY hiredate) -- 다음 사람 값
sal - LAG(sal) OVER(ORDER BY hiredate) -- 이전 사람과 차이ROWS BETWEEN (범위 지정)
sql
-- 누적합 (처음부터 현재까지)
SUM(sal) OVER(ORDER BY sal
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)키워드의미
| UNBOUNDED PRECEDING | 맨 처음 행부터 |
| CURRENT ROW | 현재 행 |
| UNBOUNDED FOLLOWING | 맨 마지막 행까지 |
FIRST_VALUE / LAST_VALUE
sql
-- 최고연봉자 이름을 모든 행에 붙이기
FIRST_VALUE(ename) OVER(ORDER BY sal DESC)
-- 마지막 값 (범위 지정 필수!)
LAST_VALUE(ename) OVER(
ORDER BY sal
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)NTILE (등급 나누기)
sql
NTILE(4) OVER(ORDER BY sal DESC)
-- 1등급(상위25%), 2등급, 3등급, 4등급(하위25%)
-- 고객 VIP/골드/실버/일반 나눌 때 사용!부서 1등만 뽑기 - 중요!
RANK 결과를 WHERE에 바로 못 씀 → 인라인 뷰로 감싸야 함!
sql
SELECT * FROM (
SELECT ename, sal, deptno,
RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) 순위
FROM emp
)
WHERE 순위 = 1;CHAPTER 7. DML (데이터 변경)
SELECT vs DML
SELECT → 읽기만! 데이터 안 바뀜! (안전!)
DML → 데이터 실제 변경!INSERT
sql
-- 컬럼 지정 (권장! 나머지는 NULL)
INSERT INTO emp (empno, ename, sal)
VALUES (100, '홍길동', 3000);
-- 전체 컬럼 (순서 맞춰야 함!)
INSERT INTO emp
VALUES (100, '홍길동', 'CLERK', 7902, '2024-01-01', 3000, NULL, 20);UPDATE
sql
-- 한 명 수정
UPDATE emp SET sal = 6000 WHERE empno = 7839;
-- 여러 컬럼 동시에
UPDATE emp
SET sal = 6000, job = 'PRESIDENT'
WHERE empno = 7839;
-- ⚠️ WHERE 없으면 전체 수정! 위험!DELETE
sql
-- 한 명 삭제 (안전!)
DELETE FROM emp WHERE empno = 100;
-- ⚠️ WHERE 없으면 전체 삭제! 위험!
DELETE FROM emp;MySQL 특이 규칙
UPDATE/DELETE에서 같은 테이블로 서브쿼리 쓰면 오류! → 한번 더 감싸서 해결!
sql
-- ❌ 오류
DELETE FROM emp WHERE sal < (SELECT AVG(sal) FROM emp);
-- ✅ 해결
DELETE FROM emp
WHERE sal < (
SELECT * FROM (SELECT AVG(sal) FROM emp) avgsal
);CHAPTER 8. TCL (저장/취소)
버퍼가 뭐야?
DML 실행하면 바로 DB에 저장 안 됨! 버퍼(임시저장소)에 먼저!
카카오톡 비유:
메시지 작성 중 = 버퍼
전송 버튼 = COMMIT
삭제 버튼 = ROLLBACK사용법
sql
SET autocommit = 0; -- 자동저장 끄기
START TRANSACTION; -- 시작!
INSERT INTO emp (empno, ename, sal) VALUES (100, '홍길동', 3000);
SELECT * FROM emp; -- 확인!
COMMIT; -- 맞으면 진짜 저장!
ROLLBACK; -- 틀리면 전부 취소!SAVEPOINT
sql
START TRANSACTION;
INSERT INTO 주문 VALUES ('치킨');
SAVEPOINT A; -- 게임 세이브!
UPDATE 계좌 SET 잔액 = 잔액 - 20000;
SAVEPOINT B; -- 또 세이브!
ROLLBACK TO B; -- B로 돌아가!
ROLLBACK TO A; -- A로 돌아가!
ROLLBACK; -- 처음으로!
COMMIT; -- 저장!CHAPTER 9. 트랜잭션
트랜잭션이 뭐야?
여러 작업을 하나로 묶은 것. 전부 성공 or 전부 실패!
카카오페이 이체:
- ① 내 계좌 -10,000원
- ② 친구 계좌 +10,000원
- ①됐는데 ②에서 오류 → ROLLBACK → ①도 취소 → 내 돈 돌아옴!
ACID 4가지 특성 - 면접 필수!
특성설명비유
| 원자성 | 전부 성공 or 전부 실패 | 쪼갤 수 없는 하나 |
| 일관성 | 이체 전후 합계 항상 같음 | 돈이 생기거나 사라지면 안됨 |
| 독립성 | 내 작업 중 다른 사람 차단 | 화장실 자물쇠 |
| 지속성 | COMMIT하면 영원히 저장 | 카카오톡 전송 완료 |
트랜잭션 상태
시작 → 활성(작업중) → 부분완료 → COMMIT → 완료!(저장)
↓
실패 → ROLLBACK → 철회!(취소)UNDO / REDO LOG
UNDO LOGREDO LOG
| 역할 | 취소용 | 복구용 |
| 저장 내용 | 수정 전 원래 값 | 수정 후 새 값 |
| 사용 시점 | ROLLBACK 시 | 장애 후 복구 시 |
CHAPTER 10. LOCK
내가 작업하는 동안 다른 사람 못 건드리게 잠그는 것! COMMIT or ROLLBACK하면 풀림!
LOCK 종류
종류설명언제비유
| S Lock | 읽기만 OK, 여러 명 동시 가능 | SELECT | 도서관 책 |
| X Lock | 읽기도 수정도 전부 차단 | UPDATE/DELETE | 화장실 자물쇠 |
| Row Lock | 특정 행만 잠금 | 행 단위 수정 | 특정 좌석 예약 |
| Table Lock | 테이블 전체 잠금 | ALTER TABLE, 인덱스 생성 | 식당 통째로 빌리기 |
| Gap Lock | 행 사이 공간 잠금 | 없는 값 조회 시 | 범위 잠금 |
| Next-Key Lock | Row + Gap 합침 | MySQL 기본 | 가장 강력한 잠금 |
CHAPTER 11. 동시성 제어
여러 명이 동시에 DB 쓸 때 충돌 안 나게 관리!
T1T2결과
| 읽기 | 읽기 | 문제 없음! 그냥 둘 다 읽으면 됨 |
| 쓰기 | 쓰기 | Lock 필요! 한 명씩 순서대로 |
| 읽기 | 쓰기 | 격리 수준 정책으로 해결 |
Thread와 DBMS 동시성
Thread = 일꾼
여러 Thread가 동시에 DB 요청
→ DBMS가 동시에 처리!
→ 읽기+읽기는 문제 없음
→ 쓰기가 있을 때만 관리 필요!CHAPTER 12. Dead Lock
서로 기다리다가 둘 다 멈춰버리는 상황!
T1: 데이터1 잠금 → 데이터2 기다림
T2: 데이터2 잠금 → 데이터1 기다림
→ 서로 영원히 기다림! 😱
→ Lock 메커니즘이 오히려 문제를 만든 것!해결: MySQL이 모니터링하다가 자동으로 한쪽 강제 ROLLBACK! 예방: 항상 같은 순서로 Lock 걸기!
CHAPTER 13. MVCC
수정 중에도 다른 사람이 옛날 버전 읽을 수 있게 해주는 것!
T1이 7000 → 7100 수정 중 (COMMIT 안 함)
T2가 조회 → 7000 보임! (기다릴 필요 없음!)
T1이 COMMIT → 이제 모든 사람이 7100 봄!UNDO LOG에 원래 값 저장 → 다른 사람한테 보여줌! Lock 기다릴 필요 없어서 빠름!
CHAPTER 14. 격리 수준 - 면접 필수!
레벨이름Dirty ReadNon-RepeatablePhantom Read
| L0 | READ UNCOMMITTED | 발생 😱 | 발생 😱 | 발생 😱 |
| L1 | READ COMMITTED (Oracle 기본) | 없음 ✅ | 발생 😱 | 발생 😱 |
| L2 | REPEATABLE READ (MySQL 기본) | 없음 ✅ | 없음 ✅ | 없음 ✅ |
| L3 | SERIALIZABLE | 없음 ✅ | 없음 ✅ | 없음 ✅ |
3가지 문제 쉽게!
Dirty Read → COMMIT 안 한 값 읽기
T2: 7000 → 9000 수정 (COMMIT 안 함)
T1: 9000 읽음
T2: ROLLBACK! → T1이 읽은 9000은 없어진 값! 😱Non-Repeatable Read → 같은 쿼리인데 결과 달라짐
T1: 조회 → 7000
T2: 9000으로 수정 COMMIT!
T1: 다시 조회 → 9000 😵Phantom Read → 없던 데이터가 갑자기 생김
T1: 10~30살 조회 → 1명
T2: 25살 Bob 추가 COMMIT!
T1: 다시 조회 → 2명 👻CHAPTER 15. 트랜잭션 회복 - 면접 필수!
장애 발생 시 DB를 일관성 있는 상태로 되돌리는 것!
체크포인트란?
주기적으로 메모리 내용을 하드디스크에 저장하면서 "여기까지 저장 완료!" 라고 표시해두는 것
3가지 경우
상황처리이유
| 체크포인트 이전 COMMIT | 아무것도 안 함 | 이미 하드디스크에 저장 완료 |
| 체크포인트 이후 COMMIT 있음 | REDO (재실행) | 완료됐으니 다시 반영 |
| 체크포인트 이후 COMMIT 없음 | UNDO (취소) | 완료 안 됐으니 롤백 |
COMMIT 했으면 → REDO (살려!)
COMMIT 못 했으면 → UNDO (없애!)로그 파일 날아가면 복구 불가! → 백업이 생명!
면접 핵심 키워드 최종 정리
📌 SQL 기본
SELECT → 조회
WHERE → 조건
GROUP BY → 그룹 묶기
HAVING → 그룹 조건 (집계함수 가능)
ORDER BY → 정렬
📌 JOIN
INNER JOIN → 둘 다 있는 것만
LEFT JOIN → 왼쪽 전부
RIGHT JOIN → 오른쪽 전부
📌 서브쿼리
단일행 → = != > >= < <=
다중행 → IN / ANY / ALL
FROM절 → 인라인 뷰
SELECT절 → 스칼라 (1행 1열만)
📌 윈도우 함수
ROW_NUMBER → 1,2,3,4
RANK → 1,2,2,4 (건너뜀)
DENSE_RANK → 1,2,2,3 (안건너뜀)
LAG/LEAD → 이전/다음 값
NTILE(N) → N등급으로 나누기
📌 DML
INSERT → 추가
UPDATE → 수정
DELETE → 삭제
📌 TCL
COMMIT → 저장!
ROLLBACK → 취소!
SAVEPOINT → 중간 저장
📌 트랜잭션 ACID
원자성 → 전부 성공 or 전부 실패
일관성 → 데이터가 항상 말이 돼야 함
독립성 → 내 작업 중 다른 사람 차단
지속성 → COMMIT하면 영원히 저장
📌 동시성
읽기+읽기 → 문제 없음
쓰기+쓰기 → Lock 필요
Dead Lock → 서로 기다림, MySQL 자동 해결
📌 LOCK
S Lock → 읽기만 OK
X Lock → 전부 차단
Row Lock → 행 하나만 잠금
Table Lock → 전체 잠금
📌 MVCC
수정 중에도 옛날 버전 읽기 가능
UNDO LOG 활용
📌 격리수준
MySQL 기본 = REPEATABLE READ
Oracle 기본 = READ COMMITTED
Dirty Read → COMMIT 안 한 값 읽기
Non-Repeatable → 같은 쿼리 결과 달라짐
Phantom Read → 없던 데이터 생김
📌 회복
체크포인트 이전 COMMIT → 냅둬
체크포인트 이후 COMMIT → REDO
체크포인트 이후 미완료 → UNDO