25. MySQL 테이블과 뷰 - 뷰의 개념과 실습

뷰(view)

일반 사용자 입장에서는 테이블과 동일하게 사용하는 개체다. 뷰는 한 번 생성해 놓으면 테이블이라고 생각하고 사용해도 될 정도로 사용자들의 입장에서는 테이블과 거의 동일한 개체로 여겨진다.

뷰의 개념

쿼리 창에서 SELECT문을 수행해서 나온 결과를 생각해 보자.

SELECT에서 아이디, 이름, 주소를 가져와서 출력한 결과다. 그런데, 출력된 결과를 보니 SELECT의 결과가 결국 테이블의 모양을 가지고 있는 것이 확인된다.
즉, 테이블 형태로 나온 결과를 userID, name, addr의 3개의 열을 가진 테이블로 봐도 무방하지 않을까?
뷰는 바로 이러한 개념이다. 그래서, 뷰의 실체는 SELECT문이 되는 것이다. 위의 예를 보면 SELECT userID, name, addr FROM usertb문의 결과를 v_usertbl이라고 부른다면, 앞으로는 v_usertbl을 그냥 테이블이라고 생각하고 접근하면 될 것 같다.
이렇게 뷰를 생성하는 구문은 다음과 같다.

USE tabledb;
CREATE VIEW v_usertbl
AS
	SELECT userid, name, addr FROM usertbl;

이제는 뷰를 새로운 테이블로 생각하고 접근하면 된다.

SELECT * FROM v_usertbl; -- 뷰를 테이블이라고 생각해도 무방

뷰를 생성한 후에는 생성한 뷰를 그냥 테이블처럼 생각하고 접근하니 원래의 테이블을 접근한 것과 동일한 결과를 얻을 수 있었다. 다음은 뷰의 작동 방식이다.

사용자는 뷰를 그냥 테이블이라고 생각하고 접근하면 MySQL이 나머지는 알아서 처리해 준다.

그렇다면 뷰는 수정이 가능할까? 뷰는 기본적으로 '읽기 전용'으로 많이 사용되지만, 뷰를 통해서 원테이블의 데이터를 수정할 수도 있다. 뷰를 통해서 테이블의 데이터를 수정하는 것이 그다지 바람직하지는 않지만, 꼭 필요한 경우도 있을 수 있으니 어떠한 제한이 있는지 알아둘 필요는 있다. 이 내용은 잠시 후에 살펴보자.

뷰의 장점

뷰를 사용하는 이유는 무엇일까?

1. 보안에 도움이 된다.
위의 그림에서 뷰 v_usertbl에는 사용자의 이름과 주소만이 있을 뿐, 사용자의 중요한 개인정보인 출생년도, 연락처, 키, 가입일 등의 정보는 들어 있지 않다.

예를 들어 아르바이트생을 고용해서 회원의 이름과 주소를 확인하는 작업을 시킨다고 가정하자. 그런데, 이 아르바이트생에게 테이블 usertbl을 접근하도록 한다면, 사용자의 중요 개인정보(키, 가입일 등)까지 모두 볼 수 있을 것이다. 이를 방지하기 위해서 테이블의 데이터를 열로 분할할 수도 있겠지만 데이터의 일관성 및 관리가 무척 복잡해져서 배보다 배꼽이 커지는 결과를 낳을 수도 있다.

이런 경우 위의 그림과 같이 아이디, 이름, 주소만 보이는 뷰를 생성해서 아르바이트생은 usertbl에 접근하지 못하도록 권한을 제한하고 뷰에만 접근 권한을 준다면 이러한 문제가 쉽게 해결될 수 있다.

2. 복잡한 쿼리를 단순화 시켜 줄 수 있다.
다음은 물건을 구매한 회원들에 대한 쿼리다.

SELECT U.userid, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM usertbl U
	INNER JOIN buytbl B
    	ON U.userid = B.userid;

좀 복잡하다. 만약 이 쿼리를 자주 사용해야 한다면 사용자들은 매번 위와 같은 복잡한 쿼리를 입력해야 할 것이다. 이를 뷰로 생성해 놓고 사용자들은 해당 뷰만 접근하면 간단히 해결된다.

CREATE VIEW v_userbuytbl
AS
SELECT U.userid, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM usertbl U
	INNER JOIN buytbl B
    	ON U.userid = B.userid;

접근할 경우에는 v_userbytbl을 그냥 테이블이라 생각하고 접근하면 된다. WHERE절도 사용할 수 있다. '김범수'의 구매 기록을 알고 싶다면 다음과 같이 사용하면 된다.

SELECT * FROM v_userbuytbl WHERE name = '김범수';

<실습>
뷰를 생성해서 활용하자.

-- 진행하기 전 sqlDB 초기화 하기
USE sqlDB;
CREATE VIEW v_userbuytbl
AS
	SELECT U.userid AS 'USER ID', U.name AS 'USER NAME', B.prodName AS 'PRODUCT NAME', U.addr, CONCAT(U.mobile1, U.mobile2) AS 'MOBILE PHONE'
    	FROM usertbl U
     INNER JOIN buytbl B
      ON U.userid = B.userid;
      
SELECT `USER ID`, `USER NAME` FROM v_userbuytbl; -- 주의! 백틱을 사용한다.

ALTER VIEW v_userbuytbl
AS
	SELECT U.userid AS '사용자 아이디', U.name AS '이름', B.prodName AS '제품 이름', U.addr, CONCAT(U.mobile1, U.mobile2) AS '전화 번호'
    	FROM usertbl U
        	INNER JOIN buytbl B
            	ON U.userid = B.userid;

SELECT `이름`, `전화 번호` FROM v_userbuytbl;

열 이름에 한글을 사용하는 것을 권장하지 않음.

DROP VIEW v_userbuytbl; -- 뷰 삭제

USE sqlDB;
CREATE OR REPLACE VIEW v_usertbl -- 뷰가 없으면 새로 생성하고 없으면 덮어쓴다.
AS
	SELECT userid, name, addr FROM usertbl;
    
DESCRIBE v_usertbl;

SHOW CREATE VIEW v_usertbl;

view를 통해 내용을 변경해 보자.

SELECT * FROM v_usertbl;
UPDATE v_usertbl SET addr = '부산' WHERE userid = 'JKW';
SELECT * FROM v_usertbl;

view를 통해 새로운 내용을 입력해 보자.

INSERT INTO v_usertbl(userid, name, addr) VALUES('KBM','김병만','충북');

오류가 남. NOT NULL로 되어있는 것이 있기 때문이다. 뷰를 통해서 입력할 수 있는 방법이 없음. 꼭 수정하고 싶다면 NOT NULL을 NULL로 바꿔줘야 한다.(일반적으로 바람직하진 않음)

집계함수를 이용한 view를 만들어 보자.

CREATE VIEW v_sum
AS
	SELECT userid AS 'userid', SUM(price*amount) AS 'total'
    	FROM buytbl GROUP BY userid;
        
SELECT * FROM v_sum;

SELECT * FROM INFORMATION_SCHEMA.VIEWS
	WHERE TABLE_SCHEMA = 'sqldb' AND TABLE_NAME = 'v_sum';

그룹함수가 쓰여진 view는 업데이트가 가능할까? -> 불가능하다.(합계가 된 결과를 바꿀 순 없으니까) IS_UPDATABLE에 NO로 되어있다.

어떤 범위에 해당하는 view를 만들어보자.

CREATE VIEW v_height177
AS
	SELECT * FROM usertbl WHERE height >= 177;
    
SELECT * FROM v_height177;

DELETE FROM v_height177 WHERE height < 177;

삭제건수가 0이다. 실제테이블에선 177미만이 많이 있지만 뷰안에서는 없다. 뷰안에 없는 데이터는 삭제를 못한다.

INSERT INTO v_height177 VALUES('KBM', '김병만', 1977, '경기', '010', '5555555', 158, '2011-11-11')
SELECT * FROM v_height177;

키가 158 김병만을 입력하고 조회를 했더니 보이질 않는다.(입력은 잘 되었다고 뜸) 뷰의 조건이 177이상이기 때문이다. 뷰에 해당하는 조건을 만족해야 결과가 보인다.

ALTER VIEW v_height177
AS
	SELECT * FROM usertbl WHERE height >= 177
    	WITH CHECK OPTION; -- 뷰의 조건에 해당하는지 확인하고 입력해라라는 의미

INSERT INTO v_height177 VALUES('WDT', '서장훈', 2006, '서울', '010', '3333333', 155, '2011-11-11')

입력이 되지 않는 것을 확인할 수 있다.

복합뷰: 두 개 이상의 테이블을 합친 것

CREATE VIEW v_userbuytbl
AS
	SELECT U.userid, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS mobileNum
    	FROM usertbl U
        	INNER JOIN buytbl B
            	ON U.userid = B.userid;
                
INSERT INTO v_userbuytbl VALUES('PKL',' 박경리', '운동화', '경기', '00000000000', '2023-2-2');

입력이 되지 않는 것을 확인할 수 있다. 두개이상의 테이블로 조회되는 것은 UPDATE나 INSERT가 실행이 안된다.

DROP TABLE IF EXISTS buytbl, usertbl;

뷰가 설정되어있다고 하더라도 테이블은 문제없이 삭제된다.

SELECT * FROM v_userbuytbl;

뷰가 가리키는 테이블이 삭제되었기 때문에 오류가 발생한다.

CHECK TABLE v_userbuytbl;

뷰에 대한 정보 확인