MYSQL数据库表结构修改

1.表名的修改

语法如下：

ALTER TABLE 表名

RENAME TO 新表名

注意：修改表名还可以执行命令：RENAME TABLE 表名 TO 新表名；

例：将mybatis数据库中Register名改为bbs_Register。

代码如下：

ALTER TABLE Register

RENAME TO bbs_Register;
 

或

RENAME TABLE RegisterTO bbs_Register;

2.字段的修改

（1）增加字段

语法如下：

ALTER TABLE 表名

ADD [COLUMN] 字段名 数据类型 [列级约束] [FIRST|AFTER 字段名]

注意:ADD指向表中添加字段。

字段添加包括三部分：字段名、数据类型和约束，语法和字段定义相同。

（字段定义可查看——MYSQL数据库表的创建（定义字段））

FIRST——将新字段添加到第一列。

AFTER——将新字段添加到指定字段之后。

例：在数据库mybatis的Register中添加自增字段RId,数据类型为整型，取值不重复，并添加到第一列。代码如下：

ALTER TABLE Register
ADD RId INT AUTO_INCREMENT UNIQUE FIRST;

（2）删除字段

语法如下：

ALTER TABLE 表名

DROP [COLUMN] 字段名 

例：删除在数据库mybatis的Register的字段RId。

代码如下：

ALTER TABLE Register
DROP RId ;

（3）修改字段名和数据类型

1）修改字段名和数据类型属于表结构的修改，语法如下   ：

ALTER TABLE 表名

CHANGE [COLUMN] 原字段名 新字段名 新数据类型

注意：关键词CHANGE用于同时修改字段名和数据类型，也可以用来单独修改字段名或者单独修改数据类型。

2）只修改字段名时，字段类型不变，为原来的数据类型，语法如下：

 CHANGE [COLUMN] 原字段名 新字段名 原数据类型

3）只修改数据类型时，字段名不变，语法如下 ：

 CHANGE [COLUMN] 原字段名 原字段名 新数据类型

4）修改字段的数据类型除了CHANGE 关键词可以实现，MODIFY关键字也可以实现，语法如下：

ALTER TABLE 表名

MODIFY [COLUMN] 字段名  新数据类型

注意：MODIFY 关键字仅对字段的数据类型进行修改。

（4）修改字段排列位置

1）表中各列位置是可以调整的，修改字段的排列位置属于表结构的修改，语法如下：

ALTER TABLE 表名

CHANGE [COLUMN] 字段名1 字段名1  数据类型 FIRST|AFTER 字段名2

注意：两个字段名1表示不修改原字段名。

FIRST——表示将字段名1对应的字段调整到第一列。

AFTER——字段名2表示将原字段名1对应的字段移动到字段名2对应字段之后。

2）修改字段的排列位置也可以用modify语法如下：

ALTER TABLE 表名

MODIFY [COLUMN] 字段名1   数据类型 FIRST|AFTER 字段名2

例：将mybatis数据库中user表的Score字段放在RDate字段之后。

代码如下：

ALTER TABLE user

CHANGE  Score Score  int AFTER RDate;

3.约束的修改

(1)删除约束

1)删除主码约束

语法如下：

ALTER TABLE 表名

DROP PRIMARY KEY;

例：删除mybatis数据库Register(注册表)中的主码约束。

代码如下：

ALTER TABLE Register

DROP PRIMARY KEY;

2)删除外码约束

(外码一旦删除，就会解除父表和子表间的父子关系）

语法如下：

ALTER TABLE 表名 DROP FOREIGN KEY 外码约束名;

例：删除mybatis数据库Register(注册表)中的两个外码约束。

代码如下：

ALTER TABLE Register
DROP FOREIGN KEY fk_CId;
ALTER TABLE Register
DROP FOREIGN KEY fk_SId;

3)删除唯一约束

语法如下：

ALTER TABLE 表名

DRO INDEX 唯一约束名;

例：删除mybatis数据库Department（系部表）中的唯一性约束。

代码如下：

ALTER TABLE Department
DROP INDEXD Name;

Department（系部表）中唯一性约束的名称是系统分配的，而删除唯一性约束需要指定约束名。

查看表中约束名方式:

1.使用MYSQL命令：

SHOW CREATE TABLEDepartment;

2.使用SQL语句：

SELECT constraint_name , constraint_type

FROM information_schema.table_constraints

WHERE table_schema='mybatis' AND table_Name ='Department';

4)删除默认值约束

语法如下：

ALTER TABLE 表名

CHANGE [COLUMN] 原字段名 原字段名  原数据类型 DEFAULT NULL;

或（使用modify语法）

ALTER TABLE 表名

MODIFY [COLUMN] 字段名   数据类型 DEFAULT NULL;

例：删除mybatis数据库Course(课程表)中,CLimit(限选人数)字段的默认值约束。

代码如下：

ALTER TABLE Course
CHANGE CLimit CLimit  int DEFAULT NULL;

5)删除非空约束

语法如下：

ALTER TABLE 表名

CHANGE [COLUMN] 原字段名 原字段名  原数据类型 NULL;

或

ALTER TABLE 表名

MODIFY [COLUMN] 字段名   数据类型  NULL;

例：删除mybatis数据库Course(课程表)中,CName(课程名)字段的非空约束。

代码如下：

ALTER TABLE Course
CHANGE CName CName int NULL;

(2)增加约束

1)添加主键约束

语法如下：

ALTER TABLE 表名

ADD PRIMARY KEY(列名)；

例：添加mybatis数据库Register(注册表)中的主码约束。

代码如下：

ALTER TABLE Register

ADD PRIMARY KEY(SId,CId);

2)添加外码约束

语法如下：

ALTER TABLE 表名 

ADD [CONSTRAINT 约束名] FOREIGN KEY (字段名) REFERENCES 父表 (主码字段名)

例：修改mybatis数据库Register(注册表)，将字段SId设置为外码，与数据表Student的主键SId进行关联，同时字段CId设置为外码，与数据表Course的主键CId进行关联。

代码如下：

ALTER TABLE Register
ADD FOREIGN KEY (SId) REFERENCES Student (SId);
ALTER TABLE Register
ADD CONSTRAINT fk_CId FOREIGN KEY (CId) REFERENCES Course (CId);

3)添加唯一约束

语法如下：

ALTER TABLE 表名 

ADD [CONSTRAINT 约束名] UNIQUE(列名);

例：删除mybatis数据库Department（系部表）,指定部门名唯一。

代码如下：

ALTER TABLE Department
ADD UNIQUE(DName);

4)添加默认值约束

语法如下：

ALTER TABLE 表名 

CHANGE [COLUMN] 原字段名 原字段名  原数据类型 DEFAULT 默认值;

或（使用modify语法）

ALTER TABLE 表名

MODIFY [COLUMN] 字段名   数据类型 DEFAULT 默认值;

5)添加非空约束

语法如下：

ALTER TABLE 表名 

CHANGE [COLUMN] 原字段名 原字段名  原数据类型 NOT NULL;

或（使用modify语法）

ALTER TABLE 表名

MODIFY [COLUMN] 字段名   数据类型  NOT NULL;

4.选项的修改

(1)修改存储引擎

语法如下：

ALTER TABLE 表名

ENGINE=新的存储引擎类型

(2)修改默认字符集

语法如下：

ALTER TABLE 表名

DEFAULT CHARSET=新的字符集

(3)修改索引关键字压缩方式

语法如下：

ALTER TABLE 表名

PACK_KEYS=新的压缩类型

(4)设置自增字段初始值

ALTER TABLE 表名

AUTO_INCREMFNT=新的初始值