mysql 自连接与内连接

偏执的太偏执、 2023-05-31 15:17 33阅读 0赞

mysql自连接  
mysql有时在信息查询时需要进行对自身连接（自连接），所以我们需要为表定义别名。我们举例说明，下面是商品采购表，我们需要找到采购价格比惠惠高的所有信息。

一般情况我们看到这张表我们第一时间用语句进行操作：

SELECT \* FROM shoping WHERE price>27  
1  
可想而知，这是有多么简单，假设你并不知道数据库表详细数据或者数据量相当庞大呢？作为一个数据库管理员，我们就要用别的方式迅速找出所需要的数据。

分步查询  
最简单的一种方式，也是最容易想到操作：

**SELECT price FROM shopping WHERE name='惠惠' //得出price查询结果为27  
SELECT \* FROM shopping WHERE price>27**  
  
与采用自连接的方式相比，这种方法需要对中间结果进行人工干预，显然不利于程序中的自动处理操作。

自连接方式：  
**SELECT b.\*   
from shopping as a,shopping as b  
where a.name='惠惠'   
and a.price<b.price   
order by b.id**

三层自连接查询

**SELECT   c.dept\_name area ,  b.dept\_name as city ,  a.\*   
from sys\_dept as a,sys\_dept as b ,sys\_dept as c  
where a.parent\_id= b.dept\_id   
and   b.parent\_id= c.dept\_id  
and   a.agency\_id >= -1   
order by b.dept\_id **

我们可以获取到如下表信息：

注意点：

别名 a,b虽然名称不同，但是同一个表，定义别名的目的是更方便在自身进行删选。  
执行select通过（中间表）所得到的b.\*，，就是最终结果。  
子查询  
子查询也是常用的一种方式，就是在select里嵌套select。实现代码如下：

SELECT \* FROM shopping   
WHERE price>(select price from 'shopping' where name='惠惠')

所得结果如下所示,可见两种方式所得结果相同：

JOIN  
INNER JOIN  
内关联的主要作用是在表中存在至少一个匹配时，就返回结果集，这里的inner join和join作用相同，所以一起介绍。  
下面给出两张表,分别为goods和category表：

**SELECT \* FROM goods INNER JOIN category   
ON goods.id=category.goods\_id   
ORDER BY gods.id**  
  
结果如下所示：

LEFT JOIN  
LEFT JOIN 关键字会从左表 (table\_name1) 那里返回所有的行，即使在右表 (table\_name2) 中没有匹配的行。做项目时建议使用左关联。但是存在很多表关联的，一个表对a是左连接，但同时对b是右连接，这时加上右连接，写起来可能方便一些。  
套用一开始的两张表，进行左关联查询：

**SELECT goods.\*,category.cate\_name   
FROM goods LEFT JOIN category   
ON goods.id=category.goods\_id   
ORDER BY goods.id**  
  
RIGHT JOIN  
RIGHT JOIN 关键字会右表 (table\_name2) 那里返回所有的行，即使在左表 (table\_name1) 中没有匹配的行。套用一开始的两张表进行右关联查询：