实验五 SQL练习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语句文档及相应的执行结果。