mysql修改表
mysql修改表
修改表结构：?
1 | alter [ignore] table <table specification> <table structure change> |
包括表属性改变，列属性改变，完整性约束改变。
1.表属性改变
1.1重命名表名
?
1 2 | mysql> alter table student rename to s; Query OK, 0 rows affected (0.03 sec) |
也可以直接使用rename命令改名：
?
1 2 | mysql> rename table s to student; Query OK, 0 rows affected (0.13 sec) |
1.2表排序改变
?
1 2 3 4 5 6 7 8 9 10 | 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 添加列
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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删除列
?
1 2 3 4 | mysql> alter table student -> drop sex; Query OK, 4 rows affected (0.33 sec) Records: 4 Duplicates: 0 Warnings: 0 |
2.3修改列属性
初始列属性：
?
1 2 3 4 5 6 7 8 | +-------------+-----------+ column_name | data_type | +-------------+-----------+ stu_id | int | stu_name | varchar | stu_tel | int | stu_score | int | +-------------+-----------+ |
将stu_tel列修改为tel char型，并放在stu_score后面。
?
1 2 3 4 5 6 7 8 9 10 11 | 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型
?
1 2 3 4 5 6 7 8 9 10 11 12 | 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.完整性约束改变
?
1 2 3 4 5 6 7 8 | <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 ] 为完整性约束定义名字。
删除主键：
?
1 | mysql> alter table student drop primary key; |
添加主键：
?
1 | mysql> alter table student add primary key(stu_id); |