ããä¸ãMySQLå®è£
ããCentosä¸å®è£
mysql 请ç¹å¼:
http://www.centoscn.com/CentosServer/sql/2013/0817/1285.htmläºãMySQLçå 个éè¦ç®å½
ããMySQLå®è£
å®æåä¸è±¡SQL Serveré»è®¤å®è£
å¨ä¸ä¸ªç®å½ï¼å®çæ°æ®åºæ件ãé
ç½®æ件åå½ä»¤æ件åå«å¨ä¸åçç®å½ï¼äºè§£è¿äºç®å½é常éè¦ï¼å°¤å
¶å¯¹äºLinuxçåå¦è
ï¼å 为 Linuxæ¬èº«çç®å½ç»æå°±æ¯è¾å¤æï¼å¦ææä¸æ¸
æ¥MySQLçå®è£
ç®å½é£å°±æ ä»è°èµ·æ·±å
¥å¦ä¹ ã
ããä¸é¢å°±ä»ç»ä¸ä¸è¿å 个ç®å½ã
ãã2.1ãæ°æ®åºç®å½
ãã/var/lib/mysql/
ãã2.2ãé
ç½®æ件
ãã/usr/share/mysqlï¼mysql.serverå½ä»¤åé
ç½®æ件ï¼2.3ãç¸å
³å½ä»¤
ãã/usr/bin(mysqladmin mysqldumpçå½ä»¤)
ãã2.4ãå¯å¨èæ¬
ãã/etc/rc.d/init.d/ï¼å¯å¨èæ¬æ件mysqlçç®å½ï¼ä¸ãç»å½MySQL
ãã3.1ãè¿æ¥æ¬æºMySQL
ããä¾1ï¼è¿æ¥å°æ¬æºä¸çMYSQLã
ããé¦å
å¨æå¼DOSçªå£ï¼ç¶åè¿å
¥ç®å½ mysqlbinï¼åé®å
¥å½ä»¤mysql -uroot -pï¼å车åæç¤ºä½ è¾å¯ç ï¼å¦æåå®è£
好MYSQLï¼è¶
级ç¨æ·rootæ¯æ²¡æå¯ç çï¼æ
ç´æ¥å车å³å¯è¿å
¥å°MYSQLä¸äºï¼MYSQLçæ示符 æ¯ï¼mysql>ã
ãã3.2ãè¿æ¥è¿ç¨MySQL
ããä¾2ï¼è¿æ¥å°è¿ç¨ä¸»æºä¸çMYSQLãå设è¿ç¨ä¸»æºçIP为ï¼110.110.110.110ï¼ç¨æ·å为root,å¯ç 为abcd123ãåé®å
¥ä»¥ä¸å½ä»¤ï¼
ããmysql -h110.110.110.110 -uroot -pabcd123
ããï¼æ³¨:uä¸rootå¯ä»¥ä¸ç¨å ç©ºæ ¼ï¼å
¶å®ä¹ä¸æ ·ï¼
ãã3.3ãéåºMYSQL
ããå½ä»¤ï¼ exit ï¼å车ï¼ã
ããåãä¿®æ¹ç»å½å¯ç
ããMySQLé»è®¤æ²¡æå¯ç ï¼å®è£
å®æ¯å¢å å¯ç çéè¦æ§æ¯ä¸è¨èå»çã
ããæ ¼å¼ï¼mysqladmin -uç¨æ·å -pæ§å¯ç password æ°å¯ç 4.1ãé¦æ¬¡æ¹å¯ç
ããä¾1ï¼ç»rootå 个å¯ç ab12ãé¦å
å¨DOSä¸è¿å
¥ç®å½mysqlbinï¼ç¶åé®å
¥ä»¥ä¸å½ä»¤ï¼
ããmysqladmin -uroot -password ab12
ãã注ï¼å 为å¼å§æ¶root没æå¯ç ï¼æ以-pæ§å¯ç ä¸é¡¹å°±å¯ä»¥çç¥äºã
ãã4.2ãå次æ¹å¯ç
ããä¾2ï¼åå°rootçå¯ç æ¹ä¸ºdjg345ã
ããmysqladmin -uroot -pab12 password djg345
ããäºãå¢å ç¨æ·
ããï¼æ³¨æï¼åä¸é¢ä¸åï¼ä¸é¢çå 为æ¯MySQLç¯å¢ä¸çå½ä»¤ï¼æ以åé¢é½å¸¦ä¸ä¸ªåå·ä½ä¸ºå½ä»¤ç»æ符ï¼æ ¼å¼ï¼grant select on æ°æ®åº.* to ç¨æ·å@ç»å½ä¸»æº identified by \"å¯ç \"ä¾1ãå¢å ä¸ä¸ªç¨æ·test1å¯ç 为abcï¼è®©ä»å¯ä»¥å¨ä»»ä½ä¸»æºä¸ç»å½ï¼å¹¶å¯¹æææ°æ®åºææ¥è¯¢ãæå
¥ãä¿®æ¹ãå é¤çæéãé¦å
ç¨ä»¥rootç¨æ·è¿å
¥MySQLï¼ç¶åé®å
¥ä»¥ä¸å½ä»¤ï¼
ããgrant select,insert,update,
ããdelete on *.* to test1@\"%\" Identified by \"abc\";ä½ä¾1å¢å çç¨æ·æ¯ååå±é©çï¼ä½ æ³å¦æ个人ç¥étest1çå¯ç ï¼é£ä¹ä»å°±å¯ä»¥å¨internetä¸çä»»ä½ä¸å°çµèä¸ç»å½ä½ çMySQLæ°æ®åºå¹¶å¯¹ä½ çæ°æ®å¯ä»¥ä¸ºæ欲为äºï¼è§£å³åæ³è§ä¾2ã
ããä¾2ãå¢å ä¸ä¸ªç¨æ·test2å¯ç 为abc,让ä»åªå¯ä»¥å¨localhostä¸ç»å½ï¼å¹¶å¯ä»¥å¯¹æ°æ®åºmydbè¿è¡æ¥è¯¢ãæå
¥ãä¿®æ¹ãå é¤çæä½ ï¼localhostææ¬å°ä¸»æºï¼å³MySQLæ°æ®åºæå¨çé£å°ä¸»æºï¼ï¼è¿æ ·ç¨æ·å³ä½¿ç¨ç¥étest2çå¯ç ï¼ä»ä¹æ æ³ä»internetä¸ç´æ¥è®¿é®æ°æ® åºï¼åªè½éè¿MySQL主æºä¸çweb页æ¥è®¿é®ã
ããgrant select,insert,update,
ããdelete on mydb.* to test2@localhost identified by \"abc\";å¦æä½ ä¸æ³test2æå¯ç ï¼å¯ä»¥åæä¸ä¸ªå½ä»¤å°å¯ç æ¶æã
ããgrant select,insert,update,delete on mydb.* to test2@localhost identified by \"\";ç¨æ°å¢çç¨æ·å¦æç»å½ä¸äºMySQLï¼
ããå¨ç»å½æ¶ç¨å¦ä¸å½ä»¤ï¼ããmysql -u user_1 -pã-h 192.168.113.50ãï¼-håè·çæ¯è¦ç»å½ä¸»æºçipå°åï¼å
ãå¯å¨ä¸åæ¢
ãã6.1ãå¯å¨
ããMySQLå®è£
å®æåå¯å¨æ件mysqlå¨/etc/init.dç®å½ä¸ï¼å¨éè¦å¯å¨æ¶è¿è¡ä¸é¢å½ä»¤å³å¯ã
ãã[root@test1 init.d]# /etc/init.d/mysql start6.2ãåæ¢
ãã/usr/bin/mysqladmin -u root -p shutdown
ãã6.3ãèªå¨å¯å¨
ãã6.3.1ãå¯çmysqlæ¯å¦å¨èªå¨å¯å¨å表ä¸
ãã[root@test1 local]#ã/sbin/chkconfig âlist6.3.2ãæMySQLæ·»å å°ä½ ç³»ç»çå¯å¨æå¡ç»éé¢å»[root@test1 local]#ã/sbin/chkconfigãâ addãmysql6.3.3ãæMySQLä»å¯å¨æå¡ç»éé¢å é¤ã
ãã[root@test1 local]#ã/sbin/chkconfigãâ delãmysqlä¸ãæ´æ¹MySQLç®å½
ããMySQLé»è®¤çæ°æ®æ件åå¨ç®å½ä¸º/var/lib/mysqlã
ããåå¦è¦æç®å½ç§»å°/home/dataä¸éè¦è¿è¡ä¸é¢å æ¥ï¼
ãã7.1ãhomeç®å½ä¸å»ºç«dataç®å½
ããcd /home ããmkdir data
ãã7.2ãæMySQLæå¡è¿ç¨åæï¼
ããmysqladmin -u root -p shutdown
ãã7.3ãæ/var/lib/mysqlæ´ä¸ªç®å½ç§»å°/home/datamv /var/lib/mysqlã/home/data/
ããè¿æ ·å°±æMySQLçæ°æ®æ件移å¨å°äº/home/data/mysqlä¸7.4ãæ¾å°my.cnfé
ç½®æ件
ããå¦æ/etc/ç®å½ä¸æ²¡æmy.cnfé
ç½®æ件ï¼è¯·å°/usr/share/mysql/ä¸æ¾å°*.cnfæ件ï¼æ·è´å
¶ä¸ä¸ä¸ªå°/etc/并æ¹å为my.cnf)ä¸ã
ããå½ä»¤å¦ä¸ï¼
ãã[root@test1 mysql]# cp /usr/share/mysql/my-medium.cnfã/etc/my.cnf7.5ãç¼è¾MySQLçé
ç½®æ件/etc/my.cnf
ãã为ä¿è¯MySQLè½å¤æ£å¸¸å·¥ä½ï¼éè¦ææmysql.sockæ件ç产çä½ç½®ã
ããä¿®æ¹socket=/var/lib/mysql/mysql.sockä¸è¡ä¸çå·å³è¾¹çå¼ä¸ºï¼/home/mysql/mysql.sock ã
ããæä½å¦ä¸ï¼
ããviã my.cnf
ãã(ç¨viå·¥å
·ç¼è¾my.cnfæ件ï¼æ¾å°ä¸åæ°æ®ä¿®æ¹ä¹)# The MySQL server ããã [mysqld]
ããportããã= 3306
ãã#socketã = /var/lib/mysql/mysql.sockï¼åå
容ï¼ä¸ºäºæ´ç¨³å¦¥ç¨â#â注éæ¤è¡ï¼socketã = /home/data/mysql/mysql.sockãããï¼å ä¸æ¤è¡ï¼7.6ãä¿®æ¹MySQLå¯å¨èæ¬/etc/rc.d/init.d/mysqlæåï¼éè¦ä¿®æ¹MySQLå¯å¨èæ¬/etc/rc.d/init.d/mysqlï¼æå
¶ä¸datadir=/var/lib/mysqlä¸è¡ä¸ï¼çå·å³è¾¹çè·¯å¾æ¹æä½ ç°å¨çå®é
åæ¾è·¯å¾ï¼home/data/mysqlã
ãã[root@test1 etc]# viã/etc/rc.d/init.d/mysql#datadir=/var/lib/mysqlããããï¼æ³¨éæ¤è¡ï¼datadir=/home/data/mysqlãã ï¼å ä¸æ¤è¡ï¼7.7ãéæ°å¯å¨MySQLæå¡
ãã/etc/rc.d/init.d/mysqlãstart
ããæç¨rebootå½ä»¤éå¯Linux
ããå¦æå·¥ä½æ£å¸¸ç§»å¨å°±æåäºï¼å¦å对ç
§åé¢ç7æ¥åæ£æ¥ä¸ä¸ã
ããå
«ãMySQLç常ç¨æä½
ãã注æï¼MySQLä¸æ¯ä¸ªå½ä»¤åé½è¦ä»¥åå·ï¼ç»å°¾ã
ãã8.1ãMySQL常ç¨æä½å½ä»¤
ãã8.1.1ãæ¾ç¤ºæ°æ®åºå表ï¼
ããshow databases;
ããåå¼å§æ¶æ两个æ°æ®åºï¼mysqlåtestãMySQLåºå¾éè¦å®éé¢æMYSQLçç³»ç»ä¿¡æ¯ï¼æ们æ¹å¯ç åæ°å¢ç¨æ·ï¼å®é
ä¸å°±æ¯ç¨è¿ä¸ªåºè¿è¡æä½ã
ãã8.1.2ãæ¾ç¤ºåºä¸çæ°æ®è¡¨ï¼
ããuse mysqlï¼ ï¼ï¼æå¼åºï¼å¦è¿FOXBASEçä¸å®ä¸ä¼éçå§show tables;
ãã8.1.3ãæ¾ç¤ºæ°æ®è¡¨çç»æï¼
ããdescribe 表å;
ãã8.1.4ã建åºï¼
ããcreate database åºå;
ãã8.1.5ã建表ï¼
ããuse åºåï¼
ããcreate table 表å (å段设å®å表)ï¼
ãã8.1.6ãå åºåå 表:
ããdrop database åºå;
ããdrop table 表åï¼
ãã8.1.7ãå°è¡¨ä¸è®°å½æ¸
空ï¼
ããdelete from 表å;
ãã8.1.8ãæ¾ç¤ºè¡¨ä¸çè®°å½ï¼
ããselect * from 表å;
ãã8.1.9ãå¢å è®°å½
ããä¾å¦ï¼å¢å å æ¡ç¸å
³çºªå½ã
ããmysql> insert into name values('','å¼ ä¸','ç·','1971-10-01');mysql> insert into name values('','ç½äº','女','1972-05-20');å¯ç¨selectå½ä»¤æ¥éªè¯ç»æã
ããmysql> select * from name;
ãã+----+------+------+------------+
ãã| id | xmã | xbã | csnyããã |
ãã+----+------+------+------------+
ãã|ã1 | å¼ ä¸ | ç·ã | 1971-10-01 |
ãã|ã2 | ç½äº | 女ã | 1972-05-20 |
ãã+----+------+------+------------+
ãã8.1.10ãä¿®æ¹çºªå½
ããä¾å¦ï¼å°å¼ ä¸çåºçå¹´ææ¹ä¸º1971-01-10
ããmysql> update name set csny='1971-01-10' where xm='å¼ ä¸';8.1.11ãå é¤çºªå½
ããä¾å¦ï¼å é¤å¼ ä¸ç纪å½ã
ããmysql> delete from name where xm='å¼ ä¸';
ãã8.2ãä¸ä¸ªå»ºåºå建表以åæå
¥æ°æ®çå®ä¾
ããdrop database if exists school; //å¦æåå¨SCHOOLåå é¤create database school; //建ç«åºSCHOOL
ããuse school; //æå¼åºSCHOOL
ããcreate table teacher //建ç«è¡¨TEACHER
ãã(
ããid int(3) auto_increment not null primary key,name char(10) not null,
ããaddress varchar(50) default âæ·±å³â,
ããyear date
ãã); //建表ç»æ
ãã//以ä¸ä¸ºæå
¥å段
ããinsert into teacher values(ââ,âglchengangâ,âæ·±å³ä¸ä¸â,â1976-10-10â);insert into teacher values(ââ,âjackâ,âæ·±å³ä¸ä¸â,â1975-12-23â);注ï¼å¨å»ºè¡¨ä¸ï¼1ï¼å°ID设为é¿åº¦ä¸º3çæ°åå段:int(3)并让å®æ¯ä¸ªè®°å½èªå¨å ä¸:auto_increment并ä¸è½ä¸ºç©º:not nullèä¸è®©ä»æ为主å段primary keyï¼2ï¼å°NAME设为é¿åº¦ä¸º10çå符å段ï¼3ï¼å°ADDRESS设为é¿åº¦50çå符å段ï¼èä¸ç¼ºçå¼ä¸ºæ·±å³ãvarcharåcharæä»ä¹åºå« å¢ï¼åªæç以åçæç« å说äºãï¼4ï¼å°YEAR设为æ¥æå段ã
ããå¦æä½ å¨MySQLæ示符é®å
¥ä¸é¢çå½ä»¤ä¹å¯ä»¥ï¼ä½ä¸æ¹ä¾¿è°è¯ãä½ å¯ä»¥å°ä»¥ä¸å½ä»¤åæ ·åå
¥ä¸ä¸ªææ¬æ件ä¸å设为school.sqlï¼ç¶åå¤å¶å°c:\\ä¸ï¼å¹¶å¨DOSç¶æè¿å
¥ç®å½\\mysql\\binï¼ç¶åé®å
¥ä»¥ä¸å½ä»¤ï¼
ããmysql -uroot -på¯ç < c:\\school.sql
ããå¦ææåï¼ç©ºåºä¸è¡æ ä»»ä½æ¾ç¤ºï¼å¦æé误ï¼ä¼ææ示ãï¼ä»¥ä¸å½ä»¤å·²ç»è°è¯ï¼ä½ åªè¦å°//ç注éå»æå³å¯ä½¿ç¨ï¼ã
ããä¹ ãä¿®æ¹æ°æ®åºç»æ
ãã9.1ãå段æä½
ãã9.1.1ãå¢å å段
ããalter table dbname add column <å段å><å段é项>
ãã9.1.2ãä¿®æ¹å段
ããalter table dbname change <æ§å段å> <æ°å段å><é项>
ãã9.1.3ãå é¤å段
ããalter table dbname drop column <å段å>
ããåãæ°æ®å¯¼åº
ããæ°æ®å¯¼åºä¸»è¦æ以ä¸å ç§æ¹æ³ï¼
ãã使ç¨select into outfile "filename"è¯å¥
ãã使ç¨mysqldumpå®ç¨ç¨åº
ãã10.1ã使ç¨select into outfile "filename"è¯å¥å¯ä»¥å¨mysqlçå½ä»¤è¡ä¸æå¨phpç¨åºä¸æ§è¡å®ãæä¸é¢ä»¥å¨mysqlå½ä»¤è¡ä¸ä¸º ä¾ãå¨phpä¸ä½¿ç¨æ¶ï¼å°å
¶æ¹æç¸åºçæ¥è¯¢è¿è¡å¤çå³å¯ãä¸è¿å¨ä½¿ç¨è¿ä¸ªå½ä»¤æ¶ï¼è¦æ±ç¨æ·æ¥æfileçæéãå¦æ们æä¸ä¸ªåºä¸ºphptestï¼å
¶ä¸æä¸ ä¸ªè¡¨ä¸ºdriverãç°å¨è¦ædriverå¸ææ件ãæ§è¡å½ä»¤ï¼
ããmysql> use phptest;
ããDatabase Changed
ããmysql> select * from driver into outfile "a.txt";Query OK, 22 rows affected (0.05 sec)
ããä¸é¢å°±å¯ä»¥å®æå°è¡¨driverä»æ°æ®åºä¸å¸å°a.txtæ件ä¸ã注ææ件åè¦å åå¼ å·ãé£ä¹è¿ä¸ªæ件å¨åªå¢ï¼å¨mysqlç®å½ä¸æä¸ä¸ªdataç®å½ï¼å®å³æ¯æ°æ®åºæ件ææ¾çå°æ¹ãæ¯ä¸ªåºå¨åç¬å ä¸ä¸ªåç®å½ï¼æ以phptestçç®å½ä¸º c:\mysql\data\phptest(注æï¼æçmysqlå®è£
å¨c:\mysqlä¸)ã好ï¼ç°å¨æ们è¿å»ï¼a.txtå°±æ¯å®ãæå¼è¿ä¸ªæ件ï¼å¯ è½æ¯ï¼
ãã1 Mika Hakinnen 1
ãã2 David Coulthard 1
ãã3 Michael Schumacher 2
ãã4 Rubens Barrichello 2
ãã...
ããå¯è½è¿æå¾å¤è®°å½ãæ¯ä¸ªå段ä¹é´æ¯ç¨å¶è¡¨ç¬¦åå¼ç(\t)ãé£ä¹æ们å¯ä»¥ä¿®æ¹è¾åºæ件 åçç®å½ï¼ä»¥ä¾¿æ¾å¨æå®çä½ç½®ãå¦"a.txt"å¯ä»¥æ¹æ"./a.txt"æ"/a.txt"ãå
¶ä¸"./a.txt"æ¾å¨c:\mysql\data ç®å½ä¸äºï¼è"/a.txt"æ件åæ¾å¨c:\ç®å½ä¸äºãæ以selectå½ä»¤è®¤ä¸ºçå½åç®å½æ¯æ°æ®åºçåæ¾ç®å½ï¼è¿éæ¯c:\mysql\dataã
ãã使ç¨selectå½ä»¤è¿å¯ä»¥æå®å¸åºæ件æ¶ï¼å段ä¹é´çåéå符ï¼è½¬ä¹å符ï¼å
æ¬å符ï¼åè®°å½è¡åéå符ãåå¨ä¸é¢ï¼
ããFIELDS
ããTERMINATED BY "\t"
ãã[OPTIONALLY] ENCLOSED BY ""
ããESCAPED BY ""
ããLINES
ããTERMINATED BY "\n"
ããTERMINATED 表示å段åé
ãã[OPTIONALLY] ENCLOSED 表示å段ç¨ä»ä¹å符å
æ¬èµ·æ¥ï¼å¦æ使ç¨äºOPTIONALLYååªæCHARåVERCHAR被å
æ¬ESCAPED 表示å½éè¦è½¬ä¹æ¶ç¨ä»ä¹ä½ä¸ºè½¬ä¹å符LINES TERMINATED 表示æ¯è¡è®°å½ä¹é´ç¨ä»ä¹åéä¸é¢åçæ¯ç¼ºçå¼ï¼èä¸è¿äºé¡¹é½æ¯å¯éçï¼ä¸éå使ç¨ç¼ºçå¼ãå¯ä»¥æ ¹æ®éè¦è¿è¡ä¿®æ¹ãç»åºä¸ä¸ªä¾åå¦ä¸ï¼
ããmysql> select * from driver into outfile "a.txt" fields terminated by ","enclosed by """;
ããQuery OK, 22 rows affected (0.06 sec)
ããç»æå¯è½å¦ä¸ï¼
ãã"1","Mika","Hakinnen","1"
ãã"2","David","Coulthard","1"
ãã"3","Michael","Schumacher","2"
ãã"4","Rubens","Barrichello","2"
ãã...
ããå¯ä»¥çå°æ¯ä¸ªå段é½ç¨","è¿è¡äºåéï¼ä¸æ¯ä¸ªå段é½ç¨"""å
æ¬äºèµ·æ¥ã注æï¼è¡è®°å½åé符å¯ä»¥æ¯ä¸ä¸ªå符串ï¼è¯·å¤§å®¶èªè¡æµè¯ãä¸è¿ï¼å¦æè¾åºæ件å¨æå®ç®å½ä¸å¦æåå¨çè¯å°±ä¼æ¥éï¼å
å é¤åæµè¯å³å¯ã
ãã10.2ã使ç¨mysqldumpå®ç¨ç¨åº
ããä»ä¸é¢çselectæ¹æ³å¯ä»¥çåºï¼è¾åºçæ件åªææ°æ®ï¼è没æ表ç»æãèä¸ï¼ä¸æ¬¡åª è½å¤çä¸ä¸ªè¡¨ï¼è¦å¤çå¤ä¸ªè¡¨åä¸æ¯å¾å®¹æçãä¸è¿å¯ä»¥å°selectå½ä»¤åå
¥ä¸ä¸ªsql æ件ï¼å¤å¶ææ¬åºè¯¥æ¯å¾å®¹æçå§ï¼ï¼ç¶åå¨å½ä»¤è¡ä¸æ§è¡å³å¯ï¼mysql åºåå
æ¥ä¸ªæç®åçå§ï¼
ããmysqldump phptest > a.sql
ããå¯è½ç»æå¦ä¸ï¼
ãã# MySQL dump 7.1
ãã#
ãã# Host: localhost Database: phptest
ãã#--------------------------------------------------------# Server version 3.22.32-shareware-debug
ãã#
ãã# Table structure for table "driver"
ãã#
ããCREATE TABLE driver (
ããdrv_id int(11) DEFAULT "0" NOT NULL auto_increment,drv_forename varchar(15) DEFAULT "" NOT NULL,drv_surname varchar(25) DEFAULT "" NOT NULL,drv_team int(11) DEFAULT "0" NOT NULL,
ããPRIMARY KEY (drv_id)
ãã);
ãã#
ãã# Dumping data for table "driver"
ãã#
ããINSERT INTO driver VALUES (1,"Mika","Hakinnen",1);INSERT INTO driver VALUES (2,"David","Coulthard",1);INSERT INTO driver VALUES (3,"Michael","Schumacher",2);INSERT INTO driver VALUES (4,"Rubens","Barrichello",2);...
ããå¦ææå¤è¡¨ï¼ååå«åå¨ä¸é¢ãå¯ä»¥çå°è¿ä¸ªæ件æ¯ä¸ä¸ªå®æ´çsqlæ件ï¼å¦æè¦å° å
¶å¯¼å
¥å°å
¶å®çæ°æ®åºä¸å¯ä»¥éè¿å½ä»¤è¡æ¹å¼ï¼å¾æ¹ä¾¿ï¼mysql phptest < a.sqlãå¦æå°æ°æ®ä»æ¬å°ä¼ å°æå¡å¨ä¸ï¼åå¯ä»¥å°è¿ä¸ªæ件ä¸ä¼ ï¼ç¶åå¨æå¡å¨éè¿å½ä»¤è¡æ¹å¼è£
å
¥æ°æ®ã
ããå¦æåªæ³å¸åºå»ºè¡¨æ令ï¼åå½ä»¤å¦ä¸ï¼
ããmysqldump -d phptest > a.sql
ããå¦æåªæ³å¸åºæå
¥æ°æ®çsqlå½ä»¤ï¼èä¸éè¦å»ºè¡¨å½ä»¤ï¼åå½ä»¤å¦ä¸ï¼
ããmysqldump -t phptest > a.sql
ããé£ä¹å¦ææåªæ³è¦æ°æ®ï¼èä¸æ³è¦ä»ä¹sqlå½ä»¤æ¶ï¼åºè¯¥å¦ä½æä½å¢ï¼
ããmysqldump -T./ phptest driver
ããå
¶ä¸ï¼åªææå®äº-Tåæ°æå¯ä»¥å¸åºçº¯ææ¬æ件ï¼è¡¨ç¤ºå¸åºæ°æ®çç®å½ï¼./表示å½åç® å½ï¼å³ä¸mysqldumpåä¸ç®å½ãå¦æä¸æå®driver表ï¼åå°å¸åºæ´ä¸ªæ°æ®åºçæ°æ®ãæ¯ä¸ªè¡¨ä¼çæ两个æ件ï¼ä¸ä¸ªä¸º.sqlæ件ï¼å
å«å»ºè¡¨æ§è¡ã å¦ä¸ä¸ªä¸º.txtæ件ï¼åªå
å«æ°æ®ï¼ä¸æ²¡æsqlæ令ã
ãã对å¸åºçæ°æ®æ件ï¼ä¹å¯ä»¥åselectæ¹æ³ä¸æ ·ï¼æå®å段åé符ï¼å
æ¬å符ï¼è½¬ä¹å段ï¼è¡è®°å½åé符ãåæ°åå¨ä¸é¢ï¼
ãã--fields-terminated-by= å段åé符
ãã--fields-enclosed-by= å段å
æ¬ç¬¦
ãã--fields-optionally-enclosed-by= å段å
æ¬ç¬¦ï¼åªç¨å¨CHARåVERCHARå段ä¸--fields-escaped-by= 转ä¹å符
ãã--lines-terminated-by= è¡è®°å½åé符
ããææ³å¤§å®¶åºè¯¥æç½è¿äºåæ°çææäºå§ãä¸ä¸ªä¾åå¦ä¸ï¼
ããmysqldump -T./ --fields-terminated-by=, --fields-enclosed-by=" phptest driverè¾åºç»æ为ï¼
ãã"1","Mika","Hakinnen","1"
ãã"2","David","Coulthard","1"
ãã"3","Michael","Schumacher","2"
ãã"4","Rubens","Barrichello","2"
ãã...
ãã请注æå符ç使ç¨ã
ãã10.3ãå°ç»
ãã以ä¸ä¸ºä½¿ç¨selectåmysqldumpå®ç¨ç¨åºæ¥å¸åºææ¬çæ¹æ³ãselectéåå©ç¨ç¨åºè¿è¡å¤çï¼èmysqldumpå为æå·¥æä½ï¼åæ¶æä¾å¼ºå¤§ç导åºåè½ï¼å¹¶ä¸å¯ä»¥å¤çæ´ä¸ªåºï¼æåºä¸æå®çå¤è¡¨ã大家å¯ä»¥æ ¹æ®éæ±èªè¡å³å®ä½¿ç¨ã
ããåæ¶è¿æä¸äºæ¹æ³ï¼å¦ç´æ¥æ°æ®åºæ件æ·è´ä¹å¯ä»¥ï¼ä½æ¯ç§»å¨åçæ°æ®åºç³»ç»ä¸åç³»ç»åºä¸è´æè¡ãè¿éå°±ä¸åæäºã
ããåä¸ãæ°æ®å¯¼å
¥
ããå导åºç¸ç±»ä¼¼ï¼å¯¼å
¥ä¹æ两ç§æ¹æ³ï¼
ãã使ç¨LOAD DATA INFILE "filename"å½ä»¤
ãã使ç¨mysqlimportå®ç¨ç¨åº
ãã使ç¨sqlæ件
ããç±äºå两个å¤çä¸å¯¼åºå¤çç¸ä¼¼ï¼åªä¸è¿æ¯å®ä»¬çéæä½ï¼æ
åªç»åºå ç§å½ä»¤ä½¿ç¨çä¾åï¼ä¸å解éäºï¼å¤§å®¶å¯ä»¥èªè¡æ¥é
æåã
ãã11.1ã使ç¨loadå½ä»¤ï¼
ããload data infile "driver.txt" into table driver fields terminated by ","enclosed by """;
ãã11.2ã使ç¨mysqlimportå®ç¨ç¨åºï¼
ããmysqlimport --fields-terminated-by=, --fields-enclosed-by=" phptest driver.txt11.3ã使ç¨SQLæ件
ããåå¯ä»¥ä½¿ç¨ç±mysqldump导åºçsqlæ件ï¼å¨å½ä»¤è¡ä¸æ§è¡mysqlåºåã
ããé¦å
è¦å£°æä¸ç¹ï¼å¤§é¨åæ
åµä¸ï¼ä¿®æ¹MySQLæ¯éè¦æmysqléçrootæéçï¼æ以ä¸è¬ç¨æ·æ æ³æ´æ¹å¯ç ï¼é¤é请æ±ç®¡çåã
ããæ¹æ³ä¸
ãã使ç¨phpmyadminï¼è¿æ¯æç®åçäºï¼ä¿®æ¹mysqlåºçuser表ï¼ä¸è¿å«å¿äºä½¿ç¨PASSWORDå½æ°ã
ããæ¹æ³äº
ãã使ç¨mysqladminï¼è¿æ¯åé¢å£°æçä¸ä¸ªç¹ä¾ã
ããmysqladmin -u root -p password mypasswd
ããè¾å
¥è¿ä¸ªå½ä»¤åï¼éè¦è¾å
¥rootçåå¯ç ï¼ç¶årootçå¯ç å°æ¹ä¸º mypasswdãæå½ä»¤éçrootæ¹ä¸ºä½ çç¨æ·åï¼ä½ å°±å¯ä»¥æ¹ä½ èªå·±çå¯ç äºãå½ç¶å¦æä½ çmysqladminè¿æ¥ä¸ä¸mysql serverï¼æè
ä½ æ²¡æåæ³æ§è¡mysqladminï¼é£ä¹è¿ç§æ¹æ³å°±æ¯æ æçãèä¸mysqladminæ æ³æå¯ç æ¸
空ã
ããä¸é¢çæ¹æ³é½å¨mysqlæ示符ä¸ä½¿ç¨ï¼ä¸å¿
é¡»æmysqlçrootæéï¼
ãã