1.修改表名
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | cf | | dept01 | | tb_dept2 | | tb_emp | | tb_emp3 | | tb_emp6 | | tb_emp7 | | tb_emp8 | | test02 | +----------------+ 9 rows in set (0.00 sec) mysql> alter table tb_emp8 rename to ladykiller; Query OK, 0 rows affected (0.05 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | cf | | dept01 | | ladykiller | | tb_dept2 | | tb_emp | | tb_emp3 | | tb_emp6 | | tb_emp7 | | test02 | +----------------+ 9 rows in set (0.00 sec)
2.修改表中数据类型
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | cf | | dept01 | | ladykiller | | tb_dept2 | | tb_emp | | tb_emp3 | | tb_emp6 | | tb_emp7 | | test02 | +----------------+ 9 rows in set (0.00 sec) mysql> desc dept01; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(22) | NO | | NULL | | | location | varchar(50) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table dept01 modify location int; Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc dept01; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(22) | NO | | NULL | | | location | int(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
如果要改变的数据类型转换有误，则会出现
mysql> mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | cf | | dept01 | | ladykiller | | tb_dept2 | | tb_emp | | tb_emp3 | | tb_emp6 | | tb_emp7 | | test02 | +----------------+ 9 rows in set (0.00 sec) mysql> desc tb_emp -> ; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(25) | YES | | NULL | | | tept | varchar(25) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> select * from tb_emp; Empty set (0.00 sec) mysql> insert into tb_emp values (1,'first','qinghua'),(2,'second','beida'),(3,'thirld',45); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> alter table tb_emp modify tept int; ERROR 1366 (HY000): Incorrect integer value: 'qinghua' for column 'tept' at row 1
所以说，数据类型需要转换正确
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | cf | | dept01 | | ladykiller | | tb_dept2 | | tb_emp | | tb_emp3 | | tb_emp6 | | tb_emp7 | | test02 | +----------------+ 9 rows in set (0.00 sec) mysql> desc tb_emp -> ; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(25) | YES | | NULL | | | tept | varchar(25) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into tb_emp values (1,'a',10),(2,'b',20),(3,'c',30); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> alter table tb_emp modify tept int; Query OK, 3 rows affected (0.27 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from tb_emp; +------+------+------+ | id | name | tept | +------+------+------+ | 1 | a | 10 | | 2 | b | 20 | | 3 | c | 30 | +------+------+------+ 3 rows in set (0.00 sec) mysql>
3.修改字段名
alter table tb_emp change idd op varchar(20); 语法规则 alter table 表名 change 旧字段名 新字段名 新数据类型 新数据类型不为空 并且遵循数据优先级规则 user-defined data types (highest) sql_variant xml datetimeoffset datetime2 datetime smalldatetime date time float real decimal money smallmoney bigint int smallint tinyint bit ntext text image timestamp uniqueidentifier nvarchar (including nvarchar(max) ) nchar varchar (including varchar(max) ) char varbinary (including varbinary(max) ) binary (lowest)
4.添加字段
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | cf | | dept01 | | ladykiller | | tb_dept2 | | tb_emp | | tb_emp3 | | tb_emp6 | | tb_emp7 | | test02 | +----------------+ 9 rows in set (0.00 sec) mysql> desc tb_emp; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | op | varchar(20) | YES | | NULL | | | name | varchar(25) | YES | | NULL | | | tept | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> alter table tb_emp add sky int first; Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table tb_emp add kkk int not null unique after name; ERROR 1062 (23000): Duplicate entry '0' for key 'kkk' mysql> alter table tb_emp add kkk int unique after name; Query OK, 0 rows affected (0.24 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from tb_emp; +------+------+------+------+------+ | sky | op | name | kkk | tept | +------+------+------+------+------+ | NULL | 1 | a | NULL | 10 | | NULL | 2 | b | NULL | 20 | | NULL | 3 | c | NULL | 30 | +------+------+------+------+------+ 3 rows in set (0.00 sec)
5.删除字段
mysql> select * from tb_emp; +------+------+------+------+------+ | sky | op | name | kkk | tept | +------+------+------+------+------+ | NULL | 1 | a | NULL | 10 | | NULL | 2 | b | NULL | 20 | | NULL | 3 | c | NULL | 30 | +------+------+------+------+------+ 3 rows in set (0.00 sec) mysql> alter table tb_emp drop sky -> ; Query OK, 0 rows affected (0.23 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table tb_emp drop kkk,tept; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tept' at line 1 mysql> desc tb_emp; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | op | varchar(20) | YES | | NULL | | | name | varchar(25) | YES | | NULL | | | kkk | int(11) | YES | UNI | NULL | | | tept | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
6.修改字段的排列位置
mysql> desc tb_emp; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | op | varchar(20) | YES | | NULL | | | name | varchar(25) | YES | | NULL | | | kkk | int(11) | YES | UNI | NULL | | | tept | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table tb_emp modify name varchar(25) first; Query OK, 0 rows affected (0.76 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table tb_emp modify kkk varchar(25) after op; Query OK, 3 rows affected (0.32 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> desc tb_emp; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(25) | YES | | NULL | | | op | varchar(20) | YES | | NULL | | | kkk | varchar(25) | YES | UNI | NULL | | | tept | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
7.更改表的存储引擎
语法 alter table 表名 ENGINE=引擎 mysql> show create table tb_emp; +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_emp | CREATE TABLE `tb_emp` ( `name` varchar(25) DEFAULT NULL, `op` varchar(20) DEFAULT NULL, `kkk` varchar(25) DEFAULT NULL, `tept` int(11) DEFAULT NULL, UNIQUE KEY `kkk` (`kkk`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> alter table tb_emp engine=MyISAM; Query OK, 3 rows affected (0.53 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show create table tb_emp; +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_emp | CREATE TABLE `tb_emp` ( `name` varchar(25) DEFAULT NULL, `op` varchar(20) DEFAULT NULL, `kkk` varchar(25) DEFAULT NULL, `tept` int(11) DEFAULT NULL, UNIQUE KEY `kkk` (`kkk`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
8.删除外键约束
语法 alter table 表名 drop foreign key 外键名称 mysql> create table tb_emp9 -> ( -> id int primary key, -> constraint haha foreign key(id) references tb_emp3(id)); Query OK, 0 rows affected (0.10 sec) mysql> desc tb_emp9; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> show create table tb_emp9; +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_emp9 | CREATE TABLE `tb_emp9` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `haha` FOREIGN KEY (`id`) REFERENCES `tb_emp3` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table tb_emp9 drop foreign key haha; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table tb_emp9; +---------+---------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------------+ | tb_emp9 | CREATE TABLE `tb_emp9` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---------+---------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
9.删除表
从表可以直接删除 mysql> create table tb_emp10 ( id int primary key, constraint haha foreign key(id) references tb_emp3(id));; Query OK, 0 rows affected (0.26 sec) mysql> drop table tb_emp10; Query OK, 0 rows affected (0.04 sec) 主表的话，可以先删除从表再删除主表 如果需要保留从表，需要将外键关系解除即可