[database] 관계형 데이터베이스(2)

관계형 데이터베이스

PK & FK

• Primary Key : 해당 테이블의 고유한 id값.
• Foriegn Key : 참조하는 테이블과 참조되는 테이블의 관계를 말한다.

테이블 생성시 FOREIGN KEY 가 설정된 컬럼을 포함하는 방법

CREATE TABLE [테이블명] (    [컬럼명] [데이터형식] FOREIGN KEY REFERENCES [테이블명] ([컬럼명])  )

컬럼에 FOREIGN KEY 추가방법

ALTER TABLE [테이블명] ADD CONSTRAINT [FOREIGN KEY명] FOREIGN KEY ([컬럼명]) REFERENCES [테이블명] ([컬럼명])

컬럼에 FOREIGN KEY 삭제방법

ALTER TABLE [테이블명] DROP CONSTRAINT [FOREIGN KEY명]

1:1 관계

하나의 레코드가 다른 테이블의 레코드 한 개와 연결된 경우

위 그림에서 User 테이블의 phone_id는 외래키(foreign key)로 Phonebook 테이벌의 phone_id와 연결되어 있다. 각 전화번호가 단 한 명의 유저와 연결되어 있고, 그 반대도 동일하면 User테이블과 Phonebook 테이블은 1:1 관계이다.

1:N 관계

하나의 레코드가 서로 다른 여러 개의 레코드와 연결된 경우

위 그림에서 한 명의 유자는 여러 전화번호를 가질 수 있지만, 하나의 전화번호는 여러 명의 유저를 가질 수 없다. 이럴때 1:N 관계라고 한다.

N:N 관계

여러 레코드가 다른 테이블의 여러 개의 레코드와 관계가 있는 경우. N:N 관계를 위해 스키마를 디자인 할 때는 Join테이블을 만들어서 관리한다.

customer_package 테이블에서는 고객 한 명이 여러 개의 여행 상품을 가질 수 있고, 여행 상품 하나를 여러 명의 고객이 가질 수 있다. customer_package 테이블은 customer_id와 package_id를 묶어주는 역할을 한다.

SQL 내장함수

링크를 참고해보자. 자주 사용되는 함수들은 꼭 기억해두자

GROUP BY

데이터를 조회할 때 동일한 값을 가진 행을 그룹으로 묶어서 조회한다.

SELECT column_name(s) FROM table_name
GROUP BY column_name(s)

HAVING

HAVING은 GROUP BY로 조회된 결과를 특정 조건에 맞게 필터링 한다.

SELECT column_name(s) FROM table_name
GROUP BY column_name(s)
HAVING condition

HAVING은 WHERE과는 적용방식이 다르다. HAVING는 그룹화한 결과에 대한 필터고, WHERE는 저장된 레코드를 필터링 한다.

집계함수 (COUNT(), MAX(), MIN(), SUM(), AVG())

집계함수들은 각각 레코드의 갯수(COUNT), 최대값(MAX), 최소값(MIN), 합계(SUM), 평균값(AVG)을 리턴한다.

SELECT column_name(s) COUNT(expression) FROM table_name
GROUP BY column_name(s)

SELECT column_name(s) MAX(expression) FROM table_name
GROUP BY column_name(s)

SELECT column_name(s) MIN(expression) FROM table_name
GROUP BY column_name(s)

SELECT column_name(s) SUM(expression) FROM table_name
GROUP BY column_name(s)

SELECT column_name(s) AVG(expression) FROM table_name
GROUP BY column_name(s)

SELECT 실행 순서

데이터를 조회하는 SELECT 문은 정해진 순서대로 동작한다.

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• ORDER BY

예시와 함께 살펴보자.

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) 필드를 기준으로 오름차순 정렬한 결과를 리턴