[5주차 - DAY4] DB 테이블 생성

 

 

MySQL(mariadb) 날짜/시간 자료형

	type	format
DATE	날짜	YYYY-MM-DD
DATETIME	날짜 + 시간	YYYY-MM-DD HH:MM:SS
TIME	시간	HH:MM:SS
TIMESTAMP	날짜 + 시간	YYYY-MM-DD HH:MM:SS

 

DATETIME vs TIMESTAMP

DATETIME과 TIMESTAMP의 가장 큰 차이점은 데이터 저장 방식이다. TIMESTAMP는 UTC(협정 세계시)로 저장한다. 

우리나라는 협정 세계시보다 9시간 빠르다(UTC+9). 우리나라에서 오후 9시에 글을 작성하고, UTC 기준 국가 중 하나인 영국에서 그 게시물을 본다고 가정해 보자. DATETIME을 사용하면 오후 9시로 보이고, TIMESTAMP를 사용하면 오후 12시로 보인다. 따라서 글로벌 서비스를 제공한다면 TIMESTAMP를 사용해야 한다.

 

업데이트 시간 반영하기

'ON UPDATE NOW()'가 이 행이 업데이트된다면 현재 시간을 반영하겠다는 의미이다.

CREATE TABLE posts(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at DATETIME DEFAULT NOW() ON UPDATE NOW()
);

테이블 생성

수정 전

수정 후

 

 

게시판 실습

설계했던 테이블을 구현해 보자.

스키마 생성

먼저 "Board" 스키마를 생성한다.

CREATE DATABASE Board;

생성한 스키마로 들어가자. 사용할 스키마를 선택해야 테이블도 생성할 수 있고 CRUD 명령을 실행할 수 있다.

USE Board;

 

테이블 생성

사용자 테이블 생성

아래 두 코드 중 아무거나 사용하면 된다. AUTO_INCREMENT는 자동으로 숫자를 하나씩 증가시켜 주는 역할을 해주기 때문에 데이터를 삽입할 때 id 값은 적지 않아도 된다. AUTO_INCREMENT는 데이터 삽입을 실패해도 값이 증가한다. 값이 증가하지 않게 하려면 auto_increment_lock_mode 설정을 변경하면 된다.

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

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

테이블 생성

사용자 테이블 구조 확인

 

게시글 테이블 생성

아래 두 코드 모두 사용 가능하다. 

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)
);

ALTER TABLE posts
    ADD COLUMN updated_at DATETIME
    DEFAULT NOW()
    ON UPDATE NOW();
    
ALTER TABLE posts 
    ADD COLUMN user_id INT;
    
ALTER TABLE posts 
    ADD FOREIGN KEY (user_id) 
    REFERENCES users(id);

CREATE TABLE posts(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    content VARCHAR(2000),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at DATETIME DEFAULT NOW() ON UPDATE NOW(),
    user_id INT,
    FOREIGN KEY(user_id) REFERENCES users(id)
);

테이블 생성

수정일자 컬럼 추가

사용자 ID 컬럼, 외래키 추가

게시글 테이블 구조 확인

 

데이터 삽입

사용자 데이터 삽입

사용자 ID는 자동으로 증가하기 때문에 이름, 직업, 생년월일에 대해서 데이터를 삽입하면 된다. DATE 자료형에 데이터를 입력할 때는 1900년대인지 2000년대인지 확실하게 지정할 수 있도록 두 번째 코드를 사용하는 것이 좋다.

INSERT INTO users(name, job, birth) VALUES("gongu", "actor", "800123");

INSERT INTO users(name, job, birth) VALUES("gongu", "actor", "1980-01-23");

데이터 삽입

삽입된 데이터 확인

 

게시글 데이터 삽입

현재 FK 제약 조건에서는 NULL을 허용하기 때문에 user_id가 NULL이 될 수 있다. 하지만 users 테이블에 존재하지 않은 값을 user_id로 입력하면 에러가 발생하며 데이터가 삽입되지 않는다.

INSERT INTO posts(title, content) VALUES("title1", "content1");

INSERT INTO posts(title, content, user_id) VALUES("title2", "content2", 1);

데이터 삽입

데이터 삽입

데이터 삭제를 좀 했어서 없는 id가 존재한다.

users 테이블에 존재하지 않는 id에 대해 삽입을 시도할 경우

 

JOIN

데이터베이스 내의 여러 테이블을 붙여서 가상의 테이블을 만들어 돌려주는 기능이다.

어떤 컬럼을 기준으로 테이블을 붙일지는 ON 절에 작성한다.

LEFT JOIN은 왼쪽 테이블 값은 모두 가져오고, 오른쪽 테이블 값은 교집합이 존재하는 데이터만 가져온다는 뜻이다. RIGHT JOIN은 그 반대의 의미이다. 자주 쓰게 될 INNER JOIN은 양쪽 테이블의 교집합 데이터만 가져온다.

 

해당 게시글을 쓴 사람의 생년월일을 JOIN을 사용해 조회해 보자.

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

조회 결과