[MySQL] 테이블 분리하고 JOIN

mysql> RENAME TABLE topic TO topic_backup; // 이름 바꾸기

• topic 테이블 만들기

mysql> CREATE TABLE topic(
	-> id INT(11) NOT NULL AUTO_INCREMENT,
    -> title VARCHAR(30) NOT NULLL,
    -> description TEXT NULL,
    -> author_id INT(11) NULL,
    -> PRIMARY KEY(id));
mysql> DESC topic;

• author 테이블 만들기

mysql> CREATE TABLE author(
	-> id INT(11) NOT NULL, AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> profile VARCHAR(200) NULL,
    -> PRIMARY KEY(id)
    -> );
    mysql> DESC author;
 mysql> INSERT INTO author (name, profile) VALUES('ssj', 'student');
 mysql> SELECT * FROM author;

mysql> INSERT INTO topic(id, author_id) VALUES(1, 1);

mysql> INSERT INTO `topic` VALUES(1, 1);

mysql> SELECT * FROM topic;

JOIN

mysql> SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;
mysql> SELECT * FROM topic AS T LEFT JOIN author ON T.author_id = author.id;
mysql> SELECT topic.id, title, description, name, profile FROM topic LEFT JOIN author 
	-> ON topc.author_id = author.id;
mysql> SELECT topic.id AS topic_id, title, description, name, profile FROM topic 
	-> LEFT JOIN author ON topic.author_id = author.id;