[MySQL] 테이블 합치기

테이블 합치기

CREATE TABLE myMember (
    memberID int(10) unsigned auto_increment,
    youEmail varchar(40) NOT NULL,
    youName varchar(20) NOT NULL,
    youPass varchar(20) NOT NULL,
    youBirth int(20) NOT NULL,
    youAge int(20) NOT NULL,
    youPhone int(20) NOT NULL,
    regTime int(11) NOT NULL,
    PRIMARY KEY (memberID)
) charset=utf8;

CREATE TABLE myReview (
    reviewID int(10) unsigned auto_increment,
    memberID int(10) unsigned,
    youCont tinytext NOT NULL,
    regTime int(11) NOT NULL,
    PRIMARY KEY (reviewID)
) charset=utf8;

INSERT INTO myReview(memberID, youCont, regTime) VALUES('1','너무 좋네요','1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('2','ㅇㄴㅁ너무 좋네요','1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('3','너ㄴㅇㅁㄴ무 좋네요','1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('4','너ㅇㅁㄴㅇㅁㄴㅇ무 좋네요','1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('5','너무ㄴㅇㅁ 좋네요','1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('6','너무ㅇㅁㄴ 좋네요','1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('7','너무 좋ㅇㄴㅁㅇ네요','1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('8','너무ㅇㅁㄴ 좋네요','1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('9','너무 ㅇㅁㄴ좋네요','1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('10','너무 ㅇㄴㅁ','1234');

INSERT INTO myreview(memberID, youCont, regTime) VALUES('4', '정말 감사합니다. 너무 좋네요!', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('7', '이거 정말 사야돼!!', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('1', '이거 가방 오나전 별루예요.. 사진빨', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('9', '이거 좋다! 좋다! 좋다!', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('12', '마라탕 먹고싶다.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('3', '오늘은 정말 마라탕 먹고싶다.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('16', '가성비 짱입니다. 당장 사세요.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('19', '안녕하세요. 안녕하세요.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('2', '안녕못해요. 안녕못해요.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('14', '빨리빨리빨리.', '12345');

JOIN 사용하기

SELECT 필드명 FROM 테이블명 앨리어스 JOIN 연결할 테이블명 엘리어스 ON (조건문);

SELECT m.youName, m.youEmail, r.youCont, r.regTime FROM myMember m JOIN myReview r ON(m.memberID = r.memberID);

필드명 바꾸기

SELECT 필드명 AS 기존 필드명 대신 출력할 필드명 FROM 테이블명

SELECT m.youName, m.youEmail, r.youCont AS regTime FROM myMember m JOIN myReview r ON(m.memberID = r.memberID);

필드명 내용이 없는 경우 표시

LEFT JOIN

SELECT m.youName, m.youEmail, r.youCont, r.regTime FROM myMember m LEFT JOIN myReview r ON(m.memberID = r.memberID);