[SQL] 뷰(View)

뷰(View)

• 데이터베이스 개체 중에 하나이다.
• 테이블처럼 데이터를 갖는 것이 아니라 SELECT문으로 만들어져 그 결과가 화면에 출력되는 방식이다.
• 단순 뷰 : 하나의 테이블과 연관된 뷰
• 복합 뷰 : 2개 이상의 테이블과 연관된 뷰

 

뷰의 기본 생성

뷰 개념

SELECT mem_id, mem_name, addr FROM member;

SELECT의 결과로 출력된 것이 결국 테이블의 모양이다.

결국 mem_id, mem_name, addr 3개의 열을 가진 테이블로 봐도 된다는 것이다.

뷰가 이런 개념으로 실체가 SELECT문이 되는 것이다.

이 실행 결과를 v_member라고 부르자. 보통 뷰의 이름 앞에 v_를 붙이는 것이 일반적이다.

 

뷰의 형식

뷰를 만들 때 아래의 형식으로 만든다.

CREATE VIEW 뷰_이름
AS
    SELECT 문;

 

뷰를 접근할 때 테이블과 동일하게 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('서울', '경기');

 

뷰를 사용하는 이유

보안성

테이블의 일부를 뷰로 뽑아 만들었으므로 보안에 도움이 된다.

즉, 회원 테이블에 접근을 제한하고, 뷰에만 접근을 할 수 있도록 권한을 준다면 보안적이라는 것이다.

 

간결성

복잡한 SQL을 단순하게 만들 수 있다.

아래는 구매 기록이 있는 회원의 정보를 뽑아내는 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;

 

뷰를 생성해 미리 SELECT를 해놓는다면, 복잡한 SQL을 입력할 필요가 없다.

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;

 

이제 v_memberbuy 뷰를 이용해 동일한 출력 결과를 얻는다.

SELECT * FROM v_memberbuy;

 

 

위 내용들은 간단한 형태로 뷰를 살펴본 것이고 실제 뷰의 사용에는 복잡하다.

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

뷰의 생성

뷰를 생성하며 사용될 열 이름을 테이블과 다르게 지정할 수 있다.

기존에 쓴 별칭과 동일하게 사용하면 되는데 중간에 띄어쓰기 사용이 가능하다.

별칭은 열 이름 뒤에 작은따옴표 또는 큰 따옴표로 묶어주고, 형식상 AS를 붙여준다.

단, 뷰를 조회할 때 열 이름에 띄어쓰기가 존재한다면 백틱(`)으로 묶어준다. ( 1 왼쪽에 있는 키 )

CREATE VIEW v_viewtest1
AS
	SELECT B.mem_id 'Member ID', M.mem_name AS 'Member Name',
           B.prod_name "Product Name", CONCAT(M.phone1, M.phone2) AS "Phone num"
        FROM buy B
        INNER JOIN member M
        ON B.mem_id = M.mem_id;
	
SELECT DISTINCT `Member ID`, `Member Name` FROM v_viewtest1;

 

뷰의 수정

ALTER VIEW를 사용.

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

 

뷰의 삭제

DROP VIEW를 사용한다.

DROP VIEW v_viewtest1;

 

뷰의 정보 확인

CREATE OR REPLACE VIEW v_viewtest2
AS
    SELECT mem_id, mem_name, addr FROM member;
    
DESCRIBE v_viewtest2;

 

member 테이블 정보도 확인해서 비교해보자.

DESCRIBE member;

-> PRIMARY KEY 등의 정보는 확인되지 않으므로 주의한다.

 

뷰의 소스 코드 조회

SHOW CREATE VIEW v_viewtest2;

 

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

뷰 데이터 수정

UPDATE v_member SET addr = '부산' WHERE mem_id='BLK';

 

뷰 데이터 입력

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

에러가 뜬다. member 테이블에 mem_number 열이 있는데 이것이 NOT NULL로 설정되어 있어서 반드시 입력해야 한다.

그런데 뷰 v_member 에는 mem_number 열을 참조하고 있지 않아 입력할 방법이 없다.

그래서 뷰를 통해서 member 테이블에 값을 입력하고자 한다면, v_member에 mem_number 열을 포함하도록

뷰를 재정의하거나 member에서 mem_number 열의 속성을 NULL로 바꾸거나, 기본값(Default)을 지정하면 된다.

 

뷰 데이터 삭제

키(height)가 167 이상인 데이터만 뽑아 뷰로 만들어보겠다.

CREATE VIEW v_height167
AS
    SELECT * FROM member WHERE height >= 167;

 

167 미만인 데이터를 삭제해보겠다.

DELETE FROM v_height167 WHERE height < 167;

 

뷰 데이터 입력(2)

위에 만든 키(height) 167 이상인 뷰에 167 미만인 데이터를 입력해보겠다.

INSERT INTO v_height167 VALUES('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01');

잘 입력이 되었다고 나온다. 한번 조회해보겠다.

SELECT * FROM v_height167;

 

 

뷰의 SQL은 167 이상만 조회가 되도록 만들어져 있으므로 조회가 되지 않는다.

그래서 이런 키 167 이상인 뷰에 키 159가 입력되는 걸 막고자 할 수 있다.

WITH CHECK OPTION  예약어를 사용해 뷰에 설정된 값의 범위가 벗어나면 입력되지 않도록 한다.

ALTER VIEW v_height167
AS
    SELECT * FROM member WHERE height >= 167
            WITH CHECK OPTION;

INSERT INTO v_height167 VALUES('TOB', '텔레토비', 4, '영국', NULL, NULL, 140, '1995-01-01');

 

+ 복합 뷰는 아래와 같은 뷰인데 복합 뷰는 읽기 전용이다. 복합 뷰를 통해 테이블에 입력/수정/삭제 불가능하다.

 

CREATE VIEW v_complex
AS
    SELECT B.mem_id, M.mem_name, B.prod_name, M,addr
        FROM buy B
        INNER JOIN member M
        ON B.mem_id = M.mem_id;

 

뷰가 참조하는 테이블 삭제

회원, 구매 테이블을 삭제해보겠다.

DROP TABLE IF EXISTS buy,member;

여러 개 뷰가 두 테이블과 관련이 있는데도 테이블이 삭제가 되었다.

이제 뷰를 다시 조회해보겠다.

 

SELECT * FROM v_height167;

에러가 뜬다. 관련된 뷰가 있더라도 테이블은 삭제가 된다.

 

뷰의 상태 조회

CHECK TABLE v_height167;

뷰가 참조하는 테이블이 존재하지 않아서 오류가 발생하는 것을 확인할 수 있다.

 

모든 내용은 '혼자 공부하는 SQL' 도서를 기반으로 학습 후 정리한 내용입니다.