[TIL] 9/12 데이터베이스 실습 - SELECT, ALTER, JOIN, FK, PK, AUTO_INCREMENT, NOT NULL, DEFALUT

1. DB 테이블 실습

 

1.1. ERD 테이블

 

- 회원 테이블 (users)

id	name	job	birth
1	...	...	...
2	...	...	...

 

- 게시글 테이블 (posts)

id	title	content	created_at
1	...	...	...
2	...	...	...

 

 

1.2. 기본 셋팅

 

1) "Board" 스키마 생성

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

# 방 들어가기
USE Board;

 

 

2) 사용자 테이블 생성

# users 테이블 생성
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    job VARCHAR(100),
    birth DATE,
    PRIMARY KEY (id)
);

 

 

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

 

 

1.3. ALTER 로 컬럼 추가하기

 

- 게시글 테이블 (posts)

updated_at, 작성자 필드를 추가함

id	title	content	created_at	updated_at	작성자
1	...	...	...	...	...
2	...	...	...	...	...

 

 

-updated_at 컬럼 추가

# 컬럼 추가

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

 

수정일자 칼럼을 추가하였다. 처음에 INSERT 할 때 수정일자 필드는 최초로 글을 올린 시각, 즉 created_at 과 동일한 현재 시간으로 값이 들어간다. 그리고 ON UPDATE 조건을 걸어주었기 때문에, 이후에 UPDATE 될 때의 시각으로 UPDATE 가 일어난다. 

 

 

- 작성자 컬럼 추가

 

작성자는 users 테이블에서 참조해야 한다. FOREIGN KEY 는 다른 테이블에서 참조해서 가지온 컬럼을 말한다. 

 

현재 만들어둔 테이블에는 user_id 라는 컬럼이 없기 때문에 먼저 user_id 컬럼을 만들고, users 테이블의 회원 id 를 참조해오면 된다.

# 컬럼 추가

ALTER TABLE posts ADD COLUMN user_id INT;

user_id 필드 추가

 

# 조건 추가

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

 

MUL 은 multiple 이라는 말로, 여러 행이 같은 값을 가질 수 있다는 말이다. (중복이 가능하다는 것)

FK 조건 추가

 

현재 users 테이블

# users 테이블의 id 1번을 가진 user 가 글을 작성함
INSERT INTO posts (title, content, user_id) VALUES ("title2", "content2", 1);

 

user_id 컬럼 추가 전에 넣어둔 1 번 row 는 user_id 가 NULL 값, user_id 컬럼 추가 후에는 user_id 값을 넣어줬기 때문에 user_id 컬럼에 값이 들어가 있는 것을 확인할 수 있다. 추가로 존재하지 않는 user_id 값을 넣는다면 참조 에러가 나니 주의하자.

 

 

 

1.4. AUTO_INCREMENT

 

테이블의 특정 열에 자동으로 고유한 값을 할당해주는 속성으로, 주로 기본키 역할을 하는 INT 타입의 열에 사용된다. 

값을 수동으로 입력할 필요 없이 새로운 행이 추가 될 때 마다 자동으로 값이 1씩 증가해서 들어간다.

 

id 컬럼에 값을 넣어주지 않았지만, 값이 들어가 있는 것을 확인할 수 있다. 

INSERT INTO users (name, job, birth) VALUES ('kim', 'student', '2000-01-01');
INSERT INTO users (name, job, birth) VALUES ('lee', 'developer', '1990-12-12');
INSERT INTO users (name, job, birth) VALUES ('park', 'developer', '1992-07-07');

 

 

 

※ 참고1

여기서 내 궁금증.. 그렇다면 3번 row 를 지우고 다른 row 를 넣어주면 id 값은 3이 될까 4가 될까?

 

id 가 3 인 row 를 지우고

 

새로운 row 추가했더니 id 값은 3이 아닌 4가 되었다!

 

AUTO_INCREMENT 속성을 걸어놓으면, row 를 지워도 숫자가 채워지지 않고 계속 증가한다. 사실 당연한 것이라고 생각되는데, 나는 지금 몇 개 안되는 데이터로 실험을 했지만, 데이터가 방대할 때 어떤 row 가 지워졌다고 그걸 채워주는 것보다 쭉쭉 숫자를 키워나가는게 훨씬 효율적인 방법이기 때문이다.

 

그리고 회사에서는 보통 데이터들을 바로바로 삭제하는 것이 아니라 soft delete 라는 것을 통해 삭제를 한다고 한다. 

soft delete 는 물리적으로 정보를 삭제하는 것이 아니라, 컬럼에 delete 필드를 만들어 놓고 지워지기 전에는 컬럼 값을 true (또는 1 등) 로 놔뒀다가 삭제시 컬럼 값만 false (또는 0) 으로 바꿔주는 것이라고 한다. (정보 복구나 등등의 이유에 의해 그렇게 한다고 한다)

그래서 데이터베이스에서 데이터가 물리적으로 슥삭슥삭 많이 지워지는 일이 없다고.. 🧐

 

 

※ 참고2 - auto_increment_lock_mode

INSERT 하다가 실패해도 숫자가 증가한다. auto_increment_lock_mode 의 디폴트 값이 실패해도 숫자가 증가하도록 되어있기 때문

 

 

1.5. NOT NULL 제약 조건

 

말 그대로 NULL 이 아닌 컬럼 값을 무조건 넣어야 한다는 제약 조건이다. 

 

1) NOT NULL 필드에 NULL 을 넣는 경우 -> 컬럼에 null 이 들어갈 수 없다는 에러 남

 

2) NOT NULL 필드에 아무값도 넣지 않는 경우 -> 매치할 값이 없다(값을 넣어주지 않았다)는 에러 남

 

 

1.6. DEFALUT

 

값이 들어오지 않을 때 기본 값으로 셋팅해주는 값, 즉 공란으로 INSERT 되었을 때 DEFAULT 설정 해둔 기본 값이 INSERT 된다. 

테이블을 만들 때 DEFAULT 값을 따로 설정해주지않으면 기본적으로 NULL 이 기본 값으로 설정되어 있다. 

 

 

1.7. 날짜 / 시간 타입

 

- DATE

• 날짜만 포함하는 경우
• YYYY-MM-DD

- DATETIME

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

- TIME

• 시간만 포함하는 경우
• HH:MM:SS

- TIMESTAMP

• 날짜 + 시간
• YYYY-MM-DD HH:MM:SS (24시간제임)
• 시스템 시간대 정보에 맞게 일시를 저장
• 자동입력 (DATETIME 과의 다른점)

 

1.8. JOIN

 

실제로 테이블을 만드는 것이 아니라, select 할 때만 보기 편하도록 테이블들을 임시로 합쳐서 돌려주는 것이다. 

 

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

 

근데 posts.id 와 users.id 컬럼의 중복을 제거하고 싶다면 이렇게 조인해서 보여줄 컬럼명을 써주면 된다. 

id 라는 컬럼명은 users 테이블과 posts 테이블에 둘 다 존재하기 때문에 posts.id 라고 명시를 해주면 posts 테이블의 컬럼만 나타난다. 

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