[DB] SQL - (4) View

SQL뷰는 기본테이블을 들여다보는 유리창이다. 

뷰란, 하나또는 둘이상의 기본테이블로부터 유도되어 만들어지는 가상 테이블이다. 

with check option : 뷰에 소속되지 않은 데이터 삽입 / 업데이트 / 삭제 못한다. 

with read only : 기본 테이블의 변경연산이 불가하다.  

뷰는 하나의 테이블처럼 사용된다. 

insert into cstudents (sno, sname, year) values (100, 'Hong', 3); //이것을 삽입시켜보자.

create view cstudents (sno, sname , year) as select sno,sname,year from students where dept = '컴퓨터' with check option; 

with check option 하면 insert가 reject된다. 

insert도 그렇고 update 도 마찬가지이다. 

update cstudents set year = year + 1 where dept = 'SW'; // 이것도 reject 된다. 

with check option : 뷰에 변경연산할때 뷰의 정의에 맞는 튜플에 대해서만 변경연산을 하게 해주는 것이다 .

(뷰의 조건에 합당한 튜플에 대해서만 업데이트한다.)

뷰는 업데이트가 제약사항이 심하다. 

use university;

select * from students; 

create view cstudents (sno, sname, year,dept) as select sno, sname, year, dept from student where dept = 'SW' with check option;

select * from enrol e , cstudents cs where e.sno = cs.sno and final >= 80 ;

update cstudent set year = 4 where sno = 1200; //성공 

update cstudent set year = 4 where dept  = 'CS'; //실패 

insert into cstudents (sno, sname, year, dept) values (300,'Kim', 3,'SW'); //성공 

* 중복되어 있을경우 열이름을 명세하는 것이 좋다 .

Group by이나  JOIN 쓸때는 반드시 Read only로 하자.

4. 두 과목 이상 수강하는 학생들의 기말고사 평균을 구하되, 학번 순으로 출력하라. 

select sno, avg(final) from enrol group by sno having count(*) >= 2 order by sno;

5. 'A'학점을 한과목이라도 받은 학생의 학번, 이름, 해당과목이름을 검색하라.

* select distinct s.sno, sname, cname  from enrol e , students s, course c where grade = 'A' and e.sno=s.sno and c.cno = e.cno ; 

* select sno , sname , cname from enrol e, students s, course c where sno in (select sno from enrol where grade = 'A'); 

6. 수강하는 모든 과목을 'A'학점을 받은 학생의 학번, 이름을 검색하라

* select distinct s.sno , s.sname from students s, enrol e where s.sno in(select sno from enrol where  grade = 'A') and s.sno not in (select sno from enrol where grade <> 'A'); 

* select s.sno , s.sname from students s where not exists (select 1 from grade <> 'A' and s.sno = e.sno ) and s.sno in (select sno from enrol);

7. 'A'학점을 받지 않은 학생의 이름을 검색하라. 

select distinct s.sno, s.sname from students s, enrol e where e.sno = s.sno and e.sno not in (select e2.sno from students s, enrol e2 where s.sno = e2.sno and e2.grade = 'A'); 

---

제거 연산 

drop table student; // 테이블의 모든 것을 없애는 것이다.

truncate table students; // 구조는 남기고 비우는 것이다. 이것이 더 유용하다. 

열부분 집합 뷰  

기본키를 포함한 뷰는 삽입,삭제,갱신이 자유롭다. (기본키를 포함한 뷰는 삽입/삭제/갱신이 가능) 

* 보통은 기본키를 포함하도록 만든다. 

행부분 집합 뷰 : where 절에 조건을 걸어서 뽑아내는 것이기 때문에 기본키가 포함되어있다. // 삽입,삭제,갱신 가능 

조인뷰나 통계적 요약 뷰는 변경이 어렵다. (업무 보고시 요약정보를 자기 상사에게 제공할 경우 뷰를 편리하게 사용할 수 있다.) 

---

과목별로 중간고사 평균을 출력하되, 과목번호, 과목 이름, 개설학과명 , 평균 형식으로 출력하라.

select c.cno, c.cname , c.dept , cm.mid_avg from (select cno, avg(midterm) as mid_avg from enrol group by cno) cm, couse c where cm.cno = c.cno order by c.cname; //from절에도 서브쿼리가 들어갈 수 있다. 

where절이 아니라 사실 from절 안에도 넣을 수 있고, select 안에도 부속질의가 들어갈 수 있다. 

* OLTP 시스템 ( Online Transaction Processing )

: 증권회사에서 많이 쓰이는 시스템이다. (테이블의 인덱싱이 잘 구현되어있을때 가능하다.) 

뷰는 간단한 select from where 구조를 기본키를 포함해서 만들면 변경이 가능하지만,

기본키가 포함이 안되있거나 조인, 그룹핑, 집계함수를 통해 만든경우 업데이트가 불가하다.  

with check 옵션이 붙었을때 이론적으로 가능한데 실제로 불가한 뷰가 있다. 

뷰는 업데이트 용도 보다는 read only 용으로 많이 쓰인다. 

뷰는 외부스키마이다. 외부 스키마를 구현한 것이 뷰이다.

외부스키마와 개념스키마 사이에 사상관계를 통해서 논리적 데이터 독립성을 제공한다. 

개념스키마 내용이 바뀌더라도 외부스키마 내용이 바뀌지 않을 수 있는데 이것을 할 수있게 하는 것이 뷰이다. 

뷰를 통해 실제로는 논리적 데이터의 독립성을 제공한다. 

단점) 뷰의 정의를 변경하기 어렵다 . 정의를 변경하기 어렵기 떄문에 뷰는 다시 생성해야한다.