[PostgreSQL] 모두를 위한 PostgreSQL 2장 예제 풀이

 

모두를 위한 PostgreSQL 2장 예제를 풀이하겠습니다. 

---

문제 1. 데이터베이스 만들기 

CREATE DATABASE community_board;

SQL 실행 후 \l 쉘 명령어를 입력하면 community_board 데이터베이스가 잘 생성됐는지 확인할 수 있습니다.

\ㅣ

데이터베이스 목록 출력

이제 우리가 만든 데이터베이스에 접속하고, 그 안에 테이블들을 만들어야겠죠. 다음의 쉘 명령어를 사용해 앞에서 생성한 데이터베이스로 이동합니다. 

\c community_board

명령어를 실행해 데이터베이스로 잘 이동하면 다음의 출력결과를 얻습니다. 

접속정보: 데이터베이스="community_board", 사용자="postgres".

---

문제 2. 유저 테이블 만들기

유저 테이블을 만들어 주었습니다. 이 때, user_pk 는 INTEGER 형으로 선언하라고 되어있지만 SERIAL PRIMARY KEY 로 선언했습니다. 뒤쪽의 출력 결과를 살펴보니 user_pk 는 value 삽입 순서대로 1 부터 증가하는 형태였으므로 따로 값을 입력하지 않아도 넘버가 증가할 수 있도록 SERIAL 처리 했습니다. 또한, user_pk 의 PK 가 PRIMARY KEY 를 의미하므로 PRIMARY KEY 도 함께 선언해주었습니다. 

CREATE TABLE users (
       user_pk SERIAL NOT NULL PRIMARY KEY,
       user_id VARCHAR(80),
       user_pw VARCHAR(12),
       register_date DATE 
);

---

문제 3. 게시판 테이블 만들기 

위와 같은 방법으로 board 테이블도 생성했습니다. 

CREATE TABLE board (
      board_pk SERIAL NOT NULL PRIMARY KEY,
      board_user INTEGER,
      register_date DATE,
      title VARCHAR(30),
      description VARCHAR(3000),
      likes INTEGER,
      image_name VARCHAR(50)
);

 

아래 쉘 명령어로 지금까지 만든 테이블들이 잘 생성됐는지 확인해보겠습니다. 

\dt

테이블들이 잘 생성됐습니다. 

생성 테이블 확인

---

문제 4. 데이터를 직접 유저와 게시판 테이블에 넣기 

먼저 users 테이블에 필요한 데이터를 처리하겠습니다. 테이블을 만들 때 user_pk 값은 SERIAL 처리 해주었으므로 다른 처리를 하지 않아도 1, 2, 3.. 의 순서대로 증가할 것입니다.

INSERT INTO users (user_id, user_pw, register_date) VALUES
      ('Carveinus', 'car1234', 2020/04/03),
      ('Jenna', 'fur0022', 2020/07/12),
      ('Wlfur', 'kk3375', 2020/08/31);

 

user_pk 도 쿼리 내에 명시 해주고 싶다면 다음 쿼리를 사용하면 됩니다. 

INSERT INTO users (user_pk, user_id, user_pw, register_date) VALUES
      (DEFAULT, 'Carveinus', 'car1234', 2020/04/03),
      (DEFAULT, 'Jenna', 'fur0022', 2020/07/12),
      (DEFAULT, 'Wlfur', 'kk3375', 2020/08/31);

데이터가 잘 들어갔는지 다음 쿼리로 확인합니다. 

SELECT * FROM users;

데이터가 잘 들어갔습니다. 

users 데이터 삽입 결과

 

다음으로, board 테이블에 필요한 데이터도 넣어보겠습니다. 이 때, image_name 값을 가지고 있는 로우도 있고, 아닌 로우도 있습니다. 데이터를 한번에 넣어 주기 위해 값이 없는 로우는 '' 처리해주었습니다. 처음부터 NULL 처리를 했다면 좋았을 것 같네요..

INSERT INTO (board_user, register_date, title, description, image_name) VALUES
        (1, '2020-05-02', 'Developer', 'Perhaps', ''),
        (1, '2020-09-28', 'Why', 'I', 'er.png'),
        (1, '2020-07-13', 'Coffee', 'I', 'coffee.jpeg'),
        (1, '2020-08-14', 'Chicken', 'This', ''),
        (1, '2020-06-22', 'When', 'Let''s', '');

다음 쿼리를 사용해 마찬가지로 데이터가 잘 들어갔는지 확인합니다. 

SELECT * FROM board;

---

문제 5. 한 페이지에 등록날짜를 desc 정렬하여 최근 3개 게시글 조회하기

문제가 조금 잘못된것이 아닌가 생각했는데요. 등록날짜를 기준으로 DESC 정렬을 한다면 지금으로 부터 빠른 날짜, 즉 최근 날짜부터 조회되게 됩니다. 하지만 책의 출력 결과는 오래된 날짜부터 결과값을 보여주고 있습니다. 책의 결과값과 같은 결과를 낼 수 있도록 하겠습니다. 날짜 데이터를 오름차순 정렬합니다. 가장 옛날 날짜부터 3개 데이터만 조회하므로 LIMIT 을 사용합니다. 

SELECT title, description FROM board
ORDER BY register_date ASC
LIMIT 3;

같은 결과가 출력되었습니다. 

쿼리 실행 결과

---

문제 6. 유저의 비밀번호 변경하기

데이터 값 변경을 원하는 유저와 관련된 정보를 WHERE 에 넣고 SET 으로 어떤 변화를 원하는지 적었습니다. 마지막으로 RETURNING * 을 입력하면 쿼리 처리 결과를 반환받을 수 있습니다. 

UPDATE users
       SET user_pw='car4321'
       WHERE user_pk=1
RETURNING *;

쿼리 실행 결과

---

문제 7. 불쾌한 내용이 있는 게시판 삭제하기

WHERE 로 원하는 로우의 조건을 지정해 데이터를 삭제합니다. 

DELETE FROM board WHERE user_pk=5;