DDL 表操作-修改

添加字段

    ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束]；

案例 ： 为 typecho_users 表增加一个新的字段“昵称” 为 nickname;

mysql>alter table typecho_users add nickname varchar(20) comment '昵称';
Query OK, 1row affected(0.04 sec)
Records: 1 Duplicates:0 Warnings:0

mysql> desc typecho_users;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| uid        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(32)      | YES  | UNI | NULL    |                |
| password   | varchar(64)      | YES  |     | NULL    |                |
| mail       | varchar(200)     | YES  | UNI | NULL    |                |
| url        | varchar(200)     | YES  |     | NULL    |                |
| screenName | varchar(32)      | YES  |     | NULL    |                |
| created    | int(10) unsigned | YES  |     | 0       |                |
| activated  | int(10) unsigned | YES  |     | 0       |                |
| logged     | int(10) unsigned | YES  |     | 0       |                |
| group      | varchar(16)      | YES  |     | visitor |                |
| authCode   | varchar(64)      | YES  |     | NULL    |                |
| nickname   | varchar(20)      | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
12 rows in set (0.01 sec)

修改表名

ALTER TABLE 表名  RENAME TO 新表名

案例： 创建一个user_login 表，将表名改为 login_user

mysql> CREATE TABLE user_login( 
     > `id` INT  UNSIGNED AUTO_INCREMENT, 
     > `name` VARCHAR(10) NOT NULL, 
     > PRIMARY KEY (`id`) 
     > ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+-----------------------+
| Tables_in_typecho_bak |
+-----------------------+
| user_login            |
+-----------------------+
10 rows in set (0.00 sec)
mysql> ALTER TABLE user_login RENAME TO login_users;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-----------------------+
| Tables_in_typecho_bak |
+-----------------------+
| login_users           |
+-----------------------+
10 rows in set (0.00 sec)

修改数据类型

 ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

修改字段名和字段类型

 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释][约束]；

案例 ：将 login_users 表中的 name 字段改成user_name， 类型为 varcha(30)

mysql> ALTER TABLE login_users CHANGE `name` `user_name` varchar(30) COMMENT '用户名' NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除字段

 ALTER 表名 DROP 字段名；

案例： 将 typecho_users 表中的 nickname 删除；

mysql> ALTER TABLE typecho_users DROP nickname;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc typecho_users;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| uid        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(32)      | YES  | UNI | NULL    |                |
| password   | varchar(64)      | YES  |     | NULL    |                |
| mail       | varchar(200)     | YES  | UNI | NULL    |                |
| url        | varchar(200)     | YES  |     | NULL    |                |
| screenName | varchar(32)      | YES  |     | NULL    |                |
| created    | int(10) unsigned | YES  |     | 0       |                |
| activated  | int(10) unsigned | YES  |     | 0       |                |
| logged     | int(10) unsigned | YES  |     | 0       |                |
| group      | varchar(16)      | YES  |     | visitor |                |
| authCode   | varchar(64)      | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)