SQL - VIEW

*VIEW

실체하는 테이블이 아닌 가상에 존재하는 임시 테이블이다.

복잡한 서브쿼리나 조인을 통해 view로 생성해두면 이후에는 테이블에서 조회하듯이 간단히 select문으로 데이터를 검색할 수 있다.

view는 자신만의 인덱스를 가질 수 없다.

view를 생성할 때 OR REPLACE를 사용하면 기존에 같은 이름으로 뷰가 있다면 수정하고, 없다면 생성을 한다.

원래는 보기만 가능하지만 점점 수정도 가능해지는 추세이다.

 

*view의 장점

누군가 어려운 쿼리로 만들어두면 다른 사람이 사용하기 쉽다.

보안성이 높다. (원본 테이블에 직접 관여하는 것이 아니기 때문에 보여져도 되는 데이터만 뷰로 만들 수 있어서)

 

*view의 수정

view를 수정했을 때 왜 원본테이블의 모든 값이 변경되지 않았을까?

이유 : view를 생성할 때 사용했던 서브쿼리문에 해당하지 않은 원본 테이블의 값들은 view에 없었기 때문에 view에 해당하는 값만 수정된다.

alter를 사용해도 되지만 or replace를 사용한다.

update를 통해 값을 변경 가능하다.

 

*with check option

view를 생성할 때 만든 조건식을 만족하면 update 할 수 없다.

with check option을 걸지 않았을 때는 update가 가능은 하지만 처음에 view를 생성할 때의 조건식에서 값이 벗어나게 되면 아예 view에서 빠지게 된다.

 

select * from information_schema.ALL_PLUGINS; -- view
-- 1) view는 무엇인가? -> 실체하는 테이블이 아니다.
-- 가상에 존재하는 임시 테이블
-- 복잡한 서브쿼리나 조인을 이용해 view로 생성해두면 
-- 이후에는 간단한 select문으로 데이터를 검색할 수 있다.
-- view는 자신만의 인덱스를 가질 수 없다.

-- 2) view 생성 문법
-- CREATE [OR REPLACE] VIEW [뷰 이름] AS [뷰를 구성할 서브쿼리];
-- OR REPLACE는 기존 뷰를 수정할 때 사용한다.

-- 3) view 생성
-- 3-1) view를 생성할 쿼리를 작성
select e.ename, d.deptname from emp e, dept d where e.deptno = d.deptno;
-- 3-2) 쿼리를 이용하여 view 생성
create view vw_emp as select e.ename, d.deptname from emp e, dept d where e.deptno = d.deptno;

-- 4) view 사용법
-- 장점1 : 누군가 어려운 쿼리로 만들어두면 다른 사람들이 사용하기 쉽다.
-- 장점2 : 보안성이 높다.(뷰를 구성하는 테이블의 구조를 알아내는 것이 번거롭다.)
select * from vw_emp;

-- view는 원래 보기만 가능하지만 점점 수정이 가능해지는 추세
update vw_emp set ename = 'oh' where ename = 'kim';
-- kim을 oh로 수정했는데 emp를 보면 하나의 kim은 수정되어있지 않다.
-- 이유 : view 생성 당시 등가조인을 사용했기 때문에 dept에 없는 deptno=6의 데이터는 포함되지 않았다.
-- 그렇기 때문에 view 데이터 수정 시에 같이 수정되지 않았다.
select * from emp;

-- 5) 뷰 수정
-- ALTER를 사용해도 된다.
-- 하지만 CREATE OR REPLACE를 사용하면 view가 있으면 수정, 없으면 생성된다.
create or replace view vw_emp as select e.ename, e.job, d.deptname, d.loc from emp e, dept d where e.deptno = d.deptno;

-- 6) WITH CHECK OPTION : view를 생성할 때 만든 조건식을 만족하면 UPDATE를 할 수 없다.
-- deptno=1인 데이터만 가져오는 뷰를 생성
create view check_option as select ename, job, deptno from emp where deptno = 1 with check option;

select * from check_option;

-- ename은 변경 가능
update check_option set job = 'manager' where ename = 'lee';
-- dept 변경 (CHECK OPTION failed)
update check_option set deptno = 2 where ename = 'lee';
-- 만약 with check option을 안 걸었다면?
-- deptno=2가 된 lee는 view에서 빠지게 된다 -> view를 생성할 때 deptno=1인 데이터만 가져오기 때문에 조건에 맞지 않아서

-- 7) 뷰 조회
show full tables where table_type = 'view'; -- view 리스트
show create view check_option; -- view를 생성한 쿼리문 확인
show create view vw_emp;

-- 8) 뷰 삭제
drop view check_option;
drop view vw_emp;