뷰(view)
뷰란 데이터베이스 개체 중 하나로 테이블과 밀접하게 연관이 있다. 뷰는 데이터를 가지진 않고 실체는 SELECT 문으로 만들어졌기에 접근하는 순간 SELECT가 실행되는 구조이다.
쉽게 말해 바로가기 아이콘과 같은 개념으로 뷰란 가상의 테이블을 이용한다.
뷰는 단순 뷰와 복합 뷰로 나뉘며 단순 뷰는 하나의 테이블과 연관된 뷰, 복합 뷰는 2개 이상의 테이블과 연관된 뷰를 말한다.
<실행>
USE market_db;
SELECT mem_id, mem_name, addr FROM member;<결과>
SELECT문 실행 결과로 나온 것은 3개의 열을 가진 테이블의 모양이고, 뷰는 이를 테이블로 간주한다. 따라서 뷰의 실체가 SELECT문이고 해당 쿼리의 실행 결과를 v_member라고 부른다면 앞으로 v_member를 테이블로 생각하고 접근해야 한다.
뷰의 경우 접두어로 v_를 붙이는 것이 일반적이다.
CREATE VIEW 뷰 이름 AS SELECT ...;뷰를 만드는 형식은 위와 같으며 SELECT 뒤에 추출하고 싶은 형태로 정의하면 된다.
SELECT 열 이름 FROM 뷰 이름 [WHERE 조건];뷰를 만든 후 뷰에 접근하는 방식은 테이블에 접근하는 방식과 동일하게 FROM 뒤에 뷰 이름을 입력하면 된다.
<실행>
USE market_db;
CREATE VIEW v_member
AS
SELECT mem_id, mem_name, addr
FROM member;
SELECT * FROM v_member;<결과>
해당 예시에서는 회원 테이블의 아이디, 이름, 주소에 접근하는 뷰를 생성한 것이다. 뷰를 새로운 테이블로 생각하고 접근하였고, 필요한 열만 보거나 조건식을 사용하여 필터링을 할 수 있다.
정리
뷰에 접근하는 방식은 사용자는 뷰를 테이블로 생각하고 접근한다. 뷰는 기본적으로 ‘읽기 전용’으로 사용되나 몇 가지 조건을 만족한다면 원본 테이블의 데이터 수정할 수 있다.
단순 뷰는 데이터를 조건만 맞다면 입력, 수정, 삭제가 가능하나 복합 뷰는 데이터를 입력, 수정, 삭제하는 것이 불가능하다.(join으로 여러 테이블이 묶여있기 때문)
뷰를 사용하는 이유
- 보안에 도움된다.
예를 들어 인터넷 마켓 회원의 이름과 주소를 확인하는 작업에서 인터넷 마켓에서 일하는 아르바이트생이 회원 테이블에 접근 가능하다면 개인 정보(연락처, 데뷔 일자 등)가 노출될 위험이 있다.
따라서 개인 정보를 담은 열을 제외한 열을 가진 뷰를 생성하여 제공할 경우 아르바이트생에게는 뷰에만 접근할 수 있도록 권한을 조절할 수 있다.
사용자별 권한 부여는 데이터베이스 보안의 중요한 주제 중 하나이다.
- 복잡한 SQL의 단순화
join이 섞인 복잡한 쿼리를 뷰로 생성해놓고 접근하도록 하면 복잡한 쿼리문을 쓸때마다 입력할 필요가 없다.
SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCT(M.phone1, M.phone2) '연락처'
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;
-- VIEW를 이용한 JOIN 쿼리 단순화
CREATE VIEW v_memberbuy
AS
SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCT(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 (뷰 이름) AS SELECT ~을 통해 생성할 수 있다.
<실행>
USE market_db;
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;<결과>
뷰에서는 열 이름을 별칭으로 사용하여 기존 테이블의 열 이름과 다르게 사용 가능하다. 별칭은 열 이름 뒤에 작은 따옴표 또는 큰 따옴표로 묶어주고 형식 상 AS를 사용하여 지정하나 생략할 수 있다.
뷰를 조회할 때 열 이름에 공백이 존재하면 백틱(` 작은 따옴표가 아님에 주의)으로 묶어야 한다.
뷰 변경
뷰를 변경하고 싶을 때는 ALTER TABLE (뷰 이름) AS SELECT ~를 통해 기존에 생성했던 뷰를 변경할 수 있다.
<실행>
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;<결과>
뷰의 수정은 ALTER VIEW 구문으로 수정할 수 있다. 위 쿼리문은 기존에 영문으로 되어 있는 열의 이름을 한글로 바꾼 것이다. 열 이름에 한글을 사용할 수 있으나 권장되지 않는다.
뷰 삭제
DROP VIEW v_viewtest1;뷰를 삭제할 때는 DROP VIEW (뷰 이름)으로 생성한 뷰를 삭제할 수 있다.
뷰 생성 시 덮어쓰기
기존의 CREATE VIEW는 동일한 이름의 뷰가 있을 경우 생성할 시 오류가 발생한다. 그렇다면 기존에 동일한 이름의 뷰가 존재할 시 이를 덮어쓰는 쿼리문은 없을까?
USE market_db;
CREATE OR REPLACE VIEW v_viewtest2
AS
SELECT mem_id, mem_name, addr FROM member;CREATE VIEW는 기존에 뷰가 있으면 오류가 발생하나 `CREATE OR REPLACE VIEW (뷰 이름)`은 기존의 뷰를 덮어쓰는 효과가 있기에 오류가 발생하지 않는다.
뷰 정보 확인하기
<실행>
DESCRIBE v_viewtest2;
DESC v_viewtest2;<결과>
DESCRIBE, 또는 DESC는 테이블의 정보를 출력하는 쿼리문이다. 해당 쿼리문으로는 기본 키와 같은 정보는 확인할 수 없다.
<실행>
SHOW CREATE VIEW v_viewtest2;<결과>
SHOW CREATE VIEW (뷰 이름)을 이용하여 뷰와 관련된 정보를 확인할 수 있다.
뷰 수정
뷰를 수정하는 쿼리문은 복합 뷰가 아닌 단순 뷰에 한해 기존의 테이블을 수정하는 쿼리문과 같이 UPDATE를 이용하여 수정할 수 있다.
UPDATE v_member SET addr = '부산' WHERE mem_id = 'BLK';뷰를 수정하면, 뷰가 SELECT한 기존 테이블의 데이터가 수정된다.
뷰에 데이터 삽입
단순 뷰에 데이터를 삽입하게 될 경우 실제 테이블에도 데이터가 추가된다. 단 여기서 NOT NULL 옵션과 관련된 주의할 점이 있다.
INSERT INTO v_member(mem_id, mem_name, addr) VALUES ('BTS', '방탄소년단', '경기')위 쿼리문은 오류가 발생한다. 그 이유는 기존 테이블에 mem_number열이 존재하는데 해당 열이 NOT NULL 옵션이라서 오류가 발생한다. 그러나 v_member 뷰에서는 mem_number열을 가져오지 않으므로 해당 NOT NULL인 열에 값을 입력할 방법이 없다.
따라서 이를 해결하려면 문제가 되는 NOT NULL 옵션인 열을 포함하도록 뷰를 재정의하거나 문제가 되는 열을 NULL로 바꾸기, 또는 DEFAULT로 기본값 지정이 필요하다.
정리하자면 뷰를 통해서 데이터를 입력하려면, 뷰에서 보이지 않는 테이블의 열에 NOT NULL이 존재하지 않아야 한다.
뷰 데이터 삭제
단순 뷰에서 데이터 삭제는 DELETE FROM (뷰 이름)을 통해 조건에 해당하는 데이터를 삭제할 수 있다. 단순 뷰의 데이터를 삭제하면 실제 테이블의 데이터도 삭제되므로 주의하자.
<실행>
CREATE VIEW v_height167
AS
SELECT * FROM member WHERE height >= 167;
DELETE FROM v_heigth167 WHERE height < 167;
SELECT * FROM v_height167;<결과>
뷰에는 167 미만의 데이터가 없으므로 데이터가 삭제되지 않는다.
뷰 활용
<실행>
INSERT INTO v_height167 VALUES ('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01');
SELECT * FROM v_height167;<결과>
위 쿼리에서 INSERT를 통해 키가 167 미만의 데이터가 입력되었으나 해당 뷰는 167 이상만 보이도록 만든 뷰이다. 따라서 뷰의 데이터를 확인하면 167 미만의 티아라는 조회x
ALTER VIEW v_height167
AS
SELECT * FROM member WHERE height >= 167 WITH CHECK OPTION;WITH CHECK OPTION을 통해 뷰에 설정된 값의 범위가 벗어나는 값은 입력 시 오류가 발생하도록 테이블 정의에서 사용한 옵션과 같이 사용할 수 있다.
DROP TABLE IF EXISTS buy, member;
SELECT * FROM v_height167;만약 뷰가 참조하는 테이블을 삭제하고 뷰를 조회할 시 오류가 발생한다. 왜냐하면 뷰는 실제 테이블을 참조하고 있기 때문이다. 테이블은 뷰가 참조하고 있어도 삭제할 수 있다.
<실행>
CHECK TABLE v_height167;<결과>
CHECK TABLE (뷰 이름)문을 사용하여 뷰의 상태를 확인할 수 있다.(뷰가 조회되지 않을 시 오류 메시지를 확인 가능)
출처
- 혼자 공부하는 SQL(우재남 저, 한빛미디어)
