第一关 修改表名
任务描述
本关任务:修改表的名称。
本实验将介绍Alter table语句的大部分功能和修改表结构(添加列、约束,删除列、约束,修改列)等基础知识,这些知识将在接下来的实验中排上用场。
相关知识
修改表,包括更改表的名称,删除表中的列、约束,为表添加新的列、约束,修改名中列的名称、数据类型和约束等操作,均通过Alter Table语句来实现。
为了完成本关任务,你需要掌握:
1.Alter Table语句及其作用;
2.如何更改表名。
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关键词修改列的名称,同时还可以修改其定义(类型和约束)。
需要说明的是:
- 注意RENAME,MODIFY和CHANGE的区别:仅改列名,用RENAME; 只改数据类型不改名,用MODIFY; 既改名又改数据类型,用CHANGE。
- 在用MODIFY,CHANGE更改列的数据类型和约束时,修改后的CHECK约束并不会生效(MySQL只作语法检查,并未实现代码--至少MySQL 8.0.22还未实现)。但用ADD新增列的CHECK约束,是有效的。另外,用ADD新增的CHECK约束,也会生效。
- 删除主码约束只能用Drop Primary Key短语,不能使用drop constraint短语,即便在创建主码约束时显式命名了该主码约束。试图使用“drop constraint 主码约束名”短语删除主码,会给出错误提示,显示该约束并不存在。因为MySQL并没有完全实现“constraint 约束名 primary key(...)”短语的功能,仅作了语法检查,然后直接忽略了主码约束的命名。
- 给已有列增加Default约束,可用“alter 列 set default ...”短语;删除列的default约束,可用“alter 列 drop default”短语。当然,也可以用“Modify 列名 数据类型 ...”短语。如果该短语没有default约束,就相当于删除了原来的default约束,如果该短语带有default约束,就相当于添加了default约束,如果之前已有default约束,则新的Default约束将代替原有的Default约束;
- 删除unique约束,既可用“drop constraint 约束名”短语,也可以用“drop key 索引名”短语来实现,唯一性(unique)约束实际是用Unique索引来实现的,Unique索引的名字总是与Unique约束名完全一样,它们本就是同一套机制。如果没有显式命名的话,Unqiue索引名或者说Unique约束名一般与列同名(组合属性作索引,则与组合属性中的第1列同名)。但要注意是的,在更改列名后,Unique索引名并不会随之更改。在创建Unqiue约束时,用“constriant”短语给约束取一个有意义的名字,是一个值得推荐的习惯。
如何更改表名
根据前面介绍的alter table语句的相关内容,易知更改表名的语句如下:alter table 表名 rename [TO|AS] 新表名
编程要求
数据库TestDb1中有表your_table,请根据提示,在右侧代码文件编辑窗中添加恰当的语句,将表名your_table更改为my_table。
确信语句无误后,请点击“评测”按扭开始评测。
右侧同时还开放了命令行窗口,你可以通过命令行登录到MySQL服务器,进行练习、实验,测试你的语句,验证你的想法。
这个窗口仅供练习使用,你在本窗口连接到数据时,看不到数据库“TestDb1”,也看不到表“your_table”,你也不知道该表的结构。你需要自行建库建表,然后再验证给表改名的语句。
代码窗口和命令行窗口可以随时切换,但平台只会评测代码文件中的代码,至于你在命令行输入的代码,不在评测之列。既使你已在命令行自行登录,并完整地实现了编程要求,你也必须把实现的代码填写在代码文件里,以供评测!
需要注意的是,你在命令行的操作有可能会影响评测结果。尽管评测程序尽可能地考虑了可能发生的各种情况,以避免你的其它操作干扰评测,但不能绝对保证完全避免这种情况的发生。所以,建议你在另一个数据库中进行练习和验证,并在验证无误后,将代码复制到代码文件中。
USE TestDb1;
#请在以下空白处添加恰当的语句,将表名your_table更改为my_table:
use TestDb1;
alter table your_table rename my_table;
第二关 添加与删除字段
任务描述
本关任务:为表添加和删除字段。
相关知识
为了完成本关任务,你需要掌握:
1.ALTER TABLE语句的用法;
2.如何给表添加字段;
3.如何删除表中已有的字段。
Alter Table 语句
Alter Table语句用于修改由Create 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] 新表名
如何给表添加字段
随着业务发展的需要,可能需要在原有表结构的基础上添加新的字段,由于建表时的疏忽,遗漏了某个字段,也需要将遗漏的列添加到表结构中。给表添加字段的语法是:
ALTER TABLE 表名 ADD [COLUMN] 列名 数据类型 [列约束] [FIRST | AFTER 列名]
在alter table语句中指定表名,接着用关键字add column申明要添加的列,包括列的名称,数据类型,以及可选的列约束。列约束可以是主码约束、外码约束、CHECK约束、Default约束、Unique约束等其中的任何一个或一组约束。这些约束在之前的实验中都介绍过。最后,还可以指定新添加的列在表中的位置:
- 关键字FIRST指示新添加的列为第1列;
- AFTER指示新添加的列紧跟在指定列的后面。
如果省略位置指示,则新添加的列将成为表的最后一列。
关键字column可以省略。
如何删除表中的字段
删除字段,即从表中将某个列移出,其语法格式为:
ALTER TABLE 表名 DROP [COLUMN] 列名
关键字COLUMN可以省略。
编程要求
假设数据库MyDb中有表order(订单)和orderDetail(订单明细) 等表,两表的结构分别如下:
order表
字段名称 | 数据类型 | 备注 |
---|---|---|
orderNo | char(12) | 订单号,主码 |
orderDate | date | 订购日期 |
customerNo | char(12) | 客户编号,外码,与customer.customerNo对应 |
employeeNo | char(12) | 雇员工号,外码,与employee.employeeNo对应 |
orderDetail表
字段名称 | 数据类型 | 备注 |
---|---|---|
orderNo | char(12) | 订单号,主属性,外码,与order.orderNo对应 |
productNo | char(12) | 产品编号,主属性,外码,与product.productNo对应 |
quantityOrdered | int | 订购数量 |
orderDate | date | 订购日期 |
注:表orderDetail的主码由(orderNo,productNo)组成
编程的任务是对orderDetail表进行修改:
- orderDetail表的orderDate列明显多余,因为同一订单中的每一笔交易都发生在同一天,这个日期在订单主体表order中已有记录,请删除列orderDate。
- 产品的单价是订单明细需要记录的内容,请在orderDetail中添加列unitPrice以记录产品的单价:
字段名称 | 数据类型 | 备注 |
---|---|---|
unitPrice | numeric(10,2) | 产品的成交单价 |
use MyDb;
#请在以下空白处添加适当的SQL代码,实现编程要求
#语句1:删除表orderDetail中的列orderDate
alter table orderDetail drop orderDate;
#语句2:添加列unitPrice
alter table orderDetail add unitPrice numeric(10,2);
第三关 修改字段
任务描述
本关任务:修改字段。
相关知识
为了完成本关任务,你需要掌握:
1.ALTER TABLE语句的用法;
2.如何给修改字段的名称;
3.如何修改字段的数据类型和约束;
4.如何修改字段在表中的位置。
如何修改字段的名称
如果只需要修改列的名称,显然用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某个列。注意:如果数据类型和约束重述的跟之前的不一样,则相当于修改了这个列,如果重述的列名跟之前的不一样,则会抛出错误信息(列不存在)。
编程要求
数据库MyDb中有表addressBook(通信录),结构如下:
字段名称 | 数据类型 | 备注 |
---|---|---|
serialNo | int | 自动编号,主码 |
name | char(32) | 姓名 |
company | char(32) | 工作单位 |
position | char(10) | 职位 |
workPhone | char(16) | 办公电话 |
mobile | char(11) | 手机 |
int | QQ号 | |
weixin | char(12) | 微信号 |
当初创建表的语句如下:
create table addressBook(
serialNo int auto_increment primary key,
name char(32),
company char(32),
position char(10),
workPhone char(16),
mobile char(11),
QQ int,
weixin char(12)
);
你的编程任务是对表addressBook作以下修改:
- 将QQ号的数据类型改为char(12);
- 将列名weixin改为wechat。
请根据提示,在右侧代码文件test3.sql编辑窗中添加恰当的语句,实现上述编程任务。
use MyDb;
#请在以下空白处添加适当的SQL语句,实现编程要求
alter table addressBook modify QQ char(12);
alter table addressBook rename column weixin to wechat;
第四关 添加、删除与修改约束
任务描述
本关任务:添加、删除与修改约束。
相关知识
为了完成本关任务,你需要掌握:
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括起来。
外码的删除与添加
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索引。
编程要求
数据库MyDb中有以下两表:
Dept(部门)
字段名称 | 数据类型 | 备注 |
---|---|---|
deptNo | INT | 部门号,主键 |
deptName | VARCHAR(32) | 部门名称,不同部门不允许重名 |
tel | char(11) | 部门电话 |
mgrStaffNo | int | 部门经理的工号,外码 |
Staff(职工)
字段名称 | 数据类型 | 备注 |
---|---|---|
staffNo | INT | 工号,主键 |
staffName | VARCHAR(32) | 职工姓名 |
gender | CHAR(1) | 性别,取值范围:F-女,M-男 |
dob | date | 出生日期 |
Salary | numeric(8,2) | 工资 |
dept | INT | 部门号,外键 |
现通过以下语句,完成了两表的基础创建工作(部分约束没有实现):
create table Dept(
deptNo int primary key,
deptName varchar(32),
tel char(11),
mgrStaffNo int
);
create table Staff(
staffNo int,
staffName varchar(32),
gender char(1),
dob date,
salary numeric(8,2),
dept int
);
请在右侧代码编辑窗对应位置写出适当的语句,完成以下工作:
(1) 为表Staff添加主码;
(2) Dept.mgrStaffNo是外码,对应的主码是Staff.staffNo,请添加这个外码,名字为FK_Dept_mgrStaffNo;
(3) Staff.dept是外码,对应的主码是Dept.deptNo. 请添加这个外码,名字为FK_Staff_dept;
(4) 为表Staff添加check约束,规则为:gender的值只能为F或M;约束名为CK_Staff_gender;
(5) 为表Dept添加unique约束:deptName不允许重复。约束名为UN_Dept_deptName.
use MyDb;
#请在以下空白处填写适当的诘句,实现编程要求。
#(1) 为表Staff添加主码
alter table Staff add constraint primary key(staffNo);
#(2) Dept.mgrStaffNo是外码,对应的主码是Staff.staffNo,请添加这个外码,名字为FK_Dept_mgrStaffNo:
alter table Dept add constraint FK_Dept_mgrStaffNo foreign key(mgrStaffNo) references Staff(staffNo);
#(3) Staff.dept是外码,对应的主码是Dept.deptNo. 请添加这个外码,名字为FK_Staff_dept:
alter table Staff add constraint FK_Staff_dept foreign key(dept) references Dept(deptNo);
#(4) 为表Staff添加check约束,规则为:gender的值只能为F或M;约束名为CK_Staff_gender:
alter table Staff add constraint CK_Staff_gender check(gender='F'or gender='M');
#(5) 为表Dept添加unique约束:deptName不允许重复。约束名为UN_Dept_deptName:
alter table Dept add constraint UN_Dept_deptName UNIQUE(deptName);