mysql alter table命令修改表結(jié)構(gòu)實(shí)例_MySQL

mysql實(shí)例之使用alter table命令修改表結(jié)構(gòu)

mysql alter table語(yǔ)句可以修改表的基本結(jié)構(gòu),例如添加字段、刪除字段、添加主鍵、添加索引、修改字段數(shù)據(jù)類型、對(duì)表重命名等等操作,本文章通過(guò)兩個(gè)簡(jiǎn)單的實(shí)例向大家介紹mysql alter table的使用方法

?實(shí)例一:使用ALTER TABLE命令向表中添加字段、修改字段類型以及設(shè)置主鍵。

首先創(chuàng)建一個(gè)表,SQL語(yǔ)句如下:

 mysql> CREATE TABLE myTable(   ->  ID SMALLINT   -> ); 

使用desc命令查看表結(jié)構(gòu):

 mysql> desc myTable; +-------+-------------+------+-----+---------+-------+ | Field | Type    | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ID  | smallint(6) | YES |   | NULL  |    | +-------+-------------+------+-----+---------+-------+ 

使用ALTER TABLE命令向表中添加字段、修改字段類型以及設(shè)置主鍵,SQL語(yǔ)句如下:

 mysql> ALTER TABLE myTable   ->  ADD COLUMN Quantity SMALLINT UNSIGNED NOT NULL,   ->  MODIFY ID SMALLINT UNSIGNED NOT NULL,   ->  ADD PRIMARY KEY (ID); 

再次使用desc命令查看表結(jié)構(gòu),我們發(fā)現(xiàn)上面的修改命令生效了:

 desc myTable; +----------+----------------------+------+-----+---------+-------+ | Field  | Type         | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | ID    | smallint(5) unsigned | NO  | PRI |     |    | | Quantity | smallint(5) unsigned | NO  |   |     |    | +----------+----------------------+------+-----+---------+-------+ 

實(shí)例二:在上面的實(shí)例基礎(chǔ)上,我們刪除myTable表中的Quantity字段和PRIMARY KEY主鍵

 mysql> ALTER TABLE myTable   ->  DROP COLUMN Quantity,   ->  DROP PRIMARY KEY; 

查看表結(jié)構(gòu):

 mysql> desc myTable; +-------+----------------------+------+-----+---------+-------+ | Field | Type         | Null | Key | Default | Extra | +-------+----------------------+------+-----+---------+-------+ | ID  | smallint(5) unsigned | NO  |   |     |    | +-------+----------------------+------+-----+---------+-------+

感謝閱讀此文,希望能幫助到大家,謝謝大家對(duì)本站的支持!

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊6 分享