数据库原理及应用-李唯唯主编-实验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 gLEFTJOIN 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 c LEFTJOIN goods g ON c.CategoryNO = g.CategoryNO WHERE c.CategoryName ='咖啡'))AND Major ='MIS'-- 方法2:使用左右外连接SELECTDISTINCT st.SNO,st.SName,st.BirthYear,st.Ssex,st.College,st.Major,st.WeiXin FROM category cLEFTJOIN goods g ON c.CategoryNO = g.CategoryNOLEFTJOIN salebill s ON s.GoodsNO = g.GoodsNOLEFTJOIN 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 c LEFTJOIN goods g ON c.CategoryNO = g.CategoryNO WHERE c.CategoryName ='咖啡'))GROUPBY Major;-- 方法二:使用左右外连接SELECT sc.Major 专业,COUNT(sc.Major) 人数 FROM student stRIGHTJOIN(SELECTDISTINCT st.SNO,st.SName,st.BirthYear,st.Ssex,st.College,st.Major,st.WeiXin FROM category c LEFTJOIN goods g ON c.CategoryNO = g.CategoryNO LEFTJOIN salebill s ON s.GoodsNO = g.GoodsNO rightJOIN student st ON st.SNO = s.SNOWHERE c.CategoryName ='咖啡') sc ON st.SNO = sc.SNOGROUPBY 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(SELECTDISTINCT SNO FROM salebill WHERE GoodsNO IN(SELECT GoodsNO FROM goods WHERE CategoryNO IN(SELECT CategoryNO FROM category)))GROUPBY Major;- 效果图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-B5lFf5wK-1665624066606)(C:\Users\SayHello\AppData\Roaming\Typora\typora-user-images\image-20221012162056489.png)]
9. ---题目：查询从未购买过商品的学生信息- 代码：-- 9. 查询从未购买过商品的学生信息。-- 方法一:使用子查询语句SELECT*FROM student WHERE SNO NOTIN(SELECTDISTINCT 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 gLEFTJOIN supplier sp ON g.SupplierNO = sp.SupplierNOWHERE g.GoodsNO ='GN0005';-- 第二步:根据商品的产地查询到供应商的编号SELECT SupplierNO FROMsupplierWHERE Address =(SELECT Address FROM goods g LEFTJOIN supplier sp ON g.SupplierNO = sp.SupplierNO WHERE g.GoodsNO ='GN0005')-- 第三步:使用子查询SELECT*FROM goods WHERE SupplierNO IN(SELECT SupplierNO FROM supplier WHERE Address =(SELECT Address FROM goods g LEFTJOIN supplier sp ON g.SupplierNO = sp.SupplierNO WHERE 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 spLEFTJOIN(SELECT SupplierNO,SUM(Number) sum_number FROM goods GROUPBY 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 goodsGROUPBY CategoryNO;SELECT g.GoodsNO 商品编号,g.SupplierNO 供应商编号, sg.CategoryNO 商品种类编号,g.GoodsName 商品名,g.SalePrice 售价,sg.avg_saleprice 该类商品的平均售价 FROM goods grightJOIN(SELECT CategoryNO,ROUND(AVG( SalePrice ),2) avg_saleprice FROM goods GROUPBY CategoryNO) 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'GROUPBY SNO HAVINGCOUNT(GoodsNO)=2)-- 方法二:使用连接查询SELECT st.SNO,st.SName FROM student stRIGHTJOIN(SELECTDISTINCT SNO FROM salebill WHERE GoodsNO ='GN0003'OR GoodsNO ='GN0007') gON st.SNO = g.SNOORDERBY st.SNO ASCSELECT st.SNO,st.SName FROM student stRIGHTJOIN(SELECT SNO FROM salebill WHERE GoodsNO ='GN0003'OR GoodsNO ='GN0007'GROUPBY SNO HAVINGCOUNT(GoodsNO)=2) gON st.SNO = g.SNOORDERBY 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 LEFTJOIN goods g ON s.GoodsNO = g.GoodsNO GROUPBY SNO;-- 第二步:计算各校的销售数量SELECT st.College 学校,SUM(sg.sum_number_price) 销售额FROM student stLEFTJOIN(SELECT s.SNO s_SNO ,SUM((s.Number*g.SalePrice)) sum_number_price FROM salebill s LEFTJOIN goods g ON s.GoodsNO = g.GoodsNO GROUPBY SNO) sgON st.SNO = sg.s_SNOGROUPBY College;-- 14. 查询各公司销售额。-- 第一步:计算各类商品的销售数量SELECT GoodsNO,SUM(Number) sum_number FROM salebillGROUPBY GoodsNO;SELECT sg.GoodsNO,g.GoodsName,sp.SupplierName 公司,g.SalePrice*sg.sum_number 销售额 FROM goods gRIGHTJOIN(SELECT GoodsNO,SUM(Number) sum_number FROM salebillGROUPBY GoodsNO) sgON g.GoodsNO = sg.GoodsNOLEFTJOIN 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 stLEFTJOIN(SELECT s.SNO s_SNO ,SUM((s.Number*g.SalePrice)) sum_number_price FROM salebill s LEFTJOIN goods g ON s.GoodsNO = g.GoodsNO GROUPBY SNO) sgON st.SNO = sg.s_SNOGROUPBY College,MajorORDERBY Major DESCLIMIT0,3;SELECT st.College 学校,Major 专业名,SUM(sg.sum_number) 购买额FROM student stLEFTJOIN(SELECT s.SNO s_SNO ,SUM(s.Number) sum_number FROM salebill s LEFTJOIN goods g ON s.GoodsNO = g.GoodsNO GROUPBY SNO) sgON st.SNO = sg.s_SNOGROUPBY College,MajorORDERBY Major DESCLIMIT0,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')<0OR 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 g
LEFTJOIN supplier s ON g.SupplierNO = s.SupplierNO
WHERE s.Address ='广州佛山';-- 6. 查询购买了商品种类为咖啡的MIS专业的学生信息。-- 方法一:使用子查询语句SELECT*FROM student st WHERE SNO IN(SELECT SNO FROM salebill WHERE GoodsNO IN(SELECT GoodsNO 
        FROM category c
        LEFTJOIN goods g ON c.CategoryNO = g.CategoryNO
        WHERE c.CategoryName ='咖啡'))AND Major ='MIS'-- 方法2:使用左右外连接SELECTDISTINCT st.SNO,st.SName,st.BirthYear,st.Ssex,st.College,st.Major,st.WeiXin 
FROM category c
LEFTJOIN goods g ON c.CategoryNO = g.CategoryNO
LEFTJOIN salebill s ON s.GoodsNO = g.GoodsNO
LEFTJOIN student st ON st.SNO = s.SNO
WHERE 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 c
        LEFTJOIN goods g ON c.CategoryNO = g.CategoryNO
        WHERE c.CategoryName ='咖啡'))GROUPBY Major;-- 方法二:使用左右外连接SELECT sc.Major 专业,COUNT(sc.Major) 人数 
FROM student st
RIGHTJOIN(SELECTDISTINCT st.SNO,st.SName,st.BirthYear,st.Ssex,st.College,st.Major,st.WeiXin 
        FROM category c
        LEFTJOIN goods g ON c.CategoryNO = g.CategoryNO
        LEFTJOIN salebill s ON s.GoodsNO = g.GoodsNO
        rightJOIN student st ON st.SNO = s.SNO
WHERE c.CategoryName ='咖啡') sc ON st.SNO = sc.SNO
GROUPBY sc.Major;-- 8. 查询购买各商品种类的各专业的学生人数。-- 方法一:使用子查询语句SELECT Major,COUNT(Major)FROM student WHERE SNO IN(SELECTDISTINCT SNO FROM salebill WHERE GoodsNO IN(SELECT GoodsNO FROM goods WHERE CategoryNO IN(SELECT CategoryNO FROM category)))GROUPBY Major;-- 9. 查询从未购买过商品的学生信息。-- 方法一:使用子查询语句SELECT*FROM student WHERE SNO NOTIN(SELECTDISTINCT SNO FROM salebill WHERE GoodsNO IN(SELECT GoodsNO FROM goods WHERE CategoryNO IN(SELECT CategoryNO FROM category)));-- 10. 查询与商品编号GN0005相同产地的商品编号、商品名。-- 第一步:查询到商品编号GN0005商品的产地SELECT Address 
FROM goods g
LEFTJOIN supplier sp ON g.SupplierNO = sp.SupplierNO
WHERE g.GoodsNO ='GN0005';-- 第二步:根据商品的产地查询到供应商的编号SELECT SupplierNO FROM
supplier
WHERE Address =(SELECT Address 
    FROM goods g
    LEFTJOIN supplier sp ON g.SupplierNO = sp.SupplierNO
    WHERE g.GoodsNO ='GN0005')-- 第三步:使用子查询SELECT*FROM goods WHERE SupplierNO IN(SELECT SupplierNO FROM supplier WHERE Address =(SELECT Address 
        FROM goods g
        LEFTJOIN supplier sp ON g.SupplierNO = sp.SupplierNO
        WHERE g.GoodsNO ='GN0005'))-- 11. 使用派生表查询各供应商的存货量。SELECT sp.SupplierNO 供应商编号,sp.SupplierName 供应商名称,sg.sum_number 存货量 
FROM supplier sp
LEFTJOIN(SELECT SupplierNO,SUM(Number) sum_number FROM goods GROUPBY SupplierNO) sg
ON sp.SupplierNO = sg.SupplierNO;-- 12. 查询售价大于该种类商品售价均值的商品号、商品名。-- 第一步:按种类分组求平均售价(保留小数点后两位)SELECT CategoryNO,(ROUND(AVG(SalePrice),2)) avg_saleprice FROM goods
GROUPBY CategoryNO;SELECT g.GoodsNO 商品编号,g.SupplierNO 供应商编号, sg.CategoryNO 商品种类编号,g.GoodsName 商品名,g.SalePrice 售价,sg.avg_saleprice 该类商品的平均售价 
FROM goods g
rightJOIN(SELECT
        CategoryNO,ROUND(AVG( SalePrice ),2) avg_saleprice 
    FROM
        goods 
    GROUPBY
    CategoryNO) sg
ON 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'GROUPBY SNO HAVINGCOUNT(GoodsNO)=2)-- 方法二:使用连接查询SELECT st.SNO,st.SName 
FROM student st
RIGHTJOIN(SELECTDISTINCT SNO FROM salebill WHERE GoodsNO ='GN0003'OR GoodsNO ='GN0007') g
ON st.SNO = g.SNO
ORDERBY st.SNO ASCSELECT st.SNO,st.SName 
FROM student st
RIGHTJOIN(SELECT SNO FROM salebill WHERE GoodsNO ='GN0003'OR GoodsNO ='GN0007'GROUPBY SNO HAVINGCOUNT(GoodsNO)=2) g
ON st.SNO = g.SNO
ORDERBY st.SNO ASC-- 14. 查询各校销售额。-- 第一步:计算各个学生的销售额SELECT s.SNO s_SNO ,SUM((s.Number*g.SalePrice)) sum_number_price FROM salebill s LEFTJOIN goods g ON s.GoodsNO = g.GoodsNO GROUPBY SNO;-- 第二步:计算各校的销售数量SELECT st.College 学校,SUM(sg.sum_number_price) 销售额
FROM student st
LEFTJOIN(SELECT s.SNO s_SNO ,SUM((s.Number*g.SalePrice)) sum_number_price FROM salebill s LEFTJOIN goods g ON s.GoodsNO = g.GoodsNO GROUPBY SNO
) sg
ON st.SNO = sg.s_SNO
GROUPBY College;SELECT GoodsNO,SUM(Number) sum_number FROM salebill
GROUPBY GoodsNO;SELECT sg.GoodsNO,g.GoodsName,sp.SupplierName 公司,g.SalePrice*sg.sum_number 销售额 FROM goods g
RIGHTJOIN(SELECT GoodsNO,SUM(Number) sum_number FROM salebill
GROUPBY GoodsNO) sg
ON g.GoodsNO = sg.GoodsNO
LEFTJOIN supplier sp ON g.SupplierNO = sp.SupplierNO;-- 15. 查询销售额、购买额前三的校名、专业名。SELECT st.College 学校,Major 专业名,SUM(sg.sum_number_price) 销售额
FROM student st
LEFTJOIN(SELECT s.SNO s_SNO ,SUM((s.Number*g.SalePrice)) sum_number_price FROM salebill s LEFTJOIN goods g ON s.GoodsNO = g.GoodsNO GROUPBY SNO
) sg
ON st.SNO = sg.s_SNO
GROUPBY College,Major
ORDERBY Major DESCLIMIT0,3;SELECT st.College 学校,Major 专业名,SUM(sg.sum_number) 购买额
FROM student st
LEFTJOIN(SELECT s.SNO s_SNO ,SUM(s.Number) sum_number FROM salebill s LEFTJOIN goods g ON s.GoodsNO = g.GoodsNO GROUPBY SNO
) sg
ON st.SNO = sg.s_SNO
GROUPBY College,Major
ORDERBY Major DESCLIMIT0,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')<0OR 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子句的知识