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

修改表结构： alter [ignore] table <table specification> <table structure change>

<table structure change> 包括表属性改变，列属性改变，完整性约束改变。

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，对于时间填入0. 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.完整性约束改变 <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 key drop foreign key <index name> drop constraint <constraint name> 在add后面可以添加[constraint <constraint name>] 为完整性约束定义名字。 删除主键： mysql> alter table student drop primary key; 添加主键： mysql> alter table student add primary key(stu_id);

查看更多关于mysql修改表 - mysql数据库栏目 - 自学php的详细内容...