Home Map Index Search News Archives Links About LF
[Top Bar]
[Bottom Bar]
Manuel Soriano




°³·Ð

ù¹ø° ´Ü°è

ÇÔ¼ö

°³³ä Review

¿ä¾à

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 ÆÄÀÏÀ» ÂüÁ¶Çϱ⠹ٶõ´Ù.

¶Ç ´Ù¸¥ ¸é¿¡ ´ëÇØ ¾ð±ÞÇغ¸ÀÚ. °ü°èÇü µ¥ÀÌÅͺ£À̽º ¼­¹ö´Â ÀϹÝÀûÀ¸·Î ´ÙÀ½°ú °°Àº ºÎºÐµé·Î ÀÌ·ç¾îÁø´Ù.

  1. Data access layer
  2. SQL processing layer
  3. SQL parser layer
  4. 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 ¾îÇø®ÄÉÀ̼ÇÀ» ¸®ºäÇÒ °ÍÀÌ´Ù.


ÇÑ±Û ¹ø¿ª:


º» À¥»çÀÌÆ®´Â Miguel Angel Sepulveda¾¾¿¡ ÀÇÇØ °ü¸®µË´Ï´Ù.
© Manuel Soriano 1998
LinuxFocus 1998