14. ¼ºêÄõ¸®
• ¶Ç ´Ù¸¥ Äõ¸® ¾È¿¡ Äõ¸®
• Å×À̺í ŸÀÔ °á°ú¿¡ µû¸¥ ÇÊ¿äÇÑ ¾î¶² Äõ¸® ¾È¿¡ Á¤È®È÷ ±× ¼ºê Äõ¸®´Â À§Ä¡ÇÔ
• Çѹø¿¡ ÇÊ¿äÇÑ ¾Ë¸Â´Â ¼ºêÄõ¸® Ä«Å×°í¸® ÀÌÇØ¿Í »ç¿ë
• ±¸¹®¾È¿¡ ¼ºêÄõ¸®°¡ À§Ä¡ÇÒ ¶§ ¾Ë¸Â´Â SQL ¹®¹ý »ç¿ë
• Á¶ÀÎÀ¸·Î ¼ºêÄõ¸® º¯È¯
14-1. ¼ºêÄõ¸® º¸±â
• Äõ¸®´Â ¶Ç ´Ù¸¥ Äõ¸® ¾È¿¡ ÀÖÀ½
• °ýÈ£'()' ·Î µÑ·¯ ½ÎÀÓ
mysql> SELECT Language
-> FROM CountryLanguage
-> WHERE CountryCode = (SELECT Code
->FROM Country
->WHERE Name='Finland');
• ¿Ö ¼ºêÄõ¸®¸¦ »ç¿ëÇϳª?
• Å×À̺í ŸÀÔ °á°ú
- ŸÀÔÀº ¾î¶»°Ô ±×°ÍÀ» »ç¿ëÇØ¾ß ÇÏ´Â Áö¸¦ °áÁ¤
Type: Á¤ÀÇ:
Scalar ½Ì±Û Ä÷³°ú ½Ì±Û ¿
Row Çϳª ȤÀº ±× ÀÌ»óÀÇ Ä÷³°ú ½Ì±Û ¿
Column Çϳª ȤÀº ±× ÀÌ»óÀÇ ¿°ú ½Ì±Û Ä÷³
Table Çϳª ±× ÀÌ»óÀÇ Ä÷³°ú Çϳª ȤÀº ±× ÀÌ»óÀÇ ¿
Empty ¾î¶² ¿ ȤÀº Ä÷³ µ¥ÀÌŸ°¡ ¾øÀ½
14-2. ¼ºêÄõ¸®ÀÇ Ä«Å×°í¸®
• ¿¬°ü °ü°è ÀÖÀ½
- ¿ÜºÎ Äõ¸® ÂüÁ¶
- È¥ÀÚ »ç¿ë ¾ÈµÊ
• ¿¬°ü °ü°è ¾øÀ½
- ¿ÜºÎ Äõ¸® ÂüÁ¶ ¾ÈÇÔ
- È¥ÀÚ »ç¿ë µÊ
14-3. ¿¬°ü °ü°è°¡ ÀÖ´Â ¼ºêÄõ¸®
mysql> SELECT Country.Name,
-> (SELECT COUNT(*) FROM City
->WHERE CountryCode=Country.Code)
-> as CityCount FROM Country;
14-4. ¿¬°ü °ü°è°¡ ¾ø´Â ¼ºêÄõ¸®
mysql> SELECT Name FROM City WHERE CountryCode IN
-> (SELECT Code FROM Country
->WHERE Continent = 'Oceania');
¡¦ ±×¸®°í ¼ºêÄõ¸®¸¸ »ç¿ë¡¦
mysql> SELECT Code FROM Country
-> WHERE Continent = 'Oceania');
14-5. ¼ºêÄõ¸® À§Ä¡
• ´Ù¾çÇÑ Å¸ÀÔ/Ä«Å×°í¸®
- ½ºÅÙµå-Æ÷ÀÎÆ®(stand-point) ¹èÄ¡·Î ºÎÅÍ °¡Àå ÁÁÀº ÀÇ°ß
• Â÷Æ® ¹èÄ¡
– Ä÷³
– FROM Àý
– WHERE Àý
14-6. ¼ºêÄõ¸® ŸÀÔ/¹èÄ¡ Ç¥
• ¹èÄ¡¿Í Å©·Î½º-ÂüÁ¶ ŸÀÔ
14-7. FROM Àý¿¡ ¾È¿¡ ¼ºêÄõ¸®
• ¸ðµç Ä÷³Àº À̸§À» °¡Áü
• ¼ºêÄõ¸®´ÂÅ×À̺í Äõ¸®°¡ µÉ ¼ö ÀÖÀ½
- ¸ðµç °ªÀ» »ç¿ëÇÏÁö ¸øÇÒ Áö¶óµµ
mysql> SELECT AVG(cont_sum)
-> FROM(SELECT Continent, SUM(Population) AS cont_sum
-> FROM Country
-> GROUP BY Continent)
-> AS t;
14-8. WHERE Àý ¾È¿¡ ¼ºêÄõ¸®
• ¼ºêÄõ¸®ÀÇ °¡Àå ÀϹÝÀûÀÎ À§Ä¡
- ÇÑ Å¸ÀÔ/¹èÄ¡ Ç¥
- ¸ðµç Å×À̺í ŸÀÔÀº WHERE Àý/¿¬»êÀÚ¿Í ÇÔ²² »ç¿ë
• ÀÏ¹Ý ¿¬»êÀÚ¿Í ÄÁ½ºÆ®·°Æ®(Construct) »ç¿ë
14-9. ¿¬»êÀÚ¸¦ °¡Áø WHERE
• ¿¬»êÀÚ
- =, <, >
• ¼ºêÄõ¸® °á°ú¿Í ¿ÜºÎ Äõ¸® ºñ±³
mysql> SELECT Continent, Name, Population
-> FROM Country c
-> WHERE Population = (SELECT MAX(Population)
-> FROM Country c2
-> WHERE c.Continent=c2.Continent
-> AND Population > 0
-> );
14-10. WHERE ÄÁ½ºÆ®·°Æ®(Construct)
• IN/NOT IN
• ANY
• ALL
• SOME
• EXISTS/ NOT EXISTS
• IN/ NOT IN »ç¿ë
• 'IN'Àº ±â´ÉÀûÀ¸·Î '=ANY'¿Í °°À½
mysql> SELECT Name, Population FROM City
-> WHERE Country IN(SELECT Code FROM Country
-> WHERE Continent = 'Europe')
-> ORDER BY Name LIMIT 10;
• ALL, ANY ¿Í SOME »ç¿ë
• ¼ºêÄõ¸® Ä÷³À» ¸í½ÃÇÑ ºñ±³
• ALL
mysql> SELECT Name FROM City
-> WHERE Population >
-> ALL(SELECT Population FROM City
-> WHERE CountryCode = 'CHN');
• ANY¸¦ »ç¿ëÇÑ ºñ±³
- ¾î¶² ¸í½ÃµÈ ¼ºêÄõ¸® °ª
mysql> SELECT Name
-> FROM Country
-> WHERE Continent = 'Europe'
-> AND Code = ANY(SELECT CountryCode
-> FROM CountryLanguage
-> WHERE Language = 'Spanish')
-> ORDER BY Name;
• °á°ú¸¦ È®ÀÎÀ» À§ÇØ ¼ºêÄõ¸®¸¦ ½ÇÇà
mysql> SELECT CountryCode FROM CountryLanguage
-> WHERE Language = 'Spanish';
• EXISTS/ NOT EXISTS »ç¿ë
• ¾î¶² ¼ºêÄõ¸®°¡ ¿À» ¸®ÅÏÇÒ Áö ¾ÈÇÒ Áö Å×½ºÆ®
mysql> SELECT Continent FROM Country WHERE
-> EXISTS(SELECT * FROM City WHERE Code =
-> CountryCode AND Population > 8000000)
-> GROUP BY Continent;
14-11. ¼ºêÄõ¸®¸¦ »ç¿ëÇÏ¿© ¼öÁ¤µÈ Å×À̺í
• ¼ºêÄõ¸®´Â SELECT¿¡ Á¦ÇѵÇÁö ¾ÊÀ½
• DELETE¿Í UPDATE¸¦ »ç¿ë
• DELETE
mysql> DELETE FROM City
-> WHERE CountryCode IN
-> (SELECT Code
-> FROM Country
-> WHERE LifeExpectancy < 70.0);
• UPDATE
mysql> UPDATECity SET District = 'The Big Apple'
-> WHERE CountryCode IN
-> (SELECT Code FROM Country WHERE Name = 'United States')
-> AND District = 'New York';
14-12. Á¶ÀÎÀ¸·Î ¼ºêÄõ¸® º¯È¯
• Á¶ÀÎÀº ¼ºêÄõ¸® º¸´Ù ´õ È¿À²ÀûÀÏ ¼ö ÀÖÀ½
- ¸¸¾à ¼ºêÄõ¸®°¡ ´À¸®°Ô µ¿ÀÛÇÑ´Ù¸é, Á¶ÀÎÀ¸·Î ¼ºêÄõ¸® º¯È¯
• ¼ºêÄõ¸®¿Í Á¶ÀÎ
mysql> SELECT Name FROM Country
-> WHERE Code IN
-> (SELECT CountryCode
-> FROM CountryLanguage);
mysql> SELECT Name
-> FROM Country JOIN CountryLanguage
-> ON Code=CountryCode;
• °¢°¢ÀÇ À̸§À» Çѹø¸¸ ³ª¿
mysql> SELECT DISTINCTName
-> FROM Country JOIN CountryLanguage
-> ON Code=CountryCode;