mysql alter table 修改表命令詳細介紹_Mysql

MySQL ALTER文法如下：

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...] alter_specification: ADD [COLUMN] create_definition [FIRST | AFTER column_name ] or ADD INDEX [index_name] (index_col_name,...) or ADD PRIMARY KEY (index_col_name,...) or ADD UNIQUE [index_name] (index_col_name,...) or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} or CHANGE [COLUMN] old_col_name create_definition or MODIFY [COLUMN] create_definition or DROP [COLUMN] col_name or DROP PRIMARY KEY or DROP INDEX index_name or RENAME [AS] new_tbl_name or table_options  

下面來看幾個執行個體：

1、向表employee中添加Account_Number欄位並設定其欄位類型為INT

ALTER TABLE employee ADD COLUMN Account_Number INT

2、修改表employee中的ID欄位為索引

ALTER TABLE employee ADD INDEX (ID) 

3、修改表employee中的ID欄位為主鍵PRIMARY KEY

ALTER TABLE employee ADD PRIMARY KEY (ID)

4、修改表employee中的ID欄位為唯一索引UNIQUE

ALTER TABLE employee ADD UNIQUE (ID)

5、將employee表中的id欄位重新命名為salary並設定其資料類型為int

ALTER TABLE employee CHANGE ID salary INT

6、刪除employee表中的Customer_ID欄位

ALTER TABLE employee DROP Customer_ID

7、刪除employee表中所有主鍵

ALTER TABLE employee DROP PRIMARY KEY

8、刪除employee表中欄位Customer_ID的索引，只是將Customer_ID的索引取消，不會刪除Customer_ID欄位。

ALTER TABLE employee DROP INDEX Customer_ID

9、修改employee表中First_Name的欄位類型為varchar(100)

ALTER TABLE employee MODIFY First_Name varchar(100) 

10、將表employee重新命名為Customer

ALTER TABLE employee RENAME Customer 

11、多命令寫在一起：

mysql> ALTER TABLE Books  -> ADD PRIMARY KEY (BookID),  -> ADD CONSTRAINT fk_1 FOREIGN KEY (PubID) REFERENCES Publishers (PubID),  -> ADD COLUMN Format ENUM('paperback', 'hardcover') NOT NULL AFTER BookName;

感謝閱讀，希望能協助到大家，謝謝大家對本站的支援！