数据库原理及应用-李唯唯主编-实验四-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。

相关文章

MySQL数据库约束深入详解

MySQL数据库约束深入详解

《MySQL数据库约束深入详解》：本文主要介绍MySQL数据库约束,在MySQL数据库中,约束是用来限制进入表中的数据类型的一种技术,通过使用约束,可以确保数据的准确性、完整性和可靠性,需要的朋友... 目录一、数据库约束的概念二、约束类型三、NOT NULL 非空约束四、DEFAULT 默认值约束五、UN
阅读更多...
MySQL 多表连接操作方法(INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN)

MySQL 多表连接操作方法(INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN)

《MySQL多表连接操作方法(INNERJOIN、LEFTJOIN、RIGHTJOIN、FULLOUTERJOIN)》多表连接是一种将两个或多个表中的数据组合在一起的SQL操作,通过连接,... 目录一、 什么是多表连接？二、 mysql 支持的连接类型三、 多表连接的语法四、实战示例 数据准备五、连接的性
阅读更多...
MySQL中的分组和多表连接详解

MySQL中的分组和多表连接详解

《MySQL中的分组和多表连接详解》：本文主要介绍MySQL中的分组和多表连接的相关操作,本文通过实例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录mysql中的分组和多表连接一、MySQL的分组（group javascriptby ）二、多表连接（表连接会产生大量的数据垃圾）MySQL中的
阅读更多...
redis中使用lua脚本的原理与基本使用详解

redis中使用lua脚本的原理与基本使用详解

《redis中使用lua脚本的原理与基本使用详解》在Redis中使用Lua脚本可以实现原子性操作、减少网络开销以及提高执行效率,下面小编就来和大家详细介绍一下在redis中使用lua脚本的原理... 目录Redis 执行 Lua 脚本的原理基本使用方法使用EVAL命令执行 Lua 脚本使用EVALSHA命令
阅读更多...
Java并发编程之如何优雅关闭钩子Shutdown Hook

Java并发编程之如何优雅关闭钩子Shutdown Hook

《Java并发编程之如何优雅关闭钩子ShutdownHook》这篇文章主要为大家详细介绍了Java如何实现优雅关闭钩子ShutdownHook,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起... 目录关闭钩子简介关闭钩子应用场景数据库连接实战演示使用关闭钩子的注意事项开源框架中的关闭钩子机制1.
阅读更多...
Java Spring 中 @PostConstruct 注解使用原理及常见场景

Java Spring 中 @PostConstruct 注解使用原理及常见场景

《JavaSpring中@PostConstruct注解使用原理及常见场景》在JavaSpring中,@PostConstruct注解是一个非常实用的功能,它允许开发者在Spring容器完全初... 目录一、@PostConstruct 注解概述二、@PostConstruct 注解的基本使用2.1 基本代
阅读更多...
MySQL 中的 JSON 查询案例详解

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》：本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J
阅读更多...
Golang HashMap实现原理解析

Golang HashMap实现原理解析

《GolangHashMap实现原理解析》HashMap是一种基于哈希表实现的键值对存储结构,它通过哈希函数将键映射到数组的索引位置,支持高效的插入、查找和删除操作,：本文主要介绍GolangH... 目录HashMap是一种基于哈希表实现的键值对存储结构，它通过哈希函数将键映射到数组的索引位置，支持
阅读更多...
C语言中位操作的实际应用举例

C语言中位操作的实际应用举例

《C语言中位操作的实际应用举例》：本文主要介绍C语言中位操作的实际应用,总结了位操作的使用场景,并指出了需要注意的问题,如可读性、平台依赖性和溢出风险,文中通过代码介绍的非常详细,需要的朋友可以参... 目录1. 嵌入式系统与硬件寄存器操作2. 网络协议解析3. 图像处理与颜色编码4. 高效处理布尔标志集合
阅读更多...
Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》：本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1：以跳过权限验证模式启动 mysql 并重置密码方法 2：使用 my.ini 文件的临时配置在 Wi
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2