[Database] SQL VIEW

사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상 테이블이다.

VIEW의 특징

• 기본테이블로부터 유도된 테이블이기에 테이블과 같은 형태의 구조이며 조작도 테이블과 거의 같다.
• 가상 테이블이기에 테이터의 논리적 독립성을 제공한다.
• 뷰를 통해서만 데이터를 접근하여 뷰에 나타나지 않는 데이터 보안에 도움
• 필요한 데이터만 뷰로 정의해서 처리하기에 관리가 용이하고 명령문이 간단함.

VIEW의 단점

• 가상 테이블이기에 데이터의 논리적 독립성을 제공한다.
• ALTER VIEW 문을 사용할 수 없다. (뷰의 정의를 변경할 수 없다.)
• 뷰로 구성된 내용에 대한 삽입, 삭제, 갱신, 연산에 제약이 따른다. (사용하지 않는 것이 바람직.)

MySQL VIEW의 제약사항

• 뷰의 정의는 select 구문이 허용하는 wher, group by와 같은 것을 모두 포함 가능.
• TEMPORARY 뷰를 만들 수 없다.
• 뷰와 함께 연결되는 트리거를 만들 수 없다,.
• 뷰 정의에 있는 select 구문은 다음과 같은 구성을 포함할 수 없다.
  • from절 안 서브쿼리
  • TEMPORARY 테이블로 참조
  • 사용자 변수로 참조

---

view 생성 실습

1

회원 별 구매이력 중 구매금액이 가장 높은 금액의 상품명을 추출하여 회원아이디와 이메일을 포함한 뷰테이블을 생성하시오.

SELECT
	m.m_id								AS v_id
	,m.m_email							AS v_email
	,g.g_name							AS v_g_name
	,MAX(g.g_price* o.o_amount)	AS v_max_amt
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 
	m.m_id = o.o_id
GROUP BY m.m_id;

↓

---

 

CREATE VIEW v_max_amt_member
AS 
SELECT
	m.m_id								AS v_id
	,m.m_email							AS v_email
	,g.g_name							AS v_g_name
	,MAX(g.g_price* o.o_amount)	AS v_max_amt
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 
	m.m_id = o.o_id
GROUP BY m.m_id;

↓

 

2

tb_test 테이블을 다음과 같이 조회하시오.

SELECT
	t.t_season AS '계절'
	,SUM(if(t.t_name = '김성주',t.t_amount,0)) AS '김성주'
	,SUM(if(t.t_name = '정동영',t.t_amount, 0)) AS '정동영'
	,SUM(t.t_amount) AS '합계'
FROM
	tb_test AS t
GROUP BY t.t_season
;

 

3

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

SELECT
 	m.m_name AS '구매자'
 	,GROUP_CONCAT(DISTINCT g.g_name ORDER BY g.g_name) AS '상품명'
FROM
	tb_member AS m
	INNER JOIN
	tb_order AS o
	ON
	m.m_id = o.o_id
	INNER JOIN
	tb_goods AS g
	ON
	o.o_g_code = g.g_code
WHERE
	o.o_amount >= 20
GROUP BY m.m_name;