SQL系列3-连表查询

SQL系列3-连表查询✅

子查询

嵌套子查询进行过滤：

SELECT cust_name, cust_contact         #顾客信息
FROM Customers
WHERE cust_id IN (SELECT cust_id       #订单所属顾客
 FROM Orders 
 WHERE order_num IN (SELECT order_num  #包含物品RGAN01的订单
 FROM OrderItems
 WHERE prod_id = 'RGAN01'));

子查询创建计算字段：

SELECT cust_name, 
 cust_state,
 (SELECT COUNT(*) 
 FROM Orders 
 WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers 
ORDER BY cust_name;

假如需要显示 Customers 表中每个顾客的订单总数

image-20230918094419349

虽然这里给出的样例代码运行良好，但它并不是解决这种数据检索的最有效方法。

联结表

使用联结表之前，需先了解关系表以及关系数据库。

现在有同一供应商生产的多种物品，那么在何处存储供应商名、地址、联系方法等供应商信息呢？

 同一供应商生产的每个产品，其供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间；

 如果供应商信息发生变化，例如供应商迁址或电话号码变动，只需修改一次即可；

 如果有重复数据（即每种产品都存储供应商信息），则很难保证每次输入该数据的方式都相同。不一致的数据在报表中就很难利用。

​ 关键是，相同的数据出现多次决不是一件好事，这是关系数据库设计的基础。关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联（所以才叫关系数据库）。

可伸缩性(scale):能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好(scale well)

联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。
使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。
联结不是物理实体，它在实际的数据库中不存在，联结由MYSQL根据需要建立，它存在于查询的执行当中。
维护引用完整性：通过在表的定义中指定主键和外键来实现的。

内联结（连接）

内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只用关键字 JOIN。 使用内连接后，FROM 子句中的 ON 子句可用来设置连接表的条件。 在 FROM 子句中可以在多个表之间连续使用 INNER JOIN 或 JOIN，如此可以同时实现多个表的内连接。内连接也被称为等值联结。

image-20230918135041635

自联结

此查询中需要的两个表实际上是相同的表，因此表table在FROM子句中出现了两次。为了避免二义性，使用了表别名。

  • 用自联结而不用子查询
    自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句，虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。

  • 假如要给与 Jim Jones 同一公司的所有顾客发送一封信件。这个查询要求

    首先找出 Jim Jones 工作的公司，然后找出在该公司工作的顾客

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

自然联结

无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。标准的联结（前一课中介绍的内联结）返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次。

怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT *），而对其他表的列使用明确的子集来完成。

外联结

许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。可以省略 OUTER 关键字

SELECT a.*,b.column2_name FROM table1 AS a LEFT JOIN table2 AS b ON a.column1 = b.column2;

如：

 对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；

 列出所有产品以及订购数量，包括没有人订购的产品；

 计算平均销售规模，包括那些至今尚未下订单的顾客。

SELECT Customers.cust_id, Orders.order_num
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN左边的表）。上面的例子使用 LEFT OUTER JOIN 从 FROM 子句左边的表。

image-20230918140116407

image-20230918140133284

还有一个全外连接，MySQL目前不支持此种方式，可以用其他方式替代解决，不细说了。

它检索两个表中的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行。

（Customers 表）中选择所有行

image-20230918135605835

带聚集函数的联结

SELECT Customers.cust_id,
 COUNT(Orders.order_num) AS num_ord
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

这条 SELECT 语句使用 INNER JOIN 将 Customers 和 Orders 表互相关联。

GROUP BY 子句按顾客分组数据，因此，函数调用 COUNT(Orders.order_num)

对每个顾客的订单计数，将它作为 num_ord 返回。

总结：

 注意所使用的联结类型。一般我们使用内联结，但使用外联结也有效。

 关于确切的联结语法，应该查看具体的文档，看相应的 DBMS 支持何

种语法（大多数 DBMS 使用这两课中描述的某种语法）。

 保证使用正确的联结条件（不管采用哪种语法），否则会返回不正确

的数据。

 应该总是提供联结条件，否则会得出笛卡儿积。

 在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结

类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们

前分别测试每个联结。这会使故障排除更为简单。

mysql系列（十三）SQL语句之联结表_sql语录联结表格_zhaojiaxing0216的博客-CSDN博客

可以完美总结联结这一仗知识点。

posted @ 2023-09-18 16:00  zheng-s  阅读(195)  评论(0)  编辑  收藏  举报
,