数据库原理及应用-李唯唯主编-实验3-3

redvelvets

已于 2022-10-23 15:20:41 修改

阅读量4.6k

点赞数 15

文章标签：数据库 sql mysql

于 2022-10-23 15:08:16 首次发布

本文链接：https://blog.csdn.net/qq_45659753/article/details/127474989

版权

实验3-3 索引和视图

一、实验目的与要求

掌握索引的建立、修改与删除
掌握建立视图、修改视图、删除视图
掌握使用视图进行查询、更新数据

二、实验内容

实验平台：
- 操作系统：windows11
- 数据库：MySQL8
- 数据库管理平台：Navicat Premium 15
题目：在数据库supermarket数据库上完成下列操作
1. 为表supplier的字段SupppelierName创建一个非聚集、唯一索引
2. 使用系统存储过程Sp_helpindex查看表supplier的索引情况，如果已有主码，能否为其再建立一个聚集索引？为什么？
3. 删除第1题中所建立的索引。
4. 写出创建满足下述要求的视图的SQL语句
  1. 统计每个学生的消费金额。
  2. 统计每个供货商提供的商品种类（一个商品编号代表一种）
  3. 统计各商品种类的销售数量及平均售价。
  4. 建立Sup001供货商的商品信息视图，并要求通过视图完成修改与插入操作时视图仍只有Sup001供货商的商品。
  5. 利用上述视图，完成如下任务
    1. 统计每个MIS专业学生的消费金额。
    2. 查询售价低于该商品种类售价平均价的商品名和售价。
    3. 利用第4题(4)中的视图插人供货商Sup002的商品信息，结果如何？为什么？
    4. 利用第4题(4)中的视图删除GN0004的商品信息，结果如何？为什么？
    5. 查询供货种类大于等于2的供货商的名称及数量.

三、实验过程、步骤及结果

为表supplier的字段SupppelierName创建一个非聚集、唯一索引

代码：

-- 1. 为表supplier的字段SupppelierName创建一个非聚集、唯一索引
DESC supplier
EXPLAIN SELECT * FROM supplier WHERE SupplierName = '重启缙云日化品贸易公司';

-- 创建索引
CREATE UNIQUE INDEX SupplierName_index ON supplier(SupplierName);

DESC supplier
EXPLAIN SELECT * FROM supplier WHERE SupplierName = '重启缙云日化品贸易公司';

效果图： $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JSa1f0ZQ-1666508251347)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221023102401949.png)]$

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

代码：

-- 2. 使用系统存储过程Sp_helpindex查看表supplier的索引情况，如果已有主键，能否为其再建立一个聚集索引？为什么？
SHOW INDEX FROM supplier;
-- 不行,因为已有主键，系统会自动为该表创建一个唯一、聚集性索引，其次按照规定来说一张表只能有一种数据的存储顺序，而聚集索引恰恰是决定了数据的存储顺序，索引一张已经有主键的表不能再为其创建一个聚集索引

效果图： $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0iE9nBlN-1666508251348)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221023102524230.png)]$

删除第1题中所建立的索引。

代码：

-- 3. 删除第1题中所建立的索引。
DROP INDEX SupplierName_index ON supplier
SHOW INDEX FROM supplier;

效果图：

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

统计每个学生的消费金额：

代码：

-- 4.写出创建满足下述要求的视图的SQL语句
-- 1. 统计每个学生的消费金额。
-- 第一步查询所有学生的消费金额
SELECT st.SNO,st.SName,SUM(s.Number*g.SalePrice) 消费 FROM student st
LEFT JOIN salebill s ON st.SNO = s.SNO
LEFT JOIN goods g ON s.GoodsNO = g.GoodsNO
GROUP BY st.SNO,st.SName

-- 第二步根据上表创建视图
CREATE VIEW student_expense AS (
	SELECT st.SNO,st.SName,SUM(s.Number*g.SalePrice) 消费 FROM student st
	LEFT JOIN salebill s ON st.SNO = s.SNO
	LEFT JOIN goods g ON s.GoodsNO = g.GoodsNO
	GROUP BY st.SNO,st.SName
);
-- 查看视图结构
DESC student_expense;
-- 查看视图数据
SELECT * FROM student_expense;

效果图：

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

代码：

-- 2. 统计每个供货商提供的商品种类（一个商品编号代表一种）
-- 第一步查goods表按供应商分组统计Cno列
SELECT sp.SupplierNO,sp.SupplierName,COUNT(g.GoodsNO) 商品种类数量 FROM supplier sp
LEFT JOIN goods g ON sp.SupplierNO = g.SupplierNO
GROUP BY sp.SupplierNO,SupplierName;

-- 第二步创建视图
CREATE VIEW goods_type AS (
	SELECT sp.SupplierNO,sp.SupplierName,COUNT(g.GoodsNO) 商品种类数量 FROM supplier sp
	LEFT JOIN goods g ON sp.SupplierNO = g.SupplierNO
	GROUP BY sp.SupplierNO,SupplierName
)

DESC goods_type;
SELECT * FROM goods_type;

- 效果图： 在这里插入图片描述

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

代码：

-- 3. 统计各商品种类的销售数量及平均售价。
-- 销售数量来自goods表的Number
-- 查询
SELECT c.CategoryNO,c.CategoryName,SUM(g.Number) num,g.SalePrice,AVG(g.SalePrice) avg FROM category c 
LEFT JOIN goods g ON c.CategoryNO = g.CategoryNO
GROUP BY c.CategoryNO,c.CategoryName;

-- 创建视图
CREATE VIEW goods_sale AS (
	SELECT c.CategoryNO,c.CategoryName,SUM(g.Number) num,g.SalePrice,AVG(g.SalePrice) avg FROM category c 
	LEFT JOIN goods g ON c.CategoryNO = g.CategoryNO
	GROUP BY c.CategoryNO,c.CategoryName
)
DESC goods_sale;
SELECT * FROM goods_sale;

效果图： $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-L4N1lm0c-1666508251349)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221023111149005.png)]$

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

代码：

-- 4. 建立Sup001供货商的商品信息视图，并要求通过视图完成修改与插入操作时视图仍只有Sup001供货商的商品。
-- 查出Sup001供货的所有商品的信息
SELECT sp.SupplierNO,sp.SupplierName,g.GoodsNO,c.CategoryNO,g.GoodsName,g.SalePrice,s.Number,s.SNO FROM supplier sp
LEFT JOIN goods g ON sp.SupplierNO = g.SupplierNO
LEFT JOIN salebill s ON s.GoodsNO = g.GoodsNO
LEFT JOIN category c ON c.CategoryNO = g.CategoryNO
WHERE sp.SupplierNO = 'Sup001'

CREATE VIEW sp01 AS (
	SELECT sp.SupplierNO,sp.SupplierName,g.GoodsNO,c.CategoryNO,g.GoodsName,g.SalePrice,s.Number,s.SNO FROM supplier sp
LEFT JOIN goods g ON sp.SupplierNO = g.SupplierNO
LEFT JOIN salebill s ON s.GoodsNO = g.GoodsNO
LEFT JOIN category c ON c.CategoryNO = g.CategoryNO
WHERE sp.SupplierNO = 'Sup001'
);

SELECT * FROM sp01;

效果图： $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1dPjdnVG-1666508251350)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221023113806160.png)]$

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

统计每个MIS专业学生的消费金额。

代码：

--    1. 统计每个MIS专业学生的消费金额。
-- 方案一：按照上述视图查student_expense
-- 求得每个学生的消费金额
SELECT * FROM student_expense se
LEFT JOIN student st ON se.SNO = st.SNO;
-- 求得MIS专业每个学生的消费金额
SELECT st.Major,st.SName,se.`消费` FROM student_expense se
LEFT JOIN student st ON se.SNO = st.SNO
WHERE st.Major = 'MIS';
-- 统计
SELECT st.Major,SUM(se.`消费`) sum_se FROM student_expense se
LEFT JOIN student st ON se.SNO = st.SNO
WHERE st.Major = 'MIS';

效果图：

查询售价低于该商品种类售价平均价的商品名和售价。

- 代码：

--    2. 查询售价低于该商品种类售价平均价的商品名和售价。
-- goods表连视图goods_sale
SELECT g.GoodsName,g.SalePrice FROM goods g
LEFT JOIN goods_sale gs ON g.CategoryNO = gs.CategoryNO
WHERE g.SalePrice < gs.avg;

- 效果图： 在这里插入图片描述

利用第4题(4)中的视图插人供货商Sup002的商品信息，结果如何？为什么？
- 插入失败，因为前面创建视图的时候规定了该视图在修改与插入的操作时，视图仍只有Sup001供货商的商品
利用第4题(4)中的视图删除GN0004的商品信息，结果如何？为什么？
- 删除成功，因为前面创建视图的时候规定了该视图在修改与插入的操作时，视图仍只有Sup001供货商的商品，但是对删除的权限没有规定，而且视图内有GN004的商品信息

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

- 代码：

--    5. 查询供货种类大于等于2的供货商的名称及数量.
-- 利用4.2创建的视图进行查询
SELECT SupplierName,商品种类数量 FROM goods_type WHERE 商品种类数量 >= 2;

- 效果图： $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EX1l2v7I-1666508251352)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221023143755294.png)]$

四、实验问题及解决方法

第一题创建的非聚集性、唯一索引，由于我的MySQL使用的InnoDB引擎，所有创建的索引都是以b+树的方式存储的，叶子节点内存放的索引加数据，只能是聚集性的，如果要创建非聚集性的索引，需要将存储引擎更换至MyISAM
第4.4题的要求，通过视图完成修改与插入操作时视图仍只有Sup001供货商的商品，由于MySQL的视图和权限是分离的不能直接在创建视图的时候就赋予用户增删查改的权限，所以只能先创建用户，再给用户赋予相对应的权限：

使用系统存储过程Sp_helpindex查看表supplier的索引情况，使用了MySQL的show index from 表名代替

-- 例如
-- 创建用户qxy
create user 'qxt'@'%' identified by 'root';
-- 设置密码
set password for qxy@'%' = 'qxyPwd';
-- 刷新
flush privileges;
-- 授予权限
-- 不给用户赋予增加和修改的权限，保证了视图内的数据不会被修改和新增
grant select,delete,create on `supermarket`.`user` to 'qxy'@'%';