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

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

二、联合查询

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

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

（二）笛卡尔乘积

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

（三）三种联合查询方式

左外连接（left join）：
- 语法与示例：以左表为基准，返回左表中的所有记录以及右表中满足连接条件的记录。若右表中无匹配记录，则对应字段值为 NULL。例如，查询所有学生及其班级信息（适合查看哪些学员分配了班级，哪些学员未分配班级）：

select
    s.id,
    s.stu_name,
    c.class_name 
from
    students s
    left join class_info c on s.fk_class_id = c.id;

Java 全栈关联：在 Java 全栈开发的学校管理系统中，后端使用 Java 代码连接数据库执行此查询。通过 MyBatis 或 Hibernate 等框架，将查询结果映射为 Java 对象，如StudentWithClass对象，包含学生 ID、姓名和班级名称属性。前端可以通过 RESTful API 获取这些数据，以表格或列表形式展示给用户，方便管理员查看学生的班级分配情况。

右外连接（right join）：
- 语法与示例：与左外连接相反，以右表为基准，返回右表中的所有记录以及左表中满足连接条件的记录。若左表中无匹配记录，则对应字段值为 NULL。例如，查询所有班级及其包含的学生信息（适合查看哪些班有学员，哪些班没有学员）：

select
    s.id,
    s.stu_name,
    c.class_name 
from
    students s
    right join class_info c on s.fk_class_id = c.id;

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

内连接（inner join）：
- 语法与示例：只返回两张表中满足连接条件的记录。有两种常见写法，例如：

-- 内联查（一）
select
    s.id,
    s.stu_name,
    c.class_name 
from
    students s
    inner join class_info c on s.fk_class_id = c.id;
-- 内联查（二）
select
    s.id,
    s.stu_name,
    c.class_name 
from
    students s, class_info c where s.fk_class_id = c.id;

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

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

（四）练习题巩固

部门与职员表操作：
- 建表与插入数据：

-- 创建部门表
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 人员查看和分析数据。

学生与班级表操作：
- 分组统计不同班级人数：

SELECT
    c.CLASS_NAME,
    count( * ) 
FROM
    students s
    LEFT JOIN class_info c ON s.FK_CLASS_ID = c.id 
GROUP BY
    c.CLASS_NAME;

查询班级人数大于 0 的班级：

SELECT
    c.CLASS_NAME,
    count( s.id ) total 
FROM
    students s
    RIGHT JOIN class_info c ON s.FK_CLASS_ID = c.id 
GROUP BY
    c.CLASS_NAME 
HAVING
    count( s.id ) > 0 
ORDER BY
    total DESC;

Java 全栈关联：在学校教务管理系统中，这些统计数据可用于班级规模分析、资源分配等决策。后端将查询结果通过 Java 代码处理后，提供给前端生成柱状图或饼状图，直观展示班级人数分布情况。

车辆相关表操作：
- 建表与插入数据：

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)
);
SELECT
    s.id,
    s.STU_NAME,
    c.course_name,
    c.score 
FROM
    students s
    LEFT JOIN stu_course_info sc ON sc.FK_STU_ID = s.id
    LEFT JOIN course_info c ON sc.fk_course_id = c.id;

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

SELECT
    s.id,
    s.STU_NAME,
    c.course_name,
    c.score 
FROM
    students s
    LEFT JOIN stu_course_info sc ON sc.FK_STU_ID = s.id
    LEFT JOIN course_info c ON sc.fk_course_id = c.id
where c.course_name = '音乐鉴赏';

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

三、子查询

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

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

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

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

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

select 后面的子查询：
- 特点与示例：此类子查询返回单行、单列数据。例如，查询学生 id、学生名称以及班级名称（通过子查询替代外键直接关联查询）：

SELECT
    id,
    stu_name,
    ( SELECT class_name FROM class_info WHERE id = fk_class_id ) class_name 
FROM
    students;

Java 全栈关联：在 Java 开发的小型信息管理系统中，当需要展示学生及其班级信息时，后端执行此查询。将查询结果映射为 Java 对象后传递给前端，前端可以在学生信息展示页面中，将班级名称与学生其他信息一同呈现，方便用户查看。

from 后面的子查询：
- 特点与示例：子查询的结果被视为一张表。例如，查询所有男学生及其班级名称：

SELECT
    s.id,
    s.stu_name,
    class_name 
FROM
    ( SELECT * FROM students WHERE gender = '男' ) s
    LEFT JOIN class_info c ON s.FK_CLASS_ID = c.id;

Java 全栈关联：在学校的学生统计模块中，后端利用此查询获取男学生及其班级信息。将结果处理后，前端可以生成男学生班级分布报表，以图表形式展示不同班级男学生的数量。

where 后面的子查询：如前面提到的查询语文成绩低于平均分的学员示例，通过子查询先计算出语文平均分，再在主查询中筛选出符合条件的学员。在 Java 全栈开发的成绩分析系统中，这种查询可用于生成成绩分析报告，帮助教师了解学生成绩分布情况，为教学改进提供依据。

四、企业工作小技巧

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