공부 일지 #18 | 이틀치 SQL 따라잡기: DDL, DML, TCL, INDEX, VIEW

학습 날짜: 2025-08-08

---

‘여름 감기는 개도 안 걸린다’고들 하는데, 나는 걸렸다. 그것도 꽤 심하게 걸려서 08/06~08/07 이틀 동안 수업을 빠질 수밖에 없었다.
다행히 실습 코드를 공유해주셔서, 그걸 보며 진도를 따라갈 수 있었다. 마침 정처기 공부도 병행 중이라 전반적인 내용은 어렵지 않게 이해할 수 있었다.코드를 하나씩 읽고 실행해보면서, 내가 이해한 내용을 정리해보았다.

1. DDL (DROP / CREATE / ALTER)

• 문자열 타입
  • CHAR(n): 고정 길이. 항상 n자리를 차지함
  • VARCHAR(n): 가변 길이. 입력된 글자 수만큼 저장됨
• PK / FK
  • PK(Primary Key): 테이블의 고유 식별자
  • FK(Foreign Key): 다른 테이블의 PK를 참조함

-- CONSTRAINT(PK설정)
ALTER TABLE TB_EMP ADD CONSTRAINT PK_TB_EMP PRIMARY KEY(EMP_NO);
-- CONSTRAINT(FK설정)
ALTER TABLE TB_EMP ADD CONSTRAINT FK_TB_EMP 
FOREIGN KEY (DEPT_CD) REFERENCES TB_DEPT(DEPT_CD);

• 참조 무결성
  • 자식 테이블의 FK는 반드시 부모 테이블의 PK에 존재해야 함
  • 존재하지 않는 값을 참조하거나 삭제하면 오류 발생

---

2. DML (SELECT / INSERT / UPDATE / DELETE)

• 테이블 간 삽입/삭제 순서
  • INSERT: 부모 테이블 → 자식 테이블 순
  • DELETE: 자식 테이블 → 부모 테이블 순
• FK 제약조건이 설정되어 있다면 반드시 위 순서를 지켜야 함

-- 현재상황 : TB_DEPT_TMP, TB_EMP_TMP 테이블간에 관계가 설정되어 있는 가정상황입니다.
-- 아래 DML Query는 수행되지 않습니다. why? 부모 테이블인 TB_DEPT_TMP에 'd01'이 없음
INSERT INTO TB_EMP_TMP (EMP_NO, EMP_NM, BIRTH_DATE, GENDER_CD, ADDR, HP_NO, DEPT_CD) 
VALUES ('e001', 'James', '20120101', 'M', '서울 마포구', '010123456789', 'd01');

-- 먼저 부모부터 넣어줘야 합니다.
INSERT INTO TB_DEPT_TMP VALUES ('d01', '데이터팀', '데이터팀 일단 TRY');
INSERT INTO TB_DEPT_TMP VALUES ('d02', '홍보팀', '홍보팀 일단 TRY');

SELECT * FROM TB_DEPT_TMP;

-- 그 후, 자식 데이터를 넣어주면 됩니다.
INSERT INTO TB_EMP_TMP (EMP_NO, EMP_NM, BIRTH_DATE, GENDER_CD, ADDR, HP_NO, DEPT_CD) 
VALUES ('e001', 'James', '20120101', 'M', '서울 마포구', '010123456789', 'd01');

INSERT INTO TB_EMP_TMP (EMP_NO, EMP_NM, GENDER_CD, BIRTH_DATE, ADDR, HP_NO, DEPT_CD) 
VALUES ('e002', '신사임당', 'F', '20101212', '서울 종로구', '01045679871', 'd02');

SELECT * FROM TB_EMP_TMP;

-- 아래 DML Query는 수행되지 않습니다. why? 부모인 TB_DEPT_TMP에 'd03'이 없음
INSERT INTO TB_EMP_TMP (EMP_NO, EMP_NM, GENDER_CD, BIRTH_DATE, ADDR, HP_NO, DEPT_CD) 
VALUES ('e003', '신사임당', 'F', '20101212', '서울 종로구', '01045679871', 'd03');

-- 이것은 수행됨.
INSERT INTO TB_EMP_TMP (EMP_NO, EMP_NM, GENDER_CD, BIRTH_DATE, ADDR, HP_NO, DEPT_CD) 
VALUES ('e003', '신사임당', 'F', '20101212', '서울 종로구', '01045679871', 'd02');
SELECT * FROM TB_EMP_TMP;

---

3. Transaction (ROLLBACK / COMMIT)

• 트랜잭션 개념
  • 데이터베이스의 논리적 연산단위를 의미
  • 하나의 트랜잭션에는 하나 이상의 SQL문장이 포함
  • 하나 이상의 SQL문장은 DML을 의미하는데, 이 중에 SELECT 제외 (Insert, Update, Delete)
  • 트랜잭션은 컨셉은 ALL or Nothing.
  • MySQL - Query메뉴 → Auto-Commit Transactions 체크해제 후 테스팅을 꺼야 명확히 실습 가능
• 동작 흐름
  • ROLLBACK: COMMIT 전이면 되돌릴 수 있음
  • COMMIT: 변경사항을 확정함 → 이후 ROLLBACK 불가
• Null관련 처리 함수: IFNULL, COALESCE 
  • IFNULL(A, B) → A가 NULL이면 B를 반환
  • COALESCE(A, B, C) → NULL이 아닌 첫 번째 값을 반환
    • 예: COALESCE(BONUS, SALARY, 250)
      → BONUS가 NULL이면 SALARY, 둘 다 NULL이면 250

---

4. Index

• 인덱스 없이도 SELECT는 동작함
  • 단, 데이터가 많을수록 성능 차이 극심
• 인덱스가 있으면
  • 전체 스캔 없이 빠르게 해당 값을 찾을 수 있음
  • 조건 예시: WHERE first_name = '민국'
• 성능 확인
  • EXPLAIN으로 인덱스 성능 확인 가능

# Index에 대한 성능 검증 (Index 건 후)
EXPLAIN analyze 
SELECT customer_id, date_of_birth, last_name, first_name 
FROM customer 
WHERE first_name = '진희';
#-> Index lookup on customer using idx_first_name (first_name='진희')  
# (cost=0.7 rows=2) (actual time=0.0299..0.032 rows=2 loops=1) : 
# 평균적으로 한번의 필터링에 0.032ms소요

---

5. View (가상 테이블)

• View의 개념
  • 여러 테이블을 조합한 가상의 테이블
  • 복잡한 쿼리를 재사용 가능하게 함
• 특징
  
  • 수정 불가 → 수정하려면 DROP 후 재생성

CREATE VIEW vt_emp_salary AS -- 조인결과를 가상공간에 만들어놓자 !
SELECT e.EMP_NO, e.EMP_NM, e.BIRTH_DATE, s.SAL_DATE, s.BONUS 
FROM tb_emp_tmp e, tb_salary s -- join 안써도 됨
WHERE e.EMP_NM = "신사임당" AND s.SALARY > 2000;

SELECT * FROM vt_emp_salary;