MySQL开发手册(六) 数据表结构的修改

(1)添加列


  • 单列—ALTER TABLE tab_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
  • 多列—ALTER TABLE tab_name ADD COLUMN
mysql> create table tab(
    -> id tinyint primary key auto_increment,
    -> username varchar(20)                                                         -> );
Query OK, 0 rows affected (0.03 sec)

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

mysql> alter table tab add password tinyint;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tab add (sex smallint,salary smallint);
Query OK, 0 rows affected (0.04 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      | smallint(6) | YES  |     | NULL    |                |
| salary   | smallint(6) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> alter table tab add career varchar(20) first;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from tab;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| career   | varchar(20) | YES  |     | NULL    |                |
| id       | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
| password | 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 add age tinyint after password;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from tab;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| career   | varchar(20) | YES  |     | NULL    |                |
| 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    |                |
+----------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

(2)删除列


  • ALTER TABLE tab_name DROP [COLUMN] col_name column_definition,DROP ...
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 ta_name ADD [CONSTRAINT[symbol]] PRIMARY KEY index_type; 约束标记 索引类型
mysql> create table user(
    -> username varchar(20) not null,
    -> sex smallint);
Query OK, 0 rows affected (0.04 sec)

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

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

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

mysql> alter table user add primary key(id);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from user;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20)          | NO   |     | NULL    |       |
| sex      | smallint(6)          | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • 唯一约束—ALTER TABLE ta_name ADD [CONSTRAINT[symbol]] UNIQUE [index_name] index_type;
mysql> alter table user add unique(username);
Query OK, 0 rows affected (0.08 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    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • 外键约束—ALTER TABLE ta_name ADD [CONSTRAINT[symbol]] FOREIGN KEY (index_col_name,...) reference_definition;
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 ta_name ALTER[COLUMN] col_name {SET DEFAULT litera | 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)

(4)删除约束


  • 删除主键约束—ALTER TABLE tb_name DROP PRIMARY KEY;
mysql> alter table user drop primary key;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from user;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20)          | NO   | PRI | NULL    |       |
| sex      | smallint(6)          | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | NO   |     | NULL    |       |
| pid      | smallint(6)          | YES  | MUL | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
* 删除唯一约束—ALTER TABLE tb_name DROP {INDEX | KEY} index_name;
mysql> alter table user add unique(sex);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

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

mysql> show columns from user;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20)          | NO   | PRI | NULL    |       |
| id       | smallint(5) unsigned | NO   |     | NULL    |       |
| pid      | smallint(6)          | YES  | MUL | NULL    |       |
| sex      | smallint(6)          | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
  • 删除外键约束—ALTER TABLE tb_name DROP FOREIGN KEY fk_symbol;删除外键约束(先找到外键的symbol,按这个删除外键,再DROP INDEX)
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)

(5)修改列定义


  • 修改列定义(列名或者位置)—ALTER TABLE tb_name MODIFY [COLUMNS] col_name colum_definition [FIRST | AFTER col_name];
mysql> alter table user modify id smallint unsigned not null first;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table user;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` smallint(5) unsigned NOT NULL,
  `username` varchar(20) 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)

mysql> alter table user modify id tinyint unsigned not null first;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table user;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` tinyint(3) unsigned NOT NULL,
  `username` varchar(20) 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.01 sec)
  • 修改列名称—ALTER TABLE tb_name CHANGE [COLUMNS] old_col_name new_col_name colum_definition [FIRST | AFTER col_name];(不仅修改列的定义,还可以改名称)
mysql> alter table user change pid p_id smallint not null;
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 |
+----------+---------------------+------+-----+---------+-------+
| id       | tinyint(3) unsigned | NO   |     | NULL    |       |
| username | varchar(20)         | NO   | PRI | NULL    |       |
| p_id     | smallint(6)         | NO   | MUL | NULL    |       |
| sex      | smallint(6)         | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

(6)修改数据表名称


  • 修改数据表名称—ALTER TABLE tb_name RENAME [TO|AS] new_tal_name;
  • RENAME TABLE tb1_name TO new tb1_name [,tb2_name TO new tb2_name ];
mysql> ALTER TABLE users2 RENAME users3;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| province       |
| tab            |
| users          |
| users1         |
| users3         |
+----------------+
11 rows in set (0.00 sec)

mysql> RENAME TABLE users3 TO users4;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| province       |
| tab            |
| users          |
| users1         |
| users4         |
+----------------+
11 rows in set (0.00 sec)

笔记资源下载链接 ,支持PDF、ENEX印象笔记、HTML,方便大家整理导入

© 著作权归作者所有
这个作品真棒,我要支持一下!
主要帮助小白掌握MySQL数据库的增删改查操作,最终提供一个工具类语句手册和完善的学习笔记
0条评论
top Created with Sketch.