[DB_MySQL] 이것이 MySQL이다 ch07 SQL 고급 4(피벗, JSON 데이터, 조인)
bobo122023. 2. 7. 19:25
728x90
반응형
1. 피벗의 구현
◎ 피벗(Pivot) 이란?
▷ 한 열에 포함된 여러 값 출력, 이를 여러 열로 변환하여 테이블 반환식 회전, 필요하면 집계까지 수행
1. uName을 기준으로 group
-- 피벗의 구현
use sqlDB;
create table pivotTest(
uName CHAR(3),
season CHAR(2),
amount int
);
desc pivotTest;
insert into pivotTest values ('김범수', '겨울', 10), ('윤종신', '여름', 15), ('김범수', '가을', 25),
('김범수', '봄', 3), ('김범수', '봄', 37), ('윤종신', '겨울', 40), ('김범수', '여름', 14),
('김범수', '겨울', 22), ('윤종신', '여름', 64);
select * from pivotTest;
truncate table pivotTest;
-- 피벗 구현 SUM()과 IF(), GROUP BY
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;
2. season을 기준으로 group
-- 계절별로 결과가 나타나게 합니다.
select season,
sum(if(uName='김범수', amount, 0)) as '김범수',
sum(if(uName='윤종신', amount, 0)) as '윤종신',
sum(amount) as '합계' from pivotTest group by season;
2. JSON 데이터
▷ JSON (JavaScript Object Notation)이란?
▷ 웹과 모바일 응용프로그램 등과 데이터 교환하기 위한 개방형 표준 포맷
▷ 속성(Key) 과 값(Value)으로 쌍을 이루며 구성
▷ JavaScript 언어에서 파생
▷ 특정한 프로그래밍 언어에 종속되어 있지 않은 독립적인 데이터 포맷
▷ 포맷이 단순하고 공개되어 있기에 거의 대부분의 프로그래밍 언어에서 쉽게 읽거나 쓸 수 있도록 코딩 가능
▷ MySQL 5.7.8부터 지원
▷ MySQL 8.0에서는 인라인 패스라 불리는
→ 연산자 및 JSON_ARRAYAGG(), JSON_OBJECTAGG(), JSON_PRETTY(), JSON_STORAGE_SIZE(),
JSON_STORAGE_FREE(), JSON_MERGE_PATCH()등의 함수가 추가됨
-- JSON 데이터 : 일반적인 데이터 내용을 JSON 타입으로 변경 출력(속성명 : 속성값 JSON 형태로 출력)
use sqldb;
select JSON_OBJECT('name', name, 'height', height) as 'JSON 값' from usertbl
where height >= 180;
-- {"name": "임재범", "height": 182}
-- {"name": "이승기", "height": 182}
-- {"name": "성시경", "height": 186} 반환
set @json = '{"usertbl" : [
{"name" : "임재범", "height" : 182},
{"name" : "이승기", "height" : 182},
{"name" : "성시경", "height" : 186}
]
}';
-- JSON 형식을 가지고 있는지 확인(만족 : 1, 불만족 : 0)
select JSON_VALID(@json) as JSON_VALID;
-- 1반환
-- 성시경의 위치를 확인(ONE : 첫번째 매칭, all : 모두)
select JSON_SEARCH(@json, 'one', '성시경') as JSON_SEARCH;
-- "$.usertbl[2].name" 반환
-- usertbl[2].name 2행의 이름 값 출력
select JSON_EXTRACT(@json, '$.usertbl[2].name') as JSON_EXTRACT;
-- "성시경" 반환
-- usertbl[0] 번째에 mdate 값 추가
select JSON_INSERT(@json, '$.usertbl[0].mDate', '2009-09-09') as JSON_INSERT;
-- {"usertbl": [{"name": "임재범", "mDate": "2009-09-09", "height": 182},
-- {"name": "이승기", "height": 182}, {"name": "성시경", "height": 186}]} 반환
-- 치환 usertbl[0].name 이름을 변경
select JSON_REPLACE(@json, '$.usertbl[0].name', '홍길동') as JSON_REPLACE;
-- {"usertbl": [{"name": "홍길동", "height": 182}, {"name": "이승기", "height": 182},
-- {"name": "성시경", "height": 186}]} 반환
-- 제거 usertbl[0]번째 값 제거
select JSON_REMOVE(@json, '$.usertbl[0]') as JSON_REMOVE;
-- {"usertbl": [{"name": "이승기", "height": 182}, {"name": "성시경", "height": 186}]} 반환
SELECT <열 목록>
FROM <첫 번째 테이블>
INNER JOIN <두 번째 테이블>
ON <조인될 조건>
[WHERE 검색조건]
◎ inner join을 이용한 결합 예제
-- 조인 : 테이블과 테이블 연결해서 데이터 출력
-- inner join : 내부 조인 (가장 많이 이용)
-- outer join : 외부 조인
-- cross join : 테스트 샘플 데이터 (대용량)
-- self join : 자신의 테이블을 다시 연결하는 방식 (조직도)
use sqldb;
select * from usertbl;
select * from buytbl;
-- usertbl에서는 사용자의 물품 구매 내역을 알지 못하고
-- buytbl에서는 사용자의 주소 및 연락처를 알지 못한다.
select * from buytbl
inner join usertbl
on buytbl.userID = usertbl.userID -- buytbl, usertbl 내에 userID가 같은 부분만 매칭
where buytbl.userID = 'JYP';
-- 8, JYP, 모니터, 전자, 540, 1, JYP, 조용필, 1950, 경기, 011, 4444444, 166, 2009-04-04 반환
select * from buytbl
inner join usertbl
on buytbl.userID = usertbl.userID
order by buytbl.userID;
-- 정확한 기준이 없으니 전체 다 나타납니다.
select b.userID, u.name, b.prodName, u.addr, concat(u.mobile1, u.mobile2) as '연락처'
from buytbl b
inner join usertbl u
on b.userID = u.userID
order by b.userID;
-- b.userID, u.name, b.prodName, u.addr, 연락처만 테이블로 출력됩니다.
◎ INNER JOIN으로 3개의 테이블 연결 예제
-- 3개의 테이블을 조인 p.281
-- 학생 테이블(이름, 지역), 학생 동아리 테이블(일련번호, 이름, 동아리명), 동아리 테이블(동아리명, 동아리방)
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,
-- 외래키를 이용해서 stdName를 stdTbl.stdName과 관계 설정
FOREIGN KEY(stdName) REFERENCES stdTbl(stdName),
-- 외래키를 이용해서 clubName를 clubTbl.clubName과 관계 설정
FOREIGN KEY(clubName) REFERENCES clubTbl(clubName)
);
desc stdTbl;
desc clubTbl;
desc stdclubTbl;
-- PRI : 기본키, MUL : 외래키
insert into stdtbl values ('김범수', '경남'), ('성시경', '서울'),
('조용필', '경기'), ('은지원', '경북'), ('바비킴', '서울');
insert into clubtbl values ('수영', '101호'), ('바둑', '102호'),
('축구', '103호'), ('봉사', '104호');
insert into stdclubtbl values (NULL, '김범수', '바둑'), (NULL, '김범수', '축구'),
(NULL, '조용필', '축구'), (NULL, '은지원', '축구'),
(NULL, '은지원', '봉사'), (NULL, '바비킴', '봉사');
select * from stdtbl;
select * from clubtbl;
select * from stdclubtbl;
-- 학생 테이블, 동아리 테이블, 학생동아리 테이블을 이용해서
-- 학생을 기준으로 학생이름/지역/가입한 동아리/동아리방 출력
select S.stdName, S.addr, C.clubName, C.roomNo
from stdTbl S
inner join stdclubtbl SC
ON S.stdName = SC.stdName
inner join clubtbl C
on SC.clubName = C.clubName
order by S.stdName;
-- 반환 결과 :
-- 김범수 경남 바둑 102호
-- 김범수 경남 축구 103호
-- 바비킴 서울 봉사 104호
-- 은지원 경북 봉사 104호
-- 은지원 경북 축구 103호
-- 조용필 경기 축구 103호
-- 동아리를 기준으로 동아리/동아리방/학생이름/지역 출력
select C.clubName, C.roomNo, S.stdName, S.addr
from clubtbl C
inner join stdclubtbl SC
on SC.clubName = C.clubName
inner join stdtbl S
on SC.stdName = S.stdName
order by C.clubName;
-- C.clubName, C.roomNo, S.stdName, S.addr 순서대로 출력
-- 회원테이블, 물품테이블, 구매테이블
▶ JOIN만 써도 INNER JOIN으로 인식함
-- 구매 이력이 있는 회원 출력 userID, name, addr
select distinct U.userid, U.name, U.addr
from usertbl U
join buytbl B
on U.userid = B.userid
order by U.userid;
-- 반환 결과 :
-- BBK 바비킴 서울
-- EJW 은지원 경북
-- JYP 조용필 경기
-- KBS 김범수 경남
-- SSK 성시경 서울
-- EXISTS(내용 있는지 없는지 판별) : EXISTS를 이용하여 위와 같은 동일한 결과 나타내기
select distinct U.userid, U.name, U.addr
from usertbl U
where EXISTS(
select * from buytbl B
where U.userid = B.userid
);
둘 다 동일한 결과를 출력합니다.
◎ OUTER JOIN(외부 조인)
▷ 조인의 조건에 만족되지 않는 행까지도 포함시키는 것
▷ LEFT OUTER JOIN
▶ 왼쪽 테이블의 것은 모두 출력되어야 합니다.
▶ 줄여서 LEFT JOIN으로 쓸 수 있습니다.
▷ RIGHT OUTER 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
ORDER BY U.userID;
select U.userID, U.name, B.prodName, U.addr, concat(U.mobile1, U.mobile2) as '연락처'
from buyTbl B
RIGHT OUTER JOIN userTbl U -- userTbl에 있는 모든 행에 있는 부분을 다 출력
ON U.userID = B.userID
ORDER BY U.userID;
select * from buyTbl;
select * from userTbl;
select U.userID, U.name, B.prodName, U.addr, concat(U.mobile1, U.mobile2) as '연락처'
from userTbl U
RIGHT OUTER JOIN buyTbl B -- userTbl에 있는 모든 행에 있는 부분을 다 출력
ON U.userID = B.userID
ORDER BY U.userID;
select U.userID, U.name, B.prodName, U.addr, concat(U.mobile1, U.mobile2) as '연락처'
from userTbl U
LEFT OUTER JOIN buyTbl B -- userTbl에 있는 모든 행에 null 모두 출력
ON U.userID = B.userID
WHERE concat(U.mobile1, U.mobile2) IS NULL
ORDER BY U.userID;
◎ FULL OUTER JOIN : LEFT + RIGHT OUTER JOIN
-- FULL OUTER JOIN : LEFT + RIGHT OUTER JOIN
-- 동아리에 가입을 안한 학생들 목록 출력
select S.stdName, S.addr, C.clubName, C.roomNo
from stdtbl S
LEFT OUTER JOIN stdclubtbl SC
ON S.stdName = SC.stdName
LEFT OUTER JOIN clubtbl C
ON SC.clubName = C.clubName
ORDER BY S.stdName;
-- 반환값 :
-- 김범수 경남 바둑 102호
-- 김범수 경남 축구 103호
-- 바비킴 서울 봉사 104호
-- 성시경 서울
-- 은지원 경북 축구 103호
-- 은지원 경북 봉사 104호
-- 조용필 경기 축구 103호
-- 학생이 한명도 가입하지 않은 동아리에 대한 목록까지 출력
select C.clubName, C.roomNo, S.stdName, S.addr
from stdtbl S
LEFT OUTER JOIN stdclubtbl SC
ON S.stdName = SC.stdName
LEFT OUTER JOIN clubtbl C
ON SC.clubName = C.clubName
ORDER BY C.clubName;
-- 반환값
-- 성시경 서울
-- 바둑 102호 김범수 경남
-- 봉사 104호 바비킴 서울
-- 봉사 104호 은지원 경북
-- 축구 103호 김범수 경남
-- 축구 103호 은지원 경북
-- 축구 103호 조용필 경기
-- 두 쿼리 결과를 하나로 출력이 되게 UNION
-- 위의 두 출력값이 하나로 합쳐지게 됩니다.
select S.stdName, S.addr, C.clubName, C.roomNo
from stdtbl S
LEFT OUTER JOIN stdclubtbl SC
ON S.stdName = SC.stdName
LEFT OUTER JOIN clubtbl C
ON SC.clubName = C.clubName
UNION -- 출력하고자하는 컬럼의 순서와 개수 동일
select C.clubName, C.roomNo, S.stdName, S.addr
from stdtbl S
LEFT OUTER JOIN stdclubtbl SC
ON S.stdName = SC.stdName
LEFT OUTER JOIN clubtbl C
ON SC.clubName = C.clubName;
◎ CROSS JOIN(상호 조인)
▷ 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능
▷ CROSS JOIN의 결과 개수 : 두 테이블 개수를 곱한 개수
▷ 테스트로 사용할 많은 용량의 데이터를 새성할 때 주로 사용
▷ ON 구문을 사용할 수 없음
▷ 대량의 데터를 생성하면 시스템이 다운되거나 디스크 용량이 모두 찰 수 있어 COUNT(*) 함수로 개수만 카운트
-- CROSS JOIN(상호 조인) : 대량의 데이터를 만들 때(실험용 데이터)
select * from buytbl;
select * from usertbl;
select * from buytbl
cross join usertbl; -- 12 * 10 = 120건
use employees;
select count(*) as '데이터개수'
from employees
cross join titles;
-- 133003039392 반환
◎ SELF JOIN(자체 조인)
▷ 자기 자신과 자기 자신이 조인한다는 의미
▶ 대표적인 예(조직도와 관련된 테이블)
create table empTbl (emp char(3), manager char(3), empTel VARCHAR(8));
INSERT INTO empTbl values ('나사장', null, '0000');
INSERT INTO empTbl values ('김재무', '나사장', '2222');
INSERT INTO empTbl values ('김부장', '김재무', '2222-1');
INSERT INTO empTbl values ('이부장', '김재무', '2222-2');
INSERT INTO empTbl values ('우대리', '이부장', '2222-2-1');
INSERT INTO empTbl values ('지사원', '이부장', '2222-2-2');
INSERT INTO empTbl values ('이영업', '나사장', '1111');
INSERT INTO empTbl values ('한과장', '이영업', '1111-1');
INSERT INTO empTbl values ('최정보', '나사장', '3333');
INSERT INTO empTbl values ('윤차장', '최정보', '3333-1');
INSERT INTO empTbl values ('이주임', '윤차장', '3333-1-1');
select * from empTbl;
위의 코드는 테이블 생성 및 값 입력입니다.
-- 우대리 상관의 연락처 확인
select A.emp as '부하직원', A.empTel as '부하직원연락처', B.emp as '직속상관', B.empTel as '직속상관연락처'
from empTbl A -- 부하직원 기준
inner join empTbl B -- 직속상관 기준
on A.manager = B.emp
where A.emp = '우대리';
-- 우대리, 2222-2-1, 이부장, 2222-2 반환
-- 우대리 상관의 연락처 확인
select A.emp as '직원', B.emp as '직속부하직원', B.empTel as '직속부하직원연락처'
from empTbl A -- 직속상관 기준
inner join empTbl B -- 부하직원 기준
on A.emp = B.manager
where A.emp = '나사장';
-- 반환값
-- 나사장 김재무 2222
-- 나사장 이영업 1111
-- 나사장 최정보 3333
◎ UNION / UNION ALL / NOT IN / IN
▷ 두 쿼리의 결과를 행으로 합치는 것
1. UNION, UNION ALL
-- UNION 2개의 쿼리 결과를 하나로 합칠때
-- UNION 중복값 제거
-- UNION ALL 중복값 제거 없이 출력
use sqldb;
select stdName, addr from stdtbl
union all
select clubName, roomNo from clubtbl;
-- 반환값 :
-- 김범수 경남
-- 바비킴 서울
-- 성시경 서울
-- 은지원 경북
-- 조용필 경기
-- 바둑 102호
-- 봉사 104호
-- 수영 101호
-- 축구 103호
2. NOT IN
-- NOT IN : 뒤에 나오는 결과 값을 제외한 것만 실행
-- 전화번호가 없는 사람에 대해서 제외하고 출력
select name, concat(mobile1, mobile2) as '전화번호' from usertbl
where name not in (select name from usertbl where mobile1 is null);
-- 반환값 :
-- 바비킴 0100000000
-- 은지원 0118888888
-- 조관우 0189999999
-- 조용필 0114444444
-- 김범수 0112222222
-- 김경호 0193333333
-- 임재범 0166666666
-- 이승기 0111111111
-- 전화번호가 없는 사람 : 성시경, 윤종신
select name from usertbl where mobile1 is null;
3. IN
-- IN : 뒤에 나오는 결과 값을 앞에 대입해서 실행
-- 전화번호가 없는 사람만 출력
select name, concat(mobile1, mobile2) as '전화번호' from usertbl
where name in (select name from usertbl where mobile1 is null);
select name, concat(mobile1, mobile2) as '전화번호' from usertbl
where name in ('윤종신', '성시경');
-- 반환값 :
-- 성시경
-- 윤종신
피벗, JSON 데이터, 조인 등을 통해서 데이터를 연결하고 필요한 값들만 출력해보았습니다.
