石家庄网站制作网页,南沙网站建设wwiw,最热门的网络游戏排行,旅游网站建设研究综述目录
复杂查询
一、目的和要求
二、实验内容
#xff08;1#xff09;查询出所有水果产品的类别及详情。
查询出编号为“00000001”的消费者用户的姓名及其所下订单。#xff08;分别采用子查询和连接方式实现#xff09;
查询出每个订单的消费者姓名及联系方式。
在…目录
复杂查询
一、目的和要求
二、实验内容
1查询出所有水果产品的类别及详情。
查询出编号为“00000001”的消费者用户的姓名及其所下订单。分别采用子查询和连接方式实现
查询出每个订单的消费者姓名及联系方式。
在3的基础上查出每个订单的消费者姓名和助农商户姓名。
查询出所有消费者用户的用户名和订单信息。
查询出所有类别的类别名称对应的水果产品名称和单价。
查出所有平均库存比“核果类”类别的高的所有类别名称。
统计出“浆果类”类别的总库存。
查询出所有没有订单的消费者用户信息。
3、实验分析讨论
查询 1使用子查询和连接
查询 2使用旧式连接和 IN 子查询
不考虑数据库管理系统的自动优化没有排序和索引
假设订单表已经按照消费者编号和水果编号进行排序
结论
使用 SSMS 查询计划观察
三、实验小结
总体来说关键步骤包括
完整代码 复杂查询
一、目的和要求
1、了解笛卡尔积外连接与内连接等值连接与自然连接的结果运算方式。
2、熟悉连接运算的sql语句语法。
3、学会使用子查询和连接运算查询出所需来源于多张表的数据。
二、实验内容
1、附加或还原前面实验所建立的助农水果销售系统数据库数据库名为ZNSGXS。如自己没有备份可以直接执行所附的实验二.sql。
2、复杂查询练习查询语句可以直接保存为扩展名为sql的文本文件可以把本次实验所用的程序放到一个文本文件中 sql语句需写入实验报告。
1查询出所有水果产品的类别及详情。
select CategoryName,Remark,FruitName from (select * from FruitCategory) as A
join
(select CategoryID,FruitName from Fruit) as B
on A.CategoryIDB.CategoryID 查询出编号为“00000001”的消费者用户的姓名及其所下订单。分别采用子查询和连接方式实现
select A.ConsumerName,C.FruitID,C.OrderDate,C.OrderID,C.Quantity from (select UserID,ConsumerName from Consumer
where UserIDuser001 ) as A
join
(select * from Orders
where UserIDuser001)as C
on A.UserIDC.UserID select B.ConsumerName, OrderID, OrderDate, Quantity, FruitID
from Orders as A,Consumer as B
where A.UserID user001 AND B.UserID IN (select UserID from Consumer where UserID user001); 查询出每个订单的消费者姓名及联系方式。
select A.ConsumerName,A.ContactNumber,B.OrderID from (select UserID,ConsumerName,ContactNumber from Consumer)as A
join
(select UserID,OrderID from Orders) as B
on A.UserIDB.UserID 在3的基础上查出每个订单的消费者姓名和助农商户姓名。
select A.ConsumerName,B.OrderID,D.MerchantName from ((select UserID,ConsumerName,ContactNumber from Consumer)as A
join
(select UserID,OrderID,FruitID from Orders) as B
on A.UserIDB.UserID)
join
(select FruitID,MerchantID from Fruit) as C
on B.FruitIDc.FruitID
join
(select MerchantID,MerchantName from Merchant) as D
on D.MerchantIDC.MerchantID 查询出所有消费者用户的用户名和订单信息。
select A.UserName,OrderID,OrderDate,FruitID,Quantity from (select UserID,UserName from Consumer) as A
join
(select * from Orders) as B
on A.UserIDB.UserID 查询出所有类别的类别名称对应的水果产品名称和单价。
select A.FruitName,A.UnitPrice,B.CategoryName from (select FruitName,CategoryID,UnitPrice from Fruit) as A
join
(select CategoryID,CategoryName from FruitCategory) as B
on A.CategoryIDB.CategoryID 查出所有平均库存比“核果类”类别的高的所有类别名称。
select A.CategoryName, A.CategoryID from (select CategoryID, CategoryName from FruitCategory) as A
join
(select categoryid, stock from fruit) as B
on A.CategoryID B.CategoryID
group by A.CategoryName, A.CategoryID
having avg(b.stock) (
select avg(stock) from fruit
join
fruitcategory
on fruit.CategoryID fruitcategory.CategoryID
where fruitcategory.CategoryName 核果类
); 统计出“浆果类”类别的总库存。
select sum(stock)as浆果类总库存 from fruit
join
fruitcategory
on fruit.CategoryID fruitcategory.CategoryID
where fruitcategory.CategoryName 浆果类 查询出所有没有订单的消费者用户信息。
select C.* from Consumer as C
left join
Orders as O
on C.UserID O.UserID
where O.OrderID is null; 查询出所有有订单的消费者用户信息。
select distinct C.* from Consumer as C
inner join
Orders as O
on C.UserID O.UserID; 3、实验分析讨论
对复杂查询练习的第2小题进行分析如果不考虑数据库管理系统的自动优化即假设数据没有采用任何方式排序仅考虑运算方式哪种查询的效率更高为什么再假设实际数据库订单表中已经按照消费者编号水果编号进行排序但仍不考虑数据库管理系统的自动优化哪种查询的效率更高为什么再使用SSMS的查询计划观察两个语句的查询计划是否一致验证自动优化后的结果。
查询分析
查询 1使用子查询和连接
select A.ConsumerName,C.FruitID,C.OrderDate,C.OrderID,C.Quantity from (select UserID,ConsumerName from Consumer
where UserIDuser001 ) as A
join
(select * from Orders
where UserIDuser001)as C
on A.UserIDC.UserID
内层的第一个子查询 (SELECT UserID, ConsumerName FROM Consumer WHERE UserID user001) 执行时从 Consumer 表中筛选出 UserID user001 的消费者信息。此操作可能会扫描整个 Consumer 表。
第二个内层子查询 (SELECT * FROM Orders WHERE UserID user001) 从 Orders 表中筛选出 UserID user001 的所有订单信息通常会对整个 Orders 表进行扫描。
外层查询执行 内连接 (JOIN)将两个子查询的结果通过 UserID 字段进行连接。
如果没有索引查询需要对 Consumer 和 Orders 表进行全表扫描。
由于查询中使用了子查询数据库需要首先完成子查询操作再进行连接。
子查询的执行可能会重复计算如果数据量很大性能会受到影响尤其是每个子查询都可能执行一次全表扫描。
查询 2使用旧式连接和 IN 子查询
select B.ConsumerName, OrderID, OrderDate, Quantity, FruitID
from Orders as A,Consumer as B
where A.UserID user001 AND B.UserID IN (select UserID from Consumer where UserID user001);
IN 子查询 (SELECT UserID FROM Consumer WHERE UserID user001) 从 Consumer 表中筛选出特定的 UserID类似于查询 1 中的第一个子查询但此时子查询会在整个查询执行之前执行一次确定用户 user001 的 UserID。
外层查询 Orders AS A, Consumer AS B 使用 笛卡尔积Cross Join 来连接 Orders 和 Consumer 表查询条件限定了 A.UserID user001 和 B.UserID user001使得笛卡尔积结果通过 WHERE 条件被过滤。
IN 子查询的执行通常需要对 Consumer 表进行扫描。假设 Consumer 表比较大可能需要执行全表扫描。
由于 IN 子查询可能会执行多次且笛卡尔积可能产生大量的中间结果查询的效率较低尤其在数据量大的情况下。 不考虑数据库管理系统的自动优化没有排序和索引
从执行顺序来看查询 1子查询和连接需要对 Consumer 和 Orders 表分别执行子查询连接时再进行一次匹配导致重复计算。
查询 2IN 子查询也会执行全表扫描但它的结构相对简单一些。IN 子查询会首先执行一次结果然后传递给主查询。
在没有排序和索引的情况下查询 2 会稍微高效一些因为它没有重复的子查询执行且连接操作相对简单一些。虽然 IN 子查询存在一定的性能问题但它不会导致每个外层查询都进行子查询的重复执行。 假设订单表已经按照消费者编号和水果编号进行排序
假设 Orders 表已经按照 UserID 和 FruitID 排序查询 1子查询和连接
排序优化
如果 Orders 表已经按照 UserID 排序连接操作可以利用排序来提高效率减少数据扫描的成本。数据库系统可以使用 合并连接Merge Join 来连接已排序的表。
由于数据已经排序合并连接可能会比哈希连接等算法更高效减少了排序的开销。
查询 2旧式连接和 IN 子查询
排序优化
如果 Orders 表已经排序IN 子查询的优化效果较小。IN 子查询依然需要对 Consumer 表进行扫描而且连接仍然是通过 WHERE 子句过滤的排序不会对 IN 子查询的效率产生显著的优化效果。
结论
在 Orders 表已排序的情况下查询 1子查询 的效率会大大提高特别是如果数据库能够利用合并连接Merge Join来利用排序。
使用 SSMS 查询计划观察 三、实验小结
查询所有水果产品的类别及详情
使用 JOIN 语句将 FruitCategory 和 Fruit 表连接通过 CategoryID 提取水果类别及其详细信息。这样可以查询每个水果所属的类别和水果名称。
查询编号为“user001”的消费者用户的姓名及其所下订单
通过多表联接查询获取特定消费者的姓名以及该消费者所下的所有订单信息包括水果编号、订单日期和数量。
查询每个订单的消费者姓名及联系方式
使用 INNER JOIN 将 Consumer 表和 Orders 表连接查询每个订单对应的消费者姓名和联系方式。
查询每个订单的消费者姓名和助农商户姓名
通过多次表连接获取每个订单的消费者信息以及与其相关联的商户姓名形成完整的订单信息链。
查询所有消费者用户的用户名和订单信息
将 Consumer 和 Orders 表进行连接查询所有消费者的用户名和他们所下的所有订单信息。
查询所有类别的类别名称对应的水果产品名称和单价
通过连接 Fruit 和 FruitCategory 表获取每种水果的名称、类别和单价。
查出所有平均库存比“核果类”类别的高的所有类别名称
通过子查询计算“核果类”的平均库存再筛选出库存高于该值的其他类别确保数据准确并满足条件。
统计“浆果类”类别的总库存
使用 SUM() 聚合函数计算“浆果类”水果的总库存确保查询的是该类别下所有水果的库存总和。
查询所有没有订单的消费者用户信息
通过 LEFT JOIN 查询所有没有下订单的消费者信息确保未下订单的消费者也能被正确提取。
查询所有有订单的消费者用户信息
使用 INNER JOIN 查找所有下过订单的消费者信息确保返回的仅为那些有订单记录的消费者。
总体来说关键步骤包括
连接表通过 JOIN 连接不同的表来获取更丰富的数据。
筛选数据使用 WHERE 或子查询来筛选满足特定条件的数据。
聚合函数例如使用 SUM() 来计算库存的总和。
完整代码 --查询出所有水果产品的类别及详情。
select CategoryName,Remark,FruitName from (select * from FruitCategory) as Ajoin(select CategoryID,FruitName from Fruit) as Bon A.CategoryIDB.CategoryID--查询出编号为“00000001”的消费者用户的姓名及其所下订单。
select A.ConsumerName,C.FruitID,C.OrderDate,C.OrderID,C.Quantity from (select UserID,ConsumerName from Consumerwhere UserIDuser001 ) as Ajoin(select * from Orders where UserIDuser001)as Con A.UserIDC.UserID select B.ConsumerName, OrderID, OrderDate, Quantity, FruitID
from Orders as A,Consumer as B
where A.UserID user001AND B.UserID IN (select UserID from Consumer where UserID user001);--查询出每个订单的消费者姓名及联系方式。
select A.ConsumerName,A.ContactNumber,B.OrderID from (select UserID,ConsumerName,ContactNumber from Consumer)as Ajoin (select UserID,OrderID from Orders) as Bon A.UserIDB.UserID--在3的基础上查出每个订单的消费者姓名和助农商户姓名。
select A.ConsumerName,B.OrderID,D.MerchantName from ((select UserID,ConsumerName,ContactNumber from Consumer)as Ajoin (select UserID,OrderID,FruitID from Orders) as Bon A.UserIDB.UserID)join (select FruitID,MerchantID from Fruit) as Con B.FruitIDc.FruitIDjoin(select MerchantID,MerchantName from Merchant) as Don D.MerchantIDC.MerchantID--查询出所有消费者用户的用户名和订单信息。
select A.UserName,OrderID,OrderDate,FruitID,Quantity from (select UserID,UserName from Consumer) as Ajoin(select * from Orders) as Bon A.UserIDB.UserID--查询出所有类别的类别名称对应的水果产品名称和单价。
select A.FruitName,A.UnitPrice,B.CategoryName from (select FruitName,CategoryID,UnitPrice from Fruit) as Ajoin(select CategoryID,CategoryName from FruitCategory) as Bon A.CategoryIDB.CategoryID--查出所有平均库存比“核果类”类别的高的所有类别名称。select A.CategoryName, A.CategoryID from (select CategoryID, CategoryName from FruitCategory) as Ajoin (select categoryid, stock from fruit) as Bon A.CategoryID B.CategoryIDgroup by A.CategoryName, A.CategoryIDhaving avg(b.stock) (select avg(stock) from fruit join fruitcategory on fruit.CategoryID fruitcategory.CategoryIDwhere fruitcategory.CategoryName 核果类);--统计出“浆果类”类别的总库存。select sum(stock)as浆果类总库存 from fruit join fruitcategory on fruit.CategoryID fruitcategory.CategoryIDwhere fruitcategory.CategoryName 浆果类--查询出所有没有订单的消费者用户信息。
select C.* from Consumer as Cleft join Orders as O on C.UserID O.UserIDwhere O.OrderID is null;--查询出所有有订单的消费者用户信息。
select distinct C.* from Consumer as Cinner join Orders as O on C.UserID O.UserID;
