Oracle 数据库基础入门（四）：分组与联表查询的深度探索（下）

news/2025/3/5 0:06:12/

在 Oracle 数据库的操作中，联合查询与子查询是获取复杂数据的关键手段。当单表数据无法满足业务需求时，联合查询允许我们从多张表中提取关联信息，而子查询则能以嵌套的方式实现更灵活的数据筛选。对于 Java 全栈开发者而言，掌握这些技术不仅能提升数据库操作能力，还能为构建高效的后端应用提供有力支持。

目录

二、联合查询

（一）联合查询的必要性与场景

（二）笛卡尔乘积

（三）三种联合查询方式

（四）练习题巩固

（五）三表联合查询

三、子查询

（一）子查询的概念与本质

（二）子查询的位置与应用

四、企业工作小技巧

二、联合查询

（一）联合查询的必要性与场景

联合查询在实际应用中极为常见，当我们需要整合来自多个表的数据时，它就派上了用场。比如在一个家庭信息管理系统中，若要查询丈夫信息的同时显示其妻子的名称；在学校管理系统中，查询学生时需要展示其所在班级；在教育选课系统中，查询选课时要同时呈现学生名称、课程名称以及课程分数。这些场景都需要联合查询来实现。

（二）笛卡尔乘积

笛卡尔乘积是理解联合查询的重要基础概念，它源自数学领域。假设有两个集合 X 和 Y，笛卡尔乘积就是将 X 集合中的每个元素与 Y 集合中的每个元素进行组合，组合的总数为 X 集合元素个数乘以 Y 集合元素个数。例如，若 X = {1, 2}，Y = {a, b}，那么它们的笛卡尔乘积为 {(1, a), (1, b), (2, a), (2, b)}。在数据库中，当我们对两张表进行无条件的联合操作时，就会得到笛卡尔乘积结果。但通常笛卡尔乘积的结果集非常庞大，且包含大量无意义的数据，所以需要通过等值判断等方式对其进行筛选，以获取我们真正需要的数据。

（三）三种联合查询方式

  1. 左外连接（left join）：
    • 语法与示例：以左表为基准，返回左表中的所有记录以及右表中满足连接条件的记录。若右表中无匹配记录，则对应字段值为 NULL。例如，查询所有学生及其班级信息（适合查看哪些学员分配了班级，哪些学员未分配班级）：
selects.id,s.stu_name,c.class_name 
fromstudents sleft join class_info c on s.fk_class_id = c.id;
  • Java 全栈关联：在 Java 全栈开发的学校管理系统中，后端使用 Java 代码连接数据库执行此查询。通过 MyBatis 或 Hibernate 等框架，将查询结果映射为 Java 对象，如StudentWithClass对象，包含学生 ID、姓名和班级名称属性。前端可以通过 RESTful API 获取这些数据，以表格或列表形式展示给用户，方便管理员查看学生的班级分配情况。

  1. 右外连接（right join）：
    • 语法与示例：与左外连接相反，以右表为基准，返回右表中的所有记录以及左表中满足连接条件的记录。若左表中无匹配记录，则对应字段值为 NULL。例如，查询所有班级及其包含的学生信息（适合查看哪些班有学员，哪些班没有学员）：
selects.id,s.stu_name,c.class_name 
fromstudents sright join class_info c on s.fk_class_id = c.id;

  • Java 全栈关联：在 Java 开发的类似系统中，该查询结果可用于生成班级人员统计报表。后端将结果处理后传递给前端，前端利用图表库（如 Echarts）将数据可视化，以直观展示每个班级的学生分布情况。

  1. 内连接（inner join）：
    • 语法与示例：只返回两张表中满足连接条件的记录。有两种常见写法，例如：
-- 内联查（一）
selects.id,s.stu_name,c.class_name 
fromstudents sinner join class_info c on s.fk_class_id = c.id;
-- 内联查（二）
selects.id,s.stu_name,c.class_name 
fromstudents s, class_info c where s.fk_class_id = c.id;

  • Java 全栈关联：在电商系统中，若要查询已下单的用户及其订单信息，可使用内连接。后端通过 Java 代码执行查询，将结果用于订单处理流程，如计算订单总价、更新库存等操作。前端则可展示订单详情页面，让用户确认订单信息。

在企业开发中，左外联合查询和内联合查询应用最为广泛。因为左外连接能保留左表所有数据，适用于需要全面展示某一方数据及其关联信息的场景；内连接则专注于获取有实际关联的数据，常用于查询相互匹配的数据对。

（四）练习题巩固

  1. 部门与职员表操作：
    • 建表与插入数据：
-- 创建部门表
CREATE TABLE Departments (ID NUMBER PRIMARY KEY,DepartmentName NVARCHAR2(100) NOT NULL
);
-- 创建职员表
CREATE TABLE Employees (ID NUMBER PRIMARY KEY,EmployeeName VARCHAR2(100) NOT NULL,Position VARCHAR2(100),Salary NUMBER,DepartmentID NUMBER,CONSTRAINT fk_department FOREIGN KEY(DepartmentID) REFERENCES Departments(ID)
);
-- 插入部门数据
INSERT INTO Departments (ID,DepartmentName) VALUES (1,'研发部');
INSERT INTO Departments (ID,DepartmentName) VALUES (2,'市场部');
-- 创建职员表的序列
CREATE SEQUENCE seq_employees
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
-- 插入员工数据
INSERT INTO Employees (ID, EmployeeName, Position, Salary, DepartmentID) VALUES (seq_employees.NEXTVAL, '张三', '高级工程师', 8000, 1);
INSERT INTO Employees (ID, EmployeeName, Position, Salary, DepartmentID) VALUES (seq_employees.NEXTVAL, '李四', '产品经理', 7500, 1);
INSERT INTO Employees (ID, EmployeeName, Position, Salary, DepartmentID) VALUES (seq_employees.NEXTVAL, '王五', '销售经理', 6000, 2);
INSERT INTO Employees (ID, EmployeeName, Position, Salary, DepartmentID) VALUES (seq_employees.NEXTVAL, '赵六', '市场营销', 5500, 2);
INSERT INTO Employees (ID, EmployeeName, Position, Salary, DepartmentID) VALUES (seq_employees.NEXTVAL, '孙七', '实习生', 3000, 2);

  • 查询需求：
    • 查询所有职员信息，并显示其部门名称：
select e.ID, e.EmployeeName, d.DepartmentName
from Employees e
left join Departments d on e.DepartmentID = d.ID;

  • 查询工资高于 6K 以上的职员，显示其部门名称：
select e.ID, e.EmployeeName, d.DepartmentName
from Employees e
left join Departments d on e.DepartmentID = d.ID
where e.Salary > 6000;

  • 查询研发部下，都有哪些职员：
select e.ID, e.EmployeeName
from Employees e
left join Departments d on e.DepartmentID = d.ID
where d.DepartmentName = '研发部';

  • Java 全栈关联：在企业人力资源管理系统中，这些查询结果可用于生成员工报表、薪资统计分析等功能。后端 Java 代码调用数据库执行查询，将结果封装成 Java 对象，如EmployeeWithDepartment对象，传递给前端展示。前端可以提供筛选、排序等交互功能，方便 HR 人员查看和分析数据。

  1. 学生与班级表操作：
    • 分组统计不同班级人数：
SELECTc.CLASS_NAME,count( * ) 
FROMstudents sLEFT JOIN class_info c ON s.FK_CLASS_ID = c.id 
GROUP BYc.CLASS_NAME;

  • 查询班级人数大于 0 的班级：
SELECTc.CLASS_NAME,count( s.id ) total 
FROMstudents sRIGHT JOIN class_info c ON s.FK_CLASS_ID = c.id 
GROUP BYc.CLASS_NAME 
HAVINGcount( s.id ) > 0 
ORDER BYtotal DESC;
  • Java 全栈关联：在学校教务管理系统中，这些统计数据可用于班级规模分析、资源分配等决策。后端将查询结果通过 Java 代码处理后，提供给前端生成柱状图或饼状图，直观展示班级人数分布情况。

  1. 车辆相关表操作：
    • 建表与插入数据：
CREATE TABLE vehicle_types (id NUMBER PRIMARY KEY,       -- 车辆类型ID，主键type_name VARCHAR2(100) NOT NULL  -- 车辆类型名称，不允许为空
);
CREATE TABLE vehicles (id NUMBER PRIMARY KEY,    -- 车辆ID，主键license_no VARCHAR2(20) NOT NULL, -- 车牌号，不允许为空model VARCHAR2(50) NOT NULL,     -- 车型，不允许为空fk_type_id NUMBER NOT NULL,          -- 车辆类型ID，外键owner_name VARCHAR2(100),         -- 车主姓名CONSTRAINT fk_vehicle_type FOREIGN KEY (fk_type_id) REFERENCES vehicle_types(id) -- 外键约束
);
INSERT INTO vehicle_types (id, type_name)
VALUES (1, '轿车');
INSERT INTO vehicle_types (id, type_name)
VALUES (2, 'SUV');
INSERT INTO vehicle_types (id, type_name)
VALUES (3, '卡车');
-- 插入车辆数据
INSERT INTO vehicles (id, license_no, model, fk_type_id, owner_name)
VALUES (1, '沪A12345', '卡罗拉', 1, '张三');
INSERT INTO vehicles (id, license_no, model, fk_type_id, owner_name)
VALUES (2, '京B67890', 'X5', 2, '李四');
INSERT INTO vehicles (id, license_no, model, fk_type_id, owner_name)
VALUES (3, '粤C24680', 'F-150', 3, '王五');

  • 查询需求：
    • 查询所有的车辆，并显示其车辆类型：
select v.id, v.license_no, vt.type_name
from vehicles v
left join vehicle_types vt on v.fk_type_id = vt.id;

  • 查询车辆类型是 “轿车” 的车辆：
select v.id, v.license_no, vt.type_name
from vehicles v
left join vehicle_types vt on v.fk_type_id = vt.id
where vt.type_name = '轿车';

  • 按照车辆类型分组统计下，不同的车辆类型各自有多少辆车：
select vt.type_name, count(*)
from vehicles v
left join vehicle_types vt on v.fk_type_id = vt.id
group by vt.type_name;
  • Java 全栈关联：在汽车租赁管理系统中，这些查询结果可用于车辆库存管理、车型统计分析等功能。后端 Java 代码处理查询结果，前端展示车辆列表、车型占比等信息，方便管理员进行车辆调度和采购决策。

（五）三表联合查询

在企业开发中，一般不建议联合查询超过三张表。因为随着表的增加，笛卡尔乘积会导致数据量呈指数级增长，严重影响查询性能。例如，在一个学校选课系统中，若要查询每个学生的选课情况，涉及学生表、选课关系表和课程表：

create table course_info(id number(11) primary key,course_name nvarchar2(20),score number(1)
);
create table stu_course_info(id number(11) primary key,fk_stu_id number(11),fk_course_id number(11)
);
SELECTs.id,s.STU_NAME,c.course_name,c.score 
FROMstudents sLEFT JOIN stu_course_info sc ON sc.FK_STU_ID = s.idLEFT JOIN course_info c ON sc.fk_course_id = c.id;

若要查询选择 “音乐鉴赏” 的学生：

SELECTs.id,s.STU_NAME,c.course_name,c.score 
FROMstudents sLEFT JOIN stu_course_info sc ON sc.FK_STU_ID = s.idLEFT JOIN course_info c ON sc.fk_course_id = c.id
where c.course_name = '音乐鉴赏';

在 Java 全栈开发中，处理三表联合查询时，后端开发人员需要谨慎优化查询语句，如合理使用索引、避免不必要的字段选择等。同时，前端在展示大量数据时，也需要采用分页、懒加载等技术，以提升用户体验。

三、子查询

（一）子查询的概念与本质

子查询是一种特殊的联合查询方式，其本质是在一个 SQL 查询语句中嵌套另一个 SQL 查询语句，就像俄罗斯套娃一样。例如，查询语文考试成绩比语文平均分还低的学员：

SELECTid,name,score 
FROMstudent_exam_info 
WHEREsubject = '语文' AND score <= ( SELECT avg( score ) FROM student_exam_info WHERE subject = '语文' );

子查询可以放置在select后面、from后面以及where后面，不同位置的子查询具有不同的特点和用途。

（二）子查询的位置与应用

  1. select 后面的子查询：
    • 特点与示例：此类子查询返回单行、单列数据。例如，查询学生 id、学生名称以及班级名称（通过子查询替代外键直接关联查询）：
SELECTid,stu_name,( SELECT class_name FROM class_info WHERE id = fk_class_id ) class_name 
FROMstudents;

  • Java 全栈关联：在 Java 开发的小型信息管理系统中，当需要展示学生及其班级信息时，后端执行此查询。将查询结果映射为 Java 对象后传递给前端，前端可以在学生信息展示页面中，将班级名称与学生其他信息一同呈现，方便用户查看。
  1. from 后面的子查询：
    • 特点与示例：子查询的结果被视为一张表。例如，查询所有男学生及其班级名称：
SELECTs.id,s.stu_name,class_name 
FROM( SELECT * FROM students WHERE gender = '男' ) sLEFT JOIN class_info c ON s.FK_CLASS_ID = c.id;
  • Java 全栈关联：在学校的学生统计模块中，后端利用此查询获取男学生及其班级信息。将结果处理后，前端可以生成男学生班级分布报表，以图表形式展示不同班级男学生的数量。
  1. where 后面的子查询：如前面提到的查询语文成绩低于平均分的学员示例，通过子查询先计算出语文平均分，再在主查询中筛选出符合条件的学员。在 Java 全栈开发的成绩分析系统中，这种查询可用于生成成绩分析报告，帮助教师了解学生成绩分布情况，为教学改进提供依据。

四、企业工作小技巧

  1. 优化联合查询性能：
    • 合理使用索引：在联合查询涉及的表中，为连接字段创建索引可以显著提升查询速度。例如，在学生表和班级表通过fk_class_id进行连接时，为fk_class_id字段创建索引，能加快数据匹配速度。但要注意，索引并非越多越好，过多索引会增加数据插入和更新的时间成本。
    • 减少笛卡尔乘积影响：在进行联合查询时，确保连接条件准确且充分，避免产生不必要的笛卡尔乘积。例如，明确指定学生表和班级表之间的连接条件为students.fk_class_id = class_info.id，防止出现大量无意义的组合数据。
  2. 子查询的优化与使用：
    • 避免多层嵌套：尽量减少子查询的嵌套层数，因为过多的嵌套会使查询语句复杂难懂，且性能下降。如果可能，将多层子查询转换为联合查询或使用临时表来优化。
    • 利用子查询的原子性：子查询可以将复杂的查询逻辑拆分成多个原子部分，方便理解和维护。例如，在复杂的数据分析查询中，先通过子查询计算出一些中间结果，再在主查询中使用这些结果进行最终筛选。
  3. Java 全栈开发中的数据处理：
    • 前后端数据传输优化：在 Java 全栈开发中，联合查询和子查询返回的数据量可能较大。前端在接收数据时，要采用合适的数据传输格式（如 JSON），并进行必要的压缩。后端可以对查询结果进行分页处理，减少一次性传输的数据量，提升系统响应速度。
    • 业务逻辑与查询结合：不要单纯依赖数据库查询来完成所有业务逻辑。在 Java 代码中进行一些数据处理和逻辑判断，例如对查询结果进行二次筛选、计算等操作，这样可以减轻数据库负担，同时增强系统的灵活性和可维护性。

通过深入学习联合查询和子查询，我们在 Oracle 数据库操作能力上又迈出了重要一步。在未来的 Java 全栈开发工作中，灵活运用这些技术将帮助我们高效地处理复杂的数据需求，为企业构建强大的数据驱动应用。

查看分组基础查询&复合分组&having过滤请点击查看剩余部分

               Oracle 数据库基础入门（四）：分组与联表查询的深度探索（上）-CSDN博客                


http://www.ppmy.cn/news/1576677.html

相关文章

服务异步通讯与RabbitMQ

服务异步通讯与RabbitMQ

服务异步通讯 文章目录 服务异步通讯MQRabbitMQ1、安装&#xff08;部署&#xff09;2、结构3、消息模型4、SpringAMQP4.1、基本消息队列4.2、工作消息队列4.3、发布订阅模型4.3.1、FanoutExchange&#xff08;广播类型的交换机&#xff09;4.3.2、DirectExchange&#xff08;路…
阅读更多...
2月28日，三极管测量，水利-51单片机

2月28日，三极管测量，水利-51单片机

众所周知&#xff0c;三极管&#xff08;BJT&#xff09;有三个管脚&#xff0c;基极&#xff08;B&#xff09;、集电极&#xff08;C&#xff09;、发射极&#xff08;E&#xff09;&#xff0c;在实际应用中&#xff0c;不可避免地会遇到引脚辨别的问题。接下来就讲下三极管…
阅读更多...
哪些方法可以查看drupal版本

哪些方法可以查看drupal版本

在 Drupal 中&#xff0c;你可以使用多种方式来查看当前的 Drupal 版本&#xff0c;以下是几种方法&#xff1a; 方法 1&#xff1a;通过管理后台查看&#xff08;适用于管理员&#xff09; 登录 Drupal 后台。进入 管理 → 报告 → 状态报告&#xff08;路径&#xff1a;/adm…
阅读更多...
使用通义万相Wan2.1进行视频生成

使用通义万相Wan2.1进行视频生成

使用通义万相Wan2.1进行视频生成 源代码准备运行环境准备创建Python虚拟环境并激活安装依赖包 模型下载生成视频官网的视频生成例子简单描述场景视频生成示例详细描述场景视频生成示例 最近通义万相开源了其视频生成模型。模型有两个版本&#xff0c;一个是1.3B的&#xff0c;一…
阅读更多...
一种事件驱动的设计模式-Reactor 模型

一种事件驱动的设计模式-Reactor 模型

Reactor 模型 是一种事件驱动的设计模式&#xff0c;主要用于处理高并发的 I/O 操作&#xff08;如网络请求、文件读写等&#xff09;。其核心思想是通过事件分发机制&#xff0c;将 I/O 事件的监听和处理解耦&#xff0c;从而高效管理大量并发连接&#xff0c;避免传统多线程模…
阅读更多...
为何在用户注销时使用 location.href 而非 Vue Router 的 router.push

为何在用户注销时使用 location.href 而非 Vue Router 的 router.push

在开发 Web 应用时&#xff0c;用户注销功能的设计看似简单&#xff0c;但背后隐藏着对状态管理、安全性和用户体验的深层考量。以下将详细探讨为何许多项目在注销跳转时选择 location.href&#xff08;强制刷新页面&#xff09;而非 Vue Router 的 router.push&#xff08;单页…
阅读更多...
javaFX自定义标题栏（含自定义表单tableView）

javaFX自定义标题栏（含自定义表单tableView）

基于&#xff1a;openjfx 17.0.2 openJDK 17.0.1 idea win10 scene builder 2.0 使用fxml制作ui 必须定义AnchorPane&#xff0c;他的作用就是让嵌入其中的元件固定在此&#xff08;相对上级元素的&#xff09;位置。AnchorPane此时在BorderPane的Top位置&#xff…
阅读更多...
JAVA毕设项目-基于SSM框架的ssm数据结构学习网(vue)源码+设计文档

JAVA毕设项目-基于SSM框架的ssm数据结构学习网(vue)源码+设计文档

文末获取源码数据库文档 感兴趣的可以先收藏&#xff0c;有毕设问题&#xff0c;项目以及论文撰写等问题都可以和博主沟通&#xff0c;尽最大努力帮助更多的人&#xff01; 目 录 目 录 摘 要 abstract 第1章 绪论 1.1开发背景 1.2开发意义 1.3研究内容 1.4主…
阅读更多...
最新文章

Copyright @ 2022~2023