|
2#
楼主 |
发表于 2018-4-12 16:26:10
|
只看该作者
-- This table reference contains authors and their books
-- There is one record for each book and its author.
-- ... OR there is an "empty" record for authors without books
-- ("empty" meaning that all book columns are NULL)
author LEFT OUTER JOIN book ON author.id = book.author_id
SEMI JOIN
这种连接关系在 SQL 中有两种表现方式:使用 IN,或者使用 EXISTS。“ SEMI ”在拉丁文中是“半”的意思
。这种连接方式是只连接目标表的一部分。这是什么意思呢?再想一下上面关于作者和书名的连接。我们想
象一下这样的情况:我们不需要作者 / 书名这样的组合,只是需要那些在书名表中的书的作者信息。那我们
就能这么写:
[sql] view plain copy
-- Using IN
FROM author
WHERE author.id IN (SELECT book.author_id FROM book)
-- Using EXISTS
FROM author
WHERE EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)
尽管没有严格的规定说明你何时应该使用 IN ,何时应该使用 EXISTS ,但是这些事情你还是应该知道的:
[sql] view plain copy
IN比 EXISTS 的可读性更好
EXISTS 比IN 的表达性更好(更适合复杂的语句)
二者之间性能没有差异(但对于某些数据库来说性能差异会非常大)
因为使用 INNER JOIN 也能得到书名表中书所对应的作者信息,所以很多初学者机会认为可以通过 DISTINCT
进行去重,然后将 SEMI JOIN 语句写成这样:
[sql] view plain copy
-- Find only those authors who also have books
SELECT DISTINCT first_name, last_name
FROM author
JOIN book ON author.id = book.author_id
这是一种很糟糕的写法,原因如下:
[sql] view plain copy
SQL 语句性能低下:因为去重操作( DISTINCT )需要数据库重复从硬盘中读取数据到内存中。(译者注:
DISTINCT 的确是一种很耗费资源的操作,但是每种数据库对于 DISTINCT 的操作方式可能不同)。
这么写并非完全正确:尽管也许现在这么写不会出现问题,但是随着 SQL 语句变得越来越复杂,你想要去重
得到正确的结果就变得十分困难。
更多的关于滥用 DISTINCT 的危害可以参考这篇博文
(http://blog.jooq.org/2013/07/30/ ... e-when-writing-sql/)。
ANTI JOIN
这种连接的关系跟 SEMI JOIN 刚好相反。在 IN 或者 EXISTS 前加一个 NOT 关键字就能使用这种连接。举个
例子来说,我们列出书名表里没有书的作者:
[sql] view plain copy
-- Using IN
FROM author
WHERE author.id NOT IN (SELECT book.author_id FROM book)
-- Using EXISTS
FROM author
WHERE NOT EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)
关于性能、可读性、表达性等特性也完全可以参考 SEMI JOIN。
这篇博文介绍了在使用 NOT IN 时遇到 NULL 应该怎么办,因为有一点背离本篇主题,就不详细介绍,有兴
趣的同学可以读一下
(http://blog.jooq.org/2012/01/27/ ... in-and-null-values/)。
CROSS JOIN
这个连接过程就是两个连接的表的乘积:即将第一张表的每一条数据分别对应第二张表的每条数据。我们之
前见过,这就是逗号在 FROM 语句中的用法。在实际的应用中,很少有地方能用到 CROSS JOIN,但是一旦
用上了,你就可以用这样的 SQL语句表达:
[sql] view plain copy
-- Combine every author with every book
author CROSS JOIN book
DIVISION
DIVISION 的确是一个怪胎。简而言之,如果 JOIN 是一个乘法运算,那么 DIVISION 就是 JOIN 的逆过程。
DIVISION 的关系很难用 SQL 表达出来,介于这是一个新手指南,解释 DIVISION 已经超出了我们的目的。
但是有兴趣的同学还是可以来看看这三篇文章
(http://blog.jooq.org/2012/03/30/ ... l-division-in-jooq/)
(http://en.wikipedia.org/wiki/Relational_algebra#Division)
(https://www.simple-talk.com/sql/ ... elational-division/)。
推荐阅读 →_→ 《画图解释SQL联合语句》
我们学到了什么?
学到了很多!让我们在脑海中再回想一下。 SQL 是对表的引用, JOIN 则是一种引用表的复杂方式。但是
SQL 语言的表达方式和实际我们所需要的逻辑关系之间是有区别的,并非所有的逻辑关系都能找到对应的 J
OIN 操作,所以这就要我们在平时多积累和学习关系逻辑,这样你就能在以后编写 SQL 语句中选择适当的
JOIN 操作了。
7、 SQL 中如同变量的派生表
在这之前,我们学习到过 SQL 是一种声明性的语言,并且 SQL 语句中不能包含变量。但是你能写出类似于
变量的语句,这些就叫做派生表:
说白了,所谓的派生表就是在括号之中的子查询:
[sql] view plain copy
-- A derived table
FROM (SELECT * FROM author)
需要注意的是有些时候我们可以给派生表定义一个相关名(即我们所说的别名)。
[sql] view plain copy
-- A derived table with an alias
FROM (SELECT * FROM author) a
派生表可以有效的避免由于 SQL 逻辑而产生的问题。举例来说:如果你想重用一个用 SELECT 和 WHERE
语句查询出的结果,这样写就可以(以 Oracle 为例):
[sql] view plain copy
-- Get authors' first and last names, and their age in days
SELECT first_name, last_name, age
FROM (
SELECT first_name, last_name, current_date - date_of_birth age
FROM author
)
-- If the age is greater than 10000 days
WHERE age > 10000
需要我们注意的是:在有些数据库,以及 SQL : 1990 标准中,派生表被归为下一级——通用表语句( comm
on table experssion)。这就允许你在一个 SELECT 语句中对派生表多次重用。上面的例子就(几乎)等价于
下面的语句:
[sql] view plain copy
WITH a AS (
SELECT first_name, last_name, current_date - date_of_birth age
FROM author
)
SELECT *
FROM a
WHERE age > 10000
当然了,你也可以给“ a ”创建一个单独的视图,这样你就可以在更广泛的范围内重用这个派生表了。
我们学到了什么?
我们反复强调,大体上来说 SQL 语句就是对表的引用,而并非对字段的引用。要好好利用这一点,不要害怕
使用派生表或者其他更复杂的语句。
8、 SQL 语句中 GROUP BY 是对表的引用进行的操作
让我们再回想一下之前的 FROM 语句:
[java] view plain copy
FROM a, b
现在,我们将 GROUP BY 应用到上面的语句中:
[sql] view plain copy
GROUP BY A.x, A.y, B.z
上面语句的结果就是产生出了一个包含三个字段的新的表的引用。我们来仔细理解一下这句话:当你应用 GRO
UP BY 的时候, SELECT 后没有使用聚合函数的列,都要出现在 GROUP BY 后面。(译者注:原文大意为“当你是
用 GROUP BY 的时候,你能够对其进行下一级逻辑操作的列会减少,包括在 SELECT 中的列”)。
[sql] view plain copy
需要注意的是:其他字段能够使用聚合函数:
SELECT A.x, A.y, SUM(A.z)
FROM A
GROUP BY A.x, A.y
还有一点值得留意的是: MySQL 并不坚持这个标准,这的确是令人很困惑的地方。(译者注:这并不是说 MyS
QL 没有 GROUP BY 的功能)但是不要被 MySQL 所迷惑。 GROUP BY 改变了对表引用的方式。你可以像这样既在
SELECT 中引用某一字段,也在 GROUP BY 中对其进行分组
我们学到了什么?
GROUP BY,再次强调一次,是在表的引用上进行了操作,将其转换为一种新的引用方式。
9、 SQL 语句中的 SELECT 实质上是对关系的映射
我个人比较喜欢“映射”这个词,尤其是把它用在关系代数上。(译者注:原文用词为 projection ,该词有两层含
义,第一种含义是预测、规划、设计,第二种意思是投射、映射,经过反复推敲,我觉得这里用映射能够更直观
的表达出 SELECT 的作用)。一旦你建立起来了表的引用,经过修改、变形,你能够一步一步的将其映射到另一
个模型中。 SELECT 语句就像一个“投影仪”,我们可以将其理解成一个将源表中的数据按照一定的逻辑转换成目
标表数据的函数。
通过 SELECT语句,你能对每一个字段进行操作,通过复杂的表达式生成所需要的数据。
SELECT 语句有很多特殊的规则,至少你应该熟悉以下几条:
你仅能够使用那些能通过表引用而得来的字段;
如果你有 GROUP BY 语句,你只能够使用 GROUP BY 语句后面的字段或者聚合函数;
当你的语句中没有 GROUP BY 的时候,可以使用开窗函数代替聚合函数;
当你的语句中没有 GROUP BY 的时候,你不能同时使用聚合函数和其它函数;
有一些方法可以将普通函数封装在聚合函数中;
一些更复杂的规则多到足够写出另一篇文章了。比如:为何你不能在一个没有 GROUP BY 的 SELECT 语句中同
时使用普通函数和聚合函数?(上面的第 4 条)
原因如下:
凭直觉,这种做法从逻辑上就讲不通。
如果直觉不能够说服你,那么语法肯定能。 SQL : 1999 标准引入了 GROUPING SETS,SQL: 2003 标准引入
了 group sets : GROUP BY() 。无论什么时候,只要你的语句中出现了聚合函数,而且并没有明确的 GROUP BY
语句,这时一个不明确的、空的 GROUPING SET 就会被应用到这段 SQL 中。因此,原始的逻辑顺序的规则就
被打破了,映射(即 SELECT )关系首先会影响到逻辑关系,其次就是语法关系。(译者注:这段话原文就比
较艰涩,可以简单理解如下:在既有聚合函数又有普通函数的 SQL 语句中,如果没有 GROUP BY 进行分组,S
QL 语句默认视整张表为一个分组,当聚合函数对某一字段进行聚合统计的时候,引用的表中的每一条 record
就失去了意义,全部的数据都聚合为一个统计值,你此时对每一条 record 使用其它函数是没有意义的)。
糊涂了?是的,我也是。我们再回过头来看点浅显的东西吧。
我们学到了什么?
SELECT 语句可能是 SQL 语句中最难的部分了,尽管他看上去很简单。其他语句的作用其实就是对表的不同形
式的引用。而 SELECT 语句则把这些引用整合在了一起,通过逻辑规则将源表映射到目标表,而且这个过程是
可逆的,我们可以清楚的知道目标表的数据是怎么来的。
想要学习好 SQL 语言,就要在使用 SELECT 语句之前弄懂其他的语句,虽然 SELECT 是语法结构中的第一个关
键词,但它应该是我们最后一个掌握的。
[sql] view plain copy
集合运算( DISTINCT 和 UNION )
排序运算( ORDER BY,OFFSET…FETCH)
集合运算( set operation):
集合运算主要操作在于集合上,事实上指的就是对表的一种操作。从概念上来说,他们很好理解:
[sql] view plain copy
DISTINCT 在映射之后对数据进行去重
UNION 将两个子查询拼接起来并去重
UNION ALL 将两个子查询拼接起来但不去重
EXCEPT 将第二个字查询中的结果从第一个子查询中去掉
INTERSECT 保留两个子查询中都有的结果并去重
排序运算( ordering operation):
排序运算跟逻辑关系无关。这是一个 SQL 特有的功能。排序运算不仅在 SQL 语句的最后,而且在 SQL 语句运
行的过程中也是最后执行的。使用 ORDER BY 和 OFFSET…FETCH 是保证数据能够按照顺序排列的最有效的
方式。其他所有的排序方式都有一定随机性,尽管它们得到的排序结果是可重现的。
OFFSET…SET是一个没有统一确定语法的语句,不同的数据库有不同的表达方式,如 MySQL 和 PostgreSQL
的 LIMIT…OFFSET、SQL Server 和 Sybase 的 TOP…START AT 等。具体关于 OFFSET..FETCH 的不同语法可
以参考这篇文章 |
|