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 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) VALUE('4', '정말 감사합니다!! 너무 좋네요', '12345');
INSERT INTO myReview(memberID, youCont, regTime) VALUE('12', '베스트 아이템 추천해요!!', '21312');
INSERT INTO myReview(memberID, youCont, regTime) VALUE('1', '다음에도 또 방문할거에요!!', '45213');
INSERT INTO myReview(memberID, youCont, regTime) VALUE('20', '좋아용 정말로 좋아요!', '56443');
INSERT INTO myReview(memberID, youCont, regTime) VALUE('3', '가성비가 정말 좋은거 같습니다!', '45373');
INSERT INTO myReview(memberID, youCont, regTime) VALUE('7', '다른분들한테도 추천 해야겠어요!', '23476');
INSERT INTO myReview(memberID, youCont, regTime) VALUE('15', '아주 좋은거 같아요!!! 좋아요!', '98755');
INSERT INTO myReview(memberID, youCont, regTime) VALUE('8', '굳굳 Very Good!!', '78443');
INSERT INTO myReview(memberID, youCont, regTime) VALUE('17', '정말 감사합니다!! 너무 좋네요', '43597');
INSERT INTO myReview(memberID, youCont, regTime) VALUE('9', '아주 좋은거 같아요!!! 좋아요!', '54675');

필드명 바꾸기

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

SELECT m.youName, m.youEmail, r.youCont, r.regTime AS reviewTime 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);