SQL Áß±Þ : SELECT ¹® (3) : SELECT ¹®
SELECT name, age, salary
FROM employee
WHERE age > 50;
ÀÌ ¸í·ÉÀ» ½ÇÇàÇϸé employee Å×ÀÌºí¿¡¼ age°¡ 50 ÀÌ»óÀÎ ·¹Äڵ带 ÃßÃâÇÏ¿© name, age, salary Ä÷³ÀÇ ³»¿ëÀ» Á¶È¸ÇÏ°Ô µÈ´Ù.
ÁÖÀÇ : SQL¹®ÀÇ ³¡¿¡ ¼¼¹ÌÄÝ·Ð(;)À» Ãß°¡ÇØ¾ß ÇÔÀ» ÀØÀ¸¸é ¾ÈµÈ´Ù. ¼¼¹ÌÄÝ·ÐÀº SQL¹®ÀÌ ³¡³µÀ¸¸ç ½ÇÇàÇÒ Áغñ°¡ µÇ¾úÀ½À» ¾Ë·ÁÁØ´Ù.
ºñ±³ ¿¬»êÀÚ
= °°´Ù
> º¸´Ù Å©´Ù
< º¸´Ù ÀÛ´Ù
>= Å©°Å³ª ÀÛ´Ù
<= À۰ųª °°´Ù
<> °°Áö ¾Ê´Ù
LIKE ¹®ÀÚ¿ ºñ±³
LIKE¹®°ú °°ÀÌ ¾²¸é
Select * from Test where name Like '%bou%'
°á°ú °ªÀº
abou
bou
bout
about
°ú °°ÀÌ %´Â ±æÀÌ°¡ ¾ó¸¶Å µÈ´Ù Çصµ ÀüºÎ ã¾Æ ÁØ´Ù.
ALL°ú DISTINCT´Â Äõ¸® °á°ú¸¦ ¸ðµÎ(ALL:±âº»°ª) Á¶È¸Çϰųª, ¾Æ´Ï¸é Áߺ¹µÈ ·¹ÄÚµå´Â °É·¯ ³»°í(DISTINCT) Ãâ·ÂÇÒÁö¸¦ ÁöÁ¤ÇÏ´Â Å°¿öµåÀÌ´Ù. ƯÁ¤ Ä÷³À» Áߺ¹µÇÁö ¾ÊÀº »óÅ·ΠÁ¶È¸ÇÏ·Á¸é DISTINCT Å°¿öµå¸¦ »ç¿ëÇÏ¸é µÈ´Ù.
DISTINCT¸¦ ÁöÁ¤Çϸé SELECT ¹® ´ÙÀ½¿¡ ÁöÁ¤ÇÑ Ä÷³ °ªÀÌ Áߺ¹µÈ ·¹ÄÚµå´Â ¹«½ÃÇÏ°í Çϳª ¾¿¸¸ Ãâ·ÂÇÏ°Ô µÈ´Ù. ¿¹¸¦ µé¸é:
SELECT DISTINCT age
FROM employee_info;
ÀÌ ¸í·ÉÀº employee_info Å×ÀÌºí¿¡¼ age Ä÷³ °ªÀ» Á¶È¸ÇÏ¿© Áߺ¹µÈ ·¹ÄÚµå´Â Á¦¿ÜÇÏ°í Ãâ·ÂÇØ ÁØ´Ù.
ALLÀ» ÁöÁ¤Çϸé Áߺ¹µÈ ·¹Äڵ带 Æ÷ÇÔÇÏ¿© ÁöÁ¤ÇÑ Ä÷³ °ªÀ» ¸ðµÎ º¸¿©ÁØ´Ù. ¾Æ¹« °Íµµ ÁöÁ¤ÇÏÁö ¾ÊÀ¸¸é ALLÀÌ ±âº»°ªÀ¸·Î »ç¿ëµÈ´Ù.
ÁÖÀÇ : º» °Á¿¡¼´Â ´ÙÀ½ÀÇ µÎ Å×À̺íÀ» °è¼Ó »ç¿ëÇÏ°Ô µÈ´Ù. ´Ù¸¥ À©µµ¿ì¿¡ À̵éÀ» ¶ç¿ö ³õ°Å³ª ³»¿ëÀ» Ãâ·ÂÇØ ³õ°í »ç¿ëÇÏ´Â ÆíÀÌ ÁÁ´Ù.
SQL Áß±Þ : ÁýÇÕ ÇÔ¼ö (4) : ÁýÇÕ ÇÔ¼ö
MIN : ÁöÁ¤ÇÑ Ä÷³ÀÇ °¡Àå ÀÛÀº °ªÀ» ¸®ÅÏ
MAX : ÁöÁ¤ÇÑ Ä÷³ÀÇ °¡Àå Å« °ªÀ» ¸®ÅÏ
SUM : ÁöÁ¤ÇÑ Ä÷³À» ¸ðµÎ ´õÇÑ °ªÀ» ¸®ÅÏ
AVG : ÁöÁ¤ÇÑ Ä÷³ÀÇ Æò±Õ°ªÀ» ¸®ÅÏ
COUNT : ÁöÁ¤ÇÑ Ä÷³ÀÇ ·¹ÄÚµå °³¼ö¸¦ ¸®ÅÏ
COUNT(*) : Å×À̺íÀÇ ·¹ÄÚµå °³¼ö¸¦ ¸®ÅÏ
¡¡
ÁýÇÕ ÇÔ¼ö´Â SELECT ¹®ÀÇ °á°ú Áß¿¡¼ ¼ýÀÚ µ¥ÀÌÅ͸¦ ´ë»óÀ¸·Î °è»êÇÏ´Â ÇÔ¼öÀÌ´Ù.
Á¶È¸ÇÑ µ¥ÀÌÅÍ °¡¿îµ¥ ƯÁ¤ Ä÷³ÀÇ °á°ú¸¦ ¿ä¾àÇÏ´Â ±â´ÉÀ» °®´Â´Ù°í ÇÒ ¼ö ÀÖ´Ù.
SQL Áß±Þ : HAVINGÀý ±¸¹® (6) : HAVING Àý ±¸¹®
´ÙÀ½ ÀåÀÎ "GROUB BY Àý"¿¡¼ ÇÊ¿äÇϱ⠶§¹®¿¡ ¿©±â¼´Â ÁýÇÕ ÇÔ¼ö¿¡ ´ëÇؼ ¾Ë¾Æº¸µµ·Ï ÇÏÀÚ.
ÁýÇÕ ÇÔ¼ö°¡ GROUP BY Àý¿¡¼ ÇÊ¿äÇÑ ¿ä¼ÒÀ̱ä ÇÏÁö¸¸ GROUB BY Àý ¾øÀÌ »ç¿ëÇÒ ¼öµµ ÀÖ´Ù.
SELECT column1, SUM(column2)
FROM "list-of-tables"
GROUP BY "column-list"
HAVING "condition";
HAVING
ÀýÀº °¢ ±×·ìº°·Î Á¶°ÇÀ» ÁöÁ¤ÇÒ ¼ö ÀÖ°Ô ÇØ ÁØ´Ù. Áï, »ç¿ëÀÚ°¡ ÁöÁ¤ÇÑ Á¶°Ç¿¡ µû¶ó¼ ¾î¶² ·¹Äڵ带 Á¶È¸ÇÒ °ÍÀÎÁö °áÁ¤ µÈ´Ù.
HAVING ÀýÀº GROUP BY Àý ´ÙÀ½¿¡ ³ª¿Í¾ß ÇÑ´Ù.
HAVING ÀýÀ» ÀÌÇØÇÏ·Á¸é ¿¹Á¦¸¦ º¸´Â ÆíÀÌ ºü¸£´Ù. Á÷¿øÀÇ name, department, salary, age¸¦ ÀúÀåÇÏ°í ÀÖ´Â employee Å×À̺íÀÌ ÀÖ´Ù°í ÇÏÀÚ. °¢ ºÎ¼º°·Î Á÷¿øµéÀÇ Æò±Õ ±Þ¿©¸¦ ±¸ÇÏ°íÀÚ ÇÑ´Ù¸é ´ÙÀ½°ú °°ÀÌ ½ÇÇàÇÏ¸é µÈ´Ù.
SELECT dept, avg(salary)
FROM employee
GROUP BY dept;
¸¸¾à Æò±Õ ±Þ¿©°¡ 20000 ÀÌ»óÀÎ °æ¿ì¿¡ ´ëÇؼ¸¸ Á¶È¸ÇÏ°í ÇÑ´Ù¸é :
SELECT dept, avg(salary)
FROM employee
GROUP BY dept
HAVING avg(salary)>=20000;
SQL Áß±Þ : ORDERÀý ±¸¹® (7) : ORDER Àý ±¸¹®
ORDER BY´Â SELECT ¹® ¿É¼ÇÀÇ Çϳª·Î¼ ÁöÁ¤ÇÑ Ä÷³À» ±âÁØÀ¸·Î Á¶È¸ °á°ú¸¦ Á¤·Ä(¿À¸§Â÷¼ø ¶Ç´Â ³»¸²Â÷¼ø)ÇÏ¿© º¸¿© Áִµ¥ »ç¿ëµÈ´Ù.
ASC = ¿À¸§ Â÷¼ø - ±âº»°ª
DESC = ³»¸² Â÷¼ø