SQL 复杂查询

目录

复杂查询

一、目的和要求

二、实验内容

（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.CategoryID=B.CategoryID

  1. 查询出编号为“00000001”的消费者用户的姓名及其所下订单。（分别采用子查询和连接方式实现）

select A.ConsumerName,C.FruitID,C.OrderDate,C.OrderID,C.Quantity from (select  UserID,ConsumerName from Consumer

where UserID='user001' ) as A

join

(select * from Orders

where UserID='user001')as C

on A.UserID=C.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');

  1. 查询出每个订单的消费者姓名及联系方式。

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.UserID=B.UserID

  1. 在（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.UserID=B.UserID)

join 

(select FruitID,MerchantID from Fruit) as C

on B.FruitID=c.FruitID

join

(select MerchantID,MerchantName from Merchant) as D

on D.MerchantID=C.MerchantID

  1. 查询出所有消费者用户的用户名和订单信息。

select A.UserName,OrderID,OrderDate,FruitID,Quantity from (select UserID,UserName from Consumer) as A

join

(select * from Orders) as B

on A.UserID=B.UserID

  1. 查询出所有类别的类别名称，对应的水果产品名称和单价。

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.CategoryID=B.CategoryID

  1. 查出所有平均库存比“核果类”类别的高的所有类别名称。

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 = '核果类'

);

  1. 统计出“浆果类”类别的总库存。

select sum(stock)as'浆果类总库存' from fruit

join 

fruitcategory

on fruit.CategoryID = fruitcategory.CategoryID

where fruitcategory.CategoryName = '浆果类'

  1. 查询出所有没有订单的消费者用户信息。

select C.* from Consumer as C

left join 

Orders as O

on C.UserID = O.UserID

where O.OrderID is null;

  1. 查询出所有有订单的消费者用户信息。

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 UserID='user001' ) as A

join

(select * from Orders

where UserID='user001')as C

on A.UserID=C.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 表分别执行子查询，连接时再进行一次匹配，导致重复计算。

查询 2（IN 子查询）也会执行全表扫描，但它的结构相对简单一些。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 A
				join
				(select CategoryID,FruitName from Fruit) as B
				on A.CategoryID=B.CategoryID

--查询出编号为“00000001”的消费者用户的姓名及其所下订单。
select A.ConsumerName,C.FruitID,C.OrderDate,C.OrderID,C.Quantity from (select  UserID,ConsumerName from Consumer
						where UserID='user001' ) as A
						join
						(select * from Orders 
						where UserID='user001')as C
						on A.UserID=C.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.UserID=B.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.UserID=B.UserID)
				join 
				(select FruitID,MerchantID from Fruit) as C
				on B.FruitID=c.FruitID
				join
				(select MerchantID,MerchantName from Merchant) as D
				on D.MerchantID=C.MerchantID

--查询出所有消费者用户的用户名和订单信息。
select A.UserName,OrderID,OrderDate,FruitID,Quantity from (select UserID,UserName from Consumer) as A
				join
				(select * from Orders) as B
				on A.UserID=B.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.CategoryID=B.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;

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/921161.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

如何在 UniApp 中实现 iOS 版本更新检测

如何在 UniApp 中实现 iOS 版本更新检测

随着移动应用的不断发展，保持应用程序的更新是必不可少的，这样用户才能获得更好的体验。本文将帮助你在 UniApp 中实现 iOS 版的版本更新检测和提示，适合刚入行的小白。我们将分步骤进行说明，每一步所需的代码及其解释都会一一列出…
阅读更多...
ssm面向品牌会员的在线商城小程序

ssm面向品牌会员的在线商城小程序

摘要 随着Internet的发展，人们的日常生活已经离不开网络。未来人们的生活与工作将变得越来越数字化，网络化和电子化。它将是直接管理面向品牌会员的在线商城小程序的最新形式。本小程序是以面向品牌会员的在线商城管理为目标，使用 java技术制…
阅读更多...
《OpenCV 图像缩放、翻转与变换全攻略：从基础操作到高级应用实战》

《OpenCV 图像缩放、翻转与变换全攻略：从基础操作到高级应用实战》

简介：本文详细阐述了 OpenCV 在图像操作中的关键技术，包括缩放（确定尺寸缩放与按比例缩放）、翻转（沿不同轴的翻转方式）以及变换（平移、旋转、三点确定变换和四点确定变换即透视变换）…
阅读更多...
sql注入报错分享（mssql+mysql）

sql注入报错分享（mssql+mysql）

mysql mysql的报错内容比较多 网上也有比较多的 这里重复的就不多介绍了。一笔带过 溢出类 bigint 当超过mysql的整形的时候，就会导致溢出，mysql可能会将错误信息带出。这里user()是字母默认为0 取反以后1可能就会导致异常。 报错特征 BIGINT UNSIG…
阅读更多...
FastAPI重载不生效？解决PyCharm中Uvicorn无法重载/重载缓慢的终极方法！

FastAPI重载不生效？解决PyCharm中Uvicorn无法重载/重载缓慢的终极方法！

文章目录 📖 介绍 📖🏡 演示环境 🏡📒 重载缓慢 📒📝 问题概述🚨 相关原因📝 解决方案一📝 解决方案二📝 解决方案三📝 解决方案四⚓️ 相关链接 ⚓️📖 介绍 📖 在使用FastAPI开发时，reload=True 本应让你在修改代码后自动重启服务，提升开发效率…
阅读更多...
AI智能稿件排版系统订单管理系统

AI智能稿件排版系统订单管理系统

在现代制造业和服务行业中，高效的生产流程和精确的订单管理是企业保持竞争优势的核心要素。AI智能稿件排版系统和订单管理系统作为一体化解决方案，以其强大的自动化能力和智能化技术，帮助企业实现排版效率提升、数据格式兼容性增强和生产流程…
阅读更多...
jetson orin系列开发版安装cuda的gpu版本的opencv

jetson orin系列开发版安装cuda的gpu版本的opencv

opencv安装包下载地址： https://github.com/opencv/opencv/扩展库下载地址： https://github.com/opencv/opencv_contrib1. 删除jetpack包中的opencv版本 原先的opencv库安装在目录/usr/lib/aarch64-linux-gnu/下（一般其他的第三方库也都安…
阅读更多...
24小时自动监控，自动录制直播蓝光视频！支持抖音等热门直播软件

24小时自动监控，自动录制直播蓝光视频！支持抖音等热门直播软件

文章目录 📖 介绍 📖🏡 演示环境 🏡📒 工具特点📒📝 使用🎈 获取方式 🎈⚓️ 相关链接 ⚓️📖 介绍 📖 对于许多直播爱好者和内容创作者而言，错过心爱的直播或难以搜集视频素材始终是一个难题。今天，给大家分享的这款工具可以轻松解决这个问题，它拥有…
阅读更多...
dockerfile构建Nginx镜像练习二（5-2）

dockerfile构建Nginx镜像练习二（5-2）

环境准备： (1)保证拥有centos基础镜像 docker images | grep centos (2)服务器保证可以连接外网 1.创建工作目录 mkdir nginx cd nginx 2.在工作目录中创建并编写Dockerfile文件 vim dockerfile #定义基础镜像 FROM centos:7#维护者信息(可缺省) MAINTAINER d…
阅读更多...
Etcd 框架

Etcd 框架

基本了解 客户端、长连接与租约的关系 客户端对象 etcd的客户端对象是用户与etcd服务进行交互的主要接口，主要功能就是存储、通知和事务等功能访问 键值存储：客户端通过put 和 get操作存储数据；数据存储在etcd的层级化键值数据库中监听器&a…
阅读更多...
滑动窗口篇——如行云流水般的高效解法与智能之道（1）

滑动窗口篇——如行云流水般的高效解法与智能之道（1）

前言： 上篇我们介绍了双指针算法，并结合具体题目进行了详细的运用讲解。本篇我们将会了解滑动窗口。滑动窗口是一种常用的算法技巧，主要用于处理子数组、子串等具有“窗口”特性的题目。柳暗花明，乃巧解复杂问题的高效之道。 一. …
阅读更多...
数据结构-树状数组专题（2）

数据结构-树状数组专题（2）

一、前言 接上回树状数组专题&#xff08;1&#xff09;&#xff0c;这次主要介绍差分跟树状数组联动实现区间更新 二、我的模板 重新放了一遍&#xff0c;还是提一嘴&#xff0c;注意下标从0开始&#xff0c;区间左闭右开 template <typename T> struct Fenwick {in…
阅读更多...
QA｜使用 MapleSim 模拟卷料生产 (Converting)和卷对卷系统 (R2R)

QA｜使用 MapleSim 模拟卷料生产 (Converting)和卷对卷系统 (R2R)

使用 MapleSim 模拟卷料生产 (Converting)和卷对卷系统 (R2R) 纸张、薄膜、塑料、金属箔、新能源电池和卷料生产设备 (converting equipment) 的制造商正在转向建模和仿真&#xff0c;以提升卷料处理的设备性能和产品质量。MapleSim 卷料处理库提供了专业的建模元件以及功能&a…
阅读更多...
2024ARM网络验证 支持一键云注入引流弹窗注册机 一键脱壳APP加固搭建程序源码及教程

2024ARM网络验证 支持一键云注入引流弹窗注册机 一键脱壳APP加固搭建程序源码及教程

此套源码功能强大&#xff0c;支持APK脱壳、注入、网络验证、注册机、引流弹窗、更新弹窗和公告等功能&#xff0c;并具有强大的系统应用管理端&#xff0c;可轻松管理用户数量和卡密状态等数据统计。armpro脱壳软件可在线修改手机文件和游戏数据&#xff0c;并可添加会员功能、…
阅读更多...
汉诺塔（hanio）--C语言函数递归

汉诺塔（hanio）--C语言函数递归

文章目录 前言一、汉诺塔的图解二、问题分析总结 前言 什么是汉诺塔&#xff1f; 汉诺塔(Tower of Hanoi)&#xff08;也称河内塔&#xff09;是有法国数学家爱德华卢卡斯于1883年发明的一道智力题。它源于印度的一个古老传说&#xff1a;大梵天创造世界的时候做了三根钻石柱子…
阅读更多...
【MySQL】数据库精细化讲解：内置函数知识穿透与深度学习解析

【MySQL】数据库精细化讲解：内置函数知识穿透与深度学习解析

前言&#xff1a;本节内容讲述mysql里面的函数的概念&#xff0c; 在mysql当中&#xff0c; 内置了很多函数工作。 这些函数丰富了我们的操作。 比如字符串函数、数据函数以及一些其他函数等等。 ps:友友们学习了表的基本操作后就可以观看本节内容啦! 目录 日期函数 current_…
阅读更多...
Is:cannat access /data: Input/output error

Is:cannat access /data: Input/output error

说明&#xff1a; 1&#xff09;访问应用业务&#xff0c;输入账号密码报如下图所示&#xff1a;invalid login. 2&#xff09;登录服务器查看数据日志&#xff0c;报如下图所示&#xff1a;ls:cannot access /data: Input/output error 3&#xff09;查看日志dmesg |grep erro…
阅读更多...
Python MySQL SQLServer操作

Python MySQL SQLServer操作

Python MySQL SQLServer操作 Python 可以通过 pymysql 连接 MySQL&#xff0c;通过 pymssql 连接 SQL Server。以下是基础操作和代码实战示例&#xff1a; 一、操作 MySQL&#xff1a;使用 pymysql python 操作数据库流程 1. 安装库 pip install pymysql2. 连接 MySQL 示例 …
阅读更多...
迅为RK3562开发板直连电脑配置方法（无线上网）

迅为RK3562开发板直连电脑配置方法（无线上网）

概述 由于环境限制&#xff0c;笔记本电脑和开发板无法通过路由器连接起来&#xff0c;所以本文的目的是要实现笔记本电脑和虚拟机能够通过 WIFI 上网&#xff0c;并且开发板通过网线连接笔记本电脑和虚拟机在同一个网段内&#xff0c;最终实现 TFTP 或 NFS 来进行开发调试。 通…
阅读更多...
Mono Repository方案与ReactPress的PNPM实践

Mono Repository方案与ReactPress的PNPM实践

ReactPress Github项目地址&#xff1a;https://github.com/fecommunity/reactpress 欢迎Star。 Mono Repository方案与ReactPress的PNPM实践 在当今软件开发领域&#xff0c;Mono Repository&#xff08;简称Monorepo&#xff09;已成为一种流行的代码管理方式&#xff0c;特…
阅读更多...
最新文章

Copyright @ 2022~2023