Oracle/MySQL °ü·Ã °Ô½ÃÆÇ |
---|
2001/08/25(15:40) from 210.117.182.251 | |
ÀÛ¼ºÀÚ : °ÁÙ±â (jkkang65@hanmail.net) | Á¶È¸¼ö : 4331 , ÁÙ¼ö : 1249 |
[MySQL] Tutorial |
---|
¸Å´º¾óÀÌ ¾÷µ¥ÀÌÆ®µÇ¸é¼ ¹Ù²Û ºÎºÐÀÔ´Ï´Ù. Àü¿¡ 3.21ÀÇ °æ¿ì¿¡´Â 8ÀåÀÌ SQL¿¡ ´ëÇÑ °£·«ÇÑ ¿¹Á¦¸¸ ÀÖ¾ú´Âµ¥ ÃÖ±Ù¿¡ ¸¹ÀÌ º¸°µÈ °ÍÀÌÁö¿ä. ÀÚ·á Á¦°øÇØÁֽŠÀÌ»ó¿ë´Ô¿¡°Ô °¨»çµå¸³´Ï´Ù. ¹®ÅÂÁØ(taejun@taejun.pe.kr) ¹ø¿ª: ÀÌ»ó¿ë(sangyong@nownuri.net, sylee@linuxkorea.co.kr) ¹ø¿ªÀÏÀÚ: 1999³â 9¿ù 9ÀÏ ------------------------------------------------------------------------------ ´ÙÀ½ ¹®¼´Â MySQL 3.22.25 ¹öÀü ¸Å´º¾óÀÇ 8Àå ºÎºÐÀ» ¹ø¿ªÇÑ °ÍÀÔ´Ï´Ù. º¸°í ¿À¿ªÀ̳ª ³»¿ëÀÌ ÀÌ»óÇÏ´Ù¸é À§ÀÇ ÀüÀÚ¿ìÆí ÁÖ¼Ò·Î ¿¬¶ôÇØ ÁÖ¸é °í¸¿°Ú½À´Ï´Ù. Á÷¿ªÇÑ °Íµµ ÀÖ°í ÀÇ¿ªÇÑ °Íµµ ÀÖ°í ³»¿ë ¼ø¼°¡ ¹Ù²ï °Íµµ ÀÖ°í, Á¦°¡ Ãß°¡ÇÑ °Íµµ ÀÖ½À´Ï´Ù. ¿ø¹®°ú´Â ³»¿ëÀÌ ´Ù¸¥ ºÎºÐµµ ¸¹À» °ÍÀÔ´Ï´Ù. ÀÌÁ¡ Âü°íÇϱ⠹ٶø´Ï´Ù. ------------------------------------------------------------------------------ MySQL Æ©Å丮¾ó mysqlÀ̶ó´Â Ŭ¶óÀ̾ðÆ® ÇÁ·Î±×·¥À» ÀÌ¿ëÇÏ¿© MySQLÀ» ÀÍÇô º¸µµ·Ï ÇÏÀÚ. mysqlÀº °£´ÜÈ÷ µ¥ÀÌÅͺ£À̽º¸¦ ¸¸µé°í »ç¿ëÇÒ ¼ö ÀÖ°Ô ÇØ ÁÖ´Â ÇÁ·Î±×·¥À¸·Î 'Å͹̳Π¸ð´ÏÅÍ' ȤÀº °£´ÜÈ÷ '¸ð´ÏÅÍ'¶ó°íµµ ÇÑ´Ù. mysqlÀº ´ëÈ½Ä ÇÁ·Î±×·¥À¸·Î¼ ¼¹ö¿¡ ¿¬°áÇÏ°í, Áú¹®À» ¼öÇàÇÏ°í, °á°ú¸¦ ȸ鿡 º¸¿©ÁÖ´Â ÀÏÀ» ÇÑ´Ù. mysqlÀº ¹èÄ¡ ¸ðµå(batch mode)¿¡¼µµ »ç¿ëÇÒ ¼ö ÀÖ´Ù: ¹Ì¸® ÆÄÀÏ¿¡ sql ¸í·É¹®À» ³Ö¾îµÎ°í mysql¿¡°Ô ÆÄÀÏÀÇ ¸í·ÉÀ» ¼öÇàÇ϶ó°í ÇÏ¸é µÈ´Ù(µÚ¿¡¼ ¾Ë¾Æ º¸°ÚÁö¸¸ 'mysql -vvv < batch_test.txt' ½ÄÀ¸·Î »ç¿ëÇÏ¸é µÈ´Ù). mysqlÀÇ ¿É¼ÇµéÀ» º¸·Á¸é --help ¿É¼ÇÀ» ºÙ¿©¼ ½ÇÇàÇÏ¸é µÈ´Ù: shell> mysql --help ÀÌ Æ©Å丮¾ó¿¡¼´Â mysql¿¡ ¼³Ä¡µÇ¾î ÀÖÀ¸¸ç Á¢±ÙÇÒ ¼ö ÀÖ´Â MySQL ¼¹ö°¡ ÀÖ´Ù´Â °ÍÀ» °¡Á¤ÇÑ´Ù. ±×·¸Áö ¾ÊÀ¸¸é MySQL °ü¸®ÀÚ¿¡°Ô ¹®ÀÇÇ϶ó(¿©·¯ºÐÀÌ °ü¸®ÀÚ¶ó¸é MySQL ¹®¼ ÀÇ ´Ù¸¥ ºÎºÐÀ» »ìÆ캼 ÇÊ¿ä°¡ ÀÖÀ» °ÍÀÌ´Ù). º» Æ©Å丮¾ó¿¡¼´Â µ¥ÀÌÅͺ£À̽º¸¦ ¼³°èÇÏ°í »ç¿ëÇÏ´Â ¸ðµç °úÁ¤À» ´Ù·é´Ù. ÀÌ¹Ì Á¸ÀçÇÏ´Â µ¥ÀÌÅͺ£À̽º¸¦ »ç¿ëÇÏ´Â °Í¿¡¸¸ °ü½ÉÀÌ ÀÖ´Ù¸é µ¥ÀÌÅÍ º£À̽º¿Í ±× ¾È¿¡ ÀÖÀ» Å×À̺íÀ» ¸¸µå´Â ¹æ¹ýÀ» ¼³¸íÇÑ ÀýÀº °Ç³Ê¶ç¾îµµ ÁÁ´Ù. Æ©Å丮¾ó ¼º°ÝÀÇ ±ÛÀ̶ó ÀÚ¼¼ÇÑ °ÍÀº ¼³¸íµÇÁö ¾Ê´Â´Ù. ¿©±â¿¡ ¾ð±ÞµÈ °Í¿¡ ´ëÇØ ´õÀÚ¼¼È÷ ¾Ë°í ½Í°Åµç MySQLÀÇ °ü·Ã ¸Å´º¾óÀ» º¸¸éµÈ´Ù. shell>Àº ½© ÇÁ·ÒÇÁÆ®¸¦, mysql>Àº MySQL ÇÁ·ÒÇÁÆ®¸¦ ³ªÅ¸³½´Ù. 1. ¼¹ö¿¡ ¿¬°áÇϱâ/¿¬°á²÷±â ¼¹ö¿¡ Á¢¼ÓÇÏ·Á¸é mysql ¸í·ÉÀ» ³»¸± ¶§ MySQL »ç¿ëÀÚ À̸§°ú ´ë°³ÀÇ °æ¿ì Æнº¿öµå¸¦ ½á ÁÖ¾î¾ß ÇÒ °ÍÀÌ´Ù. ¼¹ö°¡ ¿©·¯ºÐÀÌ ·Î±äÇÑ ÄÄÇ»ÅÍ°¡ ¾Æ´Ñ °Í¿¡¼ ¿î¿µµÈ´Ù¸é È£½ºÆ® À̸§µµ ½á ÁÙ ÇÊ¿ä°¡ ÀÖÀ» °ÍÀÌ´Ù(È£½ºÆ® À̸§, »ç¿ëÀÚ À̸§, Æнº¿öµå).¸ðµç °ÍÀ» ¾Ë¾Ò´Ù¸é ´ÙÀ½Ã³·³ ¿¬°áÇÒ ¼ö ÀÖ´Ù: shell> mysql -h host -u user -p Enter password: ******** ******* ºÎºÐÀº Æнº¿öµå´Ù. 'Enter password' ÇÁ·ÒÇÁÆ®°¡ º¸À̸é Æнº¿öµå¸¦ ÃÄÁÖ¸é µÈ´Ù. ¼º°øÇÏ¸é °£´ÜÇÑ ¼Ò°³ ¸Þ½ÃÁöµéÀ» º¸°í 'mysql>' ÇÁ·ÒÇÁÆ®¸¦ º¼ ¼ö ÀÖÀ» °ÍÀÌ´Ù. shell> mysql -h host -u user -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 459 to server version: 3.22.20a-log Type 'help' for help. mysql> 'mysql>' ÇÁ·ÒÇÁÆ®°¡ ÀǹÌÇÏ´Â ¹Ù´Â ÁغñµÇ¾úÀ¸´Ï ¸í·É¾î¸¦ ÀÔ·ÂÇ϶ó´Â ¸»ÀÌ´Ù. ¾î¶»°Ô ¼³Ä¡ÇÏ´Â °¡¿¡ µû¶ó MySQLÀº ·ÎÄà ȣ½ºÆ®(LOCAL host)¿¡¼ ¿î¿µµÇ´Â ¼¹ö¿¡ "¹«¸íÀÇ »ç¿ëÀÚ(anonymous user)"·Î Á¢¼ÓÇÒ ¼ö ÀÖ°Ô ÇÑ´Ù. ÀÌ·² °æ¿ì¿¡´Â ´Ü¼øÈ÷ shell> mysql ó·³ Çؼ ¿¬°áÇÒ ¼ö ÀÖ´Ù. ¼º°øÀûÀ¸·Î Á¢¼ÓÇÏ¿´´Ù¸é 'mysql>' ÇÁ·ÒÇÁÆ®¿¡¼ ¾ðÁ¦µçÁö 'QUIT'À̶ó°í Ãļ ¼¹ö¿¡¼ ³ª¿Ã ¼ö ÀÖ´Ù: mysql> QUIT Bye CtrlÅ°¿Í D Å°¸¦ µ¿½Ã¿¡ ´·¯ ºüÁ® ³ª¿Ã ¼öµµ ÀÖ´Ù. À̾îÁö´Â Àý¿¡¼ ³ª¿À´Â ´ëºÎºÐÀÇ ¿¹´Â ¼¹ö¿¡ ¿¬°áÇÑ »óŶó´Â °ÍÀ» °¡Á¤ÇÑ´Ù. 'mysql>' ÇÁ·ÒÇÁÆ®´Â ¼¹ö¿¡ ¿¬°áµÈ »óŶó´Â °ÍÀ» ³ªÅ¸³½´Ù. 2. Áú¹® Çϱâ(Entering Queries) ÀÌÀü Àý¿¡¼ ¾ð±ÞÇÏ¿´µíÀÌ ¼¹ö¿¡ Á¢¼ÓµÈ °ÍÀ» È®ÀÎÇÏÀÚ. ÀÌ·¸°Ô ÇÑ´Ù°í ÀÛ¾÷ÇÒ µ¥ÀÌÅͺ£À̽º ¾î¶² °Íµµ ¼±ÅÃÇÏ´Â °ÍÀº ¾Æ´ÏÁö¸¸ ¾î¶µç Á¢¼ÓÀº ÇØ¾ß ÇÑ´Ù. Áö±Ý »óȲ¿¡¼´Â µ¥ÀÌÅͺ£À̽º¾È¿¡ Å×À̺íÀ» ¸¸µé°í, Å×ÀÌºí¿¡ ÀڷḦ ¿Ã¸®°í, Å×ÀÌºí¿¡¼ ÀڷḦ »© ³»´Â °Íº¸´Ù´Â Áú¹®ÇÏ´Â ¹ýÀ» ¾à°£ÀÌ¶óµµ ¹è¿ì´Â °Ô ´õ Áß¿äÇÏ´Ù. À̹ø Àý¿¡¼´Â ¸í·É¾î ÀÔ·ÂÀÇ ±âº» ¿øÄ¢À» ¸î°¡Áö Áú¹® ¿¹¸¦ ÅëÇØ ¾Ë¾Æ º»´Ù. ¿¹¸¦ ÅëÇØ ¾î¶»°Ô mysqlÀÌ µ¿ÀÛÇÏ´ÂÁö Àͼ÷ÇØ Áú °ÍÀÌ´Ù. ¾Æ·¡¿¡ MySQLÀÇ ¹öÀü°ú ¿À´Ã ³¯Â¥¸¦ Ãâ·ÂÇÏ´Â ¸í·É¾î¸¦ º¸ÀδÙ. 'mysql>' ÇÁ·ÒÇÁÆ®´ÙÀ½¿¡ ³ª¿À´Â ´ë·Î ÃÄ ³ÖÀÚ. ±×¸®°í ¿£ÅÍÅ°¸¦ Ä£´Ù. mysql> SELECT version(), current_date; +-------------+------------------+ | version() | current_date | +-------------+-----------------+ | 3.22.20a-log| 1999-03-19 | +-------------+-----------------+ 1 row in set (0.01 sec) mysql> ÀÌ ¿¹·ÎºÎÅÍ mysql¿¡ ´ëÇÑ ¸î°¡Áö °ÍµéÀ» ¾Ë ¼ö ÀÖ´Ù: ¡Ü ¸í·ÉÀº SQL ¹®°ú ±× µÚ¿¡¿À´Â ¼¼¹ÌÄÝ·Ð(;)À¸·Î ÀÌ·ç¾î Áø´Ù(¼¼¹ÌÄÝ·ÐÀÌ ÇÊ¿ä¾ø´Â ¿¹¿Ü°¡ ÀÖ±äÇÏ´Ù. QUITÀÌ ±× Áß Çϳª´Ù. ³ªÁß¿¡ ÀÌ°Í¿¡ ´ëÇØ ´Ù½Ã ¾ð±ÞÇÏ°Ú´Ù). ¡Ü ¿©·¯ºÐÀÌ ¸í·ÉÀ» ³»¸®¸é, mysqlÀº ¼¹ö·Î ±× ¸í·ÉÀ» º¸³»¾î ½ÇÇàµÇ°Ô ÇÏ°í, ±× °á°ú¸¦º¸¿© ÁÖ°í ´Ù½Ã ¸í·É ´ë±â »óÅ ÇÁ·ÒÇÁÆ®('mysqld>')¸¦ ³½´Ù. ¡Ü mysqlÀº Å×À̺í Çü½Ä(Çà°ú ¿·Î ÀÌ·ç¾îÁø)À¸·Î °á°ú¸¦ º¸¿©ÁØ´Ù. ù ÇàÀº °¢ ¿¿¡ ´ëÇѶóº§À» °®°í ÀÖ´Ù. µÎ ¹ø° Çà ºÎÅÍ´Â Áú¹®ÀÇ ´äÀÌ ³õÀδÙ. º¸Åë, ¿ÀÇ ¶óº§Àº µ¥ÀÌÅÍ º£À̽º Å×ÀÌºí¿¡¼ °¡Á®¿À´Â ¿ÀÇ À̸§ÀÌ´Ù. ¹æ±Ý º¸ÀÎ °Íó·³ Å×ÀÌºí ¿ÀÌ ¾Æ´Ñ Ç¥Çö½Ä(expression)À» »ç¿ëÇÒ ¶§´Â ¶óº§¸íÀº ±× Ç¥Çö½ÄÀÌ µÈ´Ù. ¡Ü mysqlÀº ÇàÀÇ ¼ö¿Í ¸í·É ½ÇÇà ½Ã°£(´ë·«ÀûÀÎ ¼¹ö ¼º´É ÃøÁ¤ µµ±¸´Ù)À» º¸¿© ÁØ´Ù. ¸í·É ½ÇÇà ½Ã°£Àº Á¤È®ÇÑ °ªÀº ¾Æ´Ï´Ù. ¿Ö³ÄÇϸé ÀÌ ½Ã°£Àº wall clock time(CPU ½Ã°£ÀÌ ¾Æ´Ï´Ù)À̶ó´Â °Í°ú ¼¹ö ºÎÇÏ ¹× ³×Æ®¿öÅ© ºÎÇÏ¿¡ ÀÇÇÑ Áö¿¬½Ã°£¿¡ ¿µÇâÀ» ¹Þ±â ¶§¹®ÀÌ´Ù(¾ÕÀ¸·Î ³ª¿Ã ¿¹¿¡¼´Â Áö±Ý ¼³¸íÇÑ ºÎºÐÀº ³ªÅ¸³»Áö ¾Ê°Ú´Ù). Å°¿öµå('¿¹¾à¾î'¶ó°í ÇÕ´Ï´Ù. ¹Ì¸® ¿¹¾àµÈ °ÍÀÌ¶ó ¸¶À½´ë·Î »ç¿ëÇÒ ¼ö ¾ø´Â À̸§ÀÔ´Ï´Ù)´Â ´ë¹®ÀÚ·Î ÇÏ´ø, ¼Ò¹®ÀÚ·Î ÇÏ´ø »ó°ü¾ø´Ù. ¾Æ·¡ ¼¼ °³ÀÇ ¸í·ÉÀº ´Ù µ¿ÀÏÇÏ´Ù: mysql> SELECT VERSION(), CURRENT_DATE; mysql> SELECT version(), current_date; mysql> seLect vErSiOn(), current_DATE; ´Ù¸¥ ¿¹¸¦ Çϳª ´õ »ìÆì º¸ÀÚ. mysqlÀ» °£´ÜÇÑ °è»ê±â·Î »ç¿ëÇÑ ¿¹ÀÌ´Ù: mysql> SELECT SIN(PI()/4), (4+1)*5; +-------------------------+ | SIN(PI()/4) | (4+1)*5) | +-----------+-----------+ | 0.707107 | 25 | +------------+-----------+ Áö±Ý±îÁöÀÇ ¿¹¿¡¼ ¸í·É¾î´Â ºñ±³Àû ª¾Ò°í, ÇÑ Á٠¥¸®¿´´Ù. ÇÑÁÙ¿¡ ¿©·¯ ¸í·ÉÀ» ±â¼úÇÒ ¼ö ÀÖ´Ù. °¢ ¸í·ÉÀ» ¼¼¹ÌÄÝ·ÐÀ¸·Î ³¡³»±â¸¸ ÇÏ¸é µÈ´Ù: mysql> SELECT version(); SELECT now(); +------------+ | version() | +------------+ |3.22.20a-log| +------------+ +-----------------------+ | now() | +-----------------------+ | 1999-03-19 00:15:33 | +-----------------------+ ¸í·É¾î´Â ÇÑÁÙ¿¡ ¸ðµÎ ´Ù ±â¼úÇؾ߸¸ ÇÏ´Â °Ç ¾Æ´Ï´Ù. ±ä ¸í·ÉÀÎ °æ¿ì ¸î ÁÙ¿¡ °ÉÃıâ¼úÇÒ ¼ö ÀÖ´Ù. mysqlÀº ¼¼¹ÌÄÝ·ÐÀ» º¸°í ¾îµð¼ ¸í·ÉÀÌ ³¡³ª´Â Áö¸¦ ºÐ°£ÇÑ´Ù(mysqlÀº ÀÓÀÇÀÇ Æ÷¸ËÀ» °®´Â ÀÔ·ÂÀ» ¹Þ¾Æ µéÀδÙ: ÀÔ·Â ÁÙÀ» ¸ð¾Æ ¼¼¹ÌÄÝ·ÐÀ» º¼ ¶§±îÁö ½ÇÇàÇÑ´Ù). ¿©·¯ ÁÙÀ» °ÉÃÄ ¸í·ÉÀ» ÁØ ¿¹¸¦ º¸ÀÚ: mysql> SELECT -> user() -> , -> current_date; +-------------------+-------------+ | user() | current_date| +-------------------+-------------+ | joesmith@localhost| 1999-03-18 | +-------------------+-------------+ ¿©·¯ÁÙ ÀÔ·ÂÇÒ ¶§ ùÁÙÀ» ÀÔ·ÂÇÏ°í ¿£ÅÍÅ°¸¦ ÃÆÀ» ¶§ ÇÁ·ÒÇÁÆ®°¡ 'mysql>'¿¡¼ '->'·Î ¹Ù²ï °ÍÀ» ÁÖ¸ñÇ϶ó. ÀÌ°ÍÀº ¾ÆÁ÷ ¸í·ÉÀÌ ´Ù ¿Ï¼ºµÇÁö´Â ¾Ê¾ÒÀ¸¸ç, µû¶ó¼ ´õ ÀÔ·ÂÀ» ±â´Ù¸°´Ù¶ó°í mysqlÀÌ ¿©·¯ºÐ¿¡°Ô ¾Ë¸®´Â °ÍÀÌ´Ù. ÇÁ·ÒÇÁÆ®´Â ¿©·¯ºÐÀÇ Ä£ÀýÇÑ ¾È³»ÀÚ´Ù. ±ÍÇÑ Á¤º¸¸¦ ¿©·¯ºÐ¿¡°Ô ¾Ë·Á ÁØ´Ù. ÇÁ·ÒÇÁÆ®°¡ ¾Ë·ÁÁÖ´Â °ÍµéÀ» ÅëÇØ mysqlÀÌ ¹«¾ùÀ» ±â´Ù¸®°í ÀÖ´ÂÁö Ç×»ó ¾Ë ¼ö ÀÖÀ» °ÍÀÌ´Ù. ¸í·É¾î ÀÔ·Â µµÁß Ãë¼ÒÇÏ·Á¸é \c¸¦ ÃÄÁÖ¸é µÈ´Ù: mysql> SELECT -> user() -> \c mysql> ÇÁ·ÒÇÁÆ® º¯È¸¦ Àß º¸¶ó. \c¸¦ Ä£ÈÄ 'mysql>'·Î ¹Ù²î¾ú´Ù. »õ ¸í·É¾î¸¦ ¹Þ¾ÆµéÀÏ Áغñ°¡ µÇ¾ú´Ù´Â °ÍÀ» ¾Ë¸®´Â °ÍÀÌ´Ù. ´ÙÀ½ Ç¥´Â ¸¶ÁÖÄ¡°Ô µÉ ÇÁ·ÒÇÁÆ®µé°ú ±× Àǹ̸¦ ¼³¸íÇÑ °ÍÀÌ´Ù. ÇÁ·ÒÇÁÆ® ÀÇ¹Ì --------------------------------------------- mysql> »õ ¸í·ÉÀ» ¹Þ¾Æ µéÀÏ Áغñ°¡ µÇ¾úÀ½ ---------------------------------------------- -> ¸í·É¾î¸¦ ¿©·¯ ÁÙ¿¡ ±â¼úÇÒ ¶§ ´ÙÀ½ ÁÙÀ» ±â´Ù¸®°í ÀÖÀ½À» ÀÇ¹Ì ---------------------------------------------- '> ´ÙÀ½ÁÙ ÀÔ·ÂÀ» ³ªÅ¸³½´Ù. ÇöÀç '·Î ½ÃÀÛÇÏ´Â ¹®ÀÚ¿À» ¼öÁýÇÏ´Â ÁßÀ̶ó´Â °ÍÀ» ³ªÅ¸³¿ (¹®ÀÚ¿ ÀÔ·ÂÀ» ³¡³»·Á¸é ¹®ÀÚ¿À» ´Ù ÀÔ·Â ÇÑ ÈÄ '¸¦ ºÙ¿© ÁÙ°Í) ----------------------------------------------- "> '>¿Í °°´Ù. ´ÜÁö Â÷ÀÌ´Â ¹®ÀÚ¿À» '°¡ ¾Æ´Ï ¶ó " ·Î µÎ¸¥´Ù´Â Á¡ÀÌ´Ù. ----------------------------------------------- ¼¼¹ÌÄÝ·ÐÀ» ºÙÀÌ´Â °ÍÀ» Àؾî¹ö·Á ¿ì¿¬È÷ ȤÀº ½Ç¼ö·Î ¿©·¯ ÁÙ¿¡ °ÉÄ¡´Â ¸í·ÉÀ» ÀÔ·ÂÇÒ ¶§°¡ Á¾Á¾ ÀÖ´Ù. ÀÌ °æ¿ì ¹°·Ð mysqlÀº ÀÔ·ÂÀ» ´õ ±â´Ù¸°´Ù: mysql> SELECT user() -> ÀÌ·² ¶§´Â mysqlÀº ¼¼¹ÌÄÝ·ÐÀ» ±â´Ù¸®°í ÀÖ´Â °ÍÀÌ´Ù(¿©·¯ºÐÀº ¸í·ÉÀ» Á¦´ë·Î ¿ÏÀüÈ÷ ´Ù ÀÔ·ÂÇß´Ù°í »ý°¢ÇÏÁö¸¸ mysqlÀº ±×·¸Áö ¾Ê´Ù. ¼¼¹ÌÄÝ·ÐÀÌ ºüÁ³±â ¶§¹®ÀÌ´Ù). ÇÁ·ÒÇÁÆ®°¡ ¹Ù²ï °ÍÀ» ´«Ä¡ äÁö ¸øÇÑ´Ù¸é °á°ú¸¦ ±â´Ù¸®¸ç ÇÑÂü µ¿¾ÈÀÇ ½Ã°£À» ³¶ºñÇÒ ¼öµµ ÀÖ´Ù. ¼¼¹ÌÄÝ·ÐÀ» ÃÄ ÁÖ¾î ¸í·ÉÀ» ¿Ï¼ºÇÏ¸é ½ÇÇà°á°ú¸¦ º¼ ¼ö ÀÖÀ»°ÍÀÌ´Ù: mysql> SELECT user() -> ; +-------------------+ | user() | +-------------------+ | joesmith@localhost | +-------------------+ '>¿Í ">´Â ¹®ÀÚ¿À» ¸ðÀ¸´Â Áß¿¡ ³ªÅ¸³ª´Â ÇÁ·ÒÇÁÆ®ÀÌ´Ù. MySQL¿¡¼´Â ¹®ÀÚµéÀ» ' ³ª " ·Î µÑ·¯½Î¸é ¹®ÀÚ¿ÀÌ µÈ´Ù(¿¹¸¦ µé¸é 'hello', "goodbye"µîÀÌ´Ù). ¶ÇÇÑ ¿©·¯ ÁÙ¿¡ °ÉÃÄ ¹®ÀÚ¿À» ÀÔ·ÂÇÒ ¼öµµ ÀÖ´Ù.'> ³ª "> ÇÁ·ÒÇÁÆ®°¡ ³ªÅ¸³ª¸é ÀÌ°ÍÀº '³ª "·Î ½ÃÀÛÇÏ´Â ¹®ÀÚ¿À» Æ÷ÇÔÇÏ´Â ¸í·É¾î¸¦ ÃÄ ³Ö¾úÀ¸³ª ´Ý´Â ' ³ª " ¸¦ ¾ÆÁ÷ ÃÄ ³ÖÁö ¾Ê¾Ò´Ù´Â °ÍÀ» ÀǹÌÇÏ´Â °ÍÀÌ´Ù. ¿©·¯ ÁÙ¿¡ °ÉÃÄ ¹®ÀÚ¿À» ÀÔ·ÂÇÒ ¶§´Â »ó°ü¾ø´Ù. ÇÏÁö¸¸ ¹®ÀÚ¿À» ¿©·¯ ÁÙ¿¡ ÀÔ·ÂÇÏ°í ÀÚ ÇÏ´Â °æ¿ì°¡ ¾ó¸¶³ª µÉ±î? ±×´ÙÁö ¸¹Áö ¾Ê´Ù. ´ëºÎºÐÀÇ °æ¿ì, '> ³ª "> ÇÁ·ÒÇÁÆ®´Â ´Ý´Â ' ³ª " ¸¦ »©¸Ô¾ú´Ù°í ¾Ë·ÁÁÖ´Â ÀǹÌÀÏ °ÍÀÌ´Ù. ¿¹¸¦ µé¸é ´ÙÀ½°ú °°´Ù: mysql> SELECT * FROM my_table WHERE name = "Smith And age < 30; "> À§¿Í °°Àº SELECT ¹®À» ÀÔ·ÂÇÏ°í ¿£ÅÍÅ°¸¦ Ä¡°í °á°ú¸¦ ±â´Ù¸°´ÙÇصµ ¾Æ¹« °á°úµµ º¼ ¼ö ¾øÀ» °ÍÀÌ´Ù. "¿Ö ¾Æ¹« ¹ÝÀÀµµ ¾øÀ» °ÍÀϱî?"¶ó°í ÀÌ»óÇÏ°Ô »ý°¢ÇÏÁö ¸»°í "> ÇÁ·ÒÇÁÆ®°¡ ³ªÅ³»´Â Àǹ̸¦ »ý°¢ÇØ º¸ÀÚ. ¹®ÀÚ¿À» ´Ý´Â ÀÎ¿ë ºÎÈ£¸¦ »©¸Ô¾ú´Ù´Â °ÍÀ» ¾Ë¸®°í ÀÖ´Ù. »ç½Ç À§ÀÇ ¹®ÀåÀº À߸øÀÌ ÀÖ´Ù. "Smith ´ÙÀ½¿¡ "¸¦ »©¸ÔÀº °ÍÀÌ´Ù. ÀÚ, ¾î¶»°Ô ÇØ¾ß ÇÒ±î? °¡Àå °£´ÜÇÑ ¹æ¹ýÀº ¸í·ÉÀ» Ãë¼ÒÇÏ´Â °ÍÀÌ´Ù. ±×·¯³ª °£´ÜÈ÷ \c¸¦ Ä¥¼ö´Â ¾ø´Ù. ¿Ö³ÄÇϸé \cµµ "¸¦ ÀÔ·ÂÇϱâ Àü±îÁö´Â ¹®ÀÚ¿ÀÇ ÀϺηΠÃë±ÞÀ» ¹ÞÀ» °ÍÀ̱⠶§¹®ÀÌ´Ù. ´ë½Å "\c¸¦ ÀÔ·ÂÇÏ¸é µÈ´Ù: mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30; "> "\c mysql> ÇÁ·ÒÇÁÆ®°¡ mysql>·Î µÇµ¹·Á Á³´Ù. ¹°·Ð ÀÌ°ÍÀº "»õ ¸í·É¾î ½ÇÇà Áغñ ¿Ï·á"ÀÇ ¶æÀÌ´Ù. '>¿Í ">°¡ ÀǹÌÇÏ´Â ¹Ù¸¦ ±â¾ïÇÏ´Â °ÍÀº Áß¿äÇÏ´Ù. À߸øÇÏ¿© ´Ý´Â ÀÎ¿ë ºÎÈ£À» »©¸Ô¾úÀ» ¶§ °è¼Ó ÀÔ·ÂÇÏ´Â °ÍµéÀº ¸ðµÎ ¹«½ÃµÇ´Â µíÇÏ°Ô º¸À̱⠶§¹®ÀÌ´Ù(¿©±â¿¡´Â QUITµµ Æ÷ÇԵȴÙ). ÇöÀç ¸í·ÉÀ» Ãë¼ÒÇϱâ Àü¿¡ ´Ý´Â ÀοëºÎÈ£¸¦ ²À ½á¾ßÇÑ´Ù´Â °ÍÀ» ¸ð¸£¸é ÀÌ°ÍÀº ¸Å¿ì È¥µ¿½º·¯¿î ÀÏÀÏ °ÍÀÌ´Ù. 3. µ¥ÀÌÅÍ º£À̽º ¸¸µé°í »ç¿ëÇϱ⠸í·É¾î ÀÔ·Â ¹æ¹ýÀ» ¾Ë¾ÒÀ¸´Ï µ¥ÀÌÅÍ º£À̽º¸¦ ¸¸µé°í »ç¿ëÇØ º¼ ¶§°¡ µÇ¾ú´Ù. Áý¿¡¼ ¾Ö¿Ïµ¿¹°À» Å°¿î´Ù°í °¡Á¤ÇØ º¸ÀÚ. ¾Ö¿Ïµ¿¹° °¢°¢¿¡ ´ëÇؼ ¿©·¯ °¡Áö Á¤º¸¸¦ µÎ°í À¯ÁöÇÏ°í ½ÍÀ» °ÍÀÌ´Ù. µ¥ÀÌÅÍ º£À̽º¸¦ ¸¸µé°í ±× ¾È¿¡ Å×À̺íÀ» ¸¸µé¾î¼ ¿©±â¿¡ ¿øÇÏ´Â µ¥ÀÌÅ͸¦ ³Ö¾îµÎ¸é µÈ´Ù. ±×·¸°Ô Çϸé Å×ÀÌºí¿¡¼ ÀڷḦ °¡Á®¿Í¼ ¾Ö¿Ïµ¿¹°¿¡ ´ëÇÑ ¿©·¯ °¡Áö Á¤º¸µéÀ» ¾Ë¾Æ ³¾ ¼ö ÀÖ´Ù. ÀÌ Àý¿¡¼´Â ÀÌ·¯ÇÑ °ÍµéÀ» Æ÷ÇÔÇÏ¿© ´ÙÀ½°ú °°Àº »çÇ×µéÀ» ´Ù·ç¾î º»´Ù: ¡Ü µ¥ÀÌÅÍ º£À̽º ¸¸µé±â ¡Ü Å×ÀÌºí ¸¸µé±â ¡Ü Å×ÀÌºí¿¡ ÀÚ·á ³Ö±â ¡Ü Å×ÀÌºí¿¡¼ ÀÚ·á »© ³»±â ¡Ü ¿©·¯°³ÀÇ Å×ÀÌºí »ç¿ëÇϱ⠵¥ÀÌÅÍ º£À̽º À̸§À» menagerie('µ¿¹°¿ø'À̶ó´Â ¶æÀÌ´Ù)¶ó°í ÁþÀÚ. menagerie µ¥ÀÌÅÍ º£À̽º´Â ¸Å¿ì °£´ÜÇϳª ½ÇÁ¦ »ýÈ°¿¡¼µµ °£´ÜÇÑ µ¥ÀÌÅÍ º£À̽º¸¦ »ç¿ëÇÏ´Â °æ¿ì°¡ ÀÖ´Ù. ¿¹¸¦ µé¸é Áö±Ý ¸¸µé°íÀÚ ÇÏ´Â µ¥ÀÌÅÍ º£À̽º¸¦ °¡ÃàÀ» »çÀ°ÇÏ´Â ³óºÎ³ª ¾Ö¿Ïµ¿¹°ÀÇ Ä¡·á ±â·ÏÀ» ³²°ÜµÎ¾î¾ß ÇÏ´Â ¼öÀǻ翡 ÀÇÇØ »ç¿ëµÇ¾î Áú ¼ö ÀÖ´Ù. SHOW ¹®À» »ç¿ëÇÏ¿© ÇöÀç ¼¹ö°¡ À¯Áö ÁßÀÎ µ¥ÀÌÅÍ º£À̽º ¸ñ·ÏÀ» º¼ ¼ö ÀÖ´Ù: mysql> SHOW DATABASES; +---------+ | Database | +---------+ | mysql | | test | | tmp | +---------+ ½ÇÁ¦ ¸ñ·ÏÀº À§¿Í ´Ù¸¦ ¼ö ÀÖ´Ù. ÇÏÁö¸¸ mysql, test µ¥ÀÌÅͺ£À̽º´Â Ç×»ó º¼ ¼ö ÀÖÀ» °ÍÀÌ´Ù. mysql µ¥ÀÌÅͺ£À̽º´Â »ç¿ëÀÚ Á¢±Ù ±ÇÇÑ Á¤º¸¸¦ °®°í ÀÖ´Â Áß¿äÇÑ µ¥ÀÌÅͺ£À̽ºÀÌ´Ù. test´Â ¸» ±×´ë·Î ¿¬½ÀÇϱâ À§ÇØ ÀÖ´Â µ¥ÀÌÅͺ£À̽º´Ù. test µ¥ÀÌÅͺ£À̽º°¡ ÀÖ´Ù¸é ´ÙÀ½Ã³·³ Çؼ »ç¿ëÇÒ ¼ö ÀÖ´Ù: mysql> USE test Database changed QUITó·³ USE ¹®Àº ¼¼¹ÌÄÝ·ÐÀÌ ÇÊ¿äÇÏÁö ¾Ê´Ù´Â °ÍÀ» ±â¾ïÇÏÀÚ(¼¼¹ÌÄÝ·ÐÀ¸·Î ³¡³»µµ »ó°ü¾ø´Ù. ±×³É °£´ÜÇÏ°Ô ¸ðµç ¹®À» ¼¼¹ÌÄÝ·ÐÀ¸·Î ³¡³½´Ù°í ±â¾ïÇØ µÎ´Â °Íµµ ÁÁ´Ù). USE ¹®Àº ¶ÇÇÑ ÇÑ ÁÙ¿¡ ±â¼úÇØ¾ß ÇÑ´Ù´Â °Íµµ ¹Ýµå½Ã ±â¾ïÇÏÀÚ. test µ¥ÀÌÅͺ£À̽º¿¡ Á¢±ÙÇÒ ¼ö ÀÖÀ¸¸é ÀÌ°ÍÀ» »ç¿ëÇÒ ¼ö ÀÖ´Ù. ÇÏÁö¸¸ µ¿ÀÏÇÑ µ¥ÀÌÅͺ£À̽º(ÀÌ °æ¿ì¿£ test)¿¡ Á¢±ÙÇÒ ¼ö ÀÖ´Â »ç¶÷ÀÌ ¿©·¯ »ç¶÷À̶ó¸é ¿©·¯ºÐÀÌ ¸¸µé¾î ³õÀº ¾î¶² ÀÚ·á¶óµµ ´Ù¸¥ »ç¶÷¿¡ ÀÇÇØ Á¢±ÙÀÌ °¡´ÉÇÏ´Ù. ÀÌ°ÍÀº »èÁ¦ ¹× º¯°æµÉ ¼ÒÁö°¡ ÀÖ´Ù´Â ¸»ÀÌ´Ù. ±×·¡¼ MySQL °ü¸®ÀÚ¿¡°Ô ¿©·¯ºÐ¸¸ÀÇ µ¥ÀÌÅͺ£À̽º¸¦ »ç¿ëÇÒ ±ÇÇÑÀ» ´Þ¶ó°í ¿äûÇØ¾ß ÇÑ´Ù. ¿©±â¼´Â menagerie¶ó°í ÇÏÀÚ. °ü¸®ÀÚ´Â ´ÙÀ½°ú °°Àº ¸í·É¹®À» ½ÇÇàÇÒ ÇÊ¿ä°¡ ÀÖ´Ù: mysql> GRANT ALL ON menagerie.* TO your_mysql_name; your_mysql_nameÀº ¹°·Ð Çã¶ôÇØ ÁÙ MySQL °èÁ¤¸íÀ¸·Î ´ëÄ¡ÇØ¾ß ÇÑ´Ù. 3.1 µ¥ÀÌÅͺ£À̽º ¸¸µé°í ¼±ÅÃÇϱ⠰ü¸®ÀÚ°¡ Á¢±Ù ±ÇÇÑÀ» ¼³Á¤ÇÒ ¶§ µ¥ÀÌÅͺ£À̽º¸¦ ¸¸µé¾î ÁÖ¾ú´Ù¸é ±×°ÍÀ» ±×³É »ç¿ëÇÏ¸é µÈ´Ù. ±×·¸Áö ¾ÊÀ¸¸é ´ÙÀ½ ó·³ ÇÏ¿© ¼Õ¼ö ¿©·¯ºÐÀÌ ¸¸µé¾î ÁÖ¸é µÈ´Ù: mysql> CREATE DATABASE menagerie; À¯´Ð½º¿¡¼´Â µ¥ÀÌÅͺ£À̽º À̸§Àº ´ë¼Ò¹®ÀÚ¸¦ ±¸º°ÇÑ´Ù(SQL Å°¿öµå´Â ±×·¸Áö ¾Ê´Ù). µû¶ó¼ µ¥ÀÌÅͺ£À̽º À̸§À» Ç×»ó 'menagerie'·Î ÇؾßÁö Menagerie, MENAGERIE, meNaGerIE°°Àº °ÍÀº ¾ÈµÈ´Ù. Å×À̺í À̸§µµ ¸¶Âù°¡Áö·Î ´ë¼Ò¹®ÀÚ¸¦ ±¸ºÐÇÑ´Ù. µ¥ÀÌÅͺ£À̽º¸¦ ¸¸µç´Ù°í »ç¿ëÇÏ°Ú´Ù°í ¾Ë¸®´Â °ÍÀº ¾Æ´Ï´Ù. ¸í½ÃÀûÀ¸·Î »ç¿ëÇÏ°Ú´Ù°í ÇØ¾ß ÇÑ´Ù: mysql> USE menagerie Database changed µ¥ÀÌÅͺ£À̽º´Â Çѹø¸¸ ¸¸µé¸é µÇÁö¸¸ »ç¿ëÇÒ ¶§¸¶´Ù use ¹®À» ÀÌ¿ëÇÏ¿© »ç¿ëÇÒ µ¥ÀÌÅÍ º£À̽º¸¦ ¼±ÅÃÇØ¾ß ÇÑ´Ù. ´ç¿¬ÇÑ ³í¸®°¡ ¾Æ´Ò±î? ´Ù¸¥ ¹æ¹ýÀ¸·Î´Â mysqlÀ» ½ÃÀÛÇÒ ¶§ µ¥ÀÌÅͺ£À̽º À̸§À» ½á Á־ µÈ´Ù: shell> mysql -h host -u user -p menagerie Enter password: ******** ¿©±â¼ menagerie°¡ Æнº¿öµå´Â ¾Æ´Ï´Ù. È¥µ¿ÇÏÁö ¸¶¶ó. Æнº¿öµå¸¦ ¾²·Á¸é °ø¹é¾øÀÌ ¹Ù·Î -p µÚ¿¡ ºÙ¿© ½á ÁÖ¾î¾ß ÇÑ´Ù(ÇÏÁö¸¸ ÀÌ ¹æ¹ýÀº º¸¾È»ó ¹Ù¶÷Á÷ÇÑ ¹æ¹ýÀÌ Àý´ë ¾Æ´Ï´Ù. Æнº¿öµå°¡ ±ÛÀÚ±×´ë·Î ȸ鿡 º¸À̱⠶§¹®ÀÌ´Ù. ´©°¡ ¾î±ú ³Ê¸Ó·Î º¸°íÀÖ´Ù¸é ¾î¶»°Ô ÇÒ °ÍÀΰ¡? Æнº¿öµå°¡ ±ÛÀÚ±×´ë·Î ȸ鿡 ½áÁø´Ù´Â °ÍÀº Á¤¸» À§ÇèÇÏ´Ù. Çö¸íÇÑ °ü¸®ÀÚ ¹× »ç¿ëÀÚ¶ó¸é -p µÚ¿¡ Æнº¿öµå¸¦ Àû¾î ÁÖ´Â "Áþ"Àº ÇÏÁö ¾ÊÀ» °ÍÀÌ´Ù. MySQL °³¹ßÀÚµéÀº ¿Ö ÀÌ·¸°Ô ÇßÀ»±î?). Æнº¿öµå°¡ ¾Æ´Ï¶ó »ç¿ëÇÒ µ¥ÀÌÅͺ£À̽ºÀ̸§ÀÌ´Ù. 3.2 Å×ÀÌºí ¸¸µé±â µ¥ÀÌÅͺ£À̽º¸¦ ¸¸µå´Â °ÍÀº ½±´Ù. ¸¸µé°í ³ Á÷ÈÄ¿¡´Â ´ÙÀ½ ó·³ mysql> SHOW TABLES; Empty set (0.00 sec) µ¥ÀÌÅͺ£À̽º´Â ºñ¾î ÀÖ´Ù. ´ç¿¬ÇÏ´Ù. ÀÌÁ¦ ¸· ¸¸µé¾ú´Âµ¥ µé¾îÀÖ´Â °ÍÀÌ ÀÖÀ» ¸® ¾ø´Ù. SHOW TABLES; ¹®Àº ¼±ÅÃµÈ µ¥ÀÌÅͺ£À̽º¿¡ ÀÖ´Â Å×À̺íÀ» º¸ÀÌ´Â ¸í·ÉÀÌ´Ù. Á¤¸¶·Î Áß¿äÇÏ°í ¾î·Æ°Ô ´À²¸Áö´Â °ÍÀº µ¥ÀÌÅͺ£À̽º¸¦ ¾î¶»°Ô ¼³°èÇÒ °ÍÀΰ¡ÀÌ´Ù. ¾î¶² Å×À̺íÀÌ ÇÊ¿äÇÏ°í ÀÌ ¾È¿¡ ¹«½¼ ÀÚ·áµéÀ» ³Ö¾î¾ß ÇÒÁö¸¦ »ý°¢ÇØ¾ß ÇÑ´Ù. ¿©±â¼ÀÇ ¿¹¿¡¼´Â °¢ ¾Ö¿Ï µ¿¹°¸¶´Ù ÇÑ °³ÀÇ ·¹Äڵ带 µÎ¾î¾ß ÇÒ °ÍÀÌ´Ù. pet Å×À̺íÀ̶ó°í À̸§ ÁþÀÚ. °¢ Å×ÀÌºí¿¡´Â ¾Ö¿Ï µ¿¹°ÀÇ À̸§, ¼ÒÀ¯ÁÖ(½Ä±¸ À̸§ÀÌ µÉ °ÍÀÌ´Ù), Á¾, ¼º(¾ÏÄÆÀÎÁö ¼öÄÆÀÎÁö) µîµîÀÇ Á¤º¸¸¦ ÀÔ·ÂÇÏ°í ½ÍÀ» °ÍÀÌ´Ù. ³ªÀÌ´Â? ³ªÀ̵µ ÇÊ¿äÇÒ °Í °°Áö¸¸ ½Ã°£¿¡ µû¶ó º¯ÇÏ´Â °ÍÀÌ ³ªÀÌÀ̹ǷΠ³ªÀÌ¿¡ ´ëÇÑ Á¤º¸¸¦ ÀÚÁÖ °»½ÅÇØ ÁÖ¾î¾ß ÇÒ °ÍÀÌ´Ù. º¸´Ù ³ªÀº ¹æ¹ýÀ» °±¸ÇØ¾ß ÇÑ´Ù. ÀÌ·± »óȲÀ» ¹Ì¸® ¿°µÎ¿¡ µÎ°í ¼³°èÇØ¾ß ÇÑ´Ù´Â °ÍÀÌ µ¥ÀÌÅͺ£À̽º ¼³°è½Ã °Þ´Â ¾î·Á¿òÀÌ ¾Æ´Ò±î »ý°¢ÇÑ´Ù. ³ªÀÌ´Â ½Ã°£¿¡ µû¶ó º¯ÇϹǷΠÃâ»ýÀÏÀ» ±â·ÏÇصΰí ÇöÀç ³¯Â¥¿ÍÀÇ Â÷À̷κÎÅÍ °è»êÇϸé ÁÁÀ» °ÍÀÌ´Ù. MySQLÀº ¸î°¡Áö »ê¼ú ·çƾÀ» Á¦°øÇϹǷΠÀÌ°ÍÀº ¾î·Á¿î ÀÏÀÌ ¾Æ´Ï´Ù. ³ªÀÌ´ë½Å Ãâ»ýÀÏÀ» ±â·ÏÇØ µÎ´Â °ÍÀº ´ÙÀ½ µÎ°¡Áö ÀÕÁ¡ÀÌ ÀÖ´Ù: ¡Ü ´Ù°¡¿À´Â ¾Ö¿Ï µ¿¹°ÀÇ »ýÀÏÀ» ¹Ì¸® ¾Ë·ÁÁÖ´Â ÀÏ¿¡ »ç¿ëÇÒ ¼ö ÀÖ´Ù(µ¿¹°¿¡°Ô »ýÀÏÀ̶ó... ºñÇö½ÇÀûÀÎ °Í °°Áö¸¸, ÀÌ°ÍÀº ´Ù¸¥ °üÁ¡¿¡¼ »ý°¢ÇØ º¼ ¼ö ÀÖ´Ù. ¿©·¯ºÐÀÇ °í°´ÀÇ »ýÀÏÀº ¾î¶²°¡? ¾ðÁ¦ »ýÀÏ ÃàÇÏÄ«µå¸¦ º¸³»¾ß ÇÏ´Â Áö ¾Ë ÇÊ¿ä°¡ ÀÖÁö ¾ÊÀº°¡?). ¡Ü ¿À´Ã ³¯Â¥ ¸»°í ´Ù¸¥ ³¯Â¥¸¦ ±âÁØÀ¸·Îµµ ³ªÀ̸¦ °è»êÇÒ ¼ö ÀÖ´Ù. ¿¹¸¦ µé¾î »ç¸ÁÀÏÀ» ±â·ÏÇØ ³õÀ¸¸é ¾Ö¿Ï µ¿¹°ÀÇ ¼ö¸íÀ» ¾Ë ¼ö ÀÖÀ» °ÍÀÌ´Ù. ¾Ö¿Ï µ¿¹°¿¡ ´ëÇÑ Á¤º¸·Î¼ ´Ù¸¥ °Íµéµµ »ý°¢ÇÒ ¼ö ÀÖ°ÚÁö¸¸ ÀÌÁ¤µµ·Î ÇØ µÎÀÚ. ÃæºÐÇÏ´Ù. CREATE TABLE ¹®À¸·Î Å×ÀÌºí¿¡ µÑ ÀÚ·á ±¸Á¶¸¦ ¸í½ÃÇÒ ¼ö ÀÖ´Ù: mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(2), birth DATE, death DATE); CREATE TABLE ´ÙÀ½¿¡ Å×À̺í À̸§À» ½á ÁÖ°í °ýÈ£ ¾È¿¡ ¿ÀÇ À̸§°ú ±× ¿ÀÇ ÀÚ·áÇü À» ÇѦÀ¸·Î ÇÏ¿© ½°Ç¥·Î ±¸ºÐÇÏ¿© ¿°ÅÇØ ÁÖ¸é µÈ´Ù. name, owner, species, sex, birthµîÀÌ ¿ÀÇ À̸§À̸ç, VARCHAR(20), CHAR(2), DATE°¡ ÀÚ·áÇüÀÌ´Ù. ÀÚ·áÇüÀ̶õ ¸» ±×´ë·Î ÀÚ·áÀÇ ÇüÅÂÀÌ´Ù. ÀÚ·á´Â ¹®ÀÚ¿ÀÏ ¼ö ÀÖ°í, ³¯Â¥ ÀÏ ¼ö ÀÖ°í, ¼ö Àϼö ÀÖ´Ù. ´ÙÀ½ Ç¥¿Í °°Àº Å×À̺íÀÌ ¸¸µé¾î Áø´Ù: pet TABLE ¿ 1¿ 2¿ 3¿ 4¿ 5¿ 6¿ ¿À̸§ name owner species sex birth death ¾î¶»°Ô ÀڷḦ ÀÔ·ÂÇÏ´À³Ä¿¡ µû¶ó ´Ù¸£°ÚÁö¸¸ Áøµ¾°³ "¿ë°¨ÀÌ"ÀÇ °æ¿ì ´ÙÀ½Ã³·³ µÉ ¼ö ÀÖ´Ù. name owner species sex birth death ------------------------------------------- ¿ë°¨ÀÌ È«±æµ¿ °³ ¼öÄÆ 1998-3-4 NULL VARCHAR´Â ±æÀÌ°¡ º¯ÇÏ´Â ¹®ÀÚ¿¿¡ »ç¿ëÇÑ´Ù. À̸§, ¼ÒÀ¯ÁÖ, Á¾Àº ±æÀÌ°¡ °íÁ¤ÀûÀÌÁö ¾ÊÀº ¹®ÀÚ¿À» ±× ÀÚ·áÇüÀ¸·Î ÇÒ ¶§ Àû´çÇÒ °ÍÀÌ´Ù. VARCHAR ÇüÀÇ ¿µé¿¡ ´ëÇØ, ±æÀÌ´Â ¹Ýµå½Ã ¸ðµÎ °°Àº ÇÊ¿äµµ ¾ø°í 20À¸·Î °íÁ¤µÉ ÇÊ¿äµµ ¾ø´Ù. 1¿¡¼ 255»çÀÌÀÇ ±æÀ̸¦ °¡Áú ¼ö ÀÖ´Ù. Àû´çÇÏ°Ô Àâ¾Æ ÁÖ¸éµÈ´Ù(³ªÁß¿¡ ALTER TABLE ¹®À¸·Î Á¶Á¤ÇÒ ¼öµµ ÀÖ´Ù). Å×À̺íÀ» ¸¸µé¾úÀ¸´Ï µ¥ÀÌÅͺ£À̽º³» Å×ÀÌºí ¸ñ·Ï¿¡ Ãß°¡µÇ¾ú´Â°¡ È®ÀÎÇÏÀÚ: mysql> SHOW TABLES; +--------------------+ |Tables in menagerie | +--------------------+ | pet | +--------------------+ Å×À̺íÀÌ ¸í½ÃÇÑ´ë·Î ¸¸µé¾ú´ÂÁö È®ÀÎÇϱâ À§Çؼ´Â DESCRIBE ¹®À» »ç¿ëÇÑ´Ù: mysql> DESCRIBE pet; +---------+-------------+-----+-----+---------+------+ | Field | Type | Null| Key | Default |Extra | +---------+-------------+-----+-----+---------+------+ | name | varchar(20) | YES | | NULL | | +---------+-------------+-----+-----+---------+------+ | owner | varchar(20) | YES | | NULL | | +---------+-------------+-----+-----+---------+------+ | species | varchar(20) | YES | | NULL | | +---------+-------------+-----+-----+---------+------+ | sex | char(2) | YES | | NULL | | +---------+-------------+-----+-----+---------+------+ | birth | date | YES | | NULL | | +---------+-------------+-----+-----+---------+------+ | death | date | YES | | NULL | | +---------+-------------+-----+-----+---------+------+ Filed ºÎºÐ°ú Type ºÎºÐÀ» º¸°í ¿ÀÇ À̸§°ú ÀÚ·áÇüÀ» È®ÀÎÇÏÀÚ. DESCRIBE´Â ¾ðÁ¦µçÁö »ç¿ëÇÒ ¼ö ÀÖ´Ù. Å×ÀÌºí ³×ÀÇ ¿ÀÇ À̸§ ¹× ÀÚ·áÇüÀ» ÀؾúÀ» ¶§ »ç¿ëÇϸé À¯¿ëÇÏ´Ù. 3.3 Å×ÀÌºí¿¡ ÀڷḦ ³Ö¾î º¸ÀÚ. Å×À̺íÀ» ¸¸µç ÈÄ¿¡´Â Å×ÀÌºí¿¡ ÀڷḦ ³Ö¾î¾ß ÇÑ´Ù. LOAD DATA ȤÀº insert ¹®À» »ç¿ëÇÏ¸é µÈ´Ù. ¾Ö¿Ï µ¿¹° ÀÚ·á°¡ ´ÙÀ½°ú °°´Ù°í °¡Á¤ÇÏÀÚ(MySQLÀº YYYY-MM-DD Çü½ÄÀÇ ³¯Â¥ Æ÷¸ËÀ» ¿ä±¸ÇÑ´Ù). name owner species sex birth death ------------------------------------------------------------- Fluffy Harold cat f 1993-02-04 Claws Gwen cat m 1994-03-17 Buffy Harold dog f 1989-05-13 Fang Benny dog m 1990-08-27 Bowser Dianne dog m 1998-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 Whistler Gwen bird 1997-12-09 Slim Benny snake m 1996-04-29 ¿©·¯ºÐÀº ºó Å×ÀÌºí¿¡¼ ½ÃÀÛÇϹǷΠ¹Ì¸® ÆÄÀÏ¿¡ °¢ µ¿¹°¿¡ ´ëÇÑ ÀڷḦ Àû¾î µÎ°í ÆÄÀÏ¿¡¼ ÀÐ¾î¼ Å×À̺íÀ» ä¿ì¸é ÁÁÀ» °ÍÀÌ´Ù. pet.txt¶ó´Â ÆÄÀÏ(ÆÄÀÏÀ̸§Àº ¾Æ¹«°ÍÀÌµç »ó°ü¾ø´Ù)¿¡ ÇÑ ÁÙ¿¡ ÇϳªÀÇ ·¹Äڵ带 ±â·ÏÇÏ¸é µÈ´Ù. ´ÙÀ½ ó·³: # cat pet.txt Fluffy Harold cat f 1993-02-04 -ÀÌÈÄ »ý·«- ¿ÀÇ °ªµéÀº ÅÇÅ° Çϳª·Î ±¸ºÐÇϸç CREATE TABLE ¹®¿¡ ¸í½ÃÇÑ ¼ø¼´ë·Î °¢ ¿ÀÇ °ªµéÀ» ¿°ÅÇØ¾ß ÇÑ´Ù. »ý·«Çصµ µÇ´Â °ª(À§¿¡¼ Á×Àº ³¯Â¥¿Í ¼º)¿¡ ´ëÇؼ´Â NULL°ªÀ» »ç¿ëÇÒ ¼ö ÀÖ´Ù. ÅؽºÆ® ÆÄÀÏ¿¡¼ NULL°ªÀ» ³ªÅ¸³»±â À§Çؼ´Â \N À̶ó°í ½áÁÖ¸é µÈ´Ù. ¿¹¸¦ µé¾î WhistlerÀÇ ¿¹´Â ´ÙÀ½°ú °°À» °ÍÀÌ´Ù. Whistler Gwen bird \N 1997-12-09 \N pet.txt ÆÄÀÏÀ» ·ÎµåÇϱâ À§Çؼ´Â ´ÙÀ½Ã³·³ LOAD DATA ¹®À» »ç¿ëÇÑ´Ù: mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet; »ç¿ëÇü½ÄÀº ´ÙÀ½°ú °°´Ù: LOAD DATA LOCAL INFILE "ÆÄÀÏÀ̸§" INTO TABLE Å×À̺íÀ̸§; Çѹø¿¡ ÇÑ °³ÀÇ ·¹Äڵ带 Ãß°¡ÇÏ°íÀÚ ÇÒ ¶§°¡ ÀÖÀ» °ÍÀÌ´Ù. CREATE TABLE ¹®À» »ç¿ëÇÏ¿´À»¶§ ¿°ÅÇÑ ¼ø¼´ë·Î °¢ ¿ÀÇ °ªÀ» Àû¾î ÁÖ¸é µÈ´Ù. ¹°·Ð ¸Â´Â ÀÚ·áÇüÀ¸·Î ¸»ÀÌ´Ù: mysql> insert into pet -> values('Puffball', 'Diane', 'hamster', 'f', '1999-03-30', NULL); ¹®ÀÚ¿ ¹× ³¯Â¥¸¦ ÀÛÀº µû¿ÈÇ¥ ' ·Î ÀοëÇÏ¿´´Ù. À§¿¡¼ ¾ð±ÞÇÑ NULL°ªµµ ÀÔ·ÂÇÒ ¼ö ÀÖ´Ù(\NÀ̶ó°í ÀûÀ¸¸é ¾ÈµÈ´Ù). 3.4 Å×À̺í·ÎºÎÅÍ Á¤º¸¸¦ °Ë»öÇØ º¸ÀÚ. SELECT ¹®À» »ç¿ëÇÏ¸é µÈ´Ù. ÀÏ¹Ý Çü½ÄÀº ´ÙÀ½°ú °°´Ù: SELECT <°Ë»ö´ë»ó> FROM <Å×À̺í> WHERE <°Ë»öÁ¶°Ç> <°Ë»ö´ë»ó>Àº ¹«¾ùÀ» º¸°í ½ÍÀº°¡¸¦ ¾Ë¸®´Â °ÍÀÌ´Ù. ¿©·¯ ¿À» ½°Ç¥·Î ±¸ºÐÇÏ¿© ÀûÀ» ¼ö ÀÖÀ¸¸ç ¸ðµç ¿À» ÀǹÌÇÏ´Â *¸¦ ¾µ ¼öµµ ÀÖ´Ù. WHERE ºÎºÐÀº »ý·«ÇÒ ¼ö ÀÖ´Ù. WHERE ¹®À» ¾µ ¶§´Â °Ë»öÁ¶°ÇÀ» ½á ÁØ´Ù. °Ë»öÁ¶°ÇÀ» ¸¸Á·ÇÏÁö ¾Ê´Â ÇàÀº °Ë»ö´ë»ó¿¡¼ Á¦¿ÜµÈ´Ù. 3.4.1 ¸ðµç µ¥ÀÌÅ͸¦ °Ë»öÇÏÀÚ. °¡Àå °£´ÜÇÑ SELECT ¹®ÀÇ ÇüÅ·Π´ÙÀ½ ó·³ »ç¿ëÇÒ ¼ö ÀÖ´Ù: mysql> SELECT * FROM pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------+---------+------+------------+------------+ ÀÌ·± ½ÄÀ¸·Î SELECT¹®À» »ç¿ëÇÏ´Â °ÍÀº Å×À̺íÀÇ Àüü Á¤º¸¸¦ º¸°íÀÚ ÇÒ ¶§ À¯¿ëÇÏ´Ù. ¹æ±Ý ¸· Ãʱ⠵¥ÀÌÅÍ ¹¶Ä¡¸¦ ¿Ã·ÈÀ» ¶§ Á¦´ë·Î ¿Ã·ÁÁ³´ÂÁö È®ÀÎÄÚÀÚ »ç¿ëÇÒ ¼ö ÀÖ´Ù. »ç¶÷ »ç´Â ÀÏÀÌ ±×·¸µí, ¹æ±Ý º» °á°ú¿¡´Â À߸øµÈ °ÍÀÌ ÀÖ´Ù: BowerÀÇ Ãâ»ýÀÏÀÚ°¡ »ç¸ÁÀÏÀÚº¸´Ù ´Ê´Ù. Á×Àº µÚ¿¡ ž´Ù?! È®ÀÎÇØ º¸´Ï birth´Â 1989-08-31ÀÌ µÇ¾î¾ß ÇÔÀ» ¾Ë¼ö ÀÖ¾ú´Ù°í Çϸé À̸¦ ¾î¶»°Ô °íÄ¥±î? µÎ°¡Áö ¹æ¹ýÀ» »ç¿ëÇÒ ¼ö ÀÖ´Ù: ¡Ü ÆÄÀÏ pet.txt¸¦ ÆíÁýÇÏ¿© ¼öÁ¤ÇÑ´Ù. Å×À̺íÀ» ºñ¿îÈÄ pet.txt¿¡¼ ´Ù½Ã ÀÐ¾î µéÀδÙ: mysql> DELETE FROM pet; mysql> LOAD DATA LOCAL infile "pet.txt" into TABLE pet; ÇÏÁö¸¸ ÀÌ·¸°Ô Çϸé 3.3Àý¿¡¼ °³º°ÀûÀ¸·Î insert¹®À» ÀÌ¿ëÇÏ¿© ÀÔ·ÂÇÑ Puffball¿¡ ´ëÇؼ ´Ù½Ã ÀÔ·ÂÇØ¾ß ÇÑ´Ù. ´õ °£´ÜÇÏ°í ¹Ù¶÷Á÷ÇÑ ¹æ¹ýÀº? ¡Ü À߸øµÈ °÷¸¸ ¼öÁ¤ÇÑ´Ù. UPDATE ¹®À» »ç¿ëÇÑ´Ù: mysql> UPDATE pet SET birth="1989-08-31" WHERE name="Bowser"; À§¿¡¼ º¼ ¼ö ÀÖµíÀÌ, Àüü Å×ÀÌºí ³»¿ëÀ» º¸´Â °ÍÀº ½±´Ù. ±×·¯³ª º¸Åë ÀÌ·¸°Ô ÇÏÁö´Â ¾Ê´Â´Ù. Å×À̺í Å©±â°¡ Ä¿Áö¸é ¾î¶»°Ô ÇÒ °ÍÀΰ¡? ¾î¶² ÀÚ·áµéÀ» °Ë»öÇÒ ¶§ ±× ¸¹Àº °ÍÀ» ÀÏÀÏÀÌ ´Ù º¼°ÍÀΰ¡? ´ë½Å Ưº°ÇÑ Á¶°ÇÀ» ¸¸Á·ÇÏ´Â ÀÚ·áµé¸¸ »Ì¾Æ¼ º¸±æ ¿øÇÒ °ÍÀÌ´Ù. 3.4.2 ÁÖ¾îÁø Á¶°Ç¿¡ ¸Â´Â ƯÁ¤ ÇุÀ» °Ë»öÇØ º¸ÀÚ. ¿©·¯ºÐÀº Å×ÀÌºí¿¡¼ Ưº°ÇÑ Çàµé¸¸ »Ì¾Æ³¾ ¼ö ÀÖ´Ù. ¿¹¸¦ µé¾î BowerÀÇ »ýÀÏÀÌ Á¤¸»·Î ¹Ù²î¾ú´Â °¡ È®ÀÎÇϱâ À§ÇØ BowerÀÇ ·¹Äڵ常 »Ì¾Æ³¾ ¼ö ÀÖ´Ù: mysql> SELECT * FROM pet WHERE name = "Bower"; +--------+-------+---------+------+------------+-----------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+-----------+ | Bowser | Diane | dog | m |1989-08-31 |1995-07-29 | +--------+-------+---------+------+------------+-----------+ birth ¿ÀÇ °ªÀÌ 1998³âµµ°¡ ¾Æ´Ñ 1989³âÀ¸·Î ¿Ã¹Ù¸£°Ô ¼öÁ¤µÊÀ» È®ÀÎÇÒ ¼ö ÀÖ´Ù. ¹®ÀÚ¿ ºñ±³´Â ´ë¼Ò¹®ÀÚ¸¦ ¹«½ÃÇÏ´Â ºñ±³´Ù. µû¶ó¼ "bowser", "BOWSER" µîÀº °°Àº ¹®ÀÚ¿À» ÀǹÌÇÑ´Ù(À§¿¡¼´Â "Bowser"¸¦ »ç¿ëÇß´Ù). ¾î¶² ¿¿¡´ëÇؼµµ Á¶°ÇÀ» ¸í½ÃÇØ ÁÙ¼ö°¡ ÀÖ´Ù. ¿¹¸¦ µé¾î 1998³â ÀÌÈÄ¿¡ ÅÂ¾î³ µ¿¹°À» ¾Ë°í ½Í´Ù¸é birth ¿À» ´ë»óÀ¸·Î °Ë»çÇÏ¸é µÈ´Ù: mysql> SELECT * FROM pet WHERE birth >= "1998-1-1" +----------+-------+---------+------+-----------+------+ | name | owner | species | sex | birth |death | +----------+-------+---------+------+-----------+------+ | Chirp y | Gwen | bird | f |1998-09-11 | NULL | | Puffball | Diane | hamster | f |1999-03-30 | NULL | +----------+-------+---------+------+-----------+------+ Á¶°ÇÀ» Á¶ÇÕÇÒ ¼ö µµ ÀÖ´Ù: mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Buffy | Harold | dog | f |1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ À§ÀÇ ¿¹´Â °³ÀÌ¸é¼ ¼ýÄÆÀÎ µ¿¹°À» °Ë»öÇÏ´Â °ÍÀÌ´Ù. À§¿¡¼´Â AND¸¦ »ç¿ëÇÏ¿´Áö¸¸ OR¸¦ »ç¿ëÇÒ ¼öµµ ÀÖ´Ù: mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird"; +----------+-------+---------+------+-----------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+-----------+-------+ | Chirpy | Gwen | bird | f |1998-09-11 | NULL | | Whistler | Gwen | bird |NULL |1997-12-09 | NULL | | Slim | Benny | snake | m |1996-04-29 | NULL | +----------+-------+---------+------+-----------+-------+ AND¿Í OR¸¦ ¼¯¾î¼ »ç¿ëÇÒ ¼ö ÀÖ´Ù. ÀÌ·¸°Ô ÇÒ ¶§´Â ±×·ìÁö¾îÁö´Â Á¶°ÇµéÀ» °ýÈ£·Î ¹´Â °ÍÀÌ ÁÁ´Ù: mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m") -> OR (species = "dog" AND sex = "f"); +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 3.4.3 ƯÁ¤ÇÑ ¿ ¼±ÅÃÇϱâ Å×ÀÌºí¿¡¼ ÇÑÇà Àüü¸¦ º¸±â º¸´Ù´Â "°ü½É °Å¸®" ¿µé¸¸ º¸°í ½Í´Ù¸é º¸°íÀÚ ÇÏ´Â ¿ À̸§À» ´ÙÀ½Ã³·³ »ç¿ëÇÏ¸é µÈ´Ù(¾Æ·¡ ¿¹´Â name, birth¿À» º¸°í ½ÍÀº °æ¿ìÀÌ´Ù): mysql> SELECT name, birth FROM pet; +----------+------------+ | name | birth | +----------+------------+ | Fluffy | 1993-02-04 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | | Fang | 1990-08-27 | | Bowser | 1989-08-31 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Puffball | 1999-03-30 | +----------+------------+ ¼ÒÀ¯ÁÖ¸¸ º¸°íÀÚ ÇÒ ¶§: mysql> SELECT owner FROM pet; +--------+ | owner | +--------+ | Harold | | Gwen | | Harold | | Benny | | Diane | | Gwen | | Gwen | | Benny | | Diane | +--------+ ÇÏÁö¸¸ Áߺ¹µÈ ÇàÀÌ ÀÖ´Ù. Áߺ¹µÈ ÇàÀ» ¾ø¾Ö º¸ÀÚ: mysql> SELECT distinct owner FROM pet; +--------+ | owner | +--------+ | Benny | | Diane | | Gwen | | Harold | +--------+ distinct Å°¿öµå¸¦ »ç¿ëÇÏ¸é µÈ´Ù. WHERE ÀýÀ» ÀÌ¿ëÇÏ¿© ÇàÀÇ ¼±Åðú ¿ÀÇ ¼±ÅÃÀ» Á¶ÇÕÇÒ ¼ö ÀÖ´Ù. ¿¹¸¦ µé¾î °³¿Í °í¾çÀÌ¿¡ ´ëÇؼ¸¸ À̸§, Á¾, »ýÀÏÀ» ¾Ë°íÀÚ ÇÒ ¶§ ´ÙÀ½ ó·³ SELECT¹®À» »ç¿ëÇÒ ¼ö ÀÖ´Ù: mysql> SELECT name, species, birth FROM pet -> WHERE species = "dog" OR species = "cat"; +--------+---------+------------+ | name | species | birth | +--------+---------+------------+ | Fluffy | cat |1993-02-04 | | Claws | cat |1994-03-17 | | Buffy | dog |1989-05-13 | | Fang | dog |1990-08-27 | | Bowser | dog |1989-08-31 | +--------+---------+------------+ 3.4.4 Çà Á¤¿Çϱâ Áö±Ý±îÁö º¸ÀÎ ¿¹¿¡¼ °á°ú°¡ ÀüÇô Á¤·ÄµÇ¾î ÀÖÁö ¾Ê¾ÒÀ½À» ¾Ë ¼ö ÀÖÀ» °ÍÀÌ´Ù. °á°ú°¡ Á¤·ÄµÈ´Ù¸é ¿øÇÏ´Â ÀڷḦ ÈξÀ ½±°Ô ¾Ë¾Æ º¼ ¼ö ÀÖ´Ù. Á¤·ÄÇϱâ À§Çؼ´Â ORDER BY ÀýÀ» »ç¿ëÇÏ¸é µÈ´Ù. ¾Æ·¡´Â »ýÀÏÀ» ±âÁØÀ¸·Î Á¤·ÄÇÑ °ÍÀÌ´Ù. mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | Buffy | 1989-05-13 | | Bowser | 1989-08-31 | | Fang | 1990-08-27 | | Fluffy | 1993-02-04 | | Claws | 1994-03-17 | | Slim | 1996-04-29 | | Whistler | 1997-12-09 | | Chirpy | 1998-09-11 | | Puffball | 1999-03-30 | +----------+------------+ ¿ª¼øÀ¸·Î Á¤·ÄÇÏ·Á¸Õ desc Å°¿öµå¸¦ ¿ À̸§ µÚ¿¡ Àû¾îÁØ´Ù: mysql> SELECT name, birth FROM pet ORDER BY birth desc; +----------+------------+ | name | birth | +----------+------------+ | Puffball | 1999-03-30 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Claws | 1994-03-17 | | Fluffy | 1993-02-04 | | Fang | 1990-08-27 | | Bowser | 1989-08-31 | | Buffy | 1989-05-13 | +----------+------------+ ÇϳªÀÇ ¿¿¡ ´ëÇؼ¸¸ÀÌ ¾Æ´Ï¶ó ¿©·¯ ¿¿¡ ´ëÇؼµµ Á¤·ÄÇÒ ¼ö ÀÖ´Ù. ¿¹¸¦ µé¾î ¸ÕÀú µ¿¹° À̸§À¸·Î Á¤·ÄÇÏ°í °¢ µ¿¹°¿¡ ´ëÇØ »ýÀÏ·Î Á¤·ÄÇ쵂 °¡Àå ¾î¸° °Í(°¡Àå ´Ê°Ô ÅÂ¾î³ °Í; ¿ª¼ø Á¤·Ä)ÀÌ ¸ÕÀú ¿À°Ô ÇÏ·Á¸é ´ÙÀ½Ã³·³ ÇÑ´Ù: mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC; +----------+---------+-----------+ | name | species | birth | +----------+---------+-----------+ | Chirpy | bird |1998-09-11 | | Whistler | bird |1997-12-09 | | Claws | cat |1994-03-17 | | Fluffy | cat |1993-02-04 | | Fang | dog |1990-08-27 | | Bowser | dog |1989-08-31 | | Buffy | dog |1989-05-13 | | Puffball | hamster |1999-03-30 | | Slim | snake |1996-04-29 | +----------+---------+-----------+ DESC Å°¿öµå´Â ¹Ù·Î ±× ¾Õ¿¡ ¿ À̸§(birth)¿¡¸¸ Àû¿ëµÈ´Ù´Â °ÍÀ» ÁÖÀÇÇ϶ó. species´Â ¿©ÀüÈ÷ ¿À¸§Â÷¼øÀ¸·Î Á¤·ÄµÈ´Ù. 3.4.5 ³¯Â¥ °è»ê MySQLÀº ³¯Â¥¸¦ ´Ù·ç´Â ¸î°¡Áö ÇÔ¼ö¸¦ Á¦°øÇØ ÁØ´Ù. ¾Ö¿Ï µ¿¹°ÀÇ ³ªÀÌ°¡ ¾ó¸¶³ª µÇ´Â Áö °è»êÇÏ·Á¸é ¿À´Ã ³¯Â¥¿Í Ãâ»ý½Ã ³¯Â¥¸¦ ±¸ÇÏ°í, µÎ ³¯Â¥¸¦ Àϼö·Î ȯ»êÇÑ ÈÄ, ±× Â÷¸¦ ¿¬°£ Àϼö Áï 365ÀÏ·Î ³ª´©¾î ÁÖ¸é µÉ °ÍÀÌ´Ù: mysql> SELECT name, (to_days(now())-to_days(birth))/365 FROM pet; +----------+-------------------------------------------+ | name | (TO_DAYS(NOW())-TO_DAYS(birth))/365 | +----------+-------------------------------------------+ | Fluffy | 6.15 | | Claws | 5.04 | | Buffy | 9.88 | | Fang | 8.59 | | Bowser | 9.58 | | Chirpy | 0.55 | | Whistler | 1.30 | | Slim | 2.92 | | Puffball | 0.00 | +----------+-------------------------------------------+ ¿©±â¼ µÎ°¡Áö »çÇ×À» °³¼±ÇØ º¸ÀÚ. °á°ú°¡ À̸§ ȤÀº ³ªÀÌ ¼øÀ¸·Î Á¤·ÄµÇ¾úÀ¸¸é ÁÁ°Ú°í, ³ªÀÌ¿¡ ÇØ´çÇÏ´Â ¶óº§¸íÀ» Ç¥Çö½Ä ±×´ë·Î ¾²´Â °Í º¸´Ù´Â "age"°°Àº °ÍÀ¸·Î ÇÏ´Â °ÍÀÌ ÁÁÀ» °ÍÀÌ´Ù: mysql> SELECT name, (to_days(now())-to_days(birth))/365 as age -> FROM pet ORDER BY name; ³ªÀ̼øÀ¸·Î Á¤·ÄÇÏ·Á¸é ORDER BY name ´ë½Å ORDER BY age·Î ½á ÁÖ¸é µÈ´Ù. »ç¸Á½Ã ³ªÀ̵µ ºñ½ÁÇÑ ¹æ¹ýÀ¸·Î ¾Ë¾Æ ³¾ ¼ö ÀÖ´Ù: mysql> SELECT name, (to_days(death)-to_days(birth))/365 as age -> FROM pet WHERE death is not null ORDER BY age; now()´ë½Å death¸¦ »ç¿ëÇÏ¸é µÈ´Ù. ¿©±â¼ ¾ÆÁ÷ Á×Áö ¾ÊÀº µ¿¹°ÀÇ ¼ö¸íÀ» Á¶»çÇÑ´Ù´Â °ÍÀº ¹«ÀǹÌÇϱ⠶§¹®¿¡ death Çʵ尡 nullÀÌ ¾Æ´Ñ °æ¿ì¸¦ Á¶°ÇÀ¸·Î ÇØ ÁÖ¾úÀ½À» ÁÖÀÇÇÏÀÚ. Á¶½ÉÇÒ °ÍÀº death is not nulló·³ Á¶°ÇÀ» ÁÖ¾î¾ß ÇÑ´Ù. death != null ó·³ Á־ ¾ÈµÈ´Ù. null°ª¿¡ ºñ±³ ¿¬»êÀÚ¸¦ Àû¿ëÇÒ ¼ö ¾ø´Ù. ³ªÁß¿¡ ÀÌ ¹®Á¦´Â ´Ù½Ã ´Ù·ê °ÍÀÌ´Ù. ´ÙÀ½ ´Þ¿¡ »ýÀÏÀÎ µ¿¹°À» ¾Ë·Á¸é ¾î¶»°Ô ÇØ¾ß ÇÒ±î? ÀÌ·¯ÇÑ ¹®Á¦¸¦ À§ÇØ MySQLÀº ³¯Â¥¿¡¼ ¿¬µµ³ª, ´ÞÀ» °è»êÇÏ´Â ÇÔ¼ö¸¦ Á¦°øÇÑ´Ù: year(), month, day().month() ¿¹¸¦ ÅëÇØ ¾Ë¾Æ º¸ÀÚ: mysql> SELECT name, birth, month(birth) FROM pet; +----------+------------+--------------+ | name | birth | MONTH(birth) | +----------+------------+--------------+ | Fluffy | 1993-02-04 | 2 | | Claws | 1994-03-17 | 3 | | Buffy | 1989-05-13 | 5 | | Fang | 1990-08-27 | 8 | | Bowser | 1989-08-31 | 8 | | Chirpy | 1998-09-11 | 9 | | Whistler | 1997-12-09 | 12 | | Slim | 1996-04-29 | 4 | | Puffball | 1999-03-30 | 3 | +----------+------------+--------------+ month´Â ´Þ¿¡ ÇØ´çÇÏ´Â ¼ö¸¦ ¹ÝȯÇØ ÁÖ¸ç, ¹°·Ð ±× ¹üÀ§´Â 1¿¡¼ 12±îÁöÀÌ´Ù. ´ÙÀ½´ÞÀ» ³ªÅ³»±â À§Çؼ´Â 1À» ´õÇÑ ´ÞÀ» ¸í½ÃÇØÁÖ¸é µÈ´Ù: mysql> SELECT name, birth FROM pet WHERE month(birth) = 10; ±×·±µ¥ ¹®Á¦°¡ ÀÖ´Ù. 12¿ùÀÎ °æ¿ì 13À» ¸í½ÃÇØ ÁÖ¾î¾ß Çϳª? 13¿ùÀ̶õ ¾ø´Ù. ÇöÀç ´ÞÀÌ ¸î¿ùÀÌ´øÁö »ó°ü¾øµµ·Ï »õ·Î¿î Á¶°Ç½ÄÀ» »ý°¢ÇØ ³»¾ß ÇÑ´Ù. ¿©±â¿¡ µÎ°¡Áö¸¦ ¼Ò°³ÇÑ´Ù: ¡Ü month(date_add(now(), interval 1 month)); now()´Â ÇöÀç ³¯Â¥ ¹× ½Ã°£À» ¹ÝȯÇØ ÁØ´Ù. ¿©±â¿¡ 1´ÞÀ̶ó´Â ±â°£À» ´õ ÇØÁÖ°í ´Þ·Î ¹Ù²Ù¸é ÇØ°áµÈ´Ù. ¡Ü mod(month(now()), 12) + 1; mod´Â ¾î¶² ¼ö¸¦ ´Ù¸¥ ¼ö·Î ³ª´« ³ª¸ÓÁö °ªÀ» ¹ÝȯÇÏ´Â ÇÔ¼öÀÌ´Ù. ù ¹ø° ÀÎÀÚ¸¦ µÎ¹ø° ÀÎÀÚ·Î ³ª´« °á°ú¸¦ ¹ÝȯÇÑ´Ù. ¿©±â¼´Â ÇöÀç ´Þ month(now())¸¦ 12·Î ³ª´« µÚ ´ÙÀ½ ´ÞÀ» ³ªÅ¸³»±â À§ÇØ 1À» ´õÇØ ÁØ´Ù. À̹ø ´ÞÀÌ 12¿ùÀ̶ó¸é 12·Î ³ª´« ³ª¸ÓÁö°¡ 0À̹ǷΠ¿©±â¿¡ 1À» ´õÇØ ´ÙÀ½´Þ 1¿ùÀ» ³ªÅ³» ÁÙ ¼ö ÀÖ´Ù. ¿ÏÀüÇÑ SQL¹®Àº °¢°¢ ´ÙÀ½°ú °°´Ù: mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH)); mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MOD(MONTH(NOW()),12) + 1; 3.4.6 null °ª¿¡ ´ëÇØ NULL°ªÀº Ưº°ÇÑ °ªÀÌ´Ù. Àͼ÷ÇØ Áú ¶§ ±îÁö È¥µ¿µÉ °ÍÀÌ´Ù. °³³äÀûÀ¸·Î NULLÀÌ ÀǹÌÇÏ´Â ¹Ù´Â "ºüÁø, »©¸ÔÀº °ª", "¾ÆÁ÷ Á¤ÇØÁöÁö ¾ÊÀº ºÒÈ®Á¤ °ª"À» ÀǹÌÇÑ´Ù. ÀÌ°ÍÀº ´Ù¸¥ °ªµé°ú´Â ´Ù¸£°Ô Ãë±ÞµÈ´Ù. NULL¿¡´Â »ê¼ú ºñ±³ ¿¬»êÀ» ¼öÇàÇÒ ¼ö ¾ø´Ù. ¾î¶² °ª°ú NULL°ªÀ» =, <, !=À» ÀÌ¿ëÇÏ¿© ºñ±³ÇÏ´Â °ÍÀº Àǹ̰¡ ¾ø´Ù. ºÒÈ®Á¤ °ªÀ» ¾î¶»°Ô È®Á¤µÈ °ª°ú ºñ±³ÇÒ ¼ö ÀÖÀ» °ÍÀΰ¡? ´ÙÀ½À» º¸¶ó: mysql> SELECT 1 = NULL, 1 != NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ |1 = NULL |1 != NULL |1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+ À§¿¡¼ º¸µí ºÒÈ®Á¤ °ª°ú È®Á¤°ª°úÀÇ ºñ±³´Â ºÒÈ®Á¤°ªÀÌ µÈ´Ù. Àǹ̰¡ ¾ø´Ù. ´ÙÀ½°ú °°ÀÌ Çϸé Àǹ̰¡ ÀÖ´Ù: mysql> SELECT 1 IS NULL, 1 IS NOT NULL; +-----------+----------------+ | 1 IS NULL | 1 IS NOT NULL | +-----------+---------------+ | 0 | 1 | +-----------+---------------+ MySQL¿¡¼ °ÅÁþ °ªÀº 0À¸·Î Âü°ªÀº 1·Î ³ªÅ¸³½´Ù. 3.4.7 ÆÐÅÏ ÀÏÄ¡ ÆÐÅÏ ÀÏÄ¡ ±â´ÉÀº ¸Å¿ì À¯¿ëÇÑ ±â´ÉÀÌ´Ù. º¸´Ù ºü¸£°í Á¤±³ÇÏ°Ô ¿øÇÏ´Â Á¶°ÇÀ» ¸í¼¼ÇÏ¿© °Ë»öÇÒ ¼ö ÀÖ°Ô ÇØÁÖ´Â ±â´ÉÀ̱⠶§¹®ÀÌ´Ù. MySQLÀº Ç¥ÁØ SQL ÆÐÅÏ »Ó¸¸¾Æ´Ï¶ó À¯´Ð½º¿¡¼ »ç¿ëÇÏ´Â Á¤±Ô Ç¥Çö½Ä¿¡ ÇØ´çÇÏ´Â ÆÐÅÏ ÀÏÄ¡ ±â´Éµµ Áö¿øÇÑ´Ù. SQL¿¡¼ _ Àº ÀÓÀÇ ÇÑ ¹®ÀÚ¸¦ ÀǹÌÇϸç, %´Â ÀÓÀÇÀÇ ¼öÀÇ ¹®ÀÚ(0°³ÀÇ ¹®ÀÚ¸¦ Æ÷ÇÔ)¸¦ °¡¸£Å²´Ù. SQL ÆÐÅÏÀº ´ë¼Ò¹®ÀÚ¸¦ ºñ±³ÇÏÁö ¾Ê´Â´Ù. LIKE ÀÌÈÄ¿¡ ÆÐÅÏÀ» Áشٴ °ÍÀ» ±â¾ï ÇÏÀÚ. ¾Æ·¡ ¿¹¸¦ º¸¶ó: b·Î ½ÃÀÛÇÏ´Â À̸§¿¡ ´ëÇؼ °Ë»öÇÒ ¶§: mysql> SELECT * FROM pet WHERE name LIKE "b%"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ fy·Î ³¡³ª´Â À̸§¿¡ ´ëÇؼ °Ë»öÇÒ ¶§: mysql> SELECT * FROM pet WHERE name LIKE "%fy"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ w¸¦ Æ÷ÇÔÇÏ´Â À̸§À» °Ë»öÇÒ ¶§: mysql> SELECT * FROM pet WHERE name LIKE "%w%"; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ Á¤È®ÇÏ°Ô 5°³ÀÇ ±ÛÀÚ·Î ÀÌ·ç¾îÁø À̸§¿¡ ´ëÇؼ °Ë»öÇÒ ¶§´Â? mysql> SELECT * FROM pet WHERE name LIKE "_____"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ ¹ØÁÙÀ» 5°³ Àû¾î ÁØ´Ù. ÀÌÁ¨ Á¤±ÔÇ¥Çö¿¡ ±â¹ÝÇÑ ÆÐÅÏÀÏÄ¡¿¡ ´ëÇØ ¾Ë¾Æ º¸ÀÚ. Á¤±ÔÇ¥Çö½Ä¿¡ »ç¿ëµÇ´Â ¹®ÀÚ ¼³¸í . ¹®ÀÚ Çϳª * ¾Õ¿¡ ³ª¿Â ¹®ÀÚÀÇ 0°³ ÀÌ»óÀÇ ¹Ýº¹ ^ ¹®ÀÚ¿ óÀ½ $ ¹®ÀÚ¿ ³¡ [,] °ýÈ£¾ÈÀÇ ¹®Àڵ鿡 ÀÏÄ¡ {,} ¹Ýº¹À» ³ªÅ¸³¾ ¶§. ¿¹·Î n¹ø ¹Ýº¹ÇÒ ¶§ {n}À¸·Î Àû´Â´Ù. ¿©±â¼ SQL ÆÐÅÏÀº Àüü °ª°ú ÀÏÄ¡ÇØ¾ß "ÀÏÄ¡ÇÑ´Ù"°í ÇÏÁö¸¸ Á¤±ÔÇ¥ÇöÀº °ªÀÇ ¾î´À ºÎºÐ°ú ÀÏÄ¡Çصµ "ÀÏÄ¡ÇÑ´Ù"°í ´ÜÁ¤ÇÑ´Ù´Â °ÍÀ» À¯ÀÇÇØ¾ß ÇÑ´Ù. ¿¹¸¦ µé¾î, SELECT * FROM pet WHERE name REGEXP "ffy"; ¿Í SELECT * FROM pet WHERE name LIKE "ffy"; ´Â ÀüÇô ´Ù¸¥ °á°ú¸¦ ³º´Â´Ù. ¹®ÀÚ a³ª b³ª cÁß Çϳª¸¦ °¡¸£Å°´Â Ç¥ÇöÀº [abc]ÀÌ´Ù. ¹üÀ§¸¦ ÁÖ¾î¼ Ç¥ÇöÇÒ ¼öµµ ÀÖ´Ù. Á¤±ÔÇ¥ÇöÀº ´ë¼Ò¹®ÀÚ¸¦ ±¸º°ÇÑ´Ù. µû¶ó¼ ´ë¹®ÀÚ´ø ¼Ò¹®ÀÚ´ø »ó°ü¾øÀÌ ¾ËÆĺª ¹®ÀÚ Çϳª¸¦ °¡¸£Å°´Â Ç¥ÇöÀº [a-zA-Z]·Î ÇØ¾ß ÇÑ´Ù. *´Â 0°³ ÀÌ»óÀÇ ¹®ÀÚµéÀ̶ó°í Çß´Ù. x* ´Â x, xx, xxx ... ¿¡ ÇØ´çÇÑ´Ù. [0-9]*´Â 7, 12, 345, 678µîÀÇ ÀÓÀÇÀÇ ±æÀ̸¦ °®´Â ¼ö¸¦ ³ªÅ¸³½´Ù. ^abc´Â Á٠óÀ½¿¡ abc·Î ½ÃÀÛÇÏ´Â ÆÐÅÏÀ» abc$´Â abc·Î ³¡³ª´Â ¹®ÀÚ¿À» ÀǹÌÇÑ´Ù. Á¤±Ô Ç¥Çö½ÄÀ» ¾µ ¶§´Â LIKE´ë½Å REGEXPÀ» »ç¿ëÇÑ´Ù. ¿¹¸¦ º¸¸ç ÀÍÇô º¸ÀÚ. À̸§ÀÌ ¼Ò¹®ÀÚ b ȤÀº ´ë¹®ÀÚ B·Î ½ÃÀÛÇÏ´Â Á¶°ÇÀ¸·Î °Ë»ö: mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ À̸§ÀÌ fy·Î ³¡³¯ ¶§($¸¦ »ç¿ëÇÑ´Ù): mysql> SELECT * FROM pet WHERE name REGEXP "fy$"; +--------+--------+---------+------+-----------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+-----------+-------+ | Fluffy | Harold | cat | f |1993-02-04 | NULL | | Buffy | Harold | dog | f |1989-05-13 | NULL | +--------+--------+---------+------+-----------+-------+ Á¤È®ÇÏ°Ô 5°³ÀÇ ¹®ÀÚ·Î À̸§¾îÁø °ªÀº ´ÙÀ½ Á¤±Ô Ç¥Çö¿¡ ÀÏÄ¡ÇÑ´Ù: ^.....$ ÀÌ°ÍÀº ¹Ýº¹ ¿¬»êÀÚ¸¦ ÀÌ¿ëÇÏ¿© ´ÙÀ½Ã³·³ ¾µ ¼öµµ ÀÖ´Ù. ^.{5}$ 3.4.8 Çà¼ö ¼¼±â ´©°¡ ¾î¶² ¾Ö¿Ï µ¿¹°À» ¸îÀ̳ª ¼ÒÀ¯Çß´ÂÁö ¾î¶»°Ô ¾Ë¾Æ³¾ ¼ö ÀÖÀ»±î? ÀÌ¿¡ ´ëÇÑ ´äÀ¸·Î count()ÇÔ¼ö¸¦ »ç¿ëÇÏ¸é µÇ¸ç Àû´çÇÏ°Ô Á¶°ÇÀ» ºÎ¿©ÇØ ÁÖ¸é µÈ´Ù. mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 9 | +----------+ °¢ ¼ÒÀ¯ÁÖ°¡ ¼ÒÀ¯ÇÑ ¾Ö¿Ïµ¿¹°ÀÇ ¼ö´Â ´ÙÀ½ ó·³ Çϸé È®ÀÎ ÇÒ ¼ö ÀÖ´Ù: mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; +--------+----------+ | owner | COUNT(*) | +--------+----------+ | Benny | 2 | | Diane | 2 | | Gwen | 3 | | Harold | 2 | +--------+----------+ °¢ ownerÀÇ ¸ðµç ·¹ÄÚµåµéÀ» Çѵ¥ ¹±â À§ÇØ GROUP BY ÀýÀ» »ç¿ëÇÑ °ÍÀ» ÁÖ¸ñÇ϶ó. ÀÌ·¸°Ô ÇÏÁö ¾ÊÀ¸¸é ¿¡·¯ ¸Þ½ÃÁö¸¦ º¸°Ô µÉ °ÍÀÌ´Ù. mysql> SELECT owner, COUNT(owner) FROM pet; ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause COUNT()¿Í GROUP BY´Â µ¥ÀÌÅÍ¿¡ ¿©·¯ ¸ð¾çÀ¸·Î Ư¼ºÀ» ºÎ¿©ÇÏ´Â µ¥ ¾µ¸ð°¡ ÀÖ´Ù. ´ÙÀ½ ¿¹Á¦µéµµ Âü°íÇÏÀÚ: °¢ Á¾¿¡ ÇØ´çÇÏ´Â µ¿¹°ÀÇ ¼ö: mysql> SELECT species, COUNT(*) FROM pet GROUP BY species; +---------+----------+ | species | COUNT(*) | +---------+----------+ | bird | 2 | | cat | 2 | | dog | 3 | | hamster | 1 | | snake | 1 | +---------+----------+ ¼º¿¡ µû¸¥ µ¿¹°ÀÇ ¼ö: mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex; +------+----------+ | sex | COUNT(*) | +------+----------+ | NULL | 1 | | f | 4 | | m | 4 | +------+----------+ NULLÀº "°ªÀ» ¸ð¸§"ÀÇ ÀǹÌÀÌ´Ù. Á¾°ú ¼º¿¡ µû¸¥ µ¿¹°ÀÇ ¼ö: mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; +---------+------+----------+ | species | sex |COUNT(*) | +---------+------+----------+ | bird | NULL | 1 | | bird | f | 1 | | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | | hamster | f | 1 | | snake | m | 1 | +---------+------+----------+ ¹Ù·Î À§ÀÇ °æ¿ì¿Í´Â ´Þ¸®, ƯÁ¤ÇÑ µ¿¹°¿¡ ´ëÇؼ¸¸ Á¶»çÇØ º¼ ¼ö µµ ÀÖ´Ù. °³¿Í °í¾çÀÌÀÇ °æ¿ì¿¡¸¸ °¢ ¼º¿¡ ´ëÇØ ¸î¸¶¸®ÀÎÁö Á¶»çÇØ º¸ÀÚ: mysql> SELECT species, sex, COUNT(*) FROM pet -> WHERE species = "dog" OR species = "cat" -> GROUP BY species, sex; +---------+------+----------+ | species | sex | COUNT(*) | +---------+------+----------+ | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | +---------+------+----------+ 3.5 Å×ÀÌºí ¿©·¯°³ »ç¿ëÇϱâ pet Å×À̺íÀº ¾Ö¿Ïµ¿¹°¿¡ ´ëÇÑ Á¤º¸¸¦ °®°í ÀÖ´Ù. ¼öÀǻ翡 Ä¡·á ¹ÞÀ¸·¯ °¬´ø Ƚ¼ö³ª »õ³¢¸¦ ³ªÀº ³¯Â¥ °°Àº »ç°Çµé¿¡ ´ëÇÑ ´Ù¸¥ Á¤º¸¸¦ ±â·ÏÇÏ°í½Í´Ù¸é º°µµÀÇ Å×À̺íÀÌ ÇÊ¿äÇÒ °ÍÀÌ´Ù. Å×À̺íÀº ´ÙÀ½°ú °°Àº Á¶°ÇÀ» ¿ä±¸ÇÒ °ÍÀÌ´Ù: ¡Ü ÇØ´ç µ¿¹°ÀÇ À̸§À» °®°í ÀÖ¾î¾ß ÇÑ´Ù. ¾î¶² ¾Ö¿Ï µ¿¹°¿¡°Ô ÀÏ¾î³ »ç°ÇÀÎÁö ºÐº°ÇØ¾ß Çϱ⠶§¹®ÀÌ´Ù. ¡Ü ¾ðÁ¦ ÀÏ¾î³ ÀÏÀÎÁö ¾Ë±â À§ÇØ ³¯Â¥ Á¤º¸°¡ ÇÊ¿äÇÏ´Ù. ¡Ü ¾î¶² »ç°ÇÀÎÁö ¹¦»çÇØ µÑ ÇÊ¿ä°¡ ÀÖ´Ù. ¡Ü »ç°ÇÀ» ºÐ·ùÇÏ·Á¸é »ç°Ç À¯ÇüÀ» ³ªÅ¸³»´Â Çʵ嵵 ÀÖÀ¸¸é ÁÁÀ» °ÍÀÌ´Ù. ÀÌ¿Í °°Àº Á¶°ÇÀ» »ý°¢ÇÏ¿©, ´ÙÀ½ ó·³ Å×À̺íÀ» ¸¸µé¾î º¸ÀÚ: mysql> CREATE TABLE event (name VARCHAR(20), date DATE, -> type VARCHAR(15), remark VARCHAR(255)); pet Å×À̺íÀÇ °æ¿ì ó·³ ÆÄÀϷκÎÅÍ µ¥ÀÌÅ͸¦ Å×À̺í·Î ¿Ã¸®ÀÚ. event.txt¿¡ ´ÙÀ½Ã³·³ ÀûÇô ÀÖ´Ù°í ÇÏÀÚ. Fluffy 1995-05-15 litter 4 kittens, 3 female, 1 male Buffy 1993-06-23 litter 5 puppies, 2 female, 3 male Buffy 1994-06-19 litter 3 puppies, 3 female Chirpy 1999-03-21 vet needed beak straightened Slim 1997-08-03 vet broken rib Bowser 1991-10-12 kennel Fang 1991-10-12 kennel Fang 1998-08-28 birthday Gave him a new chew toy Claws 1998-03-17 birthday Gave him a new flea collar Whistler 1998-12-09 birthday First birthday ´ÙÀ½ ó·³ Å×À̺íÀ» ä¿ìÀÚ: mysql> LOAD DATA LOCAL INFILE "event.txt" INTO TABLE event; Áö±Ý±îÁö pet Å×À̺íÀ» ´Ù·ç¸é¼ ¹è¿üµíÀÌ event Å×ÀÌºí¿¡ ´ëÇØ ¿©·¯ °¡Áö ÁúÀǸ¦ ÇØ º¼ ¼ö ÀÖÀ» °ÍÀÌ´Ù. ÇÏÁö¸¸ Á¤º¸°¡ ºÒÃæºÐÇÒ ¶§´Â ¾î¶»°Ô Çϴ°¡? »õ³¢¸¦ ³º¾ÒÀ» ¶§ ¾î¹ÌÀÇ ³ªÀ̸¦ ¾Ë·Á¸é ¾î¶»°Ô ÇØ¾ß Çϴ°¡? event Å×À̺íÀ» ÅëÇØ ¾ðÁ¦ »õ³¢¸¦ ³º¾Ò´ÂÁö´Â ¾Ë ¼ö ÀÖÁö¸¸ ¾î¹ÌÀÇ ³ªÀ̶óµç°¡, ¼ÒÀ¯ÁÖ¶óµç°¡ ÇÏ´Â °ÍÀº pet Å×À̺íÀ» ÅëÇؼ ¾Ë¾Æ ³»¾ß ÇÑ´Ù. µû¶ó¼ SELECT ¹®À» »ç¿ëÇÒ ¶§ µÎ °³ÀÇ Å×À̺íÀÌ ÇÊ¿äÇÏ´Ù: mysql> SELECT pet.name, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, -> remark FROM pet, event -> WHERE pet.name = event.name AND type = "litter"; +--------+------+-----------------------------+ | name | age | remark | +--------+------+-----------------------------+ | Fluffy | 2.27 | 4 kittens, 3 female, 1 male | | Buffy | 4.12 | 5 puppies, 2 female, 3 male | | Buffy | 5.10 | 3 puppies, 3 female | +--------+------+-----------------------------+ À§ÀÇ ¿¹·ÎºÎÅÍ ¸î°¡Áö ¾Ë¾Æ µÎ¾î¾ß ÇÒ »çÇ×ÀÌ ÀÖ´Ù: ¡Ü FROM Àý¿¡ »ç¿ëÇÒ Å×À̺íÀ» ¸ðµÎ Àû¾î ÁÖ¾î¾ß ÇÑ´Ù. ÀÌ°Íµé ¸ðµÎ·ÎºÎÅÍÀÇ Á¤º¸°¡ ÇÊ¿äÇϱ⠶§¹®ÀÌ´Ù. ¡Ü ¿©·¯ Å×ÀÌºí¿¡¼ Á¤º¸¸¦ »Ì¾Æ ÇÕÇÒ ¶§´Â ÇÑ Å×À̺íÀÇ ·¹Äڵ尡 ´Ù¸¥ Å×À̺íÀÇ ·¹ÄÚµå¿Í ¾î¶»°Ô ÀÏÄ¡ÇÏ´Â Áö ¸í½ÃÇØ ÁÖ¾î¾ß ÇÑ´Ù. ¿©±â¼´Â µÎ Å×ÀÌºí ¸ðµÎ name Çʵ带 °®°í ÀÖÀ¸¹Ç·Î ÀÌ°ÍÀ» ÀÌ¿ëÇÏ¸é µÈ´Ù. À§¿¡¼ WHERE Àý¿¡ pet.name = event.name Á¶°ÇÀ» ÁÜÀ¸·Î½á µÎ °³ÀÇ Å×À̺íÀÇ °°Àº µ¿¹°¿¡ ÇØ´çÇÏ´Â ·¹Äڵ忡 ´ëÇؼ ÁúÀǸ¦ ÇÏ°Ô µÈ´Ù. ¼·Î ´Ù¸£´Ù¸é Àǹ̰¡ ¾ø´Ù. ¡Ü µÎ Å×ÀÌºí ¸ðµÎ name Çʵ带 °®°í ÀÖÀ¸¹Ç·Î ¾î´À Å×ÀÌºí¿¡ ¼ÓÇÏ´Â ÇʵåÀÎÁö¸¦ ±¸ºÐÇϱâ À§ÇØ <Å×À̺íÀ̸§>.<ÇʵåÀ̸§>ÀÇ Çü½ÄÀ¸·Î Àû¾î ÁÖ¾ú´Ù. Áï Å×À̺í À̸§°ú Çʵå À̸§À» Á¡À¸·Î ±¸ºÐÇÏ¿© Àû¾î ÁØ´Ù. À§¿¡¼ Å×À̺íÀº ¼·Î ´Þ¶ú´Ù. ÇÏÁö¸¸ µ¿ÀÏÇÑ Å×ÀÌºí¿¡ ´ëÇؼ À§¿¡¼Ã³·³ »ç¿ëÇÒ ÇÊ¿ä°¡ ÀÖÀ» ¶§°¡ ÀÖ´Ù. ¿¹¸¦ µé¾î °³ÀÇ ¼öÄÆ°ú ¾ÏÄÆÀ» ¦Áö¾î ÁÖ·Á¸é ¾î¶»°Ô ÇØ¾ß Çϴ°¡? µ¿ÀÏÇÑ Å×ÀÌºí¿¡ ´ëÇؼ ¼ºÀÌ °°ÀºÁö ´Ù¸¥Áö °Ë»çÇØ¾ß ÇÑ´Ù. ´ÙÀ½ ÇÑ ¿¹¸¦ µç´Ù: mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species -> FROM pet AS p1, pet AS p2 -> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m"; +--------+------+--------+------+---------+ | name | sex | name | sex | species | +--------+------+--------+------+---------+ | Fluffy | f | Claws | m | cat | | Buffy | f | Fang | m | dog | | Buffy | f | Bowser | m | dog | +--------+------+--------+------+---------+ 3.6 ¹èÄ¡ ¸ðµå(ÀÏ°ý ó¸® ¸ðµå)·Î »ç¿ëÇϱâ Áö±Ý±îÁö´Â ´ëȽÄÀ¸·Î »ç¿ëÇÏ¿´´Ù. ÁúÀǸ¦ ÃÄ ³Ö°í °á°ú¸¦ º¸´Â ½ÄÀÇ ¹Ýº¹ÀûÀÎ ÀÛ¾÷À̾ú´Ù. ÀÛ¾÷ ³»¿ë ÀüºÎ¸¦ ÆÄÀÏ¿¡ ±â¼úÇØ ÁØ ÈÄ ÇѲ¨¹ø¿¡ ó¸®ÇÒ ¼öµµ ÀÖ´Ù. ÀÌ·¸°Ô ÇÏ´Â ÀÛ¾÷À» ¹èÄ¡ ÀÛ¾÷À̶ó°í ÇÑ´Ù´Â °ÍÂëÀº ¾Ë¾Æ µÎÀÚ. ´ÙÀ½°ú °°Àº ½ÄÀ¸·Î »ç¿ëÇÑ´Ù: shell> mysql < batch-file ÀÛ¾÷ ³»¿ëÀ» ½á µÐ ÆÄÀÏ ³»¿ëÀ» Ç¥ÁØ ÀÔ·ÂÀ¸·Î ¹ÞÀ¸¸é µÈ´Ù. È£½ºÆ®¸í ¹× »ç¿ëÀÚ ¸í, Æнº¿öµå¸¦ ÀÔ·ÂÇÒ ÇÊ¿ä°¡ ÀÖÀ¸¸é Ãß°¡·Î ½á ÁØ´Ù: shell> mysql -h host -u user -p < batch-file Enter password: ******** ¿©·¯ºÐÀÌ ¹èÄ¡ ¸ðµå¿¡¼ »ç¿ëÇÒ ÆÄÀÏÀ» ÀÛ¼ºÇÏ´Â °ÍÀº ¹Ù·Î ½ºÅ©¸³Æ®¸¦ ÀÛ¼ºÇÏ´Â °ÍÀÌ´Ù. ±âº» °á°ú´Â ´ëȽÄÀ¸·Î ÇÒ ¶§¿Í´Â ´Ù¸£´Ù. SELECT DISTINCT species FROM petÀ» ½ÃÄ×À» ¶§ ´ëȽİú ¹èÄ¡ ¸ðµå¿¡¼ÀÇ °á°ú¸¦ º¸ÀÚ. ³»¿ëÀº °°Áö¸¸ Çü½ÄÀÌ ´Ù¸£´Ù. ´ëȽÄ: +---------+ | species | +---------+ | bird | | cat | | dog | | hamster | | snake | +---------+ ¹èÄ¡ ¸ðµå: species bird cat dog hamster snake ¹èÄ¡ ¸ðµå¿¡¼µµ ´ëÈ ¸ðµå¿¡¼¿Í °°Àº Çü½ÄÀ¸·ÎÀÇ Ãâ·ÂÀ» ¿øÇϸé mysql ½ÇÇà½Ã -t ¿É¼ÇÀ» ÁÖ¸é µÈ´Ù. ¸¸¾à ½ÇÇàµÇ´Â ¸í·É¾îµµ Ãâ·Â¿¡ Æ÷ÇÔÇÏ°í ½Í´Ù¸é -vvv¸¦ ºÙ¿©¶ó. ±×·¸´Ù¸é ¹«½¼ À¯ÀÍÀÌ Àֱ淡 ¹èÄ¡ ¸ðµå¸¦ »ç¿ëÇÒ±î? ´ÙÀ½¿¡ ¸î°¡Áö Àû¾î µÎ¾ú´Ù. ¡Ü ÁúÀǸ¦ ÀÚÁÖ ÇÑ´Ù¸é ½ºÅ©¸³Æ®·Î ¸¸µé¾î µÎ´Â °ÍÀÌ ½ÇÇàÇÒ ¶§¸¶´Ù ¸Å¹ø ´Ù½Ã ÃÄ ³Ö¾îÁÖ´Â ¼ö°í¸¦ ¾ø¾Ö ÁØ´Ù. ¡Ü ÀÌ¹Ì ÀÛ¼ºÇÑ ½ºÅ©¸³Æ®¸¦ ¼öÁ¤ÇÏ¿© °³¼±ÇÒ ¼ö ÀÖ°í »õ·Î¿î ½ºÅ©¸³Æ®¸¦ ÀÛ¼ºÇÒ ¼ö ÀÖ´Â ÀÕÁ¡ÀÌ ÀÖ´Ù. ¡Ü ¿©·¯ ÁÙ¿¡ °ÉÄ¡´Â ¸Å¿ì º¹ÀâÇÑ ÁúÀǸ¦ ¼öÇàÇÒ ¶§´Â ¹èÄ¡ ¸ðµå°¡ Àû´çÇÒ °ÍÀÌ´Ù. ½Ç¼ö¸¦ ÇßÀ» ¶§ ´ëȸðµå¶ó¸é ÀüºÎ ´Ù½Ã ÃÄ ³Ö¾î ÁÖ¾î¾ß ÇÑ´Ù. ¹èÄ¡ ¸ðµåÀ϶§´Â ÆÄÀϸ¸ ¼öÁ¤ÇØ ÁÖ¸é µÈ´Ù. ÇÏÁö¸¸ MySQLÀº readline ¶óÀ̺귯¸®(È÷½ºÅ丮±â´ÉÀ» ±¸ÇöÇÑ ¶óÀ̺귯¸®)±â´ÉÀ» »ç¿ëÇϹǷΠ´ëÈ ¸ðµåÀÏ ¶§µµ ´Ù½Ã ¸í·É¾î¸¦ ÃÄ ³Ö´Â ¼ö°í¸¦ Å©°Ô ´ú ¼ö ÀÖ´Ù. ¡Ü Ãâ·Â °á°ú°¡ ±²ÀåÈ÷ ¸¹´Ù¸é ¹èÄ¡¸ðµå·Î ½ÇÇà½ÃÅ°°í ÆäÀÌÀú(ÀÏÁ¤ÇÑ ÆäÀÌÁö ÁÙ¼ö·Î ¹®¼¸¦ º¸¿©ÁÖ´Â ÇÁ·Î±×·¥À» ÅëĪÇÏ´Â ¸íĪ)¸¦ ÅëÇØ º¸¸é ÁÁÀ» °ÍÀÌ´Ù. ´ÙÀ½ ó·³: shell> mysqlk < batch-file | less ¡Ü Ãâ·Â °á°ú¸¦ ´Ù¸¥ ÆÄÀÏ·Î ÀúÀåÇÒ ¼ö ÀÖ´Ù. ÀúÀåµÈ ÆÄÀÏÀº Ãß°¡ ÀÛ¾÷ÀÇ Ãâ¹ßÁ¡À¸·Î È°¿ëµÉ ¼ö ÀÖ´Ù. shell> mysql < batch-file > mysql.out ¡Ü ÀÛ¼ºÇÑ ½ºÅ©¸³Æ®¸¦ ´Ù¸¥ »ç¶÷°ú °øÀ¯ÇÒ ¼ö ÀÖ´Ù. ´Ù¸¥ »ç¶÷µµ ¿©·¯ºÐÀÌ ÀÛ¼ºÇÑ ½ºÅ©¸³Æ®¸¦ ½ÇÇàÇÒ ¼ö ÀÖÀ¸¸ç Âü°íÇÒ ¼ö ÀÖ´Ù. ¡Ü ¾î¶² ÀÛ¾÷Àº ¼º°Ý»ó ¹èÄ¡¸ðµå¿¡¼¸¸ ½ÇÇàÇÒ ¼ö ÀÖ´Ù. ÀÏÁ¤ÇÑ ½Ã°£ °£°ÝÀ¸·Î ¾î¶² ÀÛ¾÷À» ÇÒ ¶§´Â cronÀ» ÀÌ¿ëÇÏ¿© ¹èÄ¡¸ðµå¿¡¼ ó¸®ÇÒ ¼ö ¹Û¿¡ ¾ø´Ù. |