mysql查两个关联表的数据怎么查 mysql数据库关联表查数据

转载

mob6454cc649dc8 2023-10-28 13:59:36

文章标签 mysql查两个关联表的数据怎么查 mysql 自联结子查询 sql 文章分类 MySQL 数据库

文章目录

1、联结查询概述
2、WHERE实现联结查询
3、JOIN关键字实现内联结查询
4、外部联结
总结

MySQL数据库联结查询笔记整理

1、联结查询概述

在对数据库进行查询操作时，通常会遇到查询条件涉及多个数据表，或者需要展示多个表中的数据的情况，这种时候就可以使用联结查询来完成查询操作。

联结是一种机制，用来在一条SELECT语句中关联表。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

这里提一下子查询，子查询是指在一个SELECT语句中嵌套另一个SELECT语句，以该语句的查询结果来作为外层SELECT语句的查询条件，这种方式从结果来看也能实现联结查询的效果，但是子查询自身的效率非常低，在我们使用SQL查询语句的时候，特别是数据量很大，涉及的表很多的时候，应该尽量避免使用子查询。

2、WHERE实现联结查询

举个例子：
采用下面的语句来查询所有商品和其对应的供应商信息，其中商品信息和供应商信息分属两个数据表里（vendors,products）。

select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id  = products.vend_id
order by vend_name;


+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Sling          |       4.49 |
| ACME        | Carrots        |       2.50 |
| ACME        | Safe           |      50.00 |
| ACME        | Bird seed      |      10.00 |
| ACME        | Detonator      |      13.00 |
| ACME        | TNT (5 sticks) |      10.00 |
| ACME        | TNT (1 stick)  |       2.50 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Jet Set     | JetPack 2000   |      55.00 |
| Jet Set     | JetPack 1000   |      35.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+
14 rows in set (0.01 sec)

在上面的语句中，用where联结了两个表中共有的字段vend_id，用来匹配vendors表中的vend_id和products表的vend_id，可以看到要匹配的两个列列以 vendors. vend_id 和 products. vend_id指定。这⾥需要这种完全限定列名，因为如果只给出vend_id，则MySQL不不知道指的是哪一个（它们有两个，每个表中一个）。

上述的语句如果没有where子句作为联结条件，那么第一个表的每一行都与第二个表的每一行配对，无论逻辑上他们是否有关联，这样返回的结果数目将是两表行数之积，这个积称作笛卡尔积。
所以，一定不要忘记给出正确的where条件。

（用where子句来完成联结查询其实是sql92的标准语法。）

3、JOIN关键字实现内联结查询

sql99的语法规范中，是用join来完成联结查询的。

可以将联结查询分为 内联结 和 外联结，先来看内联结。

对于内联结，可以使用下面的统一格式的语法：
两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。

SELECT 查询列表
FROM 表1  别名
INNER(可省略) JOIN 表2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY
HAVING 
ORDER BY

例如，有一个员工信息表和部门信息表，要查询部门中员工数大于10个的部门名称，并按员工数降序排列，则sql语句如下：
SELECT COUNT(*) 员工数,d.department_name
FROM employees e
JOIN departments d
ON e.department_id=d.department_id
GROUP BY d.department_id
HAVING COUNT(*)>10
ORDER BY COUNT(*) DESC;

上述案例是内联结中等值联结的例子，等值指的是连接条件是用等号确定联结的，实际上还有非等值联结的场景：

例如，要查询员工工资级别

 SELECT COUNT(*) 个数,grade 
 FROM employees e
 JOIN sal_grade g
 ON e.salary BETWEEN g.min_salary AND g.max_salary
 WHERE e.department_id BETWEEN 10 and 90
 GROUP BY g.grade ;

也就是说联结条件处，ON联结的不是用等号确定联结的，而是指定了一个范围。

另外，内联结中还有一种形式叫做自联结，自联结从形式上来看，就是一个表与自己联结来进行查询，如下面的例子：

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。
此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。

-- 使用 自联结方式查询
select p1.prod_id,p2.prod_name
from products as p1
join products as p2 on p1.vend_id = p2.vend_id
where p2.prod_id = 'DTNTR';

+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
| DTNTR   | Detonator |
| FB      | Detonator |
| FC      | Detonator |
| SAFE    | Detonator |
| SLING   | Detonator |
| TNT1    | Detonator |
| TNT2    | Detonator |
+---------+-----------+

做自联结的时候，由于join 的两个表是同一个表，所以需要在join的前后给两个表起不同的别名加以区分。
自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但正如前面所说，有时候处理联结远比处理子查询快得多。

4、外部联结

内联结查询，是将多个表中的行互相关联起来，但是在某些应用场景里会需要包含一些没有关联行的行，这种时候就需要用到外部联结。

外部联结：
查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项，如果从表没有匹配项，则显示null
一般用于查询主表中有而从表中没有的记录

外连接分主从表，主表和从表的顺序不能颠倒调换
左连接，左边为主表，Left join
右连接，右边为主表， Right join

SELECT 查询列表
FROM 表1 别名
left/right outer join 表2 别名
on 等值判断条件（连接条件）
WHERE 筛选条件
GROUP BY 分组条件;

上述语法格式中的outer和前面内联结的inner一样都是可以省略的。

下面也简单给个案例吧：

--所有客户的订单信息，包括没有下订单的客户
select customers.cust_id,orders.order_num 
from customers 
left join orders on customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001   | 20005 |
| 10001   | 20009 |
| 10002   | NULL  |
| 10003   | 20006 |
| 10004   | 20007 |
| 10005   | 20008 |
+---------+-----------+
6 rows in set (0.00 sec)

--对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；
select customers.cust_id,count(orders.order_num) as nums
from customers left join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;

+---------+------+
| cust_id | nums |
+---------+------+
|   10001 |    2 |
|   10002 |    0 |
|   10003 |    1 |
|   10004 |    1 |
|   10005 |    1 |
+---------+------+

可以看到，外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出了。