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

实验3-3 索引和视图

一、实验目的与要求

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

二、实验内容

1. 实验平台：
   • 操作系统：windows11
   • 数据库：MySQL8
   • 数据库管理平台：Navicat Premium 15
2. 题目：在数据库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的供货商的名称及数量.

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

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

   • 代码：

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

   • 效果图：

2. ---

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

   • 代码：

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

   • 效果图：

3. ---

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

   • 代码：

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

   • 效果图：

4. ---

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

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

      • 代码：

        -- 4.写出创建满足下述要求的视图的SQL语句-- 1. 统计每个学生的消费金额。-- 第一步查询所有学生的消费金额SELECT st.SNO,st.SName,SUM(s.Number*g.SalePrice) 消费 FROM student stLEFT JOIN salebill s ON st.SNO = s.SNOLEFT JOIN goods g ON s.GoodsNO = g.GoodsNOGROUP BY st.SNO,st.SName-- 第二步根据上表创建视图CREATE VIEW student_expense AS (SELECT st.SNO,st.SName,SUM(s.Number*g.SalePrice) 消费 FROM student stLEFT JOIN salebill s ON st.SNO = s.SNOLEFT JOIN goods g ON s.GoodsNO = g.GoodsNOGROUP BY st.SNO,st.SName);-- 查看视图结构DESC student_expense;-- 查看视图数据SELECT * FROM student_expense;

      • 效果图：

   2. ---

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

      • 代码：

        -- 2. 统计每个供货商提供的商品种类（一个商品编号代表一种）-- 第一步查goods表按供应商分组统计Cno列SELECT sp.SupplierNO,sp.SupplierName,COUNT(g.GoodsNO) 商品种类数量 FROM supplier spLEFT JOIN goods g ON sp.SupplierNO = g.SupplierNOGROUP BY sp.SupplierNO,SupplierName;-- 第二步创建视图CREATE VIEW goods_type AS (SELECT sp.SupplierNO,sp.SupplierName,COUNT(g.GoodsNO) 商品种类数量 FROM supplier spLEFT JOIN goods g ON sp.SupplierNO = g.SupplierNOGROUP BY sp.SupplierNO,SupplierName)DESC goods_type;SELECT * FROM goods_type;

      - 效果图：

   3. ---

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

      • 代码：

        -- 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.CategoryNOGROUP 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.CategoryNOGROUP BY c.CategoryNO,c.CategoryName)DESC goods_sale;SELECT * FROM goods_sale;

      • 效果图：

   4. ---

      建立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 spLEFT JOIN goods g ON sp.SupplierNO = g.SupplierNOLEFT JOIN salebill s ON s.GoodsNO = g.GoodsNOLEFT JOIN category c ON c.CategoryNO = g.CategoryNOWHERE 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 spLEFT JOIN goods g ON sp.SupplierNO = g.SupplierNOLEFT JOIN salebill s ON s.GoodsNO = g.GoodsNOLEFT JOIN category c ON c.CategoryNO = g.CategoryNOWHERE sp.SupplierNO = 'Sup001');SELECT * FROM sp01;

      • 效果图：

   5. ---

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

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

         • 代码：

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

         • 效果图：
           
           

      2. ---

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

         - 代码：

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

         - 效果图：

      3. ---

         利用第4题(4)中的视图插人供货商Sup002的商品信息，结果如何？为什么？

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

      4. ---

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

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

      5. ---

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

         - 代码：

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

         - 效果图：

四、实验问题及解决方法

1. 第一题创建的非聚集性、唯一索引，由于我的MySQL使用的InnoDB引擎，所有创建的索引都是以b+树的方式存储的，叶子节点内存放的索引加数据，只能是聚集性的，如果要创建非聚集性的索引，需要将存储引擎更换至MyISAM

2. 第4.4题的要求，通过视图完成修改与插入操作时视图仍只有Sup001供货商的商品，由于MySQL的视图和权限是分离的不能直接在创建视图的时候就赋予用户增删查改的权限，所以只能先创建用户，再给用户赋予相对应的权限：

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

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

五、实验总结

1. 综合考查对索引的存储结构的认识，聚簇索引和非聚簇索引的区别
2. 综合考查的对索引的创建、删除操作的能力
3. 综合考查的对视图的创建、删除、修改、使用视图进行查询的能力

六、资源

数据库文件:>
supermarket.sql
https://www.aliyundrive.com/s/hmXsCqZJHN3
提取码: 4rx3
点击链接保存，或者复制本段内容，打开「阿里云盘」APP ，无需下载极速在线查看，视频原画倍速播放。

来源地址：https://blog.csdn.net/qq_45659753/article/details/127474989