MySQL修改表名、添加列、删除列、修改列等等
(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>