多年前收在笔记里的一篇文章，今天无意中找到，重温一遍，还是很有收获的。分享出来，一起讨论。本文专为以下读者而写：1.工作中会用到SQL但又不是很了解的人。2.能熟练使用SQL但不了解其语法逻辑的人。3.想教别人SQL的人。本文重点介绍SELECT语句。完全理解SQL的10个简单步骤。1.SQL是一种声明式语言。首先要牢记这个概念：“声明”。SQL语言是一种范例，用于向计算机声明您希望从原始数据中得到什么结果，而不是告诉计算机如何获得结果。sql执行引擎会根据你声明的数据结果获取相应的数据。SELECTfirst_name,last_nameFROMemployeesWHEREsalary>100000上面的例子很容易理解，我们不关心这些员工记录是从哪里来的，我们只需要工资大于10W的员工。我们在哪里学这个？如果SQL语言如此简单，那么是什么让人“闻到SQL味”呢？主要原因是：在我们的潜意识里，我们是按照命令式编程的思维方式来思考问题的。就像这样：“计算机，先做这个，再做那个，但在此之前检查条件A和条件B是否满足”。比如用变量传递参数，使用循环语句，迭代，调用函数等等，都是这种命令式编程的思维习惯。2、SQL语法不按语法顺序执行SQL语句有一个特点，让大多数人感到困惑，那就是：SQL语句的执行顺序与语句的语法顺序不一致。SQL语句的语法顺序为：SELECT[DISTINCT]FROMWHEREGROUPBYHAVINGUNIONORDERBY为了便于理解，上面没有列出所有的SQL语法结构，但足以说明SQL语句的语法顺序与其执行顺序完全不同，所以上面的语句比如执行顺序是：FROMWHEREGROUPBYHAVINGSELECTDISTINCTUNIONORDERBY关于SQL语句的执行顺序，有三点值得我们注意：1、FROM是SQL语句执行的第一步，不是SELECT。数据库执行SQL语句的第一步是将数据从硬盘加载到数据缓冲区中，以便对数据进行操作。2、SELECT在大部分语句执行完之后执行，严格来说是在FROM和GROUPBY之后执行。理解这一点非常重要，这就是为什么不能使用SELECT中的别名字段作为WHERE中的判断条件的原因。SELECTA.x+A.yASzFROMAWHEREz=10--z这里是没有的，因为SELECT是执行最多的语句！如果您想重复使用别名z，您有两种选择。重写由z表示的表达式：SELECTA.x+A.yASzFROMAWHERE(A.x+A.y)=10或求助于派生表、公共数据表达式或视图以避免别名重用。3.无论语法或执行顺序如何，UNION总是在ORDERBY之前。很多人认为每个UNION段都可以使用ORDERBY排序，但是根据SQL语言标准和各个数据库中SQL实现的差异，并不是这样的。虽然有些数据库允许SQL语句对子查询（subqueries）或派生表（derivedtables）进行排序，但这并不意味着在UNION操作后排序会保持排序后的顺序。注意：并非所有数据库都使用相同的方式解析SQL语句。比如在MySQL、PostgreSQL、SQLite中，就不会按照上面第二点的方式执行。我们学到了什么？由于并非所有数据库都执行如上所述的SQL，我们的收获是什么？我们的要点是永远记住SQL语句的语法顺序与其执行顺序不同，这样我们就可以避免常见的错误。如果你能记住SQL语句的语法顺序和执行顺序之间的区别，你就可以很容易地理解一些非常常见的SQL问题。当然，如果一种语言被设计成语法顺序直接反映其语句的执行顺序，那么这种语言对程序员是非常友好的。这种编程语言级别的设计理念已经被微软应用到LINQ语言中。3.SQL语言的核心是对表的引用（tablereferences）。由于SQL语句的语法顺序和执行顺序的不同，很多同学会认为SELECT中的字段信息才是SQL语句的核心。其实真正的核心在于对表的引用。根据SQL标准，FROM语句定义为：::=FROM[{}...]FROM语句的“输出”是来自所有引用维度上的表联合。我们慢慢分析：FROMa,b上面FROM语句输出的是一张联表，将表a和表b结合在一起。如果a表有3个字段，b表有5个字段，那么这个“输出表”就有8（=5+3）个字段。这个联表中的数据是a*b，是a和b的笛卡尔积。换句话说，a表中的每条数据都必须与b表中的每条数据配对。如果a表有3条数据，b表有5条数据，那么联合表就有15（=5*3）条数据。FROM输出的结果经过WHERE语句过滤后，经过GROUPBY语句处理，形成新的输出结果。如果我们从集合论（关系代数）的角度来看，数据库表就是一组数据元素关系，每条SQL语句都会改变一个或几个关系，从而产生新的数据元素关系（即生成新表）).我们学到了什么？在思考问题的时候，站在表的角度思考问题，这样就很容易理解数据在SQL语句的“管道”上发生了怎样的变化。4、灵活的引用表可以让SQL语句更强大灵活的引用表可以让SQL语句更强大。一个简单的例子是JOIN的使用。严格来说，JOIN语句不是SELECT的一部分，而是一种特殊的表引用语句。SQL语言标准中的表连接定义如下：::= | |就拿前面的例子来说：FROMa,ba可能输入连接表：a1JOINa2ONa1.id=a2.id把这个放到前面的例子中变成：FROMa1JOINa2ONa1.id=a2.id,b虽然用逗号连接一个连接表和另一个表不是常见的做法，但你可以做到.结果，最终输出表有a1+a2+b字段。派生表在SQL语句中的应用比表连接更强大，我们接下来会讲到。我们学到了什么？思考问题的时候，从表引用的角度出发，这样就很容易理解SQL语句是如何处理数据的，也能帮助你理解那些复杂的表引用是干什么的。更重要的是，一定要了解JOIN是构建连接表的关键字，而不是SELECT语句的一部分。有些数据库允许在INSERT、UPDATE、DELETE中加入JOIN。5、建议在SQL语句中使用表连接。再来看看刚才的那句话：FROMa,b。高级SQL程序员可能会给你建议：尽量不要使用逗号代替JOIN进行表连接，这会提高你的SQL语句的性能。可读性好，一些错误是可以避免的。使用逗号来简化SQL语句有时会造成思维混乱。考虑以下语句：FROMa,b,c,d,e,f,g,h WHEREa.a1=b.bx ANDa.a2=c.c1 ANDd.d1=b.bc --etc...我们很容易看出使用JOIN语句的好处：安全。JOIN和要连接的表之间非常接近，这样可以避免错误。对于更多的连接方式，JOIN语句可以区分外连接和内连接。我们学到了什么？切记尽量使用JOIN连接表，千万不要在FROM连接表后使用逗号。6、SQL语句中不同的连接操作在SQL语句中，表的连接方式基本分为五种：EQUIJOINSEMIJOINANTIJOINCROSSJOINDIVISIONEQUIJOIN是最常见的JOIN操作，它包括两种连接方式：INNERJOIN（或JOIN）OUTERJOIN（包括：LEFT,RIGHT，FULLOUTERJOIN）最容易用例子来说明区别：--Thistablereferencecontainsauthorsandtheirbooks。ORthereisan"empty"recordforauthorswithoutbooks--("empty"meaningthatallbookcolumnsareNULL)authorLEFTOUTERJOINbookONauthor.id=book.author_idSEMIJOIN这种连接关系在SQL中有两种表达方式：使用IN或者使用EXISTS。“SEMI”在拉丁语中是“一半”的意思。这种类型的连接是只连接目标表的一部分。这是什么意思？再想想上面关于作者和标题的链接。让我们想象一下我们不需要作者/书名组合的情况，只需要书名表中那些书的作者信息。那么我们可以这样写：--UsingINFROMauthorWHEREauthor.idIN(SELECTbook.author_idFROMbook)--UsingEXISTSFROMauthorWHEREEXISTS(SELECT1FROMbookWHEREbook.author_id=author.id)虽然没有严格规定什么时候该用IN，什么时候该用EXISTS，但是这些你仍然应该知道的事情：IN比EXISTS更具可读性EXISTS比IN更具表现力（对于复杂语句更好）两者之间没有性能差异（但对于某些数据库，性能差异可能非常大）因为INNERJOIN也可以获取title表中书对应的作者信息，很多初学者认为可以用DISTINCT去重，然后SEMIJOIN语句这样写：--FindonlythoseauthorswhoalsohavebooksSELECTDISTINCTfirst_name,last_nameFROMauthorJOINbookONauthor.id=book.author_id是很糟糕的做法写的原因如下：SQL语句性能低：因为去重操作(DISTINCT)需要数据库反复从硬盘读取数据到内存中。这样写也不完全正确：虽然现在这样写可能不是问题，但是随着SQL语句越来越复杂，你去重得到正确的结果会变得非常困难。ANTIJOIN的连接关系与SEMIJOIN正好相反。您可以通过在IN或EXISTS之前添加NOT关键字来使用这种连接。比如我们在title表中列出没有书的作者：--UsingINFROMauthorWHEREauthor.idNOTIN(SELECTbook.author_idFROMbook)--UsingEXISTSFROMauthorWHERENOTEXISTS(SELECT1FROMbookWHEREbook.author_id=author.id)为了性能、可读性、表现力等特性也可以参考SEMIJOIN。CROSSJOIN的连接过程是两个连接表的乘积：即第一个表中的每条数据对应第二个表中的每条数据。我们以前见过，这就是在FROM语句中使用逗号的方式。在实际应用中，很少有地方可以使用CROSSJOIN，但是一旦用到，就可以用这条SQL语句来表达：--CombineeveryauthorwitheverybookauthorCROSSJOINbookDIVISIONDIVISION确实是个怪胎。简而言之，如果JOIN是乘法运算，那么DIVISION就是JOIN的逆运算。DIVISION关系很难在SQL中表达，并且由于这是初学者指南，因此解释DIVISION超出了我们的目的。我们学到了什么？学到了很多！让我们回顾一下。SQL是对表的引用，JOIN是引用表的复杂方式。但是，SQL语言的表达方式和我们实际需要的逻辑关系是有区别的。并不是所有的逻辑关系都能找到对应的JOIN操作，所以我们需要在平时多积累和学习关系逻辑，这样你以后在写SQL语句的时候才能选择合适的JOIN操作。7、派生表就像SQL中的变量在这之前，我们了解到SQL是一种声明式语言，SQL语句中不能包含变量。但是可以写类似变量的语句，这些叫做派生表：说白了，所谓派生表就是括号里的子查询：--AderivedtableFROM(SELECT*FROMauthor)需要注意的是，有时候我们可以给A派生表定义了一个相关名称（我们称之为别名）。--AderivedtablewithanaliasFROM(SELECT*FROMauthor)派生表可以有效避免SQL逻辑带来的问题。例如：如果要复用一个SELECT和WHERE语句查询的结果，可以这样写（以Oracle为例）：--Getauthors'firstandlastnames,andtheirageindaysSELECTfirst_name,last_name,ageFROM(SELECTfirst_name,last_name,current_date-date_of_birthageFROMauthor)--Iftheageisgreaterthan10000daysWHEREage>10000我们需要注意的是：在一些数据库中，以及在SQL:1990标准中，派生表被归类为下一级——公用表表达式（commontableexperssion）。这允许您在一个SELECT语句中多次重复使用派生表。上面的例子（几乎）等价于下面的语句：WITHHaAS(SELECTfirst_name,last_name,current_date-date_of_birthageFROMauthor)SELECT*FROMaWHEREage>10000当然你也可以为“a”创建一个单独的视图，这样你就可以这样导出table被更广泛地重用。我们学到了什么？我们反复强调，一般情况下，SQL语句是引用表，而不是引用字段。要利用这一点，不要害怕使用派生表或其他更复杂的语句。8、SQL语句中的GROUPBY是对表的引用的操作。让我们回忆一下前面的FROM语句：FROMa,b现在，我们将GROUPBY应用于上面的语句：GROUPBYA.x,A.y,B.z上面的语句结果是对包含三个字段的新表的引用。仔细理解这句话：当你应用GROUPBY时，SELECT后不使用聚合函数的列必须出现在GROUPBY之后。（译者注：原文大致意思是“当你使用GROUPBY时，你可以进行下一级逻辑操作的列会减少，包括SELECT中的列”）。注意：其他字段可以使用聚合函数：SELECTA.x,A.y,SUM(A.z)FROMAGROUPBYA.x,A.y还有一点值得注意：MySQL不遵守这个标准，这确实是一个很混乱的地方。（译者注：这并不是说MySQL没有GROUPBY的功能）但是不要被MySQL给骗了。GROUPBY改变了引用表的方式。您既可以在SELECT中引用一个字段，又可以像这样在GROUPBY中对其进行分组。我们学到了什么？GROUPBY，再次对表的引用进行操作，转换成新的引用方式。9、SQL语句中的SELECT本质上是关系的映射。我个人喜欢“映射”这个词，尤其是当它用在关系代数中时。（译者注：原文中使用的词是projection，有两个意思，第一个意思是预测、规划、设计，第二个意思是投影、映射。经过反复推敲，我觉得这里用mapping可以更直观的表达SELECT的作用）。一旦你建立了对表的引用，经过修改和转换，你就可以一步步将它映射到另一个模型。SELECT语句就像一个“投影仪”，我们可以把它理解为一个函数，将源表中的数据按照一定的逻辑转换成目标表中的数据。通过SELECT语句，可以对各个字段进行操作，通过复杂的表达式生成需要的数据。SELECT语句有很多特殊的规则，至少你应该熟悉以下几点：只能使用那些可以通过表引用获取的字段；如果有GROUPBY语句，则只能使用GROUPBY语句函数后的字段或聚合；当语句中没有GROUPBY时，可以使用窗口函数代替聚合函数；当语句中没有GROUPBY时，不能同时使用聚合函数和其他函数；有一些方法可以将普通函数封装在聚合函数中；...一些更复杂的规则足以用于另一篇文章。例如：为什么不能在没有GROUPBY的SELECT语句中将普通函数和聚合函数一起使用？（上面的#4）原因如下：从直觉上讲，这不符合逻辑。如果直觉不能说服你，语法肯定可以。SQL:1999标准引入了GROUPINGSETS，SQL:2003标准引入了组集：GROUPBY()。每当您的语句中出现聚合函数，并且没有明确的GROUPBY语句时，一个不明确的空GROUPINGSET将应用于此SQL。因此，原有的逻辑顺序规则被打破，映射（即SELECT）关系将首先影响逻辑关系，然后才是句法关系。（译者注：这段话原文比较难，可以简单理解为：在同时包含聚合函数和普通函数的SQL语句中，如果没有GROUPBY进行分组，则SQL语句将默认情况下将整个表作为一个组。当聚合函数对某个字段进行聚合统计时，引用表中的每条记录都失去了意义，所有的数据都聚合成一个统计值。你再使用其他函数是没有意义的此时每条记录）。使困惑？我也是。让我们回到更明显的事情上。我们学到了什么？SELECT语句可能是SQL语句中最难的部分，尽管它看起来很简单。其他语句的作用实际上是对表的不同形式的引用。SELECT语句将这些引用整合起来，通过逻辑规则将源表映射到目标表，这个过程是可逆的，所以我们可以清楚的知道目标表中的数据是怎么来的。要想学好SQL语言，在使用SELECT语句之前必须了解其他语句。虽然SELECT是语法结构中的第一个关键字，但它应该是我们掌握的最后一个关键字。10、SQL语句中几个简单的关键字：DISTINCT、UNION、ORDERBY、OFFSET学完了复杂的SELECT，再来看一个简单的：表上的操作。从概念上讲，它们很容易理解：DISTINCT在映射后删除重复数据UNION连接两个子查询并删除重复数据UNIONALL连接两个子查询而不删除重复数据EXCEPT在第二个子查询中删除重复数据Result从第一个子查询中删除INTERSECT，保留两个子查询的结果并删除排序操作：排序操作与逻辑关系无关。这是一个特定于SQL的功能。排序操作不仅在SQL语句开始时执行，而且在SQL语句运行过程中也执行。使用ORDERBY和OFFSET...FETCH是保持数据有序的最有效方法。所有其他排序都有些随机，尽管它们产生可重现的排序。OFFSET...SET是一个没有统一语法的语句。不同的数据库有不同的表达方式，比如MySQL和PostgreSQL中的LIMIT...OFFSET，SQLServer和Sybase中的TOP...STARTAT等。
