MySQL修改、删除表的结构

柔光的暖阳◎ 2022-11-17 14:52 345阅读 0赞

## MySQL修改、删除表的结构 ##

1、增加一列

alter table student add score double(3,1);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70][]

说明

double（3,1）指数据是三位，小数部分占一位，整数部分占两位，默认最后一列

alter table student add score double (5,2) first

在第一列插入score

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 1][]

alter table student add score double(5,2) after enterdate;

在enterdate后插入

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 2][]

3、删除一列

alter table student drop score;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 3][]

4、修改一列，列名不变

alter table student modify score float(2,1);

update student set score = 3.45 where sno =2 ；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 4][]

修改一列，列名改变

alter table student change score score2 double(3,1);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 5][]

5、修改表名

alter table student rename to stu;

![20210410172325374.png][]

6、查看表的结构

desc student；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 6][]

7、删除数据库表

drop table student；

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70]: /images/20221022/76a001f0d28f4c5abdb54da60e051787.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 1]: /images/20221022/52cef2ab071e46afbf2eb71173e27bbe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 2]: /images/20221022/ff64efbca9f34bb3bf08c5bc5cd03040.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 3]: /images/20221022/0c3a657c3cb046d39f14cd4c113c313f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 4]: /images/20221022/573cdca733574b909cb98d614787e768.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 5]: /images/20221022/de8631cee3dc46168af06efccc01e529.png
[20210410172325374.png]: /images/20221022/71544a28130c41d1bafe64c45bb41690.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 6]: /images/20221022/7e3d86c5662849669b58d6186af38559.png