数据库原理实验三（MySQL版）

实验三 SQL练习3

一、实验目的

1．掌握基本表的删除与修改；

2．掌握实体完整性、参照完整性和用户定义的完整性的定义、检查和违约处理；

3．掌握视图的定义、查询和更新，了解视图的作用。

二、实验学时

2学时

三、实验内容

1．完成以下操作：

⑴ 向在实验二中所定义的数据表增加“备注”列，其数据类型为字符型，并查看新增列的值。

⑵ 对上述数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功，然后再次执行实验二中实验内容3-⑵的操作，并观察记录执行结果。

⑶ 删除上述数据表中的数据，然后再删除该数据表，对这两个操作进行比较。

2．完成以下任务：

⑴ 对实验一中所定义的5个数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功，然后执行以下2个操作，观察并记录实体完整性的检查和违约处理。

① insert into 材料消耗表 values('zy2023001','wm004',100)

② insert into 材料消耗表 values('zy2023002',NULL,200)

⑵ 对实验一中所定义的5个数据表增加相应的参照完整性约束，并观察在数据表中存在数据的情况下参照完整性约束是否创建成功，然后执行以下操作，观察并记录参照完整性的检查和违约处理。

① 将（y007 油井 112203002）插入到油水井表。

② insert into 材料消耗表 values('zy2023007','wm006',100)

③ 将单位表中的（112202002 采油二矿二队）删除，查看油水井表和成本表中的数据有何变化。

④ 将材料表中的（wm004 材料四 袋 10）修改为（wm04 材料四 袋 10）。

⑤ 撤销上述成功的更新操作。

⑶ 对实验一中所定义的5个数据表按以下要求增加相应的完整性约束条件，并观察在数据表中存在数据的情况下完整性约束是否创建成功。

① 单位表的单位名称不能取空值、且取值唯一。

② 油水井表的井别只允许取“油井”或“水井”，单位代码不能取空值。

③ 材料表的名称不能取空值、且取值唯一，计量单位不能取空值。

④ 材料消耗表的消耗数量不能取空值。

⑤ 对成本表根据实际应用的要求定义适当的用户定义的完整性约束条件。

3．完成以下操作：

⑴ 定义视图V1，用于保存成本表和材料消耗表的全部列。

⑵ 查询上面定义的视图V1，可任意组合查询条件，构造出2个查询。

⑶ 定义一个反映成本表预算状态的视图V2，并向该视图插入('zy2023008','112202002','y005',10000,'张三', '2023-07-02')，查看成本表的数据有何变化。

⑷ 撤销上述成功的更新操作。

四、实验报告

提交实验内容中用SQL语句完成的题目的SQL语句文档及相应的执行结果。

# 1.1向在实验二中所定义的数据表增加“备注”列，其数据类型为字符型，并查看新增列的值。
alter table 汇总表 add 备注 varchar(20);
select * from 汇总表
 
# 1.2对上述数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功，然后再次执行实验二中实验内容3-2的操作，并观察记录执行结果。
alter table 汇总表
add primary key(施工单位,年月);
# 子查询将各个施工单位每月的结算金额总和插入到所建立的数据表中：
insert into 汇总表(施工单位,年月,结算金额)
select 施工单位,date_format(结算日期, '%Y%m'),sum(结算金额) from 成本消耗表
group by `施工单位`,date_format(结算日期, '%Y%m');
select * from 汇总表;
 
# 1.3-1删除上述数据表中的数据，然后再删除该数据表，对这两个操作进行比较。
delete from 汇总表
select * from 汇总表;
# 1.3-2删除上述数据表中的数据，然后再删除该数据表，对这两个操作进行比较。
drop table 汇总表
select * from 汇总表;
# 2.1对实验一中所定义的5个数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功
# 然后执行以下2个操作，观察并记录实体完整性的检查和违约处理。
 
alter table 材料表 alter column 物码 varchar(20) not null;
alter table 材料表 add constraint 主码 primary key (物码);
 
alter table 材料消耗表 alter column 物码 varchar(20) not null;
alter table 材料消耗表 alter column 单据号 varchar(20) not null;
alter table 材料消耗表 add constraint 主码_材料消耗表 primary key (单据号,物码);
 
alter table 单位表 alter column 单位代码 varchar(20) not null;
alter table 单位表 add constraint 主码_单位表 primary key (单位代码);
 
alter table 油水井表 alter column 井号 varchar(20) not null;
alter table 油水井表 add constraint 主码_油水井表 primary key(井号);
 
alter table 成本消耗表 alter column 单据号 varchar(20) not null;
alter table 成本消耗表 add constraint 主码_成本消耗表 primary key(单据号);
 
# 2.1.1insert  into  材料消耗表  values('zy2023001','wm004',100)
insert  into  材料消耗表  values('zy2023001','wm004',100)
# 2.1.2insert  into  材料消耗表  values('zy2023002',NULL,200)
insert  into  材料消耗表  values('zy2023002',NULL,200)
 
# 2.2.0对实验一中所定义的5个数据表增加相应的参照完整性约束，并观察在数据表中存在数据的情况下参照完整性约束是否创建成功，然后执行以下操作，观察并记录参照完整性的检查和违约处理。
 
alter table 材料消耗表 add foreign key(单据号) references 成本消耗表(单据号);
alter table 材料消耗表 add foreign key(物码) references 材料表(物码);
alter table 油水井表 add foreign key(单位代码) references 单位表(单位代码);
alter table 成本消耗表 add foreign key(预算单位) references 单位表(单位代码);
alter table 成本消耗表 add foreign key(井号) references 油水井表(井号);
 
# 2.2.1将（y007  油井  112203002）插入到油水井表。
begin
insert into 油水井表 values('y007','油井','112203002');
# 2.2.2insert  into  材料消耗表  values('zy2023007','wm006',100)
insert  into  材料消耗表  values('zy2023007','wm006',100);
# 2.2.3将单位表中的（112202002  采油二矿二队）删除，查看油水井表和成本表中的数据有何变化。
delete from 单位表 where 单位代码 ='112202002' and
单位名称 = '采油二矿二队'
 
# 2.2.4将材料表中的（wm004 材料四 袋 10）修改为（wm04 材料四 袋 10）。
update 材料表 set 物码 = 'wm04' where '物码' = 'wm004';
 
# 2.2.5撤销上述成功的更新操作。
rollback;
# 2.3.0对实验一中所定义的5个数据表按以下要求增加相应的完整性约束条件，并观察在数据表中存在数据的情况下完整性约束是否创建成功。
# 2.3.1单位表的单位名称不能取空值、且取值唯一。
alter table 单位表 alter column 单位名称 varchar(12) not null;
alter table 单位表 add unique(单位名称);
 
# 2.3.2油水井表的井别只允许取“油井”或“水井”，单位代码不能取空值。
alter table 油水井表 add check(井别 = '油井' or 井别 = '水井');
alter table 油水井表 alter column 单位代码 varchar(20) not null;
 
# 2.3.3材料表的名称不能取空值、且取值唯一，计量单位不能取空值。
alter table 材料表 alter column 名称 varchar(20) not null; 
alter table 材料表 add unique(名称);
alter table 材料表 alter column 计量单位 varchar(20) not null;
 
# 2.3.4材料消耗表的消耗数量不能取空值。
alter table 材料消耗表 alter column 消耗数量 int not null;
 
 
# 2.3.5对成本表根据实际应用的要求定义适当的用户定义的完整性约束条件。
# 假设用户规定成本消耗标的预算金额不能为空
alter table 成本消耗表 alter column 预算金额 varchar(20) not null; 
 
 
# 3.1定义视图V1，用于保存成本表和材料消耗表的全部列。
create view V1 as select 成本消耗表.*,材料消耗表.消耗数量,材料消耗表.物码 
from 成本消耗表,材料消耗表 
where 成本消耗表.单据号 = 材料消耗表.单据号;
# 3.2查询上面定义的视图V1，可任意组合查询条件，构造出2个查询。
# 查询1——查询视图V1中井号为y001的全部列
select * from V1 where 井号 = 'y001';
 
# 查询2——查询视图V1中施工单位为作业公司作业二队的项目的单据号和预算单位
select 单据号,预算单位 from V1 where 施工单位 = '作业公司作业二队';
 
# 3.3定义一个反映成本表预算状态的视图V2，并向该视图插入('zy2023008','112202002','y005',10000,'张三', '2023-07-02')，查看成本表的数据有何变化。
create view V2 as select 单据号,预算单位,井号,预算金额,预算人,预算日期
from 成本消耗表;
 
begin;
insert into V2 values('zy2023008','112202002','y005',10000,'张三', '2023-07-02');
select * from V2;
 
# 3.4撤销上述成功的更新操作。
rollback;
select * from V2;