SQL Áß±Þ : IN°ú BETWEEN ¿¬»êÀÚ (9) : IN°ú BETWEEN ¿¬»êÀÚ
SELECT column1, SUM(column2)
FROM "list-of-tables"
WHERE column3 IN (list-of-values);
SELECT column1, SUM(column2)
FROM "list-of-tables"
WHERE column3 BETWEEN value1 AND value2;
IN Á¶°Ç ¿¬»êÀÚ´Â ÁýÇÕÀÇ ¿ø¼Ò ¿©ºÎ¸¦ °Ë»çÇÏ´Â ¿¬»êÀÚ´Ù. Áï, Å°¿öµå IN ¾Õ¿¡ ÁÖ¾îÁø °ªÀÌ IN ´ÙÀ½¿¡ ³ª¿Í ÀÖ´Â ¸®½ºÆ®¿¡ Æ÷ÇԵǾî ÀÖ´ÂÁö¸¦ °Ë»çÇÒ ¶§ »ç¿ëµÈ´Ù.
¿¹:
SELECT employeeid, lastname, salary
FROM employee_info
WHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');
ÀÌ°ÍÀº employee_info Å×ÀÌºí¿¡¼ lastnameÀÌ Hernandez, Jones, Roberts, Ruiz ÁßÀÇ ÇϳªÀÎ °æ¿ì¿¡ ´ëÇؼ employeeid, lastname ,salary¸¦ Á¶È¸ÇÏ´Â ¸í·ÉÀÌ´Ù.
IN
Á¶°Ç ¿¬»êÀÚ ´ë½Å µîÈ£(=)¿Í OR ¿¬»êÀÚ¸¦ Á¶ÇÕÇÏ¿© µ¿ÀÏÇÑ °á°ú¸¦ ¸¸µé¾î ³¾ ¼öµµ ÀÖ´Ù.
SELECT employeeid, lastname, salary
FROM employee_info
WHERE lastname = 'Hernandez' OR lastname = 'Jones' OR lastname = 'Roberts' OR lastname = 'Ruiz';
º¸´Â ¹Ù¿Í °°ÀÌ µÎ¼¼ °³ ÀÌ»óÀÇ Á¶°ÇÀ» ÁöÁ¤ÇÒ ¶§¿¡´Â IN ¿¬»êÀÚ¸¦ »ç¿ëÇÏ´Â ÆíÀÌ ÈξÀ °£°áÇÏ°í Àб⵵ ÆíÇÔÀ» ¾Ë ¼ö ÀÖ´Ù.¡¡
¹Ý´ë·Î ¸®½ºÆ®¿¡ ³ª¿Í ÀÖ´Â °ªµéÀÌ ÀÖ´Â °æ¿ì¸¸À» Á¦¿ÜÇϱâ À§Çؼ´Â NOT IN ¿¬»êÀÚ¸¦ »ç¿ëÇÒ ¼öµµ ÀÖ´Ù.¡¡
BETWEEN Á¶°Ç ¿¬»êÀÚ´Â BETWEEN ¾Õ¿¡ ³ª¿Í ÀÖ´Â °ªÀÌ BETWEEN µÚ¿¡ ³ª¿Í ÀÖ´Â 2°³ÀÇ °ª »çÀÌ¿¡ À§Ä¡ÇÏ´Â Áö¸¦ °Ë»çÇÒ ¶§ »ç¿ëµÈ´Ù.¡¡
¿¹:¡¡
SELECT employeeid, age, lastname, salary
FROM employee_info
WHERE age BETWEEN 30 AND 40;¡¡
À̸¦ ½ÇÇàÇϸé employee_info Å×ÀÌºí¿¡¼ age°¡ 30¿¡¼ 40 »çÀÌÀÎ °æ¿ì(30°ú 40 Æ÷ÇÔ)¿¡ ´ëÇÏ¿© employeeid, age, lastname, salary¸¦ Á¶È¸ÇÏ°Ô µÈ´Ù.
ÀÌ ¸í·ÉÀº ´ÙÀ½°ú °°ÀÌ BETWEEN ¿¬»êÀÚ¸¦ »ç¿ëÇÏÁö ¾Ê°íµµ ÀÛ¼ºÇÒ ¼ö ÀÖ´Ù.¡¡
SELECT employeeid, age, lastname, salary
FROM employee_info
WHERE age >= 30 AND age <= 40;
NOT BETWEENÀ» »ç¿ëÇϸé ÁöÁ¤ÇÑ ¹üÀ§¿¡ ÇØ´çÇÏ´Â °ª¸¸À» Á¦¿ÜÇÒ ¼öµµ ÀÖ´Ù.
SQL Áß±Þ : ¼öÇÐ ÇÔ¼öµé (10) : ¼öÇÐ ÇÔ¼öµé
+ |
´õÇϱâ |
- |
»©±â |
* |
°öÇϱâ |
/ |
³ª´©±â |
% |
³ª¸ÓÁö |
³ª¸ÓÁö(%) ¿¬»êÀÚ´Â ³ª´°¼À¿¡¼ ³ª¸ÓÁö °ªÀ» ±¸ÇÏ´Â ¿¬»êÀÚÀÌ´Ù. ÀÌ ¿¬»êÀÚ´Â ANSI SQL¿¡ Æ÷ÇԵǾî ÀÖÁö´Â ¾ÊÁö¸¸ ´ëºÎºÐÀÇ µ¥ÀÌÅͺ£À̽º¿¡¼ Áö¿øÇÏ°í ÀÖ´Ù. ¾Æ·¡¿¡´Â ¾Ë¾ÆµÎ¸é À¯¿ëÇÑ ¸î °¡Áö ¼öÇÐ ÇÔ¼ö¸¦ ¼Ò°³ÇÏ°í ÀÖ´Ù. À̵éÀº ANSI SQL-92¿¡´Â Ç¥ÁØÀ¸·Î äÅõǾî ÀÖÁö ¾Ê±â ¶§¹®¿¡ µ¥ÀÌÅͺ£À̽º¿¡ µû¶ó¼ Áö¿øÇÒ ¼öµµ ÀÖ°í ±×·¸Áö ¾ÊÀ» ¼öµµ ÀÖ´Ù. ±×·¯³ª ¸î¸î ÁÖ¿ä µ¥ÀÌÅͺ£À̽º¿¡¼´Â Áö¿øÇÏ°í ÀÖÀ½À» È®ÀÎÇÏ¿´À¸¸ç º» °Á¿¡¼µµ »ç¿ëÇÒ ¼ö ÀÖ´Ù.
¡¡
ABS(x) |
xÀÇ Àý´ë°ªÀ» ¸®ÅÏ |
SIGN(x) |
xÀÇ ºÎÈ£¸¦ ¸®ÅÏ(À½¼ö¸é -1, 0À̸é 0, ¾ç¼ö¸é +1) |
MOD(x,y) |
x¸¦ y·Î ³ª´« ÈÄÀÇ ³ª¸ÓÁö °ªÀ» ¸®ÅÏ(x%y¿Í µ¿ÀÏ) |
FLOOR(x) |
xº¸´Ù °°°Å³ª ÀÛÀº °¡Àå Å« Á¤¼ö¸¦ ¸®ÅÏ |
CEILING(x) or CEIL(x) |
xº¸´Ù Å©°Å³ª °°Àº °¡Àå ÀÛÀº Á¤¼ö¸¦ ¸®ÅÏ |
POWER(x,y) |
xÀÇ y½ÂÀ» ¸®ÅÏ |
ROUND(x) |
x¸¦ °¡Àå °¡±î¿î Á¤¼ö·Î ¹Ý¿Ã¸²ÇÑ °á°ú¸¦ ¸®ÅÏ |
ROUND(x,d) |
x¸¦ d¿¡ ÁöÁ¤ÇÑ ¼Ò¼öÁ¡±îÁö ¹Ý¿Ã¸²ÇÑ °á°ú¸¦ ¸®ÅÏ |
SQRT(x) |
xÀÇ Á¦°ö±ÙÀ» ¸®ÅÏ |
¿¹:
SELECT round(salary), firstname
FROM employee_info
ÀÌ Äõ¸® ¹®Àº employee_info Å×ÀÌºí¿¡¼ salary¸¦ °¡Àå °¡±î¿î Á¤¼ö·Î ¹Ý¿Ã¸²ÇÑ °á°ú¿Í firstnameÀ» Á¶È¸ÇÏ´Â ¸í·ÉÀÌ´Ù.