大师网-带你快速走向大师之路 解决你在学习过程中的疑惑,带你快速进入大师之门。节省时间,提升效率

数据库学习笔记——13 创建高级联结

1 使用别名

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 = 'RGAN01'
    as.png

    表别名不仅能用于WHERE子句,还可以用于SELECT的列表、ORDER BY子句以及其他语句部分。
    注意:Oracle不支持AS关键字,直接指定别名即可。(例如:orders O)

2 使用不同类型的联结

除了内联结(inner join)之外,现在来看三种其他联结:自联结(self-join)、自然联结(natural join)和外联结(outer join)。

2.1 自联结

使用子查询:
SELECT cust_id,cust_name,cust_contact FROM customers WHERE cust_name = (SELECT cust_name FROM customers WHERE cust_contact = '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'

self_join.jpg

提示:使用自联结而不用子查询
DBMS处理联结远比处理子查询快得多。

2.2 自然联结

标准的联结(内联结)返回所有数据,相同的列甚至多次出现。自然联结排除多次出现,使每一列只返回一次。系统不会完成这项工作,只能自己完成。
自然联结要求只能选择那些唯一的列,一般通过对一个表使用通配符(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 = 'RGAN01';

natural_join.jpg

事实上,我们迄今为止建立的每个内联结都是自然联结,很可能永远都不会用到不是自然联结的内联结。

2.3 外联结

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

先看一个简单的内联结:
SELECT customers.cust_id, orders.order_num FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id;

inner_join.jpg

外联结语法类似:检索包括没有订单顾客在内的所有顾客。
SELECT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id ;

left_outer_join.jpg

与内联结关联两个表中的行不同的是,外联结还包括没有关联行的行。
在使用OUTER JOIN语法时,必须使用RIGHT或LEFT关键字指定包括其所有行的表(RIGHT指出的是OUTER JOIN右边的表,而LEFT指出的是OUTER JOIN左边的表)。
SELECT customers.cust_id,orders.order_num FROM customers RIGHT OUTER JOIN orders ON customers.cust_id = orders.cust_id ;

right_outer_join.jpg

提示:外联结的类型
要记住,总是有两种基本的外联结形式:左外联结和右外联结。它们之间的唯一差别是所关联的表的顺序。换句话说,调整FROM或WHERE子句中表的顺序,左外联结可以转换为右外联结。因此,这两种外联结可以相互使用。

还存在另一种外联结,就是全外联结(full outer join),它检索两个表中的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表的不关联的行不同,全外联结包含两个表的不关联的行。
然而MySQL不支持这个语法,但是可以通过union来实现 :

SELECT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id UNION SELECT customers.cust_id,orders.order_num FROM customers RIGHT OUTER JOIN orders ON customers.cust_id = orders.cust_id ;

union.jpg

3 使用带聚集函数的联结

0556A69C.jpg

例子:检索所有顾客及每个顾客所下的订单数。
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;

聚集_join.jpg

例子:使用左外联结检索包含所有顾客及其对应的订单数(包含没有任何订单的顾客)
SELECT c.cust_id,COUNT(o.order_num) AS num_ord FROM customers c LEFT OUTER JOIN orders o ON c.cust_id = o.cust_id GROUP BY c.cust_id;

聚集函数_外联结.jpg

4 总结

  • 注意所使用的联结类型。一般我们使用内联结,但使用外联结也有效。
  • 注意使用正确的联结语法。
  • 保证使用正确的联结条件。
  • 必须给出联结条件,避免出现笛卡尔积。
  • 在一个联结中可以包含多个表,甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的,一般也很有用,但是应该在一起测试它们前分别测试每个联结。