MySQL 学习笔记 —— 13、创建高级联结

最新推荐文章于 2023-01-11 16:59:26 发布

timedecdec

最新推荐文章于 2023-01-11 16:59:26 发布

阅读量193

点赞数

分类专栏：学习笔记大数据文章标签： mysql sql 数据库其他经验分享

本文链接：https://blog.csdn.net/un357951/article/details/109401723

版权

学习笔记同时被 2 个专栏收录

110 篇文章 2 订阅

订阅专栏

大数据

45 篇文章 0 订阅

订阅专栏

使用表别名

之前在创建计算字段的时候，已经介绍了如何使用别名来引用被检索的表列，如下代码：

SELECT Concat(vend_name, ' ( ', vend_country, ' )') AS vend_title
FROM  Vendors
ORDER BY vend_name;

SQL 除了可以对列名和计算字段使用别名，还可以允许对表名起别名

这样做的理由主要是：

缩短 SQL 的语句
允许在一条 SELECT 语句中多次使用相同的表

如下面的示例代码，与之前讲述的例子语句基本相同，但是改成了使用别名：

SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'ANV01';

可以看到，FROM 子句中的三个表都是用了别名，这样就可以使用省略的 C 而不使用全名 Customers 等等

在这个例子中，表的别名只用于了 WHERE 子句

但是其实别名不仅是能够用于 WHERE 子句，还可以用于 SELECT 的列表，ORDER BY 子句以及其他的语句部分

注意：
并不是所有的 DBMS 都支持使用 AS 作为别名，有些语法可能需要一些改变；但是在 MySQL 中，使用 AS 指定列名

表别名只在查询执行中使用，与列别名不同，表别名不返回到客户端

使用不同类型的联结

之前讲述的联结都是内联结或者等值联结的简单联结
接下来是三种其他的联结：

自联结（self-join）

自然联结（natural join）

外联结（outer join）

自联结（self-join）

如前所述，使用表别名的一个主要原因就是能在一条 SELECT 语句中不止一次引用相同的表

看一个例子，假如要给与 Jim Jones 同一个公司的所有顾客发送一封信件；这个查询要求首先找出 Jim Jones 工作的公司，然后找出在该公司工作的顾客，下面是解决此问题的一种方法：

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (
SELECT cust_name FROM Customers
WHERE cust_contact = 'Jim Jones');

使用子查询，内部的 SELECT 语句做一个简单的搜索，返回 Jim Jones 工作公司的 cust_name
该名字用于外部查询的 WHERE 子句中，以检索出为该公司工作的所有雇员

现在来看使用联结实现相同的查询：

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';

此查询中需要的两个表实际是相同的表，因此 Customers 表在 FROM 子句中出现了两次
虽然这是合法的，但是对 Customers 的引用具有歧义性，因为 DBMS 不知道你引用的是哪个 Customers 表

解决此问题，需要使用表别名

Customers 第一次出现使用了别名 c1，第二次出现使用了别名 c2，现在可以将这些别名用作表名

例如，SELECT 语句使用 c1 前缀明确给出所需列的全名，如果不这样，DBMS 将会返回错误，因为名为 cust_id、cust_name 、cust_contact 的列各有两个；DBMS 并不知道想要的是哪一列（即使它们其实是同一列）

WHERE 首先联结两个表，然后按第二个表中的 cust_contact 过滤数据，返回所需的数据

使用自联结而不是子查询：
自联结通常作为外部语句，用来代替从相同表中检索数据的使用子查询语句
虽然最终的结果是相同的，但是许多 DBMS 处理联结的速度远比处理子查询快得多
实际中可以试验一下，以确定哪一种的性能更好

自然联结（natural join）

无论何时对表进行联结，应该至少有一列不止出现一个表中（被联结的列）

标准的联结（即内联结）返回所有的数据，相同的列甚至多次出现

自然联结排除多次出现，使每一列只返回一次

那么怎么完成这一项工作呢？
系统完不成的工作，由自己完成！

自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT *），而对其他表的列使用明确的子集来完成

如下面的示例代码：

SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'ANV01';

在这个例子中，通配符只对第一个表使用，所有其他列明确列出，所有没有重复的列被检索

事实上，大部分使用的内联结都是自然联结，很多人基本不会用到不是自然联结的内联结

外联结

许多联结将一个表中的行与另一个表中的行相关联，但是有时候需要包含没有关联行的那些行

例如，可能需要使用联结来完成以下工作：

对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客
列出所有产品以及订购数量，包括没有人订购的产品
计算平均销售规模，包括那些至今尚未下订单的顾客

在这个例子中，联结包含了那些在相关表中没有关联行的行，这种联结称为外联结

注意：
用来创建外联结的语法在不同的 SQL 实现中可能稍有不同

首先看一个简单的内联结，它检索所有的顾客及其订单：

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

外联结的语法类似，要检索包括没有订单顾客在内的所有顾客，可以如下进行：

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

这条 SELECT 语句使用了关键字 OUTER JOIN 来指定联结类型（而不是在 WHERE 子句中指定）

但是，与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行

在使用 OUTER JOIN 语法时，必须使用 RIGHT 或者 LEFT 关键字指定包括其所有行的表
（RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN 左边的表）

上面语句中，使用 LEFT OUTER JOIN 从 FROM 子句左边的表（Customers 表）中选择所有行，为了从右边的表中选择所有的行，需要使用 RIGHT OUTER JOIN，如下示例：

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

外联结的类型：

总是有两种基本的外联结形式：
左外联结和右外联结

它们之间的唯一差别是所关联的表的顺序

调整 FROM 或 WHERE 子句中表的顺序，左外联结可以转化为右外联结

还存在一种外联结，就是全外联结（full outer join），它检索两个表中的所有行并关联那些可以关联的行

与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行

语法如下：

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

（MySQL 不支持全外联结）

使用带聚集函数的联结

聚集函数用来汇总数据，聚集函数可以从一个表中汇总数据，也可以与联结一起使用

如下例子中，要检索所有顾客及每个顾客所下的订单数，可以使用 COUNT() 函数来完成：

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;

图10

上述 SELECT 语句使用 INNER JOIN 将 Customers 和 Orders 表互相关联，GROUP BY 子句按顾客分组数据

因此，函数调用 COUNT(Orders.order_num) 对每个顾客的订单计数，将它作为 num_ord 返回

聚集函数也可以方便地与其他联结一起使用：

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