目录

一.实验目的

二.实验代码

三.代码汇总

一.实验目的

1. 查询全部商品信息。

2．查询类别为“cn002”的所有商品信息。

3. 查询类别为“cn001”和“cn002”对应的所有商品信息。

4．查询类别为“cn005”且进价大于20的商品信息。

5．查询库存数量小于10的商品号，供应商号，商品名和数量。

6．查询有购买记录学生的学号。

7．查询计算机专业“IT”学生的学号，姓名和年龄

8．查询年龄在22到24岁学生的学号，姓名，学院，专业和年龄

9．查询姓张的同学的学号，姓名，学院。

10．查询库存数量在20以上的商品信息，并按数量的降序排列。

11．查询库存数量在10以内的商品编号、商品名称、类别号、数量，按类别升序库存数量降序排列结果。

12．查询库存数量排名前三的商品信息。

13. 查询类别为“cn001”库存数量最大的商品，显示商品号，商品名和数量

14．查询管理信息系统专业“MIS”的学生人数。

15．按学院统计各个学院，年龄在20岁以上的学生人数。

16．统计2018年各商品的销售总量。

17．查询学生人数在3人以上的学院及学生人数。

18．查询2019年商品购买总数量在5以上的学生的学号和购买总数。

19．查询咖啡类商品的平均售价。

20．查询与张小红同学院的学生的学号、姓名、学院。

21．查询所有商品的销售情况，包括商品号，商品名，学生姓名，销售时间，销售数量，按销售日期降序排列。

22．查询购买了商品的学生学号、姓名、学院。

23．查询购买了咖啡类商品的学生的学号、姓名、学院。

24．查询没有出售过的商品号，商品名，类别名、售价和数量。（连接查询和子查询）

25．查询购买了商品号为“gn001”或“gn002”商品的学号，姓名和学院(1)。

26. 查询购买了商品号为“gn001”和“gn002”商品的学号，姓名和学院(2)。

27. 查询各学院年龄最大的学生，显示学号，姓名和学院。（相关子查询和派生表）

28. 统计每个商品的销售总数，要求显示商品号，商品名和销售总数。

29.查询2019年商品购买总数在5以上的学生的姓名、学院和购买总数。

30. 找出供应商品总数在50以上的供应商号，供应商名，供应商地址，供应总数，结果按地址分组，并按供应总数的降序排列。

二.实验代码

回到先前创建的T2数据库，依题意有:

1. 查询全部商品信息。

--1--查询全部商品信息。SELECT * FROM goods

2．查询类别为“cn002”的所有商品信息。

--2--查询类别为“cn002”的所有商品信息。SELECT *FROM goods WHERE categoryno='cn002'

3. 查询类别为“cn001”和“cn002”对应的所有商品信息。

--3--查询类别为“cn001”和“cn002”对应的所有商品信息。SELECT *FROM goods WHERE categoryno='cn001' OR categoryno='cn002'--或者SELECT *FROM goods WHERE categoryno IN ('cn001','cn002')

4．查询类别为“cn005”且进价大于20的商品信息。

--4--查询类别为“cn005”且进价大于20的商品信息。SELECT *FROM goods WHERE categoryno='cn005' AND inprice>20

5．查询库存数量小于10的商品号，供应商号，商品名和数量。

--5--查询库存数量小于10的商品号，供应商号，商品名和数量。SELECT goodsno,supplierno,goodsname,number FROM goods WHERE number<10

6．查询有购买记录学生的学号。

--6--查询有购买记录学生的学号。SELECT sno FROM salebill WHERE happentime is not null

7．查询计算机专业“IT”学生的学号，姓名和年龄。

--7--查询计算机专业“IT”学生的学号，姓名和年龄SELECT sno,sname,birthyear FROM student WHERE major='IT'

8．查询年龄在22到24岁学生的学号，姓名，学院，专业和年龄。

--8--查询年龄在22到24岁学生的学号，姓名，学院，专业和年龄SELECT sno,sname,college,major,YEAR(GETDATE())-birthyear --getdate()为获取当前时间 year()读取年份 减去出生年份即为年龄AS age --记为column名称ageFROM studentWHERE YEAR(GETDATE())-birthyear BETWEEN 22 AND 24

9．查询姓张的同学的学号，姓名，学院。

--9--查询姓张的同学的学号，姓名，学院。SELECT sno,sname,college FROM studentWHERE sname LIKE '张%'

10．查询库存数量在20以上的商品信息，并按数量的降序排列。

--10--查询库存数量在20以上的商品信息，并按数量的降序排列。SELECT * FROM goodsWHERE number>20ORDER BY number ASC

11．查询库存数量在10以内的商品编号、商品名称、类别号、数量，按类别升序库存数量降序排列结果。

--11--查询库存数量在10以内的商品编号、商品名称、类别号、数量，--按类别升序库存数量降序排列结果。SELECT goodsno,goodsname,categoryno,number FROM goodsWHERE number<10ORDER BY categoryno ASC,number DESC

12．查询库存数量排名前三的商品信息。

--12--查询库存数量排名前三的商品信息。SELECT TOP 3* FROM goodsORDER BY number DESC--降序排列，前三个即为前三

13. 查询类别为“cn001”库存数量最大的商品，显示商品号，商品名和数量。

--13--查询类别为“cn001”库存数量最大的商品，显示商品号，商品名和数量SELECT TOP 1 goodsno,goodsname,number FROM goodsWHERE categoryno='cn001'ORDER BY number DESC

14．查询管理信息系统专业“MIS”的学生人数。

--14--查询管理信息系统专业“MIS”的学生人数。SELECT major,COUNT(*)AS '人数总和' FROM student--AS表示为命名GROUP BY majorHAVING major='MIS'

15．按学院统计各个学院，年龄在20岁以上的学生人数。

--15--按学院统计各个学院，年龄在20岁以上的学生人数。SELECT college,YEAR(GETDATE())-birthyear AS Age,COUNT(*)AS '个数' FROM studentGROUP BY college,YEAR(GETDATE())-birthyearHAVING (YEAR(GETDATE())-birthyear)>20

16．统计2018年各商品的销售总量。

--16--统计2018年各商品的销售总量。SELECT goodsno,happentime,number,COUNT(*) AS 个数 FROM salebillGROUP BY goodsno,number,happentimeHAVING happentime LIKE '%2018%'

17．查询学生人数在3人以上的学院及学生人数。

--17--查询学生人数在3人以上的学院及学生人数SELECT college,major,COUNT(*)AS '人数' FROM studentGROUP BY college,majorHAVING COUNT(*)>3

18．查询2019年商品购买总数量在5以上的学生的学号和购买总数。

--18----查询2019年商品购买总数量在5以上的学生的学号和购买总数SELECT sno,happentime,number,COUNT(*) FROM salebillWHERE number>=5 GROUP BY sno,number,happentimeHAVING happentime LIKE '%2019%'

19．查询咖啡类商品的平均售价。

--19--查询咖啡类商品的平均售价SELECTAVG(saleprice)AS '咖啡的平均售价' FROM goods--AVG()取平均数WHERE goodsname LIKE '%咖啡%'

20．查询与张小红同学院的学生的学号、姓名、学院。

--20--查询与张小红同学院的学生的学号、姓名、学院。--查询与张小红同学院的学生的学号、姓名、学院SELECT sno,sname,college FROM studentWHERE college in (select college from student where sname='张小红')

21．查询所有商品的销售情况，包括商品号，商品名，学生姓名，销售时间，销售数量，按销售日期降序排列。

--21--查询所有商品的销售情况，包括商品号，商品名，学生姓名，销售时间，销售数量，--按销售日期降序排列。SELECT * FROM goods,salebill,studentWHERE goods.goodsno=salebill.goodsno AND salebill.sno=student.snoORDER BY happentime DESC--也可以用Join on 连接SELECT * FROM salebill s1 joingoods g on s1.goodsno=g.goodsnojoinstudent s2 on s1.sno=s2.snoORDER BY happentime DESC

22．查询购买了商品的学生学号、姓名、学院。

--22--查询购买了商品的学生学号、姓名、学院。SELECT DISTINCT student.sno,sname,college FROM student join salebillon student.sno=salebill.sno

23．查询购买了咖啡类商品的学生的学号、姓名、学院。

--23--查询购买了咖啡类商品的学生的学号、姓名、学院。SELECT sno,sname,collegeFROM student WHERE sno in (select sno from salebill wheregoodsno in (select goodsno from goodswhere goodsname like '%咖啡%'))--以上是对goodsname进行相似查询--可再次嵌套对category的categoryname=’咖啡'查询SELECT sno,sname,collegeFROM student WHERE sno in (select sno from salebill wheregoodsno in (select goodsno from goodswhere categoryno in (select categoryno from category wherecategoryname='咖啡')))

24．查询没有出售过的商品号，商品名，类别名、售价和数量。（连接查询和子查询）

--24--查询没有出售过的商品号，商品名，类别名、售价和数量。（连接查询和子查询）SELECT goods.goodsno,goodsname,categoryname,saleprice,number-- table_1 join table_2 on t1.column=t2.columnFROM goods join category on goods.categoryno=category.categorynoWHERE goods.goodsno not --通过column连接时候goods.goodsno在goods表中选择goodsnoIN (SELECT goods.goodsno FROM goods join salebill ON goods.goodsno=salebill.goodsno)

25．查询购买了商品号为“gn001”或“gn002”商品的学号，姓名和学院(1)。

--25--查询购买了商品号为“gn001”或“gn002”商品的学号，姓名和学院(1)。SELECT DISTINCT student.sno,sname,college FROM goods JOIN salebillON goods.goodsno=salebill.goodsnojoin student on student.sno=salebill.snoWHERE goods.goodsno Like 'gn00[1-2]'

26. 查询购买了商品号为“gn001”和“gn002”商品的学号，姓名和学院(2)。

--26查询购买了商品号为“gn001”或“gn002”商品的学号，姓名和学院(2)。select sno,sname,college from student where snoin (select sno from salebill where goodsno='gn001')orsno in(select sno from salebill where goodsno='gn002')

27. 查询各学院年龄最大的学生，显示学号，姓名和学院。（相关子查询和派生表）

--27查询各学院年龄最大的学生，显示学号，姓名和学院。select sno,sname,college from student swhere (YEAR(getdate())-birthyear)=(select MAX(YEAR(getdate())-birthyear)from student where s.college=student.college)

28. 统计每个商品的销售总数，要求显示商品号，商品名和销售总数。

--28统计每个商品的销售总数，要求显示商品号，商品名和销售总数。select goods.goodsno,goodsname,SUM(salebill.number)as 销售总数from goods,salebillwhere goods.goodsno=salebill.goodsno group by goods.goodsno,goodsname

29.查询2019年商品购买总数在5以上的学生的姓名、学院和购买总数。

--29查询2019年商品购买总数在5以上的学生的姓名、学院和购买总数。select sname,college,salebill.numberfrom goods join salebill on goods.goodsno=salebill.goodsnojoin student on salebill.sno=student.snowhere(happentime LIKE '%2019%')and salebill.number>=5group by sname,college,salebill.number

30. 找出供应商品总数在50以上的供应商号，供应商名，供应商地址，供应总数，结果按地址分组，并按供应总数的降序排列。

--30找出供应商品总数在50以上的供应商号，供应商名，供应商地址，供应总数，--结果按地址分组，并按供应总数的降序排列。select supplier.suppliername,address,SUM(goods.number)as 供应总数 from goods join supplier on goods.supplierno=supplier.suppliernogroup by address,suppliernamehaving SUM(goods.number)>50order by SUM(goods.number) desc

三.代码汇总

--实验二----1--查询全部商品信息。SELECT * FROM goods--2--查询类别为“cn002”的所有商品信息。SELECT *FROM goods WHERE categoryno='cn002'--3--查询类别为“cn001”和“cn002”对应的所有商品信息。SELECT *FROM goods WHERE categoryno='cn001' OR categoryno='cn002'--或者SELECT *FROM goods WHERE categoryno IN ('cn001','cn002')--4--查询类别为“cn005”且进价大于20的商品信息。SELECT *FROM goods WHERE categoryno='cn005' AND inprice>20--5--查询库存数量小于10的商品号，供应商号，商品名和数量。SELECT goodsno,supplierno,goodsname,number FROM goods WHERE number20ORDER BY number ASC--11--查询库存数量在10以内的商品编号、商品名称、类别号、数量，--按类别升序库存数量降序排列结果。SELECT goodsno,goodsname,categoryno,number FROM goodsWHERE number20--16--统计2018年各商品的销售总量。SELECT goodsno,happentime,number,COUNT(*) AS 个数 FROM salebillGROUP BY goodsno,number,happentimeHAVING happentime LIKE '%2018%'--17--查询学生人数在3人以上的学院及学生人数SELECT college,major,COUNT(*)AS '人数' FROM studentGROUP BY college,majorHAVING COUNT(*)>3--18----查询2019年商品购买总数量在5以上的学生的学号和购买总数SELECT sno,happentime,number,COUNT(*) FROM salebillWHERE number>=5 GROUP BY sno,number,happentimeHAVING happentime LIKE '%2019%'--19--查询咖啡类商品的平均售价SELECTAVG(saleprice)AS '咖啡的平均售价' FROM goods--AVG()取平均数WHERE goodsname LIKE '%咖啡%' --20--查询与张小红同学院的学生的学号、姓名、学院。--查询与张小红同学院的学生的学号、姓名、学院SELECT sno,sname,college FROM studentWHERE college in (select college from student where sname='张小红')--21--查询所有商品的销售情况，包括商品号，商品名，学生姓名，销售时间，销售数量，--按销售日期降序排列。SELECT * FROM goods,salebill,studentWHERE goods.goodsno=salebill.goodsno AND salebill.sno=student.snoORDER BY happentime DESC--也可以用Join on 连接SELECT * FROM salebill s1 joingoods g on s1.goodsno=g.goodsnojoinstudent s2 on s1.sno=s2.snoORDER BY happentime DESC--22--查询购买了商品的学生学号、姓名、学院。SELECT DISTINCT student.sno,sname,college FROM student join salebillon student.sno=salebill.sno--23--查询购买了咖啡类商品的学生的学号、姓名、学院。SELECT sno,sname,collegeFROM student WHERE sno in (select sno from salebill wheregoodsno in (select goodsno from goodswhere goodsname like '%咖啡%'))--以上是对goodsname进行相似查询--可再次嵌套对category的categoryname=’咖啡'查询SELECT sno,sname,collegeFROM student WHERE sno in (select sno from salebill wheregoodsno in (select goodsno from goodswhere categoryno in (select categoryno from category wherecategoryname='咖啡')))--24--查询没有出售过的商品号，商品名，类别名、售价和数量。（连接查询和子查询）SELECT goods.goodsno,goodsname,categoryname,saleprice,numberFROM goods join category on goods.categoryno=category.categorynoWHERE goods.goodsno not IN (SELECT goods.goodsno FROM goods join salebill ON goods.goodsno=salebill.goodsno) --25--查询购买了商品号为“gn001”或“gn002”商品的学号，姓名和学院(1)。SELECT DISTINCT student.sno,sname,college FROM goods JOIN salebillON goods.goodsno=salebill.goodsnojoin student on student.sno=salebill.snoWHERE goods.goodsno Like 'gn00[1-2]' --26查询购买了商品号为“gn001”或“gn002”商品的学号，姓名和学院(2)。select sno,sname,college from student where snoin (select sno from salebill where goodsno='gn001')orsno in(select sno from salebill where goodsno='gn002')--27查询各学院年龄最大的学生，显示学号，姓名和学院。select sno,sname,college from student swhere (YEAR(getdate())-birthyear)=(select MAX(YEAR(getdate())-birthyear)from student where s.college=student.college)--28统计每个商品的销售总数，要求显示商品号，商品名和销售总数。select goods.goodsno,goodsname,SUM(salebill.number)as 销售总数from goods,salebillwhere goods.goodsno=salebill.goodsno group by goods.goodsno,goodsname--29查询2019年商品购买总数在5以上的学生的姓名、学院和购买总数。select sname,college,salebill.numberfrom goods join salebill on goods.goodsno=salebill.goodsnojoin student on salebill.sno=student.snowhere(happentime LIKE '%2019%')and salebill.number>=5group by sname,college,salebill.number--30找出供应商品总数在50以上的供应商号，供应商名，供应商地址，供应总数，--结果按地址分组，并按供应总数的降序排列。select supplier.suppliername,address,SUM(goods.number)as 供应总数 from goods join supplier on goods.supplierno=supplier.suppliernogroup by address,suppliernamehaving SUM(goods.number)>50order by SUM(goods.number) desc