MySQL—关联查询与子查询（从小白到大牛）

---

前言 

在大部分的数据库查询中，我们往往都不是进行简单的单表查询，而是多表查询。所以我们也必须学习SQL查询中的高级部分，如内连接、外连接和子查询，通过这些查询技术我们将能够解决项目中复杂的查询问题。

---

表关联关系

在使用关联查询之间，我们需要知道表和表之间的关联关系，以此才能更好的操作表。

---

 关联查询

在实际运用数据的场景中，往往我们使用的数据并不是来自一个表，而需要从多个表中抽取数据来形成我们需要的结果集。

首先我们在数据库中建表和插入相关数据，方便我们案例学习： 

-- 班级表
create table t_class(
	id int primary key auto_increment,
	name varchar(255) not null
);
-- 学生表
create table t_student(
	id int primary key auto_increment,
	name varchar(255) not null,
	class_id int not null
);

 

1、左连接：保证左表的全部记录都显示出来，右表只显示关联的记录，不关联的记录不会显示出来。

代码实例：

-- 左连接 left join 
select * from t_class c 
left join t_student s on c.id = s.class_id;

运行结果：

 2、右连接：右表记录全查，左表只显示关联部分

代码实例：

-- 右连接 right join
select * from t_class c
right join t_student s on c.id = s.class_id;

 运行结果:

 3、内连接:  只查询左右表相交部分数据

  代码实例：

-- 内连接 inner join 
select * from t_class c
inner join t_student s on c.id = s.class_id;

运行结果：

内连接的另一种实现方法，是在笛卡尔积的基础上实现的，查询结果相同：

-- 另一种方式实现内连接
select * from t_class c,t_student t
where c.id = t.class_id;

 4、左外连接：是指以左边的表的数据为基准，去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为null。简单来说就是在左连接的基础上，把交叉部分干掉。

代码实例:

-- 左外连接
select * from t_class c 
left join t_student s on c.id = s.class_id
where s.id is null;

运行结果:

 5、右外连接：简单来说就是在右连接的基础上，把交叉部分干掉。

代码实例：

-- 右外连接
select * from t_class c
right join t_student s on c.id = s.class_id
where c.id is null;

运行结果:

6、全连接:  把所有的情况都显示下来。(左连接+右外连接)

代码实例:

-- 全连接
select * from t_class c 
left join t_student s on c.id = s.class_id
union
select * from t_class c
right join t_student s on c.id = s.class_id
where c.id is null;

运行结果:

7、全外连接 :只查询两表不相交的部分（左外连接+右外连接）

代码实例:

-- 全外连接
select * from t_class c 
left join t_student s on c.id = s.class_id
where s.id is null
union
select * from t_class c
right join t_student s on c.id = s.class_id
where c.id is null;

 运行结果：

8、笛卡尔积 : 当两个表或多个表，没有通过连接字段进行关联，而直接进行连接，这时会形成笛卡尔积，结果集会是所有参与连接的表的条数的乘积。

代码实例：

-- 笛卡尔积
select * from t_class,t_student;

 运行结果：

 9、自连接：简单来说就是自己连接自己。

代码实例：

-- 自连接
select * from t_class a 
left join t_class b on a.id = b.id;

运行结果：

 

---

子查询 

子查询就是在查询中嵌套另外一个查询的查询。子查询需要使用括号括起来。

按照子查询出现的位置，我们可以将子查询分成4类，select型子查询、where型子查询、from型子查询、exists型子查询。

1、select型子查询：把子查询当作一个字段来看。

案例：

-- 求各个部门的人数是多少
select *,(
SELECT count(*) from emp e where d.deptno = e.deptno) n
from dept d;

结果：

归纳： select子查询是先查外面语句，再把查询结果一个一个代入到里面的查询语句，里面的查询语句一定要等于一个外面的列，这样就可以关联起来了。

2、where型子查询：顾名思义，where型子查询就是子查询出现在主查询的where条件子句中。

案例一：

-- where子查询
-- 找出比孙七工资高的员工
select * from emp e where e.sal >
(select e.sal from emp e where e.ename = '孙七');

 运行结果：

 案例二：

-- 找出比孙七、周八工资都高的员工
select * from emp e where e.sal >
(select e.sal from emp e where e.ename in ('孙七','周八') 
order by e.sal desc limit 1);

运行结果：

 归纳：where型子查询是子语句先查询，然后再执行外面的语句。

3、from型子查询：查询结果集在结构上可以当成表看，那就可以当成临时表对他进行再次查询

案例：

-- from子查询（把查询结果当一张表来看）
-- 找出研发部和销售部工资大于2500的员工
select a.* from 
(select e.* from emp e
left join dept d on e.deptno = d.deptno
where d.dname in ('研发部','销售部')) a 
where a.sal > 2500;

运行结果：

 4、exists型子查询：又叫相关子查询

案例：

-- exists 子查询,通常可以用来代替in
-- 查询实际存在员工的部门
select * from dept d where EXISTS(
	select * from emp e where e.deptno = d.deptno
)

运行结果：

---

any、some和all关键字

我们在where型子查询中，使用了 in 与 not in 关键字。这是一种集合处理。还有集合处理的关键字，也经常用到子查询中，就是 any 和 all 关键字（some是 any的同义词，较少使用）。  

 案例：

-- any some all
-- 找出工资大于郑十或孙七的员工
select * from emp e where e.sal > any(
	select e.sal from emp e where e.ename = '郑十' or e.ename = '孙七'
);
-- 找出工资大于郑十和孙七的员工
select * from emp e where e.sal > all(
	select e.sal from emp e where e.ename = '郑十' or e.ename = '孙七'
);

---

limit关键字与分页查询

在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。

案例一：

-- limit 分页 每页显示5条
-- limit 索引值，每页多少条
select * from emp limit 0,5;

 案例二：

-- limit 每页多少条 从索引为0的地方开始算
select * from emp limit 1;

---