SQL Data Types and Schemas

상위 문서: SQL

개요

SQL은 기본적인 데이터 타입 외에도 추가적인 자료형과 사용자 정의 타입(user-defined type)을 지원한다.

Type Conversion

시스템은 몇몇 데이터의 타입 변환을 자동으로 수행하지만, 명시적으로 타입 변환을 요청해야 하는 경우도 있다. 이때 cast (e as t) 형식의 표현식을 사용하여 표현식 e를 타입 t로 변환할 수 있다.
예를 들어, instructor 테이블의 ID 속성이 문자열(varchar(5))로 지정되어 있다고 하자. 이 속성으로 출력을 정렬하려 할 경우, ID 11111이 ID 9보다 먼저 온다. 왜냐하면 첫 번째 문자 '1'이 '9'보다 먼저 오기 때문이다.(알파벳 순서) 하지만 다음과 같이 작성한다면:

select cast(ID as numeric(5)) as inst_id from instructor
order by inst_id

위 코드는 cast(ID as numeric(5)) 부분을 통해서 문자열로 정의된 ID를 숫자 형식으로 변환한다. 이를 통해서 문자열 순서대로의 정렬이 아닌, 숫자 순서대로의 정렬을 할 수 있다.

Defalt Values

SQL은 어떤 속성(attribute)에 대해 기본값(default value) 을 지정할 수 있게 해준다. 다음의 create table 문이 그 예이다:

create table student (
    ID varchar(5),
    name varchar(20) not null,
    dept_name varchar(20),
    tot_cred numeric(3,0) default 0,
    primary key (ID)
);

위 코드에서 tot_cred 속성의 기본값은 0으로 선언되어 있다. 그 결과, student 릴레이션에 튜플을 삽입할 때 tot_cred에 대해 아무 값도 제공되지 않으면, 그 값은 자동으로 0으로 설정된다. 아래는 그 예시이다:

insert into student(ID, name, dept_name)
values ('12789', 'Newman', 'Comp. Sci.');

이 삽입문에서 tot_cred 속성은 제공되지 않았기 때문에, SQL은 그 속성에 기본값 0을 자동으로 할당한다.

Date and Time Types in SQL

SQL은 날짜와 시간에 관련된 여러 타입을 지원한다. 이는 아래와 같다.

date: 년도(네 자리), 월, 일로 구성된 날짜에 해당한다.
time: 시, 분, 초로 구성되는 시간에 해당한다.
- time(p)라는 변형을 사용하면 초의 소수점 단위를 지정할 수 있다.^[1]
- with timezone을 추가로 지정하여 시간대(time zone) 정보를 시간과 함께 저장하는 것도 가능하다.
timestamp: 날짜와 시간의 조합이다.
- timestamp(p)라는 변형을 사용하면 초의 소수점 단위를 지정할 수 있다.^[2]
- time with timezone을 추가로 지정하여 시간대(time zone) 정보^[3]를 시간과 함께 저장하는 것도 가능하다.

날짜와 시간 값은 다음과 같이 명시할 수 있다:

date '2018-04-25' --날짜는 반드시 연도-월-일 순서로 지정한다.
time '09:30:00'
timestamp '2018-04-25 10:29:01.45' --초를 소수점 단위로 저장 가능

날짜나 시간 값 d에서 개별 필드를 추출하려면 extract (field from d)를 사용할 수 있다. 이때 field는 year, month, day, hour, minute, second 중 하나이다. 시간대 정보는 timezone hour, timezone minute로 추출할 수 있다.

SQL은 현재 날짜와 시간을 가져오는 여러 함수를 정의한다. 예를 들어:

current_date: 현재 날짜를 반환
current_time: 현재 시간(시간대 o)을 반환
localtime: 현재 지역 시간(시간대 x)을 반환
current_timestamp: 현재의 날짜와 시간(시간대 o)을 반환
localtimestamp: 현재의 지역 날짜 및 시간(시간대 x)을 반환

SQL은 날짜, 시간 타입과 같은 다양한 숫자형 타입에 대해 산술 연산 및 비교 연산을 허용한다. SQL은 또한 interval이라는 데이터 타입을 제공하며, 날짜와 시간, 그리고 interval에 기반한 계산을 허용한다. 예를 들어 x와 y가 date 타입일 경우 x - y는 x에서 y까지의 일 수를 나타내는 interval 값이 된다. 마찬가지로, 날짜나 시간에 interval을 더하거나 빼면 다시 날짜 또는 시간이 반환된다.

Large-Object Types

많은 데이터베이스 응용 프로그램에서는 사진, 고해상도 의료 영상, 비디오와 같은 대용량 데이터 항목을 속성으로 저장해야 할 필요가 있다. 따라서 SQL은 문자 데이터용 대용량 객체 타입(clob)과 이진 데이터용 대용량 객체 타입(blob) 을 제공한다. 이 데이터 타입들에서 "lob"은 "Large OBject(대용량 객체)"를 의미한다. 예를 들어, 다음과 같이 속성을 선언할 수 있다:

book_review clob(10KB)
image blob(10MB)
movie blob(2GB)

대용량 객체를 포함한 결과 튜플을 전부 메모리로 가져오는 것은 비효율적이거나 비현실적일 수 있다. 따라서 응용 프로그램은 보통 SQL 쿼리를 이용하여 대용량 객체에 대한 locator^[4]를 반환받고, 이후 이 locator를 사용해 응용 프로그램이 작성된 호스트 언어에서 해당 객체를 조작한다.

User-Defined Types

SQL은 두 가지 형태의 사용자 정의 데이터 타입을 지원한다. 그중 하나는 distinct type이다. 여러 속성이 동일한 데이터 타입을 가질 수 있는 경우가 있다. 예를 들어, student의 이름 속성과 instructor의 이름 속성은 동일한 도메인을 가진다. 하지만, "학과 이름과 동일한 이름을 가진 강사를 찾으시오"와 같은 쿼리는 의미가 없다. 따라서 데이터베이스를 물리적 수준이 아니라 개념적 수준에서 본다면, name과 dept_name은 서로 다른 도메인을 가져야 한다고 보는 것이 타당하다. 또한 실용적인 수준에서 볼 때, 강사의 이름을 학과 이름에 할당하는 것은 프로그래밍 오류일 가능성이 높다. 따라서 이렇게 잘못된 할당이나 비교 등을 감지하고 잡아내기 위해서, SQL은 distinct type 기능을 제공한다. 아래와 같이 create type 구문을 사용해 새로운 타입을 정의할 수 있다. 예를 들어 아래와 같은 구문은:

create type Dollars as numeric(12,2) final;
create type Pounds as numeric(12,2) final;

Dollars와 Pounds라는 사용자 정의 타입을 생성하며, 이는 소수점 이하 2자리까지를 포함하는 총 12자리의 숫자형이다. 이렇게 생성한 새 타입들은 릴레이션의 속성 타입으로 사용할 수 있다. 예를 들어 다음과 같이 department 테이블을 선언할 수 있다:

create table department (
    dept_name varchar(20),
    building varchar(15),
    budget Dollars
);

만약 Dollars 타입의 값을 Pounds 타입 변수에 할당하려 하면, 비록 둘 다 numeric 타입 기반이라 하더라도 컴파일 오류가 발생한다. 이는 프로그래머가 통화 단위를 혼동했을 가능성이 높기 때문에, 타입을 다르게 선언함으로써 이런 오류를 사전에 잡아낼 수 있는 것이다. 이때 department.budget + 20과 같은 표현식은 허용되지 않는다. 이는 budget이 Dollars 자료형이고 20은 정수형이기 때문이다. 따라서 해당 연산을 수행하기 위해서는 명시적인 자료형 변환을 이용해야 한다. 이는 아래와 같다:

cast(department.budget as numeric(12,2))

Domains

SQL-92에서 도입된 <create domain>은 사용자 정의 타입과 유사한 개념인 사용자 정의 도메인을 만드는 구문이다. 예를 들어, 다음과 같이 DDollars 도메인을 정의할 수 있다:

create domain DDollars as numeric(12,2) not null;

DDollars 도메인은 앞서의 Dollars 타입처럼 속성의 타입으로 사용할 수 있다. 하지만 타입과 도메인 사이에는 중요한 차이점 두 가지가 존재한다:

도메인(Domain)은 제약조건을 가질 수 있다.
- 예를 들면 not null과 같은 제약이나, 기본값(default value)을 정의할 수 있다.
- 하지만 사용자 정의 타입은 제약조건이나 기본값을 가질 수 없다.
도메인은 강한 타입(strong type)이 아니다.
- 예를 들어, 두 도메인이 같은 int 타입을 기반으로 정의되었다면, 서로 간의 연산과 비교가 가능하다.
- 하지만 사용자 정의 타입은 앞의 Pounds와 Dollars의 예시에서 살펴보았듯이, 타입에 상관없이 서로 간의 연산과 비교가 불가능하다.

Create Table Extensions

응용 프로그램은 종종 기존 테이블과 동일한 스키마(schema) 를 가진 테이블을 생성해야 할 필요가 있다. SQL은 이 작업을 지원하기 위해 create table like 확장 구문을 제공한다:

create table temp_instructor like instructor;

위 문장은 instructor와 동일한 스키마를 가진 새 테이블 temp_instructor를 생성한다.

복잡한 질의를 작성할 때, 질의 결과를 임시 테이블에 저장하는 경우가 많다. 이를 위해서는 두 개의 명령문이 필요하다. 첫 번째는 적절한 열(column)들을 가진 테이블을 생성하는 것이고, 두 번째는 그 테이블에 질의 결과를 삽입하는 것이다. SQL:2003은 이 작업을 더 간단히 수행할 수 있도록 create table as구문을 제공한다. 예를 들어, 다음 문장은 질의 결과를 포함하는 테이블 t1을 생성한다:

create table t1 as (select * 
                    from instructor
                    where dept_name = 'Music') with data;

위의 코드에서는 새 테이블 t1을 생성한 후, 괄호 안의 select절의 결과를 보고, 어떤 열이 있고, 각 열의 데이터 타입이 무엇인지 유추하여 자동으로 t1의 구조를 정한다. select * from instructor where dept_name = 'Music' 절은 instructor 테이블에서 dept_name이 "Music"인 튜플만 선택한다. 이렇게 선택된 데이터는 with data를 이용하여 명시적으로 t1의 테이블 내에 할당된다. 만약 with data가 없다면, 테이블 구조는 만들어지지만, 데이터가 할당되지 않아 단순히 빈 테이블만 할당된다.
이때 create table ... as 문은 create view문과 쿼리를 통해 정의된다는 점에서 서로 유사하다. 하지만 가장 큰 차이점은

테이블은 생성 시점의 데이터로 내용이 고정되는 반면,
뷰(view)는 항상 최신 질의 결과를 반영한다는 것이다.

각주

↑ 기본 소수점 단위는 0이다.
↑ 기본 소수점 단위는 0이다.
↑ 한국, 미국 시간대 등...
↑ 포인터와 비슷한 역할을 한다.

[1] 기본 소수점 단위는 0이다.

[2] 기본 소수점 단위는 0이다.

[3] 한국, 미국 시간대 등...

[4] 포인터와 비슷한 역할을 한다.

[1]

[2]

[3]

[4]