【Sql Server】实验报告四：索引与视图的使用

一、实验目的

1．掌握索引建立、修改与删除。

2．掌握建立视图、修改视图、删除视图。

3．掌握使用视图查询、更新数据、

二、实验内容

操作系统：Windows 10

数据库管理系统：SQL Server 2017

参考的是教材P84页的“实验3-3 索引与视图”的内容

• 实验过程

在数据库supermarket上完成下列操作

use SuperMarket

1.为表Supplier的字段SupplierName创建一个非聚集、唯一的索引

create unique nonclustered 

index index_supplier

on Supplier(SupplierName)

2.使用系统存储过程Sp_helpindex查看表Supplier的索引情况，如果已有主码，能否为其再建立一个聚集索引？为什么？

Sp_helpindex Supplier

不能，聚集索引是通过设置主码来完成的，每个表的主码都是聚集索引，一个表只能有一个聚集索引， 非聚集索引可以有多个。因此无法重复创建聚焦索引

3.删除第1题中所创建的索引

drop index index_supplier on Supplier

--或者

drop index Supplier.index_supplier

4.写出创建满足下述要求的视图的SQL语句

（1）统计每个学生的消费金额

create view student_sale as

(select S.SNO,SName,SUM(SalePrice * SA.Number) 消费额

from Student S join SaleBill SA

on S.SNO = SA.SNO

join Goods G on SA.GoodsNO = G.GoodsNO

group by S.SNO,SName);

go

select * from student_sale

--删除视图 ：drop view student_sale

（2）统计每个供货商提供的商品种类（一个商品编号代表一种）

create view supplier_kinds as

(select S.SupplierNO,SupplierName,count(S.SupplierNO) 商品种类数量

from Supplier S

join Goods G on S.SupplierNO = G.SupplierNO

group by S.SupplierNO,SupplierName)

go

select * from supplier_kinds

（3）统计各商品种类的销售数量及平均售价

create view googs_sum_avg as

(select g.CategoryNO,sum(s.Number) 销售数量,avg(SalePrice)  平均售价

from Goods g

join SaleBill s on g.GoodsNO=s.GoodsNO

join Category c on g.CategoryNO=c.CategoryNO

group by g.CategoryNO)

go 

select * from googs_sum_avg

（4）建立Sup001供货商的商品信息视图，并要求通过视图完成修改与插入操作时视图仍只有Sup001供货商的商品

create view Sup001_goods as

select * from Goods where SupplierNO='Sup001'

with check option

go

select* from Sup001_goods

5.利用上述视图，完成如下任务

（1）统计每个MIS专业学生的消费金额

select ssa.SNO,ssa.SName,Major,消费额 from student_sale ssa

join Student s on ssa.SNO=s.SNO

where Major='MIS'

（2）查询售价低于该商品总类售价平均价的商品名和售价

select GoodsName,SalePrice from Goods g

join googs_sum_avg gsa on g.CategoryNO=gsa.CategoryNO

where SalePrice<平均售价

（3）利用第4题（4）中的视图插入供货商Sup002的商品信息，结果如何？为什么？

insert into Sup001_goods (GoodsNO,SupplierNO, CategoryNO,GoodsName)

values('GN00011','Sup002','CN001','雀巢咖啡')

插入失败，原因是因为前面创建视图的时候规定了该视图在修改与插入的操作时， 视图仍只有Sup001供货商的商品

（4）利用第4题（4）中的视图删除GN004的商品信息，结果如何？为什么？

delete from Sup001_goods  where GoodsNO='GN0004'

删除成功，因为前面创建视图的时候规定了该视图在修改与插入的操作时，视图仍只有Sup001供货商的商品， 但是对删除的权限没有规定，而且视图内有GN0004的商品信息

（5）查询供货种类大于等于2的供货商的名称及数量

select SupplierName,商品种类数量 from supplier_kinds  

where 商品种类数量>=2

四、实验结果

4.（1）：

4.（2）：

4.（3）：

4.（4）：

5.（1）：

5.（2）：

5.（4）：

5.（5）：