数据库：SQL练习2

实验目的

1．掌握索引的建立、删除及使用；
2．掌握单表查询、连接查询、嵌套查询和集合查询；
3．掌握插入数据、修改数据和删除数据语句的非常用形式。

实验内容

1.创建和删除索引

⑴ 在预算日期、结算日期和入账日期上分别建立索引，并在查询操作中体会索引的作用。
⑵ 在完成第2题的查询操作后，删除预算日期、结算日期和入账日期上的索引。

-- 创建索引
create index Budget_date_index on 成本表(预算日期);
create index Settlement_date_index on 成本表(结算日期);
create index Billing_date_index on 成本表(入账日期);

-- 删除索引
drop index Budget_date_index on 成本表;
drop index Settlement_date_index on 成本表;
drop index Billing_date_index on 成本表;

2.完成以下查询操作

⑴ 采油一矿二队2020-5-1到2020-5-28有哪些项目完成了预算，列出相应明细。
⑵ 采油一矿二队2020-5-1到2020-5-28有哪些项目完成了结算，列出相应明细。
⑶ 采油一矿二队2020-5-1到2020-5-28有哪些项目完成了结算，列出相应的材料费消耗明细。
⑷ 采油一矿二队2021-5-1到2021-5-28有哪些项目完成了入账，列出相应明细。

select * from 成本表
where 预算日期>='2020-5-1' AND 预算日期<='2020-5-28'
      AND 预算单位=
         (select 单位代码
	      from 单位表
	      where 单位名称='采油一矿二队'
	      );



select * from 成本表,单位表
where 预算单位 = 单位代码 AND 单位名称 = '采油一矿二队'
      AND 结算日期 between '2020-5-1' and '2020-5-28'; 



select  * from 材料消耗表 
where 单据号 in 
      (select 单据号 
	   from 成本表,单位表
	   where 预算单位 = 单位代码
	         and 结算日期 between '2020-5-1' and '2020-5-28'
	         and 单位名称 = '采油一矿二队'
	  );


select * from 成本表,单位表
where 预算单位 = 单位代码
      and 单位名称 = '采油一矿二队'
	  and 入账日期 between '2020-5-1' and '2020-5-28';
	  

聚集函数，字符串匹配
⑸ 列出采油一矿二队2021-5-1到2021-5-28总的预算金额。
⑹ 列出采油一矿二队2021-5-1到2021-5-28总的结算金额。
⑺ 列出采油一矿二队2021-5-1到2021-5-28总的入账金额。
⑻ 列出采油一矿2021-5-1到2021-5-28总的入账金额。

-- 2⑸ 列出采油一矿二队2020-5-1到2020-5-28总的预算金额。
select 总预算金额 = sum(预算金额) from 成本表,单位表
where 预算单位 = 单位代码
      and 单位名称 = '采油一矿二队'
	  and 预算日期 between '2020-5-1' and '2020-5-28';


-- 2⑹ 列出采油一矿二队2020-5-1到2020-5-28总的结算金额。
select 总结算金额 = sum(结算金额) from 成本表,单位表
where 预算单位 = 单位代码
      and 单位名称 = '采油一矿二队'
	  and 结算日期 between '2020-5-1' and '2020-5-28';


-- 2⑺ 列出采油一矿二队2020-5-1到2020-5-28总的入账金额。
select 总入账金额 = sum(入账金额) from 成本表,单位表
where 预算单位 = 单位代码
      and 单位名称 = '采油一矿二队'
	  and 入账日期 between '2020-5-1' and '2020-5-28';


-- 2⑻ 列出采油一矿2021-5-1到2021-5-28总的入账金额。（聚集函数，字符串匹配）
-- 注意，成本表中没有“采油一矿”对应的预算单位，只有具体的队。所以这里用字符串匹配的方式。
select 总入账金额 = sum(入账金额) from 成本表,单位表
where 预算单位 = 单位代码
      and 单位名称 like '采油一矿%'
	  and 入账日期 between '2020-5-1' and '2020-5-28';

空值 is null / is not null
⑼ 有哪些人员参与了入账操作。
⑽ 列出2021-5-1到2021-5-28进行了结算但未入账的项目。

-- 2⑼ 有哪些人员参与了入账操作。
select distinct 入账人 from 成本表
where 入账日期 is not null;


-- 2⑽ 列出2021-5-1到2021-5-28进行了结算但未入账的项目。
select 单据号 from 成本表
where 结算日期 between '2020-5-1' and '2020-5-28'
      and 入账日期 is null;

⑾ 列出采油一矿二队的所有项目，按入账金额从高到低排列。（排序）

select 单据号,入账金额 from 成本表,单位表
where 预算单位 = 单位代码
      and 单位名称 = '采油一矿二队'
order by 入账金额 desc;

⑿ 列出有哪些施工单位实施了项目，并计算各单位所有项目结算金额总和。（聚集函数，分组）

select 单位名称,项目数量 = count(结算金额),结算金额总和 = sum(结算金额)
from 成本表,单位表
where 预算单位 = 单位代码
group by 单位名称;

⒀ 找出消耗了材料三且消耗超过了2000元的项目，列出相应消耗明细。(利用子查询)

select * from 材料消耗表
where 物码 in
      (select 物码 from 材料表
	   where 名称 = '材料三'
	         and 单价*消耗数量>=2000
	  );

⒁ 作业公司二队参与了哪些项目。
⒂ 作业公司一队和二队参与了哪些项目。（利用union，并操作）
⒃ 采油一矿的油井是哪些作业队参与施工的。（子查询）

-- 2⒁ 作业公司二队参与了哪些项目。
select 单据号 from 成本表
where 施工单位 = '作业公司作业二队';


-- 2⒂ 作业公司一队和二队参与了哪些项目（利用union，并操作）。
select 单据号 from 成本表
where 施工单位 = '作业公司作业一队'
union
select 单据号 from 成本表
where 施工单位 = '作业公司作业二队';


-- 2⒃ 采油一矿的油井是哪些作业队参与施工的。（子查询）
select 施工单位 from 成本表
where 井号 in
      (select 井号 from 油水井表
	   where 井别 = '油井'
	   and 单位代码 in
	       (select 单位代码 from 单位表
		    where 单位名称 like '采油一矿%'
		   )
	  );

3.完成以下数据更新操作

⑴ 建立汇总表(包含3个属性列：★施工单位、★年月、◆结算金额)保存各个施工单位每月的结算金额总和。
⑵ 用子查询将各个施工单位每月的结算金额总和插入到所建立的数据表中。
⑶ 用带子查询的修改语句将采油一矿油井作业项目的结算人改为“李兵”。
⑷ 用带子查询的删除语句删除采油一矿油井作业项目。
⑸ 撤消上述两个操作。

-- 3⑴ 建立汇总表(包含3个属性列：★施工单位、★年月、◆结算金额)保存各个施工单位每月的结算金额总和。
create table 汇总表
       (施工单位 varchar(50),
	    年月 varchar(50),
		结算金额 decimal(10, 2)
	   )


-- 3⑵ 用子查询将各个施工单位每月的结算金额总和插入到所建立的数据表中。
insert into 汇总表(施工单位,年月,结算金额)
       select 单位名称,year(结算日期)*100+month(结算日期),结算金额总和 = sum(结算金额)
       from 成本表,单位表
       where 预算单位 = 单位代码
       group by 单位名称,year(结算日期)*100+month(结算日期);

select * from 汇总表;


-- 3⑶ 用带子查询的修改语句将采油一矿油井作业项目的结算人改为“李兵”。
begin transaction;
update 成本表
set 结算人='李兵'
where 预算单位 in
      (select 单位代码 from 单位表
	   where 单位名称 like '采油一矿%'
	  );

select * from 成本表;
rollback;


-- 3⑷ 用带子查询的删除语句删除采油一矿油井作业项目。
begin transaction
delete
from 成本表
where 预算单位 in
      (select 单位代码 from 单位表
	   where 单位名称 like '采油一矿%'
	  );

rollback;

为了便于撤销增删改操作，请在增删改操作之前执行begin transaction，如果要撤销增删改操作，执行rollback。
保存点提供了一种机制，用于回滚部分事务。
可以使用 SAVE TRANSACTION savePoint_name 语句创建保存点。然后执行 ROLLBACK TRANSACTION savePoint_name 语句以回滚到保存点，而不是回滚到事务的起点。
savePoint_name,用户定义的保存点名称.