[ DB ] Chapter 3. Introduction to SQL

해당 게시물은 건국대학교 김욱희 교수님의 데이터베이스 강의와

DATABASE SYSTEM CONCEPTS 7th 원서를 참고하여 작성하였습니다. 

---

 

1. Data Definition Language (DDL)

 

DDL은 컴퓨터 사용자 또는 응용 프로그램 소프트웨어가 컴퓨터의 데이터를 정의할 수 있는 언어이다. 그중에서도 SQL은 관계형 DB의 구조를 정의한다. SQL에 의해 정의되는 관계형 데이터베이스의 구조는 튜플, attribute, relation, index파일 위치 등 데이터베이스 고유의 특성을 포함하고 있다.

또, relation의 스키마와 보안 및 권한, 디스크에 있는 물리적 sorage 구조도 담고 있다.

 

---

 

2. SQL Data Definition

 

Basic Type

SQL에는 기본적으로 탑재되어있는 타입들이 있다.

• char(n) : 길이가 n인 문자열이다
• varchar(n) : n을 최대로 하는 가변길이 문자열이다
• int : 정수집합
• smallint : 작은 정수 집합
• numeric(p,d) : 정밀도를 가진 고정 소수점을 표현한다. p자리를 표현하고 소수점 d 자리만큼 표현한다.
• real, double precision : 정밀도를 가진 부동 소수점 숫자
• float(n) : 정확도가 n 인 부동소수점

모든 타입은 null 값을 가질 수 있다.

Create

SQL은 table을 만들 때 creat table명령어를 사용한다.

creat

위 쿼리를 실행시키면 ID, name, dept_name, salary라는 attribute를 가진 instructor 테이블이 생성된다.

여기에는 여러가지 조건을 더 추가할 수 있다.

• primary key (A1,A1,..An) : 안에 있는 attribute들을 primary key로 만들어준다. 이는 null이 들어올 수 없고 키 속성이 모두 같은 튜플은 있을 수 없다.
• foreign key (B1, B2, ,,,Bn) references s : s에 있는 값을 기반으로 만들어진다.
• not null : null 값을 허용하지 않는다.

이 조건들을 활용한 예시는 다음과같다.

creat

Update

• insert into A values(a,b,..) : 테이블에 튜플을 추가하는 명령어. A라는 테이블에 value들을 추가한다. 여기서 value들은 A의 attribute 순서대로 작성해주어야한다.
• delete from R: table과 attribute는 남아있는채로 모든 튜플을 삭제한다.
• drop table R : table을 삭제한다.
• alter table R add a b : table에 b속성을 가진 attribute a를 추가한다.
• alter table R drop a : table에 a라는 attribute를 제거한다.

---

3. Basic Query Structure

 

Select

select문은 원하는 attribute의 list를 제공한다. 위 예시는 instuctor테이블에 name이라는 attribute 속성과 내용을 출력한다.

select 뒤에 distinct를 추가하면 중복이 제거가 되고 all을 추가하면 제거 없이 모두 갖고오게 된다. all은 *으로 쓰기도 하며 아무것도 안쓰면 중복이 제거되지 않는다.

from

from문은 쿼리문에 사용되는 relation을 정의한다. 여기에는 연산이 들어갈 수도 있다.

Where

where문은 조건을 추가하고 싶을 때 사용한다. where문 뒤에 나오는 조건을 만족하는 내용만 출력된다.

위 예시는 instructor 테이블에서 부서 이름이 ‘Comp.Sci’이고 salary가 70000 이상인 name만 보여주는 구문이다.

 

---

4. Additional Basic Operations

 

Rename Operation

as구문을 이용하면 rename을 할 수 있다.

 

select

이런식으로 attribute의 이름도 바꿔줄 수 있다 .여기에선 name 이 instructor_name으로 바꼈다.

select

이렇게 relation의 이름이 길 때 줄이는 기능을 할 수도 있다.

as는 생략도 가능하다.

String Operations

SQL은 문자열를 비교하기 위한 두개의 특수 문자를 제공한다.

• Percent(%) : %에는 아무 문자열이 올 수 있다.

예를 들어 %A 라고 하면, 이는 A로 끝나는 문자열을 찾고, A%라 하면 A로 시작하는 문자열을 찾는다. %A%라면 A가 들어가는 문자열을 찾을 것이다. 이는 글자수와 상관이 없다.

• Underscore( _ ) : _에는 아무 문자가 올 수 있다.

예를 들어 a_ 라면 a로 시작하는 2글자의 문자열을 찾을 것이고, a_b 라면 a로 시작하고 b로 끝나는 3글자의 문자열을 찾는다는 것이다. 이는 글자수를 지정할 수 있다.

like연산자는 %와 _를 활용하여 해당하는 문자열을 찾아준다. %라는 문자열을 찾고싶다면 \%라고 명시해줘야한다.

또, SQL은 string을 변경시키는 함수도 제공한다

• upper(s) : 대문자로 변경
• lower(s) : 소문자로 변경
• || : 문자열 연결
• LENGTH(s) : 길이
• SUBSTRING(str, position, length) : 자르기

Ordering the display Tuples

order by는 정렬을 해주는 절이다. order by 뒤에 기준 attribute 이름을 넣고 그 뒤에 desc, asc으로 내림차순, 오름차순 설정을 해줄 수 있다.

asc a, desc b 처럼 두개를 지정해주면 a를 기준으로 오름차순으로 정렬 뒤 같은 값을 같는 애들끼리는 b를 기준으로 내림차순으로 정렬하게 된다.

Where-Cluse Predicates

SQL에는 where 안에서 사용할 수 있는 연산자가 있다

between은 범위를 지정해준다. 아래 두 쿼리문은 의미가 같다.

또, where문 안에서는 괄호로 묶어서 비교하는 것도 가능하다.

아래 두 구문은 같은 구문이다.

 

---

5. Set Operations

 

SQL에는 집합의 개념을 활용한 Union, Intersect, Except 연산이 있다. 이 연산들은 모두 중복을 없애주며, 중복을 포함하고 싶다면 연산자 뒤에 all를 주가하면 된다.

집합 연산자는 attribute이름은 달라도 상관 없지만 attribute의 타입과 순선는 같아야한다. attribute의 이름이 다를 경우 마지막 SELECT문의 attribute이름을 따른다

 

---

6. NULL Values

NULL은 알수가 없거나 존재하지 않음을 명시한다. 이는 +,-,*와 같은 연산 혹은 >, <와 같은 비교를 하면 결과가 null로 나오게 된다.

Boolean operation에서는 다음과 같은 결과가 나온다.

OR은 하나라고 true가 있으면 true를 반환하고, AND는 하나라도 false가 있다면 False를 반환한다

 

where문 안에 is null 혹은 is not null를 사용하면 null값을 갖고있는 튜플만, 혹은 null이 아닌 값만 가지고 있는 튜플을 찾아낼 수 있다.

 

---

7. Aggregate Functions

 

Aggregate functions은 relation의 attribute에 대하여 작용하여 값을 도출한다. SQL에는 다음과 같은 5개의 함수가 내장되어있다.

• Average: avg
• Minimum: min
• Maximum: max
• Total: sum
• Count: count

Aggregation with Grouping

group by 를 사용하면 지정된 attribute 별로 그룹이 만들어진다. 동일한 값을 가진 튜플이 한 그룹에 배치된다.

위 쿼리를 실행시키면 다음과같은 결과를 불러온다.

dept_name별로 값이 합쳐졌고 각 부서의 평균 salary가 생성되었다.

이 쿼리는 오류가 나는 쿼리이다.

부서별 ID가 없기 때문에 표기되지 않는다.

 

The Having Clause

Having은 튜플이 아닌 그룹에 조건을 거는 구문이다. where문은 그룹 형성 전 튜플에게 적용이 되지만 having은 그룹이 형성된 후 그룹에게 적용이된다.

이 쿼리문은 부서의 평균 salary가 42000이 넘는 부서를 찾는 구문이다.

 

Aggregation with Null

aggregation이 사용되는 중에 null값이 발견되었을 때, 보통 이를 무시한다. 하지만 count는 null을 무시하지 않는다.

 

---

8. Nested Subqueries

 

SQL은 쿼리의 중첩을 지원한다. 서브쿼리는 select-from-where구문이 다른 쿼리 구문안에 들어있다. 서브쿼리는 자격설정, 비교설정에 대한 조건을 표현한다.

 

Set Membership

SQL를 사용하면 in과 not in 을 이용하여 relation의 member들의 자격에 대해 테스트할 수 있다.

이 쿼리문은 2018년 Spring학기와 2017년 Fall학기에 열린 과목을 중복없이 출력하는 구문이다. in을 이용하여 2018Spring의 정보를 빼낸후 거기서 2017Fall 정보를 빼낸다

 

Set Comparison

비교연산자를 이용해서도 서브쿼리를 구현할 수 있다.

이 쿼리문은 Bio학과 salary보다 높은 salary를 가진 교수의 이름을 출력하는 코드이다.

여기서 some이 사용되었다. some의 의미는 Bio학과의 아무 교수보다 높다면 true를 반환한다는 의미이다. 즉, bio학과의 최소 salay보다 높은 교수들의 정보다 출력된다.

all이 쓰이기도 한다. 이는 bio의 모든 salay보다 높아야한다. 즉, bio학과 교수들중에 제일 높은 salay보다 높은 교수들이 출력된다.

Test for Empty Relations

SQL에는 exists, not exists 서브쿼리의 결과에 원하는 튜플이 있는지 테스트 하는 기능이 포함되어있다. exists문에서는 메인쿼리를 먼저 읽고 exists쿼리를 읽는다.

exists에서는 서브쿼리의 결과가 한 건이라도 존재하면 True, 없으면 False를 리턴한다. 이는 true인 경우에만 조건을 읽는다. 반대로 not exsist에서는 서브쿼리의 결과가 한건이라도 존재하지 않으면 true, 한건이라도 존재하면 False를 리턴한다.

그렇기 때문에 두 테이블에서 같은 값을 가져오려면 where절로 조건을 주어야한다.

Test for the Absence of Duplicate Tuples

SQL에는 unique라는 중복을 체크해주는 Boolean 함수가 있다. 이는 하위커리에 중복이 없으면 1, 있으면 0을 리턴한다. 반대로 not unique는 중복이 있으면 1, 없으면 0을 리턴한다.

이때 하위쿼리가 null이라면 true를 반환한다.

이는 튜플을 한줄 씩 읽으면서 unique한 값들만 모아 리턴한다.

 

위 쿼리는 R.course_id에 대하여 중복이 있는지 확인하고 중복이 없다면, course의 id들이 출력된다. 하지만 이때는 section id와 cource의 id가 겹치고 2017년도에 열린 과목들 중 중복되지 않은 튜플들과, 서브쿼리 조건에 해당하지 않아 중복체크를 받지 않은 모든 튜플들이 출력되게 된다.

아래 쿼리문은 unique를 사용하지 않고 중복을 체크해주는 쿼리문이다.

Subqueries in the From Clause

SQL은 서브쿼리가 from절에 있는 것도 허용한다. select-from-where 구조는 relation을 리턴하기 때문에 from절에 사용되도 상관이 없다.

 

위 커리의 서브쿼리는 instructor 테이블에서 부서이름과 부서별 평균 salary만 있는 relaion을 리턴한다. 그러고 메인쿼리는 그중에서 평균 월급이 42000이 넘는 부서와 부서의 월급을 select한다.

위 쿼리와 다르게 리턴되는 table명과 attribute명을 서브쿼리문 안에서 지정해줄 수도 있다.

 

The With Clause

with clause는 쿼리문 안에서만 존재하는 일시적 table인 view의 사용을 제공한다. 우리는 지금 까지 서브쿼리를 썼지만 view를 활용하면 더 쉽고 깔끔하게 같은 기능을 제공할 수 있다.

with문은 다음과 같은 형식을 갖는다.

아래 쿼리는 instructor 테이블의 부서명과, 부서의 월급의 합을 attribute로 하는 dept_total이라는 뷰와 dept_total의 부서별 월급의 합의 평균을 구해서 저장하는dept_total_avg라는 뷰를 만들었다. 그 이후로 select-from-where문을 사용하여 평균 월급의 합보다 더 높은 월급의 합을 가진 부서명을 출력한다.

 

이는 with구문없이도 충분히 짤 수 있지만, 복잡하고 이해하기 힘들것이다.

Scalar Subqueries

SQL에서는 하나의 값을 요하는 곳에서 scalar서브쿼리를 사용할 수 있다. 이는 단일 속성을 포함하는 튜플 하나만 반환한다. 하나의 값이지만 이 또한 relation형태라는 것을 알아야한다.

하나의 값을 요하지만 이때 여러개의 튜플을 반환하면 런타임 오류를 발생기킬 수 있다.

 

이 쿼리는 조건에 맞는 튜플의 개수를 리턴하므로 1행 1열이 맞다.

 

---

9. Modification of the Database

 

Deletion

SQL은 제거를 용이하게 해주는 delete함수를 제공한다.

이는 delete from ~ where~ 의 형태로 생겼으며 where문을 만족하는 튜플을 모두 삭제한다. where문을 생략하면 테이블의 모든 내용이 삭제된다.

 

하지만 아래와 같은 조건이 있다면 주의해야할 점이 있다.

 

이때 avg는 한 튜플이 delete될 때마다 다시 계산되어 평균이 계속 변하게 될것이다. 그렇게 되면 우리가 원하는 결과가 나오지 않을 수있다.

이를 방지하기 위해서는 평균을 넘는 튜플을 따로 찾아놓고 모두 delete하는 방법도 있다.

 

Insertion

SQL은 데이터를 삽입해주는 insert into함수를 제공한다.

이는 insert into ~ values~의 형식을 띄며 into뒤에는 테이블명 혹은 테이블명 뒤에 괄호로 attribute명을 적어주고, values뒤에는 넣을 데이터를 attribute순서대로 명시해주면 된다. 이때 null을 넣을 수 있다.

attribute명을 명시해줄때는 꼭 테이블의 순서대로 해줄 필요 없으며 이때는 values를 내가 앞에서 작성한 순서에 맞게 써줘야한다.

위와 같이 insert문 안에서도 서브쿼리를 사용할 사용할 수 있다. 혹시 서브쿼리 안에 테이블 attribute에 외부 attribute 중 없는 것이 있다면 위와 같이 0을 명시해주면 해당 열이 모두 0으로 채워진다. 0뿐만 아니라 null이나 다른 숫자나 문자를 명시해주어도 된다.

이처럼 자기 자신을 가져오게 되면 primary key가 사라지게된다.

또, 이를 반복하면 student는 무한이 된다.

 

Updates

upsate문은 하나이상의 데이터를 수정할때 사용한다. 기본적인 형태는 다음과 같다.

update할 table명을 명시하고 set뒤에 업데이트할 내용을 작성한다. 그리고 where에 업데이트될 조건을 작성해주면된다. 그러면 조건에 만족하는 튜플들이 set에 적힌것과 같이 업데이트되게 된다.

위 쿼리는 salary가 10,000이상이면 1.03배, 10,000이하면 1.05배를 해주는 구문이다. 이렇게 조건별로 쿼리를 작성할때는 조건이 서로 영향을 미치지 않게 순서를 잘 짜는 것이 중요하다.

이럴때는 case문을 쓰는것이 더 유용하다. 이는 when-then-else구조로 이루어져있고, 시작할때 case라고, 끝날때 end라고 명시해주면 된다.