[DB] 03. SQL 1

Database Languages

DBMS는 사용자가 데이터베이스에서 원하는 작업을 표현할 수 있도록 데이터베이스 언어를 지원

 

기능적 측면에서의 분류

• DDL (data definition language) : 데이터 정의 언어
• DML (data manipulate language) : 데이터 조작 언어
• DCL (data control language) : 데이터 제어 언어

객체를 표현하는 방식 측면에서의 분류

• 절차적 언어
• 비절차적 언어

---

DDL (data definition language)

• 데이터베이스 스키마를 정의하기 위한 명세
• 각 속성의 도메인 정의 명세
• 무결성 제약 조건에 대한 명세
• DDL 컴파일러는 데이터 사전에 저장되는 테이블의 집합을 생성

---

DML (data manipulate language)

• DMBS에 의해 조직된 데이터베이스 인스턴스에 접근하고 조작하는 언어
• 데이터 질의
• 데이터 삽입, 삭제, 업데이트
• query 언어라고도 불림

---

DCL (data control language)

• 트랜잭션 시작, 종료
• 세션 시작, 종료
• 백업, 복구
• 권한 부여, 회수
• 사용자 계정 관리 등
• 데이터베이스 시스템의 스키마와 인스턴스를 제외한 다른 객체를 조작하는 언어

---

절차적 언어 Procedural Language

• 필요한 데이터를 명시하고 그 데이터를 어떻게 얻을지 지정
• 대부분의 언어
• 관계 대수, C, C++, Java 등

---

비절차적 언어 Non-procedural Language

   = 선언적 언어 Declarative Language

• 필요한 데이터를 명시하고 그 데이터를 어떻게 얻을지는 지정하지 않음
• SQL, Prolog, Lisp 등
• 비절차적(선언적) 언어는 절차적 언어보다 더 발전된 언어이지만 컴퓨터 시스템에서 처리하기는 어려움

---

순수 언어 Pure Language

• 이론적으로 개발된 언어
• 실제 시스템에선 구현 X
• 관계 대수, Tuple relational calculus, Domain relational calculus =>3가지 언어는 동등한 표현 능력을 가짐

---

실제 시스템에 구현된 언어

• SQL
• QUEL
• Query by Example
• LDL(logic database language)

---

SQL 개요

• SQL : Structured Query Language
• 관계형 데이터베이스 언어
• DDL + DML + DCL의 기능을 모두 갖춘 완전한 언어
• 관계형 데이터베이스 세계에서 실질적이고 공식적인 표준

SQL 역사

• 70년대에 IBM Sequel은 System R의 일부로 처음 개발 (나중에 이름이 SQL로 변경)
• SQL-86, SQL-89 (FIPS 127-1로 채택)
• -> SQL-92 (SQL2로도 불림, FIPS 127-2로 채택)
• -> SQL:1999 (SQL3)
• -> SQL:2003, SQL:2006, SQL:2008
• -> SQL:2011, SQL:2016
• 상용 시스템은 대부분 SQL2의 기능을 제공하고 이후의 표준과 특별한 특징 기능들을 포함함
• 표준화 단체 : ISO(International Standard Organization), ANSI(American National Standards Institute), W3C, OMG

---

DDL SQL

관계의 집합뿐만 아니라 각 관계의 설명도 정의할 수 있음

• 각 관계의 스키마
• 각 속성과 관련된 값의 도메인
• 무결성 제약 조건
• 유지되어야 하는 인덱스의 집합
• 관계의 물리적 저장 구조
• 보안 및 권한

SQL 명칭은 대소문자를 구분하지 않음

인용부호(‘’, “”) 내에서만 대소문자를 구별함

---

SQL 도메인 타입

• char(n) : 길이가 n인 고정 길이 문자열
• varchar(n) : 최대 길이가 n인 가변 길이 문자열
• int : 정수
• smallint : 작은 정수
• numeric(p,d) : 소수점 오른쪽에 d 자릿수가 있는 p 자릿수의 고정 소수점 숫자 (numeric(5, 2) = xxx.xx)
• real, double precision : 부동 소수점 / 두 배 정밀도 부동 소수점 숫자
• float(n) : 최소 n 자릿수 정밀도를 갖는 부동 소수점 숫자

---

테이블 생성

• Create table 뒤에 생성하고자 하는 테이블 이름을 명시
• 속성 이름과 도메인 이름이 쌍을 이루어 작성
• 마지막에 관련된 데이터 무결성 제약을 작성

professor 테이블 정의

• 4개의 속성을 작성하고 각각의 데이터 타입을 지정
• name 속성은 null을 허용하지 않는 무결성 제약을 가짐
• Insert into professor values (‘10’, ‘Lee’, ‘CS’, 7500);
• Insert 문장은 professor 테이블에 하나의 tuple을 추가하는 명령어

---

무결성 제약

• not null : null 값을 허용하지 않음
• primary key : 테이블의 주 키로 선언 (자동으로 not null 적용)
• foreign key : 외래 키를 선언 / 참조되는 테이블의 주 키를 참조하므로 참조되는 테이블만 명시해도 됨

---

스키마 삭제/변경

• 스키마 삭제 : Drop table student;
• 스키마 변경 : Alter table statement;

Alter table r add A D;

A는 관계 r에 추가할 속성 이름, D는 A의 도메인, 새 속성은 null 값을 가짐

 

Alter table r drop A;

A는 관계 r에서 삭제할 속성 이름, 속성을 Drop하는 것은 많은 DB에서 지원하지 않음

 

Alter table student add constraint myConst foreign key (deptName) references DEPARTMENT on delete cascade;

‘myConst’라는 이름의 제약 조건을 생성, ‘deptName’ 속성을 외래 키로 지정, 이 외래 키는 DEPARTMENT 테이블을 참조, DEPARTMENT 테이블이 삭제되면 자동으로 연쇄 삭제되도록 제약 조건 설정

 

DROP : 스키마를 삭제하는 DDL 기능 (관계된 tuple까지 삭제)

DELETE : 테이블 스키마는 남기고 tuple만 삭제 (DML 기능)

DDL의 3가지 키워드 : CREATE / ALTER / DROP

 

스키마 : 테이블, 타입, 제약 사항, 뷰, 도메인 등의 모임

카탈로그 : SQL 환경에서 스키마들의 모임

SQL 환경은 0개 이상의 카탈로그를 포함 / 카탈로그는 1개 이상의 스키마를 포함

카탈로그가 항상 포함하는 1개의 스키마 : INFORMATION_SCHEMA : 정보 스키마의 뷰와 도메인을 포함

SQL 스키마는 스키마의 이름으로 식별되며 사용자, 계정 정보를 나타내는 권한 식별자와 각 요소의 디스크립터를 포함
 

Create schema my-schema authorization 'shlee'; 

-> ‘shlee’라는 사용자가 소유한 "my-schema"라는 스키마를 생성

---

DML SQL

DML의 4가지 키워드

• Select
• Insert
• Delete
• Update

---

Insert

데이터베이스 tuple을 입력하는 연산

---

Delete

tuple을 삭제하는 연산

---

Update

tuple의 특정 속성값을 변경하는 연산

---

Select SQL Statement

전형적인 SQL의 Select 질의 형태

---

select 절
질의 결과에 원하는 속성을 나열

관계 대수의 project 연산에 해당

---

where 절
결과가 만족해야 하는 조건을 지정

관계 대수의 select 연산에 해당

---

from 절

쿼리에 관련된 관계를 나열

관계 대수의 Cartesian product에 해당

---

SQL 실행 모델

• ‘from’ 절의 각 테이블에서 하나의 tuple을 가져옴 (가능한 모든 tuple 조합에 적용해야 함) (Cartesian product처럼)
• 이러한 tuple들을 ‘where’ 조건에 적용 / 참이면 통과되고 ‘group by’ 평가의 대상이 됨
• ‘group by’ 속성을 이용하여 subgroup을 형성
• ‘having’ 조건을 각 subgroup에 적용 / 참인 subgroup만 가져옴
• ‘order by’ 속성을 이용하여 결과는 재정렬되고 쿼리의 결과로 표시

---

Join

---

자연 조인

두 테이블에서 동일한 이름을 가지는 속성 간에 조인 연산을 적용

중복이 제거됨

동일한 이름을 가진 무관한 속성끼리의 원하지 않은 조인이 일어날 수 있으므로 주의해야 함

 

교수의 이름과 그들이 강의하는 강의 제목을 나열하는 질의

---

Rename 연산

SQL에서는 “as” 절을 이용하여 관계와 속성을 재명명할 수 있음 (기존 이름 as 새 이름)

---

String 연산

문자열 비교 연산자인 “like”를 사용할 수 있음

2개의 특수 문자를 사용하여 패턴을 설명 ( % , _ )

• % : 어떤 substring과도 일치 (empty string 포함)
• _ : 어떤 단일 문자와도 일치

 

그 외에 다양한 문자열 연산을 지원

• 문자열 연결(“||” 사용)
• 대문자를 소문자로 변환
• 소문자를 대문자로 변환
• 문자열 길이 찾기
• Substring 추출

---

Tuple 정렬

오름차순이 기본 설정

 

내림차순 : order by name desc

여러 속성에 대해 정렬 가능 : Order by deptName desc, name

-> deptName 값 기준으로 내림차순 정렬 후 동일한 deptName 값 사이에서는 name 속성 값 기준으로 오름차순 정렬

---

where 절 연산자

“between” 비교 연산자 사용 가능

---

중복

• SQL은 관계에서 동일 tuple의 중복을 허용
• 즉, SQL 테이블은 tuple의 Multiset이라고 볼 수 있음
• 기본적으로 중복에 상관없이 입력 테이블의 모든 tuple에 대해서 연산을 수행
• 결과로 나오는 모든 tuple도 중복에 상관없이 결과 테이블에 속함

---

집합 연산

• 집합 연산 : "union", "intersect", "except" (각 연산은 자동으로 중복 항목을 제거)
• 집합 연산에서 중복을 허용하는 방법 : "union all", "intersect all", "except all" 사용
• 관계 r에서 tuple은 m번 발생, 관계 s에서 tuple은 n번 발생한다고 가정
   "r union all s"에서는 m+n번 발생
   "r intersect all s"에서는 min(m, n)번 발생
   "r except all s"에서는 max(0, m-n)번 발생 (첫 번째 집합에서 두 번째 집합에 있는 원소를 제외한 결과를 생성)

---

SQL 질의문 작성 예제

---

Null Values

tuple의 일부 속성은 null 값을 가질 수 있음 -> 알 수 없는 값 또는 존재하지 않는 값을 의미

null이 포함된 산술 계산 식은 항상 null을 반환 -> ‘5+null’은 항상 null을 반환

 

“is null” 조건을 사용하여 null 값의 여부를 확인 가능

 

null이 포함된 비교 연산의 결과는 ‘unknown’, 즉 모름임

 

where 절의 조건문 결과가 ‘unknown’이면 ‘거짓’으로 처리됨

 

Three-valued 논리 연산자

• True : 1
• False : 0
• Unknown : 0.5
• AND 연산자 -> 최솟값을 취함
• OR 연산자 -> 최댓값을 취함
• NOT 연산자 -> (1-value)를 취함

예제

• True AND False -> 1과 0 중에서 최솟값을 취함 -> 0 -> False
• True OR Unknown -> 1과 0.5 중에서 최댓값을 취함 -> 1 -> True
• False가 아님 -> (1 - 0) -> 1 -> True
• Unknown이 아님 -> (1 - 0.5) -> 0.5 -> Unknown