使用表别名

第 7 节介绍了如何使用别名引用被检索的表列，给列起别名的语法如下

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

SQL 除了可以对列名和计算字段使用别名，还允许给表名起别名 ，这样做有两个理由

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

多次使用相同的表？是不是听上去很奇怪？别急，后面会有例子，但是先来尝试一下给表取别名

从上一篇的代码为例

1
2
3
4
5
SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
  AND OrderItems.order_num = Orders.order_num
  AND prod_id = 'RGAN01';

下面给表名取别名

1
2
3
4
5
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 = 'RGAN01';

运行结果和上面是一样的，在这个例子中，别名只用于 WHERE 子句，但其实还可以用在 SELECT 、ORDER BY 以及其他部分。并且表别名只在查询执行中使用，与列别名不同，表别名不返回到客户端

注意：Oracle 中没有 AS

在 Oracle 中使用别名，不需要 AS ，简单地指定列名即可（因此，应该是 Customers C，而不是 Customers AS C

使用不同类型的联结

自联结

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

假如要给与 Jim Jones 同一公司的所有顾客发送一封信件。这个查询需要首先找出 Jim Jones 工作的公司，然后找出在公司工作的顾客，下面是一种解决方法

1
2
3
4
5
SELECT  cust_id, cust_name, cust_contact # 鄙人按：cust_name 是公司名，cust_contact 应该是联系人名
FROM Customers
WHERE cust_name = (SELECT cust_name
                   FROM Customers
                   WHERE cust_contact = 'Jim Jones');

现在来看看使用自联结的写法

1
2
3
4
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';

image-20220623161409611

在此查询中需要的两个表实际上是相同的表，但是是有必要使用别名的，写的时候可以认为是复制出来了两张名字不同的表

自然联结

这部分我看得有点迷，书上讲的跟网上讲的也不一样，而且我感觉这东西没什么用（

1
2
3
4
5
6
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 = 'RGAN01';

外联结

许多联结将表中的行与另一个表中的行相关联，但有时需要包含没有关联的那些行。例如，可能需要使用联结完成以下工作：

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

创建外联结的语法在不同的 SQL 实现中可能稍有不同，下面的语法形式覆盖了大多数实现

先从一个内联结开始，它检索所有顾客及其订单

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

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

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

image-20220623202109152

类似于内联结，外联结使用 OUTER JOIN 来指定联结类型。与内连接关联两个表中的行不同的是，外联结还包含没有关联行的行。在使用 OUTER JOIN 时，必须使用 RIGHT 或者 LEFT 关键字指定那个表应该包含所有行。像这里，想要列出所有的顾客情况，而顾客表在左边，所有使用 LEFT ，又称为左联结，相对地，也有右联结

注意：SQLite 外联结

SQLite 支持 LEFT OUTER JOIN ，但是不支持 LEFT OUTER JOIN ，但这两个联结的不同也就只是顺序的区别，调换一下表的顺序即可

使用带聚集函数的联结

聚集函数用来汇总数据，但至今为止的例子都只是从一个表中汇总数据，其实聚集函数可以与联结一起使用

来看个例子，要检索所有顾客及每个顾客所下的订单数

1
2
3
4
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_order
FROM Customers
 INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

image-20220623205546448

聚集函数还可以与其他联结一起使用

1
2
3
4
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;

image-20220623210031626