mysql多表查询

---

前言

---

一、多表关系

由于业务需求，有时候各个表之间会相互关联。

1.一对多（多对一）

常见的例子，部门与员工之间的关系。
在多的一方建立外键，关联另一方的主键
一个部门对应多个员工，一个员工对应一个部门

2.多对多

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

3.一对一

实际开发中遇到的一对一的情况比较少。多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。

将上表拆分成下面两个表在任意一方加入一个外键，关联另一方的主键，并且设置外键为唯一约束

二、多表查询

一个概念：
链接表：数据库在做查询形成的中间表

1.交叉链接

mysql的实现方式有两种

SELECT * FROM emp,dept;　
SELECT * FROM emp CROSS JOIN dept;　

如果不加条件直接进行查询，则数据条数是两个表记录条数的乘积，这种结果我们称之为 笛卡尔乘积。

上图中4条SMITH的数据对应了4个部门。

笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积

消除笛卡尔积：

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

利用等值条件来处理笛卡尔积

2.内连接

隐式内连接

SELECT * FROM 表1，表2 WHERE 条件;

显示内连接

SELECT * FROM 表1 [INNER] JOIN 表2 ON 条件; 

隐式的意思是不需要关键字（INNER，JOIN），就像之前普通的查询方法一样。

等值连接

特指在连接条件中使用等于号(=)运算符比较被连接列的列值

SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;
SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

等值连接可以使用USING来自动关联两表中相同的列

SELECT * FROM emp INNER JOIN dept USING(deptno);

非等值连接

在连接条件使用除等于运算符以外的其它比较运算符比较被连接的 列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>等。

3.外连接

LEFT OUTER JOIN、RIGHT OUTER JOIN
关键字OUTER可省略。

1.左外连接

在表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联左边表（查询语句中体现的左）中不匹配的数据记录。
员工表
注意这里部门编号deptno为null
部门表
没有员工表中所对应部门的编号
左外连接语句

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

结果（还有一些正常的有部门编号的数据没显示）
可以看到员工表那些字段有值，而部门那几个字段都为NULL。

2.右外连接

在表关系的笛卡尔积中，出了选择相匹配的数据记录，还包含关联右边表中不匹配的数据记录。

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

同理，当某部门（右表中）没有员工时
查询结果

4.全连接

在表关系的笛卡尔积中，出了选择相匹配的数据记录，还包含关联左右两边表中不匹配的数据记录。

mysql似乎不支持直接实现全连接，不过我们可以通过集合来实现（下面会讲）。

5.自连接

顾名思义，就是自己跟自己连接（一张表）。
举个例子
test表

SELECT t.员工编号,t.姓名,t.性别,t.领导编号,te.姓名 AS 领导姓名 from test t,test te where t.领导编号=te.员工编号;

为了区分是分开的哪张表，要起不一样的别名
结果

三、SQL JOINS

图中

• 中间那个（只有A、B的交集是红的），实现的就是内连接（只包含有部门编号的员工连接后的信息）。

• 左上，实现了我们前面讲的左外连接，包含了“小冯”这个数据，同时也包含其他有部门编号的员工信息。
  

• 左中，只包含连接后没有部门编号部门信息的员工（上图中“小冯”这条数据）。

• 右边两个亦是同理。

• 左下，全连接，通过观察可以发现，可以用左上和右中两个图的并集来实现。

select * from emp LEFT JOIN dept ON emp.deptno=dept.deptno
UNION
SELECT * from emp RIGHT JOIN dept ON emp.deptno=dept.deptno WHERE emp.deptno IS NULL;

图中既包含了其它员工信息，也包含了两条不完整信息。

• 右下同理。

四、集合运算

MySQL支持并集运算。

上面例子中全连接时用到了UNION，这个就是集合运算的关键字。

select * from emp LEFT JOIN dept ON emp.deptno=dept.deptno
UNION
SELECT * from emp RIGHT JOIN dept ON emp.deptno=dept.deptno WHERE emp.deptno IS NULL;

• UNION DISTINCT
  可以省略DISTINCT
  相同的行在结果中只出现一次
• UNION ALL
  相同的行在结果中可能出现多次

两者的唯一区别就是，是否会删除重复行。