实验3-4 数据更新
一、实验目的与要求
- 掌握插入数据、删除数据、修改数据
- 掌握实验子查询插入数据、更新数据
二、实验内容
-
实验平台:
- 操作系统:windows11
- 数据库:MySQL8
- 数据库管理平台:Navicat Premium 15
-
题目:在数据库supermarket数据库上完成下列操作
-
添加新品"GN0011 Sup002 CN001 乐至三合一咖啡 12.30 17.30 100 2018-11-12 18" 。
-
建立一张新表,使用子查询将各月的销售额插人该表,存储月份及销售额。
-
使用子查询将各学生的购买额插入新表,由系统自建新表,存储学生学号、姓名、销售额。
-
将所有商品存量增加2。
-
将保质期还有30天的商品价格打8折。
-
.分别使用子查询方式与连接方式将广州地区供货商的商品加价10%。
-
将销售额后两位的商品下架。
-
删除销售额最小的供应商信息。
-
三、实验过程、步骤及结果
-
添加新品"GN0011 Sup002 CN001 乐至三合一咖啡 12.30 17.30 100 2018-11-12 18" 。
-
代码:
-- 1.添加新品"GN0011 Sup002 CN001 乐至三合一咖啡 12.30 17.30 100 2018-11-12 18" 。 -- 方法一: SELECT * FROM goods; INSERT INTO goods VALUES('GN0011','Sup002','CN001','乐至三合一咖啡',12.30,17.30,100,'2018-11-12',18); SELECT * FROM goods; -- 方法二: DELETE FROM goods WHERE GoodsNO = 'GN0011'; SELECT * FROM goods; INSERT INTO goods (GoodsNO,SupplierNO,CategoryNO,GoodsName,InPrice,SalePrice,Number,ProductTime,QGPeriod) VALUES('GN0011','Sup002','CN001','乐至三合一咖啡',12.30,17.30,100,'2018-11-12',18); SELECT * FROM goods;
-
效果图:
-
-
-
建立一张新表,使用子查询将各月的销售额插人该表,存储月份及销售额。
-
代码:
-- 2.建立一张新表,使用子查询将各月的销售额插人该表,存储月份及销售额。 -- 求得各月的销售额 SELECT MONTH(s.HappenTime) month,SUM(g.SalePrice*s.Number) sale FROM goods g RIGHT JOIN salebill s ON g.GoodsNO = s.GoodsNO GROUP BY MONTH(s.HappenTime) ORDER BY MONTH(s.HappenTime) ASC -- 创建表 CREATE TABLE tmp( month INT, sale DOUBLE ); -- 插入数据 INSERT INTO tmp (month,sale) ( SELECT MONTH(s.HappenTime) month,SUM(g.SalePrice*s.Number) sale FROM goods g RIGHT JOIN salebill s ON g.GoodsNO = s.GoodsNO GROUP BY MONTH(s.HappenTime) ORDER BY MONTH(s.HappenTime) ASC ) SELECT * FROM tmp;
-
效果图:
-
-
使用子查询将各学生的购买额插入新表,由系统自建新表,存储学生学号、姓名、销售额。
-
代码:
-- 3.使用子查询将各学生的购买额插入新表,由系统自建新表,存储学生学号、姓名、销售额。 -- 查询各个学生的购买额 SELECT st.SNO,st.SName,SUM(s.Number*g.SalePrice) sale FROM student st LEFT JOIN salebill s ON st.SNO = s.SNO LEFT JOIN goods g ON s.GoodsNO = g.GoodsNO GROUP BY st.SNO,st.SName; -- 查询并且建表 CREATE table tmp2 (SELECT st.SNO,st.SName,SUM(s.Number*g.SalePrice) sale FROM student st LEFT JOIN salebill s ON st.SNO = s.SNO LEFT JOIN goods g ON s.GoodsNO = g.GoodsNO GROUP BY st.SNO,st.SName); -- 查看创建的表 SELECT * FROM tmp2;
-
效果图:
-
-
将所有商品存量增加2。
-
代码:
-- 4.将所有商品存量增加2。 -- 未添加前 SELECT * FROM goods; UPDATE goods SET Number = Number + 2; -- 添加后 SELECT * FROM goods;
-
效果图:
-
-
将保质期还有30天的商品价格打8折。
-
代码:
-- 5.将保质期还有30天的商品价格打8折。 -- 打折前 SELECT * FROM goods; -- 打折 UPDATE goods SET SalePrice = SalePrice*0.8 WHERE QGPeriod <= 30; -- 打折后 SELECT * FROM goods;
-
效果图:
-
-
分别使用子查询方式与连接方式将广州地区供货商的商品加价10%。
-
代码:
-- 6.分别使用子查询方式与连接方式将广州地区供货商的商品加价10%。 -- 方法一:子查询 -- 加价前查询广州地区供货商品的进价 SELECT * FROM goods WHERE SupplierNO IN( SELECT SupplierNO FROM supplier WHERE Address LIKE '广州%' ) -- 加价 UPDATE goods SET InPrice = (1+0.1)*InPrice WHERE SupplierNO IN( SELECT SupplierNO FROM supplier WHERE Address LIKE '广州%' ) SET g.InPrice = (1+0.1)*g.InPrice; ; -- 加价后查询广州地区供货商品的进价 -- 方法二:连接查询 -- 加价前,查询广州地区供货商品的进价 SELECT sp.SupplierName,g.GoodsName,g.InPrice,sp.Address FROM goods g LEFT JOIN supplier sp ON g.SupplierNO = sp.SupplierNO WHERE sp.Address LIKE '广州%'; -- 加价 UPDATE goods g LEFT JOIN supplier sp ON g.SupplierNO = sp.SupplierNO SET g.InPrice = (1+0.1)*g.InPrice; WHERE sp.Address LIKE '广州%'; -- 加价后,查询广州地区供货商品的进价 SELECT sp.SupplierName,g.GoodsName,g.InPrice,sp.Address FROM goods g LEFT JOIN supplier sp ON g.SupplierNO = sp.SupplierNO WHERE sp.Address LIKE '广州%';
-
效果图:
-
-
将销售额后两位的商品下架。
-
代码:
-- 7.将销售额后两位的商品下架。--》删除销售额最低的两件商品 -- 第一步 查询所有商品的销售额,找出销售额最低的两位商品的商品id,然后删除 SELECT DISTINCT g.GoodsNO,g.GoodsName,SUM(g.SalePrice*s.Number) sale FROM goods g LEFT JOIN salebill s ON g.GoodsNO = s.GoodsNO GROUP BY g.GoodsNO,g.GoodsName ORDER BY sale ASC LIMIT 0,2; -- 删除 DELETE FROM goods WHERE GoodsNO IN ( SELECT DISTINCT g.GoodsNO,g.GoodsName,SUM(g.SalePrice*s.Number) sale FROM goods g LEFT JOIN salebill s ON g.GoodsNO = s.GoodsNO GROUP BY g.GoodsNO,g.GoodsName ORDER BY sale ASC LIMIT 0,2); -- 删除后,查看goods表 SELECT * FROM goods;
-
效果图:
-
-
删除销售额最小的供应商信息。
-
代码:
-- 8.删除销售额最小的供应商信息。 -- 统计各个供应商的销售额 SELECT sp.SupplierNO FROM supplier sp LEFT JOIN goods g ON sp.SupplierNO = g.SupplierNO LEFT JOIN salebill s ON g.GoodsNO = s.GoodsNO GROUP BY sp.SupplierNO ORDER BY SUM(g.SalePrice*s.Number) ASC LIMIT 0,1; -- 删除 -- 由于由多个表引用了待删除供应商的信息作为外键约束,所以删除供应商信息前一个先删除引用外键的数据 -- 查询到该供应商的所有商品信息并删除,再删除供应商的信息 SELECT GoodsNO FROM goods WHERE SupplierNO = ( SELECT sp.SupplierNO FROM supplier sp LEFT JOIN goods g ON sp.SupplierNO = g.SupplierNO LEFT JOIN salebill s ON g.GoodsNO = s.GoodsNO GROUP BY sp.SupplierNO ORDER BY SUM(g.SalePrice*s.Number) ASC LIMIT 0,1 ) -- 删除最低销售额的供应商的货物对应的账单信息 DELETE FROM salebill WHERE GoodsNO IN ( SELECT t.GoodsNO FROM (SELECT GoodsNO FROM goods WHERE SupplierNO = ( SELECT sp.SupplierNO FROM supplier sp LEFT JOIN goods g ON sp.SupplierNO = g.SupplierNO LEFT JOIN salebill s ON g.GoodsNO = s.GoodsNO GROUP BY sp.SupplierNO ORDER BY SUM(g.SalePrice*s.Number) ASC LIMIT 0,1)) t ) -- 删除最低销售额的供应商的货物信息 DELETE FROM goods WHERE SupplierNO = ( SELECT * FROM ( SELECT sp.SupplierNO FROM supplier sp LEFT JOIN goods g ON sp.SupplierNO = g.SupplierNO LEFT JOIN salebill s ON g.GoodsNO = s.GoodsNO GROUP BY sp.SupplierNO ORDER BY SUM(g.SalePrice*s.Number) ASC LIMIT 0,1 ) t ) -- 删除最低销售额的供应商信息 DELETE FROM supplier WHERE SupplierNO IN ( SELECT * FROM ( SELECT sp.SupplierNO FROM supplier sp LEFT JOIN goods g ON sp.SupplierNO = g.SupplierNO LEFT JOIN salebill s ON g.GoodsNO = s.GoodsNO GROUP BY sp.SupplierNO ORDER BY SUM(g.SalePrice*s.Number) ASC LIMIT 0,1 ) t ); -- 查看删除后的结果 SELECT * FROM supplier;
-
效果图:
-
四、实验问题及解决方法
-
上述第八题
—》limit子句不能用于嵌套循环- 解决办法:在外面再嵌套一层子查询
-
- 第七题,将销售额后两位的商品下架。实际上并不是删除商品信息,而是将商品的库存清零,即将销售额后两位的商品的set Number = 0
-
-- 7.将销售额后两位的商品下架。--》删除销售额最低的两件商品 -- 第一步 查询所有商品的销售额,找出销售额最低的两位商品的商品id,然后将库存置为0 SELECT DISTINCT g.GoodsNO,g.GoodsName,SUM(g.SalePrice*s.Number) sale FROM goods g LEFT JOIN salebill s ON g.GoodsNO = s.GoodsNO GROUP BY g.GoodsNO,g.GoodsName ORDER BY sale ASC LIMIT 0,2; -- 删除 update goods set Number = 0 WHERE GoodsNO IN ( SELECT DISTINCT g.GoodsNO,g.GoodsName,SUM(g.SalePrice*s.Number) sale FROM goods g LEFT JOIN salebill s ON g.GoodsNO = s.GoodsNO GROUP BY g.GoodsNO,g.GoodsName ORDER BY sale ASC LIMIT 0,2); -- 删除后,查看goods表 SELECT * FROM goods;
-
第八题:如果该表没有外键引用该表数据(supplier)直接删除即可,如果引用了则需要如上述方法删除,根据实际情况自行选择即可
-
-- 如果没有外键删除最低销售额的供应商信息 DELETE FROM supplier WHERE SupplierNO IN ( SELECT * FROM ( SELECT sp.SupplierNO FROM supplier sp LEFT JOIN goods g ON sp.SupplierNO = g.SupplierNO LEFT JOIN salebill s ON g.GoodsNO = s.GoodsNO GROUP BY sp.SupplierNO ORDER BY SUM(g.SalePrice*s.Number) ASC LIMIT 0,1 ) t );
-
五、实验总结
-
综合考查对数据库DML语句(数据库操作语言)与嵌套查询、子查询的组合使用
-
考查了对外键使用的优点和缺点:
- 优点:可以保持数据的一致性更新
- 缺点:增加数据、删除数据、修改数据十分耗费资源,必须先删除引用了该列为外键的数据先删除,才能删除被引用的列
-
考查了DML(数据库查询语句)与limit子句、常用聚合函数的搭配使用