데이터 베이스 - 조인

지환·2023년 8월 25일

Mysql

목록 보기

7/17

출처| https://www.youtube.com/watch?v=UVY0mfa4VP0&list=PLqTUMsvO70nk8WfCyU-IPmc85390CaSqM&index=1

https://cafe.naver.com/thisisMySQL
https://velog.io/@ong_hh/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EA%B5%AC%EC%B6%95

피벗의 구현

피벗 : 한 열에 포함된 여러 값을 출력하고, 이를 여러 열로 변환하여 테이블 반환 식을 회전하고 필요하면 집계까지 수행하는 것.

 USE sqldb;
 CREATE TABLE pivotTest
 	( uName CHAR(3),
    	  season CHAR(2),
          amount INT );
 
 INSERT INTO pivotTest VALUES
 	('김범수','겨울',10), ('윤종신','여름',15) ;
 
 SELECT * FROM pivoitTest;
 
 SELECT uName,
 	SUM(IF(season='봄',amount,0)) AS '봄',
    	SUM(IF(season='여름',amount,0)) AS '여름',
    	SUM(IF(season='가을',amount,0)) AS '가을',
    	SUM(IF(season='겨울',amount,0)) AS '겨울',
    	SUM(amount) AS '합계' FROM pivotTest GROUP BY uName;
        
        -- 김범수로 묶임(uName)으로 묶인다.

JSON데이터

JSON데이터 : 현대의 웹가 모바일 응용 프로그램 등과 데이터를 교환하기 위한 개방형 표준 포맷.
속성과 값으로 쌍을 이루는 것을 확인할 수 있다.

USE sqldb;
SELECT JSON_OBJECT('name',name,'height',height) AS 'JSON 값'
	FROM usertbl
    WHERE height >= 180;

SET @json='{"usertbl" :
	[
    	{"name":"임재범","height":182},
        {"name":"이승기","height":182},
        {"name":"성시경","height":186}
    ]
}';
SELECT JSON_VALID(@json) AS JSON_VALID;
SELECT JSON_SEARCH(@json, 'one','성시경') AS JSON_EXTRACT; -- 첫번째로 나오는 성시경을 찾아라, one과 all이 있음
SELECT JSON_EXTRACT(@json,'$.usertbl[2].name') AS JSON_EXTRACT;
SELECT JSON_INSERT(@json, '$usertbl[0].mDate','2009-09-09') AS JSON_INSERT;
SELECT JSON_REPLACE(@json,'$.usertbl[0].name', '홍길동') AS JSON_REPLACE; -- 0번째 name을 바꿈
SELECT JSON_REMOVE(@json,'$.usertbl[0]') AS JSON_REMOVE;

조인

조건 : 회원테이블과 구매테이블의 pk와 fk가 1:N 관계

INNER JOIN(내부 조인)

물건을 배송하기 위해 구매한 회원의 주소와 이 회원의 주소 정보를 알기 위해 주소 정보가 있는 회원 테이블과 결합하는 것 : INNER JOIN

형식

SELECT <열 목록>
FROM <첫 번째 테이블>
	INNER JOIN <두 번째 테이블>
    	ON <조인될 조건>
[WHERE 검색조건]

👇

USE sqldb;
SELECT *
	FROM buytbl
		INNER JOIN usertbl
    		ON buytbl.userID = usertbl.userID
	WHERE buytbl.userID = 'JYP';

PK와 FK가 같은지 확인하는 부분을 주로 ON으로 조건을 준다.

구매 테이블의 userID인 'JYP'를 추출하고 'JYP'와 동일한 값을 회원 테이블의 userID열에서 검색한 후 'JYP'라는 아이디를 찾으면 구매 테이블과 회원 테이블의 두 행을 결합(JOIN)한다.
MySQL 8.0.16 버전까지는 맨 아랫줄에 ORDER BY num 구문을 넣지 않아도 buytbl의 num열에 의해서 정렬되었으나 MySQL 8.0.17버전에서는 ORDER BY num 구문을 넣지 않으면 userID열로 정렬된다.

USE sqldb;
SELECT *
	FROM buytbl
		INNER JOIN usertbl
    		ON buytbl.userID = usertbl.userID
	ORDER BY num;

이렇게 정렬되어 나타난다. 열의 항목이 너무 많아서 필요한 열만 추출하는 예제를 보자.
[열을 골라와서(아이디/이름/구매물품/주소/연락처 만) 뽑고 싶음]

SELECT userID, name, prodName, addr, CONCAT(mobile, mobile2) AS '연락처'
  FROM buytbl
          INNER JOIN usertbl
              ON buytbl.userID = usertbl.userID
      ORDER BY num;

위처럼 코드를 작성한다면, 에러가 뜬다.

구매테이블열도 userID열이 있고 회원테이블에도 userID열이 있다. 테이블 두 개에 같은 열 이름이 있어서 userID가 어느쪽 열인지 알 수 없다는 오류 메시지가 뜬다.

SELECT buytbl.userID, name, prodName, addr, CONCAT(mobile, mobile2) AS '연락처'
  FROM buytbl
          INNER JOIN usertbl
              ON buytbl.userID = usertbl.userID
      ORDER BY num;

따라서 위처럼 buytbl.userID라 써서 어느 테이블에 있는 userID를 쓰는지 명시해줘야 한다.

테이블 명을 앞에다가 다 붙여주는 것이 더 안전하지만, 코드가 너무 길어질 수 있다.
이를 간편하게 하기 위해서 각 테이블에 별칭을 줄 수 있다.

SELECT B.userID, U.name, B.prodName, U.addr, CONCAT(mobile, mobile2) AS '연락처'
  FROM buytbl B
          INNER JOIN usertbl U
              ON B.userID = U.userID
      ORDER BY num;

table다음에 띄우고 별칭을 써주면 된다.

전체 회원 중 물품을 구매한 회원을 선별해보자.

SELECT DISTINCT U.userID, U.name, U.addr
  FROM usertbl U
          INNER JOIN buytbl B
              ON U.userID = B.userID
      ORDER BY U.userID;

DISTINCT를 이용해 중복을 제거한다.

세 개의 테이블의 조인 해보자.

'다대다(many-to-many)'의 관계이다.
다대다 관계는 논리적으로는 구성이 가능하지만 이를 물리적으로 구성하기 위해서는 두 테이블의 사이에 연결 테이블을 둬서 이 연결 테이블과 두 테이블이 일대다 관계를 맺도록 구성해야 한다.

SELECT S.stdName, S.addr, SC.clubName, C.roomNo
	FROM stdtbl S
    		INNER JOIN stdclubtbl SC
        		ON S.stdName = SC.stdName
        	INNER JOIN clubtbl C
        		ON SC.clubName = SC.stdName
	ORDER BY S.stdName;

첫번째 INNER JOIN한 결과를 가지고 다시 두번째 INNER JOIN을 하게 되는 것

use sqldb;
create table stdtbl
(
stdName varchar(10) NOT NULL PRIMARY KEY,
addr char(4) not null
);

create table clubtbl
(
clubName varchar(10) not null primary key,
roomNo char(4) not null
);

create table stdclubtbl
( num int auto_increment not null primary key,
  stdName varchar(10) not null,
  clubName varchar(10) not null,
  foreign key(stdName) references stdtbl(stdName),
  foreign key(clubName) references clubtbl(clubName)
);

insert into stdtbl values ('김범수','경남'), ('성시경', '서울'), ('조용필', '경기'), ('은지원', '경북'), ('바비킴','서울');
insert into clubtbl values ('수영', '101호'), ('바둑', '102호'), ('축구','103호'),('봉사','104호');
insert into stdclubtbl values (null, '김범수', '바둑'), (null, '김범수', '축구'), (null, '조용필', '축구'), (null, '은지원', '축구'), (null, '은지원', '봉사'), (null,'바비킴','봉사');

select S.stdName, S.addr, SC.clubName, C.roomNo
from stdtbl S
inner join stdclubtbl SC
		ON S.stdName = SC.stdName -- 이너 조인을 한 결과를 가지고 밑에 inner join 시작한다. 
inner join clubtbl C
		ON SC.clubName = C.clubName
	order by S.stdName;
    
select C.clubName, C.roomNo, S.stdName, S.addr
from stdtbl S
inner join stdclubtbl SC
		ON SC.stdName = S.stdName
inner join clubtbl C
		ON SC.clubName = C.clubName
	order by C.clubName;

1-inner join

select S.stdName, S.addr, SC.clubName, C.roomNo
from stdtbl S
inner join stdclubtbl SC
		ON S.stdName = SC.stdName -- 이너 조인을 한 결과를 가지고 밑에 inner join 시작한다. 
inner join clubtbl C
		ON SC.clubName = C.clubName
	order by S.stdName;

2-inner join

select C.clubName, C.roomNo, S.stdName, S.addr
from stdtbl S
inner join stdclubtbl SC
		ON SC.stdName = S.stdName
inner join clubtbl C
		ON SC.clubName = C.clubName
	order by C.clubName;

OUTER JOIN(외부조인)

조인의 조건에 만족되지 않는 행까지도 포함시키는 것

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
<LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
	ON <조인될 조건>
[WHERE 검색조건] ;

전체 회원 출력시 사용.

use sqldb;
select U.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
from usertbl U
	LEFT OUTER JOIN buytbl B -- 왼쪽 테이블의 것은 모두 출력되어야 한다.(usertbl)
    ON U.userID = B.userID
    ORDER BY U.userID;

INNER JOIN의 활용 중에서 구매한 기록이 있는 우수 회원들의 목록만 뽑아 봤지만, 이번엔 한 번도 구매한 적이 없는유령 회원의 목록을 뽑아보자.

use sqldb;
select U.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
from usertbl U
	LEFT OUTER JOIN buytbl B -- 왼쪽 테이블의 것은 모두 출력되어야 한다.(usertbl)
    ON U.userID = B.userID
    where B.prodName IS NULL
    ORDER BY U.userID;

예시2

SELECT S.stdName, S.addr, C.clubName, C.roomNo
	FROM  stdtbl S
		LEFT OUTER JOIN stdclubtbl SC
			ON SC.stdName = S.stdName
		RIGHT OUTER JOIN clubtbl C
			ON SC.clubName = C.clubName
	ORDER BY S.stdName;

SELECT C.clubName, C.roomNo, S.stdName, S.addr
	FROM stdtbl S
		LEFT OUTER JOIN stdclubtbl SC
			ON SC.stdName = S.stdName
		RIGHT OUTER JOIN clubtbl C
			ON SC.clubName = C.clubName
		ORDER BY C.clubName;

UNION
SELECT S.stdName, S.addr, C.clubname, C.roomNo
	FROM stdtbl S
		LEFT OUTER JOIN stdclubtbl SC
			ON SC.stdName = S.stdName
		RIGHT OUTER JOIN clubtbl C
			ON SC.clubName = C.clubName;

문법 및 많은 예시는 이 블로그를 참조하자.

CROSS JOIN(상호 조인)

한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능
그래서 결과 개수는 두 테이블 개수를 곱한 개수가 된다.

USE sqldb;
SELECT *
	FROM buytbl
    	CROSS JOIN usertbl;

샘플데이터 만들 때 종종 사용한다.

SELF JOIN(자체 조인)

별도의 구문이 있는 것이 아니라 자기 자신과 자기 자신이 조인한다는 의미.

USE sqldb;
SELECT *
	FROM buytbl A
		INNER JOIN buytbl B
    		ON A.userID = B.userName
	WHERE A.userID = 'JYP';

같은 테이블인데 별칭을 다르게 하면서 씀.

UNION/UNION ALL/NOT IN/IN

두 쿼리의 결과를 합친 것

UNION : 중복된 열은 제거
UNION ALL : 중복된 열까지 모두 출력

NOT IN : 뒤의 내용을 빼고 출력
IN : 첫 번째 쿼리의 결과 중에서, 두 번째 쿼리에 해당되는 것만 조회

지환

아는만큼보인다.

이전 포스트

데이터베이스-영화사이트 구축 실습

다음 포스트