MySQL과 SQL 기본기

파일 저장에서 데이터베이스로 넘어가기

앞 장에서는 파일에 텍스트, JSON, 업로드 정보를 저장하는 방법을 배웠습니다. 파일은 간단한 설정이나 로그를 저장할 때 유용합니다. 하지만 게시글 수가 많아지고, 회원별로 글을 찾고, 최신순으로 정렬하고, 특정 단어를 검색해야 한다면 파일만으로는 금방 복잡해집니다.

이때 사용하는 것이 데이터베이스입니다. 데이터베이스는 데이터를 체계적으로 저장하고, 필요한 데이터를 빠르게 찾고, 잘못된 데이터가 들어오지 않도록 도와주는 저장 시스템입니다. PHP 웹 애플리케이션에서는 관계형 데이터베이스 관리 시스템인 MySQL을 많이 사용합니다.

PHP가 화면과 요청 처리를 맡는다면, MySQL은 회원, 게시글, 댓글, 주문 내역 같은 데이터를 오래 보관하는 역할을 합니다. PHP는 SQL이라는 언어로 MySQL에 질문합니다. 예를 들어 게시글 목록을 최신순으로 가져오고 싶다면 다음과 같은 SQL을 사용할 수 있습니다.

SELECT title, created_at
FROM posts
ORDER BY created_at DESC;

이 문장은 "게시글 테이블에서 제목과 작성일을 가져오되, 최신순으로 정렬해 달라"는 뜻입니다. posts 테이블은 뒤에서 직접 만들어 볼 테니, 지금은 SQL이 대략 이런 모양으로 생겼다는 정도만 봐도 충분합니다.

AI 생성 이미지

데이터베이스, 테이블, 행, 열

관계형 데이터베이스는 데이터를 표처럼 다룹니다. 엑셀 표를 떠올리면 이해하기 쉽습니다.

예를 들어 users 테이블은 다음처럼 생각할 수 있습니다.

여기서 id는 기본 키입니다. 이름은 같을 수 있고 이메일도 변경될 수 있지만, id는 각 사용자를 구별하는 기준으로 사용합니다. 웹 애플리케이션에서 "3번 게시글 보기", "5번 사용자 수정"처럼 특정 데이터를 정확히 가리킬 때 기본 키가 필요합니다.

데이터베이스와 테이블 만들기

먼저 블로그 예제에 사용할 데이터베이스를 만든다고 가정해 봅시다.

CREATE DATABASE php_textbook
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE php_textbook;

CREATE DATABASE는 새 데이터베이스를 만드는 명령이고, USE는 앞으로 사용할 데이터베이스를 선택하는 명령입니다. SQL 문장은 보통 세미콜론(;)으로 끝냅니다.

utf8mb4는 한글과 이모지까지 폭넓게 저장할 수 있는 문자 인코딩입니다. 새 프로젝트라면 특별한 이유가 없는 한 utf8mb4를 사용하는 것이 좋습니다.

이제 사용자 테이블을 만들어 보겠습니다.

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

각 열에는 자료형과 규칙이 붙습니다.

• BIGINT UNSIGNED: 음수가 없는 큰 정수입니다.

• AUTO_INCREMENT: 새 행이 추가될 때 숫자가 자동으로 증가합니다.

• PRIMARY KEY: 이 열을 기본 키로 사용합니다.

• VARCHAR(50): 최대 50자까지 저장하는 문자열입니다.

• NOT NULL: 반드시 값이 있어야 한다는 뜻입니다.

• UNIQUE: 같은 값이 중복될 수 없다는 뜻입니다.

• DEFAULT CURRENT_TIMESTAMP: 행이 만들어진 시간을 기본값으로 넣습니다.

비밀번호는 원문으로 저장하지 않고 해시된 값을 저장해야 합니다. 위 예제의 password_hash 열은 그 해시 값을 담기 위한 곳입니다. 실제 PHP에서 비밀번호를 처리하는 방법은 보안 장에서 더 자세히 다룹니다.

SELECT: 데이터 조회하기

SELECT는 데이터를 읽을 때 사용합니다.

SELECT id, name, email
FROM users;

이 문장은 users 테이블에서 id, name, email 열을 조회합니다. 모든 열을 가져오고 싶을 때 *를 사용할 수도 있습니다.

SELECT *
FROM users;

하지만 실무에서는 필요한 열만 명시하는 습관이 좋습니다. 데이터 양을 줄일 수 있고, 코드가 어떤 값을 사용하는지도 분명해집니다.

특정 사용자만 찾으려면 WHERE를 사용합니다.

SELECT id, name, email
FROM users
WHERE id = 1;

WHERE는 "조건에 맞는 행만" 고르는 역할을 합니다. 숫자는 그대로 쓰지만, 문자열 값은 작은따옴표로 감쌉니다.

SELECT id, name, email
FROM users
WHERE email = '[email protected]';

조건에는 비교 연산자를 사용할 수 있습니다.

SELECT id, title, created_at
FROM posts
WHERE view_count >= 100;

문자열 일부를 찾을 때는 LIKE를 사용할 수 있습니다.

SELECT id, title
FROM posts
WHERE title LIKE '%PHP%';

%PHP%는 앞뒤에 어떤 글자가 와도 되고, 중간에 PHP가 포함되면 된다는 뜻입니다.

ORDER BY와 LIMIT

웹 페이지는 보통 모든 데이터를 한 번에 보여주지 않습니다. 최신 게시글 10개, 조회수 높은 글 5개처럼 필요한 만큼만 가져옵니다.

정렬에는 ORDER BY를 사용합니다.

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC;

• ASC: 오름차순입니다. 작은 값에서 큰 값으로 정렬합니다.

• DESC: 내림차순입니다. 큰 값에서 작은 값으로 정렬합니다.

가져올 개수를 제한하려면 LIMIT을 사용합니다.

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 10;

이 문장은 최신 게시글 10개만 가져옵니다. 게시판 첫 화면을 만들 때 자주 쓰는 형태입니다.

페이지네이션을 만들 때는 LIMIT과 OFFSET을 함께 사용할 수 있습니다.

SELECT id, title, created_at
FROM posts
ORDER BY id DESC
LIMIT 10 OFFSET 20;

이 문장은 앞의 20개를 건너뛰고 10개를 가져옵니다. 즉 한 페이지에 10개씩 보여줄 때 3페이지에 해당하는 데이터를 가져오는 방식입니다.

INSERT: 데이터 추가하기

INSERT는 새 행을 추가할 때 사용합니다.

INSERT INTO users (name, email, password_hash)
VALUES ('김민수', '[email protected]', 'hashed_password_here');

id와 created_at은 직접 넣지 않았습니다. id는 AUTO_INCREMENT로 자동 증가하고, created_at은 기본값으로 현재 시간이 들어가기 때문입니다.

게시글 테이블이 있다면 글 작성은 다음처럼 표현할 수 있습니다.

INSERT INTO posts (user_id, title, content)
VALUES (1, '첫 번째 글', 'PHP와 MySQL을 연결하기 전 SQL을 먼저 배웁니다.');

여기서 user_id는 이 글을 작성한 사용자의 id입니다. 게시글과 사용자를 연결하는 열입니다.

UPDATE: 데이터 수정하기

UPDATE는 기존 행을 수정합니다.

UPDATE users
SET name = '김민준'
WHERE id = 1;

가장 중요한 부분은 WHERE입니다. WHERE가 없으면 모든 행이 수정됩니다.

UPDATE users
SET name = '이름없음';

이 문장은 모든 사용자의 이름을 이름없음으로 바꿉니다. 의도하지 않았다면 큰 사고입니다. UPDATE를 실행하기 전에는 같은 조건으로 SELECT를 먼저 해 보는 습관이 좋습니다.

SELECT id, name, email
FROM users
WHERE id = 1;

수정할 대상이 맞는지 확인한 뒤 UPDATE를 실행하면 실수를 줄일 수 있습니다.

DELETE: 데이터 삭제하기

DELETE는 행을 삭제합니다.

DELETE FROM posts
WHERE id = 10;

DELETE도 WHERE가 매우 중요합니다.

DELETE FROM posts;

이 문장은 posts 테이블의 모든 행을 삭제합니다. 테이블 구조가 사라지는 것은 아니지만, 저장되어 있던 게시글 데이터는 모두 없어집니다. 그래서 삭제 SQL은 항상 신중하게 작성해야 합니다. 특히 실제 서비스 데이터베이스에서는 백업, 권한, 트랜잭션 같은 안전장치가 필요합니다.

트랜잭션은 여러 작업을 하나의 묶음으로 처리하고, 문제가 생기면 되돌리는 기능입니다. 이후 장에서 데이터베이스를 PHP와 함께 다룰 때 다시 만납니다.

게시글과 사용자 테이블 모델링

데이터 모델링은 "어떤 데이터를 어떤 테이블에 나누어 담을지 정하는 일"입니다. 처음에는 어렵게 느껴질 수 있지만, 핵심은 중복을 줄이고 관계를 분명히 하는 것입니다.

블로그에는 사용자와 게시글이 있습니다.

• 사용자 한 명은 여러 게시글을 쓸 수 있습니다.

• 게시글 하나는 한 명의 작성자를 가집니다.

이 관계를 테이블로 표현하면 다음과 같습니다.

CREATE TABLE posts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(200) NOT NULL,
  content TEXT NOT NULL,
  view_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  CONSTRAINT fk_posts_user
    FOREIGN KEY (user_id) REFERENCES users(id)
);

posts.user_id는 users.id를 가리킵니다. 이런 관계를 외래 키(foreign key)라고 합니다. 외래 키를 사용하면 존재하지 않는 사용자의 게시글이 만들어지는 것을 막을 수 있습니다. 이 예제에서 users.id와 posts.user_id가 모두 BIGINT UNSIGNED인 것처럼, 서로 연결되는 열의 자료형은 맞춰 두는 것이 좋습니다.

예를 들어 users 테이블에 id = 999인 사용자가 없다면, 다음 INSERT는 실패해야 정상입니다.

INSERT INTO posts (user_id, title, content)
VALUES (999, '잘못된 글', '없는 사용자가 작성한 글입니다.');

이런 규칙은 데이터 무결성을 지키는 데 중요합니다. 데이터 무결성이란 데이터가 앞뒤가 맞고 신뢰할 수 있는 상태를 뜻합니다.

JOIN 맛보기

게시글 목록을 보여줄 때는 글 제목뿐 아니라 작성자 이름도 함께 보여주고 싶습니다. 그런데 제목은 posts 테이블에 있고, 작성자 이름은 users 테이블에 있습니다. 이때 JOIN을 사용합니다.

SELECT
  posts.id,
  posts.title,
  users.name AS author_name,
  posts.created_at
FROM posts
JOIN users ON posts.user_id = users.id
ORDER BY posts.id DESC
LIMIT 10;

JOIN은 관련 있는 두 테이블을 연결해서 조회하는 문법입니다. 지금은 "게시글의 user_id와 사용자의 id가 같은 행을 연결한다" 정도로 이해하면 충분합니다. AS author_name은 조회 결과에서 users.name 열을 author_name이라는 이름으로 보여주겠다는 뜻입니다.

인덱스와 제약 조건

데이터가 적을 때는 어떤 방식으로 조회해도 큰 차이가 나지 않습니다. 하지만 게시글이 10만 개, 100만 개가 되면 조건 검색과 정렬 속도가 중요해집니다.

인덱스(index)는 책의 찾아보기와 비슷합니다. 책에서 특정 단어를 찾을 때 처음부터 끝까지 읽지 않고 찾아보기를 확인하듯, 데이터베이스도 인덱스를 사용해 필요한 행을 더 빨리 찾을 수 있습니다.

예를 들어 게시글을 작성자별로 자주 조회한다면 user_id에 인덱스를 만들 수 있습니다.

CREATE INDEX idx_posts_user_id
ON posts (user_id);

다만 위에서 만든 posts 테이블처럼 외래 키가 걸려 있는 열은 MySQL의 InnoDB가 필요한 인덱스를 이미 만들어 두었을 수 있습니다. 이 예제는 인덱스를 만드는 문법을 보여 주기 위한 것으로 이해하면 됩니다. 실제로는 이미 같은 역할을 하는 인덱스가 있는지 확인한 뒤 중복해서 만들지 않는 것이 좋습니다.

최신 글 목록을 자주 조회한다면 created_at에 인덱스를 고려할 수 있습니다.

CREATE INDEX idx_posts_created_at
ON posts (created_at);

하지만 인덱스는 무조건 많이 만들수록 좋은 것이 아닙니다. 조회는 빨라질 수 있지만, 데이터 추가·수정·삭제 때 인덱스도 함께 관리해야 하므로 쓰기 작업은 느려질 수 있습니다. 자주 검색하거나 정렬하는 열에 신중하게 만드는 것이 좋습니다.

제약 조건은 데이터가 지켜야 할 규칙입니다.

CREATE TABLE categories (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

여기서 NOT NULL은 값이 비어 있으면 안 된다는 규칙이고, UNIQUE는 같은 카테고리 이름을 중복 저장할 수 없다는 규칙입니다. 제약 조건을 사용하면 PHP 코드에서 실수하더라도 데이터베이스가 한 번 더 막아 줍니다.

실습 과제

다음 SQL을 직접 실행해 보세요.

1. php_textbook 데이터베이스를 만듭니다.

2. users 테이블과 posts 테이블을 만듭니다.

3. 사용자 2명을 추가합니다.

4. 각 사용자에게 게시글을 2개씩 추가합니다.

5. 최신 게시글 3개만 조회합니다.

6. 제목에 PHP가 들어간 게시글만 조회합니다.

7. 특정 게시글의 조회수를 1 증가시킵니다.

조회수 증가는 다음처럼 작성할 수 있습니다.

UPDATE posts
SET view_count = view_count + 1
WHERE id = 1;

실습할 때는 INSERT한 사용자 id를 확인한 뒤 그 값을 posts.user_id에 넣어야 합니다. 예를 들어 사용자가 id = 1, id = 2로 만들어졌다면 게시글의 user_id에는 1 또는 2를 넣습니다.

자주 발생하는 오류 점검

WHERE 없이 UPDATE나 DELETE를 실행했다

가장 위험한 실수입니다. 수정과 삭제 전에는 같은 조건으로 SELECT를 먼저 실행해 대상 행을 확인하세요.

문자열을 따옴표로 감싸지 않았다

SQL에서 문자열 값은 작은따옴표로 감쌉니다.

-- 잘못된 예
SELECT * FROM users WHERE email = minsu@example.com;

-- 올바른 예
SELECT * FROM users WHERE email = '[email protected]';

기본 키와 외래 키의 의미를 혼동했다

기본 키는 자기 테이블의 행을 구별하는 값입니다. 외래 키는 다른 테이블의 기본 키를 가리키는 값입니다. posts.id는 게시글 자체의 번호이고, posts.user_id는 작성자의 번호입니다.

사용자 입력을 SQL에 직접 붙이려고 했다

다음 장에서는 PHP에서 MySQL에 접속하고 SQL을 실행하는 방법을 배웁니다. 이때 사용자가 입력한 값을 SQL 문자열에 직접 붙이면 SQL 인젝션이라는 보안 문제가 생길 수 있습니다. 그래서 실무에서는 PDO와 준비된 문장을 사용합니다.

이번 장에서는 SQL의 모양과 데이터베이스의 기본 구조를 익히는 데 집중했습니다. 이제 데이터베이스, 테이블, 행, 열, 기본 키, 외래 키, SELECT, INSERT, UPDATE, DELETE, JOIN의 의미를 알게 되었습니다. 다음 장에서는 이 SQL을 PHP 코드에서 안전하게 실행하는 방법으로 넘어갑니다.