• [Java] mybatis 和 hibernate 的区别
    (1)Mybatis和hibernate不同,它不完全是一个ORM框架,因为MyBatis需要程序员自己编写Sql语句。(2)Mybatis直接编写原生态sql,可以严格控制sql执行性能,灵活度高,非常适合对关系数据模型要求不高的软件开发,因为这类软件需求变化频繁,一但需求变化要求迅速输出成果。但是灵活的前提是mybatis无法做到数据库无关性,如果需要实现支持多种数据库的软件,则需要自定义多套sql映射文件,工作量大。(3)Hibernate对象/关系映射能力强,数据库无关性好,对于关系模型要求高的软件,如果用hibernate开发可以节省很多代码,提高效率。
  • [技术干货] 【转载】十八般武艺玩转GaussDB(DWS)性能调优:总体调优策略
     摘要: 性能调优是应用迁移或开发过程中的关键步骤,同时也在整个项目实施过程中占据很大的份量,本篇主要介绍数据库级别的性能调优思路和总体策略。性能调优是应用迁移或开发过程中的关键步骤,同时也在整个项目实施过程中占据很大的份量,在很多实施步骤中都需要进行考虑,从开始的数据建模,表定义的设计,到数据库硬件、集群部署的选择,再到数据库系统级调优、数据表结构设计,以及单个SQL语句的编写及调优,都要考虑对性能的影响。同时,性能调优通常没有明确的衡量标准,没有明确的对错之分,通常需要的隐式技能比较多,使得其的技术含金量得以提升。通常来看,要做到一个性能调优的高手,除了对于应用程序的逻辑做到游刃有余外,还需要对应用的数据库的基本实现原理有所了解,更甚者,还需要对操作系统、网络等基础知识有所涉猎,同时还要具备性能诊断和分析技巧。当然,性能调优是一个不断积累的过程,大家不用考虑一步到位,唯有进行实践的积累,才能在广阔的调优战场所向披靡。本篇博文作为《GaussDB(DWS)性能调优系列》的专题文章,主要介绍数据库级别的性能调优思路和总体策略,包括系统级和语句级调优。同时,《GaussDB(DWS)性能调优系列》文章分为基础篇和实战篇,各位读者在通过基础篇文章了解数据库的基本原理后,可以结合调优思路,对实战篇的各个调优技巧进行深入的学习。有关整个调优过程中的其它方面,后续论坛会推出其它博文进行介绍。1. GaussDB DWS执行架构及说明GaussDB DWS是典型的share-nothing架构,其计算组件的示意图如上图所示,主要由CN(Coordinator)和DN(DataNode)组成。CN是整个集群的协调者,是整个集群与应用进行连接处理的门户,用于接收客户的SQL语句并返回执行结果。DN是集群进行数据计算的主体,各个DN拥有独立的存储和计算资源,使得各个DN可以独立地进行计算。GaussDB DWS支持多CN架构,通常应用程序会通过LVS(负载均衡设备)将语句均匀分发到各个CN上,以减少单个CN的瓶颈作用。GaussDB DWS的SQL处理流程如上方右图所示,其包含以下几个主要步骤:(1)CN通过驱动或客户端接收到一条SQL后,会进行解析、优化,并最终返回执行结果;(2)CN进行优化后,生成相应的执行计划;(3)CN将相同的执行计划下发到各个DN进行执行;(4)如果DN之间需要进行数据交换,则执行计划中包含流操作算子Stream,DN之间同步通过Stream算子进行数据交换,共同完成计划的执行并向CN返回结果。同时,GaussDB DWS对于不需要DN间数据交换的语句,还支持语句下发到DN生成计划;对于部分不支持分布式查询的语句,生成不能下推的计划(此计划对于大数据量性能较差)。各种计划的对比可详见博文《GaussDB(DWS)性能调优系列基础篇三:衍化至繁之分布式计划详解》。本文后续的讨论均基于Stream计划。2. 整体调优思路通过前面对SQL语句执行流程的介绍,我们可以知道,性能瓶颈可能发生在CN端、DN端,以及结果集返回,驱动数据处理等环节,性能调优的第一步就是定位瓶颈点主要发生在哪个环节。由于GaussDB DWS大数据量处理时,大部分执行时间消耗在DN端,故本博文主要针对DN端语句执行进行总体调优思路的分享。谈到执行性能,其实从数据模型建模、集群部署、表结构设计,到最终的SQL语句优化,都与之紧密相关,如上图所示,我们使用金字塔来描述整个调优过程。越接近塔尖,其对于整个业务性的影响范围越广,需要调优时,调优成本也越高,所以在设计之初需要投入足够精力,从上至下,我们需要全面的设计,才能减少在最终SQL调优时返工的可能。整个调优过程其实是一个不断迭代的过程,如上图所示。即使设计再严密,也有可能出现SQL语句性能的优化需要导致数据建模更改、集群部署、表分布键调整的情况,这时一发动全身将引起较高的成本,同时会对其它已经调优完毕的SQL产生影响,导致重新调优,成本较高。我们统一将前三阶段归结为静态调优,将SQL语句级调优归结为执行态调优。下面重点来探讨执行态调优-SQL语句调优,从调优步骤来看可以分为性能瓶颈诊断、性能原因分析和调优项实施,从调优实施对象来看,可能包括前面提到的数据建模、集群部署、表结构设计方面的修改,SQL语句层调优可以分为系统级调优和语句级调优。当然,有一些调优项,例如系统调优项,可以作为经验固化下来,在集群部署的时候就一并设置好,减少这方面调优花费的成本。同时,SQL调优也是一个迭代的过程,在实施一次调优项后,需要继续重新进行调优分析,直至性能达到标准为止。后面的章节,将围绕调优步骤和SQL层的调优项来开展。3. 性能瓶颈诊断GaussDB DWS提供了丰富的计划信息显示工具Explain,以及动态执行信息分析工具Top SQL。Explain工具主要针对单个语句进行展示,可以使用explain命令显示CN生成的SQL语句的计划,也可以使用explain analyze/performance命令显示执行态信息。通过执行态信息,我们可以分析出算子为单位的性能,也可以分析出算子内部各步骤的性能,进一步为诊断性能的瓶颈打下了基础。Explain工具相关内容请参考博文《GaussDB(DWS)性能调优系列基础篇二:大道至简explain计划信息》。Top SQL工具则针对集群中运行的语句进行整体性能分析,其包含12个视图,可以将执行时间超过一定设置阈值的语句的执行状态、执行结果进行实时查询,同时可以设置将其转储用作后续分析。附加于该工具之上的SQL自诊断调优工具,则通过瓶颈点的分析,给出可能的性能原因分析。同时,我们还提供Unique SQL工具,进行一类SQL的性能持续跟踪,可以用于发现系统资源及硬件问题对SQL性能产生的影响。Top SQL工具相关内容请参考博文《GaussDB(DWS)性能调优系列实战篇二:十八般武艺之坏味道SQL识别》。4. 性能原因分析性能原因分析属于性能调优里的高阶知识了,通常要对数据库的执行实现原理有基本了解才能够逐步深入下去。本章节将深入浅出地介绍数据库执行实现原理的基本技术,帮助各位读者朋友能够有兴趣去主动查找性能产生的原因,从而自己找到性能调优的方法。前面已经对GaussDB DWS的执行流程进行了介绍,由CN生成执行计划下发到DN去执行。GaussDB DWS是基于代价来生成计划的,因此需要依据基表的统计信息,进行每一步结果集统计信息的估算,根据数据规模的场景从GaussDB DWS支持的备选算子中选择最优的算子组合成计划进行执行。因此,统计信息是计划准确的前提,在执行SQL语句前要确保收集最新的统计信息,有关统计信息的收集可以参见博文《GaussDB(DWS)性能调优系列基础篇一:万物之始analyze统计信息》。由于统计信息只包含基表的统计信息,表关联之后的统计信息只能通过估算得到,因此仍然可能存在估算不准的情况。GaussDB DWS针对不同的SQL语句中的操作,为每个操作内部实现了不同的算子。每个算子可能在部分场景下是占优的,但其它场景比较差。SQL优化时,根据具体的场景去自动匹配最优的算子。如果存在估算不准,将导致算子选择出现失误,从而计划较差,此时就需要根据计划的瓶颈来分析具体的原因了。通常情况下,GaussDB处理的操作类型主要分为:扫描算子(Scan)、关联算子(Join)、聚集算子(Agg)和网络传输算子(Stream)。下表列出了各算子类别的使用场景,以及各类别中可选的算子,及其适用范围,同时列出调优场景,供大家参考。(1)扫描算子(Scan):主要用于处理从存储扫描数据,返回上层算子,包括:全表扫描算子、索引扫描算子,其中行列存均对应不同的全套扫描算子,索引扫描包括:IndexScan(普通索引扫描)、IndexOnlyScan(仅扫描索引即可获得结果)、BitmapScan(需要索引扫描获取位图后再到基表上扫描),BitmapScanAnd/Or(从多个索引扫描进行位图运算后再到基表上扫描),由于索引扫描的原理基本都相同,故一并探讨。(2)关联算子(Join):主要用于处理表的关联操作。在数据库中,多表关联时,SQL优化会选择关联顺序进行两两关联。表关联时可以包含关联操作,也可以没有关联操作(笛卡尔积)。在GaussDB DWS中,主要包含NestLoop, HashJoin, MergeJoin三种关联算子。(3)聚集算子(Agg):(4)网络传输算子(Stream):(5)其它算子:同时GaussDB DWS还支持排序(Sort)、集合(SetOp)、物化(Materialize)、窗口聚集(WindowAgg)和输出限制(Limit)算子,由于调优基本不涉及,故此处略过。5. 调优项实施在知道导致性能问题的原因后,就可以制定调优项并开始实施了。前面已经提到,调优项实施的范围很广。本博文仅探讨数据库级的调优项,包括系统级调优和语句级调优两部分。a)系统级调优项系统级调优又细分为操作系统参数调优和数据库全局参数调优,通常涉及到的是系统CPU、IO、内存、网络资源的充分使用,避免资源冲突,提升整个系统查询的吞吐量。由于数据库是运行在操作系统之上的,因此操作系统资源的利用率对于数据库性能的提升起到基石的作用。对于操作系统参数的调优,主要集中在操作系统内存参数、IO参数以及网络参数的设置上,具体可参见GaussDB DWS产品文档。数据库级别的调优,主要也是集中在上述资源的使用上,在上述四维度有以下主要因素的考虑(具体设置方法可以参见GaussDB DWS产品文档):b)语句级调优项语句级调优通常需要通过计划分析,找到性能瓶颈点,然后根据瓶颈点对应的扫描、关联、聚集、Stream等算子,分析是否属于算子适用场景,是否符合调优条件。如果是,我们有以下调优手段:i. 通过修改表定义,包括行列存、表的分布方式,达到减少IO和网络资源开销的目的,详见博文《GaussDB(DWS)性能调优系列实战篇三:十八般武艺之好味道表定义》。ii. 如果最终分析是由于估算不准导致,可以通过相关GUC参数调整来设置不同的结果集估算模型,或禁止生成某种类型的算子,通过改进估算值达到优化计划的目的,详见博文《GaussDB(DWS)性能调优系列实战篇五:十八般武艺之路径干预》。iii.如果在迁移或升级过程中出现计划劣化,也可以通过Plan Hint的调优方式干预优化器生成理想的计划,详见博文《GaussDB(DWS)性能调优系列实战篇六:十八般武艺Plan hint运用》。iv.对于上述调优手段都无法解决的问题,例如:下推问题,相关子查询提升,NOT IN等问题,或者SQL语句存在计算冗余等问题,需要根据瓶颈点选择灵活多变的SQL改写策略消除瓶颈点,具体可参见博文《GaussDB(DWS)性能调优系列实战篇四:十八般武艺之SQL改写》总的来说,性能调优是一项艰巨的工程,当然深入其中,学习到的知识以及获得的收获都是非常大的。后续论坛也会推出更多的博文对性能调优的方方面面进行介绍,帮助各位读者迅速积累调优的经验
  • [技术干货] MySQL WHERE:条件查询
    在使用 MySQL SELECT语句时,可以使用 WHERE 子句来指定查询条件,从 FROM 子句的中间结果中选取适当的数据行,达到数据过滤的效果。语法格式如下:WHERE <查询条件> {<判定运算1>,<判定运算2>,…}其中,判定运算其结果取值为 TRUE、FALSE 和 UNKNOWN。判定运算的语法分类如下:<表达式1>{=|<|<=|>|>=|<=>|<>|!=}<表达式2><表达式1>[NOT]LIKE<表达式2><表达式1>[NOT][REGEXP|RLIKE]<表达式2><表达式1>[NOT]BETWEEN<表达式2>AND<表达式3><表达式1>IS[NOT]NULL单一条件的查询语句【实例 1】在表 tb_students_info 中查询身高为 170cm 的学生的姓名,输入的 SQL 语句和行结果如下所示。mysql> use test_db Database changed mysql> SELECT name,height     -> FROM tb_students_info     -> WHERE height=170; +-------+--------+ | name  | height | +-------+--------+ | Susan |    170 | +-------+--------+ 1 row in set (0.17 sec)该语句采用了简单的相等过滤,查询一个指定列 height 的具体值 170。【实例 2】查询年龄小于 22 的学生的姓名,输入的 SQL 语句和执行结果如下所示。mysql> SELECT name,age     -> FROM tb_students_info     -> WHERE age<22; +------+------+ | name | age  | +------+------+ | John |   21 | +------+------+ 1 row in set (0.05 sec)可以看到,查询结果中所有记录的 age 字段的值均小于 22 岁,而大于或等于 22 岁的记录没有被返回。多条件的查询语句使用 SELECT 查询时,可以增加查询的限制条件,这样可以使查询的结果更加精确。MySQL 在 WHERE 子句中使用 AND 操作符限定只有满足所有查询条件的记录才会被返回。可以使用 AND 连接两个甚至多个查询条件,多个条件表达式之间用 AND 分开。【实例 3】在 tb_students_info 表中查询 age 大于 21,并且 height 大于等于 175 的学生的信息,输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info     -> WHERE age>21 AND height>=175; +----+--------+---------+------+------+--------+------------+ | id | name   | dept_id | age  | sex  | height | login_date | +----+--------+---------+------+------+--------+------------+ |  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 | |  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 | |  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 | +----+--------+---------+------+------+--------+------------+ 3 rows in set (0.06 sec)注意:上例的 WHERE 子句中只包含一个 AND 语句,把两个过滤条件组合在一起,实际上可以添加多个 AND 过滤条件,增加条件的同时增加一个 AND 关键字。使用 LIKE 的模糊查询字符串匹配的语法格式如下:<表达式1> [NOT] LIKE <表达式2>字符串匹配是一种模式匹配,使用运算符 LIKE 设置过滤条件,过滤条件使用通配符进行匹配运算,而不是判断是否相等进行比较。相互间进行匹配运算的对象可以是 CHAR、VARCHAR、TEXT、DATETIME 等数据类型。运算返回的结果是 TRUE 或 FALSE。利用通配符可以在不完全确定比较值的情形下创建一个比较特定数据的搜索模式,并置于关键字 LIKE 之后。可以在搜索模式的任意位置使用通配符,并且可以使用多个通配符。MySQL 支持的通配符有以下两种:1) 百分号(%)百分号是 MySQL 中常用的一种通配符,在过滤条件中,百分号可以表示任何字符串,并且该字符串可以出现任意次。使用百分号通配符要注意以下几点:MySQL 默认是不区分大小写的,若要区分大小写,则需要更换字符集的校对规则。百分号不匹配空值。百分号可以代表搜索模式中给定位置的 0 个、1 个或多个字符。尾空格可能会干扰通配符的匹配,一般可以在搜索模式的最后附加一个百分号。2) 下划线(_)下划线通配符和百分号通配符的用途一样,下画线只匹配单个字符,而不是多个字符,也不是 0 个字符。注意:不要过度使用通配符,对通配符检索的处理一般会比其他检索方式花费更长的时间。【实例 4】在 tb_students_info 表中,查找所有以“T”字母开头的学生姓名,输入的 SQL 的语句和执行结果如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE name LIKE 'T%'; +--------+ | name   | +--------+ | Thomas | | Tom    | +--------+ 2 rows in set (0.12 sec)注意:在搜索匹配时,通配符“%”可以放在不同位置。【实例 5】在 tb_students_info 表中,查找所有包含“e”字母的学生姓名,输入的 SQL 的语句和执行结果如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE name LIKE '%e%'; +-------+ | name  | +-------+ | Green | | Henry | | Jane  | +-------+ 3 rows in set (0.00 sec)由执行结果可以看出,该语句查询字符串中包含字母 e 的学生的姓名,只要名字中有字母 e,其前面或后面无论有多少个字符,都满足查询的条件。【实例 6】在 tb_students_info 表中,查找所有以字母“y”结尾,且“y”前面只有 4 个字母的学生的姓名,输入的 SQL 语句和执行结果如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE name LIKE '____y'; +-------+ | name  | +-------+ | Henry | +-------+ 1 row in set (0.00 sec)日期字段作为条件的查询语句以日期字段作为条件,可以使用比较运算符设置查询条件,也可以使用 BETWEEN AND 运算符查询某个范围内的值。BETWEEN AND 用来查询某个范围内的值,该操作符需要两个参数,即范围的开始值和结束值,若字段值满足指定的范围查询条件,则这些记录被返回。【实例 7】在表 tb_students_info 中查询注册日期在 2016-01-01 之前的学生的信息,输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info     -> WHERE login_date<'2016-01-01'; +----+-------+---------+------+------+--------+------------+ | id | name  | dept_id | age  | sex  | height | login_date | +----+-------+---------+------+------+--------+------------+ |  1 | Dany  |       1 |   25 | F    |    160 | 2015-09-10 | |  3 | Henry |       2 |   23 | M    |    185 | 2015-05-31 | |  6 | John  |       2 |   21 | M    |    172 | 2015-11-11 | |  8 | Susan |       4 |   23 | F    |    170 | 2015-10-01 | +----+-------+---------+------+------+--------+------------+ 4 rows in set (0.04 sec)【实例 8】在表 tb_students_info 中查询注册日期在 2015-10-01 和 2016-05-01 之间的学生的信息,输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info     -> WHERE login_date     -> BETWEEN '2015-10-01'     -> AND '2016-05-01'; +----+-------+---------+------+------+--------+------------+ | id | name  | dept_id | age  | sex  | height | login_date | +----+-------+---------+------+------+--------+------------+ |  5 | Jim   |       1 |   24 | M    |    175 | 2016-01-15 | |  6 | John  |       2 |   21 | M    |    172 | 2015-11-11 | |  7 | Lily  |       6 |   22 | F    |    165 | 2016-02-26 | |  8 | Susan |       4 |   23 | F    |    170 | 2015-10-01 | +----+-------+---------+------+------+--------+------------+ 4 rows in set (0.02 sec)在使用 MySQL SELECT语句时,可以使用 WHERE 子句来指定查询条件,从 FROM 子句的中间结果中选取适当的数据行,达到数据过滤的效果。语法格式如下:WHERE <查询条件> {<判定运算1>,<判定运算2>,…}其中,判定运算其结果取值为 TRUE、FALSE 和 UNKNOWN。判定运算的语法分类如下:<表达式1>{=|<|<=|>|>=|<=>|<>|!=}<表达式2><表达式1>[NOT]LIKE<表达式2><表达式1>[NOT][REGEXP|RLIKE]<表达式2><表达式1>[NOT]BETWEEN<表达式2>AND<表达式3><表达式1>IS[NOT]NULL单一条件的查询语句【实例 1】在表 tb_students_info 中查询身高为 170cm 的学生的姓名,输入的 SQL 语句和行结果如下所示。mysql> use test_db Database changed mysql> SELECT name,height     -> FROM tb_students_info     -> WHERE height=170; +-------+--------+ | name  | height | +-------+--------+ | Susan |    170 | +-------+--------+ 1 row in set (0.17 sec)该语句采用了简单的相等过滤,查询一个指定列 height 的具体值 170。【实例 2】查询年龄小于 22 的学生的姓名,输入的 SQL 语句和执行结果如下所示。mysql> SELECT name,age     -> FROM tb_students_info     -> WHERE age<22; +------+------+ | name | age  | +------+------+ | John |   21 | +------+------+ 1 row in set (0.05 sec)可以看到,查询结果中所有记录的 age 字段的值均小于 22 岁,而大于或等于 22 岁的记录没有被返回。多条件的查询语句使用 SELECT 查询时,可以增加查询的限制条件,这样可以使查询的结果更加精确。MySQL 在 WHERE 子句中使用 AND 操作符限定只有满足所有查询条件的记录才会被返回。可以使用 AND 连接两个甚至多个查询条件,多个条件表达式之间用 AND 分开。【实例 3】在 tb_students_info 表中查询 age 大于 21,并且 height 大于等于 175 的学生的信息,输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info     -> WHERE age>21 AND height>=175; +----+--------+---------+------+------+--------+------------+ | id | name   | dept_id | age  | sex  | height | login_date | +----+--------+---------+------+------+--------+------------+ |  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 | |  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 | |  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 | +----+--------+---------+------+------+--------+------------+ 3 rows in set (0.06 sec)注意:上例的 WHERE 子句中只包含一个 AND 语句,把两个过滤条件组合在一起,实际上可以添加多个 AND 过滤条件,增加条件的同时增加一个 AND 关键字。使用 LIKE 的模糊查询字符串匹配的语法格式如下:<表达式1> [NOT] LIKE <表达式2>字符串匹配是一种模式匹配,使用运算符 LIKE 设置过滤条件,过滤条件使用通配符进行匹配运算,而不是判断是否相等进行比较。相互间进行匹配运算的对象可以是 CHAR、VARCHAR、TEXT、DATETIME 等数据类型。运算返回的结果是 TRUE 或 FALSE。利用通配符可以在不完全确定比较值的情形下创建一个比较特定数据的搜索模式,并置于关键字 LIKE 之后。可以在搜索模式的任意位置使用通配符,并且可以使用多个通配符。MySQL 支持的通配符有以下两种:1) 百分号(%)百分号是 MySQL 中常用的一种通配符,在过滤条件中,百分号可以表示任何字符串,并且该字符串可以出现任意次。使用百分号通配符要注意以下几点:MySQL 默认是不区分大小写的,若要区分大小写,则需要更换字符集的校对规则。百分号不匹配空值。百分号可以代表搜索模式中给定位置的 0 个、1 个或多个字符。尾空格可能会干扰通配符的匹配,一般可以在搜索模式的最后附加一个百分号。2) 下划线(_)下划线通配符和百分号通配符的用途一样,下画线只匹配单个字符,而不是多个字符,也不是 0 个字符。注意:不要过度使用通配符,对通配符检索的处理一般会比其他检索方式花费更长的时间。【实例 4】在 tb_students_info 表中,查找所有以“T”字母开头的学生姓名,输入的 SQL 的语句和执行结果如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE name LIKE 'T%'; +--------+ | name   | +--------+ | Thomas | | Tom    | +--------+ 2 rows in set (0.12 sec)注意:在搜索匹配时,通配符“%”可以放在不同位置。【实例 5】在 tb_students_info 表中,查找所有包含“e”字母的学生姓名,输入的 SQL 的语句和执行结果如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE name LIKE '%e%'; +-------+ | name  | +-------+ | Green | | Henry | | Jane  | +-------+ 3 rows in set (0.00 sec)由执行结果可以看出,该语句查询字符串中包含字母 e 的学生的姓名,只要名字中有字母 e,其前面或后面无论有多少个字符,都满足查询的条件。【实例 6】在 tb_students_info 表中,查找所有以字母“y”结尾,且“y”前面只有 4 个字母的学生的姓名,输入的 SQL 语句和执行结果如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE name LIKE '____y'; +-------+ | name  | +-------+ | Henry | +-------+ 1 row in set (0.00 sec)日期字段作为条件的查询语句以日期字段作为条件,可以使用比较运算符设置查询条件,也可以使用 BETWEEN AND 运算符查询某个范围内的值。BETWEEN AND 用来查询某个范围内的值,该操作符需要两个参数,即范围的开始值和结束值,若字段值满足指定的范围查询条件,则这些记录被返回。【实例 7】在表 tb_students_info 中查询注册日期在 2016-01-01 之前的学生的信息,输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info     -> WHERE login_date<'2016-01-01'; +----+-------+---------+------+------+--------+------------+ | id | name  | dept_id | age  | sex  | height | login_date | +----+-------+---------+------+------+--------+------------+ |  1 | Dany  |       1 |   25 | F    |    160 | 2015-09-10 | |  3 | Henry |       2 |   23 | M    |    185 | 2015-05-31 | |  6 | John  |       2 |   21 | M    |    172 | 2015-11-11 | |  8 | Susan |       4 |   23 | F    |    170 | 2015-10-01 | +----+-------+---------+------+------+--------+------------+ 4 rows in set (0.04 sec)【实例 8】在表 tb_students_info 中查询注册日期在 2015-10-01 和 2016-05-01 之间的学生的信息,输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info     -> WHERE login_date     -> BETWEEN '2015-10-01'     -> AND '2016-05-01'; +----+-------+---------+------+------+--------+------------+ | id | name  | dept_id | age  | sex  | height | login_date | +----+-------+---------+------+------+--------+------------+ |  5 | Jim   |       1 |   24 | M    |    175 | 2016-01-15 | |  6 | John  |       2 |   21 | M    |    172 | 2015-11-11 | |  7 | Lily  |       6 |   22 | F    |    165 | 2016-02-26 | |  8 | Susan |       4 |   23 | F    |    170 | 2015-10-01 | +----+-------+---------+------+------+--------+------------+ 4 rows in set (0.02 sec)
  • [其他] 【故障】SQL执行C函数报错,查看后台UDF进程异常,UDF state显示down
    问题描述:sql执行UDF函数报错,后台查看集群UDF进程,发现集群进程异常:cm_ctl query –CviF解决方法:  查看cm_agent日志,发现日志一直在拉起secbox进程,且日志中存在以下报错: df -h 查看磁盘挂载情况,发现/var/log/Bigdata是单独挂载的查看$GAUSSHOME/secbox/secbox.conf,发现配置文件中未对单独挂载的目录进行配置,导致了cm_agent的上述报错解决方案:[mount_path] read /var/log后面追加一条[mount_path] read /var/log/Bigdata的配置,等待几秒再检查一下集群状态,正常后把其他节点做同样的修改
  • [技术干货] SQL Server数据库中的表名称、字段比较
    前言项目中一般分测试环境(QAS),生产环境(PRD),当我们的项目经历了一次周期跨度较长的更新后,当我们发布到生产环境时,首要的任务是将新增的表,字段更新到生产数据库。很多时候,当我们发布更新的时候,已经很难记得做了哪些变更。当然有的人会说,1.EF Code First 有history记录,这是一种办法,可靠么?不可靠。相信即便是用Code First,直接改数据库的肯定不止我一个。2.查看实体类变更记录,这也是一个办法。那如果用的DB First的呢?当然也可以看,就是很麻烦。3.开发过程中,对数据库的变更记下来。这么做过的肯定也不止我一个。手动狗头中午的时候,就想着另外一个项目下个月要更新,改了N多的东西,到时候数据库咋更新呢。就想着写个工具比较两个版本数据库,表名称,字段,字段类型的区别。说干就干(本来想着用EF,DBContext应该可以实现,无奈学艺不精,最终还是回到了ADO.Net)。控制台应用程序,目前只能对比新增,修改(SQl Server)。using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Text; using Microsoft.EntityFrameworkCore; namespace EFGetTable {  class Program  {   static void Main(string[] args)   {    string prdconnectionstring = "Data Source=localhost;initial catalog=ttPRD;user id=sa;password=password;MultipleActiveResultSets=True";    var prd = GetTableNames(prdconnectionstring);    string qasconnectionstring = "Data Source=localhost;initial catalog=ttqas;user id=sa;password=password;MultipleActiveResultSets=True";    var qas = GetTableNames(qasconnectionstring);    CompareTable(prd, qas);   }   public static List<TableInfo> GetTableNames(string connectionstr)   {    var tableresult = new List<TableInfo>();    string sqlTableName = "Select * From Information_Schema.Tables";    using (SqlConnection connection = new SqlConnection(connectionstr))    {     using (SqlCommand cmd = new SqlCommand(sqlTableName, connection))     {      try      {       connection.Open();       SqlDataReader dr = cmd.ExecuteReader();//       while (dr.Read())       {        // 表名        TableInfo table = new TableInfo();        table.TableName = dr["Table_Name"].ToString();        table.columns.AddRange(GetColumnNamesByTable(dr["Table_Name"].ToString(), connection));        tableresult.Add(table);       }       connection.Close();      }      catch (System.Data.SqlClient.SqlException e)      {       Console.ForegroundColor = ConsoleColor.Red;       Console.Error.WriteLine(e.Message);       connection.Close();      }     }     return tableresult;    }   }   public static List<CloumnInfo> GetColumnNamesByTable(string tableName, SqlConnection connection)   {    var Columnresults = new List<CloumnInfo>();    string sqlcolum = $"Select * From Information_Schema.Columns t Where t.Table_Name =\'{tableName}\'";    using (SqlCommand cmd = new SqlCommand(sqlcolum, connection))    {     SqlDataReader dr = cmd.ExecuteReader();//     while (dr.Read())     {      // 表名      CloumnInfo column = new CloumnInfo();      column.CloumnName = dr["Column_name"].ToString();      column.DateType = dr["DATA_TYPE"].ToString() + dr["CHARACTER_MAXIMUM_LENGTH"].ToString();      Columnresults.Add(column);     }     return Columnresults;    }   }   public static void CompareTable(List<TableInfo> prd, List<TableInfo> qas)   {    foreach (var p in qas)    {     var tablequery = prd.AsQueryable().Where(t => t.TableName.Equals(p.TableName));     if (!tablequery.Any())     {      Console.WriteLine($"New Created Table {p.TableName}");      continue;     }     else     {      var querytable = tablequery.FirstOrDefault();      p.columns.ForEach(c =>      {       var Cloumnquery = querytable.columns.Select(cc => cc.CloumnName).Contains(c.CloumnName);       if (!Cloumnquery)       {        Console.WriteLine($"New add cloumn: {c.CloumnName} on Table {p.TableName}");       }       else       {        var querycloumn = querytable.columns.Where(qt => qt.CloumnName.Equals(c.CloumnName)).FirstOrDefault();        if (!querycloumn.DateType.Equals(c.DateType))        {         Console.WriteLine($"DateType Different: cloumn: {c.CloumnName} , {querycloumn.DateType}==>{c.DateType} on Table {p.TableName}");        }       }      });     }    }   }  }  public class TableInfo  {   public TableInfo()   {    columns = new List<CloumnInfo>();   }   public string TableName { get; set; }   public List<CloumnInfo> columns { get; set; }  }  public class CloumnInfo  {   public string CloumnName { get; set; }   public string DateType { get; set; }  } }测试结果学习本就是解决问题的过程,在问题中成长。加油!
  • [技术干货] SQL中exists的使用方法
    EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False有一个查询如下:  SELECT c.CustomerId, CompanyName  FROM Customers c  WHERE EXISTS(  SELECT OrderID FROM Orders o  WHERE o.CustomerID = cu.CustomerID)这里面的EXISTS是如何运作呢?子查询返回的是OrderId字段,可是外面的查询要找的是CustomerID和CompanyName字段,这两个字段肯定不在OrderID里面啊,这是如何匹配的呢?EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False。EXISTS 指定一个子查询,检测行的存在。语法:EXISTS subquery。参数 subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。结果类型为 Boolean,如果子查询包含行,则返回 TRUE。在子查询中使用 NULL 仍然返回结果集这个例子在子查询中指定 NULL,并返回结果集,通过使用 EXISTS 仍取值为 TRUE。  SELECT CategoryName  FROM Categories  WHERE EXISTS (SELECT NULL)  ORDER BY CategoryName ASC比较使用 EXISTS 和 IN 的查询这个例子比较了两个语义类似的查询。第一个查询使用 EXISTS 而第二个查询使用 IN。注意两个查询返回相同的信息  SELECT DISTINCT pub_name  FROM publishers  WHERE EXISTS  (SELECT *  FROM titles  WHERE pub_id = publishers.pub_id  AND type = \'business\')  SELECT distinct pub_name  FROM publishers  WHERE pub_id IN  (SELECT pub_id  FROM titles  WHERE type = \'business\')比较使用 EXISTS 和 = ANY 的查询本示例显示查找与出版商住在同一城市中的作者的两种查询方法:第一种方法使用 = ANY,第二种方法使用 EXISTS。注意这两种方法返回相同的信息。  SELECT au_lname, au_fname  FROM authors  WHERE exists  (SELECT *  FROM publishers  WHERE authors.city = publishers.city)  SELECT au_lname, au_fname  FROM authors  WHERE city = ANY  (SELECT city  FROM publishers)比较使用 EXISTS 和 IN 的查询本示例所示查询查找由位于以字母 B 开头的城市中的任一出版商出版的书名:  SELECT title  FROM titles  WHERE EXISTS  (SELECT *  FROM publishers  WHERE pub_id = titles.pub_id  AND city LIKE \'B%\')  SELECT title  FROM titles  WHERE pub_id IN  (SELECT pub_id  FROM publishers  WHERE city LIKE \'B%\')使用 NOT EXISTSNOT EXISTS 的作用与 EXISTS 正相反。如果子查询没有返回行,则满足 NOT EXISTS 中的 WHERE 子句。本示例查找不出版商业书籍的出版商的名称:  select distinct 姓名 from xs  where not exists (  select * from kc  where not exists (  select * from xs_kc  where 学号=xs.学号 and 课程号=kc.课程号  )把最外层的查询xs里的数据一行一行的做里层的子查询。中间的 exists 语句只做出对上一层的返回 true 或 false,因为查询的条件都在 where 学号=xs.学号 and 课程号=kc.课程号这句话里。每一个 exists 都会有一行值。它只是告诉一层,最外层的查询条件在这里成立或都不成立,返回的时候值也一样回返回上去。直到最高层的时候如果是 true(真)就返回到结果集。为 false(假)丢弃。  where not exists  select * from xs_kc  where 学号=xs.学号 and 课程号=kc.课程号这个 exists 就是告诉上一层,这一行语句在我这里不成立。因为他不是最高层,所以还要继续向上返回。select distinct 姓名 from xs where not exists (这里的 exists 语句收到上一个为 false 的值。他在判断一下,结果就是为 true(成立),由于是最高层所以就会把这行的结果(这里指的是查询条件)返回到结果集。几个重要的点:最里层要用到的醒询条件的表比如:xs.学号、kc.课程号等都要在前面的时候说明一下select * from kc,select distinct 姓名 from xs不要在太注意中间的exists语句.把exists和not exists嵌套时的返回值弄明白
  • [技术干货] 数据库常见面试题
    1.事务四大特性 原子性,要么执行,要么不执行    隔离性,所有操作全部执行完以前其它会话不能看到过程 一致性,事务前后,数据总额一致 持久性,一旦事务提交,对数据的改变就是永久的2.MYSQL的两种存储引擎区别(事务、锁级别等等),各自的适用场景索引区别Hashhash索引,等值查询效率高,不能排序,不能进行范围查询B+数据有序,范围查询3.聚集索引和非聚集索引索引区别聚集索引数据按索引顺序存储,中子结点存储真实的物理数据非聚集索引存储指向真正数据行的指针4.索引的优缺点,什么时候使用索引,什么时候不能使用索引索引最大的好处是提高查询速度, 缺点是更新数据时效率低,因为要同时更新索引 对数据进行频繁查询进建立索引,如果要频繁更改数据不建议使用索引。5.InnoDB索引和MyISAM索引的区别一是主索引的区别,InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。 二是辅助索引的区别:InnoDB的辅助索引data域存储相应记录主6.索引的底层实现(B+树,为何不采用红黑树,B树)重点树区别红黑树增加,删除,红黑树会进行频繁的调整,来保证红黑树的性质,浪费时间B树也就是B-树B树,查询性能不稳定,查询结果高度不致,每个结点保存指向真实数据的指针,相比B+树每一层每屋存储的元素更多,显得更高一点。B+树B+树相比较于另外两种树,显得更矮更宽,查询层次更浅7.B+树的实现一个m阶的B+树具有如下几个特征: 1.有k个子树的中间节点包含有k个元素(B树中是k-1个元素),每个元素不保存数据,只用来索引,所有数据都保存在叶子节点。 2.所有的叶子结点中包含了全部元素的信息,及指向含这些元素记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。 3.所有的中间节点元素都同时存在于子节点,在子节点元素中是最大(或最小)元素8.为什么使用B+Tree索引查找过程中就要产生磁盘I/O消耗,主要看IO次数,和磁盘存取原理有关。 根据B-Tree的定义,可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理, 将一个节点的大小设为等于一个页,这样每个节点只需要一次I/O就可以完全载入 局部性原理与磁盘预读9.Sql的优化1.sql尽量使用索引,而且查询要走索引2.对sql语句优化索引类型概念普通索引最基本的索引,没有任何限制唯一索引与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。主键索引它是一种特殊的唯一索引,不允许有空值。全文索引针对较大的数据,生成全文索引很耗时好空间。组合索引为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则失效条件 条件是or,如果还想让or条件生效,给or每个字段加个索引 like查询,以%开发 内部函数 对索引列进行计算 is null不会用,is not null 会用9.关系型数据库和非关系型数据库区别关系型数据库优点 1、容易理解:二维表结构是非常贴近逻辑世界一个概念,关系模型相对网状、层次等其他模型来说更容易理解; 2、使用方便:通用的SQL语言使得操作关系型数据库非常方便; 3、易于维护:丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率; 4、支持SQL,可用于复杂的查询。 5.支持事务 缺点 1、为了维护一致性所付出的巨大代价就是其读写性能比较差; 2、固定的表结构; 3、不支持高并发读写需求; 4、不支持海量数据的高效率读写非关系型数据库1、使用键值对存储数据; 2、分布式; 优点 无需经过sql层的解析,读写性能很高 基于键值对,数据没有耦合性,容易扩展 存储数据的格式:nosql的存储格式是key,value形式 缺点 不提供sql支持10.最左匹配原则最左匹配原则是针对索引的 举例来说:两个字段(name,age)建立联合索引,如果where age=12这样的话,是没有利用到索引的, 这里我们可以简单的理解为先是对name字段的值排序,然后对age的数据排序,如果直接查age的话,这时就没有利用到索引了, 查询条件where name=‘xxx’ and age=xx 这时的话,就利用到索引了,再来思考下where age=xx and name=’xxx‘ 这个sql会利用索引吗, 按照正常的原则来讲是不会利用到的,但是优化器会进行优化,把位置交换下。这个sql也能利用到索引了
  • [技术干货] vs code连接sql server数据库步骤及遇到的问题小结
    最近因为要在vs code上连接到sql server数据库,第一次尝试,之前用 java的时候使用jdbc连接过数据库,感觉用vs code连接数据库步骤要简单一些,网上写的博客也不是特别多,就顺便把我的步骤记录下来。第一步,安装mssql扩展先在vs code的扩展里面找到SQL Server(mssql)扩展,并下载安装即可。下载好之后会出现SQL SERVER的版块。(请忽略已经连接好的数据库)第二步,连接到 SQL Server官方文档给出的连接步骤请按照以下步骤创建连接配置文件并连接到 SQL Server。按“Ctrl”+“Shift”+“P”或“F1”打开“命令面板”。键入 sql 以显示 mssql 命令,或键入 sqlcon,然后从下拉列表中选择“MS SQL:连接”。也可直接在扩展里进行连接官方的文字表述有点晦涩,不太好理解的话,可以按照我的操作,如图,点击connection右边的+号即可新建一个连接,会弹出连接的属性配置框。第三步,配置连接属性在弹出的属性配置依此按要连接的数据库情况填写即可服务器名称或 ADO 连接字符串指定 SQL Server 实例名称这里如果是连接本地的数据库的话,直接填localhost即可,其他的可参考引用。使用 localhost 连接到本地计算机上的 SQL Server 实例。 如果要连接到远程 SQL Server,请输入目标 SQLServer 的名称,或它的 IP 地址。 若要连接到 SQL Server 容器,请指定容器主机的 IP 地址。如果需要指定端口,请使用逗号将其与名称分开。 例如,对于侦听端口 1401 的服务器,请输入 ,1401。“数据库名称”(可选)这里可填要使用的数据库,也可不填即为默认数据库。 若要连接到默认数据库,请不要在此处指定数据库名称。(这里我开始填写的时候填错了,后面连接的时候报错了)我这里选择的是数据库TEST。这里我开始因为理解错了数据库名称填错了,遇到了如下的错误。(mssql: Error: Unable to connect using the connection information provided. Retry profile creation?),我在博客上看到有和我遇到一样问题的,把错误提示发出来,方便检索。只要检查每一步的信息无误就不会报这个错误了。3.身份验证类型选择“集成”或“SQL 登录”。或者,可以在此处输入数据库的 ADO 连接字符串。 这里选择第一个SQL Login就好了。4.用户名如果选择了“SQL 登录”,则输入拥有访问服务器上数据库权限的用户名。这里填数据库的用户名就好,如我的:sa5.密码输入指定用户的密码。6.保存密码按“Enter”选择“是”并保存密码。 选择“否”,系统将在每次使用连接配置文件时提示输入密码。7.配置文件名称”(可选)键入连接配置文件的名称,例如 localhost 配置文件。8.配置完成,到这里就可以在vs code里对sql server进行操作了。
  • [技术干货] MySQL WHERE:条件查询
    在使用 MySQL SELECT语句时,可以使用 WHERE 子句来指定查询条件,从 FROM 子句的中间结果中选取适当的数据行,达到数据过滤的效果。语法格式如下:WHERE <查询条件> {<判定运算1>,<判定运算2>,…}其中,判定运算其结果取值为 TRUE、FALSE 和 UNKNOWN。判定运算的语法分类如下:<表达式1>{=|<|<=|>|>=|<=>|<>|!=}<表达式2><表达式1>[NOT]LIKE<表达式2><表达式1>[NOT][REGEXP|RLIKE]<表达式2><表达式1>[NOT]BETWEEN<表达式2>AND<表达式3><表达式1>IS[NOT]NULL单一条件的查询语句【实例 1】在表 tb_students_info 中查询身高为 170cm 的学生的姓名,输入的 SQL 语句和行结果如下所示。mysql> use test_db Database changed mysql> SELECT name,height     -> FROM tb_students_info     -> WHERE height=170; +-------+--------+ | name  | height | +-------+--------+ | Susan |    170 | +-------+--------+ 1 row in set (0.17 sec)该语句采用了简单的相等过滤,查询一个指定列 height 的具体值 170。【实例 2】查询年龄小于 22 的学生的姓名,输入的 SQL 语句和执行结果如下所示。mysql> SELECT name,age     -> FROM tb_students_info     -> WHERE age<22; +------+------+ | name | age  | +------+------+ | John |   21 | +------+------+ 1 row in set (0.05 sec)可以看到,查询结果中所有记录的 age 字段的值均小于 22 岁,而大于或等于 22 岁的记录没有被返回。多条件的查询语句使用 SELECT 查询时,可以增加查询的限制条件,这样可以使查询的结果更加精确。MySQL 在 WHERE 子句中使用 AND 操作符限定只有满足所有查询条件的记录才会被返回。可以使用 AND 连接两个甚至多个查询条件,多个条件表达式之间用 AND 分开。【实例 3】在 tb_students_info 表中查询 age 大于 21,并且 height 大于等于 175 的学生的信息,输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info     -> WHERE age>21 AND height>=175; +----+--------+---------+------+------+--------+------------+ | id | name   | dept_id | age  | sex  | height | login_date | +----+--------+---------+------+------+--------+------------+ |  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 | |  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 | |  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 | +----+--------+---------+------+------+--------+------------+ 3 rows in set (0.06 sec)注意:上例的 WHERE 子句中只包含一个 AND 语句,把两个过滤条件组合在一起,实际上可以添加多个 AND 过滤条件,增加条件的同时增加一个 AND 关键字。使用 LIKE 的模糊查询字符串匹配的语法格式如下:<表达式1> [NOT] LIKE <表达式2>字符串匹配是一种模式匹配,使用运算符 LIKE 设置过滤条件,过滤条件使用通配符进行匹配运算,而不是判断是否相等进行比较。相互间进行匹配运算的对象可以是 CHAR、VARCHAR、TEXT、DATETIME 等数据类型。运算返回的结果是 TRUE 或 FALSE。利用通配符可以在不完全确定比较值的情形下创建一个比较特定数据的搜索模式,并置于关键字 LIKE 之后。可以在搜索模式的任意位置使用通配符,并且可以使用多个通配符。MySQL 支持的通配符有以下两种:1) 百分号(%)百分号是 MySQL 中常用的一种通配符,在过滤条件中,百分号可以表示任何字符串,并且该字符串可以出现任意次。使用百分号通配符要注意以下几点:MySQL 默认是不区分大小写的,若要区分大小写,则需要更换字符集的校对规则。百分号不匹配空值。百分号可以代表搜索模式中给定位置的 0 个、1 个或多个字符。尾空格可能会干扰通配符的匹配,一般可以在搜索模式的最后附加一个百分号。2) 下划线(_)下划线通配符和百分号通配符的用途一样,下画线只匹配单个字符,而不是多个字符,也不是 0 个字符。注意:不要过度使用通配符,对通配符检索的处理一般会比其他检索方式花费更长的时间。【实例 4】在 tb_students_info 表中,查找所有以“T”字母开头的学生姓名,输入的 SQL 的语句和执行结果如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE name LIKE 'T%'; +--------+ | name   | +--------+ | Thomas | | Tom    | +--------+ 2 rows in set (0.12 sec)注意:在搜索匹配时,通配符“%”可以放在不同位置。【实例 5】在 tb_students_info 表中,查找所有包含“e”字母的学生姓名,输入的 SQL 的语句和执行结果如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE name LIKE '%e%'; +-------+ | name  | +-------+ | Green | | Henry | | Jane  | +-------+ 3 rows in set (0.00 sec)由执行结果可以看出,该语句查询字符串中包含字母 e 的学生的姓名,只要名字中有字母 e,其前面或后面无论有多少个字符,都满足查询的条件。【实例 6】在 tb_students_info 表中,查找所有以字母“y”结尾,且“y”前面只有 4 个字母的学生的姓名,输入的 SQL 语句和执行结果如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE name LIKE '____y'; +-------+ | name  | +-------+ | Henry | +-------+ 1 row in set (0.00 sec)日期字段作为条件的查询语句以日期字段作为条件,可以使用比较运算符设置查询条件,也可以使用 BETWEEN AND 运算符查询某个范围内的值。BETWEEN AND 用来查询某个范围内的值,该操作符需要两个参数,即范围的开始值和结束值,若字段值满足指定的范围查询条件,则这些记录被返回。【实例 7】在表 tb_students_info 中查询注册日期在 2016-01-01 之前的学生的信息,输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info     -> WHERE login_date<'2016-01-01'; +----+-------+---------+------+------+--------+------------+ | id | name  | dept_id | age  | sex  | height | login_date | +----+-------+---------+------+------+--------+------------+ |  1 | Dany  |       1 |   25 | F    |    160 | 2015-09-10 | |  3 | Henry |       2 |   23 | M    |    185 | 2015-05-31 | |  6 | John  |       2 |   21 | M    |    172 | 2015-11-11 | |  8 | Susan |       4 |   23 | F    |    170 | 2015-10-01 | +----+-------+---------+------+------+--------+------------+ 4 rows in set (0.04 sec)【实例 8】在表 tb_students_info 中查询注册日期在 2015-10-01 和 2016-05-01 之间的学生的信息,输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info     -> WHERE login_date     -> BETWEEN '2015-10-01'     -> AND '2016-05-01'; +----+-------+---------+------+------+--------+------------+ | id | name  | dept_id | age  | sex  | height | login_date | +----+-------+---------+------+------+--------+------------+ |  5 | Jim   |       1 |   24 | M    |    175 | 2016-01-15 | |  6 | John  |       2 |   21 | M    |    172 | 2015-11-11 | |  7 | Lily  |       6 |   22 | F    |    165 | 2016-02-26 | |  8 | Susan |       4 |   23 | F    |    170 | 2015-10-01 | +----+-------+---------+------+------+--------+------------+ 4 rows in set (0.02 sec)
  • [技术干货] MySQL ORDER BY:对查询结果进行排序
    在 MySQL SELECT 语句中,ORDER BY 子句主要用来将结果集中的数据按照一定的顺序进行排序。其语法格式为:ORDER BY {语法说明如下。1) 列名指定用于排序的列。可以指定多个列,列名之间用逗号分隔。2) 表达式指定用于排序的表达式。3) 位置指定用于排序的列在 SELECT 语句结果集中的位置,通常是一个正整数。4) ASC|DESC关键字 ASC 表示按升序分组,关键字 DESC 表示按降序分组,其中 ASC 为默认值。这两个关键字必须位于对应的列名、表达式、列的位置之后。使用 ORDER BY 子句应该注意以下几个方面:ORDER BY 子句中可以包含子查询。当排序的值中存在空值时,ORDER BY 子句会将该空值作为最小值来对待。当在 ORDER BY 子句中指定多个列进行排序时,MySQL 会按照列的顺序从左到右依次进行排序。查询的数据并没有以一种特定的顺序显示,如果没有对它们进行排序,则将根据插入到数据表中的顺序显示。使用 ORDER BY 子句对指定的列数据进行排序。【实例 1】查询 tb_students_info 表的 height 字段值,并对其进行排序,输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info ORDER BY height; +----+--------+---------+------+------+--------+------------+ | id | name   | dept_id | age  | sex  | height | login_date | +----+--------+---------+------+------+--------+------------+ |  2 | Green  |       3 |   23 | F    |    158 | 2016-10-22 | |  1 | Dany   |       1 |   25 | F    |    160 | 2015-09-10 | |  4 | Jane   |       1 |   22 | F    |    162 | 2016-12-20 | |  7 | Lily   |       6 |   22 | F    |    165 | 2016-02-26 | | 10 | Tom    |       4 |   23 | M    |    165 | 2016-08-05 | |  8 | Susan  |       4 |   23 | F    |    170 | 2015-10-01 | |  6 | John   |       2 |   21 | M    |    172 | 2015-11-11 | |  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 | |  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 | |  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 | +----+--------+---------+------+------+--------+------------+ 10 rows in set (0.08 sec)该语句通过指定 ORDER BY 子句,MySQL 对查询的 height 列的数据按数值的大小进行了升序排序。有时需要根据多列进行排序。对多列数据进行排序要将需要排序的列之间用逗号隔开。【实例 2】查询 tb_students_info 表中的 name 和 height 字段,先按 height 排序,再按 name 排序,输入的 SQL 语句和执行结果如下所示。mysql> SELECT name,height     -> FROM tb_students_info     -> ORDER BY height,name; +--------+--------+ | name   | height | +--------+--------+ | Green  |    158 | | Dany   |    160 | | Jane   |    162 | | Lily   |    165 | | Tom    |    165 | | Susan  |    170 | | John   |    172 | | Jim    |    175 | | Thomas |    178 | | Henry  |    185 | +--------+--------+ 10 rows in set (0.09 sec)注意:在对多列进行排序时,首行排序的第一列必须有相同的列值,才会对第二列进行排序。如果第一列数据中所有的值都是唯一的,将不再对第二列进行排序。默认情况下,查询数据按字母升序进行排序(A~Z),但数据的排序并不仅限于此,还可以使用 ORDER BY 对查询结果进行降序排序(Z~A),这可以通过关键字 DESC 实现。可以对多列进行不同的顺序排序。【实例 3】查询 tb_students_info 表,先按 height 降序排序,再按 name 升序排序,输入的 SQL 语句和执行过程如下所示。mysql> SELECT name,height FROM tb_student_info ORDER BY height DESC,name ASC; +--------+--------+ | name   | height | +--------+--------+ | Henry  |    185 | | Thomas |    178 | | Jim    |    175 | | John   |    172 | | Susan  |    170 | | Lily   |    165 | | Tom    |    165 | | Jane   |    162 | | Dany   |    160 | | Green  |    158 | +--------+--------+ 10 rows in set (0.00 sec)注意:DESC 关键字只对前面的列进行降序排列,在这里只对 height 排序,而并没有对 name 进行排序,因此,height 按降序排序,而 name 仍按升序排序,如果要对多列进行降序排序,必须要在每一列的后面加 DESC 关键字。
  • [技术干货] MySQL ORDER BY:对查询结果进行排序
    在 MySQL SELECT 语句中,ORDER BY 子句主要用来将结果集中的数据按照一定的顺序进行排序。其语法格式为:ORDER BY {<列名> | <表达式> | <位置>} [ASC|DESC]语法说明如下。1) 列名指定用于排序的列。可以指定多个列,列名之间用逗号分隔。2) 表达式指定用于排序的表达式。3) 位置指定用于排序的列在 SELECT 语句结果集中的位置,通常是一个正整数。4) ASC|DESC关键字 ASC 表示按升序分组,关键字 DESC 表示按降序分组,其中 ASC 为默认值。这两个关键字必须位于对应的列名、表达式、列的位置之后。使用 ORDER BY 子句应该注意以下几个方面:ORDER BY 子句中可以包含子查询。当排序的值中存在空值时,ORDER BY 子句会将该空值作为最小值来对待。当在 ORDER BY 子句中指定多个列进行排序时,MySQL 会按照列的顺序从左到右依次进行排序。查询的数据并没有以一种特定的顺序显示,如果没有对它们进行排序,则将根据插入到数据表中的顺序显示。使用 ORDER BY 子句对指定的列数据进行排序。【实例 1】查询 tb_students_info 表的 height 字段值,并对其进行排序,输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info ORDER BY height; +----+--------+---------+------+------+--------+------------+ | id | name   | dept_id | age  | sex  | height | login_date | +----+--------+---------+------+------+--------+------------+ |  2 | Green  |       3 |   23 | F    |    158 | 2016-10-22 | |  1 | Dany   |       1 |   25 | F    |    160 | 2015-09-10 | |  4 | Jane   |       1 |   22 | F    |    162 | 2016-12-20 | |  7 | Lily   |       6 |   22 | F    |    165 | 2016-02-26 | | 10 | Tom    |       4 |   23 | M    |    165 | 2016-08-05 | |  8 | Susan  |       4 |   23 | F    |    170 | 2015-10-01 | |  6 | John   |       2 |   21 | M    |    172 | 2015-11-11 | |  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 | |  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 | |  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 | +----+--------+---------+------+------+--------+------------+ 10 rows in set (0.08 sec)该语句通过指定 ORDER BY 子句,MySQL 对查询的 height 列的数据按数值的大小进行了升序排序。有时需要根据多列进行排序。对多列数据进行排序要将需要排序的列之间用逗号隔开。【实例 2】查询 tb_students_info 表中的 name 和 height 字段,先按 height 排序,再按 name 排序,输入的 SQL 语句和执行结果如下所示。mysql> SELECT name,height     -> FROM tb_students_info     -> ORDER BY height,name; +--------+--------+ | name   | height | +--------+--------+ | Green  |    158 | | Dany   |    160 | | Jane   |    162 | | Lily   |    165 | | Tom    |    165 | | Susan  |    170 | | John   |    172 | | Jim    |    175 | | Thomas |    178 | | Henry  |    185 | +--------+--------+ 10 rows in set (0.09 sec)注意:在对多列进行排序时,首行排序的第一列必须有相同的列值,才会对第二列进行排序。如果第一列数据中所有的值都是唯一的,将不再对第二列进行排序。默认情况下,查询数据按字母升序进行排序(A~Z),但数据的排序并不仅限于此,还可以使用 ORDER BY 对查询结果进行降序排序(Z~A),这可以通过关键字 DESC 实现。可以对多列进行不同的顺序排序。【实例 3】查询 tb_students_info 表,先按 height 降序排序,再按 name 升序排序,输入的 SQL 语句和执行过程如下所示。mysql> SELECT name,height FROM tb_student_info ORDER BY height DESC,name ASC; +--------+--------+ | name   | height | +--------+--------+ | Henry  |    185 | | Thomas |    178 | | Jim    |    175 | | John   |    172 | | Susan  |    170 | | Lily   |    165 | | Tom    |    165 | | Jane   |    162 | | Dany   |    160 | | Green  |    158 | +--------+--------+ 10 rows in set (0.00 sec)注意:DESC 关键字只对前面的列进行降序排列,在这里只对 height 排序,而并没有对 name 进行排序,因此,height 按降序排序,而 name 仍按升序排序,如果要对多列进行降序排序,必须要在每一列的后面加 DESC 关键字。
  • [技术干货] SQL 列不同的表查询结果合并操作
    介绍下SQL列不同的表查询结果合并操作,具有很好的参考价值,希望对大家有所帮助两个不同的表进行查询,需要把结果合并,比如table1的列为 id, user_id, type_id,pro_id;table2的列为 id,user_id,collect_id;分别如下图所示table1:table2:将两个表的查询结果合并到一起的查询语句为select *, null as collect_id from table1 where user_id = 527 union select id,user_id,null as type_id,null as pro_id, collect_id from table2 where user_id = 527;结果为:其实就是把对应的列补充到没有该列的表中,在例子中就是把collect_id补充到table1中,把type_id,pro_id补充到table2中。补充知识:sql结果集合并用union all 不同表的列合并用join结果集合并用union all 不同表的列合并用joinSELECT "模块名", "事件编码", "点击数量", "使用时长(单位:分)" FROM (SELECT  T.fun_name as "模块名", T.event_code as "事件编码", SUM(click_records) as "点击数量" FROM  (SELECT m.* FROM default.daily_new_clientrpt_master m WHERE event_id in ( SELECT max(event_id) AS "事件" from default.daily_new_clientrpt_master group by user_name,fun_code ORDER BY "事件" DESC ) ) T where day = today() GROUP BY "模块名" ,"事件编码") T5 JOIN ( SELECT  T.fun_name as "模块名", T.event_code as "事件编码", round(sum(stay_time)/60000,0) as "使用时长(单位:分)" FROM  (SELECT m.* FROM default.daily_new_clientrpt_master m WHERE event_id in   (   SELECT "事件" FROM (  SELECT max(event_id) AS "事件", max(stay_time) AS "事件1" from default.daily_new_clientrpt_master group by user_name,fun_code ORDER BY "事件1" DESC) ) )   T where day = today() AND like(event_code,'%10000') GROUP BY "模块名" ,"事件编码" ) T6 ON T5."模块名"=T6."模块名" AND T5."事件编码"=T6."事件编码"
  • [问题求助] 请问是否有SQL查看存储过程(PROCEDURE)和函数(function)的DDL
    版本:6.5.1请问是否有SQL查看存储过程(PROCEDURE)和函数(function)的DDL
  • [技术干货] MySQL子查询详解
    子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从 MySQL 4.1 开始引入,在 SELECT 子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表。子查询中常用的操作符有 ANY(SOME)、ALL、IN 和 EXISTS。子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中,而且可以进行多层嵌套。子查询也可以使用比较运算符,如“<”、“<=”、“>”、“>=”、“!=”等。子查询中常用的运算符1) IN子查询结合关键字 IN 所使用的子查询主要用于判断一个给定值是否存在于子查询的结果集中。其语法格式为:<表达式> [NOT] IN <子查询>语法说明如下。<表达式>:用于指定表达式。当表达式与子查询返回的结果集中的某个值相等时,返回 TRUE,否则返回 FALSE;若使用关键字 NOT,则返回的值正好相反。<子查询>:用于指定子查询。这里的子查询只能返回一列数据。对于比较复杂的查询要求,可以使用 SELECT 语句实现子查询的多层嵌套。2) 比较运算符子查询比较运算符所使用的子查询主要用于对表达式的值和子查询返回的值进行比较运算。其语法格式为:<表达式> {= | < | > | >= | <= | <=> | < > | != }{ ALL | SOME | ANY} <子查询>语法说明如下。<子查询>:用于指定子查询。<表达式>:用于指定要进行比较的表达式。ALL、SOME 和 ANY:可选项。用于指定对比较运算的限制。其中,关键字 ALL 用于指定表达式需要与子查询结果集中的每个值都进行比较,当表达式与每个值都满足比较关系时,会返回 TRUE,否则返回 FALSE;关键字 SOME 和 ANY 是同义词,表示表达式只要与子查询结果集中的某个值满足比较关系,就返回 TRUE,否则返回 FALSE。3) EXIST子查询关键字 EXIST 所使用的子查询主要用于判断子查询的结果集是否为空。其语法格式为:EXIST <子查询>若子查询的结果集不为空,则返回 TRUE;否则返回 FALSE。子查询的应用【实例 1】在 tb_departments 表中查询 dept_type 为 A 的学院 ID,并根据学院 ID 查询该学院学生的名字,输入的 SQL 语句和执行结果如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE dept_id IN     -> (SELECT dept_id     -> FROM tb_departments     -> WHERE dept_type= 'A' ); +-------+ | name  | +-------+ | Dany  | | Henry | | Jane  | | Jim   | | John  | +-------+ 5 rows in set (0.01 sec)上述查询过程可以分步执行,首先内层子查询查出 tb_departments 表中符合条件的学院 ID,单独执行内查询,查询结果如下所示。mysql> SELECT dept_id     -> FROM tb_departments     -> WHERE dept_type='A'; +---------+ | dept_id | +---------+ |       1 | |       2 | +---------+ 2 rows in set (0.00 sec)可以看到,符合条件的 dept_id 列的值有两个:1 和 2。然后执行外层查询,在 tb_students_info 表中查询 dept_id 等于 1 或 2 的学生的名字。嵌套子查询语句还可以写为如下形式,可以实现相同的效果。mysql> SELECT name FROM tb_students_info     -> WHERE dept_id IN(1,2); +-------+ | name  | +-------+ | Dany  | | Henry | | Jane  | | Jim   | | John  | +-------+ 5 rows in set (0.03 sec)上例说明在处理 SELECT 语句时,MySQL 实际上执行了两个操作过程,即先执行内层子查询,再执行外层查询,内层子查询的结果作为外部查询的比较条件。【实例 2】与前一个例子类似,但是在 SELECT 语句中使用 NOT IN 关键字,输入的 SQL 语句和执行结果如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE dept_id NOT IN     -> (SELECT dept_id     -> FROM tb_departments     -> WHERE dept_type='A'); +--------+ | name   | +--------+ | Green  | | Lily   | | Susan  | | Thomas | | Tom    | +--------+ 5 rows in set (0.04 sec)提示:子查询的功能也可以通过连接查询完成,但是子查询使得 MySQL 代码更容易阅读和编写。【实例 3】在 tb_departments 表中查询 dept_name 等于“Computer”的学院 id,然后在 tb_students_info 表中查询所有该学院的学生的姓名,输入的 SQL 语句和执行过程如下所示。 mysql> SELECT name FROM tb_students_info     -> WHERE dept_id =     -> (SELECT dept_id     -> FROM tb_departments     -> WHERE dept_name='Computer'); +------+ | name | +------+ | Dany | | Jane | | Jim  | +------+ 3 rows in set (0.00 sec)【实例 4】在 tb_departments 表中查询 dept_name 不等于“Computer”的学院 id,然后在 tb_students_info 表中查询所有该学院的学生的姓名,输入的 SQL 语句和执行过程如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE dept_id <>     -> (SELECT dept_id     -> FROM tb_departments     -> WHERE dept_name='Computer'); +--------+ | name   | +--------+ | Green  | | Henry  | | John   | | Lily   | | Susan  | | Thomas | | Tom    | +--------+ 7 rows in set (0.00 sec)【实例 5】查询 tb_departments 表中是否存在 dept_id=1 的供应商,如果存在,就查询 tb_students_info 表中的记录,输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info     -> WHERE EXISTS     -> (SELECT dept_name     -> FROM tb_departments     -> WHERE dept_id=1); +----+--------+---------+------+------+--------+------------+ | id | name   | dept_id | age  | sex  | height | login_date | +----+--------+---------+------+------+--------+------------+ |  1 | Dany   |       1 |   25 | F    |    160 | 2015-09-10 | |  2 | Green  |       3 |   23 | F    |    158 | 2016-10-22 | |  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 | |  4 | Jane   |       1 |   22 | F    |    162 | 2016-12-20 | |  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 | |  6 | John   |       2 |   21 | M    |    172 | 2015-11-11 | |  7 | Lily   |       6 |   22 | F    |    165 | 2016-02-26 | |  8 | Susan  |       4 |   23 | F    |    170 | 2015-10-01 | |  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 | | 10 | Tom    |       4 |   23 | M    |    165 | 2016-08-05 | +----+--------+---------+------+------+--------+------------+ 10 rows in set (0.00 sec)由结果可以看到,内层查询结果表明 tb_departments 表中存在 dept_id=1 的记录,因此 EXSTS 表达式返回 TRUE,外层查询语句接收 TRUE 之后对表 tb_students_info 进行查询,返回所有的记录。EXISTS 关键字可以和条件表达式一起使用。【实例 6】查询 tb_departments 表中是否存在 dept_id=7 的供应商,如果存在,就查询 tb_students_info 表中的记录,输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info     -> WHERE EXISTS     -> (SELECT dept_name     -> FROM tb_departments     -> WHERE dept_id=7); Empty set (0.00 sec)
  • [技术干货] 详解MySQL的limit用法和分页查询语句的性能分析
    limit用法在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能。SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的便宜量是(0而不是1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1:mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.如果只给定一个参数,它表示返回最大的记录行数目:mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行换句话说,LIMIT n 等价于 LIMIT 0,n。换句话说,LIMIT  n  等价于Mysql的分页查询语句的性能分析MySql分页sql语句,如果和MSSQL的TOP语法相比,那么MySQL的LIMIT语法要显得优雅了许多。使用它来分页是再自然不过的事情了。最基本的分页方式:SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...在中小数据量的情况下,这样的SQL足够用了,唯一需要注意的问题就是确保使用了索引:举例来说,如果实际SQL类似下面语句,那么在category_id, id两列上建立复合索引比较好:SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10子查询的分页方式:随着数据量的增加,页数会越来越多,查看后几页的SQL就可能类似:SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10一言以蔽之,就是越往后分页。limit语句的偏移量就会越大,熟读就会明显变慢。此时,我们可以通过子查询的方式来提高分页效率,大致如下:SELECT * FROM articles WHERE id >=  (SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10JOIN分页方式SELECT * FROM `content` AS t1  JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2  WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;经过我的测试,join分页和子查询分页的效率基本在一个等级上,消耗的时间也基本一致。 explain SQL语句id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1  1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where 2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index为什么会这样呢?因为子查询是在索引上完成的,而普通的查询时在数据文件上完成的,通常来说,索引文件要比数据文件小得多,所以操作起来也会更有效率。实际可以利用类似策略模式的方式去处理分页,比如判断如果是一百页以内,就使用最基本的分页方式,大于一百页,则使用子查询的分页方式。
总条数:868 到第 页
上滑加载中