SQL必知必会—创建高级联结

49人阅读 评论(0) 收藏 举报
分类:

《SQL必知必会》读书笔记

本课讲解另外一些联结(包括它们的含义和使用方法),介绍如何是以表别名,如何对被联结的表使用聚集函数。

1.使用表别名

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

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

请看下面的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';

注意:Oracle中没有AS

Oracle不支持AS关键字。要在Oracle中使用别名,可以不用AS,简单地指定列名即可(因此,应该是Customers C,而不是Customers AS C)。

需要注意,表列名只在查询执行中使用。与列别名不一样,表别名不返回到客户端。

2.自联结

联结类型:内联结、自联结、自然联结、外联结

查询要求:首先找出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 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表。解决此问题,需要使用别名表。

提示:用自联结而不用子查询

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

3.自然联结

标准的联结(前一课中介绍的内联结)返回所有数据,相同的列甚至多次出现。自然联结排除多次出现,使每一列只返回一次。

怎样完成这项工作呢? 自然联结要求你只能选择那些唯一的列,一般通过对一个表使用通配符(SELECT *),而对其他表的列使用明确的子集来完成。

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

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

4.外联结

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

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

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

下面的SELECT语句给出一个简单的内联结。它检索所有顾客及其订单:

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;

注意:SQLite外联结

SQLite支持LEFT OUTER JOIN,但不支持RIGHT OUTER JOIN。

提示:外联结的类型

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

还存在另一种外联结,就是全外联结,它检索两个表中的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表的不关联的行不同,全外联结包含两个表的不关联的行。全外联结的语法如下:

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

注意:FULL OUTER JOIN的支持

Access、MariaDB、MySQL、Open Office Base或SQLite不支持FULL OUTER JOIN语法。

5.使用带聚集函数的联结

要检索所有顾客及每个顾客所下的订单数,下面代码使用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


cust_id         num_ord
10000001        2
10000003        1
10000004        1
10000005        1

聚集函数也可以方便地与其他联结一起使用。请看下面的例子:

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

cust_id     num_ord
10000001    2
10000002    0
10000003    1
10000004    1
10000005    1

这个例子使用左外部联结来包含所有顾客,甚至包含那些没有任何订单的顾客。结果中也包含了顾客10000002,他有0各订单。

6.使用联结和联结条件

汇总一下联结及其使用的要点:

  • 注意所使用的联结类型。一般我们使用内联结,但使用外联结也有效。
  • 关于确切的联结语法,应该查看具体的文档,看相应的DBMS支持何种语法。
  • 保证使用正确的联结条件,否则会返回不正确的数据。
  • 应该总是提供联结条件,否则会得出笛卡尔积。
  • 在一个联结中可以包含多个表,甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的,一般也很有用,但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单。
查看评论

SQL必知必会 第4版pdf

下载地址:网盘下载 内容简介  · · · · · · SQL语法简洁,使用方式灵活,功能强大,已经成为当今程序员不可或缺的技能。 本书是深受世界...
  • cf406061841
  • cf406061841
  • 2017年06月03日 23:18
  • 7239

sql必知必会(第四版) 学习笔记一

温习一遍简单的sql语法,把自己掌握还不够的地方,做了些笔记.... 1 去重复关键词,distinct     select distinct sname from student;   2...
  • Dreamworker007
  • Dreamworker007
  • 2013年10月26日 23:02
  • 8383

SQL必知必会(5)——汇总数据

目的:我们经常需要汇总数据而不是把它们实际检索出来,例如确定表的行数,某些列的总和,某些列的最值等。此时检索所有数据只能是浪费时间和系统资源。 第一部分:SQL给出了5个聚集函数,聚集函数是指对...
  • ddd_1206
  • ddd_1206
  • 2017年08月23日 13:37
  • 221

SQL必知必会

1 不能部分使用distinct,distinct作用于所有的列。 2 多用 in,not,少用,and or 加()的组合,因为前者的语义更明确一切。 3...
  • lllliulin
  • lllliulin
  • 2015年11月02日 16:55
  • 809

SQL必知必会学习笔记大总结

第一课:了解SQL 数据库:保存有组织的数据的容器(通常是一个文件或一组文件)。注意误用混淆:数据库软件被称为DBMS,数据库是通过DBMS创建和操纵的容器模式:关于数据库和表的布局及特性的信...
  • basycia
  • basycia
  • 2016年09月11日 11:31
  • 2752

《SQL必知必会》学习笔记(一):实验数据初始化

摘要:本文主要提供《SQL必知必会》一书中实验所用的数据库,以及创建数据库、表、约束和初始化的SQL语句。...
  • zwxtfqd
  • zwxtfqd
  • 2015年11月18日 16:00
  • 2627

SQL必知必会第4版读书笔记

SQL必知必会_4前言@author 鲁伟林 在读电子版<<SQL必知必会>> 第4版时,做了下笔记。供以后自己或者其他学习者参考。 电子版&a...
  • thinking_fioa
  • thinking_fioa
  • 2017年10月17日 20:21
  • 11445

sql必知必会

主键: 表中任何列都可以作为主键,但必须满足:任何两行不能有相同的主键 每一行都必须有主键,即主键值不能是null 主键的值不能修改 主键的值不能重用,即某行被删除了,他的主键值也不能再给其他行用。...
  • baiyun8789
  • baiyun8789
  • 2016年05月22日 00:02
  • 881

《SQL必知必会》(1-7)

《SQL必知必会》读书笔记 说明:本书SQL语句均使用PLSQL Developer客户端,在oracle数据库中实践。 1. 基本概念 表(table):某种特定类型数据的结构化清单。 模式...
  • Regina_niu
  • Regina_niu
  • 2016年12月21日 09:33
  • 720

《SQL必知必会》学习笔记

基本常识去掉返回结果空格两边都去掉:TRIM去掉左边:LTRIM去掉右边:RTRIMUnion默认,Union从查询结果集中自动去除重复的行,如果想返回所有匹配的行,用Union ALL。如果需求是后...
  • BlackEnn
  • BlackEnn
  • 2017年04月23日 17:37
  • 440
    个人资料
    专栏达人 持之以恒
    等级:
    访问量: 4万+
    积分: 1475
    排名: 3万+
    博客专栏
    最新评论