[DB] VIEW, CTE(WITH), GROUP_CONCAT

[목차]

01. VIEW

02. CTE(WITH)

03. GROUP_CONCAT

---

01. VIEW

💡

VIEW는 사용자에게 접근이 허용된 자료를 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 만들어진 가상의 테이블

• 장점

  👉🏻

  보안 강화

  간단한 SQL문을 사용 가능

  사용자에게 필요한 내용만 보이도록 할 수 있다.

  편리성, 재사용성

  보안성

  독립성

• 특징

  👉🏻

  이름을 가지는 가상 테이블

  ALTER 구문을 사용할 수 없다. (MySQL은 ALTER VIEW 허용)

• 종류

  👉🏻

  단순 뷰

  • 하나의 테이블과 연관된 뷰

  복합 뷰

  • 두 개 이상의 테이블과 연관된 뷰

• 구문

  ➡️ 뷰 생성

  👉🏻

  CREATE 뷰_이름

  AS

  SELECT문;

  ➡️ 뷰 조회

  👉🏻

  SELECT

  컬럼명

  FROM

  정의한 뷰_이름;

  뷰 생성 시, 조회할 열 이름을 입력하고, 뷰를 조회할 때, 원하는 열 이름만 작성해서 조회할 수 있다.

  ➡️ 뷰 삭제

  • DROP VIEW 뷰_이름

  ➡️ 뷰 수정

  • UPDATE 뷰_이름 SET 컬럼명=’값’ WHERE 조

  ➡️ 예제(단순 뷰)

  city table에서 district가 England인 국가의 이름과 국가 코드를 조회하는 뷰를 만드시오. (뷰 조회 시, 이름만 조회)

  -- 뷰 생성
  CREATE VIEW v_city
  AS
  SELECT
  	c.Name,
  	c.CountryCode
  FROM
  	city AS c
  WHERE
  	c.District = 'England';
  
  -- 뷰 조회
  SELECT
  	vs.Name
  FROM
  	v_city AS vs;
  
  -- 뷰 삭제
  DROP VIEW v_city;

  ➡️ 예제(복합 뷰)

  회원 별 구매 이력 중, 구매한 금액이 가장 높은 상품의 이름, 가격, 구매자의 아이디를 조회하는 뷰 생성.

  CREATE VIEW v_max_price_order
  AS
  SELECT
  	g.g_name,
  	MAX(g.g_price) AS 'max_price',
  	o.o_id
  FROM
  	tb_order AS o
  	INNER JOIN
  	tb_goods AS g
  	ON
  	o.o_g_code = g.g_code
  	INNER JOIN
  	tb_member AS m
  	ON
  	o.o_id = m.m_id
  GROUP BY
  	o.o_id;
  
  -- 뷰 조회
  SELECT
  	mpo.g_name,
  	mpo.max_price,
  	mpo.o_id
  FROM
  	v_max_price_order AS mpo

02. CTE(WITH)

💡

CTE(Common Table Expression)

MySQL 8 버전 이상에서 사용 가능

임시로 쿼리 결과를 저장하고, 사용한다고 생각.

CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신할 수 있다. 그리고 더 간결하다는 장점

미리 추출한 테이블을 CTE로 정의한다.

필요한 컬럼과 데이터를 미리 정해서 활용할 수 있다.

• CTE와 VIEW 비교

  👉🏻

  VIEW는 생성하려면 권한이 필요하지만 CTE는 필요 없다.

  하나의 쿼리문이 종료될 때 까지만 유지되는 일회성 테이블이다.

  주로 복잡한 쿼리문에서 가독성과 코드 재사용성을 높이기 위해 사용한다.

• 구문

  👉🏻

  WITH cte_test(userId, total)

  AS

  (

  SELECT

  m_id, → userId

  SUM(amount * price) → total

  FROM

  tb_test

  );

  SELECT

  ct.total

  FROM

  cte_test AS ct

  GROUP BY total;

  ➡️ 예제

  -- city테이블에서 CountryCode가 USA인 도시의
  -- 이름, district, 인구수를 조회한 임시 쿼리 저장
  WITH cte_pop_usa(city_name, city_dist, usa_pop)
  AS
  (
  	SELECT
  		c.Name,
  		c.District,
  		c.Population
  	FROM
  		city AS c
  	WHERE
  		c.CountryCode = 'USA'
  )
  
  -- 저장된 임시 쿼리 테이블에서 인구수를 내림차순으로 정렬하고,
  -- 조회되는 제한 수는 10개
  SELECT
  	*
  FROM
  	cte_pop_usa AS cp
  ORDER BY cp.usa_pop DESC
  LIMIT 10;

  -- city 테이블에서 최대인구수, 최소인구수를 조회하는 쿼리를 저장
  WITH cte_city_pop
  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_city_pop AS ccp 
  -- JOIN의 ON은 데이터가 섞이기 때문에 사용하는데 지금은 데이터가 2개뿐이라 필요 없다.
  WHERE
  	c.Population = ccp.max_pop
  	OR
  	c.Population = ccp.min_pop;

03. GROUP_CONCAT

💡

서로 다른 결과를 한 줄로 보여줄 때 사용한다.

기본 구분자는 쉼표(,)이다.

• 사용예시

  ➡️ 구분자 변경

  👉🏻

  GROUP_CONCAT(컬럼명 SEPARATOR ‘&’) → 쉼표에서 &로 변경

  ➡️ 중복 문자열 제거

  👉🏻

  GROUP_CONCAT(DISTINCT 컬럼)

  ➡️ 예제

  구매자 별 구매 이력 중, 구매 상품 수량 별 구매 수량이 20개 이상인 상품의 목록을 추출하여 다음과 같이 조회하시오.

  조회 컬럼 : 회원이름, 상품리스트(GROUP_CONCAT)

  WITH cte_order_group
  AS
  (
  	SELECT
  		o.o_id,
  		m.m_name,
  		g.g_name,
  		o.o_amount
  	FROM
  		tb_order AS o
  		INNER JOIN
  		tb_goods AS g
  		ON
  		o.o_g_code = g.g_code
  		INNER JOIN
  		tb_member AS m
  		ON
  		o.o_id = m.m_id
  	GROUP BY o.o_id, g.g_code
  	HAVING SUM(o.o_amount) >= 20
  );
  
  SELECT
  	cog.m_name,
  	GROUP_CONCAT(DISTINCT cog.g_name SEPARATOR '&') AS '구매리스트'
  FROM
  	cte_order_group AS cog
  GROUP BY cog.o_id;

---

tag : #데이터베이스 #DB #VIEW #CTE #WITH #GROUP_CONCAT

Uploaded by N2T