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 ¾îÇø®ÄÉÀ̼ÇÀ» ¸®ºäÇÒ °ÍÀÌ´Ù.
ÇÑ±Û ¹ø¿ª:
|