多表关系

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

一对多（多对一）
多对多
一对一

一对多

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

多对多

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

一对一

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

笛卡尔积

概述：两个集合，A集合和B集合的所有组合情况，如：select * from employee, dept;（在多表查询时，需要消除无效的笛卡尔积）

消除无效笛卡尔积： select * from employee, dept where employee.dept = dept.id;

内连接查询

内连接也被称为相等连接或简单连接，它是把两张表中满足on子句中搜索条件/where子句搜索条件的行连接起来或者说把两张表中相关的行连接起来（注意：搜索条件中指定的列都会被显示，并不是说被合并成一列）

隐式内连接： SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
显式内连接： SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;
显式性能比隐式高

例子：

表结构如下：
案例表结构

  
-- 查询员工姓名，及关联的部门的名称
-- 隐式
select e.name, d.name from employee as e, dept as d where e.dept = d.id;
-- 显式
select e.name, d.name from employee as e inner join dept as d on e.dept = d.id;

外连接查询

左外连接：两张表中满足on子句中搜索条件的行连接起来以及左表所有数据，SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;在这个格式中表1就是左表
右外连接：两张表中满足on子句中搜索条件的行连接起来以及右表所有数据，SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;

例子：

  
-- 左
select e.*, d.name from employee e left join dept d on e.dept = d.id;
-- 右
select d.name, e.* from employee e right join dept d on e.dept = d.id;

内连接和外连接的区别：外连接有个明显的特点，就是不管满不满足on子句搜索条件，左表或右表的数据会全部查询，而内连接中要是不相关的行就不会被查询

自连接查询

当前表与自身的连接查询，自连接必须使用表别名，

如何理解内连接：内连接是自己于自己连接，也就是行与行之间是有相关逻辑的，例如下面例子中行与行之间可以通过id和manager两个字段联系起来，因此我们也可以将其看作两张不同的表，其中manager是从表中的外键，id是主表中的主键，这样一来就可作为内连接或外连接理解了

语法：SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;，自连接查询，可以是内连接查询，也可以是外连接查询

例子：

  
-- 查询员工及其所属领导的名字
select a.name, b.name from employee a, employee b where a.manager = b.id;
-- 没有领导的也查询出来
select a.name, b.name from employee a left join employee b on a.manager = b.id;

联合查询 union, union all

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

语法：

  
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...

注意：
1.进行联合查询的多张表的列数必须相等，字段类型也需要保持一致
2.相比于UNION ALL，UNION会对合并的结果集去重
3.联合查询比使用or效率高，不会使索引失效

子查询

概述：SQL中嵌套select语句，称为嵌套查询，也叫子查询，如：
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);
子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询结果可以分为：

标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）
行子查询（子查询结果为一行）
表子查询（子查询结果为多行多列）

根据子查询位置可分为：

WHERE 之后
FROM 之后
SELECT 之后

标量子查询

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

例子：

  
-- 查询销售部所有员工
select id from dept where name = '销售部';
-- 根据销售部部门ID，查询员工信息
select * from employee where dept = 4;
-- 合并（子查询）
select * from employee where dept = (select id from dept where name = '销售部');

-- 查询xxx入职之后的员工信息
select * from employee where entrydate > (select entrydate from employee where name = 'xxx');

列子查询

返回的结果是一列（可以是多行），即该字段下有多条记录符合要求。

常用操作符：

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

例子：

  
-- 查询销售部和市场部的所有员工信息
select * from employee where dept in (select id from dept where name = '销售部' or name = '市场部');
-- 查询比财务部所有人工资都高的员工信息
select * from employee where salary > all(select salary from employee where dept = (select id from dept where name = '财务部'));
-- 查询比研发部任意一人工资高的员工信息
select * from employee where salary > any (select salary from employee where dept = (select id from dept where name = '研发部'));

行子查询

返回的结果是一行（可以是多列）。常用操作符：=, <, >, IN, NOT IN

例子：

  
-- 查询与xxx的薪资及直属领导相同的员工信息
select * from employee where (salary, manager) = (12500, 1);
select * from employee where (salary, manager) = (select salary, manager from employee where name = 'xxx');

表子查询

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

例子：

  
-- 查询与xxx1，xxx2的职位和薪资相同的员工
select * from employee where (job, salary) in (select job, salary from employee where name = 'xxx1' or name = 'xxx2');
-- 查询入职日期是2006-01-01之后的员工，及其部门信息
select e.*, d.* from (select * from employee where entrydate > '2006-01-01') as e left join dept as d on e.dept = d.id;

注意：在MySQL中，子查询得到的临时表（结果集）应该给别名，不然当外侧select from 的表与里侧的相同就会报
You can't specify target table '外侧表名' for update in FROM clause
案例地址：删除重复的电子邮箱

多表查询例题

删除重复的电子邮箱原题链接

题解：

  
delete from person where person.id in
(select a.id from (select p1.id from person p1, person p2 where p1.email=p2.email and p1.id>p2.id) as a);

易错点：
1.如果是(select * from person p1, person p2 where p1.email=p2.email and p1.id>p2.id) as a，就会出错；因为a最后被作为临时表被查询，表中是不允许有重复的字段的
2.select * from person p1, person p2 where p1.email=p2.email and p1.id>p2.id，必须有别名，规定派生表必须有自己的别名，否则报Every derived table must have its own alias
3.如果少了这条语句select a.id from 就会报错You can't specify target table 'person' for update in FROM clause，意思是子查询操作的表和最近外层操作的表不能是同一张表，所以往往把子查询作为临时表，再重复查询一次

多表查询