MySQL 기초 실습 - CRUD 연산 완전 정복

MySQL Workbench를 사용해서 데이터베이스 생성부터 CRUD(Create, Read, Update, Delete) 연산까지 전체적으로 실습해보았습니다. 실무에서 자주 사용하는 쿼리들을 단계별로 정리했습니다.

1. 데이터베이스 및 테이블 생성

데이터베이스 생성

CREATE DATABASE practice_db;
USE practice_db;

users 테이블 생성

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

contact 테이블 생성 (외래키 포함)

CREATE TABLE contact (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

2. 데이터 삽입 (INSERT)

사용자 데이터 삽입

INSERT INTO users (username, email, password) 
VALUES 
('김철수', 'chulsoo@email.com', 'hashed_password123'),
('이영희', 'younghee@email.com', 'hashed_password456'),
('박민수', 'minsu@email.com', 'hashed_password789');

연락처 데이터 삽입

INSERT INTO contact (user_id, name, phone, email, message)
VALUES 
(1, '홍길동', '010-1234-5678', 'hong@email.com', '문의사항이 있습니다.'),
(2, '김영수', '010-9876-5432', 'kimys@email.com', '제품에 대해 궁금합니다.'),
(NULL, '익명사용자', '010-5555-5555', 'anon@email.com', '회원가입 없이 문의드립니다.');

추가 데이터 삽입

INSERT INTO contact (user_id, name, phone, email, message)
VALUES 
(3, '박민수친구', '010-3333-4444', 'friend@email.com', '추천받고 왔어요'),
(1, '김철수동생', '010-5555-6666', 'brother@email.com', '형 소개로 문의드려요');

3. 데이터 조회 (SELECT)

기본 조회

-- 전체 연락처 조회
SELECT * FROM contact;

-- 특정 조건 조회
SELECT * FROM contact WHERE id = 1;

JOIN을 사용한 복합 조회

-- 사용자와 연락처 정보 함께 조회
SELECT 
    u.username as '실제사용자',
    c.name as '연락처이름', 
    c.phone as '전화번호',
    c.message as '메시지'
FROM users u
JOIN contact c ON u.id = c.user_id
ORDER BY u.username;

4. 데이터 수정 (UPDATE)

단일 레코드 수정

-- 특정 연락처 메시지 수정
UPDATE contact 
SET message = '수정된 문의사항입니다.' 
WHERE id = 1;

조건부 일괄 수정

-- 특정 사용자의 모든 연락처에 확인완료 표시 추가
UPDATE contact 
SET message = CONCAT(message, ' - 확인완료')
WHERE user_id = 2;

5. 데이터 삭제 (DELETE)

조건부 삭제

-- 특정 ID 연락처 삭제
DELETE FROM contact WHERE id = 3;
DELETE FROM contact WHERE id = 4;
DELETE FROM contact WHERE id = 5;
DELETE FROM contact WHERE id = 6;

6. 고급 쿼리 연습

집계 함수와 GROUP BY

-- 사용자별 연락처 개수 통계
SELECT 
    u.username as '사용자',
    COUNT(c.id) as '연락처개수'
FROM users u
LEFT JOIN contact c ON u.id = c.user_id
GROUP BY u.id, u.username
ORDER BY COUNT(c.id) DESC;

문자열 패턴 검색

-- 메시지에 '문의'가 포함된 연락처 검색
SELECT 
    u.username as '사용자',
    c.name as '연락처이름',
    c.message as '메시지'
FROM contact c
JOIN users u ON c.user_id = u.id
WHERE c.message LIKE '%문의%';

문자열 함수 활용

-- 전화번호 앞 3자리별 통계
SELECT 
    SUBSTRING(phone, 1, 3) as '통신사코드',
    COUNT(*) as '개수'
FROM contact
GROUP BY SUBSTRING(phone, 1, 3);

종합 데이터 조회

-- 사용자와 연락처 전체 데이터 LEFT JOIN으로 조회
SELECT 
    u.id as 'USER_ID',
    u.username as '사용자',
    u.email as '사용자이메일',
    c.name as '연락처이름',
    c.phone as '연락처전화번호',
    c.message as '메시지'
FROM users u
LEFT JOIN contact c ON u.id = c.user_id
ORDER BY u.id, c.created_at;

연습을 통해 배운 주요 포인트

1. CRUD 연산의 기본

• CREATE: INSERT로 새로운 데이터 추가
• READ: SELECT로 다양한 조건의 데이터 조회
• UPDATE: 기존 데이터 수정 및 일괄 처리
• DELETE: 조건에 맞는 데이터 삭제

2. JOIN의 활용

• INNER JOIN: 두 테이블의 매칭되는 데이터만 조회
• LEFT JOIN: 기준 테이블의 모든 데이터 + 매칭되는 데이터 조회

3. 집계 함수와 그룹핑

• COUNT, MAX 등의 집계 함수 활용
• GROUP BY를 통한 데이터 그룹핑
• ORDER BY를 통한 정렬

4. 문자열 처리

• LIKE 연산자를 통한 패턴 매칭
• CONCAT 함수를 통한 문자열 결합
• SUBSTRING 함수를 통한 문자열 추출