¸¶½ºÅÍÆ÷À¯(Master4U)
Monday, 17 of January
 

·Î±×ÀÎ Category
MySQL ±âº»°³³ä
°ü¸®ÀÚ  2010-12-23 03:27:49 Hit:5932
¸µÅ© #1: http://jjjboard.com/zb/zboard.php?id=mysql&page=2&sn1=&divpage=1&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=5

MySQL ±âº»°³³ä   (2004-11-04 21:42:37, Hit : 1184, Vote : 253)



MySQL ±âº»°³³ä

ÃÖÁ¶ ¼³Ä¡ ÈÄ MySQL DBÀÇ °¢Á¾ ±ÇÇÑÀ» °ü¸®ÇÏ´Â DB mysql¿¡ Å×À̺íÀ» »ý¼ºÇÏ´Â ½ºÅ©¸³Æ® ¸¦ ½ÇÇàÇØ¾ß ÇÑ´Ù.
#./bin/mysql_install_db (ÃÖÃÊ ¼³Ä¡½Ã¿¡¸¸ ½ÇÇàÇÏ¸é µÈ´Ù.)

¼­¹ö µ¥¸óÀ» °¡µ¿
#cd /sbin
#chmod 700 mysql.server
#./mysql.server start ¶Ç´Â ./bin/safe_mysqld &

admin password º¯°æ
#./bin/mysqladmin ?u root password ¡®user password¡¯
¶Ç´Â
./mysql
mysql>update user
->set password = password(¡®³Ö°í½ÍÀºÆÐ½º¿öµå¡¯)
->where user = ¡®root¡¯;

**update¹®À¸·Î mysql DBÀÇ userÅ×À̺íÀÇ Á¤º¸¸¦ º¯°æ½ÃŲ ÈÄ¿¡´Â MySQL¼­¹ö¸¦ Àç°¡µ¿½ÃÄÑ¾ß ÇÑ´Ù.
#./bin/mysqladmin ?u root reload ?p
µ¥ÀÌÅͺ£À̽º Á¤Áö
#./mysql.server stop ¶Ç´Â ./bin/mysqladmin shutdown -p

MySQLÀ» óÀ½ ¼³Ä¡Çϸé 2°³(mysql, test)ÀÇ DB°¡ Á¸ÀçÇÑ´Ù. (#./bin/mysql_install_db)
#./bin/mysql ?p =>ÆÐ½º¿öµå°¡ ÀÖ´Â ·çÆ®ÀÇ ±ÇÇÑÀ¸·Î SQLŬ¶óÀÌ¾ðÆ®¿¡ Á¢¼ÓÇÑ´Ù.
mysql>show databases; => ÇöÀç ÀÖ´Â DB¸¦ º»´Ù.
mysql>\q [¶Ç´Â exit]; => SQLŬ¶óÀ̾ðÆ®ÇÁ·Î±×·¥¿¡¼­ ºüÁ®³ª¿Â´Ù.
¸ðµç ÀÛ¾÷Àº DB·Î Á¢¼ÓÇØ¼­ ÇØ¾ßÇÑ´Ù(Å×À̺íÀÛ¼ºµîµî¡¦¡¦.).
#./bin/mysql ?u»ç¿ëÀÚ ?p db-name =>db-nameÀ̶ó´Â DB¿¡ »ç¿ëÀÚ°èÁ¤À¸·Î Á¢¼ÓÇÑ´Ù. ±ÇÇÑ ¼³Á¤Àº grant¡¦¡¦.
mysql>Show tables; => ÇöÀçÀÇ DB¿¡ ÀÖ´Â Å×À̺íµéÀ» º¸¿©ÁØ´Ù.
mysql>Desc table-name; => Å×À̺íÀÇ ±¸Á¶¸¦ º¸¿©ÁØ´Ù.
mysql>select * from table-name; => table-nameÀÇ µ¥ÀÌŸµéÀ» º¸¿©ÁØ´Ù.
°¢ °èÁ¤Àڵ鿡°Ô BDÇϳª¾¿À» ¸¸µé¾î ÁÖ°í ±×°÷¿¡¼­ ÀÛ¾÷ÇÏ°Ô ÇÏ·Á¸é¡¦¡¦.
#./bin/mysql -p mysql
mysql>create database µ¥ÀÌŸº£À̽º¸í; ¶Ç´Â
#./bin/mysqladmin -u root -p create µ¥ÀÌÅͺ£À̽º¸í; => DB»ý¼º
(mysql>drop database µ¥ÀÌŸº£À̽º¸í;
#./bin/mysqladmin -u root -p drop µ¥ÀÌÅͺ£À̽º¸í; =>DB»èÁ¦)

mysql>grant all privileges on µ¥ÀÌŸº£À̽º¸í.* to °èÁ¤ÀÚ@localhost identified
by ¡®passwd¡¯; => °èÁ¤ÀÚ¿¡°Ô µ¥ÀÌŸº£À̽º¸íÀÇ DB¿¡ ¸ðµç ±ÇÇÑÀ» ºÎ¿©.

»õ·Î¿î »ç¿ëÀÚ¸¦ µî·Ï(mysqlÀÇ userÅ×ÀÌºí¿¡ µî·Ï)
mysql>insert into (host, user, password) values(¡®localhost¡¯ ,¡¯pclab¡¯,
¡®password(¡®A¡¯)¡¯); =>À§ÀÇ grant¹®°ú ºñ±³¹Ù¶÷.

°¢ °èÁ¤ÀÚµéÀÌ Á¢¼ÓÇÏ¿© ÀÛ¾÷ÇÏ´Â ¹æ¹ý(µ¥ÀÌÅͺ£À̽º°ü¸®¾ð¾î)
$/usr/local/mysql/bin/mysql -p µ¥ÀÌŸº£À̽º¸í
mysql>create table Å×À̺í¸í(Çʵå¸í ÇüÅ [null/not null]); =>tableÀ» »ý¼ºÇÑ´Ù.
=>Çʵå¸íÀÌ ¸¹À» °æ¿ì ,±¸ºÐÇÑ´Ù.
mysql>insert into Å×À̺í¸í (Çʵå1, Çʵå2, ¡¦) values(¡®Çʵå1°ª¡¯, ¡®Çʵå2°ª¡¯, ¡¦);
mysql>drop table Å×À̺í¸í; =>Å×À̺íÀ» »èÁ¦ÇÑ´Ù.
mysql>update Å×À̺í¸í set Çʵå¸í = Çʵ尪 ¶Ç´Â »ê¼ú½Ä [where °Ë»öÁ¶°Ç];
mysql>flush privileges; =>updata½ÇÇàÈÄ¿¡ ½ÇÇà
mysql>delete from Å×À̺í¸í [where Çʵå¸í=¡±¿øÇÏ´Â ·¹Äڵ塱];
=>drop°ú ±¸ºÐ. Å×À̺í¸íÀ» Áö¿ì´Â °ÍÀ̰í delete´Â attributeÁ¦°Å
=>[where Á¶°Ç½Ä]ÀÌ ÀÖ´Â °æ¿ì´Â Á¶°Ç¿¡ ¸Â´Â Å×À̺íÀÇ ·¹Äڵ常 »èÁ¦
=>whereÀýÀÌ ¾øÀ¸¸é ¸ðµç µ¥ÀÌÅÍ»èÁ¦ÇÑ´Ù.
mysql>select * from Å×À̺í¸í [where Çʵå¸í];
=>Å×À̺í¸í¿¡ [Çʵå¸í¿¡ ÀÖ´Â] ÀÖ´Â ³»¿ëµéÀ» º»´Ù.
mysql>show tables; => ÇöÀçÀÇ DB¿¡ ÀÖ´Â Å×À̺íµéÀ» º»´Ù. ¾ø´Ù¸é »ý¼º
mysql>desc Å×À̺í¸í; => Å×ÀÌºí ±¸Á¶¸¦ º»´Ù.
mysql>select column from Å×À̺í¸í; == mysql>desc Å×À̺í¸í;
mysql>alter table Å×À̺í¸í add <attribute name><attribute list>;
=> ÀÌ¹Ì Á¸ÀçÇÏ´Â table¿¡ »õ·Î¿î attributeÃß°¡
mysql>alter table Å×À̺í¸í drop <attribute name> cascade; => ÀÌ¹Ì Á¸ÀçÇÏ´Â table¿¡ ±âÁ¸ÀÇ attribute Á¦°Å

**MySQLÀº µ¥ÀÌÅͺ£À̽º´Â µð·ºÅ丮·Î, Å×À̺íÀº ÆÄÀÏ·Î ÀúÀåÇÑ´Ù.
**°á°ú ·¹ÄÚµåÀÇ Áߺ¹Á¦°Å
mysql>select DISTINCT attribute(Çʵå) from tablae-name;
=>Çʵ忡¼­ À¯ÀÏÇÑ °ª¸¸ Ãâ·ÂÇÑ´Ù. Áߺ¹µÇ´Â °ªµé Á¦°Å.
**whereÀý µÚ¿¡´Â Á¶°Ç°Ë»öµµ °¡´É..
**°Ë»ö°á°úÀÇ Á¤·Ä
mysql>select * from table-name order by Çʵå¸í [asc/desc];
=>table-name Å×À̺íÀÇ Çʵå¸íÀ» [¿À¸§Â÷¼ø/³»¸²Â÷¼ø]À¸·Î Á¤·Ä
**selectÀý¿¡¼­´Â Çʵå¸í»Ó ¾Æ´Ï¶ó ¹®ÀÚ¿­°ú »ê¼ú½ÄÀÌ ¿Ã ¼ö ÀÖ´Ù.
mysql>select milage+300 from table-name where ·¹ÄÚµå;
**±×·ì ÇÔ¼ö¸¦ ÀÌ¿ëÇÑ °Ë»ö
1. count(Çʵå¸í) 2.sum(Çʵå¸í) 3.max(Çʵå¸í) 4. avg(Çʵå¸í)
mysql>select count(*) from table-name where Çʵå¸í[°ú Á¶°Ç];
**like¸¦ ÀÌ¿ëÇÑ °Ë»ö
mysql>select name from userdb where name like ¡®%Çö%¡¯;
¡®Çö¡¯À̶ó´Â ¹®ÀÚ°¡ µé¾î°£ ·¹Äڵ带 °Ë»öÇÑ´Ù.
%´Â ¿©·¯ºÐÀÚ¸¦ ¶æÇÑ´Ù. Áï ¡®%Çö¡¯ÀÌ¸é ³¡³ª´Â ¹®ÀÚ°¡ ÇöÀ̾î¾ß ÇÑ´Ù.
**null°ªÀ» °®´Â µ¥ÀÌÅÍ °Ë»ö
mysql>select name,id from userdb where email is null;
=>emailÀ» ÀÔ·ÂÇÏÁö ¾ÊÀº ID¸¦ ã´Â´Ù.

°³ÀÎÀÇ °¢ÀÚÀÇ ¾ÆÀ̵ð·Î ¼­¹ö¿¡ Á¢¼Ó ÈÄ °¢ °èÁ¤°ú °°Àº À̸§ÀÇ DB¿¡ Á¢¼ÓÇØ¼­ »ç¿ëÇϼ¼¿ä.
mysql>/usr/local/mysql/bin/mysql -u [ÀÚ±â°èÁ¤¸í] -p ÀÚ±â°èÁ¤
password : Ç×»ó ÇÏ´Â °Í (5°³ 5°³)
=> -u [ÀÚ±â°èÁ¤¸í] ¿¡¼­ ÀÚ±â°èÁ¤¸íÀ¸·Î Á¢¼ÓÇÏ´Â °ÍÀÌ°í µÚÀÇ ÀÚ±â°èÁ¤Àº °¢ »ç¿ëÀÚÀÇ °èÁ¤¸í°ú °°Àº DB¸¦ ¸»ÇÔ.

µ¥ÀÌÅͺ£À̽ºÀÇ »ç¿ë±ÇÇÑ ¼³Á¤
»ç¿ëÀÚ±ÇÇÑÀº mysqlÀ̶ó´Â µ¥ÀÌÅͺ£À̽º¿¡¼­ ¼³Á¤ÇÑ´Ù. Áï mysqlµ¥ÀÌÅͺ£À̽º´Â ƯÁ¤ µ¥ÀÌÅ͸¦ ÀúÀåÇϱâ À§ÇÑ ÀϹÝÀûÀÎ µ¥ÀÌÅͺ£À̽ºÀÇ Çϳª°¡ ¾Æ´Ï¶ó MySQL¼­¹ö¿¡ ÀúÀåµÇ¾î ÀÖ´Â ¸ðµç µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇÑ ±ÇÇÑ ¼³Á¤À» ´ã°í ÀÖ´Â µ¥ÀÌÅͺ£À̽º Áï ¸¶½ºÅÍ µ¥ÀÌÅͺ£À̽º¶ó°í ÇÒ ¼ö ÀÖ´Ù.
mysql DB¿¡´Â columns_priv, db, func, host, tables_priv, userµîÀÇ Å×À̺íÀÌ ÀÖ´Ù.
(#./bin/mysql ?p mysql·Î Ŭ¶óÀÌ¾ðÆ® Á¢¼Ó ÈÄ mysql>show tables;·Î È®ÀÎ)

**userÅ×À̺í
MySQL¼­¹ö¿¡ »ý¼ºµÇ¾î ÀÖ´Â µ¥ÀÌÅͺ£À̽º¸¦ »ç¿ëÇÒ ±ÇÇÑÀ» °®´Â »ç¿ëÀÚ¿¡ ´ëÇÑ Á¤º¸¸¦ ´ã°í ÀÖ´Â Å×À̺íÀÌ´Ù. Áï host, user, passwordµî »ç¿ëÀÚ Á¤º¸¿Í ÇØ´ç »ç¿ëÀÚ°¡ µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇØ °¡Áú¼ö ÀÖ´Â ±ÇÇÑ¿¡ ´ëÇÑ Á¤º¸·Î ±¸¼ºµÇ¾î ÀÖ´Ù.
mysql>desc user; => userÅ×À̺íÀÇ ±¸Á¶¸¦ º»´Ù.
mysql>select * from user; => °¢ ·¹ÄÚµå °ªµéÀ» º¼ ¼ö ÀÖ´Ù

**dbÅ×À̺í
db¸í°ú ÀÌ db¸¦ »ç¿ëÇÒ ±ÇÇÑÀ» °®´Â »ç¿ëÀÚ(user) ¹× host¿Í ÀÌ db¸¦ Á¶ÀÛÇÒ ¼ö ÀÖ´Â ¿©·¯ °¡Áö ±ÇÇÑ¿¡ ´ëÇÑ ¼³Á¤ Ç׸ñÀ» °®´Â´Ù.

¿¹¸¦ µé¾î sampleÀ̶ó´Â DB¸¦ pclab»ç¿ëÀÚ°¡ ¸ðµç ±ÇÇÑÀ» °¡Áö°Ô ÇÏ·Á¸é,
mysql>insert into db values ( ¡®%[host]¡¯, ¡¯sample¡¯ ,php¡¯ ,¡¯y¡¯ ,¡¯y¡¯ ,¡¯y¡¯ ,¡¯y¡¯ ,¡¯y¡¯ ,
¡¯y¡¯ ,¡¯y¡¯ ,¡¯y¡¯ ,¡¯y¡¯ ,¡¯y¡¯);
=>¡¯y¡¯ÀÇ °³¼ö´Â mysql>desc db;·Î ±ÇÇÑÀ» ÁÙ ¼ö ÀÖ´Â ·¹Äڵ带 È®ÀÎ,
mysql>grant all privileges on sample.* to pclab@localhost identified by ¡®P/W¡¯;
=>sample.* ÀÇ Àǹ̴ sample DBÀÇ ¸ðµç Å×ÀÌºí¿¡ ±ÇÇÑÀ» Áشٴ °ÍÀÌ´Ù.
´Ù¸¥ ¿¹·Î sample DB¾ÈÀÇ test¶ó´Â Å×ÀÌºí¿¡ ±ÇÇÑÀ» ÁÖ·Á¸é sample.test.

¸ðµç µ¥ÀÌÅͺ£À̽ºÀÇ ¸ðµç Å×ÀÌºí¿¡ ¸ðµç ±ÇÇÑÀ» ºÎ¿©Çϴ¹æ¹ý
mysql>grant all privileges on *.* to pclab@localhost identified by ¡®P/W¡¯ with
grant option;
mysql>insert into user values(¡®localhost¡¯,¡¯pclab,password(¡®P/W¡¯), ¡®y¡¯, ¡®y¡¯, ¡®y¡¯, ¡®y¡¯, ¡®y¡¯, ¡®y¡¯, ¡®y¡¯, ¡®y¡¯, ¡®y¡¯, ¡®y¡¯, ¡®y¡¯, ¡®y¡¯, ¡®y¡¯, ¡®y¡¯);
=>mysql>desc user; ¶Ç´Â mysql>select * from user; ·Î È®ÀÎ
mysql>flush privileges;

ex)
mysql>grant all privileges on µ¥ÀÌŸº£À̽º¸í.* to ¾ÆÀ̵ð@localhost identified by 'ÆÐ½º¿öµå';
mysql>create database µ¥ÀÌŸº£À̽º¸í;
mysql>show databases;


####MySQL¿¡¼­ µ¥ÀÌÅÍ ¹é¾÷°ú º¹±¸;
#./bin/mysqldump -u [DB»ç¿ëÀÚ] -p [¹é¾÷ÇÒ µ¥ÀÌÅͺ£À̽ºÀÇ À̸§] > [ÆÄÀÏÀ̸§]
=>ÆÄÀÏÀº ÇöÀçÀÇ ÀÛ¾÷µð·ºÅ丮¿¡ »ý±ä´Ù.
#./bin/mysql -u [DB»ç¿ëÀÚ] -p [º¹±¸ÇÒ µ¥ÀÌÅͺ£À̽ºÀÇ À̸§] <[¹é¾÷ÇÑ ÆÄÀÏÀ̸§]
=>¹é¾÷ÇÑ ÆÄÀÏÀÌ ÇöÀçÀÇ µð·ºÅ丮(À§ °æ¿ì/usr/local/mysql)¿¡ À§Ä¡ÇØ¾ß ÇÑ´Ù.
º»¹®Àμ⺻¹®¸ÞÀϹ߼Û
¡â MySQL Admin Tool ¾îµå¹ÎÅø
¡ä ±ÝÁÖÀÇ °Ô½Ã¹° SQL·Î ÇÑÅ¥¿¡ »Ì¾Æ³»±â! [1]
Copyright 1999-2025 Zeroboard / skin by ChanBi