RISK IT

[TIL7_23.1.15.] mySQL Database Schema & Table 구축 | Assignment 1 본문

IT/TIL

[TIL7_23.1.15.] mySQL Database Schema & Table 구축 | Assignment 1

nomoremystery 2023. 1. 15. 23:00
반응형

Assignment 1 | Database Schema & table 구축

과제 1

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

진행 과정

기존에 생성했던 westagram databases에 수정, 추가

✍️ 입력

mysql> SHOW TABLES;

💻 출력

+---------------------+
| Tables_in_westagram |
+---------------------+
| jobs                |
| posts               |
| users               |
+---------------------+
3 rows in set (0.01 sec)

✍️ 입력

mysql> DESC users;

💻 출력

+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int          | NO   | PRI | NULL              | auto_increment              |
| name       | varchar(50)  | NO   |     | NULL              |                             |
| email      | varchar(200) | NO   |     | NULL              |                             |
| password   | varchar(200) | NO   |     | NULL              |                             |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED           |
| updated_at | timestamp    | YES  |     | NULL              | on update CURRENT_TIMESTAMP |
| age        | int          | NO   |     | NULL              |                             |
| job_id     | int          | YES  | MUL | NULL              |                             |
+------------+--------------+------+-----+-------------------+-----------------------------+

// 새로운 profile_image column 추가하고, 기존 age, job_id column 삭제해야함.

✍️ 입력

mysql> ALTER TABLE users ADD profile_image VARCHAR(1000) NULL;

// profile_image column 추가

💻 출력

Query OK, 0 rows affected (0.01 sec)

✍️ 입력

mysql> DESC users;

💻 출력

+---------------+---------------+------+-----+-------------------+-----------------------------+
| Field         | Type          | Null | Key | Default           | Extra                       |
+---------------+---------------+------+-----+-------------------+-----------------------------+
| id            | int           | NO   | PRI | NULL              | auto_increment              |
| name          | varchar(50)   | NO   |     | NULL              |                             |
| email         | varchar(200)  | NO   |     | NULL              |                             |
| password      | varchar(200)  | NO   |     | NULL              |                             |
| created_at    | timestamp     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED           |
| updated_at    | timestamp     | YES  |     | NULL              | on update CURRENT_TIMESTAMP |
| age           | int           | NO   |     | NULL              |                             |
| job_id        | int           | YES  | MUL | NULL              |                             |
| profile_image | varchar(1000) | YES  |     | NULL              |                             |
+---------------+---------------+------+-----+-------------------+-----------------------------+
9 rows in set (0.01 sec)

✍️ 입력

mysql> ALTER TABLE users DROP job_id;

// job_id 칼럼 삭제하려고 했으나,

💻 출력

ERROR 1828 (HY000): Cannot drop column 'job_id': needed in a foreign key constraint 'users_job_id_fkey'

// FK로 제약조건 걸려있어서 삭제 안됨

✍️ 입력

mysql> DROP TABLE jobs;

// jobs 테이블 삭제 시도

💻 출력

ERROR 3730 (HY000): Cannot drop table 'jobs' referenced by a foreign key constraint 'users_job_id_fkey' on table 'users'

// 역시나 안됨

✍️ 입력

mysql> SET foreign_key_checks = 0;

// 제약조건 끄고

💻 출력

Query OK, 0 rows affected (0.00 sec)

✍️ 입력

mysql> DROP TABLE jobs;

// jobs 테이블 다시 삭제

💻 출력

Query OK, 0 rows affected (0.01 sec)

// 잘 삭제됨

✍️ 입력

mysql> DROP TABLE jobs;

// jobs 테이블 다시 삭제

💻 출력

Query OK, 0 rows affected (0.01 sec)

// 잘 삭제됨

✍️ 입력

mysql> SET foreign_key_checks = 1;

// 다시 제약조건 키기

💻 출력

Query OK, 0 rows affected (0.00 sec)

✍️ 입력

mysql> SHOW TABLES;

// 테이블 확인

💻 출력

+---------------------+
| Tables_in_westagram |
+---------------------+
| posts               |
| users               |
+---------------------+
2 rows in set (0.00 sec)

// jobs 테이블 삭제 확인

✍️ 입력

mysql> ALTER TABLE users DROP age;

// age 칼럼 삭제

💻 출력

Query OK, 0 rows affected (0.01 sec)

✍️ 입력

mysql> DESC users;

// jobs 테이블을 삭제해서 users 테이블에서도 job_id 삭제될거라 생각했지만, job_id의 foreign key는 그대로 살아있고 삭제도 안됨.

💻 출력

+---------------+---------------+------+-----+-------------------+-----------------------------+
| Field         | Type          | Null | Key | Default           | Extra                       |
+---------------+---------------+------+-----+-------------------+-----------------------------+
| id            | int           | NO   | PRI | NULL              | auto_increment              |
| name          | varchar(50)   | NO   |     | NULL              |                             |
| email         | varchar(200)  | NO   |     | NULL              |                             |
| password      | varchar(200)  | NO   |     | NULL              |                             |
| created_at    | timestamp     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED           |
| updated_at    | timestamp     | YES  |     | NULL              | on update CURRENT_TIMESTAMP |
| job_id        | int           | YES  | MUL | NULL              |                             |
| profile_image | varchar(1000) | YES  |     | NULL              |                             |
+---------------+---------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

✍️ 입력

mysql> ALTER TABLE users DROP FOREIGN KEY users_job_id_fkey;

// 이렇게 users 테이블의 foreign key 해제할 수 있었다

💻 출력

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

✍️ 입력

mysql> ALTER TABLE users DROP job_id;

// 드디어 job_id 삭제

💻 출력

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

✍️ 입력

mysql> DESC posts;

💻 출력

+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int          | NO   | PRI | NULL              | auto_increment              |
| title      | varchar(100) | NO   |     | NULL              |                             |
| content    | text         | YES  |     | NULL              |                             |
| user_id    | int          | NO   | MUL | NULL              |                             |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED           |
| updated_at | timestamp    | YES  |     | NULL              | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

✍️ 입력

mysql> ALTER TABLE posts MODIFY content VARCHAR(3000) NULL;

// content type VARCHAR(3000)로 변경

💻 출력

Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

✍️ 입력

mysql> CREATE TABLE likes (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> user_id INT NOT NULL,
    -> post_id INT NOT NULL,
    -> PRIMARY KEY(id),
    -> CONSTRAINT likes_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id),
    -> CONSTRAINT likes_post_id_fkey FOREIGN KEY (post_id) REFERENCES posts(id)
    -> );

// likes 테이블 생성

💻 출력

Query OK, 0 rows affected (0.01 sec)

✍️ 입력

mysql> CREATE TABLE comments (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> content VARCHAR(3000) NOT NULL,
    -> 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),
    -> CONSTRAINT comments_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id),
    -> CONSTRAINT comments_post_id_fkey FOREIGN KEY (post_id) REFERENCES posts(id)
    -> );

// comments 테이블 생성

💻 출력

Query OK, 0 rows affected (0.01 sec)

과제 2

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

진행 과정

✍️💻 입출력

mysql> SELECT * FROM users;
+----+---------+------------------------+-----------+---------------------+---------------------+---------------+
| id | name    | email                  | password  | created_at          | updated_at          | profile_image |
+----+---------+------------------------+-----------+---------------------+---------------------+---------------+
|  1 | Rebekah | Glover12345@gmail.com  | password  | 2023-01-14 16:32:55 | 2023-01-14 17:27:23 | NULL          |
|  2 | Fabian  | Connell12345@gmail.com | password  | 2023-01-14 16:34:42 | 2023-01-14 17:28:04 | NULL          |
|  3 | Elenor  | Skiles12345@gmail.com  | password2 | 2023-01-14 16:37:27 | 2023-01-14 17:28:14 | NULL          |
|  4 | Madge   | Quitzon12345@gmail.com | password3 | 2023-01-14 16:37:27 | 2023-01-14 17:28:22 | NULL          |
|  5 | Zelma   | Kozey12345@email.com   | password4 | 2023-01-14 16:37:27 | 2023-01-14 17:32:51 | NULL          |
+----+---------+------------------------+-----------+---------------------+---------------------+---------------+
5 rows in set (0.00 sec)

// 5번에 Zelma 라는 사람 삭제 해야함

✍️💻 입출력

mysql> DELETE FROM users WHERE id=5;
Query OK, 1 row affected (0.00 sec)

✍️💻 입출력

mysql> UPDATE users SET password="password1" WHERE id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE users SET profile_image="https://github.com/amacneil/dbmate#command-line-options";
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> UPDATE users SET email="Glover12345@email.com" WHERE id=1
    -> ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE users SET email="Connell12345@email.com" WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE users SET email="Skiles12345@email.com" WHERE id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE users SET email="Quitzon12345@email.com" WHERE id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

// 그 외 과제와 다른 부분들 수정

✍️💻 입출력

mysql> SELECT * FROM users;
+----+---------+------------------------+-----------+---------------------+---------------------+---------------------------------------------------------+
| id | name    | email                  | password  | created_at          | updated_at          | profile_image                                           |
+----+---------+------------------------+-----------+---------------------+---------------------+---------------------------------------------------------+
|  1 | Rebekah | Glover12345@email.com  | password  | 2023-01-14 16:32:55 | 2023-01-14 21:07:02 | https://github.com/amacneil/dbmate#command-line-options |
|  2 | Fabian  | Connell12345@email.com | password1 | 2023-01-14 16:34:42 | 2023-01-14 21:07:20 | https://github.com/amacneil/dbmate#command-line-options |
|  3 | Elenor  | Skiles12345@email.com  | password2 | 2023-01-14 16:37:27 | 2023-01-14 21:07:38 | https://github.com/amacneil/dbmate#command-line-options |
|  4 | Madge   | Quitzon12345@email.com | password3 | 2023-01-14 16:37:27 | 2023-01-14 21:07:57 | https://github.com/amacneil/dbmate#command-line-options |
+----+---------+------------------------+-----------+---------------------+---------------------+---------------------------------------------------------+
4 rows in set (0.00 sec)

✍️💻 입출력

mysql> SELECT * FROM posts;
+----+----------------------+-----------------------------------------------------+---------+---------------------+------------+
| id | title                | content                                             | user_id | created_at          | updated_at |
+----+----------------------+-----------------------------------------------------+---------+---------------------+------------+
|  4 | 자료구조 1번         | BigO Notation이란 무엇인가?                         |       2 | 2023-01-14 16:44:03 | NULL       |
|  5 | 자료구조 2번         | 시간 복잡도와 공간 복잡도에 대해서..                |       2 | 2023-01-14 16:44:03 | NULL       |
|  6 | 프론 개발 입문       | 프론트 입문 HTML이란 무엇인가?                      |       3 | 2023-01-14 16:44:03 | NULL       |
+----+----------------------+-----------------------------------------------------+---------+---------------------+------------+
3 rows in set (0.01 sec)

✍️💻 입출력

mysql> INSERT INTO posts (id, title, content, user_id) VALUES (1,'위코드 1일차','HTML과 CSS 익숙해지기..',1), (2, '위코드 2일차','Javascript 기본 문법 학습..',1),(3,'위코드 3일차','웹서비스의 역사와 발전세션을 듣고..',1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

// 이전 실습 때 삭제했던 id 1,2,3 row 다시 추가

✍️💻 입출력

mysql> UPDATE posts SET title='프론트 개발 입문' WHERE id=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

// 오타 수정

✍️💻 입출력

mysql> SELECT * FROM posts;
+----+-------------------------+-----------------------------------------------------+---------+---------------------+---------------------+
| id | title                   | content                                             | user_id | created_at          | updated_at          |
+----+-------------------------+-----------------------------------------------------+---------+---------------------+---------------------+
|  1 | 위코드 1일차            | HTML과 CSS 익숙해지기..                             |       1 | 2023-01-14 21:15:06 | NULL                |
|  2 | 위코드 2일차            | Javascript 기본 문법 학습..                         |       1 | 2023-01-14 21:15:06 | NULL                |
|  3 | 위코드 3일차            | 웹서비스의 역사와 발전세션을 듣고..                 |       1 | 2023-01-14 21:15:06 | NULL                |
|  4 | 자료구조 1번            | BigO Notation이란 무엇인가?                         |       2 | 2023-01-14 16:44:03 | NULL                |
|  5 | 자료구조 2번            | 시간 복잡도와 공간 복잡도에 대해서..                |       2 | 2023-01-14 16:44:03 | NULL                |
|  6 | 프론트 개발 입문        | 프론트 입문 HTML이란 무엇인가?                      |       3 | 2023-01-14 16:44:03 | 2023-01-14 21:16:15 |
+----+-------------------------+-----------------------------------------------------+---------+---------------------+---------------------+
6 rows in set (0.00 sec)

✍️💻 입출력

mysql> INSERT INTO likes (user_id, post_id) VALUES (1,3),(2,3),(3,3),(4,1),(1,2),(2,1);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

✍️💻 입출력

mysql> SELECT * FROM likes;
+----+---------+---------+
| id | user_id | post_id |
+----+---------+---------+
|  1 |       1 |       3 |
|  2 |       2 |       3 |
|  3 |       3 |       3 |
|  4 |       4 |       1 |
|  5 |       1 |       2 |
|  6 |       2 |       1 |
+----+---------+---------+
6 rows in set (0.00 sec)

✍️💻 입출력

mysql> ALTER TABLE likes ADD COLUMN created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

// 과제 1 ERD(Entity Relational Diagram)에서는 likes table에 created_at column이 없었는데, 과제 2 예시에서는 있어서 추가

✍️💻 입출력

mysql> SELECT * FROM likes;
+----+---------+---------+---------------------+
| id | user_id | post_id | created_at          |
+----+---------+---------+---------------------+
|  1 |       1 |       3 | 2023-01-14 21:27:47 |
|  2 |       2 |       3 | 2023-01-14 21:27:47 |
|  3 |       3 |       3 | 2023-01-14 21:27:47 |
|  4 |       4 |       1 | 2023-01-14 21:27:47 |
|  5 |       1 |       2 | 2023-01-14 21:27:47 |
|  6 |       2 |       1 | 2023-01-14 21:27:47 |
+----+---------+---------+---------------------+
6 rows in set (0.00 sec)

과제 3

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

진행 과정

post_id로 출력되어야 하는 column 때문에 users, posts, 그리고 아무 죄 없는 likes 테이블까지 지지고 볶았는데 해결이 안됐다.

사실 p.id의 칼럼 이름만 바꿀 수 굳이 3개의 테이블로 LEFT OUTER JOIN 할 필요 없이 users와 posts 테이블만 가지고 쉽게 할 수 있었던 문제다.

한참을 이렇게 저렇게 해보다가 드디어 발견
column의 이름을 바꿀 수 있는 옵션이 있었다.

AS 이후에 바꿀 이름을 적어주면 column의 이름이 변경된다.

✍️💻 입출력

mysql> SELECT u.id, u.name, p.id AS post_id, p.title, p.content FROM users u LEFT OUTER JOIN posts p ON u.id=p.user_id;
+----+---------+---------+-------------------------+-----------------------------------------------------+
| id | name    | post_id | title                   | content                                             |
+----+---------+---------+-------------------------+-----------------------------------------------------+
|  1 | Rebekah |       1 | 위코드 1일차            | HTML과 CSS 익숙해지기..                             |
|  1 | Rebekah |       2 | 위코드 2일차            | Javascript 기본 문법 학습..                         |
|  1 | Rebekah |       3 | 위코드 3일차            | 웹서비스의 역사와 발전세션을 듣고..                 |
|  2 | Fabian  |       4 | 자료구조 1번            | BigO Notation이란 무엇인가?                         |
|  2 | Fabian  |       5 | 자료구조 2번            | 시간 복잡도와 공간 복잡도에 대해서..                |
|  3 | Elenor  |       6 | 프론트 개발 입문        | 프론트 입문 HTML이란 무엇인가?                      |
|  4 | Madge   |    NULL | NULL                    | NULL                                                |
+----+---------+---------+-------------------------+-----------------------------------------------------+
7 rows in set (0.00 sec)

과제 4

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

진행 과정

과제4 문제 내용과 실제 likes 테이블의 데이터 내용이 맞지 않으므로 likes 테이블 데이터 내용을 수정

✍️💻 입출력

mysql> SELECT * FROM likes;
+----+---------+---------+---------------------+
| id | user_id | post_id | created_at          |
+----+---------+---------+---------------------+
|  1 |       1 |       3 | 2023-01-14 21:27:47 |
|  2 |       2 |       3 | 2023-01-14 21:27:47 |
|  3 |       3 |       3 | 2023-01-14 21:27:47 |
|  4 |       4 |       1 | 2023-01-14 21:27:47 |
|  5 |       1 |       2 | 2023-01-14 21:27:47 |
|  6 |       2 |       1 | 2023-01-14 21:27:47 |
+----+---------+---------+---------------------+
6 rows in set (0.00 sec)

✍️💻 입출력

mysql> UPDATE likes SET post_id=4 WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE likes SET post_id=6 WHERE id=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM likes;
+----+---------+---------+---------------------+
| id | user_id | post_id | created_at          |
+----+---------+---------+---------------------+
|  1 |       1 |       4 | 2023-01-14 21:27:47 |
|  2 |       2 |       3 | 2023-01-14 21:27:47 |
|  3 |       3 |       3 | 2023-01-14 21:27:47 |
|  4 |       4 |       1 | 2023-01-14 21:27:47 |
|  5 |       1 |       6 | 2023-01-14 21:27:47 |
|  6 |       2 |       1 | 2023-01-14 21:27:47 |
+----+---------+---------+---------------------+
6 rows in set (0.00 sec)

✍️💻 입출력

SELECT likes.post_id,
posts.title,
posts.content,
author.name AS author,
like_user.name AS like_user
FROM likes
INNER JOIN posts ON posts.id = likes.post_id
INNER JOIN users AS author ON author.id = posts.user_id
INNER JOIN users AS like_user ON like_user.id = likes.user_id
WHERE posts.content LIKE "%무엇인가?%"
AND like_user.name = "Rebekah";
+---------+-------------------------+-------------------------------------------+--------+-----------+
| post_id | title                   | content                                   | author | like_user |
+---------+-------------------------+-------------------------------------------+--------+-----------+
|       4 | 자료구조 1번            | BigO Notation이란 무엇인가?               | Fabian | Rebekah   |
|       6 | 프론트 개발 입문        | 프론트 입문 HTML이란 무엇인가?            | Elenor | Rebekah   |
+---------+-------------------------+-------------------------------------------+--------+-----------+
2 rows in set (0.00 sec)

// users.name이 두 번 사용되기 때문에 users.name의 이름을 별칭으로 구분지어 불러야 한다는 것에서 아이디어를 얻어야 한다.
반응형
저작자표시

'IT > TIL' 카테고리의 다른 글

[TIL9_23.1.17.] [Node] Express 초기 환경세팅  (0) 2023.01.17
[TIL8_23.1.16.] SQL Advanced | Assignment 3  (0) 2023.01.16
[TIL6_23.1.14.] mySQL 데이터 추가/수정/삭제/조회  (0) 2023.01.14
[TIL5_23.1.13.] mySQL - DDL 이용하여 데이터베이스 생성, 접근, 변경  (0) 2023.01.14
[TIL4_23.1.12.] Simple API - 게시글 등록, 목록조회 및 httpie 입력방법  (0) 2023.01.13
'IT/TIL' Related Articles more