[혼자 공부하는 SQL] chapter 5. 테이블과 뷰(5-3)

https://www.hanbit.co.kr/store/books/look.php?p_code=B6846155853

혼자 공부하는 SQL

위 내용은 <혼자 공부하는 SQL>을 기반으로 공부하여 작성 내용입니다.

5-3 가상의 테이블 : 뷰

• 뷰 : 데이터베이스 개체 중 하나

---

단순 뷰와 복합 뷰

• 단순 뷰  :하나의 테이블로 만든 뷰
• 복합 뷰 : 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;

---

-- 기본 형식
create view 뷰_이름
as
    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('서울', '경기');

뷰의 작동

• 뷰는 기본적으로 '읽기 전용'
• 뷰를 통해서 원본 테이블의 데이터를 수정할 수 있지만 무조건 가능한 것은 아니고 몇 가지 조건을 충족해야함

뷰를 사용하는 이유

• 보안(security)에 도움이 됨
• 복잡한 SQL을 단순하게 만들 수 있음

 

뷰의 실제 작동

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

• 뷰를 생성하면서 뷰에서 사용될 열 이름을 테이블과 다르게 지정가능
• 별칭 사용 가능, 띄어쓰기 가능
• 별칭은 작은따옴표 또는 큰 따옴표로 묶어주고, 형식상 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, 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, 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;

 

뷰의 정보 확인

• PRIMARY KEY 등의 정보는 확인되지 않음

-- 사용할 데이터베이스 설정
use market_db;

-- 뷰 생성
create or replace view v_viewtest2
as
    select mem_id, mem_name, addr from member;
    
-- 기존 뷰 정보 확인
describe v_viewtest2;

describe member;

-- 뷰의 소스 코드 확인
show create view v_viewtest2
-- result grid -> form editor 창에서 확인 가능

 

 

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

• 만약 뷰를 통해서 테이블에 값을 입력하고 싶다면 뷰를 재정의하거나, 원래 테이블에서 열의 속성을 NULL로 바꾸거나  기본값(Default)을 지정해야함

-- 오류 없이 수정됨
update v_member set addr = '부산' where mem_id = 'BLK';

-- 오류 발생
insert into v_member(mem_id, mem_name, addr) values('BTS', '방탄소년단', '경기');
-- 테이블의 열 중에서 mem_number가 not null로 설정되어 반드시 입력해야됨

-- 평균 키가 167 이상인 뷰를 생성
create view v_height167
as
	select * from member where height >= 167;
    
select * from v_height167;

 

뷰를 통한 데이터의 입력

insert into v_height167 values('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01');
-- 데이터를 입력했지만 평균 키가 167이상인 뷰에는 맞지 않는 데이터

• 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');
-- 데이터 입력이 제한됨

 

뷰를 참조하는 테이블의 삭제

• 관련 뷰가 있더라도 테이블은 쉽게 삭제됨
• 테이블이 삭제되면 뷰는 조회되지 않음
• 뷰가 조회되지 않으면 CHECK TABLE 문으로 뷰의 상태 및 뷰가 참조한 테이블을 확인할 수 있음

-- 뷰가 참조하는 테이블 모두 삭제
drop table if exists buy, member;

check table v_height167;