ʸ»úÎó¤Ï ' ¤Þ¤¿¤Ï " ¤Ç³ç¤é¤ì¤Þ¤¹¡£
\ ¤Ï¥¨¥¹¥±¡¼¥×ʸ»ú¤Ç¤¹¡£¼¡¤Î¥¨¥¹¥±¡¼¥×ʸ»ú¤¬Ç§¼±¤µ¤ì¤Þ¤¹:
\0
\n
\t
\r
\b
\'
'
ʸ»ú¡£
\"
"
ʸ»ú¡£
\\
\
ʸ»ú¡£
\%
%
ʸ»ú¡£¤³¤ì¤Ï %
¤ò¸¡º÷¤¹¤ë¤¿¤á¤Ë¡¢¥ï¥¤¥ë¥É¥«¡¼¥Éʸ»úÎóÃæ
¤Ç»ÈÍѤµ¤ì¤Þ¤¹¡£
\_
_
ʸ»ú¡£¤³¤ì¤Ï _
¤ò¸¡º÷¤¹¤ë¤¿¤á¤Ë¡¢¥ï¥¤¥ë¥É¥«¡¼¥Éʸ»úÎóÃæ
¤Ç»ÈÍѤµ¤ì¤Þ¤¹¡£
' ¤Ç»Ï¤Þ¤ëʸ»úÎóÃæ¤Î ' ¤Ï '' ¤È¤·¤Æ½ñ¤¤Þ¤¹¡£ " ¤Ç»Ï¤Þ¤ëʸ»úÎóÃæ¤Î " ¤Ï "" ¤È¤·¤Æ½ñ¤¤Þ¤¹¡£
¤É¤Î¤è¤¦¤Ë¤½¤ì¤¬Æ¯¤¯¤«¤ò¼¨¤¹¤¤¤¯¤Ä¤«¤Î select ¤ÎÎã¡£
MySQL> select 'hello', "'hello'", '""hello""', '''h''e''l''l''o''', "hel""lo"; 1 rows in set (0.00 sec) +-------+---------+-----------+-------------+--------+ | hello | 'hello' | ""hello"" | 'h'e'l'l'o' | hel"lo | +-------+---------+-----------+-------------+--------+ | hello | 'hello' | ""hello"" | 'h'e'l'l'o' | hel"lo | +-------+---------+-----------+-------------+--------+
mysql> select 'hello', "hello", '""hello""', "'ello", 'e''l''lo', '\'hello'; 1 rows in set (0.00 sec) +-------+-------+-----------+-------+--------+--------+ | hello | hello | ""hello"" | 'ello | e'l'lo | 'hello | +-------+-------+-----------+-------+--------+--------+ | hello | hello | ""hello"" | 'ello | e'l'lo | 'hello | +-------+-------+-----------+-------+--------+--------+
mysql> select "This\nIs\nFour\nlines"; 1 rows in set (0.00 sec) +--------------------+ | This Is Four lines | +--------------------+ | This Is Four lines | +--------------------+
¥Ð¥¤¥Ê¥ê¥Ç¡¼¥¿¤ò BLOB ¤ËÁÞÆþ¤·¤¿¤¤¾ì¹ç¡¢¼¡¤Îʸ»ú¤ò¥¨¥¹¥±¡¼¥×¥·¡¼¥±¥ó¥¹¤Ç ɽ¤¹É¬Íפ¬¤¢¤ê¤Þ¤¹:
\0
\
'
"
'
Æâ¤Ç¤Î "
¤È '
Æâ¤Ç¤Î "
¤Ï¥¨¥¹¥±¡¼¥×¤·¤Æ¤Ï
¤¤¤±¤Þ¤»¤ó¡£
C ¥³¡¼¥É¤ò½ñ¤¯¾ì¹ç¡¢INSERT
Àá¤Çʸ»ú¤ò¥¨¥¹¥±¡¼¥×¤¹¤ë¤¿¤á¤Ë¡¢C API
´Ø¿ô mysql_escape_string(char *to,char *from,uint length)
¤ò»ÈÍÑ
¤Ç¤¤Þ¤¹ ('to' ¤Ï¾¯¤Ê¤¯¤È¤â from ¤è¤ê2ÇÜÂ礤¤¤³¤È¤ËÃí°Õ¤·¤Æ¤¯¤À¤µ¤¤)¡£
perl ¤Ç¤Ï quote
´Ø¿ô¤ò»ÈÍѤǤ¤Þ¤¹¡£
¾åµ¤ÎÆüìʸ»ú¤Î°ì¤Ä¤ò»ý¤Ä²ÄǽÀ¤Î¤¢¤ëÁ´¤Æ¤Îʸ»úÎó¤Ë¤Ä¤¤¤Æ¡¢¥¨¥¹¥±¡¼¥×´Ø ¿ô¤ò¼Â¹Ô¤¹¤Ù¤¤Ç¤¹¡ª
À°¿ô¤Ï¿ô»ú¤Î¹àÌܤǤ¹¡£ÉâÆ°¾®¿ôÅÀ¤Ï .
¤Ç¾®¿ô¤òʬ³ä¤·¤Þ¤¹¡£
ÀµÅö¤Ê¿ôÃͤÎÎã: 1221
, 294.42
, -32032.6809e+10
¡£
NULL
¥Æ¥¥¹¥È¥Õ¥¡¥¤¥ë½ÐÎÏ·Á¼°¤ò»ÈÍѤ¹¤ë»þ¡¢NULL
¤Ï \N
¤È¤·¤Æɽ
¤µ¤ì¤Þ¤¹¡£ ¡ÖLOAD DATA INFILE ¹½Ê¸¡×ÀỲ¾È
¥Ç¡¼¥¿¥Ù¡¼¥¹¡¢¥Æ¡¼¥Ö¥ë¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¤½¤·¤Æ¹àÌܤÎ̾Á°¤Ï¡¢MySQL ¤Ç¤ÏÁ´¤ÆƱ¤¸µ¬Â§¤Ë´ð¤Å¤¤Þ¤¹¡£
̾Á°¤Ï¡¢¥Ç¥Õ¥©¥ë¥Èʸ»ú¥»¥Ã¥È¤Î¥¢¥ë¥Õ¥¡¥Ù¥Ã¥È¤È¿ô»ú¤ò»ÈÍѤ·¤Þ¤¹¡£¤³¤ì¤Ï¥Ç ¥Õ¥©¥ë¥È¤Ç¤Ï ISO-8859-1 Latin1 ¤Ç¤¹¤¬¡¢MySQL ¥³¥ó¥Ñ¥¤¥ë»þ¤ËÊѹ¹ ¤Ç¤¤Þ¤¹¡£
MySQL ¤Ï¡¢²¿¤«¤¬Ì¾Á°¤«¿ôÃͤ«¤ò·èÄê¤Ç¤¤ë¤³¤È¤¬É¬Íפʤ¿¤á¡¢¼¡¤Î Æüì¤Ê¾ì¹ç¤¬È¯À¸¤·¤Þ¤¹¡£
1e
¤Î¤è¤¦¤Ê̾Á°¤ò»ÈÍѤ¹¤ë¤³¤È¤Ï´«¤á¤é¤ì¤Þ¤»¤ó¡£¤³¤ì¤Ï 1e+1
¤Î¤è¤¦¤Êɽ¸½¤Ï¡¢É½¸½ 1e + 1
¤Þ¤¿¤Ï¿ôÃÍ 1e+1
¤Î¤è¤¦¤Ë²ò¼á¤µ
¤ì¤ë¤¿¤á¤Ç¤¹¡£
̾Á°Ãæ¤Ë .
¤ä @
¤Î¤è¤¦¤Êµ¹æʸ»ú¤Ïµö¤µ¤ì¤Þ¤»¤ó¡£¤³¤ì¤é¤Ï
MySQL ¤Î³ÈÄ¥¤Ç»ÈÍѤµ¤ì¤ë¤¿¤á¤Ç¤¹¡£
MySQL ¤Ç¤Ï¼¡¤Î¹½Ê¸¤Î¤É¤ì¤«¤Ç¹àÌܤò»²¾È¤Ç¤¤Þ¤¹:
'column' ¤Þ¤¿¤Ï 'table.column' ¤ò»ÈÍѤ¹¤ë¾ì¹ç¡¢»ÈÍѤµ¤ì¤¿¥Æ¡¼¥Ö¥ë¤Î´Ö¤Ç ̾Á°¤¬¥æ¥Ë¡¼¥¯¤Ç¤Ê¤±¤ì¤Ð¥¨¥é¡¼¤Ë¤Ê¤ê¤Þ¤¹¡ª
¼¡¤Î¹àÌÜ·¿¤¬¥µ¥Ý¡¼¥È¤µ¤ì¤Þ¤¹:
Name | Description | Size |
TINYINT[(M)] [UNSIGNED] [ZEROFILL] | ¤È¤Æ¤â¾®¤µ¤¤À°¿ô¡£Éä¹æ¤Ä¤¤ÎÈÏ°Ï¤Ï -128¡Á127¡£Éä¹æ¤Ê¤·¤ÎÈÏ°Ï¤Ï 0¡Á255¡£ | 1 |
SMALLINT[(M)]. [UNSIGNED] [ZEROFILL] | ¾®¤µ¤¤À°¿ô¡£Éä¹æ¤Ä¤¤ÎÈÏ°Ï¤Ï -32768¡Á32767¡£Éä¹æ¤Ê¤·¤ÎÈÏ°Ï¤Ï 0¡Á65535¡£ | 2 |
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] | Ãæ´Ö¤ÎÀ°¿ô¡£Éä¹æ¤¢¤ê¤ÎÈÏ°Ï¤Ï -8388608¡Á8388607¡£Éä¹æ¤Ê¤·¤ÎÈÏ°Ï¤Ï 0¡Á 16777215¡£ | 3 |
INT[(M)] [UNSIGNED] [ZEROFILL] | Ä̾ï¤ÎÀ°¿ô¡£Éä¹æ¤¢¤ê¤ÎÈÏ°Ï¤Ï -2147483648¡Á2147483647¡£Éä¹æ¤Ê¤·¤ÎÈÏ°Ï¤Ï 0¡Á 4294967295¡£ | 4 |
BIGINT[(M)] [UNSIGNED] [ZEROFILL] | Â礤¤À°¿ô¡£Éä¹æ¤¢¤ê¤ÎÈÏ°Ï¤Ï -9223372036854775808¡Á9223372036854775807¡£ Éä¹æ¤Ê¤·¤ÎÈÏ°Ï¤Ï 0¡Á18446744073709551615¡£Á´¤Æ¤Î±é»»¤ÏÉä¹çÉÕ¤ BIGINT ¤Þ ¤¿¤Ï DOUBLE ¤Ç¹Ô¤ï¤ì¤ë¤¿¤á¡¢Éä¹ç̵¤·¤Î 9223372036854775807 (63 bits) ¤è ¤ê¤âÂ礤ÊÀ°¿ô¤ò¥Ó¥Ã¥È´Ø¿ô°Ê³°¤Ç»ÈÍѤ¹¤Ù¤¤Ç¤Ï¤¢¤ê¤Þ¤»¤ó¡ª | 8 |
FLOAT(Precision) | ¾®¤µ¤¤ÉâÆ°¾®¿ôÅÀ¿ô¡£Precision ¤Ï 4 ¤Þ¤¿¤Ï 8 ¤Ë¤Ç¤¤Þ¤¹¡£FLOAT(4) ¤ÏñÀº ÅÙ¿ô¤Ç¡¢FLOAT(8) ¤ÏÇÜÀºÅÙ¿ô¤Ç¤¹ (DOUBLE ¥¨¥ó¥È¥ê¤ò»²¾È¤·¤Æ¤¯¤À¤µ¤¤)¡£¤³ ¤Î¹½Ê¸¤Ï ODBC ¸ß´¹¤Ç¤¹¡£ÈÏ°Ï¤Ï -3.402823466E+38F¡Á-1.175494351E-38, 0, -1.175494351E-38¡Á3.402823466E+38F ¤Ç¤¹¡£ | 4 |
FLOAT[(M,D)] | ¾®¤µ¤¤ÉâÆ°¾®¿ôÅÀ¿ô¡£Éä¹æ¤Ê¤·¤Ë¤Ï¤Ç¤¤Þ¤»¤ó¡£ÈÏ°Ï¤Ï -3.402823466E+38F¡Á -1.175494351E-38, 0, -1.175494351E-38¡Á3.402823466E+38F ¤Ç¤¹¡£ | 4 |
DOUBLE PRECISION[(M,D)] | Ä̾ï¤ÎÉâÆ°¾®¿ôÅÀ¿ô¡£Éä¹æ¤Ê¤·¤Ë¤Ï¤Ç¤¤Þ¤»¤ó¡£ÈÏ°Ï¤Ï -1.7976931348623157E+308¡Á-2.2250738585072014E-308, 0, 2.2250738585072014E-308¡Á1.7976931348623157E+308 ¤Ç¤¹¡£ | 8 |
REAL[(M,D)] | DOUBLE ¤ÈƱ¤¸ | 8 |
DECIMAL [(M,D)] | Èó¥Ñ¥Ã¥¯ÉâÆ°¾®¿ôÅÀ¿ô¡£Éä¹æ¤Ê¤·¤Ë¤Ï¤Ç¤¤Þ¤»¤ó¡£¸½ºß¤Ï double ¤ÎºÇÂçÈϰϤΠÈϰϤǤ¹¡£CHAR ¹àÌܤΤ褦¤Ë¿¶Éñ¤¤¤Þ¤¹¡£ | M+D |
NUMERIC [(M,D)] | DECIMAL ¤ÈƱ¤¸ | M+D |
TIMESTAMP [(M)] | ¼«Æ°Åª¤Ê¥¿¥¤¥à¥¹¥¿¥ó¥×¡£Â¿¤¯¤Î TIMESTAMP ¹àÌܤò»ý¤Ä¾ì¹ç¡¢ºÇ½é¤Î°ì¤Ä¤À¤± ¤¬¼«Æ°Åª¤Ë¤Ê¤ê¤Þ¤¹¡£ | 4 |
DATE | ÆüÉÕ¾ðÊó¤ò³ÊǼ¤¹¤ë¤¿¤á¤Î·¿¡£"YYYY-MM-DD" ¹½Ê¸¤ò»ÈÍѤ·¤Þ¤¹¤¬¡¢¿ôÃͤޤ¿¤Ï ʸ»úÎó¤Ç¹¹¿·¤Ç¤¤Þ¤¹¡£¾¯¤Ê¤¯¤È¤â¼¡¤Î¹½Ê¸¤òÍý²ò¤·¤Þ¤¹: 'YY-MM-DD', 'YYYY-MM-DD', 'YYMMDD' ¤½¤·¤Æ´°Á´¤Ê¥¿¥¤¥à¥¹¥¿¥ó¥× (YYYYMMDDHHMMDD)¡£ÈÏ°Ï¤Ï 0000-00-00 ¤«¤é 9999-12-31 ¤Ç¤¹¡£ | 3 |
TIME | »þ¹ï¾ðÊó¤ò³ÊǼ¤¹¤ë¤¿¤á¤Î·¿¡£"HH:MM:SS" ¹½Ê¸¤ò»ÈÍѤ·¤Þ¤¹¤¬¡¢¿ôÃͤޤ¿¤Ïʸ »úÎó¤Ç¹¹¿·¤Ç¤¤Þ¤¹¡£¾¯¤Ê¤¯¤È¤â¼¡¤Î·Á¼°¤¬Íý²ò¤Ç¤¤Þ¤¹: 'HH:MM:SS', 'HHMMSS', 'HHMM', 'HH'¡£ | 3 |
DATETIME | ÆüÉդȻþ¹ï¾ðÊó¤ò³ÊǼ¤¹¤ë¤¿¤á¤Î·¿¡£·Á¼° "YYYY-MM-DD HH:MM:SS"¡£8¥Ð¥¤¥È»È ÍѤ·¤Þ¤¹¡£ÈÏ°Ï¤Ï '0000-01-01 00:00:00'¡Á'9999-12-31 23:59:59' ¤Ç¤¹¡£ | 8 |
YEAR | ǯ¤ò³ÊǼ¤¹¤ë¤¿¤á¤Î·¿¡£·Á¼° "YYYY" ¤Þ¤¿¤Ï "YY"¡£1¥Ð¥¤¥È»ÈÍѤ·¤Þ¤¹¡£ÈÏ°Ï¤Ï 0, 1901-2155 ¤Ç¤¹¡£ÈÏ°Ï 00-69 ¤Î2·å¤Îǯ¤Ï 2000-2069 ¤È¤ß¤Ê¤µ¤ì¡¢Àµ¤·¤¯¥½¡¼ ¥È¤µ¤ì¤Þ¤¹¡£(MySQL 3.22 ¤Î·¿) | 1 |
CHAR(M) [binary] | ¸ÇÄêĹʸ»úÎó¡£¾ï¤Ë¸ÇͤÎŤµ¤Þ¤Ç¶õÇò¤¬Ëä¤á¤é¤ì¤Þ¤¹¡£ÈÏ°Ï¤Ï 1¡Á255 ʸ»ú¤Ç ¤¹¡£½ª¤ê¤Î¶õÇò¤ÏÆɤ߽Ф·»þ¤Ëºï½ü¤µ¤ì¤Þ¤¹¡£binary ¥¡¼¥ï¡¼¥É¤¬Í¿¤¨¤é¤ì¤Ê ¤¤¾ì¹ç¡¢¥½¡¼¥È¤ÈÈæ³Ó¤Ï¥±¡¼¥¹¤Ë°Í¸¤·¤Þ¤»¤ó¡£ | M |
VARCHAR(M) [binary] | ²ÄÊÑĹʸ»úÎ󡣤½¤ÎŤµ¤Ç³ÊǼ¤µ¤ì¤Þ¤¹¡£Á´¤Æ¤Î½ª¤ï¤ê¤Î¶õÇò¤Ï³ÊǼ»þ¤Ëºï½ü¤µ ¤ì¤Þ¤¹¡£ºÇÂçÈÏ°Ï¤Ï 1¡Á255 ʸ»ú¤Ç¤¹¡£binary ¥¡¼¥ï¡¼¥É¤¬Í¿¤¨¤é¤ì¤Ê¤¤¾ì¹ç¡¢ ¥½¡¼¥È¤ÈÈæ³Ó¤Ï¥±¡¼¥¹¤Ë°Í¸¤·¤Þ¤»¤ó¡£ | L+1 |
TINYTEXT and TINYBLOB |
ºÇÂçĹ 255 ʸ»ú¤Î TEXT /BLOB ¡£ | L+1 |
TEXT and BLOB |
ºÇÂçĹ 65535 ʸ»ú¤Î TEXT /BLOB ¡£ | L+2 |
MEDIUMTEXT and MEDIUMBLOB |
ºÇÂçĹ 16777216 ʸ»ú¤Î TEXT /BLOB ¡£ | L+3 |
LONGTEXT and LONGBLOB |
ºÇÂçĹ 4294967295 ʸ»ú¤Î TEXT /BLOB ¡£ | L+4 |
ENUM('value','value2',...) | ʸ»úÎ󥪥֥¸¥§¥¯¥È¡£µö²Ä¤µ¤ì¤¿ÃÍ¥»¥Ã¥È¤Î°ì¤Ä¡Ê¤Þ¤¿¤Ï NULL¡Ë¤Î¤ß»ý¤Ä¤³¤È ¤¬¤Ç¤¤Þ¤¹¡£ ¡Ö¥Ç¡¼¥¿·¿¤Ë¤Ä¤¤¤Æ¤µ¤é¤Ë¾ÜºÙ¡×ÀỲ¾È ¡£ | 1 ¤Þ¤¿¤Ï 2 |
SET('value','value2',...) | ʸ»úÎ󥪥֥¸¥§¥¯¥È¡£µö²Ä¤µ¤ì¤¿ÃÍ¥»¥Ã¥È¤Î°ì¤Ä¤Þ¤¿¤ÏÊ£¿ô¤ÎÃͤò»ý¤Ä¤³¤È¤¬¤Ç ¤¤Þ¤¹¡£ ¡Ö¥Ç¡¼¥¿·¿¤Ë¤Ä¤¤¤Æ¤µ¤é¤Ë¾ÜºÙ¡×ÀỲ¾È ¡£ | 1-8 |
¾å¤Î¥Æ¡¼¥Ö¥ë¤Ç L ¤Ï¥¤¥ó¥¹¥¿¥ó¥¹¤Î¼ÂºÝ¤ÎŤµ¤ò¡¢M ¤ÏºÇÂçŤò°ÕÌ£¤·¤Þ¤¹¡£¤½¤·¤Æ¡¢ "abcd" ¤Î L+1 ¤Ï¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤Î 5 ¥Ð¥¤¥È¤ò°ÕÌ£¤·¤Þ¤¹¡£
Ťµ¥Õ¥£¡¼¥ë¥É¤¬ L ¤Î¥Ç¡¼¥¿·¿¤ò»ÈÍѤ·¤¿¾ì¹ç¡¢²ÄÊÑĹ¥ì¥³¡¼¥É·Á¼°¤òÆÀ¤Þ¤¹¡£
Á´¤Æ¤ÎÀ°¿ô·¿¤Ï¥ª¥×¥·¥ç¥ó¤Î°ú¿ô unsigned
¤ò»ý¤Ä¤³¤È¤¬¤Ç¤¤Þ¤¹¡£¹àÌÜ
¤ÇÀµ¤Î¿ôÃͤÀ¤±¤òµö²Ä¤·¤¿¤¤»þ¤ä¡¢¹àÌܤ˾¯¤·Â礤ʿôÃÍÈϰϤòɬÍפʻþ¤Ë¡¢¤³¤ì
¤ò»ÈÍѤǤ¤Þ¤¹¡£
¤Þ¤¿¡¢Á´¤Æ¤ÎÀ°¿ô¹àÌܤˤĤ¤¤Æ¤Î¥ª¥×¥·¥ç¥ó¤Î°ú¿ô ZEROFILL
¤Ï¡¢¹àÌܤϺÇ
ÂçĹ¤Þ¤Ç 0 ¤ÇËä¤á¤é¤ì¤ë¤³¤È¤ò°ÕÌ£¤·¤Þ¤¹¡£
ºÇÂçɽ¼¨¥µ¥¤¥º¤È¾®¿ô¤Ï¡¢À°·Á¤ÈºÇÂç¹àÌÜÉý¤Î·×»»¤Ë¤è¤ê¤Þ¤¹¡£
Èϰϳ°¤ÎÀ°¿ôÃͤò³ÊǼ¤·¤¿»þ¡¢MySQL ¤ÏºÇÂç(¤Þ¤¿¤ÏºÇ¾®)¤Î²Äǽ¤ÊÃÍ
¤ò³ÊǼ¤·¤Þ¤¹¡£ALTER TABLE
¤Þ¤¿¤Ï LOAD DATA INFILE
»þ¡¢¤³¤ì
¤é¤ÎÊÑ´¹¤Ï '·Ù¹ð' ¤È¤·¤ÆÆÀ¤é¤ì¤Þ¤¹¡£INSERT ¤È UPDATE ¤â·Ù¹ð¤òÊÖ¤»¤ë¤è¤¦
¤Ë¤¹¤ë¤³¤È¤Ï TODO ¾å¤Ë¤¢¤ê¤Þ¤¹¡£¤·¤«¤·¡¢¤³¤ì¤Ï¼¡¤Î¥×¥í¥È¥³¥ëÊѹ¹¤Ë¥¹¥±¥¸¥å¡¼
¥ë¤µ¤ì¤Æ¤¤¤Þ¤¹¡£
Î㤨¤Ð¡¢-999999999999999
¤ò int ¹àÌܤ˳ÊǼ¤¹¤ë»þ¡¢ÃͤÏ
-2147483648
¤È¤Ê¤ê¤Þ¤¹¡£¤½¤·¤Æ 9999999999999999
¤Ï
2147483647
¤È¤Ê¤ê¤Þ¤¹¡£
¤½¤·¤Æ¡¢int
¤¬Éä¹æ̵¤·¤Î¾ì¹ç¡¢¾å¤Î³ÊǼ¤µ¤ì¤ëÃÍ¤Ï 0
¤È
4294967296
¤Ë¤Ê¤ê¤Þ¤¹¡£
Ʊ¤¸µ¬Â§¤¬Á´¤Æ¤Î¾¤ÎÀ°¿ô·¿¤Ë¤âŬÍѤµ¤ì¤Þ¤¹¡£
³ä¤êÅö¤Æ¤é¤ì¤¿Îΰè¤òĶ¤¨¤ë int(4) ¹àÌܤΥǡ¼¥¿¤òÊÖ¤¹»þ¡¢MySQL
¤Ï 9.99 ¤òÊÖ¤·¤Þ¤¹¡£¥ª¥Ú¥ì¡¼¥·¥ç¥ó¤¬ UPDATE
¤Î¾ì¹ç¤Ï¡¢·Ù¹ð¤¬½Ð¤µ
¤ì¤Þ¤¹¡£
decimal(4,2)
¤Î¤è¤¦¤Ê·¿¤Ï¡¢2·å¤Î¾®¿ôÃͤò»ý¤Ã¤¿ºÇÂç4ʸ»ú¤ò°ÕÌ£¤¹¤ë
¤³¤È¤ËÃí°Õ¤·¤Æ²¼¤µ¤¤¡£¤³¤ì¤Ï -.99
-> 9.99
¤ÎÈϰϤòÍ¿¤¨¤Þ¤¹¡£
¤¤¤¯¤Ä¤«¤Î´Ý¤á¤ÎÌäÂê¤ò²óÈò¤¹¤ë¤¿¤á¤Ë¡¢MySQL ¤ÏÉâÆ°¾®¿ôÅÀ¤Î¹àÌܤË
³ÊǼ¤¹¤ëÁ´¤Æ¤ò¾ï¤Ë¡¢¾®¿ô¤Î¿ô¤Ë°ìÃפ¹¤ë¤è¤¦¤Ë´Ý¤á¤Þ¤¹¡£¤³¤ì¤Ï¡¢
float(8,2)
Æâ¤Ë³ÊǼ¤µ¤ì¤ë 2.333
¤Ï 2.33
¤È¤·¤Æ³ÊǼ
¤µ¤ì¤ë¤³¤È¤ò°ÕÌ£¤·¤Þ¤¹¡£
TIMESTAMP
·¿
1970ǯ12·î1Æü 00:00 ¤«¤é 2106ǯ¤Î¤¤¤Ä¤«¤Þ¤Ç¤ÎÈϰϤò»ý¤Á¡¢1ÉäÎÀºÅ٤Ǥ¹¡£
TIMESTAMP ¹àÌܤϡ¢NULL
¤¬ÀßÄꤵ¤ì¤ë¤«¡¢¥¹¥Æ¡¼¥È¥á¥ó¥ÈÃæ¤Ç¹àÌܤ¬¹¹
¿·¤µ¤ì¤Ê¤±¤ì¤Ð INSERT
¤È UPDATE
¥¹¥Æ¡¼¥È¥á¥ó¥È¤Ç¼«Æ°Åª¤Ë¹¹
¿·¤µ¤ì¤Þ¤¹¡£¥¤¥ó¥Ç¥Ã¥¯¥¹(¤Î°ìÉô)¤Ë¤Ç¤¤Þ¤¹¡£Ãí°Õ: ¿¤¯¤Î timestamp ¹àÌÜ
¤¬¹Ô¤Ë¤¢¤ë¾ì¹ç¤Ï¡¢ºÇ½é¤Î timestamp ¹àÌܤÀ¤±¤¬¼«Æ°Åª¤Ë¹¹¿·¤µ¤ì¤Þ¤¹¡£Ç¤°Õ
¤Î timestamp ¹àÌÜ¤Ï NULL
¤òÀßÄꤹ¤ì¤Ð¡¢¸½ºß¤Î»þ¹ï¤ËÀßÄꤵ¤ì¤Þ¤¹¡£
ɽ¼¨¥µ¥¤¥º¤Ë°Í¸¤·¤Æ¡¢¼¡¤Î·Á¼°¤Î°ì¤Ä¤¬ÆÀ¤é¤ì¤Þ¤¹: "YYYY-MM-DD HH:MM:SS",
"YY-MM-DD HH:MM:SS", "YYYY-MM-DD" or "YY-MM-DD"¡£
TEXT
¤È BLOB
·¿
¤³¤ì¤é¤Ï¾å¸Â̵¤·¤Î²ÄÊÑŤò»ý¤Ä¤³¤È¤¬¤Ç¤¤ë¥ª¥Ö¥¸¥§¥¯¥È¤Ç¤¹¡£Á´¤Æ¤Î TEXT
¤È BLOB ¥ª¥Ö¥¸¥§¥¯¥È¤Ï¤½¤ÎŤµ(¥ª¥Ö¥¸¥§¥¯¥È¤Î·¿¤Ë°Í¸¤·¤Æ 1 ¤«¤é 4 ¥Ð¥¤
¥È)¤È¶¦¤Ë³ÊǼ¤µ¤ì¤Þ¤¹¡£»ÈÍѲÄǽ¤Ê TEXT
¤È BLOB
¤ÎºÇÂçĹ¤Ï¡¢
͸ú¤Ê¥á¥â¥ê¤È¥¯¥é¥¤¥¢¥ó¥È¥Ð¥Ã¥Õ¥¡¤Ë°Í¸¤·¤Þ¤¹¡£TEXT
¤È
BLOB
¤Î°ã¤¤¤Ï¡¢TEXT
¤Ï¥±¡¼¥¹¤Ë°Í¸¤·¤Ê¤¤¤Ç¥½¡¼¥È¤ÈÈæ³Ó¤µ¤ì¡¢
BLOB
¤Ï¥±¡¼¥¹¤Ë°Í¸¤·¤Æ(ʸ»ú¥³¡¼¥É¤Ç)Èæ³Ó¤µ¤ì¤ë¤³¤È¤À¤±¤Ç¤¹¡£
TEXT
¤È BLOB
¥ª¥Ö¥¸¥§¥¯¥È¤Ï¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ë¤Ï¤Ç¤¤Þ¤»¤ó¡£
BLOB ¤Ï¥Ð¥¤¥Ê¥êµðÂ祪¥Ö¥¸¥§¥¯¥È(binary large object)¤Ç¡¢Â礤ʥǡ¼¥¿¤òÊÝ »ý¤Ç¤¤Þ¤¹¡£4¼ïÎà¤Î BLOB ¤¬¤¢¤ê¤Þ¤¹ ¡Ö¹àÌÜ·¿¡×ÀỲ¾È ¡£Ä̾BLOB ¤ÏÀ© ¸Â̵¤·¤Î VARCHAR ¤È¸«¤Ê¤¹¤³¤È¤¬¤Ç¤¤Þ¤¹¡£
TEXT
¤Ï¡¢¥½¡¼¥È¤ÈÈæ³Ó¤¬¥±¡¼¥¹¤Ë°Í¸¤·¤Ê¤¤ BLOB
¤Ç¤¹¡£
BLOB
/TEXT
¹àÌܤϥá¥Ã¥»¡¼¥¸¥Ð¥Ã¥Õ¥¡¤è¤êÂ礤¯¤Ç¤¤Þ¤»¤ó¡£¥µ¡¼
¥Ð¤È¥¯¥é¥¤¥¢¥ó¥È¤Î¥á¥Ã¥»¡¼¥¸¥Ð¥Ã¥Õ¥¡¤òÊѹ¹¤¹¤ëɬÍפ¬¤¢¤ë¤³¤È¤ËÃí°Õ¤·¤Æ¤¯
¤À¤µ¤¤¡£ ¡ÖMySQL ¤Î¥Ð¥Ã¥Õ¥¡¥µ¥¤¥º¤ÎÊѹ¹ÊýË¡¡×ÀỲ¾È ¡£
MyODBC
¤Ï BLOB
¤ò LONGVARBINARY
¤È¤·¤Æ¡¢¤½¤·¤Æ
TEXT
¤ò LONGVARCHAR
¤È¤·¤ÆÄêµÁ¤·¤Þ¤¹¡£
BLOB
¤È TEXT
¹àÌܤÎÀ©¸Â:
BLOB
¤Þ¤¿¤Ï TEXT
¤Ï¥¤¥ó¥Ç¥Ã¥¯¥¹¤Þ¤¿¤Ï¥¤¥ó¥Ç¥Ã¥¯¥¹¤Î°ìÉô¤Ë
¤Ï¤Ç¤¤Þ¤»¤ó¡£
BLOB
¤Þ¤¿¤Ï TEXT
¤Î¥½¡¼¥È¤Þ¤¿¤Ï¥°¥ë¡¼¥×»þ¤Ë¤Ï¡¢BLOB ¤ÎºÇ½é
¤Î max_sort_length
(¥Ç¥Õ¥©¥ë¥È 1024) ¤À¤±¤¬»ÈÍѤµ¤ì¤Þ¤¹¡£¤³¤ÎÃͤϡ¢
mysqld ¥Ç¡¼¥â¥óµ¯Æ°»þ¤Ë -O
¥ª¥×¥·¥ç¥ó¤ÇÊѹ¹¤Ç¤¤Þ¤¹¡£
BLOB
/TEXT
¤ò´Þ¤ó¤Àɽ¸½¤Ç¥°¥ë¡¼¥×¤Ç¤¤Þ¤¹: SELECT
id,SUBSTR(blob,1,100) GROUP BY 2
BLOB
¤È TEXT
¤Ç¤Ï¡¢½ª¤ê¤Î¶õÇò¤Ï CHAR
¤ä
VARCHAR
¤Î¤è¤¦¤Ë¤Ï¥«¥Ã¥È¤µ¤ì¤Þ¤»¤ó¡£
ENUM
·¿
ʸ»úÎ󥪥֥¸¥§¥¯¥È¡£µö²Ä¤µ¤ì¤¿ÃÍ¥»¥Ã¥È¤Î°ì¤Ä¤À¤±¤ò»ý¤Ä¤³¤È¤¬¤Ç¤¤Þ¤¹¡£³Ê
Ǽ¤µ¤ì¤ëÃͤϥ±¡¼¥¹Èó°Í¸¤Ç¤¹¡£Â¸ºß¤·¤Ê¤¤Ãͤò³ÊǼ¤·¤è¤¦¤È¤¹¤ë¤È¡¢"" ¤¬³Ê
Ǽ¤µ¤ì¤Þ¤¹¡£¿ôÃÍʸ̮¤Ç»ÈÍѤµ¤ì¤¿¾ì¹ç¤Ï¡¢¤³¤Î¥ª¥Ö¥¸¥§¥¯¥È¤ÏÃÍ¥¤¥ó¥Ç¥Ã¥¯¥¹
¤òÊÖ/³ÊǼ¤·¤Þ¤¹¡£Í¸ú¤ÊÃͤ¬ 255 ¸Ä¤è¤ê¾¯¤Ê¤¤¾ì¹ç¤Ï¡¢¤³¤Î¥ª¥Ö¥¸¥§¥¯¥È¤Ï 1
¥Ð¥¤¥È¡¢¤½¤¦¤Ç¤Ê¤±¤ì¤Ð 2¥Ð¥¤¥ÈÀêͤ·¤Þ¤¹ (ºÇÂç¤Ç 65535 ¸Ä¤Î°Û¤Ê¤ëÃÍ)¡£À°
¿ô¤¬ ENUM
¤ËÃÖ¤«¤ì¤¿¾ì¹ç¡¢ºÇ½é¤¬ÈÖ¹æ 1 ¤È¤·¤Æ¿ô¤¨¤é¤ì¤ë¡¢Âбþ¤¹¤ë
ʸ»úÎó¤òÆÀ¤ë¤³¤È¤ËÃí°Õ¤·¤Æ²¼¤µ¤¤ (0 ¤Ï´Ö°ã¤Ã¤¿ enum ÃͤΤ¿¤á¤ËͽÌ󤵤ì¤Æ
¤¤¤Þ¤¹)¡£ENUM
·¿¤Ç¤Î¥½¡¼¥È¤Ï¡¢enum Æâ¤Îʸ»úÎó¤Î½ç¤Ë¤è¤Ã¤Æ¹Ô¤Ê¤ï¤ì
¤Þ¤¹¡£NOT NULL
¤ÈÀë¸À¤µ¤ì¤¿¾ì¹ç¡¢¥Ç¥Õ¥©¥ë¥ÈÃͤϺǽé¤ÎÃͤǤ¹¡£¤½¤¦
¤Ç¤Ê¤±¤ì¤Ð¥Ç¥Õ¥©¥ë¥ÈÃÍ¤Ï NULL
¤Ç¤¹¡£
Î㤨¤Ð¡¢¹àÌÜ test ENUM("one","two", "three")
¤Ï¼¡¤ÎÃͤÎǤ°Õ¤ò»ý¤Ä
¤³¤È¤¬¤Ç¤¤Þ¤¹:
NULL "one" "two" "three"
SET
·¿
ʸ»úÎ󥪥֥¸¥§¥¯¥È¡£µö²Ä¤µ¤ì¤¿ÃÍ¥»¥Ã¥È¤Î°ì¤Ä¤Þ¤¿¤ÏÊ£¿ô¤ò»ý¤Ä¤³¤È¤¬¤Ç¤¤Þ
¤¹¡£³ÆÃÍ¤Ï ',' ¤Ç¶èÀÚ¤é¤ì¤Þ¤¹¡£¿ôÃÍʸ̮¤Ç»È¤ï¤ì¤ë¤È¡¢¤³¤Î¥ª¥Ö¥¸¥§¥¯¥È¤Ï
»ÈÍѤµ¤ì¤¿ÃͤΥӥåȰÌÃÖ¤òÊÖ¤·/³ÊǼ¤·¤Þ¤¹¡£¤³¤Î¥ª¥Ö¥¸¥§¥¯¥È¤Ï (°Û¤Ê¤ëÃÍ
¤Î¿ô-1)/8+1 ¤ò 1,2,3,4 ¤Þ¤¿¤Ï 8 ¤Ë´Ý¤á¤¿¥Ð¥¤¥È¤òÀêͤ·¤Þ¤¹¡£64 ¸Ä°Ê¾å¤Î
°Û¤Ê¤ëÃͤϻý¤Æ¤Þ¤»¤ó¡£À°¿ô¤¬ SET
Æâ¤ËÃÖ¤«¤ì¤¿¾ì¹ç¡¢ºÇ½é¤Î¥Ó¥Ã¥È¤¬
ºÇ½é¤Îʸ»úÎó¤ËÂбþ¤¹¤ë¤è¤¦¤Ë¡¢Âбþ¤¹¤ëʸ»úÎó¤¬ÆÀ¤é¤ì¤ë¤³¤È¤ËÃí°Õ¤·¤Æ¤¯¤À
¤µ¤¤¡£SET
·¿¤Ç¤Î¥½¡¼¥È¤Ï¿ôÃͤȤ·¤Æ¹Ô¤Ê¤ï¤ì¤Þ¤¹¡£
Î㤨¤Ð¡¢¹àÌÜ test SET("one","two") NOT NULL
¤Ï¼¡¤ÎÃͤò»ý¤Ä¤³¤È¤¬
¤Ç¤¤Þ¤¹:
"" "one" "two" "one,two"
Ä̾LIKE ¤Þ¤¿¤Ï FINS_IN_SET() ¤Ç¤Î¡¢SET ¹àÌܤΠSELECT:
SELECT * from banner where banner_group LIKE '%value%'; SELECT * from banner where FIND_IN_SET('value',banner_group)>0;
¤·¤«¤·¼¡¤âƯ¤¤Þ¤¹:
SELECT * from banner where banner_group = 'v1,v2'; ;Exact match SELECT * from banner where banner_group & 1; ;Is in first group
ºÇ¤âÀºÅ٤ι⤤·¿¤òÁ´¤Æ¤Î¾ì¹ç¤Ë»ÈÍѤ·¤Æ¤ß¤Æ²¼¤µ¤¤¡£Î㤨¤Ð¡¢1-99999 ¤ÎÀ°¿ô
¤Ë¤Ï¡¢unsigned mediumint
¤¬ºÇÎɤη¿¤Ç¤¹¡£
Îɤ¯¤¢¤ëÌäÂê¤Ï¡¢²ßʾ¤ÎÃͤÎÀµ³Î¤Êɽ¸½¤Ç¤¹¡£MySQL ¤Ç¤Ï
DECIMAL
·¿¤ò»ÈÍѤ¹¤Ù¤¤Ç¤¹¡£¤³¤ì¤Ïʸ»úÎó¤È¤·¤Æ³ÊǼ¤·¡¢Àµ³Î¤µ¤Î¥í
¥¹¤ÏȯÀ¸¤·¤Þ¤»¤ó¡£Àµ³Î¤µ¤¬½ÅÍפǤʤ¤¾ì¹ç¤Ï DOUBLE
·¿¤Ç¤â½½Ê¬Îɤ¤
¤Ç¤¹¡£
¹âÀºÅ٤Τ¿¤á¡¢¾ï¤Ë BITINT
¤Ë³ÊǼ¤µ¤ì¤ë¸ÇÄê¾®¿ôÅÀ·¿¤ËÊÑ´¹¤Ç¤¤Þ¤¹¡£
¤³¤ì¤Ï¡¢Á´¤Æ¤Î·×»»¤òÀ°¿ô¤Ç¹Ô¤Ê¤¦¤è¤¦¤Ë¤·¡¢·ë²Ì¤À¤±¤òÉâÆ°¾®¿ôÅÀ¤ËÊÑ´¹¤·¤Æ
Ìᤷ¤Þ¤¹¡£
¡Ö¹Ô·Á¼°¤Î¼ïÎà¤Ï¡© ¤Þ¤¿ VARCHAR/CHAR ¤Î»ÈÍÑ»þ¤Ï¡©¡×ÀỲ¾È .
BLOB
¤È TEXT
·¿¤ò½ü¤¯ MySQL ¤ÎÁ´¤Æ¤Î¹àÌܤϥ¤¥ó¥Ç¥Ã
¥¯¥¹¤ò»ý¤Ä¤³¤È¤¬¤Ç¤¤Þ¤¹¡£Å¬ÀڤʹàÌܤǤΥ¤¥ó¥Ç¥Ã¥¯¥¹¤Î»ÈÍѤϡ¢select ¤Î
Àǽ¤ò¸þ¾å¤¹¤ëºÇÎɤÎÊýË¡¤Ç¤¹¡£
CHAR
¤È VARCHAR
¹àÌܤˤÏÀÜƬÉô¤Ë¥¤¥ó¥Ç¥Ã¥¯¥¹¤ò»ý¤Ä¤³¤È¤¬¤Ç¤
¤Þ¤¹¡£¼¡¤ÎÎã¤Ï¡¢¹àÌܤκǽé¤Î 10 ʸ»ú¤Ë¥¤¥ó¥Ç¥Ã¥¯¥¹¤òºîÀ®¤¹¤ëÊýË¡¤ò¼¨¤·¤Þ¤¹¡£
¤³¤ì¤Ï¹àÌÜÁ´ÂΤ˥¤¥ó¥Ç¥Ã¥¯¥¹¤ò»ý¤Ä¤Î¤ËÈæ¤Ù¡¢¤È¤Æ¤â®¤¯¡¢¤½¤·¤Æ¤è¤ê¾¯¤Ê¤¤¥Ç¥£
¥¹¥¯Îΰ褬ɬÍפǤ¹¡£
CREATE TABLE test ( name CHAR(200) NOT NULL, KEY index_name (name(10));
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));
¥¤¥ó¥Ç¥Ã¥¯¥¹ name
¤Ï last_name ¤È first_name ¤Ë¤Þ¤¿¤¬¤Ã¤¿¥¤¥ó¥Ç¥Ã
¥¯¥¹¤Ç¤¹¡£
name
¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ï¼¡¤Î¥¯¥¨¥ê¤Ç»ÈÍѤµ¤ì¤Þ¤¹:
SELECT * FROM test WHERE last_name="Widenius"; SELECT * FROM test WHERE last_name="Widenius" AND first_name="Michael"; SELECT * FROM test WHERE last_name="Widenius" AND (first_name="Michael" OR first_name="Monty"); SELECT * FROM test WHERE last_name="Widenius" and first_name >="M" and first_name < "N";
name
¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ï¼¡¤Î¥¯¥¨¥ê¤Ç¤Ï»ÈÍѤµ¤ì¤Þ¤»¤ó:
SELECT * FROM test WHERE first_name="Michael"; SELECT * FROM test WHERE last_name="Widenius" or first_name="Michael";
¾¤Î SQL ¥Ù¥ó¥À¤«¤é¤Î¥³¡¼¥É¤Î»ÈÍѤò¤è¤ê´Êñ¤Ë¥µ¥Ý¡¼¥È¤¹¤ë¤¿¤á¡¢ MySQL ¤Ï¼¡¤ÎÂбþ¤ò¥µ¥Ý¡¼¥È¤·¤Þ¤¹:
binary(num) | char(num) binary |
char varying | varchar |
float4 | float |
float8 | double |
int1 | tinyint |
int2 | smallint |
int3 | mediumint |
int4 | int |
int8 | bigint |
long varbinary | blob |
long varchar | text |
middleint | mediumint |
varbinary(num) | varchar(num) binary |
SELECT
¤È WHERE
Àá¤Ç»ÈÍѤ¹¤ë´Ø¿ô
select_expression
¤Þ¤¿¤Ï where_definition
¤Ï¼¡¤Î´Ø¿ô¤ò»ÈÍÑ
¤·¤¿Ç¤°Õ¤Îɽ¸½¤«¤é¤Ê¤ê¤Þ¤¹:
¼¡¤ÎÎã¤Ç¤Ï¡¢mysql
¥×¥í¥°¥é¥à¤Î½ÐÎϤÏû¤¯¤Ê¤Ã¤Æ¤¤¤Þ¤¹¡£¤Ä¤Þ¤ê:
mysql> select mod(29,9); 1 rows in set (0.00 sec) +-----------+ | mod(29,9) | +-----------+ | 2 | +-----------+
¤³¤ì¤Ï¼¡¤ËÊÑ´¹¤µ¤ì¤Æ¤¤¤Þ¤¹:
mysql> select mod(29,9); -> 2
(
)
mysql> select 1+2*3; -> 7 mysql> select (1+2)*3; -> 9
+
-
*
/
NULL
¤òÊÖ¤·¤Þ¤¹¡£
mysql> select 102/(1-1); -> NULL
¤³¤ì¤é¤ÏºÇÂç 64 ¥Ó¥Ã¥È¤ÎÈϰϤò»ý¤Á¤Þ¤¹¡£MySQL ¤Ï bigint (64 bit) ±é»»¤ò»ÈÍѤ¹¤ë¤¿¤á¤Ç¤¹¡£
|
mysql> select 29 | 15; -> 31
&
mysql> select 29 & 15; -> 13
BIT_COUNT()
mysql> select bit_count(29); -> 4
Á´¤Æ¤ÎÏÀÍý´Ø¿ô¤Ï 1 (TRUE) ¤Þ¤¿¤Ï 0 (FALSE) ¤òÊÖ¤·¤Þ¤¹¡£
NOT
!
mysql> select NOT 1; -> 0 mysql> select NOT NULL; -> NULL mysql> select ! (1+1); -> 0 mysql> select ! 1+1; -> 1
OR
||
mysql> select 1 || 0; -> 1 mysql> select 0 || 0; -> 0 mysql> select 1 || NULL; -> 1
AND
&&
mysql> select 1 && NULL; -> 0 mysql> select 1 && 0; -> 0
1 (TRUE), 0 (FALSE) ¤Þ¤¿¤Ï NULL
¤òÊÖ¤·¤Þ¤¹¡£¤³¤ì¤é¤Î´Ø¿ô¤Ï¿ôÃͤÈ
ʸ»úÎó¤ÎξÊý¤ÇƯ¤¤Þ¤¹¡£MySQL ¤ÏÈæ³Ó¤¬¤É¤Î¤è¤¦¤Ë¹Ô¤Ê¤ï¤ì¤ë¤«¤ò
·èÄꤹ¤ë¤¿¤á¤Ë¡¢¼¡¤Îµ¬Â§¤ò»ÈÍѤ·¤Þ¤¹:
TIMESTAMP
¤Þ¤¿¤Ï DATETIME
¹àÌܤǡ¢Â¾¤Î°ú¿ô¤¬
Äê¿ô¤Î¾ì¹ç¤Ï¡¢Äê¿ô¤ÏÈæ³ÓÁ°¤Ë timestamp ¤ËÊÑ´¹¤µ¤ì¤Þ¤¹¡£¤³¤ì¤Ï¤è¤ê ODBC
¥Õ¥ì¥ó¥É¥ê¤Ë¤¹¤ë¤¿¤á¤Ç¤¹¡£
¤É¤Á¤é¤«¤Þ¤¿¤ÏξÊý¤Î°ú¿ô¤¬ NULL
¤Î¾ì¹ç¤Ï¡¢Èæ³Ó·ë²Ì¤Ï NULL
¤Ç¤¹¡£
=
mysql> select 1 = 0; -> 0 mysql> select '0' = 0; -> 1 mysql> select '0.0' = 0; -> 1 mysql> select '0.01' = 0; -> 0 mysql> select '.01' = 0.01; -> 1
<>
!=
mysql> select '.01' <> '0.01'; -> 1 mysql> select .01 <> '0.01'; -> 0 mysql> select 'zapp' <> 'zappp'; -> 1
<=
mysql> select 0.1 <= 2; -> 1
<
mysql> select 2 <= 2; -> 1
>=
mysql> select 2 >= 2; -> 1
>
mysql> select 2 > 2; -> 0
ISNULL(A)
A
¤¬ NULL
¤Ê¤é 1 ¤ò¡¢¤½¤¦¤Ç¤Ê¤±¤ì¤Ð 0 ¤òÊÖ¤·¤Þ¤¹¡£
mysql> select isnull(1+1); -> 0 mysql> select isnull(1/0); -> 1
A BETWEEN B AND C
A
¤¬ B
°Ê¾å¤«¤Ä A
¤¬ C
°Ê²¼¡£Á´¤Æ¤Î°ú¿ô¤¬Æ±
¤¸·¿¤Î¾ì¹ç¡¢(A >= B AND A <= C)
¤ÈƱ¤¸¤Ç¤¹¡£Èæ³Ó¤¬¤É¤Î¤è¤¦¤Ë¹Ô¤Ê
¤ï¤ì¤ë¤«¤ò·èÄꤹ¤ë¤Î¤ÏºÇ½é¤Î°ú¿ô (A
) ¤Ç¤¹¡ª A
¤¬Ê¸»úÎóɽ¸½
¤Î¾ì¹ç¡¢¥±¡¼¥¹Èó°Í¸ʸ»úÎó¤È¤·¤ÆÈæ³Ó¤µ¤ì¤Þ¤¹¡£A
¤¬¥Ð¥¤¥Ê¥êʸ»úÎó
¤Î¾ì¹ç¡¢¥Ð¥¤¥Ê¥êʸ»úÎó¤È¤·¤ÆÈæ³Ó¤µ¤ì¤Þ¤¹¡£A
¤¬Àµ¿ôɽ¸½¤Î¾ì¹ç¡¢Àµ
¿ô¤È¤·¤Æ¸¡º÷¤µ¤ì¤Þ¤¹¡£¤½¤¦¤Ç¤Ê¤±¤ì¤Ð¼Â¿ô¤È¤·¤ÆÈæ³Ó¤µ¤ì¤Þ¤¹¡£
mysql> select 1 between 2 and 3; -> 0 mysql> select 'b' between 'a' and 'c'; -> 1 mysql> select 2 between 2 and '3'; -> 1 mysql> select 2 between 2 and 'x-3'; -> 0
expr IN (value,...)
IN
¥ê¥¹¥ÈÃæ¤ÎÃͤΤɤ줫¤Ê¤é 1 ¤òÊÖ¤·¡¢¤½¤¦¤Ç¤Ê¤±¤ì¤Ð 0 ¤ò
ÊÖ¤·¤Þ¤¹¡£Á´¤Æ¤ÎÃͤ¬Äê¿ô¤Î¾ì¹ç¤Ï¡¢Á´¤Æ¤ÎÃÍ¤Ï expr ¤Î·¿¤Ë½¾¤Ã¤Æɾ²Á¡¢¥½¡¼
¥È¤µ¤ì¤Þ¤¹¡£¹àÌܤθ¡º÷¤Ï¥Ð¥¤¥Ê¥ê¸¡º÷¤Î»ÈÍѤˤè¤Ã¤Æ¹Ô¤Ê¤ï¤ì¤Þ¤¹¡£¤³¤ì¤Ï¡¢
IN
ÉôÆâ¤ËÄê¿ô¤¬»ÈÍѤµ¤ì¤¿¾ì¹ç¤Ë IN
¤Ï¤È¤Æ¤â®¤¤¤³¤È¤ò°ÕÌ£
¤·¤Þ¤¹¡£
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
¤ÈƱ¤¸
expr LIKE expr
LIKE
¤Ë¤Ï2¤Ä¤Î¥ï¥¤¥ë¥É¥«¡¼¥É¤¬¤¢¤ê¤Þ¤¹¡£
% | Ǥ°Õ¤Î¿ô¤Îʸ»ú(0ʸ»ú¤â´Þ¤à)¤ËŬ¹ç¤·¤Þ¤¹¡£ |
_ | ¸·Ì©¤Ë1¤Ä¤Îʸ»ú¤ËŬ¹ç¤·¤Þ¤¹¡£ |
\% | 1¤Ä¤Î % ¤ËŬ¹ç¤·¤Þ¤¹¡£
|
\_ | 1¤Ä¤Î _ ¤ËŬ¹ç¤·¤Þ¤¹¡£
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE
¤Ï¿ôÃÍɽ¸½¤Ç¤âµö¤µ¤ì¤Þ¤¹¡ª¡Ê³ÈÄ¥¡Ë
NOT (expr LIKE expr)
¤ÈƱ¤¸¤Ç¤¹¡£
RLIKE
¤Ï mSQL
¸ß´¹¤Î¤¿¤á¤Ç¤¹¡£Ãí°Õ:
MySQL ¤Ïʸ»úÎóÃæ¤Ç C ¥¨¥¹¥±¡¼¥×¹½Ê¸ (\n
) ¤ò»ÈÍѤ¹¤ë¤¿¤á¡¢
REGEXP ʸ»úÎóÆâ¤Ç»ÈÍѤ¹¤ë '\'
¤ÏÆó½Å¤Ë¤¹¤ëɬÍפ¬¤¢¤ê¤Þ¤¹¡£
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr)
¤ÈƱ¤¸¡£
mysql> select strcmp('text', 'text2'); -> -1 mysql> select strcmp('text2', 'text'); -> 1 mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A
¤¬ NULL
¤Ç¤Ê¤¤¾ì¹ç¤Ï A
¤ò¡¢¤½¤¦¤Ç¤Ê¤±¤ì¤Ð
B
¤òÊÖ¤·¤Þ¤¹¡£
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A
¤¬¿¿ (A <> 0
¤«¤Ä A <> NULL
) ¤Î¾ì¹ç B
¤òÊÖ
¤·¡¢¤½¤¦¤Ç¤Ê¤±¤ì¤Ð C
¤òÊÖ¤·¤Þ¤¹¡£A ¤Ï INTEGER ¤È¤·¤Æɾ²Á¤µ¤ì¤Þ¤¹¡£
¤³¤ì¤ÏÉâÆ°¾®¿ôÅÀ¤ò»ÈÍѤ¹¤ë¾ì¹ç¡¢Èæ³Ó±é»»¤â»ÈÍѤ¹¤Ù¤¤Ç¤¢¤ë¤³¤È¤ò°ÕÌ£¤·¤Þ
¤¹¡£
mysql> select if(1>2,2,3); -> 3
¤¹¤Ù¤Æ¤Î¿ô³Ø´Ø¿ô¤Ï¥¨¥é¡¼¤Î¾ì¹ç NULL
¤òÊÖ¤·¤Þ¤¹¡£
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N
¤òÀ°¿ô¤Ë´Ý¤á¤Þ¤¹¡£
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number
¤ò Decimals
·å¤Î¾¯¿ô¤Ë´Ý¤á¤Þ¤¹¡£
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e
(¼«Á³Âпô¤Î´ð¿ô) ¤Î N
¾è¤ÎÃͤòÊÖ¤·¤Þ¤¹¡£
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X
¤Î¼«Á³Âпô¤òÊÖ¤·¤Þ¤¹¡£
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X
¤Î 10´ð¿ô¤ÎÂпô¤òÊÖ¤·¤Þ¤¹¡£
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X
¤Î Y
¾è¤ÎÃͤòÊÖ¤·¤Þ¤¹¡£
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X
¤ÎÈóÉé¤ÎÊ¿Êýº¬¤òÊÖ¤·¤Þ¤¹¡£
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X
¤Î¥³¥µ¥¤¥ó¤òÊÖ¤·¤Þ¤¹¡£X
¤Ï¥é¥¸¥¢¥ó¤ÇÍ¿¤¨¤é¤ì¤Þ¤¹¡£
mysql> select cos(PI()); -> -1.000000
SIN(X)
X
¤Î¥µ¥¤¥ó¤òÊÖ¤·¤Þ¤¹¡£X
¤Ï¥é¥¸¥¢¥ó¤ÇÍ¿¤¨¤é¤ì¤Þ¤¹¡£
mysql> select sin(PI()); -> 0.000000
TAN(X)
X
¤Î¥¿¥ó¥¸¥§¥ó¥È¤òÊÖ¤·¤Þ¤¹¡£X
¤Ï¥é¥¸¥¢¥ó¤ÇÍ¿¤¨¤é¤ì¤Þ¤¹¡£
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X
; that is the value whose cosine is
X
. If X
is not in the range -1 to 1 NULL
is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X
¤Î¥¢¡¼¥¯¥µ¥¤¥ó¤òÊÖ¤·¤Þ¤¹¡£¤¹¤Ê¤ï¤Á¥µ¥¤¥ó¤¬ X
¤Ë¤Ê¤ëÃͤÇ
¤¹¡£X
¤¬ -1 ¤«¤é 1 ¤ÎÈϰϤˤʤ¤¾ì¹ç NULL
¤¬ÊÖ¤µ¤ì¤Þ¤¹¡£
mysql> select ASIN(0.2); -> 0.201358 mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X
¤Î¥¢¡¼¥¯¥¿¥ó¥¸¥§¥ó¥È¤òÊÖ¤·¤Þ¤¹¡£¤¹¤Ê¤ï¤Á¥¿¥ó¥¸¥§¥ó¥È¤¬ X
¤Ë¤Ê¤ëÃͤǤ¹¡£
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X
¤È Y
¤Î¥¢¡¼¥¯¥¿¥ó¥¸¥§¥ó¥È¤òÊÖ¤·¤Þ¤¹¡£Y /
X
¤Î¥¢¡¼¥¯¥¿¥ó¥¸¥§¥ó¥È¤Î·×»»¤ÈƱÍͤǤ¹¤¬¡¢Î¾Êý¤Î°ú¿ô¤ÎÉä¹æ¤¬·ë²Ì¤Î
quadrant ¤ò·èÄꤹ¤ë¤¿¤á¤Ë»ÈÍѤµ¤ì¤Þ¤¹¡£
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N
¤Î¥³¥¿¥ó¥¸¥§¥ó¥È¤òÊÖ¤·¤Þ¤¹¡£
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0
¤òÊÖ¤·¤Þ¤¹¡£Àµ¿ôɽ¸½ X
¤Ï¥ª
¥×¥·¥ç¥ó¤Ç¥·¡¼¥ÉÃͤȤ·¤Æ»ÈÍѤµ¤ì¤Þ¤¹¡£
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888RAND() Ãͤò»ý¤Ä¹àÌÜ¤Ï ORDER BY ¤Ç¤¤Þ¤»¤ó¡£ORDER BY ¤Ï¹àÌܤòÊ£¿ô²óɾ²Á¤¹ ¤ë¤¿¤á¤Ç¤¹¡£
MIN(X,Y...)
GROUP
BY
´Ø¿ô¤Ç¤¹¡£°ú¿ô¤Ï¿ôÃͤȤ·¤ÆÈæ³Ó¤µ¤ì¤Þ¤¹¡£¥ì¥³¡¼¥É¤¬¸«¤Ä¤«¤é¤Ê¤¤¾ì¹ç¤Ï
NULL
¤¬ÊÖ¤µ¤ì¤Þ¤¹¡£
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP
BY
´Ø¿ô¤Ç¤¹¡£°ú¿ô¤Ï¿ôÃͤȤ·¤ÆÈæ³Ó¤µ¤ì¤Þ¤¹¡£¥ì¥³¡¼¥É¤¬¸«¤Ä¤«¤é¤Ê¤¤¾ì¹ç¤Ï
NULL
¤¬ÊÖ¤µ¤ì¤Þ¤¹¡£
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N
¤òÊÖ¤·¤Þ¤¹¡£
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N
¤òÊÖ¤·¤Þ¤¹¡£
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number
¤ò Decimals
·å¤Î¾¯¿ô¤ËÀÚ¤ê¼Î¤Æ¤Þ¤¹¡£
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S
¤Îº¸Ã¼¤Îʸ»ú¤Î ASCII ¥³¡¼¥ÉÃͤòÊÖ¤·¤Þ¤¹¡£S
¤¬
NULL
¤Î¾ì¹ç¤Ï NULL
¤òÊÖ¤·¤Þ¤¹¡£
mysql> SELECT ascii(2); -> 50 mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULL
¤ÏÈô¤Ð¤µ¤ì¤Þ¤¹¡£
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4 mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
B
Æâ¤Ë¤¢¤ë A
ʸ»úÎó¤Î°ÌÃÖ¤òÊÖ¤·¤Þ¤¹¡£ºÇ½é¤Î°ÌÃÖ¤Ï 1 ¤Ç¤¹¡£
B
Æâ¤Ë A
¤¬¤Ê¤¤»þ¤Ï 0 ¤òÊÖ¤·¤Þ¤¹¡£
mysql> select locate('bar', 'foobarbar'); -> 4 mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
A
Æâ¤ÎºÇ½é¤Îʸ»úÎó B
¤Î°ÌÃÖ¤òÊÖ¤·¤Þ¤¹¡£¤³¤ì¤Ï°ú¿ô¤ò
¸ò´¹¤·¤¿ LOCATE
¤ÈƱ¤¸¤Ç¤¹¡£
mysql> select instr('foobarbar', 'bar'); -> 4 mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
B
¤Î C
°ÌÃÖ¤«¤é¡¢Ê¸»úÎó A
¤Î°ÌÃÖ¤òÊÖ¤·¤Þ¤¹¡£
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
A
¤ÎºÇ¸å¤«¤é B
¸Ä¤Îʸ»ú¤òÆÀ¤Þ¤¹¡£
mysql> select right('foobarbar', 5); -> 'arbar' mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
mysql> select rtrim('barbar '); -> 'barbar'
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A
¥×¥ì¥Õ¥£¥Ã¥¯¥¹¤Þ¤¿¤Ï¥µ¥Õ¥£¥Ã¥¯¥¹¤ò B
¤«¤éºï½ü¤·¤¿
ʸ»úÎó¤òÊÖ¤·¤Þ¤¹¡£BOTH
, LEADING
¤½¤·¤Æ TRAILING
¤¬
»ÈÍѤµ¤ì¤Ê¤¤¾ì¹ç¡¢BOTH
¤¬Å¬ÍѤµ¤ì¤Þ¤¹¡£A
¤¬Í¿¤¨¤é¤ì¤Ê¤¤¤È¡¢
¶õÇò¤¬ºï½ü¤µ¤ì¤Þ¤¹¡£
mysql> select trim(' bar '); -> 'bar' mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx' mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar' mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S
. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING
on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('Hello'); -> 'H400' mysql> select soundex('B-Aättre'); -> 'B360'$)B mysql> select soundex('Quadratically'); -> 'Q36324'
SUBSTRING(A, B, C)
SUBSTRING(A FROM B FOR C)
MID(A, B, C)
A
¤Î B
°ÌÃÖ¤«¤é C
ʸ»ú¤Îʸ»úÎó¤òÊÖ¤·¤Þ¤¹¡£
FROM
¤Î°ã¤¤¤Ï ANSI SQL 92 ¹½Ê¸¤Ç¤¹¡£
mysql> select substring('Quadratically',5,6); -> ratica
SUBSTRING_INDEX(String, Delimiter, Count)
String
¤«¤é¡¢Delimiter
¤Ç Count
¸Ä¤Ë¶èÀÚ¤é¤ì¤¿Ê¸»ú
Îó¤òÊÖ¤·¤Þ¤¹¡£Count
¤¬Àµ¤Î¾ì¹ç¤Ïʸ»úÎó¤Ïº¸¤«¤é¸¡º÷¤µ¤ì¡¢
Count
¤¬Éé¤Î¾ì¹ç¤Ïʸ»úÎó¤Ï±¦¤«¤é¸¡º÷¤µ¤ì¤Þ¤¹¡£
mysql> select substring_index('www.tcx.se', '.', 2); -> 'www.tcx' mysql> select substring_index('www.tcx.se', '.', -2); -> 'tcx.se'
SPACE(N)
N
¸Ä¤Î¶õÇòʸ»ú¤òÊÖ¤·¤Þ¤¹¡£
mysql> select SPACE(6); -> ' '
REPLACE(A, B, C)
A
Æâ¤ÎÁ´¤Æ¤Îʸ»úÎó B
¤ò C
¤ËÃÖ¤´¹¤¨¤Þ¤¹¡£
mysql> select replace('www.tcx.se', 'w', 'Ww'); -> 'WwWwWw.tcx.se'
REPEAT(String, Count)
String
¤ò Count
²ó·«¤êÊÖ¤·¤Þ¤¹¡£Count <= 0
¤Î¾ì¹ç¤Ï
¶õ¤Îʸ»úÎó¤òÊÖ¤·¤Þ¤¹¡£String
¤Þ¤¿¤Ï Count
¤¬ NULL
¤Þ¤¿¤Ï¡¢LENGTH(string)*count > max_allowed_size
¤Î¾ì¹ç¤Ï
NULL
¤òÊÖ¤·¤Þ¤¹¡£
mysql> select repeat('MySQL', 3); -> 'MySQLMySQLMySQL'
REVERSE(String)
mysql> select reverse('abc'); -> 'cba'
INSERT(Org, Start, Length, New)
Org
Ãæ¤Î Start
°ÌÃÖ¤«¤é Length
ŤÎʸ»úÎó¤ò
New
¤ÇÃÖ¤´¹¤¨¤Þ¤¹¡£Org
Æâ¤ÎºÇ½é¤Î°ÌÃÖ¤Ï1¤Ç¤¹¡£
mysql> select insert('Quadratic', 3, 4, 'What'); -> 'QuWhattic'
INTERVAL(N, N1, N2, N3...)
Nn
> N3
> N2
> N1
¤¬Í׵ᤵ¤ì¤Þ¤¹¡£¤³¤ì¤Ï(¤È¤Æ¤â®¤¤)¥Ð¥¤¥Ê¥ê¥µ¡¼¥Á¤¬»ÈÍѤµ¤ì¤ë¤«¤é¤Ç¤¹¡£
N
< N1
¤Î¾ì¹ç¤Ï 0 ¤ò¡¢N
< N2
¤Î¾ì¹ç¤Ï 1 ¤ò
(°Ê¸åƱÍÍ¡Ä)ÊÖ¤·¤Þ¤¹¡£Á´¤Æ¤Î°ú¿ô¤Ï¿ôÃͤȤ·¤Æ¥Æ¥¹¥È¤µ¤ì¤Þ¤¹¡£
mysql> select INTERVAL(23, 1, 15, 17, 30, 44, 200); -> 3 mysql> select INTERVAL(10, 1, 10, 100, 1000); -> 2 mysql> select INTERVAL(22, 23, 30, 44, 200); -> 0
ELT(N, A1, A2, A3...)
N
= 1 ¤Ê¤é A1
¤ò¡¢N
= 2 ¤Ê¤é A2
¤òÊÖ¤·¤Þ¤¹¡£
N
¤¬ 1 ¤è¤ê¾®¤µ¤¤¾ì¹ç¡¢¤Þ¤¿¤Ï°ú¿ô¤Î¿ô¤è¤êÂ礤¤¾ì¹ç¤Ï NULL
¤¬ÊÖ¤µ¤ì¤Þ¤¹¡£
mysql> select elt(1, 'ej', 'Heja', 'hej', 'foo'); -> 'ej' mysql> select elt(4, 'ej', 'Heja', 'hej', 'foo'); -> 'foo'
FIELD(S, S1, S2, S3...)
S1
, S2
, S3
... ¥ê¥¹¥ÈÆâ¤Î S
¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤ò
ÊÖ¤·¤Þ¤¹¡£ELT()
¤ÎµÕ¤Ç¤¹¡£S ¤¬¸«¤Ä¤«¤é¤Ê¤±¤ì¤Ð 0 ¤òÊÖ¤·¤Þ¤¹¡£
mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo'); -> 2 mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo'); -> 0
FIND_IN_SET(string,string of strings)
mysql> SELECT FIND_IN_SET('b','a,b,c,d') -> 2ºÇ½é¤Î°ú¿ô¤¬ ',' ¤ò´Þ¤à¾ì¹ç¤Ï¡¢¤³¤Î´Ø¿ô¤ÏÅöÁ³Æ¯¤¤Þ¤»¤ó¡£
LCASE(A)
LOWER(A)
A
¤ò¸½ºß¤Îʸ»ú¥»¥Ã¥È,dmappings (¥Ç¥Õ¥©¥ë¥È Latin1) ¤Ë¤·¤¿¤¬¤Ã¤Æ¾®
ʸ»ú¤ËÊÑ´¹¤·¤Þ¤¹¡£
mysql> select lcase('QUADRATICALLY'); -> 'quadratically'
UCASE(A)
UPPER(A)
A
¤òÂçʸ»ú¤ËÊÑ´¹¤·¤Þ¤¹¡£
mysql> select ucase('Hej'); -> 'HEJ'
1¤Ä°Ê¾å¤ÎÆüÉÕ´Ø¿ô¤ò»ÈÍѤ¹¤ëÎã:
date_field ¤¬ºÇ¿·¤Î30Æü¤Ç¤¢¤ëÁ´¤Æ¤Î¥ì¥³¡¼¥É¤òÁªÂò¤·¤Þ¤¹¡£
SELECT something FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(date_field) <= 30;
Date
ɽ¸½¤Ï date ʸ»úÎó¡¢datetime ʸ»úÎó¡¢timestamp([6 | 8 | 14])
¤Þ¤¿¤Ï¡¢YYMMDD
¤ä YYYYMMDD
¤Î·Á¼°¤Î¿ôÃͤǤ¹¡£
date ɽ¸½¤Ç¤Ï¡¢Ç¯¤Ï 2 ¤Þ¤¿¤Ï 4 ·å¤Î¿ôÃͤǤ¹¡£2 ·å¤Ï 1970-2069 ¤ÎÈϰϤȸ« ¤Ê¤µ¤ì¤Þ¤¹¡£Date 100-199 ¤Ïǯ¤Î±é»»¤ò¤è¤ê´Êñ¤Ë¤¹¤ë¤¿¤á¡¢2000-2999 ¤ËÊÑ ´¹¤µ¤ì¤Þ¤¹¡ª Æüì¤ÊÆüÉÕ '0000-00-00' ¤Ï 0000-00-00 ¤È¤·¤Æ³ÊǼ¤µ¤ì¡¢¼è¤ê ½Ð¤µ¤ì¤Þ¤¹¡£
¿ôÃͤËÆüÉÕ´Ø¿ô¤ò»ÈÍѤ¹¤ë¾ì¹ç¡¢¿ôÃͤÎŤµ¤¬ 4, 8 ¤Þ¤¿¤Ï >= 14 ¤Ê¤éǯ¤¬4·å ¤È¸«¤Ê¤µ¤ì¤Þ¤¹¡£Â¾¤ÎÁ´¤Æ¤Î¥±¡¼¥¹¤Ç¤Ïǯ¤ÏÍ¿¤¨¤é¤ì¤¿¿ôÃͤκǽé¤Î2·å¤È¸«¤Ê ¤µ¤ì¤Þ¤¹¡£°ÂÁ´Â¦¤Ë¤ª¤¯¤¿¤á¡¢ÆüÉÕ¤ò¿ôÃÍ(ʸ»úÎó¤Ç¤Ê¤¯)¤Ç»ÈÍѤ¹¤ë»þ¤Ï¡¢¾ï¤Ë 4·å¤ÎÆüÉÕ¤ò»ÈÍѤ¹¤Ù¤¤Ç¤¹¡ª ¤½¤¦¤Ç¤Ê¤±¤ì¤Ð¡¢2000ǯ¤ÇÌäÂ꤬ȯÀ¸¤·¤Þ¤¹¡£¿ô ÃÍ 002001 ¤¬ÆüÉÕ´Ø¿ô¤ËÁ÷¤é¤ì¤¿»þ¡¢ÆüÉÕ '20002001' ¤ÎÂå¤ï¤ê¤Ë '2001' ¤Ë¤Ê ¤ê¤Þ¤¹¡£'002001' ¤Ï¤â¤Á¤í¤óÀµ¤·¤¯Æ¯¤¤Þ¤¹¡ª
Time
ɽ¸½¤Ï date ʸ»úÎó¡¢datetime ʸ»úÎó¡¢timestamp([6 | 8 | 14])
¤Þ¤¿¤Ï¡¢HHMMSS
¤ä YYYYMMDDHHMMSS
·Á¼°¤Î¿ôÃͤǤ¹¡£
DAYOFWEEK(date expr)
Date
¤ÎÍËÆü¤òÆÀ¤Þ¤¹ (0 = ÆüÍËÆü, 1 = ·îÍËÆü, 2 = ²ÐÍËÆü ..)
¤³¤ì¤Ï ODBC ɸ½à¤Ë½¾¤¤¤Þ¤¹¡£
mysql> select dayofweek('1998-02-03'); -> 3
WEEKDAY(date expr)
Date
¤ÎÍËÆü¤òÆÀ¤Þ¤¹ (0 = ·îÍËÆü, 1 = ²ÐÍËÆü ..)
mysql> select WEEKDAY('1997-10-04 22:23:00'); -> 5 mysql> select WEEKDAY('1997-11-05'); -> 2
DAYOFMONTH(date expr)
mysql> select DAYOFMONTH('1998-02-03'); -> 3
DAYOFYEAR(date expr)
mysql> select DAYOFYEAR('1998-02-03'); -> 34
MONTH(date expr)
mysql> select MONTH('1998-02-03'); -> 02
DAYNAME(date expr)
mysql> select dayname("1998-02-05"); -> Thursday
MONTHNAME(date expr)
mysql> select monthname("1998-02-05"); -> February
QUARTER(date expr)
mysql> select QUARTER('98-04-01'); -> 2
WEEK(date expr)
mysql> select WEEK('98-02-20'); -> 7
YEAR(date expr)
mysql> select YEAR('98-02-03'); -> 1998
HOUR(time expr)
mysql> select HOUR('10:05:03'); -> 10
MINUTE(time expr)
mysql> select MINUTE('98-02-03 10:05:03'); -> 5
SECOND(time expr)
mysql> select SECOND('10:05:03'); -> 3
PERIOD_ADD(P, N)
N
·î¤ò´ü´Ö P
(·¿ YYMM
¤Þ¤¿¤Ï YYYYMM
) ¤ËÄɲÃ
¤·¤Þ¤¹¡£YYYYMM
¤òÊÖ¤·¤Þ¤¹¡£
mysql> select PERIOD_ADD(9801,2); -> 199803
PERIOD_DIFF(A, B)
A
¤È B
¤Îº¹¤Î·î¤òÊÖ¤·¤Þ¤¹¡£A
¤È B
¤Ï·Á
¼° YYMM
¤Þ¤¿¤Ï YYYYMM
¤Ç¤¹¡£
mysql> select PERIOD_DIFF(9802,199703); -> 11
TO_DAYS(Date)
Date
¤ò daynumber (0ǯ¤«¤é¤ÎÆü¿ô) ¤ËÊÑ´¹¤·¤Þ¤¹¡£Date
¤Ï
DATE
ʸ»úÎó¡¢DATETIME
ʸ»úÎó¡¢TIMESTAMP([6 | 8 |
14])
¡¢YYMMDD
¤Þ¤¿¤Ï YYYYMMDD
·Á¼°¤Î¿ôÃͤǤ¹¡£
mysql> select TO_DAYS(9505); -> 733364 mysql> select TO_DAYS('1997-10-07); -> 729669
FROM_DAYS()
mysql> select from_days(729669); -> 1997-10-07
DATE_FORMAT(Date, Format)
Format
ʸ»úÎó¤Ë¤·¤¿¤¬¤Ã¤Æ Date
(date ¤Þ¤¿¤Ï timestamp) ¤ò
À°·Á¤·¤Þ¤¹¡£¼¡¤ÎÀ°·Á¥³¥Þ¥ó¥É¤¬ÃΤé¤ì¤Æ¤¤¤Þ¤¹:
M | ·î̾ |
W | ÍËÆü |
D | ±Ñ¸ì¥µ¥Õ¥£¥Ã¥¯¥¹ÉÕ¤·î¤ÎÆü |
Y | 4·å¤Îǯ |
y | 2·å¤Îǯ |
a | ¾Êά¤µ¤ì¤¿ÍËÆü̾ (Sun..Sat) |
d | ·î¤ÎÆü, ¿ôÃÍ |
m | ·î, ¿ôÃÍ |
b | ¾Êά¤µ¤ì¤¿·î̾ (Jan.Dec) |
j | ǯ¤ÎÆü (001..366) |
H | »þ (00..23) |
k | »þ ( 0..23) |
h | »þ (01..12) |
I | »þ (01..12) |
l | »þ ( 1..12) |
i | ʬ, ¿ôÃÍ |
r | »þ¹ï, 12»þ´Ö (hh:mm:ss [AP]M) |
T | »þ¹ï, 24»þ´Ö (hh:mm:ss) |
S | ÉÃ (00..59) |
s | ÉÃ (00..59) |
p | AM ¤Þ¤¿¤Ï PM |
w | ½µ¤ÎÆü (0=ÆüÍËÆü..) |
% | °ì¤Ä¤Î % ¤Ï̵»ë¤µ¤ì¤Þ¤¹¡£% ¤Î¤¿¤á¤Ë¤Ï %% ¤ò»ÈÍѤ·¤Æ¤¯¤À¤µ¤¤ (¾Íè¤Î³ÈÄ¥¤Î¤¿¤á)¡£ |
mysql> select date_format('1997-10-04 22:23:00', '%W %M %Y %h:%i:%s'); -> 'Saturday October 1997 22:23:00' mysql> select date_format('1997-10-04 22:23:00', '%D %y %a %d %m %b %j %H %k %I %r %T %S %w'); -> '4th 97 Sat 04 10 Oct 277 22 22 10 10:23:00 PM 22:23:00 00 6'¤·¤Ð¤é¤¯¤Ï
%
¤Ï¥ª¥×¥·¥ç¥ó¤Ç¤¹¡£MySQL ¤Î¾Íè¤Î¥Ð¡¼¥¸¥ç¥ó
¤Ç¤Ï¡¢%
¤ÏɬÍפȤʤê¤Þ¤¹¡£
DATE
¤ÎÍËÆü¤òÆÀ¤Þ¤¹ (0 = ·îÍËÆü, 1 = ²ÐÍËÆü)¡£Date
¤Ï date
ʸ»úÎó¡¢datetime ʸ»úÎó¡¢timestamp([6 | 8 | 14])¡¢YYMMDD
¤Þ¤¿¤Ï
YYYYMMDD
·Á¼°¤Î¿ôÃͤǤ¹¡£
mysql> select WEEKDAY('1997-10-04 22:23:00'); -> 5 mysql> select WEEKDAY('1997-11-05'); -> 2
CURDATE()
¤¬¿ôÃͤޤ¿¤Ïʸ»úÎó¤Î¤É¤Á
¤é¤Îʸ̮¤Ç»ÈÍѤµ¤ì¤¿¤«¤Ë°Í¸¤·¤Æ YYYYMMDD
¤Þ¤¿¤Ï
'YYYY-MM-DD'
¤Ç¤¹¡£
mysql> select CURDATE(); -> '1997-12-15' mysql> select CURDATE()+0; -> 19971215
HHMMSS
¤Þ¤¿¤Ï 'HH:MM:SS'
¤Î·Á¼°¤ÇÊÖ¤·¤Þ¤¹¡£¤³
¤ì¤Ï CURTIME()
¤¬¿ôÃͤޤ¿¤Ïʸ»úÎó¤Î¤É¤Á¤é¤Îʸ̮¤Ç»ÈÍѤµ¤ì¤¿¤«¤Ë°Í
¸¤·¤Þ¤¹¡£
mysql> select CURTIME(); -> '23:50:20' mysql> select CURTIME()+0; -> 235026
NOW()
¤¬¿ôÃͤޤ¿¤Ïʸ»úÎó¤Î¤É¤Á¤é¤Î
ʸ̮¤Ç»ÈÍѤµ¤ì¤¿¤«¤Ë°Í¸¤·¤Æ YYYYMMDDHHMMSS
¤Þ¤¿¤Ï
'YYYY-MM-DD HH:MM:SS'
¤Ç¤¹¡£
mysql> select NOW(); -> '1997-12-15 23:51:26' mysql> select NOW()+0; -> 19971215235131
TIMESTAMP
¹àÌܤò°ú¿ô¤È¤·¤Æ¸Æ¤Ó½Ð¤·¡¢¹àÌÜ
ÃͤòÉÿô¤ÇÊÖ¤·¤Þ¤¹¡£Date
¤Ï¥í¡¼¥«¥ë»þ¹ï¤Ç¤Î date ʸ»úÎó¡¢datetime
ʸ»úÎ󡢤ޤ¿¤Ï YYMMDD ¤Þ¤¿¤Ï YYYYMMDD ·Á¼°¤Î¿ôÃͤǤ¹¡£
mysql> select UNIX_TIMESTAMP(); -> 882226357 mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00'); -> 875996580
YYYY-MM-DD HH:MM:SS
¤Þ¤¿¤Ï
YYYYMMDDHHMMSS
·Á¼°¤Î timestamp ʸ»úÎó¤òÊÖ¤·¤Þ¤¹¡£
mysql> select FROM_UNIXTIME(875996580); -> '1997-10-04 22:23:00'
M | ·î, ʸ»úÎó |
W | Æü (½µ¤Î), ʸ»úÎó |
D | Æü (·î¤Î), ¿ôÃÍ+±Ñ¸ì¥µ¥Õ¥£¥Ã¥¯¥¹ |
Y | ǯ, ¿ôÃÍ, 4 ·å |
y | ǯ, ¿ôÃÍ, 2 ·å |
m | ·î, ¿ôÃÍ |
d | Æü (·î¤Î), ¿ôÃÍ |
h | »þ, ¿ôÃÍ |
i | ʬ, ¿ôÃÍ |
s | ÉÃ, ¿ôÃÍ |
w | Æü (½µ¤Î), ¿ôÃÍ |
¤½¤Î¾ | ¾¤Îʸ»ú¤Ï¤½¤Î¤Þ¤Þ¥³¥Ô¡¼¤µ¤ì¤Þ¤¹¡£ |
mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(), 'Y D M h:m:s x'); -> '1997 23rd December 03:12:30 x'
H:MM:SS
¤Þ¤¿¤Ï HMMSS
·Á¼°¤Ç¡¢°ú¿ô¤Î»þ, ʬ,
ÉäòÊÖ¤·¤Þ¤¹¡£
mysql> select SEC_TO_TIME(2378); -> '00:39:38' mysql> select SEC_TO_TIME(2378)+0; -> 3938
Time
¤òÉäËÊÑ´¹¤·¤Þ¤¹¡£
mysql> select TIME_TO_SEC('22:23:00'); -> 80580 mysql> select TIME_TO_SEC('00:39:38'); -> 2378
DATABASE()
mysql> select DATABASE(); -> 'test'
USER()
SYSTEM_USER()
SESSION_USER()
mysql> select USER(); -> 'davida'
PASSWORD(String)
String
¤«¤é¥Ñ¥¹¥ï¡¼¥Éʸ»úÎó¤ò·×»»¤·
¤Þ¤¹¡£¥Ñ¥¹¥ï¡¼¥É¤ò 'user' ¾µÇ§¥Æ¡¼¥Ö¥ëÆâ¤Ë³ÊǼ¤¹¤ë¤¿¤á¤Ë»ÈÍѤ¹¤ëɬÍפ¬¤¢
¤ê¤Þ¤¹¡£
mysql> select PASSWORD('badpwd'); -> '7f84554057dd964b'
ENCRYPT(String[, Salt])
crypt()
¥³¥Þ¥ó¥É¤Ç String
¤ò°Å¹æ²½¤·¤Þ¤¹¡£
Salt
¤Ï2ʸ»ú¤Îʸ»úÎó¤Ç¤¹¡£crypt()
¤¬¸«»È¤ï¤Ê¤¤¾ì¹ç¤Ï¾ï¤Ë
NULL
¤¬ÊÖ¤µ¤ì¤Þ¤¹¡£
LAST_INSERT_ID()
mysql> select LAST_INSERT_ID(); -> 1
FORMAT(Nr, Num)
Num
·å)¤Ç¿ôÃÍ Nr
¤òÀ°·Á
¤·¤Þ¤¹¡£
mysql> select FORMAT(12332.33, 2); -> '12,332.33'
VERSION
mysql> select version(); -> '3.21.16-beta-log'
GET_LOCK(String,timeout)
mysqladmin kill
¤Ç»¦¤µ¤ì¤¿¾ì¹ç¤Ê¤É)¤Ï NULL
¤¬ÊÖ¤ê¤Þ¤¹¡£RELEASE_LOCK
¤Î¼Â¹Ô¡¢¿·¤·¤¤ GET_LOCK
¤Î¼Â¹Ô¡¢
¥¹¥ì¥Ã¥É¤Î½ªÎ»¤Î¾ì¹ç¤Ë¡¢¥í¥Ã¥¯¤Ï²òÊü¤µ¤ì¤Þ¤¹¡£¤³¤Î´Ø¿ô¤Ï¥¢¥×¥ê¥±¡¼¥·¥ç¥ó
¥í¥Ã¥¯¤ä¥ì¥³¡¼¥É¥í¥Ã¥¯¤Î¥·¥ß¥å¥ì¡¼¥È¤Î¤¿¤á¤Ë»ÈÍѤǤ¤Þ¤¹¡£
mysql> select get_lock("automaticly released",10); -> 1 mysql> select get_lock("test",10); -> 1 mysql> select release_lock("test"); -> 1 mysql> select release_lock("automaticly released") -> NULL
RELEASE_LOCK(String)
GET_LOCK
¤Ç³ÍÆÀ¤·¤¿¥í¥Ã¥¯¤ò²òÊü¤·¤Þ¤¹¡£¥í¥Ã¥¯¤¬²ò
Êü¤µ¤ì¤¿¾ì¹ç¤Ï 1, ¤³¤Î¥¹¥ì¥Ã¥É¤Ë¤è¤Ã¤Æ¥í¥Ã¥¯¤µ¤ì¤Æ¤¤¤Ê¤¤¾ì¹ç¤Ï 0, 'String'
¤¬Â¸ºß¤·¤Ê¤¤¾ì¹ç¤Ï NULL ¤¬ÊÖ¤ê¤Þ¤¹¡£
GROUP BY
Àá¤Î´Ø¿ôCOUNT(Expr)
NULL
¤Î¹Ô¤Î¿ô¤ò¿ô¤¨¤Þ¤¹¡£count(*)
¤Ï¡¢SELECT
Ãæ
¤Ë¾¤Î¹àÌܤ¬»ÈÍѤµ¤ì¤Ê¤±¤ì¤Ð¡¢Èó¾ï¤Ë®¤¯ÊÖ¤ë¤è¤¦¤ËºÇŬ²½¤µ¤ì¤Æ¤¤¤Þ¤¹¡£
select count(*) from student; select count(if(length(name)>3,1,NULL)) from student;
AVG(expr)
MIN(expr)
MAX(expr)
min()
¤È max()
¤¬Ê¸»úÎó°ú¿ô¤ò¼è¤ë¤È¡¢
ºÇ¾®/ºÇÂç¤Îʸ»úÎóÃͤòÊÖ¤·¤Þ¤¹¡£
SUM(expr)
STD(expr)
STDDEV(expr) (Oracle format)
ANSI SQL
¤ËÂФ¹¤ë³ÈÄ¥
¤Ç¤¹¡£
BIT_OR(expr)
OR
¡£64 ¥Ó¥Ã¥È¤ÎÀºÅ٤Ƿ׻»¤µ¤ì¤Þ¤¹¡£
BIT_AND(expr)
AND
¡£64 ¥Ó¥Ã¥È¤ÎÀºÅ٤Ƿ׻»¤µ¤ì¤Þ¤¹¡£
MySQL ¤Ï GROUP BY
»ÈÍѤò³ÈÄ¥¤·¤Þ¤¹¡£SELECT
ɽ¸½Æâ¤Ç
GROUP BY
Éô¤Ë¸½¤ì¤Ê¤¤¹àÌܤޤ¿¤Ï·×»»¤ò»ÈÍѤǤ¤Þ¤¹¡£¤³¤ì¤Ï '¤³¤Î¥°¥ë¡¼
¥×¤Î¤¿¤á¤ÎÁ´¤Æ¤Î²Äǽ¤ÊÃÍ' ¤òɽ¤·¤Æ¤¤¤Þ¤¹¡£¤³¤Î»ÈÍѤˤè¤ê¡¢É¬Íפʤ¤¹àÌܤÇ
¤Î¥½¡¼¥È¤È¥°¥ë¡¼¥×¤¬Èò¤±¤é¤ì¤ë¤Î¤Ç¡¢¹â¤¤Àǽ¤¬ÆÀ¤é¤ì¤Þ¤¹¡£Î㤨¤Ð¡¢¼¡¤Î¥¯
¥¨¥ê¤Ç¤Ï b.name ¤Ç¥°¥ë¡¼¥×¤¹¤ëɬÍפϤ¢¤ê¤Þ¤»¤ó:
SELECT a.id,b.name,COUNT(*) from a,b WHERE a.id=b.id GROUP BY a.id
ANSI SQL ¤Ç¤Ï¡¢¼¡¤Î¥¯¥¨¥ê¤Ç¤Ï GROUP BY
Æâ¤Ë customer.name ¤òÄɲÃ
¤¹¤ëɬÍפ¬¤¢¤ê¤Þ¤¹¡£MySQL ¤Ç¤Ï name ¤Ï¾éŤǤ¹¡£
SELECT order.custid,customer.name,max(payments) from order,customer WHERE order.custid = customer.custid GROUP BY order.custid;
GROUP BY
¤Þ¤¿¤Ï ORDER BY
Àá¤Çɽ¸½¤ò»ÈÍѤǤ¤Ê¤¤¤³¤È¤ËÃí°Õ
¤·¤Æ¤¯¤À¤µ¤¤¡£Â¾¤ÎÊýË¡¤È¤·¤Æɽ¸½¤ÎÊÌ̾¤ò»ÈÍѤǤ¡¢ÌäÂê¤Î²ò·è¤È¤·¤Æ¤³¤ì¤ò
»ÈÍѤǤ¤Þ¤¹:
SELECT id,FLOOR(value/100) AS val FROM table_name GROUP BY id,val ORDER BY val
CREATE DATABASE database_name
¥Ç¡¼¥¿¥Ù¡¼¥¹¤òÍ¿¤¨¤é¤ì¤¿Ì¾Á°¤ÇºîÀ®¤·¤Þ¤¹¡£Ì¾Á°¤Ïʸ»ú¡¢¿ô»ú¤Þ¤¿¤Ï
'_'
ʸ»ú¤À¤±¤ò´Þ¤à¤³¤È¤¬¤Ç¤¡¢Ê¸»ú¤Þ¤¿¤Ï _
¤Ç»Ï¤Þ¤é¤Ê¤¤¤È
¤¤¤±¤Þ¤»¤ó¡£¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾¤ÎºÇÂçĹ¤Ï 64 ʸ»ú¤Ç¤¹¡£MySQL Æâ¤ÎÁ´
¤Æ¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹¤Ï¥Ç¥£¥ì¥¯¥È¥ê¤Ç¤¹¡£¤½¤Î¤¿¤á¡¢CREATE DATABASE ¤Ï¡¢
MySQL ¥Ç¡¼¥¿¥Ù¡¼¥¹¥Ç¥£¥ì¥¯¥È¥êÆâ¤Ë¥Ç¥£¥ì¥¯¥È¥ê¤òºîÀ®¤¹¤ë¤À¤±¤Ç
¤¹¡£mysqladmin
¤Ç¤â¥Ç¡¼¥¿¥Ù¡¼¥¹¤òºîÀ®¤¹¤ë¤³¤È¤¬¤Ç¤¤Þ¤¹¡£
¡ÖÍÍ¡¹¤Ê MySQL ¥×¥í¥°¥é¥à¤Î³µÍס×ÀỲ¾È
DROP DATABASE database_name
¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤ÎÁ´¤Æ¤Î¥Æ¡¼¥Ö¥ë¤òÇË´þ¤·¡¢¥Ç¡¼¥¿¥Ù¡¼¥¹¤òºï½ü¤·¤Þ¤¹¡£¤³¤Î
¥³¥Þ¥ó¥É¤Ï¤È¤Æ¤âÃí°Õ¤·¤Æ¹Ô¤Ê¤Ã¤Æ¤¯¤À¤µ¤¤¡ª DROP DATABASE
¤Ï¥Ç¥£
¥ì¥¯¥È¥ê¤«¤é¤¤¤¯¤Ä¤Î¥Õ¥¡¥¤¥ë¤¬ºï½ü¤µ¤ì¤¿¤«¤òÊÖ¤·¤Þ¤¹¡£Ä̾盧¤ì¤Ï¥Æ¡¼¥Ö¥ë¿ô * 3
¤Ç¤¹¡£mysqladmin
¤Ç¤â¥Ç¡¼¥¿¥Ù¡¼¥¹¤òÇË´þ¤¹¤ë¤³¤È¤¬¤Ç¤¤Þ¤¹¡£
¡ÖÍÍ¡¹¤Ê MySQL ¥×¥í¥°¥é¥à¤Î³µÍס×ÀỲ¾È
CREATE TABLE table_name ( create_definition,... ) 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
¡Ö¹àÌÜ·¿¡×ÀỲ¾È
FOREIGN KEY, CHECK ¤½¤·¤Æ REFERENCE ¹½Ê¸¤Ï¸ß´¹¤Î¤¿¤á¤À¤±¤Ë¤¢¤ê¤Þ¤¹¡£¡Ê¾ ¤Î SQL ¥µ¡¼¥Ð¤«¤é¤Î¥³¡¼¥É¤è¤ê´Êñ¤Ë°Ü¹Ô¤·¡¢¥ê¥Õ¥¡¥ì¥ó¥¹ÉÕ¤¤Î¥Æ¡¼¥Ö¥ë¤ò À¸À®¤¹¤ë¤è¤¦¤Ê¥¢¥×¥ê¥±¡¼¥·¥ç¥ó¤ò¼Â¹Ô¤¹¤ë¤¿¤á¡Ë¡£¤³¤ì¤é¤Ï¼ÂºÝ¤Ë¤Ï²¿¤â¤·¤Þ ¤»¤ó¡£ ¡ÖMySQL ¤Ë̵¤¤µ¡Ç½¡×ÀỲ¾È
¹àÌܤ¬ DEFAULT ¤ÎÃͤò»ý¤¿¤º¡¢NOT NULL ¤È¤·¤ÆÀë¸À¤µ¤ì¤Æ¤¤¤Ê¤¤¾ì¹ç¤Ï¡¢¥Ç¥Õ¥© ¥ë¥ÈÃÍ¤Ï NULL ¤Ç¤¹¡£
¹àÌܤ¬ DEFAULT ¤ÎÃͤò»ý¤¿¤º¡¢NOT NULL ¤È¤·¤ÆÀë¸À¤µ¤ì¤Æ¤¤¤ë¾ì¹ç¤Ï¡¢MySQL ¤Ï¼«Æ°Åª¤Ë¤½¤Î¹àÌܤ˥ǥե©¥ë¥ÈÃͤò³ä¤êÅö¤Æ¤Þ¤¹¡£
INT(5) ZEROFILL
¤Ï¡¢ÃÍ 5 ¤Ï 00005
¤È¤·¤Æ¼è¤ê½Ð¤µ¤ì¤Þ¤¹¡£
BINARY
¤Ï¡¢¤½¤Î¹àÌܤ¬¥±¡¼¥¹°Í¸¤ÇÈæ³Ó¤µ¤ì¤ë¤³¤È¤ò°ÕÌ£¤·¤Þ¤¹¡£¥Ç¥Õ¥©
¥ë¥È¤ÏÁ´¤Æ¤Îʸ»úÎó¤Ï ISO-8859-1 Latin1 ¤Ë½¾¤Ã¤Æ¥±¡¼¥¹Èó°Í¸¤ÇÈæ³Ó¤µ¤ì¤Þ
¤¹¡£BINARY
¤Ï 'sticky' ¤Ç¤¹¡£¤Ä¤Þ¤ê¡¢BINARY
¤È¥Þ¡¼¥¯¤µ¤ì¤¿
¹àÌܤ¬É½¸½Ãæ¤Ç»È¤ï¤ì¤ë¤È¡¢É½¸½Á´ÂΤ¬ BINARY
Èæ³Ó¤µ¤ì¤Þ¤¹¡£
column_name(length)
¹½Ê¸¤Ç¤Ï¡¢string
¹àÌܤΰìÉô¤Ë¤À¤±¥¤¥ó
¥Ç¥Ã¥¯¥¹¤ò»ØÄê¤Ç¤¤Þ¤¹¡£¤³¤ì¤Ï¥¤¥ó¥Ç¥Ã¥¯¥¹¥Õ¥¡¥¤¥ë¤ò¤È¤Æ¤â¾®¤µ¤¯¤Ç¤¤Þ¤¹¡£
BLOB
¤È TEXT
·¿¤ÎÀ©¸Â¡×ÀỲ¾È ¡£
isamchk
¥æ¡¼¥Æ¥£¥ê¥Æ¥£¤ò¥Æ¡¼
¥Ö¥ë¤ÎºÆÊÔÀ®¤ò¤¹¤ë¤¿¤á¤Ë»ÈÍѤǤ¤Þ¤¹¡£
VARCHAR
¹àÌÜ¤Ï CHAR
¤ËÊѹ¹¤µ¤ì¤Þ¤¹¡£VARCHAR
¹àÌܤ¬
°ì¤Ä»ÈÍѤµ¤ì¤ë¤È¡¢2 ¤è¤êŤ¤Á´¤Æ¤Î CHAR
¹àÌÜ¤Ï VARCHAR
¤Ë
Êѹ¹¤µ¤ì¤Þ¤¹¡£¤³¤ì¤Ï¤É¤ó¤ÊÊýË¡¤Ç¹àÌܤò»ÈÍѤ¹¤ë¤«¤Ë±Æ¶Á¤·¤Þ¤»¤ó;
MySQL ¤Ç¤Ï¡¢VARCHAR
¤Ïʸ»ú¤ò³ÊǼ¤¹¤ëÊýË¡¤¬°ã¤¦¤À¤±¤Ç¤¹¡£
Îΰè¤òÀáÌó¤·¡¢¥Æ¡¼¥Ö¥ë¤ò¤è¤ê®¤¯¤¹¤ë¤¿¤á¤Ë¡¢MySQL
¤Ï¤³¤ÎÊÑ´¹¤ò¹Ô
¤¤¤Þ¤¹¡£ ¡Ö¹Ô·Á¼°¤Î¼ïÎà¤Ï¡© ¤Þ¤¿ VARCHAR/CHAR ¤Î»ÈÍÑ»þ¤Ï¡©¡×ÀỲ¾È
INSERT
/UPDATE
¤Ç¤Ï¡¢Á´¤Æ¤Îʸ»úÎó(CHAR
¤È
VARCHAR
)¤Ï CREATE ¤ÇÍ¿¤¨¤é¤ì¤¿ºÇÂçŤÇÀÚÃǤޤ¿¤ÏºÇÂçĹ¤Þ¤ÇËä¤á¤é
¤ì¤Þ¤¹¡£½ª¤ê¤ÎÁ´¤Æ¤Î¶õÇò¤â¼«Æ°Åª¤Ëºï½ü¤µ¤ì¤Þ¤¹¡£Î㤨¤Ð
VARCHAR(10)
¤Ï¡¢¹àÌܤϺÇÂç 10 ʸ»ú¤ÎŤµ¤Îʸ»úÎó¤ò´Þ¤à¤³¤È¤¬¤Ç¤¤Þ
¤¹¡£
NULL
Ãͤˤʤê¤Þ¤¹¡£
REGEXP
¤È RLIKE
) ¤ÏISO8859-1 (Latin1) ¤òʸ»ú
¤Î·¿¤Î·èÄê¤Ë»ÈÍѤ·¤Þ¤¹¡£
ALTER [IGNORE] TABLE table_name alter_spec [, alter_spec ...] alter_specification: ADD [COLUMN] create_definition [AFTER column_namn | FIRST] 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
ALTER TABLE
¤Ï¼¡¤Î¤è¤¦¤ËƯ¤¤Þ¤¹¡£°ì»þŪ¤Ê¥Æ¡¼¥Ö¥ë¤òºîÀ®¤·¡¢Á´¤Æ
¤Î¾ðÊó¤ò¤½¤³¤Ë¥³¥Ô¡¼¤·¡¢¤½¤ì¤«¤é¸Å¤¤¥Æ¡¼¥Ö¥ë¤¬ºï½ü¤µ¤ì¡¢¿·¤·¤¤¥Æ¡¼¥Ö¥ë¤¬
¥ê¥Í¡¼¥à¤µ¤ì¤Þ¤¹¡£Á´¤Æ¤Î¹¹¿·¤Ï¼«Æ°Åª¤Ë¿·¤·¤¤¥Æ¡¼¥Ö¥ë¤Ë¥ê¥À¥¤¥ì¥¯¥È¤µ¤ì¤ë
¤Î¤Ç¡¢¹¹¿·¤¬¼ºÇÔ¤¹¤ë¤³¤È¤Ê¤¯¹Ô¤ï¤ì¤Þ¤¹¡£ALTER TABLE
¤¬Æ°ºîÃæ¤Ï¡¢
¸Å¤¤¥Æ¡¼¥Ö¥ë¤Ï¾¤Î¥¯¥é¥¤¥¢¥ó¥È¤ËÆɤ߹þ¤ß²Äǽ¤Ç¤¹¡£¥Æ¡¼¥Ö¥ë¤Î¹¹¿·/¥Æ¡¼¥Ö
¥ë¤Ø¤Î½ñ¤¹þ¤ß¤Ï»ß¤á¤é¤ì¡¢¿·¤·¤¤¥Æ¡¼¥Ö¥ë¤Î½àÈ÷¤¬¤Ç¤¤¿¸å¤Ë¤À¤±¼Â¹Ô¤µ¤ì¤Þ¤¹¡£
IGNORE
¤¬»ØÄꤵ¤ì¤Ê¤¤¾ì¹ç¡¢¿·¤·¤¤¥Æ¡¼¥Ö¥ëÆâ¤ËÊ£¿ô¤Î¥æ¥Ë¡¼¥¯¥¡¼¤¬
¸ºß¤¹¤ë¤È¡¢¥³¥Ô¡¼¤Ï¼ºÇÔ¤·´¬¤Ìᤵ¤ì(roll back)¤Þ¤¹¡£½ÅÊ£¤¹¤ë¾ì¹ç¡¢ºÇ½é
¤Ë¸«¤Ä¤«¤Ã¤¿¹Ô¤¬»ÈÍѤµ¤ì¤Þ¤¹¡£¤³¤ì¤Ï MySQL ³ÈÄ¥¤Ç¤¹¡£
CHANGE column_name
, DROP column_name
¤½¤·¤Æ DROP
INDEX
¤Ï ANSI SQL92 ¤Ø¤Î MySQL ³ÈÄ¥¤Ç¤¹¡£
COLUMN
¤Ï½ã¿è¤Ë¥´¥ß¤Îñ¸ì¤Ç¡¢¾Êά¤Ç¤¤Þ¤¹¡£
ADD
¤È CHANGE
¤Ï CREATE TABLE
¤ÈƱ¤¸
create_definition ¤ò»È¤¤¤Þ¤¹¡£ ¡ÖCREATE TABLE ¹½Ê¸¡×ÀỲ¾È ¡£
ADD
... AFTER
column_name ¤Þ¤¿¤Ï
FIRST
¤ò»ÈÍѤ·¤Æ¡¢¥Æ¡¼¥Ö¥ëÆâ¤Î°ÌÃÖ¤ò»ØÄꤷ¤Æ¹àÌܤòÄɲäǤ¤Þ¤¹¡£
¥Ç¥Õ¥©¥ë¥È¤Ï¹àÌܤκǸå¤Ç¤¹¡£
ALTER COLUMN
¤Ï¹àÌܤ˿·¤·¤¤¥Ç¥Õ¥©¥ë¥ÈÃͤòÀßÄê¤Þ¤¿¤Ï¸Å¤¤¥Ç¥Õ¥©¥ë¥ÈÃÍ
¤òºï½ü¤·¤Þ¤¹¡£
DROP INDEX
¤Ï¥¤¥ó¥Ç¥Ã¥¯¥¹¤òºï½ü¤·¤Þ¤¹¡£¤³¤ì¤Ï MySQL ¤Î³È
Ä¥¤Ç¤¹¡£
FOREIGN KEY
¹½Ê¸¤Ï¸ß´¹¤Î¤¿¤á¤À¤±¤Ë¸ºß¤·¤Þ¤¹¡£
¡ÖMySQL ¤Ë̵¤¤µ¡Ç½¡×ÀỲ¾È
DROP PRIMARY KEY
¤Ï PRIMARY
¤È̾ÉÕ¤±¤é¤ì¤¿¥¤¥ó¥Ç¥Ã¥¯¥¹¤òÇË
´þ¤·¤Þ¤¹¡£¤Þ¤¿¤Ï¡¢¤½¤Î¤è¤¦¤Ê¥¤¥ó¥Ç¥Ã¥¯¥¹¤¬Â¸ºß¤·¤Ê¤±¤ì¤Ð¡¢¥Æ¡¼¥Ö¥ë¤ÎºÇ½é
¤Î UNIQUE
¥¤¥ó¥Ç¥Ã¥¯¥¹¤òÇË´þ¤·¤Þ¤¹¡£
CHANGE
¤Ï¥Ç¡¼¥¿¤ò¤Ç¤¤ë¤À¤±Îɤ¤¿·¤·¤¤·Á¼°¤ËÊÑ´¹¤ò»î¤ß¤Þ¤¹¡£
mysql_info(MYSQL*)
¤Ç¡¢¤¤¤¯¤Ä¤Î¥ì¥³¡¼¥É¤¬¥³¥Ô¡¼¤µ¤ì¡¢¤¤¤¯¤Ä¤Î¥ì¥³¡¼
¥É¤¬Ê£¿ô¥¤¥ó¥Ç¥Ã¥¯¥¹¤Î¤¿¤á¤Ëºï½ü¤µ¤ì¤¿¤«¤ò¼è¤ê½Ð¤»¤Þ¤¹¡£
ALTER TABLE
¤ò»ÈÍѤ¹¤ë¤¿¤á¤Ë¤Ï¡¢select, insert, delete, update,
create, drop Æø¢¤¬¥Æ¡¼¥Ö¥ë¤ËɬÍפǤ¹¡£
ALTER TABLE table_name RENAME AS new_name
¤ò¾¤Î¥ª¥×¥·¥ç¥ó̵¤·¤Ë
»ÈÍѤ¹¤ë¾ì¹ç¤Ï¡¢MySQL ¤Ï¥Æ¡¼¥Ö¥ë¤Î¥ê¥Í¡¼¥à¤ò®¤¯¹Ô¤Ê¤¦¤À¤±¤Ç¤¹¡£
ALTER TABLE ¤Î¤¤¤¯¤Ä¤«¤Î»ÈÍÑÎã:
CREATE TABLE t1 (a INTEGER,b CHAR(10)); INSERT INTO t1 VALUES(1,"testing"); ALTER TABLE t1 RENAME t2; ALTER TABLE t2 CHANGE a a TINYINT NOT NULL, CHANGE b c CHAR(20); ALTER TABLE t2 ADD d TIMESTAMP; ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a); ALTER TABLE t2 DROP COLUMN c; ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c); DROP TABLE t2;
DROP TABLE table_name [, table_name...]
°ì¤Ä¤Þ¤¿¤Ï¤½¤ì°Ê¾å¤Î¥Æ¡¼¥Ö¥ë¤òºï½ü¤·¤Þ¤¹¡£Á´¤Æ¤Î¥Ç¡¼¥¿¤ÈÄêµÁ¤Ïºï½ü¤µ¤ì ¤Þ¤¹¡£¤³¤Î¥³¥Þ¥ó¥É¤Ï¤½¤ì¤ò´Êñ¤Ë¹Ô¤Ê¤¤¤Þ¤¹¡ª
DELETE FROM table_name WHERE where_definition
±Æ¶Á¤µ¤ì¤¿¥ì¥³¡¼¥É¿ô¤òÊÖ¤·¤Þ¤¹¡£
WHERE
Àá¤Ê¤·¤Çºï½ü¤ò¹Ô¤Ã¤¿¾ì¹ç¡¢¥Æ¡¼¥Ö¥ë¤¬ºÆÀ¸À®¤µ¤ì¤Þ¤¹¡£¤³¤ì¤Ï
³Æ¹Ô¤òºï½ü¤¹¤ë¤è¤ê¤â¤È¤Æ¤â®¤¤¤Ç¤¹¡£¤³¤Î¾ì¹ç¡¢±Æ¶Á¤µ¤ì¤¿¥ì¥³¡¼¥É¤Ï 0 ¤È
¤·¤Æ¥³¥Þ¥ó¥É¤¬ÊÖ¤ê¤Þ¤¹¡£MySQL ¤Ïºï½ü¤µ¤ì¤¿¹Ô¤Î¿ô¤òÊÖ¤·¤Þ¤»¤ó¡£
¥Æ¡¼¥Ö¥ëÄêµÁ¥Õ¥¡¥¤¥ë table_name.frm
¤¬Àµ¤·¤¤¸Â¤ê¡¢¥Æ¡¼¥Ö¥ë¤òºÆÀ¸
À®¤Ç¤¤ë¤³¤È¤ò³Î¤«¤á¤ë¤¿¤á¤Ë¡¢¥Ç¡¼¥¿¥Õ¥¡¥¤¥ë¤ò¥ª¡¼¥×¥ó¤¹¤ë¤³¤È¤Ï¤Ê¤·¤Ë¡¢
ºÆÀ¸À®¤¬¹Ô¤ï¤ì¤ë¤¿¤á¤Ç¤¹¡£
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]]
»ÈÍѤµ¤ì¤ëÁ´¤Æ¤Î¥¡¼¥ï¡¼¥É¤Ï¸·Ì©¤Ë¾åµ¤Î½ç¤ÎɬÍפ¬¤¢¤ê¤Þ¤¹¡£Î㤨¤Ð
HAVING
Àá¤Ï GROUP BY
¤Î¸å¤Ç ORDER BY
Àá¤ÎÁ°¤ËÍè¤Ê¤±
¤ì¤Ð¤¤¤±¤Þ¤»¤ó¡£
ɬÍפ˱þ¤¸¤Æ(Perl ¤Î¤è¤¦¤Ë)¡¢¼«Æ°Åª¤Ëʸ»úÎó¤Ï¿ôÃͤˡ¢¿ôÃͤÏʸ»úÎó¤ËÊÑ´¹
¤µ¤ì¤Þ¤¹¡£Èæ³ÓÁàºî ((=, <>, <=, <, >=, >)
) ¤Ç¤Ï¡¢°ú¿ô¤¬¿ôÃͤξì¹ç
°ú¿ô¤Ï¿ôÃͤȤ·¤ÆÈæ³Ó¤µ¤ì¤Þ¤¹¡£¤½¤¦¤Ç¤Ê¤¤¾ì¹ç¤Ï°ú¿ô¤Ïʸ»úÎó¤È¤·¤ÆÈæ³Ó¤µ¤ì
¤Þ¤¹¡£Á´¤Æ¤Îʸ»úÎóÈæ³Ó¤Ï¥Ç¥Õ¥©¥ë¥È¤Ç¤Ï ISO8859-1 (±Ñ¸ì¤Ç¤âÍ¥½¨¤ËƯ¤¯¥¹¥«
¥ó¥¸¥Ê¥Ó¥¢Ê¸»ú¥»¥Ã¥È) ¤Ë¤è¤ë¥±¡¼¥¹Èó°Í¸¤Ç¤¹¡£
select 1 > '6x'; -> 0 select 7 > '6x'; -> 1 select 0 > 'x6'; -> 0 select 0 = 'x6'; -> 1
HAVING
ÀáÆâ¤Ç»ÈÍѤǤ¤Þ¤¹¡£
select concat(last_name,' ',first_name) as name from table order by name
LEFT OUTER
JOIN
»²¾È¤â´Þ¤ß¤Þ¤¹¡£ ¡ÖJoin ¹½Ê¸¡×ÀỲ¾È
LIKE
ɽ¸½¤Ç¤Ï¡¢% ¤È _ ¤Ï '\' ¤òÁ°¤ËÃÖ¤¯¤³¤È¤Ç¡¢¥ï¥¤¥ë¥É¥«¡¼¥É¤Î°Õ
Ì£¤ò¤Ê¤¯¤·¡¢¥ê¥Æ¥é¥ë¤Î % ¤È _ ¤Ë¤Ê¤ê¤Þ¤¹¡£
IFNULL()
¤È IF()
¤Ï¡¢»ÈÍѤ˱þ¤¸¤Æ¿ôÃͤޤ¿¤Ïʸ»úÎóÃͤòÊÖ¤·
¤Þ¤¹¡£
ORDER
¤È GROUP
¤Î¹àÌܤϡ¢¹àÌÜ̾, ¹àÌÜÊÌ̾ ¤Þ¤¿¤Ï SELECT
ÀáÆâ¤Î¹àÌÜÈÖ¹æ¤È¤·¤Æ¡¢Í¿¤¨¤é¤ì¤Þ¤¹¡£
HAVING
Àá¤Ï¡¢select_expressions Ãæ¤ÎǤ°Õ¤Î¹àÌܤޤ¿¤ÏÊÌ̾¤ò»ý¤Ä¤³¤È
¤¬¤Ç¤¤Þ¤¹¡£ºÇ¸å¤ËŬÍѤµ¤ì¡¢Ä¾Á°¤Î¹àÌܤ¬¥¯¥é¥¤¥¢¥ó¥È¤ËºÇŬ²½¤µ¤ì¤º¤ËÁ÷¤é
¤ì¤Þ¤¹¡£WHERE ÀáÆâ¤ËÃÖ¤¯¤Ù¤¹àÌܤˤϻȤï¤Ê¤¤¤Ç²¼¤µ¤¤¡£¼¡¤Ï(¤Þ¤À)¤Ç¤¤Þ¤»
¤ó:
SELECT user,MAX(salary) FROM users GROUP BY user HAVING max(salary)>10
¼¡¤Î¤è¤¦¤ËÊѹ¹¤·¤Æ²¼¤µ¤¤:
SELECT user,MAX(salary) AS sum FROM users GROUP BY user HAVING sum > 10
STRAIGHT_JOIN
¤Ï¡¢FROM
Àá¤ÇÍ¿¤¨¤é¤ì¤¿½çÈÖÄ̤ê¤Ë¥Æ¡¼¥Ö¥ë¤ò·ë¹ç¤¹
¤ë¤è¤¦¤Ë¡¢¥ª¥×¥Æ¥£¥Þ¥¤¥¶¤Ë¶¯À©¤·¤Þ¤¹¡£¥ª¥×¥Æ¥£¥Þ¥¤¥¶¤¬¥Æ¡¼¥Ö¥ë¤òºÇŬ¤Ç¤Ê¤¤½ç¤Ë
·ë¹ç¤¹¤ë¾ì¹ç¡¢¤è¤ê®¤¯¹Ô¤Ê¤¦¥¯¥¨¥ê¤òÆÀ¤ë¤¿¤á¤Ë¤³¤ì¤ò»ÈÍѤ¹¤ë¤³¤È¤¬¤Ç¤¤Þ
¤¹¡£ ¡ÖEXPLAIN ¹½Ê¸¡£SELECT ¤Ë¤Ä¤¤¤Æ¤Î¾ðÊó¤òÆÀ¤ë¡×ÀỲ¾È
MySQL ¤Ï¼¡¤Î 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 }
ºÇ¸å¤ÎÎã¤Ï ODBC ¹½Ê¸¤Ç¤¹¡£
table_reference AS alias
¤Þ¤¿¤Ï
table_reference alias
¤ÇÊÌ̾¤ò¤Ä¤±¤é¤ì¤Þ¤¹¡£
,
¤È JOIN
¤Ï°Ọ̃Ū¤Ë¤ÏƱ¤¸¤Ç¤¹¡£¤³¤ì¤Ï»ÈÍѤµ¤ì¤¿¥Æ¡¼¥Ö¥ë´Ö
¤Î´°Á´¤Ê·ë¹ç¤ò¹Ô¤¤¤Þ¤¹¡£Ä̾ï¤Ï¡¢¥Æ¡¼¥Ö¥ë¤ò¤É¤Î¤è¤¦¤Ë¥ê¥ó¥¯¤¹¤ë¤«¤ò
WHERE
¾ò·ïÃæ¤Çµ½Ò¤·¤Þ¤¹¡£
ON
conditional ¤ÏǤ°Õ¤Î WHERE
¾ò·ï¤Ç¤¹¡£LEFT JOIN
¤Î±¦¤Î¥Æ¡¼¥Ö¥ë¤ËŬ¹ç¤¹¤ë¥ì¥³¡¼¥É¤¬¤Ê¤¤¾ì¹ç¡¢Á´¤Æ¤Î¹àÌܤ¬ NULL ¤ËÀßÄꤵ¤ì
¤¿¹Ô¤¬±¦¤Î¥Æ¡¼¥Ö¥ë¤È¤·¤Æ»ÈÍѤµ¤ì¤Þ¤¹¡£
USING
column-list ¤ÏξÊý¤Î¥Æ¡¼¥Ö¥ë¤Ë¸ºß¤¹¤ë¹àÌܤΥꥹ¥È¤Ç¤¹¡£
A LEFT JOIN B USING (C1,C2,C3...)
¤Ï¡¢°Ọ̃Ū¤Ë¤Ï ON
expression A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3...
¤ÈƱ¤¸¤ÈÄêµÁ
¤µ¤ì¤Þ¤¹¡£
NATURAL LEFT JOIN
¤Ï°Ọ̃Ū¤Ë¤Ï¡¢Î¾Êý¤Î¥Æ¡¼¥Ö¥ë¤Ë
¸ºß¤¹¤ëÁ´¤Æ¤Î¹àÌÜ̾¤ò»ý¤Ä USING
¤ÈƱ¤¸¤ÈÄêµÁ¤µ¤ì¤Þ¤¹¡£
LEFT JOIN
¹½Ê¸¤Ï ODBC ¤È¤Î¸ß´¹¤Î¤¿¤á¤À¤±¤Ë¸ºß¤·¤Þ¤¹¡£
INSERT INTO table [ (column_name,...) ] VALUES (expression,...) or INSERT INTO table [ (column_name,...) ] SELECT ...
expression ¤Ï column_name ¥ê¥¹¥È(¤Þ¤¿¤Ï column_name ¥ê¥¹¥È¤¬Í¿¤¨¤é¤ì¤Ê¤¤ ¾ì¹ç¤Ï¥Æ¡¼¥Ö¥ë)¤ÎÁ°¤Î¹àÌܤò»ÈÍѤ·¤Þ¤¹¡£
¼¡¤Ï¡¢Ê£¿ô¹Ô¤Î INSERT
¥¹¥Æ¡¼¥È¥á¥ó¥È¤òÊÝ»ý¤·¤Þ¤¹:
ORDER BY
Àá¤ò´Þ¤á¤ë¤³¤È¤Ï¤Ç¤¤Þ¤»¤ó¡£
INSERT
¥¹¥Æ¡¼¥È¥á¥ó¥È¤Î¥¿¡¼¥²¥Ã¥È¥Æ¡¼¥Ö¥ë¤Ï¡¢¥¯¥¨¥ê¤Î FROM Àá¤Ë¸½¤ì¤ë¤³
¤È¤¬¤Ç¤¤Þ¤»¤ó¡£
INSERT INTO ... SELECT ...
¤ò»ÈÍѤ¹¤ë¾ì¹ç¡¢C API ´Ø¿ô
mysql_info()
¤Ç¼¡¤Î¾ðÊóʸ»úÎó¤òÆÀ¤ë¤³¤È¤¬¤Ç¤¤Þ¤¹¡£
Records: 100 Duplicates: 0 Warnings: 0
Duplicates ¤Ï¤¤¤¯¤Ä¤«¤Î¥¤
¥ó¥Ç¥Ã¥¯¥¹¤¬½ÅÊ£¤·¤¿¤¿¤á¤Ë½ñ¤½Ð¤µ¤ì¤Ê¤«¤Ã¤¿¹Ô¿ô¤Ç¤¹¡£Warnings ¤Ï NOT
NULL ¤ÈÀë¸À¤µ¤ì¤Æ¤¤¤ë¤Î¤Ë NULL ¤¬ÀßÄꤵ¤ì¤¿¹àÌÜ¿ô¤Ç¤¹¡£¤³¤ì¤é¤Ï¤½¤Î¥Ç¥Õ¥©
¥ë¥ÈÃͤ¬ÀßÄꤵ¤ì¤Þ¤¹¡£
¤³¤Î¾ì¹ç¡¢ÁÞÆþ¤·¤Æ¤¤¤ë¤Î¤ÈƱ¤¸¥Æ¡¼¥Ö¥ë¤«¤é¤Î SELECT ¤Ï ANSI SQL ¤Ç¶Ø¤¸¤é
¤ì¤Æ¤¤¤Þ¤¹¡£Æ±¤¸¼Â¹Ô¤ÇÁÞÆþ¤µ¤ì¤¿¹Ô¤ò SELECT ¤¬¸«¤Ä¤±¤ë¾ì¹ç¤Ë¡¢ÌäÂê¤È¤Ê¤ê
¤Þ¤¹¡£sub select »ÈÍÑ»þ¤Ë¤Ï¡¢¾õ¶·¤Ï´Êñ¤Ë¤È¤Æ¤âº®Í𤷤ޤ¹¡ª
REPLACE INTO table [ (column_name,...) ] VALUES (expression,...) or REPLACE INTO table [ (column_name,...) ] SELECT ...
¤³¤ì¤ÏÀµ³Î¤Ë INSERT
¤È»÷¤¿Æ°¤¤ò¤·¤Þ¤¹¡£¥Æ¡¼¥Ö¥ëÆâ¤ËƱ¤¸¥æ¥Ë¡¼¥¯¥¡¼¤ò
»ý¤Ä¸Å¤¤¥ì¥³¡¼¥É¤¬¤¤¤¯¤Ä¤«¤¢¤ë¾ì¹ç¤Ë¡¢¸Å¤¤¥ì¥³¡¼¥É(°ì¤Ä¤Þ¤¿¤ÏÊ£¿ô)¤¬¤³¤Î
¥ì¥³¡¼¥É¤¬ÁÞÆþ¤µ¤ì¤ëÁ°¤Ëºï½ü¤µ¤ì¤ë¤³¤È¤¬°Û¤Ê¤ê¤Þ¤¹¡£
¡ÖINSERT ¹½Ê¸¡×ÀỲ¾È .
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...)]
¤³¤ì¤Ï¥µ¡¼¥Ð¤ËÃÖ¤«¤ì¤¿¥Æ¥¥¹¥È¥Õ¥¡¥¤¥ë¤«¤é¹Ô¤òÆɤ߹þ¤à¤¿¤á¤Ë»ÈÍѤµ¤ì¡¢¤È
¤Æ¤â¹â®¤Ç¤¹¡£¥µ¡¼¥Ð - ¥¯¥é¥¤¥¢¥ó¥È¥×¥í¥È¥³¥ë¤Ï¤Þ¤ÀÀܳ¾å¤Î¥Õ¥¡¥¤¥ë¤ò¥µ
¥Ý¡¼¥È¤·¤Æ¤¤¤Þ¤»¤ó¡£¥¯¥é¥¤¥¢¥ó¥È¾å¤Ë¤À¤±¥Õ¥¡¥¤¥ë¤¬¤¢¤ë¾ì¹ç¤Ï¡¢LOAD
DATA INFILE
¤ò»ÈÍѤ¹¤ëÁ°¤Ë¡¢²Äǽ¤Ê¤é¤Ð°µ½Ì¤·¤Æ rcp ¤ä
ftp ¤Ç¥µ¡¼¥Ð¤Ë¥³¥Ô¡¼¤·¤Æ¤¯¤À¤µ¤¤¡£¥Æ¥¥¹¥È¥Õ¥¡¥¤¥ë¤Ø¤ÎÁ´¤Æ¤Î¥Ñ
¥¹¤Ï¥Ç¡¼¥¿¥Ù¡¼¥¹¥Ç¥£¥ì¥¯¥È¥ê¤Ø¤ÎÁêÂФǤ¹¡£
¥Æ¥¥¹¥È¥Õ¥¡¥¤¥ë¤Ë¥Ç¡¼¥¿¤ò½ñ¤¹þ¤à¤Ë¤Ï¡¢
SELECT ... INTO OUTFILE 'interval.txt' fields terminated by ','
enclosed by '"' escaped by '\\' lines terminated by '\n' FROM ....
¹½Ê¸¤ò»ÈÍѤ·¤Þ¤¹¡£
Ä̾¥Æ¥¥¹¥È¥Õ¥¡¥¤¥ë·¿¥ª¥×¥·¥ç¥ó¤Ë¤Ï²¿¤âµ½Ò¤¹¤ë¤Ù¤¤Ç¤Ï¤¢¤ê¤Þ¤»¤ó¡£¥Ç
¥Õ¥©¥ë¥È¤Ï tab
ʸ»ú¤Ç¶èÀÚ¤é¤ì¤¿¹àÌܤΥ³¥ó¥Ñ¥¯¥È¥Æ¥¥¹¥È¥Õ¥¡¥¤¥ë¤Ç¡¢
Á´¤Æ¤Î¹Ô¤Ï²þ¹Ô¤Ç½ª¤ê¤Þ¤¹¡£¹àÌÜÆâ¤Î¥¿¥Ö, ²þ¹Ô¤È \
¤Ï \
¤¬Á°
¤ËÃÖ¤«¤ì¤Þ¤¹¡£NULL ¤Ï \N ¤È¤·¤ÆÆɤޤ졢½ñ¤«¤ì¤Þ¤¹¡£
FIELDS TERMINATED BY
¤Î¥Ç¥Õ¥©¥ë¥ÈÃÍ¤Ï \t
¤Ç¤¹¡£
FIELDS [OPTIONALLY] ENCLOSED BY
¤Î¥Ç¥Õ¥©¥ë¥ÈÃÍ¤Ï ''
¤Ç¤¹¡£
FIELDS ESCAPED BY
¤Î¥Ç¥Õ¥©¥ë¥ÈÃÍ¤Ï '\\'
¤Ç¤¹¡£
LINES TERMINATED BY
¤Î¥Ç¥Õ¥©¥ë¥ÈÃÍ¤Ï '\n'
¤Ç¤¹¡£
FIELDS TERMINATED BY
¤È LINES TERMINATED BY
¤Ï1ʸ»ú°Ê¾å¤Ë
¤Ç¤¤Þ¤¹¡£
LINES TERMINATED BY
¤¬¶õʸ»úÎó¤Ç FIELDS TERMINATED BY
¤¬¶õ
¤Ç¤Ê¤±¤ì¤Ð¡¢¹Ô¤â FIELDS TERMINATED BY
¤Ç½ªÃ¼¤µ¤ì¤Þ¤¹¡£
FIELDS TERMINATED BY
¤È FIELDS ENCLOSED BY
¤ÎξÊý¤¬¶õʸ»ú
Îó (''
) ¤Ê¤é¡¢¸ÇÄê¹Ô·Á¼°("¶³¦¤Ê¤·" Æɤ߹þ¤ß·Á¼°)¤òÍ¿¤¨¤Þ¤¹¡£¸ÇÄê
¹Ô¥µ¥¤¥º¤Ç¤Ï¡¢NULL Ãͤ϶õÇòʸ»úÎó¤È¤·¤Æ½ÐÎϤµ¤ì¤Þ¤¹¡£
ENCLOSED BY
Æâ¤Ç OPTIONALLY
¤òµ½Ò¤¹¤ë¤È¡¢SELECT ...
INTO
µ½Ò¤Ç¡¢Ê¸»úÎó¤Ï ENCLOSED BY
¤Ç°Ï¤Þ¤ì¤Þ¤¹¡£
Æó½Å¤Î ENCLOSED BY
ʸ»ú¤Ï¡¢ENCLOSED BY
¤Ç»Ï¤Þ¤ëʸ»úÎ󤫤é
ºï½ü¤µ¤ì¤Þ¤¹¡£Î㤨¤Ð ENCLOSED BY '"'
¤Ç¤Ï:
"The ""BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss
ESCAPED BY
¤¬¶õ¤Ç¤Ê¤±¤ì¤Ð¡¢¼¡¤Îʸ»ú¤Ï¥¨¥¹¥±¡¼¥×ʸ»ú¤¬Á°¤ËÃÖ¤«¤ì¤Þ
¤¹: ESCAPED BY
, ASCII 0
, ¤½¤·¤Æ FIELDS TERMINATED
BY
, FIELDS ENCLOSED BY
¤È LINES TERMINATED BY
Æâ¤ÎºÇ½é¤Î
ʸ»ú¡£
FIELDS ENCLOSED BY
¤¬¶õ¤Ç¤Ê¤±¤ì¤Ð¡¢NULL
¤Ï NULL
ÃÍ
¤È¤·¤ÆÆɤޤì¤Þ¤¹¡£FIELDS ESCAPED BY
¤¬¶õ¤Ç¤Ê¤±¤ì¤Ð \N
¤â
NULL
ÃͤȤ·¤ÆÆɤޤì¤Þ¤¹¡£
REPLACE
¤¬»ÈÍѤµ¤ì¤ë¤È¡¢¿·¤·¤¤¹Ô¤ÏƱ¤¸¥æ¥Ë¡¼¥¯¥¤¥ó¥Ç¥Ã¥¯¥¹¤ò»ý¤Ä
Á´¤Æ¤Î¹Ô¤òÃÖ¤´¹¤¨¤Þ¤¹¡£IGNORE
¤¬»ÈÍѤµ¤ì¤ë¤È¡¢Æ±¤¸¥æ¥Ë¡¼¥¯¥¡¼¤ò
»ý¤Ä¥ì¥³¡¼¥É¤¬´û¤Ë¸ºß¤·¤Æ¤¤¤¿¾ì¹ç¡¢¹Ô¤Ï¥¹¥¥Ã¥×¤µ¤ì¤Þ¤¹¡£¾åµ¥ª¥×¥·¥ç¥ó
¤¬¤Ê¤±¤ì¤Ð¥¨¥é¡¼¤Ë¤Ê¤ê¤Þ¤¹¡£½ÅÊ£¥¤¥ó¥Ç¥Ã¥¯¥¹¤Î¥¨¥é¡¼¤Ë¤Ê¤Ã¤¿¾ì¹ç¡¢¥Æ¥¥¹
¥È¥Õ¥¡¥¤¥ë¤Î»Ä¤ê¤Ï̵»ë¤µ¤ì¤Þ¤¹¡£
LOAD DATA
¤¬¥µ¥Ý¡¼¥È¤µ¤ì¤Ê¤¤¤¤¤¯¤Ä¤«¤Î¥±¡¼¥¹:
FIELDS TERMINATED BY
¤È FIELDS ENCLOSED BY
¤ÎξÊý¤¬¶õ) ¤È BLOB ¹àÌÜ¡£
FIELDS ESCAPED BY
¤¬¶õ¤Ç¡¢¥Ç¡¼¥¿¤¬ LINES TERMINATED BY
¤Þ
¤¿¤Ï FIELDS TERMINATED BY
¤¬Â³¤¯ FIELDS ENCLOSED BY
¤ò´Þ¤ó
¤Ç¤¤¤ë¡£
Á´¤Æ¤Î¹Ô¤Ï¥Æ¡¼¥Ö¥ë¤ËÆɤ߹þ¤Þ¤ì¤Þ¤¹¡£¹Ô¤Î¹àÌܤ¬¾¯¤Ê¤¤¾ì¹ç¤Ï¡¢»Ä¤ê¤Î¹àÌܤË
¤Ï¥Ç¥Õ¥©¥ë¥ÈÃͤ¬ÀßÄꤵ¤ì¤Þ¤¹¡£TIMESTAMP
¹àÌܤϡ¢NULL Ãͤ¬¤½¤ÎÎó¤Ë
Í¿¤¨¤é¤ì¤¿¾ì¹ç¡¢¤Þ¤¿¤Ï¹àÌܥꥹ¥È¤¬»ÈÍѤµ¤ì¤¿»þ¤Ë TIMESTAMP
¹àÌܤ¬¡¢
¤½¤Î¹àÌܥꥹ¥È¤Ë¤Ê¤¤¾ì¹ç¤Ë¡¢¸½ºß¤Î»þ¹ï¤òÀßÄꤷ¤Þ¤¹ (ºÇ¸å¤Î¥±¡¼¥¹¤Ç¤Ï¡¢ºÇ
½é¤Î TIMESTAMP
¹àÌܤÀ¤±¤òÊÝ»ý¤·¤Þ¤¹)¡£
¥»¥¥å¥ê¥Æ¥£¾å¤ÎÍýͳ¤Ë¤è¤ê¡¢¥Æ¥¥¹¥È¥Õ¥¡¥¤¥ë¤Ï¥Ç¡¼¥¿¥Ù¡¼¥¹¥Ç¥£¥ì¥¯¥È¥êÆâ
¤Ë¤¢¤ë¤«Á´°÷¤ËÆɤ߹þ¤ß²Äǽ¤Ç¤Ê¤±¤ì¤Ð¤¤¤±¤Þ¤»¤ó¡£LOAD DATA INFLIE
¤ò»ÈÍѤ·¤¿¤¤³Æ¥æ¡¼¥¶¤Ï¡¢user Æø¢¥Æ¡¼¥Ö¥ë¤Î 'File_priv' ¹àÌܤâ 'Y' ¤Ç¤Ê
¤±¤ì¤Ð¤Ê¤ê¤Þ¤»¤ó¡ª ¡ÖÆø¢¥·¥¹¥Æ¥à¤¬¤É¤Î¤è¤¦¤ËƯ¤¯¤«¡©¡×ÀỲ¾È
LOAD DATA INFILE
¤ÏÁ´¤Æ¤ÎÆþÎϤòʸ»úÎó¤È¤·¤Æ¤ß¤ë¤¿¤á¡¢enum
¤Þ¤¿¤Ï set
¹àÌܤˤϡ¢INSERT
¥¹¥Æ¡¼¥È¥á¥ó¥È¤Ç»ÈÍѤǤ¤ë¤è¤¦
¤Ë¤Ï¡¢¿ôÃͤϻÈÍѤǤ¤Þ¤»¤ó¡£Á´¤Æ¤Î enum
¤È set
¤Ïʸ»úÎó¤Ç
Í¿¤¨¤é¤ì¤ëɬÍפ¬¤¢¤ê¤Þ¤¹¡ª
¥¨¥¹¥±¡¼¥×¹½Ê¸¤Ë¤Ä¤¤¤Æ¤Î¤³¤ì°Ê¾å¤Î¾ðÊó¤Ï ¡Ö¥ê¥Æ¥é¥ë¡£Ê¸»úÎó¤È¿ôÃͤò¤É¤Î¤è¤¦¤Ë½ñ¤¯¤«¡©¡×ÀỲ¾È ¡£
LOAD DATA
Ì䤤¹ç¤ï¤»¤¬¹Ô¤Ê¤ï¤ì¤¿»þ¡¢¼¡¤Î¾ðÊóʸ»úÎó¤¬ C API ´Ø¿ô
mysql_info()
¤ÇÆÀ¤é¤ì¤Þ¤¹¡£
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
Warnings ¤Ï¡¢ÀºÅ٤λ¼º¤Ê¤·¤ÇÃÖ¤«¤ì¤Ê¤«¤Ã¤¿¹àÌÜ¡¢Æɤ߹þ¤ó¤À¥Æ¥¥¹¥È¹Ô¤« ¤éÃͤòÆÀ¤Ê¤«¤Ã¤¿¹àÌÜ(¹Ô¤¬Ã»¤«¤¹¤®¤ë¤ÈȯÀ¸)¡¢Í¿¤¨¤é¤ì¤¿¹àÌܤËŬ¹ç¤Ç¤¤ë¤µ ¤é¤Ê¤ë¥Ç¡¼¥¿¤ò»ý¤Ä¹Ô¤ÇÁý²Ã¤·¤Þ¤¹¡£warning ¤Ï¤Þ¤¿ time, date, timestamp ¤Þ¤¿¤Ï datetime ¹àÌܤ¬ 0 ¤ËÀßÄꤵ¤ì¤¿»þ¤Ë¤âÍ¿¤¨¤é¤ì¤Þ¤¹¡£
Á´¤Æ¤Î¹àÌܤò¥í¡¼¥É¤¹¤ëÎã:
LOAD DATA INFILE 'persondata.text' INTO TABLE persondata;
¡Ö»ä¤Î¥Æ¡¼¥Ö¥ë¤ò¤Ç¤¤ë¤À¤±Â®¤¯/¾®¤µ¤¯°·¤¦ÊýË¡¤Ï¡©¡×ÀỲ¾È
UPDATE table SET column=expression,... WHERE where_definition
Á´¤Æ¤Î¹¹¿·¤Ïº¸¤«¤é±¦¤Ë¹Ô¤Ê¤ï¤ì¤Þ¤¹¡£expression Æâ¤Ç¹àÌܤò¥¢¥¯¥»¥¹¤¹¤ë¤È¡¢ ¹¹¿·¤Ï¹àÌܤθ½ºß¤ÎÃÍ(Í¿¤¨¤é¤ì¤¿Ãͤޤ¿¤Ï¥Ç¥Õ¥©¥ë¥ÈÃÍ)¤ò»ÈÍѤ·¤Þ¤¹¡£
UPDATE persondata SET count=count+1
UPDATE
¥¹¥Æ¡¼¥È¥á¥ó¥È¤Ï¤¤¤¯¤Ä¤Î¹Ô¤¬¼ÂºÝ¤ËÊѹ¹¤µ¤ì¤¿¤«¤òÊÖ¤·¤Þ¤¹¡£
MySQL 3.22 ¤Ç¤Ï mysql_info()
¤ÏŬ¹ç¤·¤¿¹Ô¿ô¤È¹¹¿·¤µ¤ì¤¿
¹Ô¿ô¤È¡¢¹¹¿·Ãæ¤Ë¤¤¤¯¤Ä¤Î·Ù¹ð¤òÆÀ¤¿¤«¤òÊÖ¤·¤Þ¤¹¡£
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]
¥Ç¡¼¥¿¥Ù¡¼¥¹¡¢¥Æ¡¼¥Ö¥ë¡¢¹àÌܤˤĤ¤¤Æ¤Î¾ðÊó¤òÍ¿¤¨¤Þ¤¹¡£LIKE wild
Éô¤¬»ÈÍѤµ¤ì¤¿¾ì¹ç¡¢wild
ʸ»úÎó¤ÏÄ̾ï¤Î SQL ¥ï¥¤¥ë¥É¥«¡¼¥É (% ¤È _)
¤Ç¤¹¡£FIELDS
¤Ï COLUMNS
¤ÎÊÌ̾¤È¤·¤Æ»ÈÍѤµ¤ì¡¢KEYS
¤Ï INDEXES
¤ÎÊÌ̾¤È¤·¤Æ»ÈÍѤµ¤ì¤Þ¤¹¡£
STATUS
¤Ï¡¢mysqladmin status
¤ÈƱÍͤˡ¢¥µ¡¼¥Ð¤«¤é¤Î¥¹¥Æ¡¼
¥¿¥¹¾ðÊó¤òÍ¿¤¨¤Þ¤¹¡£½ÐÎϤϼ¡¤È¤Ï°Û¤Ê¤ë¤«¤â¤·¤ì¤Þ¤»¤ó:
Uptime | Running_threads | Questions | Reloads | Open_tables
|
119 | 1 | 4 | 1 | 3 |
VARIABLES
¤Ï MySQL ¥·¥¹¥Æ¥àÊÑ¿ô¤Î¤¤¤¯¤Ä¤«¤ÎÃͤò¼¨¤·¤Þ¤¹¡£
¤³¤ì¤é¤ÎÊÑ¿ô¤Î¿¤¯¤Ï¡¢mysqld
¤Ø¤ÎÍÍ¡¹¤Ê¥ª¥×¥·¥ç¥ó¤ÇÊѹ¹¤Ç¤¤Þ¤¹¡ª
EXPLAIN SELECT select_options
¥Æ¡¼¥Ö¥ë¤ò¤É¤Î¤è¤¦¤Ë¤É¤Î½ç¤Ç·ë¹ç¤¹¤ë¤Î¤«¤Î¾ðÊó¤òÍ¿¤¨¤Þ¤¹¡£EXPLAIN
¤Î½õ
¤±¤Ç¡¢¥ì¥³¡¼¥É¤ò¸«¤Ä¤±¤ë¤¿¤á¤Ë¥¡¼¤ò»ÈÍѤ¹¤ë¤è¤ê®¤¤ select ¤òÆÀ¤ë¤¿¤á¤Ë¡¢
¤µ¤é¤Ê¤ë¥¤¥ó¥Ç¥Ã¥¯¥¹¤ò¥Æ¡¼¥Ö¥ë¤ËÄɲ乤٤»þ¤ò¸«¤ë¤³¤È¤¬¤Ç¤¤Þ¤¹¡£¤Þ¤¿¡¢¥ª¥×¥Æ¥£
¥Þ¥¤¥¶¤¬ºÇŬ¤Ê½ç¤Ç¥Æ¡¼¥Ö¥ë¤ò·ë¹ç¤¹¤ë¤«¤É¤¦¤«¤ò¸«¤ë¤³¤È¤â¤Ç¤¤Þ¤¹¡£select ¤Ø¤Î
STRAIGHT_JOIN
¥ª¥×¥·¥ç¥ó¤Ç¡¢ÆÃÄê¤Î·ë¹ç½ç½ø¤Î»ÈÍѤò¥ª¥×¥Æ¥£¥Þ¥¤¥¶
¤Ë¶¯À©¤¹¤ë¤³¤È¤¬¤Ç¤¤Þ¤¹¡£
ÍÍ¡¹¤Ê·ë¹ç¥¿¥¤¥×:
system
const
eq_ref
ref
range
all
¤³¤ì¤Ï EXPLAIN
¤Î½õ¤±¤ÇºÇŬ²½¤µ¤ì¤¿ join ¤ÎÎã¤Ç¤¹¡£
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn, tt.ProjectReference, tt.EstimatedShipDate, tt.ActualShipDate, tt.ClientID, tt.ServiceCodes, tt.RepetitiveID, tt.CurrentProcess, tt.CurrentDPPerson, tt.RecordVolume, tt.DPPrinted, et.COUNTRY, et_1.COUNTRY, do.CUSTNAME FROM tt, et, et AS et_1, do WHERE tt.SubmitTime Is Null and tt.ActualPC = et.EMPLOYID and tt.AssignedPC = et_1.EMPLOYID and tt.ClientID = do.CUSTNMBR;
EXPLAIN
¤Ï¼¡¤òÊÖ¤·¤Þ¤¹:
table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 do ALL PRIMARY NULL NULL NULL 2135 et_1 ALL PRIMARY NULL NULL NULL 74 tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 range checked for each record (key map: 35)
¤³¤Î¾ì¹ç MySQL ¤ÏÁ´¤Æ¤Î¥Æ¡¼¥Ö¥ë¤Ë´°Á´¤Ê·ë¹ç¤ò¹Ô¤Ã¤Æ¤¤¤Þ¤¹¡ª ³Æ ¥Æ¡¼¥Ö¥ëÆâ¤Î¹Ô¤Î¿ô¤ÎÀ½Éʤ¬¸¡Æ¤¤µ¤ì¤ë¤Î¤Ç¡¢¤³¤ì¤Ï¤«¤Ê¤êŤ¤»þ´Ö¤«¤«¤ê¤Þ¤¹¡ª Á´¤Æ¤Î¥Æ¡¼¥Ö¥ë¤¬ 1000 ¥ì¥³¡¼¥É»ý¤Ã¤Æ¤¤¤¿¾ì¹ç¡¢MySQL ¤Ï 1000^4 = 1000000000000 ¹Ô¤òÄ´¤Ù¤Ê¤¤¤È¤¤¤±¤Þ¤»¤ó¡£¥Æ¡¼¥Ö¥ë¤¬¤â¤Ã¤ÈÂ礤¤¤È¡¢¤É¤ì¤¯ ¤é¤¤¤Î»þ´Ö¤«¤«¤ë¤«ÁÛÁü¤Ç¤¤Þ¤¹...¡£
¤³¤Î¾ì¹ç¡¢ºÇ½é¤Î´Ö°ã¤¤¤Ï MySQL ¤Ï¤Þ¤À¡¢°Û¤Ê¤Ã¤¿Àë¸À¤¬¤µ¤ì¤¿¹àÌÜ ¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤òǽΨŪ¤Ë»ÈÍѤ·¤Ê¤¤¤³¤È¤Ç¤¹: (varchar() ¤È char() ¤Ï¤³¤Î ʸ̮¤Ç¤Ï°Û¤Ê¤ê¤Þ¤»¤ó)
¤³¤Î¾ì¹ç¡¢tt.ActualPC
¤Ï char(10)
¤Ç et.EMPLOYID
¤Ï
char(15)
¤Ç¤¹¡£
½¤Àµ:
mysql> alter table tt change ActualPC ActualPC varchar(15);
¤½¤·¤Æ¾å¤Î EXPLAIN ¤Ï¼¡¤ò¼¨¤·¤Þ¤¹:
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used do ALL PRIMARY NULL NULL NULL 2135 range checked for each record (key map: 1) et_1 ALL PRIMARY NULL NULL NULL 74 range checked for each record (key map: 1) et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
¤³¤ì¤Ï´°Á´¤Ç¤Ï¤¢¤ê¤Þ¤»¤ó¤¬¡¢¤«¤Ê¤ê¤Þ¤·¤Ç¤¹¡£¤³¤Î¥Ð¡¼¥¸¥ç¥ó¤Ï¿ôÉäǼ¹Ԥµ ¤ì¤Þ¤¹¡£
¤µ¤é¤Ë¡¢
mysql> alter table tt change AssignedPC AssignedPC varchar(15), change ClientID Clientid varchar(15);
EXPLAIN
¤«¤é¼¡¤¬ÆÀ¤é¤ì¤Þ¤¹:
table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 tt ref AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.Clientid 1
Which is 'almost' as good as it can get. The problem is that
MySQL assumes that tt.AcutalPC
is evenly distributed which
isn't the case in the tt.
Fortunately it is easy to tell MySQL about this:
shell> isamchk --analyze PATH_TO_MYSQL_DATABASE/tt shell> mysqladmin refresh
And now the join is 'perfect':
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1 et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.Clientid 1
(DESCRIBE | DESC) table [column]
¹àÌܤˤĤ¤¤Æ¤Î¾ðÊó¤òÍ¿¤¨¤Þ¤¹¡£¤³¤Î¥³¥Þ¥ó¥É¤Ï Oracle ¤Î¸ß´¹¤Î¤¿¤á¤Ë¤¢¤ê¤Þ ¤¹¡£ ¡ÖSHOW ¹½Ê¸¡£¥Æ¡¼¥Ö¥ë¤ä¹àÌܤʤɤˤĤ¤¤Æ¤Î¾ðÊó¤òÆÀ¤ë¡×ÀỲ¾È ¡£¹àÌܤϹàÌÜ̾¤Þ¤¿¤Ïʸ»úÎó¤Ç¤¹¡£Ê¸»úÎó¤Ï¥ï¥¤¥ë¥É¥«¡¼¥É¤ò ´Þ¤á¤Þ¤¹¡£
LOCK TABLES
¹½Ê¸LOCK TABLES table_name [AS alias] READ|WRITE [, table_name READ|WRITE] ... UNLOCK TABLES
¤³¤Î¥¹¥ì¥Ã¥É¤Î¤¿¤á¤Ë¥Æ¡¼¥Ö¥ë¤ò¥í¥Ã¥¯¤·¤Þ¤¹¡£¥¹¥ì¥Ã¥É¤¬¥Æ¡¼¥Ö¥ë¤Ë READ ¥í¥Ã ¥¯¤ò»ý¤Ä¾ì¹ç¡¢¤½¤Î¥¹¥ì¥Ã¥É(¤È¾¤ÎÁ´¤Æ¤Î¥¹¥ì¥Ã¥É)¤Ï¥Æ¡¼¥Ö¥ë¤«¤é¤ÎÆɤ߹þ¤ß ¤À¤±¤¬¤Ç¤¤Þ¤¹¡£¥¹¥ì¥Ã¥É¤¬¥Æ¡¼¥Ö¥ë¤Ë WRITE ¥í¥Ã¥¯¤ò»ý¤Ä¾ì¹ç¡¢¤³¤Î¥¹¥ì¥Ã ¥É¤À¤±¤¬¥Æ¡¼¥Ö¥ë¤Î READ ¤È WRITE ¤¬¤Ç¤¤Þ¤¹¡£Á´¤Æ¤Î¥¹¥ì¥Ã¥É¤Ï¤½¤ì¤é¤¬Á´ ¤Æ¤Î¥í¥Ã¥¯¤òÆÀ¤ë¤Þ¤ÇÂÔ¤Á¤Þ¤¹(¥¿¥¤¥à¥¢¥¦¥È̵¤·)¡£
LOCK TABLES
¤ò»ÈÍѤ¹¤ë¤È¤¡¢»ÈÍѤ·¤è¤¦¤È¤¹¤ëÁ´¤Æ¤Î¥Æ¡¼¥Ö¥ë¤ò¥í¥Ã
¥¯¤¹¤Ù¤¤Ç¤¹¡ª ¤³¤Î¥Ý¥ê¥·¡¼¤Ï¥Æ¡¼¥Ö¥ë¥í¥Ã¥¯¤ò¥Ç¥Ã¥É¥í¥Ã¥¯¥Õ¥ê¡¼¤Ë¤¹¤ë¤³
¤È¤ò³Î¤«¤Ë¤·¤Þ¤¹¡£
LOCK TABLES trans READ, customer AS c WRITE SELECT SUM(value) FROM trans WHERE customer_id= #some_id#; UPDATE customer SET total_value=#value_from_last_statement# WHERE customer_id=#some_id# UNLOCK TABLES
Á´¤Æ¤Î¥Æ¡¼¥Ö¥ë¤Ï¡¢Â¾¤Î LOCK TABLES
¤òȯ¹Ô¤·¤¿»þ¡¢¤Þ¤¿¤Ï¥µ¡¼¥Ð¤Ø¤Î
Àܳ¤¬¥¯¥í¡¼¥º¤µ¤ì¤¿¾ì¹ç¡¢¼«Æ°Åª¤Ë¥¢¥ó¥í¥Ã¥¯¤µ¤ì¤Þ¤¹¡£
Ä̾ï¤Ï¥Æ¡¼¥Ö¥ë¤ò¥í¥Ã¥¯¤¹¤ëɬÍפϤ¢¤ê¤Þ¤»¤ó¡£¤È¤Ë¤«¤¯¥Æ¡¼¥Ö¥ë¤ò¥í¥Ã¥¯¤·¤¿ ¤¤»þ¤Ë¤Ï¤¤¤¯¤Ä¤«¤Î¾ì¹ç¤¬¤¢¤ê¤Þ¤¹:
MySQL
¤Ï¥È¥é¥ó¥¶¥¯¥·¥ç¥ó´Ä¶¤ò¥µ¥Ý¡¼¥È¤·¤Ê¤¤¤¿¤á¡¢Â¾¤Î¥¹¥ì¥Ã¥É¤¬
Æɤ߹þ¤ßÅÔ¹¹¿·¤Î´Ö¤ËÍè¤Ê¤¤¤³¤È¤òÊݾڤ·¤¿¤¤¾ì¹ç¤Ï¡¢lock tables ¤ò»ÈÍѤ¹¤ë
ɬÍפ¬¤¢¤ê¤Þ¤¹¡£Î㤨¤ÐÁ°¤ÎÎã¤Ï°ÂÁ´¤Î¤¿¤á¤Ë¤Ï LOCK TABLES
¤òɬÍפÈ
¤·¤Þ¤¹¡ª LOCK TABLES
¤ò»ÈÍѤ·¤Ê¤¤¾ì¹ç¡¢SELECT ¤È UPDATE ¥¹¥Æ¡¼¥È
¥á¥ó¥È¤Î´Ö¤Ë¡¢Ã¯¤«¤¬¿·¤·¤¤ 'trans' ¹Ô¤òÁÞÆþ¤¹¤ëÊѹ¹¤¬¤Ç¤¤Þ¤¹¡£
UPDATE customer set value=value+new_value
) ¤Þ¤¿¤Ï
LAST_INSERT_ID() ´Ø¿ô¤Î»ÈÍѤˤè¤ê¡¢Â¿¤¯¤Î¾ì¹ç LOCK TABLES
¤ò²óÈò
¤Ç¤¤Þ¤¹¡£
¤¤¤¯¤Ä¤«¤Î¾ì¹ç¡¢¥æ¡¼¥¶¥ì¥Ù¥ë¥í¥Ã¥¯: GET_LOCK() ¤È RELEASE_LOCK() ¤Î»ÈÍÑ ¤Ë¤è¤Ã¤Æ¤â²ò·è¤Ç¤¤Þ¤¹¡£¤³¤ì¤é¤Î¥í¥Ã¥¯¤Ï¥µ¡¼¥ÐÆâ¤Î¥Ï¥Ã¥·¥å¥Æ¡¼¥Ö¥ëÆâ¤ËÊÝ »ý¤µ¤ì¡¢¹â®¤Î¤¿¤á pthread_mutex ¤Ç¼ÂÁõ¤µ¤ì¤Þ¤·¤¿¡£ ¡Ö¤½¤Î¾¤Î´Ø¿ô¡×ÀỲ¾È
SET [OPTION] SQL_VALUE_OPTION=value, ...
»ÈÍѤµ¤ì¤¿¥ª¥×¥·¥ç¥ó¤Ï¼ÂºÝ¤Ë¤Ï¸½ºß¤Î¥»¥Ã¥·¥ç¥óÁ´ÂΤËÂФ·¤Æ»Ä¤ê¤Þ¤¹¡£
ÍÍ¡¹¤Ê¥ª¥×¥·¥ç¥ó:
SQL_SELECT_LIMIT=value
SQL_BIG_TABLES= 0 | 1
The table ### is full
¤Ë¤Ê¤ë¤³¤È¤Ï¤¢¤ê¤Þ¤»¤ó¡£
¿·¤·¤¤Àܳ¤Î¥Ç¥Õ¥©¥ë¥ÈÃÍ¤Ï 0 ¤Ç¤¹(¥á¥â¥ê°ì»þ¥Æ¡¼¥Ö¥ë¤ò»ÈÍѤ·¤Þ¤¹)¡£
SQL_BIG_SELECTS= 0 | 1
WHERE
¥¹¥Æ¡¼
¥È¥á¥ó¥È¤¬È¯¹Ô¤µ¤ì¤¿»þ¤ËÍÍѤǤ¹¡£max_join_size
¹Ô°Ê¾å¤ò¸¡Æ¤¤¹¤ë
¤è¤¦¤Ê SELECT
¤¬Â礤ʥ¯¥¨¥ê¤ÈÄêµÁ¤µ¤ì¤Þ¤¹¡£¿·¤·¤¤Àܳ¤Î¥Ç¥Õ¥©¥ë
¥ÈÃÍ¤Ï 0 ¤Ç¤¹(¤³¤ì¤ÏÁ´¤Æ¤Î SELECT ¤òµö¤·¤Þ¤¹)¡£
CHARACTER SET character_set_name | DEFAULT
cp1251_koi8
¤À¤±¤Ç¤¹¤¬¡¢¥Õ¥¡¥¤¥ë
mysql_source_directory/sql/convert.cc
¤òÊÔ½¸¤¹¤ë¤³¤È¤Ë¤è¤ê¡¢¿·¤·
¤¤¥Þ¥Ã¥Ô¥ó¥°¤ò´Êñ¤ËÄɲäǤ¤Þ¤¹¡£¥Ç¥Õ¥©¥ë¥È¥Þ¥Ã¥Ô¥ó¥°¤òÌ᤹¤Ë¤Ï
character_set_name ¤È¤·¤Æ DEFAULT
¤ò»ÈÍѤ·¤Þ¤¹¡£
SQL_LOG_OFF= 0 | 1
TIMESTAMP= timestamp_value | DEFAULT
LAST_INSERT_ID= #
GRANT (ALL PRIVILEGES | (SELECT, INSERT, UPDATE, DELETE, REFERENCES (column list), USAGE)) ON table TO user,... [WITH GRANT OPTION]
¤³¤Î¥³¥Þ¥ó¥É¤Ï²¿¤â¹Ô¤Ê¤¤¤Þ¤»¤ó¡£¤³¤ì¤¬ MySQL ¤Ë¤¢¤ë¤Î¤Ï¸ß´¹À¤Î ¤¿¤á¤À¤±¤Ç¤¹¡£¡Ê¾¤Î SQL ¥µ¡¼¥Ð¤«¤é¤Î¥³¡¼¥É¤Î°Ü¹Ô¤ò¤è¤ê´Êñ¤Ë¤¹¤ë¤¿¤á¡Ë¡£ MySQL ¤ÎÆø¢¤Ï MySQL ¾µÇ§¥Æ¡¼¥Ö¥ë¤ÇÁàºî¤µ¤ì¤Þ¤¹¡£ ¡ÖÆø¢¥·¥¹¥Æ¥à¤¬¤É¤Î¤è¤¦¤ËƯ¤¯¤«¡©¡×ÀỲ¾È
CREATE [UNIQUE] INDEX index_name ON table_name ( column_name[(length)],... )
¥Ð¡¼¥¸¥ç¥ó¤¬ 3.22 ¤è¤ê¤âÁ°¤Î MySQL ¤Ç¤Ï¡¢¤³¤Î´Ø¿ô¤Ï²¿¤â¹Ô¤Ê¤¤¤Þ¤»¤ó¡£¤³
¤ì¤Ï¥¤¥ó¥Ç¥Ã¥¯¥¹¤òÀ¸À®¤¹¤ë ALTER TABLE
¸Æ¤Ó½Ð¤·¤Ë¥Þ¥Ã¥×¤µ¤ì¤Æ¤¤¤Þ
¤¹¡£ ¡ÖALTER TABLE ¹½Ê¸¡×ÀỲ¾È
Ä̾Á´¤Æ¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ï CREATE TABLE
¤ÈƱ»þ¤ËÀ¸À®¤Ç¤¤Þ¤¹¡£
¡ÖCREATE TABLE ¹½Ê¸¡×ÀỲ¾È
(col1, col2)
¤Ï2¤Ä¤Î¹àÌܤ«¤é¤Ê¤ëÊ£¹ç¥¤¥ó¥Ç¥Ã¥¯¥¹¤òºîÀ®¤·¤Þ¤¹¡£¥¤
¥ó¥Ç¥Ã¥¯¥¹¤ÏÍ¿¤¨¤é¤ì¤¿¹àÌܤηë¹ç¤Î¤è¤¦¤Ë¸«¤¨¤Þ¤¹¡£CREATE TABLE
Ãæ
¤Ç INDEX(col1,col2)
¤ÎÂå¤ï¤ê¤Ë INDEX(col1)
,
INDEX(col2)
¤ò»È¤¨¤Ð¡¢°ì¤Ä¤ÎÊ£¹ç¥¤¥ó¥Ç¥Ã¥¯¥¹¤ÎÂå¤ï¤ê¤ËÆó¤Ä¤ÎÊÌ¡¹
¤Î¥¡¼¤¬Âå¤ï¤ê¤ËÆÀ¤é¤ì¤Þ¤¹¡£
SELECT * FROM table WHERE col1=# AND col2=#
(col1,col2)
¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤Î¾ì¹ç¤Ï¡¢Àµ¤·¤¤¹Ô¤¬Ä¾ÀÜ fetch ¤µ¤ì¤Þ¤¹¡£
(col1)
, (col2)
¤Î¾ì¹ç¤Ï¡¢¤É¤Á¤é¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤ÎÊý¤¬¤è¤ê¾¯
¤Ê¤¤¹Ô¤ò¸«¤Ä¤«¤ë¤«¤ò¥ª¥×¥Æ¥£¥Þ¥¤¥¶¤¬·èÄꤷ¡¢¤³¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤¬¹Ô¤ò fetch
¤¹¤ë¤¿¤á¤Ë»ÈÍѤµ¤ì¤Þ¤¹¡£
¥Æ¡¼¥Ö¥ë¤¬¥¤¥ó¥Ç¥Ã¥¯¥¹ (col1,col2,col3...)
¤ò»ý¤Ã¤Æ¤¤¤ë¾ì¹ç¤Ï¡¢¤³
¤ÎƬ¤ÎÊý¤¬¥ª¥×¥Æ¥£¥Þ¥¤¥¶¤Ë¤è¤Ã¤Æ¹Ô¤ò¸«¤Ä¤±¤ë¤¿¤á¤Ë»ÈÍѤµ¤ì¤Þ¤¹¡£¤³¤ì¤Ï¡¢
¼¡¤Î¤è¤¦¤Ê¸¡º÷ǽÎϤò¤¢¤Ê¤¿¤ËÍ¿¤¨¤ë¤³¤È¤ò°ÕÌ£¤·¤Þ¤¹: (col1)
¤½¤·¤Æ
(col1,col2)
¤½¤·¤Æ (col1,col2,col3)
...
MySQL ¤Ï¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¤òÄ̤·¤Æ¹Ô¤òÇÛÃÖ¤¹¤ë¤¿¤á¤Ë¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹ ¤Î°ìÉôʬ¤ò»ÈÍѤ¹¤ë¤³¤È¤Ï¤Ç¤¤Þ¤»¤ó¡£
(col1,col2,col3)
ÄêµÁ¤Ç¤Ï:
SELECT * FROM table WHERE col1=# SELECT * FROM table WHERE col2=# SELECT * FROM table WHERE col2=# and col3=#
ºÇ½é¤Î¥¯¥¨¥ê¤À¤±¤¬¥¤¥ó¥Ç¥Ã¥¯¥¹¤ò»ÈÍѤ·¤Þ¤¹¡£
LIKE
°ú¿ô¤¬¥ï¥¤¥ë¥Éʸ»ú¤Ç»Ï¤Þ¤é¤Ê¤¤Äê¿ôʸ»úÎó¤Î¾ì¹ç¡¢
MySQL ¤Ï¥¤¥ó¥Ç¥Ã¥¯¥¹¤â»ÈÍѤ·¤Þ¤¹:
¼¡¤Ï¥¤¥ó¥Ç¥Ã¥¯¥¹¤ò»ÈÍѤ·¤Þ¤¹:
SELECT * from table WHERE key_column like "Patrick%"; SELECT * from table WHERE key_column like "Pat%_ck%";
¾å¤Î¥±¡¼¥¹¤Ç¤Ï¡¢Patrick <= key_column < Patric1
¤È Pat <=
key_column < Pau
¤Î¹Ô¤À¤±¤¬¹Í褵¤ì¤Þ¤¹¡£
¼¡¤Î select ¤Ï¥¤¥ó¥Ç¥Ã¥¯¥¹¤ò»ÈÍѤ·¤Þ¤»¤ó:
SELECT * from table WHERE key_column like "%Patrick%"; SELECT * from table WHERE key_column like other_column;
column_name(length)
¹½Ê¸¤Ç string
¹àÌܤǤÀ¤±°ìÉô¤Î¥¤¥ó¥Ç¥Ã
¥¯¥¹¤òµ½Ò¤Ç¤¤Þ¤¹¡£¤³¤ì¤Ï¥¤¥ó¥Ç¥Ã¥¯¥¹¥Õ¥¡¥¤¥ë¤ò¤è¤ê¾®¤µ¤¯¤·¤Þ¤¹¡£
CREATE INDEX part_of_name ON customer (name(10))
¿¤¯¤Î̾Á°¤ÏºÇ½é¤Î10ʸ»ú¤¬°Û¤Ê¤ë¤³¤È¤ÏÁ´¤¯ÉáÄ̤ʤΤǡ¢¾å¤ÎÄêµÁ¤Ï̾Á°¤Î¸¡ º÷¤òÃÙ¤¯¤·¤Þ¤»¤ó¤¬¡¢Â¿¤¯¤Î¥Ç¥£¥¹¥¯¤òÀáÌó¤·¡¢ÁÞÆþ¤ò®¤¯¤·¤Þ¤¹¡ª
DROP INDEX index_name
¤³¤Î´Ø¿ô¤Ï¥Ð¡¼¥¸¥ç¥ó 3.22 ¤è¤êÁ°¤Î MySQL
¤Ç¤Ï²¿¤â¹Ô¤¤¤Þ¤»¤ó¡£¤³¤ì
¤Ï INDEX
¤Þ¤¿¤Ï UNIQUE
ÄêµÁ¤òÇË´þ¤¹¤ë ALTER TABLE
¸Æ¤Ó½Ð¤·¤Ë¥Þ¥Ã¥×¤µ¤ì¤Æ¤¤¤Þ¤¹¡£ ¡ÖALTER TABLE ¹½Ê¸¡×ÀỲ¾È
MySQL ¤Ï # ¤«¤é¹ÔËö¤Þ¤Ç
¤È /* Ê£¿ô¹Ô */
¥³¥á¥ó¥È
·Á¼°¤ò¥µ¥Ý¡¼¥È¤·¤Þ¤¹¡£
select 1+1; # This comment is to the end of line select 1 /* in-line-comment */ + 1; select 1+ /* This will be ignored */ 1;
MySQL ¤Ï --
ANSI SQL ·Á¼°¥³¥á¥ó¥È¤ò¥µ¥Ý¡¼¥È¤·¤Þ¤»¤ó¡£
¡Ö¥³¥á¥ó¥È³«»Ï¤È¤·¤Æ¤Î --¡×ÀỲ¾È ¡£
CREATE FUNCTION <function_name> RETURNS [string|real|integer] SONAME <name_of_shared_library> DROP FUNCTION <function_name>
¥æ¡¼¥¶ÄêµÁ²Äǽ´Ø¿ô (UDF:User definable functions) ¤Ï¡¢ABS()
¤È
concat()
¤Î¤è¤¦¤Ê¥Í¥¤¥Æ¥£¥Ö MySQL ´Ø¿ô¤È¤·¤Æµ¡Ç½¤¹¤ë¿·¤·
¤¤´Ø¿ô¤ò MySQL ¤Ë³ÈÄ¥¤¹¤ëÊýË¡¤Ç¤¹¡£UDF ¤Ï C ¤Þ¤¿¤Ï C++ ¤Ç½ñ¤«¤ì¡¢
OS ¤ÇưŪ¥í¡¼¥É¤¬µ¡Ç½¤¹¤ë¤³¤È¤òÍ׵ᤷ¤Þ¤¹¡£¥½¡¼¥¹ÇÛÉۤϡ¢£µ¤Ä¤Î¿·¤·¤¤´Ø
¿ô¤òÄêµÁ¤¹¤ë¥Õ¥¡¥¤¥ë `udf_example.cc' ¤ò´Þ¤ó¤Ç¤¤¤Þ¤¹¡£
´Ø¿ô̾¡¢·¿¡¢¶¦Í¥é¥¤¥Ö¥é¥ê¤Ï 'mysql' ¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤Î¿·¤·¤¤¥·¥¹¥Æ¥à¥Æ¡¼ ¥Ö¥ë 'func' ¤ËÊݸ¤µ¤ì¤Þ¤¹¡£¿·¤·¤¤´Ø¿ô¤ÎºîÀ®¤ò²Äǽ¤Ë¤¹¤ë¤¿¤á¤Ë¤Ï¡¢¥Ç¡¼¥¿ ¥Ù¡¼¥¹ 'mysql' ¤Ë½ñ¤¹þ¤ß¸¢¤¬É¬ÍפǤ¹¡£MySQL ¤ò --skip-grant-tables ¤Ç³«»Ï¤¹¤ë¾ì¹ç¤Ï¡¢UDF ½é´ü²½¤â¥¹¥¥Ã¥×¤µ¤ì¤Þ¤¹¡£
³ÆÄêµÁ´Ø¿ô¤Ï xxxx_init ´Ø¿ô¤È xxx_deinit ´Ø¿ô¤ò»ý¤Á¤Þ¤¹¡£init ´Ø¿ô¤Ï´Ø¿ô ¤Î¤¿¤á¤Ë¥á¥â¥ê¤ò³ä¤êÅö¤Æ¡¢·ë²Ì¤ÎºÇÂçĹ(ʸ»úÎó´Ø¿ô)¡¢¾®¿ôÅÀ¤Î·å¿ô(double ´Ø¿ô)¡¢·ë²Ì¤¬ null Ãͤˤʤ뤫¤É¤¦¤«¤Ë¤Ä¤¤¤Æ main ´Ø¿ô¤ËÃΤ餻¤Þ¤¹¡£
´Ø¿ô¤¬ 'error' °ú¿ô¤ò 1 ¤ËÀßÄꤹ¤ë¾ì¹ç¡¢¤½¤Î´Ø¿ô¤Ï¤½¤ì°Ê¾å¸Æ¤Ð¤ì¤º¡¢ mysqld ¤Ï¤³¤Î´Ø¿ô¤Î¥¤¥ó¥¹¥¿¥ó¥¹¤ò¸Æ¤Ó½Ð¤¹Á´¤Æ¤Ë¤Ä¤¤¤Æ NULL ¤òÊÖ¤·¤Þ¤¹¡£
´Ø¿ô¤Ø¤ÎÁ´¤Æ¤Îʸ»úÎó°ú¿ô¤Ï¡¢¥Ð¥¤¥Ê¥ê¥Ç¡¼¥¿¤ÎÁàºî¤ò¤Ç¤¤ë¤è¤¦¤Ë¡¢Ê¸»úÎó¤Î ¥Ý¥¤¥ó¥¿ + Ťµ¤È¤·¤ÆÍ¿¤¨¤é¤ì¤Þ¤¹¡£Á´¤Æ¤Î´Ø¿ô¤Ï¥¹¥ì¥Ã¥É¥»¡¼¥Õ¤Ç¤Ê¤¤¤È¤¤ ¤±¤Ê¤¤¤³¤È¤òÃΤäƤ¤¤Æ¤¯¤À¤µ¤¤¡£¤³¤ì¤ÏÊѹ¹¤µ¤ì¤ë¤è¤¦¤Ê¥°¥í¡¼¥Ð¥ëÊÑ¿ô¤Þ¤¿ ¤ÏÀÅŪÊÑ¿ô¤Î³ä¤êÅö¤Æ¤Ïµö¤µ¤ì¤Ê¤¤¤³¤È¤ò°ÕÌ£¤·¤Þ¤¹¡ª ¥á¥â¥ê¤òɬÍפȤ¹¤ë¾ì ¹ç¤Ï¡¢init ´Ø¿ôÆâ¤Ç³ä¤êÅö¤Æ¡¢__deinit ´Ø¿ô¤Ç¤³¤ì¤ò²òÊü¤¹¤Ù¤¤Ç¤¹¡£
ưŪ¥í¡¼¥É¥Õ¥¡¥¤¥ë¤Ï¶¦Í²Äǽ¤È¤·¤Æ¥³¥ó¥Ñ¥¤¥ë¤µ¤ì¤ë¤Ù¤¤Ç¤¹ (¼¡¤Î¤è¤¦¤Ê¤â
¤Î: gcc -shared -o udf_example.so myfunc.cc
)¡£¼¡¤ò¹Ô¤¦¤³¤È¤ÇÀµ¤·
¤¤Á´¤Æ¤Î¥¹¥¤¥Ã¥Á¤ò´Êñ¤ËÆÀ¤ë¤³¤È¤¬¤Ç¤¤Þ¤¹: cd sql ; make
udf_example.o
½ñ¤¹þ¤ß¤ò¹Ô¤¦¥³¥ó¥Ñ¥¤¥ë¹Ô¤ò¼è¤ê½Ð¤·¡¢¹ÔËö¶á¤¯¤Î '-c' ¤ò
ºï½ü¤·¤Æ¡¢¥³¥ó¥Ñ¥¤¥ë¹Ô¤ÎºÇ¸å¤Ë -o udf_example.so ¤òÄɲ䷤Ƥ¯¤À¤µ¤¤¡£·ë
²Ì¤Î¥é¥¤¥Ö¥é¥ê (udf_example.so) ¤Ï ld ¤Ë¤è¤Ã¤Æ¸¡º÷¤µ¤ì¤ë¤É¤³¤«¤Î¥Ç¥£¥ì¥¯
¥È¥ê¡¢Î㤨¤Ð /usr/lib ¤Ë¥³¥Ô¡¼¤·¤Æ¤¯¤À¤µ¤¤¡£
¥µ¥ó¥×¥ë´Ø¿ô¤Ë´Ø¤¹¤ë¤¤¤¯¤Ä¤«¤ÎÃí°Õ:
metaphon
¤Ïʸ»úÎó°ú¿ô¤Î metaphon ʸ»úÎó¤òÊÖ¤·¤Þ¤¹¡£
¤³¤ì¤Ï soundex ʸ»úÎó¤Ë»÷¤¿¤â¤Î¤Ç¤¹¤¬¡¢¤µ¤é¤Ë±Ñ¸ì¤Ë tune ¤µ¤ì¤Þ¤¹¡£
myfunc_double
¤Ï¡¢°ú¿ô¤ÎÁ´¤Æ¤Îʸ»ú¥³¡¼¥É¤Î¹ç·×¤ò¡¢Á´¤Æ¤Î°ú¿ô
¤ÎŤµ¤Î¹ç·×¤Ç³ä¤Ã¤¿ÃͤòÊÖ¤·¤Þ¤¹¡£
myfunc_int
¤ÏÁ´¤Æ¤Î°ú¿ô¤ÎŤµ¤Î¹ç·×¤òÊÖ¤·¤Þ¤¹¡£
lookup
¤Ï¥Û¥¹¥È̾¤Î IP ÈÖ¹æ¤òÊÖ¤·¤Þ¤¹¡£
reverse_lookup
¤Ï IP ÈÖ¹æ¤Î¥Û¥¹¥È̾¤òÊÖ¤·¤Þ¤¹¡£
¤³¤Î´Ø¿ô¤Ïʸ»úÎó "xxx.xxx.xxx.xxx" ¤Þ¤¿¤Ï4·å¤Î¿ôÃͤǸƤФì¤Þ¤¹¡£
¥é¥¤¥Ö¥é¥ê¤¬¥¤¥ó¥¹¥È¡¼¥ë¤µ¤ì¤¿¸å¡¢mysqld
¤Ë¿·¤·¤¤´Ø¿ô¤ò¼¡¤Î¥³¥Þ¥ó
¥É¤ÇÃΤ餻¤ëɬÍפ¬¤¢¤ê¤Þ¤¹:
CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so"; CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so"; CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so"; CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so"; CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so";
´Ø¿ô¤Ï°ìÅÙ¤À¤±ºîÀ®¤µ¤ì¤Þ¤¹¡£ ´Ø¿ô¤Ï¼¡¤Î¤è¤¦¤Ë¤·¤Æºï½ü¤Ç¤¤Þ¤¹:
DROP FUNCTION metaphon; DROP FUNCTION myfunc_double; DROP FUNCTION myfunc_int; DROP FUNCTION lookup; DROP FUNCTION reverse_lookup;
CREATE FUNCTION
¤È DROP FUNCTION
¤Ï func
¥Æ¡¼¥Ö¥ë¤ò
¹¹¿·¤·¤Þ¤¹¡£Á´¤Æ¤Î¥¢¥¯¥Æ¥£¥Ö¤Ê´Ø¿ô¤Ï¥µ¡¼¥Ð¤ÎºÆµ¯Æ°¤´¤È¤Ë¥ê¥í¡¼¥É¤µ¤ì¤Þ¤¹
(--skip-grant-tables ¤¬Í¿¤¨¤é¤ì¤Ê¤¤¾ì¹ç)¡£
¤è¤¯¤¢¤ëÌäÂê¤Ï¡¢timestamp
¤ä group
¡¢MySQL ¤ËËä¤á
¹þ¤Þ¤ì¤Æ¤¤¤ë¥Ç¡¼¥¿·¿¤ä´Ø¿ô¤Î̾Á°¤ò¹àÌÜ̾¤È¤·¤Æ»ý¤Ä¥Æ¡¼¥Ö¥ë¤ÎºîÀ®¤ò»î¤ß¤ë
¤³¤È¤ò¤¯¤¤¤È¤á¤Þ¤¹¡£¤½¤ì¤ò¹Ô¤Ê¤¦¤³¤È¤Ïµö¤µ¤ì¤Æ¤¤¤Þ¤¹(Î㤨¤Ð¡¢ABS
¤Ï¹àÌÜ̾¤È¤·¤Æµö¤µ¤ì¤Þ¤¹)¤¬¡¢Ì¾Á°¤¬¹àÌÜ̾¤Ç¤â¤¢¤ë´Ø¿ô»ÈÍÑ»þ¤Ë¤Ï¡¢´Ø¿ô̾
¤È '('
¤Î´Ö¤Ë¶õÇò¤Ïµö¤µ¤ì¤Þ¤»¤ó¡£
¼¡¤Ï MySQL ¤Ç¤ÎÌÀÇò¤ÊͽÌó¸ì¤Ç¤¹¡£¤³¤ì¤é¤Î¤Û¤È¤ó¤É¤Ï (Î㤨¤Ð)
group
¤Ï ANSI SQL92 ¤Ç¤Ï¹àÌÜ̾¤ä¥Æ¡¼¥Ö¥ë̾¤È¤·¤Æ¤Ï¶Ø¤¸¤é¤ì¤Æ¤Þ¤¹¡£
¤¤¤¯¤Ä¤«¤Ï MySQL ¤¬É¬ÍפȤ¹¤ë¤¿¤á¤Ç¡¢(¸½ºß) Yacc ¥Ñ¡¼¥µ¤ò»ÈÍѤ·
¤Æ¤¤¤Þ¤¹:
action | add | all | alter
|
and | as | asc | auto_increment
|
between | bigint | bit | binary
|
blob | both | by | cascade
|
char | character | change | check
|
column | columns | create | data
|
database | databases | date | datetime
|
day | day_hour | day_minute | day_second
|
dayofweek | dec | decimal | default
|
delete | desc | describe | distinct
|
double | drop | escaped | enclosed
|
enum | explain | fields | float
|
float4 | float8 | foreign | from
|
for | full | grant | group
|
having | hour | hour_minute | hour_second
|
ignore | in | index | infile
|
insert | int | integer | interval
|
int1 | int2 | int3 | int4
|
int8 | into | is | join
|
key | keys | leading | left
|
like | lines | limit | lock
|
load | long | longblob | longtext
|
match | mediumblob | mediumtext | mediumint
|
middleint | minute | minute_second | month
|
natural | numeric | no | not
|
null | on | option | optionally
|
or | order | outer | outfile
|
partial | precision | primary | procedure
|
privileges | read | real | references
|
rename | regexp | repeat | replace
|
restrict | rlike | select | set
|
show | smallint | sql_big_tables | sql_big_selects
|
sql_select_limit | sql_log_off | straight_join | starting
|
table | tables | terminated | text
|
time | timestamp | tinyblob | tinytext
|
tinyint | trailing | to | use
|
using | unique | unlock | unsigned
|
update | usage | values | varchar
|
varying | varbinary | with | write
|
where | year | year_month | zerofill
|
Go to the first, previous, next, last section, table of contents.