bitsCN.com 修改表结构：

alter [ignore] table <table specification> <table structure change>

Copy after login

包括表属性改变，列属性改变，完整性约束改变。

1.表属性改变

1.1重命名表名

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

Copy after login

也可以直接使用rename命令改名：

mysql> rename table s to student;Query OK, 0 rows affected (0.13 sec)

Copy after login

1.2表排序改变

mysql> alter table student order by stu_id desc;mysql> select * from student;+--------+----------+---------+-----------+| stu_id | stu_name | stu_tel | stu_score |+--------+----------+---------+-----------+|      4 | d        |     154 |        63 ||      3 | c        |     153 |        62 ||      2 | b        |     152 |        61 ||      1 | a        |     151 |        60 |+--------+----------+---------+-----------+

Copy after login

2列属性改变

2.1 添加列

mysql> alter table student    -> add sex char(1) after stu_name;Query OK, 4 rows affected (0.34 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> select * from student;+--------+----------+------+---------+-----------+| stu_id | stu_name | sex  | stu_tel | stu_score |+--------+----------+------+---------+-----------+|      1 | a        | NULL |     151 |        60 ||      2 | b        | NULL |     152 |        61 ||      3 | c        | NULL |     153 |        62 ||      4 | d        | NULL |     154 |        63 |+--------+----------+------+---------+-----------+4 rows in set (0.02 sec)

Copy after login

新添加的列默认放在最后一列，且默认填充空值。这里使用after指定了新增列sex放在stu_name后面.如果新增列设置不能为空，那么mysql将根据列的数据类型填入实际的值：对于数值填入0，对于字符串填入空字符串，对于日期填入0000-00-00，对于时间填入00:00:00.

2.2删除列

mysql> alter table student    -> drop sex;Query OK, 4 rows affected (0.33 sec)Records: 4  Duplicates: 0  Warnings: 0

Copy after login

2.3修改列属性

初始列属性：

+-------------+-----------+| column_name | data_type |+-------------+-----------+| stu_id      | int       || stu_name    | varchar   || stu_tel     | int       || stu_score   | int       |+-------------+-----------+

Copy after login

将stu_tel列修改为tel char型，并放在stu_score后面。

mysql> alter table student    -> change stu_tel tel char(3) after stu_score;;Query OK, 4 rows affected (0.23 sec)+-------------+-----------+| column_name | data_type |+-------------+-----------+| stu_id      | int       || stu_name    | varchar   || stu_score   | int       || tel         | char      |+-------------+-----------+

Copy after login

如果仅仅是更改列其他属性而不改列名时，可以使用modify参数。
将tel列改回int型

mysql> alter table student    -> modify tel int(3);Query OK, 4 rows affected (0.25 sec)Records: 4  Duplicates: 0  Warnings: 0+-------------+-----------+| column_name | data_type |+-------------+-----------+| stu_id      | int       || stu_name    | varchar   || stu_score   | int       || tel         | int       |+-------------+-----------+

Copy after login

3.完整性约束改变

<integrity constraint change>:=add primary key<index name>add unique <index name>add foreign key <index name>(column list) referencing <specification>add check <condition>drop primary keydrop foreign key <index name>drop constraint <constraint name>

Copy after login

在add后面可以添加[constraint ] 为完整性约束定义名字。
删除主键：

mysql> alter table student drop primary key;

Copy after login

添加主键：

mysql> alter table student add primary key(stu_id);

Copy after login

bitsCN.com

Related labels：

mysql

source：php.cn

Previous article：MySQL函数大全_MySQL Next article：MySQL在x64系统上1067问题解决_MySQL

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

mysql修改表_MySQL

1.表属性改变

1.1重命名表名

1.2表排序改变

2列属性改变

2.1 添加列

2.2删除列

2.3修改列属性

3.完整性约束改变