🎯 설계한 DB를 직접 생성해보고, 외래 키를 설정하여 테이블을 연결합니다.

📙 Today I Learned

DB 테이블 생성 실습

DB 생성을 위해 터미널을 통해 mariadb에 접속해줍니다.

docker exec -it mariadb /bin/bash

mariadb -u root -p

비밀번호 입력

1. Board 데이터베이스 생성 및 사용

-- 데이터베이스 생성
CREATE DATABASE Board;

-- 데이터베이스 사용
USE Board;

2. 테이블 생성 후 테이블 구조 확인

-- 테이블 생성
CREATE TABLE users 
(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(30) NOT NULL,
  password VARCHAR(30) NOT NULL,
  founding_date DATE,
  phone_num VARCHAR(11),
  PRIMARY KEY (id)
);

-- 테이블 구조 확인
DESC users;

3. 사용자 데이터 삽입 후 조회

-- 사용자 데이터 삽입
INSERT INTO users (name, password, founding_date) 
VALUES ("민음사", 1234, "1966-05-19");

-- 테이블 모든 데이터 조회
SELECT * FROM users;

4. 게시글 테이블 생성

-- 게시글 테이블 생성
CREATE TABLE posts 
(
  id INT NOT NULL AUTO_INCREMENT,
  author VARCHAR(100) NOT NULL,
  subscriber INT,
  created_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (id)  
);

-- 게시글 테이블 구조 확인
DESC posts;

5. 게시글 데이터 삽입 후 조회

-- 게시글 테이블 데이터 삽입
INSERT INTO posts (author, subscriber) 
VALUES ("김훈", 35764);

-- 테이블 모든 데이터 조회
SELECT * FROM posts;

6. 게시글 테이블에 수정일자 추가 후 조회

-- 게시글 테이블에 수정일자 추가
ALTER TABLE posts ADD COLUMN updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
ON UPDATE CURRENT_TIMESTAMP;

-- 게시글 테이블 전체 조회
SELECT * FROM posts;

7. 게시글 테이블 수정

-- 게시글 테이블 작가 이름 수정
UPDATE posts  
SET author = "정대건"  
WHERE id = 1;

-- 게시글 테이블 전체 조회
SELECT * FROM posts;

8. 게시글 테이블에 사용자 외래 키(FK) 추가 및 설정

-- 게시글 테이블에 user_id 칼럼 추가
ALTER TABLE posts 
ADD COLUMN user_id INT;

-- user_id에 외래 키(FK) 설정
ALTER TABLE posts
ADD FOREIGN KEY(user_id) 
REFERENCES users(id);

-- 게시글 테이블 전체 데이터 확인
SELECT * FROM posts;

-- 테이블 구조 확인
DESC posts;

🤔 NOT NULL과 Default의 차이는 무엇일까?

NOT NULL

• 해당 칼럼에 반드시 값이 있어야 한다는 것을 나타냅니다.
• NULL 값을 직접 넣는 것도 불가능합니다.
  
  

Default

• 값이 들어오지 않을 경우, 미리 설정된 기본값으로 자동 입력합니다.
• NULL을 직접 입력하면 NULL이 저장됩니다.

9. 연결을 위해 user_id 삽입

INSERT INTO posts (author, user_id) 
VALUES ("최재영", 1);

10. 조인으로 두 테이블을 연결하여 조회

SELECT posts.id, author, subscriber, created_at, updated_at, name, password, founding_date, phone_num 
FROM posts 
LEFT JOIN users ON posts.user_id = users.id;

✨ 추가로 알면 좋은 지식! auto_increment_lock_mode

트랜잭션 롤백이나 DELETE 후 INSERT한 경우 AUTO_INCREMENT 값이 건너뛰는 현상이 발생할 수 있습니다.

+----+---------+
| id | name    |
+----+---------+
|  1 | 민음사   |
|  2 | 문학동네 |
|  4 | 김영사   |  -- 🚨 3을 건너뛰고 4가 됨!
|  5 | 북이십일 |      
+----+---------+

SET GLOBAL innodb_autoinc_lock_mode = 0;

이를 방지하기 위해 위와 같은 명령어를 사용하여 잘못된 INSERT 문이 id 값에 영향을 미치지 않도록 제어할 수 있습니다.

---

MySQL (mariadb) 날짜 / 시간 타입

• DATE

  • 날짜만 저장합니다.
  • 형식: YYYY-MM-DD
    
    

• DATETIME

  • 날짜와 시간을 저장합니다.
  • 형식: YYYY-MM-DD HH:MM:SS
    
    

• TIME

  • 시간만 저장합니다.
  • 형식: HH:MM:SS
    
    

• TIMESTAMP

  • 날짜와 시간을 저장합니다.

  • 형식: YYYY-MM-DD HH:MM:SS

  • ✨ 시스템 시간대 정보에 맞게 자동 저장합니다.

  • UTC 기준으로 데이터를 저장한 후, 조회할 때 시스템 시간대(한국 시간 등)에 맞게 변환하여 출력합니다.

    TIMESTAMP 시간대 저장 방법

    UTC (Coordinated Universal Time) : 세계 표준 시간
    KST (Korea Standard Time) : 한국 표준 시간 ( UTC + 9시간 )

    ex. 오후 3시(KST) 데이터를 저장한다면 UTC 기준으로는 오전 6시로 저장됩니다.

---

✏️ 한 줄 회고

관계형 데이터베이스에서 데이터를 독립적인 테이블로 분리하고, 다시 외래 키와 조인을 활용해 연결하는 과정을 직접 실습하면서 데이터 간의 연관성을 설계하는 것이 얼마나 중요한지 깨달았습니다.