数据库实验3
一、实验内容
1．完成以下操作
⑴ 向在实验二中所定义的数据表增加“备注”列，其数据类型为字符型，并查看新增列的值
alter table 汇总表
add 备注 char(20);
select *
from 汇总表
⑵ 对上述数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功，然后再次执行实验二中实验内容3-⑵的操作，并观察记录执行结果
alter table 汇总表
alter column 施工单位 set default 5;
alter table 汇总表
alter column 年月 set default 5;
alter table 汇总表
add primary key (施工单位, 年月);
TRUNCATE TABLE 汇总表;
insert into 汇总表(施工单位, 年月, 结算金额)
select 施工单位,
convert(结算日期, char),
sum(结算金额)
from 作业项目表
group by 施工单位, convert(结算日期, char);
⑶ 删除上述数据表中的数据，然后再删除该数据表，对这两个操作进行比较
# delete
# from 汇总表;
truncate table 汇总表;
2．完成以下任务
⑴ 对实验一中所定义的5个数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功，然后执行以下2个操作，观察并记录实体完整性的检查和违约处理
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 (井号);
①
insert into 材料消耗表
values ('zy2020001', 'wm004', 100)
②
insert into 材料消耗表
values ('zy2020002', null, 200)
⑵ 对实验一中所定义的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 单位表 (单位名称);
insert into 油水井表
values ('y007', '油井', '112203002');
insert into 材料表
values (' zy2016007', ' wm006', 100, 10);
update 作业项目表
set 施工单位='作业公司作业五队'
where 单据号 = 'zy2016001';
① 将（y007 油井 112203002）插入到油水井表。
② insert into 材料消耗表 values(‘zy2020007’,‘wm006’,100)
③ 将单位表中的（112202002 采油二矿二队）删除，查看油水井表和成本表中的数据有何变化。
delete
from 单位表
where 单位代码 = ' 112202002';
④ 将材料表中的（wm004 材料四 袋 10）修改为（wm04 材料四 袋 10）。
update 材料表
set 物码='wm04'
where 名称 = '材料四';
⑤ 撤销上述成功的更新操作。
⑶ 对实验一中所定义的5个数据表按以下要求增加相应的完整性约束条件，并观察在数据表中存在数据的情况下完整性约束是否创建成功
① 单位表的单位名称不能取空值、且取值唯一。
② 油水井表的井别只允许取“油井”或“水井”，单位代码不能取空值。
③ 材料表的名称不能取空值、且取值唯一，计量单位不能取空值。
④ 材料消耗表的消耗数量不能取空值。
⑤ 对成本表根据实际应用的要求定义适当的用户定义的完整性约束条件。
alter table 单位表
add constraint aa1 check 单位名称 is not null );
alter table 单位表
add constraint uni unique (单位代码);
alter table 油水井表
add constraint aa2 check 井别 in (' 油井', '水井'));
alter table 油水井表
alter column 单位代码 char (20) not null;
alter table 材料表
alter column 计量单位 char (20) not null;
alter table 材料表
add constraint uni2 unique (计量单位);
alter table 材料表
alter column 计量单位 char (20) not null;
alter table 材料表
alter column 消耗数量 int not null;
alter table 材料费表
alter column 单价 int not null;
alter table 作业项目表
add constraint asd check (材料费 + 人工费 + 设备费 + 其它费用 = 结算金额);
3．完成以下操作
⑴ 定义视图V1，用于保存成本表和材料消耗表的全部列
create view 视图一
as
select 作业项目表.*, 物码, 消耗数量, 单价
from 作业项目表,
材料表
where 作业项目表.单据号 = 材料表.单据号;
⑵ 查询上面定义的视图V1，可任意组合查询条件，构造出2个查询
select 结算金额, 消耗数量
from 视图一
where 物码 = 'wm003';
select 预算单位,
施工内容
from 视图一
where 单据号 = 'zy201 6001'
and 物码 = 'wm001';
⑶ 定义一个反映成本表预算状态的视图V2，并向该视图插入(‘zy2020008’,‘112202002’,‘y005’,10000,‘张三’, ‘2020-07-02’)，查看成本表的数据有何变化
create view 预算状态
as
select 单据号,
预算单位,
井号,
预算金额,
预算人,
预算日期
from 作业项目表;
insert into 预算状态
values (' zy2016008', '112202002', ' y005', '10000', '张三', ' 2020-07-02');