修改表ALTER TABLE,用于更改原有表结构
查看帮助:
mysql> \h alter table
Name: 'ALTER TABLE'
Description:
Syntax:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
修改表名 ALTER TABLE <old_table_name> RENAME [TO] <new_table_name>;
mysql> ALTER TABLE tb1 RENAME table1;
修改字段数据类型 ALTER TABLE <table_name> MODIFY <column_name> <data_type>
mysql> desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(45) | YES | | NULL | |
| price | float | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> ALTER TABLE tb2 MODIFY price DOUBLE;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(45) | YES | | NULL | |
| price | double | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
修改字段名 ALTER TABLE <table_name> CHANGE <old_column_name> <new_column_name> <data_type>;
mysql> ALTER TABLE tb2 CHANGE name book_name varchar(200);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
添加字段 ALTER TABLE <table_name> ADD <new_column_name> <data_type>;
mysql> ALTER TABLE tb2 ADD author VARCHAR(100);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
在表的第一列添加字段 ALTER TABLE <table_name> ADD <new_column_name> <data_type> FIRST;
在表的指定列之后添加一个字段 ALTER TABLE <table_name> ADD <new_column_name> <data_type> AFTER <column_name>;
删除字段 ALTER TABLE <table_name> DROP <column_name>;
修改字段排列位置
修改字段为表的第一个字段 ALTER TABLE <table_name> MODIFY <column_name> <data_type> FIRST;
修改字段为表的指定列之后 ALTER TABLE <table_name> MODIFY <column_name> <data_type> AFTER <location_name>;
更改表的存储引擎 ALTER TABLE <table_name> ENGINE=<new_engine_name>;
查看MySQL支持的引擎:mysql> show engines \G;
删除表的外键约束 ALTER TABLE <table_name> DROP FOREIGN KEY <fk_name>;
fk_name即为添加外键时CONSTRAINT后面的参数,这是外键的名字。创建时没有指定怎么办?没关系,MySQL会指定一个名字给它。
通过SHOW CREATE TABLE <table_name>查看表的创建信息,就会看到相关外键的名字了,可以通过这个名字进行删除。
删除表 DROP TABLE <table_name>
有其他表关联时,要先删除其他表或其他表的外键约束