数据库原理实验二（MySQL版）

实验二 SQL练习2

一、实验目的

1．掌握索引的建立、删除及使用；

2．掌握单表查询、连接查询、嵌套查询和集合查询；

3．掌握插入数据、修改数据和删除数据语句的非常用形式。

二、实验学时

2学时

三、实验内容

1．完成以下操作：

⑴ 在预算日期、结算日期和入账日期上分别建立索引，并在查询操作中体会索引的作用。

⑵ 在完成第2题的查询操作后，删除预算日期、结算日期和入账日期上的索引。

2．完成以下操作：

⑴ 采油一矿二队2023-5-1到2023-5-28有哪些项目完成了预算，列出相应明细。

⑵ 采油一矿二队2023-5-1到2023-5-28有哪些项目完成了结算，列出相应明细。

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

⑷ 采油一矿二队2023-5-1到2023-5-28有哪些项目完成了入账，列出相应明细。

⑸ 列出采油一矿二队2023-5-1到2023-5-28总的预算金额。

⑹ 列出采油一矿二队2023-5-1到2023-5-28总的结算金额。

⑺ 列出采油一矿二队2023-5-1到2023-5-28总的入账金额。

⑻ 列出采油一矿2023-5-1到2023-5-28总的入账金额。

⑼ 有哪些人员参与了入账操作。

⑽ 列出2023-5-1到2023-5-28进行了结算但未入账的项目。

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

⑿ 列出有哪些施工单位实施了项目，并计算各单位所有项目结算金额总和。

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

⒁ 作业公司作业二队参与了哪些项目。

⒂ 作业公司作业一队和作业二队参与了哪些项目（利用union）。

⒃ 采油一矿的油井是哪些作业队参与施工的。

3．完成以下操作：

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

⑵ 用子查询将各个施工单位每月的结算金额总和插入到所建立的数据表中。

⑶ 用带子查询的修改语句将采油一矿油井作业项目的结算人改为“李兵”。

⑷ 用带子查询的删除语句删除采油一矿油井作业项目。

⑸ 撤消上述两个操作。

四、实验报告

提交实验内容中用SQL语句完成的题目的SQL语句文档及相应的执行结果。

# 1.1在预算日期、结算日期和入账日期上分别建立索引，并在查询操作中体会索引的作用
create index budget_date on 成本消耗表(预算日期)
create index settlement_date on 成本消耗表(结算日期)
create index entry_date on 成本消耗表(入账日期)
 
# 2.1采油一矿二队2023-5-1到2023-5-28有哪些项目完成了预算，列出相应明细
select * from 成本消耗表
where 预算日期 >='2023-5-1' AND 预算日期 <='2023-5-28' AND 预算人 is not null AND 预算金额 is not null
      AND 预算单位 =
         (select 单位代码
          from 单位表
          where 单位名称 ='采油一矿二队'
          );
# 2.2采油一矿二队2023-5-1到2023-5-28有哪些项目完成了结算，列出相应明细
select * from 成本消耗表
where 结算日期 >='2023-5-1' AND 结算日期 <='2023-5-28' AND 结算人 is not null AND 结算金额 is not null
      AND 预算单位 =
         (select 单位代码
          from 单位表
          where 单位名称 ='采油一矿二队'
          );
# 2.3采油一矿二队2023-5-1到2023-5-28有哪些项目完成了结算，列出相应的材料费消耗明细			
select 材料费 from 成本消耗表
where 结算日期 >='2023-5-1' AND 结算日期 <='2023-5-28' AND 结算人 is not null AND 结算金额 is not null
      AND 预算单位 =
         (select 单位代码
          from 单位表
          where 单位名称 ='采油一矿二队'
          );
# 2.4采油一矿二队2023-5-1到2023-5-28有哪些项目完成了入账，列出相应明细
select * from 成本消耗表
where 入账日期 >='2023-5-1' AND 入账日期 <='2023-5-28' AND 入账人 is not null AND 入账金额 is not null
      AND 预算单位 =
         (select 单位代码
          from 单位表
          where 单位名称 ='采油一矿二队'
          );
					
# 2.5列出采油一矿二队2023-5-1到2023-5-28总的预算金额 
select sum(预算金额) from 成本消耗表
where 预算日期 >='2023-5-1' AND 预算日期 <='2023-5-28' AND 预算人 is not null AND 预算金额 is not null
      AND 预算单位 =
         (select 单位代码
          from 单位表
          where 单位名称 ='采油一矿二队'
          );
# 2.6列出采油一矿二队2023-5-1到2023-5-28总的结算金额
select sum(结算金额) from 成本消耗表
where 结算日期 >='2023-5-1' AND 结算日期 <='2023-5-28' AND 结算人 is not null AND 结算金额 is not null
      AND 预算单位 =
         (select 单位代码
          from 单位表
          where 单位名称 ='采油一矿二队'
          );
# 2.7列出采油一矿二队2023-5-1到2023-5-28总的入账金额
select sum(入账金额) from 成本消耗表
where 入账日期 >='2023-5-1' AND 入账日期 <='2023-5-28' AND 入账人 is not null AND 入账金额 is not null
      AND 预算单位 =
         (select 单位代码
          from 单位表
          where 单位名称 ='采油一矿二队'
          );	
# 2.8列出采油一矿2023-5-1到2023-5-28总的入账金额
select sum(入账金额) from 成本消耗表,单位表
where 入账日期 >='2023-5-1' AND 入账日期 <='2023-5-28' AND 入账人 is not null AND 入账金额 is not null
      AND 成本消耗表.预算单位 = 单位表.单位代码 AND 单位名称 LIKE '采油一矿%'
          
# 2.9列出有哪些人员参与了入账操作
SELECT DISTINCT
	入账人 
FROM
	成本消耗表;# select语句添加distinct关键字做到去除重复行
WHERE
	入账日期 IS NOT NULL 
	AND 入账金额 IS NOT NULL
# 2.10列出2023-5-1到2023-5-28进行了结算但未入账的项目
select * from 成本消耗表
where 结算日期 >='2023-5-1' AND 结算日期 <='2023-5-28' AND 入账日期 is null; 
					
# 2.11列出采油一矿二队的所有项目，按入账金额从高到低排列
SELECT 单据号,入账金额 FROM 成本消耗表,单位表
WHERE 预算单位 = 单位代码
      AND 单位名称 = '采油一矿二队'
      ORDER BY 入账金额 DESC;
			
# 2.12列出有哪些施工单位实施了项目，并计算各单位所有项目结算金额总和
select 单位名称,sum(结算金额) from 单位表,成本消耗表
where 单位代码 = 预算单位 group by 单位名称
 
# 2.13找出消耗了材料三且消耗超过了2000元的项目，列出相应消耗明细(利用子查询)
select * from 材料消耗表
where 物码 in 
(select 物码 from 材料表 where 名称 = '材料三' and 单价*消耗数量 >=2000);
 
# 2.14作业公司作业二队参与了哪些项目
select * from 成本消耗表
where 施工单位 = '作业公司作业二队'
 
# 2.15作业公司作业一队和作业二队参与了哪些项目（利用union）
select * from 成本消耗表
where 施工单位 = '作业公司作业一队'
union
select * from 成本消耗表
where 施工单位 = '作业公司作业二队'
 
# 2.16采油一矿的油井是哪些作业队参与施工的
select distinct 施工单位 from 成本消耗表,油水井表,单位表
where 成本消耗表.井号=油水井表.井号 and 油水井表.单位代码=单位表.单位代码 and 单位表.单位名称 like '采油一矿%'
 
# 1.2 在完成第2题的查询操作后，删除预算日期、结算日期和入账日期上的索引
DROP INDEX budget_date ON 成本消耗表;
DROP INDEX settlement_date ON 成本消耗表;
DROP INDEX entry_date ON 成本消耗表;
 
# 3.1建立汇总表(包含3个属性列：★施工单位、★年月、◆结算金额)保存各个施工单位每月的结算金额总和：
create table 汇总表
(施工单位 varchar(20),
年月 varchar(20),
结算金额 decimal(10,2));
select * from 汇总表;
 
# 3.2用子查询将各个施工单位每月的结算金额总和插入到所建立的数据表中：
insert into 汇总表(施工单位,年月,结算金额)
select 施工单位,date_format(结算日期, '%Y%m'),sum(结算金额) from 成本消耗表
group by `施工单位`,date_format(结算日期, '%Y%m');
select * from 汇总表;
 
# 3.3用带子查询的修改语句将采油一矿油井作业项目的结算人改为“李兵”：
begin;
update 成本消耗表 set 结算人='李兵'
where 预算单位 in 
(select 单位代码 from 单位表 where 单位名称 in (select 单位代码 from 单位表 where 单位名称 like '采油一矿%'));
select * from 成本消耗表;
 
# 3.4用带子查询的删除语句删除采油一矿油井作业项目：
delete from 成本消耗表 
where 预算单位 in (select 单位代码  from 单位表 where 单位名称 like '采油一矿%');
select * from 成本消耗表;
 
# 3.5撤消上述两个操作：
rollback;
select * from 成本消耗表;