개론

첫번째 단계

함수

개념 Review

요약

SQL개론 - PostgreSQL 설치

요약: 이 짧은 코스는 세 부분으로 이루어진다. 첫번째 부분에서는 PostgreSQL이라는 공개 데이터베이스 서버를 이용하여 SQL에 대한 일반적인 내용을 설명한다. 두번째 부분에서는 SQL 명령어에 대해 더욱 자세하게 공부해 볼 것이다. 마지막으로 세번째 부분에서는 SQL 명령어의 고급 옵션들과 우리 프로젝트에 관련될만한 PostgreSQL만의 함수들을 알아보고 마지막으로 모든 것들을 하나로 모아 조그만 C 프로그램을 살펴볼 것이다.

들어가는 글

개론

최근까지 데이터 아이템에 액세스는 데이터베이스 계통의 잘 정의된 링크를 통해 내부적으로 연관된 개체들을 통해 이루어졌었다. 이러한 종류의 액세스는 주로 액세스 속도면에서 장점을 지니고 있었지만 커다란 단점이 있다. 예를 들면 다음과 같이 현재 존재하는 링크를 통해서만 데이터를 액세스할 수 있고,

     country -> states -> counties

     country -> counties

위의 두번째와 같은 관계를 만들려면 계통을 다시 정의하고 다시 컴파일해야 할 것이다.

실제로, 계층적 DB에서는 다양한 개체들 사이의 관계는 정적이며 DB 계통을 변경하고 그것을 다시 컴파일해야만 수정될 수 있다.

관계형 데이터베이스의 배후에 있는 기본적인 아이디어는 정적인 링크를 필요로 하지 않고 대신에 하나의 레지스터를 다른 것과 연결할 수 있도록 구분자를 이용하여 정확하게 질의 순간에 데이터를 연결시키는 것이다.

위의 글을 읽으려면 아스피린이라도 먹어야겠군요. :)

관계형 데이터베이스 매니저는 개체들의 계층을 따라 내려가기 위해 정적인 링크를 필요로 하지 않는다. 대신에 질의의 결과로서 일시적인 관계를 만드는 동안에 이러한 개체를 인지하게 되는 단일 코드를 사용한다.

그러한 인지는 코드일 뿐이다. 예를 들면 내 전화번호는

1234567

이 아니라

34 6 1234567

이다.

명확하게 내 전화번호는 country code(34), state code(6) 그리고 적절한 device number (1234567)로 구분된다.

• country의 집합에서 코드 34(스페인)은 유일하다.
• state의 집합에서 코드 34-6 (스페인/발렌치아)는 유일하다.
• device의 집합에서 코드 34-6-1234567(스페인/발렌치아/내 전화번호)는 유일하다.

방금 언급한 것을 묘사하는 첫번째 기본내용을 정립해보자.

     모든 county들은 state와 country에 속하는 코드를 지니고 있다.
     모든 state들은 country에 속하는 코드를 지니고 있다.
     모든 country들은 코드를 지니고 있다.

state안의 모든 county를 찾기 위해 country 코드와 county코드를 통해 county를 state와 연관시킨다. country안의 모든 county를 찾기 위해 country 코드에 의해 county를 country와 연관시킨다. 이러한 관계들은 일시적이며 질의되는 동안에만 존재한다.

조금 무미건조하고 이해하기 힘들겠지만 처음의 몇 예제들로 코드와 소속에 대한 개념이 명확해졌기를 바란다.

DB 매니저에 첫번째 질의를 보내면 DB 매니저는 모든 관계된 데이터 아이템들을 리턴한다. 하지만 어떤 데이터를 내가 받게 될까? 그것은 country와 county 아이템의 결합이며 모든 county에 대해 연관된 country 아이템을 받게 될 것이다.

첫번째 질의에서 이름이 없는 새로운 개체가 갑자기 생성되며 그것은 country와 county의 복제를 포함한다. 이러한 새로운 개체는 다시 질의가 끝나고 나면 사라지게 된다.

전에 우리는 데이터의 집합을 "파일"이라고 불렀다. 파일은 레지스터로 이루어져 있으며 각각의 레지스터는 "필드"를 가지고 있다. 관계형 데이터베이스에서는 "파일"은 테이블이며 테이블은 행(row)를 가지게 되고, 각각의 행에는 column이 있다. 이것은 단지 약간의 표면적인 변화일 뿐이다. ;-)

어떠한 DB 매니저들은 액세스 언어로서 SQL을 사용한다는 점을 말하고 싶다. 하지만 이것은 특수한 경우일 뿐이다. SQL언어는 거의 관계형 데이터베이스 매니저가 독점적으로 가지고 있는 특질 중의 하나이다.

SQL의 사용법을 알아보기 위해 우리는 관계형 데이터베이스 매니저인 PostgreSQL을 사용할 것이다. SQL 규칙과 완전히 부합되지는 않지만 우리의 목적에는 충분히 쓸 수 있고, 더욱 고난이도의 작업에도 알맞는 매우 좋은 데이터베이스 매니저이다.

이 글의 목적은 SQL임을 감안하여 설치 과정은 간략하게만 설명해 보겠다. 우선 www.postgresql.org로부터 필요한 소스를 다운로드 받도록 하자. 물론 사용 가능한 패치들도 다운로드 받는다. 소스들을 임의의 디렉토리에 압축을 풀고(tar zxvf), cd postgresql-6.3

cd src
./configure --prefix=/the/desired/path
make all >& make.log &
tail -f make.log

export PATH=$PATH:/the/desired/path/pgsql/bin
export MANPATH=$MANPATH:/the/desired/path/pgsql/man
export PGLIB=/the/desired/path/pgsql/lib
export PGDATA=/the/desired/path/pgsql/data

initdb
createdb test
psql test
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: postgres

test=>

mytest=>create table mytest (field1 varchar(10));
CREATE

mytest=>insert into mytest values ('hello');
INSERT number 1

mytest=>commit work;
NOTICE:EndTransactionBlock and not inprogress/abort state
END

mytest=>select * from mytest;
field1
------
hello
(1 row)

mytest=>drop table mytest;
DROP

mytest=>Ctrl-d

Postgres95의 컴파일과 설치에서 문제가 생긴다면 배포판의 루트디렉토리에 있는 INSTALL 파일을 참조하기 바란다.

또 다른 면에 대해 언급해보자. 관계형 데이터베이스 서버는 일반적으로 다음과 같은 부분들로 이루어진다.

1. Data access layer
2. SQL processing layer
3. SQL parser layer
4. Communications layer

첫번째 단계

File: countries.sql
create table countries (cod_country integer, name varchar(30));
insert into countries values (1, 'country 1');
insert into countries values (2, 'country 2');
insert into countries values (3, 'country 3');
commit work;

File: states.sql
create table states (cod_State int, 
			cod_country int, 
			nam_State varchar(30));
insert into states values (1, 1, 'State 1, Country 1');
insert into states values (2, 1, 'State 2, Country 1');
insert into states values (1, 2, 'State 1, Country 2');
insert into states values (2, 2, 'State 2, Country 2');
insert into states values (1, 3, 'State 1, Country 3');
insert into states values (2, 3, 'State 2, Country 3');
commit work;

File: counties.sql
create table counties (cod_country int, 
			cod_state int, 
			cod_county int,
			nam_county varchar(60));
insert into counties values (1, 1, 1, 'County 1, State 1, Country 1');
insert into counties values (2, 1, 1, 'County 2, State 1, Country 1');
insert into counties values (3, 1, 1, 'County 3, State 1, Country 1');
insert into counties values (1, 2, 1, 'County 1, State 2, Country 1');
insert into counties values (2, 2, 1, 'County 2, State 2, Country 1');
insert into counties values (3, 2, 1, 'County 3, State 2, Country 1');
insert into counties values (1, 3, 1, 'County 1, State 3, Country 1');
insert into counties values (2, 3, 1, 'County 2, State 3, Country 1');
insert into counties values (3, 3, 1, 'County 3, State 3, Country 1');
insert into counties values (1, 1, 2, 'County 1, State 1, Country 2');
insert into counties values (2, 1, 2, 'County 2, State 1, Country 2');
insert into counties values (3, 1, 2, 'County 3, State 1, Country 2');
insert into counties values (1, 2, 2, 'County 1, State 2, Country 2');
insert into counties values (2, 2, 2, 'County 2, State 2, Country 2');
insert into counties values (3, 2, 2, 'County 3, State 2, Country 2');
insert into counties values (1, 3, 2, 'County 1, State 3, Country 2');
insert into counties values (2, 3, 2, 'County 2, State 3, Country 2');
insert into counties values (3, 3, 2, 'County 3, State 3, Country 2');
insert into counties values (1, 1, 3, 'County 1, State 1, Country 3');
insert into counties values (2, 1, 3, 'County 2, State 1, Country 3');
insert into counties values (3, 1, 3, 'County 3, State 1, Country 3');
insert into counties values (1, 2, 3, 'County 1, State 2, Country 3');
insert into counties values (2, 2, 3, 'County 2, State 2, Country 3');
insert into counties values (3, 2, 3, 'County 3, State 2, Country 3');
insert into counties values (1, 3, 3, 'County 1, State 3, Country 3');
insert into counties values (2, 3, 3, 'County 2, State 3, Country 3');
insert into counties values (3, 3, 3, 'County 3, State 3, Country 3');
commit work;

SQL 명령어로 만들어진 파일은 pgsql에서 다음과 같은 방법으로 실행될 수 있다.

\i file_name

물론 간단하게 cut & paste기능을 이용하여 명령어를 집어넣을 수도 있다.

이제 어떤 county들이 있는지를 알아보자.

manu=> select * from counties;
cod_country|cod_state|cod_county|nam_county
-----------+---------+----------+----------------------------
          1|        1|         1|County 1, State 1, Country 1
          2|        1|         1|County 2, State 1, Country 1
          3|        1|         1|County 3, State 1, Country 1
          1|        2|         1|County 1, State 2, Country 1
          2|        2|         1|County 2, State 2, Country 1
          3|        2|         1|County 3, State 2, Country 1
          1|        3|         1|County 1, State 3, Country 1
          2|        3|         1|County 2, State 3, Country 1
          3|        3|         1|County 3, State 3, Country 1
          1|        1|         2|County 1, State 1, Country 2
          2|        1|         2|County 2, State 1, Country 2
          3|        1|         2|County 3, State 1, Country 2
          1|        2|         2|County 1, State 2, Country 2
          2|        2|         2|County 2, State 2, Country 2
          3|        2|         2|County 3, State 2, Country 2
          1|        3|         2|County 1, State 3, Country 2
          2|        3|         2|County 2, State 3, Country 2
          3|        3|         2|County 3, State 3, Country 2
          1|        1|         3|County 1, State 1, Country 3
          2|        1|         3|County 2, State 1, Country 3
          3|        1|         3|County 3, State 1, Country 3
          1|        2|         3|County 1, State 2, Country 3
          2|        2|         3|County 2, State 2, Country 3
          3|        2|         3|County 3, State 2, Country 3
          1|        3|         3|County 1, State 3, Country 3
          2|        3|         3|County 2, State 3, Country 3
          3|        3|         3|County 3, State 3, Country 3
(27 rows)
manu=>

manu=> select * from countries, states;
cod_country|name     |cod_state|cod_country|nam_state
-----------+---------+---------+-----------+------------------
          1|country 1|        1|          1|State 1, Country 1
          2|country 2|        1|          1|State 1, Country 1
          3|country 3|        1|          1|State 1, Country 1
          1|country 1|        2|          1|State 2, Country 1
          2|country 2|        2|          1|State 2, Country 1
          3|country 3|        2|          1|State 2, Country 1
          1|country 1|        1|          2|State 1, Country 2
          2|country 2|        1|          2|State 1, Country 2
          3|country 3|        1|          2|State 1, Country 2
          1|country 1|        2|          2|State 2, Country 2
          2|country 2|        2|          2|State 2, Country 2
          3|country 3|        2|          2|State 2, Country 2
          1|country 1|        1|          3|State 1, Country 3
          2|country 2|        1|          3|State 1, Country 3
          3|country 3|        1|          3|State 1, Country 3
          1|country 1|        2|          3|State 2, Country 3
          2|country 2|        2|          3|State 2, Country 3
          3|country 3|        2|          3|State 2, Country 3
(18 rows)

마지막 명령어는 두 테이블 사이의 유니온을 수행했다. 우리는 country의 테이블과 county의 테이블을 연관시켰다. union exclusion rule을 전혀 지정해주지 않았기 때문에 pgsql은 모든 state와 관련된 가능한 country를 모두 (예를 들면 country의 3 곱하기 state의 6을 하면 18개가 된다) 돌려준다. 이러한 결과는 명백하게 비논리적이고 쓸모없는 것이다. 다음과 같이 해 주었다면 더 좋았을 것이다.

manu=> select * from countries, states
manu-> where countries.cod_country = states.cod_country;
cod_country|name     |cod_state|cod_country|nam_state
-----------+---------+---------+-----------+------------------
          1|country 1|        1|          1|State 1, Country 1
          1|country 1|        2|          1|State 2, Country 1
          2|country 2|        1|          2|State 1, Country 2
          2|country 2|        2|          2|State 2, Country 2
          3|country 3|        1|          3|State 1, Country 3
          3|country 3|        2|          3|State 2, Country 3
(6 rows)

그렇다. 6개의 county가 있으며 모든 county들은 country 안에 있다. country가 county의 자격을 주므로 county의 개수와 똑같은 수의 행을 결과로 받는 것이 합당하다. 우리는 방금 country의 테이블을 country 코드를 통해 county의 테이블과 연관시켰다. country는 코드를 지니며, county들은 그들이 속해 있는 나라의 코드를 지닌다는 것을 기억하자.

왜 countries.cod_country = states.cod_country 인가 ?

country의 테이블 안의 country 코드는 cod_country이며 county의 테이블에서도 마찬가지이다. 그러므로

cod_country = cod_country

select * from countries, states
		where cod_country = cod_country;

ERROR:  Column cod_country is ambiguous 

manu=> select * from countries a, states b
manu-> where a.cod_country = b.cod_country;
cod_country|name     |cod_state|cod_country|nam_state
-----------+---------+---------+-----------+------------------
          1|country 1|        1|          1|State 1, Country 1
          1|country 1|        2|          1|State 2, Country 1
          2|country 2|        1|          2|State 1, Country 2
          2|country 2|        2|          2|State 2, Country 2
          3|country 3|        1|          3|State 1, Country 3
          3|country 3|        2|          3|State 2, Country 3
(6 rows)

"select * from countries, states"라는 질의를 했는데 와일드카드 *가 모든 것을 의미하므로 우리는 countries에 해당하는 두 개의 컬럼과 counties에 해당하는 세 개의 컬럼을 얻게 된다. 조금 더 구 체적으로 들어가보자.

manu=> select a.cod_country, cod_state, name, nam_state
manu-> from countries a, states b
manu-> where a.cod_country = b.cod_country;
cod_country|cod_state|name     |nam_state
-----------+---------+---------+------------------
          1|        1|country 1|State 1, Country 1
          1|        2|country 1|State 2, Country 1
          2|        1|country 2|State 1, Country 2
          2|        2|country 2|State 2, Country 2
          3|        1|country 3|State 1, Country 3
          3|        2|country 3|State 2, Country 3
(6 rows)   

조금 더 복잡하게 만들어보자:

manu=> select a.cod_country, cod_state, name, nam_state
manu-> from countries a, states b
manu-> where a.cod_country = b.cod_country
manu-> and a.cod_country = 3;
cod_country|cod_state|name     |nam_state
-----------+---------+---------+------------------
          3|        1|country 3|State 1, Country 3
          3|        2|country 3|State 2, Country 3
(2 rows)

함수

select count(*) from states;

count
-----
   27
(1 row)

manu=> select cod_country, count(*) from states
manu-> group by cod_country;
cod_country|count
-----------+-----
          1|    2
          2|    2
          3|    2
(3 rows)

다음과 같은 더 좋은 예도 있다:

manu=> select name, count(*) from countries a, states b
manu-> where a.cod_country = b.cod_country
manu-> group by name;
name     |count
---------+-----
country 1|    2
country 2|    2
country 3|    2
(3 rows)

지금까지는 개론만 이야기했다. 워밍업이었을 뿐이죠 :-)

개념 Review

아래와 같은 명령어들을 알아보았다:

CREATE TABLE	해당하는 컬럼들을 가진 테이블을 생성한다.
DROP TABLE	테이블을 삭제한다.
SELECT	이 명령어는 SQL의 기초가 되는 명령어로, 필요한 데이터 아이템만을 포함하는 임시 테이블을 만들게 해 준다. SELECT는 함수, 복합구문은 물론 sub_selects도 인자로 받을 수 있다: select count(*) from states where cod_country in (select cod_country from countries); count ----- 27 (1 row)
BEGIN WORK	또 다른 기본 명령어 중의 하나이다. 이 명령어는 DB 매니저가 BEGIN WORK 부터 행해진 모든 변동사항들을 처리하도록 한다. 우리가 다루는 DB 매니저에서 BEGIN WORK는 transaction의 초기화를 의미하며, 다른 매니저들에서는 데이터베이스의 내용을 바꾸는 첫번째 명령어부터 초기화된다. PostgreSQL에서는 BEGIN WORK가 전에 없다면 데이터를 변경시키는 모든 동작들은 바로 처리된다. NOTE: 데이터베이스의 구조를 바꾸는 명령어들은 COMMIT WORK를 실행하므로 transaction이 열린 상태에서 그러한 명령어가 시행되면 transaction은 바로 닫히며 ROLLBACK WORK 를 실행하는 것은 불가능해진다. 한 사용자가 열린 transaction을 가지고 있을 경우에 그 사용자는 그의 데이터에 대한 다른 사용자들의 접근권한을 선언할 수 있다: Modified data Original Data previous to the transaction Block data access
COMMIT WORK	변동사항들을 처리하면서 transaction 을 닫는다. ROLLBACK WORK는 데이터를 현재의 transaction 이전의 상태로 되돌려 준다.

transaction의 개념은 에러가 있을 경우에 이전의 상태로 되돌려준다는 점에서 매우 중요하다. 다음과 같은 작업을 해 보자. 우선 "rollback work" 명령으로 이전의 모든 transaction을 닫자:

manu=> select * from countries;
cod_country|name
-----------+---------
          1|country 1
          2|country 2
          3|country 3
(3 rows)

begin work;

insert into countries values (5, 'Country Not True');

manu=> select * from countries;
cod_country|name
-----------+----------------
          1|country 1
          2|country 2
          3|country 3
          5|Country Not True
(4 rows)

rollback work;

manu=> select * from countries;
cod_country|name
-----------+---------
          1|country 1
          2|country 2
          3|country 3
(3 rows)

요약

SQL은 우리의 자료의 추출된 층을 형성하며 우리의 필요에 따라 관리할 수 있게 해 준다.

지금까지 살펴본 것을 보고 어떤 사람이 이와 같이 물을 수 있다: application 안에서 SQL을 어떻게 쓸 것인가?

질문의 답은 한 번에 하나씩 알게 될 것이다. 세번째 글에서 우리는 SQL을 사용하는 짧은 C 어플리케이션을 리뷰할 것이다.

한글 번역: 서환수