软件开发培训班 >> 编程开发 >> MySQL


MySQL教程数据定义语言DDL示例详解

濡傛灉浣犳槸鍒氬垰瀛︿範MySQL鐨勫皬鐧斤紝鍦ㄤ綘鐪嬭繖绡囨枃绔犱箣鍓嶏紝璇峰厛鐪嬬湅涓嬮潰杩欎簺鏂囩珷銆傛湁浜涚煡璇嗕綘鍙兘鎺屾彙璧锋潵鏈夌偣鍥伴毦锛屼絾璇风浉淇℃垜锛屾寜鐓ф垜鎻愪緵鐨勮繖涓涔犳祦绋嬶紝鍙嶅鍘荤湅锛岃偗瀹氬彲浠ョ湅鏄庣櫧鐨勶紝杩欐牱灏变笉鑷充簬鍒颁簡鏈�鍚庢煇浜涚煡璇嗕笉鎳傚嵈涓嶇煡閬撲粠鍝噷涓嬫墜鍘绘煡銆?/p>

銆奙ySQL璇︾粏瀹夎鏁欑▼銆?/a>
銆奙ySQL瀹屾暣鍗歌浇鏁欑▼銆?/a>
銆婅繖鐐瑰熀纭�閮戒笉鎳傦紝鎬庝箞鍏ラ棬MySQL锛熴�?/a>
銆奵harset=utf8鐨勫師鐞嗭紝浣犵湡鐨勫紕鏄庣櫧浜嗗悧锛熴�?/a>
銆奙ySQL鏁版嵁绫诲瀷璇﹁В銆?/a>

1.SQL璇█鐨勫熀鏈姛鑳戒粙缁?/h2>

SQL鏄竴绉嶇粨鏋勫寲鏌ヨ璇█锛屼富瑕佹湁濡備笅鍑犱釜鍔熻兘锛?/p>

鏁版嵁瀹氫箟璇█锛氬叏绉版槸Data Definition Language锛岀畝绉版槸DDL锛涙暟鎹搷绾佃瑷�锛氬叏绉版槸Data Manipulation Language锛屽缓鎴愭椂DML锛涙暟鎹帶鍒惰瑷�锛氬叏绉版槸Data Control Language锛屽缓鎴愭椂DCL锛?/p>

鍏朵腑鏈�閲嶈鐨勬槸鏁版嵁鎿嶇旱璇█(DML)锛岄噷闈㈠寘鍚簡鎴戜滑甯哥敤鐨勫姛鑳?澧炪�佸垹銆佹敼銆佹煡)銆傚浜庢暟鎹畾涔夎瑷�(DDL)鍜屾暟鎹帶鍒惰瑷�(DCL)锛屼綔涓轰竴鍚嶆暟鎹垎鏋愬笀锛屾垜浠彧闇�瑕佷簡瑙ｏ紝鐭ラ亾鎬庝箞鐢ㄥ氨琛屼簡銆備粖澶╁氨浠嶥DL璇█寮�濮嬶紝甯︾潃澶у瀛︿範銆?/p>

2.鏁版嵁瀹氫箟璇█鐨勭敤閫?/h2>

涓昏鏄鏁版嵁搴撳璞★紙鏁版嵁搴撱�佽〃銆佽鍥俱�佺储寮曪級鐨勬搷浣溿�?/p>

DDL甯哥敤鍛藉悕濡備笅锛?/p>
鍒涘缓 淇敼 閿�姣?/th>
create alter drop

3.鏁版嵁搴撶殑鍒涘缓鍜岄攢姣?/h2> 
-- 鍒涘缓鏁版嵁搴擄細鍒涘缓student鏁版嵁搴?
create database if not exists student;
-- 閿�姣佹暟鎹簱(灏戠敤)
drop database if exists student;

-- 鍒涘缓鏁版嵁搴撳悗锛屽綋杩涜瀵硅〃鐨勬搷浣滀箣鍓嶏紝蹇呴』瑕佸厛浣跨敤鏁版嵁搴撱�?
use student;

4.鏁版嵁搴撹〃鐨勬搷浣?鎵�鏈夋紨绀洪兘浠tudent琛ㄤ负渚?

1锛夊垱寤鸿〃

1锛夊垱寤鸿〃锛氬垱寤簊tudent琛?
-- 鍒涘缓琛ㄧ粨鏋勬椂闇�瑕佹弿杩扮殑瀛楁淇℃伅
create table student(
     sid int,
     sname varchar(20),
     age int
)charset=utf8;

2锛?瀹屾暣鐨勫缓琛ㄨ鍙?搴旇杩欐牱鍐?
create table student(
     sid int,
     sname varchar(20),
     age int
)engine=InnoDB default charset=utf8;
娉細鐢变簬榛樿浣跨敤鐨別ngine灏辨槸InnoDB锛岃繖涓琛ㄦ椂鍊欏彲浠ヤ笉鍐欍�傚洜涓哄浜庡涔犵殑浣狅紝浣跨敤杩欎釜
榛樿寮曟搸灏卞浜嗐�備絾鏄痗harset=utf8杩欎釜鏈�濂芥槸鍔犱笂锛屽挨鍏舵槸鍦–MD榛戠獥鍙ｄ腑杈撳叆涓枃鐨勬椂鍊欙紝
涓嶅啓杩欎竴鍙ワ紝浼氬嚭鐜扮被浼煎涓嬮敊璇細
ERROR 1366 (HY000): Incorrect string value: 'xD5xC5' for column 'sname' at row 1

鏌ョ湅瀹屾暣寤鸿〃鐨凷QL璇彞濡備笅锛?/p>

鍦ㄨ繖閲屾彃鍏ュ浘鐗囨弿杩? src=

2锛変慨鏀硅〃

鈶?淇敼琛ㄥ悕

-- 灏嗚〃鍚峴tudent鏀逛负stu銆?
rename table student to stu;
-- 淇敼鏁版嵁搴撳悕銆?
rename database 鏃у悕 to 鏂板悕;

鈶?淇敼琛ㄧ粨鏋?淇敼琛ㄧ粨鏋勪腑鍖呭惈缁欒〃娣诲姞鏌愪釜鏂板瓧娈碉紝淇敼琛ㄤ腑鏌愪釜瀛楁锛屽垹闄よ〃涓煇涓瓧娈碉紱

鈪?缁欒〃娣诲姞鏌愪釜鏂板瓧娈?/p> 

"娣诲姞瀛楁锛氱粰student琛紝娣诲姞涓�涓柊鐨勫瓧娈点�?
1锛夐粯璁ゆ槸杩藉姞锛屽嵆鍦ㄦ渶鍚庝竴鍒楁坊鍔犳柊鐨勫瓧娈点�?
alter table student add sex varchar(20);

2锛夊湪棣栦綅娣诲姞鏂扮殑瀛楁锛屼娇鐢╢irst鍏抽敭瀛椼�?
alter table student add classid int first;

3锛夊湪鎸囧畾浣嶇疆娣诲姞鏂扮殑瀛楁锛屼娇鐢╝fter鍏抽敭瀛?鍦ㄦ寚瀹氬瓧娈靛悗闈㈡坊鍔犳柊瀛楁)銆?
alter table student add birthday date after age;

"鏌ョ湅琛ㄧ粨鏋勶細鍙互甯姪鎴戜滑浜嗚В琛ㄧ殑缁撴瀯淇℃伅"
desc student;

鈪?淇敼琛ㄤ腑鏌愪釜瀛楁

1锛?淇敼瀛楁鍚嶇О"锛歝hange
-- 淇敼瀛楁age鐨勫悕绉帮紝涓簊age銆?
-- modify涓嶈兘鐩存帴淇敼瀛楁鍚嶇О锛屽叾浣欒兘鐢╟hange鐨勫湴鏂癸紝灏卞彲浠ョ敤modify銆?
alter table student change age sage int;

2锛?淇敼瀛楁绫诲瀷"锛氭棦鍙互浣跨敤change锛岃繕鍙互浣跨敤modify銆?
"杩樺彲浠ヤ慨鏀箆archar(m)涓繖涓猰鐨勯暱搴?銆?
-- 淇敼sname瀛楁鐨勬暟鎹被鍨嬬敱varchar(20)涓簐archar(50)銆?
-- 鍙互浣跨敤濡備笅涓ょ鏂瑰紡锛?
alter table student change sname sname varchar(50);
alter table student modify sname varchar(50);

3锛?淇敼瀛楁浣嶇疆"锛氬彲浠ラ厤鍚堜娇鐢╢irst銆乤fter鍏抽敭瀛椼�?
-- 灏唖name瀛楁锛屾斁缃埌age鍚庨潰銆傚彲浠ヤ娇鐢ㄥ涓嬩袱绉嶆柟寮忥細
alter table student change sname sname varchar(50) after age;
alter table student modify sname varchar(50) after age;

鈪?鍒犻櫎琛ㄤ腑鏌愪釜瀛楁

-- 鍒犻櫎classid杩欎釜瀛楁
alter table student drop classid;

鈶?娓呯┖琛細娓呯┖琛ㄤ腑鎵�鏈夋暟鎹�?/p>

truncate鍙垹闄ゆ暟鎹絾鏄笉鍒犻櫎琛ㄧ粨鏋勶紱

娉ㄦ剰truncate涓巇elete鐨勫尯鍒紝閮界敤浜庡垹闄よ〃涓暟鎹紝鍖哄埆鍦ㄥ摢閲屽憿锛熷彲浠ヨ嚜琛屾煡鐪嬨�?/p> 

truncate table stu;

3锛夐攢姣佽〃

drop table stu;

浠ヤ笂灏辨槸MySQL鏁欑▼鏁版嵁瀹氫箟璇█DDL绀轰緥璇﹁В鐨勮缁嗗唴瀹癸紝鏇村鍏充簬MySQL鏁版嵁瀹氫箟璇█DDL鐨勮祫鏂欒鍏虫敞鑴氭湰涔嬪鍏跺畠鐩稿叧鏂囩珷锛?/p>