데이터베이스 기초 (1) - Relational Model and Basic SQL

데이터베이스 기초 시리즈의 1편입니다. 전체 목차는 0편에 있습니다.

relational model과 table 구조

PostgreSQL과 MySQL 같은 관계형 database는 데이터를 table에 저장합니다. table은 행(row)과 열(column)로 이루어진 2차원 구조이고, row 하나가 데이터 한 건, column 하나가 그 데이터의 속성 하나에 해당합니다. 모든 column에는 타입이 선언되고 server가 이를 강제합니다. 예를 들어 INTEGER column에 문자열 ‘abc’를 넣으려고 하면 저장되지 않고 오류가 반환됩니다.

NULL은 그 자리에 값이 없음을 나타내는 표시입니다. 숫자 0이나 빈 문자열과는 다른 상태이고, 비교하는 방법도 일반 값과 달라서 아래 “NULL 다루기”에서 따로 정리합니다.

자주 쓰는 타입은 다음과 같습니다.

타입	용도
INTEGER, BIGINT	정수. BIGINT가 더 넓은 범위를 표현합니다
TEXT, VARCHAR(n)	문자열. VARCHAR(n)은 최대 길이를 제한합니다
BOOLEAN	true 또는 false
TIMESTAMPTZ	시간대 정보를 포함한 시각
NUMERIC	소수점 자릿수가 정확해야 하는 값

돈 계산에는 부동소수점 타입 대신 NUMERIC을 씁니다. 부동소수점은 0.1 같은 십진 소수를 정확히 표현하지 못해 계산을 반복할수록 오차가 쌓이기 때문입니다.

실습 준비

실습은 PostgreSQL 18 Docker container와 psql로 진행합니다. container 실행과 psql 접속은 PostgreSQL 편에서 다뤘습니다. 이 시리즈는 게시판 데이터를 공통 예제로 쓰고, 이번 편에서는 회원 table인 users만 만듭니다. server 하나는 여러 database를 관리하고 table은 database 안에 만들어지므로, 실습용 database인 board를 먼저 만들고 접속을 전환합니다. 아래를 psql에 붙여넣습니다. 두 번째 줄의 \c는 접속 database를 board로 바꾸는 psql 명령입니다.

CREATE DATABASE board;
\c board
CREATE TABLE users (
    id        BIGSERIAL PRIMARY KEY,
    username  TEXT UNIQUE NOT NULL,
    joined_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

BIGSERIAL은 row를 넣을 때마다 1씩 자동으로 증가하는 값이 채워지는 BIGINT입니다. PRIMARY KEY는 각 row를 고유하게 식별하는 column이라는 표시이고, UNIQUE는 중복 값 금지, NOT NULL은 NULL 금지, DEFAULT now()는 값을 생략하면 현재 시각을 넣는다는 뜻입니다. 여기서는 이름만 알아두면 되고, 제약 전체는 2편에서 다룹니다.

INSERT로 회원 세 명을 넣습니다. INSERT는 INSERT INTO table (column 목록) VALUES (값 목록) 형태이고, column 목록과 값 목록이 순서대로 대응하며, 값 묶음을 쉼표로 이어 여러 row를 한 번에 넣습니다.

INSERT INTO users (username) VALUES ('alice'), ('bob'), ('carol');

id와 joined_at은 생략했으므로 자동으로 채워지고, alice가 id 1, bob이 2, carol이 3을 받습니다.

SELECT 조회

조회할 column은 쉼표로 나열하고, 전체 column은 *로 지정합니다.

SELECT username, joined_at FROM users;
SELECT * FROM users;

WHERE는 조건에 맞는 row만 걸러냅니다. 비교 연산자는 =, <>, <, >, <=, >=이고, 조건 여러 개는 AND와 OR로 결합합니다. IN은 목록 중 하나와 일치하는지, BETWEEN은 양 끝을 포함한 범위에 드는지, LIKE는 패턴과 일치하는지를 검사하며, LIKE 패턴의 %는 길이 제한이 없는 임의의 문자열을 뜻합니다.

SELECT * FROM users WHERE id >= 2;
SELECT * FROM users WHERE id > 1 AND username <> 'carol';
SELECT * FROM users WHERE username IN ('alice', 'carol');
SELECT * FROM users WHERE id BETWEEN 1 AND 2;
SELECT * FROM users WHERE username LIKE 'a%';

두 번째 쿼리는 bob 한 row를, 마지막 쿼리는 a로 시작하는 alice 한 row를 반환합니다.

ORDER BY는 결과를 정렬합니다. 기본값은 오름차순 ASC이고, DESC를 붙이면 내림차순입니다. ORDER BY가 없으면 결과 순서는 보장되지 않습니다. 아래 쿼리는 carol, bob, alice 순서로 반환합니다.

SELECT * FROM users ORDER BY username DESC;

LIMIT은 반환할 row 수를 제한하고, OFFSET은 앞의 row를 건너뜁니다. 아래 쿼리는 id 순서에서 첫 row인 alice를 건너뛰고 bob과 carol을 반환합니다.

SELECT * FROM users ORDER BY id LIMIT 2 OFFSET 1;

DISTINCT는 결과에서 중복 row를 제거합니다. 문자열 길이를 반환하는 length 함수로 확인해 봅니다.

SELECT DISTINCT length(username) FROM users;

세 username의 길이는 5, 3, 5이지만 중복이 제거되어 5와 3 두 row만 반환됩니다.

NULL 다루기

NULL이 든 column을 직접 만들어 확인합니다. ALTER TABLE로 NULL을 허용하는 bio column을 추가하면, 기존 세 row의 bio는 모두 NULL이 됩니다.

ALTER TABLE users ADD COLUMN bio TEXT;

NULL은 = 연산자로 비교할 수 없습니다. NULL = NULL의 결과는 true가 아니라 NULL이고, WHERE는 조건이 true인 row만 반환하므로 아래 첫 쿼리는 세 row의 bio가 전부 NULL인데도 아무것도 반환하지 않습니다. NULL 검사는 IS NULL과 IS NOT NULL로 합니다.

SELECT username FROM users WHERE bio = NULL;
SELECT username FROM users WHERE bio IS NULL;
SELECT username FROM users WHERE bio IS NOT NULL;

첫 쿼리는 0 row, 두 번째 쿼리는 세 row 전부, 세 번째 쿼리는 0 row를 반환합니다. 확인이 끝났으니 column을 지워 표준 schema로 되돌립니다.

ALTER TABLE users DROP COLUMN bio;

UPDATE와 DELETE

UPDATE는 UPDATE table SET column = 값 WHERE 조건, DELETE는 DELETE FROM table WHERE 조건 형태입니다. 연습용 회원을 하나 넣고 수정해 봅니다.

INSERT INTO users (username) VALUES ('dave');
SELECT * FROM users WHERE username = 'dave';
UPDATE users SET username = 'dave_kim' WHERE username = 'dave';

두 문장 모두 WHERE를 빼먹으면 table의 모든 row에 적용됩니다. 예를 들어 DELETE FROM users;는 회원 전체를 지우고, psql 기본 설정에서는 실행 즉시 확정되어 되돌릴 수 없습니다. 그래서 위처럼 실행 전에 같은 WHERE 조건으로 SELECT를 먼저 실행해 대상 row를 확인하는 습관을 들입니다. 변경을 확정하기 전에 취소하는 방법은 6편에서 다룹니다.

연습용 회원을 지워 원래 상태로 되돌립니다. SELECT로 대상 row를 확인한 뒤 같은 WHERE 조건으로 DELETE를 실행합니다.

SELECT * FROM users WHERE username = 'dave_kim';
DELETE FROM users WHERE username = 'dave_kim';
SELECT * FROM users;

마지막 SELECT 결과에 alice, bob, carol 세 row만 남은 것을 확인합니다. 이것으로 users는 다음 편에서 이어 쓸 표준 상태가 되었습니다.

MySQL 차이

MySQL 8.4에는 TIMESTAMPTZ 타입이 없습니다. 시각은 DATETIME이나 TIMESTAMP로 저장하는데, DATETIME은 입력된 값을 그대로 저장하고 TIMESTAMP는 UTC로 변환해 저장한 뒤 접속(세션)마다 설정된 시간대에 맞춰 보여줍니다. 자동 증가 정수는 BIGSERIAL이 아니라 column에 AUTO_INCREMENT 속성을 붙여 만들며, 이 차이는 2편에서 다시 다룹니다. 이 편에서 다룬 SELECT, INSERT, UPDATE, DELETE와 WHERE, ORDER BY, LIMIT, OFFSET, DISTINCT, IN, BETWEEN, LIKE, IS NULL 문법은 MySQL에서도 그대로 쓸 수 있습니다. 한 가지 차이는 대소문자입니다. MySQL의 기본 collation은 대소문자를 구분하지 않아 LIKE 'A%'가 alice와 일치하지만, PostgreSQL의 LIKE와 문자열 비교는 대소문자를 구분합니다. 다만 \c로 접속 database를 바꾸는 것은 psql 방식이고, MySQL client에서는 USE board;를 실행해 database를 전환합니다. MySQL client에도 백슬래시 명령이 있지만 뜻이 달라서, \c는 입력 중인 문장을 취소하는 명령입니다. MySQL의 설치와 기본 사용은 MySQL 편에서 다뤘습니다.

다음 편

다음 편인 2편에서는 게시판 데이터를 users, posts, comments 세 table로 나누는 schema 설계와 이를 강제하는 제약을 다룹니다.