2024-03-21-오전오후:DDL실습8:VIEW, CTE(WITH)

/*
1. View
(1) 정의
-사용자의 접근이 허용된 자료를 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 만들어진 가상의 테이블

(2) 장점
-보안 강화
-간단한 SQL문을 사용 가능
-사용자에게 필요한 내용만 보여줄 수 있다.

(3) 특징
-이름을 가지는 가상의 테이블
-사용자 입장에서는 테이블과 동일하게 사용 가능
-ALTER 구문을 사용할 수 없다.

(4) 종류
-단순 뷰 : 하나의 테이블과 연결된 뷰
-복합 뷰 : 2개 이상의 테이블과 연관된 뷰

(5) 구문
CREATE VIEW v_뷰이름
AS
SELECT문

주의 : 뷰가 테이블 X SQL문을 저장한다.
SQL문을 만들어서 실행해보고(테스트), 그걸 뷰로 제작
*/

-- 1. city table에서 지역구가 England에 속한 도시 이름과 국가 코드를 조회하는 뷰를 생성
-- 	그리고, 뷰 호출 시, 도시 이름만 조회

CREATE VIEW v_england
AS
SELECT
	c.Name,
	c.CountryCode
FROM
	city AS c
WHERE 
	c.District = 'England';
	
SELECT
	ve.Name AS '도시 이름'
FROM
	v_england AS ve;

view로 만들기 전 select조회

view 생성 후 조회

-- 2. 회원 별 구매 이력 중, 구매 금액이 가장 높은 금액의 상품의 이름과 가격, 회원의 이메일을 조회하는 뷰
--	생성 뷰 조회시 구매자 아이디, 제품명, 가격 조회
CREATE VIEW v_highprice
AS
SELECT
	g.g_price,
	g.g_name,
	m.m_email,
	m.m_id
FROM
	tb_order AS o
	INNER Join
	tb_goods AS g
	on
	g.g_code = o.o_g_code
	INNER join
	tb_member AS m
	on
	m.m_id=o.o_id
GROUP BY
	m.m_id
HAVING
	MAX(g.g_price);
	
SELECT
	vh.m_id AS '구매자 아이디',
	vh.g_name AS '제품명',
	vh.g_price AS '가격'
FROM
	v_highprice AS vh;

view 생성전 테스트

view생성 후 조회

/*
2. CTE(common table expression, 공통 테이블 식)
-mysql 8버전 이상에서 사용 가능
(1) 정의
-임시로 쿼리 조회 결과를 저장하고 사용.

3. WITH절
(1) 정의
-CTE 사용하기 위한 구문
-SQL 표준(ANSI-SQL99)
-세미콜론(;)사용하면 안됨!

(2) 특징
-복잡한 쿼리 부분을 조각해서 나눠 가독성 향상

(3) 구문
-- 방법1
WITH cte_이름(열_이름1, 열_이름2...)
AS
(
		SELECT
			조회할_열1,
			조회할_열2
		FROM
			R
)

-- 방법2
WITH
		cte1 AS(SELECT 조회할_열1, 조회할_열2 FROM R1),
		cte2 AS(SELECT 조회할_열3, 조회할_열4 FROM R2),

*/

 

-- 예시1번
WITH cte_pop_usa(cityName,cityDist,cityPop)
AS
(
	select
		c.Name,
		c.District,
		c.Population
	from
		city AS c
	where
		c.CountryCode = 'usa'
)

SELECT
	*
FROM
	cte_pop_usa
ORDER BY cityPop DESC;

-- 예시2번
WITH cte_pop_usa AS(SELECT * FROM city AS c WHERE c.CountryCode = 'usa')
SELECT
	*
FROM
	cte_pop_usa
ORDER BY c.CountryCode DESC;

-- 3. 가장 인구수가 많은 도시의 이름과 인구수, 
--		가장 인구수가 적은 도시 이름과 인구수를 조회하는 cte를 만들고,
--		가장 많은 인구수의 도시이름과 인구수, 가장 적은 인구수의 도시이름과 인구수 조회

WITH cte_cityPop
AS
(
	SELECT
		MAX(c.Population) AS max_pop,
		MIN(c.Population) AS min_pop
	FROM 
		city AS c
)
SELECT
	c.Name,
	c.Population
FROM
	city AS c
	INNER JOIN
	cte_cityPop AS cp
WHERE
	c.Population=cp.max_pop
	or
	c.Population=cp.min_pop;

/*

GROUP_CONCAT
(1) 정의
-서로 다른 결과를 한 줄로 합쳐서 보여줄때 사용

(2) 구문
SELECT
	type,
	GROUP_CONCAT(DISTINCT name)
FROM
	R
GROUP_BY type;

(3) 구분자 변경
GROUP_CONCAT(컬럼_이름 SEPARATOR '&') <-- &로 구분지음

*/


--	4. CTE를 사용하여 구매자 별 구매 이력 중, 구매 상품 별 구매 수량이 20개 이상인 상품의 목록을 추출
--		조회시 GROUP_CONCAT 사용하여 구매자와 구매리스트를 조회
WITH cte_order
AS
(
	SELECT
		o.o_id AS oid,
		g.g_name	AS gname
	FROM
		tb_order AS o
		INNER JOIN
		tb_goods AS g
		on
		o.o_g_code=g.g_code
	GROUP by
		o.o_id, g.g_code
	HAVING SUM(o.o_amount) >= 20
)
SELECT
	m.m_name AS '구매자',
	GROUP_CONCAT(DISTINCT co.gname SEPARATOR '&') AS '구매리스트'
FROM
	tb_member AS m
	INNER JOIN
	cte_order AS co
	on
	m.m_id = co.oid
GROUP BY m.m_name;

WITH cte_swithc
AS
(
SELECT
	g.g_seller_id AS sid,
	o.o_id AS oid
FROM
	tb_goods AS g
	INNER JOIN
	tb_order AS o
	on
	g.g_code = o.o_g_code
)
SELECT
	cte_sc.sid AS '판매자 아이디',
	m.m_name AS '판매자의 이름',
	GROUP_CONCAT(DISTINCT cte_sc.oid SEPARATOR ', ') AS '구매자 아이디'
FROM
	tb_member AS m
	INNER JOIN
	cte_swithc AS cte_sc
	on
	m.m_id = cte_sc.sid
GROUP BY cte_sc.sid;