SQL Server实战2

SQL Server实战2

前言：第二次上机实验，难度逐渐增大，这才发现SQL语句还得多写多练，看起来简单还真不一定能写出来。此次试验主要做增删改查，特别是查询尤其重要，建议多练习。

推荐SQL练习网站：http://xuesql.cn/ SQL即刻执行，边写边观察结果，特别方便

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

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

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

注意：试验中有很多相同的语句，不会重复写

实验内容

1．索引练习

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

create  index 预算日期索引
on 成本(预算日期)

select *
from 成本
where 预算日期 = '2021-05-01'

（夹带私活中）什么是索引？

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

如果学过数据结构，从二叉树的角度应该不难理解。

举个栗子：

如果还不明白，建议去看看： 索引在查询中如何使用(例子一)

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

drop index 预算日期索引
on 成本

2．查询练习

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

select 单位名称,单据号,预算单位,井号,预算金额,预算人,预算日期
from 成本,单位
where 成本.预算单位 = 单位.单位代码
and 单位.单位名称 = '采油一矿二队'
and 预算日期 between '2021-5-1' and '2021-5-28'

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

select 单位名称,开工日期,完工日期,施工单位,施工内容,材料费,人工费,设备费,其它费用,结算金额,结算人,结算日期
from 成本,单位
where 成本.预算单位 = 单位.单位代码
and 单位.单位名称 = '采油一矿二队'
and 结算日期 between '2021-5-1' and '2021-5-28'

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

select 成本.单据号,单位名称,材料.物码,消耗数量,名称,单价,消耗数量*单价 该材料费
from 成本,单位,材料消耗,材料
where 成本.预算单位 = 单位.单位代码
and 材料消耗.单据号 = 成本.单据号
and 单位.单位名称 = '采油一矿二队'
and 结算日期 between '2021-5-1' and '2021-5-28'
and 材料.物码 = 材料消耗.物码

这个查询很麻烦，建议一步一步写，先找查完成预算的项目，再连接材料消耗表，最后连接材料表

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

select 单位名称,入账金额,入账人,入账日期
from 成本,单位
where 成本.预算单位 = 单位.单位代码
and 单位.单位名称 = '采油一矿二队'
and 入账日期 between '2021-5-1' and '2021-5-28'

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

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

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

类上

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

类上

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

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

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

select distinct 入账人
from 成本
where 入账人 is not null

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

select *
from 成本
where 结算日期 is not null
and 入账日期 is null
and 结算日期 between '2021-5-1' and '2021-5-28'

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

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

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

select 单位.单位名称,SUM(结算金额) 总结算
from 单位,成本
where 单位.单位代码 = 成本.预算单位
group by 单位名称

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

select 单据号,消耗数量,单价
from 材料消耗,材料
where  材料消耗.物码 = 材料.物码
and 名称 = '材料三'
and 消耗数量*单价 > 2000

/*
只会连接,不知道怎么用子查询得到超过了2000元的项目。。。
*/
select 单据号
from 材料消耗
where 物码 in 
	(select 物码 
	from 材料
	where 名称 = '材料三')

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

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

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

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

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

select 施工单位
from 单位,成本,油水井
where 单位.单位代码 = 油水井.单位代码
and 油水井.井号 = 成本.井号
and 油水井.井别 = '油井'
and 单位.单位名称 like '采油一矿%'

3．子查询

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

create table 汇总表
(
	施工单位 varchar(20),
	年月 varchar(20),
	结算金额 float
)

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

insert into 汇总表
select 施工单位,
       convert(varchar(7),结算日期),	--这里的convert相当于截取前7位
       sum(结算金额)
from 成本
group by 施工单位, convert(varchar(7),结算日期);	
-- 仍然不会用子查询处理...

同时对施工单位和年月两个字段进行分组！

参考convert函数：https://www.runoob.com/sql/func-convert.html

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

update 成本
set 结算人 = '李兵'
where 预算单位 in 
(
	select distinct 单位代码
	from 油水井
	where 井别 = '油井'
	and 单位代码 in
		(select 单位代码
		from 单位
		where 单位名称 like '采油一矿%')
)

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

类上

⑸ 撤消上述两个操作。

第十章的内容，下次一定，下次一定。。。