简单十步让你全面理解SQL

时间：2022-03-10 17:59

　　很多程序员认为SQL是一头难以驯服的野兽。它是为数不多的声明性语言之一，也因为这样，其展示了完全不同于其他的表现形式、命令式语言、面向对象语言甚至函数式编程语言（虽然有些人觉得SQL 还是有些类似功能）。

　　我每天都写SQL，我的开源软件JOOQ中也包含SQL。因此我觉得有必要为还在为此苦苦挣扎的你呈现SQL的优美！下面的教程面向于：

　　SEMI JOIN（半连接）

　　这种关系的概念在SQL中用两种方式表达：使用IN谓词或使用EXISTS谓语。"Semi"是指在拉丁语中的"half"。这种类型的连接用于连接只有"half"的表引用。再次考虑上述加入的作者和书。让我们想象，我们想要作者/书的组合,但只是那些作者实际上也有书。然后我们可以这样写:

-- 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删除重复项。他们认为他们可以表达一个像这样的半联接：

-- Find only those authors who also have books

SELECT DISTINCT first_name, last_name

FROM author

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

它非常慢，因为该数据库有很多数据加载到内存中，只是要再删除重复项。
它不完全正确，即使在这个简单的示例中它产生了正确的结果。但是，一旦你JOIN更多的表引用，，你将很难从你的结果中正确删除重复项。

　　更多的关于DISTINCT滥用的问题，可以访问这里的。

　　ANTI JOIN（反连接）

　　这个关系的概念跟半连接刚好相反。您可以简单地通过将 NOT 关键字添加到IN 或 EXISTS中生成它。在下例中，我们选择那些没有任何书籍的作者：

-- 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时对NULLs会有一个小警告，这个问题有点超出本教程范围。

　　CROSS JOIN（交叉连接）

　　结合第一个表中的内容和第二个表中的内容，引用两个join表交叉生成一个新的东西。我们之前已经看到，这可以在FROM语句中通过逗号分隔表引用来实现。在你确实需要的情况下，可以在SQL语句中明确地写一个CROSS JOIN。

-- Combine every author with every book

author CROSS JOIN book

　　DIVISION（除法）

　　关系分割就是一只真正的由自己亲自喂养的野兽。简而言之，如果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

　　很明显，对广泛重用的常见SQL子查询，你也可以灌输具体"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语句引用列语法的原因。

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

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

值得注意并很不幸的是，MySQL不遵守这一标准，只会造成混乱。不要陷入MySQL的把戏。GROUP BY转换表引用，因此你可以只引用列也引用GROUPBY语句。

从中我们学到了什么呢？

GROUP BY，在表引用上操作，将它们转换成一个新表。

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

　　当它在关系代数中使用时，我个人比较喜欢用"投影"一词中。一旦你生成你的表引用，过滤，转换它，你可以一步将它投影到另一个表中。SELECT语句就像一个投影机。表函数利用行值表达式将之前构造的表引用的每个记录转化为最终结果。

　　在SELECT语句中，你终于可以在列上操作，创建复杂的列表达式作为记录/行的一部分。

　　有很多关于可用的表达式，函数性质等的特殊规则。最重要的是，你应该记住这些：

　　1、你只能使用从“output”表引用产生的列引用

　　2、如果你有GROUP BY语句，你只可能从该语句或聚合函数引用列

　　3、当你没有GROUP BY语句时，你可以用窗口函数替代聚合函数

　　4、如果你没有GROUP BY语句，你就不能将聚合函数与非聚合函数结合起来

　　5、这有一些关于在聚合函数包装常规函数的规则，反之亦然

　　6、还有…

　　嗯，这有很多复杂的规则。他们可以填补另一个教程。例如，之所以你不能将聚合函数与非聚合函数结合起来投影到没有GROUP BY的SELECT语句中是因为：

　　1、凭直觉，没有任何意义。

　　2、对一个SQL初学者来说，直觉还是毫无帮助的，语法规则则可以。SQL:1999年引入了分组集，SQL：2003引入了空分组集GROUP BY()。每当存在没有显式GROUP BY语句的聚合函数时就会应用隐式的空分组集（规则2）。因此，最初关于逻辑顺序的那个规则就不完全正确了，SELECT的投影也会影响前面的逻辑结果，但语法语句GROUP BY却不受影响。

是不是有点迷糊？其实我也是，让我们看一些简单的吧。

　　从中我们学到了什么呢？

　　在SQL语句中，SELECT语句可能是最复杂的语句之一，即使它看起来是那么的简单。所有其他语句只不过是从这个到另一个表引用的的输送管道。通过将它们完全转化，后期地对它们应用一些规则，SELECT语句完完全全地搅乱了这些表引用的美。

　　为了了解SQL，重要的是要理解其他的一切，都要尝试在SELECT之前解决。即便SELECT在语法顺序中排第一的语句，也应该将它放到最后。

　　10.相对简单一点的SQL DISTINCT,UNION,ORDER BY,和OFFSET

　　看完复杂的SELECT之后，我们看回一些简单的东西。