编辑数据表结构本节只介绍开发中最常用的部分,更多内容,请参考MySQL手册
基本语法:ALTER TABLE 表名 [ADD ...] [DROP ... ] [CHANGE ...] [ RENAME ...]
语句看上去并不复杂,下面我用一个个实例演示用法
1、添加字段语法:ALTER TABLE 表名 ADD 新字段名 字段类型 约束条件 [AFTER 字段名] [FIRST]FIRST :添加到表中第一个字段之前!
默认情况下,新添加的字段做为表的最后一个字段。实例:tp5_staff表sex字段后添加新字段:age(年龄),要求整型、无符号、不为空、默认值为22;
语句如下:#添加新字段:age(年龄),要求整型、无符号、不为空、默认值为22,加到sex字段后面
ALTER TABLE `tp5_staff` ADD age TINYINT(3) UNSIGNED NOT NULL DEFAULT '22' AFTER sex;
#查看增加age字段后的表结构
DESC `tp5_staff`;运行结果:
2、修改字段名称语法:ALTER TABLE 表名 CHANGE 原字段名 新字段名 原字段类型 原约束条件 ;1、哪怕仅仅是修改字段名称,新字段的类型也必须给出;
2、如果仅仅修改字段名称,那么最好在修改之前,查询一下原表要修改的字段类型和约束条件(DESC 表名),防止原来存储的数据因类型不对而出错;实例:将tp5_staff表sex字段修改为xb,其它内容不变;
语句如下:#添加新字段:age(年龄),要求整型、无符号、不为空、默认值为22,加到sex字段后面
ALTER TABLE `tp5_staff` CHANGE sex xb TINYINT(2) UNSIGNED NOT NULL DEFAULT 1;
#查看增加age字段后的表结构
DESC `tp5_staff`;运行结果如下:
3、修改字段名称和类型其实与上面的语法基本是一样的。
1、如果仅修改类型,只要将新字段名,设置与原字段同名即可。
2、新字段后面要写新的类型和约束条件等,不要复制原来字段的内容。语法:ALTER TABLE 表名 CHANGE 原字段名 新字段名 新字段类型 新约束条件 ;实例:tp5_staff表中,salary字段变更为gz,类型修改成INT(8),无符号、不为空;
语句:#将salary字段变更为gz,类型变更为INT(8),无符号,不为空
ALTER TABLE `tp5_staff` CHANGE salary gz INT(8) UNSIGNED NOT NULL ;
#查看字段变更后的表结构
DESC `tp5_staff`;运行结果:
如果想修改数据表的当前主键,并将主键起始值设置为指定值,可这样操作:# 修改一下表结构,设置主键和约束条件
ALTER TABLE 表名 CHANGE id id INT(4) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT ;
#设置自增起始于1001
ALTER TABLE 表名 AUTO_INCREMENT = 自增主键起始值 ;
4、删除字段删除字段是不可撤消的操作,所以删除字段之前,请确保该字段值已无效语法:(非常简单)ALTER TABLE 表名 DROP 要删除的字段名 ;实例:删除tp5_staff表中的job 字段为不了破坏原数据,我们新建一个字段来演示删除操作语句:# 先添加一个job字段
ALTER TABLE `tp5_staff` ADD job CHAR(20) NOT NULL ;
#查看是否添加成功
DESC `tp5_staff`;
#删除刚添加的job字段
ALTER TABLE `tp5_staff` DROP job;
#查看是否删除成功
DESC `tp5_staff`;运行结果:
5、数据表更名语法:数据表更名有二种方法,用哪个都可以完成#语法一:
ALTER TABLE 原表名 RENAME 新表名 ;
# 语法二:
RENAME TABLE 原表名 TO 新表名 ;语句#语法一:
ALTER TABLE `tp5_staff` RENAME tp_user;
# 语法二:
RENAME TABLE tp5_staff TO tp_user;运行结果:
6、添加索引可以同时给数据表添加多个索引,字段一旦设置了索引,其值将按顺序排列,可以极大的加快查询速度,特别是大量数据读取时,速度提升明显。
所以,对于查询频繁的字段,创建索引是非常有必要的。语法:ALTER TABLE 表名 ADD INDEX (字段名) [ , ADD INDEX (字段名) ];实例:给tp5_staff表中的name字段,salary 字段添加索引
语句:# 在name字段,salary字段创建索引,加快查询速度
ALTER TABLE `tp5_staff` ADD INDEX (name), ADD INDEX (salary);
#经测试,salary字段经过索引后,已按升序输出
SELECT salary FROM `tp5_staff`;运行效果:
7、其它数据表编辑操作有些数据表编辑操作使用频率不高,做为了解即可如果仅仅修改字段类型,除了可以用CHANGE关键字后,还可以用MODIFY;
修改数据表的存储引擎#修改数据表引擎
ALTER TABLE 表名 ENGINE = MyISAM 或者 InnoDB修改数据表默认字符集#修改数据表默认字符集
ALTER TABLE 表名 DEFAULT CHARSET = 编码集实例:将tp_staff 表存储引擎变更成:InnoDB
语句:#查看变更之前的引擎状态
show table status from tp5 where name= 'tp5_staff';
#变更引擎
alter table `tp5_staff` engine=InnoDB;
#查看变更之后的引擎
show table status from tp5 where name='tp5_staff';运行效果如下:
8、设置或删除字段默认值
掌握语句中ALTER关键字的用法一般来说,如果字段有默认值,在创建数据表时,就设置好了。但凡事总有例外,当你发现原来的默认值已不符合需求,或者新加的字段忘记了设置默认值时,可以用这个语句进行单独设置。语法#设置默认值
ALTER TABLE 表名 ALTER 字段名 SET DEFAULT 默认值;
#删除默认值
ALTER TABLE 表名 ALTER 字段名 DROP DEFAULT 默认值;实例:将salary 字段默认值设置为5000
语句:#设置salary字段默认值为5000
ALTER TABLE `tp5_staff` ALTER salary SET DEFAULT 5000;
#新增记录,没有设置salary,则salary 取默认值 5000
INSERT `tp5_staff` (name,sex) VALUES ('老顽童',1);
#查询全部记录
SELECT * FROM `tp5_staff` LIMIT 100;运行效果:
下面再演示一下删除salary默认值
语句如下:#将salary字段默认值删除
ALTER TABLE `tp5_staff` ALTER salary DROP DEFAULT ;
#新增记录,没有设置salary
INSERT `tp5_staff` (name,sex) VALUES ('欧阳峰',1);
#查询全部记录
SELECT * FROM `tp5_staff` LIMIT 100;运行效果
删除数值型字段默认值后,系统自动设置为:0
总结数据表结构修改是非常重要的,因为我们在开发过程中,很难将数据表设计的一步到位,随着项目开发进度,用户需求的变化,数据表变更再所难免。
所以,这项技能,必须掌握!