第十三课 创建高级联结表
#使用表别名的原因
#不同的联结类型以及每类联结所使用的语法
#如何与联结一起使用聚集函数
#使用联结时的注意问题
一、使用表别名
[1]前面(第七课)介绍使用别名引用被检索的表列
[2]SQL除了可以对列名和计算字段使用别名,还允许给表名起别名
给表名起别名的原因:
- 1.缩短SQL语句
- 2.允许在一条SELECT语句中多次使用相同的表
Vendors表
【1】SELECT CONCAT(RTRIM(vend_name),' (',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;
#对列取别名
Customers表
Orders表
OrderItems表
【2】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';
#FROM子句中的三个表都有别名
#customers AS C使用C作为customers表的别名
#表别名不仅能用于WHERE子句,也可用于SELECT列表,ORDER BY子句以及其它语句部分
注意1:
Oracle中没有AS:
Oracle不支持AS关键字,为在Oracle中使用别名,可以不用AS,简单地指定列名即可,如customers C
注意2:
表别名只在查询执行中使用。与列别名不一样,表别名不返回到客户端。
二、不同的联结类型:
[1]迄今为止,我们使用的只是内联结或等值联结的简单联结
[2]三种其他联结:
- 自联结(self-join)
- 自然联结(natural join)
- 外联结(outer join)
1.自联结
#给与Jim Jones同一公司的所有顾客发送一封信件
#此查询要求首先找出Jim Jones工作的公司,然后找出在该公司工作的顾客
方法一:
使用子查询
【3】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
#cust_name该名字用于外部查询的WHERE子句中,以检索出为该公司工作的所有雇员
方法二:
使用联结的相同查询
【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';
#此查询中需要的两个表实际上是相同的表
#customers表在FROM语句中出现两次,为避免引用歧义而使用别名
#SELECT语句使用c1前缀明确给出所需列的全名,否则DBMS将返回错误(DBMS不知道想要的是哪一列)
#WHERE语句首先联结两个表
#然后按第二个表中的cust_contact过滤数据,返回所需的数据。
注意3:
使用自联结而不用子查询
[1]自联结通常作为外部语句,用来替代从相同表中检索数据使用的子查询语句
[2]许多DBMS处理联结远比子查询快得多
2.自然联结
#标准的联结(内联结)返回所有数据,相同的列甚至多次出现
#自然联结排除多次出现,使每一列只返回一次
#自然联结要求只能选择那些唯一的列,一般通过对一个表使用通配符(SELECT *),而对其他表的列使用明确的子集来完成
【5】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';
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';
#此例中通配符只对第一个表使用,所有其他列明确列出
#没有重复的列被检索出
#迄今为止建立的每个内联结都是自然联结,很可能永远都不会用到不是自然联结的内联结
3.外联结
#许多联结将一个表中的行与另一个表中的行相关联,但有时需要包含没有关联行的那些行
#如:对每个顾客下的订单进行计数,包括那些至今尚未下单的顾客
#联结包含了那些在相关表中没有关联行的行,这种联结称为外联结
【6】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 INNER JOIN orders
ON customers.cust_id = orders.cust_id;
#简单的内联结,外联结语法类似
#检索所有顾客及其订单
【7】SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER 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;
#检索包括没有订单顾客在内的所有顾客
#使用关键字OUTER JOIN来指定联结的类型-外联结
#与内联结关联两个表中的行不同的是,外联结还包括没有关联行的行
#使用OUTER JOIN语法时,必须使用RIGHT或LEFT关键字指定包括其所有行的表
#RIGHT指出的是OUTER JOIN右边的表,LEFT指出的是OUTER JOIN左边的表
#上例使用LEFT OUTER JOIN从FROM子句左边的表(Customers表)中选择所有行
#为了从右边的表中选择所有行,需要使用RIGHT OUTER JOIN,如下例
【8】SELECT customers.cust_id, orders.order_num FROM customers RIGHT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
SELECT customers.cust_id, orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
#使用RIGHT OUTER JOIN从FROM子句右边的表(Customers表)中选择所有行
注意4:
SQLite外联结
SQLite支持LEFT OUTER JOIN但不支持RIGHT OUTER JOIN
注意5:
外联结的类型
[1]两种基本的外联结形式:左外联结和右外联结
[2]调整FROM或WHERE子句中表的顺序,左外联结和右外联结可以相互转换
[3]存在另一种联结:全外联结(full outer join)
全外联结:
#检索两个表中所有的行并关联那些可以关联的行
#与左外联结或右外联结包含一个表的不关联的行不同,全外联结包含两个表的不关联的行
【9】SELECT customers.cust_id, orders.order_num FROM orders FULL OUTER JOIN customers ON orders.cust_id = customers.cust_id;
SELECT customers.cust_id, orders.order_num
FROM orders FULL OUTER JOIN customers
ON orders.cust_id = customers.cust_id;
注意6:
FULL OUTER JOIN的支持
Access、MySQL、Open Office Base和SQLite不支持FULL OUTER JOIN语法
三、使用带聚集函数的联结:
#聚集函数用来汇总数据
#聚集函数也可以与联结一起使用
【10】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 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;
#检索所有顾客及每个顾客所下的订单数,使用COUNT()函数
#SELECT语句使用INNER JOIN将Customers和orders表互相关联
#GROUP BY子句按顾客分组数据
#函数调用COUNT(orders.order_num)对每个顾客的订单计数,将它作为num_ord返回
【11】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;
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;
#聚集函数与其他联结一起使用
#使用左外联结来包含所有顾客,甚至包含那些没有任何订单的顾客
#结果中包含顾客100000000002,他有0个订单
四、使用联结和联结条件:
联结及其使用要点:
- 注意所使用的联结类型,一般使用内联结,但使用外联结也有效
- 联结的具体语法,应查阅相应DBMS的具体文档
- 保证使用正确的联结
- 总是提供联结条件,否则会得到笛卡尔积
- 一个联结中可包含多个表,甚至对每个联结采用不同的联结类型。在一起测试它们前分别测试每个联结,可使得排除故障更容易
需要完整代码的小伙伴可关注微信公众号:菜田里守望者
打开微信扫一扫关注吧,你们的支持就是我的动力
参考文献:
【1】《SQL必知必会》第四版 人民邮电出版社 [美] Ben Forta 著 钟鸣 刘晓霞 译