Sql语句

举荐：应用NSDT场景编辑器助你疾速搭建可编辑的3D利用场景什么是 SQL 中的连贯？SQL 联接容许您基于公共列合并来自多个数据库表的数据。这样，您就能够将信息合并在一起，并在相干数据集之间创立有意义的连贯。 SQL 中的连贯类型有几种类型的 SQL 联接： 内联接左外连贯右外连贯齐全内部联接穿插连贯让咱们解释每种类型。SQL 外部联接外部联接仅返回在要联接的两个表中存在匹配项的行。它基于共享键或列合并两个表中的行，抛弃不匹配的行。咱们通过以下形式对此进行可视化。 在 SQL 中，这种类型的连贯是应用关键字 JOIN 或 INNER JOIN 执行的。 SQL 左内部联接左外连贯返回左侧（或第一个）表中的所有行和右侧（或第二个）表中的匹配行。如果没有匹配项，则返回右侧表中列的 NULL 值。咱们能够这样设想它。如果要在 SQL 中应用此联接，能够应用 LEFT OUTER JOIN 或 LEFT JOIN 关键字来实现。这是一篇探讨左联接与左外联接的文章。 SQL 右外联接右联接与左联接相同。它返回右侧表中的所有行和左侧表中的匹配行。如果没有匹配项，则返回左侧表中列的 NULL 值。在 SQL 中，此连贯类型是应用关键字 RIGHT OUTER JOIN 或 RIGHT JOIN 执行的。 SQL 齐全内部联接齐全内部联接返回两个表中的所有行，尽可能匹配行，并为不匹配的行填充 NULL 值。SQL 中此联接的关键字是“齐全内部联接”或“齐全联接”。 SQL 穿插联接这种类型的联接将一个表中的所有行与第二个表中的所有行合并在一起。换句话说，它返回笛卡尔积，即两个表行的所有可能组合。这是可视化成果，使其更容易了解。在 SQL 中穿插联接时，关键字是 CROSS JOIN。 理解 SQL 联接语法要在 SQL 中执行联接，您须要指定要联接的表、用于匹配的列以及要执行的联接类型。在 SQL 中联接表的根本语法如下： SELECT columnsFROM table1JOIN table2ON table1.column = table2.column;此示例演示如何应用 JOIN。援用 FROM 子句中的第一个（或左侧）表。而后，应用 JOIN 追随它并援用第二个（或右侧）表。 ...

hi 好久不见！明天将为大家带来一期干货满满的技术分享。 作为一款数据库管控平台，大家通常认为 CloudQuery 的外围能力是对平台的管控，包含对立入口治理、权限体系、审计剖析等，但实际上 CloudQuery 的核心技术点之一在于其独特的 SQL 解析能力。 01SQL 解析性能界定 SQL 解析是指将结构化查询语言（ SQL 语句）转换成能够被数据库系统了解和执行的外部示意模式的过程。在执行 SQL 查问之前，数据库系统须要对查问语句进行解析，以确定查问语句的语法是否正确，是否存在语义谬误，并生成执行打算。 目前 SQL 解析工具有 Druid、JSqlParser、Apache Calcite、Presto 等，它们都提供了一些 API 用于剖析 SQL 语句中的信息，比方获取表名、列名等字段。然而上述工具提供的 SQL 解析性能通常封装性比拟强，扩展性有余，反对的数据源无限，在许多非凡场景下它们无奈精确的给出 SQL 解析后果。 因而， CloudQuery 技术团队抉择应用 ANTLR 作为 SQL 解析的引擎。ANTLR 作为 SQL 解析引擎具备很多劣势，它帮忙咱们构建高效、可扩大和易于保护的 SQL 解析器。 02SQL 解析原理介绍SQL 解析是将用户输出的 SQL 语句转换为数据库可能了解的结构化查询语言的过程，与一般编程语言的解析无本质区别。次要分为词法剖析、语法分析、语义剖析、优化、代码生成这些步骤。SQL 解析的原理能够分为两个次要阶段：词法剖析和语法分析。词法剖析词法剖析是将 SQL 语句合成为一个个独自的 Token，标识每一个关键字、符号或者其余语法元素的过程。词法分析器会一一读取 SQL 语句中的字符，依据预约义好的规定组成不同的 Token，并将 Token 序列传递给下一个步骤。比方，上面是一个简略的 SELECT 语句：SELECT FirstName FROM Employee WHERE Department = 'Sales' 在进行词法剖析后，将生成的 Token 序列如下： ' fill='%23FFFFFF'%3E%3Crect x='249' y='126' width='1' height='1'%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E) 语法分析语法分析是将 Token 序列转换成语法分析树，并进行语义剖析、类型查看等解决的过程。语法分析器会依据当时定义好的 SQL 语法规定，将 Token 序列转换为语法分析树，并对其进行剖析，以确定 SQL 语句是否合乎语法标准。 比方，针对上述 SELECT 语句，语法分析器会将其转换为以下语法分析树：SELECT_STATEMENT/ | \ \SELECT COLUMN_LIST FROM WHERE| | |FirstName Employee Department = 'Sales'SQL ...

数据分析的语言接口OLAP计算引擎是一架机器，而操作这架机器的是编程语言。使用者通过特定语言通知计算引擎，须要读取哪些数据、以及须要进行什么样的计算。编程语言有很多种，任何人都能够设计出一门编程语言，而后设计对应的编译器做解析。编程语言从分类上来说，能够分为命令式，申明式。 命令式编程语言是咱们最常见的编程语言，C/C++/Java等都是命令式编程语言，这类语言明确的通知机器应该执行什么样的指令，留给编译器优化的空间很小了。 申明式编程形容程序应该取得什么后果，至于如何做到，并不关注细节。SQL就是一种申明式编程语言。例如SQL语句select count(1) from department where kpi =3.25，指明计算kpi=3.25的人数，但不会具体指定如何实现计算。这给后续的优化器留下了很大的操作空间，优化器能够依据SQL的需要和理论的数据做各种各样的摸索，寻找到最佳的执行形式。 一个优良的剖析语言应该具备以下几个特色： 语言简略，门槛低语意明确，无歧义材料丰盛，不便学习生态丰盛，工具多不便扩大，可编排简单的逻辑 SQL是一种历史悠久的，应用比拟宽泛的剖析语言。在关系型数据库时代就宽泛应用的一种语言。在21世纪初，呈现了MapReduce算法，数据分析师须要编写MapReduce程序来剖析数据。MapReduce程序是一种命令式语言，编写过程十分麻烦，堪比写程序，这就须要数据分析师不仅具备算法能力，还要具备工程能力，应用体验十分蹩脚。这就须要两个团队来单干，BI团队把剖析需要传递给开发团队，由开发团队去开发分析程序。为了改善剖析体验，呈现了SQL on Hadoop的解决方案，典型的如Hive，提供SQL接口，并把用户输出的SQL转写成MapReduce执行打算，因此极大的晋升了数据分析的体验，实现了BI团队的自主剖析，升高了数据分析的门槛，大大增加了受众范畴。因而，SQL的影响力是十分大的。从Hive开始，大数据的次要应用接口就转移到了SQL上。而工程师们能够在SQL这张皮之下，分心的优化性能，无缝的降级计算引擎，放弃应用接口的一致性。 SQL的语法简略，逻辑清晰，理解了最简略的查问语句之后，就能够嵌套多层表白很简单的逻辑。SQL基于关系代数，有实践根底，保障语意明确没有歧义。SQL的倒退历史十分长远，因此学习材料也比拟多，不便新入门者学习。同时围绕SQL的生态也比拟丰盛，有很多工具应用SQL做剖析。 除了SQL之外，也有一些软件推出自定义的语言，例如Elasticsearch应用Lucene语法，Prometheus推出了自定义的PromQL，而Splunk推出了SPL。每一种新的语法，对于新用户而言，都存在肯定的学习门槛。因此都不如SQL应用宽泛。能够说SQL是数据分析的事实标准。 数据模型数据模型（DataModel） 用于形容数据在数据库中的组织模式。常见的模型有关系模型（Relational），键值模型（Key/Value），图模型（Graph），文档模型（Document），列簇模型（Column-family）等。 关系型数据库采纳关系模型。Redis采纳键值模型。图数据库采纳图模型。MongolDB采纳文档模型。关系模型中的关系有点绕口，在英文中是Relational，硬翻译成了关系，我的了解，关系指的是一些相互之间有关系的属性组成的一个实体，因为各个列属性之间存在关联关系，而被称为一个关系，其实指的是属性之间的相关性，这种相关性体现在：属于同一行；满足列之间的约束条件；满足行之间的约束条件；满足不同关系之间的约束条件。通过不同的约束条件，是全副的数据造成一种有组织的存在。 数据库通过关系模型，定义出一个个关系实体，确保内容之间满足肯定的束缚标间，并且提供编程接口去读写数据库内容。一个数据库蕴含一堆关系，每个关系是一个多行多列的表格。每一行的各个列之间是相干的，也可能会定义一些约束条件。行与行之间，也可能通过定义惟一键（Primary Key），定义排序形式来束缚行之间的关系。关系与关系之间，能够通过内部键来实现。 这种列之间和行之间的束缚关系，在OLTP场景中比拟实用，因为OLTP关注的数据自身，因而在存储数据时，更多关注数据的存储模式。而OLAP关注的数据的剖析，所以在数仓中，这些约束条件是弱化的，因而，在数仓中，咱们只需关注一张多行多列的表格即可，像PK、排序这类束缚属性，更多只是用来做数据减速的伎俩。关系模型用来作为一种紧密的实践，给执行器的优化提供实践根底。然而这个名字毕竟太绕口，在后续文章中，除非波及到关系模型相干的实践，会应用关系这个词，个别状况下，会用表来指代一个关系。 关系代数(Relational Algebra)关系模型和关系代数是SQL的实践根底。代数不仅是咱们所熟知的简略的加减乘除等数学计算。在计算机行业，咱们见到过多种algebra，在神经网络中罕用的线性代数（linear algebra），在电路中用到的布尔代数（boolean algebra），香农把布尔代数带入到了逻辑电路设计中，为计算机二进制计算提供了理论依据。此外还有N多种algebra，这里不一一列举。 关系代数，源自于汇合代数，讲述汇合之间的变换关系。关系代数中的一系列操作，承受一个或两个关系作为输出，产生一个新的关系作为后果。因为输出和输入都是一个关系，咱们能够串联多个算子，造成更加简单的算子。关系代数中蕴含的算子有： (select，从一个关系中筛选出局部行，造成一个新的关系),(projection，从一个关系中筛选出局部列，造成一个新的关系),∪(Union，合并两个关系), ∩(Intersection，取两个关系的交加局部), –(difference，取两个关系的差集局部), ×(Product，两个关系的笛卡尔积),⋈(Join，两个关系在满足某些条件下的连贯),（Rename，重命名关系中的列）, ←(Assignments，把一个长期的查问命名成一个新的关系), (Duplicate Eliminating，去重), (Aggregation，对局部列做聚合计算，后果造成一个新关系), (Sorting，排序后果造成一个新关系)。这里定义了罕用的关系操作，名字曾经示意出了其操作的含意，在这里不再介绍每个操作的明细了。在语法解析和优化器阶段咱们会再次接触到关系代数，并且借助于关系代数的理论依据，来做一些语法树上的转换。在这里咱们只须要晓得在关系代数上有这些操作，并且在之后的SQL语法上看到如何用SQL语法来表白这些操作。 SQLSQL语言的倒退历史SQL的倒退历史，能够追溯到机械化数据分析的历史。在20世纪初，IBM次要的业务是打孔卡业务，也就是应用卡上的孔来记录信息，而后利用电路的通断判断是否有孔，并通过电路驱动机械安装，累计计算结果。打孔卡相似咱们古代应用的答题卡，答题卡的每一个题目，都提供了四个选项，而后用铅笔涂黑对应的选项；打孔卡不同的中央在于，选中的局部穿透成孔，当搁置到电路板上时，有孔的局部会有电流通过，进而触发之后的动作。这是在过后是一项十分先进的数据分析办法，相较于古老的依赖人去计数，也让大数据的自动化剖析成为可能。在20世纪初，要统计数千万人口的信息，须要投入大量的人力资源，而打孔卡这种创世纪的创造，带来了数据分析行业的疾速倒退。因而能够说IBM的业务次要是提供数据分析的机器，次要的客户场景是联邦政府的人口普查，以及业机构做商业剖析。这个时候，数据存储以来打孔卡，而数据计算是机械安装，计算结果输入到打印机。 到20世纪50年代，随着电气化的倒退，磁带取代打孔卡成为新的存储设备，电气安装取代机械安装做计数。计算结果能够持续存储到磁带上。磁带的存储空间很大，不过磁带的毛病是只能程序读写，这导致数据处理的程序不得不适应这种个性依照程序解决。 到了60、70年代，磁盘被创造进去，磁盘能够被随机读写，这极大的扭转了数据处理形式。数据结构无需思考数据之间的程序，一些更加简单的数据模型被创造进去，例如网状模型或者层次化模型。1970年，Edgar Codd定义了关系模型，给出了非过程式的查问数据的办法，关系型数据库诞生了。关系模型十分简洁，并且提供了实践根底。非过程式的查询方法，屏蔽了实现的细节，使用者只须要申明所须要的后果即可，实现的过程则交给优化器给出最优的执行打算，这极大的升高了应用门槛。关系模型的发明者也因而取得了图灵奖。只管关系模型在学术上十分吸引人，然而在事实中，在性能上还比不上曾经存在的数据库。 到了70年代前期、80年代，IBM推出了一个突破性的我的项目System R，在我的项目中研发了至关重要的可能使关系型数据库十分高效的技术。在System R中，IBM推出了SQL的最晚期版本，称为Sequal，起初演化成了SQL（Structed Query Language结构化查询语言）。这个我的项目尽管是个原型，然而它促成了之后IBM推出了第一个商用的关系模型的数据库产品System/38(1979),SQL/DS(1981),DB2(1983)。其中DB2目前还是沉闷的商用数据库，在大学中也有DB2的应用课程。至此，SQL语言呈现了，并且被其余的商用数据库系统所采纳，比方Oracle的数据库。在数十年内，SQL语言凭借着其易用性，击败了其余须要关怀底层实现的数据库产品，成为了事实上的规范。 1986年ANSI规范推出了SQL规范，称为SQL86，就是咱们常说的ANSI SQL。之后规范通过陆续补充，以增加新的个性，陆续呈现了SQL89，SQL92，SQL1999（正则式，触发器，OO）， SQL2003（XML,窗口函数，Sequence，自增ID），SQL2006， SQL2008（清空表语法，Fancy Sorting）， SQL2011（长期表，管道式DML）， 最近的是SQL2016（Json，多态表）。 一般来说，一个数据分析系统，不肯定齐全遵循SQL的规范，这次要是由剖析零碎的特有个性所决定的，有些个性，在SQL规范里边是没有的，所以个别会在SQL规范上做一些拓展，号称是兼容ANSI SQL。一个零碎须要反对的最小性能汇合是SQL92规范。 SQL的性能SQL语法蕴含了几个类别的性能，别离是 Data Manipulation Language（DML）：数据操作语言，用于增删改查数据。Data Definition Language（DDL）：数据定义语言，用于定义表的格局。Data Control Language（DCL）:数据管制语言，用于管制权限等。 尽管DML和DCL是SQL零碎的根底性能，本文的关注重点更多是数据处理的技术，以及如何放慢数据处理的技术，因而更多关注DDL。 在DDL中，也有增删改查，在这几项中，本文更多关注查的局部内容，即如何放慢数据的读取和计算。而数据的写入、存储局部的优化伎俩，也是为了满足减速数据计算的目标。 SQL的处理过程SQL全称Structed Query Language(结构化查询语言)。SQL语法简略，易学易用，是数据分析畛域最通用的语言。SQL是数据分析的操作工具，对于用户而言SQL代表浙用户的操作语义，然而对于程序而言，只是接管到一串字符串。程序须要了解SQL的意义，要通过词法剖析、语法分析、语义剖析、结构成形象语法树。词法剖析、语法分析是十分根底的操作。大学的计算机的编译原理课程应该蕴含了本局部内容，词法剖析和语法分析的模式是固定的，玩不出花色，无助于晋升计算速度。不过作为OLAP引擎中必不可少的第一环，还是有必要对词法剖析和语法分析做出简略的介绍，有助于理解后续章节中的查问打算和优化器，然而本章不会占用太多篇幅，本文的重点是对于计算速度的内容。 开发者也能够研发自定义的剖析语言，只有语言合乎肯定的规定，没有歧义，在语义上残缺，也能过称为一种语言。不过开发一个新的语言十分艰难，大多数的新语言采纳程序式编程，每一个短语示意一个简略的操作；或者采纳管道式申明语法，每一部分代表输出，计算和输入，然而要定义一种可能有限扩大而没有歧义的语法是很难的。在语义残缺水平上是不能和SQL相比拟的。无论是开发一门新的语言，还是采纳SQL，流程都和下图相似。OLAP引擎解析SQL，生成形象语法树，再转化成逻辑执行打算，通过优化后，生成高性能的算子组合。这就是编译和优化的过程。 图2-1 程序编译和SQL编译 ...

一、前言1.1 作者介绍王波，SphereEx MeshLab 研发工程师，目前专一于 Database Mesh，Cloud Native 的研发。Linux，llvm，yacc，ebpf user。 Gopher & Rustacean and c bug hunter。 GitHub: https://github.com/wbtlb 1.2 背景在上篇文章《Pisa-Proxy 之 SQL 解析实际》中介绍了 Pisa-Proxy 的外围模块之一 SQL 解析器的相干内容。在 MySQL 和 PostgreSQL 中 SQL 解析是通过 Yacc 实现的，同样 Pisa- Proxy 的 SQL 解析器是由相似 Yacc 这样的工具实现的，所以本篇文章会围绕 SQL 解析器为大家介绍一些编译原理和 Lex & Yacc 的应用，同时也会为读者展现如何通过 Lex & Yacc 实现一个简略的 SQL 解析器。从而帮忙大家更好地了解 Pisa-Proxy 中 SQL 解析器是如何工作的。 二、编译器初探一个程序语言不论是咱们罕用的 Java，Golang 或者是 SQL 实质上都是一个记号零碎，如同自然语言一样，它的残缺定义应该包含语法和语义两个方面。一种语言的语法其实是对应的一组规定，用它能够造成和产生一个适合的程序。以后应用最宽泛的伎俩是上下文无关的文法，上下文无关的文法作为程序设计语言语法的形容工具。语法只是定义什么样的符号序列是非法的，与这些符号的含意毫无关系。然而在语义中分为两类：动态语义和动静语义。动态语义是指一系列的限定规定，并确定哪些语法对于程序来说是适合的；动静语义也称作运行语义或者执行语义，明确程序具体要计算什么。 2.1 编译器工作流程如图 2.1.1 中所示，通常编译器将源代码编译成可执行文件次要有以下几步： 对源文件进行扫描，将源文件的字符流拆分分一个个的词（token），此为词法剖析依据语法规定将这些记号结构出语法树，此为语法分析对语法树的各个节点之间的关系进行查看，查看语义规定是否被违反，同时对语法树进行必要的优化，此为语义剖析遍历语法树的节点，将各节点转化为中间代码，并按特定的程序拼装起来，此为两头代码生成对中间代码进行优化将中间代码转化为指标代码对指标代码进行优化，生成最终的目标程序 图 2.1.1对于 SQL 解析来说，就能够将上图中的步骤简化为如图2.1.2 的模式，源码输出（SQL 语句），将 SQL 语句进行词法剖析，生成 SQL 中特定的 token 记号流。而后拿到记号流后进行语法分析后生成最终的 SQL AST。 ...

SQL 语句解析是一个重要且简单的技术，数据库流量相干的 SQL 审计、读写拆散、分片等性能都依赖于 SQL 解析，而 Pisa-Proxy 作为 Database Mesh 理念的一个实际，对数据库流量的治理是其外围，因而实现 SQL 解析是一项很重要的工作。本文将以 Pisa-Proxy 实际为例，为大家展示 Pisa-Proxy 中的 SQL 解析实现，遇到的问题及优化。一、背景对于语法分析语法分析个别通过词法分析器，如 Flex，生成相应的 token，语法分析器通过剖析 token，来判断是否满足定义的语法规定。 语法分析器个别会通过解析生成器生成。 语法分析算法罕用的有以下： LL（自上而下）与上下文无关文法，从左到右扫描，从最左推导语法树，相比 LR 更容易了解，错误处理更敌对。 LR（自下而上）与上下文无关文法，从左到右扫描，从最右节点推导语法树，相比 LL 速度快。 LALR与 LR 相似，在解析时比 LR 生成的状态更少，从而缩小 Shift/Reduce 或者 Reduce/Reduce 抵触，被业界宽泛应用的 bison/yacc 生成的就是基于 LALR 解析器。 对于调研在开发 SQL 解析之初，咱们从性能、维护性、开发效率、完成度四方面别离调研了 antlr_rust，sqlparser-rs，nom-sql 我的项目，但都存在一些问题。 antlr_rustShardingSphere 实现了基于 Antlr 的不同的 SQL 方言解析，为了应用它的 Grammar，咱们调研了 antlr_rust 我的项目，此我的项目不够沉闷，成熟度不够高。 sqlparser-rs在 Rust 社区里，sqlparser-rs 我的项目是一个较为成熟的库，兼容各种 SQL 方言，Pisa-Proxy 在将来也会反对多种数据源，然而因为其词法和语法解析都是纯手工打造的，对咱们来说会不易保护。 nom-sqlnom-sql 是基于 nom 库实现的 SQL 解析器，然而未实现残缺，性能测试不如预期。 ...

【摘要】本文介绍间接用 SQL 查问文件数据的各种状况，并用 esProc SPL 举例实现。请点击在文件上应用 SQL 查问的示例理解详情 在数据分析业务中常常要解决数据文件。咱们晓得，对于数据库中的数据，应用SQL来查问是十分方便快捷的，所以很容易想到把文件数据先导入到数据库再用SQL来查问。然而文件数据导入数据库自身也是很繁琐的工作，那么有没有间接对数据文件应用SQL查问的方法呢？本文将介绍这样的方法，列举出用 SQL 查问文件数据的各种状况，并提供用 esProc SPL 编写的代码示例。esProc 是业余的数据计算引擎，SPL 中提供了欠缺的用 SQL 查问文件数据的办法。 本文用文本文件举例，但同时也实用于Excel文件。 1. 过滤应用SQL从文本文件中筛选满足条件的记录。 示例：从学生成绩表Students_scores.txt中筛选出10班的学生问题，文件中第一行是列名，第二行开始是数据，如下图所示。 2. 汇总应用SQL对文本文件中的数据进行汇总。 示例：计算学生成绩表中全体学生的语文平均分、数学最高分、英语总分。 3. 跨列计算应用SQL对文本文件中的数据进行跨列计算。 示例：计算学生成绩表中每位学生的总分。 A1中后果如下，减少了一个新的计算列total_score： 4. CASE语句在SQL中能够应用CASE语句进行简单条件计算。 示例：计算学生成绩表中每位同学的英语问题是否及格。 A1中后果如下，减少了一个新的计算列English_evaluation： 5. 排序应用SQL对文本文件中的数据进行升/降序排序。 示例：将学生成绩表依照班号升序、总分降序的顺序排列。 6. TOP-N应用SQL对文本文件中的数据求TOP-N。 示例：查看英语问题最高的3个同学问题。 7. 分组汇总应用SQL对文本文件中的数据进行分组汇总。 示例：查问各班的英语最低分、语文最高分、数学总分。 8. 分组后过滤应用SQL对文本文件中的数据分组汇总后再过滤。 示例：找出英语平均分低于70分的班级。 A1中查问后果如下： 9. 去重应用SQL对文本文件中的数据进行去重查问。 示例：查问所有班级编号。 10. 去重计数应用SQL对文本文件中的数据进行去重计数。 示例：在产品数据文件中，统计共有多少种不同产品。文件局部数据如下所示： ...

引言优化SQL，是DBA常见的工作之一。如何高效、快速地优化一条语句，是每个DBA经常要面对的一个问题。在日常的优化工作中，我发现有很多操作是在优化过程中必不可少的步骤。然而这些步骤重复性的执行，又会耗费DBA很多精力。于是萌发了自己编写小工具，提高优化效率的想法。 那选择何种语言来开发工具呢？ 对于一名DBA来说，掌握一门语言配合自己的工作是非常必要的。相对于shell的简单、perl的飘逸，Python是一种严谨的高级语言。其具备上手快、语法简单、扩展丰富、跨平台等多种优点。很多人把它称为一种“胶水”语言，通过大量丰富的类库、模块，可以快速搭建出自己需要的工具。 于是乎，这个小工具就成了我学习Python的第一个作业，我把它称之为“MySQL语句优化辅助工具”。而且从此以后，我深深爱上了Python，并开发了很多数据库相关的小工具，以后有机会介绍给大家。 一、优化手段、步骤下面在介绍工具使用之前，首先说明下MySQL中语句优化常用的手段、方法及需要注意的问题。这也是大家在日常手工优化中，需要了解掌握的。 1、执行计划 — EXPLAIN命令执行计划是语句优化的主要切入点，通过执行计划的判读了解语句的执行过程。在执行计划生成方面，MySQL与Oracle明显不同，它不会缓存执行计划，每次都执行“硬解析”。查看执行计划的方法，就是使用EXPLAIN命令。 1）基本用法EXPLAIN QUERY 当在一个Select语句前使用关键字EXPLAIN时，MySQL会解释了即将如何运行该Select语句，它显示了表如何连接、连接的顺序等信息。 EXPLAIN EXTENDED QUERY 当使用EXTENDED关键字时，EXPLAIN产生附加信息，可以用SHOW WARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名，重写并且执行优化规则后SELECT语句是什么样子，并且还可能包括优化过程的其它注解。在MySQL5.0及更新的版本里都可以使用，在MySQL5.1里它有额外增加了一个过滤列(filtered)。 EXPLAIN PARTITIONS QUERY 显示的是查询要访问的数据分片——如果有分片的话。它只能在MySQL5.1及更新的版本里使用。 EXPLAIN FORMAT=JSON (5.6新特性) 另一个格式显示执行计划。可以看到诸如表间关联方式等信息。 2）输出字段下面说明一下EXPLAIN输出的字段含义，并由此学习如何判断一个执行计划。 id MySQL选定的执行计划中查询的序列号。如果语句里没有子查询等情况，那么整个输出里就只有一个SELECT，这样一来每一行在这个列上都会显示一个1。如果语句中使用了子查询、集合操作、临时表等情况，会给ID列带来很大的复杂性。如上例中，WHERE部分使用了子查询，其id=2的行表示一个关联子查询。 select_type 语句所使用的查询类型。是简单SELECT还是复杂SELECT(如果是后者，显示它属于哪一种复杂类型)。常用有以下几种标记类型。 DEPENDENT SUBQUERY子查询内层的第一个SELECT，依赖于外部查询的结果集。 DEPENDENT UNION子查询中的UNION，且为UNION中从第二个SELECT开始的后面所有SELECT，同样依赖于外部查询的结果集。 PRIMARY子查询中的最外层查询，注意并不是主键查询。 SIMPLE除子查询或UNION之外的其他查询。 SUBQUERY子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集。 UNCACHEABLE SUBQUERY结果集无法缓存的子查询。 UNIONUNION语句中的第二个SELECT开始后面的所有SELECT，第一个SELECT为PRIMARY。 UNION RESULTUNION中的合并结果。从UNION临时表获取结果的SELECT。 DERIVED衍生表查询(FROM子句中的子查询)。MySQL会递归执行这些子查询，把结果放在临时表里。在内部，服务器就把当做一个"衍生表"那样来引用，因为临时表就是源自子查询。 table 这一步所访问的数据库中表的名称或者SQL语句指定的一个别名表。这个值可能是表名、表的别名或者一个为查询产生的临时表的标识符，如派生表、子查询或集合。 type 表的访问方式。以下列出了各种不同类型的表连接，依次是从最好的到最差的。 system系统表，表只有一行记录。这是const表连接类型的一个特例。 const读常量，最多只有一行匹配的记录。由于只有一行记录，优化程序里该行记录的字段值可以被当作是一个恒定值。const用于在和PRIMARY KEY或UNIQUE索引中有固定值比较的情形。 eq_ref最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合。与const类型不同的是，这是最好的连接类型。它用在索引所有部分都用于做连接并且这个索引是一个PRIMARY KEY或UNIQUE类型。eq_ref可以用于在进行"="做比较时检索字段。比较的值可以是固定值或者是表达式，表达示中可以使用表里的字段，它们在读表之前已经准备好了。 refJOIN语句中驱动表索引引用的查询。该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是PRIMARY KEY或UNIQUE索引(换句话说，就是连接程序无法根据键值只取得一条记录)的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。ref还可以用于检索字段使用"="操作符来比较的时候。 ref_or_null与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。这种连接类型类似ref，不同的是MySQL会在检索的时候额外的搜索包含NULL值的记录。这种连接类型的优化是从MySQL 4.1.1开始的，它经常用于子查询。 index_merge查询中同时使用两个(或更多)索引，然后对索引结果进行合并(merge)，再读取表数据。这种连接类型意味着使用了Index Merge优化方法。 unique_subquery子查询中的返回结果字段组合是主键或唯一约束。 index_subquery子查询中的返回结果字段组合是一个索引(或索引组合)，但不是一个主键或唯一索引。这种连接类型类似unique_subquery。它用子查询来代替IN，不过它用于在子查询中没有唯一索引的情况下。 range索引范围扫描。只有在给定范围的记录才会被取出来，利用索引来取得一条记录。 index全索引扫描。连接类型跟ALL一样，不同的是它只扫描索引树。它通常会比ALL快点，因为索引文件通常比数据文件小。MySQL在查询的字段知识单独的索引的一部分的情况下使用这种连接类型。 fulltext全文索引扫描。 all全表扫描。 possible_keys 该字段是指MySQL在搜索表记录时可能使用哪个索引。如果没有任何索引可以使用，就会显示为null。 key ...

【摘要】 使用 SQL 查询语言，你只能查询位于数据库里面的数据，但是当你面对的数据是一些 Excel 表格或者 Txt 文本格式时，有什么办法能直接对着文件进行 Select 查询呢？去乾学院看个究竟：Java 中如何使用 SQL 查询文本! Ø 引言 作为一名数据库开发程序员，使用 SQL 语言查询数据是再普通不过的一件事。而 SQL 语言是基于数据库的查询语言，这就要求被查询的数据只能位于数据库中。但在实际工作中，时常会碰到一些不是来源于数据库的数据，特别是很多来自一些 Excel 表格或者 TXT 文本文件。如果要对它们进行 SQL 查询，通常做法是在数据库中创建临时表，然后导入数据后再使用 SQL 对其查询。这种做法本身未尝不可，但是有几点不妥： 首先，比较麻烦，数据的来源五花八门，格式不同，表结构也各不相同。导入操作往往只能由数据库管理员手动维护，其面临的复杂度可想而知。 其次，数据库常常会涉及敏感数据或安全考虑，就算你不怕麻烦，愿意去维护，也未必有权限进行这类操作。 最后，这些数据往往可能是临时的、突发的，根本不适合往数据库里添加，否则会搞得数据库越来越臃肿，最终导致整体访问性能低下。 有了这些不妥，程序员一般也就不得不放弃 SQL 式查询了，只能通过程序实现一些简单的关键字搜索等功能，毕竟自己去实现 SQL 语法的查询不仅难度大而且完全没必要。 但是 SQL 查询有时候真的很好用啊……那么，有没有一种第三方软件能实现 SQL 式查询文本，让程序员在享受便利的同时，不需要考虑上面这些烦心的问题呢？ 答案自然是有，那就是本文要介绍的——集算器。Ø 开始 下面就来介绍一下，如何在 Java 中利用集算器实现 SQL 式查询文本文件。当然，此处的文本文件不是指完全自由的文本文件，而是有格式规定的，类似于数据表的文件。 从官网下载并安装好集算器，将 dm.jar 及其依赖的配置文件 raqsoftConfig.xml 加入到当前程序的类路径。然后使用集算器提供的 JDBC 类，便可将文本文件当成数据库中的数据表来进行 SQL 式查询了。 示例用到两个文件，第一个 student.txt 数据内容如下： 数据格式为：第一行为字段名，后续行是数据，各列之间用 Tab 键分开。这个表是各个班级的学生基本情况。 第二个 score.txt 数据内容如下： 这个文件通过‘班级’，‘学生 ID’两个关键字段，记录每个学生的各科成绩。Ø JDBC 示例 对于熟悉 JDBC 的同学来说，Java 调用集算器使用 SQL 查询文本的过程很简单，下面贴出示例代码：import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;public class SQLDemo {public static void main(String[] args) {Connection con = null;// 连接Statement stmt = null;// 执行语句ResultSet rst = null;// 结果集try {/********* 通过 JDBC 连接到 集算器 /Class.forName(“com.esproc.jdbc.InternalDriver”);con = DriverManager.getConnection(“jdbc:esproc:local://”);/****************** 执行语句方法 /stmt = con.createStatement();rst = stmt.executeQuery(“SELECT * FROM score.txt”);/******************* 执行语句方法结束 /ResultSetMetaData meta = rst.getMetaData();for (int i = 0; i < meta.getColumnCount(); i++) {System.out.print(meta.getColumnName(i + 1) + “\t”);}System.out.println();// 输出结果while (rst.next()) {for (int i = 0; i < meta.getColumnCount(); i++) {System.out.print(rst.getObject(i + 1) + “\t”);}System.out.println();}stmt.close();con.close();} catch (ClassNotFoundException cnf) {System.out.println(“没找到驱动程序”);cnf.printStackTrace();} catch (SQLException se) {se.printStackTrace();}}} 示例中可以看到加粗的 SQL 语句跟普通 SQL 语法基本一致，仅仅是表名不一样，这里直接写 TXT 文件名。执行后结果如下： 可以看到，使用集算器的 JDBC，完全可以将具有表结构的文本文件直接当成数据表来查询。而集算器除了支持 TXT 格式之外，还支持 CSV、XLS、XLSX 甚至 JSON。另外，集算器自身也有两种数据存储格式：BTX 和 CTX，也是可以直接查询的。 好，进一步的问题来了，集算器对 SQL 的各种命令能够支持到何种程度呢？ 首先需要说明的是，集算器不是一个数据库产品，所以对 SQL 中一些数据库维护命令是不支持的，比如 Create、Delete 等。 那么对于 Select 可以支持到什么程度呢？Ø 分组统计 下面来看看 SQL 查询中最普遍的查询，将 student.txt 跟 score.txt 关联起来，并分组统计出每名学生的总成绩。将上面示例代码中加粗的 SQL 语句替换为如下语句：SELECT A. 班级,A. 姓名,sum(B. 成绩) 总分 FROM student.txt A JOIN score.txt B ON A. 班级 =B. 班级 AND A. 学生 ID=B. 学生 ID GROUP BY A. 班级,A. 姓名 执行后，得到每班学生的总分表： 可以看到，集算器用 SQL 查询文本时，对于常规的分组、表的联合都没问题。那么带参数的查询，又该如何写呢？Ø 使用参数 只需将执行语句方法块里的代码替换为以下代码：/***************** 执行语句方法 /CallableStatement cs = null; // 定义 CallableStatement 对象 StringString sql = “SELECT 姓名, 性别 FROM student.txt WHERE 班级 =?”;cs = con.prepareCall(sql);cs.setString(1, “一班”);rst = cs.executeQuery();/******************* 执行语句方法结束 **/ 换完后，注意要引入 CallableStatement 类。执行后结果如下： 带参数的句子仍然没问题，调用方法也跟数据库标准是一致的。不过上述结果中，性别显示为数字，集算器 SQL 能不能支持转换语法呢？Ø CASE 语句 将上面的 SQL 语句替换成带 CASE 的 SQL 语句：SELECT 姓名,CASE 性别 WHEN 0 THEN ‘男’ ELSE ‘女’ END 性别 FROM student.txt WHERE 班级 =? 结果如下： 通过上面这几个例子可以看到，集算器对常用查询的支持还是比较全面的。鉴于篇幅原因，这里就不继续一一列举了。更多集算器查询文件时的详细语法以及函数，请参考文档：http://doc.raqsoft.com.cn/esproc/func/dbquerysql.html#db_sql_ ...

【摘要】 SQL 虽然是针对记录的集合进行运算, 但在记录的多次利用以及有序运算却经常要重复计算，效率不佳。而集算器 SPL 则要直观许多，可以按自然思维习惯写出运算。这里对 SQL 和集算器 SPL 在记录的利用及有序运算方面进行了对比，如果需要了解更多，请前往乾学院：SQL 难点解决：记录的引用!1、 求最大值 / 最小值所在记录 示例 1 ：计算招商银行 (600036)2017 年收盘价达到最低价时的所有交易信息。 MySQL8: with t as (select * from stktrade where sid=‘600036’ and tdate between ‘2017-01-01’ and ‘2017-12-31’) select * from t where close=(select min(close) from t); 集算器SPL: A3: 计算 A2 中 close 为最小值的所有记录示例 2：计算招商银行 (600036)2017 年最后的最低价和最早的最高价相隔多少自然日 MySQL8: with t as (select , row_number() over(order by tdate) rn from stktrade where sid=‘600036’ and tdate between ‘2017-01-01’ and ‘2017-12-31’), t1 as (select * from t where close=(select min(close) from t)), t2 as (select * from t where close=(select max(close) from t)), t3 as (select * from t1 where rn=(select max(rn) from t1)), t4 as (select * from t2 where rn=(select min(rn) from t2)) select abs(datediff(t3.tdate,t4.tdate)) inteval from t3,t4; 集算器SPL: A3: 从后往前查找 close 第 1 个最小值的记录 A4: 从前往后查找 close 第 1 个最大值的记录 2、 查找满足条件的记录 示例 1：计算招商银行 (600036)2017 年收盘价超过 25 元时的交易信息 MySQL8: with t as (select * from stktrade where sid=‘600036’ and tdate between ‘2017-01-01’ and ‘2017-12-31’) select * from t where tdate=(select min(tdate) from t where close>=25); 集算器SPL: A3: 从前往后查找收盘价超过25元的第1条记录 示例 2：计算招商银行 (600036) 上一周的涨幅(考虑停牌) MySQL8: with t1 as (select * from stktrade where sid=‘600036’), t11 as (select max(tdate) tdate from t1), t2 as (select subdate(tdate, weekday(tdate)+3)m from t11), t3 as (select max(tdate) m from t1,t2 where t1.tdate<=t2.m), t4 as (select subdate(m, weekday(m)+3)m from t3), t5 as (select max(tdate) m from t1,t4 where t1.tdate<=t4.m) select s1.close/s2.close-1 from (select * from t1,t3 where t1.tdate=t3.m) s1, (select * from t1,t5 where t1.tdate=t5.m) s2; 集算器SPL: A3: 求最后1个交易日所在周的周日(周日为一周的第一天) A4: 从后往前查找上周5以前的第1条记录，即上一交易周的最后一条记录 A5: 求上一个交易周的周日 A6: 从后往前查找上一个交易周的前一个周5的第1第记录，即上上交易周的最后一条记录 示例 3：重叠部分不重复计数时求多个时间段包含的总天数 MySQL8: with t(start,end) as ( select date'2010-01-07’,date'2010-01-9’ union all select date'2010-01-15’,date'2010-01-16’ union all select date'2010-01-07’,date'2010-01-12’ union all select date'2010-01-08’,date'2010-01-11’), t1 as (select , row_number() over(order by start,end desc) rn from t), t2 as (select * from t1 where not exists(select * from t1 s where s.rn=t1.end)) select sum(end-start+1) from t2; 集算器SPL: A3: 按起始时间升序、结束时间降序进行排序 A4: 选取结束时间比前面所有记录的结束时间都要晚的记录 A5: 计算总天数，max(start,end[-1])选起始时间和上一个结束时间较大者，interval计算2个日期相差天数 注：A4也可以改成 =A3.run(end=max(end,end[-1])) 示例 3：列出超 42% 人口使用的语言有 2 种以上的国家里使用人口超 42% 的语言的相关信息 MySQL8: with t as (select * from world.countrylanguage where percentage>=42), t1 as (select countrycode, count() cnt from t group by countrycode having cnt>=2) select t. from t join t1 using (countrycode); 集算器SPL: A3: 按国家编码分组 A4: 对成员数超过2个的组求和集 3、 求前 n 个表达式值最小的记录 示例 1：计算招商银行 (600036)2017 年成交量最大的 3 天交易信息 MySQL8: select * from stktrade where sid=‘600036’ and tdate between ‘2017-01-01’ and ‘2017-12-31’ order by volume desc limit 3; 集算器SPL: A3: 根据-volume排序，然后取前 3 条记录 示例 2：计算招商银行 (600036) 最近 1 天的涨幅 MySQL8: with t as (select *, row_number() over(order by tdate desc) rn from stktrade where sid=‘600036’) select t1.close/t2.close-1 rise from t t1 join t t2 where t1.rn=1 and t2.rn=2; 集算器SPL: A3: 按交易日期倒序取最后 2 条记录 (效果等同于 A2.top(2;-tdate))，最后一天的交易记录序号为 1，倒数第 2 天的交易记录序号为 2 A4: 计算涨幅 示例 3：计算每个国家最大城市中人口前 5 的城市的相关信息 MySQL8: with t as (select *,row_number() over(partition by countrycode order by population desc) rn from world.city), t1 as (select id,name,countrycode,district,population from t where rn=1) select * from t1 order by population desc limit 5; 集算器SPL: A3: 按国家分组，分组返回人口最多的城市的记录 A4: 取所有国家最大城市中人口前 5 的城市记录 4、 外键引用记录 示例 1：计算亚洲和欧洲人口前 3 城市的相关信息 MySQL8: with t as ( select co.Continent, co.name CountryName, ci.name CityName, ci.Population, row_number()over(partition by continent order by population desc) rn from world.country co join world.city ci on co.code=ci.countrycode where continent in (‘Asia’,‘Europe’) ) select Continent, group_concat(cityname,’,’,countryname, ‘,’, population order by population desc separator ‘;’) Cities from t where rn<=3 group by continent; 集算器SPL: A4: 将 A2 中序表的键设为 Code 字段 A5: 将 A3 中序表 CountryCode 字段转换为 A2 中相应记录，无对应记录时删除 A6: 先根据 Continent 分组，再计算每组人口前 3 的城市，然后将每条记录中的城市名称、国家名称和人口拼成串，最后将每组中的串相连 示例 2：以“上级姓名 / 下级姓名”的形式返回指定雇员的所有上级 MySQL8: with recursive emp(id,name,manager_id) as ( select 29,‘Pedro’,198 union all select 72,‘Pierre’,29 union all select 123,‘Adil’, 692 union all select 198,‘John’,333 union all select 333,‘Yasmina’,null union all select 692,‘Tarek’, 333 ), t2(id,name,manager_id,path) as( select id,name,manager_id,cast(name as char(400) ) from emp where id=(select manager_id from emp where id=123) union all select t1.id,t1.name, t1.manager_id, concat(t1.name,’/’,t2.path) from t2 join emp t1 on t2.manager_id=t1.id) select path from t2 where manager_id is null; 集算器SPL: A3: 将manager_id转换成A2中与manager_id相等的id所在的记录 A4: 查找id为123的记录 A5: 依次列出A4上级、上级的上级、……，直到最高上级(即manager_id为null) A6: 将所有上级按从最高上级到最下上级排列，然后将所有上级的姓名用/分隔相连 ...