[DB_MySQL] 이것이 MySQL이다 ch08 테이블과 뷰 2(뷰, view)

1. 뷰

◎ 뷰의 개념

   ▷ 일반 사용자 입장에서 테이블과 동일하게 사용하는 개체

   ▷ 뷰를 생성한 후에는 테이블처럼 접근 가능하여 동일한 결과 얻을 수 있음

 

   ▷ 뷰의 작동 방식

 

   ▷ 뷰 생성 구문

1. 테이블 생성

drop database if exists sqldb;  -- sqldb가 있으면 drop해서 db를 삭제, 없으면 그냥 빠져나가기
create database sqldb;

use sqldb;

-- 테이블 생성
-- 회원 테이블
create table usertbl(
	userID		char(8)		not null	primary key,	-- 사용자 아이디(PK)
	name		varchar(10)	not null,			-- 이름
	birthYear	int		not null,			-- 출생년도
	addr		char(2)		not null,			-- 지역 : 경기, 서울, 경남 식으로 2글자 입력
	mobile1		char(3),					-- 휴대폰의 국번 : 011, 016, 017, 018, 019, 010					
	mobile2		char(8),					-- 휴대폰의 나머지 전화번호
	height		smallint,					-- 키
	mDate		date						-- 회원 가입일
 );
 
 -- 회원 구매 테이블 (buytbl)
 create table buytbl(
	num		int		auto_increment not null primary key,	-- 순번(PK)
	userID		char(8)		not null,	-- 아이디(FK)
	prodName	char(6),	-- 물품명
	groupName	char(4),	-- 분류
	price		int		not null,	-- 단가
	amount		smallint	not null,	-- 수량
	foreign key (userID) references usertbl(userID)
);

INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울',  null,  NULL    , 186, '2013-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남',  NULL,  NULL    , 170, '2005-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');
INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화',  NULL, 30,	2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000,	1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200,	1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200,	5);
INSERT INTO buytbl VALUES(NULL, 'KBS', '청바지', '의류', 50,	3);
INSERT INTO buytbl VALUES(NULL, 'BBK', '메모리', '전자', 80,	10);
INSERT INTO buytbl VALUES(NULL, 'SSK', '책'   , '서적', 15,	5);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'   , '서적', 15,	2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '청바지', '의류', 50,	1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화',  NULL, 30,	2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'   , '서적', 15,	1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화',  NULL, 30,	2);

select * from usertbl;

2. v_usertbl 뷰 생성

create view v_usertbl
as
	select userid, name, addr from usertbl;

select * from v_usertbl;

테이블과 동일한 형태로 출력됩니다.
userid, name, addr 순서로 출력


3. 기본적인 뷰 생성, 열 이름 변경

-- 기본적인 뷰 생성, 뷰 생성시 뷰에 사용될 열의 이름을 변경 가능

create view v_userbuytbl
as
	select U.userid as 'USER ID', U.name as 'USER NAME', B.prodName as 'PRODUCT NAME',
	U.addr, concat(U.mobile1, U.mobile2) as 'MOBILE PHONE'
	from usertbl U
    	inner join buytbl B
			on U.userid = B.userid;

select * from v_userbuytbl;

USER ID, USER NAME, PRODUCT NAME, U.addr, MOBILE PHONE 순서대로 출력합니다.


4. 뷰 수정

-- 에러 발생 : 컬럼에 스페이스바 때문에 인식 문제로 에러 발생
select user id, user name from v_userbuytbl;

-- 백팁을 이용하면 에러 발생하지 않고 뷰로 출력됩니다.
select `user id`, `user name` from v_userbuytbl;


-- 뷰 수정 alter view

alter view v_userbuytbl
as
	select U.userid as '사용자 아이디', U.name as '이름', B.prodName as '제품 이름',
    U.addr, concat(U.mobile1, U.mobile2) as '전화 번호'
    from usertbl U
		inner join buytbl B
			on U.userid = B.userid;
            
select * from v_userbuytbl;

select `이름`, `전화 번호` from v_userbuytbl;

USER ID, USER NAME, PRODUCT NAME, U.addr, MOBILE PHONE를
사용자 아이디, 제품 이름, 주소, 전화 번호로 변경할 수 있습니다.


5. 뷰 삭제

-- 뷰 삭제 DROP VIEW
drop view v_userbuytbl;

 

◎ 뷰의 장점

   ▷ 보안에 도움
      ▶ 사용자가 중요한 정보에 바로 접근하지 못함
   ▷ 복잡한 쿼리 단순화 
      ▶ 긴 쿼리를 뷰로 작성, 뷰를 테이블처럼 사용 가능

 

1. 테이블을 통한 뷰 생성 및 실제 데이터 변경

-- 뷰 정보 확인
use sqldb;

-- v_usertbl view를 생성
create or replace view v_usertbl
as
	select userid, name, addr from usertbl;

-- usertbl의 userid, name, addr의 정보만 가져와 뷰로 저장
desc v_usertbl;

-- 현재 view의 상태를 나타냄
show create view v_usertbl;

select * from v_usertbl;

-- 뷰를 통해서 실제 데이터 변경
update v_usertbl set addr = '부산' where userid = 'JKW';

select * from v_usertbl;
select * from usertbl;​

JKW의 addr을 부산으로 변경합니다.


2. 뷰를 통한 데이터 입력

-- 뷰를 통한 데이터 입력 : not null에 처리가 되어야지만 뷰를 통해서 데이터 입력 가능
-- birthYear은 null 값이 허용되지 않기 때문에 에러 발생
insert into v_usertbl(userid, name, addr) values('KBM', '김병만', '충북');

select * from v_usertbl;

desc usertbl;

 
 birthYear이 없기때문에 에러가 발생합니다.


3. 그룹 함수를 포함하는 뷰 생성

-- 그룹 함수를 포함하는 뷰 : 집계함수로 정의된 뷰(수정되지 않음)
create view v_sum
as
	select userid as 'userid', sum(price*amount) as 'total'
		from buytbl group by userid;

select * from v_sum;

-- sum() 함수를 사용한 뷰는 수정할 수 없습니다.
select * from information_schema.views
	where table_schema = 'sqldb' and table_name = 'v_sum';
    
-- IS_UPDATABLE이 NO로 되어 있어서 데이터 수정 불가능(insert, update, delete)

-- view를 통해서 데이터의 수정이나 삭제할 수 없는 경우
-- 집계함수를 사용한 뷰
-- UNION ALL, JOIN 등을 사용한 뷰
-- DISTINCT, GROUP BY 등을 사용한 뷰​

그룹 함수를 포함하는 뷰는 수정할 수 없습니다.


4. 뷰를 통한 데이터 입력

-- 뷰를 통한 데이터 입력 : 조건을 이용해서 View 생성시
create view v_height177
as
	select * from usertbl where height >= 177;

select * from v_height177;

-- 177 미만인 사용자를 삭제 : v_height177 뷰 안에는 177 미만인 사용자가 없어서 데이터가 삭제되지 않는다.
delete from v_heigth177 where height < 177;

drop view v_heigth177;

-- 177 미만인 사용자를 추가
-- 입력 가능
INSERT INTO v_height177 values ('KBM', '김병만', '1977', '경기', '010', '55555555', 158, '2023-01-01');

select * from v_height177;
select * from usertbl;
-- v_height177뷰에서는 해당 값이 입력은 되지만 보이지 않음
-- 직접 usertbl에서 확인할 수 있음

-- 뷰 : 177이상인 사용자만 다룰 수 있는 권한(조건 옵션에 맞는 데이터만 입력 가능하게 설정)
alter view v_height177
as
	select * from usertbl where height >= 177
		with check option;	-- where 조건을 체크

-- 입력되지 않음
INSERT INTO v_height177 values ('SJH', '서장훈', '2006', '서울', '010', '33333333', 155, '2023-03-03');

조건에 맞지않는 값을 입력하면 데이터는 입력되지만 view에는 나타나지 않고 테이블에만 넣을 수 있습니다.
check 조건을 넣으면 데이터 자체도 입력되지 않습니다.


5. join을 통한 뷰 생성

-- 2개 이상의 테이블이 관련된 복합 뷰
create view v_userbuytbl
as
	select U.userid, U.name, B.prodName, U.addr, concat(U.mobile1, U.mobile2)
    from usertbl U
		inner join buytbl B
			on U.userid = B.userid;
            
select * from v_userbuytbl;

-- join을 통해서 만든 뷰에는 수정, 삭제 등의 작업 불가능
-- 에러발생
insert into v_userbuytbl values ('PKL', '박경리', '운동화', '경기', '00000000');

-- 실제 사용되는 테이블 삭제시
drop table if exists buytbl, usertbl;

join을 통해 뷰를 생성하면 수정할 수 없습니다.

 

뷰에 대해서 익혀보았습니다! 뷰는 나타나는 형식이 테이블이랑 비슷하지만 실제 연산 등의 작업은 테이블에서만 할 수 있다고 합니다!

 

좀 더 간단한 데이터들을 나타내서 저장하기 위해 View를 사용하는 것 같아요!!

 

많은 분들의 피드백은 언제나 환영합니다! 많은 댓글 부탁드려요~~