3. Intermediate SQL

1. Natural join

: 두 테이블의 동일한 항목에 대해 이어 붙여서 보여줌

select name , course_id
from students, takes
where student.ID = takes.ID;

select name course_id
from student natural join takes;

 

위 두 구문은 같은 결과를 반환함.

 

 

 

주의할 점

: Natural join은 동일한 항목이 여러 개가 있을 경우 그것들이 다 같아야만 합쳐서 출력해 줌
-> 같지 않은건 빼고  출력돼서 원하는 정보들이 누락됨
-> 과도하게 정보가 삭제됨.
이럴 땐 조건절을 써야 함

예) 

select name , title
from student natural join takes , course
where takes.course_id = course.course_id;

select name , title
from student natural join takes natural join course;

 

2. outer join

 

예)

 

 

<natural left outer join>

: 왼쪽 테이블의 정보손실을 막기 위함.

동일하지 않은 정보는 null로 

 

<natural right outer join>

: 오른쪽 테이블의 정보손실을 막기 위함.

동일하지 않은 정보는 null로 

 

<natural full outer join>

: 양쪽 모두에 정보손실을 막기 위함

동일하지 않은 정보는  null로

 

* sqllite에서는 left outer join만 가능.

테이블의 순서 바꾸면 right outer join처럼 사용 가능.

 

<using절> 

() 안에 묶고 싶은 기준을 적음

동일한 항목이 course_id 뿐이라 

natural full outer join으로 했을 때와 결과는 같다.

 

<on 절>

on절을 쓰면 중복을 포함해서 이어 붙이기만 해 줌

저 뒤에 course_id도 중복돼서 나옴

 

 

 

 

3. inner join

: 두 테이블의 동일한 항목을 기준으로 합쳐줌

 

 

 

 

---

Views

: 물리적인 형태가 아닌 가상 테이블.
내가 원하는 정보만 뽑아서 만든 테이블.

 

 

뷰 생성하기

create view v as 
< query expression >

 

 

예 1)

 

예 2)

 

 

* 뷰를 참조하여 뷰를 생성할 수도 있다.

 

* Materialized Views

: 물리적으로 저장된 뷰

이 뷰는 변경사항이 있을 때마다 반영해줘야 함.

 

 

 

대부분의 sql에서 simple view만 업데이트만 가능하다.

simple view란?

• from 절에는 단 하나의 릴레이션만 있어야 함
• select절에는 일반 속성값만 올 수 있고 집계함수, distinct, 연산은 할 수 없음
  -> 당연히 group by, having 절은 불가
• select 절에 없는 속성은 null로 세팅될 수 있어야 함.

 

---

Integrity Constraints(무결성 제약)

 

1. Single Relation에 대한 제약조건

• not null
• primary key
• unique(속성) : 속성값으로 중복된 값을 가질 수 없음을 명시
• check(제약조건) : 속성값에 대한 제약조건 명시

 

 

2. Referential Integrity (참조 무결성)

foreign key(외래키로 사용할 속성명) references 참조할 테이블명(참조할 속성명)
    on delete 옵션값
    on update 옵션값

 

• 보통 foreign key는 primary key를 참조한다.
• 옵션값으론 
  cascade: 참조키가 삭제(수정)되면 외래키도 삭제(수정)
  set null: 참조키가 삭제(수정)되면 외래키는 null로
  set default: 참조키가 삭제(수정)되면 외래키는 기본값으로

 

 

---

Built-in Data Types

• date
• time
• timestamp: date + time
• interval - sqllite에서는 시간끼리 빼주면 0이 나온다.
  시간을 율리우스로 변환한 후 빼주어야 한다.
  * 율리우스력: 기원전 4713년 1월 1일부터 현재까지 며칠이 지났는지를 반환
  JULYANDAY(시간)
  -> 일단위로 변환되기 때문에 시간단위로 보고 싶다면 24를 곱하고,
  분단위로 보고 싶다면 24*60을 곱한다.
  
  

 

 

Large-Object Types

• blob(binary large object)
  : 바이너리 데이터 타입(사진, 동영상)
• clob(character large object)
  : 긴 문자데이터

이런 큰 데이터타입을 반환할 때, 직접 반환하지 않고 포인터를 반환해 준다.

 

 

 

 

Index

: 색인

인덱스를 지정하지 않으면 데이터를 검색할 때 처음부터 끝까지 다 봐야 하지만

인덱스를 지정하면 전체를 다 보지 않고 한 번에 원하는 데이터를 빠르게 접근할 수 있다

-> 성능이 향상됨

 

 

생성방법

create index 지정하고 싶은 인덱스명 on 테이블명(인덱스로 지정할 컬럼명)

 

 

 

- SQL은 중복을 허용한다!!!