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

相关文章

查询SQL Server数据库服务器IP地址的多种有效方法

查询SQL Server数据库服务器IP地址的多种有效方法

《查询SQLServer数据库服务器IP地址的多种有效方法》作为数据库管理员或开发人员,了解如何查询SQLServer数据库服务器的IP地址是一项重要技能,本文将介绍几种简单而有效的方法,帮助你轻松... 目录使用T-SQL查询方法1：使用系统函数方法2：使用系统视图使用SQL Server Configu
阅读更多...
SQL Server数据库迁移到MySQL的完整指南

SQL Server数据库迁移到MySQL的完整指南

《SQLServer数据库迁移到MySQL的完整指南》在企业应用开发中,数据库迁移是一个常见的需求,随着业务的发展,企业可能会从SQLServer转向MySQL,原因可能是成本、性能、跨平台兼容性等... 目录一、迁移前的准备工作1.1 确定迁移范围1.2 评估兼容性1.3 备份数据二、迁移工具的选择2.1
阅读更多...
MySQL 缓存机制与架构解析(最新推荐)

MySQL 缓存机制与架构解析(最新推荐)

《MySQL缓存机制与架构解析(最新推荐)》本文详细介绍了MySQL的缓存机制和整体架构,包括一级缓存（InnoDBBufferPool）和二级缓存（QueryCache）,文章还探讨了SQL... 目录一、mysql缓存机制概述二、MySQL整体架构三、SQL查询执行全流程四、MySQL 8.0为何移除查
阅读更多...
在Mysql环境下对数据进行增删改查的操作方法

在Mysql环境下对数据进行增删改查的操作方法

《在Mysql环境下对数据进行增删改查的操作方法》本文介绍了在MySQL环境下对数据进行增删改查的基本操作,包括插入数据、修改数据、删除数据、数据查询（基本查询、连接查询、聚合函数查询、子查询）等,并... 目录一、插入数据：二、修改数据：三、删除数据：1、delete from 表名;2、truncate
阅读更多...
MySQL的cpu使用率100%的问题排查流程

MySQL的cpu使用率100%的问题排查流程

《MySQL的cpu使用率100%的问题排查流程》线上mysql服务器经常性出现cpu使用率100%的告警,因此本文整理一下排查该问题的常规流程,文中通过代码示例讲解的非常详细,对大家的学习或工作有一... 目录1. 确认CPU占用来源2. 实时分析mysql活动3. 分析慢查询与执行计划4. 检查索引与表
阅读更多...
MySQL中的MVCC底层原理解读

MySQL中的MVCC底层原理解读

《MySQL中的MVCC底层原理解读》本文详细介绍了MySQL中的多版本并发控制（MVCC）机制,包括版本链、ReadView以及在不同事务隔离级别下MVCC的工作原理,通过一个具体的示例演示了在可重... 目录简介ReadView版本链演示过程总结简介MVCC（Multi-Version Concurr
阅读更多...
Python中连接不同数据库的方法总结

Python中连接不同数据库的方法总结

《Python中连接不同数据库的方法总结》在数据驱动的现代应用开发中,Python凭借其丰富的库和强大的生态系统,成为连接各种数据库的理想编程语言,下面我们就来看看如何使用Python实现连接常用的几... 目录一、连接mysql数据库二、连接PostgreSQL数据库三、连接SQLite数据库四、连接Mo
阅读更多...
MySQL报错sql_mode=only_full_group_by的问题解决

MySQL报错sql_mode=only_full_group_by的问题解决

《MySQL报错sql_mode=only_full_group_by的问题解决》本文主要介绍了MySQL报错sql_mode=only_full_group_by的问题解决,文中通过示例代码介绍的非... 目录报错信息DataGrip 报错还原Navicat 报错还原报错原因解决方案查看当前 sql mo
阅读更多...
Oracle数据库如何切换登录用户(system和sys)

Oracle数据库如何切换登录用户(system和sys)

《Oracle数据库如何切换登录用户(system和sys)》文章介绍了如何使用SQL*Plus工具登录Oracle数据库的system用户,包括打开登录入口、输入用户名和口令、以及切换到sys用户的... 目录打开登录入口登录system用户总结打开登录入口win+R打开运行对话框，输php入：sqlp
阅读更多...
MYSQL关联关系查询方式

MYSQL关联关系查询方式

《MYSQL关联关系查询方式》文章详细介绍了MySQL中如何使用内连接和左外连接进行表的关联查询,并展示了如何选择列和使用别名,文章还提供了一些关于查询优化的建议,并鼓励读者参考和支持脚本之家... 目录mysql关联关系查询关联关系查询这个查询做了以下几件事MySQL自关联查询总结MYSQL关联关系查询
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2