java建立sql7个步骤_全面理解SQL的10个简单步骤

java建立sql7个步骤

太多的程序员认为SQL有点像野兽。 它是少数几种声明性语言之一 ，因此其行为与命令式，面向对象甚至功能性语言完全不同（尽管有些人说SQL在某种程度上也具有功能性 ）。

作为一名SQL培训师（ 请访问我们的培训，这太好了 ！），我每天都在编写SQL，并使用我们公司的开源库jOOQ来拥抱SQL。

因此，我不得不将SQL的美丽带给那些仍在努力挣扎的人们。 以下教程适用于

• 已经使用过SQL但从未完全理解过SQL的读者
• 精通SQL但从未真正考虑过其语法的读者
• 想向他人讲授SQL的读者

本教程将仅关注SELECT语句。 其他DML语句将在另一个教程中介绍。

注意：本教程以前仅在Tech.Pro上发布（ 请在此处查看历史版本 ）。 不幸的是，Tech.Pro脱机了。 经过Tech.Pro的许可，我们将再次在jOOQ博客上重新发布此内容。

这是...

全面理解SQL的10个简单步骤

1. SQL是声明性的

先把这个弄清楚。 声明式的。 您“只是”声明您想要获得的结果的性质的唯一范例。 不是您的计算机应如何计算这些结果。 那不是很好吗？

SELECT first_name, last_name 
FROM employees 
WHERE salary > 100000

容易理解。 您不在乎员工记录的实际来源。 您只想要薪水适中的人。

我们从中学到什么？

那么，如果这是如此简单，那是什么问题呢？ 问题是我们大多数人凭直觉就命令式编程进行思考。 如： “在机器上先执行此操作，然后再执行该操作，但是在执行此操作之前，如果执行此操作，则失败” 。 这包括将临时结果存储在变量，编写循环，迭代，调用函数等中。

算了吧。 考虑如何声明事物。 与如何告诉机器进行计算无关。

2. SQL语法不是“井井有条”的

一个常见的混淆源是一个简单的事实，即SQL语法元素的执行方式没有排序。 词汇顺序为：

• 选择[DISTINCT]
• 从
• 哪里
• 通过...分组
• 拥有
• 联盟
• 订购

为简单起见，未列出所有SQL子句。 这种词法排序从根本上不同于逻辑顺序（取决于优化器的选择，它又可能与执行顺序不同）：

• 从
• 哪里
• 通过...分组
• 拥有
• 选择
• 不同
• 联盟
• 订购

有三件事要注意：

1. FROM是第一个子句，而不是SELECT。 发生的第一件事是将数据从磁盘加载到内存中，以便对此类数据进行操作。
2. SELECT在大多数其他子句之后执行。 最重要的是，在FROM和GROUP BY之后。 当您认为可以引用从WHERE子句的SELECT子句中声明的内容时，理解这一点很重要。 以下是不可能的：

   SELECT A.x + A.y AS z
   FROM A
   WHERE z = 10 -- z is not available here!

   如果要重用z ，则有两个选择。 重复以下表达式：

   SELECT A.x + A.y AS z
   FROM A
   WHERE (A.x + A.y) = 10

   …或者您求助于派生表，公用表表达式或视图，以避免代码重复。 进一步查看示例。

3. UNION在词汇和逻辑顺序上都放在ORDER BY之前。 许多人认为每个UNION子选择都可以排序，但是根据SQL标准和大多数SQL方言，这是不正确的。 尽管某些方言允许对子查询或派生表进行排序，但不能保证在UNION操作之后将保留这种排序

注意，并非所有数据库都以相同的方式实现事物。 例如，规则编号2不适用于上述情况，不适用于MySQL，PostgreSQL和SQLite。

我们从中学到什么？

始终记住SQL子句的词法顺序和逻辑顺序 ，以避免非常常见的错误。 如果您理解这种区别，那么为什么某些事情起作用而其他事情却不起作用将变得非常明显。

当然，如果语言的设计方式能够使词汇顺序实际上反映逻辑顺序 ，那将是很好的，因为它是在Microsoft的LINQ中实现的。

3. SQL是关于表引用的

由于词法顺序和逻辑顺序之间的差异，大多数初学者可能被欺骗，以为列值是SQL中的一等公民。 他们不是。 最重要的是表引用。

SQL标准这样定义FROM子句：

<from clause> ::= 
    FROM <table reference> 
        [ { <comma> <table reference> }... ]

FROM子句的“输出”是所有表引用的组合度的组合表引用。 让我们慢慢地消化一下。

FROM a, b

上述产生的程度的组合表引用a的+程度b 。 如果a具有3列， b具有5列，则“输出表”将具有8（ 3 + 5 ）列。

此组合表参考中包含的记录是axb的叉积/笛卡尔积。 换句话说， a每个记录与b每个记录配对。 如果a有3条记录， b有5条记录，则上述组合表引用将产生15条记录（ 3 x 5 ）。

该“输出”被“馈送” /“输送”到GROUP BY子句中（在WHERE子句中过滤之后），在此它被转换为新的“输出”。 我们稍后会处理。

如果我们从关系代数 / 集合论的角度来看这些事情，那么SQL表就是一个关系或一组元组 。 每个SQL子句将转换一个或几个关系以产生新的关系。

我们从中学到什么？

始终以表引用的方式思考，以了解如何通过SQL子句“流水线化”数据。

4. SQL表引用可能非常强大

表引用功能相当强大。 一个简单的例子就是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的度数。

派生表甚至比联接表更强大。 我们将解决这个问题。

我们从中学到什么？

始终要始终根据表引用进行思考。 这不仅对于理解如何通过SQL子句“管道化”数据非常重要（请参见上一节），而且还有助于您理解如何构造复杂的表引用。

并且，重要的是，要了解JOIN是用于构造联接表的关键字。 不是SELECT语句的一部分。 一些数据库允许在INSERT，UPDATE，DELETE中使用JOIN

5.应使用SQL JOIN表，而不要使用逗号分隔的表

之前，我们已经看过以下子句：

FROM a, b

高级SQL开发人员可能会告诉您，完全不建议使用逗号分隔的列表，并且始终完全表达您的JOIN。 这将帮助您提高SQL语句的可读性，从而防止出错。

一个非常常见的错误是忘记某个位置的JOIN谓词。 考虑以下几点：

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

联接表的语法是

• 更安全，因为可以将联接谓词放置在联接表附近，从而防止出错。
• 更具表现力，因为您可以区分OUTER JOIN，INNER JOIN等。

我们从中学到什么？

始终使用JOIN。 切勿在FROM子句中使用逗号分隔的表引用。

6. SQL的不同JOIN操作

JOIN操作本质上具有五种风味：

• 参加
• 半加入
• 反加入
• 交叉加入
• 师

这些术语通常在关系代数中使用 。 如果上述概念存在，SQL将使用不同的术语。 让我们仔细看看：

参加

这是最常见的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谓词更具表达力（即，表达非常复杂的SEMI JOIN更容易）
• 在性能上没有正式的差异。 但是， 某些数据库可能会有巨大的性能差异 。

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

这是非常不好的做法，原因有两个：

• 这非常慢，因为数据库必须将大量数据加载到内存中，只是要再次删除重复项。
• 即使在此简单示例中得出正确的结果，它也不是完全正确的。 但是，只要您加入更多表引用，就很难正确地从结果中删除重复项。

有关滥用DISTINCT的更多信息，请参见此博客文章 。

反加入

这种关系概念与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)

关于性能，可读性和表达性的相同规则适用。 但是，在使用NOT IN时，对于NULL有一个小警告， 这对于本教程来说有点超出范围 。

交叉加入

这将产生两个联接的表引用的叉积，将第一个表引用的每个记录与第二个表引用的每个记录进行组合。 之前我们已经看到，这可以通过FROM子句中用逗号分隔的表引用来实现。 在真正需要这样做的极少数情况下，您还可以在大多数SQL方言中显式编写CROSS JOIN：

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

师

关系部门实际上是它自己的野兽。 简而言之，如果JOIN是乘法，则除法是JOIN的逆。 关系划分很难用SQL表示。 由于这是初学者的教程，因此无法对其进行解释。 对于你们中间的勇者， 请在这里 ， 这里 和这里继续阅读 。

我们从中学到什么？

很多。 再次，让我们把它锤入脑海。 SQL与表引用有关。 联接表是非常复杂的表引用。 但是在关系型和SQL型方面有所不同。 并非所有的关系联接操作也是正式SQL联接操作。 借助一些有关关系理论的实践和知识，您将始终能够选择正确的关系JOIN类型，并将其转换为正确SQL。

7. SQL的派生表就像表变量

之前，我们已经了解到SQL是一种声明性语言 ，因此，变量没有位置（但是在某些SQL方言中却没有）。 但是您可以编写类似变量的内容。 这些野兽被称为派生表。

派生表不过是用括号括起来的子查询。

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

请注意，某些SQL方言要求派生表具有相关名 （也称为别名）。

-- 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：1999标准已将派生表提高到了新的水平，引入了常见的表表达式 。 这将使您可以在单个SQL 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子选择。 在此处阅读有关视图的更多信息 。

我们从中学到什么？

一次又一次。 SQL主要是关于表引用，而不是列。 利用它们。 不要害怕编写派生表或其他复杂的表引用。

8. SQL GROUP BY转换先前的表引用

让我们重新考虑之前的FROM子句：

FROM a, b

现在，让我们将GROUP BY子句应用于上述组合表引用

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

上面的代码产生了一个新的表引用，仅剩下三列（！）。 让我们再消化一次。 如果应用GROUP BY，则可以减少所有后续逻辑子句（包括SELECT）中可用列的数量。 这是语法上的原因，为什么您只能在SELECT子句中引用GROUP BY子句中的列。

• 请注意，其他列可能仍可用作聚合函数的参数：

  SELECT A.x, A.y, SUM(A.z)
  FROM A
  GROUP BY A.x, A.y

• 请注意，不幸的是，MySQL不遵守该标准 ，只会造成混乱。 不要迷恋MySQL的花招。 GROUP BY转换表引用。 因此，您只能引用在GROUP BY子句中也引用的列。

我们从中学到什么？

同样，GROUP BY对表引用进行操作，将它们转换为新形式。

9. SQL SELECT在关系代数中称为投影

我个人喜欢“投影”一词，因为它是关系代数中使用的。 生成表引用，对其进行过滤，对其进行转换之后，就可以将其投影到另一个表单。 SELECT子句就像一个投影仪。 表函数利用行值表达式将每个记录从先前构造的表引用转换为最终结果。

在SELECT子句中，您最终可以对列进行操作，创建复杂的列表达式作为记录/行的一部分。

关于可用表达式，函数等的性质，有很多特殊规则。最重要的是，您应该记住以下几点：

1. 您只能使用可以从“输出”表引用中生成的列引用
2. 如果您有GROUP BY子句，则只能引用该子句中的列或聚合函数。
3. 如果没有GROUP BY子句，则可以使用窗口函数而不是聚合函数。
4. 如果没有GROUP BY子句，则不得将集合函数与非集合函数组合在一起。
5. 关于将常规函数包装在聚合函数中有一些规则，反之亦然。
6. 有 …

好吧，有很多复杂的规则。 他们可以填写另一个教程。 例如，为什么在没有GROUP BY子句（规则编号4）的SELECT语句中无法将集合函数与非集合函数组合在一起的原因是：

1. 这没有道理。 凭直觉。
2. 如果直觉没有帮助（对于SQL初学者来说几乎没有帮助），则语法规则会有所帮助。 SQL：1999引入了GROUPING SETS，而SQL：2003引入了空分组集：GROUP BY（）。 只要存在聚合函数，并且没有显式的GROUP BY子句，就会应用隐式的空GROUPING SET（规则2）。 因此，关于逻辑排序的原始规则不再完全正确，并且投影（SELECT）影响逻辑上先行但词汇上连续的子句（GROUP BY）的结果。

困惑？ 是。 我也是。 让我们回到简单的事情上。

我们从中学到什么？

SELECT子句可能看起来很简单，但它可能是SQL中最复杂的子句之一。 所有其他子句只是“管道”表引用之间的引用。 SELECT子句通过完全转换表引用，追溯性地应用一些规则，使这些表引用的美感混乱。

为了理解SQL，在尝试处理SELECT之前，首先了解其他所有内容很重要。 即使SELECT是词法顺序中的第一个子句，它也应该是最后一个子句。

10. SQL DISTINCT，UNION，ORDER BY和OFFSET再次简单

完成复杂的SELECT之后，我们可以再次回到简单的事情：

• 设置操作（DISTINCT和UNION）
• 订购操作（ORDER BY，OFFSET .. FETCH）

设定操作

集合操作对“集合”进行操作，实际上，这些集合不过是……表。 好吧，差不多。 从概念上讲，它们很容易理解。

• DISTINCT删除投影后的重复项。
• UNION连接两个子选择并删除重复项
• UNION ALL连接两个保留重复项的子选择
• EXCEPT从第一个子选择中删除记录，该记录也包含在第二个子选择中（然后删除重复记录）
• INTERSECT仅保留两个子选择中包含的记录（然后删除重复项）

所有这些删除重复项通常都是胡说八道。 通常，当您要串联子选择时，应该只使用UNION ALL。

订购操作

排序不是关系功能。 这是仅SQL的功能。 它在SQL语句的词法排序和逻辑排序的最后应用。 使用ORDER BY和OFFSET .. FETCH是唯一可以确保索引可以可靠地访问记录的方法。 所有其他排序始终是任意且随机的，即使它看起来是可重复的。

OFFSET .. FETCH只是一种语法变体。 其他变体包括MySQL和PostgreSQLLIMIT .. OFFSET或SQL Server和Sybase的TOP .. START AT。 可以在此处看到实现OFFSET..FETCH的各种方法的良好概述。

上班吧

与每种语言一样，SQL需要进行大量练习。 上面的10个简单步骤将帮助您更好地理解每天编写SQL。 另一方面，从常见错误中学习也很有益。 以下两篇文章列出了Java（和其他）开发人员在编写SQL时犯的许多常见错误：

• Java开发人员在编写SQL时常犯的10个错误
• Java开发人员在编写SQL时犯的10个常见错误
• Java开发人员在编写SQL时再犯的10个常见错误

翻译自: https://www.javacodegeeks.com/2016/03/10-easy-steps-complete-understanding-sql.html

java建立sql7个步骤