Mysql常用基礎指令操作

甯歌鎿嶄綔鍛戒護锛? 1銆佽繛鎺ysql 锛堝鎴風宸ュ叿NaviCat銆乸hpMyAdmin銆丮ySQL-Front锛? 鏍煎紡锛?mysql -h 涓繪満鍦闆潃 -u鐢ㄦ埛鍚?-p鐢ㄦ埛瀵嗙爜

锛?锛夎繛鎺ュ埌鏈満涓婄殑MYSQL銆? 棣栧厛杩涘叆Mysql瀹夎绋嬪簭鐨刡in鐩綍涓嬶紝

鍛戒護./mysql -u root -p锛屽洖杞﹀悗鎻愮ず浣犺緭瀵嗙爜. 娉ㄦ剰鐢ㄦ埛鍚嶅墠鍙互鏈夌┖鏍間篃鍙互娌℃湁绌烘牸锛屼絾鏄瘑鐮佸墠蹇呴』娌℃湁绌烘牸锛屽惁鍒欒浣犻噸鏂拌緭鍏ュ瘑鐮併€?

锛?锛夎繛鎺ュ埌杩滅▼涓繪満涓婄殑MySQL銆傚亣璁捐繙绋嬩富鏈虹殑IP涓猴細110.110.110.110锛岀敤鎴峰悕涓簉oot,瀵嗙爜涓篴bcd123銆傚垯閿叆浠ヤ笅鍛戒護锛? mysql -h 110.110.110.110 -u root -pabcd123

娉?u涓巖oot涔嬮棿鍙互涓嶇敤鍔犵┖鏍?

锛?锛夐€€鍑篗ySQL鍛戒護锛?exit 锛堝洖杞︼級鎴栬€卶uit锛堝洖杞︼級

2銆佷慨鏀瑰瘑鐮? 鏍煎紡锛歮ysqladmin -u鐢ㄦ埛鍚?-p鏃у瘑鐮?password 鏂闆瘑鐮? 锛?锛夐鍏堝湪Mysql瀹夎鐩綍涓嬮潰鐨刡in鐩綍锛岀劧鍚庨敭鍏ヤ互涓嬪懡浠? mysqladmin -u root -p鏃у瘑鐮?password ab12 娉細鍥犱負寮€濮嬫椂root娌℃湁瀵嗙爜锛屾墍浠?p鏃у瘑鐮佷竴椤瑰氨鍙互鐪佺暐锛屼絾鍥炶濺鍚庝細鎻愮ず杈撳叆鏃у瘑鐮? 锛?锛夊啀灏唕oot鐨勫瘑鐮佹敼涓篴bc345銆? mysqladmin -u root -p ab12 password abc345

*************************************************

鍙︿竴绉嶄慨鏀瑰瘑鐮佹柟娉曪細锛?.7鐗堟湰涔嬪悗锛岄槻瀵嗙爜娉勯湶锛? 杩涘叆鏁版嵁搴撳悗锛屽湪mysql鍛戒護琛屼笅鎵ц鍛戒護锛? mysql> SET PASSWORD = PASSWORD('xxxxx(鏂闆瘑鐮?'); 銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€ #淇敼瀵嗙爜

mysql> ALTER USER '[email聽protected]' PASSWORD EXPIRE NEVER; 銆€銆€銆€銆€銆€銆€#璁劇疆瀵嗙爜姘鎬笉杩囨湡

mysql> flush privileges;銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€ #鍒鋒柊鏉冮檺锛堟縺娲夥級

娉細鏁版嵁搴撳懡浠ゅ畬鎴愬悗椤昏窡鈥?鈥濆彿

*************************************************

聽

3銆佸鍔犳柊鐢ㄦ埛/鎺堟潈鐢ㄦ埛

娉ㄦ剰锛氬拰涓婇潰涓嶅悓锛屼笅闈㈢殑鍥犱負鏄疢YSQL鍛戒護琛屼腑鐨勫懡浠わ紝鎵€浠ュ悗闈㈤兘甯︿竴涓垎鍙蜂綔涓哄懡浠ょ粨鏉熺

鏍煎紡锛歡rant select on 鏁版嵁搴?* to 鐢ㄦ埛鍚岪鐧誨綍涓繪満 identified by 鈥滃瘑鐮佲€? 瑙ｆ瀽锛? grant 銆€銆€聽 聽 聽聽 #鎺堟潈

select 銆€銆€聽 聽 聽 #鏌ヨ鏉冮檺

on 銆€銆€銆€銆€聽 聽 #鍏抽敭瀛?鍚庨潰璺熸暟鎹簱鍚嶇О

鏁版嵁搴?* 銆€銆€聽 #鏁版嵁搴撳悕绉幫紝鍚庨潰璺熲€?*鈥濊〃绀鴻繖涓暟鎹簱涓嬫墍鏈夌殑琛紱涔熷彲浠ヨ窡鈥?XXX鈥濊〃绀烘煇涓崟鐙殑琛? to 銆€銆€銆€銆€銆€聽#鍏抽敭瀛?鍚庨潰璺熺敤鎴峰悕鍜岀櫥褰曚富鏈猴紙鏄寚杩滅▼闇€瑕佺櫥褰曠殑涓繪満锛?琛ㄧず鍓嶉潰鏉冮檺缁欏摢涓敤鎴? identified銆€銆€ #鍏抽敭瀛?璁劇疆瀵嗙爜锛堣〃绀哄悗闈㈡暟鎹瘑鐮侊級

by銆€銆€銆€銆€聽 聽 #鍏抽敭瀛?鍚庨潰璺熷瘑鐮?

eg锛? 锛?锛夊鍔犱竴涓敤鎴穞est1瀵嗙爜涓篴bc锛岃浠栧彲浠ュ湪浠諱綍涓繪満涓婄櫥褰曪紝骞跺鎵€鏈夋暟鎹簱鏈夋煡璇€佹彃鍏ャ€佷慨鏀廣€佸垹闄ょ殑鏉冮檺銆?

棣栧厛鐢╮oot鐢ㄦ埛杩炲叆MYSQL锛岀劧鍚庨敭鍏ヤ互涓嬪懡浠わ細

mysql>grant select,insert,update,delete on *.* to [email聽protected]"%" Identified by "abc";

瑙ｆ瀽锛? 鎺堟潈鍙互澶氭潈闄愶紝鐢?鍙烽棿闅旓紱

on *.* 鈥?.*鈥?琛ㄧず鎵€鏈夋暟鎹簱鐨勬墍鏈夎〃锛屽墠闈?鍙瘋〃绀烘墍鏈夋暟鎹簱锛屽悗闈?鍙瘋〃绀烘暟鎹簱鍐呯殑琛? to [email聽protected]鈥?鈥?琛ㄧずtest1鐢ㄦ埛锛孈鈥?鈥?%鍙瘋〃绀轟換鎰忎富鏈? by 鈥渁bc鈥?琛ㄧず璁劇疆鐨勫瘑鐮? 娉ㄦ剰锛氳繖绉嶆潈闄愬鍔犵殑鐢ㄦ埛鏄緢鍗遍櫓鐨勶紝濡傛煇涓漢鐭ラ亾test1鐨勫瘑鐮侊紝閭ｄ箞浠栧氨鍙互鍦╥nternet涓婄殑浠諱綍涓€鍙扮數鑴戜笂鐧誨綍杩欏彴mysql鏁版嵁搴擄紝骞跺彲瀵規暟鎹繘琛屼換鎰忔搷浣滐紝瑙ｅ喅鍔炴硶鏄缃櫥褰曟潈闄愩€?

锛?锛夊鍔犱竴涓敤鎴穞est2瀵嗙爜涓篴bc,璁╁畠鍙彲浠ュ湪localhost涓婄櫥褰曪紝骞跺彲浠ュ鏁版嵁搴搈ydb杩涜鏌ヨ銆佹彃鍏ャ€佷慨鏀廣€佸垹闄ょ殑鎿嶄綔锛坙ocalhost鎸囨湰鍦頒富鏈猴紝鍗矼YSQL鏁版嵁搴撴墍鍦ㄧ殑閭ｅ彴涓繪満锛夈€? mysql>grant select,insert,update,delete on mydb.* to [email聽protected] identified by "abc";

濡傛灉浣犱笉鎯硉est2鏈夊瘑鐮侊紙鎴栬€呭彇娑坱est2瀵嗙爜锛夛紝鍙互鎵ц涓嬮潰杩欎釜鍛戒護灏嗗瘑鐮佸彇娑堟帀銆? mysql>grant select,insert,update,delete on mydb.* to [email聽protected] identified by "";

濡傛灉鎯崇粰涓€涓敤鎴穞est2鎺堜簣璁塊棶mydb鏁版嵁搴撶殑鎵€鏈夋潈闄愶紝骞朵笖浠呭厑璁竧est2鍦?92.168.11.121杩欎釜瀹㈡埛绔痠p鐧誨綍璁塊棶锛屽彲鎵ц濡備笅鍛戒護锛? mysql>grant all on mydb.* to [email聽protected] identified by "abc";

瑙ｆ瀽锛? all 琛ㄧず鎵€鏈夋潈闄?

聽

4銆佹暟鎹簱鍩虹鎿嶄綔

锛?锛夊垱寤烘暟鎹簱

娉ㄦ剰锛氬垱寤烘暟鎹簱涔嬪墠瑕佸厛杩炴帴Mysql鏈嶅姟鍣?

鍛戒護锛歝reate database <鏁版嵁搴撳悕>

鍒涘緩鏁版嵁搴擄紝骞跺垎閰嶇敤鎴鋒柟娉曪細

CREATE DATABASE 鏁版嵁搴撳悕;

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 鏁版嵁搴撳悕.* TO 鏁版嵁搴撳悕@localhost IDENTIFIED BY '瀵嗙爜';

eg锛氬緩绔嬩竴涓悕涓篴bc鐨勬暟鎹簱

mysql> create database abc;

锛?锛夋樉绀烘暟鎹簱

鍛戒護锛歴how databases 锛堟敞鎰忥細鏈€鍚庢湁涓猻锛? mysql> show databases;

锛?锛夊垹闄ゆ暟鎹簱

鍛戒護锛歞rop database <鏁版嵁搴撳悕>

eg锛氬垹闄ゅ悕涓?iivey鐨勬暟鎹簱

mysql> drop database iivey;

mysql> drop database if exists drop_database; 銆€銆€銆€銆€#if exists 鍒ゆ柇鏁版嵁搴撴槸鍚﹀瓨鍦紝涓嶅瓨鍦ㄤ篃涓嶄駭鐢熼敊璇?锛堝鏋渄rop_database鏁版嵁搴撳瓨鍦紝鍒犻櫎锛? Query OK, 0 rows affected (0.00 sec)

锛?锛夎繛鎺ユ暟鎹簱

鍛戒護锛?use <鏁版嵁搴撳悕>

eg锛氬鏋渋ivey鏁版嵁搴撳瓨鍦紝灏濊瘯瀛樺彇瀹冿細

mysql> use iivey;

use璇彞鍙互閫氬憡MySQL鎶奿ivey鏁版嵁搴撲綔涓洪粯璁わ紙褰撳墠锛夋暟鎹簱浣跨敤锛岀敤浜庡悗缁鍙ャ€傝鏁版嵁搴撲繚鎸佷負榛樿鏁版嵁搴擄紝鐩村埌璇鐨勭粨灏撅紝鎴栬€呯洿鍒闆彂甯冧竴涓笉鍚岀殑USE璇彞銆?

聽

5銆丮ysql琛ㄦ搷浣?

锛?锛夊垱寤烘暟鎹〃

鍛戒護锛歝reate table <琛ㄥ悕> ( <瀛楁鍚?> <绫誨瀷1> [,..<瀛楁鍚峮> <绫誨瀷n>]);

mysql> create table MyClass(

> id int(4) not null primary key auto_increment,

> name char(20) not null,

> sex int(4) not null default '0',

> degree double(16,2));

锛?锛夊垹闄ゆ暟鎹〃

鍛戒護锛歞rop table <琛ㄥ悕>

eg锛氬垹闄よ〃鍚嶄負 MyClass 鐨勮〃

mysql> drop table MyClass;

锛?锛夎〃鎻掑叆鏁版嵁

鍛戒護锛歩nsert into <琛ㄥ悕> [( <瀛楁鍚?>[,..<瀛楁鍚峮 > ])] values ( 鍊? )[, ( 鍊糿 )]

瑙ｆ瀽锛? <瀛楁鍚?>[,..<瀛楁鍚峮 > ]) 杩欓噷瀛楁鍙互鐪佺暐锛屽悗闈㈠搴旂殑values 灏變細浠ュ垱寤烘椂瀛楁杩涜榛樿鎻掑叆璁闆綍銆? eg锛氬湪琛∕yClass涓彃鍏ヤ袱鏉¤褰? 杩欎袱鏉¤褰曡〃绀猴細缂栧彿涓?鐨勫悕涓篢om鐨勬垚缁╀負90.45, 缂栧彿涓? 鐨勫悕涓篔oan 鐨勬垚缁╀負88.99锛?缂栧彿涓?鐨勫悕涓篧ang鐨勬垚缁╀負99.5銆? mysql> insert into MyClass values(1,'Tom',90.45),(2,'Joan',88.99), (3,'Wang', 99.5);

锛?锛夋煡璇㈣〃涓殑鏁版嵁

1)銆佹煡璇㈡墍鏈夎

鍛戒護锛?select <瀛楁1锛屽瓧娈?锛?..> from < 琛ㄥ悕 > where < 琛ㄨ揪寮?>

瑙ｆ瀽锛? *鍙?琛ㄧず浠繪剰鍖歸厤瀛楁锛堟墍鏈夋暟鎹簱鎴栬〃鍐呮墍鏈夋暟鎹級

from 鍚庨潰璺熻〃鍚? where 鏉′歡锛堣繘涓€姝ユ煡璇㈢殑鏉′歡锛? eg锛氭煡鐪嬭〃 MyClass 涓墍鏈夋暟鎹? mysql> select * from MyClass;

2锛夈€佹煡璇㈠墠鍑犺鏁版嵁

eg锛氭煡鐪嬭〃 MyClass 涓墠2琛屾暟鎹? mysql> select * from MyClass order by id limit 0,2;

瑙ｆ瀽锛? ORDER BY 璇彞鐢ㄤ簬鏍規嵁鎸囧畾鐨勫垪瀵圭粨鏋滈泦杩涜鎺掑簭銆? ORDER BY 璇彞榛樿鎸夌収鍗囧簭瀵矽褰曡繘琛屾帓搴忋€? 濡傛灉甯屾湜鎸夌収闄嶅簭瀵矽褰曡繘琛屾帓搴忥紝鍙互浣跨敤 DESC 鍏抽敭瀛?

锛?锛夊垹闄よ〃涓暟鎹? 鍛戒護锛歞elete from 琛ㄥ悕 where 琛ㄨ揪寮? eg锛氬垹闄よ〃 MyClass涓紪鍙蜂負1鐨勮褰? mysql> delete from MyClass where id=1;

锛?锛変慨鏀矽〃涓暟鎹? 璇硶锛歶pdate 琛ㄥ悕 set 瀛楁=鏂闆€?鈥?where 鏉′歡

mysql> update MyClass set name='Mary' where id=1;

锛?锛夊鍔犲瓧娈? 鍛戒護锛歛lter table 琛ㄥ悕 add 瀛楁 绫誨瀷 鍏朵粬;

eg锛氬湪琛∕yClass涓坊鍔犱簡涓€涓瓧娈祊asstest锛岀被鍨嬩負int(4)锛岄粯璁ゅ€間負0

mysql> alter table MyClass add passtest int(4) default '0'

锛?锛変慨鏀矽〃鍚? 鍛戒護锛歳ename table 鍘熻〃鍚?to 鏂拌〃鍚?

eg锛氬湪琛∕yClass鍚嶅瓧鏇存敼涓篩ouClass

mysql> rename table MyClass to YouClass;

鍙︼細

鏌ョ湅鏌愪釜鏁版嵁搴撳唴鎵€鏈夎〃淇℃伅锛? 杩涘叆鏁版嵁搴? show tables;

desc xxxx; 琛ㄧず鏌ョ湅琛ㄧ殑缁撴瀯淇℃伅

6銆佸浠芥暟鎹簱

锛?锛夊鍑烘暣涓暟鎹簱

瀵煎嚭鏂囦歡榛樿鏄瓨鍦? mysqldump -u 鐢ㄦ埛鍚?-p 鏁版嵁搴撳悕 > 瀵煎嚭鐨勬枃浠跺悕

mysqldump -u user_name -p123456 database_name > outfile_name.sql

锛?锛夊鍑轟竴涓〃

mysqldump -u 鐢ㄦ埛鍚?-p 鏁版嵁搴撳悕 琛ㄥ悕> 瀵煎嚭鐨勬枃浠跺悕

mysqldump -u user_name -p database_name table_name > outfile_name.sql

锛?锛夊鍑轟竴涓暟鎹簱缁撴瀯

mysqldump -u user_name -p -d --add-drop-table database_name > outfile_name.sql

鍚箟锛? -d 琛ㄧず鍙鍑鴻〃缁撴瀯涓嶅鍑烘暟鎹? --add-drop-table锛堥粯璁よ鍙ワ紝鍙互涓嶅姞锛?鍦ㄦ瘡涓猚reate璇彞涔嬪墠澧炲姞涓€涓猟rop table

涔熷氨鏄榛樿涓嬶紝瀵煎嚭鐨勬瘡涓〃鎴栨暟鎹簱鐨刢reate璇彞鍓嶏紝閮戒細鍔犲叆鈥淒ROP TABLE IF EXISTS 'XXXXX'鈥濊繖鍙ユ彁绀猴紝濡傛灉涓嶉渶瑕佹彁绀哄彲浠ョ敤鈥?-skip-add-drop-table鈥濊〃绀鴻煩杩囨彁绀?

锛?锛夊甫璇█鍙傛暟瀵煎嚭

mysqldump -uroot -p 鈥揹efault-character-set=latin1 --set-charset=gbk --skip-opt database_name > outfile_name.sql

锛?锛夊鍏ユ暟鎹? create database XXX; 銆€銆€銆€銆€ #鍏堝垱寤烘暟鎹簱

use XXX;銆€銆€銆€銆€銆€銆€銆€銆€銆€銆€ #鎸囧畾璇ユ暟鎹簱

source XXX.sql; 銆€銆€銆€銆€銆€銆€銆€聽 #瀵煎叆鏁版嵁锛涙敞鎰忚鎿嶄綔闇€瑕佸湪淇濆瓨澶囦喚鏁版嵁鏂囦歡鍐呰繘琛屻€?