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

ÇÊÀÚ¿¡°Ô ¿¬¶ôÇϱâ


Contents:
µé¾î °¡´Â ¸»
Table ¸¸µé±â
Table ¼öÁ¤Çϱâ
Table¿¡ µ¥ÀÌŸ ÀÔ·ÂÇϱâ
µ¥ÀÌŸ ¼±ÅÃÇϱâ
WHERE¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ¼³¸í
UPDATE ¸í·É
DELETE ¸í·É
ÂüÁ¶

SQL Tutorial
Á¦ 2Àå

[Illustration]

¿ä¾à: À̹ø µÎ¹ø° ±â»ç¿¡¼­´Â SQL ¾ð¾îÀÇ ±âº» ÀûÀÎ ±¸¹®(statement)¿¡ ´ëÇؼ­ ¹è¿î´Ù.




µé¾î °¡´Â ¸»

ÀÌ ±ÛÀº SQL °úÁ¤ Áß µÎ ¹ø° ±ÛÀÌ´Ù. ÀÌ ±Û¿¡¼­´Â tableÀ» ¸¸µé°í, ¼öÁ¤ÇÏ°í, Áö¿ì´Â µîÀÇ SQL ¸í·É¿¡ ´ëÇؼ­ ¹è¿î´Ù.

°¡Àå ¸ÕÀú, °³ÀÎÀûÀ¸·Î °¡Àå Áß¿äÇÏ´Ù°í »ý°¢µÇ´Â SELECT ¸í·É¿¡ ´ëÇØ ¹è¿î´Ù.

ÀÌ ±ÛÀÌ ÀÌ ±ÛÀ» Àд ¸ðµç µ¶ÀÚ¿¡°Ô Àç¹ÌÀÖ°í, ¸¹Àº µµ¿òÀÌ µÇ¾úÀ¸¸é ÇÑ´Ù.

Table ¸¸µé±â

óÀ½ ¼³Ä¡À» ÇßÀ» ¶§ ºÃµíÀÌ, tableÀ» ¸¸µé±â À§Çؼ­´Â TABLE°ú ÇÔ²² »ç¿ëÇÏ¿©CREATE ¸í·ÉÀ» ÀÌ¿ëÇÑ´Ù. ½ÇÁ¦·Î´Â CREATE´Â ´ÙÀ½°ú °°Àº °ÍµéÀ» ¸¸µé ¼ö ÀÖ´Ù.

  • users: CREATE USER
  • tables: CREATE TABLE
  • indexes: CREATE INDEX
  • views: CREATE VIEW

CREATE ¸í·ÉÀº ¹«¾ùÀΰ¡¸¦ ¸¸µé·Á°í ÇÑ´Ù´Â °ÍÀ» ¾Ë·ÁÁØ´Ù. ´ÙÀ½¿¡ ¾î¶»°Ô ÇÏ´ÂÁö ¹è¿ï °ÍÀÌ´Ù.

¹®¹ý

CREATE TABLE name ( column type [DEFAULT value] [NOT NULL], ...
      [INHERITS (inherits, ...)]
      [CONSTRAINT constraints CHECK (test), CHECK (test)] );
name: »ý¼ºµÉ tableÀÇ À̸§ÀÌ´Ù.
Column: Ä÷³ÀÇ À̸§ÀÌ´Ù.
Type: µ¥ÀÌŸÀÇ Å¸ÀÔÀÌ´Ù.(varchar, char, int, date, time, timestamp), Postgres ´Â ´Ù¸¥ data typeµµ ÀÖÁö¸¸, ANSI SQL°ú´Â ȣȯµÇÁö ¾Ê´Â´Ù.
Value: default·Î °¡Áú °ªÀ» ÁöÁ¤ÇÑ´Ù.
Inherits: Postgre¿¡¸¸ ÀÖ´Â °ÍÀ¸·Î, ´Ù¸¥ Å×ÀÌºí¿¡ ÀÖ´Â ¸ðµç Ä÷³À» »ó¼Ó¹Þ°Ô µÈ´Ù.
Nom_cons: row°¡ ¼öÁ¤µÉ ¶§¸¶´Ù µ¥ÀÌŸÀÇ °ªÀÌ Á¤´çÇÑÁö¸¦ Á¤ÀÇÇÑ´Ù.
Test: °Ë»çÇÒ Á¶°ÇÀÌ´Ù.
¿¹)
CREATE TABLE countries (
         cod_country integer NOT NULL,
         name varchar(30))
   CONSTRAINT cod_raro CHECK (cod_country > 0 AND cod_country < 154);

À§ÀÇ ¿¹¿¡¼­ countries¶ó´Â tableÀ» ¸¸µé¾ú´Ù. Å×ÀÌºí¿¡ °ªÀ» ÀÔ·ÂÇÒ ¶§¸¶´Ù ´ÙÀ½ Á¶°Ç¿¡ ¸Â´ÂÁö È®ÀÎÇÏ°Ô µÈ´Ù.

  • cod_country´Â NULLÀÌ ¾Æ´Ï´Ù. ¸¸¾à NULLÀº ´ëÀÔÇÏ°Ô µÇ¸é, °ü¸®ÀÚ´Â error¸¦ ³»°Ô µÈ´Ù.
    ExecAppend: cod_countryÀÇ ¼Ó¼ºÀÌ NULLÀÌ ¾Æ´Ï±â ¶§¹®¿¡ ½ÇÆÐÇÏ°Ô µÈ´Ù.
    
  • cod_country´Â 0º¸´Ù Å©°í 154º¸´Ù´Â ÀÛ´Ù. ¹üÀ§¿¡ ¹þ¾î³ª´Â °ªÀ» ´ëÀÔÇÏ°Ô µÇ¸é °ü¸®ÀÚ´Â error¸¦ ³½´Ù.
    ExecAppend: cod_raro °Ë»ç¸¦ °ÅºÎÇÏ°Ô µÈ´Ù.
    

Note

NULLÀº ¹«¾ùÀ» ÀǹÌÇϴ°¡? SQL¿¡´Â data¿Í data°¡ ¾Æ´Ñ °Í µÎ°¡Áö »óÅ°¡ ÀÖ´Ù. ¿µ(0)À̳ª °ø¹é ¹®ÀÚ µ¥ÀÌŸ¸¦ ÀǹÌÇÏ´Â °Íó·³ ¿ì¸®´Â µ¥ÀÌŸ¸¦ °¡ÁöÁö ¾Ê´Â Çʵ带 ÇÊ¿ä·Î ÇÒ ¶§°¡ ÀÖ´Ù. SQL¿¡¼­´Â NULLÀ̶ó´Â °³³äÀ» µµÀÔÇß´Ù. ½ÇÁ¦ÀûÀÎ ¿¹´Â ´ÙÀ½°ú °°´Ù.

billÀ̶ó´Â µ¥À̺íÀ» ¸¸µé°í, customer, value, date_issued, date_paid¶ó´Â Çʵ带 °¡Áö°í ÀÖ´Ù.

±×¸®°í customer, value, date_issued Çʵ忡 °ªÀ» ´ëÀÔÇÏ°í,

date_paid´Â NULL·Î Çß´Ù. ÀÌ·± ½ÄÀ¸·Î ÁöºÒÇؾßÇÒ µ·À» ¾Ë¼ö°¡ ÀÖ´Ù.

 
SELECT * FROM bills WHERE date_paid IS NULL ;

date_padi¸¦ ¿µ(0)À¸·Î Çصµ °ªÀº È¿°ú¸¦ ³¾ ¼ö ÀÖ´Ù°í »ý°¢ÇÏ´Â »ç¶÷µµ ÀÖÀ» ÅÙµ¥, ¿µ(0)ÀÌ ³¯Â¥°¡ ¾Æ´Ñ °ÍÀ» Á¦¿ÜÇÏ°í, ¸Â´Â ¸»ÀÌ´Ù. ±×¸®°í ³¯Â¥¿¡ °üÇÑ ÇÔ¼ö¸¦ ÀÌ¿ëÇÏ°í, date typeÀÇ date_paid¸¦ ¸¸µå´Â °ÍÀ» ¹æÁöÇÑ´Ù.

(¿ªÀÚÁÖ : ¿µ¾î°¡ ª¾Æ¼­ ¹ø¿ªÀÌ ¾û¼ºÇϱº¿© ^^; ¿ø¹®ÀÔ´Ï´Ù.
One can claim that a zero (0) in the field date_paid would do the same thing. It's true, except that zero (0) is not a date, and it prevents creating date_paid of date type and applying appropriate date functions.)

NULLÀ» ÀÌ¿ëÇÏ¿© »ý¼ºÇÏ´Â ¿¹:

insert into countries values (15, NULL);
¶Ç´Â:
insert into countries (cod_country) values (27);

'name'¿¡ °ªÀ» ´ëÀÔÇÏÁö ¾Ê´Â °ÍÀº NULL °ªÀ» ¹Þ´Â ´Ù´Â °ÍÀ» ÀǹÌÇÑ´Ù.

Table ¼öÁ¤Çϱâ;

PostgreSQL¿¡¼­´Â TableÀÇ ¼öÁ¤Àº »õ·Î¿î Ä÷³À» Ãß°¡ÇÏ´Â Àϸ¸À» ÇÒ ¼ö ÀÖ´Ù.

ALTER TABLE table ADD name type;
Table ¼öÁ¤ÇÏ°íÀÚ ÇÏ´Â TableÀÇ À̸§
Name Ãß°¡ÇÏ°íÀÚ ÇÏ´Â Ä÷³ÀÇ À̸§
Type dataÀÇ Å¸ÀÔ ( CREATE TABLEÀ» º¸¶ó)

Table¿¡ µ¥ÀÌŸ ÀÔ·ÂÇϱâ

ÀÌÁ¦ Table¿¡ °ªÀ» ÀÔ·ÂÇÏ´Â °Í¿¡ ´ëÇØ ¹è¿î´Ù.

¹®¹ý:
INSERT INTO table [(column, column, ...)] VALUES (value-1, value-2,
...)
or else:
INSERT INTO table [(column, column, ...)] SELECT ....

º¸¾Ò´Ù ½ÃÇÇ table¿¡ µ¥ÀÌŸ¸¦ ÀÔ·ÂÇÏ´Â ¹æ¹ýÀº µÎ °¡Áö°¡ ÀÖ´Ù. ÇÑÁÙ ÇÑÁÙ ÀÔ·ÂÇÏ´Â ¹æ¹ý°ú ¿©·¯ row¸¦ ¸®ÅÏÇÏ´Â sub-select¸¦ ÀÌ¿ëÇÏ´Â ¹æ¹ýÀÌ´Ù.

table¿¡ µ¥ÀÌŸ¸¦ ÀÔ·ÂÇÒ ¶§´Â NULL °ªÀ» Æ÷ÇÔÇÑ ¸ðµç Ä÷³µé¿¡ °ªÀ» Áý¾î ³Ö¾î¾ß ÇÑ´Ù.

µ¥ÀÌŸ¸¦ ÀÔ·ÂÇÏ´Â ¸í·É¿¡ ¾î¶² Ä÷³¿¡ °ªÀ» ÀÔ·ÂÇÒÁö ¸í½ÃÈ­ÇÏÁö ¾ÊÀ¸¸é ¸ðµç Ä÷³¿¡ µ¥ÀÌŸ¸¦ ÀÔ·ÂÇÏ´Â °ÍÀ¸·Î ÀÌÇØÇÑ´Ù. ¿¹¸¦ µé¾î:

INSERT INTO countries VALUES (34, 'Spain');
´ÙÀ½Àº Ʋ¸° °ÍÀÌ´Ù.
INSERT INTO countries VALUES (34);
ÇÏÁö¸¸ ´ÙÀ½Àº ¿ÇÀº °ÍÀÌ´Ù.
INSERT INTO countries (cod_country) VALUES (34);

ÇÊÀÚ´Â C ¾ð¾î¿¡¼­ DB ÇÁ·Î±×·¡¹ÖÀ» Çϰųª database ÇÔ¼ö¸¦ »ç¿ëÇÒ ¶§ ¿øÇÏ´Â Ä÷³À» ²À ¸í½ÃÈ­ ÇÒ °ÍÀ» ÃßõÇÏ´Â ¹ÙÀÌ´Ù. ¿Ö³ÄÇϸé ALTER TABLEµîÀ» ÀÌ¿ëÇÏ¿© »õ·Î¿î Ä÷³À» Ãß°¡ÇßÀ» ¶§ ´ÙÀ½ ¹øÀÇ insert´Â ¿¡·¯¸¦ ³»±â ¶§¹®ÀÌ´Ù.

¿¹:


INSERT INTO countries VALUES (34, 'Spain');

INSERT 18301 1

ALTER TABLE countries add population integer

INSERT INTO countries VALUES (34, 'Spain');

ÀÌ´Â 'population' data°¡ ºüÁ³±â ¶§¹®¿¡ ±¸¹® ¿¡·¯¸¦ ³»°Ô µÈ´Ù.

Note

PostgreSQL¿¡¼­´Â ¿¡·¯¸¦ ¹ß»ýÇÏÁö ¾Ê°í, 'population' Çʵ带 NULL·Î ä¿î´Ù. ÇÏÁö¸¸, ÀÌ°ÍÀº PostgreSQLÀÇ Æ¯Â¡ÀÌ°í, ´Ù¸¥ SQL¿¡¼­´Â ¿¡·¯¸¦ ¹ß»ýÇÒ °ÍÀÌ´Ù.

sub-select¸¦ ÀÌ¿ëÇÑ ´Ù¸¥ ¹æ¹ýÀÇ INSERT°¡ ÀÖ´Ù.

ÀÌ ¹æ¹ýÀÇ insert´Â ÀÓ½ÃÀûÀÎ tableÀ» ¸¸µé°Å³ª À§ÇèÀ» ³»Æ÷ÇÑ °è»êÀ» ¾ÈÀüÇÏ°Ô Çϱâ À§Çؼ­ ÀÚÁÖ ¾²ÀδÙ.

insert¸¦ ÀÌ¿ëÇؼ­ data°¡ ÀÚü°¡ ´ëÄ¡µÈ °æ¿ì, ÀÌ°ÍÀº SELECT¿¡ ÀÇÇؼ­ ¹ÝȯµÈ´Ù. SELECT´Â ÇÑ row ¶Ç´Â ¿©·¯ row¸¦ ¸®ÅÏÇÒ ¼ö ÀÖ´Ù. SELECT´Â SELECT¿Í °°Àº Á¦¾à »çÇ×À» °¡Áø´Ù.

data ¼±ÅÃÇϱâ

ÇÊÀÚ´Â »¡¸® ÀÌ ºÎºÐÀ¸·Î ¿À±æ ¿øÇß´Ù.! :-))

SQL ¸í·ÉÀ» ¸î °¡Áö º¸¾Ò´Àµ¥, SELECT¾ø´Â SQL ¾ð¾î´Â ¼ø´ë¿¡ ºÒ°úÇÏ´Ù.

SELECT ¸í·ÉÀº ¿ì¸®°¡ data¿¡ Á¢±ÙÇÏ´Â °ÍÀ» Çã¶ôÇÑ´Ù. ÇÏÁö¸¸ ÀÌ·¯ÇÑ Á¢±ÙÀº ¿¹¾àµÈ °Íµé, Áï Ž»öÀ̳ª, tableµéÀÇ °áÇÕ, data¿¡ °üÇÑ ÇÔ¼ö, Ž»ö ±Ô¾à µîÀÌ´Ù.

¿¹:

select * from countries;

*´Â ¸ðµç Ä÷³À» ÀǹÌÇÑ´Ù.

´Ù¸¥ ¿¹:

SELECT a.name, SUM(population)
    FROM countries a, states b, counties c
    WHERE b.cod_country = a.cod_country
        AND (c.cod_country = b.cod_country
        AND c.state_code = b.state_code)
        AND population IS NOT NULL
        GROUP BY a.name
        ORDER BY sum ASC;

¼³¸í»ó, ÇÊÀÚ´Â ¸ðµç ³ª¶óÀÇ Àα¸¸¦ ¿Ã¸²Â÷¼øÀ¸·Î º¸±æ ¿øÇß´Ù.

À̸¦ À§ÇÏ¿© 'population'À̶ó´Â Ä÷³À» counties table¿¡ Ãß°¡Çß´Ù.

´ÙÀ½°ú °°´Ù:
create table counties (cod_country int, 
                        state_code int, 
                        county_code int,
                        county_name varchar(60),
                        population int);
insert into counties values (1, 1, 1, 'Country 1, State 1, County 1',
5435);
insert into counties values (2, 1, 1, 'Country 2, State 1, County 1',
7832);
insert into counties values (3, 1, 1, 'Country 3, State 1, County 1',
4129);
insert into counties values (1, 2, 1, 'Country 1, State 2, County 1',
76529);
insert into counties values (2, 2, 1, 'Country 2, State 2, County 1',
9782);
insert into counties values (3, 2, 1, 'Country 3, State 2, County 1',
852);
insert into counties values (1, 3, 1, 'Country 1, State 3, County 1',
3433);
insert into counties values (2, 3, 1, 'Country 2, State 3, County 1',
7622);
insert into counties values (3, 3, 1, 'Country 3, State 3, County 1',
2798);
insert into counties values (1, 1, 2, 'Country 1, State 1, County 2',
7789);
insert into counties values (2, 1, 2, 'Country 2, State 1, County 2',
76511);
insert into counties values (3, 1, 2, 'Country 3, State 1, County 2',
98);
insert into counties values (1, 2, 2, 'Country 1, State 2, County 2',
123865);
insert into counties values (2, 2, 2, 'Country 2, State 2, County 2',
886633);
insert into counties values (3, 2, 2, 'Country 3, State 2, County 2',
982345);
insert into counties values (1, 3, 2, 'Country 1, State 3, County 2',
22344);
insert into counties values (2, 3, 2, 'Country 2, State 3, County 2',
179);
insert into counties values (3, 3, 2, 'Country 3, State 3, County 2',
196813);
insert into counties values (1, 1, 3, 'Country 1, State 1, County 3',
491301);
insert into counties values (2, 1, 3, 'Country 2, State 1, County 3',
166540);
insert into counties values (3, 1, 3, 'Country 3, State 1, County 3',
165132);
insert into counties values (1, 2, 3, 'Country 1, State 2, County 3',
0640);
insert into counties values (2, 2, 3, 'Country 2, State 2, County 3',
65120);
insert into counties values (3, 2, 3, 'Country 3, State 2, County 3',
1651462);
insert into counties values (1, 3, 3, 'Country 1, State 3, County 3',
60650);
insert into counties values (2, 3, 3, 'Country 2, State 3, County 3',
651986);
insert into counties values (3, 3, 3, 'Country 3, State 3, County 3',
NULL);
commit work;

ÀÌÁ¦ ¿ì¸®´Â ALTER TABLE¸¦ ÀÌ¿ëÇÒ ¼ö ¾øÁö¸¸, ¾ÆÁ÷ ¼³¸íÇÏÁö ¾ÊÀº UPDATE¸¦ »ç¿ëÇÒ ¼ö ÀÖ´Ù. "cut & paste"¸¦ ÀÌ¿ëÇ϶ó. ¸ðµç »ç¶÷ÀÌ happy ÇØÁú °ÍÀÌ´Ù. :-))

Äõ¸®¸¦ ¼öÇàÇÔÀ¸·Î¼­ ´ÙÀ½°ú °°Àº °á°ú¸¦ ¾òÀ» ¼ö ÀÖ´Ù.

name     |    sum
- ---------+-------
country 1| 705559
country 2|1212418
country 3|2804018
(3 rows)
´ÙÀ½°ú °°ÀÌ Çϸé:

select sum(population) from counties where cod_country = 1;

°á°ú´Â ´ÙÀ½°ú °°´Ù.
   sum
- ------
791986
(1 row)

!!!!!! Çϳª°¡ ´Ù¸£´Ù !!!!!!

ÀÌÁ¦ states tableÀ» º¸ÀÚ. state 3¸¦ »©¾ï¾ú´Ù.

INSERT INTO states VALUES (3, 1, 'State 3, Country 1');
INSERT INTO states VALUES (3, 2, 'State 3, Country 2');
INSERT INTO states VALUES (3, 3, 'State 3, Country 3');
±×¸®°í ¸í·ÉÀ» ¹Ýº¹ÇÏ¿© ´ÙÀ½°ú °°Àº °á°ú¸¦ ¾ò´Â´Ù.

name     |    sum
- ---------+-------
country 1| 791986
country 2|1872205
country 3|3003629 

°¢°¢ÀÇ country¿¡ state 3°¡ ¾ø´Ù.

(Å×ÀÌºí °£ÀÇ joindl EXACTÀÓÀ» ±â¾ïÇ϶ó) ¾ø¾îÁø °Íµé¿¡ ´ëÇؼ­ Á¶°Ç¿¡ ¸Â´Â °Íµé¸¸ ÃßÃâÇÒ ¼ö ÀÖ´Ù.

´Ù½Ã À­ ºÎºÐÀ¸·Î ¿Ã¶ó°¡¼­WHERE b.cod_country = a.cod_countryºÎºÐÀ» º¸ÀÚ.

ÀÌ°ÍÀº country table°ú state¸¦ cod_country°¡ °°Àº °ÍÀ» joinÇß´Ù´Â °ÍÀ» ¸»ÇØÁØ´Ù. country data°¡ »çÀԵƴٴ °ÍÀ» ±â¾ïÇضó.

´ÙÀ½À» ½ÇÇà½ÃÅ°Áö´Â ¸¶¶ó. ´Ü¼øÇÑ ¿¹ÀÌ´Ù.

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;
ÀÌÁ¦ states data¸¦ º¸ÀÚ
create table states (state_code int, 
                        cod_country int, 
                        state_name 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;
°¢°¢ÀÇ country¿¡ ´ëÇؼ­ state 3°¡ ¾ø´Ù. ±×·¯³ª, country table¿¡´Â states 3°¡ ÀÖ´Ù. µû¶ó¼­, ¿ì¸®°¡ whereÀÇ µÎ ¹ø° ºÎºÐ¿¡¼­ code 3¸¦ ¹«½ÃÇÏ°í, country data¿¡ states¸¦ Ãß°¡ÇÏÁö ¾ÊÀº °ÍÀº ´ç¿¬ÇÑ °ÍÀÌ´Ù.
        AND (c.cod_country = b.cod_country
        AND c.state_code = b.state_code)

state´Â counties table¿¡´Â ÀÖÁö¸¸, states table¿¡´Â ¾ø´Ù.

À§¿¡¼­ ¸»ÇÑ °ÍÀÌ ÀÌÇØ°¡ Àß ¾È °£´Ù¸é, ¾Æ½ºÇǸ° ÇÑ ¾ËÀ» ¸Ô°í, °³¿Í ÇÔ²² »êÃ¥À» ³ª°¡¶ó(°³°¡ ¾øÀ¸¸é È¥ÀÚ °¡¶ó). ½ÉÈ£ÈíÀ» Á» Çϸ鼭 ½® ÈÄ¿¡ óÀ½ºÎÅÍ ´Ù½Ã ½ÃÀÛÇ϶ó

JoinÀ» ÇÑ °á°ú°¡ ¾î¶»°Ô ³ª¿À´ÂÁö ÀÌÇØÇÏ´Â °ÍÀº ¸Å¿ì Áß¿äÇÏ´Ù. ÀÌ·¯ÇÑ °³¹ßÀ» ÇÏÁö ¾Ê´Â´Ù¸é, ¿¹ÃøÇÒ ¼ö ¾ø´Â °á°ú°¡ ³ª¿Ã ¼ö ÀÖ´Ù.

ÀÌÁ¦ °üÁ¡À» Á¶±Ý ¹Ù²ã¼­ SELECT ¸í·ÉÀÇ ¹®¹ý¿¡ ´ëÇؼ­ ¾Ë¾Æ º¸ÀÚ

SELECT [DISTINCT] expression1 [AS nom-attribute] {, expression-i [as
nom-attribute-i]} 
       [INTO TABLE classname] 
       [FROM from-list] 
       [WHERE where-clause] 
       [GROUP BY attr_name1 {, attr_name-i....}] 
       [ORDER BY attr_name1 [ASC | DESC ] [USING op1 ] {,
nom-attribute-i...}] 
       [UNION {ALL} SELECT ...] 
Çϳª¾¿ »ìÆ캸ÀÚ
DISTINCT: row°¡ Áߺ¹µÇ¾î ¸®ÅϵǴ °ÍÀ» ¸·´Â´Ù.
expression1: ¿ì¸®°¡ ¸®ÅϵDZ⠿øÇÏ´Â °ÍÀÌ´Ù. º¸Åë FROM µÚ¿¡ ¿À´Â TableÀÇ Ä÷³ÀÌ µÈ´Ù.
AS nom-attribute: Ä÷³ À̸§ÀÇ º°¸í(alias)ÀÌ´Ù.¿¹¸¦ µé¾î:
manu=> select cod_country from countries;
cod_country
- -----------
          1
          2
          3
(3 rows)          
º°¸í(alias)¸¦ ÀÌ¿ëÇϸé:
manu=> select cod_country as countr from countries;
countr
- ------
     1
     2
     3
(3 rows)
INTO TABLE: °á°ú°¡ ´Ù¸¥ Table·Î Á÷Á¢ »ðÀÔ µÇ°Ô ÇÑ´Ù. ( INSERT ... SELECT...¸¦ º¸¶ó)
FROM: TableµéÀÇ ¸ñ·ÏÀÌ´Ù.
WHERE: ¼±Åà ¹®, selection statement (union °ú selection criteria).
GROUP BY: group criteria, ƯÁ¤ÇÑ ±×·ìÈ­ÇÏ´Â ÇÔ¼ö°¡ ¾²ÀδÙ.
ORDER BY: ¸®ÅϵǴ rowÀÇ ¼ø¼­¸¦ Á¤ÇÑ´Ù. ASC:¿À¸§ Â÷¼ø, DESC:³»¸² Â÷¼ø, USING:(expression) ¸ñ·Ï¿¡ ¼ø¼­°¡ Á¤ÀǵǾî ÀÖÁö ¾ÊÀ» ¶§.
UNION ALL SELECT: This says to add to the results of the first SELECT this second SELECT that can be different tables, but return the same number of columns.

SELECT ¸í·ÉÀº DB·ÎºÎÅÍ ¾ÆÀÌÅÛÀ» ¸®ÅÏÇÏ´Â °Í »Ó¸¸¾Æ´Ï¶ó, itemÀ» ¼öÁ¤ÇÒ ¼öµµ ÀÖ´Ù´Â °ÍÀ» º¸¾Ò´Ù.


SELECT SUM(salary * 1.1) - SUM(salary) AS increment FROM employees;

ÀÌ°ÍÀº ÁöºÒÀÇ 10%¸¦ ¸®ÅÏÇÑ´Ù.

»ç¿ë°¡´ÉÇÑ ÇÔ¼öµé¿¡ ´ëÇؼ­ ¾Ë¾Æ º¸ÀÚ:

COUNT(): NULLÀÌ ¾Æ´Ñ rowÀÇ ¼ö¸¦ ¸®ÅÏÇÑ´Ù.
SUM(): Ä÷³µéÀÇ ÇÕÀ» ¸®ÅÏÇÑ´Ù.
AVG(): Ä÷³µéÀÇ Æò±ÕÀ» ¸®ÅÏÇÑ´Ù.
MIN(): Ä÷³¿¡¼­ °¡Àå ÀÛÀº °ªÀ» ¸®ÅÏÇÑ´Ù.
MAX(): Ä÷³¿¡¼­ °¡Àå Å« °ªÀ» ¸®ÅÏÇÑ´Ù.
FLOAT(int): Á¤¼ö¸¦ ½Ç¼ö·Î ¸®ÅÏÇÑ´Ù.,FLOAT(12345)
FLOAT4(int): Á¤¼ö¸¦ ½Ç¼ö·Î ¸®ÅÏÇÑ´Ù.FLOAT4(12345)
INT(float): ½Ç¼ö¸¦ Á¤¼ö·Î ¸®ÅÏÇÑ´Ù.,INT(123.456)
LOWER(text): text¸¦ ¼Ò¹®ÀÚ·Î ¸®ÅÏÇÑ´Ù.
UPPER(text): text¸¦ ´ë¹®ÀÚ·Î ¸®ÅÏÇÑ´Ù.
LPAD(text, long, char): textÀÇ ¿ÞÂʺÎÅÍ long¸¸Å­ÀÇ ±æÀ̱îÁö char·Î ä¿î´Ù.
RPAD(text, long, char): textÀÇ ¿À¸¥ÂʺÎÅÍ long ¸¸Å­ÀÇ ±æÀ̸¦ char·Î ä¿î´Ù.
LTRIM(text, char): textÀÇ ¿ÞÂʺÎÅÍ text¾ÈÀÇ ¸ðµç char¸¦ Áö¿î´Ù.
RTRIM(text, char): textÀÇ ¿À¸¥ÂʺÎÅÍ text¾ÈÀÇ ¸ðµç char¸¦ Áö¿î´Ù.
POSITION(string IN text): string¾È¿¡¼­ textÀÇ À§Ä¡¸¦ ÃßÃâÇÑ´Ù. ÇÏÁö¸¸, ÀÌ°ÍÀº ÀÛµ¿ÇÏÁö ¾Ê´Â´Ù.
SUBSTR(text,from[,to]): textÀÇ fromÀ§Ä¡ºÎÅÍ to±îÁö, ȤÀº ½ºÆ®¸µÀÇ ¸¶Áö¸·±îÁö(to°¡ »ý·«µÇ¾ú´Ù¸é) ¹®ÀÚ¸¦ ÃßÃâÇÑ´Ù.
DATETIME(date, hour): datetimeÀÇ Çü½ÄÀ» date (YYYY-MM-DD) ¿Í hour (HH:MM) Çü½ÄÀ¸·Î ¹Ù²Û´Ù.

À̰͵éÀº SQL¿¡ ÀÖ´Â ¸î°¡ÁöÀÇ ÇÔ¼öµéÀÌ´Ù. À̵é ÇÔ¼ö´Â ANSI SQL¿¡ Á¤ÀǵǾî ÀÖ°í, Postgres95¿¡µµ Á¤ÀǵǾî ÀÖ´Ù.

WHERE¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ¼³¸í

Áö±Ý±îÁö SELECT¿¡¼­ÀÇ WHERE¿¡ ´ëÇØ º¸¾Ò°í, ´ÙÀ½°ú °°ÀÌ ¾²ÀÏ ¼ö ÀÖ´Ù.

AND column = value

°£´ÜÇÑ ¿¹ÀÏ»ÓÀÌ°í, ´ÙÀ½°ú °°ÀÌ ¾²°Å³ª, ¾Æ´Ï¸é ÇÔ²² ¹­¾î¼­ ¾µ ¼ö ÀÖ´Ù.

AND, OR, NOT, IN, IN ALL, =, !=, >, <, (SELECT....), LIKE µîµµ ¶ÇÇÑ ¾²ÀÏ ¼ö ÀÖ´Ù. ¿¹:

WHERE 
   column IN (SELECT DISTINCT column FROM table WHERE ....)
   column IN ('value1','value2','value3',...)
   (column = 'value' and column = 'other_value' OR column != 'value')
!= '°°Áö ¾ÊÀ½'À» ³ªÅ¸³½´Ù.

LIKE wildcard¿Í ÇÔ²² ¾²¿© Ä÷³ ¾È¿¡ ¹®ÀÚ¿­ÀÌ ÀÖ´ÂÁö ã´Â´Ù.:
WHERE column LIKE '%Pepito%'
%´Â wildcardÀÌ´Ù. ¿¹¸¦ µé¾î "Pepito"°¡ ¹®ÀÚ¿­¿¡ ¾È¿¡ ÀÖÀ¸¸é ÂüÀÌ´Ù.
WHERE column LIKE 'Pepito%'
"Pepito"°¡ ¹®ÀÚ¿­ÀÇ ½ÃÀÛÀ̸é ÂüÀÌ´Ù.
WHERE column LIKE '%Pepito'
ÀÌ´Â "Pepito"°¡ ¹®ÀÚ¿­ÀÇ ³¡À̸é ÂüÀÌ´Ù.

WHERE¿Í ÇÔ²² ¾²ÀÏ ¼ö ÀÖ´Â ¸ðµç ¿É¼ÇµéÀ» ¿­°ÅÇÒ¸¸ÇÑ ½Ã°£ÀÌ ¾ø´Ù. ÇÑ°è´Â ÇÁ·Î±×·¡¸ÓÀÇ »ó»ó·ÂÀ̳ª, °¢°¢ÀÇ ¹®Àå ÀÛ¾÷ÀÇ ÇÑ°è¿¡ ÀÖ´Ù. ÀÌÁ¦ SELECT ¸í·ÉÀº ±×¸¸ÇÏ°í, ¸¶Áö¸· µÎ°¡Áö¿¡ ´ëÇؼ­ ÁýÁßÀ» ÇÏÀÚ

UPDATE ¸í·É

UPDATE ¸í·ÉÀº WHERE¿¡ ÀÖ´Â Á¶°ÇµéÀ» ¸¸Á·ÇÏ´Â, ÇÑ row ȤÀº ¿©·¯ rowÀÇ °ªÀ» ¼öÁ¤ÇÒ ¼ö ÀÖ´Ù.

SYNTAX:
UPDATE table SET column-1 = expression-1 
                 [, column-i = expression-i] 
       [WHERE condition] 
Where:
table: ¼öÁ¤ÇÏ°íÀÚ ÇÏ´Â TableÀÌ´Ù. Çѹø¿¡ ÇϳªÀÇ Table¸¸ÀÌ ¼öÁ¤µÉ ¼ö ÀÖ´Ù.
column:¼öÁ¤µÇ°íÀÚ ÇÏ´Â Ä÷³ÀÌ´Ù.
expression: Ä÷³ÀÌ ¼öÁ¤µÇ¾îÁú °ªÀÌ´Ù. ÀÌ °ªÀº Á¤ÀûÀ̰ųª, ȤÀº ÇÔ¼ö·ÎºÎÅÍ ³ª¿Â °á°ú °ªÀÏ ¼ö ÀÖ´Ù.
condition: SELECT¿¡ ÀÇÇØ ÁÖ¾îÁø ¼öÁ¤µÇ¾îÁú Á¶°ÇµéÀÌ´Ù.

DELETE ¸í·É

DELETE ¸í·ÉÀº TableÀÇ ÇÑ row ȤÀº ¿©·¯ row¸¦ º¯°æÇÒ ¼ö ÀÖ°Ô ÇÑ´Ù.

¹®¹ý
DELETE FROM table
       [WHERE condition] 

table: »èÁ¦µÉ row°¡ ÀÖ´Â TableÀÇ À̸§ÀÌ´Ù. Çѹø¿¡ ÇϳªÀÇ Table¸¸ÀÌ Áö¿öÁú ¼ö ÀÖ´Ù.
condition: SELECT¿¡ ÀÇÇؼ­ ÁÖ¾îÁø Áö¿öÁú Á¶°ÇµéÀÌ´Ù.
NOTE: WHEREÀ» ¾È ¾²¸é TableÀÇ ¸ðµç rowµéÀÌ »èÁ¦µÉ °ÍÀÌ´Ù.


ÂüÁ¶

SQL: SQLÀÇ ¼Ò°³ . PostgreSQLÀÇ ¼³Ä¡, ÀÌ ½Ã¸®ÁîÀÇ Ã³À½ ±â»ç.


¹ø¿ª:ÇãÁ¤¼ö

¿µ¹®À¸·Î ¹ø¿ª: Dallas L. Gale


º» À¥½ÎÀÌÆ®´Â Miguel Ángel Sepúlveda¾¾¿¡ ÀÇÇØ °ü¸®µË´Ï´Ù.
© Manuel Soriano 1998
LinuxFocus 1998

mirror server hosted at Truenetwork, Russian Federation.