涓€銆乵ysql鐨勫熀鏈嬌鐢?
1. 杩炴帴鍒癿ysql
mysql [-h 涓繪満] -u 鐢ㄦ埛 -p
涓句緥锛堣繖閲岀敤鐨勬槸鎿嶄綔绯葷粺鐨刢md锛夛細
娉細聽
锛?锛?
濡傛灉娌℃湁鍐橻-h涓繪満]榛樿杩炴帴鐨勬槸涓繪満銆?
锛?锛?
濡傛灉闇€瑕佺櫥褰曞埌鍙﹀涓€涓猰ysql锛屽垯闇€瑕佷慨鏀歸厤缃紝涓€鑸儏鍐典笅涓嶅厑璁歌繙绋嬬櫥褰曘€?
2. mysql鍏抽棴鍜屽惎鍔?
鍏抽棴锛歴ervice mysqld stop 聽
鍚姩锛歴ervice mysqld start聽
浜屻€佸簱鐨勬搷浣?
鎵撳紑mysql:
1.鏌ョ湅鏈夊摢浜涙暟鎹簱
show databases;
2.鍒涘緩鏁版嵁搴?
create database class_1;
璇硶锛?
聽
create database [if not exists] db_name [create_specification [, create_specification] 鈥
create_specification鍖呮嫭锛?
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name聽聽
娉細锛?锛塠]鏄彲閫夐」聽
聽 聽 聽聽 锛?锛塁HARACTER SET鏄暟鎹簱閲囩敤鐨勫瓧绗﹂泦锛?
聽 聽 聽聽 锛?锛塁OLLATE鏄寚瀹氭暟鎹簱瀛楃闆嗙殑姣旇緝鏂瑰紡锛?
聽 聽 聽聽 锛?锛夋煡鐪嬬郴缁熼粯璁ゅ瓧绗﹂泦鍜屾牎楠岃鍒欑殑鍛戒護锛?
聽 聽 聽 聽聽 show variables like 鈥榗haracter_set_database鈥?
聽 聽 聽 聽聽 show variables like 鈥榗ollation_database鈥?
褰撴垜浠垱寤烘暟鎹簱娌℃湁鎸囧畾瀛楃闆嗗拰鏍￠獙瑙勫垯鏃訛紝绯葷粺浣跨敤榛樿锛屽瓧绗﹂泦锛歶tf8锛屾牎楠岃鍒欐槸锛歶tf8_ general_ ci銆?
聽 鏍￠獙瑙勫垯锛?
1.鍖哄垎澶у皬鍐?
utf8_general_ci涓嶅尯鍒嗗ぇ灏忓啓锛堥粯璁ゆ牎楠岃鍒欙級
utf8_bin鍖哄垎澶у皬鍐?
2.褰卞搷鎺掑簭
鈶爑tf8_general_ci鎸夌収瀛楃椤哄簭
鈶tf8_bin鎸夌収ascii鎺掑簭
璇存槑锛?
鈶犲叧閿瓧鏈€濂戒嬌鐢ㄥぇ鍐?
鈶?!40100 DEFAULT CHARACTER SET utf8 /琛ㄧず鐗堟湰澶т簬4.0
3.鍒犻櫎鏁版嵁搴?
drop database class_1;
鈥?
聽4銆佹煡鐪嬪綋鍓峂ySQL鏁版嵁搴撶殑杩炴帴鎯呭喌
show processlist;
5.澶囦喚鍜屾仮澶嶆暟鎹簱
澶囦喚
鏁版嵁搴擄細
鍦ㄦ櫘閫氬懡浠よ涓嬶細
mysqldump -u root -p 鏁版嵁搴撳悕 > 鏁版嵁搴撳瓨鏀捐礬寰?sql
澶囦喚鎴愬姛锛岃繘鍏ysql鎺у埗鍙般€?
鎭㈠鏁版嵁搴擄細
瑕佹仮澶嶆暟鎹簱锛屽繀椤誨厛鍒涘緩涓€涓┖鐨勬暟鎹簱銆傛渶濂藉拰浠ュ墠鐨勬暟鎹簱鍚嶅瓧涓€鑷淬€?
聽
source C:\Users\闄堟鼎\Desktop\class_1.sql
澶囦喚鏁版嵁搴撴敞鎰忎簨椤癸細
濡傛灉澶囦喚鐨勪笉鏄暣涓暟鎹簱锛岃€屾槸鍏朵腑鐨勪竴寮犺〃锛屾€庝箞鍋氾紵
澶囦喚琛細
mysqldump -u root -p 鏁版嵁搴撳悕 琛ㄥ悕1 琛ㄥ悕2鈥?gt; 璺緞.sql
鎭㈠琛細
source 澶囦喚鐨勬枃浠惰礬寰?
娉細锛?锛夊湪浣跨敤source 鎭㈠鏁版嵁鏃訛紝淇濊瘉use瀵瑰簲鐨勬暟鎹簱
聽 聽 聽聽 锛?锛夊悓鏃跺浠藉涓暟鎹簱锛歮ysqldump -u root -p瀵嗙爜 -B 鏁版嵁搴撳悕1聽 鏁版嵁搴撳悕2 ... > 鏁版嵁搴撳瓨鏀捐礬寰?
聽 聽 聽聽 锛?锛夊悓鏃舵仮澶嶅涓暟鎹簱聽 mysql > source 澶囦喚鐨勬枃浠?
聽 聽 聽聽 锛?锛夊鏋滄垜浠浠戒竴涓暟鎹簱鏃訛紝甯︿笂-B鍙傛暟锛屽湪鎭㈠鏁版嵁搴撴椂锛屼笉闇€瑕佸啀鍒涘緩绌烘暟鎹簱銆?
6.淇敼鏁版嵁搴?
瀵規暟鎹簱鐨勪慨鏀逛富瑕佹寚鐨勬槸淇敼鏁版嵁搴撶殑瀛楃闆嗭紝鏍￠獙瑙勫垯
璇硶锛毬?ALTER DATABASE [IF EXISTS] db_name [alter_spaci铿乧ation [,alter_spaci铿乧ation]...]
淇敼瀛楃闆?
涓句緥锛歛lter database test charset=gbk;
鏀規牎楠岃鍒?
涓句緥锛歛lter database test collate utf8_bin;
7.鍒犻櫎搴?drop database 鏁版嵁搴撳悕锛?
涓夈€佽〃鐨勬搷浣?/h2>
琛ㄦ搷浣滀箣鍓嶆垜浠鍏堝緱璇存槑鍦ㄥ摢涓暟鎹簱杩涜鎿嶄綔 锛?use 鏁版嵁搴撳悕锛?
1.鍒涘緩琛?
CREATE TABLE table_name
聽 聽 聽 聽 聽聽 (聽 field1 datatype,聽
聽 聽 聽 聽 聽聽 field2 datatype,聽
聽 聽 聽 聽 聽 field3 datatype
) character set 瀛楃闆?collate 鏍￠獙瑙勫垯 engine 瀛樺偍寮曟搸;
璇存槑锛?
铿乪ld 琛ㄧず鍒楀悕锛?
datatype 琛ㄧず鍒楃殑绫誨瀷锛?
character set 瀛楃闆嗭紝濡傛灉娌℃湁鎸囧畾瀛楃闆嗭紝鍒欎互鎵€鍦ㄦ暟鎹簱鐨勫瓧绗﹂泦涓哄噯銆?
collate 鏍￠獙瑙勫垯锛屽鏋滄病鏈夋寚瀹氭牎楠岃鍒欙紝鍒欎互鎵€鍦ㄦ暟鎹簱鐨勬牎楠岃鍒欎負鍑?
涓句緥锛?
鈥?code>鈥媡able鈥?/code>鈥?tt1(
鈥?code>鈥媔d鈥?/code>鈥?int,
鈥?code>鈥媙ame鈥?/code>鈥?varchar(20)
);
2.琛ㄤ腑鎻掑叆鏁版嵁
涓句緥鎻掑叆锛?
insert into tt1 values(1,鈥機鈥?,(2,鈥橞鈥?,(3,鈥檆鈥?,(4,鈥檅鈥?;
3.鏌ョ湅琛ㄧ殑鍐呭
select * from tt1;
4.鎺掑簭聽
姣斿鎴戜滑鎯蟲寜涓婅〃涓殑name鎺掑簭锛?
select * from tt1 order by name;聽
聽
5銆佹煡鐪嬭〃缁撴瀯
desc 琛ㄥ悕锛?
6銆佷慨鏀矽〃
淇敼鍩烘湰鎴戜滑鐢ㄧ殑鏄痑lter銆?
锛?锛夋坊鍔犲瓧娈?
濡備笅锛屾坊鍔犲勾榫勫拰鎬у埆锛?
娣誨姞鎴愬姛锛屾敞鎰弒ex娣誨姞鍒頒簡id鍚庨潰銆?
聽锛?锛?鏇存敼鍒楀ぇ灏?
eg锛氫慨鏀筺ame鐨勫ぇ灏忥細
娉ㄦ剰name鏀規垚浜唙archar(20)銆?
锛?锛夊垹闄ゆ煇鍒?
eg:鍒犻櫎age鍒楋細
锛?锛変慨鏀矽〃鍚?
eg锛氬皢tt1鏀逛負test锛?
聽
鎴戜滑鍦ㄦ煡鐪媡t1浼氭姤閿欙紝杩欐槸鍥犱負鏀瑰悕鍚巘t1宸茬粡涓嶅瓨鍦ㄤ簡锛屽彉鎴愪簡test锛?
锛?锛変慨鏀瑰瓧绗﹂泦聽 alter charset
聽eg:灏嗗瓧绗﹂泦鏀逛負gbk锛?
alter table test charset=gbk;
锛?锛変慨鏀瑰瓧娈靛悕锛堥渶瑕佺粰鍑哄叿浣撶被鍨嬪畾涔夛級
eg:灏唍ame鏀逛負濮撳悕锛?