2024년 9월 12일

✏️ DB 테이블 생성 실습

: CLI로 테이블을 만들어 볼 것

1. 스키마 생성(Board)

CREATE DATABASE Board;
USE Board;
SHOW DATABASES;

2. 사용자 테이블 생성 (users)

: 테이블은 기본적으로 복수형으로 작성_현재는 user들이 들어가 있으므로 users 그리고 객체지향을 의미)

CREATE TABLE users(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    job VARCHAR(100),
    birth DATE,
    PRIMARY KEY (id)
);
DESC users;

• 뒤에 제약 조건(NOT NULLNULL이면 안됨, AUTO_INCREMENT자동증가)를 쓰고, PK도 설정
• DATE 자료형은 기본적으로 형태가 'YYYY-MM-DD'으로 작성 되어 있음
• 따로 적어서 복붙 하는게 실수를 줄일 수 있음
• DESC : Describe
  

3. 게시글 테이블 생성 (posts)

CREATE TABLE posts(
	id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    content VARCHAR(2000),
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (id)
);
DESC posts;

• created_at : 작성일자 / updated_at : 수정일자 (보편적으로 작성되는 용어)
• TIMESTAMP : 자동으로 기록됨(현재 시간 도장 찍음)
• TIMESTAMP의 방법은 여러 가지가 있음 -> DEFAULT NOW() : 생성되면서 찍힘
• updated_at은 나중에 만들기로

4. 사용자 테이블 데이터 삽입

INSERT INTO users(name, job, birth)
VALUES("user1", "actor", "1980-01-01");
SELECT * FROM posts;

• id는 AUTO-CREMENT 했으니 알아서 DB가 증가해서 넣음
• 따옴표는 복붙이 안됨
• DATE를 명확하게 형태에 맞게 넣는 것이 중요(1900년대부터 자동으로 입력됨 -> "1930-01-02")

사용자 2명 더 추가 해놓고,,

5. 게시글 테이블 데이터 삽입

INSERT INTO posts(title, content)
VALUES("Hello1", "My name is user1");
SELECT * FROM posts;

• created_at은 자동으로 날짜와 시간이 삽입됨 -> TIMESTAMP

💡 MySQL(mariadb) 날짜 / 시간 타입

• DATE : 날짜만, YYYY-MM-DD의 형식

• DATETIME : 날짜+시간, YYYY-MM-DD HH:MM:SS (24시간제)

• TIME : 시간만, HH:MM:SS

• TIMESTAMP : 날짜+시간, YYYY-MM-DD HH:MM:SS (24시간제), 시스템 시간대(UTC) 정보에 맞게 일시를 저장함 -> 자동 입력 도와줌
  *UTC : 국제표준시 (한국시간 - 9)

• TIMESTAMP vs DATETIME
  TIMESTAMP는 UNIX timestamp 를 사용. "1970-01-01 00:00:01 UTC" 부터 "2038-01-19 03:14:07 UTC" 에서만 사용
  DATETIME은 MySQL에 종속되어 있는 시간 타입을 사용. "9999-12-31 23:59:59"의 범위까지 사용.

• 보통 데이터베이스에 데이터를 저장할 때 (특정 날짜에 회원가입한 사용자들의 데이터를 뽑을 때)는 TIMESTAMP

• 참조 : https://velog.io/@jejualrock/TIMESTAMP-DATETIME#datetime-%EC%97%90%EC%84%9C%EB%8F%84-%EB%98%91%EA%B0%99%EC%9D%80-%EB%B0%A9%EC%8B%9D%EC%9C%BC%EB%A1%9C-%EA%B5%AC%ED%98%84%EC%9D%B4-%EA%B0%80%EB%8A%A5%ED%95%9C%EA%B0%80

*데이터베이스를 자동화 하는 것을 개발자는 아주 좋아함

💡 Not Null vs Default

• Not Null : 직접 null 이라고 작성해서 넣는 것은 안됨
• Default : 값이 안들어올 때, 기본값으로 셋팅해주는 값
  • 공란으로 insert 되면, 들어오는 값이 아무것도 없어서 default로 설정해둔 값이 insert됨
  • 직접 null 이라고 작성해서 넣으면, 값이 있는 것으로 인지해서 null이 셋팅됨

6. 게시글 테이블 수정

: upated_at(수정일자) 넣고, FK도 넣어보자

• updated_at 칼럼 추가

ALTER TABLE posts
ADD COLUMN updated_at DATETIME
DEFAULT NOW()
ON UPDATE NOW();

DESC posts;

updated_at 이 들어간 것을 확인할 수 있다.

그런데,

INSERT INTO posts (title, content)
VALUES("Hello2", "My name is user2");

SELECT * FROM posts;

하면,

id=1인 updated_at에는 컬럼을 추가한 시간이 삽입된 것을 볼 수 있고,
id=2의 created_at과 updated_at의 시간이 같은 것을 알 수 있다. (=생성된 시간으로 같음)

업데이트 되었는지 알 수 없으므로,

게시글 테이블 id=2를 수정해보자.

UPDATE posts
SET content = "updated!"
WHERE id = 2;

SELECT * FROM posts;

이제 created_at(생성 일자)와 updated_at(수정 일자)가 다른 것을 확인할 수 있다.

• FK 칼럼 추가

ALTER TABLE posts
ADD COLUMN user_id INT;

SELECT * FROM posts;

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

DESC posts;

user_id가 추가 된 것을 볼 수 있고, user_id의 Key에 Mul이 입력되어 있는 것을 알 수 있다(=FK 지정)

*Mul : Mul은 여러 행이 같은 값을 가질 수 있다는 뜻(=중복가능)
ex) 사용자 번호가 중복이 가능

• 게시글 삽입 다시 한 번

INSERT INTO posts (title, content, user_id)
VALUES("Hello1-2", "My pet is a dog", 5);

SELECT * FROM posts;

이런 결과 화면을 볼 수 있는데, 이는 FOREGIN KEY로 쓰고 싶은 users의 id가 없다는 뜻이다(=참조불가: 참조할게 없음) -> 따라서, users에 있는 id값을 적어야한다

• 다시 삽입 트라이

INSERT INTO posts (title, content, user_id)
VALUES("Hello1-2", "My pet is a dog", 3);

SELECT * FROM posts;

삽입 성공!

*나중엔 FOREGIN KEY도 Null이 안되게 만들어야겠지?!

💡 JOIN

: SELECT 할때만 보기 편하라고 분리된 테이블을 붙여서 다시 보여주는 것 (실제로 table을 생성하지 않고 가상으로 보여줌)

• 게시글 테이블을 기준으로, 사용자 테이블을 붙여서 출력
• 방향 JOIN : 방향에 붙인다는 의미
  ex) LEFT JOIN : select할 테이블을 왼쪽에 붙이고 join을 시킨다는 의미

SELECT * FROM posts LEFT
JOIN users ON posts.user_id = users.id;

• 사용자 번호(user_id, id)는 원래 있지 않았던 칼럼이었으니 게시글 테이블처럼 보여주기 위해서
  

SELECT posts.id, title, content, created_at, updated_at, name, job, birth
FROM posts LEFT
JOIN users ON posts.user_id = users.id;

❗️ 그런데, id가 7로 증가 된것을 볼 수 있다.

: 이 이유는 fail 되어도 increment 값이 하나씩 증가하기 때문이다 (아까 게시글 테이블에 데이터 삽입을 여러번 실패해서 id값이 자동으로 증가 되었다)

이 이유는 auto_increment_lock_mode 때문이라고 한다.

구글링 해보니,

https://velog.io/@inhwa1025/MySQL-autoincrement-locking-%EC%84%A4%EC%A0%95-t2dj0741

요런 방식을 취해주면 자동 증가가 되지 않는다.

🍎🍏 오늘의 느낀점
: 데이터베이스를 CLI로 직접 만들어보니까 쉽지 않도다.. 이것도 역시 연습만이 살 길인가.. 무튼 ERD설계를 먼저 잘해야 데이터베이스도 곰방곰방 생성할 수 있으니 로직을 역시 잘 정리하는게 중요하다는 것을 깨달았다. 그리고 오늘 쓴 쿼리들도 나중에 구글링 하면서 쓸 수도 있지만 조금은 더 공부해 놓는게 좋을 것 같다.. 이상 끝!