《数据库原理》MYSQL实验三

一、实验目的

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

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

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

二、实验学时

2学时

三、实验内容

1．完成以下操作：

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

alter table 汇总表
add 备注 char(20);

SELECT * FROM 成本表;

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

Alter table 汇总表
Add primary key (年月，结算金额);
-执行失败，因为有数据存在，先删掉原有数据再添加主码

Delete
	from 汇总表;

Alter table 汇总表
Add primary key (年月，结算金额);

insert into 汇总表(施工单位,年月,结算金额)
select 单位名称,year(结算日期)*100+month(结算日期),sum(结算金额)
from 成本表,单位表
where 预算单位 = 单位代码
group by 单位名称,year(结算日期)*100+month(结算日期);

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

-删除数据
Delete
from 汇总表;

-删除表
drop table 汇总表；

2．完成以下任务：

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

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

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

alter table 材料表 add primary key(物码); 
alter table 材料消耗表 add primary key(单据号,物码); 
alter table 成本表 add primary key(单据号); 
alter table 单位表 add primary key(物单位代码);
alter table 油水井表 add primary key(井号); 

再执行插入操作显然不成功

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

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

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

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

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

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

-根据三个基本表，成本表和其中的细表材料消耗表可得其中属性的逻辑依赖关系
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 单位表(单位代码);

 

insert into  油水井表
values ('y007','油井','112203002');

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

delete 
from 单位表
where 单位代码='112202002' and 单位名称='采油二矿二队';

update 材料表
set 物码='wm04'
where 物码='wm004';

-操作全部不允许，所以无需撤销

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

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

alter table 单位表 
change column 单位名称 
单位名称 char(12) not null;
alter table 单位表 add unique(单位名称);

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

alter table 油水井表 
change column 井别 
井别 char(12) not null;
alter table 油水井表 add check (井别='油井' or 井别='水井');

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

alter table 材料表 
change column 名称 
名称 char(20) not null;
alter table 材料表 
change column 计量单位 
计量单位 char(20) not null;
alter table 材料表 add unique(名称);

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

alter table 材料消耗表 
change column 消耗数量 
消耗数量 char(20) not null;

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

alter table 成本表 
change column 预算金额 
预算金额 decimal(10,2) not null;
alter table 成本表
add check(预算日期 between "2023-1-1" and "2023-11-15");

3．完成以下操作：

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

create view v1 
as
select 成本表.*,材料消耗表.物码,材料消耗表.消耗数量
from 成本表,材料消耗表
where 成本表.单据号=材料消耗表.单据号;

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

-因为zy2023001分别有物码wm001，wm002,wm003,wm004所以出现了四次。
select *
from v1
where 单据号="zy2023001";

-限制后查询结果只有一个
select *
from v1
where 单据号="zy2023001" and 物码="wm001";

⑶ 定义一个反映成本表预算状态的视图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');
Rollback;

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