MySQLalter修改表、字段等信息

一、alter 修改表

1. 修改表名

alter table student rename to test_student;

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| student          |
| test_field_type  |
| test_null_zero   |
+------------------+
3 rows in set (0.00 sec)

mysql> alter table student rename to test_student;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| test_field_type  |
| test_null_zero   |
| test_student     |
+------------------+
3 rows in set (0.00 sec)

2. 修改表注释

alter table test_student comment '设计学生表';

mysql> alter table test_student comment '设计学生表';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| test_field_type  |
| test_null_zero   |
| test_student     |
+------------------+
3 rows in set (0.00 sec)

mysql> show create table test_student;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                         |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_student | CREATE TABLE `test_student` (
  `name` varchar(50) DEFAULT NULL,
  `nickname` varchar(50) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `score` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='设计学生表'      |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

二、alter 修改字段

1. 修改字段类型和注释

alter table sys_application modify column app_name varchar(20) COMMENT '应用的名称';

2. 修改字段类型

alter table sys_application modify column app_name text;

3. 单独修改字段注释

目前没发现有单独修改字段注释的命令语句。

4. 设置字段允许为空

alter table sys_application modify column description varchar(255) null COMMENT '应用描述';

5. 增加一个字段，设好数据类型，且不为空，添加注释

alert table sys_application add `url` varchar(255) not null comment '应用访问地址';

6. 增加主键