070-8260-2526

ÆòÀÏ ¿ÀÀü 9½Ã ~ ¿ÀÈÄ 6½Ã
´ÜÀ§³óÇù

352-0331-1626-83

¿¹±ÝÁÖ:°­¸¸¼öÅ×Å©³ÝÄÚ¸®¾Æ


ÀÚÁÖ¹¯´Â Áú¹®
Ȩ > °í°´¼¾ÅÍ > ÀÚÁÖ¹¯´Â Áú¹®

Á¶È¸¼ö 8986
Áú¹® Mysql ±âº» »ç¿ë¹ý
1. Mysql ±âº» »ç¿ë¹ý
->Mysql µ¥ÀÌÅͺ£À̽º Á¢¼ÓÇϱâ
# mysql -u root mysql
-> mysql °ü¸®ÀÚ root Æнº¿öµå ¼³Á¤Çϱâ
# mysql -u root mysql
mysql> update user set password = password('shcm0816') where user = ' root';
-> ºüÁ®³ª°£ÈÄ µ¥ÀÌÅͺ£À̽º¸¦ Àç½ÃÀÛ
# mysqladmin -u root reload
->Æнº¿öµå ÀÔ·Â
# mysql -u root -p mysql
< ¿©·¯°¡Áö °ü·Ã ¸í·É¾î >
->µ¥ÀÌÅͺ£À̽º È®ÀÎ
mysql> show databases;
->Å×À̺í È®ÀÎ
msyql> show databases;
msyql> desc user =>µ¿ÀÏÇÑ °á°ú
2. µ¥ÀÌÅͺ£À̽º »ý¼º ¹× »èÁ¦
->µ¥ÀÌÅͺ£À̽º »ý¼º
¨ç ù¹ø° ¹æ¹ý
# mysql -u root -p mysqlll
msyql> create database TESTDB;
msyql> show databases;
¨è µÎ¹ø° ¹æ¹ý
# ./mysqladmin -u root -p create testdb2
Enter password:
# ./mysql -u root -p mysql
Enter passwor
cd /
msyql> show databases;
msyql> exit
Bye
#
->µ¥ÀÌŸ º£À̽º »èÁ¦
# ./mysqladmin -u root -p drop testdb2
Enter password:
mysql> drop database testdb2
# ./mysql -u root -p mysql
msyql> show databases;
3. »õ·Î »ý¼ºÇÑ µ¥ÀÌÅͺ£À̽º µî·ÏÇϱâ
-> µ¥ÀÌÅͺ£À̽º¸¦ »õ·Î »ý¼ºÇÑ ÈÄ¿¡´Â mysql À̶ó´Â °ü¸®¿ë µ¥ÀÌÅͺ£À̽º¿¡ µî·Ï
->mysql À̶ó´Â µ¥ÀÌÅͺ£À̽ºÀÇ db¶ó´Â Å×ÀÌºí¿¡ TESTDB¸¦ µî·ÏÇÑ °Í
msyql> insert into db values('%','TESTDB','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
msyql> select host, db, user from db
4. Mysql »ç¿ëÀÚµî·Ï(µ¥ÀÌÅͺ£À̽º ¼ÒÀ¯ÀÚ)
->mysql À» »ç¿ëÇÒ »ç¿ëÀÚ´Â ¹Ýµå½Ã user ¶ó´Â Å×ÀÌºí¿¡ µî·ÏÀ» ÇØÁà¾ßÇÔ
msyql> insert into user (host, user, password) values('localhost', 'sspark', password('shcm0816'));
->Á¤»óÀûÀÎ µî·Ï¿©ºÎ È®ÀÎ
msyql> select host, user, password from user;
->ssparkÀ̶ó´Â »ç¿ëÀÚ·Î mysql µ¥ÀÌÅͺ£À̽º·Î Á¢¼Ó
msyql> mysql -u sspark -p
Enter password:
->»õ·Î¿î µ¥ÀÌÅͺ£À̽º¸¦ »ý¼ºÇϰųª »õ·Î¿î »ç¿ëÀÚ¸¦ »ý¼ºÇÑ ÈÄ¿¡´Â ¹Ýµå½Ã reload¸¦ ÇØÁà¾ßÇÔ
# mysqladmin -u root -p reload
Enter password:
5. Å×ÀÌºí »ý¼º ¹× »èÁ¦
msyql> create table testtable (
->uid mediumint(4) unsigned DEFAULT '0' NOT NULL auto_increment,
-> name varchar(12) DEFAULT "" NOT NULL,
-> email varchar (20) DEFAULT "" NOT NULL,
-> PRIMARY KEY (uid)
-> );
Query OK, 0 rows affected (0.00 sec)
msyql>show tables;
-> ÇÊµå ±¸Á¶°¡ Á¤ÀÇÇÑ Çʵ屸Á¶·Î »ý¼ºµÇ¾ú´ÂÁö È®ÀÎ
msyql> desc testtable;
-> »èÁ¦
msyql> drop table testtable;
mysql> show tables;
6. Å×À̺í¼Ó¼º ¹× ±¸Á¶º¯°æ¹ý
ALTER TABLE Å×À̺í¸í CHANGE[COLUMN] Çʵå¸í ÇʵåŸÀÔ
-> ±âÁ¸ Å×ÀÌºí¿¡ Çʵå Ãß°¡Çϱâ
mysql> alter table testtable add column homepage varchar(30);
mysql> desc testtable;
-> ±âÁ¸¿¡ ÇÊµå ¼Ó¼º º¯°æÇϱâ
ALTER TABLE Å×À̺í¸í CHANGE[COLUMN] ±âÁ¸Çʵå¸í »õÇʵå¸í ÇʵåŸÀÔ
mysql> alter table testtable change column homepage home varchar(50);
-> ±âÁ¸¿¡ ÇÊµå »èÁ¦
ALTER TABLE Å×À̺í¸í DROP [COLUMN] Çʵå¸í
mysql> alter table testtable drop column home;
mysql> desc testtable;
-> ±âÁ¸ÀÇ Å×À̺í»èÁ¦Çϱâ
mysql> create table sampletable
mysql> show tables;
mysql> drop table sampletable;
mysql> show tables;
7. µ¥ÀÌÅÍ ÀÔ·ÂÇϱâ
mysql> insert into testtable (uid, name, email) values ('', 'parksungsoo', 'sspark09@soback.kornet.net');
-> ÁÖÀÇ: uid¶ó´Â Çʵå´Â auto_increment ¶ó´Â ¼Ó¼ºÀ¸·Î »ý¼ºÇ߱⠶§¹®¿¡ ÀÌ ¼Ó¼ºÀ» °¡Áø ÇʵåµéÀº ½ÇÁ¦·Î µ¥ÀÌÅÍ°ªÀ» ÁÖÁö ¾Ê¾Æµµ ÀÚµ¿ÀûÀ¸·Î 1¾¿ Áõ°¡.
->»ðÀÔÇÒ Çʵå´Â »ý·«°¡´É
mysql> insert into testtable values('','sontaesoo','shutterbug@orgio.net');
->ƯÁ¤ Çʵ忡¸¸ µ¥ÀÌÅ͸¦ ÀÔ·ÂÇÒ °æ¿ì¿¡´Â ÀÔ·ÂÇÒ ÇʵåÀ̸§¸¸À» ³ª¿­ ±×¿¡ ÇØ´çÇÏ´Â µ¥ÀÌÅÍ ÀÔ·Â
mysql> insert into testtable (uid,name) values('','junwooki');
->Á¤»óÀûÀ¸·Î ÀԷµǾú´ÂÁö È®ÀÎ
mysql> select * from testtable;
8. µ¥ÀÌÅÍ °Ë»ö
SELECT(µ¥ÀÌÅÍ°Ë»öÇϱâ)
->µ¥ÀÌÅÍ°Ë»öÀÇ ÀϹÝÀûÀÎ ¹æ¹ý
-> Å×À̺íÀÇ ¸ðµç Å×ÀÌÅ͸¦ °Ë»ö
mysql> select * from testtable;
-> uid¿Í name Çʵ常À» Á¶È¸
mysql> select uid, name from testtable;
->where¹®À» »ç¿ëÇÏ¿© ƯÁ¤ Á¶°Ç¿¡ ¸Â´Â µ¥ÀÌÅ͸¸À» °Ë»ö
mysql> select * from testtable where name = "sontaesoo";
->Ãâ·Â °á°ú ·¹ÄÚµåÀÇ Áߺ¹Á¦°ÅÇϱâ(DISTINCT)
SELECT DISTINCT sex FROM testtable
->´Ù¾çÇÑ °Ë»ö ¹æ¹ý
->'soo'¶ó´Â ¹®ÀÚ°¡ Æ÷ÇÔµÈ µ¥ÀÌÅ͸¦ ¸ðµÎ °Ë»ö
->SELECT name FROM testtable WHERE name LIKE '%soo%'
mysql> select name from testtable where name LIKE '%soo%';
->ƯÁ¤ÇÑ ÇÑ ¹®ÀÚ·Î ½ÃÀÛÇÏ´Â µ¥ÀÌÅ͸¸À» Ãâ·ÂÇÒ ¶§¿¡ »ç¿ëÇÏ´Â Á¶È¸Çü½Ä
SELECT name FROM testable WHERE name LIKE 'park%'
mysql> select name from testtable where name LIKE 'park%';
->ƯÁ¤ Çʵ忡 µ¥ÀÌÅÍ°¡ NULL ÀÎ °æ¿ì¸¦ Á¶È¸ÇÑ°Í
SELECT uid, name FROM testtable WHERE age IS NULL
mysql>select uid, name from testtable where age IS NULL;
-> ƯÁ¤ ÇʵåÀÇ µ¥ÀÌÅÍ°¡ NULLÀÌ ¾Æ´Ñ °æ¿ì
SELECT uid, name FROM testtable WHERE age IS NULL
mysql>select uid, name from testtable where age IS NOT NULL;
-> SELECT name, uid, email FROM testtable WHERE savem > 5000 AND sex='M'
-> SELECT name, uid, savem FROM testtable WHERE savem >= 5000 ORDER BY savem
DESC
-> SELECT count(*) FROM testtable WHERE sex='M'
-> SELECT avg(savem) FROM testtable WHERE sex='M'
9. µ¥ÀÌÅÍ ¼öÁ¤Çϱâ
->UPDATE Å×À̺í¸í SET Çʵå¸í=Çʵ尪 ¶Ç´Â »ê¼ú½Ä {, Çʵå¸í = Çʵ尪 ¶Ç´Â »ê¼ú½Ä}*[WHERE
°Ë»öÁ¶°Ç]
mysql> select * from testtable;
->uid 4 ¹øÀÇ µ¥ÀÌÅÍ¿¡ name °ª°ú emailÀ» ÀÔ·ÂÇØ º½
mysql> update testtable set name = 'junyangmi' where age = 23;
mysql> update testtable set email = 'jym@superuser.co.kr' where age = 23;
mysql> select * from testtable;
-> uid 1 ÀÎ »ç¶÷ (parksungsoo)ÀÇ ³ªÀ̸¦ 25»ì·Î ¼öÁ¤
mysql> update testtable set age= 25 where uid =1;
->uid 2ÀÎ »ç¶÷ÀÇ ³ªÀ̸¦ 30»ì·Î ¼öÁ¤
mysql> update testtable set age = 30 where uid =2;
mysql> update testtable set age = 41 where uid=3;
-> ÇÑÇØ°¡ Áö³µÀ¸¹Ç·Î ¸ðµç »ç¶÷ÀÇ ³ªÀ̸¦ ÇÑ »ì¾¿ Áõ°¡
mysql> update testtable set age = age + 1;
mysql> select * from testtable;
10. µ¥ÀÌÅÍ »èÁ¦Çϱâ(delete)
->Delete FROM Å×À̺í¸í [WHERE °Ë»öÁ¶°Ç]
mysql> select * from testtable;
mysql> delete from testtable where uid = 3;
11. »õ·Î¿î »ç¿ëÀÚ µî·Ï
-> Mysql ¿¡ root·Î Á¢¼Ó
./mysql -u root -p
->Mysql DB ¼±ÅÃ
mysql> use mysql;
mysql> show tables;
->»õ·Î¿î DB »ý¼º
mysql> create database xlare;
-> user Å×ÀÌºí¿¡ ÀÔ·Â (user ¶ó´Â Å×ÀÌºí¿¡ »õ·Î¿î »ç¿ëÀÚÀÇ °èÁ¤À̸§°ú ±ÇÇѼ³Á¤À» ÇÒ Â÷·Ê)
-> È£½ºÆûç¿ëÀÚ¿¡ ´ëÇÑ ¼³Á¤ userÅ×ÀÌºí¿¡ ÇöÀç »ç¿ëÀå0¤Ä ´ëÇÑ ±ÇÇѵéÀ» ¸ðµÎ 'N'·Î ¼³Á¤
mysql> insert into user valuses('localhost', 'xlare', password('123'),'Y','Y','Y','Y','Y','N','N','N','N','N','N','N','N','N');
->user Å×ÀÌºí¿¡ ÀÔ·ÂÈ®ÀÎ
mysql> select * from user where user = 'xlare' ;
-> db Å×ÀÌºí¿¡ ÀÔ·Â
mysql> insert into db values('localhost', 'xlare', 'xlare', 'Y','Y','Y','Y','Y','N','N','N','N','N',);
-> db Å×ÀÌºí¿¡ ÀÔ·Â È®ÀÎ
mysql> select * from db where db = 'xlare';
-> »õ·Î¿î ¼³Á¤À» ¹Ù·Î Àû¿ëÅ°À§ÇØ mysqladmin À̶ó´Â °ü¸®ÀÚ¸í·É¾î·Î mysql À» Àç½ÃÀÛ
->reload¸¦ ÇØÁÖÁö ¾ÊÀ¸¸é »ý¼ºÇß´ø °ÍµéÀÌ Àû¿ëÀÌ µÇÁö ¾ÊÀ½
# ./mysqladmin -u root -p reload;
12. ÀϹݻç¿ëÀÚ ±ÇÇѼ³Á¤(º¸¾È»çÇ×)
#./mysql -u root -p mysql
Enter password:
mysql> show tables;
--------------------
tables in mysql
--------------------
columns_priv
db
func
host
tables_priv
user
----------------------
->columns_priv Å×À̺í
* ¸ðµç ƯÁ¤ DB ÀÇ Æ¯Á¤ User ¿¡ ´ëÇÑ Æ¯Á¤ Å×À̺íÀÇ °¢ Ä÷³¿¡ ´ëÇÑ select, insert, update,
reference ±ÇÇÑÀ» ¼³Á¤
mysql> desc columns_priv;
->db Å×À̺í
* db¶ó´Â Å×ÀÌºí¿¡´Â ƯÁ¤ DB¿¡ ´ëÇÑ userµéÀÇ ¿©·¯°¡Áö ±ÇÇѵéÀ» ¼³Á¤ÇÏ´Â Å×À̺í
mysql> desc db;
-> kebia ¶ó´Â µ¥ÀÌÅͺ£À̽ºÀÇ ¼ÒÀ¯ÀÚ¿Í ±× ±ÇÇÑ¿¡ ´ëÇؼ­ ¾Ë¾Æº¸°íÀÚ ÇÔ.
-> kebia¶ó´Â µ¥ÀÌÅͺ£À̽ºÀÇ ¼ÒÀ¯ÀÚ´Â hyung À̶ó´Â mysqkl »ç¿ëÀÚÀ̸ç ÀÌ hyung À̶ó´Â »ç¿ëÀÚ
´Â ÀÌ kebia ¶ó´Â µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇÑ °¢Á¾ ±ÇÇÑÀ» º¼¼ö ÀÖÀ½.
mysql> select * from db where db = 'kebia';
| localhost | kebia | hyung | Y | - - - - - ...........
------------------------
->tables_privÅ×À̺í
* columns_priv Å×À̺íó·³ °¢ µ¥ÀÌÅͺ£À̽º¿Í »ç¿ëÀÚ ¹× ±× Å×ÀÌºí¿¡ ´ëÇÑ ±ÇÇÑ ¼³Á¤
mysql > desc tables_priv;
---------------------
->user Å×À̺í : »õ·Î¿î »ç¿ëÀÚ¸¦ »ý¼ºÇÒ¶§ ÀÌ Å×ÀÌºí¿¡ µî·Ï
-> ƯÁ¤ »ç¿ëÀÚ¿¡ ´ëÇÑ Mysql¿¡ Á¸ÀçÇÏ´Â ¸ðµç µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇÑ ±ÇÇѼ³Á¤
-> ƯÁ¤ »ç¿ëÀÚÀÇ ¾î¶²±ÇÇÑ¿¡ ´ëÇÑ »çÇ×Àº ƯÁ¤ µ¥ÀÌÅͺ£À̽º°¡ ¾Æ´Ï¶ó Mysql¿¡ Á¸ÀçÇÏ´Â ¸ðµç µ¥
ÀÌÅͺ£À̽º¿¡ ±ÇÇÑÀÌ ÁÖ¾îÁü.
-> host¿Í user, password Ç׸ñÀ» Á¦¿ÜÇÑ ³ª¸ÓÁö ±ÇÇѵéÀº ¸ðµÎ 'N"·Î ¼³Á¤
->ƯÁ¤ÇÑ »ç¿ëÀÚ¿¡ ´ëÇÑ Æ¯Á¤ÇÑ µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇÑ ±ÇÇÑÀ» ÁÖ·Á¸é À§¿¡¼­ º¸¾Ò´ø db¶ó´Â Å×À̺í
¿¡¼­ ±× ±ÇÇѼ³Á¤À» Çϴ°ÍÀÌ ¾ÈÀüÇÔ
13 .Mysql root ¾ÏÈ£ º¯°æÇϱâ
-> ¹æ¹ý 1: mysqladmin À̶ó´Â ¸í·É¾î »ç¿ë
# ./mysqladmin -u root -p password 12345
->¹æ¹ý 2: mysqlÀÇ set À̶õ ¸í·É¾î·Î root ¾ÏÈ£ º¯°æ
# ./mysql -u root -p mysql
mysql> set password for root=passwd('12345678');
-> ¹æ¹ý 3: mysql µ¥ÀÌÅͺ£À̽º·Î Á¢¼ÓÇÏ¿© update¹®À» »ç¿ëÇÏ´Â ¹æ¹ý
# ./mysql -u root -p mysql
mysql> update user set password=password('12345' where user = 'root';
mysql> flush privileges;
->flush-privileges: ±ÇÇÑ Å×À̺íÀ» Àç½ÃÀÛÇÑ´Ù. ±ÇÇÑ ¼³Á¤À» º¯°æÇßÀ»°æ¿ì ¹Ýµå½Ã Àç½ÃÀÛ
mysql> flush privileges;
14. root Æнº¿öµå¸¦ Àؾî¹ö·ÈÀ» ¶§´Â ¾î¶»°Ô?
->´Ü°è 1: ½ÇÇàÁßÀÎ mysql Á¾·á
# ps -ef | grep mysqld
# killall mysqld
->´Ü°è 2: grant-table ¹Ì»ç¿ë¸ðµå·Î mysql½ÃÀÛ ¹× root·Î Á¢¼Ó
# .safe_mysqld --skip-grant-tables&
-> ´Ü°è 3: update¹®À¸·Î root»ç¿ëÀÚ Æнº¿öµå º¯°æ
mysql> update user set password=password('12345') where user = 'root';
mysql> flush privileges;
-> ´Ü°è 4: ½ÇÇàÁßÀÎ mysql ´Ù½Ã Á¾·á
# ps -ef | grep mysqld
# killall mysqld
-> ´Ü°è 5: ÀϹݸðµå·Î Mysql Àç½ÃÀÛ
# ./safe_mysqld&
# ps -ef | grep mysql
15. ƯÁ¤ Å×À̺íÀÇ ±¸Á¶º¸±â(show columns from Å×À̺í)
-> ƯÁ¤Å×À̺íÀÇ Çʵå¸í type, ¼³Á¤°ªµéÀ» º¸±â À§ÇÑ °Í
-> ƯÁ¤ÇÑ Å×À̺íÀÇ Ä®·³(Çʵå)±¸Á¶¸¦ º¸·Á¸é "show columns from Å×À̺í¸í"
-> Á»´õ Æí¸®ÇÏ¿© ¸¹ÀÌ »ç¿ëµÇ´Â ¸í·É¹® " desc Å×À̺í¸í"À¸·Î È®ÀÎ
mysql> show columns form user;
->ƯÁ¤ Å×À̺íÀÇ À妽º º¸±â
mysql> show index from user;
-> µ¥ÀÌŸº£À̽ºÀÇ ¼³Á¤»óÅ º¸±â(show status)
mysql> show status;
# ./mysqladmin -u root -p status
-> µ¥ÀÌÅͺ£À̽ºÀÇ ¼³Á¤È¯°æº¯¼ö¿Í °ªº¸±â(show variables)
mysql> show variables;
# ./mysqladmin -u root -p variables
-> ÇöÀç µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°áµÈ ÇÁ·Î¼¼½ºµé º¸±â(show processlist)
# ./mysqladmin -u root -p processlist