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

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

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

uniapp-vue2引用了vue-inset-loader插件编译小程序报错

uniapp-vue2引用了vue-inset-loader插件编译小程序报错

报错信息 Error: Vue packages version mismatch: - vue3.2.45 (D:\qjy-myApp\admin-app\node_modules\vue\index.js) - vue-template-compiler2.7.16 (D:\qjy-myApp\admin-app\node_modules\vue-template-compiler\package.json) This may cause things to work incorrectly.…
阅读更多...
VOLO实战：使用VOLO实现图像分类任务（二）

VOLO实战：使用VOLO实现图像分类任务（二）

文章目录 训练部分导入项目使用的库设置随机因子设置全局参数图像预处理与增强读取数据设置Loss设置模型设置优化器和学习率调整策略设置混合精度，DP多卡，EMA定义训练和验证函数训练函数验证函数调用训练和验证方法 运行以及结果查看测试完整的代码 在上…
阅读更多...
【Linux】TCP网络编程

【Linux】TCP网络编程

目录 V1_Echo_Server V2_Echo_Server多进程版本 V3_Echo_Server多线程版本 V3-1_多线程远程命令执行 V4_Echo_Server线程池版本 V1_Echo_Server TcpServer的上层调用如下，和UdpServer几乎一样： 而在InitServer中，大部分也和UDP那里一样&…
阅读更多...
XG(S)-PON原理

XG(S)-PON原理

前言 近年来，随着全球范围内接入市场的飞快发展以及全业务运营的快速开展，已有的PON技术标准在带宽需求、业务支撑能力以及接入节点设备和配套设备的性能提升等方面都面临新的升级需求XG(S)-PON(10G GPON)是在已有GPON技术标准上演进的增强下一代GPON技…
阅读更多...
C语言学习 12（指针学习1）

C语言学习 12（指针学习1）

一.内存和地址 1.内存 在讲内存和地址之前，我们想有个⽣活中的案例： 假设有⼀栋宿舍楼，把你放在楼⾥，楼上有100个房间，但是房间没有编号，你的⼀个朋友来找你玩，如果想找到你，就得挨…
阅读更多...
前端---CSS（部分用法）

前端---CSS（部分用法）

HTML画页面--》这个页面就是页面上需要的元素罗列起来，但是页面效果很差，不好看，为了让页面好看，为了修饰页面---》CSS CSS的作用：修饰HTML页面 用了CSS之后，样式和元素本身做到了分离的效果。---》降低了代…
阅读更多...
H.265流媒体播放器EasyPlayer.js无插件H5播放器关于移动端（H5）切换网络的时候，播放器会触发什么事件

H.265流媒体播放器EasyPlayer.js无插件H5播放器关于移动端（H5）切换网络的时候，播放器会触发什么事件

EasyPlayer.js无插件H5播放器作为一款功能全面的H5流媒体播放器，凭借其多种协议支持、多种解码方式、丰富的渲染元素和强大的应用功能，以及出色的跨平台兼容性，为用户提供了高度定制化的选项和优化的播放体验。无论是视频直播还是点播&#x…
阅读更多...
零基础学安全--云技术基础

零基础学安全--云技术基础

目录 学习连接 前言 云技术历史 云服务 公有云服务商 云分类 基础设施即服务（IaaS） 平台即服务（PaaS） 软件即服务（SaaS） 云架构 虚拟化 容器 云架构设计 组件选择 基础设施即代码 集成部署…
阅读更多...
【AI绘画】Midjourney进阶：色调详解（上）

【AI绘画】Midjourney进阶：色调详解（上）

博客主页： [小ᶻ☡꙳ᵃⁱᵍᶜ꙳] 本文专栏: AI绘画 | Midjourney 文章目录 💯前言💯Midjourney中的色彩控制为什么要控制色彩？为什么要在Midjourney中控制色彩？ 💯色调白色调淡色调明色调 &#x1f4af…
阅读更多...
前端适配：常用的几种方案

前端适配：常用的几种方案

一、rem和第三方插件 rem与em不同，rem会根据html的根节点字体大小进行变换，例如1rem就是一个字体大小那么大，比如根大小font size为12px，那么1rem即12px，大家可以在网上寻找单位换算工具进行换算（从设计稿…
阅读更多...
蓝桥杯c++算法秒杀【6】之动态规划【下】（数字三角形、砝码称重(背包问题)、括号序列、异或三角：：：非常典型的必刷例题！！！）

蓝桥杯c++算法秒杀【6】之动态规划【下】（数字三角形、砝码称重(背包问题)、括号序列、异或三角：：：非常典型的必刷例题！！！）

别忘了请点个赞收藏关注支持一下博主喵！！！! ! ! ! ！ 关注博主，更多蓝桥杯nice题目静待更新:) 动态规划 三、括号序列 【问题描述】 给定一个括号序列，要求尽可能少地添加若干括号使得括号序列变得合…
阅读更多...
AIGC--AIGC与人机协作:新的创作模式

AIGC--AIGC与人机协作:新的创作模式

AIGC与人机协作：新的创作模式 引言 人工智能生成内容（AIGC）正在以惊人的速度渗透到创作的各个领域。从生成文本、音乐、到图像和视频，AIGC使得创作过程变得更加快捷和高效。然而，AIGC并非完全取代了人类的创作角色&am…
阅读更多...
Hot100 - 字母异位词分组

Hot100 - 字母异位词分组

Hot100 - 字母异位词分组 最佳思路&#xff1a;排序 时间复杂度&#xff1a; O(nmlogm)&#xff0c;其中 n 为 strs 数组的长度&#xff0c;m 为每个字符串的长度。 代码&#xff1a; class Solution {public List<List<String>> groupAnagrams(String[] strs) …
阅读更多...
C++11特性(详解)

C++11特性(详解)

目录 1.C11简介 2.列表初始化 3.声明 1.auto 2.decltype 3.nullptr 4.范围for循环 5.智能指针 6.STL的一些变化 7.右值引用和移动语义 1.左值引用和右值引用 2.左值引用和右值引用的比较 3.右值引用的使用场景和意义 4.右值引用引用左值及其一些更深入的使用场景分…
阅读更多...
【H2O2|全栈】JS进阶知识（十一）axios入门

【H2O2|全栈】JS进阶知识（十一）axios入门

目录 前言 开篇语 准备工作 获取 介绍 使用 结束语 前言 开篇语 本系列博客主要分享JavaScript的进阶语法知识&#xff0c;本期主要对axios进行基本的了解。 与基础部分的语法相比&#xff0c;ES6的语法进行了一些更加严谨的约束和优化&#xff0c;因此&#xff0c;在…
阅读更多...
【前端】ES6基础

【前端】ES6基础

1.开发工具 vscode地址 :https://code.visualstudio.com/download, 下载对应系统的版本windows一般都是64位的 安装可以自选目录&#xff0c;也可以使用默认目录 插件&#xff1a; 输入 Chinese&#xff0c;中文插件 安装&#xff1a; open in browser&#xff0c;直接右键文件…
阅读更多...
代码美学：MATLAB制作渐变色

代码美学：MATLAB制作渐变色

输入颜色个数n&#xff0c;颜色类型&#xff1a; n 2; % 输入颜色个数 colors {[1, 0, 0], [0, 0, 1]}; createGradientHeatmap(n, colors); 调用函数&#xff1a; function createGradientHeatmap(n, colors)% 输入检查if length(colors) ~ nerror(输入的颜色数量与n不一…
阅读更多...
【Reinforcement Learning】强化学习下的多级反馈队列（MFQ）算法

【Reinforcement Learning】强化学习下的多级反馈队列（MFQ）算法

&#x1f4e2;本篇文章是博主强化学习&#xff08;RL&#xff09;领域学习时&#xff0c;用于个人学习、研究或者欣赏使用&#xff0c;并基于博主对相关等领域的一些理解而记录的学习摘录和笔记&#xff0c;若有不当和侵权之处&#xff0c;指出后将会立即改正&#xff0c;还望谅…
阅读更多...
103.【C语言】数据结构之TopK问题详细分析

103.【C语言】数据结构之TopK问题详细分析

目录 1.定义 2.实现 一个容易想到的方法 稍微改进的方法 最优的方法 分析方法的可行性 取出无序数组的取出前K个元素有几种可能 1.取的全是非TopK个元素中的 2.取的前K个既有非TopK个元素也有TopK个元素 3.取的前K个q恰为TopK个元素 代码实现 步骤 TestTopK代码 …
阅读更多...
国土变更调查拓扑错误自动化修复工具的研究

国土变更调查拓扑错误自动化修复工具的研究

提示&#xff1a;文章写完后&#xff0c;目录可以自动生成&#xff0c;如何生成可参考右边的帮助文档 目录 一、拓扑错误的形成原因 1.边界不一致 2.不规则图形 3.尖锐角 4.局部狭长 5.细小碎面 6.更新层相互重叠 二、修复成果展示 1.边界不一致 2.不规则图形 3.尖锐角 4.局部狭…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023