테이블 JOIN 하기

 

< MEMBER 테이블 전체 코드 >

CREATE TABLE MEMBER(
	MID VARCHAR(30) PRIMARY KEY,
	PASSWD VARCHAR(30) NOT NULL,
	NAME VARCHAR(30) NOT NULL,
	MROLE VARCHAR(30) NOT NULL
);

DROP TABLE MEMBER;

SELECT * FROM MEMBER;
SELECT * FROM MEMBER WHERE MID = 'teemo' AND PASSWD = '1234';

INSERT INTO MEMBER VALUES('teemo', '1234', '김티모', 'USER');
INSERT INTO MEMBER VALUES('admin', '1234', '관리자', 'ADMIN');
INSERT INTO MEMBER VALUES('kim', '1234', '김유진', 'ADMIN');

UPDATE MEMBER SET NAME = '김' WHERE MID = 'teemo';

DELETE FROM MEMBER WHERE MID = 'test';

 

 

 

 

 

 

 

< BOARD 테이블 전체 코드 >

CREATE TABLE BOARD(
	BID INT PRIMARY KEY,
	TITLE VARCHAR(50) NOT NULL,
	CONTENT VARCHAR(100) NOT NULL,
	MID VARCHAR(30) NOT NULL,
	BCOUNT INT DEFAULT 0
);

DROP TABLE BOARD;

INSERT INTO BOARD(BID, TITLE, CONTENT, MID) VALUES((SELECT NVL(MAX(BID), 100) FROM BOARD)+1, '제목', '내용', 'admin');
INSERT INTO BOARD(BID, TITLE, CONTENT, MID) VALUES((SELECT NVL(MAX(BID), 100) FROM BOARD)+1,  '제목', '내용', 'test');
INSERT INTO BOARD(BID, TITLE, CONTENT, MID) VALUES((SELECT NVL(MAX(BID), 100) FROM BOARD)+1,  '제제제', '내용', 'test');
INSERT INTO BOARD VALUES((SELECT NVL(MAX(BID), 100) FROM BOARD)+1,  '공지', '내용', 'admin', 100);


SELECT * FROM BOARD;
SELECT BID,TITLE,CONTENT,MID,BCOUNT FROM BOARD ORDER BY DESC;
SELECT * FROM BOARD ORDER BY BID DESC;
SELECT * FROM BOARD WHERE TITLE LIKE '%제%' ORDER BY BID DESC;
SELECT * FROM BOARD WHERE MID = 'admin' ORDER BY BID DESC;
SELECT * FROM BOARD ORDER BY BCOUNT DESC, BID DESC;

SELECT * FROM BOADR WHERE BID = 101;

DELETE FROM BOARD WHERE BID = 103;

UPDATE BOARD SET BCOUNT = BCOUNT+1 WHERE BID = 101;
UPDATE BOARD SET TITLE = '변경' WHERE BID = 101;
UPDATE BOARD SET CONTENT = '변경' WHERE BID = 101;

 

 

 

 

 

< 두 테이블 JOIN 하기 >

-- JOIN 조인

-- BOARD 정보밖에 없는데, 작성자의 이름을 알아햐함!
SELECT * 
FROM BOARD B
INNER JOIN MEMBER M
ON B.MID = M.MID;

SELECT * 
FROM BOARD B 
LEFT JOIN MEMBER M
ON B.MID = M.MID;

SELECT B.TITLE, M.NAME
FROM BOARD B
INNER JOIN MEMBER M
ON B.MID = M.MID;

-- 내 테이블에 없는 데이터를 가져와야 할 때
SELECT B.BID, B.TITLE, B.CONTENT, B.MID, B.BCOUNT, M.NAME 
FROM BOARD B 
INNER JOIN MEMBER M 
ON B.MID = M.MID 
ORDER BY BID DESC;

-- 제목으로 검색
SELECT * 
FROM BOARD B
LEFT JOIN MEMBER M
ON B.MID = M.MID
WHERE B.TITLE LIKE '제목' 
ORDER BY BID DESC;

-- 작성자 ID 로 검색
SELECT *
FROM BOARD B
INNER JOIN MEMBER M
ON B.MID = M.MID
WHERE B.BID LIKE '101'
ORDER BY B.BID DESC;

-- 조회수 순서로 검색
SELECT *
FROM BOARD B
LEFT JOIN MEMBER M
ON B.MID = M.MID
ORDER BY B.BCOUNT DESC, BID DESC;

-- 하나만 출력
SELECT * 
FROM BOARD B
LEFT JOIN MEMBER M
ON B.MID = M.MID
WHERE B.BID = '102';


SELECT B.TITLE, M.NAME
FROM BOARD B
INNER JOIN MEMBER M
ON B.MID = M.MID;