Frogrin's Tech Blog

분석 목표

E-commerce Data를 분석하여 상품별 주문 건수와 재구매율을 파악하여 사이트 운영에 참고하고자 함.
 

데이터 설명

- 데이터 자료는 포스팅 아래 쪽에 첨부 -

• Orders 테이블

• Order_products__prior 테이블

• Products 테이블

• Departments 테이블

• Aisles 테이블

연습문제

-- Q1) 전체 주문 건수를 구하라.
select count(order_id) from orders;

-- Q2) 구매자의 수를 구하라.
select count(distinct user_id) from orders;

-- Q3) 상품이름별로 몇 건의 주문이 있었는지 체크!!!!
select P.product_name, count(OP.order_id) `CNT`
from order_products__prior OP left join products P
	on OP.product_id = P.product_id
group by P.product_name
order by `CNT` desc;

-- Q4) 장바구니에 제일 먼저 담는 상품 상위 10개의 상품번호, 상품이름, 처음 담긴 횟수를 출력하라.
select A.*, P.product_name
from (select product_id, sum(case when add_to_cart_order = 1 then 1 else 0 end) `1st_CNT`
from order_products__prior
group by product_id)A left join products P
	on A.product_id = P.product_id
order by `1st_CNT` desc
limit 10;

-- Q5) 시간대 별로 주문 건수를 구하라.
select order_hour_of_day, count(order_id) `CNT`
from orders
group by 1
order by 1;

-- Q6) 첫 구매 후에 다음 구매까지 걸리는 평균 일수를 구하라.
select avg(days_since_prior_order) `Avg_Recency`
from orders
where order_number = 2;

-- Q7) 1회 주문시 평균 몇 '종류'의 상품을 같이 주문하는지 구하라.
select count(product_id)/count(distinct order_id) `UPT`
from order_products__prior;

-- Q8) 고객 1명당 평균 주문 건수를 구하라.
select count(order_id)/count(distinct user_id) `AVG_CNT`
from orders;

-- Q9) 상품별 재구매율을 구하라.
select product_id, sum(case when reordered = 1 then 1 else 0 end)/count(product_id) `Re_Ratio`
from order_products__prior
group by product_id;

-- Q10) 위의 결과에서 재구매율이 높은 순서대로 순위를 매겨라.
select A.*, row_number() over(order by `Re_Ratio` desc) `RNK`
from (select product_id, sum(case when reordered = 1 then 1 else 0 end)/count(product_id) `Re_Ratio`
from order_products__prior
group by product_id)A;

-- Q11) 위의 결과에서 상위 랭킹 100위 이내인 상품의 이름을 함께 출력하라.
select B.* , P.product_name
from (select A.*, row_number() over(order by `Re_Ratio` desc) `RNK`
from (select product_id, sum(case when reordered = 1 then 1 else 0 end)/count(product_id) `Re_Ratio`
from order_products__prior
group by product_id)A)B left join Products P
	on B.product_id = P.product_id
having `RNK` <= 100
order by `RNK`;

데이터 자료

 
데이터 출처 : Instacart Market Basket Analysis | Kaggle

분석 목표

의류 쇼핑몰 리뷰 데이터를 분석하여 상품 분류별, 연령대별 평점과 리뷰 양상을 파악하고 분석 결과를 바탕으로 쇼핑몰 매출 향상에 대한 방향을 설정하려고 함.
 

컬럼 설명

- 데이터 자료는 포스팅 아래 쪽에 첨부 -

연습문제

-- Q1) Division Name 별로 평점의 평균을 구하라. 단, 평균 평점의 내림차순으로 정렬하라.
select `Division Name`, avg(Rating) `Avg Rate` from dataset2 group by `Division Name` order by `Avg Rate` desc; 

-- Q2) Department Name별로 평점의 평균을 구하라. 단, 평균 평점의 내림차순으로 정렬하라.
select `Department Name`, avg(Rating) `Avg Rate` from dataset2 group by `Department Name` order by `Avg Rate` desc; 

-- Q3) Department Name의 값이 Trend인 항목에 대해서 평점이 3점 이하인 데이터만 출력하라.
select * from dataset2 where `Department Name` = 'Trend' and c <= 3;

-- Q4) 위에서 얻은 결과에 연령대 정보를 추가하라.
select *, case
	when age < 20 then '10대 이하'
    when age < 30 then '20대'
    when age < 40 then '30대'
    when age < 50 then '40대'
    when age < 60 then '50대'
    when age < 70 then '60대'
    else '기타'
end `Age Band`
from dataset2 where `Department Name` = 'Trend' and Rating <= 3;

select *, floor(age/10)*10 `Age Band`
from dataset2 where `Department Name` = 'Trend' and Rating <= 3;

-- Q5) Trend 항목에 3점 이하의 평점을 매긴 건수를 연령대별로 구하라.
select floor(age/10)*10 `Age Band`, count(1) `CNT`
from dataset2
where `Department Name` = 'Trend' and Rating <= 3
group by `Age Band`
order by `CNT` desc;

-- Q6) Trend 항목에 대한 3점 이하의 리뷰 중에서 50대가 남긴 리뷰 10개만 출력하라.
select `Review Text`
from dataset2
where `Department Name` = 'Trend' and Rating <= 3 and age between 50 and 59
limit 10;

-- Q7) Deparment Name 과 ClothingID의 항목별로 평점의 평균을 출력하라. 단, 평균 평점이 높은 것부터 순서대로 정렬하라.
select `Department Name`, `Clothing ID`, avg(Rating) `Avg Rate`
	from dataset2
group by 1, 2
order by 3 desc;

-- Q8) 위의 결과에서 평균 평점이 높은 것부터 순위를 매겨라. 단, 순위는 Department별로 독립적으로 부여하라.
select *, rank() over(partition by `Department Name` order by `Avg Rate` desc) `Rank`
	from (select `Department Name`, `Clothing ID`, avg(Rating) `Avg Rate`
	from dataset2
group by 1, 2)A;

-- Q9) 위의 결과에서 Department 별로 평균 평점 순위가 10위 이내인 데이터만 출력하라.
select *
from (select *, rank() over(partition by `Department Name` order by `Avg Rate` desc) `Rank`
	from (select `Department Name`, `Clothing ID`, avg(Rating) `Avg Rate`
	from dataset2
group by 1, 2)A)B
where `Rank` <= 10
order by `Department Name`;

-- Q10) Department 와 연령대 항목 별로 평점의 평균을 구하라.
select `Department Name`, floor(age/10)*10 `Age Band`, avg(Rating) `Avg Rate`
from dataset2
group by 1, 2
order by 3 desc;

-- Q11) 위의 결과에서 연령대별로 평점 평균이 높은 순서대로 순위를 매겨라.
select *, rank() over(partition by `Age Band` order by `Avg Rate` desc) `Rank`
from(select `Department Name`, floor(age/10)*10 `Age Band`, avg(Rating) `Avg Rate`
from dataset2
group by 1, 2)A
order by `Department Name`;

-- Q12) 리뷰에 size라는 단어가 있으면 1, 없으면 0을 출력하는 필드를 생성하라.
select `Review Text`,
case when `Review Text` like '%size%' then 1
else 0
end
from dataset2;

-- Q13) 전체 리뷰 데이터 수와 'size'가 언급된 리뷰 데이터 수를 구하라.
select count(1) `Total`, sum(case when `Review Text` like '%size%' then 1
else 0
end) `Size_Included`
from dataset2;

-- Q14) 'size', 'large', 'loose', 'small', 'tight'가 각각 언급된 리뷰수와 전체 리뷰수를 구하라.
select 
	sum(case when `Review Text` like "%size%" then 1 else 0 end) as `Size_Included`,
    sum(case when `Review Text` like "%large%" then 1 else 0 end) as `Large_Included`,
    sum(case when `Review Text` like "%loose%" then 1 else 0 end) as `Loose_Included`,
    sum(case when `Review Text` like "%small%" then 1 else 0 end) as `Small_Included`,
    sum(case when `Review Text` like "%tight%" then 1 else 0 end) as `Tight_Included`,
    sum(1) `Total`
from dataset2;

-- Q15) 위의 결과를 Department 별로 출력하라.
select  `Department Name`,
	sum(case when `Review Text` like "%size%" then 1 else 0 end) as `Size_Included`,
    sum(case when `Review Text` like "%large%" then 1 else 0 end) as `Large_Included`,
    sum(case when `Review Text` like "%loose%" then 1 else 0 end) as `Loose_Included`,
    sum(case when `Review Text` like "%small%" then 1 else 0 end) as `Small_Included`,
    sum(case when `Review Text` like "%tight%" then 1 else 0 end) as `Tight_Included`,
    sum(1) `Total`
from dataset2
group by `Department Name`;

실제 데이터를 가지고 EDA를 전개한다면 추출할 정보들을 스스로 결정해야 한다.
보유하고 있는 도메인 지식을 바탕으로 주어진 데이터에서 의미있게 활용할 수 있는 부분이
어떤 것인지 고민하고 SQL 쿼리문을 작성하여 결과 도출까지 해내야 한다.

데이터 자료

 
데이터 출처 : https://www.kaggle.com/datasets/nicapotato/womens-ecommerce-clothing-reviews

데이터 생성

CREATE TABLE tCar
(
    car VARCHAR(30) NOT NULL,   	 -- 이름
    capacity INT NOT NULL,   		 -- 배기량
    price INT NOT NULL,   		 -- 가격
    maker VARCHAR(30) NOT NULL   	 -- 제조사
);

INSERT INTO tCar (car, capacity, price, maker) VALUES ('소나타', 2000, 2500, '현대');
INSERT INTO tCar (car, capacity, price, maker) VALUES ('티볼리', 1600, 2300, '쌍용');
INSERT INTO tCar (car, capacity, price, maker) VALUES ('A8', 3000, 4800, 'Audi');
INSERT INTO tCar (car, capacity, price, maker) VALUES ('SM5', 2000, 2600, '삼성');

CREATE TABLE tMaker
(
    maker VARCHAR(30) NOT NULL,   	 -- 회사
    factory CHAR(10) NOT NULL,   	 -- 공장
    domestic CHAR(1) NOT NULL   	 -- 국산 여부. Y/N
);

INSERT INTO tMaker (maker, factory, domestic) VALUES ('현대', '부산', 'y');
INSERT INTO tMaker (maker, factory, domestic) VALUES ('쌍용', '청주', 'y');
INSERT INTO tMaker (maker, factory, domestic) VALUES ('Audi', '독일', 'n');
INSERT INTO tMaker (maker, factory, domestic) VALUES ('기아', '서울', 'y');

연습문제

-- 1) 두 테이블에 대한 cross join을 실시하라
select * from tCar cross join tMaker;

-- 2) 위의 결과에서 tCar의 회사명과 tMaker의 회사명이 일치하는 것만 표시하라.
select * from tCar cross join tMaker
where tCar.maker = tMaker.maker;

-- 3) 위의 결과에서 차 이름, 가격, 제조사, 공장만 표시하라.
select C.car, C.price, C.maker, M.factory from tCar C cross join tMaker M
where C.maker = M.maker;

-- 4) 2)의 결과에서 자동차 테이블의 모든 결과를 출력하고, 회사 정보는 공장 위치만 출력하라.
select C.*, M.factory from tCar C cross join tMaker M
where C.maker = M.maker;

-- 5) inner join을 활용해서 양쪽 테이블에 모두 있는 정보를 출력하라.
select * from tCar C inner join tMaker M
on C.maker = M.maker;

-- 6) 모든 자동차의 정보를 출력하고 제조사 정보가 있다면 덧붙여라.
select * from tCar C left join tMaker M
on C.maker = M.maker;

-- 6) 모든 제조사의 정보를 출력하고 자동차 정보가 있다면 덧붙여라.
select * from tCar C right join tMaker M
on C.maker = M.maker;

JOIN을 통해 데이터를 조회하고자 하는 경우 어떤 테이블을 기준으로 할 지,
해당 컬럼은 어느 테이블에서 가져온 것인지 소속을 명확히 해주어야 한다.

데이터 생성

CREATE DATABASE test_01;
USE test_01;

CREATE TABLE tCity
(
	name CHAR(10) PRIMARY KEY,
	area INT NULL ,
	popu INT NULL ,
	metro CHAR(1) NOT NULL,
	region CHAR(6) NOT NULL
);

INSERT INTO tCity VALUES ('서울', 605,974, 'y', '경기');
INSERT INTO tCity VALUES ('부산', 765,342, 'y', '경상');
INSERT INTO tCity VALUES ('오산', 42,21, 'n', '경기');
INSERT INTO tCity VALUES ('청주', 940,83, 'n', '충청');
INSERT INTO tCity VALUES ('전주', 205,65, 'n', '전라');
INSERT INTO tCity VALUES ('순천', 910,27, 'n', '전라');
INSERT INTO tCity VALUES ('춘천', 1116,27, 'n', '강원');
INSERT INTO tCity VALUES ('홍천', 1819,7, 'n', '강원');

SELECT * FROM tCity;

CREATE TABLE tStaff
(
	name CHAR (15) PRIMARY KEY,
	depart CHAR (10) NOT NULL,
	gender CHAR(3) NOT NULL,
	joindate DATE NOT NULL,
	grade CHAR(10) NOT NULL,
	salary INT NOT NULL,
	score DECIMAL(5,2) NULL
);

INSERT INTO tStaff VALUES ('김유신', '총무부', '남', '2000-2-3', '이사', 420, 88.8);
INSERT INTO tStaff VALUES ('유관순', '영업부', '여', '2009-3-1', '과장', 380, NULL);
INSERT INTO tStaff VALUES ('안중근', '인사과', '남', '2012-5-5', '대리', 256, 76.5);
INSERT INTO tStaff VALUES ('윤봉길', '영업부', '남', '2015-8-15', '과장', 350, 71.25);
INSERT INTO tStaff VALUES ('강감찬', '영업부', '남', '2018-10-9', '사원', 320, 56.0);
INSERT INTO tStaff VALUES ('정몽주', '총무부', '남', '2010-9-16', '대리', 370, 89.5);
INSERT INTO tStaff VALUES ('허난설헌', '인사과', '여', '2020-1-5', '사원', 285, 44.5);
INSERT INTO tStaff VALUES ('신사임당', '영업부', '여', '2013-6-19', '부장', 400, 92.0);
INSERT INTO tStaff VALUES ('성삼문', '영업부', '남', '2014-6-8', '대리', 285, 87.75);
INSERT INTO tStaff VALUES ('논개', '인사과', '여', '2010-9-16', '대리', 340, 46.2);
INSERT INTO tStaff VALUES ('황진이', '인사과', '여', '2012-5-5', '사원', 275, 52.5);
INSERT INTO tStaff VALUES ('이율곡', '총무부', '남', '2016-3-8', '과장', 385, 65.4);
INSERT INTO tStaff VALUES ('이사부', '총무부', '남', '2000-2-3', '대리', 375, 50);
INSERT INTO tStaff VALUES ('안창호', '영업부', '남', '2015-8-15', '사원', 370, 74.2);
INSERT INTO tStaff VALUES ('을지문덕', '영업부', '남', '2019-6-29', '사원', 330, NULL);
INSERT INTO tStaff VALUES ('정약용', '총무부', '남', '2020-3-14', '과장', 380, 69.8);
INSERT INTO tStaff VALUES ('홍길동', '인사과', '남', '2019-8-8', '차장', 380, 77.7);
INSERT INTO tStaff VALUES ('대조영', '총무부', '남', '2020-7-7', '차장', 290, 49.9);
INSERT INTO tStaff VALUES ('장보고', '인사과', '남', '2005-4-1', '부장', 440, 58.3);
INSERT INTO tStaff VALUES ('선덕여왕', '인사과', '여', '2017-8-3', '사원', 315, 45.1);

연습문제

-- 1. tCity의 모든 값들을 확인하라.
select * from tCity;

-- 2.  tStaff의 모든 값들을 확인하라.
select * from tStaff;

-- 3. tCity의 도시 이름과 인구에 대한 정보를 확인하라.
select name, popu from tCity;

-- 4. tCity의 도시이름, 지역, 면적에 대한 정보를 확인하라.
select name, region, area from tCity;

-- 5.  tStaff의 이름과 월급에 대한 정보를 확인하라.
select name, salary from tStaff;

-- 6. 직원테이블에서 이름, 부서, 직급만 출력하라.
select name, depart, grade from tStaff;

-- 7. 도시테이블에서 도시명, 면접(제곱km) 인구(만명)으로 이름이 보이도록 출력하라.
select name `도시명`, area `면적(제곱km)`, popu `인구(만명)` from tCity;

-- 8. 도시테이블에서 name, popu 값에 10000을 곱해서 인구(명)으로 이름이 보이도록 출력하라.
select name, popu * 10000 `인구(명)` from tCity;

-- 9. 도시테이블에서 이름, 면적, 인구와 인구밀도라는 이름으로 (기존의 popu * 10000 / area 로 계산이 되는)것을 보고 나타내라.
select name `이름`, area `면적`, popu `인구`, popu * 10000 / area `인구밀도` from tCity;

-- 10. 도시테이블에서 면적이 1000제곱키로미터 이상인 도시만 출력하라.
select * from tCity where area >= 1000; 

-- 11. 도시테이블에서 면적이 1000제곱키로미터 이상인 도시의 이름과 면적을 출력하라.
select name, area from tCity where area >= 1000; 

-- 12. 인구가 10만명 미만의 도시의 이름을 출력하라.
select name from tCity where popu < 10;

-- 13. 전라도에 있는 도시의 정보를 출력하라.
select * from tCity where region = '전라';

-- 14. 월급이 400만원 이상인 직원의 이름을 출력하라.
select name from tStaff where salary >= 400;

-- 15. 스탭의 테이블에서 SCORE의 값이 NULL인 정보를 출력하라.
select * from tStaff where score is null;

-- 16. 스탭의 테이블에서 SCORE의 값이 있는 사람들의 정보를 출력하라.
select * from tStaff where score is not null;

-- 17. 도시테이블에서 인구가 100만이상이면서, 면적이 700제곱키로 이상인 도시를 찾아라.
select * from tCity where popu >= 100 and area >= 700;

-- 18. 도시테이블에서 경기권 도시 중에서 인구가 50만명 이상이거나 또는 경기원이 아니고 인구가 50만보다 적더라도 면적이 500이상인 도시를 찾아라.
select * from tCity where (region = '경기' and popu >= 50) or (region != '경기' and popu < 50 and area >= 500);

-- 19.  직원 목록에서 월급이 300미만이면서 성취도는 60 이상인 직원이 누구인지 찾아라.
select * from tStaff where salary < 300 and score >= 60;

-- 20. 영업무의 여직원 분들의 이름을 찾아라.
select * from tStaff where depart = '영업부' and gender = '여';

-- 21.  도시 이름에 ‘천’이 들어가는 도시들을 찾아라.
select * from tCity where name like '%천%';

-- 22. 직원 목록에서 성이 “정”씨인 사람들을 찾아라.
select * from tStaff where name like '정%';

-- 23.  이름에 “신”자가 포함된 직원을 찾아라.
select * from tStaff where name like '%신%';

-- 24.  인구가 50~100만 사이인 도시를 찾아라.
select * from tCity where popu between 50 and 100;

-- 25. 직원들 중에서 입사일이 2015년부터 2018년 사이의 분들을 찾아라.
select * from tStaff where joindate between '2015-01-01' and '2018-12-31';

-- 26. 면적인 50~1000사이의 도시의 목록을 조사하라.
select * from tCity where area between 50 and 1000;

-- 27. 월급이 200만원대의 직원들을 조사하라.
select * from tStaff where salary between 200 and 299.9999;

-- 28. 지역이 경상/전라인 모든 도시를 찾아라.
select * from tCity where region in ('경상', '전라');

-- 29. 인구가 적은 도시부터 출력하라.
select * from tCity order by popu;

-- 30. 지역, 도시이름, 면적, 인구에 대한 것을 지역과 도시 이름에 대해서 정렬하라.
select region, name, area, popu from tCity order by region, name;

-- 31. 면적에 의해서 도시들의 정보들을 정렬하라.
select * from tCity order by area desc;

-- 32.  도시이름을 인구수에 따라서 도시의 이름만 출력하라.
select name from tCity order by popu desc;

-- 33. 경기도에 있는 도시만 골라서 면적별로 그 도시의 정보들을 출력하라.
select * from tCity where region = '경기' order by area desc;

-- 34. 직원 목록을 월급이 적은 사람부터 순서대로 출력하되, 월급이 같다면 성취도가 높은 사람을 먼저 출력하라.
select * from tStaff order by salary asc, score desc;

-- 35. 영업부 직원을 먼저 입사한 순서대로 정렬하라.
select * from tStaff where depart = '영업부' order by joindate;

CASE

• 조건에 따라 분기
• 다중 분기
• 조건에 맞는 WHEN이 여러개더라도 먼저 조건이 만족하는 WHEN 처리됨
• SELECT문에서 많이 사용됨

CASE
WHEN 조건 THEN 조건을 만족할 때 적용할 내용
ELSE 조건을 만족하지 않을 때 적용할 내용
END

Q1. buytbl에서 구매액이 1500원 이상인 사람은 최우수 고객, 1000원 이상인 사람은 우수고객, 1원 이상인 사람은 일반고객, 구매내역이 없는 고객은 유령회원으로 등급을 매기려고 한다. 회원 아이디, 이름, 총 구매액, 분류한 등급을 총 구매액이 높은 순서대로 출력하라.

select U.userID, U.name, sum(price*amount) Total_Price,
	case
    		when sum(price*amount) >= 1500 then 'VVIP'
		when sum(price*amount) >= 1000 then 'VIP'
		when sum(price*amount) >= 1 then 'Basic'
		else 'Ghost'
	end CustomerClass
from usertbl U left join buytbl B
    on B.userID = U.userID
    group by B.userID, U.name
    order by Total_Price desc;​

when 안에 별칭을 사용하면 에러가 발생, 그렇다면 별칭을 사용할 수 있는 다른 방법은?
>> 아래와 같이 서브쿼리문 사용!

select *,
	case
		when Total_Price >= 1500 then 'VVIP'
		when Total_Price >= 1000 then 'VIP'
		when Total_Price >= 1 then 'Basic'
		else 'Ghost'
	end CustomerClass
from (select U.userID, U.name, sum(price*amount) Total_Price
	from usertbl U left join buytbl B
    on B.userID = U.userID
    group by B.userID, U.name
    order by Total_Price desc)A;

>> 여기에서 A는 삽입된 서브쿼리문의 별칭, MySQL은 서브쿼리를 참조할 때 무의미한 별칭이라도 꼭 붙여줘야 함

Reference : 이것이 MySQL이다

조인(Join)

• 두개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것
• INNER JOIN, OUTER JOIN, CROSS JOIN, SELF JOIN 등이 있음.

INNER JOIN(내부 조인)

• 조인 중에서 가장 많이 사용됨.
• 일반적으로 join이라고 얘기하면 inner join을 말함.
• inner를 생략하고 join이라고만 쓰면 Inner Join으로 인식함.

SELECT 열1, 열2, ...
FROM 테이블1
INNER JOIN 테이블2
ON 조인될 조건
WHERE 검색조건 ;

Q1. 전체 회원들이 구매한 목록 중 아이디, 이름, 구매물품, 주소, 연락처를 아이디 순서대로 출력하라.

select B.userID, U.name, B.prodName, U.addr, concat(U.mobile1 + U.mobile2) phonenumber
	from buytbl B
    inner join usertbl U
    on B.userID = U.userID
    order by U.userID;

Q2. 회원 테이블을 기준으로 아이디가 JYP인 회원이 구매한 물건의 목록을 출력하라.

select B.userID, U.name, B.prodName, U.addr, concat(U.mobile1 + U.mobile2) phonenumber
	from buytbl B
    inner join usertbl U
    on B.userID = U.userID
    where B.userID = 'JYP';

Q3. 쇼핑몰에서 한 번이라도 구매한 기록이 있는 회원의 아이디, 이름, 주소를 출력하라.

select distinct U.userID, U.name, U.addr
	from usertbl U
    inner join buytbl B
    on B.userID = U.userID
    order by U.userID;​

Q4. 학생 정보, 동아리 정보, 학생들의 동아리 정보가 각각 담긴 3개의 테이블을 이용해서 학생이름, 지역, 가입한 동아리, 동아리방을 출력하라. 단, 학생이름 사전 순으로 정렬하라.

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 = C.clubName
	order by S.stdname;​

OUTER JOIN(외부 조인)

• 조인의 조건에 만족되지 않는 행까지도 포함시키는 것
• LEFT OUTER JOIN
  • 왼쪽 테이블을 기준으로 오른쪽 테이블에서 추가할 내용이 있으면 함께 출력
• RIGHT OUTER JOIN
  • 오른쪽 테이블을 기준으로 왼쪽 테이블에서 추가할 내용이 있으면 함께 출력
• OUTER는 생략 가능
• JOIN 후 기준이 되는 테이블의 행이 늘어날 수는 있지만 기준 테이블의 데이터가 누락되는 일은 없음

SELECT 열1, 열2, ...
FROM 테이블1
LEFT/RIGHT (OUTER) JOIN 테이블2
ON 조인될 조건
WHERE 검색조건 ;

Q1. 회원 테이블과 구매 테이블이 주어졌을 때, 회원 아이디, 회원 이름, 구매 품목 이름, 회원 주소, 회원 연락처를 출력하라. 단, 회원 아이디 순서대로 출력하라.

select U.userID, U.name, B.prodName, U.addr, concat(U.mobile1, U.mobile2) phonenumber
	from usertbl U
	left join buytbl B
        on U.userID = B.userID
	order by U.userID;

Q2. 회원 테이블에는 있지만 구매 내역이 없는 회원들을 찾아라.

select U.userID, U.name, B.prodName, U.addr, concat(U.mobile1, U.mobile2) phonenumber
	from usertbl U
	left join buytbl B
        on U.userID = B.userID
	where B.prodName is null
	order by U.userID;

Q3. 앞의 학생, 동아리, 학생이 가입한 동아리 정보를 담은 3개의 테이블에서 동아리에 가입하지 않은 학생의 정보도 출력하라.

select S.stdName, S.addr, SC.ClubName, C.roomNo
	from stdtbl S 
    left join stdclubtbl SC
		on S.stdName = SC.stdName
    left join clubtbl C
		on SC.clubName = C.clubName
    order by S.stdName;

 

Q3. 동아리를 기준으로 가입된 학생들을 출력하라. 단, 가입 학생이 하나도 없는 동아리의 정보도 출력하라.하라.

select C.clubName, C.roomNo, S.stdName, S.addr
	from clubtbl C
    left join stdclubtbl SC
		on C.clubName = SC.clubName
	left join stdtbl S
		on SC.stdName = S.stdName
	order by C.clubName;​

CROSS JOIN(상호 조인)

• 한 쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능
• M행의 테이블과 N행의 테이블을 CROSS JOIN 하면 M*N개의 행이 생성됨 
• 테스트로 사용할 많은 용량의 데이터를 생성할 때 주로 사용
• ON  구문을 사용할 수 없음
• 대량의 데이터를 생성하면 시스템이 다운되거나 디스크 용량이 모두 찰 수 있으므로 COUNT(*) 함수로 개수만 카운트

- 방법1
SELECT 열1, 열2, ...
FROM 테이블1
CROSS JOIN `테이블2`
WHERE 출력조건 ;

- 방법2

SELECT 열1, 열2
FROM 테이블1, 테이블2
WHERE 출력조건 ;

Q. 회원 정보와 빌려간 영화의 정보가 담긴 테이블이 있을 때, 두 테이블에서 얻을 수 있는 모든 조합을 출력하라.

select * from movies cross join members;​

select * from movies, members;

Reference : 이것이 MySQL이다

USE 구문

• SELECT문 학습 위해 사용할 데이터베이스 지정
• 지정해 놓은 후 다른 DB를 사용하겠다고 명시하지 않는 이상 모든 SQL문은 지정된 DB에서 수행

USE `데이터베이스 이름`;

use employees;

 
SELECT ... FROM

• 데이터베이스 내 테이블에서 원하는 정보를 추출
• 테이블에 있는 모든 정보 조회

SELECT * FROM `테이블 이름`;

select * from employees;

• 테이블에서 필요로 하는 열만 가져오기

SELECT `열 이름` FROM `테이블 이름`;

select first_name from employees;

• 여러 개의 열을 가져오고 싶을 때는 콤마로 구분
• 열 이름의 순서는 출력하고 싶은 순서대로 배열 가능

SELECT 열1, 열2, 열3 FROM `테이블 이름`;

select first_name, last_name, gender from employees;

 
주석(Remark)

• 한 줄 주석처리 --
  • -- 뒤에 하나 이상의 공백이 있어야 함.

-- 한 줄 주석

• 여러 줄 주석처리 /* */

/* 블록주석
   여러줄 주석
*/

DB, TABLE, 열의 이름이 확실하지 않을 때 조회하는 방법

• 현재 서버에 어떤 DB가 있는지 조회

SHOW DATABASES;

• 현재 DB에 있는 TABLE 정보 조회

SHOW TABLE STATUS;

• 현재 DB에 있는테이블 이름만 간단히 보기

SHOW TABLES;

• 테이블에 있는 열 정보 확인

DESCRIBE 테이블 이름 ; 
DESC 테이블 이름;

 
 
열 이름에 별칭 지정하기

열이름 as 별칭

• as는 생략가능, 별칭 내에 공백이 있다면 ` `(백틱)으로로 묶어줘야 함. ' '(따옴표)와 구별에 주의

select first_name as 이름,
		gender as 성별,
		hire_date as `회사 입사일`
    from employees;

 
SELECT ... FROM ... WHERE

• 특정 조건의 데이터만 조회

SELECT `열 이름` FROM `테이블 이름` WHERE 조건식;

select * from usertbl where name = '김경호';

Q1. 이름이 김범수인 사람을 찾아라.

select * from usertbl where name = '김범수';​

Q2. 이름이 조관우인 사람을 찾아라.

select * from usertbl where name = '조관우';​

Q3. 주소지가 서울인 사람을 찾아라.

select * from usertbl where Addr = '서울';​

Q4. 출생년도가 1973인 사람을 찾아라.

select * from usertbl where birthYear = 1973;​

Q5. Mobile1의 정보가 없는 사람을 찾아라.

select * from usertbl where mobile1 is null;​

 
관계 연산자를 사용한 특정 조건의 데이터 조회

• 조건 연산자(=, <, >, <=, >=, <>, != 등)와 관계 연산자(NOT, AND, OR 등)를 조합하여 데이터를 효율적으로 추출 가능

Q1. 1970년 이후에 출생하고, 신장이 182 이상인 사람의 모든 정보를 조회하라.

select * from usertbl where birthYear >= 1970 and height >= 182;​

Q2.1970년 이후에 출생하고, 신장이 182 이상인 사람의 아이디와 이름을 조회하라.

select userID, name from usertbl where birthYear >= 1970 and height >= 182;​

Q3. 주소지가 서울인 사람들의 모든 정보를 조회하라.

select * from usertbl where addr = '서울';​

Q4. 주소지가 서울이면서 mobile1의 번호를 011로 사용하는 사람의 모든 정보를 조회하라.

select * from usertbl where addr = '서울' and mobile1 = 011;​

Q5. 주소지가 서울이면서 mobile1의 번호를 011로 사용하는 사람의 아이디, 이름, mobile1과 mobile2를 조회하라.

select userID, name, mobile1, mobile2 from usertbl where addr = '서울' and mobile1 = 011;​

Q6. 위의 결과 중 mobile1과 mobile2 사이에 구분자(-)를 넣어서 phonenumber라는 하나의 열로 나타내라.

select userID, name, concat(mobile1, '-' ,mobile2) as phonenumber
	from usertbl
    where addr = '서울' and mobile1 = 011;​

• BETWEEN... AND : 데이터가 숫자로 구성되어 있으며 연속적인 값일 때

Q. 키가 180에서 183 사이인 사람들의 이름과 키를 출력하라.

select name, height from usertbl where height between 180 and 183;​

• IN () : 이산적인 값의 조건을 나타낼 때

Q. 주소지가 경남, 전남, 경북 중 하나인 사람들의 이름과 주소를 출력하라.

select name, addr from usertbl where addr in ('경남', '전남', '경북');​

• LIKE : 문자열의 내용 검색
  • % : 무엇이 오든 제한없이 허용
  •  _ : 한 글자와 매치

Q. 이름의 두번째 글자가 '용'인 사람의 이름을 출력하라.

select name from usertbl where name like '_용%';

 
서브쿼리(SubQuery, 하위쿼리)

• 쿼리문 안에 또 쿼리문이 들어있는 것
• 서브쿼리 결과가 2개 이상이면 에러가 발생하므로 ANY, SOME, ALL 등과 함께 사용

Q. 김경호보다 키가 큰 사람의 이름과 키를 출력하라.

select name, height from usertbl
	where height > (select height from usertbl where name = '김경호');​

 

• ANY/SOME
  • 서브쿼리의 여러 개의 결과 중 한 가지만 만족해도 가능
  • = 'ANY(서브쿼리)'와 'IN(서브쿼리)'는 동일한 의미

Q. 지역이 경남인 사람의 키보다 큰 사람의 이름과 키를 출력하라. (any 사용)

select name, height from usertbl
	where height > any (select height from usertbl where Addr = '경남');​

>> 지역이 경남인 사람 중 어느 한사람보다만 크면 되므로 키가 제일 작은 170 보다 큰 사람들이 출력됨.

• ALL
  • 서브쿼리의 결과 중 여러개의 결과를 모두 만족해야 함.

Q. 지역이 경남인 사람 누구의 키보다 큰 사람의 이름과 키를 출력하라. (all 사용)

select name, height from usertbl
	where height > all (select height from usertbl where Addr = '경남');​

>> 지역이 경남인 사람 누구보다 커야 하므로 키가 제일 큰 173 보다 큰 사람들이 출력됨.

 
원하는 순서대로 정렬하여 출력 : ORDER BY

• 결과물에 대해 영향을 미치지는 않고 출력되는 순서를 조절
• 오름차순으로 정렬하려면 열 이름 뒤에 ASC, 내림차순으로 정렬하려면 열 이름 뒤에 DESC를 붙임.
• 기본값이 오름차순이므로 ASC는 생략 가능
• 조건을 여러 개로 설정하여 정렬하는 것도 가능

Q. 키가 큰 순서로 정렬하되 만약 키가 같을 경우 이름 순으로 정렬하라.

select name, height from usertbl order by height desc, name asc;​

 
중복된 값에 대한 처리 : DISTINCT

• 중복된 것을 유니크한 값으로 처리
• 테이블의 크기가 클수록 효율적
• DISTINCT 뒤에 여러개를 나열하면 모든 열에 적용됨.

Q. usertbl의 지역들을 중복없이 출력하라.

select distinct addr from usertbl;​

 
출력의 개수를 제한 : LIMIT

• 일부를 보기 위해 여러 건의 데이터를 출력하는 부담을 줄임
• 상위 N개의 데이터만 보고싶다면 limit N 사용

Q. employees 테이블에서 입사일이 오래된 5명의 사번과 입사일을 입사일이 오래된 순서대로 출력하라.

select emp_no, hire_date
	from employees
    order by hire_date asc
    limit 5;

 
테이블 복사하기 : CREATE TABLE ... SELECT

• 테이블을 복사해서 사용할 경우 주로 사용
• 지정한 일부 열만 복사하는 것도 가능
• PK나 FK같은 제약 조건은 복사되지 않음.

CREATE TABLE `새로운 테이블` (SELECT `복사할 열` FROM `기존 테이블`);

Q1. sqldb의 buytbl 테이블을 buytbl2로 복사하라.

use sqldb;
create table buytbl2 (select * from buytbl);​

Q2. buytbl 테이블 중 userID와 prodName 열만 buytbl3로 복사하라.

create table buytbl3 (select userID, prodName from buytbl);​

GROUP BY ... HAVING

• 그룹으로 묶어주는 역할
• 집계 함수(Aggregate Function)와 함께 사용
• GROUP BY 와 함께 자주 사용되는 집계 함수

Q. 각 사용자 별로 구매한 물품의 총 구매액을 ID의 사전 순서대로 출력하라.

select userID as `사용자 아이디`, sum(amount * price) as `총 구매 개수`
	from buytbl
    group by userID
    order by userID;​

• HAVING절
  • WHERE와 비슷한 개념으로 GROUP BY절 뒤에서 집계 함수에 대해서 조건을 제한

Q. 총 구매액이 1000 이상인 회원의 ID와 총 구매액을 총 구매액이 큰 것부터 순서대로 출력하라.

select userID as `사용자 아이디`, sum(price * amount) as `총 구매액`
	from buytbl
    group by userID
    having sum(price * amount) > 1000
    order by sum(price * amount) desc;​

ROLLUP

• 총합 또는 중간 합계가 필요한 경우 사용
• GROUP BY와 함께 WITH ROLLUP문 사용

Q. buytbl의 groupName 분류별 구매액 및 총합을 구하라.

select groupName, sum(price * amount) as `총 구매액`
	from buytbl
	group by groupName
	with rollup;​

Reference : 이것이 MySQL이다
 

인덱스(Index)

• 데이터베이스 '색인'의 개념
• 쿼리에 응답하는 시간을 단축시켜 데이터베이스의 성능 향상
• 데이터의 양이 많을수록 효과적으로 작용
• 테이블의 열 단위에 생성

참고) 기본 키(PK) 열에는 명시적으로 인덱스를 생성하지 않아도 자동 생성됨.

Q. first_name이 Mary인 사람의 정보를 indextbl에서 찾아라.

select * from indextbl where first_name = 'Mary';​

>> Query cost : 50.75

Q. first_name 열에 인덱스를 생성한 후 위의 쿼리문을 재실행하라.

create index idx_indextbl_firstname on indextbl(first_name);
select * from indextbl where first_name = 'Mary';​

>> Query cost : 0.35 (같은 쿼리문이지만 속도면에서 확실한 개선이 나타남)

뷰(View)

• 실제 행 데이터를 가지고 있지 않은 가상의 테이블
• 실제 테이블에 링크(Link)된 개념
• 뷰를 조회하면 진짜 테이블의 데이터를 조회하는 것과 동일한 결과가 나타남.
• 테이블 중 일부 데이터만 조회할 수 있는 권한을 부여하려고 할 때 사용됨.

Q. membertbl에서 mamberName 과 memberAddress 데이터에만 접근할 수 있는 View를 생성하라.

create view uv_membertbl
	as select memberName, memberAddress
	from membertbl;

select * from uv_membertbl;​

저장 프로시저(Stored Procedure)

• MySQL에서 제공해주는 프로그래밍 기능
• 쿼리문을 하나로 묶어 편리하게 사용하는 기능
• 자주 사용하는 쿼리문을 스토어드 프로시저로 만들어 놓으면 쿼리문을 매번 하나하나 수행하는 번거로움을 해결할 수 있음.

Q. membertbl에서 memberName 이 '당탕이'인 사람의 정보를 보여주고 producttbl에서 productName이 냉장고인 제품의 정보를 보여주는 쿼리문을 담은 스토어드 프로시저를 생성하고 호출하라.

DELIMITER //
create procedure myProc()
begin
	select * from membertbl where memberName = '당탕이';
    select * from producttbl where productName = '냉장고';
end //
DELIMITER ;

call myProc();

* 주의) 마지막 DELIMITER와 ; 사이에는 공백이 하나 존재해야 함.

트리거(Trigger)

• 테이블에 부착되어 테이블에 INSERT/UPDATE/DELETE 작업이 발생하면 실행되는 코드

Q. membertbl에서 데이터가 삭제되었을 때 삭제된 정보를 저장하는 트리거를 생성하고 실행하라.

create table deletedmembertbl(
	memberID CHAR(8),
    memberName CHAR(5),
    memberAddress CHAR(20),
    deletedDate DATE);
    
DELIMITER //
	create trigger trg_deletedmembertbl
	after delete
	on membertbl
	for each row
begin
	insert into deletedmembertbl
    values (OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE());
end //
DELIMITER ;

delete from membertbl where memberName = '당탕이';
select * from deletedmembertbl;

Reference : 이것이 MySQL이다!