[데이타베이스설계] Intermediate SQL

• 6-1) Natural join
• 6-2) Inner join
• 6-3) Outer join
• 6-4) View
• 6-5) Authorization

---

 

• 6-1) Natural join

join 연산자는 두개의 relation(테이블)에서 match되는 튜플들을 Cartesian product로 결합하기 위한 연산자이다.

join에는 3가지 type이 존재한다.

        - Natural join

        - Inner join

        - Outer join

이 중 먼저 Natural join에 대해서 알아본다.

 

Natural join은 두 테이블의 모든 column들을 조사하여 그 중 공통되는 공통 속성(column)을 찾는다.

그 후 두 테이블의 공통 속성을 서로 비교하여 match되는 row들을 copy한다.

(* 이때 중복 Column에 대해서는 하나만 copy한다 - Inner join과의 차이점)

Example 1

예를 들어, 위 example 1은 아래 example 2와 의미가 같다. (students와 takes테이블에서 공통되는 속성은 ID뿐)

Example 2

즉, 두 테이블에서 이름이 같은 column을 찾고 이를 기준으로 비교하여 match된 row들을 return한다.

(* Natural join에서는 ON 절을 사용하면 안된다. - 모든 Column을 대상으로 비교를 실시하기 때문이다)

Example 3

모든 Column을 비교하여 동일 이름 column인 Company_id에 대해서 비교를 실시하여 공통되는 row들을 반환한다.

(* 이때 row에 대해서 Select에서 선택한 Column들을 기준으로 반환하는데 *로 선언하면 Cartesian Product이므로 두 테이블에 존재하는 모든 Column들을 기준으로 row를 반환한다.)

 

 

 

---

 

• 6-2) Inner join

On : join에서 사용되는 Condition

on condition은 relation들을 결합할때 사용되는 일반적인 predicate(논리적 문법)이다. (Where과 동일의미)

 

Example 1

join ~ on 을 사용한 query는 아래 Example 2와 같은 의미를 나타낸다.

Example 2

 

Inner join

Inner join은 두 테이블에서 On 조건을 만족하는 row를 반환한다.

Inner join

Inner join은 위 예시처럼 Select (columns .. ) from table1 Inner join table2 on (condition ..) 처럼 사용한다.

 

 

 

* Natural join vs Inner join

가장 큰 차이점은 Select에서 show할 column을 특별히 지정하지 않고 *로 선언했을때

Natural join은 중복되는(이름이같은) column에 대해서 한 column만 copy해서 return하지만

Inner join은 아래 예시와 같이 둘다 return 한다.

Example 1 (company_id 속성 중복 출력)

두번째 차이점으로는 Inner join은 On condition을 통해 비교할 column을 지정할 수 있지만 Natural join은 모든 column을 비교하고 이 중 이름이 같은 column에 대해서만 match 비교를 한다.

 

 

 

---

 

• 6-3) Outer join

Outer join은 join연산을 할 때 정보의 손실을 피하기 위해 사용한다. (즉, 조건에 맞지 않는 데이터도 표시하고 싶을때)

기본적으로 Outer join은 match된 row, match되지 않은 row 둘다 가져오고 싶을때 사용한다.

match 되지 않은 row에 대해서는 null 값을 이용한다. Outer join은 아래 3가지 형태로 사용한다.

        - Left outer join

        - Right outer join

        - Full outer join

 

1) Left outer join

Left outer join은 두 테이블을 join할때 match된 row는 당연히 가져오고 두 테이블 중 첫번째 테이블에서 match되지 않은 row도 가져온다.

(* 이때 match되지 않았기 때문에 두 번째 테이블의 column에 대해서는 값을 갖지 않으므로 null로 채운다.)

Left outer join

 

2) Right outer join

Right outer join은 두 테이블을 join할때 match된 row는 당연히 가져오고 두 테이블 중 "두번째" 테이블에서 match되지 않은 row도 가져온다.

(* 이때 match되지 않았기 때문에 첫 번째 테이블의 column에 대해서는 값을 갖지 않으므로 null로 채운다.)

Right outer join

 

3) Full outer join

Full outer join은 두 테이블을 join할때 match된 row는 당연히 가져오고 두 테이블에서 match되지 않은 row를 모두 다가져온다.

(* 이때 match되지 않은 column들에 대해서는 값을 갖지 않으므로 null로 채운다.)

Full outer join

 

Join type에 따른 벤다이어그램

 

 

 

---

 

• 6-4) View

View란 어떤 테이블의 모든 정보를 볼 필요없이 특정 조건을 만족하고 특정 column만 보고 싶을때 만드는 가상테이블이다. 따라서 가상테이블이므로 오직 Select문만 사용할 수 있다.

create view를 하면 매번 특정 조건과 특정 column에 대해서 select ~ while ~ 을 할 필요없고 다른 user에게 제공할때 어떤 column정보를 보여주는건지 이런 자세한 data는 숨긴채 view 테이블을 제공할 수 있다.

Create view Example 1

이렇게 create view를 하면 특정 조건, 특정 column을 select *로 매번 호출할 수 있다.

Syntax : Create View (생성할view table이름) as (Select (선택할 column) from (table) where (특정조건) )

 

Index

많은 쿼리들은 table 레코드의 아주 일부분만 참조한다.

따라서 특정 값을 갖는 레코드를 찾기위해 모든 레코드를 탐색하는 건 비효과적으로 보인다.

테이블에 해당 열에 대한 index를 만들어 사용하면 특정 튜플을 찾을때 첫 row줄부터 하나하나 읽을 필요가 없어진다.

Create index (name) on (table) (attribute ... ) 형태로 index를 만든다.

Create index

 

 

 

---

 

• 6-5) Authorization

데이터베이스의 table의 part인 속성, 튜플 등.. 에 대해 유저에게 할당할 수 있는 다음의 권한들이 존재한다.

        - Read : data를 그저 읽을 수 있는 권한

        - Insert : 새로운 data를 insert 할 수 있는 권한 (수정은 안됨)

        - Update : 데이터를 수정할 수 있는 권한 (삭제는 안됨)

        - Delete : 데이터를 삭제할 수 있는 권한

이들을 privilege라고 하며 이들을 몇 가지 조합하여 권한을 줄 수도 있다.

 

데이터베이스의 relation 자체에 수정을 할 수 있는 권한도 있다. (시스템적 권한)

        - Index : index를 생성하고 삭제할 수 있는 권한 

        - Resources : 새로운 relation을 생성할 수 있는 권한

        - Alteration : relation에 새로운 속성을 추가 혹은 삭제할 수 있는 권한

        - Drop : relation을 삭제할 수 있는 권한

 

Grant

Grant절은 user에게 특정 realtion에 대해 특정 privilege를 줄 때 사용한다.

grant <privilege list> on <relation> to <user list> 형태로 사용한다.

Grant Example 1

권한을 줄 때 사용하는 실질적인 privilege는 다음과 같다. (테이블 객체 권한)

        - select : read권한이라고 보면 된다. (ex. grant select on table1 to (user1, user2))

        - insert : tuple을 insert할 수 있는 권한이다.

        - update : SQL update절을 사용할 수 있는 권한이다.

        - delete : tuple을 삭제할 수 있는 권한이다.

        - all privileges : 모든 privileges들을 준다.

 

Revoke

revoke절은 권한을 뺏을때 사용한다.

revoke <privilege list> on <table> from <user list> 형태로 사용한다.

Revoke Example 1

Role

role은 권한을 묶어서 집합으로 만들어서 줌으로써 특정 유저들을 권한으로 구분할 수 있게한다.

Role 사용 안할때

Role 사용

위 그림을 보다시피 role을 사용하면 권한들을 group으로 묶어서 쉽게 유지하고 user에게 좀 더 쉽게 줄 수 있다.

Role에 대한 프로세스는 다음과 같다.

        - 1. Create role manager; (role을 먼저 생성한다.)

        - 2. Grant create table1, create table2 to manager; (role에게 privileges를 준다. 이때 grant on 임을 주의하자)

        - 3. Grant manager To Ganghee; (다시 grant절을 사용하여 role을 user에게 준다.)

        - 4. Revoke create table1 from manager (revoke를 사용하여 privileges를 다시 뺏을 수 있다.)

        - 5. Drop role manager (role을 삭제할 수 있다.)