乐趣区

关于sql:超经典十步完全理解-SQL

jiezi

很多程序员视 SQL 为洪水猛兽。SQL 是一种为数不多的申明性语言，它的运行形式齐全不同于咱们所熟知的命令行语言、面向对象的程序语言、甚至是函数语言（只管有些人认为 SQL 语言也是一种函数式语言）。

咱们每天都在写 SQL 并且利用在开源软件 jOOQ 中。于是我想把 SQL 之美介绍给那些依然对它头疼不已的敌人，所以本文是为了以下读者而顺便编写的：

1、在工作中会用到 SQL 然而对它并不齐全理解的人。

2、可能纯熟应用 SQL 然而并不理解其语法逻辑的人。

3、想要教他人 SQL 的人。

本文着重介绍 SELECT 句式，其余的 DML（Data Manipulation Language 数据操纵语言命令）将会在别的文章中进行介绍。

10 个简略步骤，齐全了解 SQL

1、SQL 是一种申明式语言

首先要把这个概念记在脑中：“申明”。SQL 语言是为计算机申明了一个你想从原始数据中取得什么样的后果的一个范例，而不是通知计算机如何可能失去后果。这是不是很棒？

（译者注：简略地说，SQL 语言申明的是后果集的属性，计算机会依据 SQL 所申明的内容来从数据库中挑选出合乎申明的数据，而不是像传统编程思维去批示计算机如何操作。）

SELECT first_name, last_name FROM employees WHERE salary > 100000

下面的例子很容易了解，咱们不关怀这些雇员记录从哪里来，咱们所须要的只是那些高薪者的数据（译者注：salary>100000）。

咱们从哪儿学习到这些？

如果 SQL 语言这么简略，那么是什么让人们“闻 SQL 色变”？次要的起因是：咱们潜意识中的是依照命令式编程的思维形式思考问题的。就如同这样：“电脑，先执行这一步，再执行那一步，然而在那之前先检查一下是否满足条件 A 和条件 B”。例如，用变量传参、应用循环语句、迭代、调用函数等等，都是这种命令式编程的思维惯式。

2、SQL 的语法并不依照语法程序执行

SQL 语句有一个让大部分人都感到困惑的个性，就是：SQL 语句的执行程序跟其语句的语法程序并不统一。SQL 语句的语法程序是：

  • SELECT[DISTINCT]
  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • UNION
  • ORDER BY

为了不便了解，下面并没有把所有的 SQL 语法结构都列出来，然而曾经足以阐明 SQL 语句的语法程序和其执行程序齐全不一样，就以上述语句为例，其执行程序为：

  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • DISTINCT
  • UNION
  • ORDER BY

对于 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 中设定别名的字段作为判断条件的起因。

SELECT A.x + A.y AS z
FROM A
WHERE z = 10 -- z 在此处不可用，因为 SELECT 是最初执行的语句！

如果你想重用别名 z，你有两个抉择。要么就从新写一遍 z 所代表的表达式：

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 clause> ::= FROM <table reference> [{ <comma> <table reference>}... ]

FROM 语句的“输入”是一张联结表，来自于所有援用的表在某一维度上的联结。咱们们慢慢来剖析：

FROM a, b

下面这句 FROM 语句的输入是一张联结表，联结了表 a 和表 b。如果 a 表有三个字段，b 表有 5 个字段，那么这个“输出表”就有 8（=5+3）个字段。

这个联结表里的数据是 ab，即 a 和 b 的笛卡尔积。换句话说，也就是 a 表中的每一条数据都要跟 b 表中的每一条数据配对。如果 a 表有 3 条数据，b 表有 5 条数据，那么联结表就会有 15（=53）条数据。

FROM 输入的后果被 WHERE 语句筛选后要通过 GROUP BY 语句解决，从而造成新的输入后果。咱们前面还会再探讨这方面问题。

如果咱们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个 SQL 语句会扭转一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。

咱们学到了什么？

思考问题的时候从表的角度来思考问题提，这样很容易了解数据如何在 SQL 语句的“流水线”上进行了什么样的变动。

4、灵便援用表能使 SQL 语句变得更弱小

灵便援用表能使 SQL 语句变得更弱小。一个简略的例子就是 JOIN 的应用。严格的说 JOIN 语句并非是 SELECT 中的一部分，而是一种非凡的表援用语句。SQL 语言规范中表的连贯定义如下：

<table reference> ::=
    <table name>
  | <derived table>
  | <joined table>

就拿之前的例子来说：

FROM a, b

a 可能输出下表的连贯：

a1 JOIN a2 ON a1.id = a2.id

将它放到之前的例子中就变成了：

FROM a1 JOIN a2 ON a1.id = a2.id, b

只管将一个连贯表用逗号跟另一张表联结在一起并不是罕用作法，然而你确实能够这么做。后果就是，最终输入的表就有了 a1+a2+b 个字段了。

（译者注：原文这里用词为 degree，译为维度。如果把一张表视图化，咱们能够设想每一张表都是由横纵两个维度组成的，横向维度即咱们所说的字段或者列，英文为 columns；纵向维度即代表了每条数据，英文为 record，依据上下文，作者这里所指的应该是字段数。）

在 SQL 语句中派生表的援用甚至比表连贯更加弱小，上面咱们就要讲到表连贯。

咱们学到了什么？

思考问题时，要从表援用的角度登程，这样就很容易了解数据是怎么被 SQL 语句解决的，并且可能帮忙你了解那些简单的表援用是做什么的。

更重要的是，要了解 JOIN 是构建连贯表的关键词，并不是 SELECT 语句的一部分。有一些数据库容许在 INSERT、UPDATE、DELETE 中应用 JOIN。

5、SQL 语句中举荐应用表连贯

咱们先看看刚刚这句：

FROM a, b

高级 SQL 程序员兴许学会给你忠告：尽量不要应用逗号来代替 JOIN 进行表的连贯，这样会进步你的 SQL 语句的可读性，并且能够防止一些谬误。

利用逗号来简化 SQL 语句有时候会造成思维上的凌乱，想一下上面的语句：

FROM a, b, c, d, e, f, g, h
WHERE a.a1 = b.bx
AND a.a2 = c.c1
AND d.d1 = b.bc
-- etc...

咱们不难看出应用 JOIN 语句的益处在于：

  • 平安。JOIN 和要连贯的表离得十分近，这样就能防止谬误。
  • 更多连贯的形式，JOIN 语句能去辨别进去外连贯和内连贯等。

咱们学到了什么？

记着要尽量应用 JOIN 进行表的连贯，永远不要在 FROM 前面应用逗号连贯表。

6、SQL 语句中不同的连贯操作

SQL 语句中，表连贯的形式从根本上分为五种：

  • EQUI JOIN
  • SEMI JOIN
  • ANTI JOIN
  • CROSS JOIN
  • DIVISION

EQUI JOIN

这是一种最一般的 JOIN 操作，它蕴含两种连贯形式：

  • INNER JOIN（或者是 JOIN）
  • OUTER JOIN（包含：LEFT、RIGHT、FULL OUTER JOIN）

用例子最容易阐明其中区别：

-- 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

这种连贯关系在 SQL 中有两种体现形式：应用 IN，或者应用 EXISTS。“SEMI”在拉丁文中是“半”的意思。这种连贯形式是只连贯指标表的一部分。这是什么意思呢？再想一下下面对于作者和书名的连贯。咱们设想一下这样的状况：咱们不须要作者 / 书名这样的组合，只是须要那些在书名表中的书的作者信息。那咱们就能这么写：

-- 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，然而这些事件你还是应该晓得的：

  • IN 比 EXISTS 的可读性更好
  • EXISTS 比 IN 的表白性更好（更适宜简单的语句）
  • 二者之间性能没有差别（但对于某些数据库来说性能差别会十分大）

因为应用 INNER JOIN 也能失去书名表中书所对应的作者信息，所以很多初学者机会认为能够通过 DISTINCT 进行去重，而后将 SEMI JOIN 语句写成这样：

-- 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 语句性能低下：因为去重操作（DISTINCT）须要数据库反复从硬盘中读取数据到内存中。（译者注：DISTINCT 确实是一种很消耗资源的操作，然而每种数据库对于 DISTINCT 的操作形式可能不同）。
  • 这么写并非完全正确：只管兴许当初这么写不会呈现问题，然而随着 SQL 语句变得越来越简单，你想要去重失去正确的后果就变得十分困难。

ANTI JOIN

这种连贯的关系跟 SEMI JOIN 刚好相同。在 IN 或者 EXISTS 前加一个 NOT 关键字就能应用这种连贯。举个例子来说，咱们列出书名表里没有书的作者：

-- 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 应该怎么办，因为有一点背离本篇主题，就不具体介绍，有趣味的同学能够读一下

CROSS JOIN

这个连贯过程就是两个连贯的表的乘积：行将第一张表的每一条数据别离对应第二张表的每条数据。咱们之前见过，这就是逗号在 FROM 语句中的用法。在理论的利用中，很少有中央能用到 CROSS JOIN，然而一旦用上了，你就能够用这样的 SQL 语句表白：

-- Combine every author with every book
author CROSS JOIN book

DIVISION

DIVISION 确实是一个怪胎。简而言之，如果 JOIN 是一个乘法运算，那么 DIVISION 就是 JOIN 的逆过程。DIVISION 的关系很难用 SQL 表达出来，介于这是一个老手指南，解释 DIVISION 曾经超出了咱们的目标。

咱们学到了什么？

学到了很多！让咱们在脑海中再回忆一下。SQL 是对表的援用，JOIN 则是一种援用表的简单形式。然而 SQL 语言的表达方式和理论咱们所须要的逻辑关系之间是有区别的，并非所有的逻辑关系都能找到对应的 JOIN 操作，所以这就要咱们在平时多积攒和学习关系逻辑，这样你就能在当前编写 SQL 语句中抉择适当的 JOIN 操作了。

7、SQL 中如同变量的派生表

在这之前，咱们学习到过 SQL 是一种申明性的语言，并且 SQL 语句中不能蕴含变量。然而你能写出相似于变量的语句，这些就叫做派生表：

说白了，所谓的派生表就是在括号之中的子查问：

-- A derived table
FROM (SELECT * FROM author)

须要留神的是有些时候咱们能够给派生表定义一个相干名（即咱们所说的别名）。

-- A derived table with an alias
FROM (SELECT * FROM author) a

派生表能够无效的防止因为 SQL 逻辑而产生的问题。举例来说：如果你想重用一个用 SELECT 和 WHERE 语句查问出的后果，这样写就能够（以 Oracle 为例）：

-- 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 语句中对派生表屡次重用。下面的例子就（简直）等价于上面的语句：

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 语句：

FROM a, b

当初，咱们将 GROUP BY 利用到下面的语句中：

GROUP BY A.x, A.y, B.z

下面语句的后果就是产生出了一个蕴含三个字段的新的表的援用。咱们来认真了解一下这句话：当你利用 GROUP BY 的时候，SELECT 后没有应用聚合函数的列，都要呈现在 GROUP BY 前面。（译者注：原文粗心为“当你是用 GROUP BY 的时候，你可能对其进行下一级逻辑操作的列会缩小，包含在 SELECT 中的列”）。

须要留神的是：其余字段可能应用聚合函数：

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，再次强调一次，是在表的援用上进行了操作，将其转换为一种新的援用形式。

9、SQL 语句中的 SELECT 本质上是对关系的映射

我集体比拟喜爱“映射”这个词，尤其是把它用在关系代数上。（译者注：原文用词为 projection，该词有两层含意，第一种含意是预测、布局、设计，第二种意思是投射、映射，通过反复推敲，我感觉这里用映射可能更直观的表白出 SELECT 的作用）。一旦你建设起来了表的援用，通过批改、变形，你可能一步一步的将其映射到另一个模型中。SELECT 语句就像一个“投影仪”，咱们能够将其了解成一个将源表中的数据依照肯定的逻辑转换成指标表数据的函数。

通过 SELECT 语句，你能对每一个字段进行操作，通过简单的表达式生成所须要的数据。

SELECT 语句有很多非凡的规定，至多你应该相熟以下几条：

  1. 你仅可能应用那些能通过表援用而得来的字段；
  2. 如果你有 GROUP BY 语句，你只可能应用 GROUP BY 语句前面的字段或者聚合函数；
  3. 当你的语句中没有 GROUP BY 的时候，能够应用开窗函数代替聚合函数；
  4. 当你的语句中没有 GROUP BY 的时候，你不能同时应用聚合函数和其它函数；
  5. 有一些办法能够将一般函数封装在聚合函数中；
  6. ……

一些更简单的规定多到足够写出另一篇文章了。比方：为何你不能在一个没有 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 等。

让咱们在工作中纵情的应用 SQL！

正如其余语言一样，想要学好 SQL 语言就要大量的练习。下面的 10 个简略的步骤可能帮忙你对你每天所写的 SQL 语句有更好的了解。另一方面来讲，从平时常见的谬误中也能积攒到很多教训。

起源：水果泡腾片
链接：http://blog.jobbole.com/55086/

退出移动版