[pre_onboarding] Database & SQL 세션

1. What Is Database?

• 데이터베이스는 컴퓨터 시스템에 저장된 정보나 데이터를 모두 모아 놓은 집합을 의미.
• 하드 디스크에 파일 형태로 저장하면 메모리상에 저장하는것과는 다르게 데이터의 영구적 보존은 가능하지만, 단점은 편리하고 효과적인 데이터 관리및 접근이 어렵다.
• 이러한 문제들을 해결하기 위해 생겨난 시스템이 바로 우리가 일반적으로 database라고 불르는 DBMS (DataBase Management System).

2. RDBMS (Relational Database Management System)

• 데이터베이스시스템 중 가장 일반적이고 기본이 되는 데이터베이스시스템은 RDBMS, 즉 관계형 데이터베이스시스템.
• 관계형 데이터베이스에서 모든 데이터는 2차원 테이블(table)로 표현된다.
  • 모든 데이터들은 2차원 테이블(table)들로 표현 된다.
  • 각각의 테이블은 컬럼(column)과 row(로우)로 구성된다.
    • 컬럼은 테이블의 각 항목을 말한다. 행으로 생각하면 된다.
    • 로우는 각 항목들의 실제 값들을 이야기 한다. 열로 생각하면 된다.
• 각 로우는 저만의 고유 키(Primary Key)가 있다.
  • 일반적으로 테이블의 가장 첫 컬럼은 id 이다. 엑셀이나 스프레드 시트에도 제일 첫 컬럼은 언제나 변하지 않는 번호(index)가 매겨져 있는 것처럼, 각 로우는 언제나 고유한 번호를 갖고 있다. 이를 고유 키(Primary Key)라고 한다. 이 Primary Key를 통해서 특정 로우를 찾거나, 인용(reference)할 수 있다.

🦖 테이블 사이 관계의 종류

RDBMS에서는 테이블끼리의 관계를 맺을 수 있는 유형은 다음 3가지중 하나:

• One To One
• One To Many
• Many To Many

🦖 어떻게 테이블과 테이블을 연결하는가?

• 📍Foreign key(외부키)📍라는 개념을 사용하여 주로 연결한다.
• 앞서 본 one to one 예에서 user_profiles 테이블의 user_id 컬럼은 users 테이블에 걸려있는 외부 키라고 지정한다.
• 즉 데이터베이스에게 user_id의 값은 users 테이블의 id 값이며 그러므로 users 테이블의 id 컬럼에 존재하는 값만 생성될 수 있다.
• 만일 users 테이블에 없는 id 값이 user_id 에 지정되면 에러가 발생한다.
  • Foreign key로 걸려 있는 값이 존재 하지 않을때 에러가 발생 하는 성질은 굉장히 중요하다.
  • 이러한 성질을 이용하여 데이터의 무결성을 보장하는것을 정규화(normalization)라고 한다. 🥝🥝🥝🥝
  • 중복된 데이터를 피하면서 동시에 잘못된 데이터가 입력되는 것을 막을 수 있다.

3. Data Types

기본적으로 대부분의 RDBMS는 다음과 같은 데이터 타입을 가지고 있다:

• Numeric
  • Int, tinyint, bigint, float
• Date / Time
  • Timestamp, Date, DateTime
• Character / String
  • Char , Varchar, Text
• Binary
  • Binary
• Misc
  • JSON , Spatial , Enum

🦖 데이터에 적합한 데이터 타입을 사용해야 한다.

• 예를 들어, 현실적으로 120살 이상 사는 사람이 없기 때문에 age 컬럼에는 INT 보다는 TINYINT가 더 적합할것이다.

• 댓글 같은 comment는 대부분 제한된 글자수만 허용하기 때문에 TEXT 보다는 VARCHAR 가 더 적합할 것이다. 예를 들어, 50자 글자 제한 댓글이라면 VARCHAR(50)

• UNSINGED 를 적절히 사용하면 좋다.
  • View count 같은 조회수 수 유형의 값도 UNSIGNED 를 사용하도록 하자.
  • 앞서 예를 들은, age 컬럼의 경우도 UNSIGNED TINYINT 를 사용하면 혹시나 130살까지 사는 사람이 나와도 핸들링 할 수 있다.

• VARCHAR 를 적절히 잘 사용하자.
  • CHAR 는 실제 값 사이즈와 상관없이 고정된 사이즈를 차지한다. 예를 들어 CHAR(100) 라고 한다면, 📍실제 데이터는 "위코드"와 같이 3글자라고 하더라도 데이터베이스상에는 100글자 만큼의 사이즈를 차지하게 된다.📍
  • 반대로 VARCHAR 는 📍실제 데이터 사이즈만큼만 차지한다.📍 VARCHAR(100) 의 경우 "위코드" 라는 값을 저장하면 3글자 만큼의 공간만 사용하게 되는것이다.
  • 대신 CHAR 이 VARCHAR 보다 생성과 읽기가 약간 빠르다. 하여, 데이터값이 항상 고정이라면 CHAR 을 사용하고 인풋에 따라 변한다면 VARCHAR 을 사용하는것이 효율적이다 (대부분 인풋에 따라 변하는 데이터가 일반적일 것이다).

• DATETIME VS TIMESTAMP
  • 어떤 이벤트가 일어난 시점을 기록하는 목적이라면 TIMESTAMP 를 사용하자.
    • created_at 처럼 해당 로우가 생성된 시점
    • updated_at 처럼 해당 로우가 수정된 시점
  • 과거나 미래의 날짜나 일정을 기록하는 목적이라면 DATETIME 이나 DATE 사용하자.
    • 생일 정보라면 DATE
    • 식당의 예약기록 정보라면 DATETIME

• DECIMAL VS FLOAT
  • FLOAT 은 라운딩 에러가 날 수 있다.
  • 그러므로 금액 같은 정확한 값이 필요할때는 DECIMAL 을 사용하자.
  • DECIMAL(5, 2) → -999.99 ~ 999.99

• ENUM 타입에 관하여

  CREATE TABLE shirts (
      name VARCHAR(40),
      size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
  );
  INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
    ('polo shirt','small');
  SELECT name, size FROM shirts WHERE size = 'medium';
  +---------+--------+
  | name    | size   |
  +---------+--------+
  | t-shirt | medium |
  +---------+--------+
  UPDATE shirts SET size = 'small' WHERE size = 'large';
  COMMIT;

  • ENUM 타입은 굉장히 유용하다고 생각될 수 있다.
  • 하지만 의외로 단점이 있기 때문에 실제로는 조심해서 사용해야 한다.
  • 여러가지 단점이 있는데, 그 중 가장 큰 단점은 이미 한번 정해진 enum 값을 변경하는것은 굉장히 비용이 큰 작업이 된다. 그래서 enum 값들이 정해지면 변경되지 않는다는 가정하에 사용해야 하는데 실제로는 변경이 될 확률이 많이 높다.
  • ENUM 보다는 one to one 관계의 normalization이 추천된다.

• SET 타입에 관하여

  • SET 을 사용하면 정해진 값들의 리스트를 저장할 수 있다. 자료구조 SET과 비슷하지만 저장할 수 있는 값들이 미리 정해져야 한다는 차이점이 있다.

    CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
    INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
    
    SELECT col FROM myset;
    +------+
    | col  |
    +------+
    | a,d  |
    | a,d  |
    | a,d  |
    | a,d  |
    | a,d  |
    +------+

  • ENUM 과 비슷하게 SET 도 단점이 많다. 64개 값 이상 저장할 수 없고, 값에 comma가 들어갈 수 없는 둥 제한이 많기 때문에 SET 보다는 one to many normalization을 사용하는것이 추천된다 (리스트를 표한하기 위해서 one to many normalization이 많이 사용된다.

4. ACID

• RDBMS는 transaction 기능을 보장할 수 있도록, 소위 ACID라고 하는 다음 4가지 성질을 제공한다.
• 원자성(Atomicity)은 트랜잭션과 관련된 작업들이 부분적으로 실행되다가 중단되지 않는 것을 보장하는 능력이다. 📍모 아니면 도.📍즉, 트랜잭션 작업들이 데이터베이스에 모두 반영되던가 아니면 아예 반영이 안된다는 뜻
• 일관성(Consistency)은 트랜잭션이 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 유지하는 것을 의미한다. → 즉, col1 이라는 컬럼은 INT값을 넣어야 하는 컬럼이라면, 트랜잭션 과정에도 마찬가지로 INT로 적용된다는 것.
• 고립성(Isolation)은 트랜잭션을 수행 시 다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장하는 것을 의미한다.여러 트랜잭션이 진행될때 하나의 트랜잭션이 완료되기 전에는 다른 트랜잭션들이 결과값을 읽거나 영향을 끼칠 수 없다는 뜻
• 지속성(Durability)은 성공적으로 수행된 트랜잭션은 영원히 반영되어야 함을 의미한다. 트랜잭션은 로그에 모든 것이 📍저장된 후에만📍 commit 상태로 간주될 수 있다. → 즉, 트랜잭션이 성공적으로 수행되면 그 결과는 혹시 데이터베이스에 장애가 나도 저장이 된다는 뜻. 예를 들어, MySQL은 트랜잭션 결과를 로그로 하드 디스크에 저장해서 혹시나 트랜잭션 결과 커밋 과정에서 데이터베이스가 다운되도, 로그를 통해 트랜스액션 기록을 읽어들여 커밋 결과를 다시 적용시킨다

5. Transaction

• ACID를 제공함으로 따라서 트랜잭션(일련의 작업들을 한번에 하나의 unit으로 실행하는것) 기능을 제공한다.
• DB의 여러 operation을 하나의 단독 UNIT으로 묶어 작업한다고 생각하면 될 듯. 📍 ex. table A를 생성한 후 table B와 연결하다가 중간에 멈춤. 이럴 때 table A를 생성 -> table B 연결을 하나로 묶는 것 자체가 Transaction.📍
• 일련의 작업들이 마치 하나의 작업처럼 취급되어서 모두 다 성공하거나 아니면 모두 다 실패하게 된다.
• Commit & rollback
  • Transaction 상의 모든 작업이 문제 없이 성공하면 commit 된다. 즉, 데이터베이스에 실제로 반영된다.
  • 만일 transaction 진행 중 문제가 생긴다면 rollback 한다. 즉, transaction 과정중 진행된 작업은 데이터베이스에 반영이 되지 않고 transaction이 시작하기 전의 상태로 돌아간다.

6. Basic SQL

• Union 과 Union all의 차이? Union은 중복된 값 return 안해줌. Union all은 중복된 값도 다 가져온다는 것 !
• EXISTS: 존재해? OK -> 그럼 찾았으니까 쿼리 멈춤.

SELECT EXISTS (SELECT id FROM table1 WHERE age >= 70)