mysql中多对多关系sql查询 数据库多对多查询

数据库基础（三） 多表查询

一、多表关系

一对多

多对多

一对一

在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表之间也存在着某种联系。包括：

• 一对多 如部门和员工之间的关系 在多的一方建立外键，指向一的一方的主键。
  -
• 多对多 如学生与课程的关系 建立第三张中间表，中间表至少包含两个外键，分别关联双方主键。

• 一对一 如用户与用户详细信息的关系 在任意一方加入外键，关联另一方主键，并设置外键为UNIQUE.

二、多表查询

id	name	age	dept_id
1	张三	18	001
2	里斯	22	002
3	万我	23	001
4	赵六	20	null

id	dept_name
001	研发部
002	算法部

1、连接查询

• 内连接：查询表交集部分数据

//隐式内连接
select 字段列表 from 表1,表2 where 条件 ...;
select * from emp e,dept d where e.dept_id=d.id;
//显式内连接
select 字段列表 表1 [inner] join 表2 on 连接条件 ...； 
select e.name,d.name from emp e inner join dept d on e.dept_id=d.id;

• 外连接：

• 左外连接：查询左表所有数据，以及两表交集部分数据
• 右外连接：查询右表所有数据，以及两表交集部分数据

//左外连接  用于外键为null导致内连接数据无法查询到的情况
select 字段列表 from 表1 left [outer] join 表2 on 条件...;
//查询emp表所有数据，和对应部门信息
select e.*,d.name from emp e left outer join dept d on e.dept_id=d.id;
//右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
//查询dept表所有数据，和对应员工信息
select d.*,e.* from emp e right outer join dept d on e.dept_id=d.id;

• 自连接：当前表与自身的连接查询

//自连接
select 字段列表 from 表A 别名1 join 表A 别名2 on 条件...;
//查询员工及其所属领导的名字
select a.name,b.name from emp a,emp b where a.manager_id=b.id;

2、子查询

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

//联合查询语法
select 字段列表 from 表1 ...
union [all]
select 字段列表 from 表b ...;
//查询年龄大于20的员工和属于部门001的员工
select * from emp where age>20
union 
select * from emp where dept_id='001';

联合查询需要多张表查询列数和字段类型保持一致。
union all 会将数据直接合并会存在重复，而union合并后去重。

• 子查询（嵌套查询）

//查询研发部所有员工信息
select * from emp where dept_id=(select id from dept where name="研发部");
//查询研发部和算法部所有员工信息   列子查询 常用操作符：IN 、NOT IN、ANY、SOME、ALL
select * from emp where dept_id in (select id from dept where name='研发部' or name='算法部'；
//行子查询 常用操作符：=、<>、IN、NOT IN
select * from emp where(sa,mag_id)=(select sa,mag_id from emp where name='张三');