数据库：SQL练习3

一、实验目的

1．掌握基本表的删除与修改；
2．掌握实体完整性、参照完整性和用户定义的完整性的定义、检查和违约处理；
3．掌握视图的定义、查询和更新，了解视图的作用。

二、实验内容

1．完成以下操作（实体完整性）

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

alter table 汇总表
add 备注 varchar(50);
-- 新增列的值为空值null

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

alter table 汇总表
add primary key (施工单位, 年月); 
/*
无法在表 '汇总表' 中可为 Null 的列上定义 PRIMARY KEY 约束。
设为非null即可
*/

truncate table 汇总表;  
/*
清空表中的数据。如果不清空，下面的插入数据操作无法执行。
在数据表中存在数据的情况下，主码约束不能成功创建。因为此时这些列被默认定义为“可为null”，不能作为主码。
*/

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

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

delete
from 汇总表

drop table 汇总表

2．完成以下任务（参照完整性）

⑴ 对实验一中所定义的5个数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功。然后执行以下2个操作，观察并记录实体完整性的检查和违约处理。
① insert into 材料消耗表 values(‘zy2021001’,‘wm004’,100)
② insert into 材料消耗表 values(‘zy2021002’,NULL,200)

对已存在的表增加约束条件：
列值非空：alter table 表名 alter column 列名 列数据类型 not null；
列值唯一：alter table 表名 add unique(列名)；

/*
在增加主码约束之前，首先要对主码列，设置不能为空值。
*/

alter table 材料表 alter column 物码 char(20) not null;
alter table 材料表 add constraint 主码 primary key (物码);

alter table 材料消耗表 alter column 物码 char(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(单据号);

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 单位表(单位代码);
alter table 成本表 add foreign key(井号) references 油水井表(井号);

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

insert into 油水井表 values('y007','油井','112203002'); 
-- INSERT 语句与 FOREIGN KEY 约束"FK__油水井表__单位代码__6E01572D"冲突。
-- 该冲突发生于数据库"zyxt"，表"dbo.单位表", column '单位代码'。

② insert into 材料消耗表 values(‘zy2021007’,‘wm006’,100)

insert  into  材料消耗表  values('zy2021007','wm006',100);
-- INSERT 语句与 FOREIGN KEY 约束"FK__油水井__单位代码__2A4B4B5E"冲突

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

delete 
from 单位表
where 单位代码 = '112202002'
and 单位名称 ='采油二矿二队';
-- DELETE 语句与 REFERENCE 约束"FK__油水井表__单位代码__6E01572D"冲突。
-- 该冲突发生于数据库"zyxt"，表"dbo.油水井表", column '单位代码'。

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

update 材料表
set 物码 = 'wm04'
where 物码 = 'wm004';
-- UPDATE 语句与 REFERENCE 约束"FK__材料消耗表__物码__60A75C0F"冲突。
-- 该冲突发生于数据库"zyxt"，表"dbo.材料消耗表", column '物码'。

⑤ 撤销上述成功的更新操作。（因为约束条件都正确的定义了，所以没有成功的更新操作）

⑶ 对实验一中所定义的5个数据表按以下要求增加相应的完整性约束条件，并观察在数据表中存在数据的情况下完整性约束是否创建成功。
① 单位表的单位名称不能取空值、且取值唯一。
② 油水井表的井别只允许取“油井”或“水井”，单位代码不能取空值。
③ 材料表的名称不能取空值、且取值唯一，计量单位不能取空值。
④ 材料消耗表的消耗数量不能取空值。
⑤ 对成本表根据实际应用的要求定义适当的用户定义的完整性约束条件。

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

alter table 油水井表 add check(井别 = '油井' or 井别 = '水井');
alter table 油水井表 alter column 单位代码 varchar(20) not null;

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

alter table 材料消耗表 alter column 消耗数量 float not null;

3．完成以下操作（视图相关）

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

注意：
① 在sql sever查询环境中“create view V1”会报错，报错显示：“create view必须是批处理中仅有的语句”。解决方法：在创建视图的语句前后都添加“go”语句。或者执行时只勾选创建视图的语句块。
② 视图中不允许出现重复的列（但是表中可以）。也就是说这里不能用“select * from 成本表,材料消耗表”。 成本表.单据号 、 材料消耗表.单据号这两列不能同时在视图中出现。

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

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

select *
from v1
where 单据号 = 'zy2020001';

⑶ 定义一个反映成本表预算状态的视图V2，并向该视图插入 (‘zy2021008’,‘112202002’,‘y005’,10000,‘张三’, ‘2021-07-02’)，查看成本表的数据有何变化。
⑷ 撤销上述成功的更新操作。

create view v2
as
select 单据号,预算单位,井号,预算金额,预算人,预算日期
from 成本表;

begin transaction
insert 
into v2
values('zy2021008','112202002','y005',10000,'张三', '2021-07-02');
select * from v2
rollback;