Oracle数据库查询十个小技巧

数据查询，是数据库操作中最主要的功能之一；有时候数据库查询性能的好坏，直接关系到数据库的运行效率，关系到数据库的选型。下面笔者不谈大道理，只是对其中对一些平时大家容易忽略的查询小技巧做一些总结。或许大家可能正在为此犯愁呢?

　 　第一个技巧：利用连接符连接多个字段。

　　如在员工基本信息表中，有员工姓名、员工职位、出身日期等等。如果现在视图中这三个字段显示在同一个字段中，并且中间有分割符。如我现在想显示的结果为“经理Victor出身于1976年5月3日”。这该如何处理呢?其实，这是比较简单的，我们可以在Select查询语句中，利用连接符把这些字段连接起来。

　　如可以这么写查询语句：

　　SELECT员工职位 ||’ ’ ||员工姓名||’出身于’||出身日期 as 员工出身信息 FROM 员工基本信息表;

　　通过这条语句就可以实现如上的需求。也就是说，我们在平时查询中，可以利用||连接符把一些相关的字段连接起来。这在报表视图中非常的有用。如笔者以前在设计图书馆管理系统的时候，在书的基本信息处有图书的出版社、出版序列号等等内容。但是，有时会在打印报表的时候，需要把这些字段合并成一个字段打印。为此，就需要利用这个连接符把这些字段连接起来。而且，利用连接符还可以在字段中间加入一些说明性的文字，以方便大家阅读。如上面我在员工职位与员工姓名之间加入了空格;并且在员工姓名与出身日期之间加入了出身于几个注释性的文字。这些功能看起来比较小，但是却可以大大的提高内容的可读性。这也是我们在数据库设计过程中需要关注的一个内容。

　　总之，令后采用连接符，可以提高我们报表的可读性于灵活性。

　 　第二个技巧：取消重复的行。

　　如在人事管理系统中，有员工基本信息基本表。在这张表中，可能会有部门、职位、员工姓名、身份证件号码等字段。若查询这些内容，可能不会有重复的行。但是，我若想知道，在公司内部设置了哪些部门与职位的时候，并且这些部门与职位配置了相关人员。此时，又该如何查询呢?

　　若我现在直接查询部门表，其可以知道系统中具体设置了哪些部门与职位。但是，很有可能这些部门或者职位由于人事变动的关系，现在已经没有人了。所以，这里查询出来的是所有的部门与职位信息，而不能够保证这个部门或者职位一定有职员存在。也就是说，这不能够满足于我们上面的要求。

　　若我现在直接从员工信息表中查询，虽然可以保证所查询出来的部门与职位信息，一定有员工信息的存在。但是，此时查询出来的部门与职位信息会有重复的行。如采购部门分工合作，可能会有采购采购小组长。此时，在查询出来的部门与职位的信息中，就会有三条重复的记录。

　　所以，以上两种处理方式，都不能够百分之百的满足企业用户的需求。此时，我们其实可以利用一个DISTINCT函数，来消除其中查询出来的重复行。

　　如我们可以利用SELECT DISTINCT 部门信息，职位信息 FROM 员工基本信息表。通过这条加了DISTINCT约束的查询语句，不但可以查询出所有有员工的职位与部门信息，而且，会把重复的记录过滤掉，从而提高可阅读性。

　　所以，在数据库设计过程中，特别是在查询语句的使用中，这个函数特别有用。

　 　第三个技巧：勤用WHERE语句。

　　我们都知道，数据库查询效率高不高，是我们评价数据库设计好坏的一个重要标准。毋庸置疑，在数据库查询中勤用Where条件语句，是提高数据库查询性能的一个很重要的手段之一。特别是在设计到比较大的表中查询符合条件的记录过程中，利用WHERE条件语句加以限制，可以大幅度的提高查询的响应速度。

　　如在图书馆管理系统中，现在有人想查询“注册会计师”辅导用书的时候，虽然不在书的类别或者名称中输入“注册会计师”，先查询出全部的纪录，然后再一条条的看是否有相关的书籍信息，也是可行的。但是，这么处理的话，一方面系统响应的速度会非常的慢，因为里面记录很多。另一方面，查询的结果看起来也会非常的头疼。

　　其实，我们只需要在查询中加入一些查询的参数，利用Where条件语句加以限制，则即可以提高数据库响应的速度，也可以找出最符合用户需求的数据。

　　另外，我也接触过一些在Oracle数据库上设计的平台型管理软件，他们可以自定义相关的报表。在报表设计中，只要用户在前台设计平台中，选中“大表查询”的话，则这个平台会在生成报表的时候，自动应用Where条件语句，以提高前台系统从数据库查询数据的效率。

　　所以，笔者认为在Oracle数据库系统设计中，要勤于使用Where语句。利用Where语句来提高数据库查询的效率。

第四个技巧：灵活使用COUNT函数。

　　在查询处理的时候，COUNT函数可以说是我们应用的比较多的函数之一。如我们有时候需要统计员工的人数、统计图书的种类数的时候，都需要使用到这个函数。不过，这个函数很多人可能会用，但是到灵活应用的地步，还是有一点差距。

　　下面笔者就COUNT函数的一些应用技巧谈谈自己的心得。

　　一是要灵活放置COUNT函数的位置，因为利用COUNT函数统计记录数的时候，是会考虑空行的记录的。如在数据表中一般有序列字段与其它的有意义字段两类。有时候可能序列字段中有内容而其它字段中没有内容，则在利用COUNT函数统计记录数量的时候，会把这个空记录也考虑进去。很明显，则就会发生统计的错误。所以，这个COUNT函数该放在哪个位置上，还是比较讲究的。一般的话，笔者试建议不要放在序列号字段上，而要放在一些关键的实体字段中。如统计员工人数的时候，则就可以放在员工姓名或者编号上等等。

　　二是灵活跟其它函数搭配使用。如在上面的例子中，笔者谈到有时候用户需要知道现在有员工编制的部门与职位有哪一些，我们可以利用DISTINCT函数来找出具体的部门。但是，我现在只想知道有编制的部门与职位具体有多少，此时，我们也可以利用COUNT 与DISTINCT函数结合应用，找出我们所需要的数据。在COUNT函数中，可以指定ALL与DISTINCT选项。默认的情况下，是ALL选项，表示统计所有的行，其中也包括重复的行。而DISTINCT就表示只统计不重复的行。可见，COUNT函数跟其它函数搭配使用的话，可以简化我们的查询语句，提高查询效率。

　 　第五个技巧：只查询时必须的字段。

　　有时候，用户不同的查询需求都要用到同一张表。如在员工信息表中包含了很多内容。有时候用户想要知道正式员工有多少;管理层员工有多少;生产线员工又有哪些;或者想知道合同即将到期的员工有哪些。为此，就遇到一个问题，因为这些内容基本上都是在同一张表中，那是在同一个视图中实现，而是根据需求不同，设计不同的视图呢?

　　若单从技术上考虑，两这都是可以实现的，不会有多大的难度。但是，若是从数据库性能上考虑在，则还是采用不同的视图来实现不同的需求为好。

　　一方面，若从安全方面讲，则可以根据不同的视图来控制相关的访问权限。可见，把视图细化，在权限控制上则会更加的灵活。

　　另一方面，数据的查询效率，跟数据内容的多少也有非常密切的关系。如在查询员工合同到期信息的时候，一般不需要员工的地址信息等等。若把这个信息也查询出来的话，由于这个字段比较长，就会花费比较长的时间。所以，在数据库设计中，我们要学会根据用户不同的需求，设计不同的视图。虽然可能这在设计的时候会比较花时间，但是，在确可以提高数据库的性能与安全性。这笔生意还是划得来的。

第六个技巧：合理处理NULL字段。

　　Null字段在数据库中是一个比较特殊的字段。Null字段表示未知值或者说缺少数据，注意若某个字段的值为Null，则这个字段即不是空格，也不是0。当插入记录的时候，若这个字段没有被赋值，而且也没有默认值的话，则这个字段系统默认给他的值就是“Null”。

　　由于这个值比较特殊，在查询的时候，及时经验丰富的数据库管理员，有时候在处理起来的时候，也会发生错误。为此，笔者在这里总结一些，在数据库查询的时候，关于这个空字段查询的一些需要注意的地方。

　　一是要注意NULL字段的数字运算问题。

　　如现在在一个薪资管理系统中，有一张薪资表，其中有基本工资与加班工资两个字段。若某个用户的基本工资为2000，而其加班工资没有。在输入这条记录的时候，由于加班工资这个字段中，没有输入数据，而且在数据库设计的时候，也没有个这个字段设置0的默认值。所以，当这条记录保存的时候，数据库系统会给这个字段自动赋值，这个字段的值就为NULL。

　　若我们用Select语句查询这条记录的时候，其加班工资这个字段显示的数据是空的。看起来好像是空格，而实际上其存储的不是空格。此时，我们若利用查询语句想知道，这个员工的总的工资(即加班工资加上基本工资)为多少的时候会有什么结果呢?

　　我们可以利用Select 员工姓名,基本工资,加班工资,基本工资+加班工资 as 总工资 FROM 员工薪资表; 我们可以通过这条语句来查询这个员工总的工资是多少。但是，这条语句会查询出我们想要的结果吗?我们执行一下这条语句，结果我们会发现，得出的结果跟我们想象的大相径庭。最后显示的总工资一栏中，为空格。

　　原来，Oracle数据库设计中，若一个NULL字段跟其他字段进行四则运算时，其显示的结果都为空。所以，若一个字段为NUU，则无论加减乘除，最后其结果都返回的施NULL值。这显然跟我们想象的不同。

　　针对这种情况，我们该如何处理呢?在数据库设计过程中，主要有两种处理方法。

　　一是在设计表的时候，对于这些需要参与运算的字段，要设置默认值。如可以把这个字段的默认值设置为0。则当添加这条记录的时候，即使前台用户没有给其设置值。在保存数据的时候，系统也会给其默认值0。如此的话，在进行四则运算的时候，才可能得到我们想要的值。

　　二是在查询的时候，需要考虑到这个NULL值的影响。有时候，若数据库中已经有记录，则不能够改变数据库字段的默认值。遇到这种情况，若我们需要对NULL字段与数字字段进行四则运算的时候，又该如何处理呢?此时，我们就需要在查询的时候，给NULL字段赋0的值。具体我们可以在查询语句中，如此定义。Select 员工姓名,基本工资,加班工资,基本工资+NVL(加班工资,0) as 总工资 FROM 员工薪资表;如此的话，当加班工资的值为NULL的时候，则系统在运算的时候，会把其当作0来处理。这么处理，我们就可以得到我们所想要的结果。不过一般情况下，这一种处理方式是不得已而为之的。最好的是，在数据库表设计的时候，就给相关的字段设置0的默认值。

　　另外，还有一个函数NVL2跟NVL函数功能类似，只是其多了一个参数而已，其表达式为NVL2(参数1，参数2，参数3)。它的含义是，当参数1不为空值时，则返回的值为参数2;当参数1是空值时，则范围的是参数3。若用这个函数实现NVL函数的目的时，则就需要如此改写上面这个案例的函数参数写法：NAV2 (加班工资，基本工资+加班工资，基本工资)。可见，两个函数有异曲同工之妙。具体采用哪种函数为好，则就需要根据数据库管理员的爱好来选择了。

　　二是如何查询NULL字段。

　　如果现在有一张员工基本信息表，其中有一个身份证号码的字段。现在若用户想知道，有哪些员工还没有记录身份证号码信息，该如何做呢?由于这个NULL字段不为空格或者0。若我们在查询条件语句中，利用’0’ 或者’’(空格)作为查询条件的话，是查不到我们所需要的结果的。此时，在数据库中，提供了一个专门用户查询NULL字段记录的函数IS NULL。若我们现在想知道哪些员工没有注明身份证信息时，就可以利用如下的语句来实现。

　　Select 员工姓名,身份证号码 from 员工基本信息表 where 身份证号码 is not null;

　　通过以上这条语句就可以实现查找身份证件为空的员工信息的目的。

　　第七个技巧：多多利用模糊查询。

　　在应用系统设计的时候，若让用户完整的输入全部查询条件，这个要求对于普通用户来说，过于苛刻。做过软件项目培训或者实施的时候，出于种种原因，用户在查询的时候，往往只输入部分的输入条件。遇到这种情况的时候，就需要在查询设计的时候，实现模糊查询。如此的话，即使用户输入的查询条件不全，也可以查询出相关的内容。

　　如用户在查询某个产品信息的时候，其可能不记得某个产品的具体名称，只知道其叫做端子，而其他具体的信息不清楚。此时，只需要在名称字段或者规格字段处输入端子，就可以从系统中查询中这两个字段中含有“端子”的纪录信息。如此的话，用户只需要在查询出的结果中继续查找即可。

　　这就告诫我们数据库管理人员，在数据库系统设计的时候，需要多用用模糊查询的功能。

　　具体的来说，需要注意以下几方面内容。

　　一是大小写不要进行区分。默认情况下，在数据库查询的时候，大小写是区分的。也就是说，现在有个字段内容为ABC，则我们查询abc的时候，就查不到这条记录。因为其大小写不同。而作为前段应用程序的用户来说，往往其在输入查询条件的时候，其并不会区分英文的大小写。虽然，不区分大小写也可以在前端应用程序中实现，不过，一般来说，在数据库中实现要比在前端系统中实现简单的多。故笔者是建议在后台数据库中实现这个大小写的自动转换功能。在查询的时候，不要区分大小写。这可能就是国内的特有国情吧。

　　二是要实现前后模糊查询。如现在有个字段的内容为“好好学习”，若我现在输入查询条件为学习、好好、好学等，都可以查到这条件记录。此时该如何设计查询的条件语句呢?这就是前后模糊查询的概念。其实，要实现这个功能也很简单。在前台查询语句设计的时候，当把查询条件传递给后台数据库系统的时候，在查询参数的前后，分别加入模糊查询的参数%即可。即我们若输入的查询条件语句为“好学”，则其传递给数据库的参数为“%好学%”。如此，就可以实现我们所需要的模糊查询。一般来说，模糊查询需要前台应用程序与后台数据库之间共同实现，这么处理起来，工作量会少许多;也会提高数据库的运行效率。

　　三是通过Beteen函数实现模糊查询。如现在有一个考勤系统，某个员工想知道自己在9月份自己的加班情况时，只需要在查询条件中，输入时间为9月1日到9月30日时，就可以查询到自己所需要的信息。这就是通过Beteen函数来实现模糊查询的。笔者以前碰到过一个平台型的ERP系统，他在这方面作的不错。这个产品就可以自己设计相关的报表。在报表设计中，在基于日期的查询条件，其即可以查询单个日期，而且还可以查询某个范围内的纪录。这就使利用了这个函数。另外，可以实现模糊查询的函数还有IN函数等等。作为数据库管理人员，需要从提高数据库的查询性能角度出发，合理选择这些模糊查询函数。并且，还需要配合前台应用程序设计，处理好模糊查询的功能。

第八个技巧：慎用Like等通配符。

　　Like关键字，从技术上来说，是一个非常友善的通配符。利用这个通配符，我们可以实现很多模糊查询。如现在在一个人事档案系统中，用户想知道身份证号码以“339005”开头的人事信息，此时，就可以利用Like语句实现。我们可以利用下面的条件语句，实现我们的需求，“where 身份证号码 like ‘339005%’”。通过这个条件语句，可以查到所有身份证以339005开头的号码。

　　但是，当用户在一张大表中采用这个LIKE语句的话，就会发现这个查询语句的运行效率非常的慢。这是什么原因造成的呢?其实，不管是Like 关键字，若采用MATCHES关键字的话，若在大量数据中查找符合条件的记录，则其运行效率也比较低。这主要是其技术特性所造成的。

　　Like与Matches两个关键字，其支持通配符匹配。在有些专业书籍上把这个叫做“正规表达式”。不过由于在利用这些关键字查询的时候， 数据库系统不是通过索引来查询，而是采用顺序扫描的方式来查询。显然，真是这种技术特性，造成了Like与Mateches两个关键字查询效率的低下。特别是在复杂查询或者大表查询中，用户可以明显感觉到速度比较慢。

　　索引是数据库中的一个重要的数据结构。索引如果利用的合理的话，可以大幅度的提升数据库的查询性能。一般情况下，我们在数据库设计的时候，要充分的利用索引，来提高数据库的运行效率。如对于一些经常需要用到的查询功能，我们需要为没有指定外键的列建立索引;如有查询大表数据，而且又需根据好几个字段的值对其进行排序，也需要在这些列上建立复合索引。特别是在一些应用系统上，往往可以按以下字段的名称，就会对这个字段进行排序。遇到这种情况的话，更加需要在这些频繁进行排序的列上建立索引，以提高重新排序的效率。可见，若在查询的时候，若不能利用索引提高查询效率的话，则就好像跑车失去四轮驱动，速度会大受影响。

　　所以，在数据库系统设计中，要尽量避免采用Like或者Matche关键字。有时候，我们可以利用其他运算符号来代替。如我们可以利用〉(大于)或者<(小于)符号来达到类似的需求。若真的要采用这两个关键字的话，则就需要做好查询优化方面的工作。如不要在基础表中直接利用这个两个关键字，而是通过报表视图、或者临时表等来查询，以减少其不良影响。

　　 第九个技巧：利用注释提高查询语句的可读性。

　　在数据库设计中，有一个非常奇怪的现象。一些专家级的数据库设计人员，在写查询语句的时候，非对语句进行详细的注释。有时会，注释的内容大大超过了查询代码本身的篇幅。可是，往往一些入门不久的数据库设计人员，不喜欢写注释语句。这是一个很反常的现象。

　　笔者刚开始接触数据库的时候，也不喜欢写注释语句。觉得写注释语句太浪费时间。但是，一个偶然的事件让笔者改掉了这个坏习惯。那时笔者在观摩一个专家设计数据库的时候，被其密密麻麻的注释惊呆了。看了其代码之后，笔者可以非常轻松的阅读完其所有的代码。不愧为是专家级的人物。看了他的注释之后，在看看自己编写的代码注视，那真是大巫见小巫了。从此之后，笔者也在慢慢培养自己编写代码的习惯。现在笔者在数据库设计的时候，注释已经写的很详细了。至少笔者的同事在看到我的注释之后，不用看源代码就知道笔者要实现的目的了。

　　在编写注释的时候，要注意几个问题。

　　一是注释越详细越好。其实，注释最多牺牲一点磁盘空间，而不会对数据库的性能产生任何不良的影响。相反，注释详细的话，对于后续数据库维护与管理、系统二次开发的等等，都会提供很大的帮助。

　　二是最好采用英文注释。若采用中文注释的话，有时候数据库语言设置不当，在数据库实例安装的时候，不会把中文的注释带过去。所以，作为数据库管理员，要有一定的英语基础，学会利用英语写注释。其实，这也不是很难。只要多看看别人设计的数据库注释，把他们常用的注释复制下来。通过选择合适的进行复制、粘贴就可以完成任务。

　　三是不仅在写查询语句的时候，要做好代码的注释工作。在编写其他代码的时候，如过程、函数等等，也要添加详细的代码。以增加这些复杂功能的可读性。

　　四是注释的内容。一般注释应该包含如下几方面的内容。一是这段代码要实现的功能;二是这段代码需要调用的参数;三是这段代码输出的结果。若是多表关联查询的话，最好能够说明表之间的对应关系。若在查询语句中，直接调用了函数的话，则最好能够注明这个函数的功能;等等。终止一个原则就是，让其他人看到这个注释，不再需要去查询其他的资料，就可以明白这段代码的含义。

　　详细的注释内容，不仅不会降低数据库的运行性能，而且还可以提高数据库的管理与维护的效率;同时也可以加快前台应用程序开发设计的速度。又因为查询语句是系统中利用的最多的语句，也是引用的最频繁的语句。故在查询语句中，更加需要做好相关的注释。

　 　第十个技巧：必要的时候，限制用户所使用的行。

　　在使用一些大型管理系统的时候，如ERP系统。我们若查询产品信息，默认的情况下，若记录比较多的时候，其不会把所有的记录都查询出来。如在ERP系统的数据库中，其有20000个产品信息。而端子类的产品信息就占据到1000个。此时，我们在查询条件中，若产品类别限制为“端子”的话，则其查询出来的结果也可能不是所有的端子。默认显示的话，可能只有前面的100个端子类产品。若用户需要看到全部的产品信息，就需要点击“显示全部”按钮，才可以显示出全部的信息。

　　其实，不管是一些应用程序如此设计，在Oracle数据库中，本身也有这方面的限制。如直接在PL/SQL客户端中查询数据的话，其显示的记录默认情况下也是有限制的，而不会把所有符合条件的语句查询出来。若用户需要查询所有符合条件的记录，则需要点击“继续”按钮，以让数据库显示所有的记录。

　　为什么要做类似的限制呢?这主要就是为了提高数据库查询的性能。我们直接在数据库服务器上，在几百万条记录中查询的话，显示几百条记录跟现实几千条记录所花费的时间明显不同。前者可能只需要3秒即可。而后者可能需要1分钟。所以，为了减少用户等待的时间，我们往往需要限制首次查询默认显示的记录数字。

　　如我们往往在查询语句中，利用top 100 来让数据库只显示前100条记录。如此的话，可以明显的缩短用户的等待时间。默认情况下，是根据记录创建的时间顺序，来显示记录的。最迟创建的记录，其显示在最前。以此类推。

　　当用户需要的数据在前面100条之内，则就不需要再查看其他记录了。相反，若不在的话，则就需要查询全部记录信息了。

转载于:https://www.cnblogs.com/oracle-dsg/archive/2010/06/12/1757404.html