MYSQL 테이블 합치기

테이블 합치기

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);

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 varchar(12) NOT NULL,
    youAge varchar(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('4', '정말 감사합니다', '1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('17', '좋아요~', '1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('15', '잘 쓸게요', '1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('7', '너무 싸네용', '1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('9', '배송이 빨라요!', '1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('5', '감사합니다.', '1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('11', '배송이 빨라서 좋아요', '1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('1', '가성비 쵝오', '1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('3', '감사해요', '1234');
INSERT INTO myReview(memberID, youCont, regTime) VALUES('18', '잘 쓰겠습니다.', '1234');

필드명 바꾸기

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

SELECT regTime AS reviewTime FROM myReview;

필드 내용이 없는 경우 표시

LEFT JOIN

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