MySQL 완벽 가이드

MySQL은 가장 널리 사용되는 오픈소스 관계형 데이터베이스입니다. 이 가이드에서는 설치부터 실무에서 자주 사용하는 모든 SQL 문법을 다룹니다.

설치

Ubuntu/Linux

sudo apt-get update
sudo apt-get install mysql-server
mysql_secure_installation    # 보안 설정
systemctl status mysql.service   # 실행 상태 확인
sudo systemctl start mysql   # MySQL 시작

macOS

brew install mysql
mysql.server start
mysql -u root

비밀번호 설정

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

접속 및 기본 설정

MySQL 접속

mysql -u username -p

외부 접속 허용 계정 생성

-- 계정 생성
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

-- 모든 권한 부여
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'%' WITH GRANT OPTION;

-- 권한 적용
FLUSH PRIVILEGES;

외부 접속 설정 (Ubuntu)

# 설정 파일 수정
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

# bind-address = 127.0.0.1 주석 처리 후 재시작
sudo /etc/init.d/mysql restart

데이터베이스 관리

데이터베이스 생성/삭제

-- 생성
CREATE DATABASE db_name;
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 삭제
DROP DATABASE db_name;

-- 목록 조회
SHOW DATABASES;

-- 선택
USE db_name;

테이블 관리

테이블 생성

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    age INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

테이블 복사

-- 구조만 복사 (AUTO_INCREMENT, INDEX 포함)
CREATE TABLE new_table LIKE old_table;

-- 구조와 데이터 복사 (AUTO_INCREMENT 미포함)
CREATE TABLE new_table SELECT * FROM old_table;

-- 데이터만 복사
INSERT INTO new_table SELECT * FROM old_table;

-- 특정 컬럼만 복사
INSERT INTO new_table(col1, col2) SELECT col1, col2 FROM old_table;

테이블 조회

SHOW TABLES;
DESCRIBE table_name;
SHOW CREATE TABLE table_name;

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', 35);

중복 키 처리

-- 중복 시 업데이트 (UPSERT)
INSERT INTO users (id, name, email) VALUES (1, '홍길동', 'hong@example.com')
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    email = VALUES(email);

-- 중복 시 무시
INSERT IGNORE INTO users (name, email) VALUES ('홍길동', 'hong@example.com');

AUTO_INCREMENT 관리

-- AUTO_INCREMENT 시작값 변경
ALTER TABLE users AUTO_INCREMENT = 100;

-- AUTO_INCREMENT 초기화
ALTER TABLE users AUTO_INCREMENT = 0;

SELECT (데이터 조회)

기본 구문

-- 전체 조회
SELECT * FROM users;

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

-- 조건 조회
SELECT * FROM users WHERE age > 25;

-- 정렬
SELECT * FROM users ORDER BY created_at DESC;

-- 제한
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;  -- 페이지네이션

조건문

-- AND, OR
SELECT * FROM users WHERE age > 25 AND name LIKE '김%';

-- IN
SELECT * FROM users WHERE id IN (1, 2, 3);

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

-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE email IS NOT NULL;

-- LIKE 패턴 매칭
SELECT * FROM users WHERE name LIKE '김%';     -- '김'으로 시작
SELECT * FROM users WHERE name LIKE '%수';     -- '수'로 끝남
SELECT * FROM users WHERE name LIKE '%길%';    -- '길' 포함

집계 함수

SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age), MIN(age) FROM users;
SELECT SUM(age) FROM users;

-- GROUP BY
SELECT age, COUNT(*) as count FROM users GROUP BY age;

-- HAVING (그룹 조건)
SELECT age, COUNT(*) as count FROM users GROUP BY age HAVING count > 5;

JOIN

-- INNER JOIN
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN
SELECT u.name, o.order_date
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

UPDATE (데이터 수정)

기본 구문

UPDATE users SET name = '새이름' WHERE id = 1;

-- 여러 컬럼 수정
UPDATE users SET name = '새이름', age = 30 WHERE id = 1;

값 증가/감소

-- 특정 값 증가
UPDATE users SET login_count = login_count + 1 WHERE id = 1;

-- 특정 값 감소
UPDATE products SET stock = stock - 1 WHERE id = 100;

조건부 업데이트

UPDATE users
SET status = CASE
    WHEN age < 20 THEN 'minor'
    WHEN age >= 20 AND age < 65 THEN 'adult'
    ELSE 'senior'
END;

DELETE (데이터 삭제)

기본 구문

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

-- 여러 조건
DELETE FROM users WHERE age < 18 AND status = 'inactive';

-- 전체 삭제 (주의!)
DELETE FROM users;

-- 테이블 비우기 (더 빠름, AUTO_INCREMENT 초기화)
TRUNCATE TABLE users;

안전한 삭제

-- 삭제 전 확인
SELECT * FROM users WHERE status = 'inactive';

-- 확인 후 삭제
DELETE FROM users WHERE status = 'inactive';

ALTER (테이블 구조 변경)

컬럼 추가/수정/삭제

-- 컬럼 추가
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN address VARCHAR(255) AFTER email;

-- 컬럼 타입 변경
ALTER TABLE users MODIFY COLUMN age BIGINT;

-- 컬럼명 및 타입 변경
ALTER TABLE users CHANGE COLUMN old_name new_name VARCHAR(100);

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

제약조건

-- PRIMARY KEY 추가
ALTER TABLE users ADD PRIMARY KEY (id);

-- UNIQUE 제약조건 추가
ALTER TABLE users ADD UNIQUE (email);

-- FOREIGN KEY 추가
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- 제약조건 삭제
ALTER TABLE users DROP INDEX email;

INDEX (인덱스)

인덱스는 검색 성능을 향상시키지만, INSERT/UPDATE/DELETE 성능에는 영향을 줄 수 있습니다.

인덱스 생성

-- 단일 컬럼 인덱스
CREATE INDEX idx_name ON users(name);

-- 복합 인덱스
CREATE INDEX idx_name_email ON users(name, email);

-- UNIQUE 인덱스
CREATE UNIQUE INDEX idx_email ON users(email);

인덱스 조회

SHOW INDEX FROM users;

인덱스 삭제

DROP INDEX idx_name ON users;

인덱스 사용 팁

• WHERE, JOIN, ORDER BY에 자주 사용되는 컬럼에 인덱스 생성
• 카디널리티(고유값 비율)가 높은 컬럼에 효과적
• 복합 인덱스는 왼쪽부터 순서대로 사용됨

mysqladmin 명령어

MySQL 콘솔 외부에서 관리 작업을 수행할 수 있습니다.

# 데이터베이스 생성
mysqladmin -u root -p create db_name

# 데이터베이스 삭제
mysqladmin -u root -p drop db_name

# MySQL 상태 확인
mysqladmin -u root -p status

# MySQL 버전 확인
mysqladmin -u root -p version

# SQL 문 실행
mysqladmin -u root -p -e "SHOW DATABASES"

유용한 팁

대량 데이터 삽입 최적화

-- 트랜잭션으로 묶어서 삽입
START TRANSACTION;
INSERT INTO users (name) VALUES ('user1');
INSERT INTO users (name) VALUES ('user2');
-- ... 많은 INSERT
COMMIT;

쿼리 실행 계획 확인

EXPLAIN SELECT * FROM users WHERE name = '홍길동';

테이블 최적화

OPTIMIZE TABLE users;
ANALYZE TABLE users;