외래키/ 조인

외래키 

create table tblProject

 (

    prjID int identity(1,1) primary key,               

    staff nvarchar(10) not null references tblStaff([name]),                     

    prjName nvarchar(30) not null,                  

 )

 

 - 외래키를 성정할려면 설정테이블의 기본키에만 가능하다.

 - 외래키는 한테이블에 2개 이상도 가능하다.

 

--------------------------------------------------------------------------------------------------------------

 

서브 쿼리

 - -- 가장 많이 보유한 비디오의 제목은?

select max(num) from tblVideo 

select [name] from tblVideo where num=10

select [name] from tblVideo where num=max(num)    -- X

select [name] from tblVideo where num=(select max(num) from tblVideo) -- sub query

 

 

-- 서브쿼리의 반환값이 여러개일때

-- 3번테잎을 빌렸거나 대여중인 회원들은?

select [name] from tblMember where pk in (select who from tblRent where what=3)

 

서브 쿼리는 단일값 리턴형식을 많이 쓴다.

 

--------------------------------------------------------------------------------------------------------------

 

조인(Join)

-- 서브쿼리 : 최종결과값이 하나의 테이블에서만..

-- 조인 : 최종결과값이 하나 이상의 

-- 내부조인

-- inner join : 교집합

-- 각테이블의 필드값을 비교해서 조건에 맞는 레코드를 선택적으로 가져옴

-- select 필드리스트 from A join B on 조건 : 조건에 맞는 레코드 반환

 

조인에서는 

select *from tblCompany 

    inner join tblPrice on tblCompany.product = tblPrice.product 보다는

 

select *from tblCompany 

    inner join tblPrice on tblCompany.product = tblPrice.product 이렇게 많이쓴다.

    (*은 조인에서는 잘안쓴다.)

 

-- 별칭사용

select C.product, P.price, C.company from tblCompany as C

    inner join tblPrice as P on C.product = P.product

    

--outer join 

 - right outer join

select tblCompany.*, tblPrice.price from tblCompany

    right outer join tblPrice on tblCompany.product=tblPrice.product

    : tblPrice의 모든 레코드를 반환하고, tblCompany.product=tblPrice.product 반환하는 결과집합

    : (tblCompany.product = tblPrice.product 를 만족하는 inner join) + ( tblPrice의 모든 레코드)

 

 

-- 다중 조인

-- 테이블 2개의 내용을 동시 출력

-- 테이블 n개의 내용을 동시 출력

 

-- 회원중 누가 어떤 테잎을 빌려갔는지 대여 목록을 출력하시오

-- 회원(tblMember), 어떤테잎(tblVideo), 대여목록(tblRent)

select m.name, r.rentDate, v.name from tblMember as m

    inner join tblRent as r on m.pk=r.who 

    inner join tblVideo as v on v.pk=r.what

    

--------------------------------------------------------------------------------------------------------------

 

-- union

-- 분리된 테이블 정보를 하나로 합치는 역활.

 

-- View [뷰로는 select 만 하자.]

-- 목적 : 1. 간편함.

-- 자주접근되는것들은 view 로 만들어서 사용하면 편리

-- select 를 기반으로 하는 가상 테이블 or 저장된 쿼리

 

create view vwVideo

as

select [name],company, director from tblVideo

 

-- 조인결과를 갖는 뷰(가장 많이쓰임)

select v.name as [비디오], g.name as [유형] from tblVideo as v

    inner join tblGenre as g on v.genre = g.name

    

create view vwVideoGenre

as

select v.name as [비디오], g.name as [유형] from tblVideo as v

    inner join tblGenre as g on v.genre = g.name

    

    

select *from vwVideoGenre