头歌Educoder MySQL-表结构与完整性约束的修改(ALTER)-CSDN博客

本文链接：https://blog.csdn.net/m0_61669521/article/details/124711750

第一关修改表名

任务描述

本关任务：修改表的名称。
本实验将介绍Alter table语句的大部分功能和修改表结构(添加列、约束，删除列、约束，修改列)等基础知识，这些知识将在接下来的实验中排上用场。

编程要求

数据库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;

第二关添加与删除字段

任务描述

本关任务：为表添加和删除字段。

编程要求

假设数据库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);

第三关修改字段

任务描述

本关任务：修改字段。

编程要求

数据库MyDb中有表addressBook(通信录)，结构如下：

字段名称	数据类型	备注
serialNo	int	自动编号，主码
name	char(32)	姓名
company	char(32)	工作单位
position	char(10)	职位
workPhone	char(16)	办公电话
mobile	char(11)	手机
QQ	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);