一、多表关系介绍
- 一对多(多对一)
案例:部门与员工的关系
关系:一个部门对应多个员工,一个员工对应一个部门
实现:在多的地方建立,指向一的一方的主键
- 多对多
案例:学生与课程的关系
关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
- 一对一
案例:用户与用户信息的关系
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,其他详情字段放在另一张表中,以提升操作效率
实现:在任意一方插入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
二、多表查询概述
- 概述:指从多张表中查询数据
笛卡尔积:笛卡尔乘积是指在数学中,两个集合A集合和B集合的所有组合情况(在多表查询时,需要消除无效的笛卡尔乘积)
若要消除笛卡尔积,如下例:
需要加一个条件,即外键值=主键值
select * from emp, dept where emp.dept_id=dept.id;
- 多表查询分类:
三、内连接
语法
- 隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
- 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
(inner可省略)
示例:
注:多表查询时可以给两张表起别名,但是起了之后必须只用别名
相对而言,隐式连接好理解好书写,语法简单,担心的点较少
但是显式连接可以减少字段的扫描,有更快的执行速度。这种速度优势在3张或更多表连接时比较明显
四、外连接
语法(默认表1是左表,表2是右表)
- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件...;
- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 连接条件...;
(左外是指保留左边所有的数据,然后如果有和右边对应的,就把右边的数据也带上)右外连接同理哦!
示例:
通常来讲,使用左外连接更多一点,因为左外和右外本质上是镜像的关系
五、自连接
语法
隐式:SELECT 字段列表 FROM 表A 别名A,表A 别名B WHERE 条件...;
显式:SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
自连接可以是内连接和外连接
示例:
第一个是使用内连接实现的自连接,必须要求managerid和id都非空且值相等才查出来
第二个用左外连接,除了要求字段值匹配以外,哪怕managerid为空也能查出来
两种方式数学上是不同的,但是都被归为“自连接”这一类
六、联合查询
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集
语法
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;
示例:
关于or和联合查询的差异:
第一or去重,二,实际应尽量避免在where子句中使用or来连接条件。使用or可能会使索引失效,从而全表扫描。mysql是有优化器的,处于效率与成本考虑,遇到or条件,索引可能失效
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致
union all会将全部的数据直接合并在一起,union会对合并之后的数据去重
七、子查询
概览:
- 标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式
常用的操作符:= <> > >= < <=
示例:
步骤:分步写出sql语句,再合并写出子查询语句
提问:是否可以用group by实现同样的效果
不可以,如果用group by,分组是按照id分的,你只能得到6个分组,这个时候你查询的字段只有分组字段和聚合函数有意义,得不到员工信息
- 列子查询
子查询返回的结果是一列
常用的操作符:IN、NOT IN、ANY、SOME、ALL
示例:
你可能想到了聚合函数max,但是在where中是无法使用聚合函数的。
- 行子查询
子查询返回的结果是一行
常用操作符:=、<>、IN、NOT IN
- 表子查询
子查询返回的结果是多行多列
常用操作符:IN
示例:
注意上面这里‘in’的理解