[혼자공부하는SQL] 14강

05-3. 가상의 테이블 : 뷰

0. 시작하기 전에

• 뷰는 데이터베이스 개체 중 하나이다.
• 뷰는 한 번 생성해 놓으면, 테이블이라고 생각하고 사용해도 괼 정도로, 사용자들의 입장에서는 테이블과 거의 동일한 개체로 취급된다.
• 뷰의 종류 : 단순 뷰(하나 테이블과 연관된 뷰), 복합 뷰(2개 이상의 테이블과 연관된 뷰)

1) 뷰의 기본 생성

(1) 형태

CREATE VIEW 뷰_이름
AS
    SELECT 문;

(2) 뷰를 만든 후 접근 방법

SELECT 열_이름 FROM 뷰_이름
    [WHERE 조건];

(3) 예시

-- 뷰 생성
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 ('서울', '경기');

2) 뷰의 작동 방식

• 뷰는 기본적으로 ‘읽기 전용’으로 사용되지만, 뷰를 통해서 원본 테이블의 데이터를 수정할 수도 있다.
• 하지만 무조건 가능한 것은 아니고, 몇 가지 조건을 만족해야 한다.

3) 뷰를 사용하는 이유

(1) 보안(security)에 도움이 된다.

• 앞의 예에서 만든 v_member 뷰에는 사용자의 아이디, 이름, 주소만 있을 분 사용자의 중요한 개인 정보인 연락처, 평균 키, 데뷔 일자 등의 정보는 들어 있지 않다.

(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;

-- 뷰를 사용하여 검색하면 복잡한 SQL문을 단순하게 할 수 있다.
SELECT * FROM v_memberbuy WHERE mem_name = '블랙핑크';

1. 뷰의 실제 작동

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

• 기본적인 뷰를 샏성하면서 뷰에서 사용될 열 이름을 테이블과 다르게 지정할 수도 있다.
• 별칭을 사용하면 되는데, 중간에 띄어쓰기 사용이 가능하다.
• 별칭은 이름 뒤에 작은따옴표 또는 큰따옴표로 묶어주고, 형식상 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 "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 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 DISTINCT `회원 아이디`, `회원 이름`
FROm v_viewtest1;

-- 뷰의 삭제
DROP VIEW v_viewtest1;

(2) 뷰 생성 시, 이미 존재하는 뷰일 경우 덮어 쓰는 방법

-- 뷰가 있으면 덮어 써서 생성한다.
CREATE OR REPLACE VIEW v_viewtest2
AS
SELECT mem_id, mem_name, addr
FROM member;

2) 뷰의 정보 확인

• 기존에 생성된 뷰에 대한 정보를 확인할 수 있다.

(1) 형식

DESCRIBE 뷰_이름;

(2) 예시

DESCRIBE v_viewtest2;

-- 뷰를 생성하던 쿼리문 보기
SHOW CREATE VIEW v_viewtest2;

3) 뷰의 내용 수정

-- 뷰의 내용 수정
UPDATE v_member
SET addr = '부산'
WHERE mem_id = 'BLK';

SELECT * FROM v_member;

• v_member, member 모두 값이 수정된다. (사실상, 실제 값이 수정되는 것이다.)

4) 뷰에 입력 [case : 오류 발생]

-- 뷰에 입력하려고 하는 경우 : 오류 발생 (NOT NULl이 존재해서)
-- 따라서 뷰를 통해서 입력이 될 수도 있지만, 안 될 수도 있다.
-- Default를 사용하게 되면, NOT NULL의 제약 조건을 만족시킬 수 있게 된다.
INSERT INTO v_member(mem_id, mem_name, addr) VALUES ('BTS', '방탄소년단', '경기');

5) 다양한 상황

(1) 문제점 : 잘못된 입력 및 삭제인 상황

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

-- 뷰 조회 : height가 167 이상인 레코드만 보여준다. 
SELECT *
FROM v_height167;

-- 상황1 : 뷰에서 행을 삭제하지만, 0개가 삭제된다.
DELETE
FROM v_height167
WHERE height < 167;

-- 상황2 : 입력은 성공해서 테이블에 들어가긴 한다.
INSERT INTO v_height167
VALUES ('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01');
-- 하지만 조회가 안 된다.
SELECT *
FROM v_height167; -- 존재하지 않는다.
SELECT *
FROM member; -- 존재한다.

(2) 솔루션 : 입력이 삭제된다.

-- 뷰의 조건 수정
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');

6) 뷰의 부모인 원본 테이블이 삭제된 경우

-- 테이블 삭제
DROP TABLE IF EXISTS buy, member;

-- 오류 발생
SELECT * FROM v_height167;

-- 왜 조회가 안 되는지 탐색 : MySQL에서 특정 테이블 또는 뷰의 무결성을 검사하는 명령어
CHECK TABLE v_height167;