JAVA-Web后端学习7 多表关系

花の香りが怖いの，しびれるように甘くて，こころ溶ける…说明できない

多表关系

如果我们使用的数据需要多个数据表，而且这些数据表之间还存在联系，这使得我们不得不去处理表关系。

例如员工数据表和部门数据表，其中每个员工都有自己所在的部门，这就使得员工数据表和部门数据表之间存在关联。我们可以在数据量较多的员工数据表中添加关键字段部门ID以此来关联部门数据表。

多表关系分析

一对多

员工数据表和部门数据表是典型的一对多关系，一个部门有多名员工。

如果员工数据表和部门数据表只有实质内容上的关联，但是没有任何形式上的约束，那么可能存在这样的问题：部门数据表中删除了一行部门数据，但是员工数据表中依旧有一批员工的部门ID保持不变，那么此时就出现了数据不一致的问题，属于异常情况。

原因在于：这两张表并为建立数据库层面的关联，所以无法保证数据的一致性和完整性。

外键约束

MySQL的外键约束（Foreign Key Constraint）是一种用于维护表与表之间数据一致性和完整性的机制。它通过在一张表（子表或从表）中定义一个字段（外键字段），使其引用另一张表（主表或父表）的主键或唯一键，从而建立两个表之间的关联关系。

外键约束要求:

①被引用的字段必须是主键或具有唯一约束的字段
②外键字段的数量和顺序必须与主表的主键字段一致
③外键字段的数据类型必须与主表中被引用字段的数据类型完全一致

添加外键约束操作如下：

-- 创建表时添加外键
CREATE TABLE emp (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES dept(id)
);
-- 创建完表之后添加外键
ALTER TABLE emp ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES dept(id);

通过外键约束，来保证多表下的数据一致性。

FOREIGN KEY的外键约束又称之为物理外键。目前很多实际开发中都明令禁止了物理外键，因为存在如下缺点：

①影响增删改效率（需要检查外键关系）
②仅用于单节点数据库，不适合分布式以及集群场景
③容易引发数据库的死锁问题，消耗性能

而现在常见的是逻辑外键，简单来说就是只通过表中的列名在语义上表示表之间的关系从而进行约束，数据库不会进行检查因此不存在物理约束，数据一致性完全通过自行检查应用程序代码来进行校验。

一对一

一对一对用于单表拆分，即把数据表中常用数据的列名（基础字段）和不常用数据的列名分开存储

例如一个人的信息可以分为身份信息和基本信息，将二者分开在两张数据表中保存，以提升操作效率

多对多

例如一个学生会选择多门课，一门课也会被多名学生选择（莫名其妙觉得好狗血的关系）

这种情况下一般会选择使用中间的第三张表，表中至少两个外键，分别关联两张表的两个主键

多表查询

多表查询：指多张表中查询数据

笛卡尔积：在数学中，两个集合的所有组合情况。 多表查询的时候，需要消除无效的笛卡尔积

连接查询

内连接

内连接下只保存两表交集数据

内连接分为两种：隐式内连接和显式内连接

隐式内连接不使用JOIN关键字，而是利用逗号分隔表名并结合WHERE子句进行连接筛选；这种写法在早期SQL中较为常见，但随着SQL标准的发展，其可读性较差，尤其是在涉及多个表连接时，容易导致逻辑混乱。

显式内连接使用INNER JOIN关键字（INNER JOIN可以省略INNER关键字，写成JOIN）明确地指定连接操作，语法更清晰、结构更规范。它将连接条件与过滤条件分离，使得查询语句更具可读性和维护性。

-- 1.隐式内连接
select 列名 from 表1,表2 where 连接条件...

-- 2.显示内连接
select 列名 from 表1 [inner] join 表2 on 连接条件

外连接

外连接下保存一张表的所有数据以及两表交集数据

外连接分为左外连接和右外连接

-- 1.左外连接
select 列名 from 表1 left [outer] join 表2 on 连接条件
-- 2.右外连接
select 列名 from 表1 right [outer] join 表2 on 连接条件

子查询

子查询是SQL中嵌套select查询语句，又称之为嵌套查询

格式如下:

select c1 from t1 where c1=(select c1 from t2 ...);

根据子查询返回结果分类如下：

标量子查询：子查询返回的是单个值
列子查询：子查询返回的是一列
行子查询：子查询返回的是一行
表子查询：子查询返回的是多列多行

具体实力如下：

-- 1.标量子查询 

-- 查询返回入职时间最早的员工信息
select * from emp where entry_date = (select min(entry_date) from emp);
-- 查询入职时间比阮小五玩的员工信息
select * from emp where entry_date > (select entry_date from emp where name='阮小五');

-- 2.列子查询 

-- 查询所有在教育部和咨询部的员工信息
select * from emp where dept_id in (select id from dept where dept.name in ('教研部','咨询部'));

-- 3.行子查询

-- 查询与李忠薪资职位都相同的员工信息
select * from emp where (salary,job) = (select salary,job from emp where name = '李忠');

-- 4.表子查询

-- 查询每个部门中薪资最高的员工的信息
select * from emp TmpEmp,(select dept_id,max(salary) as maxSalary from emp group by dept_id) TmpResult
where TmpEmp.salary = TmpResult.maxSalary and TmpEmp.dept_id = TmpResult.dept_id;

---

事后联系一下，现在有两张表（一张仁之表一张义之表）员工信息数据表和部门数据表

-- 查询教研部性别为男而且在2011-05-01之后入职的员工
-- 交集关系，直接使用内连接
select * from emp,dept where emp.gender=1 and emp.entry_date>'2011-05-01' and emp.dept_id=dept.id and dept.name='教研部';

-- 查询工资低于公司平均工资且性别为男的员工信息
select name,salary from emp where salary<(select avg(salary) as avgSalary from emp) and gender=1;

-- 查询部门人数超过10人的部门

select * from dept,
              (select dept_id,count(id) as deptCount from emp group by dept_id having deptCount>10) as NeedDept
         where id=NeedDept.dept_id;


-- 查询在2010-05-01之后入职并且薪资高于10000的教研部员工信息，结果按照薪资降序排序
select * from emp,dept where
        emp.entry_date>'2010-05-01'
        and emp.salary > 10000
        and emp.dept_id = dept.id
        and dept.name = '教研部'
order by salary desc;

-- 查询工资低于本部门平均工资员工的信息
select * from emp,(
    select avg(salary) as avgSalary,dept_id from emp group by dept_id
) as TmpResult where emp.dept_id = TmpResult.dept_id and emp.salary < TmpResult.avgSalary;