MySQL——多表操作

一、多表关系

(一) 一对一

实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

(二) 一对多(多对一)

实现: 在多的一方建立外键，指向一的一方的主键

(三) 多对多

实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

二、外键约束

MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。
外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。
注意：外键约束对多表查询并无影响。

三、多表查询

(一) 交叉连接查询

交叉连接查询返回被连接的两个表所有数据行的笛卡尔积
笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配
假如A表有m行数据，B表有n行数据，则返回m*n行数据
笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

select * from 表1,表2,表3….; 

(二) 内连接查询

结果集只包含参加连接的表中与指定字段相符的行

隐式内连接（SQL92标准）：select * from A,B where 条件; 
显示内连接（SQL99标准）：select * from A inner join B on 条件;

查询人数大于等于3的部门，并按照人数降序排序
select 
	deptno,count(1) as total_cnt 
from dept 
	join emp on dept.deptno = emp.dept_id 
group by 
	deptno 
having 
	total_cnt >= 3 
order by 
	total_cnt desc;

(三) 外连接查询

外连接生成的结果集不仅包含符合连接条件的行数，而且还包括左表(左外连接时的表)、右表(右外连接时的表)或两边连接表(全外连接时的表)中所有的数据行。
外连接分为左外连接（left outer join）、右外连接(right outer join)，满外连接(full outer join)。
注意：oracle里面有full join,可是在mysql对full join支持的不好。我们可以使用union来达到目的。

左外连接查询 left outer join

将左表中的所有数据分别与右表中的每条数据进行连接组合，返回的结果除内连接的数据外，还包括左表中不符合条件的数据，并且在右表的相应列中添加NULL值。

select * from A left outer join B on 条件;

select * from dept LEFT OUTER JOIN emp on dept.deptno = emp.dept_id;

外连接多个条件

select
* 
from A
	left join B on 条件1,
	left join C on 条件2,
	left join D on 条件3;

右外连接查询 right outer join

将右表中的所有数据分别与左表中的每条数据进行连接组合，返回的结果除内连接的数据外，还包括右表中不符合条件的数据，并且在左表的相应列中添加NULL值。

select * from A right outer join B on 条件;

select * from dept right OUTER JOIN emp on dept.deptno = emp.dept_id;

满外连接(合并查询)

将多个select语句的查询结果合并到一起
union ：将两个查询结果上下拼接，并去重
union all： 将两个查询结果上下拼接，不去重

使用union关键字实现左外连接和右外连接的并集

select * from dept left outer join emp on dept.deptno = emp.dept_id
union 
select * from dept right outer join emp on dept.deptno = emp.dept_id;

(四) 子查询

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询。
注意： 有时关联查询和子查询都可以实现，而关联查询效率更高

基本子查询

单行单列：返回的是一个具体列的内容，可以理解为一个单值数据

-- 查询年龄最大的员工信息
select * from emp where age = (SELECT max(age) from emp);

单行多列：返回一行数据中多个列的内容
多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围

select * from emp where dept_id in (select dept.deptno from dept WHERE name = '研发部' or name = '销售部');

多行多列：查询返回的结果是一张临时表

-- 查询研发部20岁以下的员工信息,包括员工号、员工名字，部门名字
select eid,age,ename,name from (select * from emp where age < 20)t1 inner join (select * from dept where name = '研发部')t2 on t1.dept_id = t2.deptno;

子查询关键字

(1) ALL

select …from …where c > all(查询语句)
--等价于：
select ...from ... where c > result1 and c > result2 and c > result3

ALL: 与子查询返回的所有值比较为true 则返回true
ALL可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。
ALL表示指定列中的值必须要大于子查询集的每一个值，即必须要大于子查询集的最大值；如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。

-- 查询年龄大于‘1003’部门所有年龄的员工信息
select * from emp where age > all(select age from emp where dept_id = '1003');

(2) ANY和SOME

select …from …where c > any(查询语句)
--等价于：
select ...from ... where c > result1 or c > result2 or c > result3

ANY:与子查询返回的任何值比较为true 则返回true
ANY可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何一个数据。
表示制定列中的值要大于子查询中的任意一个值，即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。
SOME和ANY的作用一样，SOME可以理解为ANY的别名

select * from emp where age > any(select age from emp where dept_id = '1003') and dept_id != '1003';

(3) IN

select …from …where c in(查询语句)
--等价于：
select ...from ... where c = result1 or c = result2 or c = result3

IN关键字，用于判断某个记录的值，是否在指定的集合中
在IN关键字前边加上not可以将条件反过来

select eid,ename from emp where dept_id in (select deptno from dept where name = '研发部' or name = '销售部') ;

(4) EXISTS

select …from …where exists(查询语句)

该子查询如果“有数据结果”(至少返回一行数据)， 则该EXISTS() 的结果为“true”，外层查询执行
该子查询如果“没有数据结果”（没有任何数据返回），则该EXISTS()的结果为“false”，外层查询不执行
EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时 where条件成立
注意，EXISTS关键字，比IN关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用EXISTS关键字

-- 查询公司是否有大于60岁的员工，有则输出
select * from emp a where exists(select eid from emp where a.age > 60);

select * from emp a where eid in (select eid from emp where a.age > 60);

注意：emp表需要起别名，让外查询与子查询语句都决定条件

(五) 自关联查询

MySQL有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。注意自关联时表必须给表起别名。

-- 查询上级，上上级
select 
	a.ename,b.ename,c.ename
from t_sanguo a 
	left OUTER JOIN t_sanguo b ON a.manager_id = b.eid
	left OUTER JOIN t_sanguo c ON b.manager_id = c.eid;