我的编程空间，编程开发者的网络收藏夹
学习永远不晚
位置：首页-资讯-数据库

数据库原理及应用-李唯唯主编-实验3-2

2023-10-21 19:40

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号
获取短信验证
立即预约

请填写图片验证码后获取短信验证码

看不清楚，换张图片

免费获取短信验证码

数据库原理及应用-李唯唯主编-实验3-2

实验3-2

一、实验目的与要求

  1. 掌握单表查询
  2. 掌握多表连接查询
  3. 掌握子查询、集合查询。
  4. 掌握派生表查询。
  5. 掌握聚合函数使用方法

二、实验内容

  1. 实验平台：
    • 操作系统：windows11
    • 数据库：MySQL8
    • 数据库管理平台：Navicat Premium 15
  2. 在数据库supermarket上完成下列操作
    1. 查询商品种类信息。
    2. 查询IT专业所有学生信息。
    3. 查询MIS专业年龄小于20岁的学生信息。并为MIS列取别名为 “信息管理系统”
    4. 查询利润率大于30%的商品编号与商品名。
    5. 查询广州佛山供应的商品信息。
    6. 查询购买了商品种类为咖啡的MIS专业的学生信息。
    7. 查询购买了商品种类为咖啡的各专业的学生人数。
    8. 查询购买各商品种类的各专业的学生人数。
    9. 查询从未购买过商品的学生信息。
    10. 查询与商品编号GN0005相同产地的商品编号、商品名。
    11. 使用派生表查询各供应商的存货量。
    12. 查询售价大于该种类商品售价均值的商品号、商品名。
    13. 分别用子查询 与 连接查询查询购买了商品编号为 “GN0003” 和"GN0007"的学生学号与姓名。
    14. 查询各校销售额。
    15. 查询购买额前三的校名、专业名。
    16. 使用集合查询方式查询生产日期早于2018-1-1 或 库存量小于30的商品信息。

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

  1. 题目：查询商品种类信息

    • 代码：

      -- 实验3-2-- 1. 查询商品种类信息。-- 方法一SELECT * FROM category;-- 方法二SELECT CategoryNO 编号,CategoryName 种类,Description 具体描述 FROM category;

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BblEH3J7-1665624066603)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012161526838.png)]

  2. 题目：查询IT专业所有学生信息

    • 代码：

      -- 2. 查询IT专业所有学生信息。-- 方法一SELECT * FROM student WHERE Major = 'IT';-- 方法二SELECT SNO 学号,SName 姓名,BirthYear 出生年份,Ssex 性别,College 学院,Major 专业,WeiXin 专业 FROM student WHERE Major = 'IT';

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GGWM1ikz-1665624066604)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012161543809.png)]

  3. 题目：查询MIS专业年龄小于20岁的学生信息。并为MIS列取别名为 “信息管理系统”

    • 代码：

      -- 3. 查询MIS专业且年龄小于20岁的学生信息。并为MIS列取别名为 "信息管理系统"。-- 第一次查询不到信息，因为这里的BirthYear不是date类型，而是int，类型，所以直接减就行了SELECT * FROM student WHERE Major = 'MIS' AND (YEAR(NOW())-YEAR(BirthYear)) > 20;-- 第一次查询不到信息，因为这里的BirthYear都已经超过了20岁，所以找不到满足条件的，因此查询MIS专业且年龄大于20岁的学生信息-- 方法一:查询MIS专业且年龄小于20岁的学生信息SELECT * FROM student WHERE Major = 'MIS' AND (YEAR(NOW())-BirthYear) < 20;-- 方法二:查询MIS专业且年龄大于20岁的学生信息SELECT SNO 学号,SName 姓名,BirthYear 出生年份,(YEAR(NOW())-BirthYear) 年龄,Ssex 性别,College 学院,Major 专业,WeiXin 专业 FROM student WHERE Major = 'MIS' AND (YEAR(NOW())-BirthYear) > 20;-- 方法二SELECT SNO 学号,SName 姓名,BirthYear 出生年份,Ssex 性别,College 学院,Major 专业,WeiXin 专业 FROM student WHERE Major = 'IT';

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gf5ntqBy-1665624066604)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012161617075.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GYUeu6Yd-1665624066605)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012161632448.png)]

  4. 题目：查询利润率大于30%的商品编号与商品名

    • 代码：

      -- 4. 查询利润率大于30%的商品编号与商品名。-- SELECT * FROM goods WHERE (SalePrice-InPrice)/InPrice < 0.3;SELECT GoodsNO 商品编号,GoodsName 商品名,ROUND((SalePrice-InPrice)/InPrice,2) AS 利润率 FROM goods WHERE (SalePrice-InPrice)/InPrice < 0.3;SELECT GoodsNO 商品编号,GoodsName 商品名,ROUND((SalePrice-InPrice)/InPrice,2) AS 利润率 FROM goods WHERE (SalePrice-InPrice)/InPrice > 0.3;

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-loYhYfgu-1665624066605)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012161901957.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-d7jvMRkU-1665624066605)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012161913598.png)]

  5. 题目：查询广州佛山供应的商品信息

    • 代码：

      -- 5. 查询广州佛山供应的商品信息。SELECT g.GoodsNO 商品编号,s.SupplierNO 供应商编号,g.CategoryNO 商品种类编号,g.GoodsName 商品名,s.Address 供应商,s.SupplierName 公司  FROM goods gLEFT JOIN supplier s ON g.SupplierNO = s.SupplierNOWHERE s.Address = '广州佛山';

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4Z9XxBR0-1665624066605)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012161941446.png)]

  6. 题目：查询购买了商品种类为咖啡的MIS专业的学生信息

    • 代码：

      -- 6. 查询购买了商品种类为咖啡的MIS专业的学生信息。-- 方法一:使用子查询语句SELECT * FROM student st WHERE SNO IN(SELECT SNO FROM salebill WHERE GoodsNO IN (SELECT GoodsNO FROM category cLEFT JOIN goods g ON c.CategoryNO = g.CategoryNOWHERE c.CategoryName = '咖啡')) AND Major = 'MIS'-- 方法2:使用左右外连接SELECT DISTINCT st.SNO,st.SName,st.BirthYear,st.Ssex,st.College,st.Major,st.WeiXin FROM category cLEFT JOIN goods g ON c.CategoryNO = g.CategoryNOLEFT JOIN salebill s ON s.GoodsNO = g.GoodsNOLEFT JOIN student st ON st.SNO = s.SNOWHERE c.CategoryName = '咖啡' AND st.Major='MIS';

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KENkiepN-1665624066606)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012161959760.png)]

  7. 题目：查询购买了商品种类为咖啡的各专业的学生人数

    • 代码：

      -- 7. 查询购买了商品种类为咖啡的各专业的学生人数。-- 方法一:使用子查询语句SELECT Major 专业,COUNT(Major) 人数 FROM student st WHERE SNO IN(SELECT SNO FROM salebill WHERE GoodsNO IN (SELECT GoodsNO FROM category cLEFT JOIN goods g ON c.CategoryNO = g.CategoryNOWHERE c.CategoryName = '咖啡'))  GROUP BY Major;-- 方法二:使用左右外连接SELECT sc.Major 专业,COUNT(sc.Major) 人数 FROM student stRIGHT JOIN (SELECT DISTINCT st.SNO,st.SName,st.BirthYear,st.Ssex,st.College,st.Major,st.WeiXin FROM category cLEFT JOIN goods g ON c.CategoryNO = g.CategoryNOLEFT JOIN salebill s ON s.GoodsNO = g.GoodsNOright JOIN student st ON st.SNO = s.SNOWHERE c.CategoryName = '咖啡') sc ON st.SNO = sc.SNOGROUP BY sc.Major;

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k5V5FlzD-1665624066606)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012162027303.png)]

  8. 题目： 查询购买各商品种类的各专业的学生人数

    • 代码：

      -- 8. 查询购买各商品种类的各专业的学生人数。-- 方法一:使用子查询语句SELECT Major,COUNT(Major) FROM student WHERE SNO IN (SELECT DISTINCT SNO FROM salebill WHERE GoodsNO IN (SELECT GoodsNO FROM goods WHERE CategoryNO IN (SELECT CategoryNO FROM category)))GROUP BY Major;

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-B5lFf5wK-1665624066606)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012162056489.png)]

  9. 题目：查询从未购买过商品的学生信息

    • 代码：

      -- 9. 查询从未购买过商品的学生信息。-- 方法一:使用子查询语句SELECT * FROM student WHERE SNO NOT IN (SELECT DISTINCT SNO FROM salebill WHERE GoodsNO IN (SELECT GoodsNO FROM goods WHERE CategoryNO IN (SELECT CategoryNO FROM category)));

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8D1HWQ3o-1665624066607)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012162116300.png)]

  10. 题目： 查询与商品编号GN0005相同产地的商品编号、商品名

    • 代码：

      -- 10. 查询与商品编号GN0005相同产地的商品编号、商品名。-- 第一步:查询到商品编号GN0005商品的产地SELECT Address FROM goods gLEFT JOIN supplier sp ON g.SupplierNO = sp.SupplierNOWHERE g.GoodsNO = 'GN0005';-- 第二步:根据商品的产地查询到供应商的编号SELECT SupplierNO FROMsupplierWHERE Address = (SELECT Address FROM goods gLEFT JOIN supplier sp ON g.SupplierNO = sp.SupplierNOWHERE g.GoodsNO = 'GN0005')-- 第三步:使用子查询SELECT * FROM goods WHERE SupplierNO IN(SELECT SupplierNO FROM supplier WHERE Address = (SELECT Address FROM goods gLEFT JOIN supplier sp ON g.SupplierNO = sp.SupplierNOWHERE g.GoodsNO = 'GN0005'))

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IKOs1FdJ-1665624066607)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012162446667.png)]

  11. 题目：使用派生表查询各供应商的存货量

    • 代码：

      -- 11. 使用派生表查询各供应商的存货量。SELECT sp.SupplierNO 供应商编号,sp.SupplierName 供应商名称,sg.sum_number 存货量 FROM supplier spLEFT JOIN (SELECT SupplierNO,SUM(Number) sum_number FROM goods GROUP BY SupplierNO) sgON sp.SupplierNO = sg.SupplierNO;

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xPiwk85n-1665624066609)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012164131110.png)]

  12. 题目：查询售价大于该种类商品售价均值的商品号、商品名

    • 代码：

      -- 12. 查询售价大于该种类商品售价均值的商品号、商品名。-- 第一步:按种类分组求平均售价(保留小数点后两位)SELECT CategoryNO,(ROUND(AVG(SalePrice),2)) avg_saleprice FROM goodsGROUP BY CategoryNO;SELECT g.GoodsNO 商品编号,g.SupplierNO 供应商编号, sg.CategoryNO 商品种类编号,g.GoodsName 商品名,g.SalePrice 售价,sg.avg_saleprice 该类商品的平均售价 FROM goods gright JOIN (SELECTCategoryNO,ROUND(AVG( SalePrice ),2) avg_saleprice FROMgoods GROUP BYCategoryNO) sgON g.CategoryNO = sg.CategoryNO AND g.SalePrice > sg.avg_saleprice;

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-J7F2yrRB-1665624066609)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012165213442.png)]

  13. 题目：分别用子查询 与 连接查询查询购买了商品编号为 “GN0003” 和"GN0007"的学生学号与姓名

    • 代码：

      -- 13. 分别用子查询 与 连接查询查询购买了商品编号为 "GN0003" 和"GN0007"的学生学号与姓名。-- 方法一:使用子查询SELECT SNO,SName FROM student WHERE SNO IN(SELECT SNO FROM salebill WHERE GoodsNO = 'GN0003' OR GoodsNO = 'GN0007')SELECT SNO,SName FROM student WHERE SNO IN(SELECT SNO FROM salebill WHERE GoodsNO = 'GN0003' OR GoodsNO = 'GN0007' GROUP BY SNO HAVING COUNT(GoodsNO) = 2)-- 方法二:使用连接查询SELECT st.SNO,st.SName FROM student stRIGHT JOIN ( SELECT DISTINCT SNO FROM salebill WHERE GoodsNO = 'GN0003' OR GoodsNO = 'GN0007') gON st.SNO = g.SNOORDER BY st.SNO ASCSELECT st.SNO,st.SName FROM student stRIGHT JOIN ( SELECT SNO FROM salebill WHERE GoodsNO = 'GN0003' OR GoodsNO = 'GN0007' GROUP BY SNO HAVING COUNT(GoodsNO) = 2) gON st.SNO = g.SNOORDER BY st.SNO ASC

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xGm3rgJ9-1665624066610)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012170149140.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-u5EpgkEm-1665624066610)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221013084331224.png)]

  14. 题目：查询各校销售额

    • 代码：

      -- 14. 查询各校销售额。-- 第一步:计算各个学生的销售额SELECT s.SNO s_SNO ,SUM((s.Number*g.SalePrice)) sum_number_price FROM salebill s LEFT JOIN goods g ON s.GoodsNO = g.GoodsNO GROUP BY SNO;-- 第二步:计算各校的销售数量SELECT st.College 学校,SUM(sg.sum_number_price) 销售额FROM student stLEFT JOIN (SELECT s.SNO s_SNO ,SUM((s.Number*g.SalePrice)) sum_number_price FROM salebill s LEFT JOIN goods g ON s.GoodsNO = g.GoodsNO GROUP BY SNO) sgON st.SNO = sg.s_SNOGROUP BY College;-- 14. 查询各公司销售额。-- 第一步:计算各类商品的销售数量SELECT GoodsNO,SUM(Number) sum_number FROM salebillGROUP BY GoodsNO;SELECT sg.GoodsNO,g.GoodsName,sp.SupplierName 公司,g.SalePrice*sg.sum_number 销售额 FROM goods gRIGHT JOIN (SELECT GoodsNO,SUM(Number) sum_number FROM salebillGROUP BY GoodsNO) sgON g.GoodsNO = sg.GoodsNOLEFT JOIN supplier sp ON g.SupplierNO = sp.SupplierNO;

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UvHxMooL-1665624066611)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221013084042608.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hCFUwgIX-1665624066612)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221013084133433.png)]

  15. 题目：查询销售额、购买额前三的校名、专业名

    • 代码：

      -- 15. 查询销售额、购买额前三的校名、专业名。SELECT st.College 学校,Major 专业名,SUM(sg.sum_number_price) 销售额FROM student stLEFT JOIN (SELECT s.SNO s_SNO ,SUM((s.Number*g.SalePrice)) sum_number_price FROM salebill s LEFT JOIN goods g ON s.GoodsNO = g.GoodsNO GROUP BY SNO) sgON st.SNO = sg.s_SNOGROUP BY College,MajorORDER BY Major DESCLIMIT 0,3;SELECT st.College 学校,Major 专业名,SUM(sg.sum_number) 购买额FROM student stLEFT JOIN (SELECT s.SNO s_SNO ,SUM(s.Number) sum_number FROM salebill s LEFT JOIN goods g ON s.GoodsNO = g.GoodsNO GROUP BY SNO) sgON st.SNO = sg.s_SNOGROUP BY College,MajorORDER BY Major DESCLIMIT 0,3;

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hBuQt7OR-1665624066612)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221013083645807.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6isjtRii-1665624066613)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221013084011868.png)]

  16. 题目：使用集合查询方式查询生产日期早于2018-1-1 或 库存量小于30的商品信息

    • 代码：

      -- 16. 使用集合查询方式查询生产日期早于2018-1-1 或 库存量小于30的商品信息。-- 方法一:使用unionSELECT * FROM goods WHERE DATEDIFF(DATE(ProductTime),'2018-1-1') < 0UNIONSELECT * FROM goods WHERE QGPeriod < 30;-- SELECT GoodsNO,SupplierNO,CategoryNO,GoodsName,InPrice,SalePrice,Number,ProductTime 生产日期,QGPeriod 库存,DATEDIFF(DATE(ProductTime),'2018-1-1') 日期差  FROM goods WHERE DATEDIFF(DATE(ProductTime),'2018-1-1') < 0 OR QGPeriod < 30;

    • 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7u2WB6Dm-1665624066613)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221013083624345.png)]

四、具体查询

-- 实验3-2-- 1. 查询商品种类信息。-- 方法一SELECT * FROM category;-- 方法二SELECT CategoryNO 编号,CategoryName 种类,Description 具体描述 FROM category;-- 2. 查询IT专业所有学生信息。-- 方法一SELECT * FROM student WHERE Major = 'IT';-- 方法二SELECT SNO 学号,SName 姓名,BirthYear 出生年份,Ssex 性别,College 学院,Major 专业,WeiXin 专业 FROM student WHERE Major = 'IT';-- 3. 查询MIS专业且年龄小于20岁的学生信息。并为MIS列取别名为 "信息管理系统"。-- 第一次查询不到信息，因为这里的BirthYear不是date类型，而是int，类型，所以直接减就行了SELECT * FROM student WHERE Major = 'MIS' AND (YEAR(NOW())-YEAR(BirthYear)) > 20;-- 第一次查询不到信息，因为这里的BirthYear都已经超过了20岁，所以找不到满足条件的，因此查询MIS专业且年龄大于20岁的学生信息-- 方法一:查询MIS专业且年龄小于20岁的学生信息SELECT * FROM student WHERE Major = 'MIS' AND (YEAR(NOW())-BirthYear) < 20;-- 方法二:查询MIS专业且年龄大于20岁的学生信息SELECT SNO 学号,SName 姓名,BirthYear 出生年份,(YEAR(NOW())-BirthYear) 年龄,Ssex 性别,College 学院,Major 专业,WeiXin 专业 FROM student WHERE Major = 'MIS' AND (YEAR(NOW())-BirthYear) > 20;-- 4. 查询利润率大于30%的商品编号与商品名。-- SELECT * FROM goods WHERE (SalePrice-InPrice)/InPrice < 0.3;SELECT GoodsNO 商品编号,GoodsName 商品名,ROUND((SalePrice-InPrice)/InPrice,2) AS 利润率 FROM goods WHERE (SalePrice-InPrice)/InPrice < 0.3;SELECT GoodsNO 商品编号,GoodsName 商品名,ROUND((SalePrice-InPrice)/InPrice,2) AS 利润率 FROM goods WHERE (SalePrice-InPrice)/InPrice > 0.3;-- 5. 查询广州佛山供应的商品信息。SELECT g.GoodsNO 商品编号,s.SupplierNO 供应商编号,g.CategoryNO 商品种类编号,g.GoodsName 商品名,s.Address 供应商,s.SupplierName 公司  FROM goods gLEFT JOIN supplier s ON g.SupplierNO = s.SupplierNOWHERE s.Address = '广州佛山';-- 6. 查询购买了商品种类为咖啡的MIS专业的学生信息。-- 方法一:使用子查询语句SELECT * FROM student st WHERE SNO IN(SELECT SNO FROM salebill WHERE GoodsNO IN (SELECT GoodsNO FROM category cLEFT JOIN goods g ON c.CategoryNO = g.CategoryNOWHERE c.CategoryName = '咖啡')) AND Major = 'MIS'-- 方法2:使用左右外连接SELECT DISTINCT st.SNO,st.SName,st.BirthYear,st.Ssex,st.College,st.Major,st.WeiXin FROM category cLEFT JOIN goods g ON c.CategoryNO = g.CategoryNOLEFT JOIN salebill s ON s.GoodsNO = g.GoodsNOLEFT JOIN student st ON st.SNO = s.SNOWHERE c.CategoryName = '咖啡' AND st.Major='MIS';-- 7. 查询购买了商品种类为咖啡的各专业的学生人数。-- 方法一:使用子查询语句SELECT Major 专业,COUNT(Major) 人数 FROM student st WHERE SNO IN(SELECT SNO FROM salebill WHERE GoodsNO IN (SELECT GoodsNO FROM category cLEFT JOIN goods g ON c.CategoryNO = g.CategoryNOWHERE c.CategoryName = '咖啡'))  GROUP BY Major;-- 方法二:使用左右外连接SELECT sc.Major 专业,COUNT(sc.Major) 人数 FROM student stRIGHT JOIN (SELECT DISTINCT st.SNO,st.SName,st.BirthYear,st.Ssex,st.College,st.Major,st.WeiXin FROM category cLEFT JOIN goods g ON c.CategoryNO = g.CategoryNOLEFT JOIN salebill s ON s.GoodsNO = g.GoodsNOright JOIN student st ON st.SNO = s.SNOWHERE c.CategoryName = '咖啡') sc ON st.SNO = sc.SNOGROUP BY sc.Major;-- 8. 查询购买各商品种类的各专业的学生人数。-- 方法一:使用子查询语句SELECT Major,COUNT(Major) FROM student WHERE SNO IN (SELECT DISTINCT SNO FROM salebill WHERE GoodsNO IN (SELECT GoodsNO FROM goods WHERE CategoryNO IN (SELECT CategoryNO FROM category)))GROUP BY Major;-- 9. 查询从未购买过商品的学生信息。-- 方法一:使用子查询语句SELECT * FROM student WHERE SNO NOT IN (SELECT DISTINCT SNO FROM salebill WHERE GoodsNO IN (SELECT GoodsNO FROM goods WHERE CategoryNO IN (SELECT CategoryNO FROM category)));-- 10. 查询与商品编号GN0005相同产地的商品编号、商品名。-- 第一步:查询到商品编号GN0005商品的产地SELECT Address FROM goods gLEFT JOIN supplier sp ON g.SupplierNO = sp.SupplierNOWHERE g.GoodsNO = 'GN0005';-- 第二步:根据商品的产地查询到供应商的编号SELECT SupplierNO FROMsupplierWHERE Address = (SELECT Address FROM goods gLEFT JOIN supplier sp ON g.SupplierNO = sp.SupplierNOWHERE g.GoodsNO = 'GN0005')-- 第三步:使用子查询SELECT * FROM goods WHERE SupplierNO IN(SELECT SupplierNO FROM supplier WHERE Address = (SELECT Address FROM goods gLEFT JOIN supplier sp ON g.SupplierNO = sp.SupplierNOWHERE g.GoodsNO = 'GN0005'))-- 11. 使用派生表查询各供应商的存货量。SELECT sp.SupplierNO 供应商编号,sp.SupplierName 供应商名称,sg.sum_number 存货量 FROM supplier spLEFT JOIN (SELECT SupplierNO,SUM(Number) sum_number FROM goods GROUP BY SupplierNO) sgON sp.SupplierNO = sg.SupplierNO;-- 12. 查询售价大于该种类商品售价均值的商品号、商品名。-- 第一步:按种类分组求平均售价(保留小数点后两位)SELECT CategoryNO,(ROUND(AVG(SalePrice),2)) avg_saleprice FROM goodsGROUP BY CategoryNO;SELECT g.GoodsNO 商品编号,g.SupplierNO 供应商编号, sg.CategoryNO 商品种类编号,g.GoodsName 商品名,g.SalePrice 售价,sg.avg_saleprice 该类商品的平均售价 FROM goods gright JOIN (SELECTCategoryNO,ROUND(AVG( SalePrice ),2) avg_saleprice FROMgoods GROUP BYCategoryNO) sgON g.CategoryNO = sg.CategoryNO AND g.SalePrice > sg.avg_saleprice;-- 13. 分别用子查询 与 连接查询查询购买了商品编号为 "GN0003" 和"GN0007"的学生学号与姓名。-- 方法一:使用子查询SELECT SNO,SName FROM student WHERE SNO IN(SELECT SNO FROM salebill WHERE GoodsNO = 'GN0003' OR GoodsNO = 'GN0007')SELECT SNO,SName FROM student WHERE SNO IN(SELECT SNO FROM salebill WHERE GoodsNO = 'GN0003' OR GoodsNO = 'GN0007' GROUP BY SNO HAVING COUNT(GoodsNO) = 2)-- 方法二:使用连接查询SELECT st.SNO,st.SName FROM student stRIGHT JOIN ( SELECT DISTINCT SNO FROM salebill WHERE GoodsNO = 'GN0003' OR GoodsNO = 'GN0007') gON st.SNO = g.SNOORDER BY st.SNO ASCSELECT st.SNO,st.SName FROM student stRIGHT JOIN ( SELECT SNO FROM salebill WHERE GoodsNO = 'GN0003' OR GoodsNO = 'GN0007' GROUP BY SNO HAVING COUNT(GoodsNO) = 2) gON st.SNO = g.SNOORDER BY st.SNO ASC-- 14. 查询各校销售额。-- 第一步:计算各个学生的销售额SELECT s.SNO s_SNO ,SUM((s.Number*g.SalePrice)) sum_number_price FROM salebill s LEFT JOIN goods g ON s.GoodsNO = g.GoodsNO GROUP BY SNO;-- 第二步:计算各校的销售数量SELECT st.College 学校,SUM(sg.sum_number_price) 销售额FROM student stLEFT JOIN (SELECT s.SNO s_SNO ,SUM((s.Number*g.SalePrice)) sum_number_price FROM salebill s LEFT JOIN goods g ON s.GoodsNO = g.GoodsNO GROUP BY SNO) sgON st.SNO = sg.s_SNOGROUP BY College;SELECT GoodsNO,SUM(Number) sum_number FROM salebillGROUP BY GoodsNO;SELECT sg.GoodsNO,g.GoodsName,sp.SupplierName 公司,g.SalePrice*sg.sum_number 销售额 FROM goods gRIGHT JOIN (SELECT GoodsNO,SUM(Number) sum_number FROM salebillGROUP BY GoodsNO) sgON g.GoodsNO = sg.GoodsNOLEFT JOIN supplier sp ON g.SupplierNO = sp.SupplierNO;-- 15. 查询销售额、购买额前三的校名、专业名。SELECT st.College 学校,Major 专业名,SUM(sg.sum_number_price) 销售额FROM student stLEFT JOIN (SELECT s.SNO s_SNO ,SUM((s.Number*g.SalePrice)) sum_number_price FROM salebill s LEFT JOIN goods g ON s.GoodsNO = g.GoodsNO GROUP BY SNO) sgON st.SNO = sg.s_SNOGROUP BY College,MajorORDER BY Major DESCLIMIT 0,3;SELECT st.College 学校,Major 专业名,SUM(sg.sum_number) 购买额FROM student stLEFT JOIN (SELECT s.SNO s_SNO ,SUM(s.Number) sum_number FROM salebill s LEFT JOIN goods g ON s.GoodsNO = g.GoodsNO GROUP BY SNO) sgON st.SNO = sg.s_SNOGROUP BY College,MajorORDER BY Major DESCLIMIT 0,3;-- 16. 使用集合查询方式查询生产日期早于2018-1-1 或 库存量小于30的商品信息。-- 方法一:使用unionSELECT * FROM goods WHERE DATEDIFF(DATE(ProductTime),'2018-1-1') < 0UNIONSELECT * FROM goods WHERE QGPeriod < 30;-- SELECT GoodsNO,SupplierNO,CategoryNO,GoodsName,InPrice,SalePrice,Number,ProductTime 生产日期,QGPeriod 库存,DATEDIFF(DATE(ProductTime),'2018-1-1') 日期差  FROM goods WHERE DATEDIFF(DATE(ProductTime),'2018-1-1') < 0 OR QGPeriod < 30;

数据库文件:>
supermarket.sql
https://www.aliyundrive.com/s/hmXsCqZJHN3
提取码: 4rx3
点击链接保存，或者复制本段内容，打开「阿里云盘」APP ，无需下载极速在线查看，视频原画倍速播放。

查询语句文件:>
实验3-2.sql
https://www.aliyundrive.com/s/vshDT2MhVnG
提取码: 3x1e
点击链接保存，或者复制本段内容，打开「阿里云盘」APP ，无需下载极速在线查看，视频原画倍速播放。

五、实验总结

  1. 综合考查了对数据库表的查询的DML语句，单表查询、多表联查的使用
  2. 综合考查了子查询语句、集合查询、派生查询的具体应用
  3. 综合考查了聚合函数、分组查询、limit子句的知识

来源地址：https://blog.csdn.net/qq_45659753/article/details/127294788

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

数据库sqlmysql
阅读原文内容投诉

数据库原理及应用-李唯唯主编-实验3-2

下载Word文档到电脑，方便收藏和打印～

下载Word文档

相关文章

猜你喜欢

数据库原理及应用-李唯唯主编-实验3-2

2023-10-21

数据库原理及应用-李唯唯主编-实验3-3

数据库原理及应用-李唯唯主编-实验3-3
2023-10-28

热门标签

Linux(148)PHP(127)Java(102)正则表达式(101)JavaScript(69)最佳实践(67)jQuery(44)MySQL(39)Docker(37)C语言(36)性能优化(34)Python(34)XML(28)string(27)第三方库(23)回调函数(23)ZIP(22)数组(22)可扩展性(22)字符串比较(21)find(20)RPM(20)Go(20)grep(19)ASP.NETCore(19)XML解析器(19)事件(19)事件处理程序(19)StringBuilder(18)Nginx(18)

编程热搜

查看更多

编程资源站

  • 资料下载
  • 历年试题
2021年下半年软考高级信息系统项目管理师高频考点精选资料
2021下半年软考高级信息系统技术知识点记忆口诀精选资料
2021下半年软考《信息系统项目管理师》考试真题及答案精选资料
2021下半年软考高级考试备考攻略精选资料
2021年软考高级《信息系统项目管理师》巩固练习题汇总精选资料
2021下半年软考高级信息系统项目管理师30个易考知识点汇总精选资料
2021下半年软考高级知识点这样记，还担心记不住吗精选资料
2021年下半年软考高级考试重点汇总精选资料
2021下半年软考高级信息系统项目管理师计算公式汇总精选资料
2021年下半年软考高级《信息系统项目管理师》模拟试题精选资料
查看更多
信息系统项目管理师选择题每日一练（2024）历年试题
2023年下半年信息系统项目管理师综合知识真题演练历年试题
查看更多

目录

本网页已闲置超过3分钟，按键盘任意键或点击空白处，即可回到网页
最新资讯更多
    友情链接编程网

    反馈

    我要
    反馈