japanese site
¿Â¶óÀΰ­ÁÂÃ¥¸ÞÀÏ´Ù¿î·Îµåµ¿¾Æ¸®¼îÇαâ¼úÁö¿ø±â¾÷Á¤º¸°¶·¯¸®µ¿¿µ»ó ºí·Î±×
 ¾Æ ÀÌ µð
 ºñ¹Ð¹øÈ£
ȸ¿ø °¡ÀÔ | ºñ¹Ð¹øÈ£ ã±â

ºí·Î±×~

 
Å¥ºê¸®µå
MySQL
Oracle
PostgreSQL
Ã¥Á¦¸ñ
¸®´ª½º¼­¹ö°ü¸®½Ç¹«¹ÙÀ̺ív3.0


ÀúÀÚ ¹Ú¼º¼ö
ÆäÀÌÁö¼ö 2,032 pages
Æì³½°÷(ÁÖ)¼öÆÛÀ¯Àú

¸®´ª½º ¼­¹ö ±¸Ãà ¹× °ü¸®¿¡ ÇÊ¿äÇÑ °ÅÀÇ ¸ðµç ºÎºÐ°ú Àü¹ÝÀûÀÎ ½Ç¹« ±â¹ýµéÀ» ´Ù·ç°í ÀÖÀ½
Ã¥Á¦¸ñ
¸®´ª½ºº¸¾È°ü¸®½Ç¹«(2/E)


ÀúÀÚÈ«¼®¹ü
ÆäÀÌÁö¼ö 820 pages
Æì³½°÷(ÁÖ)¼öÆÛÀ¯Àú

¸®´ª½º º¸¾È¿¡ °ü·ÃµÈ ³»¿ëÀ» Á¾ÇÕÀûÀ¸·Î ¾´ Ã¥. ÀúÀÚÀÇ ¸í¼º ±×´ë·Î º¸¾È°ü·ÃÀÇ ¸ðµç ³»¿ëÀÌ ±×´ë·Î ´ã°ÜÁ® ÀÖ´Ù.
 
 
    µ¥ÀÌŸº£À̽º>>MySQL>>Tip&Tech


MySQL ÆÁ Á¤¸®
 


MySQL °ü·Ã
root¾ÏÈ£º¯°æ¼³Á¤
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
This is done with:
/usr/bin/mysqladmin -u root -p password 'new-password'
/usr/bin/mysqladmin -u root -h ns.dbakorea.pe.kr -p password 'new-password'

½©¿¡¼­´Â mysqlÀÌ µÇ´Âµ¥ PHP¿¡¼­ mysql.sock error¸¦ ³»¸é¼­ MySQLÀÌ ¾ÈµÇ´Â °æ¿ì
mysql.sockÀº /tmp ¾Æ´Ï¸é /var/lib/mysql¿¡ »ý±â°Ô µÈ´Ù.
³ªÀÇ °æ¿ì, /var/lib/mysql¿¡ mysql.sockÆÄÀÏÀÌ Àִµ¥ PHP¿¡¼­´Â /tmp¿¡¼­ ãÀ¸·ÁÇϸ鼭 ¿¡·¯¸¦ ¹ß»ýÇß´Ù.
/usr/bin/safe_mysqldÆÄÀÏ¿¡¼­ ´ÙÀ½°ú °°ÀÌ ¼öÁ¤ÇÑ´Ù.
ÁÖ¼®(#)ÀÌ ´Þ¸° °ÍÀÌ ¿ø·¡°ÍÀÌ°í ±× ¹Ø¿¡ Àִ°ÍÀÌ ¼öÁ¤ÇÑ °ÍÀÌ´Ù.

# MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/var/lib/mysql/mysql.sock}
MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/tmp/mysql.sock}

À§¿Í °°ÀÌ ÇÏ´Ï /usr/bin/mysqlÀÌ /var/lib/mysql/mysql.sock¿¡¼­ ¼ÒÄÏÆÄÀÏÀ» ãÀ¸·Á Çß´Ù.
socket fileÀ» ÁöÁ¤ÇÏ´Â --socketÀ̶ó´Â ¿É¼ÇÀ¸·Î ´ÙÀ½°ú °°ÀÌ ÁöÁ¤ÇÏ¸é µÈ´Ù.

mysql --socket=/tmp/mysql.sock -u dbakorea -p db_test

ÇÏÁö¸¸ mysql½ÇÇà½Ã¸¶´Ù ÀÌ·¸°Ô ½áÁà¾ßÇÑ´Ù´Â °ÍÀÌ »ó´çÈ÷ ±ÍÂú´Ù. ¿É¼ÇÀÌ ¹Ù·Î Àû¿ëµÇ°Ô ¼³Á¤ÇÏÀÚ.
mysqlÀº ¼³Á¤»çÇ×À» ´ÙÀ½ 3°¡Áö ÆÄÀÏ¿¡¼­ °Ë»öÇÑ´Ù.

/etc/my.cnf            global options(MySQL ÀüüÀûÀ¸·Î »ç¿ëµÇ´Â ¿É¼Ç Á¤ÀÇ)
mysql-data-dir/my.cnf  ƯÁ¤ DB¿¡ Àû¿ëµÇ´Â option (/var/lib/mysql/my.cnf)
~/.my.cnf              »ç¿ëÀÚ °¢°¢ÀÇ ¼³Á¤('~'¹®ÀÚ´Â »ç¿ëÀÚÀÇ È¨µð·ºÅ丮´Â ÀǹÌ)

/usr/share/mysqlµð·ºÅ丮¿¡ ¿¹Á¦°¡ ÀÖÀ¸¹Ç·Î Âü°íÇÑ´Ù.
¼ÒÄÏÆÄÀÏÀÇ ÁöÁ¤Àº ´ÙÀ½ÁÙÀ» ³Ö¾îÁÖ¸é µÈ´Ù.

socket          = /tmp/mysql.sock


== /etc/my.cnf¿¹ ==
# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock


# root¾ÏÈ£¼³Á¤
% mysqladmin -u root password 'º¯°æ¾ÏÈ£'  root·Î ·Î±×ÀÎÇÏ¿© ÇØ¾ßÇÔ
or
% mysqladmin -u root -p±âÁ¸¾ÏÈ£ password 'º¯°æ¾ÏÈ£'  root·Î ·Î±×ÀÎÇÏ¿© ÇØ¾ßÇÔ
 
DB»ý¼º
mysql> create database DB¸í ( or % mysqladmin -u root -p create DB¸í )

DB»èÁ¦
mysql> drop database DB¸í

MySQL ¿¬°á
mysql -u »ç¿ëÀÚ -p DB¸í ( or % mysqladmin -u root -p drop DB¸í )

µ¥ÀÌÅÍÆÄÀÏ ½ÇÇà(sql*loader±â´É)
mysql>load data local infile "µ¥ÀÌÅÍÆÄÀÏ" into table Å×À̺í¸í ;
µ¥ÀÌÅÍÆÄÀÏ¿¡¼­ Ä÷³±¸ºÐÀº Åǹ®ÀÚ, Null°ªÀº /n·Î ÀÔ·Â

ÁúÀÇ ÆÄÀÏ ½ÇÇà
½©ÇÁ·ÒÇÁÆ®»ó¿¡¼­ 
mysql -u »ç¿ëÀÚ -p DB¸í < ÁúÀÇÆÄÀÏ
or
mysqlÇÁ·ÒÇÁÆ®»ó¿¡¼­
mysql> source ÁúÀÇÆÄÀÏ

½©ÇÁ·ÒÇÁÆ®»ó¿¡¼­ ÁúÀÇ ½ÇÇà
dbakorea@lion board]$ mysql mysql -u root -pxxxx -e \
>            "INSERT INTO db VALUES(
>            'localhost', 'aaa', 'aaa',
>            'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')"


»ç¿ëÀÚ »ý¼º & »ç¿ëÀÚ¿¡°Ô DBÇÒ´ç
shell> mysql --user=root -p mysql

mysql> INSERT INTO user VALUES('localhost','»ç¿ëÀÚ',PASSWORD('ºñ¹Ð¹øÈ£'),'Y','Y','Y','Y','Y','Y','Y',
'Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','»ç¿ëÀÚ',PASSWORD('ºñ¹Ð¹øÈ£'),'Y','Y','Y','Y','Y','Y','Y','Y','Y',
'Y','Y','Y','Y','Y');

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
 VALUES ('localhost','DB¸í','»ç¿ëÀÚ','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) 
VALUES('%','DB¸í','»ç¿ëÀÚ','Y','Y','Y','Y','Y','Y');

mysql> FLUSH PRIVILEGES; (or shell prompt: mysqladmin -u root -pxxxx reload)


DB, Å×À̺í¸ñ·Ïº¸±â
mysql> show databases;
mysql> show tables;

»ç¿ë DB¼±ÅÃ
mysql> use DB¸í

Å×ÀÌºí±¸Á¶
mysql> describe Å×À̺í¸í

À妽º º¸±â
show index from Å×À̺í¸í

Å×À̺í¸í º¯°æ
mysql> rename table test to test2;

Á¢¼Ó
mysql {-h Á¢¼ÓÈ£½ºÆ®} -u »ç¿ëÀÚ -p »ç¿ëDB
-h·Î ´Ù¸¥ ¼­¹ö¿¡ Á¸ÀçÇÏ´Â MySQLÁ¢¼Ó½Ã ´ÙÀ½°ú °°ÀÌ MySQL DB¿¡ ¼³Á¤ÇØÁà¾ß ÇÑ´Ù.
mysql> INSERT INTO user VALUES('Á¢±ÙÀ» Çã¿ëÇÒ È£½ºÆ®ip','»ç¿ëÀÚ',PASSWORD('ºñ¹Ð¹øÈ£'),'Y','Y','Y',
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
 VALUES('Á¢±ÙÀ» Çã¿ëÇÒ È£½ºÆ®ip','»ç¿ëDB','»ç¿ëÀÚ','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES; or ½¯ÇÁ·ÒÇÁÆ®»ó¿¡¼­ % mysqladmin -u root -p flush-privileges

¹öÀüüũ
mysql> select version();

°Ë»öÁ¶°Ç(where)
regular expressionÀ» Áö¿øÇÏ´Ù´Ï ½Å±âÇϱº..
mysql> select * from work where ¿­¸í regexp "Á¤±ÔÇ¥Çö½Ä";

Ä÷³ÀÇ Ãß°¡, »èÁ¦(¿À¶óŬ°ú µ¿ÀÏ)
alter table Å×À̺í¸í add Ä÷³¸í µ¥ÀÌÅÍŸÀÔ;
alter table Å×À̺í¸í del Ä÷³¸í;


¹é¾÷ & º¹±¸
mysqldump {-h È£½ºÆ®} -u »ç¿ëÀÚ -p DB¸í > ¹é¾÷ÆÄÀÏ
mysql {-h È£½ºÆ®} -u »ç¿ëÀÚ -p DB¸í < ¹é¾÷ÆÄÀÏ

mysqldump -u root -p --opt db_dbakorea > dbakorea.sql
mysqldump -u root -p --opt db_board | mysql ---host=remote-host -C database (»óÀÌÇÑ ¸Ó½®)
mysql -u dbakorea -p db_dbakorea < dbakorea.sql       


mysqldump -u root -p --opt db_dbakorea | mysql ---host=ns.dbakorea.pe.kr -C db_dbakorea 

Å×ÀÌºí °Ë»ç
isamchk

¿À¶óŬ sysdate¿Í µ¿ÀÏ
insert into test values('12', now());

À¯´Ð½º time()ÇÔ¼ö ¸®Åϰª »ç¿ë
FROM_UNIXTIME(954788684) 
UNIX_TIMESTAMP("2001-04-04 :04:04:04") 

MySQL µðÆúÆ® DB&·Î±×ÆÄÀÏ À§Ä¡
/var/lib/mysql
/var/libµð·ºÅ丮´Â ¿©·¯ ÇÁ·Î¼¼½ºµéÀÌ »ç¿ëÇÏ´Â µ¥ÀÌÅ͸¦ ÀúÀåÇÏ´Â ÀÏÁ¾ÀÇ ÆÄÀϽýºÅÛ»óÀÇ µ¥ÀÌÅͺ£ÀÌ
½º¶ó°í º¼ ¼ö ÀÖ´Ù.

replace
ÇØ´ç ·¹ÄÚµå Á¸ÀçÇϸé updateÇϰí, Á¸ÀçÇÏÁö ¾Ê´Â´Ù¸é insertÇÑ´Ù.(insert¹®¹ý°ú µ¿ÀÏ)
replace into test values('maddog','kang myung gyu')'

explain
explain ÁúÀǹ®: ÁöÁ¤ÇÑ ÁúÀǹ®ÀÌ ¾î¶»°Ô ½ÇÇàµÉ °ÇÁö¸¦ º¸¿©ÁÜ
mysql> explain select u.uid, u.name, a.name from sm_user u, sm_addr a where u.uid=a.uid;     
+-------+------+-----------------+-----------------+---------+-------+------+-------+
| table | type | possible_keys   | key             | key_len | ref   | rows | Extra |
+-------+------+-----------------+-----------------+---------+-------+------+-------+
| u     | ALL  | PRIMARY         | NULL            |    NULL | NULL  |  370 |       |
| a     | ref  | sm_addr_uid_idx | sm_addr_uid_idx |      11 | u.uid |   11 |       |
+-------+------+-----------------+-----------------+---------+-------+------+-------+
2 rows in set (0.01 sec)

temporary table
Å©±â°¡ Å« Å×ÀÌºí¿¡ ÀÖ´Â subset¿¡ ´ëÇÑ ÁúÀǶó¸é subsetÀ» temporary table¿¡ ÀúÀåÇÑ ÈÄ
ÁúÀÇÇÏ´Â °ÍÀÌ ´õ ºü¸¦ °æ¿ì°¡ ÀÖ´Ù.
create temporary table (...);
create temporary table (...) type=heap;	µð½ºÅ©°¡ ¾Æ´Ñ ¸Þ¸ð¸®¿¡ Å×ÀÌºí »ý¼º

==============================================================================
MySQL Ư¼ºÁ¤¸®
==============================================================================
primary keyÁö¿ø
index Áö¿ø(15°³Ä÷³, 256byte±îÁö)
foreign key, check, references±â´É Áö¿øÇÏÁö ¾ÊÀ½(Çü½Ä»ó ÀÖÀ½)

mysql> CREATE TABLE test (
        id INT NOT NULL,
        last_name CHAR(30) NOT NULL,
        first_name CHAR(30) NOT NULL,
        PRIMARY KEY (id),
        INDEX name (last_name,first_name));
        
MySQL¿¡¼­ÀÇ Stored Script°³³ä => SQL server language
commit-rollback°³³ä => lock tables
 
   
°­»ç ¼Ò°³
  °­¸í±Ô    
ȨÆäÀÌÁö : http://dbakorea.pe.kr/

e-mail : myunggyu°ñ»±ÀÌorgio.net
 
root ºÐ½Ç, ¼ÒÄÏ¿¡·¯µî ¿©·¯ ÆÁ

 
ȸ»ç¼Ò°³ | º¸µµ±â»ç | ä¿ë¾È³» | ±¤°í¾È³» | ÀÌ¿ë¾à°ü | °³ÀÎÁ¤º¸Ãë±Þ¹æÄ§ | Ã¥ÀÓÇѰè¿Í ¹ýÀû°íÁö | ºÒÆí»çÇ× ½Å°íÇϱâ | »çÀÌÆ®¸Ê
¤· ¼­¿ï : ¼­¿ïƯº°½Ã °­³²±¸ ³ëÇö·Î79±æ 66, 402È£(¿ª»ïµ¿,û¼Ûºôµù)  ¤· ºÎ»ê : ºÎ»ê±¤¿ª½Ã ÇØ¿î´ë±¸ ¿ìµ¿ 1470 ¿¡À̽ºÇÏÀÌÅ×Å©21 914È£
¤· ´ëÇ¥ : 1544-8128 ¤·ºÎ»êÁ÷Åë : 051-744-0077 ¤·¼­¿ïÁ÷Åë : 02-856-0055 ¤· FAX : 02-6280-2663
¤· »ç¾÷ÀÚ¹øÈ£ : 128-86-68239 ¤· Åë½ÅÆÇ¸Å¾÷ ½Å°í¹øÈ£ : 2013-ºÎ»ê±ÝÁ¤-0035È£ ¤· °³ÀÎÁ¤º¸Ã¥ÀÓÀÚ : ÀÌÀç¼®
¤· »óÈ£¸í : (ÁÖ) ¼öÆÛÀ¯Àú ¤· ´ëÇ¥ÀÚ : ¹Ú¼º¼ö
Copyright ¨Ï (ÁÖ)¼öÆÛÀ¯Àú. All rights reserved.