[혼공S] CH5-(3). 가상의 테이블: 뷰

뷰(view)를 사용하면 사용자에게 필요한 정보만을 보여줄 수 있다. 

뷰는 데이터베이스 개체 중 하나로, 실제로 데이터를 가지고 있지는 않다. 뷰는 단순 뷰와 복합 뷰로 나뉘는데, 단순 뷰는 하나의 테이블과 연관된 뷰를 의미하고 복합 뷰는 2개 이상의 테이블과 연관된 뷰를 의미한다. 

 

뷰의 개념

뷰의 기본 생성

먼저 market_db 에 있는 member 테이블에서 mem_id, mem_name, addr의 열만을 출력해보자.

USE market_db ; 
SELECT * FROM member ; 
SELECT mem_id, mem_name, addr FROM member ;

위에서 보이는 것처럼, 출력 결과는 세 개의 열을 갖고 있는 '테이블'로 볼 수도 있다. 즉, 뷰의 실체란 SELECT 문이 되는 것이다. VIEW를 만드는 것과 접근하는 형식은 다음과 같다. 

-- 뷰 생성
CREATE VIEW 뷰_이름
AS 
   SELECT 문 ;
   
-- 뷰에 접근
SELECT 열_이름 FROM 뷰_이름
   [WHERE 조건] ;

 

회원 아이디, 이름, 주소에 접근하는 뷰를 만들어보도록 하자. 뷰는 테이블과 똑같이 필요한 열만 보거나 조건식을 넣을 수도 있다.

-- 뷰 생성 
CREATE VIEW v_member
AS
   SELECT mem_id, mem_name, addr FROM member ; 

SELECT * FROM v_member ;

SELECT mem_name, addr FROM v_member 
   WHERE addr IN('서울', '경기') ;

 

뷰를 사용하는 이유

뷰를 만들면 테이블과 동일하게 접근이 가능하다는 것을 확인하였다. 그럼, 굳이 테이블을 사용하지 않고 뷰를 사용하는 이유는 무엇일까?

1. 보안(security)에 도움이 된다. 노출되지 않아야 할 데이터는 숨긴 채로 다른 사람들의 접근을 막고 일을 진행할 수 있다. 

2. 복잡한 SQL을 단순하게 만들 수 있다. 많이 쓰이는 쿼리문을 이용한 뷰를 만든 후 간단한 쿼리문을 작성할 수 있다.

-- 복잡한 쿼리문 간단하게
SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(M.phone1, M.phone2) '연락처' 
   FROM buy B
      INNER JOIN member M
      ON B.mem_id = M.mem_id ; 

CREATE VIEW v_memberbuy 
AS 
   SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(M.phone1, M.phone2) '연락처' 
      FROM buy B
         INNER JOIN member M
         ON B.mem_id = M.mem_id ;          
SELECT * FROM v_memberbuy ;
SELECT * FROM v_memberbuy WHERE mem_name = '블랙핑크' ;

 

뷰의 실제 작동

실무에서 뷰를 어떻게 사용하는지에 대해 알아보도록 하자.

 

뷰의 실제 생성, 수정, 삭제

먼저, 뷰를 생성하면서 열 이름을 기존의 테이블에서의 열 이름과 다르게 지정 가능하다.

-- 뷰의 실제 생성, 수정, 삭제
USE market_db ; 
CREATE VIEW v_viewtest1 
AS 
   SELECT B.mem_id 'member id', M.mem_name AS 'member name', -- 여기서의 as 는 형식상임. 생략 가능 
          B.prod_name "product name", CONCAT(M.phone1, M.phone2) AS 'office phone' 
   FROM buy B  
      INNER JOIN member M 
      ON B.mem_id = M.mem_id ; 
      
SELECT * FROM v_viewtest1 ; 
SELECT DISTINCT `member id`, `member name` FROM v_viewtest1 ; -- 여기서 작은 따옴표가 아니라 백틱(`) 사용하는 거 헷갈리지 말기!

또한 ALTER VIEW 문을 이용해서 기존의 뷰를 수정할 수도 있다. 열 이름을 한글로 바꿔보자.

-- 뷰 수정
ALTER VIEW v_viewtest1 
AS
   SELECT B.mem_id '회원 아이디', M.mem_name AS '회원 이름', 
          B.prod_name '제품 이름', CONCAT(M.phone1, M.phone2) AS '연락처'
   FROM buy B 
      INNER JOIN member M 
	  ON B.mem_id = M.mem_id ; 
      
SELECT * FROM v_viewtest1 ; 
SELECT DISTINCT `회원 아이디`, `회원 이름` FROM v_viewtest1 ;

VIEW를 삭제하는 것은 DROP 문을 쓰면 된다.

-- 뷰의 삭제
DROP VIEW v_viewtest1 ;

 

뷰의 정보 확인

생성된 뷰에 대한 정보를 확인해보도록 하자.

-- 뷰의 정보
USE market_db ; 
CREATE OR REPLACE VIEW v_viewtest2 
AS 
   SELECT mem_id, mem_name, addr FROM member ; 
DESCRIBE v_viewtest2 ;

뷰를 통한 데이터의 수정/삭제

뷰를 이용해서 테이블의 데이터를 수정할 수도 있다. 

-- 데이터의 수정
SELECT * FROM v_member ; 
UPDATE v_member SET addr = '부산' WHERE mem_id = 'BLK' ; 
SELECT * FROM v_member ;

INSERT INTO v_member(mem_id, mem_name, addr) VALUES('BTS', '방탄소년단', '경기') ; -- 에러발생

위와 같이 뷰를 이용하여 새로운 데이터를 입력하려고 할 때, 에러가 발생한다. 그 이유는 뷰가 참조하고 있는 member테이블에서 mem_number열은 NOT NULL의 옵션이 추가되어 있기 때문이다. 해당 뷰에서는 mem_number열이 없으므로 새로운 데이터를 입력할 방법이 없다.

 

뷰를 통한 데이터의 입력

먼저, 평균 키가 167 이상인 뷰를 생성해본다.

-- 뷰를 통한 데이터의 입력
CREATE VIEW v_height167 
AS 
   SELECT * FROM member WHERE height >= 167 ; 

SELECT * FROM v_height167 ; 
DELETE FROM v_height167 WHERE height < 167 ; -- 해당 데이터가 없으므로 뷰는 그대로
INSERT INTO v_height167 VALUES('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01') ; -- height이 167보다 작은 데이터 입력
SELECT * FROM v_height167 ;

맨 마지막 쿼리문은, 167이상만 출력해놓은 뷰에, 그에 반하는 데이터(키가 159)를 입력한 것이다. 다른 에러는 발생하지 않고 입력은 되었다는 action output은 나오지만 뷰를 출력해보면 해당 데이터가 보이지 않는 것을 볼 수 있다. 

이러한 경우에 사용할 수 있는 옵션은 WITH CHECK OPTION이다. 이 옵션을 사용하면 설정된 값에 벗어나는 값은 입력되지 않도록 할 수 있다.

ALTER VIEW v_height167 
AS
   SELECT * FROM member WHERE height >= 167 WITH CHECK OPTION ; 
   
INSERT INTO v_height167 VALUES('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01') ; -- 이번에는 에러 발생

※ 단순 뷰와 복합 뷰 

단순 뷰는 하나의 테이블로 만든 것이고, 복합 뷰는 두 개 이상의 테이블로 만든 뷰이다. 다음은 두 개의 테이블을 조인하여 만든 복합뷰의 예시이다. 복합뷰는 읽기 전용으로, 테이블의 데이터를 입력, 수정, 삭제할 수 없다. 

-- 복합뷰
CREATE VIEW v_complex 
AS
   SELECT B.mem_id, M.mem_id, B.prod_name, M.addr
      FROM buy B 
	     INNER JOIN member M
         ON B.mem_id = M.mem_id ;
         
SELECT * FROM v_complex ;

뷰가 참조하는 테이블의 삭제

뷰가 참조하고 있는 테이블도 DROP문을 통해서 삭제가 되는 것을 볼 수 있다. 뷰가 조회되지 않는다면 CHECK TABLE을 이용하여 뷰의 상태를 확인해볼 수 있다.

-- 뷰가 참조하는 테이블의 삭제
DROP TABLE IF EXISTS buy, member ;
SELECT * FROM v_height167 ;  
CHECK TABLE v_height167 ;

 

Reference 

혼자 공부하는 SQL, 우재남 지음