4. SQL

SQL 개요

SQL(Structured Query Language)은 관계형 데이터베이스를 관리하고 조작하기 위한 표준 언어다.

SQL 분류

1. DDL (Data Definition Language) - 데이터 정의
   CREATE, ALTER, DROP, TRUNCATE

2. DML (Data Manipulation Language) - 데이터 조작
   SELECT, INSERT, UPDATE, DELETE

3. DCL (Data Control Language) - 데이터 제어
   GRANT, REVOKE

4. TCL (Transaction Control Language) - 트랜잭션 제어
   COMMIT, ROLLBACK, SAVEPOINT

DDL (데이터 정의어)

CREATE

-- 테이블 생성
CREATE TABLE Student (
    id       INT          PRIMARY KEY,
    name     VARCHAR(50)  NOT NULL,
    dept     VARCHAR(30)  DEFAULT 'Undeclared',
    grade    INT          CHECK (grade BETWEEN 1 AND 4),
    email    VARCHAR(100) UNIQUE,
    dept_id  INT,
    FOREIGN KEY (dept_id) REFERENCES Department(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- 제약 조건 정리
-- PRIMARY KEY: 기본키 (NOT NULL + UNIQUE)
-- NOT NULL: NULL 불허
-- UNIQUE: 중복 불허 (NULL은 허용)
-- CHECK: 값 범위 제한
-- DEFAULT: 기본값
-- FOREIGN KEY: 외래키 참조

ALTER

-- 컬럼 추가
ALTER TABLE Student ADD COLUMN phone VARCHAR(20);

-- 컬럼 삭제
ALTER TABLE Student DROP COLUMN phone;

-- 컬럼 수정
ALTER TABLE Student MODIFY COLUMN name VARCHAR(100) NOT NULL;

-- 제약 조건 추가
ALTER TABLE Student ADD CONSTRAINT chk_grade CHECK (grade >= 1);

-- 제약 조건 삭제
ALTER TABLE Student DROP CONSTRAINT chk_grade;

DROP / TRUNCATE

-- 테이블 삭제 (스키마 + 데이터 전체)
DROP TABLE Student;

-- 참조하는 FK가 있으면 CASCADE 필요
DROP TABLE Student CASCADE;

-- 데이터만 삭제 (스키마 유지, 롤백 불가)
TRUNCATE TABLE Student;

-- DROP vs TRUNCATE vs DELETE
-- DROP: 테이블 자체 삭제
-- TRUNCATE: 데이터만 삭제 (빠름, DDL, 롤백 불가)
-- DELETE: 데이터 삭제 (느림, DML, 롤백 가능, WHERE 사용 가능)

DML (데이터 조작어)

SELECT

-- 기본 구조
SELECT [DISTINCT] 컬럼1, 컬럼2, ...
FROM 테이블
[WHERE 조건]
[GROUP BY 컬럼]
[HAVING 그룹 조건]
[ORDER BY 컬럼 [ASC|DESC]]
[LIMIT n];

-- 실행 순서 (작성 순서와 다름!)
-- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

-- 기본 조회
SELECT * FROM Student;
SELECT name, dept FROM Student;
SELECT DISTINCT dept FROM Student;

-- 조건 조회
SELECT * FROM Student WHERE grade >= 3;
SELECT * FROM Student WHERE dept = 'CS' AND grade = 4;
SELECT * FROM Student WHERE dept IN ('CS', 'EE', 'ME');
SELECT * FROM Student WHERE name LIKE '김%';      -- 김으로 시작
SELECT * FROM Student WHERE email IS NULL;
SELECT * FROM Student WHERE grade BETWEEN 2 AND 4;

-- 정렬
SELECT * FROM Student ORDER BY grade DESC, name ASC;

-- 별칭 (Alias)
SELECT s.name AS student_name, d.name AS dept_name
FROM Student s, Department d
WHERE s.dept_id = d.id;

집계 함수

-- COUNT, SUM, AVG, MAX, MIN
SELECT COUNT(*) FROM Student;                    -- 전체 행 수
SELECT COUNT(DISTINCT dept) FROM Student;        -- 중복 제거 후 수
SELECT AVG(grade) FROM Student WHERE dept = 'CS';
SELECT MAX(grade), MIN(grade) FROM Student;

-- GROUP BY
SELECT dept, COUNT(*) AS cnt, AVG(grade) AS avg_grade
FROM Student
GROUP BY dept;

-- HAVING (그룹에 대한 조건)
SELECT dept, COUNT(*) AS cnt
FROM Student
GROUP BY dept
HAVING COUNT(*) >= 5;

-- WHERE vs HAVING
-- WHERE: 그룹화 전에 행 필터링
-- HAVING: 그룹화 후에 그룹 필터링

INSERT

-- 단일 행 삽입
INSERT INTO Student (id, name, dept, grade)
VALUES (1001, '홍길동', 'CS', 3);

-- 전체 컬럼 삽입 (컬럼 순서 일치 필요)
INSERT INTO Student VALUES (1002, '김영희', 'EE', 2, 'kim@mail.com', 1);

-- 다중 행 삽입
INSERT INTO Student (id, name, dept, grade) VALUES
    (1003, '이철수', 'ME', 4),
    (1004, '박민수', 'CS', 1);

-- 서브쿼리로 삽입
INSERT INTO CSStudent (id, name)
SELECT id, name FROM Student WHERE dept = 'CS';

UPDATE

-- 조건부 수정
UPDATE Student SET grade = 4 WHERE id = 1001;

-- 여러 컬럼 수정
UPDATE Student
SET dept = 'EE', grade = grade + 1
WHERE name = '홍길동';

-- 서브쿼리 활용
UPDATE Student
SET dept_id = (SELECT id FROM Department WHERE name = 'CS')
WHERE id = 1001;

-- 주의: WHERE 없으면 전체 행 수정!
UPDATE Student SET grade = 1;  -- 위험!

DELETE

-- 조건부 삭제
DELETE FROM Student WHERE id = 1001;

-- 전체 삭제 (롤백 가능)
DELETE FROM Student;

-- 주의: WHERE 없으면 전체 행 삭제!
-- TRUNCATE와 달리 DML이므로 롤백 가능

JOIN

조인 종류

1. INNER JOIN: 양쪽 모두 매칭되는 행만
2. LEFT (OUTER) JOIN: 왼쪽 전체 + 오른쪽 매칭
3. RIGHT (OUTER) JOIN: 오른쪽 전체 + 왼쪽 매칭
4. FULL (OUTER) JOIN: 양쪽 전체 (매칭 안 되면 NULL)
5. CROSS JOIN: 카티션 곱 (모든 조합)
6. SELF JOIN: 같은 테이블 간 조인

조인 문법

-- INNER JOIN
SELECT s.name, d.name AS dept_name
FROM Student s
INNER JOIN Department d ON s.dept_id = d.id;

-- LEFT JOIN (왼쪽 테이블의 모든 행 포함)
SELECT s.name, d.name AS dept_name
FROM Student s
LEFT JOIN Department d ON s.dept_id = d.id;
-- dept_id가 NULL인 학생도 결과에 포함 (dept_name은 NULL)

-- RIGHT JOIN
SELECT s.name, d.name AS dept_name
FROM Student s
RIGHT JOIN Department d ON s.dept_id = d.id;
-- 학생이 없는 학과도 결과에 포함

-- FULL OUTER JOIN (MySQL은 미지원, UNION으로 대체)
SELECT s.name, d.name
FROM Student s
LEFT JOIN Department d ON s.dept_id = d.id
UNION
SELECT s.name, d.name
FROM Student s
RIGHT JOIN Department d ON s.dept_id = d.id;

-- CROSS JOIN (카티션 곱)
SELECT s.name, c.course_name
FROM Student s
CROSS JOIN Course c;

-- SELF JOIN (같은 테이블)
SELECT e1.name AS 직원, e2.name AS 상사
FROM Employee e1
JOIN Employee e2 ON e1.manager_id = e2.id;

조인 시각화

INNER JOIN:
  A  [  A∩B  ]  B
      ^^^^^^

LEFT JOIN:
  [A     A∩B  ]  B
  ^^^^^^^^^^^^

RIGHT JOIN:
  A  [  A∩B     B]
      ^^^^^^^^^^^^

FULL OUTER JOIN:
  [A     A∩B     B]
  ^^^^^^^^^^^^^^^^

서브쿼리 (Subquery)

위치별 분류

-- 1. WHERE 절 서브쿼리 (가장 흔함)
SELECT name FROM Student
WHERE dept_id = (SELECT id FROM Department WHERE name = 'CS');

-- 2. FROM 절 서브쿼리 (인라인 뷰)
SELECT avg_grade
FROM (
    SELECT dept, AVG(grade) AS avg_grade
    FROM Student
    GROUP BY dept
) AS dept_avg
WHERE avg_grade >= 3;

-- 3. SELECT 절 서브쿼리 (스칼라 서브쿼리)
SELECT name,
    (SELECT name FROM Department WHERE id = s.dept_id) AS dept_name
FROM Student s;

서브쿼리 연산자

-- IN: 서브쿼리 결과에 포함되는지
SELECT name FROM Student
WHERE dept_id IN (SELECT id FROM Department WHERE building = '공학관');

-- EXISTS: 서브쿼리 결과가 존재하는지 (TRUE/FALSE)
SELECT name FROM Student s
WHERE EXISTS (
    SELECT 1 FROM Enrollment e WHERE e.student_id = s.id
);

-- ANY/SOME: 서브쿼리 결과 중 하나라도 만족
SELECT name FROM Student
WHERE grade > ANY (SELECT grade FROM Student WHERE dept = 'CS');

-- ALL: 서브쿼리 결과 모두 만족
SELECT name FROM Student
WHERE grade >= ALL (SELECT grade FROM Student WHERE dept = 'CS');

-- 상관 서브쿼리: 외부 쿼리의 값을 참조
SELECT s.name
FROM Student s
WHERE s.grade > (
    SELECT AVG(grade) FROM Student WHERE dept = s.dept
);

IN vs EXISTS

-- IN: 서브쿼리 결과를 먼저 구하고 비교
-- → 서브쿼리 결과가 작을 때 유리

-- EXISTS: 외부 행마다 서브쿼리 실행하여 존재 여부만 확인
-- → 서브쿼리 결과가 클 때 유리 (조기 종료 가능)
-- → 상관 서브쿼리에서 효율적

View

-- 뷰 생성 (가상 테이블)
CREATE VIEW CS_Student AS
SELECT id, name, grade
FROM Student
WHERE dept = 'CS';

-- 뷰 조회 (일반 테이블처럼 사용)
SELECT * FROM CS_Student WHERE grade >= 3;

-- 뷰 삭제
DROP VIEW CS_Student;

-- 뷰의 장점
-- 1. 보안: 민감한 컬럼 숨기기
-- 2. 편의성: 복잡한 쿼리를 간단하게
-- 3. 논리적 독립성: 테이블 구조 변경 시 뷰만 수정

-- 뷰의 제한
-- 집계 함수, GROUP BY, DISTINCT를 포함한 뷰는 UPDATE 불가
-- JOIN을 포함한 뷰는 INSERT/DELETE가 제한적

기타 주요 문법

집합 연산

-- UNION: 합집합 (중복 제거)
SELECT name FROM Student WHERE dept = 'CS'
UNION
SELECT name FROM Student WHERE grade = 4;

-- UNION ALL: 합집합 (중복 허용)
SELECT name FROM Student WHERE dept = 'CS'
UNION ALL
SELECT name FROM Student WHERE grade = 4;

-- INTERSECT: 교집합
SELECT name FROM Student WHERE dept = 'CS'
INTERSECT
SELECT name FROM Student WHERE grade = 4;

-- EXCEPT (MINUS): 차집합
SELECT name FROM Student WHERE dept = 'CS'
EXCEPT
SELECT name FROM Student WHERE grade = 4;

CASE 문

SELECT name, grade,
    CASE
        WHEN grade = 4 THEN '4학년'
        WHEN grade = 3 THEN '3학년'
        WHEN grade = 2 THEN '2학년'
        ELSE '1학년'
    END AS grade_text
FROM Student;

윈도우 함수

-- ROW_NUMBER: 순번
SELECT name, grade,
    ROW_NUMBER() OVER (ORDER BY grade DESC) AS rn
FROM Student;

-- RANK: 순위 (동순위 시 건너뜀)
SELECT name, grade,
    RANK() OVER (ORDER BY grade DESC) AS rnk
FROM Student;

-- DENSE_RANK: 순위 (동순위 시 안 건너뜀)
SELECT name, grade,
    DENSE_RANK() OVER (ORDER BY grade DESC) AS drnk
FROM Student;

-- PARTITION BY: 그룹별 윈도우
SELECT name, dept, grade,
    RANK() OVER (PARTITION BY dept ORDER BY grade DESC) AS dept_rank
FROM Student;

-- 집계 윈도우 함수
SELECT name, dept, grade,
    AVG(grade) OVER (PARTITION BY dept) AS dept_avg,
    SUM(grade) OVER () AS total_sum
FROM Student;

DCL

-- 권한 부여
GRANT SELECT, INSERT ON Student TO user1;
GRANT ALL PRIVILEGES ON Student TO admin WITH GRANT OPTION;

-- 권한 회수
REVOKE INSERT ON Student FROM user1;
REVOKE ALL PRIVILEGES ON Student FROM admin CASCADE;

-- WITH GRANT OPTION: 받은 권한을 다른 사용자에게 부여 가능
-- CASCADE: 연쇄적으로 권한 회수

핵심 정리

1. DDL: CREATE, ALTER, DROP (스키마 정의)
2. DML: SELECT, INSERT, UPDATE, DELETE (데이터 조작)
3. SELECT 실행 순서: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
4. JOIN: INNER, LEFT, RIGHT, FULL, CROSS, SELF
5. 서브쿼리: WHERE(조건), FROM(인라인 뷰), SELECT(스칼라)
6. View: 가상 테이블 (보안, 편의, 독립성)
7. WHERE vs HAVING: 행 필터 vs 그룹 필터
8. TRUNCATE vs DELETE: DDL(롤백불가/빠름) vs DML(롤백가능/느림)

면접 포인트

자주 나오는 질문

Q1. INNER JOIN과 OUTER JOIN의 차이는?

• INNER JOIN: 양쪽 모두 매칭되는 행만 반환
• OUTER JOIN: 매칭되지 않는 행도 포함 (NULL로 채움)
• LEFT/RIGHT/FULL로 어느 쪽을 포함할지 결정

Q2. WHERE와 HAVING의 차이는?

• WHERE: GROUP BY 이전에 개별 행 필터링
• HAVING: GROUP BY 이후에 그룹 필터링
• HAVING은 집계 함수와 함께 사용

Q3. DELETE, TRUNCATE, DROP의 차이는?

• DELETE: DML, 행 단위 삭제, WHERE 가능, 롤백 가능
• TRUNCATE: DDL, 전체 데이터 삭제, 롤백 불가, 빠름
• DROP: DDL, 테이블 자체 삭제 (스키마 + 데이터)

Q4. 서브쿼리 vs 조인, 어떤 것이 더 좋은가?

• 성능은 옵티마이저에 따라 다름 (동일한 경우 많음)
• JOIN: 여러 테이블의 컬럼을 함께 조회할 때 직관적
• 서브쿼리: 한쪽 테이블의 결과를 조건으로 쓸 때 직관적
• EXISTS + 상관 서브쿼리: 존재 여부만 확인할 때 효율적

Q5. View의 장점과 제한은?

• 장점: 보안(컬럼 숨김), 편의성(복잡한 쿼리 캡슐화), 논리적 독립성
• 제한: 집계/GROUP BY 포함 뷰는 갱신 불가
• Materialized View: 결과를 물리적으로 저장 (읽기 성능 향상)

Q6. SELECT 문의 실행 순서는?

• FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
• 이 순서 때문에 SELECT에서 정의한 별칭을 WHERE에서 사용 불가
• HAVING에서는 GROUP BY의 컬럼과 집계 함수만 사용 가능