관계형 데이터베이스의 필요성
-
장점
-
유지 보수에 좋음
-
정보를 비교할 때 더 엄밀하게 확인할 수 있음
-
-
단점
- 표가 분리되기 때문에 직관적으로 데이터를 보기 힘듦
-
MySQL에서는 저장은 분산해서 진행하지만 데이터는 합쳐서 보는게 가능하다!
JOIN
# topic 테이블과 author 테이블을 각 id를 기준으로 합침
SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
| 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 |
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
# topic 테이블과 author 테이블을 각 id를 기준으로 원하는 행들만 골라서 합침
SELECT topic.id,title,description,created,name,profile FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title | description | created | name | profile |
+----+------------+-------------------+---------------------+--------+---------------------------+
| 1 | MySQL | MySQL is... | 2018-01-01 12:10:11 | egoing | developer |
| 2 | Oracle | Oracle is ... | 2018-01-03 13:01:10 | egoing | developer |
| 3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 | duru | database administrator |
| 4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 | taeho | data scientist, developer |
| 5 | MongoDB | MongoDB is ... | 2018-01-30 12:31:03 | egoing | developer |
+----+------------+-------------------+---------------------+--------+---------------------------+
# topic 테이블과 author 테이블을 각 id를 기준으로 원하는 행들만 골라서 합치고, 일부 원하는 행의 이름도 새롭게 설정
SELECT topic.id AS topic_id,title,description,created,name,profile FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----------+------------+-------------------+---------------------+--------+---------------------------+
| topic_id | title | description | created | name | profile |
+----------+------------+-------------------+---------------------+--------+---------------------------+
| 1 | MySQL | MySQL is... | 2018-01-01 12:10:11 | egoing | developer |
| 2 | Oracle | Oracle is ... | 2018-01-03 13:01:10 | egoing | developer |
| 3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 | duru | database administrator |
| 4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 | taeho | data scientist, developer |
| 5 | MongoDB | MongoDB is ... | 2018-01-30 12:31:03 | egoing | developer |
+----------+------------+-------------------+---------------------+--------+---------------------------+
database server
- 데이터베이스 클라이언트의 예: 우리가 사용하는 명령어 기반의 프로그램(모니터)
MySQL Workbench
GUI DB클라이언트
