SQL 기초와 활용

2025-11-10

SQL 완벽 가이드: 기초부터 고급까지

서론

SQL(Structured Query Language)은 관계형 데이터베이스 관리 시스템(RDBMS)에서 데이터를 조작하고 관리하기 위한 표준 언어입니다. 웹 개발에서 데이터베이스는 핵심적인 역할을 하며, 효율적인 SQL 작성 능력은 개발자에게 필수적입니다. 이 글에서는 SQL의 기초부터 고급 기법까지 실용적인 예제와 함께 다루겠습니다.

1. SQL 기본 문법

1.1 SELECT 문

데이터 조회의 기본:

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

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

-- 별칭(Alias) 사용
SELECT 
  id AS user_id,
  name AS user_name,
  email AS user_email
FROM users;

1.2 WHERE 절

조건부 데이터 조회:

-- 단일 조건
SELECT * FROM users WHERE age > 18;

-- 여러 조건 (AND, OR)
SELECT * FROM users 
WHERE age > 18 AND status = 'active';

SELECT * FROM users 
WHERE age > 18 OR status = 'premium';

-- IN 연산자
SELECT * FROM users 
WHERE status IN ('active', 'premium', 'vip');

-- LIKE 연산자 (패턴 매칭)
SELECT * FROM users 
WHERE email LIKE '%@gmail.com';

-- BETWEEN 연산자
SELECT * FROM users 
WHERE age BETWEEN 20 AND 30;

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

1.3 ORDER BY

정렬:

-- 오름차순 정렬
SELECT * FROM users ORDER BY name ASC;

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

-- 여러 컬럼 정렬
SELECT * FROM users 
ORDER BY status ASC, created_at DESC;

1.4 LIMIT과 OFFSET

결과 제한:

-- 상위 10개만 조회
SELECT * FROM users LIMIT 10;

-- 페이징 처리
SELECT * FROM users 
ORDER BY id DESC 
LIMIT 10 OFFSET 20; -- 21번째부터 10개

2. 데이터 조작 (DML)

2.1 INSERT

데이터 삽입:

-- 단일 행 삽입
INSERT INTO users (name, email, age) 
VALUES ('홍길동', 'hong@example.com', 25);

-- 여러 행 한 번에 삽입
INSERT INTO users (name, email, age) 
VALUES 
  ('김철수', 'kim@example.com', 30),
  ('이영희', 'lee@example.com', 28),
  ('박민수', 'park@example.com', 32);

-- SELECT 결과를 INSERT
INSERT INTO users_backup (name, email, age)
SELECT name, email, age FROM users 
WHERE created_at < '2024-01-01';

2.2 UPDATE

데이터 수정:

-- 단일 행 수정
UPDATE users 
SET email = 'newemail@example.com' 
WHERE id = 1;

-- 여러 컬럼 수정
UPDATE users 
SET 
  email = 'newemail@example.com',
  age = 26,
  updated_at = NOW()
WHERE id = 1;

-- 조건부 수정
UPDATE users 
SET status = 'inactive' 
WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR);

2.3 DELETE

데이터 삭제:

-- 조건부 삭제
DELETE FROM users WHERE id = 1;

-- 여러 조건 삭제
DELETE FROM users 
WHERE status = 'inactive' 
AND last_login < DATE_SUB(NOW(), INTERVAL 2 YEAR);

-- 전체 삭제 (주의!)
DELETE FROM users;
-- 또는
TRUNCATE TABLE users;

3. 집계 함수

3.1 기본 집계 함수

-- COUNT: 행 개수
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE status = 'active';

-- SUM: 합계
SELECT SUM(price) FROM orders;

-- AVG: 평균
SELECT AVG(age) FROM users;

-- MAX/MIN: 최대/최소값
SELECT MAX(price) FROM products;
SELECT MIN(price) FROM products;

3.2 GROUP BY

그룹별 집계:

-- 상태별 사용자 수
SELECT status, COUNT(*) as count 
FROM users 
GROUP BY status;

-- 연도별 주문 금액 합계
SELECT 
  YEAR(created_at) as year,
  SUM(total_amount) as total
FROM orders 
GROUP BY YEAR(created_at);

-- 여러 컬럼으로 그룹화
SELECT 
  status,
  country,
  COUNT(*) as count
FROM users 
GROUP BY status, country;

3.3 HAVING

그룹 조건 필터링:

-- 사용자 수가 10명 이상인 상태만 조회
SELECT status, COUNT(*) as count 
FROM users 
GROUP BY status 
HAVING COUNT(*) >= 10;

-- 평균 주문 금액이 10000원 이상인 고객
SELECT 
  user_id,
  AVG(total_amount) as avg_amount
FROM orders 
GROUP BY user_id 
HAVING AVG(total_amount) >= 10000;

4. JOIN 연산

4.1 INNER JOIN

교집합 조인:

-- 사용자와 주문 정보 조인
SELECT 
  u.name,
  u.email,
  o.order_id,
  o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 여러 테이블 조인
SELECT 
  u.name,
  p.product_name,
  oi.quantity,
  oi.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

4.2 LEFT JOIN

왼쪽 테이블 기준 조인:

-- 모든 사용자와 주문 정보 (주문이 없는 사용자도 포함)
SELECT 
  u.name,
  u.email,
  o.order_id,
  o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 주문이 없는 사용자만 조회
SELECT 
  u.name,
  u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

4.3 RIGHT JOIN

오른쪽 테이블 기준 조인:

-- 모든 주문과 사용자 정보 (사용자 정보가 없는 주문도 포함)
SELECT 
  u.name,
  o.order_id,
  o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

4.4 FULL OUTER JOIN

양쪽 테이블 모두 포함:

-- MySQL에서는 지원하지 않음 (UNION으로 대체)
SELECT 
  u.name,
  o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT 
  u.name,
  o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

5. 서브쿼리

5.1 스칼라 서브쿼리

단일 값 반환:

-- 각 사용자의 주문 수 조회
SELECT 
  name,
  email,
  (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count
FROM users;

-- 평균보다 높은 주문 금액
SELECT * FROM orders 
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);

5.2 인라인 뷰 (FROM 절 서브쿼리)

-- 각 카테고리의 평균 가격보다 높은 상품
SELECT 
  p.name,
  p.price,
  p.category_id,
  avg_price.avg_price
FROM products p
INNER JOIN (
  SELECT category_id, AVG(price) as avg_price
  FROM products
  GROUP BY category_id
) avg_price ON p.category_id = avg_price.category_id
WHERE p.price > avg_price.avg_price;

5.3 EXISTS와 NOT EXISTS

존재 여부 확인:

-- 주문이 있는 사용자만 조회
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.user_id = u.id
);

-- 주문이 없는 사용자 조회
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.user_id = u.id
);

6. 윈도우 함수

6.1 ROW_NUMBER

행 번호 부여:

-- 각 카테고리별 가격 순위
SELECT 
  name,
  category_id,
  price,
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) as rank
FROM products;

6.2 RANK와 DENSE_RANK

순위 부여:

-- RANK: 동일 값은 같은 순위, 다음 순위는 건너뜀
SELECT 
  name,
  score,
  RANK() OVER (ORDER BY score DESC) as rank
FROM students;

-- DENSE_RANK: 동일 값은 같은 순위, 다음 순위는 연속
SELECT 
  name,
  score,
  DENSE_RANK() OVER (ORDER BY score DESC) as rank
FROM students;

6.3 집계 윈도우 함수

-- 누적 합계
SELECT 
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as cumulative_sum
FROM daily_sales;

-- 이동 평균
SELECT 
  date,
  amount,
  AVG(amount) OVER (
    ORDER BY date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7days
FROM daily_sales;

-- 파티션별 집계
SELECT 
  category_id,
  name,
  price,
  AVG(price) OVER (PARTITION BY category_id) as avg_category_price
FROM products;

7. 인덱스와 성능 최적화

7.1 인덱스 생성

-- 단일 컬럼 인덱스
CREATE INDEX idx_email ON users(email);

-- 복합 인덱스
CREATE INDEX idx_status_created ON users(status, created_at);

-- 고유 인덱스
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- 인덱스 삭제
DROP INDEX idx_email ON users;

7.2 쿼리 최적화

-- 나쁜 예: 인덱스 미사용
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 좋은 예: 인덱스 활용
SELECT * FROM users 
WHERE created_at >= '2024-01-01' 
AND created_at < '2025-01-01';

-- 나쁜 예: 와일드카드 앞에 %
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- 좋은 예: 인덱스 활용 가능
SELECT * FROM users WHERE email LIKE 'user%@gmail.com';

8. 트랜잭션

8.1 트랜잭션 기본

-- 트랜잭션 시작
START TRANSACTION;

-- 작업 수행
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- 커밋 또는 롤백
COMMIT;
-- 또는
ROLLBACK;

8.2 자동 커밋 제어

-- 자동 커밋 비활성화
SET autocommit = 0;

-- 작업 수행
INSERT INTO orders (user_id, total_amount) VALUES (1, 50000);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 10, 2);

-- 커밋
COMMIT;

-- 자동 커밋 재활성화
SET autocommit = 1;

9. 뷰 (Views)

9.1 뷰 생성

-- 단순 뷰
CREATE VIEW active_users AS
SELECT id, name, email, created_at
FROM users
WHERE status = 'active';

-- 복합 뷰
CREATE VIEW user_order_summary AS
SELECT 
  u.id,
  u.name,
  u.email,
  COUNT(o.id) as order_count,
  SUM(o.total_amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;

-- 뷰 사용
SELECT * FROM active_users;
SELECT * FROM user_order_summary WHERE order_count > 5;

9.2 뷰 수정 및 삭제

-- 뷰 수정
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, created_at, last_login
FROM users
WHERE status = 'active';

-- 뷰 삭제
DROP VIEW active_users;

10. 저장 프로시저와 함수

10.1 저장 프로시저

-- 프로시저 생성
DELIMITER //
CREATE PROCEDURE GetUserOrders(IN user_id INT)
BEGIN
  SELECT 
    o.id,
    o.order_date,
    o.total_amount,
    o.status
  FROM orders o
  WHERE o.user_id = user_id
  ORDER BY o.order_date DESC;
END //
DELIMITER ;

-- 프로시저 호출
CALL GetUserOrders(1);

-- 프로시저 삭제
DROP PROCEDURE GetUserOrders;

10.2 저장 함수

-- 함수 생성
DELIMITER //
CREATE FUNCTION GetOrderCount(user_id INT)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
  DECLARE order_count INT;
  SELECT COUNT(*) INTO order_count
  FROM orders
  WHERE user_id = user_id;
  RETURN order_count;
END //
DELIMITER ;

-- 함수 사용
SELECT 
  name,
  GetOrderCount(id) as order_count
FROM users;

11. 트리거

11.1 트리거 생성

-- INSERT 트리거
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
  SET NEW.updated_at = NOW();
END //
DELIMITER ;

-- UPDATE 트리거
DELIMITER //
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END //
DELIMITER ;

-- DELETE 트리거 (로그 기록)
DELIMITER //
CREATE TRIGGER after_user_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_deletion_log (user_id, deleted_at)
  VALUES (OLD.id, NOW());
END //
DELIMITER ;

12. 실전 예제

12.1 복잡한 리포트 쿼리

-- 월별 매출 리포트
SELECT 
  DATE_FORMAT(order_date, '%Y-%m') as month,
  COUNT(DISTINCT user_id) as unique_customers,
  COUNT(*) as total_orders,
  SUM(total_amount) as total_revenue,
  AVG(total_amount) as avg_order_value
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;

12.2 사용자 활동 분석

-- 사용자별 활동 요약
SELECT 
  u.id,
  u.name,
  u.email,
  COUNT(DISTINCT o.id) as order_count,
  SUM(o.total_amount) as total_spent,
  MAX(o.order_date) as last_order_date,
  DATEDIFF(NOW(), MAX(o.order_date)) as days_since_last_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email
HAVING order_count > 0
ORDER BY total_spent DESC;

12.3 상위 N개 조회

-- 카테고리별 상위 3개 상품
SELECT 
  category_id,
  name,
  price,
  rank
FROM (
  SELECT 
    category_id,
    name,
    price,
    ROW_NUMBER() OVER (
      PARTITION BY category_id 
      ORDER BY price DESC
    ) as rank
  FROM products
) ranked_products
WHERE rank <= 3;

13. 보안과 SQL 인젝션 방지

13.1 준비된 문 (Prepared Statements)

// Node.js 예제
const mysql = require('mysql2/promise');

// 나쁜 예: SQL 인젝션 취약
const query = `SELECT * FROM users WHERE email = '${email}'`;

// 좋은 예: Prepared Statement
const [rows] = await connection.execute(
  'SELECT * FROM users WHERE email = ?',
  [email]
);

13.2 입력 검증

-- 입력값 검증 예제
SELECT * FROM users 
WHERE id = ? 
AND status = 'active'
LIMIT 1;

14. 결론

SQL은 데이터베이스와 상호작용하는 핵심 언어입니다. 효율적인 SQL 작성 능력은 웹 개발에서 필수적이며, 적절한 인덱스 사용, 쿼리 최적화, 트랜잭션 관리 등을 통해 성능과 안정성을 확보할 수 있습니다.

주요 포인트:

기본 문법 숙지: SELECT, INSERT, UPDATE, DELETE
JOIN 활용: 테이블 간 관계 이해
집계 함수: GROUP BY, HAVING 활용
서브쿼리와 윈도우 함수: 복잡한 데이터 처리
성능 최적화: 인덱스 활용, 쿼리 튜닝
보안: SQL 인젝션 방지

지속적인 학습과 실습을 통해 SQL 실력을 향상시킬 수 있습니다.