이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.
뷰는 데이터베이스 개체 중에 하나이다.
모든 데이터베이스 개체는 테이블과 관련이 있지만, 특히 뷰는 테이블과 아주 밀접하게 연관되어 있다.
뷰는 한 번 생성해 놓으면 테이블이라고 생각해도 될 정도로 사용자들의 입장에서는 테이블과 거의 동일한 개체로 취급한다.
뷰는 테이블처럼 데이터를 가지고 있지 않다.
뷰의 실체는 SELECT 문으로 만들어져 있기 때문에 뷰에 접근하는 순간 SELECT가 실행되고 그 결과가 화면에 출력되는 방식이다.
뷰와 테이블의 관계는 바로 가기 아이콘과 실제 프로그램의 관계와 유사하다.
뷰는 단순 뷰와 복합 뷰로 나뉜다.
단순 뷰 : 하나의 테이블과 연관된 뷰
복합 뷰 : 2개 이상의 테이블과 연관된 뷰
복합 뷰는 주로 두 테이블을 조인한 결과를 뷰로 만들 때 사용한다.
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;
복합 뷰는 읽기 전용이다. 복합 뷰를 통해 테이블에 데이터를 입력/수정/삭제할 수 없다.
뷰의 개념
뷰의 작동
사용자가 뷰에 접근하는 방식은 아래의 그림과 같다.
사용자는 뷰를 테이블이라고 생각하고 접근한다.
그러면 데이터베이스가 뷰 안에 있는 SELECT를 실행해서 그 결과를 사용자에게 보내주므로 사용자 입장에서는 1번과 4번만, 즉 뷰에서 모두 처리된 것으로 이해한다.
뷰는 기본적으로 읽기 전용으로 사용되지만, 뷰를 통해서 원본 테이블의 데이터를 수정할 수도 있다.
하지만 무조건 가능한 것은 아니고 몇 가지 조건을 만족해야 한다.
뷰를 사용하는 이유
뷰를 만들면 테이블과 동일하게 접근이 가능하다.
테이블 대신 굳이 뷰를 사용하는 방법은 아래와 같다.
보안에 도움이 된다.
뷰에서 사용자의 아이디, 이름, 주소 등 중요하지 않은 정보만 보여주고 민감한 정보인 연락처, 주민등록번호등은 보여주지 않을 수 있다.
데이터베이스에서 보안은 상당히 중요하다.
root 계정은 모든 권한이 있는 관리자로 못하는 작업이 없다.
테이블의 생성, 삭제는 물론 테이블의 데이터를 마음대로 조작할 수 있는 막강한 권한이 있다.
은행을 예로 들면, 은행도 데이터베이스의 테이블에 정보를 저장한다.
통장 테이블에 고객의 예금을 관리한다고 가정하면 은행에서 모든 직원에게 root의 권한을 부여한다면 고의든 실수든 고객의 예금을 마음대로 사용할 수 있다.
아니면 고객의 중요한 정보(통장 비밀번호 등)를 마음대로 볼 수 있다.
이러한 사고를 방지하기 위해 중요 관계자가 아닌 사람에게는 중요한 정보를 보여주지 못하도록 권한을 제한하고, 뷰에만 접근할 수 있도록 권한을 준다면 이러한 문제를 쉽게 해결할 수 있다.
복잡한 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 = '블랙핑크';
뷰의 기본 생성
이러한 데이터베이스에서 회원 테이블에서 멤버 아이디, 멤버 이름, 멤버 주소만 보고 싶다면 아래의 쿼리문을 작성하면 된다.
SELECT mem_id, mem_name, addr FROM member;
뷰로멤버 아이디, 멤버 이름, 멤버 주소만 보고 싶다면 아래의 쿼리문을 작성하면 된다.
즉, v_member뷰로는 mem_id, mem_name, addr칼럼만 볼 수 있는 것이다.
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 ('서울', '경기');
뷰의 실제 작동
뷰의 실제 생성, 수정, 삭제
뷰 생성
기본적인 뷰를 생성하면서 뷰에 사용될 열 이름을 테이블과 다르게 지정할 수도 있다.
이는 별칭을 사용하면 되는데, 중간에 띄어쓰기 사용이 가능하다. 별칭은 열 이름 뒤에 작은따옴표 또는 큰따옴표로 묶어주고, 형식상 AS를 붙여준다.
AS를 붙이면 코드가 명확해 보이는 장점이 있다.(다르게 말하면, 별칭을 붙일 때는 AS를 안 붙여도 된다.)
단, 뷰를 조회할 때는 열 이름에 공백이 있으면 백틱(`)으로 묶어줘야 한다.(키보드 느낌표 왼쪽에 존재, (')가 아닌 (`))
CREATE OR REPLACE VIEW v_viewtest1
AS
SELECT B.mem_id 'Member ID', M.mem_name 'Member Name',
B.prod_name "Product Name",
CONCAT(M.phone1, M.phone2) "Office Phone"
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;
이제 뷰를 조회하면
SELECT DISTINCT `Member ID`, `Member Name`, `Product Name`, `Office Phone` 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; -- 백틱을 사용
뷰 삭제
뷰의 삭제는 DROP VIEW를 사용한다.
DROP VIEW v_viewtest1;
뷰의 정보 확인
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를 연속적으로 작성한 효과를 갖는다.
DRSCRIBE문으로 기존 뷰의 정보를 확인할 수 있다.
DESCRIBE v_viewtest2;
뷰도 테이블과 동일하게 정보를 보여준다.
하지만 PRIMARY KEY등의 정보는 확인되지 않는다.
뷰를 조회할때는 제약조건 등의 정보는 조회가 안된다. 반면에 테이블을 조회할 때는 제약조건 등의 정보가 조회가 된다.
SHOW CREATE VIEW SHOW CREATE VIEW 문으로 뷰의 소스코드도 확인할 수 있다. SHOW 결과는 뷰를 생성할 때보다 복잡하게 나온다. 복잡해 보이지만 핵심적인 코드는 생성할 때 사용한 코드와 동일하다.
SHOW CREATE VIEW v_viewtest2;
뷰를 통한 데이터의 수정/삽입/삭제
수정
뷰를 통해 테이블의 데이터를 수정할 수도 있다.
UPDATE v_member SET addr = '부산' WHERE mem_id='BLK' ;
삭제
평균 키가 167 이상인 뷰를 생성하는 쿼리는 아래와 같다.
CREATE VIEW v_height167 AS SELECT * FROM member WHERE height >= 167 ;
평균 키가 167 이상만 조회되었다.
뷰에서 키가 167이하인 데이터를 삭제하는 쿼리는 아래와 같다.
DELETE FROM v_height167 WHERE height <= 167;
167 이하인 데이터를 삭제하고 뷰를 조회해 보면 정상적으로 삭제된 것을 확인할 수 있다.
실제 member 테이블에서는 키가 167인 회원의 키가 삭제되었다.
(필자는 이후 다시 삭제된 데이터를 복구하였다.)
삽입
INSERT INTO v_member(mem_id, mem_name, addr) VALUES('BTS','방탄소년단','경기') ;
이 쿼리는 오류가 발생한다.
이유는 뷰가 참조하는 테이블의 컬럼 중에서 특정한 컬럼이 NOT NULL로 설정되어 있어서 반드시 입력해주어야 하기 때문이다.
만약 뷰를 통해서 참조 테이블에 값을 입력하고 싶다면, 뷰에 해당 컬럼을 포함하도록 뷰를 재정의 하거나, 참조 테이블에서 해당 컬럼의 속성을 NULL로 바꾸거나 기본값을 지정해야 한다.
평균 키가 167 이상인 뷰를 생성하는 쿼리는 아래와 같다.
CREATE VIEW v_height167 AS SELECT * FROM member WHERE height >= 167 ;
