admin管理员组

文章数量:814309

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;


 

本文标签: MySQL关联查询与子查询(从小白到大牛)