MySQL修改表名、添加列、删除列、修改列等等

原创

mb61bc2ba16eece 2021-12-31 16:05:31 博主文章分类：MySQL ©著作权

©著作权归作者所有：来自51CTO博客作者mb61bc2ba16eece的原创作品，请联系作者获取转载授权，否则将追究法律责任

（1）修改表名

格式：RANAME TABLE 原表名 TO 新表名;

mysql> RENAME TABLE student TO student_info;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student_info   |
+----------------+
1 row in set (0.00 sec)

mysql>

（2）添加列

格式：ALTER TABLE 表名 ADD COLUMN 列名类型名;

mysql> ALTER TABLE student_info ADD COLUMN score int;
Query OK, 15 rows affected (0.12 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> desc
    -> student_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| score | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.05 sec)

mysql> select * from student_info;
+------+---------+-------+
| Id   | name    | score |
+------+---------+-------+
|   12 | Lili    |  NULL |
|   27 | Jack    |  NULL |
|   25 | Rose    |  NULL |
|   25 | Ann     |  NULL |
|   26 | Mark    |  NULL |
|   68 | Bill    |  NULL |
|   16 | James   |  NULL |
|   20 | Fred    |  NULL |
|    8 | Gary    |  NULL |
|   22 | Martin  |  NULL |
|   35 | Charles |  NULL |
|   31 | Joseph  |  NULL |
|   49 | Henry   |  NULL |
|   57 | Mike    |  NULL |
| NULL | Bob     |  NULL |
+------+---------+-------+
15 rows in set (0.00 sec)

mysql>

（3）删除列

格式：ALTER TABLE 表名 DROP 列名;

mysql> ALTER TABLE student_info DROP score;
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> select * from student_info;
+------+---------+
| Id   | name    |
+------+---------+
|   12 | Lili    |
|   27 | Jack    |
|   25 | Rose    |
|   25 | Ann     |
|   26 | Mark    |
|   68 | Bill    |
|   16 | James   |
|   20 | Fred    |
|    8 | Gary    |
|   22 | Martin  |
|   35 | Charles |
|   31 | Joseph  |
|   49 | Henry   |
|   57 | Mike    |
| NULL | Bob     |
+------+---------+
15 rows in set (0.00 sec)

mysql>

（4）修改列

格式：ALTER TABLE 表名 CHANGE COLUMN 原列名新列名类型名;

mysql> ALTER TABLE student_info CHANGE COLUMN Id age int;
Query OK, 15 rows affected (0.07 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> select * from student_info;
+------+---------+
| age  | name    |
+------+---------+
|   12 | Lili    |
|   27 | Jack    |
|   25 | Rose    |
|   25 | Ann     |
|   26 | Mark    |
|   68 | Bill    |
|   16 | James   |
|   20 | Fred    |
|    8 | Gary    |
|   22 | Martin  |
|   35 | Charles |
|   31 | Joseph  |
|   49 | Henry   |
|   57 | Mike    |
| NULL | Bob     |
+------+---------+
15 rows in set (0.00 sec)

mysql>

（5）修改列类型

格式：ALTER TABLE 表名 MODIFY 列名类型名;

mysql> ALTER TABLE student_info MODIFY age float;
Query OK, 15 rows affected (0.09 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> desc student_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| age   | float       | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql>