关于 SQL 语句的执行顺序,有三个值得我们注意的地方:
1、 FROM 才是 SQL 语句执行的第一步,并非 SELECT 。数据库在执行 SQL 语句的第一步是将数据从硬盘加载到数据缓冲区中,以便对这些数据进行操作。(译者注:原文为“The first thing that happens is loading data from the disk into memory, in order to operate on such data.”,但是并非如此,以 Oracle 等常用数据库为例,数据是从硬盘中抽取到数据缓冲区中进行操作。)
2、 SELECT 是在大部分语句执行了之后才执行的,严格的说是在 FROM 和 GROUP BY 之后执行的。理解这一点是非常重要的,这就是你不能在 WHERE 中使用在 SELECT 中设定别名的字段作为判断条件的原因。
1
2
3
SELECT A.x + A.y AS z
FROM A
WHERE z = 10 -- z 在此处不可用,因为SELECT是最后执行的语句!
如果你想重用别名z,你有两个选择。要么就重新写一遍 z 所代表的表达式:
1
2
3
SELECT A.x + A.y AS z
FROM A
WHERE (A.x + A.y) = 10
…或者求助于衍生表、通用数据表达式或者视图,以避免别名重用。请看下文中的例子。
3、 无论在语法上还是在执行顺序上, UNION 总是排在在 ORDER BY 之前。很多人认为每个 UNION 段都能使用 ORDER BY 排序,但是根据 SQL 语言标准和各个数据库 SQL 的执行差异来看,这并不是真的。尽管某些数据库允许 SQL 语句对子查询(subqueries)或者派生表(derived tables)进行排序,但是这并不说明这个排序在 UNION 操作过后仍保持排序后的顺序。
注意:并非所有的数据库对 SQL 语句使用相同的解析方式。如 MySQL、PostgreSQL和 SQLite 中就不会按照上面第二点中所说的方式执行。 我们学到了什么?
既然并不是所有的数据库都按照上述方式执行 SQL 预计,那我们的收获是什么?我们的收获是永远要记得: SQL 语句的语法顺序和其执行顺序并不一致,这样我们就能避免一般性的错误。如果你能记住 SQL 语句语法顺序和执行顺序的差异,你就能很容易的理解一些很常见的 SQL 问题。
当然,如果一种语言被设计成语法顺序直接反应其语句的执行顺序,那么这种语言对程序员是十分友好的,这种编程语言层面的设计理念已经被微软应用到了 LINQ 语言中。 3、 SQL 语言的核心是对表的引用(table references)
由于 SQL 语句语法顺序和执行顺序的不同,很多同学会认为SELECT 中的字段信息是 SQL 语句的核心。其实真正的核心在于对表的引用。
根据 SQL 标准,FROM 语句被定义为:
上面这句 FROM 语句的输出是一张联合表,联合了表 a 和表 b 。如果 a 表有三个字段, b 表有 5 个字段,那么这个“输出表”就有 8 ( =5+3)个字段。
这个联合表里的数据是 a*b,即 a 和 b 的笛卡尔积。换句话说,也就是 a 表中的每一条数据都要跟 b 表中的每一条数据配对。如果 a 表有3 条数据, b 表有 5 条数据,那么联合表就会有 15 ( =5*3)条数据。
FROM 输出的结果被 WHERE 语句筛选后要经过 GROUP BY 语句处理,从而形成新的输出结果。我们后面还会再讨论这方面问题。
如果我们从集合论(关系代数)的角度来看,一张数据库的表就是一组数据元的关系,而每个 SQL 语句会改变一种或数种关系,从而产生出新的数据元的关系(即产生新的表)。 我们学到了什么?
思考问题的时候从表的角度来思考问题提,这样很容易理解数据如何在 SQL 语句的“流水线”上进行了什么样的变动。 4、 灵活引用表能使 SQL 语句变得更强大
灵活引用表能使 SQL 语句变得更强大。一个简单的例子就是 JOIN 的使用。严格的说 JOIN 语句并非是 SELECT 中的一部分,而是一种特殊的表引用语句。 SQL 语言标准中表的连接定义如下:
-- This table reference contains authors and their books.
-- There is one record for each book and its author.
-- authors without books are NOT included
author JOIN book ON author.id = book.author_id
-- 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
-- A derived table with an alias
FROM (SELECT * FROM author) a
派生表可以有效的避免由于 SQL 逻辑而产生的问题。举例来说:如果你想重用一个用 SELECT 和 WHERE 语句查询出的结果,这样写就可以(以 Oracle 为例):
1
2
3
4
5
6
7
8
-- 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 标准中,派生表被归为下一级——通用表语句( common table experssion)。这就允许你在一个 SELECT 语句中对派生表多次重用。上面的例子就(几乎)等价于下面的语句:
1
2
3
4
5
6
7
WITH a AS (
SELECT first_name, last_name, current_date - date_of_birth age
FROM author
)
SELECT *
FROM a
WHERE age > 10000
当然了,你也可以给“ a ”创建一个单独的视图,这样你就可以在更广泛的范围内重用这个派生表了。更多信息可以阅读下面的文章(http://en.wikipedia.org/wiki/View_%28SQL%29)。 我们学到了什么?
我们反复强调,大体上来说 SQL 语句就是对表的引用,而并非对字段的引用。要好好利用这一点,不要害怕使用派生表或者其他更复杂的语句。 8、 SQL 语句中 GROUP BY 是对表的引用进行的操作
让我们再回想一下之前的 FROM 语句:
1
FROM a, b
现在,我们将 GROUP BY 应用到上面的语句中:
1
GROUP BY A.x, A.y, B.z
上面语句的结果就是产生出了一个包含三个字段的新的表的引用。我们来仔细理解一下这句话:当你应用 GROUP BY 的时候, SELECT 后没有使用聚合函数的列,都要出现在 GROUP BY 后面。(译者注:原文大意为“当你是用 GROUP BY 的时候,你能够对其进行下一级逻辑操作的列会减少,包括在 SELECT 中的列”)。
需要注意的是:其他字段能够使用聚合函数:
1
2
3
SELECT A.x, A.y, SUM(A.z)
FROM A
GROUP BY A.x, A.y
还有一点值得留意的是: MySQL 并不坚持这个标准,这的确是令人很困惑的地方。(译者注:这并不是说 MySQL 没有 GROUP BY 的功能)但是不要被 MySQL 所迷惑。 GROUP BY 改变了对表引用的方式。你可以像这样既在 SELECT 中引用某一字段,也在 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 进行分组,SQL 语句默认视整张表为一个分组,当聚合函数对某一字段进行聚合统计的时候,引用的表中的每一条 record 就失去了意义,全部的数据都聚合为一个统计值,你此时对每一条 record 使用其它函数是没有意义的)。
糊涂了?是的,我也是。我们再回过头来看点浅显的东西吧。 我们学到了什么?
SELECT 语句可能是 SQL 语句中最难的部分了,尽管他看上去很简单。其他语句的作用其实就是对表的不同形式的引用。而 SELECT 语句则把这些引用整合在了一起,通过逻辑规则将源表映射到目标表,而且这个过程是可逆的,我们可以清楚的知道目标表的数据是怎么来的。
想要学习好 SQL 语言,就要在使用 SELECT 语句之前弄懂其他的语句,虽然 SELECT 是语法结构中的第一个关键词,但它应该是我们最后一个掌握的。 10、 SQL 语句中的几个简单的关键词: DISTINCT , UNION , ORDER BY 和 OFFSET
在学习完复杂的 SELECT 豫剧之后,我们再来看点简单的东西:
集合运算( DISTINCT 和 UNION )
排序运算( ORDER BY,OFFSET…FETCH)
集合运算( set operation):
集合运算主要操作在于集合上,事实上指的就是对表的一种操作。从概念上来说,他们很好理解:
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 的不同语法可以参考这篇文章
(http://www.jooq.org/doc/3.1/manual/sql-building/sql-statements/select-statement/limit-clause/)。 让我们在工作中尽情的使用 SQL!
正如其他语言一样,想要学好 SQL 语言就要大量的练习。上面的 10 个简单的步骤能够帮助你对你每天所写的 SQL 语句有更好的理解。另一方面来讲,从平时常见的错误中也能积累到很多经验。下面的两篇文章就是介绍一些 JAVA 和其他开发者所犯的一些常见的 SQL 错误:
10 Common Mistakes Java Developers Make when Writing SQL
10 More Common Mistakes Java Developers Make when Writing SQL