SQL (Structured Query Language)

SQL(Structured Query Language)은 관계형 데이터베이스 관리 시스템(RDBMS)에서 데이터를 관리하고 조작하기 위한 표준 프로그래밍 언어이다. 1970년대에 IBM에서 개발된 이후, SQL은 데이터 관리의 핵심 도구로 자리 잡았으며, 오늘날 대부분의 데이터베이스 시스템에서 사용되고 있다.

SQL은 관계형 데이터베이스 관리의 기초이자 데이터 분석, 웹 개발, 기업 시스템 등 다양한 분야에서 핵심적인 역할을 하는 언어이다. 새로운 기술이 등장하고 데이터 처리 패러다임이 진화함에 따라 SQL도 함께 발전해 왔으며, 앞으로도 계속해서 데이터 관리의 중요한 도구로 남을 것이다.

SQL의 기본 원칙과 개념을 이해하는 것은 데이터를 다루는 모든 기술 전문가에게 필수적인 역량이다. 단순한 쿼리부터 복잡한 데이터 처리 파이프라인까지, SQL은 효율적인 데이터 관리와 분석의 기반이 된다.

SQL의 역사와 발전

탄생과 초기 발전

SQL의 기원은 1970년 E.F. Codd가 발표한 관계형 데이터 모델에서 시작된다.
이 혁신적인 모델을 바탕으로 IBM의 연구원들은 1974년 SEQUEL(Structured English Query Language)이라는 언어를 개발했다. 법적 문제로 이름이 SQL로 변경되었고, 1979년 Oracle이 최초의 상용 SQL 구현을 출시했다.

SQL 표준화

SQL은 여러 차례 표준화 과정을 거쳤다:

SQL 방언

표준이 존재함에도 불구하고, 다양한 데이터베이스 시스템은 고유한 SQL 방언을 발전시켰다:

SQL의 기본 개념과 구조

관계형 데이터베이스 모델

SQL은 관계형 데이터 모델을 기반으로 한다.

이 모델의 핵심 요소는 다음과 같다:

SQL 언어 구성 요소

SQL은 크게 다음 하위 언어로 구성된다:

데이터 정의 언어(DDL)

데이터베이스 구조를 정의하고 수정하는 명령어:

데이터 조작 언어(DML)

데이터를 조작하는 명령어:

데이터 제어 언어(DCL)

데이터베이스 접근 권한을 관리하는 명령어:

트랜잭션 제어 언어(TCL)

트랜잭션을 관리하는 명령어:

데이터 타입

SQL은 다양한 데이터 타입을 지원한다:

SQL 기본 문법과 연산

SELECT 문 - 데이터 조회의 기본

SQL의 가장 기본적이고 중요한 명령어는 SELECT:

1
2
3
4
5

SELECT column1, column2, …
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC]
LIMIT n;

주요 구성 요소:

데이터 필터링과 조건식

WHERE 절에서 사용되는 다양한 연산자:

예시:

1
2

SELECT * FROM customers
WHERE country = 'USA' AND (age > 25 OR referred_by IS NOT NULL);

함수와 표현식

SQL은 다양한 내장 함수를 제공한다:

집계 함수
문자열 함수
날짜/시간 함수

데이터 그룹화와 집계

GROUP BY 절을 사용하여 데이터를 그룹화하고 집계 함수를 적용할 수 있다:

1
2
3
4
5

SELECT department, COUNT(*) as employee_count, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000
ORDER BY avg_salary DESC;

테이블 간의 관계와 조인

관계 유형

관계형 데이터베이스에서 테이블 간의 관계 유형:

SQL 조인 타입

테이블을 연결하는 다양한 JOIN 유형:

INNER JOIN

두 테이블에서 일치하는 행만 반환한다:

1
2
3

SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;
LEFT JOIN (LEFT OUTER JOIN)

왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 반환한다:

1
2
3

SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
RIGHT JOIN (RIGHT OUTER JOIN)

오른쪽 테이블의 모든 행과 왼쪽 테이블에서 일치하는 행을 반환한다:

1
2
3

SELECT c.customer_name, o.order_id
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;
FULL JOIN (FULL OUTER JOIN)

양쪽 테이블의 모든 행을 반환한다:

1
2
3

SELECT c.customer_name, o.order_id
FROM customers c
FULL JOIN orders o ON c.customer_id = o.customer_id;
CROSS JOIN

두 테이블의 모든 가능한 조합을 반환한다 (카테시안 곱):

1
2
3

SELECT c.customer_name, p.product_name
FROM customers c
CROSS JOIN products p;

자기 조인(Self Join)

같은 테이블을 자기 자신과 조인하는 기법:

1
2
3

SELECT e1.employee_name as employee, e2.employee_name as manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

서브쿼리와 조인

서브쿼리(중첩 쿼리)는 다른 쿼리 내에 포함된 쿼리이다:

1
2
3
4
5
6
7

SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE total_amount > 1000
);

조인으로도 동일한 결과를 얻을 수 있는 경우가 많다:

1
2
3
4

SELECT DISTINCT c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > 1000;

성능 측면에서는 상황에 따라 다르지만, 일반적으로 조인이 서브쿼리보다 효율적인 경우가 많다.

고급 SQL 기능

윈도우 함수

윈도우 함수는 결과 집합의 행 그룹에 대한 계산을 수행한다:

1
2
3
4
5
6
7

SELECT 
    employee_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM employees;

주요 윈도우 함수:

공통 테이블 표현식(CTE)

CTE는 복잡한 쿼리를 더 읽기 쉽고 관리하기 쉽게 만든다:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

WITH high_value_orders AS (
    SELECT customer_id, COUNT(*) as order_count
    FROM orders
    WHERE total_amount > 1000
    GROUP BY customer_id
    HAVING COUNT(*) > 5
)
SELECT c.customer_name, h.order_count
FROM customers c
JOIN high_value_orders h ON c.customer_id = h.customer_id
ORDER BY h.order_count DESC;

재귀 쿼리

재귀 CTE를 사용하여 계층적 데이터를 쿼리할 수 있다:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

WITH RECURSIVE org_hierarchy AS (
    -- 기본 케이스: 최상위 관리자
    SELECT employee_id, employee_name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 재귀 케이스: 하위 직원
    SELECT e.employee_id, e.employee_name, e.manager_id, h.level + 1
    FROM employees e
    JOIN org_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM org_hierarchy
ORDER BY level, employee_name;

PIVOT 및 UNPIVOT

일부 데이터베이스 시스템은 행-열 변환을 위한 PIVOT 및 UNPIVOT 기능을 제공한다:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- SQL Server PIVOT 예시
SELECT 
    product_category,
    [2021], [2022], [2023]
FROM (
    SELECT 
        product_category,
        YEAR(sale_date) as sale_year,
        sale_amount
    FROM sales
) as source_data
PIVOT (
    SUM(sale_amount)
    FOR sale_year IN ([2021], [2022], [2023])
) as pivot_table;

JSON 및 XML 지원

현대 데이터베이스는 반구조화된 데이터 형식을 지원한다:

1
2
3
4
5
6
7

-- PostgreSQL JSON 예시
SELECT 
    customer_id,
    data->'address'->>'city' as city,
    data->'preferences'->>'language' as language
FROM customer_profiles
WHERE data->'preferences'->>'theme' = 'dark';

SQL의 성능 최적화

인덱스 설계

인덱스는 쿼리 성능을 크게 향상시킬 수 있다:

1
2
3
4
5
6
7
8

-- 단일 열 인덱스
CREATE INDEX idx_customers_email ON customers(email);

-- 복합 인덱스
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- 부분 인덱스 (PostgreSQL)
CREATE INDEX idx_active_users ON users(user_id) WHERE active = TRUE;

인덱스 타입:

쿼리 최적화 기법

효율적인 쿼리 작성을 위한 팁:

  1. 필요한 열만 선택하기: SELECT * 대신 필요한 열만 지정
  2. WHERE 절 최적화: 인덱스된 열을 사용하고, 불필요한 함수 호출 피하기
  3. 조인 순서 고려: 작은 테이블을 먼저 조인하고, 필요한 조인만 사용
  4. 서브쿼리 대신 조인 사용: 가능한 경우 서브쿼리보다 조인 선호
  5. LIMIT 사용: 필요한 행만 검색
  6. EXPLAIN 활용: 쿼리 실행 계획 분석
1
2
3
4
5
6
7

EXPLAIN SELECT c.customer_name, SUM(o.total_amount) as total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.join_date > '2020-01-01'
GROUP BY c.customer_name
HAVING SUM(o.total_amount) > 5000
ORDER BY total_spent DESC;

정규화와 비정규화

파티셔닝과 샤딩

대규모 데이터베이스의 성능 향상 전략:

트랜잭션과 동시성 제어

트랜잭션의 ACID 속성

트랜잭션은 다음 ACID 속성을 보장한다:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 트랜잭션 예시
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- 검증 로직
IF (SELECT balance FROM accounts WHERE account_id = 1) < 0 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

트랜잭션 격리 수준

SQL 표준은 네 가지 격리 수준을 정의한다:

  1. READ UNCOMMITTED: 다른 트랜잭션의 커밋되지 않은 변경사항도 읽을 수 있음
  2. READ COMMITTED: 커밋된 변경사항만 읽을 수 있음
  3. REPEATABLE READ: 트랜잭션 내에서 같은 쿼리는 항상 같은 결과를 반환
  4. SERIALIZABLE: 가장 엄격한 격리 수준으로, 동시성 문제 없음

각 수준은 다양한 문제를 방지한다:

1
2
3
4
5

-- PostgreSQL에서 트랜잭션 격리 수준 설정
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 쿼리 수행
COMMIT;

락킹과 동시성 제어

데이터베이스 시스템은 여러 동시성 제어 메커니즘을 사용한다:

1
2
3
4

-- 명시적 락 예시 (PostgreSQL)
SELECT * FROM accounts
WHERE account_id = 1
FOR UPDATE;  -- 배타적 락 획득

SQL 보안 및 권한 관리

  1. 사용자 및 역할 관리
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 사용자 생성
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'password';

-- 역할 생성 (MySQL 8.0+)
CREATE ROLE 'read_role', 'write_role';

-- 역할에 권한 부여
GRANT SELECT ON database.* TO 'read_role';
GRANT INSERT, UPDATE, DELETE ON database.* TO 'write_role';

-- 사용자에게 역할 할당
GRANT 'read_role' TO 'appuser'@'localhost';
  1. 권한 관리
1
2
3
4
5
6
7
8

-- 특정 테이블에 대한 권한 부여
GRANT SELECT, INSERT ON database.customers TO 'appuser'@'localhost';

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

-- 권한 회수
REVOKE DELETE ON database.critical_table FROM 'appuser'@'localhost';
  1. SQL 인젝션 방지
    SQL 인젝션은 심각한 보안 위협이다:
1
2
3
4
5
6
7

-- 취약한 코드 (예시적 목적)
"SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";
-- 공격자가 username에 "admin' --"를 입력하면 패스워드 검증 우회

-- 방어 방법: 준비된 문장(Prepared Statements) 사용
"SELECT * FROM users WHERE username = ? AND password = ?";
-- 파라미터는 안전하게 바인딩됨

SQL 인젝션 방지를 위한 추가 방법:

현대 SQL 환경과 생태계

  1. NoSQL과의 통합
    많은 NoSQL 데이터베이스가 SQL 인터페이스를 제공하기 시작했다:

    • Amazon DynamoDB: PartiQL이라는 SQL 호환 쿼리 언어 제공
    • MongoDB: MongoDB Query API 외에도 SQL 호환 커넥터 제공
    • Cassandra: CQL(Cassandra Query Language)는 SQL과 유사한 구문 사용
    • Couchbase: N1QL이라는 SQL 호환 쿼리 언어 제공

  2. 클라우드 기반 SQL 서비스
    클라우드 제공업체들은 관리형 SQL 서비스를 제공한다:

    • Amazon RDS/Aurora: MySQL, PostgreSQL, SQL Server 등을 관리형 서비스로 제공
    • Google Cloud SQL: MySQL, PostgreSQL, SQL Server의 완전 관리형 버전
    • Azure SQL Database: Microsoft의 관리형 SQL Server 서비스
    • Snowflake: 클라우드 기반 데이터 웨어하우스 솔루션

  3. 최신 SQL 도구 및 유틸리티

  1. SQL과 빅데이터
    SQL은 빅데이터 생태계에서도 중요한 역할을 한다:
    • Apache Hive: Hadoop 위에서 SQL과 유사한 HiveQL 사용
    • Apache Spark SQL: 분산 데이터 처리를 위한 SQL 인터페이스
    • Presto/Trino: 다양한 데이터 소스에 대한 분산 SQL 쿼리 엔진
    • Google BigQuery: 서버리스 SQL 데이터 웨어하우스

SQL의 미래 동향

  1. SQL과 인공지능
  1. 시계열 및 스트림 처리
  1. 공간 데이터 처리
  1. 그래프 쿼리 통합
  1. 폴리글랏 영속성

SQL 응용 사례

  1. 데이터 분석 및 비즈니스 인텔리전스
    SQL은 데이터 분석의 핵심 도구로, 복잡한 비즈니스 질문에 답하는 데 사용된다.

  2. 웹 개발
    SQL은 대부분의 웹 애플리케이션의 백엔드에서 필수적이다.

  3. 데이터 마이그레이션 및 ETL
    SQL은 데이터 변환 및 마이그레이션 파이프라인에서 중요한 역할을 한다.

  4. IoT 및 센서 데이터 관리
    SQL은 IoT 장치 및 센서 데이터를 저장하고 분석하는 데도 사용된다.

SQL과 관련 기술 비교

SQL vs. NoSQL

특성SQL (관계형)NoSQL
데이터 모델테이블, 행과 열의 구조화된 형식다양한 모델(문서, 키-값, 그래프, 컬럼 계열)
스키마사전 정의된 스키마 필요유연한 스키마 또는 스키마리스
확장성주로 수직적 확장(더 강력한 하드웨어)주로 수평적 확장(더 많은 서버)
트랜잭션ACID 트랜잭션 지원일부는 BASE 모델 채택(기본적인 가용성, 소프트 상태, 최종 일관성)
복잡한 쿼리복잡한 조인과 집계 지원일반적으로 조인에 제한적 (일부 NoSQL은 SQL 유사 언어 제공)
사용 사례구조화된 데이터, 복잡한 쿼리, 트랜잭션이 중요한 경우대규모 분산 데이터, 유연한 스키마, 높은 쓰기 처리량

SQL vs. 프로그래밍 언어의 데이터 조작

SQL은 선언적 언어로, 무엇을 원하는지 지정하지만 어떻게 달성할지는 지정하지 않는다.
반면, 절차적 언어는 단계별 실행 과정을 정의한다:

1
2
3
4
5

-- SQL 방식 (선언적)
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

// JavaScript 방식 (절차적)
const employees = getEmployees();
const departments = {};

// 각 부서별로 직원 그룹화
employees.forEach(emp => {
  if (!departments[emp.department]) {
    departments[emp.department] = [];
  }
  departments[emp.department].push(emp);
});

// 각 부서별 평균 급여 계산
const results = [];
for (const [dept, emps] of Object.entries(departments)) {
  const totalSalary = emps.reduce((sum, emp) => sum + emp.salary, 0);
  const avgSalary = totalSalary / emps.length;
  
  // 평균 급여가 50,000 이상인 부서만 필터링
  if (avgSalary > 50000) {
    results.push({ department: dept, avg_salary: avgSalary });
  }
}

SQL과 데이터 과학 도구

SQL은 종종 Python, R, Jupyter Notebooks 등의 데이터 과학 도구와 함께 사용된다:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

# Python과 SQL 통합 (pandas와 SQLAlchemy 사용)
import pandas as pd
from sqlalchemy import create_engine

# 데이터베이스 연결
engine = create_engine('postgresql://username:password@localhost/database')

# SQL 쿼리 실행 및 pandas DataFrame으로 결과 가져오기
df = pd.read_sql("""
    SELECT 
        product_category,
        SUM(sales_amount) as total_sales,
        COUNT(DISTINCT customer_id) as unique_customers
    FROM sales
    WHERE sale_date >= '2023-01-01'
    GROUP BY product_category
    ORDER BY total_sales DESC
""", engine)

# pandas로 추가 분석
df['avg_sale_per_customer'] = df['total_sales'] / df['unique_customers']

용어 정리

용어설명
폴리글랏 영속성 (Polyglot Persistence)한 시스템 내에서 여러 종류의 데이터베이스를 함께 사용하는 아키텍처 패턴을 말한다.

참고 및 출처

Roadmap

Roadmap - SQL