MySQL資料庫基本操作指令

1銆佸垱寤烘暟鎹簱 create database <鏁版嵁搴撳悕>锛? 2銆佹樉绀烘暟鎹簱 show database锛? 3銆佸垹闄ゆ暟鎹簱 drop database <鏁版嵁搴撳悕>锛? 4銆佽繛鎺ユ暟鎹簱 use 聽<鏁版嵁搴撳悕>; 5銆丮ySQL涓殑select鐩稿綋浜庡叾浠栫紪绋嬭瑷€涓殑print鎴栬€厀rite锛屼綘鍙互鐢ㄥ畠鏉ユ樉绀轟竴涓瓧绗︿覆銆佹暟瀛椼€佹暟瀛楄〃杈懼紡鐨勭粨鏋滅瓑绛夈€? 渚嬪锛? 锛?锛夋樉绀篗ySQL鐨勭増鏈? select 聽version锛堬級锛? 锛?锛夋樉绀哄綋鍓嶆椂闂? select now锛堬級锛? 锛?锛夋樉绀哄勾鏈堟棩 select dayofmonth(current_date)锛? select month锛坈urrent_date锛夛紱 select year锛坈urrent_date锛夛紱 锛?锛夋樉绀哄瓧绗︿覆 select 鈥渨elcome to my blog鈥濓紱 6銆佸垱寤烘暟鎹〃 create table student(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)); 7銆佸垹闄ゆ暟鎹〃 drop table student锛? 8銆佽〃鎻掑叆鏁版嵁 insert into <琛ㄥ悕> values锛堝€?锛夛紝锛堝€?锛夛紝锛堝€?锛夈€傘€傘€傦紱 9銆佹煡璇㈣〃涓暟鎹? select <瀛楁1锛屽瓧娈?,.....> from 琛ㄥ悕 where <琛ㄨ揪寮?gt;; 10銆佹煡璇㈠墠鍑犺鏁版嵁 select * from 琛ㄥ悕 order by id limit 0,2锛? 11銆佸垹闄ゆ暟鎹〃涓殑鏁版嵁 delect from 琛ㄥ悕 where id = 1锛? 12銆佷慨鏀矽〃涓殑鏁版嵁 update 聽琛ㄥ悕 set 聽name=鈥榝ff鈥檞here id = 1锛?

13 澧炲姞瀛楁 鍛戒護锛?alter table聽琛ㄥ悕聽 add瀛楁 绫誨瀷 鍏朵粬;

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

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

鍔犵儲寮?

聽 聽mysql> alter table 琛ㄥ悕 add index 绱㈠紩鍚?(瀛楁鍚?[锛屽瓧娈靛悕2 鈥);

渚嬪瓙锛?mysql> alter table employee add index emp_name (name);

鍔犱富鍏抽敭瀛楃殑绱㈠紩

聽 mysql> alter table 琛ㄥ悕 add primary key (瀛楁鍚?;

渚嬪瓙锛?mysql> alter table employee add primary key(id);

鍔犲敮涓€闄愬埗鏉′歡鐨勭儲寮?

聽 聽mysql> alter table 琛ㄥ悕 add unique 绱㈠紩鍚?(瀛楁鍚?;

渚嬪瓙锛?mysql> alter table employee add unique emp_name2(cardnumber);

鍒犻櫎鏌愪釜绱㈠紩

聽 聽mysql> alter table 琛ㄥ悕 drop index 绱㈠紩鍚?

渚嬪瓙锛?mysql>alter table employee drop index emp_name;

澧炲姞瀛楁锛?

mysql> ALTER TABLE table_name ADD field_name field_type;

淇敼鍘熷瓧娈靛悕绉闆強绫誨瀷锛?

mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

鍒犻櫎瀛楁锛?

MySQL ALTER TABLE table_name DROP field_name;

14 淇敼琛ㄥ悕 鍛戒護锛歳ename table 鍘熻〃鍚?to 鏂拌〃鍚?

渚嬪锛氬湪琛∕yClass鍚嶅瓧鏇存敼涓篩ouClass

聽 聽mysql> rename table MyClass to YouClass;

褰撲綘鎵ц RENAME 鏃訛紝浣犱笉鑳芥湁浠諱綍閿佸畾鐨勮〃鎴栨椿鍔ㄧ殑浜嬪姟銆備綘鍚屾牱涔熷繀椤繪湁瀵瑰師鍒濊〃鐨?ALTER 鍜?DROP 鏉冮檺锛屼互鍙婂鏂拌〃鐨?CREATE 鍜?INSERT 鏉冮檺銆?

濡傛灉鍦ㄥ琛ㄦ洿鍚嶄腑锛孧ySQL 閬亣鍒頒換浣曢敊璇紝瀹冨皢瀵規墍鏈夎鏇村悕鐨勮〃杩涜鍊掗€€鏇村悕锛屽皢姣忎歡浜嬬墿閫€鍥炲埌鏈€鍒濈姸鎬併€?

RENAME TABLE 鍦?MySQL 3.23.23 涓鍔犲叆銆? 15銆佸浠芥暟鎹簱 鍛戒護鍦―OS鐨刐url=file://\\mysql\\bin]\\mysql\\bin[/url]鐩綍涓嬫墽琛?

锛?锛?瀵煎嚭鏁翠釜鏁版嵁搴?

瀵煎嚭鏂囦歡榛樿鏄瓨鍦╩ysql\bin鐩綍涓?

聽聽聽 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 鈥揳dd-drop-table database_name > outfile_name.sql

聽聽聽 -d 娌℃湁鏁版嵁 鈥揳dd-drop-table 鍦ㄦ瘡涓猚reate璇彞涔嬪墠澧炲姞涓€涓猟rop table

锛?锛?甯﹁瑷€鍙傛暟瀵煎嚭

聽聽聽 mysqldump -uroot -p 鈥揹efault-character-set=latin1 鈥搒et-charset=gbk 鈥搒kip-opt database_name > outfile_name.sql

渚嬪锛屽皢aaa搴撳浠藉埌鏂囦歡back_aaa涓細

銆€銆€[[email聽protected] root]# cd銆€/home/data/mysql

銆€銆€[[email聽protected] mysql]# mysqldump -u root -p --opt aaa > back_aaa 16涓€涓緩搴撳拰寤鴻〃鐨勫疄渚? drop database if exists school; //濡傛灉瀛樺湪SCHOOL鍒欏垹闄?

create database school; //寤虹珛搴揝CHOOL

use school; //鎵撳紑搴揝CHOOL

create table teacher //寤虹珛琛═EACHER

(

聽聽聽 id int(3) auto_increment not null primary key,

聽聽聽 name char(10) not null,

聽聽聽 address varchar(50) default 鈥樻繁鍦斥€?

聽聽聽 year date

); //寤鴻〃缁撴潫

//浠ヤ笅涓烘彃鍏ュ瓧娈?

insert into teacher values(鈥?鈥檃llen鈥?'澶ц繛涓€涓€?'1976-10-10鈥?;

insert into teacher values(鈥?鈥檍ack鈥?'澶ц繛浜屼腑鈥?'1975-12-23鈥?;

濡傛灉浣犲湪mysql鎻愮ず绗﹂敭鍏ヤ笂闈㈢殑鍛戒護涔熷彲浠ワ紝浣嗕笉鏂逛究璋冭瘯銆?

锛?锛夈€佷綘鍙互灏嗕互涓婂懡浠ゅ師鏍峰啓鍏ヤ竴涓枃鏈枃浠朵腑锛屽亣璁句負school.sql锛岀劧鍚庡鍒跺埌c:\\涓嬶紝骞跺湪DOS鐘舵€佽繘鍏ョ洰褰昜url=file://\\mysql\\bin]\\mysql\\bin[/url]锛岀劧鍚庨敭鍏ヤ互涓嬪懡浠わ細

聽聽聽 mysql -uroot -p瀵嗙爜 < c:\\school.sql

濡傛灉鎴愬姛锛岀┖鍑轟竴琛屾棤浠諱綍鏄劇ず锛涘鏈夐敊璇紝浼氭湁鎻愮ず銆傦紙浠ヤ笂鍛戒護宸茬粡璋冭瘯锛屼綘鍙灏?/鐨勬敞閲婂幓鎺夊嵆鍙嬌鐢級銆?

锛?锛夈€佹垨鑰呰繘鍏ュ懡浠よ鍚庝嬌鐢?mysql> source c:\\school.sql; 涔熷彲浠ュ皢school.sql鏂囦歡瀵煎叆鏁版嵁搴撲腑銆?

17銆?涓€涓緩搴撳拰寤鴻〃鐨勫疄渚? drop database if exists school; //濡傛灉瀛樺湪SCHOOL鍒欏垹闄?

create database school; //寤虹珛搴揝CHOOL

use school; //鎵撳紑搴揝CHOOL

create table teacher //寤虹珛琛═EACHER

(

聽 聽 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璁句負闀垮害涓?鐨勬暟瀛楀瓧娈?int(3)锛涘苟璁╁畠姣忎釜璁闆綍鑷姩鍔犱竴:auto_increment锛涘苟涓嶈兘涓虹┖:not null锛涜€屼笖璁╀粬鎴愪負涓誨瓧娈祊rimary key銆?

2銆佸皢NAME璁句負闀垮害涓?0鐨勫瓧绗﹀瓧娈?

3銆佸皢ADDRESS璁句負闀垮害50鐨勫瓧绗﹀瓧娈碉紝鑰屼笖缂虹渷鍊間負娣卞湷銆?

4銆佸皢YEAR璁句負鏃ユ湡瀛楁銆?

灏嗘暟鎹簱涓殑瀵嗙爜鏀規垚MD5鍔犲瘑鐨勫艦寮忥紝涓嶄細浠ユ槑鏂囩殑鏍峰紡鍑虹幇锛? update subscriber set `password` = MD5(password)