《数据库原理》MYSQL实验二

一、实验目的

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

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

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

二、实验学时

2学时

三、实验内容

1．完成以下操作：

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

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

create index 预算日期_index on 成本表(预算日期);
create index 结算日期_index on 成本表(结算日期);
create index 入账日期_index on 成本表(入账日期);


drop index 预算日期_index on 成本表;
drop index 结算日期_index on 成本表;
drop index 入账日期_index on 成本表;

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有哪些项目完成了入账，列出相应明细。

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



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



select  成本表.材料费,成本表.材料一费用,成本表.材料二费用,成本表.材料三费用,成本表.材料四费用
from 成本表 单位表 
where 预算单位 = 单位代码 and 结算日期 between '2023-5-1' and '2023-5-28'
	         and 单位名称 = '采油一矿二队'；
	  


select 成本表.* from 成本表,单位表
where 预算单位 = 单位代码
      and 单位名称 = '采油一矿二队'
	  and 入账日期 between '2023-5-1' and '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总的入账金额。

select sum(预算金额) from 成本表,单位表
where 预算单位 = 单位代码
      and 单位名称 = '采油一矿二队'
	  and 预算日期 between '2023-5-1' and '2023-5-28';



select sum(结算金额) from 成本表,单位表
where 预算单位 = 单位代码
      and 单位名称 = '采油一矿二队'
	  and 结算日期 between '2023-5-1' and '2023-5-28';


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


select sum(入账金额) from 成本表,单位表
where 预算单位 = 单位代码
      and 单位名称 like '采油一矿%'
	  and 入账日期 between '2023-5-1' and '2023-5-28';

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

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

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

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

select distinct 入账人 from 成本表
where 入账日期 is not null;


select 单据号 from 成本表
where 结算日期 between '2023-5-1' and '2023-5-28'
      and 入账日期 is null;


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


select 单位名称，sum(结算金额)
from 成本表,单位表
where 预算单位 = 单位代码
group by 单位名称;

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

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

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

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

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



select 单据号 from 成本表
where 施工单位 = '作业公司作业二队';



select 单据号 from 成本表
where 施工单位 = '作业公司作业一队'
union
select 单据号 from 成本表
where 施工单位 = '作业公司作业二队';


select distinct 施工单位 
from 成本表,单位表
where  单位代码=预算单位 and 单位名称 like '采油一矿%'
		 

3．完成以下操作：

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

create table 汇总表
       (施工单位 varchar(50),
	    年月 varchar(50),
		结算金额 decimal(10, 2)
	   );

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

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

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

begin;
update 成本表
set 结算人='李兵'
where 预算单位 in
	(select 单位代码 
    from 单位表
    where 单位名称 like '采油一矿%');
rollback;

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

begin;
delete 
from 成本表
where 预算单位 in
	(select 单位代码
    from 单位表
    where 单位名称 like '采油一矿%');
rollback;

⑸ 撤消上述两个操作。