Mysql 修改数据库，mysql修改表类型，Mysql增加表字段，Mysql删除表字段，Mysql修改字段名，Mysql修改字段排列顺序，Mysql修改表名...-白红宇

Mysql 修改数据库，mysql修改表类型，Mysql增加表字段，Mysql删除表字段，Mysql修改字段名，Mysql修改字段排列顺序，Mysql修改表名...

阅读量：6233 次

发布时间：2019-06-22

本文共 7002 字，大约阅读时间需要 23 分钟。

对于已经创建好的表，尤其是已经有大量数据的表，如果需要对表做一些结构上的改变，我们可以先将表删除（drop），然后再按照新的表定义重建表。这样做没有问题，但是必然要做一些额外的工作，比如数据的重新加载。而且，如果有服务在访问表，也会对服务产生影响。因此，在大多数情况下，表结构的更改一般都使用alter table 语句，以下是一些常用的命令。

（1）修改表类型，语法如下：

　　ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

　　例如，修改表emp 的ename 字段定义，将varchar(10)改为varchar(20)：

mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename | varchar(10) | YES | | | || hiredate | date | YES | | | || sal | decimal(10,2) | YES | | | || deptno | int(2) | YES | | | |+----------+---------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> alter table emp modify ename varchar(20);Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename | varchar(20) | YES | | | || hiredate | date | YES | | | || sal | decimal(10,2) | YES | | | || deptno | int(2) | YES | | | |+----------+---------------+------+-----+---------+-------+4 rows in set (0.00 sec)

（2）增加表字段，语法如下：

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

　　　例如，表emp 上新增加字段age，类型为int(3)：

mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename | varchar(20) | YES | | | || hiredate | date | YES | | | || sal | decimal(10,2) | YES | | | || deptno | int(2) | YES | | | |+----------+---------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> alter table emp add column age int(3);Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename | varchar(20) | YES | | | || hiredate | date | YES | | | || sal | decimal(10,2) | YES | | | || deptno | int(2) | YES | | | || age | int(3) | YES | | | |+----------+---------------+------+-----+---------+-------+5 rows in set (0.00 sec)

（3）删除表字段，语法如下：

ALTER TABLE tablename DROP [COLUMN] col_name

　　例如，将字段age 删除掉：

mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename | varchar(20) | YES | | | || hiredate | date | YES | | | || sal | decimal(10,2) | YES | | | || deptno | int(2) | YES | | | || age | int(3) | YES | | | |+----------+---------------+------+-----+---------+-------+5 rows in set (0.00 sec)mysql> alter table emp drop column age;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename | varchar(20) | YES | | | || hiredate | date | YES | | | || sal | decimal(10,2) | YES | | | || deptno | int(2) | YES | | | |+----------+---------------+------+-----+---------+-------+4 rows in set (0.00 sec)

（4）字段改名，语法如下：

ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition   [FIRST|AFTER col_name]

　　例如，将age 改名为age1，同时修改字段类型为int(4)：

mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename | varchar(20) | YES | | | || hiredate | date | YES | | | || sal | decimal(10,2) | YES | | | || deptno | int(2) | YES | | | || age | int(3) | YES | | | |+----------+---------------+------+-----+---------+-------+mysql> alter table emp change age age1 int(4) ;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc emp-> ;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename | varchar(20) | YES | | | || hiredate | date | YES | | | || sal | decimal(10,2) | YES | | | || deptno | int(2) | YES | | | || age1 | int(4) | YES | | | |+----------+---------------+------+-----+---------+-------+5 rows in set (0.00 sec)

注意：change 和modify 都可以修改表的定义，不同的是change 后面需要写两次列名，不方便。但是change 的优点是可以修改列名称，modify 则不能。

（5）修改字段排列顺序。

前面介绍的的字段增加和修改语法（ADD/CNAHGE/MODIFY）中，都有一个可选项first|after column_name，这个选项可以用来修改字段在表中的位置，默认ADD 增加的新字段是加在表的最后位置，而CHANGE/MODIFY 默认都不会改变字段的位置。例如，将新增的字段birth date 加在ename 之后：

mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename | varchar(20) | YES | | | || hiredate | date | YES | | | || sal | decimal(10,2) | YES | | | || deptno | int(2) | YES | | | || age | int(3) | YES | | | |+----------+---------------+------+-----+---------+-------+5 rows in set (0.00 sec)mysql> alter table emp add birth date after ename;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename | varchar(20) | YES | | | || birth | date | YES | | | || hiredate | date | YES | | | || sal | decimal(10,2) | YES | | | || deptno | int(2) | YES | | | || age | int(3) | YES | | | |+----------+---------------+------+-----+---------+-------+6 rows in set (0.00 sec)

修改字段age，将它放在最前面：

mysql> alter table emp modify age int(3) first;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| age | int(3) | YES | | | || ename | varchar(20) | YES | | | || birth | date | YES | | | || hiredate | date | YES | | | || sal | decimal(10,2) | YES | | | || deptno | int(2) | YES | | | |+----------+---------------+------+-----+---------+-------+6 rows in set (0.00 sec)

注意：CHANGE/FIRST|AFTER COLUMN 这些关键字都属于MySQL 在标准SQL 上的扩展，在其他数据库上不一定适用。

（6）表改名，语法如下：

ALTER TABLE tablename RENAME [TO] new_tablename

例如，将表emp 改名为emp1，命令如下：

mysql> alter table emp rename emp1;Query OK, 0 rows affected (0.00 sec)mysql> desc emp;ERROR 1146 (42S02): Table 'sakila.emp' doesn't existmysql> desc emp1;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| age | int(3) | YES | | | || ename | varchar(20) | YES | | | || birth | date | YES | | | || hiredate | date | YES | | | || sal | decimal(10,2) | YES | | | || deptno | int(2) | YES | | | |+----------+---------------+------+-----+---------+-------+6 rows in set (0.00 sec)

转载地址：http://zoqna.baihongyu.com/

你可能感兴趣的文章