MySQL学习笔记(五): 多表连接及子查询

MySQL中的多表连接及子查询

一.多表连接

数据表的多表连接查询比较常用的有内连接和外连接查询两种。

1.内连接

我们先来介绍下内连接查询，是通过 INNER JOIN … ON (可以缩写为 join)关键字实现的；具体语法如下：

SELECT field1,field2,...fieldn 
from table_name 
INNER JOIN join_table 
ON join_condition;

这里注意的是 on 后面其实跟的就是外键约束中相对应的两个列名！

例如:

select c_name, ct_name 
from commodity
join commoditytype
on c_type=ct_id;

2.外连接

外连接分2种，左连接和右连接；它们用法上一样的，查询逻辑上互为相反；具体语法如下：

SELECT field1,field2,...fieldn
from table_name 
LEFT|RIGHT JOIN join_table 
ON join_condition;

例如:

select c_name, ct_name
from commodity
left join commoditytype
on c_type=ct_id;

注意:

• 左连接和右连接的区别: 以执行语句中的哪个表为主表，所谓主表即以主表为准，主表中有的数据才显示，主表中没有的数据即使附表中有也不显示在结果中！
• 内连接和外连接的区别: 内连接的查询结果为连接的两个表中都有数据, 而外连接只要求连接的其中一个表中有数据就会显示在结果中

一张图看懂内外, 左右连接查询的区别:

二.子查询

对了连接查询确实能将两张表格同时查询输出，但有是我只是想
用一张表的数据作为条件去查另一张表，我们需要用到子查询了；
我们一起来看下面这条查询语句：

select * from table_a where sal > (select sal from table_b where name = 'TomCat');

这其实就是一条简单的子查询语句，先从b表中查询出tomcat的售价，那括号内的查询结果一定是单行单例的一个值了，那么再通过这个值去查询出售价大于这个值的所有商品；
注意：这里的返回值必须是 单行单列 的值！

那 单行多列 怎么查？再来看下面这条查询语句：

select * from table_a where (sal,name) = (select sal,name from table_b where name = 'TomCat');

先从b表中查询出tomcat的售价,输出的结果有2个列属于单行多列，分别是售价和名字，那么再通过这两个值去查询和这两个值相等的所有商品；
注意：这里的返回值和查询的列名顺便必须一致！

单列多行:

//查询结果在返回字段结果内的
select * from table_a where sal IN (select sal from table_b;);
//查询结果不在返回字段结果内的
select * from table_a where sal NOT IN (select sal from table_b;);
//查询结果在返回字段结果内任意满足
select * from table_a where sal >=ANY (select sal from table_b;); 
//查询结果在返回字段结果内全部满足
select * from table_a where sal >=ALL (select sal from table_b;); 

语法说明如下:

<表达式> {= | < | > | >= | <= | <=> | < > | != } { ALL | SOME | ANY} <子查询>

• <子查询>：用于指定子查询。
• <表达式>：用于指定要进行比较的表达式。
• ALL、SOME 和 ANY：可选项。用于指定对比较运算的限制。其中，关键字 ALL 用于指定表达式需要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较关系时，会返回 TRUE，否则返回 FALSE；关键字 SOME 和 ANY 是同义词，表示表达式只要与子查询结果集中的某个值满足比较关系，就返回 TRUE，否则返回 FALSE。