[SQL] 05-3 가상의 테이블: 뷰
테이블과 뷰
가상의 테이블: 뷰
뷰(view)는 데이터베이스 개체 중에 하나이다.
모든 데이터베이스 개체는 테이블과 관련이 있지만, 특히 뷰는 테이블과 아주 밀접하게 연관되어 있다.
뷰는 한 번 생성해 놓으면 테이블이라고 생각해도 될 정도로 사용자들의 입장에서는 테이블과 거의 동일한 개체로 취급한다.
뷰는 테이블처럼 데이터를 가지고 있지는 않다.
뷰의 실체는 SELECT 문으로 만들어져 있기 때문에 뷰에 접근하는 순간 SELECT가 실행되고 그 결과가 화면에 출력되는 방식이다.
비유하자면 바탕 화면의 ‘바로 가기 아이콘’과 비슷하다.
뷰는 단순 뷰와 복합 뷰로 나뉘는데, 단순 뷰는 하나의 테이블과 연관된 뷰를 말하고, 복합 뷰는 2개 이상의 테이블과 연관된 뷰를 말한다.
뷰의 개념
DROP DATABASE IF EXISTS market_db; -- 만약 market_db가 존재하면 우선 삭제한다.
CREATE DATABASE market_db;
USE market_db;
CREATE TABLE member -- 회원 테이블
( mem_id CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
mem_name VARCHAR(10) NOT NULL, -- 이름
mem_number INT NOT NULL, -- 인원수
addr CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
phone1 CHAR(3), -- 연락처의 국번(02, 031, 055 등)
phone2 CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
height SMALLINT, -- 평균 키
debut_date DATE -- 데뷔 일자
);
CREATE TABLE buy -- 구매 테이블
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
mem_id CHAR(8) NOT NULL, -- 아이디(FK)
prod_name CHAR(6) NOT NULL, -- 제품이름
group_name CHAR(4) , -- 분류
price INT NOT NULL, -- 가격
amount SMALLINT NOT NULL, -- 수량
FOREIGN KEY (mem_id) REFERENCES member(mem_id)
);
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015.10.19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016.08.08');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015.01.15');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울', NULL, NULL, 160, '2015.04.21');
INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울', '02', '44444444', 168, '2007.08.02');
INSERT INTO member VALUES('ITZ', '잇지', 5, '경남', NULL, NULL, 167, '2019.02.12');
INSERT INTO member VALUES('RED', '레드벨벳', 4, '경북', '054', '55555555', 161, '2014.08.01');
INSERT INTO member VALUES('APN', '에이핑크', 6, '경기', '031', '77777777', 164, '2011.02.10');
INSERT INTO member VALUES('SPC', '우주소녀', 13, '서울', '02', '88888888', 162, '2016.02.25');
INSERT INTO member VALUES('MMU', '마마무', 4, '전남', '061', '99999999', 165, '2014.06.19');
INSERT INTO buy VALUES(NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES(NULL, 'BLK', '맥북프로', '디지털', 1000, 1);
INSERT INTO buy VALUES(NULL, 'APN', '아이폰', '디지털', 200, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '아이폰', '디지털', 200, 5);
INSERT INTO buy VALUES(NULL, 'BLK', '청바지', '패션', 50, 3);
INSERT INTO buy VALUES(NULL, 'MMU', '에어팟', '디지털', 80, 10);
INSERT INTO buy VALUES(NULL, 'GRL', '혼공SQL', '서적', 15, 5);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 2);
INSERT INTO buy VALUES(NULL, 'APN', '청바지', '패션', 50, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 1);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 4);
SELECT * FROM member;
SELECT * FROM buy;
뷰의 기본 생성
뷰를 만드는 형식은 다음과 같다.
CREATE VIEW 뷰_이름
AS
SELECT 문;
뷰를 만든 후에 뷰에 접근하는 방식은 테이블과 동일하게 SELECT 문을 사용한다.
SELECT 열_이름 FROM 뷰_이름
[WHERE 조건];
이제 회원 테이블의 아이디, 이름, 주소에 접근하는 뷰를 생성해보겠다.
USE market_db;
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 ('서울', '경기');
뷰의 작동
사용자는 뷰를 테이블이라고 생각하고 접근한다.
그러면 MySQL이 뷰 안에 있는 SELECT를 실행해서 그 결과를 사용자에게 보내주므로 사용자 입장에서는 뷰에서 모두 처리된 것으로 이해한다.
뷰는 기본적으로 ‘읽기 전용’으로 사용되지만, 뷰를 통해서 원본 테이블의 데이터를 수정할 수도 있다.
하지만 무조건 가능한 것은 아니고 몇 가지 조건을 만족해야 한다.
뷰를 사용하는 이유
-
보안(security)에 도움이 된다.
예를 들어, 인터넷 마켓 회원의 이름과 주소를 확인하는 작업을 진행하려고 한다.
그런데 아르바이트생이 회원 테이블(member)에 접근할 수 있도록 한다면 사용자의 중요한 개인 정보까지 모두 노출될 것이다.
그렇다고 회원 테이블에 접근하지 못하게 하면 일을 진행할 수가 없다.이런 경우, 중요한 개인 정보를 제외한 정보만 보이는 뷰를 생성해서 아르바이트생은 회원 테이블에 접근하지 못하도록 권한을 제한하고, 뷰에만 접근할 수 있도록 권한을 준다면 이러한 문제를 쉽게 해결할 수 있다.
이런 방식으로 데이터베이스도 사용자마다 테이블에 접근하는 권한에 차별을 둬서 처리하고 있으며, 사용자별 권한이 데이터베이스 보안의 중요한 주제 중 하나이다.
-
복잡한 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;내용이 길고 좀 복잡하다.
그런데 이 SQL을 다음과 같이 뷰로 생성해 놓고 사용자들은 해당 뷰에만 접근하도록 하면 복잡한 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를 테이블이라 생각하고 접근하면 된다.
필요하면 WHERE 절도 사용할 수 있다.‘블랙핑크’의 구매 기록을 알고 싶다면 다음과 같이 사용하면 된다.
SELECT * FROM v_memberbuy WHERE mem_name = '블랙핑크';
뷰의 실제 작동
뷰의 실제 생성, 수정, 삭제
기본적인 뷰를 생성하면서 뷰에서 사용될 열 이름을 테이블과 다르게 지정할 수도 있다.
기존에 배운 별칭을 사용하면 되는데, 중간에 띄어쓰기 사용이 가능하다.
별칭은 열 이름 뒤에 작은따옴표 또는 큰따옴표로 묶어주고, 형식상 AS를 붙여준다.
AS를 붙이면 코드가 명확해 보이는 장점이 있다.
단, 뷰를 조회할 때는 열 이름에 공백이 있으면 백틱(`)으로 묶어줘야 한다.
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;
뷰의 수정은 ALTER VIEW 구문을 사용하며, 열 이름에 한글을 사용해도 된다.
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;
데이터베이스 개체의 생성/수정/삭제
데이터베이스 개체는 서로 완전히 다른 기능을 하지만 생성/수정/삭제하는 문법은 거의 동일하다.
-
생성
모든 데이터베이스 개체(테이블, 뷰, 인덱스, 스토어드 프로시저, 트리거 등)를 생성할 때는 CREATE 개체_종류를 사용한다.
예로 뷰를 생성할 때는 CREATE VIEW를 사용한다. -
수정
이미 생성된 데이터베이스 개체를 수정할 때는 ALTER 개체_종류를 사용한다.
예로 테이블을 수정할 대는 ALTER TABLE을 사용한다. -
삭제
기존의 데이터베이스 개체를 삭제할 때는 DROP 개체_종류를 사용한다.
예로 스토어드 프로시저를 삭제할 때는 DROP PROCEDURE를 사용한다.
뷰의 정보 확인
우선 간단한 뷰를 다시 생성해보겠다.
USE market_db;
CREATE OR REPLACE VIEW v_viewtest2
AS
SELECT mem_id, mem_name, addr FROM member;
CREATE OR REPLACE VIEW
뷰를 생성할 때 CREATE VIEW는 기존에 뷰가 있으면 오류를 발생하지만, CREATE OR REPLACE VIEW는 기존에 뷰가 있어도 덮어쓰는 효과를 내기 때문에 오류가 발생하지 않는다.
즉, DROP VIEW와 CREATE VIEW를 연속으로 작성한 효과를 갖는다.
DESCRIBE 문으로 기존 뷰의 정보를 확인할 수 있다.
DESCRIBE v_viewtest2;
뷰도 테이블과 동일하게 정보를 보여준다.
주의할 점은 PRIMARY KEY 등의 정보는 확인되지 않는다.
SHOW CREATE VIEW 문으로 뷰의 소스 코드도 확인할 수 있다.
[Result Grid] 창에서 결과가 잘 보이지 않는다면 [From Editor] 창에서 자세히 확인할 수 있다.
SHOW CREATE VIEW v_viewtest2;
뷰를 통한 데이터의 수정/삭제
뷰를 통해서 테이블의 데이터를 수정할 수도 있다.
v_member 뷰를 통해 데이터를 수정해보겠다.
UPDATE v_member SET addr = '부산' WHERE mem_id = 'BLK';
-> 오류 없이 수정되었다.
이번에는 데이터를 입력해보겠다.
INSERT INTO v_member(mem_id, mem_name, addr) VALUES('BTS', '방탄소년단', '경기');
-> 오류가 발생한다.
v_member(뷰)가 참조하는 member(테이블)의 열 중에서 mem_number 열은 NOT NULL로 설정되어서 반드시 입력해줘야 한다.
하지만 현재의 v_member에서는 mem_number 열을 참조하고 있지 않으므로 값을 입력할 방법이 없다.
만약 v_member 뷰를 통해서 member 테이블에 값을 입력하고 싶다면 v_member에 mem_number 열을 포함하도록 뷰를 재정의하거나, 아니면 member에서 mem_number 열의 속성을 NULL로 바꾸거나, 기본값(Default)을 지정해야 한다.
이번에는 지정한 범위로 뷰를 생성해보겠다.
평균 키가 167 이상인 뷰를 생성해보겠다.
CREATE VIEW v_height167
AS
SELECT * FROM member WHERE height >= 167;
SELECT * FROM v_height167;
-> 평균 키가 167 이상만 조회되었다.
v_height167 뷰에서 키가 167 미만인 데이터를 삭제해보겠다.
DELETE FROM v_height167 WHERE height < 167;
-> 0 row(s) affected (v_height167 뷰에는 167 미만인 데이터가 없으므로 삭제될 데이터가 없다.)
뷰를 통한 데이터의 입력
이번에는 v_height167 뷰에서 키가 167 미만인 데이터를 입력해보겠다.
INSERT INTO v_height167 VALUES('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01');
-> 일단 입력은 되었다.
그런데 v_height167 뷰는 167 이상만 보이도록 만든 뷰인데, 167 미만인 데이터가 입력되었다.
일단 뷰의 데이터를 확인해보겠다.
SELECT * FROM v_height167;
-> 방금 전에 입력한 ‘TRA’ 데이터는 보이지 않는다.
이번 예를 보면 키가 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');
-> 오류가 발생한다.
이제 키가 167 미만인 데이터는 입력되지 않고, 167 이상의 데이터만 입력된다.
단순 뷰와 복합 뷰
하나의 테이블로 만든 뷰를 단순 뷰라 하고, 두 개 이상의 테이블로 만든 뷰를 복합 뷰라고 한다.
복합 뷰는 주로 두 테이블을 조인한 결과를 뷰로 만들 때 사용한다.
복합 뷰의 예는 다음과 같다.
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 문으로 뷰의 상태를 확인해보겠다.
CHECK TABLE v_height167;
-> 뷰가 참조하는 테이블이 없어서 오류가 발생하는 것을 확인할 수 있다.