首页 computer-science 数据库系统原理实践MySQL

# 修改表名

# 相关知识

修改表，包括更改表的名称，删除表中的列、约束，为表添加新的列、约束，修改表中列的名称、数据类型和约束等操作，均通过 Alter Table 语句来实现。

# ALTER TABLE 语句

Alter Table 语句用于修改由 Create Table 语句创建的表的结构。比如，添加或删除列，添加或删除约束，创建或销毁索引，更改列的数据类型，更改列名甚至表名等。

ALTER TABLE 的完整语法较复杂，这里仅介绍简化后的语法：

ALTER TABLE 表名
[修改事项 [, 修改事项] ...]

可见，在一条 ALTER TABLE 语句里，可以同时对表作多项修改。可选的修改事项有很多，常用的有：

修改事项 ::=
    ADD [COLUMN] 列名 数据类型 [列约束]
       [FIRST | AFTER col_name]
  | ADD {INDEX|KEY} [索引名] [类型] (列1,...) 
  | ADD [CONSTRAINT [约束名]] 主码约束
  | ADD [CONSTRAINT [约束名]] UNIQUE约束
  | ADD [CONSTRAINT [约束名]] 外码约束
  | ADD [CONSTRAINT [约束名]] CHECK约束
  | DROP {CHECK|CONSTRAINT} 约束名
  | ALTER [COLUMN] 列名 {SET DEFAULT {常量 | (表达式)} | DROP DEFAULT}
  | CHANGE [COLUMN] 列名 新列名 数据类型 [列约束]
        [FIRST | AFTER col_name]
  | DROP [COLUMN] 列名
  | DROP {INDEX|KEY} 索引名
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | MODIFY [COLUMN] 列名 数据类型 [列约束]
        [FIRST | AFTER col_name]
  | RENAME COLUMN 列名 TO 新列名
  | RENAME {INDEX|KEY} 索引名 TO 新索引名
  | RENAME [TO|AS] 新表名

归纳一下，修改事项主要有：

  • 用 ADD 关键词添加列和约束 (主码、外码、CHECK、UNIQUE 等约束)；
  • 用 DROP 关键词删除列、约束和索引 (含 Unique)；
  • 用 MODIFY 关键词修改列的定义 (数据类型和约束)；
  • 用 RENAME 关键词修改列、索引和表的名称；
  • 用 CHANGE 关键词修改列的名称，同时还可以修改其定义 (类型和约束)。

需要说明的是：

  1. 注意 RENAME,MODIFY 和 CHANGE 的区别：仅改列名，用 RENAME; 只改数据类型不改名，用 MODIFY; 既改名又改数据类型，用 CHANGE。
  2. 在用 MODIFY,CHANGE 更改列的数据类型和约束时，修改后的 CHECK 约束并不会生效 (MySQL 只作语法检查，并未实现代码 -- 至少 MySQL 8.0.22 还未实现)。但用 ADD 新增列的 CHECK 约束，是有效的。另外，用 ADD 新增的 CHECK 约束，也会生效。
  3. 删除主码约束只能用 Drop Primary Key 短语，不能使用 drop constraint 短语，即便在创建主码约束时显式命名了该主码约束。试图使用 “drop constraint 主码约束名” 短语删除主码，会给出错误提示，显示该约束并不存在。因为 MySQL 并没有完全实现 “constraint 约束名 primary key (...)” 短语的功能，仅作了语法检查，然后直接忽略了主码约束的命名。
  4. 给已有列增加 Default 约束，可用 “alter 列 set default ...” 短语；删除列的 default 约束，可用 “alter 列 drop default” 短语。当然，也可以用 “Modify 列名 数据类型 ...” 短语。如果该短语没有 default 约束，就相当于删除了原来的 default 约束，如果该短语带有 default 约束，就相当于添加了 default 约束，如果之前已有 default 约束，则新的 Default 约束将代替原有的 Default 约束；
  5. 删除 unique 约束，既可用 “drop constraint 约束名” 短语，也可以用 “drop key 索引名” 短语来实现，唯一性 (unique) 约束实际是用 Unique 索引来实现的，Unique 索引的名字总是与 Unique 约束名完全一样，它们本就是同一套机制。如果没有显式命名的话，Unqiue 索引名或者说 Unique 约束名一般与列同名 (组合属性作索引，则与组合属性中的第 1 列同名)。但要注意是的，在更改列名后，Unique 索引名并不会随之更改。在创建 Unqiue 约束时，用 “constriant” 短语给约束取一个有意义的名字，是一个值得推荐的习惯。

# 如何更改表名

alter table 表名 rename [TO|AS] 新表名

# 添加与删除字段

# 如何给表添加字段

随着业务发展的需要，可能需要在原有表结构的基础上添加新的字段，由于建表时的疏忽，遗漏了某个字段，也需要将遗漏的列添加到表结构中。给表添加字段的语法是：

ALTER TABLE 表名 ADD [COLUMN] 列名 数据类型 [列约束]  [FIRST | AFTER 列名]

在 alter table 语句中指定表名，接着用关键字 add column 申明要添加的列，包括列的名称，数据类型，以及可选的列约束。列约束可以是主码约束、外码约束、CHECK 约束、Default 约束、Unique 约束等其中的任何一个或一组约束。这些约束在之前的实验中都介绍过。最后，还可以指定新添加的列在表中的位置：

  • 关键字 FIRST 指示新添加的列为第 1 列；
  • AFTER 指示新添加的列紧跟在指定列的后面。

如果省略位置指示，则新添加的列将成为表的最后一列。
关键字 column 可以省略。

示例：
学生表 student 的结构如下：

1667730698876

用以下语句创建了表 student:

create table student(
  sno char(10) primary key,
  sname varchar(32) not null,
  sex char(2),
  age int
);

你可能已经注意到了，这条建表语句遗漏了列 sex 的约束，这个问题我们将在后面的实验中再解决，这里先考虑其它事情：

由于业务的发展，产生了新的需求，需要为该表添加一个字段，用于存储学生的手机号码：

1667730767068

怎么将列 mobile（以及对该列的完整性约束）添加到表 student 中呢？执行下面的语句即可：

alter table student 
add mobile char(11) constraint CK_student_mobile check(mobile rlike '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

这个 check 约束中的 rlike 还可以用 regexp 替代，它们是同义语。跟 Oracle 一样，MySQL 用正则匹配表达式来测试字段值是否符合某个 pattern，rlike 比 like 关键词所支持的功能要强大得多。

成功添加 mobile 列后，可以试着执行以下几条语句观察效果：

insert into student values('1','ZHAO','男',18,'13907106666');

这条语句将插入一条数据到表 student 中；

再尝试执行：

insert into student values('2','LI','女',18,'23907106666');

这一次，系统将输出报错信息：
ERROR 3819 (HY000): Check constraint 'CK_student_mobile' is violated.

这条错误信息提醒我们：插入的数据违反了 CK_student_mobile 这条约束规则 (手机号码第 1 位必须是 1)。
你也可以试着把手机号码的第 1 位改为 1，中间的某个数字换成字母，然后再试着插入，同样会触发错误。

# 如何删除表中的字段

删除字段，即从表中将某个列移出，其语法格式为：

ALTER TABLE 表名 DROP [COLUMN] 列名

关键字 COLUMN 可以省略。

示例：
回到上面的例子（表 student)，假设在实际运行过程中，有人对数据库的设计提出了质疑：在学生档案里记录年龄的作法并不科学，因为年龄会随着时间的变化而变化，档案里记录 17 岁，还得根据当年记录的日期以及当下的日期推算实际年龄。替代方案是记录出生日期而不是年龄:

1667731242297

有两个方案实现这个改动：

  1. 用 alter table student change age DOB date 直接改变列名和类数据类型；
  2. 先用 alter table sudent add DOB date 添加列 DOB，再用 alter table student drop age 删除列 age。

如果表 student 是空表，则上述两个方案都是可行的。但在实际运行过程中，表 student 可能已经存储了数据，直接将一个 int 型的列改成 date 型的列，将出现类型不匹配错误。

另一方面，如果简单粗暴地删除列 age，同时添加 DOB 列，则原来存储的年龄信息都将丢失。

最好的方案是先添加列 DOB，然后根据当年记录时的年龄推算其大致的出生日期（相当于等价地保留了年龄数据）。在作完这个数据转换工作后，再剔除列 age。这个工作共分三步：

第 1 步，添加列 DOB:

alter table student add DOB date after sex;

列 DOB 将位于 sex 之后，age 之前。

第 2 步，根据 age 推算 DOB:
由于表 student 没有记录登记年龄的日期（实际系统总会记录学生是哪个年级的，学号中也往往包含入学年份信息），这里我们假设学生是 2020 年 9 月 1 日入学的：

update student set DOB = date_add('2020-9-1', interval -1*age year);

（注：date_add () 是 MySQL 的函数。）

执行完这条 update 语句后，可以执行下面这条语句查看表 student 的内容：

select * from student;

你将看到：

1667731557209

DOB 列成功地转录了 age 的信息（以另一种表现形式）。

第 3 步，删除列 age:

alter table student drop age;

# 修改字段

# 相关知识

1.ALTER TABLE 语句的用法；
2. 如何修改字段的名称；
3. 如何修改字段的数据类型和约束；
4. 如何修改字段在表中的位置。

# Alter Table 语句

ALTER TABLE 表名
[修改事项 [, 修改事项] ...]

与修改列名、列数据类型和列约束，以及列序的修改事项有：

修改事项 ::=
   ALTER [COLUMN] 列名 {SET DEFAULT {常量 | (表达式)} | DROP DEFAULT}
  | CHANGE [COLUMN] 列名 新列名 数据类型 [列约束]
        [FIRST | AFTER col_name]
  | MODIFY [COLUMN] 列名 数据类型 [列约束]
        [FIRST | AFTER col_name]
  | RENAME COLUMN 列名 TO 新列名

其中 CHANGE 短语可修改列名、数据类型和列约束；MODIFY 短语可修改列的数据类型和约束；RENAME 短语仅用于更改列名；ALTER 短语仅用于修改列的 DEFAULT 约束或删除列的 DEFAULT 约束。CHANGE 和 MODIFY 短语还可以修改列在表中的位置。

# 如何修改字段的名称

如果只需要修改列的名称，显然用 RENAME 短语最简单:

ALTER TABLE 表名 RENAME COLUMN 列名 TO 新列名

注意：关键字 COLUMN 不能省略。

如果修改列名的同时，还要修改列的类型和约束 (相当于删除之前的列，再插入一个全新的列，且插入的位置还可以随意指定)，则用 CHANGE 短语：

ALTER TABLE 表名 CHANGE [COLUMN] 列名 新列名 数据类型 [列约束] [FIRST | AFTER col_name]

关键字 column 可以省略，列约束和列位置都是可选的。如果新列带有 CHECK 约束的话，MySQL 只会对这个约束作语法检查，并不会去实现这个约束，其它类型的约束没有问题。如果真有这样的需求，不如先 DROP 之前的列，再 ADD 新的列，新列附带的 CHECK 约束是会被实现的。

# 如何修改字段的数据类型和约束

如果列名称不变，仅需要修改其数据类型和约束，则用 MODIFY 短语：

ALTER TABLE 表名 MODIFY [COLUMN] 列名 数据类型 [列约束]  [FIRST | AFTER col_name]

在修改数据类型和约束的同时，还可以改变列在表中的位置。
注意，一旦使用 MODIFY 短语修改列，则该列之前的数据类型、约束将被新的数据类型和约束取而代之。如果之前定义了列约束，修改后不带列约束，相当于删除了之前的约束。

如果需要修改 (或添加) 列的 DEFAULT 约束，则既可用上面的 MODIFY 短语，也可以使用 ALTER 短语：

ALTER TABLE 表名 ALTER [COLUMN] 列名 SET DEFAULT {常量 | (表达式)}

删除列的 DEFAULT 约束，则可以使用 ALTER 短语 (或 MODIFY 短语)：

ALTER TABLE 表名 ALTER [COLUMN] 列名 DROP DEFAULT

# 如何修改字段在表中的位置

如果仅需修改列在表中的位置，仍用 MODIFY 短语：

ALTER TABLE 表名 MODIFY [COLUMN] 列名 数据类型 [列约束]  [FIRST | AFTER col_name]

需要把列名、数据类型和约束完整地重述一遍，并在其后带上位置指示短语：FIRST 或 AFTER 某个列。注意：如果数据类型和约束重述的跟之前的不一样，则相当于修改了这个列，如果重述的列名跟之前的不一样，则会抛出错误信息（列不存在）。

示例

居民登记表 resident 的结构如下

1667732658484

表中，居民身份证号 idNo 为我国于 1984 年开始使用的第 1 代身份证号，只有 15 位编码 (其中 7、8 两位为出生年份)。1999 年，居民身份证编号由 15 位升至 18 位：年份用 4 位表达，并在末尾增加了 1 位校验码。现在需要对表 resident 作如下修改：

  1. 将身份证号 idNo 升级到 18 位；
  2. 身高 height 的类型改为无符号整数；
  3. 列名 educationalBackground 改为更简洁的 education。

可以用三条 alter table 语句分别实现上述需求，当然，更便捷的方法是用一条语句:

alter table resident modify idNo char(18),
       modify height int unsigned,
       rename column educationalBackground to education;

# 添加、删除与修改约束

# 相关知识

  1. 如何删除和添加主码约束；
  2. 如何删除和添加外码约束；
  3. 如何删除和添加 CHECK 约束；
  4. 如何删除和添加 UNIQUE 约束。

# Alter table 语句与约束

Alter Table 语句与修改约束有关的部分：

ALTER TABLE 表名
[修改事项 [, 修改事项] ...]

删除与添加约束的修改事项有：

修改事项 ::=
  | ADD [CONSTRAINT [约束名]] 主码约束
  | ADD [CONSTRAINT [约束名]] UNIQUE约束
  | ADD [CONSTRAINT [约束名]] 外码约束
  | ADD [CONSTRAINT [约束名]] CHECK约束
  | DROP {CHECK|CONSTRAINT} 约束名
  | DROP {INDEX|KEY} 索引名
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol

可见，删除约束主要通过 alter table 语句的 drop 短语，添加约束则通过 alter table 语句的 add 短语来实现。

# 主码的删除与添加

删除主码：

ALTER TABLE 表名 DROP PRIMARY KEY;

或者：

drop index `PRIMARY` on 表名;

添加主码：

ALTER TABLE 表名 ADD [CONSTRAINT [约束名]] PRIMARY KEY(列1,列2,...);

创建主码时，MySQL 将创建主码索引；删除主码，即意味着删除主码索引。反过来，删除主码索引，也意味着删除了主码约束。

迄今为止，MySQL 尽管在语法上支持主码约束的命名，但实际上并没有真正实现主码约束的命名功能。即，MySQL 并不会创建用户语句中所指定的约束名。所以，试图通过约束名删除主码约束是行不通的。

MySQL 中，所有的主码约束 (主码索引) 名均为 PRIMARY，无论怎么命名或更命，这个名字都不会改变。由于 PRIMARY 是 MySQL 的保留字，所以，在引用这个主码约束 (索引) 名时，必须用一对 `` 符号将 PRIMARY 括起来。

# 主码约束举例

例如，表 score 用于记录学生选修课程的成绩，其结构如下：

1667733492375

组合属性 (sno,cno) 是该表的主码。由于疏忽，用以下语句创建了该表：

create table score(
     sno char(10),
     cno char(10) not null,
    grade int,
    constraint PK_score primary key(sno)
);

显然，主码定义错了，应当删除重建：

第 1 步：删除错误的主码定义：

alter table score drop primary key;

第 2 步：重新创建主码：

alter table score add constraint PK_score primary key(sno,cno);

或者简单地写成：

alter table score add primary key(sno,cno);

以上两种写法的结果是一样的。因为 MySQL 会无视主码约束的命名短语 (仅作语法检查)，不会真正给主码约束命名。

当然，上述删除并重建主码的工作可用一条语句完成：

alter table score drop primary key, add primary key(sno,cno);

注意多个修改项之间用逗号分隔。

# 外码的删除与添加

alter table 语句提供了两个短语用来删除外码约束：

ALTER TABLE 表名 DROP CONSTRAINT 约束名

或:

ALTER TABLE 表名 DROP FOREIGN KEY 约束名

删除外码约束，必须显式给出外码约束的名字。

添加外码约束：

ALTER TABLE 表名 ADD [CONSTRAINT [约束名]] 外码约束

约束名是可选的，如果省略命名短语，MySQL 将按一定的规则自动命名。将来如果要删除该约束，必须先查询到该约束的名字（注：从 MySQL 的数据字典查询）。

创建外码时，MySQL 将同步创建外码索引，如果外码约束有显式命名，则外码索引与外码约束同名。如果外码约束未命名，则外码索引与外码列的列名同名。

删除外码约束时，外码索引不会跟着删除。如果将来重新创建了外码，并显式命名，则外码索引会自动更名 (与外码约束名保持相同)。

# Check 约束的删除与添加

删除 check 约束：

ALTER TABLE 表名 DROP CONSTRAINT 约束名

添加 check 约束：

ALTER TABLE 表名 ADD [CONSTRAINT [约束名]] check(条件表达式)

同样地，如果未显式命名 check 约束，MySQL 将按一定规则自动予以命名。
添加约束时，如果现有数据与该约束规则相矛盾，则创建约束的请求会被拒绝。

# Unique 约束的删除与添加

删除 Unique 约束：

alter table 表名 drop constraint 约束名;

或者：

drop index 索引名 on 表名;

添加 Unique 约束：

alter table 表名 ADD [CONSTRAINT [约束名]] UNIQUE(列1,...)

创建 unique 约束时，将同步创建 unique 索引，索引名与约束同名。如果未显式命名 unique 约束或索引，MySQL 将按一定规则自动命名（单列的 unique 索引或约束与列同名）。

是故，删除 unique 索引，等同于删除 unique 约束。反过来，删除 unique 约束，也等同于删除了 unique 索引。

# 举例

现在院系表 dept 和学生表 student，两表结构分别如下：

院系表:dept

1667734201259

学生表：student

1667734238582

除了两个主码和一个外码以下，表 student 还存在其它约束规则：

  • 手机号码是唯一的，不允许重复；
  • 手机号码第 1 位必须是 1；
  • 姓名是唯一的，不允许同名。

建表语句如下：

create table dept(
   deptNo char(3) primary key,
     deptName char(50),
     addr varchar(100)
)
create table  student(
  sno char(10) primary key,
    sname char(30) unique,
    mobile char(11),
    dept char(3),
    constraint UN_student_mobile unique(mobile),
    constraint FK_student_1 foreign key (dept) references dept(deptNo),
    constraint CK_student_mobile check(mobile like '1%')
);
显然，sname 列上的 unique 约束未命名，而 mobile 列上的 unique 约束则给予了显式命名。
现在对表 student 的约束作如下调整：
  1. 列 sname 上的 unique 约束不合理，删除该约束；
  2. 列 dept 上的外码约束名改为：FK_student_dept；
  3. 列 mobile 上的 check 约束改为：电话号码第 1 位为 1，接下来 10 位为 0-9 之间的数字。
约束的修改一般通过先删除旧约束再重建新约束来实现。
(1) 列 sname 上的 unique 未命名，其约束名与列名相同。可以通过以下语句删除该约束：
alter table student drop constraint sname;\
或
drop index sname on student;
(2) 为列 dept 上的外码约束改名：
alter table student drop constraint FK_student_1;
alter talbe student add constraint FK_student_dept  foreign key (dept) references dept(deptNo);
(3) 重新定义对 mobile 列的 check 约束：
alter table student drop constraint CK_student_mobile;
alter table student add constraint CK_student_mobile check(mobile regexp '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')