多表联合查询数据准备交叉连接查询内连接查询外连接查询左外连接查询右外连接查询满外连接查询

子查询子查询关键字ALL

在日常的数据库使用过程中我们处理的数据不只是单张表而是需要我们同时处理多个表的数据那么今天我将为大家分享关于MySQL的多表操作。

多表关系

在学习多表操作之前我们需要知道有哪些多表关系一对一关系、一对多关系/多对一关系、多对多关系。

一对一关系

一对一关系是指一个表的一条记录只能对应另外一个表的一条记录它们之间通过共同的主键或唯一键进行关联。

一对多/多对一关系

多表一对多或多对一关系是指一个表的一条记录可以对应另一个表的多条记录它们之间通过共同的主键或外键进行关联。

多对多关系

多表多对多关系是指两个或多个表之间相互关联并且每个表的记录可以对应其他表的多个记录。

处理多表多对多关系的常见方法是使用中间表关联表来连接关系。

外键约束

MySQL外键约束是一种数据库约束用于确保表之间的关系的完整性。

它定义了一个字段或一组字段在一个表中的值必须与另一个表中的值保持一致或满足特定条件。

外键约束通过指定一个外键关系将一个表的字段称为外键与另一个表的主键或唯一键称为参照键进行关联。

这样如果某个表中的外键值发生改变MySQL会自动检查参照表中是否存在匹配的键值以保持数据的一致性。

强制引用完整性外键约束确保表中的数据在引用时存在于关联表中。

维护数据一致性外键约束防止在关联表中删除或修改数据时引发不一致的结果。

提供查询优化MySQL优化器可以使用外键关系来加速查询操作。

级联操作外键约束可以定义级联操作当参照表中的数据更新或删除时自动对关联表中的数据进行相应操作。

强制引用完整性外键约束确保引用表中的数据在被引用表中存在防止出现无效的引用。

这可以保证数据的一致性和完整性。

自动验证和维护关系MySQL会自动验证和维护外键约束确保关联的数据被正确更新、插入或删除。

如果违反了外键约束MySQL会报错并拒绝执行操作。

支持级联操作外键约束可以定义级联操作在参照表中进行删除或更新操作时自动对关联表中相关的数据进行相应操作。

例如可以实现级联删除删除参照表中的数据时同时删除关联表中的相关数据。

优化查询性能MySQL优化器可以使用外键约束来优化查询操作。

通过正确设置外键关系MySQL可以更有效地执行JOIN操作提高查询性能。

可以禁用和启用需要时可以禁用或启用外键约束以允许执行某些需要暂时违反外键约束的操作。

这个特性在某些情况下很有用但需要谨慎使用以防止数据不一致。

对数据完整性的依赖使用外键约束可以在数据库级别维护数据完整性而不是依赖于应用程序。

这样可以减少错误和数据不一致的可能性使数据库更加可靠。

创建外键约束

创建外键约束有两种方式1.在创建表的时候创建外键约束create

table

我们在创建表之后添加外键约束的前提是从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

create

当我们插入数据的时候如果我们先向主表中插入数据的时候不需要注意什么但是当我们向从表中插入数据的时候我们需要注意插入的记录的外键列的数据在主表的主键列中是否存在如果存在则插入成功否则就插入失败。

所以建议插入数据的时候先向主表中插入数据然后再向从表中插入数据。

insert

values(4,2302,王五,男);如果我们在新建外键约束的时候表中已经存在了数据该怎么办

create

values(1,2301,张三,男),(3,2302,王五,男);

table

class(class_id);上面存在的数据外键列与主键列中的数据是相对应的所以插入成功。

create

values(1,2301,张三,男),(4,2302,王五,男);

这里class_id

删除从表中的数据可以随便删除当我们删除主表中的数据时我们需要注意我们删除的记录的主键列还被外键列所依赖时不能删除。

此时主键列

1;删除失败所以我们删除主表的数据时需要考虑该记录的主键列是否还被外键列依赖着。

在删除数据的时候建议先删除从表中的数据然后再删除主表中未被依赖的数据

删除外键约束

MySQL多表查询是指在一个查询语句中涉及到多个表通过指定表之间的连接条件关联条件来获取联合数据的过程。

多表查询可以用来检索、过滤和组合来自多个表的数据。

MySQL多表操作主要包括

Join是一种用于从两个或多个表中返回所有可能的组合的查询方式。

它会返回左边表的每一行与右边表的每一行的所有组合并且不使用任何连接条件。

因此交叉连接查询会产生一个结果集其行数等于左边表的行数乘以右边表的行数

select

dept3,emp3;但是我们看到笛卡尔积之后的表的数据会发现有很多不适合的记录所以我们还需要对笛卡尔积生成的表做出筛选。

内连接查询

Join是一种用于从两个或多个表中返回满足连接条件的记录集的查询方式。

内连接基于指定的连接条件将两个表中的行进行匹配并返回匹配的行。

内连接是最常用的连接类型它可以帮助我们在多个表之间建立关联关系并获取相关的数据。

内连接的结果集只包含匹配的行因此可以根据连接条件进行数据的关联和过滤提供更具体和有意义的结果。

隐式内连接select

Join是一种用于从两个或多个表中返回满足连接条件以及一些或全部不满足连接条件的记录的查询方式。

外连接可以帮助我们获取主表和连接表之间的关系并包含未匹配的行。

外连接查询分为

Join是一种通过从左边表主表返回所有行以及满足连接条件的右边表连接表的匹配行来获取表之间关联数据的查询方式。

左外连接会保留左边表中的所有行即使在右边表中没有匹配的行。

左外连接会将左边表的每一行与右边表的匹配行进行连接并返回匹配的结果集。

如果右边表中没有匹配的行则返回NULL值。

select

emp3.dept_id;这里因为人事部没有员工但因为是左外连接要显示左表中的所有数据所以人事部中的数据就用

NULL

Join是一种通过从右边表连接表返回所有行以及满足连接条件的左边表主表的匹配行来获取表之间关联数据的查询方式。

右外连接会保留右边表中的所有行即使在左边表中没有匹配的行。

右外连接会将右边表的每一行与左边表的匹配行进行连接并返回匹配的结果集。

如果左边表中没有匹配的行则返回NULL值。

select

满外连接是一种同时返回左边表和右边表中的所有行以及满足连接条件的匹配行的查询方式。

它能够获取左右两个表的全部数据并关联它们。

满外连接实际上就是将右外连接的数据拼接到左外连接数据的下面或者是将左外连接的数据拼接到右外连接数据的下面。

full

MySQL子查询Subquery是指在一个SQL查询中嵌套另一个完整的SQL查询。

子查询可以像普通的查询一样包含SELECT、FROM、WHERE等子句并且可以返回一个结果集这个结果集可以在外部查询中直接使用。

子查询通常用于将复杂的查询分解为更小的、可管理的查询块并通过嵌套来达到更灵活和精确的查询目的。

它可以嵌套在SELECT、FROM、WHERE等子句中用于过滤、排序、计算和联合数据。

单列单行返回的是一个具体列的内容可以理解为一个单值数据。

单行多列返回一行数据中多个列的内容多行多列返回多行记录之中同一列的内容相当于给出了一个操作范围。

多行多列查询返回的结果是一张临时表。

如果我们需要知道某个部分有哪些人我们是不是需要先知道这个部门所代表的部门编号因为员工信息表中存储的是部门编号知道了部门编号之后我们才能找出有哪些人。

查询销售部有哪些人

销售部;当我们知道了部门编号之后我们再在员工表中找出部门编号为1002的人的信息。

select

t2.deptno;因为最终要求显示员工号、员工名字、部门名字这些数据分别在两个表中所以我们需要将两个表联合起来并且需要对每张表中的数据做出判断。

子查询关键字

在MySQL中关键字ALL可以用于子查询中用于比较子查询返回的结果与外部查询条件进行比较。

关键字ALL与子查询配合使用时表示外部查询条件必须与子查询返回的所有值进行比较只有当外部查询条件大于或小于子查询中的所有值时才被视为满足条件。

select

在MySQL中ANY和SOME关键字可以与子查询一起使用用于比较外部查询条件与子查询返回的结果集中的任意一个值进行比较。

这两个关键字的作用是类似的它们都表示外部查询条件只需与子查询返回的结果集中的任意一个值相匹配即可判断为满足条件。

select

IN关键字用于检查外部查询条件是否与子查询结果集中的任意一个值相匹配。

如果外部查询条件与子查询结果集中的某个值匹配则被视为满足条件。

select

EXISTS关键字用于判断子查询是否有结果如果存在结果则被视为满足条件会执行前面的查询语句如果子查询没有结果则被视为不满足条件不执行前面的查询。

select

60);会发现这里将所有员工的信息都显示出来了这是为什么呢因为在

EXISTS

的整个表所以每次的查询都有结果所以emp3中的所有信息都打印了那么我们应该怎么办呢

我们可以形象的将MySQL的查询过程看作是一条记录一条记录的筛选所以我们可以使用表别名然后用表别名来判断每一条记录的员工的年龄是否大于60。

select

60);使用exists的情况大多也可使用in但是我们推荐使用exists因为MySQL底层exists做了优化查询效率更高。

自关联查询

MySQL自关联查询是指在查询中使用同一个表进行连接操作创建一个虚拟的关联。

在自关联查询中可以将表视为两个或多个不同的表并在查询中引用它们。

自关联查询通常涉及到在查询中使用表的别名以便可以引用同一个表的不同实例。

这样可以通过列与自身的比较来建立关联关系。

自关联查询在处理具有层级结构、关系链等的数据时非常有用常见的应用场景包括获取管理层级关系、查找相关的记录、获取亲属关系等。

create

values(1,刘协,NULL),(2,刘备,1),(3,关羽,2),(4,张飞,2),(5,曹操,1),(6,许褚,5),(7,典韦,1),(8,孙权,1),(9,周瑜,8),(10,鲁肃,8);我们需要显示出每个人对应的上级信息。

select