[MySQL & PHP] 11장 뷰 활용하기

MySQL & PHP 학습/정리 그리고 체크! 2016. 1. 25. 02:21

728x90
반응형

정리

1. 뷰는 테이블과 달리 실체가 없다.


1. 뷰란?


1.1 이용할 수 있는 버전

뷰를 학습하기 전에 먼저 MySQL의 버전을 확인하기 바랍니다. 뷰는 MySQL버전 5.0 이상에서 지원합니다. 5.0 이전 버전에서는 이용할 수가 없습니다.


MySQL은 동작이 빠른 점이 특징이지만, 이전 버전에는 뷰와 같은 기능이 없었습니다. 만약 5.0 이전 버전을 사용하고 있다고 해도 뷰에 대한 내용은 알아 두는 것이 좋습니다. 반드시 앞으로 사용할 기회가 생길 것입니다.


1.2 뷰의 정체

뷰의 정체는 무엇일까요? 지금까지 SELECT를 사용해서 다양한 방법으로 데이터를 추출해 보았습니다. 이렇게 SELECT한 결과를 가상 테이블에 저장한 것을 라고 합니다.


뷰는 테이블이 아닙니다. 그러므로 뷰의 레코드와 칼럼에는 데이터가 존재하지 않습니다. 뷰란 '◯ ◯ 테이블의 ✕ ✕ 칼럼과 ~~을 △ △의 조건으로 한데 모아 놓은 것'으로, 데이터를 추출하기 위한 정보일 뿐입니다.




2. 뷰의 장점


1.3 뷰의 활용 방법

테이블처럼 보이지만 실체가 없이 데이터를 모으기 위한 정보만 가지고 있는 뷰는 사용자가 '원하는 칼럼만', '원하는 조건으로' 데이터를 모을 수 있기 때문에 매우 편리합니다.


지금까지 배워서 알겠지만, 조건을 설정해서 테이블로부터 데이터를 추출한다는 것은 그리 간단한 작업이 아닙니다. 하지만, 같은 방법으로 추출한 것을 일반 뷰로 하번 실행해 두면 사용자 정의 형식의 테이블로 이용할 수가 있습니다.


사용자 입장에서 보면 뷰와 테이블의 이용 방법에는 큰 차이가 없습니다. 테이블과 마찬가지로 SELECT는 물론 UPDATE도 가능합니다. 뷰의 값을 변경하면 참조하는 테이블의 데이터도 변경됩니다.


변경되면 안 되는 중요한 데이터의 경우, 관리자 등 권한을 가진 사람만이 그 테이블을 조작할 수 있도록 하고, 그 외의 사람은 데이터를 조작해도 문제가 없는 부분만 모아 놓은 뷰를 이용하도록 하면 안전합니다.


또한, 데이터베이스 상급자가 초보자가 이해하기 쉽도록 복잡한 테이블 대신 알기 쉽게 작성한 뷰를 제공하는 방법으로 활용할 수도 있습니다.




3. 뷰를 생성하는 방법


2. 뷰 이용하기


2.1 뷰 생성하기

그러면 뷰를 만들어 보겠습니다. 하지만 엄밀하게 말하면 실체가 없기 때문에 만든다는 말보다는 정의한다는 표현이 더 정확할지 모르겠습니다.


다음은 뷰를 생성하는 방법입니다.


뷰 생성하기

CREATE VIEW 뷰의_이름 AS SELECT 칼럼_이름 FROM 테이블_이름 WHERE 조건;


'SELECT한 것을 CREATE VIEW한다.'라는 의미의 구문입니다. SELECT의 앞에 AS를 붙이는 것을 잊지 않도록 합니다.


앞의 명령문에서는 WHERE를 이용해서 조건을 설정했지만, ORDER BY나 LIMIT, JOIN 등 아무거나 사용해도 상관없습니다. 결과적으로 어떠한 조건으로 칼럼을 모아 가상의 테이블을 만드는 것입니다. 지금까지 실행했던 많은 SELECT의 결과를 CREATE VIEW ~ AS를 이용해서 뷰를 만든다고 생각하면 됩니다.


그러면 간단한 예를 하나 들어 보겠습니다. 사원정보 테이블 tb1은 사원번호(number)와 이름(name), 나이(age)라는 칼럼으로 구성되어 있습니다. 여기에서는 사원번호를 제외한 이름과 나이만 있는 뷰 v1을 만들어 보겠습니다.


사원정보 테이블 tb1과 같은 내용의 테이블 tb1L을 사용하겠습니다. 테이블 tb1을 복사해서 테이블 tb1L을 미리 만들어 둡니다. 그러고 나서 테이블 tb1L의 이름(name)과 나이(age)만으로 구성된 뷰 v1을 만듭니다. 그리고 뷰 v1의 내용을 표시합니다.


다음 명령을 실행합니다.

CREATE VIEW v1

AS

SELECT name, age

FROM tb1L;



다음 명령을 실행합니다.

SELECT * FROM v1;



SELECT로 레코드를 표시하는 방법은 테이블을 대상으로 했을 때와 같습니다. 이번에는 1개의 테이블에서 칼럼을 추출했지만, 여러 개의 테이블에 조건을 설정해서 칼럼을 추출할 수도 있습니다.


2.2 뷰에서 칼럼 값 변경하기

뷰는 참조 데이블의 일부분을 표시하고 있다고 할 수 있습니다. 따라서 참조 테이블의 값이 변경되면 뷰의 값도 변경됩니다.


그러면, 반대로 뷰의 값을 변경하면 참조 테이블의 값은 어떻게 될까요? 사실 뷰는 참조 테이블의 일부분을 표시하는 동시에 참조 테이블의 데이터 창구이기도 합니다. 그래서 뷰의 값이 변경되면 참조 테이블의 값도 변경됩니다.


실제로 뷰 v1의 데이터를 변경해서 참조 테이블에도 반영되는지 확인해 보겠습니다. 뷰 v1에서 A101의 이름 '강신우'를 '주임 강신우'로 변경해 보겠습니다. 뷰의 값을 변경하는 방법은 테이블을 변경하는 방법과 마찬가지로 UPDATE ~ SET을 사용합니다.


다음 명령을 실행합니다.

UPDATE v1 SET name='주임 강신우' WHERE name='강신우';




먼저, 다음 SELECT문을 실행해서 뷰 v1이 제대로 변경되었는지 확인해 보겠습니다.


SELECT * FROM v1;



뷰 v1이 제대로 변경된 것을 확인했으면, 테이블 tb1L의 데이터도 확인해 보겠습니다.


SELECT * FROM tb1L;



실행 결과를 통해 테이블 tb1L의 값도 변경된 것을 알 수 있습니다. 이로써 뷰의 값을 변경하면 참조하는 테이블의 데이터도 변경된다는 것이 확인되었습니다.



3.1 조건을 설정해서 뷰 만들기

위에서는 단순하게 하나의 테이블에서 임의의 칼럼을 가져와서 뷰를 만들었습니다. 이번에는 2개의 테이블을 이용하여 WHERE로 조건을 설정해서 뷰를 만들어 보겠습니다.


먼저, 매출정보 테이블 tb에서 매출이 100 이상인 레코드를 추출하고, 사원정보 테이블 tb1L과 결합해서 해당 사원의 이름도 함께 표시해보겠습니다. 내부 조인 JOIN의 사용 방법은 10장의 [2. 여러 개의 테이블을 내부 조인해서 표시하기]를 참고합니다.


테이블 tb에 테이블 tb1L을 결합하고 매출(sales)이 100 이상인 레코드만 추출하여, 테이블 tb의 사원번호(number)와 매출(sales), 테이블 tb1L의 이름(name)으로 구성된 뷰 v2를 만들겠습니다. 그러고 나서 뷰 v2의 모든 레코드를 표시해 보겠습니다.


다음 명령을 실행합니다.

CREATE VIEW v2

AS

SELECT tb.number, tb1L.name, tb.sales

FROM tb

JOIN tb1L

USING(number)

WHERE tb.sales>=100;



다음 명령을 실행합니다.

SELECT * FROM v2;



3.2 참조 테이블을 변경하면 뷰는 어떻게 될까?

앞에서 saeles가 100 이상인 레코드를 추출해서 뷰 v2를 만들었습니다. 테이블 tb에서 사원번호 A102의 매출(sales)은 54로 100 미만입니다. 따라서, 뷰 v2에는 표시되지 않습니다.


▶︎ 테이블 tb

number

sales 

month 

A103 

101 

A102 

54 

A104 

181 

A101 

184 

A103 

17 

A101 

300 

A102 

205 

A104 

93 

A103 

12 

A107 

87 


그럼, 뷰를 만든 이후에 이 값을 100 이상으로 변경해서 추출 조건을 충족하게 되면 어떻게 될까요?


테이블 tb에서 number가 A102이고, sales가 54인 레코드의 sales를 777로 변경하고나서 이전에 만든 뷰 v2를 표시해 보겠습니다.


다음 명령을 실행합니다.

UPDATE tb SET sales=777 WHERE sales=54;



다음 명령을 실행합니다.

SELECT * FROM v2;




뷰 v2의 값도 변경된 것을 확인할 수 있습니다. 즉, 참조하는 테이블을 변경했을 때, 뷰를 구성하는 조건에 일치한다면 뷰의 데이터도 변경되는 것입니다. 즉, 조건을 설정한 뷰는 항상 설정한 조건과 일치하는 레코드를 표시합니다.


그러면 확인이 끝났으니 다시 sales=777을 변경 전의 값 54로 되돌려 놓습니다.


UPDATE tb SET sales=54 WHERE sales=777;



3.3 뷰 확인하기

이번에는 뷰의 존재과 구성을 확인하는 방법에 대해 알아보겠습니다. 기본적으로 조작 방법은 테이블과 같습니다.


어떤 뷰가 있는지 확인하려면 SHOW TABLES;를 사용합니다.


뷰 확인하기

SHOW TABLES;


존재하는 테이블과 함께 뷰도 모두 표시됩니다.



또한, 테이블과 마찬가지로 컬럼의 구조는 DESC로 확인합니다.


뷰의 칼럼 구조 표시하기

DESC 뷰_이름;


뷰에 관한 자세한 정보도 테이블과 같은 방법으로 표시할 수 있습니다.


뷰의 세부 정보 표시하기

SHOW CREATE VIEW 뷰_이름;





4. 조건이 설정된 뷰의 데이터 변경과 관련 테이블에 미치는 영향


4. 뷰에서 데이터 변경 제한하기


4.1 뷰에서 INSERT를 하면 어떻게 될까?

2.2 뷰에서 칼럼 값 변경하기에서는 뷰를 통해 관련 테이블의 데이터를 변경했습니다. 이번에는 뷰를 통해 레코드를 INSERT해 보겠습니다. 뷰는 실체가 없는 가상의 뷰에 데이터를 추가하면 어떤 일이 일어날까요?


뷰는 사용자가 임의로 관련 테이블의 칼럼들을 모아 놓은 것입니다. 즉, 뷰에서 보이는 부분은 관련 테이블의 일부인 경우가 많습니다. 뷰에 INSERT를 한다는 것은 테이블의 일부분에만 데이터를 추가하는 것이 됩니다. 설명이 길어졌는데, 결론은 '뷰에 INSERT를 하는 데에는 제한이 있다.'라는 것입니다.


예를 들어, UNION이나 JOIN, 하위 질의를 사용하는 뷰에서는 INSERT나 UPDATE를 사용할 수 없습니다. 단, 2.1 뷰 생성하기처럼 단순하게 1개의 테이블에서 칼럼을 가져왔을 때에는 INSERT나 UPDATE를 사용해도 아무 문제가 없습니다.


2.1 뷰 생성하기에서 만든 뷰 v1은 테이블 tb1L의 칼럼 name과 age로만 구성되어 있습니다. 이 name과 age에 데이터를 추가해 보겠습니다.


데이터를 추가하는 방법은 테이블에 데이터를 추가하는 방법과 같습니다. 뷰 v1에 name='임시직 이정희', age=18이라는 레코드를 추가해 보겠습니다.


다음 명령을 실행합니다.

INSERT INTO v1 VALUES('임시직 이정희', 18);



먼저, 데이터를 추가한 뷰 v1의 내용을 확인해 보겠습니다.


다음 명령을 실행합니다.

SELECT * FROM v1;



이름과 나이가 제대로 추가되었습니다. 그럼, 뷰 v1의 관련 테이블인 tb1L은 어떻게 되었을까요?


SELECT * FROM tb1L;



뷰에서 추가한 2개의 데이터 '임시직 이정희'와 18이 테이블에도 제대로 반영된 것을 확인할 수 있습니다. 그리고 뷰 v1에 정의되지 않은 칼럼 number는 데이터를 추가하지 않았기 때문에 NULL이 입력되어 있습니다.



4.2 조건이 설정되어 있을 때 관련 테이블에는 어떻게 반영될까?

약간 억지스럽지만 조건을 설정한 뷰에 조건에 일치하지 않는 데이터를 추가하면 어떻게 될지 한번 실험해 보겠습니다.


먼저, 매출정보 테이블 tb에서 '매출이 100 이상'인 number와 saels를 추출해서 뷰를 만듭니다.


즉, 테이블 tb의 사원번호(number)와 매출(sales) 칼럼을 sales>=100의 조건으로 추출해서 뷰 v3를 생성합니다. 그러고 나서 뷰 v3의 모든 레코드를 표시해 보겠습니다.


다음 명령을 실행합니다.

CREATE VIEW v3

AS

SELECT number, sales

FROM tb

WHERE sales>=100;



다음 명령을 실행합니다.

SELECT * FROM v3;



매출이 100 이상인 레코드의 number와 sales가 표시되었습니다.


뷰의 조건에 일치하지 않는 데이터 추가하기

이번에는 뷰에 장난을 좀 쳐보겠습니다. 100미만의 데이터는 표시하지 않는 뷰 v3에 100미만의 값을 추가해 보겠습니다. 과연 뷰 v3은 어떤 반응을 보일까요? 다음은 sales에 50이라는 수치를 INSERT하는 예입니다.


INSERT INTO v3 VALUES('테스트', 50);



아무 문제 없이 추가되었습니다. 그럼, 뷰 v3은 어떻게 표시될까요? 100 미만은 표시되지 않기 때문에 당연히 SELECT * FROM  v3;을 실행하면 방금 입력한 값을 표시되지 않습니다.



그럼, 관련 테이블 tb는 어떨까요?


SELECT number, sales FROM tb;



장난삼아 뷰의 조건에 일치하지 않는 데이터를 추가해 봤는데, 그 결과 관련 테이블 tb에도 값이 반영되었습니다. 이처럼, WHERE 조건을 무시하고 관련 테이블에도 데이터가 INSERT되는 것이 기본 설정입니다.


참고로, 값을 추가하지 않은 칼럼에는 NULL이 입력됩니다.



4.3 뷰의 조건에 일치하지 않으면 오류가 나게 설정하기

뷰에서 INSERT를 하면 WHERE 조건에 일치하지 않아도 관련 테이블에 데이터가 추가 된다는 사실을 확인했습니다.


하지만 조건을 설정한 뷰에 그 조건을 무시하고 데이터가 입력되면 곤란해지는 때도 있습니다. 또한, 뷰에서 입력한 데이터를 뷰에서 확인할 수 없다면 곤란하겠지요. 이런 문제가 일어나지 않게 하려면, 뷰에서는 조건에 일치하지 않는 데이터는 입력할 수 없도록 설정할 수 있습니다. WHERE 조건에 일치하지 않으면 입력할 수 없도록 설정하려면 CREATE VIEW로 뷰를 생성할 때, WITH CHECK OPTION을 추가합니다.


다음은 WITH CHECK OPTION을 추가해서 뷰 v4를 생성하는 예입니다. WITH CHECK OPTION을 설정하는 것 외에는 뷰 v3와 같습니다.


CREATE VIEW v4

AS

SELECT number, sales

FROM tb

WHERE sales>=100

WITH CHECK OPTION;



이처럼 WITH CHECK OPTION을 추가하고 다음과 같이 조건에 맞지 않는 데이터를 추가해 봅시다.


INSERT INTO v4 VALUES('테스트', 50);


그러면 'CHECK OPTION faild'라는 오류가 발생합니다.



5. 뷰에서 덮어쓰기, 변경, 삭제하기

이번에는 뷰를 편집하는 방법을 소개하겠습니다.


5.1 뷰 덮어쓰기

이미 같은 이름의 뷰가 존재할 때 덮어쓰기를 해서 뷰를 생성하는 방법입니다.


CREATE VIEW를 실행할 때, 생성하려고 하는 이름의 뷰가 이미 존재하면 오류가 발생합니다. 이럴 때 CREATE OR REPLACE VIEW ~와 같이 OR REPLACE를 추가하면 덮어쓰기(REPLACE)를 할 수 있습니다. 즉, 이미 존재하는 같은 이름의 뷰를 삭제하고 새롭게 뷰를 생성하게 됩니다.


예를 들어, 이미 뷰 v1이 존재하는 상태에서 다음 명령을 실행합니다.


CREATE OR REPLACE VIEW v1

AS

SELECT NOW();



아무 문제 없이 뷰 v1이 생성됩니다. 기존의 뷰 v1은 삭제되고, 항상 현재 날짜와 시간을 표시하는 뷰 v1이 새롭게 생성됩니다(REPLACE된 뷰 v1은 조금 뒤에 원래대로 되돌려 놓겠습니다).


이 명령은 같은 이름의 뷰가 있는지 없는지 모르는 상태에서 일단 뷰를 생성하는 방법입니다. SQL문을 한꺼번에 실행할 때, 뷰의 존재 여부에 상관없이 뷰를 생성할 수 있는 편리한 명령입니다.



5.2 뷰의 칼럼 구조 변경하기

뷰의 정의를 변경할 때에는 ALTER VIEW를 사용합니다.


뷰의 칼럼 구조 변경하기

ALTER VIEW 뷰_이름 AS SELECT 칼럼_이름 FROM 테이블_이름;


CREATE VIEW ~ AS SELECT로 뷰를 처음 생성하는 명령과 거의 흡사합니다. 앞에서 NOW()만 표시하게 만든 뷰 v1을 원래대로 돌려놓겠습니다.


다음 명령을 실행하면 이미 존재하는 뷰 v1은 테이블 tb1의 칼럼 name, age와 같은 구조로 변경됩니다.


ALTER VIEW v1

AS

SELECT name, age

FROM tb1;



다음에 소개하는 뷰를 삭제하는 방법으로 일단 뷰를 삭제하고 나서, CREATE VIEW ~ 를 실행해도 같은 결과를 얻을 수 있습니다.



5.3 뷰 삭제하기

MySQL에서 무언가를 삭제할 때에는 DROP 명령을 사용했습니다. 뷰를 삭제할 때에도 DROP 명령을 사용합니다. 데이터베이스나 테이블에 사용하는 명령과 같습니다.


뷰 삭제하기

DROP VIEW 뷰_이름;


단, 삭제할 뷰가 존재하지 않으면 오류가 발생합니다.


만약, 다음과 같이 IF EXISTS를 추가하면 삭제할 뷰가 존재하지 않아도 오류가 발생하지 않습니다.


DROP VIEW IF EXISTS v1;








CREATE TABLE로 생성한 테이블을 기준 테이블(Base Table)이라고 합니다. 기준 테이블은 하드디스크와 같은 기억 장치에 실제로 데이터를 기록합니다. 그러나 뷰는 테이블 처럼 조작하지만, 실체가 없습니다. 데이터베이스 관리에는 한치의 오차도 허용되지 않습니다. 원본 테이블을 보호하는 의미에서도 뷰를 활용하는 것은 중요한 기술이라 할 수 있습니다.


체크!

★ 뷰란 무엇인지 이해하고 있다.

★ CREATE VIEW를 사용해서 뷰를 생성할 수 있다.

★ 뷰를 생성할 때 조건을 설정할 수 있다.

★ 조건에 일치하지 않는 데이터 추가를 허용하거나 제한할 수 있다.


반응형