[데이터베이스] 중급 SQL

1. 조인 표현식

1.1 자연 조인 (Natural Join)

student 릴레이션과 takes 릴레이션을 조인
공통 속성인 ID를 기준으로 조인

sql

select name, course_id
from student natural join takes;

student와 takes를 ID 속성으로 자연 조인
두 릴레이션에서 속성명이 같은 경우 한 번만 출력

1.2 조인 순서와 동작

student natural join takes를 먼저 계산한 후

sql

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

자연 조인을 연속으로 사용할 수도 있음

sql

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

이 경우, 공통 속성인 dept_name, course_id도 일치해야 함

1.3 join ... using 구문

조인 속성을 명시적으로 지정

sql

select name, title
from (student natural join takes) join course using (course_id);

dept_name과 같은 다른 속성은 일치하지 않아도 됨

1.4 on 조건을 이용한 조인

조인 조건을 on 절로 명시

sql

select *
from student join takes on student.ID = takes.ID;

결과에 ID 속성이 두 번 나타남 (student.ID, takes.ID)
자연 조인과 유사하지만 중복 속성 제거 차이 존재

1.5 외부 조인 (Outer Join)

내부 조인: 일치하는 튜플만 결과에 포함
외부 조인: 일치하지 않는 튜플도 널 값으로 결과에 포함

1) 왼쪽 외부 조인 (Left Outer Join)

sql

select *
from student natural left outer join takes;

student 릴레이션의 모든 튜플을 보존
수강하지 않은 학생 정보도 출력
수강하지 않은 학생 찾기:

sql

select ID
from student natural left outer join takes
where course_id is null;

2) 오른쪽 외부 조인 (Right Outer Join)

sql

select *
from takes natural right outer join student;

takes 릴레이션 기준으로 모든 튜플을 보존

3) 전체 외부 조인 (Full Outer Join)

sql

select *
from (select * from student where dept_name = 'Comp. Sci.')
natural full outer join
(select * from takes where semester = 'Spring' and year = 2017);

두 릴레이션의 모든 튜플을 보존
일치하지 않는 튜플은 널 값으로 채움

2. 뷰

2.1 뷰 개념

가상 릴레이션: 실제 데이터를 저장하지 않고 질의 결과로 구성
create view 명령어로 생성

sql

create view faculty as
select ID, name, dept_name
from instructor;

뷰를 기반으로 또 다른 뷰 생성 가능

sql

create view physics_fall_2017 as
select course.course_id, sec_id, building, room_number
from course, section
where course.course_id = section.course_id
and course.dept_name = 'Physics'
and section.semester = 'Fall'
and section.year = 2017;

2.2 뷰 사용 예시

뷰를 릴레이션처럼 사용 가능

sql

select course_id
from physics_fall_2017
where building = 'Watson';

뷰 속성 이름 명시:

sql

create view departments_total_salary (dept_name, total_salary) as
select dept_name, sum(salary)
from instructor
group by dept_name;

2.3 실체화 뷰

뷰 결과를 미리 계산하여 저장
*뷰 관리(view maintenance)**를 통해 최신 상태 유지
수정 시마다 또는 주기적으로 갱신

2.4 뷰의 갱신

뷰를 통한 삽입/수정/삭제는 문제 발생 가능
예: salary 속성이 없는 faculty 뷰에 삽입 시

sql

insert into faculty values ('30765', 'Green', 'Music');

instructor 릴레이션의 salary 값을 입력하지 않아 오류 발생
해결 방법:

3. 트랜잭션

3.1 트랜잭션 개념

질의와 갱신문의 순차적 실행 단위
자동으로 시작, 다음 명령어로 종료:

commit work: 변경 내용 영구 반영
rollback work: 변경 내용 취소

시스템 장애 발생 시 rollback으로 복구

4. 무결성 제약 조건

4.1 기본 제약 조건

not null: 속성에 널 값 금지
unique: 속성 값 유일성 보장
check: 조건을 만족하는지 검사

sql

create table department (
  dept_name varchar(20),
  building varchar(15),
  budget numeric(12,2) check (budget > 0),
  primary key (dept_name)
);

4.2 참조 무결성

foreign key: 다른 릴레이션의 주 키 참조

sql

create table course (
  course_id varchar(8),
  title varchar(50),
  dept_name varchar(20),
  credits numeric(2,0) check (credits > 0),
  primary key (course_id),
  foreign key (dept_name) references department
);

참조 동작:

on delete cascade: 참조되는 튜플 삭제 시 연쇄 삭제
on delete set null: 참조 필드를 널 값으로 설정
on update cascade: 갱신 시 참조 필드도 갱신

5. SQL의 데이터 타입과 스키마

5.1 날짜와 시간 타입

date: YYYY-MM-DD
time(p): HH:MM:SS (초의 소수점 p자리)
timestamp(p): YYYY-MM-DD HH:MM:SS.ssssss
extract(field from d): 특정 연월일/시간 추출

5.2 타입 변환과 서식 함수

cast(e as t): 데이터 타입 변환

sql

select cast(ID as numeric(5)) as inst_id
from instructor
order by inst_id;

coalesce: 널 값 처리

sql

select ID, coalesce(salary, 0) as salary
from instructor;

6. 인덱스

6.1 인덱스 생성

create index:

sql

create index dept_index on instructor (dept_name);

unique index: 후보 키 선언 가능

sql

create unique index dept_index on instructor (dept_name);

drop index index_name: 인덱스 제거

7. 권한

7.1 권한 부여와 취소

grant:

sql

grant select on department to Amit, Satoshi;
grant update (budget) on department to Amit, Satoshi;

revoke:

sql

revoke select on department from Amit, Satoshi;

7.2 역할

create role instructor
grant select on takes to instructor
역할 간 계층:

sql

create role dean;
grant instructor to dean;
grant dean to Satoshi;

7.3 특권 양도

grant ... with grant option:

sql

grant select on department to Amit with grant option;

revoke grant option for select:

sql

revoke grant option for select on department from Amit;

cascade와 restrict 옵션 사용 가능

[데이터베이스] 중급 SQL

1. 조인 표현식

1.1 자연 조인 (Natural Join)

1.2 조인 순서와 동작

1.3 join ... using 구문

1.4 on 조건을 이용한 조인

1.5 외부 조인 (Outer Join)

1) 왼쪽 외부 조인 (Left Outer Join)

2) 오른쪽 외부 조인 (Right Outer Join)

3) 전체 외부 조인 (Full Outer Join)

2. 뷰

2.1 뷰 개념

2.2 뷰 사용 예시

2.3 실체화 뷰

2.4 뷰의 갱신

3. 트랜잭션

3.1 트랜잭션 개념

4. 무결성 제약 조건

4.1 기본 제약 조건

4.2 참조 무결성

참조 동작:

5. SQL의 데이터 타입과 스키마

5.1 날짜와 시간 타입

5.2 타입 변환과 서식 함수

6. 인덱스

6.1 인덱스 생성

7. 권한

7.1 권한 부여와 취소

7.2 역할

7.3 특권 양도

함께 읽으면 좋은 글

[데이터베이스] 고급 SQL

[데이터베이스] SQL

[데이터베이스] 관계형 모델 소개

[데이터베이스] 데이터베이스 기본 개념