php修改mysql数据库_mysql修改表 - mysql数据库栏目 - 自学php

修改表结构：

alter [ignore] table

1.表属性改变

1.1重命名表名

mysql> alter table student rename to s;

Query OK, 0 rows affected (0.03 sec)

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

mysql> rename table s to student;

Query OK, 0 rows affected (0.13 sec)

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 |

+--------+----------+---------+-----------+

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: 0

mysql> 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)

新添加的列默认放在最后一列，且默认填充空值。这里使用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

2.3修改列属性

初始列属性：

+-------------+-----------+

| column_name | data_type |

+-------------+-----------+

| stu_id | int |

| stu_name | varchar |

| stu_tel | int |

| stu_score | int |

+-------------+-----------+

将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 |

+-------------+-----------+

如果仅仅是更改列其他属性而不改列名时，可以使用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 |

+-------------+-----------+

3.完整性约束改变

:=

add primary key

add unique

add foreign key (column list) referencing

add check

drop primary key

drop foreign key

drop constraint

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

删除主键：

mysql> alter table student drop primary key;

添加主键：

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