[데이터베이스] 중급 SQL

2025-04-22

#데이터베이스

4분

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를 먼저 계산한 후
그 결과와 course 릴레이션을 카티션 곱 후 조건을 적용

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 트랜잭션 개념

•
질의와 갱신문의 순차적 실행 단위

•
자동으로 시작, 다음 명령어로 종료:

1
commit work: 변경 내용 영구 반영

2
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 옵션 사용 가능