多表查询
多表关系
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在则会各种联系。
一对多/多对一
- 案例:部门与员工的关系
- 关系:一个部门对应多个员工,一个员工对应一个部门
- 实现:在多的一方建立外键,指向一的一方的主键
多对多
- 案例:学生与课程的关系
- 关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
- 实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
一对一
- 案例:用户与用户详情的关系
- 关系:一对一关系,多用于单表拆分,将一份表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
- 实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一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;
数据库MySQL(一):基础知识
数据库MySQL(二):DDL数据定义语言
数据库MySQL(三):DML、DQL、DCL
数据库MySQL(四):表中字段约束和外键约束
数据库MySQL(五):多表查询
数据库MySQL(六):事务