[SQL] 뷰

뷰

• 뷰는 데이터베이스의 개체 중 하나이다.
• 뷰는 테이블처럼 데이터를 가지고 있지는 않다.
• 뷰의 실체는 SELECT 문으로 만들어져 있다.
• 뷰에 접근하는 순간 SELECT가 실행되어 화면에 출력하는 것이다.

 

 

뷰의 종류

• 단순 뷰: 하나의 테이블과 연관된 뷰
• 복잡 뷰: 2개 이상의 테이블과 연관된 뷰

 

 

뷰를 만드는 형식

CREATE 뷰_이름
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('서울', '경기');

 

 

 

뷰의 작동

 

뷰는 기본적으로 '읽기 전용' 으로 사용하지만,

몇 가지 조건을 만족한다면 뷰를 통해서 원본 테이블의 데이터를 수정할 수도 있다.

 

 

 

뷰를 사용하는 이유

 

"테이블을 사용하면 되는데 굳이 뷰를 사용하는 이유가 뭘까?"

1. 보안(Security) 에 도움이 됩니다.
2. 복잡한 SQL을 단순하게 만들 수 있습니다.

 

보안(Security) 에 도움이 됩니다.

• 예를 들면 뷰를 사용해서 데이터베이스도 사용자마다 테이블에 접근하는 권한에 차별을 둬 처리하는 경우가 있다.

복잡한 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 WHERE mem_name = '블랙핑크';

 

 

 

 

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

 

뷰의 생성

• 뷰를 생성하면서 뷰에서 사용될 열 이름을 테이블과 다르게 지정할수 도 있다.
• 단, 뷰를 조회할 때 열 이름에 공백이 있다면 백틱( ' )으로 묶어주자.

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 "Office Phone"
		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;

• CREATE OR REPLACE 는 기존에 뷰가 있어도 덮어쓰는 효과를 내기 때문에 오류가 발생하지 않는다.

DESCRIBE v_viewtest2;

• 기존 뷰의 정보를 확인할 수 있다.
• 주의할 점은 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', '방탄소년단', '경기');

 

뷰를 통해서 데이터를 입력하고 싶다면, 뷰에서 보이지 않는 테이블의 열에 NOT NULL 이 없어야 한다.

만약 v_member  뷰를 통해서 member 테이블에 값을 넣고싶다면 

v_member 에 mem_number 열을 포함하도록 뷰를 재정의하거나,

아니면 member 에서  mem_number  열의 속성을 NULL로 바꾸거나, 기본값(DEFAULT)을 지정해야 한다.

 

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');

 

입력은 되었지만 v_height167 뷰는 167 이상만 보이도록 만든 뷰인데, 167 미만인 데이터가 입력이되었다.

SELECT * FROM v_height167;

 

뷰를 조회했지만, 방금 전에 넣은 데이터는 보이지 않는다.

이렇게 예상치 못한 경로를 통해서 입력되면 안 되는 데이터가 입력된 느낌이다.

이럴 때 예약어 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');

 

 

뷰가 참조하는 테이블 삭제

뷰가 참조하는 테이블을 삭제해보겠다.

DROP TABLE IF EXISTS member, buy;

 

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

SELECT * FROM v_height167;

 

당연히 참조하는 테이블이 없기 때문에 조회할 수 없다는 메시지가 나온다.

뷰가 조회되지 않는다면 CHECK TABLE 문으로 뷰의 상태를 볼 수 있다.

CHECK TABLE v_height167;

 

 

 

해당 포스팅의 글과 내용은 우재남 저자님의 혼자 공부하는 SQL 책을 재구성하여 만들었습니다.

좀 더 자세한 내용이 필요하시다면 '혼자 공부하는 SQL 책'을 구매해주세요

우재남 저자님 카페: https://cafe.naver.com/thisisMySQL

이것이MySQL이다 : 네이버 카페