[혼공SQL] 5-3 가상의 테이블: 뷰

본 포스팅은 '한빛출판네트워크'의 '혼공SQL' 책을 기반으로 작성한 포스팅입니다.

 

1. 뷰의 개념

1.1 뷰의 기본 생성

📌 뷰를 만드는 형식

create view 뷰_이름
as
	select 문;

 

📌 뷰에 접근하는 형식

select 열_이름 from 뷰_이름
	[where 조건];

 

• 뷰 생성

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

1.2 뷰를 사용하는 이유

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

• 사용자의 중요한 개인 정보에 접근하게 할 수 없음
• ▶ 개인정보가 담긴 테이블에는 접근 권한 제한, 뷰에만 접근할 수 있도록 권한 부여

 

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;

select * from v_memberbuy where mem_name = '블랙핑크';

2. 뷰의 실제 작동

2.1 뷰의 실제 생성, 수정, 삭제

2.1.1 뷰 생성 (CREATE VIEW)

• 뷰에서 사용될 열 이름을 테이블과 다르게 지정할 수 있음 → 별칭(alias) 사용
• 별칭 : 따옴표로 묶어주고 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;

 

2.1.2 뷰 수정 (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;

2.1.3 뷰 삭제 (DROP VIEW)

drop view v_viewtest1;

 

2.2 뷰에 대한 정보 확인

DROP VIEW v_viewtest1;

USE market_db;
CREATE OR REPLACE VIEW v_viewtest2  
AS
    SELECT mem_id, mem_name, addr FROM member;

💡 CREATE OR REPLACE VIEW

- 기존에 뷰가 있어도 덮어쓰는 효과를 내기에 오류가 발생하지 않음

- DROP VIEW와 CREATE VIEW를 연속으로 작성한 효과

 

2.2.1 DESCRIBE 활용

DESCRIBE v_viewtest2;

▶ 테이블과 동일하게 정보를 보여주나 PK 등의 정보는 확인되지 않음

 

• 테이블 정보 확인

DESCRIBE member;

 

2.2.2 뷰의 소스 코드 확인 (SHOW CREATE VIEW 활용)

SHOW CREATE VIEW v_viewtest2;

 

2.3 뷰를 통한 데이터의 수정/삭제

2.3.1 데이터 수정

update v_member set addr = '부산' 
where mem_id = 'BLK';

 

• 뷰를 통해서 데이터를 입력하려면
  • 뷰에서 보이지 않는 테이블의 열에 NOT NULL이 없어야 함

insert into v_member(mem_id, mem_name, addr) values('BTS', '방탄소년단', '경기');

Error Code: 1423. Field of view 'market_db.v_member' underlying table doesn't have a default value

 

▶ 뷰가 참조하는 테이블의 열 중에서 mem_number 열이 NOT NULL로 설정되어 있어서 오류가 발생함

 

• 위의 상황에서 뷰를 통해 테이블에 값을 입력하고 싶다면?
  • 뷰(v_member)에 mem_number 열을 포함하도록 뷰를 재정의
  • 테이블(member)에서 mem_number 열의 속성을 NULL로 변경, 기본값(Defalut)을 지정

 

2.3.2 데이터 삭제

• 키가 167 이상인 데이터로 뷰 생성

create view v_height167
as
	select * from member
    where height >= 167;

select * from v_height167;

 

• 키가 167 미만인 데이터 제거 → 없으므로 삭제할 데이터 없음

delete from v_height167 where height < 167;

→ 0 row(s) affected

 

2.4 뷰를 통한 데이터 입력

• v_height167 뷰에서 키가 167 미만인 데이터 입력이 됨

insert into v_height167 values('TRA', '티아라', 6, '서울', null, null, 159, '2005-01-01');

 

• 하지만 뷰의 데이터를 확인하였을 때는 티아라 데이터가 확인 안 됨

select * from v_height167;

▶ 키가 167 이상인 뷰이므로 167 이상의 데이터만 입력되도록 해야 함!

 

• WITH CHECK OPTION 예약어 사용 : 뷰에 설정된 값의 범위에 벗어나는 값은 입력되지 않도록 함

alter view v_height167
as
    select * from member 
    where height >= 167
        **WITH CHECK OPTION** ;

 

• 키가 167 미만인 데이터를 입력 → 에러 발생

insert into v_height167 values('TOB','텔레토비', 4, '영국', NULL, NULL, 140, '1995-01-01') ;

Error Code: 1369. CHECK OPTION failed 'market_db.v_height167'

 

2.5 뷰가 참조하는 테이블의 삭제

• 여러 개의 뷰가 두 테이블과 관련이 있으나 테이블이 삭제됨

drop table if exists buy, member;

 

• 두 테이블과 연관된 뷰 조회 → 조회되지 않음

select * from v_height167;

→ Error Code: 1356. View 'market_db.v_height167' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

 

• CHECK TABLE 문으로 뷰의 상태를 확인할 수 있음

check table v_height167;