当前位置：首页 > article >正文

数据库MySQL（五）：多表查询

article 2024/9/22 7:22:42

多表查询

多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在则会各种联系。

一对多／多对一

案例：部门与员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键

多对多

案例：学生与课程的关系
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

案例：用户与用户详情的关系
关系：一对一关系，多用于单表拆分，将一份表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一UNIQUE

笛卡尔积

A集合和B集合的所有组合情况；在多表查询中，需要消除无效的笛卡尔积，只保留两张表关联部分的数据。

select * from tableA , tableB where tableA.字段 = tableB.字段

内连接

查询两张表交集部分的数据，即下图中绿色部分的数据

//隐式内连接
select 字段列表 from tableA , tableB where 条件列表;

//显式内连接
select 字段列表 from tableA [inner] join tableB on 连接条件;

左外连接

查询左表的所有数据和两张表交集部分的数据。

右外连接

查询右表的所有数据和两张表交集部分的数据。

//左外连接
select 字段列表 from tableA left [outner] join tableB on 连接条件;

//右外连接
select 字段列表 from tableA right [outner] join tableB on 连接条件

-- 左外连接和右外连接是可以相互替换的，在需要在SQL语句中调整表结构的先后顺序即可。在日常开发中更偏向于左外连接。

自连接

自己连接自己，把一张表连接查询多次。

select 字段列表 from tableA 别名A join tableA 别名B on 连接条件 ... ;
-- 自连接查询中，必须要为表起别名，要不然不清楚所指定的的条件、返回的字段属于哪一张表的字段

联合查询

把多次查询的结果合并起来，形成一个新的查询结果集

select 字段列表 from 表A ...
	union [all]
	select 字段列表 from 表B ... ;
--! 如果多条查询语句查询出来的结果、字段数量不一致，在进行union [all]联合查询时，将会报错。

# union 可以去除重复记录结果
# union all 合并操作效率更高

子查询

在SQL语句中嵌套SELECT语句，称为嵌套查询/子查询。SQL中允许多重嵌套。

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等）
常用操作符：==、<>、>、>=、<、<=

列子查询

子查询返回的结果是一列，可以是多行。
常用操作符：IN、NOT IN、ANY、SOME、ALL

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY/SOME	子查询返回列表中，有任意一个满足即可
ALL	子查询返回列表的所有值都必须满足

行子查询

子查询返回的结果是一行，可以是多列
常用操作符：==、<>、>、>=、<、<=
把返回的一行记录中的多个字段的组合看作一个结果，等同于标量子查询

表子查询

子查询返回的结果是多行多列。
常用操作符：IN

代码示例

select * from emp where dept_id = (select id from dept where name = '销售部');

select * from emp 
	where dept_id = (select id from dept where name = '销售部' or name ='市场部');

select * from emp 
	where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');

select * from emp 
	where (job, salary) in (select job, salary from emp where name='鹿仗客' or name = '宋远桥');

select e.*, d.* 
	from (select * from emp where entrydate > '2006-01-01') e 
	left join dept d on e.dept_id = d_id;