SQL 완전 정복: SQL 핵심 개념 총정리

🎯 들어가며

SQL(Structured Query Language)은 데이터베이스를 다루는 개발자라면 반드시 마스터해야 할 필수 언어입니다. 오늘은 SQL의 핵심 개념들을 마인드맵 형태로 체계적으로 정리해보겠습니다. 이 포스트를 통해 SQL의 전체적인 구조를 한눈에 파악하고, 각 명령어의 용도와 활용법을 완벽하게 이해할 수 있을 것입니다.

---

📚 SQL 언어의 4대 범주

SQL은 크게 4가지 범주로 나뉩니다. 각각의 역할과 특징을 살펴보겠습니다.

1. DDL (Data Definition Language) - 데이터 정의어 🏗️

DDL은 데이터베이스의 구조를 정의하는 명령어들입니다. 테이블, 뷰, 인덱스 등의 구조를 생성하고 변경하는 역할을 합니다.

CREATE - 생성하기

-- 데이터베이스 생성
CREATE DATABASE shop_db;

-- 테이블 생성
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 뷰 생성 (가상 테이블)
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

DROP - 삭제하기

-- 테이블 완전 삭제 (데이터와 구조 모두 삭제)
DROP TABLE users;

-- 데이터베이스 삭제 (주의! 복구 불가능)
DROP DATABASE shop_db;

TRUNCATE - 초기화하기

-- 테이블 데이터만 전체 삭제 (구조는 유지)
-- DELETE보다 빠르지만 롤백 불가능
TRUNCATE TABLE orders;

ALTER - 구조 변경하기

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

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

-- 컬럼 수정 (데이터 타입, 제약조건 변경)
ALTER TABLE users MODIFY email VARCHAR(255);

-- 제약조건 추가
ALTER TABLE users ADD CONSTRAINT check_age CHECK (age >= 18);

🔑 주요 제약조건 (Constraints)

• UNIQUE: 중복 값 허용 안 함
• NOT NULL: NULL 값 허용 안 함
• PRIMARY KEY: 기본키 (UNIQUE + NOT NULL)
• FOREIGN KEY: 외래키 (다른 테이블 참조)
• CHECK: 특정 조건 검증
• DEFAULT: 기본값 설정

---

2. DML (Data Manipulation Language) - 데이터 조작어 📝

DML은 실제 데이터를 조작하는 명령어들입니다. 일상적으로 가장 많이 사용되는 SQL 명령어들이죠.

SELECT - 데이터 조회

-- 특정 컬럼 조회
SELECT username, email FROM users;

-- 모든 컬럼 조회
SELECT * FROM users;

-- 조건부 조회
SELECT * FROM users WHERE age >= 20 AND city = '서울';

INSERT - 데이터 삽입

-- 전체 컬럼에 데이터 삽입
INSERT INTO users VALUES (1, 'kim', 'kim@email.com', 25);

-- 특정 컬럼에만 데이터 삽입
INSERT INTO users (username, email) VALUES ('park', 'park@email.com');

-- 다른 테이블에서 데이터 복사
INSERT INTO backup_users SELECT * FROM users WHERE created_at < '2024-01-01';

UPDATE - 데이터 수정

-- 특정 조건의 데이터 수정
UPDATE users SET email = 'newemail@email.com' WHERE user_id = 1;

-- 여러 컬럼 동시 수정
UPDATE products SET price = price * 1.1, updated_at = NOW() WHERE category = 'electronics';

DELETE - 데이터 삭제

-- 특정 조건의 데이터 삭제
DELETE FROM users WHERE last_login < '2023-01-01';

-- 모든 데이터 삭제 (테이블 구조는 유지)
DELETE FROM temp_data;

---

3. DCL (Data Control Language) - 데이터 제어어 🔐

DCL은 데이터베이스 권한 관리를 담당합니다. 보안과 접근 제어를 위한 명령어들입니다.

GRANT - 권한 부여

-- 특정 사용자에게 SELECT 권한 부여
GRANT SELECT ON database.users TO 'john'@'localhost';

-- 모든 권한 부여
GRANT ALL PRIVILEGES ON database.* TO 'admin'@'%';

-- 특정 컬럼에 대한 권한 부여
GRANT SELECT(username, email) ON database.users TO 'viewer'@'localhost';

REVOKE - 권한 회수

-- 권한 회수
REVOKE INSERT, UPDATE ON database.users FROM 'john'@'localhost';

-- 모든 권한 회수
REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'localhost';

---

4. TCL (Transaction Control Language) - 트랜잭션 제어어 💾

TCL은 트랜잭션을 관리하는 명령어들입니다. 데이터의 일관성과 무결성을 보장합니다.

COMMIT - 변경사항 확정

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 10000 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE user_id = 2;
COMMIT; -- 두 작업을 모두 확정

ROLLBACK - 변경사항 취소

BEGIN TRANSACTION;
DELETE FROM orders WHERE order_date < '2023-01-01';
-- 실수를 발견!
ROLLBACK; -- 삭제 작업 취소

SAVEPOINT - 중간 저장점

BEGIN TRANSACTION;
UPDATE products SET price = price * 1.1;
SAVEPOINT price_update;

UPDATE products SET stock = stock - 10;
-- 재고 업데이트에 문제 발생
ROLLBACK TO price_update; -- 가격 업데이트는 유지, 재고 업데이트만 취소

COMMIT;

---

🔗 JOIN - 테이블 연결하기

JOIN은 여러 테이블의 데이터를 연결하여 조회하는 강력한 기능입니다.

INNER JOIN - 교집합

두 테이블에 모두 존재하는 데이터만 조회

SELECT u.username, o.order_id, o.total_amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;

LEFT JOIN - 왼쪽 테이블 기준

왼쪽 테이블의 모든 데이터 + 오른쪽 테이블의 매칭 데이터

-- 주문이 없는 사용자도 포함하여 조회
SELECT u.username, COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id;

RIGHT JOIN - 오른쪽 테이블 기준

오른쪽 테이블의 모든 데이터 + 왼쪽 테이블의 매칭 데이터

-- 사용자 정보가 없는 주문도 포함
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.user_id = o.user_id;

FULL JOIN - 합집합

양쪽 테이블의 모든 데이터 (MySQL은 UNION으로 구현)

-- PostgreSQL
SELECT * FROM users u
FULL OUTER JOIN orders o ON u.user_id = o.user_id;

-- MySQL (UNION 사용)
SELECT * FROM users u LEFT JOIN orders o ON u.user_id = o.user_id
UNION
SELECT * FROM users u RIGHT JOIN orders o ON u.user_id = o.user_id;

---

📊 GROUP BY & 집계 함수

데이터를 그룹화하고 집계하는 것은 데이터 분석의 핵심입니다.

GROUP BY - 그룹화

-- 도시별 사용자 수 계산
SELECT city, COUNT(*) as user_count
FROM users
GROUP BY city;

-- 여러 컬럼으로 그룹화
SELECT category, brand, AVG(price) as avg_price
FROM products
GROUP BY category, brand;

HAVING - 그룹 조건

WHERE는 개별 행에, HAVING은 그룹에 조건을 적용합니다.

-- 주문 횟수가 5회 이상인 사용자만 조회
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 5;

주요 집계 함수

-- COUNT(): 개수 세기
SELECT COUNT(DISTINCT user_id) FROM orders;

-- SUM(): 합계
SELECT SUM(total_amount) FROM orders WHERE order_date = CURDATE();

-- AVG(): 평균
SELECT category, AVG(price) FROM products GROUP BY category;

-- MIN() / MAX(): 최솟값 / 최댓값
SELECT MIN(price) as lowest_price, MAX(price) as highest_price FROM products;

---

🎯 ORDER BY - 정렬하기

결과를 보기 좋게 정렬하는 것은 매우 중요합니다.

-- 오름차순 정렬 (기본값)
SELECT * FROM products ORDER BY price;
SELECT * FROM products ORDER BY price ASC;

-- 내림차순 정렬
SELECT * FROM users ORDER BY created_at DESC;

-- 다중 정렬 (첫 번째 기준으로 정렬 후, 같은 값은 두 번째 기준으로)
SELECT * FROM products 
ORDER BY category ASC, price DESC;

---

🪟 Window Functions - 윈도우 함수

윈도우 함수는 행과 행 간의 관계를 정의하여 순위, 누적값 등을 계산합니다.

OVER() - 윈도우 정의

-- 전체 평균과 각 제품 가격 비교
SELECT product_name, price,
       AVG(price) OVER() as overall_avg
FROM products;

ROW_NUMBER() - 순번 부여

-- 카테고리별 가격 순위
SELECT product_name, category, price,
       ROW_NUMBER() OVER(PARTITION BY category ORDER BY price DESC) as price_rank
FROM products;

RANK() / DENSE_RANK() - 순위 부여

-- RANK(): 동일 순위 후 건너뜀 (1,2,2,4)
-- DENSE_RANK(): 동일 순위 후 연속 (1,2,2,3)
SELECT username, score,
       RANK() OVER(ORDER BY score DESC) as rank,
       DENSE_RANK() OVER(ORDER BY score DESC) as dense_rank
FROM game_scores;

LAG() / LEAD() - 이전/다음 행 값

-- 이전 주문과의 간격 계산
SELECT order_id, user_id, order_date,
       LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date) as prev_order,
       DATEDIFF(order_date, LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date)) as days_between
FROM orders;

NTILE() - 그룹 분할

-- 사용자를 4개 그룹으로 분할 (사분위수)
SELECT username, total_purchase,
       NTILE(4) OVER(ORDER BY total_purchase DESC) as quartile
FROM user_purchases;

---

🔍 조건 연산자

SQL에서 데이터를 필터링할 때 사용하는 주요 연산자들입니다.

비교 연산자

-- 기본 비교
SELECT * FROM products WHERE price > 10000;
SELECT * FROM users WHERE age >= 20 AND age <= 30;

-- NULL 체크
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

논리 연산자

-- AND, OR, NOT
SELECT * FROM products 
WHERE (category = 'electronics' OR category = 'computers') 
  AND price < 1000000
  AND brand NOT IN ('Unknown', 'Generic');

패턴 매칭

-- LIKE: 패턴 검색
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM products WHERE name LIKE '삼성%'; -- 삼성으로 시작
SELECT * FROM products WHERE code LIKE 'P__001'; -- _는 한 글자

-- BETWEEN: 범위 검색
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- IN: 목록 포함 여부
SELECT * FROM products WHERE category IN ('electronics', 'books', 'clothing');

서브쿼리 연산자

-- EXISTS: 존재 여부
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.user_id
);

-- ANY/ALL: 서브쿼리 결과와 비교
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'books');

SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'books');

---

📛 Alias (별칭)

코드 가독성을 높이고 복잡한 쿼리를 간단하게 만들어줍니다.

-- 테이블 별칭
SELECT u.username, o.order_date
FROM users AS u
JOIN orders AS o ON u.user_id = o.user_id;

-- 컬럼 별칭
SELECT 
    username AS '사용자명',
    COUNT(*) AS '주문횟수',
    SUM(total_amount) AS '총구매액'
FROM users u
JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id;

-- 서브쿼리 별칭
SELECT * FROM (
    SELECT category, AVG(price) as avg_price
    FROM products
    GROUP BY category
) AS category_stats
WHERE avg_price > 50000;

---

💡 실전 활용 팁

1. 성능 최적화

• 인덱스 활용: 자주 조회하는 컬럼에 인덱스 생성
• 적절한 JOIN 선택: INNER JOIN이 LEFT JOIN보다 빠름
• 서브쿼리 대신 JOIN: 가능하면 JOIN으로 대체

2. 안전한 데이터 작업

• 트랜잭션 사용: 중요한 작업은 트랜잭션으로 묶기
• 백업 우선: DDL 작업 전 반드시 백업
• WHERE 절 확인: UPDATE/DELETE 시 WHERE 절 필수

3. 가독성 향상

• 일관된 대소문자: SQL 키워드는 대문자로
• 적절한 들여쓰기: 복잡한 쿼리는 들여쓰기로 구조화
• 의미 있는 별칭: 약어보다는 의미를 알 수 있는 별칭 사용

---

🎓 마무리

SQL은 데이터를 다루는 가장 강력한 도구입니다. 이 마인드맵에서 정리한 개념들을 체계적으로 학습하면, 어떤 데이터베이스 작업도 자신있게 수행할 수 있을 것입니다.

각 카테고리별 명령어들을 실습하면서 익히고, 실제 프로젝트에 적용해보세요. DDL로 구조를 설계하고, DML로 데이터를 조작하며, DCL로 보안을 관리하고, TCL로 안전하게 작업하는 것. 이 모든 과정이 조화롭게 이루어질 때 진정한 SQL 마스터가 될 수 있습니다.

Remember: SQL은 단순한 쿼리 언어가 아니라, 데이터와 대화하는 언어입니다! 💪

---

📚 추천 학습 순서

1. DML (SELECT, INSERT, UPDATE, DELETE) - 기본 데이터 조작
2. JOIN - 테이블 연결
3. GROUP BY & 집계 함수 - 데이터 분석
4. DDL - 테이블 설계
5. Window Functions - 고급 분석
6. TCL - 트랜잭션 관리
7. DCL - 권한 관리

이제 SQL의 전체 그림을 머릿속에 그리셨나요? 실습을 통해 각 개념을 자신의 것으로 만들어보세요! 🚀