MySQLæå¦æç¨ï¼é带丰å¯çææ¯ææ¡£ï¼è¿ä¸¤ä¸ªå ç´ ä½¿ä¹è¢«å¹¿æ³åºç¨ãç¶èï¼éçMySQLåå±å å¿«ï¼å³ä½¿ä¸ä¸ªMySQLèæææ¶ä¹ä¼ä¸ºè¯¥è½¯ä»¶åºå ¶ä¸æçåè½æå¹ãæ¬æå°ä¸ºä½ ä»ç»è¿äºä¸ä¸ºäººç¥çç¹æ§ã
ãã以XMLæ ¼å¼æ¥çæ¥è¯¢ç»æ
ããéè¿ä½¿ç¨ä¼ ç»âxml é项è°ç¨MySQLå½ä»¤è¡å®¢æ·ç¨åºï¼ä½ å¯ä»¥ä»¥XMLæ ¼å¼(èä¸æ¯ä¼ ç»çå表形å¼)æ¥æ¥çã
ããMySQLæ¥è¯¢ç»æ
ããå¦æä½ æç®å°æ¥è¯¢è¾åºä¸å ¶å®ç¨åºéæå¨ä¸èµ·ï¼è¿ä¸æå·§é常æç¨ï¼è¿éæ¯ä¸ä¸ªä¾åï¼
表A
shell> mysql --xml
mysql> SELECT * FROM test.stories;
1
This is a test
2
This is the second test
2rows in set (0.11 sec)
ããå¿«éé建索å¼
ããé常æ åµä¸ï¼å¦æä½ æ³æ¹åæå¡å¨çå ¨ææç´¢åéï¼ä½ éè¦å¨è¡¨æ ¼ä¸éæ°å»ºç«å ¨æç´¢å¼ï¼ä»¥ç¡®ä¿ä½ çæ´æ°å¾å°æ å°ãè¿ä¸æä½å°ä¼è±è´¹å¤§éçæ¶é´ï¼ç¹å«æ¯å¦æä½ éè¦å¤çå¾å¤æ°æ®çæ¶åãä¸ç§å¿«éç解å³ã
ããæ¹æ³æ¯ä½¿ç¨REPAIR TABLEå½ä»¤ï¼ä»¥ä¸ä¸ºæ¼ç¤ºè¿ç¨ï¼
表B
mysql> REPAIR TABLE content QUICK;
+-----------+--------+----------+----------+
| Table| Op| Msg_type | Msg_text |
+-----------+--------+----------+----------+
| content| repair | status| OK|
+-----------+--------+----------+----------+
1 row in set (0.05 sec)
ããå缩ä¸å®çè¡¨æ ¼ç±»å
ããå¦æä½ å¤ççæ¯åªè¯»MyISAMè¡¨æ ¼ï¼MySQLå è®¸ä½ å°å ¶å缩以èçç£ç空é´ã对æ¤å¯ä»¥ä½¿ç¨å æ¬myisampackï¼å¦ä¸æ示ï¼
表C
shell> myisampackmovies.MYI
Compressing movies.MYD: (146 records)
- Calculating statistics
- Compressing file
41.05%
ãã使ç¨ä¼ ç»SQL
ããMySQLæ¯æSQLæ¥è¯¢ä¸çä¼ ç»ç¨æ³ï¼æ¯æIFä¸CASEç»æã以ä¸æ¯ä¸ä¸ªç®åçä¾åï¼
表D
mysql> SELECT IF (priv=1, 'admin', 'guest')
As usertype FROM privs WHERE username = 'joe';
+----------+
| usertype |
+----------+
| admin|
+----------+
1 row in set (0.00 sec)
ãã以CSVæ ¼å¼è¾åºè¡¨æ ¼æ°æ®
ããMySQL è¾åºæ件å å«ä¸ä¸ªå ¨é¨SQLå½ä»¤å表ãå¦æä½ æ³å°è¾åºæä»¶å¯¼å ¥å°MySQLï¼è¿ä¸åè½é常å®ç¨ï¼ä½å¦æç®æ ç¨åº(æ¯å¦Excel)ä¸è½ä¸SQLç¸äºé讯ï¼è¿ä¸æ¹æ³å°è¡ä¸éãå¨è¿ç§æ åµä¸ï¼å¯ä»¥éè¿åè¯MySQL ï¼ä»¥CSVæ ¼å¼å»ºç«è¾åºæ件ï¼è¿ç§CSVæ ¼å¼å¾æ¹ä¾¿å°å¯¼å ¥å°ç»å¤§é¨åçç¨åºãè¿éæ¼ç¤ºäº mysqldumpçæä½è¿ç¨ï¼
shell> mysqldump -T .
 --fields-terminated-by=", " mydbmytable
ããè¿å°å¨å½åç®å½ä¸çæä¸ä¸ªææ¬æ件ï¼å å«æ¥èªmydb.mytableå表ä¸ä»¥éå·ä¸ºé´é符çè®°å½ã
以æ¿æ´»strict模å¼åå°âbadâæ°æ®çåºç°
ããMySQLæå¡å¨è½å¤ä»¥å¤ç§ä¸åç模å¼è¿è¡ï¼èæ¯ä¸ç§é½é对äºç¹å®çç®çèä¼åãå¨é»è®¤æ åµä¸ï¼æ²¡æ设置模å¼ãç¶èï¼éè¿å¨æå¡å¨å½ä»¤è¡ä¸æ·»å 以ä¸é项å¯ä»¥å¾å®¹æå°æ¹å模å¼ç设置并å°MySQL以âstrictâ模å¼è¿è¡ï¼
shell> mysqld --sql_mode="STRICT_ALL_TABLES" &
ããå¨âstrictâ模å¼ä¸ï¼éè¿MySQLçä¸æ¢æ¥è¯¢æ§è¡å¹¶è¿åä¸ä¸ªé误ï¼æå¡å¨çå¾å¤èªå¨ä¿®æ£åè½é½è¢«æ æåãåæ ·ï¼è¯¥æ¨¡å¼ä¸ä¹å°ä¼æ§è¡æ´ä¸ºä¸¥æ ¼çæ¶é´æ£æ¥ã
ããçè§æå¡å¨
ããä½ å¯ä»¥éè¿è¿è¡SHOW STATUSå½ä»¤è·å¾ä¸ä»½æå¡å¨è¿è¡ä¸ç»è®¡çæ¥åï¼å æ¬æå¼è¿æ¥ç次æ°ï¼æ¿æ´»æ¥è¯¢æ¬¡æ°ï¼æå¡å¨æ£å¸¸è¿è¡æ¶é´ççãä¾å¦ï¼
表 E
mysql> SHOW STATUS;
+------------------+-------+
| Variable_name| Value |
+------------------+-------+
| Aborted_clients| 0|
| Aborted_connects | 0|
...
| Uptime| 851|
+------------------+-------+
156 rows in set (0.16 sec)
ããèªå¨è¿åCREATE TABLE代ç
ããMySQLå è®¸ä½ èªå¨è·å¾SQLå½ä»¤éæ°å»ºç«ä¸ä¸ªç¹å®çè¡¨æ ¼ãåªç®åå°è¿è¡SHOW CREATE TABLEå½ä»¤ï¼å¹¶æ¥çè¡¨æ ¼å»ºç«ä»£ç ï¼å¦ä¸æ示ï¼
表 F
mysql> SHOW CREATE TABLE products;
---------------------------------------
| Table| Create Table
+----------+---------------------------
| products | CREATE TABLE `products` (
`id` int(8) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`price` int(10) default NULL,
PRIMARY KEY(`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+----------------------------
1 row in set (0.27 sec)
ãã建ç«ä¸ä¸ªæ´ä¸ºæç¨çå½ä»¤æ示ï¼
ããå¨ç¼ºçæ åµä¸ï¼MySQLå½ä»¤è¡å®¢æ·ç¨åºæ¾ç¤ºä¸ä¸ªç®åçmysql>æ示符ãç¶èï¼ä½ å¯ä»¥ä½¿ç¨ç¹å®çä¿®æ¹å 容æ¥æ¹åè¿ä¸æ示符使ä¹åå¾æ´ä¸ºææï¼è¿äºå 容å æ¬ï¼å½åç¨æ·å称ï¼ä¸»æºå称ï¼ä»¥åå½åéæ©çæ°æ®åºãå¦ä¸æ示ï¼
表 G
mysql> prompt /U://d>
PROMPT set to '/U://d>'
[email protected]:/db1>