数据库原理及应用-李唯唯主编-实验四-transact-SQL 编程

2023-10-14 02:20
文章标签 sql 应用 原理 数据库 编程 实验 database transact 主编 唯唯

本文主要是介绍数据库原理及应用-李唯唯主编-实验四-transact-SQL 编程，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

实验四-transact-SQL 编程

记得点赞收藏哦

一、实验目的与要求

  1. 掌握用户自定义类型的使用

  2. 掌握变量的分类及其使用。

  3. 掌握各种流程控制语句的使用。

  4. 掌握游标的使用。

  5. 掌握系统存储过程及自定义存储过程的使用。

  6. 掌握系统函数及用户自定义函数的使用。

  7. 掌握触发器的创建和使用

二、实验内容

  1. 实验平台：
    • 操作系统：windows11
    • 数据库：MySQL8
    • 数据库管理平台：Navicat Premium 15
  2. 题目：在数据库supermarket数据库的基础上完成下列实验：
    1. 自定义数据类型GoodID_type，用于描述商品的编号
    2. 在supermarket数据库中创建表good，表结构与goods类似，而GoodsNO的数是类型为自定义数据类型GoodID_type
    3. 创建一个局部变量goods_type，并在SELECT语句中使用该变量查找商品表中有毛巾类商品的名称和售价
    4. 判断商品表goods是否存在商品类型为 “白酒” 的商品，如果存在则显示该类到有商品信息，否则显示无此类商品
    5. 如果商品表goods中存在商品数量小于10的情况，则将所有商品数量增加10，反复执行直到所有商品的数量都不小于10为止
    6. 声明一个游标，用于对 “饼干” 类商品的售价降价5%
    7. 创建一个有输入参数的存储过程，用于查询指定类别的所有商品信息。并执行该存储过程
    8. 创建一个有输入输出参数的存储过程，用于查询指定商品名的售价。并执行试存过程。
    9. 创建自定义函数，用于统计销售表salebill中某段时间内的销售情况。并调用该函数输出执行结果。
    10. 创建自定义函数，用于显示商品表goods中售价大于指定价格的商品信息，并调用该函数输出执行结果。
    11. 创建一个触发器，向销售表salebill中插人一条记录时，这个触发器将更新商品表goods。goods表中数量为原有数量减去销售数量，如果库存数量小于10，则提示 “该商品数量小于10，低于安全库存量，请及时进货物” ；如果原有数量不足，则提示 “数量不足！”

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

实验开始前先新增三个商品种类：毛巾、白酒、饼干；再新增若干个对应种类的商品

INSERT INTO category VALUES('CN004','毛巾','洗脸巾、浴巾');
INSERT INTO category VALUES('CN005','白酒','浓香型、酱香型、清香型');
INSERT INTO category VALUES('CN006','饼干','苏打饼干、曲奇饼干、威化饼干');INSERT INTO goods VALUES ('GN0011','Sup001','CN004','洁丽雅Grace毛巾',5.20,10.25,20,NOW(),18);
INSERT INTO goods VALUES ('GN0012','Sup002','CN004','三利毛巾',7.30,10.25,25,NOW(),18);
INSERT INTO goods VALUES ('GN0013','Sup001','CN004','全棉时代毛巾',4.70,10.25,16,NOW(),18);
INSERT INTO goods VALUES ('GN0014','Sup003','CN005','茅台',1888.88,3888.88,10,NOW(),36);
INSERT INTO goods VALUES ('GN0015','Sup003','CN005','剑南春',799.90,1799.99,8,NOW(),36);
INSERT INTO goods VALUES ('GN0016','Sup002','CN005','四特酒',366.60,666.66,6,NOW(),36);
INSERT INTO goods VALUES ('GN0017','Sup002','CN006','奥利奥',5.80,12.00,60,NOW(),18);
INSERT INTO goods VALUES ('GN0018','Sup001','CN006','好吃点',3.20,5.50,40,NOW(),18);
INSERT INTO goods VALUES ('GN0019','Sup001','CN006','趣多多',6.60,10.00,30,NOW(),18);

在这里插入图片描述
在这里插入图片描述

  1. 自定义数据类型GoodID_type,用于描述商品的编号

    • 代码：

      -- 1. 自定义数据类型GoodID_type，用于描述商品的编号
#思路分析: 先查看goods表内商品编号的类型，再创建自定义数据类型#查看goods表结构
DESC goods;#创建自定义数据类型
CREATE type GoodID_type VARCHAR(20) NOT NULL;#创建失败的原因-MySQL暂不支持自定义数据类型

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

  2. 在supermarket数据库中创建表good，表结构与goods类似，而GoodsNO的数是类型为自定义数据类型GoodID_type

    • 代码：

      -- 2. 在supermarket数据库中创建表good，表结构与goods类似，而GoodsNO的数是类型为自定义数据类型GoodID_type
#由于第一题可知MySQL不能自定义数据类型,但是我们可以查看原来goods表内GoodsNO的数据类型,使用其代替
#思路分析: 查看goods表的结构,创建表good#查看goods表结构
DESC goods;#创建表good
CREATE TABLE good LIKE supermarker.goods;#查看新表good结构
DESC good;

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

  3. 创建一个局部变量goods_type，并在SELECT语句中使用该变量查找商品表中有毛巾类商品的名称和售价

    • 代码：

      -- 3. 创建一个局部变量goods_type，并在SELECT语句中使用该变量查找商品表中有毛巾类商品的名称和售价
#思路分析:创建一个存储过程,存储过程内创建局部变量,给局部变量赋值为毛巾,查看毛巾category表内种类名为毛巾的种类No,再查goods表
DELIMITER //
CREATE PROCEDURE getGoodsNameAndSalePrice()
BEGIN#声明局部变量并赋值DECLARE goods_type VARCHAR(20);SET goods_type = '毛巾';SELECT GoodsName 商品名,SalePrice 售价 FROM goods WHERE CategoryNO IN(SELECT CategoryNO FROM category WHERE CategoryName LIKE goods_type);
END //
DELIMITER;
#调用存储过程
CALL getGoodsNameAndSalePrice();

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

  4. 判断商品表goods是否存在商品类型为 “白酒” 的商品，如果存在则显示该类到有商品信息，否则显示无此类商品

    • 代码：

      -- 4. 判断商品表goods是否存在商品类型为 "白酒" 的商品，如果存在则显示该类到有商品信息，否则显示无此类商品
#思路分析:使用存储过程解决,查询商品类型为白酒的种类编号,再在商品表使用子查询查找是否有对应的商品
DELIMITER //
CREATE PROCEDURE getInfo(IN cname VARCHAR(20))
BEGIN#创建局部变量记录白酒类商品的个数DECLARE total INT DEFAULT 0;#给total数赋值SELECT COUNT(*) INTO total FROM goods WHERE CategoryNO IN(SELECT CategoryNO FROM category WHERE CategoryName LIKE cname);#使用if语句IF total > 0THEN SELECT * FROM goods WHERE CategoryNO IN(SELECT CategoryNO FROM category WHERE CategoryName LIKE cname);ELSESELECT '无此类商品';END IF;	
END //
DELIMITER;#调用存储过程
CALL getInfo('白酒');
CALL getInfo('可乐');

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

  5. 如果商品表goods中存在商品数量小于10的情况，则将所有商品数量增加10，反复执行直到所有商品的数量都不小于10为止

    • 代码：

      -- 5. 如果商品表goods中存在商品数量小于10的情况，则将所有商品数量增加10，反复执行直到所有商品的数量都不小于10为止
#思路分析:使用存储过程解决,传入inout参数,返回增加的次数,存储过程内使用while循环,每次循环查找最小的数量min(Number)赋值给局部变量和10比较
DELIMITER //
CREATE PROCEDURE counts(INOUT num INT)
BEGIN#创建局部变量统计增加次数DECLARE sum INT DEFAULT 0;#创建局部变量得到每次最低的数量DECLARE currNumber INT;#查询最低的数量赋值给currNumberSELECT MIN(Number) INTO currNumber FROM goods;WHILE currNumber < 10 DO#更新数据,所有商品数量增加10UPDATE goods SET Number = Number + 10;#计数器加1SET sum = sum + 1;#重新得到最低数量比较SELECT MIN(Number) INTO currNumber FROM goods;END WHILE;#循环结束给num赋值后退出SET num = sum;
END //
DELIMITER;#调用存储过程
#查看原来表内的Number
SELECT * FROM goods;SET @num = 10;
CALL counts(@num);
#查看调用存储过程后的表内Number
SELECT * FROM goods;
#查看增加次数
SELECT @num;
#再次调用并查看
CALL counts(@num);
SELECT @num;

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

  6. 声明一个游标，用于对 “饼干” 类商品的售价降价5%

    • 代码：

      -- 6. 声明一个游标，用于对 "饼干" 类商品的售价降价5%
#思路分析:传入商品种类类型名为饼干,返回受影响的行数,使用游标和循环搭配
CREATE PROCEDURE decrementBiscuitSalePrice(IN cname VARCHAR(20),OUT num INT)
BEGIN#声明局部变量统计受影响的行数DECLARE sum INT DEFAULT 0;#声明局部变量记录当前商品的编号DECLARE curr VARCHAR(20);#声明局部变量记录饼干类型商品的个数DECLARE total INT;#1.声明游标goods_cursorDECLARE goods_cursor CURSOR FOR SELECT GoodsNO FROM goods WHERE CategoryNO IN(SELECT CategoryNO FROM category WHERE CategoryName LIKE cname);#给局部变量赋值SELECT COUNT(*) INTO total FROM goods WHERE CategoryNO IN(SELECT CategoryNO FROM category WHERE CategoryName LIKE cname);#2.打开游标OPEN goods_cursor;#3.使用游标WHILE total > 0 DO#游标下移FETCH goods_cursor INTO curr;#更新数据UPDATE goods SET SalePrice = SalePrice - SalePrice*0.05 WHERE GoodsNO LIKE curr;#计数器加1SET sum = sum + 1;#剩余饼干类型商品个数-1SET total = total - 1;END WHILE;#4.关闭游标CLOSE goods_cursor;#返回受影响个数SET num = sum;
END;
#调用存储过程
CALL decrementBiscuitSalePrice('饼干',@num);
#查看受影响个数
SELECT @num;

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

  7. 创建一个有输入参数的存储过程，用于查询指定类别的所有商品信息。并执行该存储过程

    • 代码：

      -- 7. 创建一个有输入参数的存储过程，用于查询指定类别的所有商品信息。并执行该存储过程
#思路分析:通过种类名查询到的种类编号和goods使用子查询,二者搭配
CREATE PROCEDURE getTypeInfo(IN ename VARCHAR(20))
BEGINSELECT * FROM goods WHERE CategoryNO IN(SELECT CategoryNO FROM category WHERE CategoryName LIKE ename);
END;#调用存储过程
CALL getTypeInfo('白酒');
CALL getTypeInfo('饼干');
CALL getTypeInfo('咖啡');

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

  8. 创建一个有输入输出参数的存储过程，用于查询指定商品名的售价。并执行试存过程。

    • 代码：

      -- 8. 创建一个有输入输出参数的存储过程，用于查询指定商品名的售价。并执行试存过程。
CREATE PROCEDURE getSalePriceByGoodsName(IN gname VARCHAR(20),OUT price DOUBLE)
BEGIN#查询并赋值SELECT SalePrice INTO price FROM goods WHERE GoodsName = gname;
END;#调用存储过程,进行测试
CALL getSalePriceByGoodsName('四特酒',@price);
SELECT @price;

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

  9. 创建自定义函数，用于统计销售表salebill中某段时间内的销售情况。并调用该函数输出执行结果。

    • 代码：

      -- 9. 创建自定义函数，用于统计销售表salebill中某段时间内的销售情况。并调用该函数输出执行结果。
#思路分析:传入两个时间,查询salebill表内时间在两个时刻内的销售数量,并返回
CREATE FUNCTION getSalebillInfo(pre DATE,last DATE)
RETURNS INT
BEGINRETURN(SELECT SUM(Number) FROM salebill WHERE HappenTime >= pre AND HappenTime <= last);
END;
#调用函数,测试2018年4月初到5月底的销售情况
SELECT getSalebillInfo('2018-04-01','2018-05-31');

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

  10. 创建自定义函数，用于显示商品表goods中售价大于指定价格的商品信息，并调用该函数输出执行结果。

  • 代码：

    #创建存储函数传入指定价格，返回大于此价格的商品信息
create function showPrice(@price float)
returns @tb table(gno varchar(20),sno varchar(20),cno varchar(20),gname varchar(20),inprice float,outprice float,num int,protime datetime,qgperiod INT
)
as begin insert into @tbselect * from Goods where SalePrice > @pricereturn
end
#测试商品售价大于100的
select * from showPrice(100);

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

  • 代码：

    -- 10. 创建自定义函数，用于显示商品表goods中售价大于指定价格的商品信息，并调用该函数输出执行结果。
CREATE PROCEDURE showPlus(price DOUBLE)
BEGINSELECT * FROM goods WHERE goods.SalePrice > price;
END;CALL showPlus(1000);

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

  1. 创建一个触发器，向销售表salebill中插人一条记录时，这个触发器将更新商品表goods。goods表中数量为原有数量减去销售数量，如果库存数量小于10，则提示 “该商品数量小于10，低于安全库存量，请及时进货物” ；如果原有数量不足，则提示 "数量不足！"

    • 代码：

      -- 11. 创建一个触发器，向销售表salebill中插入一条记录时，这个触发器将更新商品表goods。goods表中数量为原有数量减去销售数量，如果库存数量小于10，则提示 "该商品数量小于10，低于安全库存量，请及时进货物" ；如果原有数量不足，则提示 "数量不足！"
#思路分析:通过存储过程和触发器解决,创建存储过程insertSaleBill传入需要插入的参数,再在存储过程内判断插入的参数和goods表内的库存的差值
#条件1.如果差值>=10,则新增一条数据到sablebill表同时修改goods表内的库存
#条件2.如果差值>= 0 && < 10,则新增一条数据到sablebill表,同时修改goods表内的库存,再提示一句话-该商品数量小于10，低于安全库存量，请及时进货物
#条件3.如果差值< 0,则直接提示一句话-数量不足#创建存储过程insertSaleBill
CREATE PROCEDURE insertSaleBill(IN gno VARCHAR(20),IN sno VARCHAR(20),IN time datetime,IN num INT)
BEGIN#声明临时变量获取goods表内的库存DECLARE gnum INT;#声明临时变量获取goods表内的库存和传入参数的商品数量的差值DECLARE dvalue INT;#查询传入商品编号对应goods表内商品库存并赋值给临时变量SELECT Number INTO gnum FROM goods WHERE GoodsNO = gno;SET dvalue = gnum - num;#如果差值大于等10则执行触发器更新IF dvalue >= 10THEN INSERT INTO salebill VALUES(gno,sno,time,num);ELSEIF dvalue >= 0 AND dvalue < 10THEN INSERT INTO salebill VALUES(gno,sno,time,num);SELECT '该商品数量小于10，低于安全库存量，请及时进货物';ELSESELECT '数量不足';END IF;		
END;#创建触发器,在满足插入条件后插入数据后更新另外一张表的数据
CREATE TRIGGER test1 
AFTER INSERT ON salebill FOR EACH ROW
BEGIN#声明局部变量获取sale表内最新的销售记录的数量DECLARE snum INT;#声明局部变量获取最新添加数据的记录对应的商品编号DECLARE gno VARCHAR(20);#给局部变量赋值SELECT GoodsNO INTO gno FROM salebill ORDER BY HappenTime DESC LIMIT 1;SELECT Number INTO snum FROM salebill ORDER BY HappenTime DESC LIMIT 1;#更新数据UPDATE goods SET Number = Number - snum WHERE GoodsNO = gno; END;#测试商品GN0019的，原库存40
#1.测试条件1-预期结果插入成功，goods表库存减5,剩余35
CALL insertSaleBill('GN0019','S01',NOW(),5);
#2.测试条件2-预期结果插入成功，goods表库存减35,剩余0,提示及时进货
CALL insertSaleBill('GN0019','S02',NOW(),35);
#3.测试条件3-提示数量不足
CALL insertSaleBill('GN0019','S03',NOW(),1);

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

四、实验问题及解决方法

  1. 第一题创建自定义数据类型，由于MySQL暂不支持创建自定义数据类型，所以，无法创建，改用另一种方法：先查询goods表内用户编号的数据类型和范围大小，再创建对应的数据类型和范围大小去完成第二题。

  2. 第十题，由于MySQL暂不支持自定义函数返回多个返回值，和返回临时表，改用存储过程完成了此题；现在猜想还可以使用其它两种方式实现，但是由于复杂还未测试过，第一种使用字符串拼接，然后返回次字符串，第二种创建多个自定义函数，每个函数返回一个列值，然后统一select调用函数

  3. 第十一题首次测试，测试条件一并没有成功，因为存储过程的传入的时间参数错误，原传入为date，使用orderby排序，同一天的都一样，所以结果错误，后改为了datetime类型，测试结果全部通过。

  4. 第十题，后使用SQL server完成，代码如下：

    #创建存储函数传入指定价格，返回大于此价格的商品信息
create function showPrice(@price float)
returns @tb table(gno varchar(20),sno varchar(20),cno varchar(20),gname varchar(20),inprice float,outprice float,num int,protime datetime,qgperiod INT
)
as begin insert into @tbselect * from Goods where SalePrice > @pricereturn
end
#测试商品售价大于100的
select * from showPrice(100);

五、实验总结

  1. 综合考查了学生对自定义数据类型、变量的声明、赋值和使用的运用
  2. 综合考查了对流程控制语句、循环结构的搭配使用
  3. 综合考查了对游标的创建、使用、和关闭的运用
  4. 综合考查了对存储过程、存储函数的使用区别，和其与上述知识点的合理搭配运用
  5. 综合考查了对触发器创建和与存储过程、存储函数、流程控制语句、以及基本数据库DML、DQL语句的合理搭配运用

这篇关于数据库原理及应用-李唯唯主编-实验四-transact-SQL 编程的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/207460。

相关文章

Spring Security基于数据库验证流程详解

Spring Security基于数据库验证流程详解

Spring Security 校验流程图 相关解释说明（认真看哦） AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证，则会调用 #attemptAuthentica
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
中文分词jieba库的使用与实景应用（一）

中文分词jieba库的使用与实景应用（一）

知识星球：https://articles.zsxq.com/id_fxvgc803qmr2.html 目录 一.定义： 精确模式（默认模式）： 全模式： 搜索引擎模式： paddle 模式（基于深度学习的分词模式）： 二 自定义词典 三.文本解析   调整词出现的频率 四. 关键词提取 A. 基于TF-IDF算法的关键词提取 B. 基于TextRank算法的关键词提取
阅读更多...
水位雨量在线监测系统概述及应用介绍

水位雨量在线监测系统概述及应用介绍

在当今社会，随着科技的飞速发展，各种智能监测系统已成为保障公共安全、促进资源管理和环境保护的重要工具。其中，水位雨量在线监测系统作为自然灾害预警、水资源管理及水利工程运行的关键技术，其重要性不言而喻。 一、水位雨量在线监测系统的基本原理 水位雨量在线监测系统主要由数据采集单元、数据传输网络、数据处理中心及用户终端四大部分构成，形成了一个完整的闭环系统。 数据采集单元：这是系统的“眼睛”，
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
深入探索协同过滤：从原理到推荐模块案例

深入探索协同过滤：从原理到推荐模块案例

文章目录 前言一、协同过滤1. 基于用户的协同过滤（UserCF）2. 基于物品的协同过滤（ItemCF）3. 相似度计算方法 二、相似度计算方法1. 欧氏距离2. 皮尔逊相关系数3. 杰卡德相似系数4. 余弦相似度 三、推荐模块案例1.基于文章的协同过滤推荐功能2.基于用户的协同过滤推荐功能 前言     在信息过载的时代，推荐系统成为连接用户与内容的桥梁。本文聚焦于
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
csu 1446 Problem J Modified LCS （扩展欧几里得算法的简单应用）

csu 1446 Problem J Modified LCS （扩展欧几里得算法的简单应用）

这是一道扩展欧几里得算法的简单应用题，这题是在湖南多校训练赛中队友ac的一道题，在比赛之后请教了队友，然后自己把它a掉 这也是自己独自做扩展欧几里得算法的题目 题意：把题意转变下就变成了：求d1*x - d2*y = f2 - f1的解,很明显用exgcd来解 下面介绍一下exgcd的一些知识点：求ax + by = c的解 一、首先求ax + by = gcd(a,b)的解 这个
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2