관계형 데이터베이스

인자약·2023년 11월 13일

nosql query design schema sql

DB

목록 보기

1/1

📕 SQL

✏️ SQL 소개

📌 데이터베이스의 필요성

1. In Memory
Java에서 변수를 만들어 저장한 경우, 프로그램이 종료될 때 해당 프로그램이 사용하던 데이터도 사라짐.

2. File I/O
파일을 읽는 방식으로 작동하는 형태.

데이터가 필요할 때마다 전체 파일을 매번 읽어야 한다. 파일의 크기가 커질수록 이 작업은 버겁고, 비효율적이어서 File I/O 방식의 큰 단점.
파일이 손상되거나 여러 개의 파일들을 동시에 다뤄야 하거나 하는 등 복잡하고 데이터량이 많아질수록 데이터를 불러들이는 작업이 점점 힘들어진다.

📌 SQL 소개

하나의 언어인 Structured Query Language (SQL)은 데이터베이스 언어로, 주로 관계형 데이터베이스에서 사용. 예를 들어 MySQL, Oracle, SQLite, PostgreSQL 등 다양한 데이터베이스에서 SQL 구문을 사용.

SQL은 구조화된 쿼리 언어.

SQL이란 데이터베이스 용 프로그래밍 언어. 데이터베이스에 쿼리를 보내 원하는 데이터를 가져오거나 삽입할 수 있다. 이름에서 유추할 수 있듯이, SQL은 (relation 이라고도 불리는) 데이터가 구조화된(structured) 테이블을 사용하는 데이터베이스에서 활용할 수 있다. SQL을 사용하기 위해서는 데이터가 구조가 고정되어 있어야 한다.

SQL을 사용할 수 있는 데이터베이스와 달리, 데이터의 구조가 고정되어 있지 않은 데이터베이스를 NoSQL. 관계형 데이터베이스와는 달리, 테이블을 사용하지 않고 데이터를 다른 형태로 저장. NoSQL의 대표적인 예시는 MongoDB 와 같은 문서 지향 데이터베이스.

✏️ SQL Basics

📌 기본 쿼리문

SQL을 사용에 필요한 기본 문법
Select
Where
And, Or, Not
Order By
Insert Into
Null Values
Update
Delete
Count
Like
Wildcards
Aliases
Joins
Inner Join
Left Join
Right Join
Group By

데이터베이스 관련 용어
SQL Create DB
SQL Drop DB
SQL Create Table
SQL Drop Table
SQL Alter Table
SQL Not Null
SQL Unique
SQL Primary Key
SQL Foreign Key
SQL Default
SQL Auto Increment
SQL Dates

📌 데이터베이스 관련 명령어

데이터베이스 생성

CREATE DATABASE 데이터베이스_이름;

데이터베이스 사용
데이터베이스를 이용해 테이블을 만들거나 수정하거나 삭제하는 등의 작업을 하려면, 먼저 데이터베이스를 사용하겠다는 명령을 전달.

USE 데이터베이스_이름;

테이블 생성
USE를 이용해 데이터베이스를 선택했다면, 이제 테이블을 만들 수 있다.

CREATE TABLE user (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(255),
  email varchar(255)
);

테이블 정보 확인

DESCRIBE user;

📌 SQL 명령어

SELECT

FROM

WHERE

WHERE 특성 = "특정 값" (동일한 데이터 찾기)
<> - 특정 값을 제외한 값을 찾기
특정 값보다 크거나 작은 데이터를 필터할 때에는 '<', '>', 
비교하는 값을 포함하는 '이상', '이하' 값은 '<=', '>='을 사용
LIKE "%특정 문자열%" - (문자열에서 특정 값과 비슷한 값들을 필터할 때에는 'LIKE'와 '\%' 혹은 '\*'를 사용.)
리스트의 값들과 일치하는 데이터를 필터할 때에는 'IN'을 사용.
IS NULL - 값이 없는 경우 'NULL'을 찾을 때에는 'IS'와 같이 사용.
IS NOT NULL - 값이 없는 경우를 제외할 때에는 'NOT'을 추가해 이용.

ORDER BY
기본정렬은 오름차순
ORDER BY 특성 DESC - 내림차순

LIMIT
가장 마지막에 추가

DISTINCT
유니크한 값을 받고 싶을 때에는 SELECT DISTINCT

SELECT DISTINCT 특성
FROM 테이블 이름

INNER JOIN
둘 이상의 테이블을 서로 공통된 부분을 기준으로 연결

SELECT *
FROM 테이블_1
JOIN 테이블_2 ON 테이블_1.특성_A = 테이블_2.특성_B

OUTER JOIN

SELECT *
FROM 테이블_1
LEFT OUTER JOIN 테이블_2 ON 테이블_1.특성_A = 테이블_2.특성_B

SELECT *
FROM 테이블_1
RIGHT OUTER JOIN 테이블_2 ON 테이블_1.특성_A = 테이블_2.특성_B

✏️ ACID

📌 트랜잭션

트랜잭션이란 여러 개의 작업을 하나로 묶은 실행 유닛. 각 트랜잭션은 하나의 특정 작업으로 시작을 해 묶여 있는 모든 작업들을 다 완료해야 정상적으로 종료. 데이터베이스 트랜잭션은 ACID라는 특성을 가지고 있다.

📌 ACID

Atomicity(원자성)
원자성은 하나의 트랜잭션에 속해있는 모든 작업이 전부 성공하거나 전부 실패해서 결과를 예측할 수 있어야 한다.

Consistency(일관성)
하나의 트랜잭션 이전과 이후, 데이터베이스의 상태는 이전과 같이 유효해야 한다. 다시 말해, 트랜잭션이 일어난 이후의 데이터베이스는 데이터베이스의 제약이나 규칙을 만족해야 한다는 뜻.

Isolation(격리성, 고립성)
모든 트랜잭션은 다른 트랜잭션으로부터 독립되어야 한다 는 뜻

Durablility(지속성)
하나의 트랜잭션이 성공적으로 수행되었다면, 해당 트랜잭션에 대한 로그가 남아야 한다. 만약 런타임 오류나 시스템 오류가 발생하더라도, 해당 기록은 영구적이어야 한다는 뜻.

✏️ SQL vs NoSQL

📌 SQL(구조화 쿼리 언어) vs NoSQL(비구조화 퀴리 언어)

관계형 데이터베이스는 SQL을 기반으로 하고, 비관계형 데이터베이스는 NoSQL로 데이터를 다룬다. SQL과 NoSQL은 만들어진 방식, 저장하는 정보의 종류, 그리고 저장하는 방법 등에 차이가 있다.

관계형 데이터베이스에서는 테이블의 구조와 데이터 타입 등을 사전에 정의하고, 테이블에 정의된 내용에 알맞은 형태의 데이터만 삽입할 수 있다. 관계형 데이터베이스는 행(row)과 열(column)로 구성된 테이블에 데이터를 저장. 각 열은 하나의 속성에 대한 정보를 저장하고, 행에는 각 열의 데이터 형식에 맞는 데이터가 저장. 특정한 형식을 지키기 때문에, 데이터를 정확히 입력했다면 데이터를 사용할 때에는 매우 수월. 관계형 데이터베이스에서는 SQL을 활용해 원하는 정보를 쿼리할 수 있다. 이 말은 관계형 데이터베이스에서는 스키마가 뚜렷하게 보인다는 말과 동일. 다시 말해, 관계형 데이터베이스에서는 테이블 간의 관계를 직관적으로 파악.

대표적인 관계형 데이터베이스는 MySQL, Oracle, SQLite, PostgresSQL, MariaDB 등.

NoSQL은 SQL 앞에 붙은 'No'에서 알 수 있듯이, 주로 데이터가 고정되어 있지 않은 데이터베이스를 가리킨다. NoSQL이 SQL과 반대되는 개념처럼 사용된다고 해서, NoSQL에 스키마가 반드시 없는 것은 아니다. 관계형 데이터베이스에서는 데이터를 입력할 때 스키마에 맞게 입력해야 하는 반면, NoSQL에서는 데이터를 읽어올 때 스키마에 따라 데이터를 읽어 온다. 이런 방식을 'schema on read'라고도 한다. 읽어올 때에만 데이터 스키마가 사용된다고 하여, 데이터를 쓸 때 정해진 방식이 없다는 의미는 아니다. 데이터를 입력하는 방식에 따라, 데이터를 읽어올 때 영향을 미친다.

대표적인 NoSQL은 몽고DB, Casandra 등.

📕 Learn SQL

✏️ 실습하기

퀴즈 링크
Practice 링크

✏️ 시작하기 - Mac

📌 1. MySQL 설치

macOS
Homebrew를 이용한 설치

brew install mysql
brew info mysql

📌 2. MySQL 서비스 시작

brew services start mysql

📌 3. MySQL 접속

mysql -u root -p

📕 설계

✏️ 데이터베이스 설계

📌 관계형 데이터베이스

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

데이터(data): 각 항목에 저장되는 값.
테이블(table; 또는 relation) : 사전에 정의된 열의 데이터 타입대로 작성된 데이터가 행으로 축적.
열(column; 또는 field) : 테이블의 한 열.
레코드(record; 또는 tuple) : 테이블의 한 행에 저장된 데이터.
키(key) : 테이블의 각 레코드를 구분할 수 있는 값. 각 레코드마다 고유한 값을 가진다. 기본키(primary key)와 외래키(foreign key) 등이 있다.

📕 관계 종류

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

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

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

이 구조에서는 한 명의 유저가 여러 전화번호를 가질 수 있다. 그러나 여러 명의 유저가 하나의 전화번호를 가질 수는 없다.

N:N 관계

여러 개의 여행 상품이 있고, 여러 명의 고객이 있다. 고객 한 명은 여러 개의 여행 상품을 구매할 수 있고, 여행 상품 하나는 여러 명의 고객이 구매할 수 있다. 이렇게 다대다 관계를 위한 테이블을 조인 테이블이라고 한다. N:N(다대다) 관계를 그림으로 나타내면 다음과 같다.

self referencing 관계

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

✏️ SQL More

📌 SQL 내장함수

GROUP BY
데이터를 조회할 때 그룹으로 묶어서 조회

SELECT * FROM customers
GROUP BY State;

HAVING
HAVING은 GROUP BY로 조회된 결과를 필터링

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

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

COUNT()
COUNT 함수는 레코드의 개수를 헤아릴 때 사용

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

SUM()

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

AVG()

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

MAX(),MIN()

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

📕 Designing Schema

✏️ Instagram 스키마 디자인

// 쿼리문
CREATE TABLE `users` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(50),
  `password` varchar(50)
);

CREATE TABLE `posts` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `message` text,
  `created_at` datetime DEFAULT (CURRENT_TIMESTAMP),
  `total_likes` INT DEFAULT 0,
  `total_comments` INT DEFAULT 0,
  `user_id` INT
);

CREATE TABLE `post_images` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `image` blob,
  `post_id` INT
);

CREATE TABLE `post_comments` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `comment` varchar(255),
  `created_at` datetime DEFAULT (CURRENT_TIMESTAMP),
  `user_id` INT,
  `post_id` INT
);

CREATE TABLE `post_likes` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `created_at` datetime DEFAULT (CURRENT_TIMESTAMP),
  `user_id` INT,
  `post_id` INT
);

CREATE TABLE `follow_follower` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `follower_id` INT,
  `user_id` INT
);

CREATE TABLE `posts_hashtags` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `hashtag_id` INT,
  `post_id` INT
);

CREATE TABLE `hashtags` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` char(50)
);

ALTER TABLE `post_images` ADD FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`);

ALTER TABLE `posts` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `follow_follower` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `post_comments` ADD FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`);

ALTER TABLE `post_comments` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `post_likes` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `post_likes` ADD FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`);

ALTER TABLE `posts_hashtags` ADD FOREIGN KEY (`hashtag_id`) REFERENCES `hashtags` (`id`);

ALTER TABLE `posts_hashtags` ADD FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`);

ALTER TABLE `follow_follower` ADD FOREIGN KEY (`follower_id`) REFERENCES `users` (`id`);

📕 Learn Schema / SQL

깃허브 링크

✏️ Part 1 - 데이터베이스 연결과 스키마

✏️ Part 2 - 스키마와 함께하는 기본 SQL

✏️ Part 3 - 추가 요구조건과 스키마

인자약

인자약velog