[Database] 데이터베이스 개요, 데이터모델링, SQL 기본문법(supabase, postgreSQL)

1. 데이터베이스 개요

데이터베이스란?

데이터베이스 DBMS, Database (Management System)

데이터 모음을 보다 편리하고 효율적으로 관리하는 소프트웨어

 

데이터베이스의 필요성

• 데이터를 파일로 관리하면 중복, 무결성 유지, 동시 접근에 문제가 생기므로 체계적인 관리가 어렵다.
• 데이터베이스는 데이터를 중앙에서 통합 관리하고, 여러 사용자가 동시에 정확하게 접근할 수 있게 해준다.
• 또한 검색, 수정, 보안 등의 기능을 효율적으로 제공하여 대규모 데이터 처리와 유지보수에 필수적이다.

데이터베이스의 종류

• 관계형데이터베이스
  • 구조화된 테이블 형식
  • 강력한 데이터 무결성
  • 복잡한 쿼리에 유리
  • ex) 오라클, Mysql, SQLite, SQL server, PostgresSql
• 비관계형데이터베이스
  • 유연한 데이터 모델: 고정된 스키마가 없음
  • 수평적 확장: 더큰 서버로 업그레이드하는 대신 여러대의 서버를 추가하여 데이터베이스의 성능을 높임
  • 대규모 데이터 처리에 유리: 대량의 비정형데이터를 빠르게 저장하고 검색할수있도록 최적화되어있음
  • ex) mongoDB, cassandra, neo4J, Redis

항목	MongoDB	Cassandra	Neo4j	Redis
분류	Document Store	Wide Column Store	Graph DB	In-memory Key-Value Store
주요 특징	유연한 스키마, JSON형	고가용성, 수평확장	관계 중심, Cypher 쿼리	초고속 처리, 다양한 구조
사용 예시	Adobe, eBay, LinkedIn	Netflix, Instagram, Spotify	LinkedIn, NASA, eBay	Twitter, GitHub, StackOverflow

관계형 데이터베이스를 먼저 쓰고, nosql을 쓸 이유가 분명히 있을때 그때 nosql 사용하기!

데이터 모델링

데이터 모델링 개요

데이터 모델링 Data Modeling

현실 세계의 요구사항을 데이터의 관계로 표현하는 과정

데이터모델링 과정

요구사항 분석 → 개념적 데이터 모델링 → 논리적 데이터 모델링 → 물리적 데이터 모델링

1. 요구사항 분석
   • 사용자의 비즈니스 요구사항을 파악합니다.
   • 어떤 데이터를 저장할지, 어떤 기능이 필요한지 결정
   • 예: “고객이 주문을 조회할 수 있어야 한다.”
2. 개념적 데이터 모델링
   • 비즈니스 개념을 엔터티(Entity), 속성(Attribute), 관계(Relationship)로 추상화
   • ERD(Entity Relationship Diagram)를 작성하여 개념 구조를 시각화
   • 예: 고객(Customer), 주문(Order), 상품(Product) 등의 엔터티 정의
3. 논리적 데이터 모델링
   • 개념 모델을 실제 DBMS에 맞춰 정규화된 구조로 구체화
   • 테이블, 컬럼, 기본 키, 외래 키 등을 설계
   • 예: 고객 테이블에 customer_id, name, email 컬럼 설계
4. 물리적 데이터 모델링
   • 논리 모델을 실제로 구현할 수 있도록 물리적 구조를 설계
   • 인덱스, 파티셔닝, 성능 고려, 데이터 타입 등을 결정
   • 예: customer_id는 INT AUTO_INCREMENT, name은 VARCHAR(100) 등으로 지정

엔터티와 속성

인스턴스: 엔터티의 구체적인 사례

속성: 엔터티의 특징, 성질

관계형 데이터베이스

관계형모델은 에드거 커드라는 컴퓨터과학자가 처음 소개했고, 피터첸이 관계형 모델을 발전시켜 엔터티모델(ER)을 소개했다.

1. 관계형 모델(Relational Model)
   • 에드거 F. 커드(Edgar F. Codd)가 1970년에 처음 제안한 데이터베이스 모델
   • 데이터를 행(Row)과 열(Column)로 구성된 테이블(Table) 형태로 표현
   • 수학의 관계(Relation) 개념에 기반하여 데이터를 구조화한다.
2. ER 모델(Entity-Relationship Model)
   • 피터 첸(Peter Chen)이 1976년에 관계형 모델을 기반으로 발전시킨 모델
   • 현실 세계의 개체(Entity)와 그들 간의 관계(Relationship)를 시각적으로 모델링
   • 개념적 모델링 단계에서 ER 다이어그램(ERD)으로 많이 활용됨

2. 실습환경 소개

1티어 아키텍쳐

1개의 컴퓨터안에서 클라이언트와 데이터베이스가 갖춰짐

클라이언트와 서버가 구분되지 않고, 하나의 컴퓨터/프로세스 내에서 UI, 로직, 데이터 처리를 모두 수행함

• 초심자가 SQL과 DBMS를 학습하기에 적합
• 구조가 간단하여 처음 시작하기 좋다.
• 컴퓨터가 망가지면 장애 대응이 불가하다.

Supabase

Supabase는 Firebase 대체 오픈소스 백엔드 플랫폼으로, 인증·데이터베이스·스토리지·리얼타임 기능을 제공한다. 내부적으로 PostgreSQL을 기반으로 하며, REST API와 실시간 구독(Realtime)을 자동 생성한다. 프론트엔드 개발자가 백엔드 구축 없이 바로 데이터 연결 가능하게 도와주는 도구이다.

supabase는 BaaS(Backend as a Service)라고 부르기도한다.

PostgreSQL은 오픈소스 관계형 데이터베이스(RDBMS)로, 안정성과 ACID 트랜잭션을 강점으로 가진다. SQL 표준을 94% 따르며, 복잡한 쿼리, 인덱스, 확장 기능(예: JSON, 배열, GIS) 등을 지원한다. 전 세계적으로 가장 널리 쓰이는 오픈소스 DB 중 하나로, Supabase, Django, Rails 등에서 자주 사용된다.

3. SQL기초문법

SQL이란?

• SQL이란 ?
• Structured Query Language(구조화된 질의 언어)의 약자로, 관계형 데이터베이스(RDBMS)에서 데이터를 정의, 조회, 조작, 제어하기 위해 사용된다. Oracle, MySQL, PostgreSQL, SQLite, SQL Server 등 다양한 DBMS에서 사용된다.
• SQL 구문의 특징
  • 대소문자를 구분하지 않음 (관례적으로는 대문자 사용)
  • 세미콜론(;)으로 구문을 종료
    • 한 SQL 문장의 끝을 명확히 구분하기 위해 ;를 사용
  • 문장 순서가 중요함
    • 예: SELECT 다음에 FROM, 그 다음에 WHERE이 와야 합니다. 순서를 바꾸면 오류 발생
  • 영어 문법과 유사한 구조
    • SELECT name FROM users WHERE age > 20; 처럼 읽기 쉬운 구조를 가짐
  • 명확하고 선언적인 언어
    • 무엇을 할지를 선언하며, 어떻게 수행할지는 DBMS가 처리

SQL 종류

분류	풀네임	설명	주요 명령어
DDL	Data Definition Language 데이터 정의어	데이터 구조를 정의하기 위한 언어	CREATE, DROP, ALTER, ...
DML	Data Manipulation Language 데이터 조작어	데이터를 조회하고 조작하기 위한 언어	SELECT, INSERT, UPDATE, DELETE, ...
DCL	Data Control Language 데이터 제어어	데이터베이스 접근 권한 및 회수를 위한 언어	GRANT, REVOKE, ...
TCL	Transaction Control Language 트랜잭션 제어어	트랜잭션 단위로 작업을 제어하기 위한 언어	COMMIT, ROLLBACK, END, ...

SQL 기본문법

• 쿼리문을 다 작성하고 나서 세미콜론(;)을 꼭 작성
• 대소문자를 구별하지는 않지만. 키워드는 대문자, 식별자는 소문자, snake_case로 작성하는것이 관례
• 데이터베이스 생성 및 삭제

CREATE DATABASE my_database;
DROP DATABASE my_database;

• 테이블생성

CREATE TABLE accommodation (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address TEXT NOT NULL,
  city VARCHAR(100) NOT NULL,
  country VARCHAR(100) NOT NULL,
  rating DECIMAL(3, 2),
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• 테이블삭제

DROP TABLE accommodation
DROP TABLE IF EXISTS accommodation

데이터타입

문자열 타입

postgreSQL 16버전 공식문서에 따르면 문자열 3가지 타입간 성능차이는 없음

Boolean 타입

CREATE TABLE users (
...
cookie_agreement BOOLEAN DEFAULT FALSE
);

UPDATE users
SET cookie_agreement = TRUE, updated_at = CURRENT_TIMESTAMP
WHERE user_id = 1;

Enum 타입

숫자타입

serial은 autoincrement 할수있어 Id값으로 많이 쓰임

DateTime

timestamp(3)이 널리 쓰임

SQL 기초문법

SELECT INTO

INSERT INTO accommodation (
    name, address, city, country, phone_number, email, website, price_per_night, rating, description, total_visit, monthly_visit, created_at, updated_at
) VALUES 
('Sunrise Hotel', '123 Sunny St', 'Sunville', 'Sunnyland', '123-456-7890', 'contact@sunrisehotel.com', 'www.sunrisehotel.com', 120.00, 4.5, 'A beautiful hotel with a sunny disposition.', 500, 50, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

SELECT

-- 평점 4. 5점 이상
SELECT * FROM accommodation WHERE rating >= 4.5;

-- 평점 4.5점 이상 4.6 이하
SELECT * FROM accommodation WHERE rating BETWEEN 4.5 AND 4.6;

-- ID가 1, 3
SELECT * FROM accommodation WHERE id IN (1, 3);

-- ID가 1, 3이 아님
SELECT * FROM accommodation WHERE id NOT IN (1, 3);

-- 중복되지 않는 고유한 City 값을 선택
SELECT DISTINCT city FROM accommodation;

-- 전체 평점 평균
SELECT AVG(rating) as avg_rating FROM accommodation;

-- 내림차순(큰것부터)
SELECT * FROM accommodation ORDER BY rating DESC; 

-- 평점 상위 2개
SELECT * FRMO accommodation ORDER BY rating DESC LIMIT 2;

UPDATE

-- WHERE 조건에 해당하는 여러 로우의 값을 변경
-- 기존의 rating 값을 활용할 수 있음
UPDATE accommodation
SET rating= rating + 0.1, description ='서비스 개선'
WHERE city = 'Medellin' AND rating < 5.0;

데이터수정은 주의가 필요하다.

SELECT *
FROM accommodation
WHERE city = 'Chiang Mai';

데이터 수정 전, 어떤로우가 영향을 미칠지 먼저 파악한다.

UPDATE accommodation
SET rating = rating + 0.1
WHERE city = 'Chiang Mai'
RETURNING *;

RETURNING * 구문으로 영향받는 로우를 바로 조회해 본다.

실제로 데이터를 업데이트한 후, 영향받은 행들을 반환한다.

미리 백업작업을 해놓는다.

덤프를 따놓거나 DB를 제공하는 클라우드의 기능을 활용한다.

UPDATE문 UPDATE 테이블명 SET [컬럼명 = 변경값, ·•·] WHERE 조건

변경 가능 범위	하나의 로우, 하나의 값 하나의 로우, 여러개의 값 여러개의 로우, 여러개의 값
WHERE 주의점	WHERE를 빼먹는 경우
WHERE 조건이 잘못된 경우
UPDATE 할 때
꼭 지키기	SELECT로 먼저 영향받는 로우 확인
RETURNING으로 실제 영향받은 로우 확인 중요한 DB의 경우 백업을 미리 진행 애매하면 무조건 시니어, 선임과 상의하기

DELETE

-- id가 1인 로우 삭제
DELETE FROM accommodation
WHERE id = 1;

-- 평점이 4. 8보다 낮은 로우 삭제
DELETE FROM accommodation
WHERE rating < 4.8;

SELECT로 먼저 영향받는 로우 확인

RETURNING으로 실제 영향받은 로우 확인

중요한 DB의 경우 백업을 미리 진행

애매하면 무조건 시니어, 선임과 상의하기