兰州门户网站建设,汕头百度seo在哪里,网站和网络建设调研情况,债务优化是什么意思- 点击上方“中国统计网”设置⭐星标不迷路#xff01;-很多程序员视 SQL 为洪水猛兽。SQL 是一种为数不多的声明性语言#xff0c;它的运行方式完全不同于我们所熟知的命令行语言、面向对象的程序语言、甚至是函数语言(尽管有些人认为 SQL 语言也是一种函数式语言)。我们每天… - 点击上方“中国统计网”设置⭐星标不迷路-很多程序员视 SQL 为洪水猛兽。SQL 是一种为数不多的声明性语言它的运行方式完全不同于我们所熟知的命令行语言、面向对象的程序语言、甚至是函数语言(尽管有些人认为 SQL 语言也是一种函数式语言)。我们每天都在写 SQL 并且应用在开源软件 jOOQ 中。于是我想把 SQL 之美介绍给那些仍然对它头疼不已的朋友所以本文是为了以下读者而特地编写的在工作中会用到 SQL 但是对它并不完全了解的人能够熟练使用 SQL 但是并不了解其语法逻辑的人想要教别人 SQL 的人本文着重介绍 SELECT 句式其他的 DML (Data Manipulation Language 数据操纵语言命令)将会在别的文章中进行介绍。0110个简单步骤完全理解SQL1SQL 是一种声明式语言首先要把这个概念记在脑中“声明”。SQL 语言是为计算机声明了一个你想从原始数据中获得什么样的结果的一个范例而不是告诉计算机如何能够得到结果。这是不是很棒(译者注简单地说SQL 语言声明的是结果集的属性计算机会根据 SQL 所声明的内容来从数据库中挑选出符合声明的数据而不是像传统编程思维去指示计算机如何操作。)SELECT first_name, last_name FROM employees WHERE salary 100000上面的例子很容易理解我们不关心这些雇员记录从哪里来我们所需要的只是那些高薪者的数据(译者注salary100000 )。 我们从哪儿学习到这些如果 SQL 语言这么简单那么是什么让人们“闻 SQL 色变”主要的原因是我们潜意识中的是按照命令式编程的思维方式思考问题的。就好像这样“电脑先执行这一步再执行那一步但是在那之前先检查一下是否满足条件 A 和条件 B ”。例如用变量传参、使用循环语句、迭代、调用函数等等都是这种命令式编程的思维惯式。2SQL 的语法并不按照语法顺序执行SQL 语句有一个让大部分人都感到困惑的特性就是SQL 语句的执行顺序跟其语句的语法顺序并不一致。SQL 语句的语法顺序是SELECT[DISTINCT]FROMWHEREGROUP BYHAVINGUNIONORDER BY为了方便理解上面并没有把所有的 SQL 语法结构都列出来但是已经足以说明 SQL 语句的语法顺序和其执行顺序完全不一样就以上述语句为例其执行顺序为FROMWHEREGROUP BYHAVINGSELECTDISTINCTUNIONORDER 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 zFROM AWHERE z 10 -- z 在此处不可用因为SELECT是最后执行的语句如果你想重用别名z你有两个选择。要么就重新写一遍 z 所代表的表达式SELECT A.x A.y AS zFROM AWHERE (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 语言的核心是对表的引用由于 SQL 语句语法顺序和执行顺序的不同很多同学会认为SELECT 中的字段信息是 SQL 语句的核心。其实真正的核心在于对表的引用。根据 SQL 标准FROM 语句被定义为from clause :: FROM table reference [ { comma table reference }... ]FROM 语句的“输出”是一张联合表来自于所有引用的表在某一维度上的联合。我们们慢慢来分析FROM a, b上面这句 FROM 语句的输出是一张联合表联合了表 a 和表 b 。如果 a 表有三个字段 b 表有 5 个字段那么这个“输出表”就有 8 ( 53)个字段。这个联合表里的数据是 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 语言标准中表的连接定义如下table reference :: table name | derived table | joined table就拿之前的例子来说FROM a, ba 可能输入下表的连接a1 JOIN a2 ON a1.id a2.id将它放到之前的例子中就变成了FROM a1 JOIN a2 ON a1.id a2.id, b尽管将一个连接表用逗号跟另一张表联合在一起并不是常用作法但是你的确可以这么做。结果就是最终输出的表就有了 a1a2b 个字段了。(译者注原文这里用词为 degree 译为维度。如果把一张表视图化我们可以想象每一张表都是由横纵两个维度组成的横向维度即我们所说的字段或者列英文为columns纵向维度即代表了每条数据英文为 record 根据上下文作者这里所指的应该是字段数。)在SQL语句中派生表的引用甚至比表连接更加强大下面我们就要讲到表连接。 我们学到了什么思考问题时要从表引用的角度出发这样就很容易理解数据是怎样被 SQL 语句处理的并且能够帮助你理解那些复杂的表引用是做什么的。更重要的是要理解 JOIN 是构建连接表的关键词并不是 SELECT 语句的一部分。有一些数据库允许在 INSERT 、 UPDATE 、 DELETE 中使用 JOIN 。5SQL 语句中推荐使用表连接我们先看看刚刚这句FROM a, b高级 SQL 程序员也许学会给你忠告尽量不要使用逗号来代替 JOIN 进行表的连接这样会提高你的 SQL 语句的可读性并且可以避免一些错误。利用逗号来简化 SQL 语句有时候会造成思维上的混乱想一下下面的语句FROM a, b, c, d, e, f, g, hWHERE a.a1 b.bxAND a.a2 c.c1AND d.d1 b.bc-- etc...我们不难看出使用 JOIN 语句的好处在于安全。JOIN 和要连接的表离得非常近这样就能避免错误更多连接的方式JOIN 语句能去区分出来外连接和内连接等 我们学到了什么记着要尽量使用 JOIN 进行表的连接永远不要在 FROM 后面使用逗号连接表。6简约框线标题SQL 语句中表连接的方式从根本上分为五种EQUI JOINSEMI JOINANTI JOINCROSS JOINDIVISION 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 includedauthor 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 INFROM authorWHERE author.id IN (SELECT book.author_id FROM book)-- Using EXISTSFROM authorWHERE 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 booksSELECT DISTINCT first_name, last_nameFROM authorJOIN book ON author.id book.author_id这是一种很糟糕的写法原因如下SQL 语句性能低下因为去重操作( DISTINCT )需要数据库重复从硬盘中读取数据到内存中。(译者注DISTINCT 的确是一种很耗费资源的操作但是每种数据库对于 DISTINCT 的操作方式可能不同)。这么写并非完全正确尽管也许现在这么写不会出现问题但是随着 SQL 语句变得越来越复杂你想要去重得到正确的结果就变得十分困难。 ANTI JOIN 这种连接的关系跟 SEMI JOIN 刚好相反。在 IN 或者 EXISTS 前加一个 NOT 关键字就能使用这种连接。举个例子来说我们列出书名表里没有书的作者-- Using INFROM authorWHERE author.id NOT IN (SELECT book.author_id FROM book)-- Using EXISTSFROM authorWHERE 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 bookauthor CROSS JOIN book DIVISION DIVISION 的确是一个怪胎。简而言之如果 JOIN 是一个乘法运算那么 DIVISION 就是 JOIN 的逆过程。 我们学到了什么让我们在脑海中再回想一下。SQL 是对表的引用 JOIN 则是一种引用表的复杂方式。但是 SQL 语言的表达方式和实际我们所需要的逻辑关系之间是有区别的并非所有的逻辑关系都能找到对应的 JOIN 操作所以这就要我们在平时多积累和学习关系逻辑这样你就能在以后编写 SQL 语句中选择适当的 JOIN 操作了。7SQL 中如同变量的派生表在这之前我们学习到过 SQL 是一种声明性的语言并且 SQL 语句中不能包含变量。但是你能写出类似于变量的语句这些就叫做派生表说白了所谓的派生表就是在括号之中的子查询-- A derived tableFROM (SELECT * FROM author)需要注意的是有些时候我们可以给派生表定义一个相关名(即我们所说的别名)。-- A derived table with an aliasFROM (SELECT * FROM author) a派生表可以有效的避免由于 SQL 逻辑而产生的问题。举例来说如果你想重用一个用 SELECT 和 WHERE 语句查询出的结果这样写就可以(以 Oracle 为例)-- Get authors first and last names, and their age in daysSELECT first_name, last_name, ageFROM ( SELECT first_name, last_name, current_date - date_of_birth age FROM author)-- If the age is greater than 10000 daysWHERE 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 aWHERE age 10000 我们学到了什么我们反复强调大体上来说 SQL 语句就是对表的引用而并非对字段的引用。要好好利用这一点不要害怕使用派生表或者其他更复杂的语句。8SQL 语句中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 AGROUP BY A.x, A.y还有一点值得留意的是MySQL 并不坚持这个标准这的确是令人很困惑的地方。译者注这并不是说 MySQL 没有 GROUP BY 的功能)但是不要被 MySQL 所迷惑。GROUP BY 改变了对表引用的方式。你可以像这样既在 SELECT 中引用某一字段也在 GROUP BY 中对其进行分组。 我们学到了什么GROUP BY再次强调一次是在表的引用上进行了操作将其转换为一种新的引用方式。9SQL 语句中的 SELECT 实质上是对关系的映射我个人比较喜欢“映射”这个词尤其是把它用在关系代数上。(译者注原文用词为 projection 该词有两层含义第一种含义是预测、规划、设计第二种意思是投射、映射经过反复推敲我觉得这里用映射能够更直观的表达出 SELECT 的作用)。一旦你建立起来了表的引用经过修改、变形你能够一步一步的将其映射到另一个模型中。SELECT 语句就像一个“投影仪”我们可以将其理解成一个将源表中的数据按照一定的逻辑转换成目标表数据的函数。通过 SELECT语句你能对每一个字段进行操作通过复杂的表达式生成所需要的数据。SELECT 语句有很多特殊的规则至少你应该熟悉以下几条你仅能够使用那些能通过表引用而得来的字段如果你有 GROUP BY 语句你只能够使用 GROUP BY 语句后面的字段或者聚合函数当你的语句中没有 GROUP BY 的时候可以使用开窗函数代替聚合函数当你的语句中没有 GROUP BY 的时候你不能同时使用聚合函数和其它函数有一些方法可以将普通函数封装在聚合函数中一些更复杂的规则多到足够写出另一篇文章了。比如为何你不能在一个没有 GROUP BY 的 SELECT 语句中同时使用普通函数和聚合函数(上面的第 4 条)原因如下凭直觉这种做法从逻辑上就讲不通如果直觉不能够说服你那么语法肯定能。SQL : 1999 标准引入了 GROUPING SETSSQL2003 标准引入了 group sets : GROUP BY() 无论什么时候只要你的语句中出现了聚合函数而且并没有明确的 GROUP BY 语句这时一个不明确的、空的 GROUPING SET 就会被应用到这段 SQL 中。因此原始的逻辑顺序的规则就被打破了映射(即 SELECT )关系首先会影响到逻辑关系其次就是语法关系。(译者注这段话原文就比较艰涩可以简单理解如下在既有聚合函数又有普通函数的 SQL 语句中如果没有 GROUP BY 进行分组SQL 语句默认视整张表为一个分组当聚合函数对某一字段进行聚合统计的时候引用的表中的每一条 record 就失去了意义全部的数据都聚合为一个统计值你此时对每一条 record 使用其它函数是没有意义的)。糊涂了是的我也是。我们再回过头来看点浅显的东西吧。 我们学到了什么SELECT 语句可能是 SQL 语句中最难的部分了尽管他看上去很简单。其他语句的作用其实就是对表的不同形式的引用。而 SELECT 语句则把这些引用整合在了一起通过逻辑规则将源表映射到目标表而且这个过程是可逆的我们可以清楚的知道目标表的数据是怎么来的。想要学习好 SQL 语言就要在使用 SELECT 语句之前弄懂其他的语句虽然 SELECT 是语法结构中的第一个关键词但它应该是我们最后一个掌握的。10SQL 语句中的几个简单的关键词DISTINCT,UNION ,ORDER BY 和 OFFSET在学习完复杂的 SELECT 豫剧之后我们再来看点简单的东西集合运算( DISTINCT 和 UNION )排序运算( ORDER BYOFFSET…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 等。让我们在工作中尽情的使用 SQLEnd.作者水果泡腾片来源数据分析1480原文http://blog.jobbole.com/55086/本文为转载分享如侵权请联系后台删除往期小编推荐 点击标题即可阅读??干货技巧丨数据岗位常考如何用Vlookup数组公式做逆向查找理论知识丨10个最经典的数据分析模型你集齐了几个学员月考丨4道SQL题 | 快速入门数据分析师面试点击“在看” 让更多朋友看到好内容
