数据库技术及应用课程第2章 数据库查询-2课件

第2章

数据库查询2.1相关知识2.2实验一：单表查询2.3实验二：多表查询2.4实验三：复杂查询1第2章数据库查询2.1相关知识12.1相关知识本章使用的数据库是订单管理数据库OrderDB，由5张表组成，包括员工表、客户表、商品表、订单主表和订单明细表。22.1相关知识本章使用的数据库是订单管理数据库Order332.4实验三：复杂查询2.4.1实验目的与要求(1)熟练掌握SQL语句的使用。(2)熟练使用SQL语句进行连接操作。42.4实验三：复杂查询2.4.1实验目的与要求42.4.2实验案例1．复杂表连接操作[例2.40]查找订购了“32MDRAM”的商品的客户编号、客户名称、订货总数量和订货总金额。①查询客户编号、客户名称、订货总数量和订货总金额，涉及三张表的连接：客户表、订单主表和订单明细表。②查询订购了“32MDRAM”商品的客户，必须与商品表做连接操作，同时对商品表做一个选取操作，选取条件是商品名称为“32MDRAM”。③本例要使用分组操作，按客户编号和客户名称进行分组，统计订货总数量和订货总金额，注意该题与例2.34的区别。52.4.2实验案例1．复杂表连接操作5[例2.42]按商品类别查询每类商品的订货平均单价在280元(含280元)以上的订货总数量、订货平均单价和订货总金额①商品类别在商品表中，订货单价在订单明细表中，该查询涉及这两张表的连接操作。②按商品类别分组统计每类商品的订货总数量、订货平均单价和订货总金额。③必须对分组后的元组进行过滤，仅检索平均单价在280元(含280元)以上的商品，使用HAVINGavg(price)>=280子句。6[例2.42]按商品类别查询每类商品的订货平均单价在280[例2.43]查找至少有2次销售的业务员名单和销售日期①本例可以使用子查询或连接方法实现。②构造一个子查询，在订单主表中按销售员进行分组查询至少有2次销售记录的销售员编号。子查询语句为：③要查询业务员名单和销售日期，涉及员工表和销售主表的连接操作。④在连接的结果上，检索员工编号在子查询中销售员编号集合中。⑤使用子查询的SQL语句为：7[例2.43]查找至少有2次销售的业务员名单和销售日期7查询表与普通表、视图的使用方法相同，但是查询表出现在

FROM子句中必须为其指定元组变量名。[例2.43]查找至少有2次销售的业务员名单和销售日期⑥使用连接方法，构造一个查询表，在订单主表中按销售员进行分组查询至少有2次销售记录的销售员编号，将该查询表与员工表和订单主表进行连接操作8查询表与普通表、视图的使用方法相同，但是查询表出现在FRO[例2.44]查找订货金额最大的客户名称和总货款①本例可以使用子查询或连接方法实现。②使用连接方法，需要使用查询表用于查询在订单主表中，按客户编号进行分组统计每个客户的客户编号和订单总额然后从该查询表中查询最大的订单总额，该查询表的SQL语句SELECTcustomerNo,sum(orderSum)assumOrderFROMOrderMasterGROUPBYcustomerNo③再构造一个查询表，用于查询最高的订单总额和客户编号即从上面的查询表中，选择其订单总额最高的客户编号和相应的订单总额最后将该查询表与客户表进行连接得到结果。9[例2.44]查找订货金额最大的客户名称和总货款①本例可[例2.44]查找订货金额最大的客户名称和总货款⑤使用子查询方法由于订单金额在订单主表中，客户名称在客户表中，需要对这两张表进行连接操作在连接的基础上，按客户编号和名称进行分组统计每个客户的订单总额。⑥对分组后的每个客户的订单总额，要求查询总额最高的客户，必须进一步对分组求条使用HAVING子句HAVING子句可以直接对集聚函数进行操作在HAVING子句中构造一个子查询，该子查询用于查询客户订单总额中的最高客户，该子查询用到了查询表10[例2.44]查找订货金额最大的客户名称和总货款⑤使用子[例2.45]查找销售总额少于5000元的销售员编号、姓名和销售额。①本例也有多种解法，使用查询表或直接使用分组集聚操作。11[例2.45]查找销售总额少于5000元的销售员编号、姓名[例2.46]查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额12[例2.46]查找至少订购了3种商品的客户编号、客户名称、[例2.47]查找同时订购了商品编号为“P20060002”和商品编号为“P20070002”商品的客户编号、客户姓名、商品编号、商品名称和销售数量，按客户编号排序输出①查询客户编号、客户姓名、商品编号、商品名称和销售数量，需要使用客户表、商品表、订单主表和订单明细表的连接操作。②要查询同时订购了商品编号为“P20060002”和“P20070002”的客户，需要两个选取条件客户编号要同时在订购了商品编号为“P20060002”和商品编号为“P20070002”的客户编号集合中使用两个子查询完成该选取条件。③若输出结果仅包含这两种商品，还必须有一个选取条件，即商品编号在'P20070002'或'P20070001'中。13[例2.47]查找同时订购了商品编号为“P2006000214142．存在量词运算[例2.49]查询订购了“键盘”商品的客户姓名、订货数量和订货日期。①查询客户姓名、订货数量和订货日期，涉及客户表、订单主表和订单明细表的连接操作。②查询订购了“键盘”商品的客户，使用一个相关子查询，针对外查询的每一个客户，判断其是否订购了“键盘”EXISTS(SELECTcustomerNoFROMOrderMastere,OrderDetailf,ProductgWHEREe.orderNo=f.orderNoANDductNo=ductNoANDa.customerNo=e.customerNoANDproductName='键盘')其中的a.customerNo是外查询中的某个客户编号。③如果仅查询订购了“键盘”商品的客户，还必须满足一个条件，即查询出的商品只包含“键盘”商品152．存在量词运算[例2.49]查询订购了“键盘”商品的客户[例2.50]查询没有订购“键盘”商品的客户名称16[例2.50]查询没有订购“键盘”商品的客户名称16[例2.51]查询至少销售了5种商品的销售员编号、姓名、商品名称、数量及相应的单价，并按销售员编号排序输出构造一个子查询，针对外查询中的每个销售员，判断其是否销售了5种以上的商品，使用相关子查询。17[例2.51]查询至少销售了5种商品的销售员编号、姓名、商[例2.52]查询没有订购商品的客户编号和客户名称18[例2.52]查询没有订购商品的客户编号和客户名称18[例2.53]查询至少包含了“世界技术开发公司”所订购的商品的客户编号、客户名称、商品编号、商品名称、数量和金额①本例需要使用双重否定，第一重否定，用于查询“世界技术开发公司”订购了哪些商品。②第二重否定，对最外层的某个客户，不存在着这样的商品，“世界技术开发公司”订购了而该客户没有订购19[例2.53]查询至少包含了“世界技术开发公司”所订购的商2.4.3实验三实验内容(1)在订单明细表中查询订单金额最高的订单。(2)找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。(3)查找销售总额少于5000元的销售员编号、姓名和销售额。(4)找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。(5)查询订购的商品数量没有超过10个的客户编号和客户名称。(6)查找订货金额最大的客户名称和总货款。(7)查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。202.4.3实验三实验内容(1)在订单明细表中查询订单2.4.3实验内容(8)找出目前销售业绩超过4000元的业务员编号及销售业绩，并按销售业绩从大到小排序。(9)求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。(10)查询业绩最好的的业务员号、业务员名及其总销售金额。(11)查询订购的商品至少包含了订单“200803010001”中所订购商品的订单。(12)查询总订购金额超过“C20070002”客户的总订购金额的客户号、客户名及其住址。(13)查询总销售金额最高的销售员编号、订单编号、订单日期和订单金额。(14)用存在量词查找没有订货记录的客户名称。212.4.3实验内容(8)找出目前销售业绩超过4000元2.4.3实验内容(15)查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。(16)求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。(17)实验问题：①存在量词与集合运算IN、连接运算和全称量词之间的关系如何？它们可以互相替换吗？给出你的理由。②请写出例2.51的执行过程。③存在量词一般用在相关子查询中，请分别给出存在量词用在相关子查询和非相关子查询的查询例子。222.4.3实验内容(15)查询既订购了“52倍速光驱”第2章

数据库查询2.1相关知识2.2实验一：单表查询2.3实验二：多表查询2.4实验三：复杂查询23第2章数据库查询2.1相关知识12.1相关知识本章使用的数据库是订单管理数据库OrderDB，由5张表组成，包括员工表、客户表、商品表、订单主表和订单明细表。242.1相关知识本章使用的数据库是订单管理数据库Order2532.4实验三：复杂查询2.4.1实验目的与要求(1)熟练掌握SQL语句的使用。(2)熟练使用SQL语句进行连接操作。262.4实验三：复杂查询2.4.1实验目的与要求42.4.2实验案例1．复杂表连接操作[例2.40]查找订购了“32MDRAM”的商品的客户编号、客户名称、订货总数量和订货总金额。①查询客户编号、客户名称、订货总数量和订货总金额，涉及三张表的连接：客户表、订单主表和订单明细表。②查询订购了“32MDRAM”商品的客户，必须与商品表做连接操作，同时对商品表做一个选取操作，选取条件是商品名称为“32MDRAM”。③本例要使用分组操作，按客户编号和客户名称进行分组，统计订货总数量和订货总金额，注意该题与例2.34的区别。272.4.2实验案例1．复杂表连接操作5[例2.42]按商品类别查询每类商品的订货平均单价在280元(含280元)以上的订货总数量、订货平均单价和订货总金额①商品类别在商品表中，订货单价在订单明细表中，该查询涉及这两张表的连接操作。②按商品类别分组统计每类商品的订货总数量、订货平均单价和订货总金额。③必须对分组后的元组进行过滤，仅检索平均单价在280元(含280元)以上的商品，使用HAVINGavg(price)>=280子句。28[例2.42]按商品类别查询每类商品的订货平均单价在280[例2.43]查找至少有2次销售的业务员名单和销售日期①本例可以使用子查询或连接方法实现。②构造一个子查询，在订单主表中按销售员进行分组查询至少有2次销售记录的销售员编号。子查询语句为：③要查询业务员名单和销售日期，涉及员工表和销售主表的连接操作。④在连接的结果上，检索员工编号在子查询中销售员编号集合中。⑤使用子查询的SQL语句为：29[例2.43]查找至少有2次销售的业务员名单和销售日期7查询表与普通表、视图的使用方法相同，但是查询表出现在

FROM子句中必须为其指定元组变量名。[例2.43]查找至少有2次销售的业务员名单和销售日期⑥使用连接方法，构造一个查询表，在订单主表中按销售员进行分组查询至少有2次销售记录的销售员编号，将该查询表与员工表和订单主表进行连接操作30查询表与普通表、视图的使用方法相同，但是查询表出现在FRO[例2.44]查找订货金额最大的客户名称和总货款①本例可以使用子查询或连接方法实现。②使用连接方法，需要使用查询表用于查询在订单主表中，按客户编号进行分组统计每个客户的客户编号和订单总额然后从该查询表中查询最大的订单总额，该查询表的SQL语句SELECTcustomerNo,sum(orderSum)assumOrderFROMOrderMasterGROUPBYcustomerNo③再构造一个查询表，用于查询最高的订单总额和客户编号即从上面的查询表中，选择其订单总额最高的客户编号和相应的订单总额最后将该查询表与客户表进行连接得到结果。31[例2.44]查找订货金额最大的客户名称和总货款①本例可[例2.44]查找订货金额最大的客户名称和总货款⑤使用子查询方法由于订单金额在订单主表中，客户名称在客户表中，需要对这两张表进行连接操作在连接的基础上，按客户编号和名称进行分组统计每个客户的订单总额。⑥对分组后的每个客户的订单总额，要求查询总额最高的客户，必须进一步对分组求条使用HAVING子句HAVING子句可以直接对集聚函数进行操作在HAVING子句中构造一个子查询，该子查询用于查询客户订单总额中的最高客户，该子查询用到了查询表32[例2.44]查找订货金额最大的客户名称和总货款⑤使用子[例2.45]查找销售总额少于5000元的销售员编号、姓名和销售额。①本例也有多种解法，使用查询表或直接使用分组集聚操作。33[例2.45]查找销售总额少于5000元的销售员编号、姓名[例2.46]查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额34[例2.46]查找至少订购了3种商品的客户编号、客户名称、[例2.47]查找同时订购了商品编号为“P20060002”和商品编号为“P20070002”商品的客户编号、客户姓名、商品编号、商品名称和销售数量，按客户编号排序输出①查询客户编号、客户姓名、商品编号、商品名称和销售数量，需要使用客户表、商品表、订单主表和订单明细表的连接操作。②要查询同时订购了商品编号为“P20060002”和“P20070002”的客户，需要两个选取条件客户编号要同时在订购了商品编号为“P20060002”和商品编号为“P20070002”的客户编号集合中使用两个子查询完成该选取条件。③若输出结果仅包含这两种商品，还必须有一个选取条件，即商品编号在'P20070002'或'P20070001'中。35[例2.47]查找同时订购了商品编号为“P2006000236142．存在量词运算[例2.49]查询订购了“键盘”商品的客户姓名、订货数量和订货日期。①查询客户姓名、订货数量和订货日期，涉及客户表、订单主表和订单明细表的连接操作。②查询订购了“键盘”商品的客户，使用一个相关子查询，针对外查询的每一个客户，判断其是否订购了“键盘”EXISTS(SELECTcustomerNoFROMOrderMastere,OrderDetailf,ProductgWHEREe.orderNo=f.orderNoANDductNo=ductNoANDa.customerNo=e.customerNoANDproductName='键盘')其中的a.customerNo是外查询中的某个客户编号。③如果仅查询订购了“键盘”商品的客户，还必须满足一个条件，即查询出的商品只包含“键盘”商品372．存在量词运算[例2.49]查询订购了“键盘”商品的客户[例2.50]查询没有订购“键盘”商品的客户名称38[例2.50]查询没有订购“键盘”商品的客户名称16[例2.51]查询至少销售了5种商品的销售员编号、姓名、商品名称、数量及相应的单价，并按销售员编号排序输出构造一个子查询，针对外查询中的每个销售员，判断其是否销售了5种以上的商品，使用相关子查询。39[例2.51]查询至少销售了5种商品的销售员编号、姓名、商[例2.52]查询没有订购商品的客户编号和客户名称40[例2.52]查询没有订购商品的客户编号和客户名称18[例2.53]查询至少包含了“世界技术开发公司”所订购的商品的客户编号、客户名称、商品编号、商品名称、数量和金额①本例需要使用双重否定，第一重否定，用于查询“世界技术开发公司”订购了哪些商品。②第二重否定，对最外层的某个客户，不存在着这样的商品，“世界技术开发公司”订购了而该客户没有订购41[例2.53]查询至少包含了“世界技术开发公司”所订购的商2.4.3实验三实验内容(1)在订单明细表中查询订单金额最高的订单。(2)找出至少被订购3次