4-1

5/19

 

1)View

: 물리적으로 연결되어 있는 가상의 테이블 (뷰끼리도 조인가능)
1. 편리성 : 조인을 이용하여 view테이블을 만들 수 있다. 

               -select를 목적으로 많이 쓴다
2. 보안성 : 원본테이블의 부분을 이용하여 view테이블을 만들 수 있다.

               -테이블 하나를 부분적인 내용만 보여주고 싶을 때 주로 사용
 

예1)
create view v_student1
as
select sno, sname from student;
-- v_student1는 sno랑 sname만 보여주는 뷰이다

select * from v_student1
where sno=300 or sno=400;

 

예2)

조인시 동일한 컬럼명이 있다면 반드시 명칭으로 새로운 컬럼명을 만들어야 한다

 

create view v_join2
as
select s.sno as s_sno, e.sno as e_sno
from student s
join enrol e
on s.sno=e.sno;

select * from v_join2; 

select sname from v_join2; --에러/ 뷰에 sname이 없기때문에

 

예3)
create view v_join3
as
select s.sno as s_sno, e.sno as e_sno
from student s
join enrol e
on s.sno=e.sno
where s.sno>=400;

 

--조인이 되어있으면 수정,삭제,추가를 할 수 없다

 

(위의 예제를 해보기 위해서 enrol 테이블의 fk를 지웠다)

drop  table enrol;
drop  table student;
drop  table course;


create table student(
 sno varchar2(3) ,
 sname nvarchar2(10),
 year varchar2(10),
 dept nvarchar2(10) ,
 constraint   student_sno_pk   primary  key(sno));
 

create table course(
 cno varchar2(5) primary key,
 cname nvarchar2(10),
 credit varchar2(3),
 dept nvarchar2(10),
 prname nvarchar2(10));


create table enrol(
 sno varchar2(3) ,
 cno varchar2(5) ,
 grade varchar2(3),
 midterm int,
 final int ,
 constraint enrol_combo_pk primary key(sno, cno));

 

insert into student(sno,sname,year,dept)
values('100','나연묵','4','컴퓨터');
insert into student(sno,sname,year,dept)
values('200','이찬영','3','전기');
insert into student(sno,sname,year,dept)
values('300','정기태','1','컴퓨터');
insert into student(sno,sname,year,dept)
values('400','송병호','4','컴퓨터');
insert into student(sno,sname,year,dept)
values('500','박종화','2','산공');


insert into course(cno,cname,credit,dept,prname)
values('C123','프로그래밍','3','컴퓨터','김성기');
insert into course(cno,cname,credit,dept,prname)
values('C312','자료구조','3','컴퓨터','황수찬');
insert into course(cno,cname,credit,dept,prname)
values('C324','화일처리','3','컴퓨터','이규철');
insert into course(cno,cname,credit,dept,prname)
values('C413','데이터베이스','3','컴퓨터','이성호');
insert into course(cno,cname,credit,dept,prname)
values('E412','반도체','3','전자','홍봉희');


insert into enrol(sno,cno,grade,midterm,final)
values('100','C413','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('100','E412','A',95,95);
insert into enrol(sno,cno,grade,midterm,final)
values('200','C123','B',85,80);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C312','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C324','C',75,75);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C413','A',95,90);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C312','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C324','A',95,90);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C413','B',80,85);
insert into enrol(sno,cno,grade,midterm,final)
values('400','E412','C',65,75);
insert into enrol(sno,cno,grade,midterm,final)
values('500','C312','B',85,80);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C123','A',90,90);

select * from student;
select * from course;
select * from enrol;

 

예1)
Create view v_join_se1
as
select s.sno,sname, year, cno, grade 
from student s
join enrol e
on s.sno=e.sno;

select * from v_join_se1

update student set sname='하니'
where sno='100';
--원본 student를 수정했으니까 v_join_se1도 바뀜

update v_join_se1 set sname='하니2'
where sno='100'; --에러/ 조인된 테이블에 대해서는 insert랑 update를 할 수 없다.

delete from v_join_se1 
where sno='100'; --delete는 되기는 하나 student원본테이블은 삭제안됐고 enrol원본테이블은 삭제됐다...원치않은 결과 

--view는 join되어 있으면 select만 하자!

 

예2)

Create view v_student88
as
select sno, sname from student;

select * from v_student88;

insert into v_student88(sno,sname)
values ('700','하늘이'); --원본 테이블에 sno,sname만 추가되었음

delete from v_student88 where sno=100; --원본에서 삭제되어버림 ㅜ 하지마세요 제발