MySQL 修改表的基本语法
在MySQL中使用ALTER TABLE
语句来改变原有表的结构。
一、添加列:
场景:
- 随着业务的变化,可能需要在已经存在的表中添加新的列。
语法:
ALTER TABLE [表名] ADD [列名称] <列类型> <列参数>;
1.添加方式:
1.将新的列添加到表的最后
:
# 首先简单的创建了user表, 有两个列 mobile 与name
mysql> create table user(mobile char(11), name char) charset utf8;
Query OK, 0 rows affected (0.05 sec)
# 看一下user表的数据类型
mysql> desc user;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| mobile | char(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
# 将user表中新添加一列,列名为birth 列类型为日期
mysql> ALTER TABLE user ADD birth date;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看当前表结构, 可以发现列名为生日的已经添加到user表中
mysql> desc user;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| mobile | char(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
2.将新的列加在指定列的后面
:
- 语法:
ALTER TABLE [表名] ADD [列名称] <列类型> <列参数> AFTER 指定的列
# 新添加列名为gender的列,将此列添加到 mobile列的后面
mysql> ALTER TABLE user ADD gender char(1) AFTER mobile;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看当前表的机构
mysql> desc user;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| mobile | char(11) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
3.将新的列后添加指定参数FIRST
:
- 语法:
ALTER TABLE [表名] ADD [列名称] <列类型> <列参数> FIRST
# 给user 表添加主键id, 并且将它称为在表中的第一列
mysql> ALTER TABLE user ADD id int primary key auto_increment FIRST;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看当前表结构
mysql> desc user;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| mobile | char(11) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
+--------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
总结:新添加的列,如果不指定列参数,默认将新增的列保存到表的最后
;如果要指定新增列在表中的位置,需要要使用AFTER列参数
;如果要新增的列存储表的最前面,需指定列参数FIRST
。
二、修改列:
场景:
- 比如上面栗子,想把性别不使用char来进行表示,想使用int来进行表示,而且这列的数据要保存,把这列的名称换一个。
语法:
- 修改列类型:
ALTER TABLE [表名] MODIFY [列名] <新类型> <新参数>;
- 修改列名及列类型:
ALTER TABLE [表名] CHANGE [旧列名] <新列名> <新类型> <新参数>;
2.修改方式:
1.修改列类型:
# 将user表中的gender列的类型进行改变
mysql> ALTER TABLE user MODIFY gender char(6) not null default "";
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看修改后表的结构
mysql> desc user;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| mobile | char(11) | YES | | NULL | |
| gender | char(6) | NO | | | |
| name | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
+--------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
2.修改列名及列类型:
# 将user表中的列名为id,修改成列名uid,并且指定int 类型,列参数为unsigned 不能为负数
mysql> ALTER TABLE user CHANGE id uid int unsigned;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看当前表结构
mysql> desc user;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| uid | int(10) unsigned | NO | PRI | NULL | |
| mobile | char(11) | YES | | NULL | |
| gender | char(6) | NO | | | |
| name | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
+--------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
三、删除列:
语法:
- 删除表中的列:
ALTER TABLE [表名] DROP [列名]
举个栗子:
# 将表中的birth 列进行删除
mysql> ALTER TABLE user DROP birth;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看删除列后的表结构
mysql> desc user;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| uid | int(10) unsigned | NO | PRI | NULL | |
| mobile | char(11) | YES | | NULL | |
| gender | char(6) | NO | | | |
| name | char(1) | YES | | NULL | |
+--------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
四、修改表名:
语法:
- 修改表名:
ALTER TABLE [旧表名称] RENAME TO [新表名称]
举个栗子:
# 将表名为user表的 更改表名为new_user
mysql> ALTER TABLE user RENAME TO new_user;
Query OK, 0 rows affected (0.02 sec)
# 查看当表数据库中的表
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| class |
| money |
| new_user | # 已经修改
| str |
| student |
+-------------------+
5 rows in set (0.00 sec)
五、小技巧:
建表时,如果不指定默认值,那么这个列就会为null,为什么不想要null的值?
答:不好比较,null是一种类型,比较时,只能专门的is null 或 is not null 来比较,碰到运算符,一律返回null,效率不高,影响提高索引效果,后面会详细说特殊类型null