![[데이터베이스시스템] Chapter 3: Introduction to SQL](https://img1.daumcdn.net/thumb/R750x0/?scode=mtistory2&fname=https%3A%2F%2Fblog.kakaocdn.net%2Fdn%2FxGY6y%2FbtsbRJN0mVM%2FEoCMANyK2ADVgtN66hkuLk%2Fimg.png)
SQL Parts
SQL은 데이터베이스와 상호작용하기 위한 언어로, 기능에 따라 다음으로 나눌 수 있다.
DDL(Data Definition Language) : 데이터베이스의 구조를 정의한다. 릴레이션의 스키마, 무결성 제약 조건(integrity), 관련 속성들의 타입, 뷰(View), 액세스 권한, 물리적 저장 구조 등을 정의한다. 릴레이션 삭제/수정하는 기능도 제공한다.
DML : database에서 tuple을 수정, 삽입, 제거, 정보 검색이 가능하다.
DCL : 데이터의 사용 권한을 관리하는 데에 사용된다.
Embedded SQL, Dynamic SQL : SQL이 c++, 자바와 같은 일반 프로그래밍 언어에 내재될 수 있다.
Domain Types in SQL
다음과 같은 특징들이 있다.
다음은 DDL에서 쓰이는 명령어들이다.
Create Table Construct
create table이라는 명령어로 SQL relation이 정의된다.
이 아래에는 Integrity Constraints를 적는다.
보통 primary key와 foreign key를 정의하고, null 여부를 적는다. 이러한 제한 조건을 통과하는 것만 삽입되도록 할 것이다.
여기서부터는 DML이다. 다음과 같은 명령어를 통해 table을 update한다.
릴레이션을 제거할 때 drop table 명령을 쓴다. delete from table보다 더 강한 명령어인 것이, delete는 릴레이션은 남아있지만 모든 튜플들을 제거한다면, drop은 릴레이션 스키마까지 모드 지우기 때문에, create table을 하지 않는 이상 해당 릴레이션을 사용할 수 없다. alter은 이미 존재하는 릴레이션에 속성을 추가하거나 제거할 때 사용한다. 새로운 속성을 넣으면 모든 튜플에 대해 일단 null로 할당이 이루어진다.
select문을 query문이라고 한다. 다음과 같은 형식을 갖는다. 쿼리문의 결과는 relation이다.
쿼리문이 동작하는 순서는 보통 다음과 같다.
1. from 절에 나열된 릴레이션들의 카티션 곱을 생성한다.
2. 1번 결과에 대해 where 절에 명시된 조건을 적용한다.
3. 2번 결과의 모든 튜플에 대해 select 절에 명시된 속성을 출력한다.
select은 query의 결과에서 원하는 속성들을 열거한다. (relational algebra에서 projection operation과 유사하다.)
또한 SQL은 대소문자를 가리지 않는다. NAME = Name = name
결과가 나오면 위와 같이 중복된 것들이 나올 수 있다. 이를 방지하기 위해서는 distinct를 쓰면된다. 이와 반대로 all을 쓰면
중복값들도 모두 출력된다. 따로 적지 않으면 select all로 나온다.(default값)
*(asterisk)는 모든 속성이 나오도록 한다.
쿼리문에서 select만 필수이고 from, where은 모두 옵션이다. 즉 적지 않아도 결과가 잘 나온다.
The where Clause
결과를 출력하는 조건을 나타낸다. (relational algebra의 selection과 유사하다)
and, or, not이라는 논리적 연산자를 사용할 수 있고, <, > 등의 comparison operators도 사용가능하다.
+ between : comparison operator이다. 다음과 같이 사용할 수 있다.
튜플 간 비교도 가능하다.
The from Clause
from에 여러 relation을 넣으면 두 table에 대한 Cartesion product를 하라는 의미이다. 다른 테이블에 같은 이름의 속성이 있을 경우에는, (예를 들어 instructor에도 ID, teaches에도 ID가 있을 경우) 결과 table에서는 instructor.ID, teaches.ID와 같은 형식으로 나온다.
Cartesion product 자체는 의미없는 정보도 많이 생산하므로 그리 실용적이지 않지만, where문에서 적절한 조건을 추가해주면 유용하게 쓸 수 있다.
The Rename Operation
이러한 식으로 한 테이블 안에서 비교할 때 T, S라는 이름을 주어 비교할 수 있다. as를 생략해도 똑같이 작동한다. 긴 이름의 릴레이션을 짧게 바꿀 수 있으므로 효과적이다.
Self Join Example
자기 자신의 테이블과 결합하는 경우가 있는데, 다음 테이블을 보자.
만약, 위 테이블에서 Bob의 supervisor의 supervisor을 알아보고싶다고 가정하면, 다음과 같이 코드를 짤 수 있을 것이다.
SELECT E2.supervisor
FROM emp-super E1, emp-super E2
WHERE E1.person = "Bob" and E1.supervisor = E2.person
String Operations
특정 문자열과 비교할 때 like라는 operator을 사용한다. 지원되는 와일드문자가 몇 가지가 있는데, 다음과 같다.
%: 임의의 문자열을 대신한다. 즉 where name like 축구%라고 하면 이름이 축구로 시작하는 모든 column을 찾는다. %축구%로 하면 축구가 포함된 모든 column을 찾는다.
_: 특정 위치에 한 문자만 대신한다. _구% 라고 하면 두번째 위치가 구인 모든 문자열을 찾을 것이다.
와일드카드 %가 아니라 문자열 %을 검색한다면 앞에 escape character '\'를 붙인다.
Ordering the Display of Tuples
오름차순 혹은 내림차순으로 결과를 정렬할 수 있다. asc가 default값이다. 다음과 같이 사용한다.
order by name desc
multiple attributes를 대상으로도 sort할 수 있다. (첫번째로 적은 속성이 가장 우선순위가 높을 것이다.)
Set Operations
union, intersect, except를 통해 relaional algebra에서 합집합, 교집합, 차집합과 같은 연산을 할 수 있다. union은 두 테이블을 합치고, intersect는 둘다 참인 경우만 합치며, except는 첫번째 결과에서 두번째 결과를 제외한다.
보통 자동으로 중복을 제외해주는데, union all, intersect all, except all이라고 쓰면 중복을 허용한다.
Null Values
null은 값이 존재하지 않거나 unknown값일 때 사용한다.
arithmetic expression을 할 때 null값이 포함되어있으면 무조건 null을 반환한다.
5 + null -> null을 반환한다.
where 조건문에서 is null, is not null을 통해 null value를 확인할 수 있다.
null 관련 비교 연산은 보통 unknown값이 되는데, and와 or 연산자를 사용할 경우 true/false값이 결정될 수도 있다.
그리고 where clause에서 unknown 결과가 나왔다면 false와 같이 다룬다.
Aggregate Functions
Aggregate Function은 입력으로 집합을 가지며, 출력으로는 단일값을 갖는다. 보통 다음과 같은 내장 함수가 있다.
Aggregate Functions – Group By
여러 튜플 집합으로 나누어 각각의 결과를 알고 싶을 때 사용한다. 예를 들어 각 학과의 봉급 평균을 알고 싶다면, 학과이름대로 group을 만들고, 그 내부에서 avg(salary)를 적용할 수 있다. 결과는 다음과 같다.
당연한 말이지만, select에서 aggregation function없이 쓰는 속성은 무조건 group by 속성과 같아야 한다. 아래 예시와 같은 경우
한 그룹에서는 한 튜플만 출력해야하는데, ID와 같은 경우는 그룹 내에서도 사람마다 다르기 때문이다.
Aggregate Functions – Having Clause
having clause의 경우, group by 결과 나오는 그룹에 조건을 주어 제한하는 역할을 한다.
만약 평균 연봉이 42000 이상인 그룹만 보이고 싶다면 다음과 같이 하면 된다.
Nested Subqueries
하나의 쿼리 안에 또다른 쿼리 (select-from-where)이 있는 것을 의미한다. 보통 where 절에서 하위 쿼리를 중첩시킴으로써 집합에 속해있는지를 테스트하고, 비교나 원소 개수를 결정하는 데에 사용된다.
다음과 같은 쿼리문이 있을 때,
r1, r2 ..은 어떠한 subquery로 대체될 수 있다.
P 같은 경우도 속성 <operation> (서브쿼리) 형식으로 대체할 수 있다.
A1, A2 ..의 경우도 single value를 생성하는 서브쿼리로 대체될 수 있다.
Set Membership
생성된 서브쿼리는 특정한 릴레이션이 반환될 것이다. 여기에 특정 튜플이 속하는지 아닌지를 in/not in 키워드를 통해 알 수 있다.
Set Comparison
Set Comparison은 some, all 연산자를 통해 서브쿼리 결과의 릴레이션과 바깥 쿼리를 비교하는 것이다. 아래 두개는 같은 결과를 반환한다.
some: subquery의 결과 중 하나라도 조건을 만족시키는게 있다면 해당 튜플을 참으로 판단
some은 In 연산과 동일하지만 some 부정이 not in과 동일한 것은 아니다.
all: subquery의 결과 모두와 비교했을 때, 항상 참이어야 해당 튜플을 참으로 판단
all이 아닌 것(하나라도 해당되는 게 없는것)은 not in과 유사하지만, all은 in과 다르다.
Test for Empty Relations
질의의 결과로 튜플이 존재하는지 아닌지를 확인할 수 있다.
exists: 질의의 결과가 존재하면 true, 아니면 false
Correlation name : outer query에서 as에 의해 정의된 새로운 이름을 의미한다. 여기서는 S이다.
Correlated subquery : inner query를 의미한다. 바깥 쿼리의 Correlation name을 사용하는 하위 query를 Correlated subquery라고 한다.
위 식은 이중 for문과 유사하다고 보면 된다. S의 행 하나에 대해 하위 쿼리문에서 T값을 바꿔가며 테스트하고, 하나라도 일치하면 true를 반환한다.
Test for Absence of Duplicate Tuples
unique : subquery의 결과가 중복된 튜플을 가지는지 확인할 수 있다. 중복된 결과를 가지지 않으면 true를 반환한다. 결과가 빈 집합이어도 true로 반환된다고 한다.
만약 적어도 두번 제공된 강의를 찾고싶다면 not unique를 달면 될 것이다.
-> unique, exist 개념이 조금 헷갈리므로 관련 예제를 풀어봐야겠다.
Subqueries in the From Clause
SQL은 from 에서도 subquery를 허용한다. (subquery의 결과는 릴레이션이고, from 다음에 릴레이션이 오면 되기 때문)
"평균 급여가 42000 이상인 학과 교수들의 평균 급여를 구하라"일 때, having 절을 이용해서 구할 수도 있지만,
from의 subquery를 이용해서도 구할 수 있다.
With Clause
with clause는 with를 사용한 쿼리에서만 유효한 임시 릴레이션을 제공한다. 그 당시에만 동적으로 생성되었다가 지워진다.
Complex Queries using With Clause
학과의 총합 salary가 학과별 총합 salary의 평균값보다 큰 경우의 학과 이름을 알고자 할때 위와 같이 작성한다.
그러기 위해 with를 사용하여, dept_total과 dept_total_avg를 일시적으로 생성하여 값을 저장하고 select 연산을 통해 비교한다. 연산 후 두개의 테이블은 사라진다.
with는 복잡한 쿼리를 적을 때, 중간 결과값을 저장하는 용도로 사용할 수 있다.
Scalar Subquery
scalar subquery: subquery의 결과가 single value(하나의 튜플)인 것
scalar subquery인 경우, 어디에서나 쓸 수 있다. select 내부에서도 사용가능하다. subquery의 결과는 하나여도 릴레이션이지만, 암묵적으로 값을 추출하여 사용할 수 있다. 만약 한 개 이상의 튜플이 나온다면 런타임 에러가 발생한다.
Modification of the Database
지금까지는 데이터베이스에서 정보를 추출하는 방법을 알아보았다면, 여기서는 튜플을 어떻게 삽입, 삭제하고 어떻게 튜플 내부의 값을 변경할 것인지를 알아본다.
1. Deletion
튜플을 삭제하는 명령어이다.
2. Insertion
insert into에 릴레이션만 적어도 되고, 릴레이션이 속한 속성까지 모두 명시해줘도 된다. 릴레이션만 적는 경우에는 기존 릴레이션 순서대로 작성해야하고, 속성까지 넣어주는 경우에는 순서를 바꿔줘도 상관없다. 즉, course(dept_name ...)이런 식으로 적었다면, values도 Comp.Sci부터 적을 수 있을 것이다.
질의의 결과를 튜플로 삽입할 수도 있다. 예를 들어, 음악학과에서 144학점을 넘어가는 학생에 대해서만 넣을 수도 있다.
3. Updates
원하는 튜플의 값을 갱신시킬 수 있다. 예를 들어 교수의 월급이 인상되었다고 가정했을 때, 다음과 같이 적으면 모든 튜플에 대해 갱신이 이루어질 것이다.
여러 업데이트를 수행할 수도 있다.만약 100000 이상이면 3프로이상, 그 외는 5프로인상을 해야한다고 해보자.
SQL은 case문을 제공하는데, 이를 통해 순서와 관련해서 생길 수 있는 문제를 해결할 수 있다.
위 구문을 일반화하면 다음과 같다.
Updates with Scalar Subqueries
각 학생의 tot_cred 속성을 지금까지 이수한 수업의 학점의 합으로 설정한다고 가정했을 때, scalar subquery가 유용하게 쓰인다.
여기서는 학점이 F, null이 아니면 이수를 했다고 가정한다. 학생 릴레이션 모든 토플에 대해 takes와 course를 join해서 다음 조건을 만족하는 경우의 학점의 총합을 구하도록 한다.
(참고: natural join을 하게 되면 takes.course_id = course.course_id 조건을 할 필요가 없다. 의미있는 정보만 join을 하므로)
학생이 수업을 이수하지 못할 경우, 널값이 나올 것이다. 여기서 널값을 0으로 바꾸려면
기존 select sum(credits)를 다음과 같이 case문으로 바꿀 수 있을 것이다.
후기
chapter 3도 끝! 수업을 들을 때는 지루했는데 프로그래머스에 있는 문제 몇개 풀면서 이해해보니까 이전 챕터들보다 더 재미있었던 것 같다. 항상 생각하는 거지만 시험 전에 미리미리 정리를 하면 더 좋았을 텐데..하는 아쉬움이 든다.
'데이터베이스시스템' 카테고리의 다른 글
| [데이터베이스시스템] Chapter 4 : Intermediate SQL (0) | 2023.04.24 |
|---|---|
| [데이터베이스시스템] Chapter 6: Database Design Using the E-RModel (0) | 2023.04.21 |
| [데이터베이스시스템] Chapter 2: Introduction to Relational Model (1) | 2023.04.11 |
| [데이터베이스시스템] Chapter 1: Introduction (0) | 2023.03.12 |