Oracle/MySQL °ü·Ã °Ô½ÃÆÇ |
---|
2002/08/05(09:01) from 211.169.72.154 | |
ÀÛ¼ºÀÚ : °ÁÙ±â (jkkang65@hanmail.net) | Á¶È¸¼ö : 5185 , ÁÙ¼ö : 797 |
Re: [MySQL] ±âÃÊ ¸í·É¾î |
---|
MySQL ÀÇ ±âÃÊ 1)MySQL ±âº» ¼³¸í ¹× ¼³Ä¡Çϱ⠡á MySQL Àº ¹«¾ùÀΰ¡ MySQLÀº °ü°èÇü µ¥ÀÌŸº£À̽ºÀÌ´Ù. ÀÌ°ÍÀº °ø°³¿ë µ¥ÀÌŸº£À̽º·Î¼ ÀÏ¹Ý »ó¿ë µ¥ÀÌŸ º£ À̽º¿Í ºñ±³ÇÏ¿© Å©°Ô µÚÁú°ÍÀÌ ¾ø´Â ¸Å¿ì ¶Ù¾î³ °ü°èÇü µ¥ÀÌŸº£À̽ºÀÌ´Ù. ¹°·Ð ¾ÈÁ¤¼º¹× ¹«°á¼³ÀÇ Ãø¸é¿¡¼ ´Ù¸¥ »ó¿ë µ¥ÀÌŸº£À̽º¿¡ ºñÇÏ¿© ¶³¾îÁø´Ù´Â °ÍÀº ÀÎÁ¤ÇÑ´Ù. ÇÏÁö¸¸ ¸Å ¿ì ¾ÈÁ¤ÀûÀ̶ó°í ÇÒ¼ö ÀÖ´Ù. °ü¸®¸¸ Àß ÇÑ´Ù¸é ¸Å¿ì Àß »ç¿ëÇÒ¼ö ÀÖÀ¸¸ç ´Ù¸¥ µ¥ÀÌŸº£À̽º ¿¡ ºñÇÏ¿© º¸¾ÈÀ̳ª, °¢Á¾ ÇÔ¼öµµ ¸¹¾Æ¼ ÇÁ·Î±×·¥¿¡ ¿ëÀÌÇÏ´Ù. °ø°³¿ë µ¥ÀÌŸ º£À̽º·Î´Â mSQL,PostgreSQL,MySQL µîÀÌ Àִµ¥ ±×Áß Àü¼¼°èÀûÀ¸·Î º¼¶§ MyS QLÀ̸¹ÀÌ ¾Ö¿ëµÇ°í ÀÖ´Ù°í ÇÒ¼ö ÀÖ´Ù. ±×¸®³ª ¿ì¸®³ª¶ó¿¡¼´Â ¾ÆÁ÷ ÀÌ¿ëµµ°¡ ¹Ì¹ÌÇÏ´Ù. PHP ¿Íµµ ¿¬°áÀÌ ¿ëÀÌÇÏ°í °¢Á¾ °ø°³¿ë À¥¼¹ö¿Í ¿¬°áµµ °£ÆíÇÏ´Ù. C, C++, Java, Python, TCK/TK , ±×¸®°í ODBCµµ Á¦°øÀÌ µÈ´Ù. ODBC´Âwindows3.1, win95, NT ¿ë ¸ðµÎ Á¦°øÀÌ µÇ°í ÀÖ´Ù. Access ¿Í ÀڷḦ ¿¬µ¿ÇÒ¼ö ÀÖÀ¸¸ç ExcelÀº ¹°·Ð Delphi(µ¨ÆÄÀÌ)¿Í´Â DBE 3.2¸¦ »ç¿ëÇÏ¿© µ¥ÀÌŸ ¿¬µ¿ÀÌ °¡´ÉÇϸç C++ Builder¿Í ´Â DBE 3.0 À» ÅëÇÏ¿© »ç¿ë °¡´ÉÇÏ´Ù. ÀÌ MySQLÀ» »ó¾÷ÀûÀ¸·Î »ç¿ëÇÒ °æ¿ì DB °³¹ßÀÚ µé¿¡°Ô ¾à°£ÀÇ °³¹ßÈÄ¿ø±Ý À» Á¦°øÇϸé Á¦Ç°¿¡ Æ÷ÇÔÇÏ¿© °³¹ßµÈ ÇÁ·Î±×·¥°ú ÇÔ²² ÆǸŵµÇÒ ¼ö ÀÖÀ¸¸ç °³ÀÎÀûÀÎ ¿ëµµ·Î¸¸ »ç¿ëÇÑ ´Ù¸é ¹«·á·Î »ç¿ëÇÒ ¼ö ÀÖ´Ù. ¡á MySQL ÀÇ °ø½Ä»çÀÌÆ® http://www.tcx.se/ : Mysql °ø½Ä ȨÆäÀÌÁö http://www.kreonet.re.kr/mysql/ : Çѱ¹ÀÇ °ø½Ä ¹Ì·¯¸µ»çÀÌÆ® ftp://korenet.re.kr/pub/packages/db/mysql/ : ftp »çÀÌÆ®(Çѱ¹) ¡á MySQL ÀÇ ÇöÀç ÃֽŠ¹öÀü MySQLÀº ÇöÀç °è¼ÓÀûÀ¸·Î ¾÷±×·¹À̵尡 ÀÌ·ç¾î Áö°í ÀÖ´Ù. 1998³â 4¿ù ÇöÀç·Î¼ °¡Àå ³ôÀº ¹ö ÀüÀº MySQL 3.21.27-gamma ÀÌ°í, 3.21.28-gamma °¡ °³¹ßµÇ¾î ÀÖÀ¸³ª ¾ÆÁ÷ ¹èÆ÷µÇÁö´Â ¾Ê°í ÀÖ´Ù. °ð ¹èÆ÷°¡ ÀÌ·ç¾îÁö¸®¶ó°í »ý°¢ÀÌ µÇ¾îÁø´Ù. ¡á MySQL ¼³Ä¡ °¡´É OS MySQLÀÇ ¼³Ä¡´Â ¸Å¿ì °£´ÜÇÏ´Ù. °ø½Ä »çÀÌÆ®·Î °¡¼ ¼Ò½º¸¦ °¡Áö°í ¿ÂÈÄ ÄÄÆÄÀÏÀ» ÇÏ¿© ¼³Ä¡ÇÏ ¿©µµ µÇ°í, ¶Ç´Â ÄÄÆÄÀÏµÈ ½ÇÇà ÆÄÀÏÀ» °¡Áö°í ¿Í¼ ½ÇÇàÇÏ¿©µµ ¹«¹æÇÏ´Ù. ÇöÀç MySQLÀº °¢Á¾OS ¿¡¼ ¼³Ä¡µÇ°í ½ÇÇàµÉ¼ö ÀÖ´Ù. ½ÇÇà °¡´ÉÇÑ OS¿Í ÆÐÅ°Áö´Â ´ÙÀ½°ú °°´Ù. Solaris 2.5 & 2.6 with native threads. SunOS 4.x with the included MIT threads package. BSDI 2.x with the included MIT threads package. BSDI 3.0 and 3.1 native threads. SGI IRIX 6.x with native threads. AIX 4.x whith native threads. DEC UNIX 4.x with native threads. Linux 2.0+ with LinuxThreads 0.5, 0.6 and 0.7. FreeBSD 2.x with the included MIT threads package. FreeBSD 3.x with native threads. SCO OpenServer with a recent port of the FSU-threads package. NetBSD 1.3 Intel and NetBSD 1.3 Alpha. Openbsd 2.1 HPUX 10.20 with the included MIT threads package. Win95 and NT. This version is currently only available for users with a MySQL license or MySQL email support. ¡á MySQL À» ¼³Ä¡ÇÒ °æ¿ì ÇÊ¿äÇÑ °Í MySQL À» ¼³Ä¡ÇÒ °æ¿ì ÇÊ¿äÇÑ °ÍÀÌ ÀÖ´Ù. ±×°ÍÀº ´ÙÀ½°ú °°´Ù. - ¾ÐÃàµÇ¾îÁø ¹èÆ÷ÆÇÀ» Ç®±âÀ§ÇÑ GNU gzip ÇÁ·Î±×·¥ - tar ÇÁ·Î±×·¥ - ANSI C++ ÄÄÆÄÀÏ·¯. - gcc ÄÄÆÄÀÏ·¯ : ¹öÀüÀº 2.7 ÀÌ»óÀÎ°Í ¡á MySQL ÀÇ ¼³Ä¡¹æ¹ý ´ÙÀ½°ú °°Àº ¼ú¼·Î MySQLÀ» ¼³Ä¡ÇÑ´Ù. 1. °ø½Ä ȨÆäÀÌÁö¿¡¼ MySQLÀ» ¹Þ´Â´Ù. 2. root ¾ÆÀ̵ð·Î ¾Æ·¡ÀÇ ¸ðµç ÀÛ¾÷À» ÇÑ´Ù. 3. ´ÙÀ½ÀÇ ¸í·ÉÀ¸·Î ¾ÐÃàÀ» Ǭ´Ù. gzip mysql-VERSION.tar.gz ¿©±â¼ mysql-VERSION.tar.gz Àº ¹æ±Ý ¹ÞÀº ¾ÐÃàÆÄÀÏÀÇ À̸§ÀÌ´Ù. 4. tar ¸í·ÉÀ» ÀÌ¿ëÇÏ¿© ¹¿©ÀÖ´Â ¼Ò½º¸¦ ´Ù½Ã Ç®¾î¾ß ÇÑ´Ù. ÀÌ°æ¿ì mysql-VERSION À̶ó´Â µð·ºÅ丮°¡ »ý¼ºµÇ¸é¼ ±× ¾È¿¡ ¸ðµç¼Ò½º¿Í ÆÄÀÏÀÌ Ç®¸® °Ô µÈ´Ù. tar -xvf mysql-VERSION.tar 5. »ý¼ºµÈ µð·ºÅ丮·Î À̵¿ÇÑ´Ù. cd mysql-VERSION 6. ȯ°æÀ» ¼³Á¤ÇÑ´Ù. ¸¸ÀÏ ¾î¶² ¿É¼ÇÀ» Ãß°¡Çϱ⸦ ¿øÇÑ´Ù¸é ´ÙÀ½°ú °°¾Æ ÇÑ´Ù. ./Configure --help ÀÌ °æ¿ì °¢Á¾ ¿É¼ÇÀÌ ³ª¿À¹Ç·Î ÀÌ ³»¿ëÀ» º¸°í º¯°æÇϱ⠹ٶõ´Ù. ´õ ÀÚ¼¼ÇÑ »çÇ×Àº ¿µ¹®¸Þ ´º¾óÀ» ÀÐ¾î º¸±â ¹Ù¶õ´Ù. ÀϹÝÀûÀ¸·Î ±×³É ¾Æ·¡ÀÇ ¸í·ÉÀ» ½ÇÇàÇÏ¸é µÈ´Ù. ./Configure 7. 6Ç×ÀÇ ½ÇÇàÀÌ ³¡³ª¸é ´ÙÀ½ÀÇ ¸í·ÉÀ» ½ÇÇàÇÑ´Ù. make ÀÌ ¸í·É¿¡¼´Â ¸ðµç ³»¿ëÀÌ ÄÄÆÄÀÏ µÈ´Ù. 486-66¿¡¼ ¸Þ¸ð¸® 8¸Þ°¡ÀÎ Àåºñ¿¡¼ ÄÄÆÄÀÏÀ» ÇÏ¿´´õ´Ï, 15½Ã°£ÀÌ °É·È´Ù. Àåºñ°¡ ÁÁ°í ¸Þ¸ð¸®°¡ ´õ¿í ¸¹´Ù¸é 2~3½Ã°£ À̳»¿¡ ÄÄÆÄÀÏ µÇ¸®¶ó°í »ý°¢ÀÌ µÈ´Ù. 8. 7Ç×ÀÇ ÀÛ¾÷ÀÌ ³¡³ª¸é ÀÌÁ¦ ÄÄÆÄÀÏµÈ ½ÇÇàÆÄÀÏÀ» ÀνºÅç ÇÏ¿©¾ß ÇÑ´Ù. make install ÄÄÆÄÀÏµÈ ¸ðµç ½ÇÇà °¡´ÉÇÑ ÆÄÀÏÀÌ ¼³Ä¡°¡ µÇ°í ȯ°æÀÌ ¼³Á¤µÈ´Ù. 9. ¼³Ä¡°¡ ³¡³ÈÄ ¾Æ·¡ÀÇ ¸í·ÉÀ» ½ÇÇàÇÏ¿© ±âÃÊ Å×À̺í°ú admin À¯Àú¸¦ ¼³Ä¡ÇÑ´Ù. ÀÌ ¸í·ÉÀº ´ÜÇѹøÇÑ ÇÏ´Â °ÍÀ¸·Î¼ µÎ¹ø ½ÇÇàÇÏ¸é ¾ÊµÈ´Ù. ÇöÀç 8Ç׿¡¼ ÀÛ¾÷ÇÑÈÄ µð·ºÅ丮¸¦ º¯°æ ÇÏÁö ¾Ê°í ¾Æ·¡ÀÇ ¸í·ÉÀ» ÀÔ·ÂÇÑ´Ù. ./scripts/mysql_install_db ÀÌ °ÍÀº ½ºÅ©¸³Æ®·Î¼ ½ÇÇàÇÏ¿© ±âÃÊÀÛ¾÷À» ÇÑÈÄ MySQLÀ» ½ÇÇà½ÃÄÑ ÁØ´Ù. 10. ¼³Ä¡ µð·ºÅ丮¸¦ µðÆúÆ®·Î ÇÏ¿´´Ù¸é /usr/local/bin/ µð·ºÅ丮¿¡ ½ÇÇàÆÄÀÏÀÌ ¸ðµÎ ¼³Ä¡µÇ¾úÀ» °ÍÀÌ´Ù. µðÆúÆ®·Î ¼³Ä¡ÇÏ¿´´Ù°í °¡Á¤ÇÏ°í ´ÙÀ½ÀÇ ¸í·ÉÀ» ¼öÇà ÇÏ¿© º»´Ù. /usr/local/bin/mysqladmin version MySQL ¹öÀü Á¤º¸°¡ ¾Æ·¡¿Í ºñ½ÁÇÏ°Ô Ãâ·ÂµÉ °ÍÀÌ´Ù. mysqladmin Ver 6.8 Distrib 3.21.27-gamma, for unknown-freebsd2.2.1 on i386 TCX Datakonsult AB, by Monty Server version 3.21.27-gamma-log Protocol version 10 Connection localhost via TCP/IP TCP port 3306 Uptime: 22 hours 20 min 9 sec Running threads:1 Questions: 20 Opened_tables: 8 Reloads: 2 Open tables: 4 11. ÀÌÁ¦ mysql ÇÁ·Î±×·¥À» ¾Æ·¡¿Í °°ÀÌ ½ÇÇàÇÏ¿© ±âº»ÀûÀÎ µ¿ÀÛÀ» Å×½ºÆ®ÇÏÀÚ. mysql mysql ȤÀº mysql -u root mysql (½ÇÇà½ÃÅ°´Â À¯Àú°¡ root°¡ ¾Æ´Ò°æ¿ì) 12. ¾Æ·¡ÀÇ sql¹®ÀåÀ» mysql »ó¿¡¼ ¼öÇà½ÃÄѺ¸ÀÚ select * from user mysql ÀÇ »ç¿ë¹æ¹ýÀº mysqlÀÌ ½ÇÇàµÈ »óÅ¿¡¼ ´ÙÀ½ÀÇ ¸í·ÉÀ» ÁÖ¸é ¾Ë¼ö ÀÖ´Ù. \h °¢Á¾ ¸í·ÉÀ» Àß ÀÐ¾î º¸°í »ç¿ëÇÏ¸é µÈ´Ù. SQL¹®µµ Ç¥ÁØ SQLÀ» ¸ðµÎ Áö¿øÇϸç MySQL¸¸ÀÇ È®Àå SQL¹®À» ¾Ë°í ½Í´Ù¸é ¿µ¹® ¸Þ´º¾óÀ» ÂüÁ¶Ç϶ó.(³ªÁß¿¡ ´Ù½Ã ° ÀÇÇÒ ¿¹Á¤) ¡á MySQL ServerÀÇ ½ÇÇà°ú Á¾·á MySQL ServerÀ» ½ÇÇà ½ÃÅ°°Å³ª Á¾·á½ÃÅ°·Á¸é ´ÙÀ½ÀÇ ¸í·ÉÀ» root °¡ »ç¿ëÇÏ¸é µÈ´Ù. mysql.server start --> mysql ServerÀÇ ½ÃÀÛ mysql.server stop --> mysql ServerÀÇ Á¾·á MySQL Server°¡ ½ÇÇà½Ã µÎ°³ÀÇ ÇÁ·Î¼¼¼°¡ ½ÇÇàµÇ¹Ç·Î È®ÀÎÇØ º¸±â ¹Ù¶õ´Ù. < ½ÇÇàÁßÀÎ µÎ°³ÀÇ ÇÁ·Î¼¼¼ > /usr/local/bin/safe_mysqld /usr/local/libexec/mysqld << ¼·Ð >> À̹ø¿¡´Â MySQL ¿¡ °ü·ÃµÈ ÇÁ·Î±×·¥°ú ±âº» ¼³Ä¡½Ã ÇÔ²² ¼³Ä¡µÇ´Â À¯Æ¿¸®Æ¼¿¡ ´ëÇÏ¿© ¾Ë¾Æ º¸µµ·Ï ÇÏ°Ú½À´Ï´Ù. ±âº» µ¿ÀÛ¿¡ ´ëÇÑ ¿¹Á¦µµ ¾à°£ Æ÷ÇÔÇÏ°í ³ªÁß¿¡ Á»´õ ÀÚ¼¼ÇÑ »çÇ×À» ´ã ¾Æ¼ ´Ù½Ã ¿Ã¸®Áö¿ä. ¸¹Àº »ç¶÷µé¿¡°Ô µµ¿òÀÌ µÇ¾úÀ¸¸é ÇÕ´Ï´Ù..... 2) MySQL ¿¡ °ü·ÃµÈ ÇÁ·Î±×·¥ ¹× À¯Æ¿¸®Æ¼ ¡á mysql sql ¹®ÀåÀ» ¼öÇàÇÏ´Â Å͹̳¯ÀÌ´Ù. ´ëÈ½Ä »ç¿ë°ú ºñ´ëÈ½Ä Áú¹®À» Áö¿øÇÑ´Ù. sql¹®À» ¶óÀο¡µð ÅÍ »óÅ¿¡¼ ¹Ù·ÎÀÛ¼ºÇÏ¿© ½ÇÇàÇÏ¸é µÈ´Ù. »ç¿ë¹ýÀº mysql mysql ÇÏ¸é ¹Ù·Î »ç¿ëÇÒ¼ö ÀÖ´Ù.(root °èÁ¤ÀÏ °æ¿ì) ÀÏ¹Ý °èÁ¤ »ç¿ëÀÚ´Â mysql -u root mysql ÇϸéµÈ´Ù. (¾ÏÈ£°¡ ¾øÀ»°æ¿ì) mysql ¿¡´ë ÇÑ »ç¿ë¹ýÀº mysql ½ÇÇà»óÅ¿¡¼ help ¶ó°í Ä¡¸é Ãâ·ÂµÈ´Ù. ¡á mysqlaccess È£½ºÆ®¿Í »ç¿ëÀÚ µ¥ÀÌŸº£À̽ºÀÇ ¿¬°á¿¡ ´ëÇÑ Æ¯±ÇÀ» üũÇÏ´Â ½ºÅ©¸³Æ®ÀÌ´Ù. ¡á mysqladmin MySQL ÀÇ °ü¸®¿ë À¯Æ¿¸®Æ¼ÀÌ´Ù. µ¥ÀÌŸº£À̽ºÀÇ »ý¼º,Á¦°Å, reload, ±×¸®°í refresh µîÀ» ÇÑ´Ù. ¶ÇÇÑ MySQL¼¹ö·Î ºÎÅÍ »óÅÂÁ¤º¸¿Í ÁøÇà»óÅÂ, Á¤º¸µîÀ» ÁØ´Ù. ¡á mysqld MySQL ÀÇ ¼¹ö µ¥¸óÀÌ´Ù. ÀÌ°ÍÀÌ Ç×»ó ½ÇÇàµÇ°í ÀÖ¾î¾ß¸¸ MySQL µ¥ÀÌŸº£À̽º¸¦ »ç¿ëÇÒ¼ö ÀÖ´Ù. ¡á mysqldump MySQL µ¥ÀÌŸºñ¿¡½º¸¦ ÀÐ¾î¼ ÆÄÀÏ·Î ÀúÀåÇÑ´Ù. SQL ¹®Àå ¶Ç´Â ÅÇÀ¸·Î ºÐ¸®µÈ ÅؽºÆ® ÆÄÀÏ·Î ÀúÀåÇÑ´Ù. ¡á mysqlimport Çϳª ¶Ç´Â ´õ¸¹Àº ÅؽºÆ® ÆÄÀÏÀ» °³º°ÀûÀÎ Å×À̺í·Î ÀÔ·Â(ÀúÀå)ÇÑ´Ù. LOAD DATA INFILE ¿¡ ÀÇÇØ Áö¿øµÇ´Â ¸ðµç Çü½ÃÀ» »ç¿ëÇÒ¼ö ÀÖ´Ù. ¡á mysqlshow µ¥ÀÌŸº£À̽º¿Í, Å×À̺í, column, ±×¸®°í Index ¿¡ ´ëÇÑ Á¤º¸¸¦ º¸¿© ÁØ´Ù. ¡á mysqlbug ÀÌ°ÍÀº ½ºÅ©¸³Æ®·Î¼ MySQL¹ö±× º¸°í¼¸¦ »ç¿ëÇÒ¶§ Ç×»ó »ç¿ëµÇ¾îÁö´Â °ÍÀÌ´Ù. ¡á mysql_install_db µðÆúÆ® Ư±ÇÀ» »ç¿ëÇÏ¿© Å×À̺íÀÇ Çã°¡¸¦ »ý¼º½ÃÅ°´Â ½ºÅ©¸³Æ®ÀÌ´Ù. MySQLÀ» ½Ã½ºÅÛ¿¡ óÀ½ ¼³ Ä¡ ÇÏ¿´À» °æ¿ì MySQLÀ» ½ÇÇàÇϱâ ÀÌÀü¿¡ ½ÇÇà½ÃÅ°´Â °ÍÀ¸·Î¼ ÀÌ°ÍÀº ¼³Ä¡ÈÄ µü Çѹø¸¸ ½ÇÇà ÇÏ´Â ½ºÅ©¸³Æ®ÀÌ´Ù. ¡á isamchk MySQL Å×À̺íÀ» üũÇϰųª, optimise, ¶Ç´Â ÀÌ»ó»óŸ¦ ¼öÁ¤ÇÒ¶§ »ç¿ëÇÑ´Ù. ¡á make_binary_release ÄÄÆÄÀÏµÈ MySQLÀÇ binary ¸±¸®Áî ¹öÀüÀ» ¸¸µç´Ù. ´Ù¸¥ MySQL »ç¿ëÀÚÀÇ ÆíÀ̸¦ À§ÇÏ¿© ÄÄÆÄÀÏµÈ ½ÇÇà¹öÀüÀ» ¸¸µé¶§ »ç¿ëµÈ´Ù. ¡á msql2mysql shell ½ºÅ©¸³Æ®·Î¼ mSQL ÇÁ·Î±×·¥À» MySQL·Î º¯È¯ÇÑ´Ù. ¸ðµç°ÍÀ» º¯È¯ÇÏÁö´Â ¾ÊÁö¸¸ º¯È¯Çϱ⠿¡ ÁÁÀº »óÅ·Π¸¸µé¾îÁØ´Ù. ¡á replace msql2mysql ¿¡ »ç¿ëµÇ´Â ½ÇÇàÆÄÀÏÀÌ´Ù. ¹®ÀÚ¿À» ÆÄÀÏ, Ç¥ÁØÀÔ·Â, ¶Ç´Â ¾î¶² ƯÁ¤ »óÅ·Πº¯È¯ ÇÏ´ÂÇÁ·Î±×·¥ÀÌ´Ù. ¹®ÀÚ¿À» ±³È¯ÇÒ¶§µµ »ç¿ëÇÒ¼ö ÀÖ´Ù. ¿¹¸¦µé¸é a b b a (ÆÄÀÏ»óÀÇ a ¿Í b¸¦ ±³È¯) ¡á safe_mysqld ¾î¶°ÇÑ ¾ÈÁ¤µÈ ÇüÅ·ΠMySQL µ¥¸óÀ» ½ÃÀÛ½ÃŲ´Ù. ¿À·ù½Ã Àç½ÃÀÛ ½ÃÅ°°í, ½ÇÇàÁ¤º¸ log ¸¦ log ÆÄÀÏ¿¡ ±â·ÏÇÑ´Ù. << ¼·Ð >> sql ¹®Àº µ¥ÀÌŸº£À̽º ÁúÀÇ¿¡ »ç¿ëµÇ´Â ´ëÈÇü ¾ð¾îÀÔ´Ï´Ù. MySQLµµ ¿ª½Ã ÀÌ Ç¥ÁØ ´ëÈÇü¾ð¾î ¸¦ Áö¿øÇÏ°í ÀÖÀ¸¸ç, ´õ¿í È®ÀåµÈ °¢Á¾ sql¹®À» ³»Æ÷ÇÏ°í ÀÖ½À´Ï´Ù.. °£´ÜÇÏ°Ô ³ª¸¶ Á¦°¡ MySQL ¿¡ ´ëÇÏ¿© MySQL¹®»óÀÇ ³»¿ëÀ» ¹ø¿ªÇÏ¿´½À´Ï´Ù. 3) MySQL ¿¡¼ »ç¿ëµÇ´Â sql¹® Á¤¸® ¡á CREATE DATABASE database_name (µ¥ÀÌŸº£À̽ºÀ̸§) µ¥ÀÌŸ º£À̽º À̸§Àº ¹®ÀÚ¿Í ¼ýÀÚ, '_' ¸¦ Æ÷ÇÔÇÏ´Â 32 byte À̳»·Î ÀÛ¼ºÇÑ´Ù. ÀÌ ¸í·ÉÀº »õ ·Î¿î µ¥ÀÌŸº£À̽º °ø°£À» »ý¼º½ÃŲ´Ù. Oracle·Î ¸»ÇÏÀÚ¸é tablespace¿Í °°Àº Å×À̺íÀÌ µé¾î°¡ ´Â ÀúÀå °ø°£À» ¸»ÇÑ´Ù. ¡á DROP DATABASE database_name Á¸ÀçÇÏ´Â µ¥ÀÌŸº£À̽º¸¦ Á¦°ÅÇÑ´Ù. µ¥ÀÌŸº£À̽º »ó¿¡ Á¸ÀçÇÏ´Â Å×ÀÌºíµµ ¸ðµÎ »èÁ¦ µÇ¹Ç·Î ¸Å ¿ì Á¶½ÉÇÏ¿© ÀÛ¾÷ÇÏ¿©¾ß ÇÑ´Ù. Çѹø »èÁ¦µÇ¸é º¹±¸´Â ºÒ°¡´É ÇϹǷΠµ¥ÀÌŸ º£À̽º »óÀÇ Å×ÀÌºí °ú µ¥ÀÌŸ°¡ ¸ðµÎ ¹é¾÷ÀÌ µÇ¾ú´ÂÁö È®ÀÎÇÏ°í ÀÛ¾÷ÇÑ´Ù. ¡á CREATE TABLE table_name ( create_definition,... ) µ¥ÀÌŸº£À̽º »ó¿¡ Å×À̺íÀ» »ý¼º½ÃÅ°´Â ¸í·ÉÀÌ´Ù. ÀÚ¼¼ÇÑ ¿É¼ÇÀº ´ÙÀ½°ú °°´Ù. Oracle ÀÇ table »ý¼º ¸í·É°ú °ÅÀÇ À¯»çÇÏ¸ç ºÎ°¡ÀûÀÎ type ÀÌ Á¸ÀçÇÑ´Ù. create_definition: column_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [ PRIMARY KEY ] [reference_definition] or PRIMARY KEY ( index_column_name,... ) or KEY [index_name] KEY( index_column_name,...) or INDEX [index_name] ( index_column_name,...) or UNIQUE [index_name] ( index_column_name,...) or FOREIGN KEY index_name ( index_column_name,...) [reference_definition] or CHECK (expr) type: TINYINT[(length)] [UNSIGNED] [ZEROFILL] or SMALLINT[(length)] [UNSIGNED] [ZEROFILL] or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] or INT[(length)] [UNSIGNED] [ZEROFILL] or INTEGER[(length)] [UNSIGNED] [ZEROFILL] or BIGINT[(length)] [UNSIGNED] [ZEROFILL] or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] or DECIMAL[(length,decimals)] [UNSIGNED] [ZEROFILL] or NUMERIC[(length,decimals)] [UNSIGNED] [ZEROFILL] or CHAR(length) [BINARY], or VARCHAR(length) [BINARY], or DATE or TIME or TIMESTAMP or DATETIME or TINYBLOB or BLOB or MEDIUMBLOB or LONGBLOB or TINYTEXT or TEXT or MEDIUMTEXT or ENUM(value1,value2,value3...) or SET(value1,value2,value3...) index_column_name: column_name [ (length) ] reference_definition: REFERENCES table_name [( index_column_name,...)] [ MATCH FULL | MATCH PARTIAL] [ ON DELETE reference_option] [ ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT ¡á ALTER [IGNORE] TABLE table_name alter_spec [, alter_spec ...] Á¸ÀçÇÏ´Â Å×À̺íÀ» º¯°æ ¹× columndÀÇ Ãß°¡ ÀÛ¾÷½Ã »ç¿ëµÈ´Ù. alter_specification: ADD [COLUMN] create_definition or CHANGE [COLUMN] old_column_name create_definition or ALTER [COLUMN] column_name { SET DEFAULT literal | DROP DEFAULT } or ADD INDEX [index_name] ( index_column_name,...) or ADD UNIQUE [index_name] ( index_column_name,...) or DROP [COLUMN] column_name or DROP PRIMARY KEY or DROP INDEX key_name or RENAME AS new_table_name ¡á DROP TABLE table_name [, table_name...] Á¸ÀçÇÏ´Â Å×À̺íÀ» Á¦°ÅÇÒ °æ¿ì »ç¿ëµÇ´Â ¸í·ÉÀÌ´Ù. ¡á DELETE FROM table_name WHERE where_definition Á¸ÀçÇÏ´Â Å×ÀÌºí »óÀÇ µ¥ÀÌŸ¸¦ »èÁ¦ÇÒ¶§ »ç¿ëÇÏ´Â ¸í·ÉÀÌ´Ù. ¡á SELECT ¹® Á¸ÀçÇÏ´Â Å×ÀÌºí »óÀÇ µ¥ÀÌŸ¸¦ Á¶È¸ÇÒ¶§ »ç¿ëÇÏ´Â ¸í·ÉÀÌ´Ù. ±âº»ÀûÀÎ »ç¿ë¹ýÀº ´ÙÀ½ °ú °°´Ù. SELECT [STRAIGHT_JOIN] [DISTINCT | ALL] select_expression,... [INTO OUTFILE 'file_name' ...] [FROM table_references [WHERE where_definition ] [GROUP BY column,...] [HAVING where_definition] [ORDER BY column [ASC | DESC] ,..] [LIMIT [offset,] rows] [PROCEDURE procedure_name]] ¡á JOIN ¹® Oracle ¿¡¼ »ç¿ëÇÏ´Â join ¹®º¸´Ù Á»´õ È®ÀåµÈ join ¹®À» Áö¿øÇÑ´Ù. table_reference, table_reference table_reference [CROSS] JOIN table_reference table_reference LEFT [OUTER] JOIN table_reference ON conditional-expr table_reference LEFT [OUTER] JOIN table_reference USING (column-commalist) table_reference NATURAL LEFT [OUTER] JOIN table_reference { oj table_reference LEFT OUTER JOIN table_reference ON conditional-expr } ¡á INSERT ¹® Á¸ÀçÇÏ´Â Å×ÀÌºí¿¡ µ¥ÀÌŸ¸¦ ÀÔ·ÂÇÒ¶§ »ç¿ëÇÑ´Ù. INSERT INTO table [ (column_name,...) ] VALUES (expression,...) or INSERT INTO table [ (column_name,...) ] SELECT ... ¡á REPLACE ¹® ÀÌ ¹®ÀåÀº INSERT¹®Àå°ú °ÅÀÇ À¯»çÇÏ°Ô µ¿ÀÛÇÑ´Ù. °°Àº ·¹Äڵ尡 ÀÖÀ»°æ¿ì ÀÌ ·¹Äڵ带 »è Á¦ÇÑÈÄ INSERT ÀÛ¾÷À» ÇÑ´Ù. °°Àº ·¹Äڵ尡 ¾øÀ» °æ¿ì´Â INSERT¿Í °°ÀÌ µ¿ÀÛÇÑ´Ù. REPLACE INTO table [ (column_name,...) ] VALUES (expression,...) or REPLACE INTO table [ (column_name,...) ] SELECT ... ¡á LOAD DATA INFILE ¹® Server »ó¿¡ À§Ä¡ÇÏ´Â text ÆÄÀÏ·Î ºÎÅÍ µ¥ÀÌŸ¸¦ ÀÐ¾î¼ Å×ÀÌºí¿¡ ÀÔ·ÂÀÛ¾÷À» ÇÒ °æ¿ì »ç¿ë ÇÑ´Ù. ¸Å¿ì ¼Óµµ°¡ ºü¸£´Ù. LOAD DATA INFILE 'text_file_name.text' [REPLACE | IGNORE] INTO TABLE table_name [FIELDS [TERMINATED BY ',' [OPTIONALLY] ENCLOSED BY '"' ESCAPED BY '\\' ]] [LINES TERMINATED BY '\n'] [(Field1, Field2...)] Å×À̺í·Î ºÎÅÍ µ¥ÀÌŸ¸¦ ÀÐ¾î¼ text ÆÄÀÏ¿¡ µ¥ÀÌŸ¸¦ ÀúÀåÇÒ°æ¿ì ´ÙÀ½°ú °°ÀÌ»ç¿ëÇÑ´Ù. SELECT ... INTO OUTFILE 'interval.txt' fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\n' FROM ... ¡á UPDATE ¹® Å×ÀÌºí »ó¿¡ Á¸ÀçÇÏ´Â µ¥ÀÌŸ¸¦ º¯°æÇÒ °æ¿ì »ç¿ëÇÑ´Ù. UPDATE table SET column=expression,... WHERE where_definition ¡á SHOW syntax. Get information about tables, columns... MySQL »óÀÇ °¢Á¾ Á¤º¸¸¦ º¸¿©ÁØ´Ù. µ¥ÀÌŸ º£À̽º, Å×À̺í, columnµîÀ» È®ÀÎÇÒ¼ö ÀÖ´Ù. SHOW DATABASES [LIKE wild] or SHOW TABLES [FROM database] [LIKE wild] or SHOW COLUMNS FROM table [FROM database] [LIKE wild] or SHOW INDEX FROM table [FROM database] or SHOW STATUS or SHOW VARIABLES [LIKE wild] ¡á EXPLAIN syntax. Get information about a SELECT. select ¹®°ú À̶§ ¿ä±¸µÇ´Â Å×ÀÌºí¿¡ ´ëÇÑ Á¤º¸¸¦ ÁØ´Ù. ÀÏ¹Ý select ¹®ÀÇ Ã³À½¿¡ EXPLAIN À» Ãß°¡ÇÏ¸é µ¿ÀÛÇÑ´Ù. EXPLAIN SELECT select_options ¡á DESCRIBE syntax Á¸ÀçÇÏ´Â Å×ÀÌºí¿¡¼ column Á¤º¸¸¦ °¡Áö°í ¿Â´Ù. (DESCRIBE | DESC) table [column] ¡á LOCK TABLES syntax Å×ÀÌºí¿¡ lock À» ¼³Á¤ÇÏ¿© ŸÀÎÀÌ ÀÐÁö ¸øÇÏ°Ô ÇÒ¶§ »ç¿ëÇÑ´Ù. ÁÖÀÇÇÒ Á¡Àº ÇÑ»ç¶÷ÀÌ lock À» ½ÇÇàÇϸé ÀÌ »ç¶÷ÀÌ »ç¿ëÇÏ´Â ¸ðµç Å×À̺íÀÌ lockµÇ¹Ç·Î »ç¿ëÈÄ ²À unlock¸¦ »ç¿ëÇÏ¿© lock¸¦ Ç®µµ·Ï ÇÑ´Ù. LOCK TABLES table_name [AS alias] READ|WRITE [, table_name READ|WRITE].. UNLOCK TABLES ¡á SET OPTION syntax. ÇöÀç »ç¿ëÁßÀÎ ¼¼¼ÇÀÌ »ç¿ëµÇ´Â µ¿¾È Áö¼ÓÀûÀ¸·Î ¿µÇâÀ» ÁØ´Ù. ¿©·¯ ¿É¼ÇÀÌ ÀÖÀ¸¹Ç·Î ¿øº» ¸Þ´º¾óÀ» ÂüÁ¶Çϱ⠹ٶõ´Ù. SET [OPTION] SQL_VALUE_OPTION=value, ... ¡á GRANT ¹® ( ȣȯ¸í·É) ÀÌ ¸í·ÉÀ» »ç¿ëÇÏÁö´Â ¾Ê°í ´ÜÁö ȣȯ¼º¹®Á¦ ¶§¹®¿¡ MySQL Á¸ÀçÇÑ´Ù. ÀÌ ¸í·ÉÀº ´Ù¸¥ SQL¼¹ö ·Î ºÎÅÍ MySQL·Î ½±°Ô º¯È¯Çϱâ À§ÇÏ¿© ¸¸µéÁ³´Ù. MySQL ¿¡¼ÀÇ Æ¯±ÇÀº MySQL Çã°¡ Å×À̺íÀ» ÀÌ¿ëÇÏ¿© ´Ù·ç¾î Áø´Ù. ¼½¼Ç 6.2 ÀÇ Æ¯±Ç ½Ã½ºÅÛ ¾÷¹«¸¦ ÇÏ´Â ¹æ¹ýÀ» Âü°í Ç϶ó. GRANT (ALL PRIVILEGES | (SELECT, INSERT, UPDATE, DELETE, REFERENCES (column list), USAGE)) ON table TO user,... [WITH GRANT OPTION] ¡á CREATE INDEX ¹® (ȣȯ¸í·É) ÀÌ ¸í·ÉÀ» »ç¿ëÇÏÁö´Â ¾Ê°í ´ÜÁö ȣȯ¼º¹®Á¦ ¶§¹®¿¡ MySQL Á¸ÀçÇÑ´Ù. ALTER TABLE À» »ç¿ëÇÏ¿© »õ·Î¿î index ¸¦ »ý¼ºÇÒ¼ö ÀÖ´Ù. ¼½¼Ç 7.7 ÀÇ ALTER TABLE ÀÇ ¹®¹ýÀ» º¸¾Æ¶ó. CREATE [UNIQUE] INDEX index_name ON table_name ( column_name,... ) ¡á DROP INDEX ¹® (ȣȯ¸í·É) ÀÌ ¸í·ÉÀº Ç×¼º ¼º°øÇÒ °ÍÀÌ´Ù. ALTER TABLE À» »ç¿ëÇÏ¿© index ¸¦ Á¦°ÅÇÒ¼öÀÖ´Ù. ¼½¼Ç 7.7ÀÇ ALTER TABLE ÀÇ ¹®¹ýÀ» º¸¾Æ¶ó. DROP INDEX index_name ¡á Comment ¹®¹ý ÇÑÁÙÀÇ ÁÖ¼®Àº # ·Î ÇÏ°í ÁÖ¼®ÀÇ ³»¿ëÀÌ ¿©·¯ÁÙÀÏ °æ¿ì´Â /* */ ¸¦ ÀÌ¿ëÇÏ¿© ÁÖ¼®À» ó¸®ÇÒ ¼ö ÀÖ´Ù. ¡á CREATE FUNCTION ¹®¹ý MySQL ÀÇ ±âº» ÇÔ¼öÀÎ ABS(), constr() ó·³ »õ·Î¿î »ç¿ëÀÚ Á¤ÀÇ ÇÔ¼ö¸¦ »ý¼ºÇÏ´Â ÇÔ¼öÀÌ´Ù. »ç¿ëÀÚÁ¤ÀÇÇÔ¼öÀÇ ¼Ò½º´Â C, C++·Î ÀÛ¼ºµÇ¾î¾ß ÇÏ°í µ¿ÀûÀ¸·Î ÀÐÇôÁö´Â°ÍÀÌ ¿ä±¸µÇ¾î Áø´Ù. ¿¹Á¦·Î¼ ¹èÆ÷ÆÇ¿¡ º¸¸é udf_example.cc °¡ ÀÖ°í 5°³ÀÇ »õ·Î¿î »ç¿ëÀÚÁ¤ÀÇÇÔ¼ö°¡ ÀÛ¼ºµÇ¾î ÀÖ´Ù. CREATE FUNCTION SONAME 5) MySQL ¼³Ä¡ÈÄ ±âº»ÀûÀÎ »ç¿ë¹ý°ú rootÀÇ ¾ÏÈ£¼³Á¤ 1. MySQL »ç¿ëÇϱâ 1.1 MySQL ¼¹ö ½ÃÀÛÇϱâ MySQLÀÌ Á¤»óÀûÀ¸·Î ¼³Ä¡ µÇ¾ú´Ù¸é ±×¸®°í µðÆúÆ® ¿É¼ÇÀ¸·Î ¼³Ä¡ ÇÏ¿´´Ù¸é /usr/local/bin/ µð·ºÅ丮¿¡ °ü·Ã ½ÇÇà ÆÄÀÏÀÌ ¼³Ä¡ µÇ¾úÀ» °ÍÀÌ´Ù. ¸ÕÀú MySQL ¼¹ö¸¦ ½ÇÇàÇÏ´Â ¸í·ÉÀº ´ÙÀ½ °ú °°´Ù. ¹°·Ð root ¾ÆÀ̵ð·Î ½ÇÇàÇØ¾ß ÇÑ´Ù.(´Ù¸¥¹æ¹ýÀÌ ÀÖÀ¸¸é ¾Ë·ÁÁÖ¼¼¿ä) mysql.server start À§¿Í °°ÀÌ ÀÔ·ÂÇÏ¸é ¼¹ö°¡ ½ÇÇàµÈ´Ù. µÎ°³ÀÇ ÇÁ·Î¼¼¼°¡ µ¥¸ó»óÅ·Π½ÇÇàµÇ´Âµ¥, safe_my sqld, mysqld ÀÌ·¸°Ô µÎ°³ÀÌ´Ù. ÀÌ µÎ°³ÀÇ ÇÁ·Î¼¼¼°¡ ½ÇÇàµÇ°í ÀÖ´Ù¸é ÀÌÁ¦ MySQLÀ» »ç¿ëÇÒ ¼ö ÀÖ´Ù. MySQL µÎ¹ø° °Á¿¡¼ º¸¸é À§ÀÇ µÎ ÇÁ·Î±×·¥¿¡ ´ëÇÑ ¼³¸íÀÌ ³ª¿Í ÀÖ´Ù. ÀÌ »óÅ¿¡ ¼ ¾Æ·¡¿Í °°ÀÌ MySQLÀ» »ç¿ëÇÒ¼ö ÀÖ´Â °¡»ó Å͹̳¯À» ½ÇÇà½ÃŲ´Ù. mysql -u root mysql MySQLÀÌ Ã³À½ ¼³Ä¡µÇ¸é DB admin user ´Â root ÀÌ´Ù. ¸ô·Ð ÀÌ°ÍÀº º¯°æ ÇÒ¼öµµ ÀÖ´Ù. µðÆúÆ®·Î ¼³Ä¡ÇÑ °æ¿ì ¾ÏÈ£°¡ óÀ½¿¡´Â »ý¼ºµÇ¾î ÀÖÁö ¾ÊÀ¸¹Ç·Î À§¿Í °°ÀÌ ÇÏ¸é ½ÇÇàÀÌ µÈ´Ù. À§ÀÇ ¸í·É¿¡ ´ëÇÑ ¼³¸íÀº ´ÙÀ½°ú °°´Ù. mysql -u root mysql ½ÇÇàÆÄÀÏ À¯Àú¼±Åà root db À¯Àú »ç¿ëÇÒµ¥ÀÌŸº£À̽º ±×¿Ü ¸î°¡Áö °£´ÜÇÑ ¿É¼ÇÀÌ ÀÖÀ¸³ª ÇÊ¿äÇϸé mysql -h ÇÏ¿© ³»¿ëÀ» Âü°í Çϱ⠹ٶõ´Ù.±×¸®°í, root À¯Àú¿¡ ¾ÏÈ£¸¦ ¼³Á¤ÇÑ ÈÄ ºÎÅÍ´Â -p ¿É¼ÇÀ» Çϳª´õ Ãß°¡ÇÏ¿© ½ÇÇàÇϱ⠹ٶõ´Ù. ÀÌ ¿É¼ÇÀº Æнº¿öµå¸¦ ÀԷ¹޴ ¿É¼ÇÀÌ´Ù. 1.2 mysql ÇÁ·Î±×·¥ÀÇ »ç¿ë mysqlÀ» ½ÇÇàÇÏ¿© °£´ÜÇÑ µ¿ÀÛÀ» ÇÏ¿©º¸ÀÚ. mysqlÀº ÀÏ¹Ý À¯Àú·Î ½ÇÇàÇÏ¿©µµ µÇ´Â ÇÁ·Î±×·¥ ÀÌ´Ù. $ mysql -u root mysql ---> DB »ç¿ëÀ» À§ÇÑ ½ÇÇà Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 3.21.27-gamma-log Type 'help' for help. mysql> À§¿Í °°ÀÌ ¿¬°áµÈ´Ù. ¿©±â¼ ¸î°¡Áö °£´ÜÇÑ Å×½ºÆ®¿ë ¸í·ÉÀ» Àû´Â´Ù. mysql> show databases --> µ¥ÀÌŸº£À̽º Á¾·ùº¸±â -> \g +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.00 sec) mysql> show tables from mysql --> mysql»óÀÇ Å×À̺í Á¤º¸ º¸±â -> \g +-----------------+ | Tables in mysql | +-----------------+ | db | | func | | host | | user | +-----------------+ 4 rows in set (0.00 sec) mysql> show columns from db --> db table Ä®·³Á¤º¸ º¸±â -> \g +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | Host | char(60) | | PRI | | | | Db | char(32) | | PRI | | | | User | char(16) | | PRI | | | | Select_priv | enum('N','Y') | | | N | | | Insert_priv | enum('N','Y') | | | N | | | Update_priv | enum('N','Y') | | | N | | | Delete_priv | enum('N','Y') | | | N | | | Create_priv | enum('N','Y') | | | N | | | Drop_priv | enum('N','Y') | | | N | | +-------------+---------------+------+-----+---------+-------+ 9 rows in set (0.00 sec) mysql> show index from db --> db tableÀÇ À妽ºÁ¤º¸±â -> \g +-------+------------+----------+--------------+-------------+ -----------+-------------+----------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | +-------+------------+----------+--------------+-------------+ -----------+-------------+----------+ | db | 0 | Host | 1 | Host | A | 4 | NULL | | db | 0 | Host | 2 | Db | A | 4 | NULL | | db | 0 | Host | 3 | User | A | 4 | NULL | | db | 1 | User | 1 | User | A | NULL | NULL | +-------+------------+----------+--------------+-------------+ -----------+-------------+----------+ 4 rows in set (0.00 sec) mysql> show status --> MySQLÀÇ »óÅÂÁ¤º¸ º¸±â -> \g +--------+-----------------+-----------+---------------+--------- +-------------+ | Uptime | Running_threads | Questions | Opened_tables | Reloads | Open_tables | +--------+-----------------+-----------+---------------+--------- +-------------+ | 1493 | 1 | 26 | 4 | 1 | 4 | +--------+-----------------+-----------+---------------+--------- +-------------+ 1 row in set (0.00 sec) mysql> show variables --> MySQL ȯ°æº¯¼öº¸±â -> \g +--------------------+---------------------------------+ | Variable_name | Value | +--------------------+---------------------------------+ | back_log | 5 | | basedir | /usr/local/ | | datadir | . | | join_buffer | 131072 | | key_buffer | 1048568 | | language | /usr/local/share/mysql/english/ | | max_allowed_packet | 65536 | | max_connections | 90 | | max_join_size | 4294967295 | | max_sort_length | 1024 | | net_buffer_length | 8192 | | port | 3306 | | record_buffer | 131072 | | skip_locking | 1 | | socket | /tmp/mysql.sock | | sort_buffer | 2097144 | | table_cache | 64 | | thread_stack | 65536 | | tmp_table_size | 1048576 | +--------------------+---------------------------------+ 19 rows in set (0.00 sec) mysql> select * from db --> db Å×À̺íÀÇ µ¥ÀÌŸ °¡Á®¿À±â -> \g +-----------+----------+------------+-------------+-------------+ -------------+-------------+-------------+-----------+ | Host | Db | User | Select_priv | Insert_priv | Update_priv |Delete_priv | Create_priv | Drop_priv | +-----------+----------+------------+-------------+-------------+ -------------+-------------+-------------+-----------+ | % | test | | Y | Y | Y |Y | Y | Y | | % | test\_% | | Y | Y | Y |Y | Y | Y | | localhost | binggo | binggo | Y | Y | Y |Y | Y | Y | +-----------+----------+------------+-------------+-------------+ -------------+-------------+-------------+-----------+ 3 rows in set (0.00 sec) mysql> À§ÀÇ ¸í·ÉÀº ±âº»ÀûÀÎ ¸í·ÉÀ¸·Î¼ ½ÇÁ¦ mysql À» »ç¿ëÇÒ¶§ ÀÚÁÖ »ç¿ëµÉ ¸í·ÉÀ̶ó°í »ý°¢µÈ´Ù. ±âŸ sql¹®Àº °Á 3¿¡¼ Á¤¸®ÇÏ¿´À¸¹Ç·Î »ý·«Çϱâ·Î ÇÑ´Ù. 2. MySQL DB admin user ¾ÏÈ£°É±â MySQL À» ¼³Ä¡ÈÄ °¡Àå ¸ÕÀú ÇØ¾ß ÇÒ ÀÏÀº MySQLÀÇ adminÀÎ root (DB user ÀÓ)ÀÇ ¾ÏÈ£¸¦ ¼³Á¤ÇÏ´Â ÀÏÀÌ´Ù. ÀÌ°ÍÀº ¼¹ö°¡ ÀÎÅÍ³Ý»ó¿¡ Á¢¼ÓµÇ¾î ÀÖÀ»°æ¿ì º¸¾ÈÀ» À§ÇÏ¿© °¡Àå ¸ÕÀúÇØ¾ß ÇÏ´Â ÀÏÀ̶ó°í »ý°¢µÈ´Ù. ¾ÏÈ£¸¦ ¼³Á¤ÇÏ´Â ¹æ¹ýÀº ´ÙÀ½°ú °°´Ù. ¸ÕÀú admin À¯ÀúÀÎ root ¾ÆÀ̵ðÀÇ ¾ÏÈ£¸¦ ¼³Á¤ÇÏ ±â·Î ÇÑ´Ù. mysql> update user -> set password = password('testpass') -> where user = 'root' -> \g Query OK, 2 row affected (0.00 sec) mysql> À§¸¦ ½ÇÇàÇϸé ÀϹÝÀûÀ¸·Î user Å×À̺í»ó¿¡ root ¾ÆÀ̵𰡠µÎ°³°¡ root ¾ÆÀ̵𰡠µî·Ï µÇ¾î ÀÖÀ¸¹Ç·Î 2 row ÀÇ ºñ¹Ð¹øÈ£°¡ º¯°æÀÌ µÈ´Ù.¿©±â½Ã È®ÀÎÀ» À§ÇÏ¿© ¾Æ·¡ÀÇ ¸í·ÉÀ» ½ÇÇàÇØ º»´Ù. mysql> select * from user -> \g . . . . . . . . Å×À̺í»ó¿¡ ÀúÀåµÈ ³»¿ëÀÌ Ãâ·ÂµÉ °ÍÀÌ´Ù. ÀÚ ÀÌÁ¦ºÎÅÍ´Â admin À¯ÀúÀÎ root·Î mysql¿¡ Á¢¼ÓÇÒ °æ¿ì ²À password¸¦ ÀÔ·ÂÇØ¾ß ÇÑ´Ù. Áï ¾Æ·¡ ¿Í °°ÀÌ ½ÇÇàÇÏ¿©¾ß ÇÑ´Ù. $ mysql -u root -p mysql Enter password: ¾ÏÈ£¸¦ ¹°À»¶§ ¾ÏÈ£¸¦ ÀÔ·ÂÇϸé mysqlÀ» »ç¿ëÇÒ¼ö ÀÖ´Ù. 3. »õ·Î¿î µ¥ÀÌŸ º£À̽º »ý¼º°ú »ç¿ëÀÚ ¿¬°á 3.1 µ¥ÀÌŸ º£À̽ºÀÇ »ý¼º µ¥ÀÌŸº£À̽º »ý¼ºÀÇ ¹æ¹ýÀº µÎ°¡Áö°¡ ÀÖ´Ù. mysqladmin ÇÁ·Î±×·¥À» ÀÌ¿ëÇÏ¿© root ¾ÆÀ̵ð·Î shell »ó¿¡¼ »ý¼ºÇÏ´Â ¹æ¹ý°ú ¶ÇÇϳª mysql ÇÁ·Î±×·¥ »ó¿¡¼ »ý¼ºÇÏ´Â ¹æ¹ýÀÌ´Ù. ¸ÕÀú mysqladmin ÇÁ·Î±×·¥À» ÀÌ¿ëÇÏ´Â ¹æ¹ýÀÌ´Ù. mysqladmin create soonhg --> ÀÌ°æ¿ì soonhg ¶ó´Â µ¥ÀÌŸº£À̽º »ý¼º admin ÀÎ root ¿¡ ¾ÏÈ£¸¦ ¼³Á¤ÇÑ °æ¿ì ´ÙÀ½°ú °°ÀÌ ½ÇÇàÇÑ´Ù. mysqladmin -u root -p create soonhg ÀÌ °æ¿ì µðÆúÆ®·Î ¼³Ä¡ÇÑ °æ¿ì /usr/local/var/soonhg ¶ó´Â µð·ºÅ丮°¡ »ý¼º µÈ´Ù. ÀÌ µð·ºÅ丮 °¡ µ¥ÀÌŸ º£À̽º °ø°£À¸·Î ÀÌ¿ëµÉ °÷ÀÌ´Ù. Áï soonhg ¶ó´Â µ¥ÀÌŸ º£À̽º °ø°£¿¡ Å×À̺íÀ» »ý¼º ÇÒ °æ¿ì soonhg ¶ó´Â µð·ºÅ丮·Î °ü·ÃµÈ ÆÄÀÏÀÌ »ý¼ºµÇ¸ç µ¥ÀÌŸ°¡ ÀúÀåµÈ´Ù. ******************************************************************* Áß¿äÇÑ tip...... ******************************************************************* MySQL°ú ÁÖ°í ¹Þ´Â °¢Á¾ ³»¿ëÀº /usr/local/var »óÀÇ fullµµ¸ÞÀθí.log ÆÄÀÏ»ó¿¡ ¸ðµÎ ÀúÀåµÈ´Ù. ¸¸ÀÏ µ¿ÀÛ »óŸ¦ ¾Ë°í ½Í´Ù¸é Å͹̳¯À» Çϳª ´õ ¿¾î¼ tail -f fullµµ¸ÞÀθí.log ÇÏ¿© MySQL°ú ÀÇ ÁÖ°í¹Þ´Â ³»¿ëÀ» ¸ð´ÏÅ͸µ ÇÒ ¼ö ÀÖ´Ù. ******************************************************************* µÎ¹ø° ¹æ¹ýÀ¸·Î mysqlÀ» ÀÌ¿ëÇÏ´Â ¹ýÀÌ´Ù. mysql ½ÇÇà»óÅ¿¡¼ ¾Æ·¡¿Í °°ÀÌÇÑ´Ù. (adminÀÎ root ¾ÆÀ̵ð·Î mysql ¿¡ Á¢¼Ó) mysql> create database soonhg -> \g Query OK, 1 row affected (0.00 sec) mysql> À§ÀÇ µÎ°¡Áö ¹æ¹ýÁß ¾î´À ¹æ¹ýÀ» »ç¿ëÇÏ¿©µµ ¹«°üÇÏ´Ù. °³ÀÎÀûÀ¸·Î´Â mysql »ó¿¡¼ µ¥ÀÌŸº£ÀÌ ½º¸¦ »ý¼ºÇϴ°ÍÀ» ±ÇÀåÇÑ´Ù. ¿Ö³ÄÇÏ¸é °è¼ÓÇÏ¿© ÀÛ¾÷À» ÇؾßÇҰ͵éÀÌ Àֱ⠶§¹®ÀÌ´Ù. 3.2 µ¥ÀÌŸº£À̽º¿Í mysql °ü¸® Å×À̺í°úÀÇ ¿¬°á ¹× »ç¿ëÀÚµî·Ï ¹× Ư±ÇÁ¤ÀÇ ÀÚ ÀÌÁ¦ »ý¼ºµÈ µ¥ÀÌŸº£À̽º¿Í µ¥ÀÌŸº£À̽º °ü¸®¹× ¿¢¼¼½º ±ÇÇѵîÀ» Á¤ÀÇ ÇÏ°í ÀÖ´Â mysql»óÀÇ db, user Å×ÀÌºí¿¡ ¹æ±Ý »ý¼ºµÈ soonhg Å×À̺íÀÇ ³»¿ëÀ» Á¤ÀÇ ÇÏÀÚ. ¸ÕÀú ÀÌ soonhg µ¥ÀÌŸº£À̽º´Â ¸ðµç È£½ºÆ® »ó¿¡¼ Á¢±ÙÀÌ °¡´ÉÇϵµ·Ï ¸¸µé±â·Î ÇÑ´Ù. ±×¸®°í »ç¿ë ÀÚ´Â id ·Î park ¶ó´Â DB user ¸¦ »ý¼ºÇÑ´Ù°í ÇÏÀÚ. sql ¹®Àº ´ÙÀ½°ú °°´Ù. mysql> insert into db -> (host,db,user,select_priv,insert_priv,update_priv,delete_priv, -> create_priv,drop_priv) -> values ('%','soonhg','park','Y','Y','Y','Y','Y','Y') -> \g Query OK, 1 row affected (0.00 sec) mysql> À§ÀÇ sql¹®¿¡¼ º¸¸é park À̶ó´Â »ç¿ëÀÚ´Â soonhg µ¥ÀÌŸº£À̽º¿¡ ´ëÇÏ¿© select,insert,update, delete,create,drop ±ÇÇÑÀÌ ¸ðµÎ ÁÖ¾îÁ³´Ù. ÀÌ°ÍÀº ÇϳªÀÇ soonhg µ¥ÀÌŸ º£À̽º¿¡ ´ëÇÏ¿© °¢ sql ¸í·É¿¡ ´ëÇÑ ±ÇÇÑÀ» °³º°ÀûÀ¸·Î ÁÙ¼ö ÀÖ´Ù´Â °ÍÀ» ÀǹÌÇÑ´Ù. select * from db ¶ó´Â sql ¹®À» ÀÌ¿ë ÇÏ¿© Á¤»óÀûÀ¸·Î insert µÇ¾ú´ÂÁö È®ÀÎÇØ º¸±â ¹Ù¶õ´Ù. ÀÚ ÀÌÁ¦ park ¶ó´Â db »ç¿ëÀÚ¿¡ ´ëÇÑ µî·ÏÀ» ÇÒ Â÷·ÊÀÌ´Ù. ´ÙÀ½°ú °°Àº sql ¹®À» ¸¸µé¾î ½ÇÇàÇÑ´Ù. mysql> insert into user -> (host,user,password,select_priv,insert_priv, -> update_priv,delete_priv,create_priv,drop_priv, -> reload_priv,shutdown_priv,process_priv,file_priv) -> values ('localhost','park',password('park'),'N', -> 'N','N','N','N','N','N','N','N','N') -> \g Query OK, 1 row affected (0.00 sec) mysql> À§¿Í °°ÀÌ ÇÏ¿©µµ µÇ°í °£´ÜÈ÷ ¾Æ·¡¿Í °°ÀÌ ÇÏ¿©µµ ¹«¹æÇÏ´Ù. mysql> insert into user -> (host,user,password) -> values ('localhost','park',password('park')) -> \g Query OK, 1 row affected (0.00 sec) mysql> host,user,password ÀÌ¿ÜÀÇ Ä®·³µéÀº ¸ðµÎ µðÆúÆ®·Î 'N' À¸·Î ¼³Á¤µÇ¾î ÀÖÀ¸¹Ç·Î À§¿Í °°ÀÌ ÇϸéµÈ´Ù. ¿©±â¼ db Å×À̺í°ú user Å×À̺íÀº Ư±Ç»ó¿¡¼ ¾à°£ÀÇ Â÷ÀÌÁ¡ÀÌ ÀÖ´Ù. db Å×ÀÌºí »ó¿¡¼ ¼³Á¤µÇÁö ¾Ê°í user Å×ÀÌºí »ó¿¡¸¸ ¼³Á¤µÇ¾îÁø DB »ç¿ëÀÚ´Â user Å×ÀÌºí »ó¿¡¼ ¼³Á¤ µÈ ±ÇÇÑÀ» Á¸ÀçÇÏ´Â ¸ðµç µ¥ÀÌŸº£À̽º¿¡ ´ëÇÏ¿© ÀÎÁ¤¹Þ´Â´Ù. ÀÌ·¯ÇÑ ÀÌÀ¯·Î ÀÏ¹Ý »ç¿ëÀÚÀÎ °æ¿ì´Â user Å×ÀÌºí »ó¿¡´Â ¸ðµç ±ÇÇÑÀ» 'N' ·Î ¼³Á¤ÇÏ¿©¾ß ÇÏ°í °¢ DB user °¡ »ç¿ëÇÒ µ¥ÀÌŸ º£À̽º¿¡ ´ëÇÑ ±ÇÇÑÀ» DB Å×ÀÌºí¿¡ ´Ù½Ã Á¤ÇØÁÖ´Â °ÍÀÌ´Ù. Áï, µ¥ÀÌŸº£À̽º°¡ ¿©·¯°³ Á¸ÀçÇÑ´Ù °í °¡Á¤ÇÏÀÚ. aaa, bbb, ccc ¶ó´Â ¼¼°³ÀÇ µ¥ÀÌŸº£À̽º°¡ Á¸ÀçÇÒ¶§, park À̶ó´Â DB »ç¿ëÀÚ´Â a aa ¶ó´Â µ¥ÀÌŸº£À̽º¸¸ ÀÌ¿ëÇÏ°Ô ÇÏ·Á°í ¼³Á¤ÇÏ·Á°í ÇÑ´Ù°í ÇÏÀÚ. ÀÌ°æ¿ì´Â user Å×ÀÌºí¿¡´Â ¸ðµç ±ÇÇÑÀ» 'N' »óÅ·Π¼³Á¤ÇÏ°í db Å×ÀÌºí¿¡´Â aaa ¶ó´Â Å×ÀÌºí¿¡ ´ëÇÏ¿© ¸ðµç ±ÇÇÑÀ» 'Y' ¶ó °í ¼³Á¤Çϸé ÀÌ park¶ó´Â db »ç¿ëÀÚ´Â aaa ¶ó´Â µ¥ÀÌŸº£À̽º¿¡ ´ëÇÏ¿© »ç¿ë±ÇÇÑÀÌ ÁÖ¾îÁú °ÍÀÌ ´Ù. Àß ÀÌÇØ°¡ °¡Áö ¾Ê´Â´Ù¸é ¸î¹ø ´õ ÀÚ¼¼È÷ Àо°í test Çغ»´Ù¸é ½±°Ô ÀÌÇØÇÒ ¼ö ÀÖÀ»°ÍÀÌ´Ù. ******************************************************************* Áß¿äÇÑ tip...... ******************************************************************* user Å×ÀÌºí¿¡ »ç¿ëÀÚ¸¦ insert, update ÇÑ °æ¿ì MySQLÀ» ²À Àç±âµ¿ ½ÃÄÑ ÁÖ¾î¾ß¸¸ ÇÑ´Ù. ¸í·É Àº ´ÙÀ½°ú °°´Ù. myaqladmin -u root -p reload ÀÌ ¸í·ÉÀº user Å×ÀÌºí¿¡¼ »ç¿ëÀÚ Á¤º¸¸¦ ´Ù½Ã ÀÐ¾î¼ MySQL À» ´Ù½Ã ½ÇÇà½ÃÄÑÁØ´Ù. ******************************************************************* 4. Å×À̺íÀÇ »ý¼º Å×À̺íÀÇ »ý¼ºÀº ÀϹÝÀûÀÎ sql ¹®ÀåÀ̹ǷΠ°£´ÜÈ÷ Å×À̺í Çϳª »ý¼ºÇÏ´Â ¿¹Á¦¸¦ Àû´Â°ÍÀ¸·Î ´ë½ÅÇÏ°Ú´Ù. À§¿¡¼ »ý¼ºÇÑ »ç¿ëÀÚ¿Í »ç¿ëÀÚ¸¦ À§ÇÑ Å×À̺íÀ» ÀÌ¿ëÇÏ¿© ÀÛ¾÷À» ÇÏ¿©º¸ÀÚ ¸ÕÀú mysqlÀ» ½ÇÇàÇÑ´Ù. ½ÇÇàÀº ±×³É ÀÏ¹Ý °èÁ¤ »ç¿ëÀÚ·Î ÇÑ´Ù. $ mysql -u park -p soonhg Enter password: **** <-- ¿©±â¼ password ÀÎ parkÀ» ÀÔ·ÂÇÑ´Ù. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 to server version: 3.21.27-gamma-log Type 'help' for help. mysql> ÀÚ MySQL¿¡ Á¢¼ÓµÇ¾ú´Ù. ÀÌÁ¦ tableÀ» »ý¼ºÇÏ¿© º¸ÀÚ. ¾Æ·¡´Â individual Å×ÀÌºí »ý¼ºÀ» À§ÇÑ SQL ¹®ÀåÀÌ´Ù. create table individual( empno char(16) primary key, name char(20) not null, sex char(2), addr char(80), tel char(15) ) À§ÀÇ Å×À̺íÀº °³ÀÎÁ¤º¸ Å×À̺í·Î °£´ÜÈ÷ ¸¸µç°ÍÀÌ´Ù. empno °¡ Å°°¡ µÇ¾î ÀúÀåµÈ´Ù. ¹°·Ð in dex Å×À̺íÀÌ Çϳª »ý¼ºµÇ¸ç empno °¡ Àε¦½Ì µÇ¾îÁø Å×À̺íÀÌ »ý¼ºµÉ°ÍÀÌ´Ù. not null Àº ¸» ±×´ë·Î name Ä®·³Àº null °ªÀº ¹ÞÁö ¾Ê°Ú´Ù´Â °ÍÀÌ°í..... ÀÚ ±×·³ insert ¹®ÀåÀ» Çϳª ÀÛ¼ºÇØ º¸ÀÚ insert into individual values ('1','¹Ú¼øÇà','1','¼¿ï½Ã Á߶û±¸ »óºÀµ¿ LG apt.','02-777-7777') À§ÀÇ sql¹®À» ½ÇÇàÇϸé ÇÑ rowÀÇ µ¥ÀÌŸ°¡ insert µÈ´Ù. ±×·³ ÀÌÁ¦ select ¹®À» ÀÌ¿ëÇÏ¿© ÀÐ¾î º¸ÀÚ. mysql> select * -> from individual -> \g +-------+--------+------+------------------------------+-------------+ | empno | name | sex | addr | tel | +-------+--------+------+------------------------------+-------------+ | 1 | ¹Ú¼øÇà | 1 | ¼¿ï½Ã Á߶û±¸ »óºÀµ¿ LG apt. | 02-777-7777 | +-------+--------+------+------------------------------+-------------+ 1 row in set (0.00 sec) À§¿Í °°ÀÌ Ãâ·Â µÉ °ÍÀÌ´Ù. ¿©·¯°¡Áö sql¹®ÀåÀ» Å×½ºÆ® Çغ¸¸é µÉ°ÍÀÌ´Ù. |