（1）添加列

  • 单列

    ALTER TABLE tab_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

  • 多列

    ALTER TABLE tab_name ADD [COLUMN] (col_name column_definition,…)

  • 例子

    alter table tab add password tinyint; alter table tab add (sex smallint,salary smallint); alter table tab add career varchar(20) first; alter table tab add age tinyint after password;

  1. mysql> create table tab(
  2.     -> id tinyint primary key auto_increment,
  3.     -> username varchar(20)                                                         
  4.     -> );
  5. Query OK, 0 rows affected (0.03 sec)
  7. mysql> show columns from tab;
  8. +----------+-------------+------+-----+---------+----------------+
  9. | Field    | Type        | Null | Key | Default | Extra          |
  10. +----------+-------------+------+-----+---------+----------------+
  11. | id       | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
  12. | username | varchar(20) | YES  |     | NULL    |                |
  13. +----------+-------------+------+-----+---------+----------------+
  14. 2 rows in set (0.00 sec)
  16. mysql> alter table tab add password tinyint;
  17. Query OK, 0 rows affected (0.10 sec)
  18. Records: 0  Duplicates: 0  Warnings: 0
  20. mysql> alter table tab add (sex smallint,salary smallint);
  21. Query OK, 0 rows affected (0.04 sec)
  22. Records: 0  Duplicates: 0  Warnings: 0
  24. mysql> show columns from tab;
  25. +----------+-------------+------+-----+---------+----------------+
  26. | Field    | Type        | Null | Key | Default | Extra          |
  27. +----------+-------------+------+-----+---------+----------------+
  28. | id       | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
  29. | username | varchar(20) | YES  |     | NULL    |                |
  30. | password | tinyint(4)  | YES  |     | NULL    |                |
  31. | sex      | smallint(6) | YES  |     | NULL    |                |
  32. | salary   | smallint(6) | YES  |     | NULL    |                |
  33. +----------+-------------+------+-----+---------+----------------+
  34. 5 rows in set (0.00 sec)
  36. mysql> alter table tab add career varchar(20) first;
  37. Query OK, 0 rows affected (0.03 sec)
  38. Records: 0  Duplicates: 0  Warnings: 0
  40. mysql> show columns from tab;
  41. +----------+-------------+------+-----+---------+----------------+
  42. | Field    | Type        | Null | Key | Default | Extra          |
  43. +----------+-------------+------+-----+---------+----------------+
  44. | career   | varchar(20) | YES  |     | NULL    |                |
  45. | id       | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
  46. | username | varchar(20) | YES  |     | NULL    |                |
  47. | password | tinyint(4)  | YES  |     | NULL    |                |
  48. | sex      | smallint(6) | YES  |     | NULL    |                |
  49. | salary   | smallint(6) | YES  |     | NULL    |                |
  50. +----------+-------------+------+-----+---------+----------------+
  51. 6 rows in set (0.00 sec)
  53. mysql> alter table tab add age tinyint after password;
  54. Query OK, 0 rows affected (0.05 sec)
  55. Records: 0  Duplicates: 0  Warnings: 0
  57. mysql> show columns from tab;
  58. +----------+-------------+------+-----+---------+----------------+
  59. | Field    | Type        | Null | Key | Default | Extra          |
  60. +----------+-------------+------+-----+---------+----------------+
  61. | career   | varchar(20) | YES  |     | NULL    |                |
  62. | id       | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
  63. | username | varchar(20) | YES  |     | NULL    |                |
  64. | password | tinyint(4)  | YES  |     | NULL    |                |
  65. | age      | tinyint(4)  | YES  |     | NULL    |                |
  66. | sex      | smallint(6) | YES  |     | NULL    |                |
  67. | salary   | smallint(6) | YES  |     | NULL    |                |
  68. +----------+-------------+------+-----+---------+----------------+
  69. 7 rows in set (0.00 sec)

（2）删除列

  • ALTER TABLE tab_name DROP [COLUMN] col_name column_definition,DROP …
  • 例子

    alter table tab drop career; alter table tab drop age,drop sex; alter table tab add sex int,drop salary;

mysql> alter table tab drop career;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from tab;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
| password | tinyint(4)  | YES  |     | NULL    |                |
| age      | tinyint(4)  | YES  |     | NULL    |                |
| sex      | smallint(6) | YES  |     | NULL    |                |
| salary   | smallint(6) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> alter table tab drop age,drop sex;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from tab;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
| password | tinyint(4)  | YES  |     | NULL    |                |
| salary   | smallint(6) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> alter table tab add sex int,drop salary;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from tab;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
| password | tinyint(4)  | YES  |     | NULL    |                |
| sex      | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

（3）修改列

修改列定义

  • 修改列定义(列名或者位置)
  • ALTER TABLE tb_name MODIFY [COLUMNS] col_name colum_definition [FIRST | AFTER col_name];

    alter table tb3 modify username smallint unsigned not null first;

mysql> create table tb3(
    -> id int primary key auto_increment,
    -> username varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc tb3;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> alter table tb3 modify username smallint unsigned not null first;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb3;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| username | smallint(5) unsigned | NO   |     | NULL    |                |
| id       | int(11)              | NO   | PRI | NULL    | auto_increment |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

修改列名称

  • 修改列名称
  • ALTER TABLE tb_name CHANGE [COLUMNS] old_col_name new_col_name colum_definition [FIRST | AFTER col_name];（不仅修改列的定义，还可以改名称）

    alter table tb3 change id p_id varchar(20) not null unique;

mysql> alter table tb3 change id p_id varchar(20) not null unique;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb3;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | smallint(5) unsigned | NO   |     | NULL    |       |
| p_id     | varchar(20)          | NO   | PRI | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

（4）修改约束

添加删除默认约束

  • 默认约束
  • ALTER TABLE ta_name ALTER[COLUMN] col_name {SET DEFAULT litera | DROP DEFAULT};

    alter table user alter sex set default 15; alter table user alter sex drop default;

mysql> alter table user alter sex set default 15;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from user;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20)          | NO   | UNI | NULL    |       |
| sex      | smallint(6)          | YES  |     | 15      |       |
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
| pid      | smallint(6)          | YES  | MUL | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table user alter sex drop default;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from user;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20)          | NO   | UNI | NULL    |       |
| sex      | smallint(6)          | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
| pid      | smallint(6)          | YES  | MUL | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

添加外键约束

  • ALTER TABLE ta_name ADD [CONSTRAINT[symbol]] FOREIGN KEY (index_col_name,…) reference_definition;

    alter table user add foreign key (pid) references person(id);

mysql> create table person( 
    -> id smallint primary key auto_increment);
Query OK, 0 rows affected (0.08 sec)

mysql> alter table user add pid smallint;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from user;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20)          | NO   | UNI | NULL    |       |
| sex      | smallint(6)          | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
| pid      | smallint(6)          | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table user add foreign key (pid) references person(id);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table user;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `username` varchar(20) NOT NULL,
  `sex` smallint(6) DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL,
  `pid` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `pid` (`pid`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `person` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除外键约束

  • 删除外键约束
  • ALTER TABLE tb_name DROP FOREIGN KEY fk_symbol;删除外键约束
  • 先找到外键的symbol，按这个删除外键，再DROP INDEX

    alter table user drop foreign key user_ibfk_1;

mysql> show create table user;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `username` varchar(20) NOT NULL,
  `id` smallint(5) unsigned NOT NULL,
  `pid` smallint(6) DEFAULT NULL,
  `sex` smallint(6) DEFAULT NULL,
  UNIQUE KEY `username` (`username`),
  KEY `pid` (`pid`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `person` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table user drop foreign key user_ibfk_1;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table user;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `username` varchar(20) NOT NULL,
  `id` smallint(5) unsigned NOT NULL,
  `pid` smallint(6) DEFAULT NULL,
  `sex` smallint(6) DEFAULT NULL,
  UNIQUE KEY `username` (`username`),
  KEY `pid` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)