[SQL] 1. SQL 이란? , 기본 개념과 기본 문법(쿼리 예시)

SQL 

"SQL(Structured Query Language), 구조적 쿼리 언어"

SQL은 관계형 데이터베이스 (RDBMS)에서 데이터를 정의하고 관리하기 위해 설계된 프로그래밍 언어이다.

데이터베이스에서 데이터를 추가(INSERT), 조회(SELECT), 수정(UPDATE), 삭제(DELETE) 등 다양한 작업을 수행할 수 있도록 해주는 도구이며, 데이터를 표 형태로 저장하고, 표 간의 관계를 정의하여 정보를 효율적으로 관리할 수 있게 해준다.

SQL은 서로 다른 프로그래밍 언어와 잘 통합되고, 일반적인 영어 키워드를 사용하기 때문에 배우기도 쉬운 편이다.

 

SQL 기본 구성 개념

관계형 데이터 베이스(RDBMS) 기본 구조

• 테이블 : 데이터를 행(row)과 열(column)로 구성해 저장
• 행(Row, Record) : 데이터의 한 인스턴스 (실제 데이터)
• 열(Column, FIeld) : 속성(데이터 종류)
• 스키마(Schema): 데이터베이스 구조 정의(테이블, 관계, 제약 조건)
• 관계(Relationship): 테이블 간 연결 (1:1, 1:N, N:M)

키(Key)

• Primary Key (PK) : 행을 유일하게 식별하는 컬럼 (중복, NULL 불가), InnoDB에서는 클러스터형 인덱스로 저장됨.
• Foreign Key (FK): 다른 테이블의 PK를 참조하는 컬럼
• Unique Key : 중복은 불가능하지만 NULL 허용 가능
• Composite Key : 여러 컬럼을 묶어 PK로 설정 가능 

정규화 (1NF~3NF, BCNF)

데이터베이스 정규화의 주요 목적은 데이터 중복을 최소화하고 데이터 무결성을 유지하는 것이다.

이를 통해 데이터베이스의 효율성을 높이고, 삽입, 삭제, 갱신 이상 현상을 방지하며 데이터 관리를 용이하게 한다.

정규화는 일반적으로 3단계를 거치며, 각 단계별로 데이터 중복 및 이상 현상을 제거하는 것을 목표로 한다.

하지만 과도한 정규화는 오히려 성능 저하를 초래할 수 있어서, 적절한 수준의 정규화를 유지하는 것이 중요하다.

 

데이터 중복 최소화

• 중복된 데이터를 제거하여 저장 공간을 절약하고, 데이터 일관성을 유지한다.

데이터 무결성 유지

• 데이터의 정확성과 일관성을 보장하여, 데이터 삽입, 삭제, 갱신 시 발생할 수 있는 오류를 방지한다.

유연한 데이터 구조

• 데이터베이스 구조를 유연하게 만들어, 새로운 요구사항이나 변경 사항에 대한 유연성을 확보한다.

성능 향상

• 불필요한 데이터 중복을 제거하여 데이터 검색 및 쿼리 성능을 향상시킨다.

유지보수 용이성

• 데이터베이스 구조가 단순해져 유지보수 및 관리가 용이해진다.

 

정규화

비정규화 상태인 예시 데이터

student_id	student_name	course_ids	course_names	instructor
S001	홍길동	C101, C102	DB, Network	김교수
S002	김철수	C101	DB	김교수
S003	이영희	C103, C104, C101	AI, ML, DB	박교수, 박교수, 김교수

문제점

1. 한 셀에 여러 값 저장 → 원자성 위반
2. 교수명도 여러 개 들어가 있음
3. 검색·집계가 어려움 (예: DB 과목 수강생 찾기 힘듦)

정규화 설명에 앞선 용어 정리

• Candidate key: 행을 유일하게 식별할 수 있는 최소 컬럼 집합 (여러 개일 수도 있음)
• Primary key: 후보키 중 하나를 선택한 키
• Super key: 유일 식별 가능하지만 최소는 아님(불필요한 컬럼 포함 가능)
• Prime attribute: 하나라도 후보키에 속하는 컬럼
• Non-prime attribute: 어느 후보키에도 속하지 않는 컬럼

 

1) 1NF - 제 1정규화

모든 컬럼 값은 원자값(Atomic)을 가지며, 반복 속성은 제거한다. 

student_id	student_name	course_id	course_name	instructor
S001	홍길동	C101	DB	김교수
S001	홍길동	C102	Network	김교수
S002	김철수	C101	DB	김교수
S003	이영희	C103	AI	박교수
S003	이영희	C104	ML	박교수
S003	이영희	C101	DB	김교수

• 한 행에는 한 과목만 넣고, course_ids와 course_names를 행 단위로 분리
• 하지만 아직 학생 이름이 반복 저장하고 있어서 이름 변경 시 모든 행 수정 필요하고, 과목명/ 교수명도 중복 저장 중인 상태이다.

2) 2NF - 제 2정규화

부분 종속 제거 : 복합 키(예: PK = student_id + course_id) 중 일부에만 종속된 컬럼을 분리한다.

현재 student_name은 student_id에만 종속, course_name과 instructor는 course_id에만 종속 → 부분 종속 발생.

student_id	student_name
S001	홍길동
S002	김철수
S003	이영희

(⬆️Students 테이블, ⬇️ Courses 테이블)

course_id	course_name	instructor	dept_name
C101	DB	김교수	컴퓨터공학
C102	Network	김교수	컴퓨터공학
C103	AI	박교수	전자공학
C104	ML	박교수	전자공학

 

Enrollments 테이블

student_id	course_id
S001	C101
S001	C102
S002	C101
S003	C103
S003	C104
S003	C101

• Students 테이블과 Courses 테이블로 분리
• Enrollments는 학생과 과목 관계만 유지

3) 3NF - 제 3정규화

이행 종속 제거 : PK -> Non-prime attribute -> Non-prime attribute 형태의 종속 제거

만약 Courses 테이블에 dept_name이 있고, instructor → dept_name 종속이 생긴다면 → 이행 종속.

course_id → instructor → dept_name이므로 3NF 위반.

instructor_id	instructor	dept_name
I001	김교수	컴퓨터공학
I002	박교수	전자공학

(⬆️Instructors 테이블, ⬇️ Courses 테이블)

course_id	course_name	instructor_id
C101	DB	I001
C102	Network	I001
C103	AI	I002
C104	ML	I002

• 교수 정보 분리 → Instructors 테이블 생성
• Courses는 강사 ID만 참조

정리

• 1NF: 한 셀에 하나의 값(원자성), 반복 속성 제거
• 2NF: 복합키 일부에만 종속된 컬럼 분리(부분 종속 제거)
• 3NF: 키가 아닌 컬럼이 다른 키가 아닌 컬럼에 종속(이행 종속) → 별도 테이블 분리

---

SQL의 동작

SELECT * FROM Table 과 같은 쿼리 명령을 실행하면 내부적으로는 어떤 단계를 거쳐 동작하게 되는걸까

기본적으로는 SQL 요청 -> 파서 -> 옵티마이저 -> 실행기 -> 결과 반환의 과정을 거친다.

1. SQL 요청 -> 파서(Parser)

• SQL 문법 분석 (Syntax Analysis) : 문장이 SQL 문법에 맞는지 검사
• SQL 구조 분석 (Semantic Analysis) : 테이블/컬럼 존재 여부, 권한 확인

2. 옵티마이저(Optimizer)

• 쿼리를 가장 효율적으로 실행할 방법을 결정
• 실행 계획 (Execution Plan)을 생성
• 인덱스 사용 여부, 조인 방식(Nested Loop, Hash Join, Merge Join), 데이터 접근 방식 (Full Table Scan VS Index Scan) 등의 결정

(*대부분의 RDBMS에서 내부흐름은 비슷하지만 구현 방식은 다르다)

 

3. 실행기(Executor)

• 옵티마이저가 만든 실행 계획을 그대로 수행
• 데이터 읽기/쓰기 작업 실행
• 결과 집계 및 정렬 처리

4. 결과 반환

• 최종 결과를 클라이언트(애플리케이션)에 전달

옵티마이저(Optimizer)

옵티마이저는 SQL 쿼리를 가능한 가장 적은 비용(Cost)로 실행하는 방법을 결정하는 핵심 엔진이다.

DB 성능 최적화의 중심이 바로 옵티마이저인 것 이다.

MySQL 기준으로는 Cost-Based Optimizer(비용 기반 옵티마이저)가 사용되고 있으며, 특징은 아래와 같다.

• 각 실행 방법의 비용(CPU 사용량, I/O 횟수, 메모리 사용량)을 계산
• 통계 정보를 활용해 최적의 계획 선택
• 주요 기능
  • 인덱스 사용 여부 결정
  • Join 순서 및 방식 결정
  • 필터 조건 적용 시점 결정 (Where을 Join 전 후에할지 등)
  • LIMIT / OFFSET 처리 방식 최적화
  • 병렬 쿼리 여부 결정

EXPLAIN 

EXPLAIN은 쿼리를 실제 실행하지 않고, 옵티마이저가 생성한 실행 계획을 보여준다.

각 단계에서 어떤 테이블을 어떤 순서로 읽고, 인덱스를 사용하는지, 얼마나 많은 행을 스캔할지를 예측한다.

EXPLAIN SELECT name FROM users WHERE age > 30;

 

하지만 실행 계획만 보여줄 뿐, 실제 실행 시간이나 I/O 비용은 보여주지 않는다.

rows 값 역시 예상치라서 통계 정보가 오래되면 부정확할 수 있다. 그래서 실제 실행과 비교가 필요하다.

 

EXPLAIN 을 통해 확인할 수 있는 대표적인 값

컬럼명	설명
id	SELECT 구문의 실행 순서(큰 값이 먼저 실행됨)
select_type	쿼리 유형 (SIMPLE, PRIMARY, SUBQUERY, DERIVED 등)
table	접근하는 테이블 이름
type	조인/액세스 방식 (ALL, index, range, ref, eq_ref, const, system 등) → ALL이면 풀 테이블 스캔
possible_keys	사용 가능성이 있는 인덱스 목록
key	실제로 사용된 인덱스
rows	옵티마이저가 예상하는 스캔 행 수
filtered	필터 조건 통과 비율(%)
Extra	추가 작업(Using index, Using where, Using temporary, Using filesort 등)

 

EXPLAIN ANALYZE (MySQL 8.0+ / PostgreSQL) 

쿼리를 실제로 실행하고, 실행 계획과 함께 실제 소요 시간을 반환한다.

예상 vs 실제 차이를 확인할 수 있어서 튜닝 시 매우 유용하다.

EXPLAIN ANALYZE SELECT name FROM users WHERE age > 30;

-> Index range scan on users using idx_age (cost=15.05 rows=500)
actual time=0.025..0.070 rows=480 loops=1

 

• cost: 옵티마이저 예상 비용
• rows: 예상 행 수
• actual time: 실제 실행 시간(시작~종료)
• actual rows: 실제 반환된 행 수
• loops: 실행 반복 횟수

정리
EXPLAIN으로 예상 실행 계획을 확인하고, EXPLAIN ANALYZE를 통해 예상과 실제 실행 결과를 비교, 일치를 확인하자
그리고 옵티마이저의 선택 이유 까지 확인해보려면 Optimizer Trace를 병행해서 사용해보자.

---

SQL 명령

SQL 명령은 다음과 같이 분류할 수 있다.

분류	설명	예시
DDL (Data Definition Language)	데이터베이스 구조 정의	CREATE, ALTER, DROP
DQL (Data Query Language)	데이터베이스 검색	SELECT
DML (Data Manipulation Language)	데이터 조작	INSERT, UPDATE, DELETE
DCL (Data Control Language)	권한 제어	GRANT, REVOKE
TCL (Transaction Control Language)	트랜잭션 제어	COMMIT, ROLLBACK, SAVEPOINT

쿼리 예시

DDL (데이터 정의 언어): 데이터베이스 구조를 설계하는 SQL 명령을 나타낸다.

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

-- 테이블 생성
CREATE TABLE users (
  id            BIGINT PRIMARY KEY AUTO_INCREMENT,
  email         VARCHAR(190) NOT NULL,
  name          VARCHAR(100) NOT NULL,
  phone         VARCHAR(20),
  status        ENUM('ACTIVE', 'BLOCKED') NOT NULL DEFAULT 'ACTIVE',
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT uk_users_email UNIQUE (email),
  CONSTRAINT chk_phone_format CHECK (phone IS NULL OR phone REGEXP '^[0-9\\-]+$')
);

CREATE TABLE products (
  id          BIGINT PRIMARY KEY AUTO_INCREMENT,
  sku         VARCHAR(64) NOT NULL UNIQUE,
  title       VARCHAR(200) NOT NULL,
  price       DECIMAL(12,2) NOT NULL,
  stock       INT NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  id            BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id       BIGINT NOT NULL,
  order_date    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status        ENUM('PENDING','PAID','CANCELLED') NOT NULL DEFAULT 'PENDING',
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE RESTRICT   -- 유저 삭제 시 주문은 남김
    ON UPDATE CASCADE
);

-- 인덱스 추가
-- 단일 인덱스
CREATE INDEX idx_users_created_at ON users (created_at);

-- 복합 인덱스: (user_id, order_date) → 사용자별 최신 주문 조회용
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date DESC);

-- 프리픽스 인덱스(긴 문자열 대비; MySQL 전용)
CREATE INDEX idx_users_email_prefix ON users (email(16));

-- 함수(표현식) 인덱스 (MySQL 8.0.13+)
CREATE INDEX idx_users_lower_email ON users ((LOWER(email)));

-- 커버링 인덱스: MySQL은 INCLUDE 문법 없음.
-- "커버링"은 쿼리의 SELECT 컬럼이 전부 인덱스 안에 있을 때 성립.
-- 예: (user_id, order_date)만 조회한다면 위 idx_orders_user_date가 커버링이 될 수 있음.


-- 테이블 구조 변경
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 테이블 삭제
DROP TABLE users;

DQL (데이터 쿼리 언어): 저장된 데이터를 검색하기 위한 명령으로 구성된다.

DML (데이터 조작 언어): 새 정보를 쓰거나 기존 레코드를 수정한다.

-- INSERT
INSERT INTO users (email, name, phone)
VALUES ('a@example.com','홍길동','010-1111-2222'),
       ('b@example.com','김철수',NULL);

-- INSERT SELECT (ETL/배치에 유용)
INSERT INTO order_items (order_id, product_id, qty, unit_price)
SELECT o.id, p.id, 1, p.price
FROM orders o
JOIN products p ON p.sku IN ('ABC','XYZ')
WHERE o.id = 1001;

-- UPSERT (MySQL)
INSERT INTO products (sku, title, price, stock)
VALUES ('ABC','키보드', 39000, 10)
ON DUPLICATE KEY UPDATE
  price = VALUES(price), stock = stock + VALUES(stock);
  
// UNIQUE KEY 또는 PRIMARY KEY 충돌이 발생하면, UPDATE 구문을 실행
// 충돌이 없으면 INSERT, 있으면 UPDATE로 동작하는 Upsert 방식

-- UPDATE with JOIN
UPDATE products p
JOIN order_items oi ON oi.product_id = p.id
SET p.stock = p.stock - oi.qty
WHERE oi.order_id = 1001;

-- DELETE with JOIN
DELETE oi FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'CANCELLED';

-- 1) INNER JOIN: 주문 + 사용자
SELECT o.id, u.email, o.order_date, o.status
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'PAID';

-- 2) LEFT JOIN: 주문은 모두, 사용자 없으면 NULL
SELECT o.id, u.email
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;

-- 3) SELF JOIN: 상급자-하급자 구조 예시
-- employees(id, name, manager_id)
SELECT e.name AS emp, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

-- 4) CROSS JOIN: 모든 조합
SELECT u.name, p.title
FROM users u
CROSS JOIN products p;


-- 사용자별 결제 금액 합계 (최근 30일)
SELECT o.user_id, SUM(oi.qty * oi.unit_price) AS total_amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'PAID'
  AND o.order_date >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY o.user_id
HAVING total_amount >= 100000
ORDER BY total_amount DESC;

-- COALESCE 활용
// COALESCE는 여러 후보 중 NULL이 아닌 첫 값을 고르는 표준 SQL 함수이다.
// WHERE COALESCE(col, 'X') = 'X' 처럼 컬럼에 함수를 씌우면 
// 인덱스 사용이 어려워져(비SARGable) 풀스캔 위험이 큽니다.
// NULL 데이터를 제외하지않고 다르게 표현하긴 해야할 때 쓰면 유용하다.
SELECT 
ANIMAL_TYPE, 
COALESCE(NAME, 'No name') AS NAME, 
SEX_UPON_INTAKE 
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID ASC;

 

ON DUPLICATE KEY UPDATE 주의사항

INSERT INTO products (sku, title, price, stock)
VALUES ('ABC', '키보드', 39000, 10)
ON DUPLICATE KEY UPDATE
  price = VALUES(price), stock = stock + VALUES(stock);

• UNIQUE KEY 또는 PRIMARY KEY 충돌이 발생하면, UPDATE 구문을 실행
• 충돌이 없으면 INSERT, 있으면 UPDATE로 동작하는 Upsert 방식
• 중복 키 충돌 조건
  • PRIMARY KEY나 UNIQUE KEY가 걸린 컬럼에서만 중복을 판단한다.
  • 인덱스가 없는 컬럼의 중복은 감지하지 못함
• 트리거 및 AUTO_INCREMENT
  • 중복이 나서 UPDATE가 수행되더라도 AUTO_INCREMENT 값은 증가
    → INSERT 시도 자체는 하기 때문에 시퀀스가 건너뛴 것처럼 보일 수 있다.
  • AFTER INSERT 트리거는 중복 시 실행되지 않고, AFTER UPDATE 트리거만 실행
• Update 연산 부하
  • 충돌이 빈번하면 UPDATE가 자주 발생하면서 InnoDB의 Undo/Redo 로그가 늘어나고, I/O 부하가 커진다.
  • INSERT 기대하고 사용했는데 사실상 UPDATE 연속 호출이 되는 경우가 있다.
• 멀티컬럼 UNIQUE KEY
  • 다중 컬럼 UNIQUE 제약일 경우, 중복 판단은 해당 컬럼 조합 기준입니다.
  • 컬럼 중 일부만 같아도 중복으로 안 봄.
• 대량 Upsert 시에는 ON DUPLICATE KEY UPDATE보다 INSERT INTO ... ON DUPLICATE KEY UPDATE + Batching 사용 권장.
• 동시성 업데이트 시에는 SELECT ... FOR UPDATE 또는 GET_LOCK() 등으로 락을 잡는 방법 고려.

DCL (데이터 제어 언어): 데이터베이스 관리자가 다른 사용자의 데이터베이스 액세스를 관리하거나 권한을 부여한다.

 

TCL (트랜잭션 제어 언어): 데이터베이스 관계형 엔진은 데이터베이스를 자동으로 변경한다.

-- 세션 격리수준 설정 (예: READ COMMITTED)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
  UPDATE products SET stock = stock - 1 WHERE id = 10 AND stock > 0;
  SAVEPOINT s1;
  UPDATE orders SET status='PAID' WHERE id = 1001;
  -- 문제가 있으면
  ROLLBACK TO s1;
COMMIT;