[웹 풀사이클 데브코스 TIL] 6주차 Day 2 - 데이터베이스 생성 실습

먼저 CLI 방식으로 데이터를 만들어 봅시다

 

1. "Board" 스키마 생성

CREATE DATABASE Board;
USE Board;

 

2. 사용자 테이블 생성

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

 

3. 사용자 데이터 삽입

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

 

4. 게시글 테이블 생성

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

 

❗️지금 여기서 title이 들어가야 할 자리에 name으로 테이블을 잘못 생성했기 때문에 ALTER 명령어로 바꿔주겠습니다!!

ALTER TABLE posts
CHANGE COLUMN name title varchar(100) NOT NULL;

 

5. 게시글 데이터 삽입

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

INSERT 문을 작성할때 created_at에 대한 데이터는 넣어주지 않았는데도 이렇게 날짜 정보가 자동으로 들어왔어요 (신기!!)

여기서 잠깐 MySQL의 날짜/ 시간 타입을 정리하고 넘어갈게요 ◴_◶

 

1. DATE 
'YYYY-MM-DD' 형식의 날짜 값을 저장. 연, 월, 일을 나타내며 시간 정보는 포함되지 않습니다!

생년월일과 같이 날짜 정보만 필요한 경우에 사용됩니다.



2. DATETIME

'YYYY-MM-DD HH:MM:SS' 형식의 날짜 및 시간 값을 저장합니다. 연, 월, 일 뿐만 아니라 시간까지 포함하여 정확한 시간 정보를
저장합니다.

예약된 이벤트가 시간대를 고려해야 하는 경우에 유용합니다 



3.  TIME

'HH:MM:SS' 형식의 시간 값을 저장합니다. 날짜 정보는 포함되지 않습니다!
주로 시간 간격이나 소요 시간과 같은 시간 정보만 필요한 경우에 사용됩니다 



4. TIMESTAMP

'YYYY-MM-DD HH:MM:SS' 형식의 날짜 및 시간 값을 저장합니다. DATETIME과 유사하지만, 특정 시점에 데이터가 마지막으로 수정된 시간을 나타내는 데 주로 사용합니다. 또 시간대 변환과 자동으로 갱신되는 기능을 제공합니다.

 

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

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

 

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

 

 

7.  게시글 테이블의 id 2 수정

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

title2의 update_at 시간이 업데이트 된 것을 확인할 수 있습니다 !! 😺

 

8. 게시글 테이블에 작성자 컬럼 FK 추가 

ALTER TABLE 문을 사용해서 FK를 추가 할 수 있겠네요 !!

ALTER TABLE posts
ADD COLUMN user_id INT;

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

user_id의 Key값이 MUL인 이유는!! 현재 사용자 번호가 중복이 가능한 상태이기 때문입니다 ㅎㅎ

→ PK값을 쓰고 있는 FK이지만, 중복이 될 수 있기 때문에 MUL 이라고 합니다 👾

 

그리구 현재 users 테이블의 id값에 없는 id로 데이터를 삽입하려고 하면 요렇게 오류가 발생합니다 

(참조 값이 없다는 오류)

 

JOIN

현재 상태에서는 users와 posts 테이블이 나눠져 있기 때문에 한눈에 데이터를 확인하는 데 불편함이 있습니다 ,,

그렇다고 이걸 다시 합치자니 정규화를 한 이유가 없어지죠 !! 🥲

따라서 JOIN을 사용해서 필요할 때만 데이터를 합쳐서 볼 수 있습니다 🍭( '-' 🍭 )

 

JOIN을 사용하면 ?

• 정규화된 구조를 유지하면서 필요한 정보를 얻을 수 있습니다.
• 앞서 말했듯 관련된 정보를 연결해서 각각의 테이블에 있는 필요한 정보를 가져올 수 있습니다.
• 필요한 컬럼과 행을 연결하여 하나의 결과로 조합할 수 있기 때문에 , 필요한 정보를 유연하게 추출할 수 있습니다.
• 필요한 !! (특정한)주제의 데이터만을 관리하므로 데이터의 유지보수와 관리가 용이해집니다.

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

// 게시글의 사용자 번호와 user의 id가 같으면 join 해줘!!

좋지만 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 4를 삽입하는데 실패했는데도 id 5로 자동으로 증가가 된 것에 대해 말씀하셨는데요!
정말 왜 그런지 궁금해져서.. 이유를 찾아봤습니다.

 

데이터베이스에서 Auto Increment 실패 시에도 id 값이 증가하는 이유

데이터베이스의 Auto Increment 속성은 필드에 새로운 레코드가 삽입될 때마다 자동으로 증가하는 값을 가지는데요,

만약 데이터 삽입이 실패하더라도, 이후에 삽입될 데이터의 Auto Increment 값은 실패한 데이터 이후의 값으로 증가 한다고 합니다!

 

id가 4인 데이터를 삽입하려고 했다가 실패했는데요, 위 같은 경우에도 이미 1,2,3까지의 id값이 존재하고 있기 때문에 실패한 데이터 이후에 위치한 5를 자동 증가된 id값으로 설정한다고 합니다 !!

→ 이렇게 되는 이유는 데이터베이스의 ACID 특성 (원자성, 일관성, 고립성, 지속성)의 특성을 유지하기 위해서 입니다.

 

그리고 다시 id값을 정상적으로 증가시키려면 

ALTER TABLE 테이블명 AUTO_INCREMENT = 4;

이렇게 명령을 실행하면 다음에 삽입될 새로운 레코드의 id값은 4부터 시작하게 되지만, 데이터 베이스의 무결성을 해칠 수 있어 별로 권장되지 않는 방법이라고 합니다!
(그니까 알아서 미리 조심하자.. ㅎㅎ)