BE_[Database] MySQL and SQL_Assignment_10.31

송철진·2022년 10월 30일

✅ 1

아래 제공된 ERD(Entity Relational Diagram)를 참고하여 MySQL Server 내에 “westagram” 이름의 스키마를 생성한 후에, DDL을 사용하여 “westagram” 스키마 내에 users, posts, comments, likes 테이블 객체를 구현합니다.

1. 스키마 생성, 확인 및 사용

CREATE DATABESES westagram

2. 각 테이블 생성 및 확인

users

CREATE TABLE users 
(
  id INT NOT NULL AUTO_INCREMENT,                        
  name VARCHAR(50) NOT NULL,                             
  email VARCHAR(200) NOT NULL,
  profile_image VARCHAR(1000) NULL,    
  password VARCHAR(200) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,           
  updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP, 
  PRIMARY KEY (id)                                       
);

posts

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

likes

CREATE TABLE likes
(
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  post_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES users (id),
  FOREIGN KEY (post_id) REFERENCES posts (id)
);

comments

CREATE TABLE comments
(
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  post_id INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES users (id),
  FOREIGN KEY (post_id) REFERENCES posts (id)
);

✅ 2

MySQL의 DML(Data Manipulation Language)를 사용하여, 각 테이블에 data를 추가합니다.

mysql> SELECT * FROM users;
+----+-----------+---------------------------+---------------------------------------------------------+------------+---------------------+------------+
| id | name      | email                     | profile_image                                           | password   | created_at          | updated_at |
+----+-----------+---------------------------+---------------------------------------------------------+------------+---------------------+------------+
|  1 | Rebekah   | Glover12345@email.com     | https://github.com/amacneil/dbmate#command-line-options | password   | 2022-07-16 14:15:21 | NULL       |
|  2 | Fabian    | O'Connell12345@email.com  | https://github.com/amacneil/dbmate#command-line-options | password1  | 2022-07-16 14:15:21 | NULL       |
|  3 | Elenor    | Skiles12345@email.com     | https://github.com/amacneil/dbmate#command-line-options | password2  | 2022-07-16 14:15:21 | NULL       |
|  4 | Madge     | Quitzon12345@email.com    | https://github.com/amacneil/dbmate#command-line-options | password3  | 2022-07-16 14:15:21 | NULL       |
+----+-----------+---------------------------+---------------------------------------------------------+------------+---------------------+------------+

mysql> SELECT * FROM posts;
+----+-------------------------+-----------------------------------------------------+---------+---------------------+---------------------+
| id | title                   | content                                             | user_id | created_at          | updated_at          |
+----+-------------------------+-----------------------------------------------------+---------+---------------------+---------------------+
|  1 | 위코드 1일차               | HTML과 CSS 익숙해지기..                                |       1 | 2022-04-10 11:41:36 | 2022-04-23 11:21:44 |
|  2 | 위코드 2일차               | Javascript 기본 문법 학습..                            |       1 | 2022-04-18 11:41:36 | 2022-04-23 11:21:54 |
|  3 | 위코드 3일차               | 웹서비스의 역사와 발전 세션을 듣고..                        |       1 | 2022-05-02 11:41:36 | 2022-04-23 11:22:01 |
|  4 | 자료구조 1번               | BigO Notation이란 무엇인가?                            |       2 | 2022-04-19 11:41:36 | 2022-04-23 11:22:13 |
|  5 | 자료구조 2번               | 시간 복잡도와 공간 복잡도에 대해서..                        |       2 | 2022-04-22 14:12:45 | NULL                |
|  6 | 프론트 개발 입문            | 프론트 입문 HTML이란 무엇인가?                            |       3 | 2022-04-23 11:43:25 | 2022-04-23 11:56:25 |
+----+-------------------------+-----------------------------------------------------+---------+---------------------+---------------------+
-- likes 중간 테이블을 통해서 누가 어떤 게시물을 좋아요하고 있는지 알 수 있습니다.

mysql> SELECT * FROM likes;
+----+---------+---------+---------------------+
| id | user_id | post_id | created_at          |
+----+---------+---------+---------------------+
|  1 |       1 |       3 | 2022-07-16 14:18:26 |
|  2 |       2 |       3 | 2022-07-16 14:18:26 |
|  3 |       3 |       3 | 2022-07-16 14:18:26 |
|  4 |       4 |       3 | 2022-07-16 14:18:26 |
|  5 |       5 |       3 | 2022-07-16 14:18:47 |
|  6 |       6 |       1 | 2022-07-16 14:18:47 |
+----+---------+---------+---------------------+

1. 각 테이블 데이터 추가

users

INSERT INTO users (name, email, profile_image, password, created_at, updated_at) VALUES ("Rebekah", "Glover12345@email.com", "https://github.com/amacneil/dbmate#command-line-options", "password", "2022-07-16 14:15:21", NULL);
INSERT INTO users (name, email, profile_image, password, created_at, updated_at) VALUES ("Fabian", "O'Connell12345@email.com", "https://github.com/amacneil/dbmate#command-line-options", "password1", "2022-07-16 14:15:21", NULL);
INSERT INTO users (name, email, profile_image, password, created_at, updated_at) VALUES ("Elenor", "Skiles12345@email.com", "https://github.com/amacneil/dbmate#command-line-options", "password2", "2022-07-16 14:15:21", NULL);
INSERT INTO users (name, email, profile_image, password, created_at, updated_at) VALUES ("Madge", "Quitzon12345@email.com", "https://github.com/amacneil/dbmate#command-line-options", "password3", "2022-07-16 14:15:21", NULL);

posts

INSERT INTO posts (title, content, user_id, created_at, updated_at) VALUES ("위코드 1일차", "HTML과 CSS 익숙해지기..", 1, "2022-04-10 11:41:36", "2022-04-23 11:21:44");
INSERT INTO posts (title, content, user_id, created_at, updated_at) VALUES ("위코드 2일차", "Javascript 기본 문법 학습..", 1, "2022-04-10 11:41:36", "2022-04-23 11:21:54");
INSERT INTO posts (title, content, user_id, created_at, updated_at) VALUES ("위코드 3일차", "웹서비스의 역사와 발전 세션을 듣고..", 1, "2022-04-10 11:41:36", "2022-04-23 11:22:01");
INSERT INTO posts (title, content, user_id, created_at, updated_at) VALUES ("자료구조 1번", "BigO Notation이란 무엇인가?", 2, "2022-04-10 11:41:36", "2022-04-23 11:22:13");
INSERT INTO posts (title, content, user_id, created_at, updated_at) VALUES ("자료구조 2번", "시간 복잡도와 공간 복잡도에 대해서..", 2, "2022-04-22 14:12:45", NULL);
INSERT INTO posts (title, content, user_id, created_at, updated_at) VALUES ("프론트 개발 입문", "프론트 입문 HTML이란 무엇인가?", 3, "2022-04-23 11:43:25", "2022-04-23 11:56:25");

likes
👉 먼저, created_at 컬럼을 추가한다

ALTER TABLE likes ADD create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

근데 컬럼명을 잘못 지정했다('d'가 빠짐!)

👉 CHANGE 명령어를 통해 컬럼명을 수정한다
ALTER TABLE likes CHANGE create_at created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

👉 데이터 추가

INSERT INTO likes (user_id, post_id, created_at) VALUES (1, 3, "2022-07-16 14:18:26");
INSERT INTO likes (user_id, post_id, created_at) VALUES (2, 3, "2022-07-16 14:18:26");
INSERT INTO likes (user_id, post_id, created_at) VALUES (3, 3, "2022-07-16 14:18:26");
INSERT INTO likes (user_id, post_id, created_at) VALUES (4, 3, "2022-07-16 14:18:26");
INSERT INTO likes (user_id, post_id, created_at) VALUES (5, 3, "2022-07-16 14:18:47");
INSERT INTO likes (user_id, post_id, created_at) VALUES (6, 1, "2022-07-16 14:18:47");

에러가 발생했다 왜일까?

Error 1452 참조링크 : 외래키를 조작할 때 발생
👉 solution:

SET foreign_key_checks =0;	# 외래키 제약조건 무시하도록 처리
		# 코드 삽입,수정
SET foreign_key_checks =1;	# 외래키 제약조건 작동되도록 처리

해결!

결과:

알고보니..
users에 등록된 id는 4개인데, likes에 등록된 user_id는 6개라서 참조 오류 발생! 혹시 모르니 강제로 추가한 마지막 2개의 데이터는 삭제했다
DELETE FROM likes WHERE id = 9;
DELETE FROM likes WHERE id = 10;

✅ 3

MySQL의 DQL(Data Query Language)를 사용하여, 아래 결과가 출력될 수 있는 쿼리문을 작성해서 제보세요. 모든 사용자 정보와 해당 사용자가 작성한 게시물 목록이 출력될 수 있도록 쿼리문을 작성해주세요. 출력되는 컬럼명도 아래와 같이 만들어주세요.

+----+-----------+---------+-------------------------+-----------------------------------------------------+
| id | name      | post_id | title                   | content                                             |
+----+-----------+---------+-------------------------+-----------------------------------------------------+
|  1 | Rebekah   |       1 | 위코드 1일차               | HTML과 CSS 익숙해지기..                                |
|  1 | Rebekah   |       2 | 위코드 2일차               | Javascript 기본 문법 학습..                            |
|  1 | Rebekah   |       3 | 위코드 3일차               | 웹서비스의 역사와 발전 세션을 듣고..                        |
|  2 | Fabian    |       5 | 자료구조 1번               | BigO Notation이란 무엇인가?                            |
|  2 | Fabian    |       6 | 자료구조 2번               | 시간 복잡도와 공간 복잡도에 대해서..                        |
|  3 | Elenor    |       7 | 프론트 개발 입문            | 프론트 입문 HTML이란 무엇인가?                            |
|  4 | Madge     |    NULL | NULL                    | NULL                                                |
+----+-----------+---------+-------------------------+-----------------------------------------------------+

없는 데이터는 NULL로 표시해야하므로 LEFT JOIN을 할 것이다
1. SELECT {표시할 컬럼명, ...}
2. FROM {기준테이블}
3. LEFT JOIN {NULL 값이 들어가게 될 테이블}
4. ON {JOIN 조건}

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

👉 users.id, posts.id를 오름차순으로 정렬하고 싶다
SELECT users.id, users.name, posts.id, posts.title, posts.content FROM users LEFT JOIN posts ON users.id = posts.user_id ORDER BY users.id, posts.id ASC;

ORDER BY 컬럼명 ASC : 오름차순 정렬
ORDER BY 컬럼명 DESC : 내림차순 정렬

ORDER BY를 쓸 때 먼저 정렬하고 싶은 컬럼명을 앞에 써야한다!
posts.id, users.id 👉 NULL이 맨 위로 가서 곤란함
users.id, posts.id 👉 정답!

👉 posts.id를 를 post_id로 바꾸고 싶다
SELECT users.id, users.name, posts.id AS post_id, posts.title, posts.content FROM users LEFT JOIN posts ON users.id = posts.user_id ORDER BY users.id, posts.id ASC;

바꿀컬럼명 AS 별명 : 표시할 이름을 별명으로 바꿔준다

✅4

MySQL의 DQL(Data Query Language)를 사용하여, 아래 결과가 출력될 수 있는 쿼리문을 작성해서 제보세요. ”Rebekah”라는 이름을 가진 사용자가 좋아요하고 있는 게시물 중에서 “무엇인가?”라는 단어가 포함된 게시물만 출력해주세요. 추가로 게시물을 작성한 사람과 게시물을 좋아요하는 사람을 author와 like_user라는 컬럼명으로 만들어서 출력해주세요.

+----------+--------------+-------------------------+--------+-----------+
| post_iid | title        | content                 | author | like_user |
+----------+--------------+-------------------------+--------+-----------+
|  4       | 자료구조 1번    | BigO Notation이란 무엇인가?| Fabian | Rebekah   |
|  6       | 프론트 개발 입문 | 프론트 입문 HTML이란 무엇인가?| Elenor | Rebekah   |
+----------+--------------+-------------------------+--------+-----------+

👉 likes테이블의 데이터와 위의 예상 출력 자료가 불일치하므로 likes테이블을 수정해야 한다. 행씩 수정해도 되지만 id 오름차순 초기화를 위해 drop하고 다시 생성했다:

생각해보기

	조건	결과
post_id 👉		likes.post_id
title	likes.post_id = posts.id	인 같은 행의 posts.title
content	likes.post_id = posts.id	인 같은 행의 posts.content
author	likes.post_id = posts.id인 같은 행의 posts.user_id = users.id	인 같은 행의 users.name
like_user	likes.user_id = users.id	인 같은 행의 users.name

아래 두 결과를 어떻게 조합하면 좋을까?

select posts.id as post_iid, posts.title, posts.content, users.name as like_user
FROM posts 
INNER JOIN likes 
ON posts.id = likes.post_id
INNER JOIN users
ON likes.user_id = users.id 
WHERE content LIKE "%무엇인가?";

SELECT posts.id as post_iid, posts.title, posts.content, users.name as author 
FROM posts 
LEFT JOIN users 
ON posts.user_id = users.id 
WHERE content LIKE '%무엇인가?' ;

결과

2023.02.15. 업데이트

SELECT
    p.id as post_id,
    p.title,
    p.content,
    u.name as author,
    l.name as like_user 
FROM posts p
INNER JOIN users u ON u.id = p.user_id
LEFT JOIN (
    SELECT
        likes.post_id,
        users.name
    FROM likes
    INNER JOIN users ON users.id = likes.user_id
) l ON l.post_id = p.id
WHERE content like '%무엇인가?';

과제 1, 2: 스키마(데이터베이스)를 덤프(추출) 방법:
mysql 모드에서 나오려면 exit
터미널에 아래 명령어 치기:
mysqldump -u root -p westagram > n기_백엔드_홍길동_westagram.sql
추출된 파일은 user폴더에 있는데 검색해서 찾으면 됨.

과제 3, 4에 대한 결과물 (SQL문)을 캡쳐해서 이미지로:
n기_백엔드_홍길동_westagram_sql_과제3.png , n기_백엔드_홍길동_westagram_sql_과제4.png 형식

송철진

검색하고 기록하며 학습하는 백엔드 개발자

이전 포스트

BE_[Database] MySQL and SQL_data 검색/조회_10.31

다음 포스트