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

相关文章

Java HashMap的底层实现原理深度解析

Java HashMap的底层实现原理深度解析

《JavaHashMap的底层实现原理深度解析》HashMap基于数组+链表+红黑树结构,通过哈希算法和扩容机制优化性能,负载因子与树化阈值平衡效率,是Java开发必备的高效数据结构,本文给大家介绍... 目录一、概述：HashMap的宏观结构二、核心数据结构解析1. 数组（桶数组）2. 链表节点（Node
阅读更多...
Java AOP面向切面编程的概念和实现方式

Java AOP面向切面编程的概念和实现方式

《JavaAOP面向切面编程的概念和实现方式》AOP是面向切面编程,通过动态代理将横切关注点（如日志、事务）与核心业务逻辑分离,提升代码复用性和可维护性,本文给大家介绍JavaAOP面向切面编程的概... 目录一、AOP 是什么？二、AOP 的核心概念与实现方式核心概念实现方式三、Spring AOP 的关
阅读更多...
深入理解Mysql OnlineDDL的算法

深入理解Mysql OnlineDDL的算法

《深入理解MysqlOnlineDDL的算法》本文主要介绍了讲解MysqlOnlineDDL的算法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小... 目录一、Online DDL 是什么？二、Online DDL 的三种主要算法2.1COPY（复制法）
阅读更多...
Redis中Hash从使用过程到原理说明

Redis中Hash从使用过程到原理说明

《Redis中Hash从使用过程到原理说明》RedisHash结构用于存储字段-值对,适合对象数据,支持HSET、HGET等命令,采用ziplist或hashtable编码,通过渐进式rehash优化... 目录一、开篇：Hash就像超市的货架二、Hash的基本使用1. 常用命令示例2. Java操作示例三
阅读更多...
Redis中Set结构使用过程与原理说明

Redis中Set结构使用过程与原理说明

《Redis中Set结构使用过程与原理说明》本文解析了RedisSet数据结构,涵盖其基本操作（如添加、查找）、集合运算（交并差）、底层实现（intset与hashtable自动切换机制）、典型应用场... 目录开篇：从购物车到Redis Set一、Redis Set的基本操作1.1 编程常用命令1.2 集
阅读更多...
Redis中的有序集合zset从使用到原理分析

Redis中的有序集合zset从使用到原理分析

《Redis中的有序集合zset从使用到原理分析》Redis有序集合(zset)是字符串与分值的有序映射,通过跳跃表和哈希表结合实现高效有序性管理,适用于排行榜、延迟队列等场景,其时间复杂度低,内存占... 目录开篇：排行榜背后的秘密一、zset的基本使用1.1 常用命令1.2 Java客户端示例二、zse
阅读更多...
mysql8.0.43使用InnoDB Cluster配置主从复制

mysql8.0.43使用InnoDB Cluster配置主从复制

《mysql8.0.43使用InnoDBCluster配置主从复制》本文主要介绍了mysql8.0.43使用InnoDBCluster配置主从复制,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录1、配置Hosts解析(所有服务器都要执行)2、安装mysql shell(所有服务器都要执行)3、
阅读更多...
Redis中的AOF原理及分析

Redis中的AOF原理及分析

《Redis中的AOF原理及分析》Redis的AOF通过记录所有写操作命令实现持久化,支持always/everysec/no三种同步策略,重写机制优化文件体积,与RDB结合可平衡数据安全与恢复效率... 目录开篇：从日记本到AOF一、AOF的基本执行流程1. 命令执行与记录2. AOF重写机制二、AOF的
阅读更多...
k8s中实现mysql主备过程详解

k8s中实现mysql主备过程详解

《k8s中实现mysql主备过程详解》文章讲解了在K8s中使用StatefulSet部署MySQL主备架构,包含NFS安装、storageClass配置、MySQL部署及同步检查步骤,确保主备数据一致... 目录一、k8s中实现mysql主备1.1 环境信息1.2 部署nfs-provisioner1.2.
阅读更多...
MySQL中VARCHAR和TEXT的区别小结

MySQL中VARCHAR和TEXT的区别小结

《MySQL中VARCHAR和TEXT的区别小结》MySQL中VARCHAR和TEXT用于存储字符串,VARCHAR可变长度存储在行内,适合短文本；TEXT存储在溢出页,适合大文本,下面就来具体的了解... 目录一、VARCHAR 和 TEXT 基本介绍1. VARCHAR2. TEXT二、VARCHAR
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2