MySQL 핵심 개념과 실전 활용

1. 데이터베이스 기초

1.1 데이터베이스란?

데이터베이스는 구조화된 데이터 집합으로, 효율적인 저장, 관리, 검색을 목적으로 설계되었습니다. 단순한 파일 저장이 아닌, 체계적인 질의(query)를 통해 데이터를 조작하는 시스템입니다.

핵심 역할: 데이터 영속성 보장, 동시 접근 제어, 무결성 유지

1.2 데이터베이스 유형 분류

1.2.1 관계형 데이터베이스 (RDBMS)

테이블 기반 구조로 데이터 간 관계를 외래키로 연결합니다. SQL 표준을 따르며 ACID 특성을 보장합니다.

제품특징적합한 환경
MySQL경량 오픈소스, 높은 성능웹 애플리케이션, CMS
PostgreSQL표준 SQL 준수, 확장성 우수복잡한 분석, 공간 데이터
Oracle엔터프라이즈급 기능 풍부대규모 금융, 정부 시스템
SQL ServerWindows 생태계 통합.NET 기반 비즈니스
SQLite서버리스, 파일 기반모바일, 임베디드

1.2.2 비관계형 데이터베이스 (NoSQL)

문서형: MongoDB, Couchbase - JSON/BSON 형태의 유연한 스키마

키-값: Redis, DynamoDB - 초고속 접근이 필요한 캐싱/세션

와이드 컬럼: Cassandra, HBase - 대규모 분산 쓰기 작업

그래프: Neo4j, Neptune - 관계 중심 분석 (소셜 네트워크, 추천)

1.2.3 시계열 데이터베이스

시간 기반 메트릭 최적화: InfluxDB, Prometheus, TimescaleDB

1.2.4 검색 엔진

전문 검색 및 로그 분석: Elasticsearch, OpenSearch, Solr

1.3 MySQL 데이터 타입

-- 수치형
TINYINT       -- 1바이트 정수 (-128 ~ 127)
INT           -- 4바이트 정수
BIGINT        -- 8바이트 정수
FLOAT         -- 단정밀도 부동소수
DOUBLE        -- 배정밀도 부동소수
DECIMAL(10,2) -- 고정소수점 (금융 계산용)

-- 문자형
CHAR(n)       -- 고정 길이 (최대 255)
VARCHAR(n)    -- 가변 길이 (최대 65,535)
TEXT          -- 대용량 텍스트
BLOB          -- 바이너리 데이터

-- 날짜/시간
DATE          -- YYYY-MM-DD
DATETIME      -- YYYY-MM-DD HH:MM:SS
TIMESTAMP     -- Unix 타임스탬프 기반
YEAR          -- YYYY

-- 기타
ENUM('A','B') -- 열거형
JSON          -- JSON 문서 (5.7+)
GEOMETRY      -- 공간 데이터

2. DDL (데이터 정의어)

스키마 객체를 생성, 수정, 삭제하는 명령어: CREATE, ALTER, DROP, TRUNCATE

2.1 데이터베이스 관리

-- 데이터베이스 생성
CREATE DATABASE IF NOT EXISTS university 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

-- 사용할 데이터베이스 선택
USE university;

-- 데이터베이스 삭제
DROP DATABASE IF EXISTS university;

2.2 테이블 생성

CREATE TABLE IF NOT EXISTS students (
    student_id      INT PRIMARY KEY AUTO_INCREMENT,
    student_no      CHAR(10) NOT NULL UNIQUE,
    full_name       VARCHAR(50) NOT NULL,
    email           VARCHAR(100),
    birth_date      DATE,
    gender          ENUM('M', 'F', 'O'),
    gpa             DECIMAL(3,2) DEFAULT 0.00,
    department_id   INT,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_gpa CHECK (gpa BETWEEN 0.00 AND 4.00),
    INDEX idx_name (full_name),
    FOREIGN KEY (department_id) REFERENCES departments(dept_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB;

2.3 테이블 구조 변경

-- 컬럼 추가
ALTER TABLE students
ADD phone VARCHAR(20) AFTER email;

-- 컬럼 수정 (타입 변경)
ALTER TABLE students
MODIFY phone VARCHAR(15) NOT NULL;

-- 컬럼명 변경
ALTER TABLE students
CHANGE COLUMN full_name name VARCHAR(50);

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

-- 테이블명 변경
ALTER TABLE students RENAME TO enrolled_students;

-- 인덱스 추가/삭제
ALTER TABLE students ADD INDEX idx_email (email);
ALTER TABLE students DROP INDEX idx_email;

2.4 테이블 삭제 vs 데이터 삭제

-- 테이블 완전 삭제 (구조+데이터)
DROP TABLE IF EXISTS temp_logs;

-- 데이터만 삭제, 구조 유지 (트랜잭션 지원, 롤백 가능)
DELETE FROM students WHERE graduation_year < 2010;

-- 데이터 완전 삭제, AUTO_INCREMENT 리셋 (빠름, 롤백 불가)
TRUNCATE TABLE temp_logs;

3. DML (데이터 조작어)

레코드 수준의 CRUD 작업: INSERT, UPDATE, DELETE

3.1 데이터 삽입

-- 단일 레코드 삽입
INSERT INTO students (student_no, name, email, department_id)
VALUES ('2024001', '김철수', 'kim@uni.edu', 1);

-- 다중 레코드 삽입
INSERT INTO students (student_no, name, department_id) VALUES
    ('2024002', '이영희', 2),
    ('2024003', '박민수', 1),
    ('2024004', '정수진', 3);

-- 다른 테이블에서 복사
INSERT INTO graduated_students (student_no, name, grad_date)
SELECT student_no, name, CURDATE() 
FROM students 
WHERE credits >= 130;

-- 중복 키 시 업이트
INSERT INTO enrollment_stats (course_id, student_count)
VALUES (101, 45)
ON DUPLICATE KEY UPDATE student_count = student_count + 1;

3.2 데이터 수정

-- 단일 조건 수정
UPDATE students
SET email = 'new@uni.edu', updated_at = NOW()
WHERE student_id = 1001;

-- 다중 조건 수정
UPDATE students
SET gpa = gpa + 0.5, status = 'honors'
WHERE department_id = 2 AND gpa >= 3.5;

-- 조인을 이용한 수정
UPDATE students s
JOIN departments d ON s.department_id = d.dept_id
SET s.advisor = d.head_professor
WHERE d.college = 'Engineering';

-- 주의: WHERE 절 누락 시 전체 데이터 수정!

3.3 데이터 삭제

-- 조건 삭제
DELETE FROM students
WHERE enrollment_status = 'withdrawn' AND withdrawal_date < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- 서브쿼리를 이용한 삭제
DELETE FROM course_registrations
WHERE student_id IN (
    SELECT student_id FROM students WHERE status = 'suspended'
);

-- 안전한 삭제를 위한 LIMIT
DELETE FROM audit_logs WHERE created_at < '2023-01-01' LIMIT 10000;

4. DQL (데이터 질의어)

데이터 검색의 핵심: SELECT 문법

4.1 기본 조회

-- 전체 컬럼 조회
SELECT * FROM students;

-- 특정 컬럼 조회
SELECT student_no, name, gpa FROM students;

-- 별칭 사용
SELECT 
    s.name AS student_name,
    s.gpa AS grade_point,
    d.name AS dept_name
FROM students s
JOIN departments d ON s.department_id = d.dept_id;

-- 중복 제거
SELECT DISTINCT department_id FROM students;

-- 산술 연산
SELECT name, (midterm + final) / 2 AS average_score FROM grades;

4.2 WHERE 절 조건

-- 비교 연산자
SELECT * FROM students WHERE gpa >= 3.0;
SELECT * FROM products WHERE price BETWEEN 10000 AND 50000;
SELECT * FROM orders WHERE status IN ('paid', 'shipped', 'delivered');

-- 논리 연산자
SELECT * FROM students 
WHERE department_id = 1 AND (gpa > 3.5 OR scholarship = 'full');

-- NULL 처리
SELECT * FROM students WHERE phone IS NULL;
SELECT * FROM students WHERE COALESCE(phone, '') = '';

-- 패턴 매칭 (LIKE)
SELECT * FROM students WHERE name LIKE '김%';      -- 김으로 시작
SELECT * FROM students WHERE email LIKE '%@gmail.com';
SELECT * FROM courses WHERE code LIKE 'CS___';     -- CS + 3글자

4.3 정렬과 페이징

-- 정렬
SELECT * FROM students 
ORDER BY gpa DESC, name ASC;

-- 페이징 (LIMIT/OFFSET)
SELECT * FROM students 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 20;  -- 3페이지, 페이지당 10건

-- 또는
SELECT * FROM students 
ORDER BY created_at DESC 
LIMIT 20, 10;        -- 같은 결과

4.4 집계와 그룹화

-- 기본 집계
SELECT 
    department_id,
    COUNT(*) AS total_students,
    AVG(gpa) AS avg_gpa,
    MAX(gpa) AS highest_gpa,
    MIN(gpa) AS lowest_gpa,
    SUM(scholarship_amount) AS total_scholarship
FROM students
GROUP BY department_id;

-- HAVING으로 그룹 필터링
SELECT 
    department_id,
    COUNT(*) AS cnt
FROM students
GROUP BY department_id
HAVING COUNT(*) > 50 AND AVG(gpa) > 3.0;

-- GROUP_CONCAT (문자열 결합)
SELECT 
    department_id,
    GROUP_CONCAT(DISTINCT name ORDER BY name SEPARATOR ', ') AS student_names
FROM students
GROUP BY department_id;

4.5 조인 (JOIN)

-- INNER JOIN: 양쪽 모두 존재하는 데이터
SELECT s.name, c.title, r.grade
FROM students s
INNER JOIN registrations r ON s.student_id = r.student_id
INNER JOIN courses c ON r.course_id = c.course_id;

-- LEFT JOIN: 왼쪽 기준, 없으면 NULL
SELECT s.name, COALESCE(SUM(r.credits), 0) AS total_credits
FROM students s
LEFT JOIN registrations r ON s.student_id = r.student_id AND r.status = 'completed'
GROUP BY s.student_id;

-- RIGHT JOIN (실무에서는 LEFT JOIN으로 대체 권장)
SELECT * FROM students s
RIGHT JOIN departments d ON s.department_id = d.dept_id;

-- FULL OUTER JOIN (MySQL은 직접 지원하지 않음)
SELECT * FROM students s
LEFT JOIN departments d ON s.department_id = d.dept_id
UNION
SELECT * FROM students s
RIGHT JOIN departments d ON s.department_id = d.dept_id;

-- SELF JOIN
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = e.employee_id;

4.6 서브쿼리

-- 스칼라 서브쿼리 (단일 값)
SELECT name, gpa,
    (SELECT AVG(gpa) FROM students) AS school_avg,
    gpa - (SELECT AVG(gpa) FROM students) AS diff
FROM students;

-- 상관 서브쿼리 (행별 실행)
SELECT s.name, s.gpa
FROM students s
WHERE s.gpa > (
    SELECT AVG(gpa) 
    FROM students s2 
    WHERE s2.department_id = s.department_id
);

-- IN 서브쿼리
SELECT * FROM students
WHERE department_id IN (
    SELECT dept_id FROM departments WHERE college = 'Science'
);

-- EXISTS 서브쿼리
SELECT d.name FROM departments d
WHERE EXISTS (
    SELECT 1 FROM students s WHERE s.department_id = d.dept_id
);

-- FROM 절 서브쿼리 (파생 테이블)
SELECT dept_name, avg_gpa
FROM (
    SELECT 
        d.name AS dept_name,
        AVG(s.gpa) AS avg_gpa
    FROM departments d
    JOIN students s ON d.dept_id = s.department_id
    GROUP BY d.dept_id
) AS dept_stats
WHERE avg_gpa > 3.0;

5. 제약조건 (Constraints)

제약조건목적예시
PRIMARY KEY레코드 고유 식별id INT PRIMARY KEY
UNIQUE중복 방지 (NULL 허용)email VARCHAR(100) UNIQUE
NOT NULLNULL 값 금지name VARCHAR(50) NOT NULL
FOREIGN KEY참조 무결성FOREIGN KEY (dept) REFERENCES departments(id)
CHECK조건 검증 (8.0.16+)CHECK (age >= 18)
DEFAULT기본값 설정status VARCHAR(10) DEFAULT 'active'

6. 함수 활용

6.1 문자열 함수

SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    SUBSTRING(phone, 1, 3) AS area_code,
    REPLACE(email, '@old.com', '@new.com') AS new_email,
    UPPER(code) AS upper_code,
    TRIM(BOTH ' ' FROM address) AS clean_address,
    LENGTH(name) AS name_bytes,
    CHAR_LENGTH(name) AS name_chars;  -- 멀티바이트 문자 고려

6.2 수학 함수

SELECT 
    ROUND(price * 1.1, 0) AS price_with_tax,
    CEIL(amount / 100) * 100 AS rounded_up,
    FLOOR(amount / 1000) * 1000 AS rounded_down,
    MOD(score, 10) AS last_digit,
    POW(2, 10) AS kilobyte,
    RAND() AS random_value;  -- 0 ~ 1 사이 난수

6.3 날짜/시간 함수

SELECT 
    CURDATE() AS today,
    NOW() AS current_datetime,
    DATE_ADD(birth_date, INTERVAL 20 YEAR) AS adult_date,
    DATEDIFF(graduation_date, enrollment_date) AS study_days,
    DATE_FORMAT(created_at, '%Y년 %m월 %d일') AS formatted_date,
    YEAR(created_at) AS year_only,
    LAST_DAY(CURDATE()) AS month_end,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age;

6.4 조건 및 흐름 제어

SELECT 
    name,
    CASE 
        WHEN gpa >= 3.5 THEN 'Summa Cum Laude'
        WHEN gpa >= 3.0 THEN 'Magna Cum Laude'
        WHEN gpa >= 2.5 THEN 'Cum Laude'
        ELSE 'Pass'
    END AS honor,
    IF(scholarship > 0, 'Funded', 'Self-paid') AS funding_status,
    IFNULL(middle_name, '-') AS middle,
    COALESCE(preferred_name, official_name, username) AS display_name;

6.5 윈도우 함수

-- 순위 및 순번
SELECT 
    name,
    gpa,
    ROW_NUMBER() OVER (ORDER BY gpa DESC) AS overall_rank,
    RANK() OVER (ORDER BY gpa DESC) AS gpa_rank,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY gpa DESC) AS dept_rank,
    NTILE(4) OVER (ORDER BY gpa) AS quartile
FROM students;

-- 누적 및 이동 집계
SELECT 
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month) AS cumulative,
    AVG(revenue) OVER (ORDER BY month ROWS 2 PRECEDING) AS moving_avg,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_diff
FROM monthly_sales;

7. 트랜잭션 관리

7.1 ACID 특성

  • Atomicity: 전체 성공 또는 전체 실패
  • Consistency: 유효한 상태만 유지
  • Isolation: 동시 실행 간 간섭 없음
  • Durability: 커밋 후 영구 저장

7.2 트랜잭션 제어

-- 기본 트랜잭션
START TRANSACTION;
-- 또는 BEGIN;

UPDATE accounts SET balance = balance - 100000 WHERE id = 1;
UPDATE accounts SET balance = balance + 100000 WHERE id = 2;

-- 확인 후 커밋 또는 롤백
COMMIT;
-- 또는 ROLLBACK;

-- 저장점 활용
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (100, 50000);
SET @order_id = LAST_INSERT_ID();

SAVEPOINT after_order_insert;

INSERT INTO order_items (order_id, product_id, qty) 
VALUES (@order_id, 5, 2);

-- 재고 부족 시 특정 지점까지 롤백
ROLLBACK TO SAVEPOINT after_order_insert;
-- 또는 전체 롤백
ROLLBACK;

7.3 격리 수준

-- 세션 수준 설정
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 수준: READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ(기본) < SERIALIZABLE

8. 뷰 (View)

저장된 SELECT 리로, 논리적 테이블 역할을 합니다.

-- 뷰 생성
CREATE VIEW honor_students AS
SELECT s.student_id, s.name, s.gpa, d.name AS dept_name
FROM students s
JOIN departments d ON s.department_id = d.dept_id
WHERE s.gpa >= 3.5;

-- 뷰 조회 (일반 테이블처럼 사용)
SELECT * FROM honor_students WHERE dept_name = 'Computer Science';

-- 뷰 수정 (재정의)
CREATE OR REPLACE VIEW honor_students AS
SELECT student_id, name, gpa, dept_name, scholarship
FROM students s
JOIN departments d ON s.department_id = d.dept_id
WHERE s.gpa >= 3.5 AND s.scholarship > 0;

-- 뷰 삭제
DROP VIEW IF EXISTS honor_students;

-- 업데이트 가능한 뷰 (제한적)
CREATE VIEW student_contacts AS
SELECT student_id, name, email, phone
FROM students
WITH CHECK OPTION;  -- 뷰 조건을 벗어나는 수정 방지

9. 인덱스 최적화

-- 단일 컬럼 인덱스
CREATE INDEX idx_students_name ON students(name);

-- 복합 인덱스 (컬럼 순서 중요)
CREATE INDEX idx_students_dept_gpa ON students(department_id, gpa);

-- 유니크 인덱스
CREATE UNIQUE INDEX idx_students_email ON students(email);

-- 전문 검색 인덱스
CREATE FULLTEXT INDEX idx_courses_description ON courses(description);

-- 인덱스 활용 확인 (EXPLAIN)
EXPLAIN SELECT * FROM students WHERE name = '김철수';

-- 불필요한 인덱스 제거
DROP INDEX idx_students_name ON students;

10. SQL 실행 순서

  1. FROM/JOIN: 테이블 결합
  2. WHERE: 행 필터링
  3. GROUP BY: 그룹화
  4. HAVING: 그룹 필터링
  5. SELECT: 컬럼 선택/계산
  6. DISTINCT: 중복 제거
  7. ORDER BY: 정렬
  8. LIMIT: 결과 제한

태그: MySQL SQL RDBMS 데이터베이스 DDL

6월 12일 20:51에 게시됨

인기 태그

©2026 괴물 클럽