[TIL]SQL 심화: GROUP BY, 서브쿼리, 집합 연산, DML, 트랜잭션, DDL, 제약조건, 데이터 타입, 인덱스

1. 학습 주제

단순 조회를 넘어서 데이터를 집계하고, 가공하고, 변경하고, 구조 자체를 설계하는 SQL 흐름을 정리했다.
특히 조회 쿼리의 심화 문법뿐 아니라, 실제 데이터베이스를 안전하게 다루기 위한 트랜잭션, 제약조건, 인덱스 같은 핵심 개념까지 함께 익혔다.

• GROUP BY와 집계 함수 사용하기
• HAVING으로 그룹 결과 필터링하기
• 서브쿼리와 상관 서브쿼리 이해하기
• UNION, UNION ALL 등 집합 연산 익히기
• INSERT, UPDATE, DELETE, REPLACE 사용하기
• 트랜잭션의 COMMIT, ROLLBACK 이해하기
• CREATE, ALTER, DROP, TRUNCATE 정리하기
• NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY 등 제약조건 학습하기
• CAST, CONVERT로 형변환하기
• 인덱스의 역할과 장단점 이해하기

---

2. GROUP BY

GROUP BY는 같은 값을 가진 행들을 하나의 그룹으로 묶을 때 사용한다.

SELECT
    category_code,
    COUNT(*) AS '메뉴 개수'
FROM
    tbl_menu
GROUP BY
    category_code;

• category_code가 같은 메뉴끼리 묶인다.
• 각 그룹별 개수, 합계, 평균 같은 집계 결과를 구할 수 있다.

자주 함께 쓰는 집계 함수:

• COUNT() : 개수
• SUM() : 합계
• AVG() : 평균
• MAX() : 최댓값
• MIN() : 최솟값

SELECT
    category_code,
    SUM(menu_price) AS '가격 총합',
    AVG(menu_price) AS '가격 평균'
FROM
    tbl_menu
GROUP BY
    category_code;

중요한 점:

• GROUP BY를 사용할 때는 보통 GROUP BY에 사용한 컬럼과 집계 함수 결과만 SELECT절에 올 수 있다.
• 즉 그룹을 대표할 수 없는 일반 컬럼을 아무거나 함께 꺼내면 안 된다.

---

3. HAVING

HAVING은 그룹화가 끝난 결과에 조건을 거는 문법이다.

SELECT
    category_code,
    COUNT(*)
FROM
    tbl_menu
GROUP BY
    category_code
HAVING
    COUNT(*) >= 3;

• WHERE는 그룹화 전 원본 행을 필터링한다.
• HAVING은 그룹화 후 그룹 결과를 필터링한다.

즉 구분은 이렇게 이해하면 쉽다.

• WHERE : 행 기준 조건
• HAVING : 그룹 기준 조건

---

4. ROLLUP

WITH ROLLUP은 그룹별 집계 결과에 더해 총합까지 함께 보여준다.

SELECT
    category_code,
    SUM(menu_price)
FROM
    tbl_menu
GROUP BY
    category_code
WITH ROLLUP;

• 카테고리별 합계를 보여주고
• 마지막에 전체 총합도 추가로 보여준다

실무에서는 보고서, 통계 화면, 집계 결과 확인할 때 자주 유용할 수 있다.

---

5. 서브쿼리

서브쿼리는 SQL 안에 들어가는 또 다른 쿼리이다.
즉 “먼저 어떤 값을 구한 뒤, 그 결과를 바깥 쿼리에서 사용하는 방식”이다.

예제에서는 민트미역국과 같은 카테고리의 메뉴를 찾었다.

SELECT
    menu_name,
    category_code
FROM
    tbl_menu
WHERE
    category_code = (
        SELECT category_code
        FROM tbl_menu
        WHERE menu_name='민트미역국'
    );

이 흐름은 다음과 같다.

1. 안쪽 쿼리로 민트미역국의 카테고리 코드를 찾는다
2. 바깥 쿼리에서 그 카테고리와 같은 메뉴를 조회한다

즉 서브쿼리는 “한 번에 구하기 어려운 조건을 단계적으로 나눠서 해결하는 방식”이라고 볼 수 있다.

---

6. FROM절 서브쿼리와 파생 테이블

서브쿼리는 FROM절에도 들어갈 수 있다.
이 경우 임시 테이블처럼 동작하며, 이를 파생 테이블이라고 부른다.

SELECT
    MAX(count) AS '최대 메뉴 수'
FROM
    (
        SELECT COUNT(*) AS 'count'
        FROM tbl_menu
        GROUP BY category_code
    ) AS count_table;

• 먼저 카테고리별 메뉴 개수를 구한다
• 그 결과를 하나의 임시 테이블처럼 본다
• 그중 최댓값을 다시 구한다

중요한 점:

• FROM절의 서브쿼리는 반드시 별칭이 있어야 한다

---

7. 상관 서브쿼리

상관 서브쿼리는 안쪽 쿼리가 바깥쪽 쿼리의 현재 행 값을 참조하는 방식이다.

SELECT
    menu_code,
    menu_name,
    menu_price,
    category_code
FROM
    tbl_menu a
WHERE
    menu_price > (
        SELECT AVG(menu_price)
        FROM tbl_menu
        WHERE category_code = a.category_code
    );

이 쿼리는:

• 현재 메뉴가 속한 카테고리의 평균 가격을 구하고
• 그 평균보다 비싼 메뉴만 남긴다

즉 상관 서브쿼리는
“현재 보고 있는 행마다 안쪽 쿼리가 다시 계산되는 구조”라고 이해하면 좋다.

---

8. 집합 연산자

집합 연산자는 여러 SELECT 결과를 합치거나 비교할 때 사용한다.

8.1 UNION

SELECT ...
WHERE category_code = 10
UNION
SELECT ...
WHERE menu_price < 9000;

• 두 결과를 합친다
• 중복은 제거한다

8.2 UNION ALL

SELECT ...
UNION ALL
SELECT ...

• 두 결과를 그대로 합친다
• 중복도 포함된다

즉:

• UNION : 합치되 중복 제거
• UNION ALL : 그냥 전부 합치기

---

9. 교집합과 차집합

SQL에는 수학의 교집합/차집합 키워드가 항상 직접 있는 것은 아니므로, JOIN이나 IN을 활용해 구현할 수 있다.

교집합

WHERE
    category_code = 10
    AND menu_code IN ( ... )

• 두 조건을 동시에 만족하는 데이터만 남긴다

차집합

LEFT JOIN ...
WHERE
    a.category_code = 10 AND b.menu_code IS NULL;

• 한쪽에는 있지만 다른 쪽에는 없는 데이터를 찾을 수 있다

---

10. DML

DML은 데이터를 직접 조작하는 언어이다.

• INSERT
• UPDATE
• DELETE

10.1 INSERT

새로운 행을 추가한다.

INSERT INTO tbl_menu VALUES(null, '바나나해장국', 8500, 4, 'Y');

특정 컬럼만 지정할 수도 있다.

INSERT INTO tbl_menu(menu_name, menu_price, category_code, orderable_status)
VALUES ('초콜릿죽', 6500, 7, 'N');

여러 행을 한 번에 넣을 수도 있다.

10.2 UPDATE

기존 데이터를 수정한다.

UPDATE tbl_menu
SET category_code = 7
WHERE menu_code = 22;

중요한 점:

• WHERE 없이 실행하면 모든 행이 바뀔 수 있다

10.3 DELETE

특정 행을 삭제한다.

DELETE FROM tbl_menu
WHERE menu_code = 22;

이 역시 WHERE 없이 실행하면 전체 삭제가 될 수 있으므로 주의가 필요하다.

10.4 REPLACE

중복 키가 있을 경우 기존 행을 지우고 새 행으로 다시 넣는 방식이다.

REPLACE INTO tbl_menu VALUES(17, '참기름소주', 5000, 10, 'Y');

• 기본 키나 고유 키 충돌 시 덮어쓰기 같은 효과를 낸다

---

11. 트랜잭션

트랜잭션은 여러 작업을 하나의 묶음으로 처리하는 개념이다.

핵심은:

• 모두 성공하거나
• 모두 실패해야 한다

즉 All or Nothing이다.

START TRANSACTION;
INSERT ...
UPDATE ...
DELETE ...
ROLLBACK;
COMMIT;

주요 명령어

• START TRANSACTION : 트랜잭션 시작
• COMMIT : 작업 확정
• ROLLBACK : 작업 취소

예제에서는:

• 메뉴 추가
• 메뉴 수정
• 메뉴 삭제

를 하나의 작업 묶음으로 처리했다.

이런 구조가 중요한 이유는,
중간에 하나라도 문제가 생기면 전체를 원래 상태로 되돌려야 데이터 일관성이 깨지지 않기 때문이다.

---

12. DDL

DDL은 데이터 구조 자체를 정의하는 언어이다.

• CREATE
• ALTER
• DROP
• TRUNCATE

12.1 CREATE

테이블을 새로 만든다.

CREATE TABLE IF NOT EXISTS tb1(
    pk INT PRIMARY KEY,
    fk INT,
    col1 VARCHAR(255),
    CHECK(col1 IN ('Y', 'N'))
) ENGINE=INNODB;

12.2 AUTO_INCREMENT

기본 키 번호를 자동으로 증가시킨다.

pk INT AUTO_INCREMENT PRIMARY KEY

• 삽입할 때 직접 번호를 적지 않아도 된다

12.3 ALTER

테이블 구조를 수정한다.

• 컬럼 추가
• 컬럼 삭제
• 이름 변경
• 속성 변경
• 제약조건 추가/삭제

12.4 DROP

테이블 구조와 데이터 모두 삭제한다.

12.5 TRUNCATE

테이블 구조는 남기고 데이터만 초기화한다.

즉:

• DROP : 테이블 자체 삭제
• TRUNCATE : 내용만 비우기

---

13. 제약조건

제약조건은 잘못된 데이터가 들어오지 못하게 막는 규칙이다.
데이터 무결성을 지키는 데 매우 중요하다.

13.1 NOT NULL

반드시 값이 있어야 한다.

13.2 UNIQUE

중복 값을 허용하지 않는다.

13.3 PRIMARY KEY

행을 식별하는 대표 키이다.

• NOT NULL
• UNIQUE

의 성격을 모두 가진다.

즉 테이블에서 한 행을 구분하는 가장 중요한 컬럼이다.

13.4 FOREIGN KEY

다른 테이블의 값을 참조하는 제약조건이다.

FOREIGN KEY(grade_code) REFERENCES user_grade(grade_code)

• 부모 테이블에 없는 값은 넣을 수 없다
• 테이블 간 관계를 표현한다

13.5 ON UPDATE / ON DELETE

부모 값이 변경되거나 삭제될 때 자식 테이블을 어떻게 처리할지 정한다.

• SET NULL
• CASCADE

예:

• 부모가 삭제되면 자식도 함께 삭제
• 또는 참조값을 NULL로 변경

13.6 CHECK

들어올 수 있는 값의 범위를 제한한다.

gender VARCHAR(3) CHECK(gender IN ('남', '여'))
age INT CHECK(age >= 19)

13.7 DEFAULT

값을 생략했을 때 기본값을 자동으로 넣는다.

user_name VARCHAR(255) DEFAULT '아무개'

---

14. 데이터 타입과 형변환

CAST()와 CONVERT()를 이용하면 값을 원하는 타입으로 바꿔 볼 수 있다.

SELECT CAST(AVG(menu_price) AS SIGNED INTEGER);
SELECT CONVERT(AVG(menu_price), SIGNED INTEGER);
SELECT CAST('2026-05-15' AS DATE);

중요한 점:

• 원본 데이터를 바꾸는 것이 아니라
• 조회 결과를 원하는 타입으로 해석해서 보여주는 것이다

즉 형변환은 계산 결과를 정수로 보거나, 문자열을 날짜로 해석하는 데 유용하다.

---

15. 인덱스

인덱스는 데이터를 더 빨리 찾기 위한 구조이다.

CREATE INDEX idx_phone_name
ON phone(phone_name);

비유하면 책의 목차와 비슷하다.

• 목차가 있으면 원하는 페이지를 빨리 찾을 수 있다
• 인덱스가 있으면 WHERE 조건 검색이 더 빨라질 수 있다

예제에서는 EXPLAIN으로 실행 계획도 확인했다.

EXPLAIN
SELECT *
FROM phone
WHERE phone_name = 'iPhone 17 Pro';

인덱스의 장점

• 조회 속도 향상 가능
• 자주 검색되는 컬럼에 유리

인덱스의 단점

• INSERT, UPDATE, DELETE 때도 인덱스를 같이 관리해야 한다
• 무조건 많이 만든다고 좋은 것이 아니다

즉 인덱스는 “조회 최적화 도구”이지만, 쓰기 성능과 저장 공간 비용도 함께 고려해야 한다.

---

16. 학습 흐름 정리

16.1 조회 심화

• GROUP BY로 그룹 묶기
• HAVING으로 그룹 결과 필터링
• 서브쿼리와 상관 서브쿼리 활용
• UNION, UNION ALL로 결과 집합 결합

16.2 데이터 조작과 안정성

• INSERT, UPDATE, DELETE, REPLACE
• 트랜잭션으로 여러 작업을 하나처럼 처리
• COMMIT, ROLLBACK으로 확정/취소

16.3 구조 설계

• CREATE, ALTER, DROP, TRUNCATE
• 제약조건으로 데이터 무결성 유지
• 형변환 함수로 값 해석
• 인덱스로 조회 성능 개선

---

17. 핵심 정리

1. GROUP BY는 같은 값을 가진 행들을 하나의 그룹으로 묶는다.
2. 그룹별 필터링은 WHERE가 아니라 HAVING을 사용한다.
3. 서브쿼리는 쿼리 안에서 다른 쿼리 결과를 활용하는 방식이다.
4. UNION은 중복 제거 후 합치고, UNION ALL은 중복까지 포함해 합친다.
5. INSERT, UPDATE, DELETE는 데이터를 직접 바꾸는 DML이다.
6. 트랜잭션은 여러 작업을 하나의 묶음으로 처리하며, COMMIT과 ROLLBACK으로 관리한다.
7. DDL은 테이블 구조를 만들고 수정하고 삭제하는 명령이다.
8. 제약조건은 잘못된 데이터 입력을 막아 데이터 무결성을 지키는 규칙이다.
9. PRIMARY KEY는 행을 구분하는 대표 키이고, FOREIGN KEY는 테이블 간 관계를 표현한다.
10. 인덱스는 조회를 빠르게 도와주지만, 쓰기 작업에는 부담이 될 수 있다.