생활코딩( Database2 MySQL ) (10) - Relational Database(2)

1. RENAME database

다음의 syntax 이용

RENAME TABLE <tbl_name old> TO <tbl_name new>

> 이것을 이용해 기존 table을 save

new topic / author 테이블을 다음과 같이 생성

 

 

2. TABLE JOIN

2-1) JOIN 이란?

1) 정의 :

일치하는 id값들을 가진 column을 이용하여 서로 다른 table을 이어주는 방법

※ JOIN으로 조회하는 것 자체가 가상의 역할을 하는 새로운 table을 조합해낼 수 있다는 의미

RDB의 topic table이 게시판의 글들이라면,

comment는 topic id를 보고있고, 댓글을 어떤 사용자가 적었는지 author table을 바라보고 있음

→ author id=3의 profile 변경 시 topic과 comment table들을 수정하지 않아도 됨!!

※ non-RDB의 경우 comment + topic 둘다 author를 고쳐주어야 함

 

2-2) Syntax

SELECT < * | <col_name> > FROM <tbl_name_normal> LEFT JOIN <tbl_name_mapped> \
     ON <col_name_for_JOIN>

1) col_name_for_JOIN : <table_name_normal.col_name> = <table_name_mapped.col_name>

2) LEFT는 왼쪽에 norm(기준)이 되는 table_name을 표현

 

2-3) SQL code

USE opentutorials;

SELECT * FROM topic;
SELECT * FROM author;


-- join tables
-- LEFT used as the pre-existing table, and author is mapped to topic table
SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;

-- skip author_id and id in author
SELECT topic.id AS topic_id, description, created, name, profile \
    FROM topic LEFT JOIN author ON topic.author_id = author.id;

 

2-4) output

+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
| id | title      | description       | created             | author_id | id   | name   | profile                   |
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
|  1 | MySQL      | MySQL is...       | 2018-01-01 12:10:11 |         1 |    1 | egoing | developer                 |
|  2 | Oracle     | Oracle is ...     | 2018-01-03 13:01:10 |         1 |    1 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 |         2 |    2 | duru   | database administrator    |
|  4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 |         3 |    3 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2018-01-30 12:31:03 |         1 |    1 | egoing | developer                 |
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
5 rows in set (0.00 sec)

+----------+-------------------+---------------------+--------+---------------------------+
| topic_id | description       | created             | name   | profile                   |
+----------+-------------------+---------------------+--------+---------------------------+
|        1 | MySQL is...       | 2018-01-01 12:10:11 | egoing | developer                 |
|        2 | Oracle is ...     | 2018-01-03 13:01:10 | egoing | developer                 |
|        3 | SQL Server is ... | 2018-01-20 11:01:10 | duru   | database administrator    |
|        4 | PostgreSQL is ... | 2018-01-23 01:03:03 | taeho  | data scientist, developer |
|        5 | MongoDB is ...    | 2018-01-30 12:31:03 | egoing | developer                 |
+----------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.00 sec)

 

 

---

참조

https://opentutorials.org/course/3161/19521

테이블 분리하기 - 생활코딩

https://opentutorials.org/course/3161/19545

관계형 데이터베이스의 꽃 JOIN - 생활코딩