Reference
- 내용전반: edwith
역사
1970년대 초, IBM이 Sequel이란걸 만들었고 이후 SQL(Structed Query Language)가 되었다. 1980년대부터 ANSI와 ISO에서 SQL표준을 제정하였고 이후부터 현재까지 만들어지는 DB들은 이 표준의 일부를 따른다 (즉, 서로 어느정도 비슷하다)
🌀 Data-Definition Language (DDL)
🔸 DDL이란?
스키마를 정의/수정/삭제할 때 사용
- 각 Relation의 스키마
- 각 Attribute의 도메인
- Integrity Constraints (데이터 제약조건)
- 기타
- Relation별 index 집합
- Relation별 보안/권한 정보
- Relation별 물리적 storage 구조 등
🔸 SQL의 도메인 타입
- char(n) : 고정길이 문자열 (n: 길이)
- varchar(n) : 가변길이 문자열 (n: max길이)
- int : 정수
- smallint : 작은 정수
- numeric(a,b) : 고정 소수점 실수 (a: 정수부길이, b: 소수부길이)
- real 가변길이 부동 소수점 실수
- float : 고정길이 부동 소수점 실수
- 기타 등등
- ex) ID VARCHAR(20) NOT NULL
= ID는 최대길이 20인 문자열이며 NULL일 수 없다 - ex) Deposit INT DEFAULT 0
= Deposit은 정수이며 값을 주지 않으면 0이다
🔸 테이블 생성
아래와 같은 양식으로 Table을 생성하게 된다. 아직 table만 생성되고 데이터는 넣지 않은 상태이다
예제에서 보이듯 Integrity Constraints들이 등장하고 있다
- not null
값을 NULL로 설정하는 것을 막음 - primary key
기본키로 지정. 값이 NULL이 될 수 없으므로 자동으로 not null 처리된다. 또한, 중복된 값을 가진 튜플이 삽입되는 것을 막음 - foreign key
외래키로 지정. reference Relation의 Attribute는 해당 Relation의 기본키여야 한다. 또한 아래 예제에서instructor의 dept_name은 반드시department에 존재하는 튜플의 dept_name만 들어갈 수 있다
🔸 테이블 삭제/수정
- drop
relation과 내부 튜플을 모두 삭제 - delete
relation은 보존하고 내부 튜플만 삭제 - alter add
Attribute 추가. 추가된 attribute에 대한 값들은 일단 NULL로 초기화된다 - alter drop
Attribute 삭제. 단, 외래키로 사용되는 Attribute를 삭제해버리면 참조 무결성을 해칠 우려가 있어 지원되지 않는 경우가 많다
🌀 Data-Manipulation Language (DML)
🔸 DML이란?
데이터를 검색/삽입/삭제/수정할 때 사용
- select : 알고자 하는 Attribute 리스트
- from : 출처 Relation 리스트
- where : 튜플 필터링 조건
🔸 구조
🔸 Select절
결과를 알고자하는 Attribute 리스트를 명시. projection 연산을 사용하여 결과 relation을 도출 (참고로, 대소문자를 구분하지 않습니다)
- select / select all
중복을 허용하는 일반적인 select (중복제거가 시간이 오래 걸리므로) - select distinct
중복을 제거하는 select
- select *
모든 Attribute. 즉, 테이블을 그대로 원하는 경우 - select 사칙연산
attribute 값에 산술연산을 가미한 결과를 얻고 싶은 경우. 원본 Relation에는 영향을 주지 않음
🔸 Where절
튜플 필터링 조건을 명시. and, or, not과 같은 논리
연산자를 사용할 수 있다. 또한, 날짜, 시간, 문자열 등에 대해 비교 연산자를 사용할 수 있다
🔸 From 절
대상 Relation을 명시합니다. 위 예제와 같이 여러 Relation을 대상으로 할 수도 있습니다
이렇게 여러 Relation을 나열할 경우 이들을 Cartesian Product한 것을 결과 Relation으로 반환합니다. 이로 인해 필요없는 튜플이 많이 끼게 되므로 여러 Relation을 사용하는 경우 where절을 함께 사용하는 것이 좋다 (참고로, 이렇게 여러 Relation을 사용하는 방법을 Join이라 한다)
🌀 DML 연산 종류
🔸 Join
SQL에서 두 개 이상의 Relation을 함께 사용하여 원하는 데이터를 얻는 방법을 Join이라 한다
Q. 강의를 하는 모든 교수와 강의과목ID를 원하는 경우 어떻게 쿼리를 요청하면 될까?
1. 우선 from에 두 Relation(instructor/teaches)을 두어 Cartesian Product한다
2. where절 ID 매칭을 통해 쓸모없는 튜플은 필터링한다
3. 마지막으로 select절에서 지정한 name, course_id Attribute만 뽑아낸다
🔸 Natural Join
where절을 사용하지 않고 모든 Attribute를 select하여 Join하는 것. 의미있는 튜플만 걸러내는 작업을 하지 않는다
일반 Join과 큰 차이점으로, 겹치는 Attribute는 합쳐준다는 점이다 (위 그림에선 ID가 합쳐짐)
단, Attribute 이름을 기준으로 합치므로, 이름은 같지만 의미가 다른 경우엔 주의해야 한다. 이 경우엔
join ~ using ~구문으로 합칠 Attribute를 선택할 수 있다
🔸 Rename
as 연산자로 출력 결과의 Relation/Attribute의 이름을 정해줄 수 있다. 혹은 중간 연산 과정에서 사용하기 위해 이름을 적당히 변경할 수 있다
같은 Relation의 튜플을 서로 비교할 때 어떤 튜플을 비교하는지 의미가 모호한데 위 예제에서처럼 T/S 등으로 rename하여 명확히 해줄 수도 있다
참고로 as는 생략가능하다 (Oracle처럼 생략이 의무인 경우도 있음)
🔸 String
SQL은 문자열 비교를 위해 like라는 String 비교 연산자를 제공합니다. 찾으려는 데이터의 정확한 단어를 모른다던지 특정 단어를 포함하는 단어를 찾고싶다던지 할 때 활용될 수 있습니다. 참고로 검색어는 대소문자를 구분합니다
- Percent(%)
임의의 문자열이 들어간다는 의미. 비울 수도 있다 - Underscore(_)
Percent와 유사한데 단 한 개의 임의의 문자가 들어간다는 의미. 이건 비울 수 없다
단, 찾는 문자 자체가 퍼센트/언더스코어인 경우가 있다. 앞에 백슬래시(\)를 붙혀주고 escape에 명시해주어 해결할 수 있다
(ex. "100 %"를 찾으려면 like '100 \%' escape '\')
이 외에도 SQL은 문자열을 합치는 연산(concatenation), 대소문자 변경, substring 추출 등 많은 문자열 연산을 제공한다
🔸 Set
union/intersect/except는 자동으로 중복을 제거하는 반면, union all/intersect all/except all은 multiset version으로 중복을 허용한다
🌀 특수한 값
🔸 Duplicate
중복된 튜플이 Relation에 존재하는 것을 Duplicate라 하고 이를 허용하는 집합을 Multiset이라 한다
예로, 튜플(t1) c1개의 사본을 가지고 있는 relation(r1)에서, t1에 해당하는 selection 연산(θ)의 결과 relation에는 c1개의 t1이 모두 들어있게 된다
🔸 NULL
NULL은 값을 모르거나 없을 경우를 의미한다. NULL은 산술/비교/집합 연산을 포함한 관계 연산을 할 때 특별한 처리가 필요하다. NULL이 포함되는 산술연산의 결과는 항상 NULL이다. 특정 Attribute 값이 NULL인 튜플을 select하는 것도 가능하다
비교연산의 결과는 대부분 unknown이 되나 경우에 따라 true/false가 결정될 수도 있다
🌀 Aggregate Functions
🔸 Basic
계산 결과가 relation이 아닌 하나의 값인 함수들을 말합니다 (avg, min, max, sum, count)
where절에서는 사용될 수 없고 select나 having절에서만 사용됩니다. 경우에 따라 중복을 허용할지 말지를 판단해야 한다 (기본 select는 중복을 허용하므로 중복을 제거하려면 select distinct 사용). 참고로, count (*)는 튜플의 개수를 세는 기능으로 distinct와 함께 사용되는 것을 금지하고 있다
🔸 Group By
Aggregate Functions 사용에 group by 키워드를 활용해야 하는 경우가 있습니다. 테이블에서 특정 Attribute의 값이 같은 튜플들을 모아 그룹을 만들고, 그렇게 만들어진 각 그룹별로 검색을 하는 기능입니다. 여러 튜플 집합으로 나누어 각각의 결과를 알고 싶을 때 사용합니다
위 예제는 dept_name 별로 grouping 후, dept_name과 salary의 평균이라는 Attribute를 select한 relation을 결과로 도출합니다
group by에 명시된 Attribute만이 select절에서 Aggregate Function 없이 명시될 수 있습니다 (생각해보면 당연함)
🔸 Having절
기존에 where절이 있었다면 group by를 사용할 때는 having절을 사용합니다. 즉, having은 group by로 만들어진 그룹에 대한 조건을 명시합니다
위 예제는 dept_name을 기준으로 grouping한 후, 각각에 대한 평균 salary를 구하여 42000이 넘지 않는 경우 필터링합니다. 이후 살아남은 튜플에 대한 dept_name과 평균 salary로 이루어진 relation을 반환합니다
- takes와 student relation을 natural join
- 이 중 year가 2009가 아닌 튜플을 필터링
- 같은 시기의 같은 수업(course_id/semester/year/sec_id)끼리 그룹핑
- 이 중 학생(ID) 수가 2명 이상인 경우만 남김
- Attribute가 course_id/semester/year/sec_id와 tot_cred의 평균으로 이루어진 relation 반환
🌀 Subquery
하나의 쿼리 안에 다른 쿼리가 있는 것을 말하며, 이를 통해 좀 더 복잡한 표현이 가능하고 여러 개의 쿼리를 하나로 표현할 수 있게 해준다. where절 혹은 from 절에 subquery를 설정할 수 있다
SQL이 제공하는 subquery 관련 기능들을 알아보자
🔸 Set Membership (in)
Set Membership이란, 문자그대로 subquery가 만들어낸 relation에 해당 튜플이 속하는지를 확인하는 것이다. in과 not in이 활용된다. 먼저 in 뒤에 오는 subquery로부터 relation을 확보한다. 이후 in 앞에 오는 where절과 결합하고 상위 query를 수행한다. 참고로, 위 예제처럼 열거형으로 조건을 걸수도 있다 (name의 값이 모짜르트 혹은 아인슈타인이 아닌 경우)
위 두 예제 중 1번 예제에 대해 과정을 살펴보면
1. section에서 봄학기 2010년도의 course_id를 추출
2. 다시 section에서 1번에서 추출한 relation내에 속하는 course_id면서 가을학기 2009년도의 course_id를 추출
🔸 Set Comparison (some, all)
Set Comparison이란, subquery의 결과 relation의 튜플들과 바깥 query를 비교하는 것이다. some과 all이 활용된다. some 뒤에 오는 subquery의 결과값 중 어느하나와도 조건을 만족시키는게 있다면 해당 튜플을 참으로 판단한다. 반면, all은 subquery의 결과값들 모두와 비교하여 하나도 빠짐없이 조건을 만족해야 참으로 판단한다
위 예제는 some을 사용한 예시이다
1. subquery에서 instructor 내 dept_name이 생물학인 salary를 추출
2. 추출된 salary와 바깥 query의 instructor salary를 비교
some절을 오랜만에 보면 헷갈릴 수 있을 것 같아 메커니즘 이해를 위한 예시를 더 첨부한다. some 뒤의 relation 값들 중 어느하나라도 조건을 만족하면 true가 된다
all절은 모든 값이 조건을 만족해야 true가 된다
🔸 Empty Check (exists)
exists와 not exists를 통해 subquery의 결과가 튜플을 하나라도 가지고 있는지 확인할 수 있다
exists를 Rename operation인 as를 사용하는 Correlation Variables과 활용할 수 있습니다
- Correlation Name : Correlation Variables이라고도 하며,
as에 의해 relation에 부여된 새 이름 - Correlated Subquery : 외부 query의 correlation name을 사용하는 subquery
예제를 분석해보면,
0. 먼저 subquery의 section과 바깥 query의 section을 구분하기 위해 Correlation Variables 설정
1. section S의 튜플 중 하나를 뽑아 where절을 돌린다
2. where절 내 subquery를 수행한다. T의 튜플 중 봄학기 2010년도 수업이면서 course_id가 1번에서 뽑았던 S의 튜플의 course_id와 같은 것을 select
3. select된게 하나라도 있다면 where절의 exists문을 true로 판단
🔸 중복검사 (unique)
unique절을 통해, subquery의 결과 relation에 중복 튜플이 존재하는지 알 수 있습니다. 주의할 점은, Empty Relation일 경우에도 중복은 없는 것이므로 true를 반환한다는 점이다
- T의 튜플 하나를 뽑아 where절을 돌린다
- R에서 course_id가 같고 2009년도인 course_id를 select
- 만약 R에 2009년도 수업 튜플 중 T의 course_id와 같은 튜플이 딱 한개라면 true
(2009년도에 해당 course_id가 수업되지 않았어도 true)
🔸 From절 subquery
subquery의 결과는 relation이기에 from절에도 충분히 사용될 수 있다
from절의 subquery는 외부 query의 Correlation Variables에 접근할 수 없는데 lateral을 사용하면 가능하다. lateral은 많은 경우에 지원되지 않는다
🔸 임시 Relation (with-as)
with절은 현재 query에서만 유효한 임시 뷰를 정의할 수 있게 해준다. 뷰는 다른 relation을 기반으로 만들어지는 가상의 relation으로 실제로 저장되지는 않는 논리적인 존재이다. 여기서 as는 기존에 사용하던 rename과는 다르게 동작한다. with절의 as는 뒤에 오는 subquery의 결과를 앞에 오는 가상 relation에 튜플로 꽂아준다
- with절부터, 가상의 relation의 이름은 max_budget이며 value라는 Attribute를 가진다
- department라는 relation에서 가장 큰 budget 값을 불러오고 이를 max_budget의 value의 값으로써 튜플을 넣는다 (max_budget이라는 relation은 value Attribute가 max(budget)인 하나의 튜플을 갖게 된다)
- max_budget으로 query를 수행한다
이 예제를 nested subquery로도 작성할 수 있지만 with절을 사용하는게 쉽고 명확하다
헷갈리니 예제 하나 더 하자면,
0. 임시 relation으로 dept_total과 dept_total_avg 두 개를 만드려 한다
1. dept_total을 먼저 만든다. instructor를 dept_name으로 grouping 후 salary의 합과 뽑아낸 것
2. 이전에 만든 dept_total로 dept_total_avg를 만든다. dept_total의 평균값 튜플 하나만을 가진다
3. 본문 query 수행
🔸 Scalar Subquery
Scalar Subquery는 하나의 값이 필요한 곳에 사용되는 것으로, 결과 relation이 하나의 Attribute에 하나의 튜플만을 가집니다
🌀 데이터베이스 수정하는 방법
주어진 Relation에서 특정 튜플을 삭제/삽입/갱신하는 방법에 대해 다룹니다
🔸 삭제
delete from r where P의 양식을 가집니다. 조건 P에 해당하는 모든 튜플을 찾아 r로부터 삭제합니다. 튜플 단위로만 삭제 가능하며 특정 Attribute 값만 삭제할 순 없습니다. 그리고 대부분의 DB는 한 번에 하나의 Relation에 대해서만 명령할 수 있습니다 (from에 여러 개 쓴다고 되지 않음. 되는 DB도 있음). delete의 where절에도 subquery를 사용할 수 있습니다
위 예제에서 데이터가 실시간으로 바뀌는 경우 문제가 발생할 수 있습니다. 외부 query의 instructor의 튜플을 순회하면서 중간에 salary 데이터가 변경되면 순회 도중에 평균값이 바뀌어 삭제 대상이 중간에 변경되는 문제가 있습니다. (완전한 해결책은 아닌듯 하지만) SQL은 순서를 바꾸어 이를 해결하고자 합니다. subquery의 평균값을 먼저 계산하고 삭제대상을 전부 규정한 후에야 삭제를 진행하는 것입니다
🔸 삽입
두 가지 방법이 있습니다. 하나는 삽입할 튜플을 직접 입력하는 것이고, 다른 하나는 삽입될 튜플의 집합을 생성하는 query를 작성하는 것입니다
먼저 삽입할 튜플을 직접 작성하는 방법은 insert into Relation([Attribute]) values ([value])이며 Attribute와 value는 순서대로 일대일 대응되어 Attribute절은 생략 가능합니다
두 번째로 삽입될 튜플의 집합을 생성하는 query를 작성하는 방법은 이미 존재한 relation/튜플로부터 불러와 추가하는 것으로 select-where-from 절을 작성하게 됩니다
삽입 전에 중복확인이 필요하므로, 삽입대상이 되는 Relation에 primary key가 반드시 정의되어 있어야 합니다. 그렇지 않다면 무한루프를 돌게 될 수도 있다고 한다
🔸 갱신
update-set-where절로 수행합니다. where절로 대상 튜플을 선택하고, set절에 정의된대로 값을 업데이트합니다
다만 위 예제처럼 combination으로 갱신하는 경우 수행 순서에 따라 문제 위험이 있습니다. 만약 5% 인상을 먼저해주면 10만이 약간 안되는 튜플은 8% 인상이 발생할 수 있습니다
이런 순서와 관련한 우려를 피하고자 case문을 사용할 수 있습니다. 기존의 set-where절을 set-case-where-then-else-end절로 작성합니다
value를 Scalar Subquery로 줄 수도 있습니다
