Relational DataBase - 5. SQL Intermediate(1)

Ui Jin·2022년 4월 10일
0

Data Base

목록 보기
5/11

Join

각 테이블을 연결하는 방법중 모든 경우의 수를 포함하는 방법은 Catesian Product였다. 이 때 우리가 원하는 조건을 가지는 경우만을 뽑아내기 위해서는 주로 Where조건이나, Join을 사용하게 되는데 사실 이 Join에는 다양한 방법이 존재한다.

1. Join 옵션

특정 조건은 다음과 같은 키워드로 미리 정해져 있다.

1) natural

SELECT *
FROM instructor natural join department

두 Table에 존재하는 Attribute중에서 이름이 같은 Attribute를 기준으로 값이 같은 Tuple만을 골라준다.

2) using()

SELECT *
FROM instructor join department using(dept_name)

department의 dept_name이라는 Attribute만을 사용해서 natural join하는 방법이다.

이렇게 =라는 특징을 이용해 두 Table을 연결하는 방법을 Equi-Join이라고 한다. Using()은 이 Equi-Join만 표현이 가능하다.

3) on()

SELECT *
FROM instructor join department on(instructor.dept_name=department.dept_name)

on 뒤의 조건을 만족하는 tuple만 골라 join하는 방법이다.

이 때, 위의 3 예시가 똑같이 출력될까?
: 아니다. join on은 중복되는 Column도 모두 나누어 출력해주는데, natural이나 using은 중복되는 Column중 하나만 출력하게 된다.

2. Join의 종류

1) Inner Join

Join조건 중에서 조건을 가장 엄격하게 검사하는 방법으로 여태까지 우리가 알아보았던 Join은 모두 이 Inner Join이다. (보통 inner 생략)

2) Left Outer Join

Join연산을 하되, 일단 왼쪽의 Table은 전부 출력하도록 하는 Join 연산이다.

즉, 조건에 맞게 합치되, 왼쪽의 Table과 연결되는 오른쪽 Table 중에서 조건을 만족하지 못하는 Tuple들은 Null값으로 채워져 나온다.

3) Right Outer Join

Left Outer Join과 비슷하게 일단 오른쪽의 Table은 전부 출력하도록 하는 Join 연산이다.

즉, 조건에 맞게 합치되, 오른쪽 Table과 연결되는 왼쪽의 Table 중에서 조건을 만족하지 못하는 Tuple들은 Null값으로 채워져 나온다.

4) Full Outer Join

Full Outer Join은 조건에 맞게 합치되 오른쪽과 왼쪽의 Table중 누락되는 데이터가 없도록 전부 출력하는 Join연산이다.

이는 Catesian Product와 헷갈릴 수 있지만 Full Outer Join은 특정 조건을 검사하므로 이 Catesian Product안에 속한 집합이나오게 된다.

2. view

1) Temporary View

Temporary View는 우리가 앞에서 배웠던 With Clause를 의미한다. 즉, 해당 SQL문을 실행할 당시에만 잠시 실행하여 임시 Table을 만들게 된다.

WITH 테이블이름(컬럼이름) AS 
	(SELECT문)
  • SELECT문을 실행하여 얻은 Table에 대해 Temporary View로 제공한다.

2) View

Temporary View와는 다르게 해당 DB에서 영구적으로 사용 가능한 일종의 가상 Table이다.

CREATE VIEW 테이블이름(컬럼이름) AS
    (SELECT문)
  • 뷰를 사용할 때마다 이 CREATE VIEW 쿼리를 실행해 테이블을 얻게된다.
    (즉, 미리 해당 Table을 저장하는 것은 아니다.)

(미리 View를 실제로 Table에 저장하는 것을 Materialize View라고 한다. 이는 Update시에 Overhead가 발생하므로 만약 이를 사용하고자 할 때, Update가 빈번하지 않은 Table에 대해서만 사용하도록 하자. )

3) View를 사용한 Update

View는 사용자로 하여금 해당 Table의 전체를 제공하지 않는다.
따라서 이 View를 사용하여 Update할 때 다음과 같은 약간의 제약조건이 발생하게 된다.

  1. View에 Primary Key가 포함되지 않을 때
  2. View에 not null이어야 하는 Attribute가 포함되지 않을 때
  3. View가 여러 Table에서 Table을 만들어 사용하고 있을 때

위와 같은 경우는 아무리 View를 통해 Insert를 수행하려고 해도 Create시에 설정한 Table의 조건을 어기게 되므로 Update의 수행이 불가능하다.

만약 모든 조건을 만족해 Insert를 수행한다면 입력되지 않은 부분은 자동으로 Null값이 채워져 입력되게 된다.

위를 만족한다고 하면, View의 조건에 맞지 않더라도 Update를 수행할 수 있게된다.

즉, 다음과 같은 상황을 생각해 보자

CREATE VIEW history_instructors AS
SELECT *
FROM instructor
WHERE dept_name = 'HISTORY'

INSERT INTO history_instructors values('Brown', 'BIOLOGY')

View는 역사학과에 대한 것만 제공하는데, 생물학에 대한 정보를 넣을 수 있게 되는 것이다. 이 문제는 다음과 같이 WITH CHECK OPTION을 통해 해결할 수 있다.

CREATE VIEW history_instructors AS
SELECT *
FROM instructor
WHERE dept_name = 'HISTORY' WITH CHECK OPTION

이 경우에는 View를 사용한 Update시에 항상 where 조건 검사하게 된다.

3. Transaction

Data Base에서의 Transaction은 다음과 같은 방법으로 동작하게 된다.
1. Commit
2. Rollback

여러 SQL 문장을 수행할 때, DataBase는 내부적으로 결과를 저장하고, 하나의 SQL문장이 끝날 때마다 이 결과를 Commit해 DataBase에 반영하게 된다.

즉, 만약에 Commit이 되지 않는다면 마지막 Commit의 Data 구조로 돌아가는 Rollback이 일어난다.

이 때 Transaction을 위해 여러 SQL을 한번에 수행하고자 할 때, 자동으로 Commit되던 auto Commit을 잠시 꺼두고 사용자가 Commit을 하도록 하면 Transaction을 구현할 수 있다.

begin
(SQL문장) ...
(SQL문장) ...
end;

Integrity Constraint

지금까지 보아 왔던 CREATE TABLE을 할 때 설정할 수 있는 조건은 Primary key, not null, foreign key가 있었다. 이는 Table을 Update시에 항상 해당 조건을 검사하도록 하는 장치이였다.

이 Constraint는 몇가지가 더 있는데 이것과 또 앞에서 배웠던 것들에 추가적인 옵션들을 알아보자.에

1. not null

--- name에 대해서 null값 허용하지 않음
name varchar(20) not null

말 그대로 null값을 허용하지 않는 Attribute를 지정할 때 사용한다.

2. unique

--- {id, dept_name}에 대해서 중복 불허
unique(id, dept_name)

하나의 Attribute에서 중복되는 값을 허용하지 않을 때 사용한다.
(즉, candidate key를 지정하는 것으로 중복은 불허하지만 Null값은 허용한다.)

3. primary key

--- {id, dept_name}에 대해서 중복불허
primary key(id, dept_name)

한 Attribute에 대해 Nult null옵션과 Unique옵션을 합쳤다고 생각하면 된다.

4. check

---그 외의 조건들 표현
check(budget > 0),
check(semestor in ('Fall', 'Spring', 'Summer', 'Wintter'))

만약 조건을 따로 SQL문법을 사용하여 표현하고 싶을 때 사용할 수 있다.

5. foreign key

CREATE TABLE course(
	dept_name varchar(20) references department,
	instructor varchar(20),
foreign key(instructor) references department

);


- 옵션:
   - cascade
     - `on delete cascade`
   	 : 참조하고 있는 테이블에서 삭제가 발생하면 현재 테이블의 튜플을 삭제 
     - `on update cascade`
     : 참조하고 있는 테이블에서 업데이트가 발생하면 현재 테이블에서도 업데이트
      (ex. dept의 cse라는 이름이 ai로 바뀜
      -> dept를 참조하는 course테이블에서도 cse를 ai로 바꿈)
   - set null
   : 삭제가 발생하면 튜플삭제가 아닌 해당 값만 null로 바꿈
      - `on delete set null`
      - `on update set null`
   - set default
   : 삭제가 발생하면 튜플삭제가 아닌 해당 값을 default값으로 바꿈
      - `on delete set default`
      - `on updqte set default`


```sql
foreign key dept_name references department
    on delete cascade
    on update cascade
foreign key dept_name references department
    on delete set null
    on update cascade

(default value: 생성시 디폴트값으로 설정)

create table student(
    ID varchar(5) primary key,
    name varchar(20) not null,
    dept_name varchar(20),
    tot_cred numeric(3,0) default 0,
);

6) deferred
: 유예설정(자기 자신을 참조할 때 update시 trasaction에 문제 해결)

  1. 제약조건에 별명부여
constraint father_ref foreign key father references person,
constraint mother_ref foreign key mother references person

  1. 제약조건에 대한 deffered 설정

    • 예시: 
    create table person(
    ID      char(10),
    name    char(40) primary key,
    spouse  char(40) not null,
    
    set constraints spouse deferred
);
begin
    insert into person values(1, 'john', 'mary');
    insert into person values(2, 'mary', 'john');
end;

    : transaction이 완료 될 때 유예된 조건을 검사하겠다는 것(주로 self references를 사용할 때 사용)

Large object를 위한 몇가지 새로운 자료형

  • clob(크기): text를 위한것
  • blob(크기): 이미지, 동영상을 위한 것

ex.

review clob(10KB)
movie blob(2GB)
profile
Ui Jin
github로 이전 중... (https://uijinee.github.io/)
이전 포스트

Relational DataBase - 4. SQL Basic(2)

다음 포스트

Relational DataBase - 6. E-R Model

0개의 댓글

관련 채용 정보