实验五 SQL练习3
- 一、实验目的
- 二、实验学时
- 三、实验内容
- 1．完成以下操作：
- 2．完成以下任务：
- 3．完成以下操作：
- 四、实验报告
一、实验目的
1．掌握基本表的删除与修改；
2．掌握实体完整性、参照完整性和用户定义的完整性的定义、检查和违约处理；
3．掌握视图的定义、查询和更新，了解视图的作用。
二、实验学时
2学时
三、实验内容
1．完成以下操作：
⑴ 向在实验二中所定义的数据表增加“备注”列，其数据类型为字符型，并查看新增列的值。
alter table 汇总表
add 备注 char
select * from 汇总表
⑵ 对上述数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功，然后再次执行实验二中实验内容 3-(2) 的操作，并观察记录执行结果。
alter table 汇总表 alter column 施工单位 varchar(16) not null;
alter table 汇总表 alter column 年月 varchar(7) not null;
alter table 汇总表
add primary key(施工单位, 年月);
insert into 汇总表
select 施工单位, convert(varchar(7), 结算日期, 120), sum(结算金额)
from 成本表
group by 施工单位, convert(varchar(7), 结算日期, 120);
⑶ 删除上述数据表中的数据，然后再删除该数据表，对这两个操作进行比较。
delete
from 汇总表;
select * from 汇总表;
drop table 汇总表;
2．完成以下任务：
⑴ 对实验一中所定义的5个数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功，然后执行以下2个操作，观察并记录实体完整性的检查和违约处理。
alter table 单位表 alter column 单位代码 varchar(9) not null; /*数据类型不要只写varchar呜呜呜*/
alter table 单位表 add primary key(单位代码); /*一句一句执行，不要同时执行*/
alter table 油水井表 alter column 井号 varchar(4) not null;
alter table 油水井表 add primary key(井号);
alter table 材料表 alter column 物码 varchar(5) not null;
alter table 材料表 add primary key(物码);
alter table 材料消耗表 alter column 单据号 varchar(9) not null;
alter table 材料消耗表 alter column 物码 varchar(5) not null;
alter table 材料消耗表 add primary key(单据号, 物码);
alter table 成本表 alter column 单据号 varchar(9) not null;
alter table 成本表 add primary key(单据号);① insert into 材料消耗表 values(‘zy2021001’,‘wm004’,100)
② insert into 材料消耗表 values(‘zy2021002’,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 油水井表(井号);① 将（y007 油井 112203002）插入到油水井表。
insert into 油水井表 values('y007', '油井', '112203002');
② insert into 材料消耗表 values(‘zy2021007’,‘wm006’,100)
③ 将单位表中的（112202002 采油二矿二队）删除，查看油水井表和 成本表 中的数据有何变化。
delete from 单位表
where 单位代码 = '112202002' and 单位名称 = '采油二矿二队';
④ 将材料表中的（wm004 材料四 袋 10）修改为（wm04 材料四 袋 10）。
update 材料表
set 物码 = 'wm04'
where 物码 ='wm004';
⑤ 撤销上述成功的更新操作。
/*在最开始运行begin transaction*/
rollback transaction;⑶ 对实验一中所定义的5个数据表按以下要求增加相应的完整性约束条件，并观察在数据表中存在数据的情况下完整性约束是否创建成功。
① 单位表的单位名称不能取空值、且取值唯一。
alter table 单位表 alter column 单位名称 varchar(12) not null;
alter table 单位表 add unique(单位名称);② 油水井表的井别只允许取“油井”或“水井”，单位代码不能取空值。
alter table 油水井表 add constraint 井别约束 check (井别 in ('油井', '水井'));
alter table 油水井表 alter column 单位代码 varchar(9) not null;③ 材料表的名称不能取空值、且取值唯一，计量单位不能取空值。
alter table 材料表 alter column 名称 varchar(6) not null;
alter table 材料表 add unique(名称);
alter table 材料表 alter column 计量单位 varchar(2) not null;④ 材料消耗表 的消耗数量不能取空值。
alter table 材料消耗表 alter column 消耗数量 int not null;⑤ 对 成本表 根据实际应用的要求定义适当的用户定义的完整性约束条件。
/*例：施工内容非空*/
alter table 成本表 alter column 施工内容 varchar(4) not null;3．完成以下操作：
⑴ 定义视图V1，用于保存 成本表 和 材料消耗表 的全部列。
create view V1
as
select 成本表.*, 物码, 消耗数量
from 成本表, 材料消耗表
where 成本表.单据号 = 材料消耗表.单据号;
/*上下分开执行*/
select * from V1;
⑵ 查询上面定义的视图V1，可 任意组合查询条件，构造出2个查询。
/*查询所有单据号*/
select distinct 单据号
from V1;
/*查询物码为wm001的预算单位*/
select distinct 预算单位
from V1
where 物码 = 'wm001';
⑶ 定义一个反映 成本表 预算状态的视图V2，并向该视图插入(‘zy2021008’,‘112202002’,‘y005’,10000,‘张三’, ‘2021-07-02’)，查看 成本表 的数据有何变化。
create view V2
as
select 单据号, 预算单位, 井号, 预算金额, 预算人, 预算日期
from 成本表;
select * from V2;
insert into V2
values('zy2021008','112202002','y005',10000,'张三', '2021-07-02')
--????插不进去
⑷ 撤销上述成功的更新操作。
/*忘了提前begin transaction，这里删除视图*/
drop view V1;
drop view V2;四、实验报告
提交实验内容中用SQL语句完成的题目的SQL语句文档及相应的执行结果。
