SQL개론 - PostgreSQL 설치요약: 이 짧은 코스는 세 부분으로 이루어진다. 첫번째 부분에서는 PostgreSQL이라는 공개 데이터베이스 서버를 이용하여 SQL에 대한 일반적인 내용을 설명한다. 두번째 부분에서는 SQL 명령어에 대해 더욱 자세하게 공부해 볼 것이다. 마지막으로 세번째 부분에서는 SQL 명령어의 고급 옵션들과 우리 프로젝트에 관련될만한 PostgreSQL만의 함수들을 알아보고 마지막으로 모든 것들을 하나로 모아 조그만 C 프로그램을 살펴볼 것이다.
들어가는 글개론이 간략한 개론에서 우리는 데이터베이스(DB)에 대해서만 언급하겠다. 다른 형태의 자료구조도 있지만 그것들에 관한 것은 이 코스의 범위를 벗어날 것이다. 최근까지 데이터 아이템에 액세스는 데이터베이스 계통의 잘 정의된 링크를 통해 내부적으로 연관된 개체들을 통해 이루어졌었다. 이러한 종류의 액세스는 주로 액세스 속도면에서 장점을 지니고 있었지만 커다란 단점이 있다. 예를 들면 다음과 같이 현재 존재하는 링크를 통해서만 데이터를 액세스할 수 있고, 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=>이것이 postgres의 프롬프트이다. 이제 명령어를 실행시킬 수 있다. 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이렇게 하면 SQL 콘솔에서 빠져나간다. Postgres95의 컴파일과 설치에서 문제가 생긴다면 배포판의 루트디렉토리에 있는 INSTALL 파일을 참조하기 바란다. 또 다른 면에 대해 언급해보자. 관계형 데이터베이스 서버는 일반적으로 다음과 같은 부분들로 이루어진다. - Data access layer
- SQL processing layer
- SQL parser layer
- Communications layer
우리는 클라이언트로 4번째 레이어에 연결되며, SQL 명령어를 보내면 parser layer로 전달된다. 이것이 명령어를 번역하고 에러가 없는 경우에 요구사항을 두번째 레이어로 보낸다. 모든 질의와 처리는 data access layer의 도움을 받아서 두번째 레이어에서 이루어진다. communications layer를 통해서 데이터의 수집, 에러의 전송 등이 이루어진다. SQL processing 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=>27개의 행이 출력되었으며 pgsql은 다음 명령어를 기다리게 된다.
다음과 같은 명령을 내려보자. 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)18행 ??? 우리는 3개의 country와 6개의 state를 입력했고, 모두 한 개의 country를 의미한다. 어떻게 18개의 행이 나올 수 있을까? 마지막 명령어는 두 테이블 사이의 유니온을 수행했다. 우리는 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개의 행, 맞지 않은가? 그렇다. 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 다음으로, 우리는 컬럼들에 대해서 aliasis를 사용할 수 있다. 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)매니저가 리턴하는 값들은?: cod_country, name, cod_state, cod_country y nam_state. "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) 마지막 명령어에서 country code, state code, country와 state 의 이름들을 명백하게 요구했다. a.cod_country와 같이 어떤 컬럼 들은 테이블을 명시했고, nam_state와 같이 어떤 컬럼들은 명시하지 않았는데, 이는 nam_state는 states에만 존재하는 반면 cod_country는 두 테이블 모두에 존재하기 때문이다. 유일한 컬럼 이름은 특별한 지시자(qualifier)를 필요로 하지 않는다. 조금 더 복잡하게 만들어보자: 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)이번에는 country 번호가 3인 것만을 검색하도록 제한했다. 함수행 수를 세는 함수인 count()의 예를 들어보자: select count(*) from states;
count
-----
27
(1 row)위의 명령은 county의 테이블에 들어 있는 행의 수를 리턴한다: manu=> select cod_country, count(*) from states
manu-> group by cod_country;
cod_country|count
-----------+-----
1| 2
2| 2
3| 2
(3 rows)위의 예는 같은 country code를 가진 행의 수를 돌려준다. 그렇게 하기 위해서 cod_country를 사용한 것이다. 다음과 같은 더 좋은 예도 있다: 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지금까지 SQL의 몇몇 기본적인 개념들을 알아보았다. 가장 의미있는 것은 SQL 자체의 개념에 대한 것이다. 우리는 이제 더 이상 고형화된 데이터를 다루는 것이 아니라 데이터 본체를 다룬다. 데이터 본체는 데이터베이스의 추상적인 개념이다. 단순화시킨다면 "가능한 모든 것 중에서 오직 리턴된 부분"이라고 이해할 수 있을 것이다. 아래와 같은 명령어들을 알아보았다:
| 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;transaction을 시작한다. 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;라고 하면 transaction을 취소하게 된다. manu=> select * from countries;
cod_country|name
-----------+---------
1|country 1
2|country 2
3|country 3
(3 rows)행의 개수를 확인해 보면 원래의 3개의 행으로 되돌아갔음을 알 수 있다.
| INSERT | 이미 본 바와 같이 이 명령어는 테이블에 명령어를 집어넣는다. | | CREATE TABLE | 또 다른 중요한 명령어로 테이블과 컬럼들을 생성한다. 다룰 수 있는 자료유형을 알아보자: | char(range): | (range)바이트만큼의 고정된 길이를 가진 문자열 | | varchar(rango): | (range)바이트까지 저장할 수 있는 고정폭 문자열 | | int2: | 2바이트의 정수형 2**-15 - 2**15 | | int4: | 4바이트의 정수형 2**-31 - 2**31 | | money: | 고정소수점 숫자형, 예를 들면 money(6,3)은 소수점이하 3자리인 6개의 숫자로 된 수를 가리킨다.(123.456 765.987 과 같은식으로..) | | time: | 시간, 분, 초, 100분의 1초단위의 임시적인 시간 자료(HH:MM:SS:CCC) | | date: | 연도, 월, 일을 포함하는 날짜자료. YYYY/MM/DD | | timestamp: | YYYY/MM/DD:HH:MM:SS:CCC 와 같이 표현되는 날짜, 시간 자료형. | | float(n): | 단정도 실수형 자료. | | float3: | 배정도 실수형 자료. | |
자료형의 정의는 각 종류의 SQL 매니저에 따라 다르다. 물론 SQL 표준형(가장 최근의 것은 ANSI/92또는 SQL/3이 있다)이 그 특징과 함께 몇몇 자료형을 정희하기도 한다. 이 강좌에서 우리는 PostgreSQL에 국한된 자료형을 거의 보지 못할 것이다. | | DELETE | 테이블의 행을 지운다. | | UPDATE | 테이블의 행의 컬럼들을 수정한다. |
요약조금 산만하긴 했지만, SQL과 관계형 데이터베이스의 설치 등에 대해 알아보았다. SQL은 우리의 자료의 추출된 층을 형성하며 우리의 필요에 따라 관리할 수 있게 해 준다. 지금까지 살펴본 것을 보고 어떤 사람이 이와 같이 물을 수 있다: application 안에서 SQL을 어떻게 쓸 것인가? 질문의 답은 한 번에 하나씩 알게 될 것이다. 세번째 글에서 우리는 SQL을 사용하는 짧은 C 어플리케이션을 리뷰할 것이다.
한글 번역: 서환수 |
RECENT COMMENT