DATABASE2-MySQL(3)

1. 테이블 분리하기

 

RENAME TABLE topic TO topic_backup;

-> topic_backup으로 이름 변경

 

`author` 테이블 구조

CREATE TABLE `author` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL,

`profile` varchar(200) DEFAULT NULL,

PRIMARY KEY (`id`)

);

 

`author` 테이블 데이터 입력

INSERT INTO `author` VALUES (1,'egoing','developer');

INSERT INTO `author` VALUES (2,'duru','database administrator');

INSERT INTO `author` VALUES (3,'taeho','data scientist, developer');

 

`topic` 테이블 구조

CREATE TABLE `topic` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`title` varchar(30) NOT NULL,

`description` text,

`created` datetime NOT NULL,

`author_id` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

);

 

`topic` 테이블 데이터 입력

INSERT INTO `topic` VALUES (1,'MySQL','MySQL is...','2018-01-01 12:10:11',1);

INSERT INTO `topic` VALUES (2,'Oracle','Oracle is ...','2018-01-03 13:01:10',1);

INSERT INTO `topic` VALUES (3,'SQL Server','SQL Server is ...','2018-01-20 11:01:10',2);

INSERT INTO `topic` VALUES (4,'PostgreSQL','PostgreSQL is ...','2018-01-23 01:03:03',3);

INSERT INTO `topic` VALUES (5,'MongoDB','MongoDB is ...','2018-01-30 12:31:03',1);

 

2. 관계형 데이터베이스의 꽃 JOIN

SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;

SELECT topic.id,title,description,created, name, profile FROM topic LEFT JOIN author ON topic.author_id = author.id;

-> . 필요

SELECT topic.id AS topic_id,title,description,created, name, profile FROM topic LEFT JOIN author ON topic.author_id = author.id;

-> 표에서 id로 표시되는 것을 topic_id로 표시

 

UPDATE author SET profile='database adminstrator' WHERE id = 2;

-> 데이터 수정