[DB] 설계 (Schema & Query Design) / SQL 함수, 실행순서

시작하기 앞서 Schema?

데이터베이스에서 데이터가 구성되는 방식과 서로 다른 엔티티 간의 관계에 대한 설명. 데이터베이스의 청사진.

쉽게 말해 데이터베이스에서 자료의 구조, 자료의 표현 방법, 자료 간의 관계를 형식 언어로 정의한 구조이다.

https://dbdiagram.io/d/60fa52a9b7279e41233755ad

위 링크는 과제로 인스타 게시물에 의한 스키마디자인을 하였습니다. 인스타를 하는편이 아니어서 가볍게 했습니다. 스키마에 대해 이해에 도움이 되었으면 좋겠습니다.

과제로 스키마 디자인을 하면서 재미는 있었지만 업무라고 생각하면 스트레스 투성이일 것 같습니다.

1.  관계형 데이터베이스

구조화된 데이터는 하나의 테이블로 표현할 수 있습니다. 사전에 정의된 테이블을 relation 이라고도 부르기 때문에, 테이블을 사용하는 데이터베이스를 관계형 데이터베이스(Relational database)라고 합니다.

 

키워드 설명

• 데이터(data): 각 항목에 저장되는 값입니다.
• 테이블(table; 또는 relation) : 사전에 정의된 열의 데이터 타입대로 작성된 데이터가 행으로 축적됩니다.
• 칼럼(column; 또는 field) : 테이블의 한 열을 가리킵니다.
• 레코드(record; 또는 tuple) : 테이블의 한 행에 저장된 데이터입니다.
• 키(key) : 테이블의 각 레코드를 구분할 수 있는 값입니다. 각 레코드마다 고유한 값을 가집니다. 기본키(primary key)와 외래키(foreign key) 등이 있습니다. 테이블에 저장된 각각의 데이터를 유일하게 구분하는 키를기본키(Primary Key)라 부르며. 각 테이블 간에 연결을 만들기 위해서 테이블에서 다른 테이블의 참조되는 기본키 칼럼을외래키(Foregin Key)라 합니다. (다중컬럼기본키 라는 개념도 있습니다.)

 

Entity

entities(table 과 같은 개념)

field

field

record

record

 

1:N 관계

 

1:N

예시를 보자면 선생님은 한명이지만 수업은 여러개가 존재 합니다.

이 때, Class의 Name을 Teachers에 넣어 무슨 수업을 하는지 넣기보다 ID를 넣어 효율적으로 관리가 가능합니다.

User 테이블과 Phonebook 테이블의 관계를 다음과 같이 가정하겠습니다.

[그림] User table과 Phonebook table의 1:N 관계

이 구조에서는 한 명의 유저가 여러 전화번호를 가질 수 있습니다. 그러나 여러명의 유저가 하나의 전화번호를 가질 수는 없습니다. 이런 1:N(일대다) 관계는 관계형 데이터베이스에서 가장 많이 사용합니다.

 

 

N:N 관계

N:N

 

 

Join 테이블을 만들어 관리합니다. 1:N(일대다) 관계와 비슷하지만, 양방향에서 다수의 레코드를 가질 수 있습니다.

서로 중복해서 선택이 가능하기 때문에 JOIN 테이블을 만들어 쉽게 관리할 수 있습니다.

N:N(다대다) 관계를 위해 스키마를 디자인할 때에는, Join 테이블을 만들어 관리합니다. 1:N(일대다) 관계와 비슷하지만, 양방향에서 다수의 레코드를 가질 수 있습니다.

다음과 같이 여행 상품을 관리하는 테이블이 있다고 가정하겠습니다. 여러 개의 여행 상품이 있고, 여러 명의 고객이 있습니다. 고객 한 명은 여러 개의 여행 상품을 구매할 수 있고, 여행 상품 하나는 여러 명의 고객이 구매할 수 있습니다.

 Customer table과 Package table

이렇게 Customer 테이블과 Package table이 따로 존재한다면, N:N(다대다) 관계를 어떻게 표현할 수 있을까요? 다대다 관계는 두 개의 일대다 관계와 그 모양이 같습니다. 두 개의 테이블과 1:N(일대다) 관계를 형성하는 새로운 테이블로 N:N(다대다) 관계를 나타낼 수 있습니다.

이렇게 다대다 관계를 위한 테이블을 조인 테이블이라고 합니다. N:N(다대다) 관계를 그림으로 나타내면 다음과 같습니다.

Customer table과 Package table의 N:N 관계를 위해 customer_package 테이블을 생성

customer_package 테이블에서는 고객 한 명이 여러 개의 여행 상품을 가질 수 있고, 여행 상품 하나가 여러 개의 고객을 가질 수 있습니다.

customer_package 테이블은 customer_id와 package_id를 묶어주는 역할입니다. 이 테이블을 통해 어떤 고객이 몇 개의 여행 상품을 구매했는지 또는, 어떤 여행 상품이 몇 명의 고객을 가지고 있는지 등을 확인할 수 있습니다. 이렇게 조인 테이블을 생성하더라도, 조인 테이블을 위한 기본키(여기서는 cp_id)는 반드시 있어야 합니다.

 

1:1 관계

하나의 레코드가 다른 테이블의 레코드 한 개와 연결된 경우입니다. 쉬운 예를 들자면 학생과 폰번호나 자리를 앉을 때 짝 정도 예를 들 수 있겠습니다.

다음과 같이 User 테이블과 Phonebook 테이블이 있다고 가정하겠습니다.

[그림] User table과 Phonebook table의 1:1 관계

User 테이블은 user_id, name, phone_id를 가지고 있습니다. 이 중 phone_id는 외래키(foreign key)로써, Phonebook 테이블의 phone_id와 연결되어 있습니다. Phonebook 테이블은 phone_id와 phone_number를 가지고 있습니다.

각 전화번호가 단 한 명의 유저와 연결되어 있고, 그 반대도 동일하다면, User 테이블과 Phonebook 테이블은 1:1 관계(One-to-one relationship)입니다.

그러나 1:1 관계는 자주 사용하지 않습니다. 1:1로 나타낼 수 있는 관계라면 User 테이블에 phone_id를 대신해 phone_number를 직접 저장하는 게 나을 수 있습니다.

 

 

자기참조 관계(Self Referencing Relationship)

때로는 테이블 내에서도 관계가 필요합니다. 예를 들어 추천인이 누구인지 파악하기 위해 사용할 수 있습니다.

다음과 같이 유저 테이블이 있습니다. user_id는 기본 키(primary key), name은 사용자의 이름, 그리고 recommend_id는 추천인 아이디입니다.

자기참조 관계를 가지는 User table

User 테이블의 recommend_id는 User 테이블의 user_id와 연결되어 있습니다. 한 명의 유저(user_id)는 한 명의 추천인(recommend_id)를 가질 수 있습니다. 그러나 여러 명이 한 명의 유저를 추천인으로 등록할 수 있습니다. 이 관계는 1:N(일대다) 관계와 유사하다고 생각할 수 있습니다. 그러나 일반적으로 일대다 관계는 서로 다른 테이블의 관계를 나타낼 때 표현하는 방법입니다.

 

 

 

 

 

2.  SQL 내장함수

집합연산: 레코드를 조회하고 분류한 뒤, 특정 작업을 하는 연산

 

음원을 판매하는 사이트의 스키마 예시

 

 

SQL에서 사용되는 쿼리에는 유용하게 사용할 수 있는 함수도 많습니다. 그 중에서 자주 사용하는 몇 가지를 설명합니다.

자세한 사항은 w3schools에 찾아보면 쉽게 나와 있습니다. 전 게시글에도 써놨지만 문제도 있어서 익히기 좋기에 아래 내용은 보지않아도 됩니다

GROUP BY

데이터를 조회할 때 그룹으로 묶어서 조회합니다. 다음과 같은 쿼리가 있다고 가정하겠습니다.

SELECT * FROM customers;

customers 테이블의 모든 레코드를 조회

이 쿼리를 주(state)에 따라 그룹으로 묶어 표현할 수 있습니다.

SELECT * FROM customers
GROUP BY State;

customers 테이블의 모든 레코드를 State에 따라 그룹화

 

GROUP BY 쿼리로 간단하게 State에 따라 그룹화할 수 있습니다. 쿼리의 결과를 확인하면, 데이터가 중간에 비어있는 것을 확인할 수 있습니다. 데이터베이스에서 데이터를 불러오는 과정에서 State에 따라 그룹을 지정했지만, 그룹 대한 작업없이 조회만 했습니다. 그래서 쿼리의 결과로 나타나는 데이터는 각 그룹의 첫번째 데이터만 표현됩니다.

HAVING

HAVING은 GROUP BY 로 조회된 결과를 필터링할 수 있습니다.

SELECT CustomerId, AVG(Total)
FROM invoices
GROUP BY CustomerId
HAVING AVG(Total) > 6.00


invoices 테이블을 CustomerId로 그룹화하고 그 평균이 6을 초과한 결과를 조회

이 쿼리는 모든 고객의 주문서에서 가격의 평균을 구한 뒤에, 그 평균이 6.00을 넘는 결과만 조회합니다.

이렇게 GROUP BY로 그룹을 지은 결과에 필터를 적용할 때에는 HAVING을 사용할 수 있습니다.

HAVING은 WHERE과는 적용하는 방식이 다릅니다. HAVING은 그룹화한 결과에 대한 필터이고, WHERE는 저장된 레코드를 필터링합니다. 따라서 실제로 그룹화 전에 데이터를 필터해야 한다면, WHERE을 사용합니다.

COUNT()

COUNT 함수는 레코드의 갯수를 헤아릴 때 사용합니다. 

SELECT *, COUNT(*) FROM customers
GROUP BY State;

모든 레코드에 대한 COUNT 함수 사용 예시

 

위 커맨드를 실제로 실행하면, 각 그룹의 첫번째 레코드와 각 그룹의 레코드 갯수를 집계하여 리턴합니다. 다음과 같이 변경하면, 그룹으로 묶인 결과의 레코드 갯수를 확인할 수 있습니다.

 

SELECT State, COUNT(*) FROM customers
GROUP BY State;

각 State에 해당하는 레코드의 갯수를 확인하는 COUNT 함수 예시

SUM()

SUM 함수는 레코드의 합을 리턴합니다. SUM 함수의 사용법은 다음과 같습니다.

SELECT InvoiceId, SUM(UnitPrice)
FROM invoice_items
GROUP BY InvoiceId;

AVG()

AVG 함수는 레코드의 평균값을 계산하는 함수입니다. AVG 함수의 사용법은 다음과 같습니다.

SELECT TrackId, AVG(UnitPrice)
FROM invoice_items
GROUP BY TrackId;

MAX(), MIN()

MAX 함수와 MIN 함수는 각각 레코드의 최대값과 최소값을 리턴합니다. 이 함수들은 다음과 같이 사용합니다.

SELECT CustomerId, MIN(Total)
FROM invoices
GROUP BY CustomerId

 

 

3.  SELECT 실행 순서

 

데이터를 조회하는 SELECT 문은 정해진 순서대로 동작합니다. SELECT 문의 실행 순서는 다음과 같습니다.

 

SELECT CustomerId, AVG(Total)

FROM invoices

WHERE CustomerId >= 10

GROUP BY CustomerId

HAVING SUM(Total) >= 30

ORDER BY 2

 

설명

위 쿼리문의 실행 순서는 다음과 같습니다.

 

1. FROM invoices: invoices 테이블에 접근을 합니다.

2. WHERE CustomerId >= 10: CustomerId 필드가 10 이상인 레코드들을 조회합니다.

3. GROUP BY CustomerId: CustomerId를 기준으로 그룹화합니다.

4. HAVING SUM(Total) >= 30: Total 필드의 총합이 30 이상인 결과들만 필터링합니다.

5. SELECT CustomerId, AVG(Total): 조회된 결과에서 CustomerId 필드와 Total 필드의 평균값을 구합니다.

6. ORDER BY 2: AVG(Total) 필드를 기준으로 오름차순 정렬한 결과를 리턴합니다.