42page

SQL, Postgres and Data Modeling

January 2nd 2021

Contents

  • Introduction
  • Filtering Data
  • Bringing Tables Together with Join
  • Deletion Constraints

  • Join

    • Four Kinds of Joins
    • Using Where with Join
  • Aggregation and Grouping
  • MySql SET 구 실행 순서
  • Physical and Logical Delete

Introduction

이전에 "SQL and Data Modeling" 이란 포스팅을 작성하며 DBAPI를 이용하기 위한 각 레이어에 대한 개념 위주로 알아본 적이 있습니다. 이번 글에서는 sql 자체 사용 방법과 postgreSql의 기본적인 사용법 위주로 살펴보려 합니다.

클라이언트가 데이터베이스와 통신을 할 때 sql을 사용하기 때문에, 이에 대한 이해가 중요합니다. SQL은 Oracle, Microsoft SQL, SQL, MySQL 등의 데이터베이스에서 지원합니다. 각 RDBMS(관계형 데이터베이스 매니지먼트 시스템)마다 고유 방언이 있어 조금씩 차이가 있지만 일반적으로 SQL에 대해 알면 여러 RDBMS를 사용하는데 지장이 없습니다.

Sql 사용 시 고려해야 할 사항으로는 아래와 같은 항목들이 있어요.

  • 효율적인 데이터 읽기를 위한 쿼리 작성

    (데이터 추가, 삭제, 수정 등의 작업을 위해 쿼리를 작성하지만, 이 중 어떻게 데이터를 가져올 것인지가 중요하고 또한 어렵습니다.)

  • 데이터베이스 구조 및 스키마

    (데이터베이스의 구조 및 스키마를 구성하는 방법은 매우 많으며, 어떤 방식을 선택할 지 결정하는 것이 쉽지 않습니다.)

  • 적절한 상황에 고급 기능 사용하기
  • Production 환경에서의 데이터베이스 운영 (ex, 데이터베이스 백업, 스케일링 등)

SQL 사용 시 위 내용들에 중점을 두어 포스팅을 작성하고자 합니다.

Filtering Data

데이터베이스 테이블의 모든 행에 대한 데이터를 읽어 올 수 있지만, 많은 경우 특정 조건에 맞는 데이터만 읽는 일이 많습니다. 쿼리문 작성 시 "WHERE" 구에서 특정 조건을 설정할 수 있습니다. 기초적인 작업이지만 이에 대한 이해를 높이면 이후 복잡한 쿼리 작성에도 도움이 되기 때문에 중요합니다. Fundamentals..!

우리가 아래와 같은 쿼리를 작성하면, 이 쿼리는 앞에서부터 순차적으로 해석되지 않습니다.

우선 데이터베이스에서 필요한 데이터를 먼저 취하고, 후에 WHERE 구에 명시된 특정 조건을 적용합니다. 이렇게 추려진 데이터에서 필요한 Column을 선택해 읽습니다.

Primary Keys & Foreign Keys

관계형 데이터베이스 테이블의 각 행은 다른 행과의 구분을 위해 유니크한 값을 지닌 열이 있어야 해요. 이 column을 Primary Key라고 합니다. Foreign Key는 다른 테이블에서 사용하는 Primary Key를 말합니다. 테이블 간 관계를 형성할 수 있도록 하는데 사용됩니다.

Primary Key는 보통 컬럼 이름으로 "id"를 사용하며, 값은 interger나 uuid 등으로 대게 사용합니다. Primary Key로 integer를 사용하는 경우 postgreSQL에서는 SERIAL 데이터 타입을 사용할 수도 있습니다. 아래와 같이 users 테이블을 생성하면, 행을 추가할 때 username 값만 설정하면 id는 순차적인 숫자로 자동으로 입력이 됩니다.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50)
);

만약 photos란 테이블이 있고, 이 테이블의 user_id란 컬럼이 위 users 테이블을 참조하는 Foreign Key로 사용하고 싶다면 아래와 같이 REFERENCES를 이용해 관계를 형성할 수 있습니다.

CREATE TABLE photos (
  id SERIAL PRIMARY KEY,
  url VARCHAR(200),
  user_id INTEGER REFERENCES users(id)
);

만약 photos 테이블에 행을 추가할 때 존재하지 않는 user_id 값을 입력하면 에러가 발생합니다.

참고로, 참조하는 user가 없는 이미지 데이터를 photos 테이블에 추가하려면, user_id 값을 Null로 설정 후 추가할 수 있습니다.

하지만 user_id에 NULL 값이 있는 경우 Select 문으로 데이터를 읽을 때 고려해야 할 점이 있습니다.

Deletion Constraints

users 테이블의 한 유저를 삭제하려 할 때, 해당 유저를 다른 photos 테이블에서 참조하고 있을 수 있습니다. 이 경우 삭제와 관련된 여러 옵션을 설정해 삭제와 연관된 세세한 부분을 어떻게 처리할 지 지정할 수 있습니다.

어떠한 옵션을 지정하지 않았을 때, 삭제하려는 데이터가 다른 곳에서 참조하고 있다면 에러가 발생합니다. 이외에 아래와 같은 여러 옵션을 참고할 수 있습니다.

- ON DELETE RESTRICT
  : (default) photo가 참조하는 user 삭제 시 에러 발생 

- ON DELETE NO ACTION
  : photo가 참조하는 user 삭제 시 에러 발생 

- ON DELETE CASCADE
  : 참조하고 있는 photo도 함께 삭제 

- ON DELETE SET NULL
  : 참조하고 있는 photo의 user_id를 NULL로 수정

- ON DELETE SET DEFAULT
  : 참조하고 있는 photo의 user_id를 default 값으로 수정

실제로 위 옵션을 테이블 생성 시 적용하면 아래의 예와 같이 작성할 수 있습니다.

CREATE TABLE photos (
  id SERIAL PRIMARY KEY,
  url VARCHAR(200),
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);

photos 테이블을 생성하고, 참조하고 있는 유저를 삭제하면 이와 연관된 photo도 함께 삭제가 됩니다.

블로그 포스트나 포럼의 경우 글을 삭제하게 되면, 해당 글과 연관된 코멘트도 삭제가 필요합니다. 이러한 경우 위 옵션을 설정해 사용해 볼 수 있을거에요.

JOIN

여러 테이블의 여러 행을 연관지어 데이터를 가져오기 위해 JOIN을 사용할 수 있습니다. photos 테이블이 있고 해당 테이블에 user_id를 foreign key로 가지고 있는 컬럼이 있을 때 photos의 url과 user 테이블의 user 정보를 함께 가져오기 위해 JOIN을 사용할 수 있습니다.

JOIN statement를 사용하면 임시의 새로운 테이블이 새로 생성되고, 이 테이블에 연관 지은 테이블의 컬럼이 추가됩니다. 그리고 이 임시 테이블에서 SELECT 한 데이터를 아래와 같이 가져옵니다.

위 예시는 간단하지만, JOIN에는 상황에 따라 사용할 수 있는 기능적으로 분류한 여러 종류의 JOIN이 있습니다.

만약 위 photos 테이블의 'user id' 컬럼이 NULL인 row가 있는 상태에서 위의 쿼리를 실행하면 photos 테이블의 모든 row 데이터를 얻을 수 있을까요? 실제로 실행해 보면 user_id가 NULL인 row를 제외하고 데이터가 출력됩니다.

전체 photos에 대한 데이터를 가져오는 것이 의도였다면 위 쿼리는 문제가 될 것입니다.

Four Kinds of Joins

앞서 Join을 살펴봤는데 어떠한 추가 설정 없이 Join 쿼리를 사용하면 Inner Join이 적용됩니다. 그럼 Inner Join이 무엇인지 살펴보고, 아래의 다른 종류의 중요한 Join에 대해서 알아보겠습니다.

  • Inner Join
  • Left Join
  • Right Join
  • Full Join

Inner Join

SELECT url, username
FROM photos
JOIN users ON users.id = photos.user_id

Inner Join은 default로 설정되는 값이며 일반적으로 가장 많이 사용하는 결합 중 하나입니다. Inner Join은 테이블 결합 시 매칭되는 row가 없다면 해당 rows는 결합하는 테이블에 포함하지 않습니다.

위의 예시에서 photo id 3 row에 user_id가 없으므로 해당 row는 포함되지 않습니다. 또한 users 테이블의 id 1도 매칭되는 photo가 없기 때문에 Inner Join 시 포함되지 않았습니다.

이를 벤다이어그램으로는 아래와 같이 표현할 수 있습니다. 데이터 중 색으로 표시된 영역이 Inner Join 쿼리 시 도출되는 부분인데 Inner Join이라는 이름 또한 여기에서 나왔다고 합니다.

Left Outer Join

SELECT url, username
FROM photos
LEFT JOIN users ON users.id = photos.user_id

앞서 예시에서 Left Outer Join을 사용하게 되면, photos 테이블의 row 중 users 테이블에 매칭 되지 않는 row도 포함하게 됩니다.

Left outer join을 벤다이어그램으로 나타내면 아래와 같습니다.

Right Outer Join

SELECT url, username
FROM photos
RIGHT JOIN users ON users.id = photos.user_id

Right outer join은 Left outer join과 반대로 users 테이블의 row 중 photos 테이블에 매칭 되지 않는 row를 포함합니다.

Right outer join을 벤다이어그램으로 나타내면 아래와 같습니다.

Full Join

SELECT url, username
FROM photos
FULL JOIN users ON users.id = photos.user_id

Full join 에서는 양쪽 테이블의 매칭되지 않는 row를 모두 포함합니다.

Full join을 벤다이어그램으로 나타내면 아래와 같습니다.

기본적인 내용이지만 깊이 들어가면 매우 복잡한 토픽이라고 합니다. 먼저 기본적인 내용을 이해하고 개발 중 상황에 맞는 Join을 사용하며 익히면 좋을거 같습니다.

만약 위 예시와 같이 photos 테이블에서 url과 매칭되는 username을 포함하고, 매칭되는 user가 없는 photo도 포함하고 싶다면 Left outer join을 사용할 수 있습니다.

Using Where with Join

Join은 많이 사용하고 기본적인 개념이라 중요합니다. 앞서 몇 가지를 개념을 살펴봤는데, 이번에는 WHERE과 함께 사용하는 방법에 대해 알아보겠습니다.

블로그나 사진 공유 앱과 같은 제품에서 유저가 자신이 생성한 콘텐츠(글 또는 사진)에 스스로 남기 코멘트 리스트만 추려서 보게 할 수 있습니다.

만약 사진 앱이라고 한다면 자신이 등록한 사진의 url과 코멘트 내용을 출력하는 쿼리를 작성할 수 있을 거에요. 이를 그림으로 보면 아래와 같이 나타낼 수 있습니다.

최종적으로 comments 테이블과 photos 테이블의 user_id가 같은 행만 남겨야 하므로 여기서 WHERE 키워드를 사용할 수 있습니다.

WHERE구는 Join으로 결합한 photos, comments 테이블의 모든 row에 실행이 됩니다. WHERE구까지 실행된 쿼리를 그림으로 나타내면 아래와 같습니다.

위와 같이 자신이 등록한 사진에 자신이 남긴 comment 정보와 사진 url을 출력했습니다. 실제 앱에서 유저 이름이나 thumbnail 등 유저 정보도 함께 보여주면 좋을거에요. 하지만, 위 정보에서 유저와 관련된 정보는 볼 수 없습니다. 그러면 유저 정보까지 포함하기 위해서는 어떠한 벙법이 있을까요?

이와 같은 경우, 세 개의 다른 테이블을 결합하기 위한 Join을 사용해야 합니다.

SELECT url, contents, username
FROM comments
JOIN photos ON photos.id = comments.photo_id
JOIN users ON users.id = comments.user_id AND users.id = photos.user_id;

Aggregation and Grouping

앞선 예제에서는 테이블 전체 혹은 WHERE 구로 검색한 행을 대상으로 작업하는 것을 살펴봤습니다. 여기서는 데이터를 그룹화 하는 방법과 aggregating을 이용해 여러 컬럼에 걸쳐져 있는 데이터를 종합하여 특정 값을 도출하는 방법에 대해 알아보겠습니다.

Physical and Logical Delete

데이터베이스에서 데이터를 삭제하는 방법은 크게 "물리삭제"와 "논리삭제" 두 가지로 나뉩니다.

물리 삭제는 말 그대로 DELETE 명령을 이용해 데이터 자체를 삭제해 버리는 것입니다. 논리삭제는 여러 방법이 있지만 일반적으로 테이블에 삭제 플래그에 해당하는 column을 미리 준비하고 UPDATE 명령을 통해 삭제 플래그의 값을 수정하는 방법입니다.

논리삭제의 장점으로는 실제 데이터를 삭제하지 않기 때문에 쉽게 삭제 전 상태로 돌릴 수 있다는 것입니다. 하지만, 데이터를 삭제해도 살제 저장공간은 늘어나지 않고 검색속도에도 영향을 미칠 수 있습니다.

이커머스 사이트의 주문의 경우 논리삭제 방법을 많이 사용합니다. 주문이 취소되어도 발주 자체가 없었던 것은 아니므로 해당 데이터가 온전히 불필요한 것은 아닙니다. 또한, 이러한 데이터를 여러 통계 작업에 사용할 수도 있습니다.

Memo

OCTET_LENGTH

SQL에는 OCTETLENGTH 라는 문자열 연산 함수가 있습니다. OCTETLENGTH 함수는 문자열의 길이를 바이트 단위로 계산해 반환하는 함수입니다. 컴퓨터에는 이미지, 음성, 수치, 문자열 등 다양한 데이터가 저장됩니다. 문자를 인코딩 하는 방식에 따라 문자 하나의 데이터가 몇 바이트의 저장 공간을 필요로 할 지 결정됩니다. VARCHAR의 최대 길이 역시 바이트 단위로 지정합니다. 하지만, 문자세트에 따라 길이가 문자 수로 간주될 수도 있어 인지할 필요가 있습니다.

© Chanwhe Kim 2019

Handmade with .