SQL은 두가지 형식의 테이블을 제공하는데,
하나는 일반적으로 기본 테이블로 알려져 있는 실제 테이블이고
다른 하나는 뷰라고 하는 유도된 테이블이다.
기본 테이블은 create table 명령문을 사용하여 생성되고 데이터를 저장할 수 있는 유일한 테이블이다.
뷰는 그 자체에는 행을 가지고 있지 않고 기본 테이블로 부터 조합한 데이터에 대한 처리로 가상 테이블을 만들어서 사용자에게 보여준다.
가상이란 단어가 사용된 것은 SQL 명령문에서 뷰의 이름이 사용될 때만 뷰의 내용이 존재하기 때문이다.
물론 뷰를 통해 insert, update, delete가 가능하지만 대개의 경우는 select를 위해 사용한다.뷰란 한개 이상의 기본 테이블이나 다른 뷰를 이용하여 생성되는 가상 테이블(virtual table)이다.
뷰는 기존에 생성된 테이블 또는 다른 뷰에서 접근할 수 있는 전체 데이터 중에서 일부만 접근할 수 있도록 제한하기 위한 기법이다
뷰를 가상 테이블이라하는 이유는 테이블은 디스크 공간이 할당되어 데이터를 저장할 수 있지만, 뷰는 데이터 딕셔너리 테이블에 뷰에 대한 정의만 저장하고 디스크에 저장 공간이 할당되지 않는다. 하지만 일반 사용자들은 SQL 문을 사용하여 테이블에 저장된 데이터를 검색하고 조작하는 것과 유사하게 뷰를 이용할 수 있다.
뷰는 물리적인 저장 공간과 데이터를 갖지는 않지만 테이블에서 파생된 객체이므로 테이블과 유사하게 사용할 수 있다.
• 뷰 자체는 데이터를 갖지 않지만, 기초 테이블의 데이터를 조회하고 수정할 수 있는 창과 같다.
• 뷰는 데이터 값이 아니라 실제적으로는 질의 문장만을 가진다.
| 장점 | • DB의 선택적인 부분만 보여주므로 접근을 제한 |
|---|---|
| • 다양한 접근 경로 설정 | |
| • 복잡한 질의를 단순화 | |
| • 데이터의 독립성 제공 | |
| • 동일한 데이터를 또 다른 뷰로 표현 | |
| • 한 개의 뷰에 여러 테이블의 데이터를 검색 가능 | |
| • 한 개의 테이블로부터 여러 뷰를 생성 가능 | |
| 단점 | • 뷰의 정의를 변경할 수 없고 insert,delete,update에 많은 제한이 있음 |
VIEW 를 사용할때는 몇가지 제약 사항이 있는데 아래와 같다.
- SELECT 명령문은 FROM 구문에 있는 서브쿼리를 가질 수 없다.
- SELECT 명령문은 시스템 또는 사용자 변수를 참조 할 수 없다.
- SELECT 명령문은 준비 명령문 파라미터를 참조 할 수 없다.
- 스토어드 루틴 내에서 뷰 정의는 루틴 파라미터 또는 로컬 변수를 참조 할 수 없다.
- 테이블 또는 뷰가 정의문 내에서 참조되기 위해서는 이미 존재해 있어야 한다. 하지만, 뷰가 생성된 후에는 정의문이 참조하는 테이블또는 뷰를 없애는 것은 가능하다. 이런 종류의 문제에 대한 뷰 정의를 검사하기 위해서는 CHECK TABLE 명령문을 사용한다.
- 뷰 정의문은 TEMPORARY 테이블을 참조할 수 없으며, 사용자는 TEMPORARY를 생성할 수 없다.
- 트리거를 뷰에 연관 지을수는 없다.
View(뷰)
- 자주 쓰는 쿼리를 저장할 수 있다. (매일 확인하는 용도?)
- 쿼리 상에서만 존재하는 테이블이다. 테이블처럼 행동하고, 테이블에 할 수 있는 조작을 똑같이 수행할 수 있기 때문에 가상 테이블이라고도 한다. 하지만 가상 테이블은 DB 내에 존재하지 않는다. 가상 테이블은 View를 사용할 때 생성되고 후에 지워진다.
- 뷰를 사용하면 새 열이 데이터베이스에 추가될 때마다 새 정보가 뷰에 반영된다.
- Update, Insert, Delete에도 쓸 수 있다.(그러나 테이블에 바로 사용하는 편이 낫다.)
뷰 만들기 1
- 하나의 테이블에서 특정한 조건에 맞는 레코드 들만 질의(QUERY)가능
CREATE VIEW V_EMP AS SELECT *FROM EMPLOYEE WHERE DEPTNO = 1;
뷰 만들기 2
- 하나의 테이블에서 특정한 컬럼들만 질의(QUERY)가능
CREATE VIEW V_EMP AS SELECT EMPNO, NAME FROM EMPLOYEE
뷰 만들기 3
- 여러 테이블의 칼럼을 모아서(JOIN) 하나의 테이블처럼 질의(QUERY)할 수 있도록 한 데이터베이스 오브젝트
뷰 지우기
DROP VIEW 뷰명;
DROP VIEW V_EMP;
View(뷰) 사용 이유
- 자주 쓰는 쿼리문을 안쓰고 테이블만 조회하면 된다.
- 보안에 유리하다.
- *뷰 테이블에 자료가 추가되는 것은 실체 테이블에 반영되지 않기 때문에 주의를 요한다.
View의 장점
1. 데이터베이스의 구조를 변경하여도 테이블에 의존하는 어플리케이션을 변경할 필요가 없다.
2. 복잡한 쿼리를 간단한 명령으로 단순하게 만들 수 있어 편리하다.
3. 사용자에게 필요없는 정보를 숨기는 뷰를 만들 수 있다.
View 생성
Create View 뷰이름 AS
Select column From table1
Where title = 'ABC';
STUDENT 테이블로부터 모든 학생의 학번과 학년, 반을 가지고 있는 뷰 테이블을 생성
mysql> create view v_class as
> select distinct stu_no, grade, class
> frrom student
등록한 학생의 학번과 등록년도에 대한 뷰 테이블을 생성
mysql> create view v_feeyear as
> select stu_no, fee_year
> from fee
> where fee_year is not null;
재학생 중 21세 이상인 여학생의 학번, 이름, 성별 출생년도, 나이를 출력하라.
mysql> select stu_no, stu_name, sex,b_year, age
> from V_AGES
> where age >20 and sex ='2';
View 호출
Select * From 뷰이름
뷰 테이블에서 재학생 중 20세 이상이고, 2000년~2004년에 입학한 학생을 구하라.
mysql> select * from V_AGES
> where age > 20
> and ibhak_year between '2000' and '2004'
View 삭제
Drop View 뷰이름;
* 참고
- 실제로 뷰는 서브쿼리처럼 동작한다.
아래 예를 참고하자
예)
Select * From web_designers; 를 분해해보면
Select * From
(Select mc.first_name, mc.last_name, mc.phone, mc.email
From my_contacts mc, job_desired jd
Where jd.title = 'Web Designer') AS web_designers; 와 같다.
View의 열 이름
뷰에서 열의 이름은 뷰 생성자가 따로 지정하지 않으면 SELECT 절에서 열의 이름과 동일하다.
뷰의 변경 : With check option
뷰에서 With check option을 사용하면 UPDATE와 INSERT 명령문과 같은 모든 변경은 유효성을 검사한다.
- 만약 변경된 행이 변경을 수행한 후에 뷰의 내용에 아직 속해 있다면 UPDATE 명령문은 정확히 처리된다.
- 만약 새로운 행이 뷰의 내용에 포함된다면 INSERT 명령문은 정확하게 처리된다.
학적테이블에서 1985년 이전에 출생한 모든 학생에 대한 뷰 테이블 생성
mysql> create view v_old1 as
> select * from student
> where birth_year < 1985
> with check option
mysql> update v_old1
> set birth_year ='1986'
> where stu_no ='20001001'
에러 발생
View 구조 보기
Desc 뷰이름;
모든 View 보기 (테이블과 같이 나온다)
Show tables;
뷰 테이블 통계함수 사용
① WHERE 절에 통계함수 사용
등록테이블로부터 학번과 학생별 등록금 납입 총계의 합계로 구성하는 뷰 테이블을 생성
mysql> create view totals
> (stu_no, fee_total) as
> select stu_no, sum(fee_pay)
> from fee
> group by stu_no;
② GROUP BY 절을 이용한 VIEW 테이블과 다른 테이블과 JOIN
뷰 테이블 생성시 GROUP BY 절을 가지고 있다면 뷰는 또 다른 뷰나 테이블과 조인할 수 있다.
③ WHERE 절에 다른 테이블을 부속질의어로 사용하는 경우
뷰 테이블을 질의 할 때 다른 테이블을 부속질의어로 사용할 수 있다.
④ ORDER BY 절을 사용하는 경우
뷰 테이블을 질의 할 때 ORDER BY 절을 사용할 수 있다.
⑤ 집합 연산자를 사용하는 경우
뷰 테이블을 질의 할 때 집합 연산자를 사용할 수 있다.
⑥ HAVING 절을 사용하는 경우
뷰 테이블은 HAVING 연산자를 사용할 수 있다.
수강신청 테이블에서 학생별, 수강년도별, 학기별로 그룹을 만들고 이 그룹의 수강신청 학점이 5학점 이상인 학생의 학번, 연도, 학기, 수강학점 계를 뷰 테이블 "V_SUGA"를 생성하라.
mysql> create view v_suga(bunho, v_yy, v_hakgi, v_hakjum) as
> select stu_no, att_year, att_term, sum(att_point)
> from attend
> group by stu_no, att_year, att_term
> having sum(att_point) >=5;;;;;;;;;;;;;
View 사용시 주의사항
1. 뷰가 집계 값(SUM, COUNT, AVG 같은)을 사용한다면, 데이터의 변경에 뷰를 사용할 수 없다.
2. 뷰에 Group By, Distinct, Having이 포함되어 있는 경우 데이터를 변경할 수 없다.
3. 테이블을 삭제하기전에 반드시 뷰를 먼저 삭제해야한다.
데이터베이스 시스템마다 다르나 MySQL의 경우 뷰에 관련된 테이블이 존재하지 않으면 그 뷰를 Drop할 수 없다.
뷰의 응용 분야
mysql> select stu_no, class
> from student
> where stu_no in
> (select stu_no
> from fee)
> and class =3;
② 학급별로 등록한 학생의 학급 통계를 출력
mysql> select class, count(*)
> from student
> where stu_no in
> (select stu_no
> from fee)
> group by calss;
등록한 학생의 학번과 반으로 구성되는 뷰 테이블 "student_cnt"를 생성
mysql> create view student_cnt as
>select stu_no, clss
> from student
> where stu_no in
> (select stu_no
> from fee);
①와 ②번의 명령문을 아주 간단하게
① mysql> select *
> from student_cnt
> where class=3;
② mysql> select class, count(*)
> from student_cnt
> group by class;
댓글