仓酷云

标题: MSSQL网站制作之利用正轨表达式编写更好的 SQL [打印本页]

作者: 灵魂腐蚀 时间: 2015-1-16 22:42
标题: MSSQL网站制作之利用正轨表达式编写更好的 SQL
如果某个数据列里包含许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含的净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。
OracleDatabase10g中的正轨表达式特征是一个用于处置文本数据的壮大工具

OracleDatabase10g的一个新特征年夜年夜进步了您搜刮和处置字符数据的才能。这个特征就是正轨表达式，是一种用来形貌文本形式的暗示办法。好久以来它已在很多编程言语和大批UNIX有用工具中呈现过了。

Oracle的正轨表达式的实行是以各类SQL函数和一个WHERE子句操纵符的情势呈现的。假如您不熟习正轨表达式，那末这篇文章可让您懂得一下这类新的极为壮大但是外表上有点奥秘的功效。已对正轨表达式很熟习的读者能够懂得怎样在OracleSQL言语的情况中使用这类功效。
甚么是正轨表达式？
正轨表达式由一个或多个字符型笔墨和/或元字符构成。在最复杂的格局下，正轨表达式仅由字符笔墨构成，如正轨表达式cat。它被读作字母c，接着是字母a和t，这类形式婚配cat、location和catalog之类的字符串。元字符供应算法来断定Oracle怎样处置构成一个正轨表达式的字符。当您懂得了各类元字符的寄义时，您将体味到正轨表达式用于查找和交换特定的文本数据长短常壮大的。

考证数据、辨认反复关头字的呈现、检测不用要的空格，或剖析字符串只是正轨表达式的很多使用中的一部分。您能够用它们来考证德律风号码、邮政编码、电子邮件地点、社会平安号码、IP地点、文件名和路径名等的格局。别的，您能够查找如HTML标志、数字、日期之类的形式，或恣意文本数据中切合恣意形式的任何事物，并用别的的形式来交换它们。
用OracleDatabase10g利用正轨表达式
您可使用最新引进的OracleSQLREGEXP_LIKE操纵符和REGEXP_INSTR、REGEXP_SUBSTR和REGEXP_REPLACE函数来发扬正轨表达式的感化。您将体味到这个新的功效怎样对LIKE操纵符和INSTR、SUBSTR和REPLACE函数举行了增补。实践上，它们相似于已有的操纵符，但如今增添了壮大的形式婚配功效。被搜刮的数据能够是复杂的字符串或是存储在数据库字符列中的大批文本。正轨表达式让您可以以一种您之前从未想过的体例来搜刮、交换和考证数据，并供应高度的天真性。
正轨表达式的基础例子
在利用这个新功效之前，您必要懂得一些元字符的寄义。句号(.)婚配一个正轨表达式中的恣意字符（除换行符）。比方，正轨表达式a.b婚配的字符串中起首包括字母a，接着是别的恣意单个字符（除换行符），再接着是字母b。字符串axb、xaybx和abba都与之婚配，由于在字符串中埋没了这类形式。假如您想要准确地婚配以a开首和以b开头的一条三个字母的字符串，则您必需对正轨表达式举行定位。脱字标记(^)元字符唆使一行的入手下手，而美圆标记($)唆使一行的开头（拜见表1）。因而，正轨表达式^a.b$婚配字符串aab、abb或axb。将这类体例与LIKE操纵符供应的相似的形式婚配a_b比拟较，个中(_)是单字符通配符。

默许情形下，一个正轨表达式中的一个独自的字符或字符列表只婚配一次。为了唆使在一个正轨表达式中屡次呈现的一个字符，您可使用一个量词，它也被称为反复操纵符。.假如您想要失掉从字母a入手下手并以字母b停止的婚配形式，则您的正轨表达式看起来像如许：^a.*b$。*元字符反复后面的元字符(.)唆使的婚配零次、一次或更屡次。LIKE操纵符的等价的形式是a%b，个中用百分号(%)来唆使恣意字符呈现零次、一次或屡次。

表2给出了反复操纵符的完全列表。注重它包括了特别的反复选项，它们完成了比现有的LIKE通配符更年夜的天真性。假如您用圆括号括住一个表达式，这将无效地创立一个能够反复必定次数的子表达式。比方，正轨表达式b(an)*a婚配ba、bana、banana、yourbananasplit等。

Oracle的正轨表达式实行撑持POSIX（可移植操纵体系接口）字符类，拜见表3中列出的内容。这意味着您要查找的字符范例能够十分出格。假定您要编写一条仅查找非字母字符的LIKE前提—作为了局的WHERE子句大概不经意就会变得十分庞大。

POSIX字符类必需包括在一个由方括号([])唆使的字符列表中。比方，正轨表达式[[:lower:]]婚配一个小写字母字符，而[[:lower:]]{5}婚配五个一连的小写字母字符。

除POSIX字符类以外，您能够将独自的字符放在一个字符列表中。比方，正轨表达式^ab[cd]ef$婚配字符串abcef和abdef。必需选择c或d。

除脱字符(^)和连字符(-)以外，字符列表中的年夜多半元字符被以为是笔墨。正轨表达式看起来很庞大，这是由于一些元字符具有随高低文情况而定的多重寄义。^就是如许一种元字符。假如您用它作为一个字符列表的第一个字符，它代表一个字符列表的非。因而，[^[:digit:]]查找包括了恣意非数字字符的形式，而^[[:digit:]]查找以数字入手下手的婚配形式。连字符(-)唆使一个局限，正轨表达式[a-m]婚配字母a到字母m之间的恣意字母。但假如它是一个字符行中的第一个字符（如在[-afg]中），则它就代表连字符。

之前的一个例子先容了利用圆括号来创立一个子表达式；它们同意您经由过程输出更替元字符来输出可更替的选项，这些元字符由竖线(|)分隔。

比方，正轨表达式t(a|e|i)n同意字母t和n之间的三种大概的字符更替。婚配形式包含如tan、ten、tin和Pakistan之类的字，但不包含teen、mountain或tune。作为另外一种选择，正轨表达式t(a|e|i)n也能够暗示为一个字符列表t[aei]n。表4汇总了这些元字符。固然存在更多的元字符，但这个简明的概述充足用来了解这篇文章利用的正轨表达式。
REGEXP_LIKE操纵符
REGEXP_LIKE操纵符向您先容在Oracle数据库中利用时的正轨表达式功效。表5列出了REGEXP_LIKE的语法。

上面的SQL查询的WHERE子句显现了REGEXP_LIKE操纵符，它在ZIP列中搜刮满意正轨表达式[^[:digit:]]的形式。它将检索ZIPCODE表中的那些ZIP列值包括了恣意非数字字符的行。

SELECTzipFROMzipcodeWHEREREGEXP_LIKE(zip,[^[:digit:]])ZIP-----ab123123xy007ababcxy

这个正轨表达式的例子仅由元字符构成，更详细来说是被冒号和方括号分开的POSIX字符类digit。第二组方括号（如[^[:digit:]]中所示）包含了一个字符类列表。如前文所述，必要如许做是由于您只能够将POSIX字符类用于构建一个字符列表。
REGEXP_INSTR函数
这个函数前往一个形式的肇端地位，因而它的功效十分相似于INSTR函数。新的REGEXP_INSTR函数的语法在表6中给出。这两个函数之间的次要区分是，REGEXP_INSTR让您指定一种形式，而不是一个特定的搜刮字符串；因此它供应了更多的功效。接上去的示例利用REGEXP_INSTR来前往字符串JoeSmith,10045BerryLane,SanJoseph,CA91234中的五位邮政编码形式的肇端地位。假如正轨表达式被写为[[:digit:]]{5}，则您将失掉门商标的肇端地位而不是邮政编码的，由于10045是第一次呈现五个一连数字。因而，您必需将表达式定位到该行的开端，正如$元字符所示，该函数将显现邮政编码的肇端地位，而不论门商标的数字个数。

SELECTREGEXP_INSTR(JoeSmith,10045BerryLane,SanJoseph,CA91234,[[:digit:]]{5}$)ASrx_instrFROMdualRX_INSTR----------45
编写更庞大的形式
让我们在前一个例子的邮政编码形式上睁开，以便包括一个可选的四位数字形式。您的形式如今大概看起来像如许：[[:digit:]]{5}(-[[:digit:]]{4})?$。假如您的源字符串以5位邮政编码或5位+4位邮政编码的格局停止，则您将可以显现该形式的肇端地位。

SELECTREGEXP_INSTR(JoeSmith,10045BerryLane,SanJoseph,CA91234-1234,[[:digit:]]{5}(-[[:digit:]]{4})?$)ASstarts_atFROMdualSTARTS_AT----------44

在这个示例中，括弧里的子表达式(-[[:digit:]]{4})将按?反复操纵符的唆使反复零次或一次。别的，妄图用传统的SQL函数来完成不异的了局乃至对SQL专家也是一个应战。为了更好地申明这个正轨表达式示例的分歧构成部分，表7包括了一个对单个笔墨和元字符的形貌。
REGEXP_SUBSTR函数
相似于SUBSTR函数的REGEXP_SUBSTR函数用来提取一个字符串的一部分。表8显现了这个新函数的语法。鄙人面的示例中，婚配形式[^,]*的字符串将被前往。该正轨表达式搜刮厥后紧随着空格的一个逗号；然后按[^,]*的唆使搜刮零个或更多个不是逗号的字符，最初查找另外一个逗号。这类形式看起来有点像一个用逗号分开的值字符串。

SELECTREGEXP_SUBSTR(firstfield,secondfield,thirdfield,,[^,]*,)FROMdualREGEXP_SUBSTR(FIR------------------,secondfield,
REGEXP_REPLACE函数
让我们起首看一下传统的REPLACESQL函数，它把一个字符串用另外一个字符串来交换。假定您的数据在注释中有不用要的空格，您但愿用单个空格来交换它们。使用REPLACE函数，您必要正确地列出您要交换几个空格。但是，过剩空格的数量在注释的遍地大概不是不异的。上面的示例在Joe和Smith之间有三个空格。REPLACE函数的参数指定要用一个空格来交换两个空格。在这类情形下，了局在本来的字符串的Joe和Smith之间留下了一个分外的空格。

SELECTREPLACE(JoeSmith,,)ASreplaceFROMdualREPLACE---------JoeSmith

REGEXP_REPLACE函数把交换功效向前促进了一步，其语法在表9中列出。以下查询用单个空格交换了恣意两个或更多的空格。()子表达式包括了单个空格，它能够按{2,}的唆使反复两次或更屡次。

SELECTREGEXP_REPLACE(JoeSmith,(){2,},)ASRX_REPLACEFROMdualRX_REPLACE----------JoeSmith
后向援用
正则表达式的一个有效的特征是可以存储子表达式供今后重用；这也被称为后向援用（在表10中对其举行了概述）。它同意庞大的交换功效，如在新的地位上互换形式或显现反复呈现的单词或字母。子表达式的婚配部分保留在一时缓冲区中。缓冲区从左至右举行编号，并使用digit标记举行会见，个中digit是1到9之间的一个数字，它婚配第digit个子表达式，子表达式用一组圆括号来显现。

接上去的例子显现了经由过程按编号援用各个子表达式将姓名EllenHildiSmith变化为Smith,EllenHildi。

SELECTREGEXP_REPLACE(EllenHildiSmith,(.*)(.*)(.*),3,12)FROMdualREGEXP_REPLACE(EL------------------Smith,EllenHildi

该SQL语句显现了用圆括号括住的三个独自的子表达式。每个独自的子表达式包括一个婚配元字符(.)，并紧随着*元字符，暗示任何字符（除换行符以外）都必需婚配零次或更屡次。空格将各个子表达式分隔，空格也必需婚配。圆括号创立猎取值的子表达式，而且能够用digit来援用。第一个子表达式被赋值为1，第二个2，以此类推。这些后向援用被用在这个函数的最初一个参数(3,12)中，这个函数无效地前往了交换子字符串，并定期看的格局来分列它们（包含逗号和空格）。表11具体申明了该正则表达式的各个构成部分。

后向援用对交换、格局化和取代值十分有效，而且您能够用它们来查找相邻呈现的值。接上去的例子显现了利用REGEP_SUBSTR函数来查找恣意被空格离隔的反复呈现的字母数字值。显现的了局给出了辨认反复呈现的单词is的子字符串。

SELECTREGEXP_SUBSTR(Thefinaltestisistheimplementation,([[:alnum:]]+)([[:space:]]+)1)ASsubstrFROMdualSUBSTR------isis
婚配参数选项
您大概已注重到了正则表达式操纵符和函数包括一个可选的婚配参数。这个参数把持是不是辨别巨细写、换行符的婚配和保存多行输出。
正则表达式的实践使用
您不但能够在行列中利用正则表达式，还能够在利用SQL操纵符或函数的任何中央（好比说在PL/SQL言语中）利用正则表达式。您能够编写使用正则表达式功效的触发器，以考证、天生或提取值。

接上去的例子演示了您怎样可以在一次列反省束缚前提中使用REGEXP_LIKE操纵符来举行数据考证。它在拔出或更新时查验准确的社会保险号码格局。如123-45-6789和123456789之类格局的社会保险号码关于这类列束缚前提是可承受的值。无效的数据必需以三个数字入手下手，紧随着一个连字符，再加两个数字和一个连字符，最初又是四个数字。另外一种表达式只同意9个一连的数字。竖线标记(|)将各个选项分隔。

ALTERTABLEstudentsADDCONSTRAINTstud_ssn_ckCHECK(REGEXP_LIKE(ssn,^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$))

由^和$唆使的开首或开头的字符都是不成承受的。确保您的正则表达式没有分红多行或包括任何不用要的空格，除非您但愿格局云云并响应地举行婚配。表12申明了该正则表达式示例的各个构成部分。
将正则表达式与现有的功效举行对照
正则表达式有几个长处优于罕见的LIKE操纵符和INSTR、SUBSTR及REPLACE函数的。这些传统的SQL函数方便于举行形式婚配。只要LIKE操纵符经由过程利用%和_字符婚配，但LIKE不撑持表达式的反复、庞大的更替、字符局限、字符列表和POSIX字符类等等。别的，新的正则表达式函数同意检测反复呈现的单词和形式互换。这里的例子为您供应了正则表达式范畴的一个概览，和您怎样可以在您的使用程序中利用它们。
实其实在地丰厚您的工具包
由于正则表达式有助于办理庞大的成绩，以是它们长短常壮大的。正则表达式的一些功效难于用传统的SQL函数来仿效。当您懂得了这类稍显奥秘的言语的基本构建程序块时，正则表达式将成为您的工具包的不成短少的一部分（不但在SQL情况下也在别的的编程言语情况下）。为了使您的各个形式准确，固然实验和毛病偶然是必需的，但正则表达式的简便和壮大是无可置疑的。

AliceRischert(ar280@yahoo.com)是哥伦比亚年夜学盘算机手艺与使用系的数据库使用程序开辟和计划偏向的主席。她编写了OracleSQL交互手册第2版（PrenticeHall，2002）和行将推出的OracleSQL示例（PrenticeHall，2003）。Rischert具有凌驾15年的履历在财产100强公司内担当数据库计划师、DBA和项目主管，而且她自从Oracleversion5起就一向利用Oracle产物。

表1：定位元字符
元字符申明^使表达式定位至一行的开首$使表达式定位至一行的开端

表2：量词或反复操纵符量词申明*婚配0次或更屡次?婚配0次或1次+婚配1次或更屡次{m}恰好婚配m次{m,}最少婚配m次{m,n}最少婚配m次但不凌驾n次

表3：预界说的POSIX字符类字符类申明[:alpha:]字母字符[:lower:]小写字母字符[:upper:]年夜写字母字符[:digit:]数字[:alnum:]字母数字字符[:space:]空缺字符（克制打印），如回车符、换行符、竖直制表符和换页符[:punct:]标点字符[:cntrl:]把持字符（克制打印）[:print:]可打印字符

表4：表达式的交换婚配和分组元字符申明|交换分开交换选项，一般与分组操纵符()一同利用()分组将子表达式分组为一个交换单位、量词单位或后向援用单位（拜见“后向援用”部分）[char]字符列表暗示一个字符列表；一个字符列表中的年夜多半元字符（除字符类、^和-元字符以外）被了解为笔墨

表5：REGEXP_LIKE操纵符语法申明REGEXP_LIKE(source_string,pattern[,match_parameter])source_string撑持字符数据范例（CHAR、VARCHAR2、CLOB、NCHAR、NVARCHAR2和NCLOB，但不包含LONG）。pattern参数是正则表达式的另外一个称号。match_parameter同意可选的参数（如处置换行符、保存多行格局化和供应对辨别巨细写的把持）。

表6：REGEXP_INSTR函数语法申明REGEXP_INSTR(source_string,pattern[,start_position[,occurrence[,return_option[,match_parameter]]]])该函数查找pattern，并前往该形式的第一个地位。您能够随便指定您想要入手下手搜刮的start_position。occurrence参数默许为1，除非您指定您要查找接上去呈现的一个形式。return_option的默许值为0，它前往该形式的肇端地位；值为1则前往切合婚配前提的下一个字符的肇端地位。

表7：5位数字加4位邮政编码表达式的申明语法申明必需婚配的空缺[:digit:]POSIX数字类]字符列表的开头{5}字符列表恰好反复呈现5次(子表达式的开首-一个笔墨连字符，由于它不是一个字符列表内的局限元字符[字符列表的开首[:digit:]POSIX[:digit:]类[字符列表的开首]字符列表的开头{4}字符列表恰好反复呈现4次)停止圆括号，停止子表达式??量词婚配分组的子表达式0或1次，从而使得4位代码可选$定位元字符，唆使行尾

表8：REGEXP_SUBSTR函数语法申明REGEXP_SUBSTR(source_string,pattern[,position[,occurrence[,match_parameter]]])REGEXP_SUBSTR函数前往婚配形式的子字符串。

表9：REGEXP_REPLACE函数语法申明REGEXP_REPLACE(source_string,pattern[,replace_string[,position[,occurrence,[match_parameter]]]])该函数用一个指定的replace_string来交换婚配的形式，从而同意庞大的“搜刮并交换”操纵。

表10：后向援用元字符元字符申明digit反斜线紧随着一个1到9之间的数字，反斜线婚配之前的用括号括起来的第digit个子表达式。（注重：反斜线在正则表达式中有另外一种意义，取决于高低文，它还大概暗示Escape字符。

表11：形式互换正则表达式的申明正则表达式项目申明(第一个子表达式的开首.婚配除换行符以外的恣意单字符*反复操纵符，婚配之前的.元字符0到n次)第一个子表达式的开头；婚配了局在1中猎取（在这个例子中，了局为Ellen。）必需存在的空缺(第二个子表达式的开首.婚配除换行符以外的恣意单个字符*反复操纵符，婚配之前的.元字符0到n次)第二个子表达式的开头；婚配了局在2中猎取（在这个例子中，了局为Hildi。）空缺(第三个子表达式的开首.婚配除换行符以外的恣意单字符*反复操纵符，婚配之前的.元字符0到n次)第三个子表达式的开头；婚配了局在3中猎取（在这个例子中，了局为Smith。）

表12：社会保险号码正则表达式的申明正则表达式项目申明^行首字符（正则表达式在婚配之前不克不及有任何前导字符。）(入手下手子表达式并列出用|元字符分隔的可交换选项[字符列表的开首[:digit:]POSIX数字类]字符列表的开头{3}字符列表恰好反复呈现3次-连字符[字符列表的开首[:digit:]POSIX数字类]字符列表的开头{2}字符列表恰好反复呈现2次-另外一个连字符[字符列表的开首[:digit:]POSIX数字类]字符列表的开头{4}字符列表恰好反复呈现4次|交换元字符；停止第一个选项并入手下手下一个交换表达式[字符列表的开首[:digit:]POSIX数字类]字符列表的开头{9}字符列表恰好反复呈现9次)停止圆括号，停止用于交换的子表达式组$定位元字符，唆使行尾；没有分外的字符可以切合形式
CSV逻辑上由逗号分割数据的存储引擎

作者: 爱飞 时间: 2015-1-19 21:32
但换公司用MSSQL2K感觉自己好像根本就不了解MSSQL。什么DTS触发器以前根本没用过。

作者: 因胸联盟 时间: 2015-2-5 20:52
一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。）

作者: 谁可相欹 时间: 2015-2-13 14:31
备份方面可能还是一个老大难的问题。不能单独备份几个表总是感觉不爽。灵活备份的问题不知道什么时候才能解决。

作者: 飘灵儿 时间: 2015-3-3 22:43
可以动态传入参数，省却了动态SQL的拼写。

作者: 蒙在股里 时间: 2015-3-11 14:27
以前的DTS轻盈简单。但是现在的SSIS虽然功能强大了很多，但是总是让人感觉太麻烦。看看论坛中询问SSIS的贴子就知道。做的功能太强大了，往往会有很多用户不会用了

作者: 海妖 时间: 2015-3-18 23:25
这一点很好的加强了profiler的功能。但是提到profiler提醒大家注意一点。windows2003要安装sp1补丁才能启动profiler。否则点击没有反应。

作者: 深爱那片海 时间: 2015-3-26 19:13
我们学到了什么？思考问题的时候从表的角度来思考问

欢迎光临仓酷云 (http://ckuyun.com/)