淘宝网开发人员数据库知识手册

淘宝网开发人员数据库知识参考手

册

编写、修订、审核记录编号文档版本修订章节修订原因修订日期修订人11.0全文新建文档2007-9-27胜通22.0补充文档2008-1-6胜通345678910目录TOC\o"1-5"\h\z\o"CurrentDocument"第一章基本元素 6\o"CurrentDocument"第一节数据类型 6\o"CurrentDocument"1. VARCHAR2(size)： 6\o"CurrentDocument"LONG 7\o"CurrentDocument"CLOB 7\o"CurrentDocument"DATE 8\o"CurrentDocument"TIMESTAMP(factional_seconds_precision) 8\o"CurrentDocument"第二节NULL 8\o"CurrentDocument"第三节伪列 11\o"CurrentDocument"CURRVAL和NEXTVAL 11\o"CurrentDocument"LEVEL 12\o"CurrentDocument"ROWNUM 13\o"CurrentDocument"第四节注释 15\o"CurrentDocument"第五节数据库的对象 16\o"CurrentDocument"1. 模式对象 163. 部分模式对彖 17第六节格式模型 17第七节操作符 19操作符列表 19操作符优先级 19连接操作符 19集合操作符 19第二章表达式 19第三章条件 19\o"CurrentDocument"第一节条件的优先级 20\o"CurrentDocument"第二节比较条件 21第三节逻辑条件 23第四节成员关系条件 23第五节范围条件 23第六节NULL条件 23\o"CurrentDocument"第四章函数 23第一节单值函数 23\o"CurrentDocument"数值型函数 24<MOD 24\o"CurrentDocument"。ROUND 24\o"CurrentDocument"令 ACOS 26\o"CurrentDocument"◊ ATAN 27◊ BIN_TO_NUM 29令 BITAND 29令 CEIL 29◊ COS 29◊ FLOOR 29\o"CurrentDocument"令 SIN 29令 SINH 29\o"CurrentDocument"返回字符值的字符型函数 29\o"CurrentDocument"UPPER 30\o"CurrentDocument"RPAD 31\o"CurrentDocument"LTRIM 32\o"CurrentDocument"REPLACE 33\o"CurrentDocument"SOUNDEX 33\o"CurrentDocument"SUBSTR 34\o"CurrentDocument"TRANSLATE 3536\o"CurrentDocument"返回数字值的字符型函数 36\o"CurrentDocument"令 INSTR 36◊ 37\o"CurrentDocument"日期函数 37\o"CurrentDocument"ADD_MONTHS 38\o"CurrentDocument"CURRENT_DATE 39\o"CurrentDocument"CURRENT.TIMESTAMP 40\o"CurrentDocument"SYSDATE 40\o"CurrentDocument"SYSTIMESTAMP 41\o"CurrentDocument"NEXT_DAY 42\o"CurrentDocument"MONTHS.BETWEEN 42\o"CurrentDocument"ROUND 43\o"CurrentDocument"TRUNC 44\o"CurrentDocument"转换函数 45\o"CurrentDocument"令 TO.CHAR 45\o"CurrentDocument"其他辅助函数 47\o"CurrentDocument"GREATEST 49\o"CurrentDocument"LEAST 49\o"CurrentDocument"NVL 50SYS_CONNECT_BY_PATH 52SYS_CONTEXT 52\o"CurrentDocument"第一节聚集函数 52°AVG 54。FIRST. 58。MIN 59\o"CurrentDocument"第二节分析函数 60\o"CurrentDocument"1.概述 60评62\o"CurrentDocument".常用的分析函数 65\o"CurrentDocument"令COUNT 66\o"CurrentDocument"◊ LAST.VALUE 67\o"CurrentDocument"令 MIN 69常见问题 73计算动态累计问题 73TOP-N问题 73交叉查询问题 73◊移除重复值问题 73第四节对象引用函数 73第五节函数列表（字母序） 73ABS 73ACOS 73ADD_MONTHS 73\o"CurrentDocument"ASCII 74\o"CurrentDocument"ASIN 74\o"CurrentDocument"ATAN 74\o"CurrentDocument"A7AN2 74\o"CurrentDocument"AVG 75\o"CurrentDocument"BIN^TO_NUM 75\o"CurrentDocument"B1NAND 75\o"CurrentDocument"CHARTOROWID 76\o"CurrentDocument"COUNT. 76\o"CurrentDocument"CURRENT_DATE. 76\o"CurrentDocument"CURRENT』MESTAMP 76\o"CurrentDocument"DECODE 76\o"CurrentDocument"DENSE_RANK 77DUMP 77FIRST. 77\o"CurrentDocument"79. FIRST_VALUE. 77FLOOR 77GREATEST 77GROUPJD 77GROUPING 77GROUPING」D 77LAG 77\o"CurrentDocument"LAST 78\o"CurrentDocument"LAST_DAY 78\o"CurrentDocument"LAST_VALUE 78\o"CurrentDocument"LEAST. 78\o"CurrentDocument"LENGTH 78\o"CurrentDocument"MAX 78\o"CurrentDocument"MIN 79\o"CurrentDocument"MOD 79\o"CurrentDocument"MONTHS_BETWEEN 79\o"CurrentDocument"NVL 79\o"CurrentDocument"RANK 79\o"CurrentDocument"ROUND(clate) 80\o"CurrentDocument"ROUNDmumber) 80\o"CurrentDocument"ROW_NUMBER 80\o"CurrentDocument"RPAD 80SYSTIMESTAMP 81TO_DATE 81TRANSLATE 81\o"CurrentDocument"TRIM 81TRUNC(number) 81UPPER 81第五章后记 82第一章基本元素第一节数据类型VARCHAR2(size)：可变长度的字符串，最大size个字节。size最大可取值是4000,如果你指定了size是4000,然后插入超过4000子句的字符串，oracle会字段截取先进入库的4000字节，后面的它就不管了。如果你知道的size没有4000,而是其他一个数字如200,那么如果你插入的字符串超过200,oracle就会返回一个错误。size必须指定个正数。有关VARCHAR类型，它其实和VARCHAR2一样，是它的同义词。但是不宜指定VARCHAR作为数据类型，因为你不确定oracle哪天头脑发热，把VARCHAR的含义改掉了或者不支持它，那就完了。CHAR(size)固定长度的字符串，最大size个字节。如果你插入的字符串没有size个字节，那么oracle就会使用空白来填充它。size最大可取值是2000,如果你指定了size是2000,然后插入超过2000子句的字符串，oracle会字段截取先进入库的4000字节，后面的它就不管了。如果你知道的size没有2000,而是其他一个数字如200,那么如果你插入的字符串超过200,oracle就会返回一个错误。如果你不指定size,那么它的默认值就是1。LONG用于存储字符串，最多可以存储2G个字节的字符。LONG类型有很多的限制，已经不被oracle推荐使用，大家最好不要用，会有一系列操作限制，导致一些隐形的问题。CLOB存储字符串，即支持可变长度的，也支持固定长度的字符串，最大可支持到4G字节。NUMBER(p,s)oracle中唯一存储数字的数据类型。最大最小值分别为9.99...999X1()125(38个9后面跟了88个0,因此它的数字精度就是38)和1.0X10T30。如果你使用的数字超过这个数字,那么oracle返回错误。p表示这个数字一共有多少个数字组成，范围是从1到38。S就表示0右边的精度有多少位，就小数是多少位，范围是-84到127。p和s要么都不指定，就表示范围是使用最大值，即p=38,s=127：要么就指定p,那么S=0;要么都必须同时指定。不存在只指定S不指定p的情况。下面给个例子，来详细说明这个类型:下面正常情况下，S的值小于P的情况：实际数据指定的类型结果说明1234567.89NUMBER1234567.89没有变化1234567.89NUMBER(9)1234567小数没有了1234567.89NUMBER(9,2)1234567.89没有变化1234567.89NUMBER(9,1)1234567.9小数只有1位，四舍五入了1234567.89NUMBER(6)错误超过了最大数字精度范围1234567.89NUMBER(7,-2)1234500s为负数，则在0左边截取1234567.89NUMBER(7,2)错误超过最大数字精度范围7下面是“不正常”情况，S的值大于P的值的情况:实际数据指定的类型结果说明0.01234NUMBER(4,5)0.01234没有变化0.00012NUMBER(4,5)0.00012没有变化0.000127NUMBER(4,5)0.0013超过指定小数位数的范围了，四舍五入0.0000012NUMBER(2,7)0.0000012没有变化0.00000123NUMBER(2,7)0.0000012超过指定小数位数的范围了，四舍五入从上面的例子可以看到，S是表示小数点右边的有多少个数字，即有多少为小数，如果它为负值，那么就表示小数点左边有多少位数字；当所指定的数字超过了这个S精度指定的访问，那么oracle就执行四舍五入。p表示的是一共有多少为有效数字，不能超过这个有效数字，超过了就返回相应的错误。DATE时间类型，oracle存储时间的数据包括：世纪、年、月、日、时、分、秒。可见它只精确到秒级别。往date类型你们插入数据要使用函数to_date(),把date类型数据转换为字符串类型数据，要使用to_char()函数。有关日期的计算：可以对日期进行加、减计算，不能对其进行乘法和除法运算。指定一个数字，oracle对其附加天作为单位来和日期进行计算。如sysdate-1就表示昨天，sysdate+1就表示明天，sysdate-7就表示一周以前，而sysdate-10/1440就表示10分钟以前。TIMESTAMP(factional_seconds_precision)TIMESTAMP表示时间戳类型，就是一个时间点。oracle存储的时间戳数据包括：年、月、日、时、分、秒。可见它不存储世纪。对于faction_seconds_precision,表示将秒精确到哪个级别，它的值是从0到9,默认是6,如果是3,就表示把秒精确到毫秒级别，9就表示精确到“纳米”级别。看个例子就明白了：SQL>selectto_char(systimestamp,*yyyy-mm-dd,hh24:mi:ssxfr)timefromdual;TIME2007-09-19,11:20:41.035829存储TIMESTAMP类型，要使用TO_TIMESTAMP()函数，显示TIMESTAMP类型数据要使用TO_CHAR()函数，如例子中那样。第二节NULLnull表示novalue,unknown,它不表示0,也不表示一个空的字符串。SQL函数中的NULL所有的函数，除了REPLACE,NVL,CONCAT以外，如果你用null作为它的参数，那么结果就是返回nulL大部分的聚集函数如(COUNT,AVG),他们会忽略null,如一个列上面有10行记录,其中2行是null.那么count这个列的结果就是8。例如在增加月份函数中使用null进行计算的情况：SQL>selectto_char(sysdate,*yyyy-mm-dd,)todayfromdual;TODAY2007-09-23SQL>selectto_char(addmonths(sysdate,1),,yyyy-mm-dd，)another_dayfromdual;ANOTHERDA2007-10-23SQL>selectto_char(addmonths(sysdate,null),*yyyy-mm-dd，)another_dayfromdual;ANOTHER_DA聚集函数的情况：22:11:28SQL>selectempno,ename,commfromemp;EMPNOENAMECOMM7369SMITH7499ALLEN 3007521WARD 5007566JONES7654MARTIN14007698BLAKE7782CLARK7788SCOTT7839KING7844TURNER07876ADAMS7900JAMES7902FORD7934MILLER已选择14行。22:11:43SQL>selectcount(empno)fromemp;COUNT(EMPNO)1422:12:05SQL>selectcount(comm)fromemp;COUNT(COMM)4条件中的null.对NULL所进行的任何数学计算结果都是null,如10+null结果是nulLoracle始终认为两个null是不相等的（除了少数情况），不能用null=null这个条件来进行判断两个null是否相等，null不等于其他的null,你只能用isnull或者isnotnull来判断某个记录的某个字段是不是null,但是oracle在使用decode函数的时候，它认为两个null是相等的。oracle还有一种情况也认为两个不同的null是相等的，就是在一个复合键中，如果这两个复合键值中非null部分是相等的，那么这两个复合键值是相等的。下面给出null出现的条件表格：如果a是比较条件是那么结果是10aISNULLFALSE10aISNOTNULLTRUENULLaISNULLTRUENULLaISNOTNULLFALSE10a=NULLUNKNOWN（其实就是null）10a!=NULLUNKNOWNNULLa=NULLUNKNOWNNULLa!=NULLUNKNOWNNULLa=10UNKNOWNNULLa!=10UNKNOWN例如：SQL>selectempno,ename,commfromemp;TOC\o"1-5"\h\zEMPNOENAME COMM7369SMITH7499ALLEN 3007521WARD 5007566JONES7654MARTIN 14007698BLAKE7782CLARK7788SCOTT7839KING7844TURNER 07876ADAMS7900JAMES7902FORD7934MILLER已选择14行。SQL>selectempno,ename,commfromempwhereconun=null;未选定行SQL>selectempno,ename,commfromempwherecomm<>nul1未选定行SQL>selectempno,ename,commfromempwherecommisnul1EMPNOENAME COMM7369SMITH7566JONES已选择10行。SQL>selectempno,ename,commfromempwherecommisnotnull;EMPNOENAME COMMTOC\o"1-5"\h\z7499ALLEN 3007521WARD 5007654MARTIN 14007844TURNER 0第三节伪列伪列就像是一个表的列，但却不是，它不存储在表中。你可以对伪列进行查询，而却不能进行增删改。伪列包括下面几类：1.CURRVAL和NEXTVAL这两个伪列主要是用于访问序列(sequence)的值。currval返回当前值，而nextval返回下一个值并且导致sequence一次值的增加。你只能通过下列的形式来访问这两个伪列：sequencename.CURRVALsequencename.NEXTVAL哪里可以使用sequence的值？1、查询语句的select部分，但不是在子句的select中,如selectafrom(selectbfrom。。。)，这个里面b就在查询语句的子句的select中，可以使用sequence的位置(即使用currval和nextval的地方)是在a这个部位。2、insert语句的子句select部分中，如insertintotselectafrom。。。，就是在a这个位置。3、insert的value子句中，如insertintotvalues(a…),就是在a这个位置。4、update的set子句中，如UPDATETseta。。。，就是在a这个位置。哪些情况对sequence值的使用有限制，即currval和nextval不能使用的场合？1、DELETE,SELECT,UPDATE的子句，如deletefromtwhereain(selecta….)，这个a位置不能使用。2、在查询视图和物化视图的语句中不能使用。3,包含DISTINCT关键字的查询语句中不能使用。4、包含GROUPB丫和ORDERBY子句的查询语句中不能使用。5、集合操作中不是第一个select语句的语句中不能使用，如SELECTa...UNIONALLSELECTb..„这里b的位置不能使用，a位置能用。6、WHERE中的select子句，如SELECT...FROM...WHERE(SELECTa...)中a的位置。7、在createtable和altertable中，某个有default值的列上不能使用.check约束中不能使用。如何使用序列值？当你建立一个序列的时候，你会指定这个序列的第一个值和增加值，当你在第一次调用这个序列的时候，你不能使用sequencename.CURRVAL来访问它的当前值，因为它还没开始，而必须使用sequencename.NEXTVAL来让它开始初始化第一个值，第一次使用这个时候返回的时候是序列的第一个值。之后在时候currval是返回序列的当前值，nextval返回序列的下一个值并且导致序列增加一个值。oracle对序列的增加只是一次性的，单向的，即它只增加，不能减少，并且只能增加一次。下面给个简单的例子：SQL>createsequenceseq_teststartwith1incrementby1nomaxvalue;——创建序歹U，第•值I,每次增加1Sequencecreated.SQL>selectseq_test.currvalfromdual; 第一次必须通过nextval让序列初始化selectseq_test.currvalfromdualERRORat1ine1:0RA-08002:sequenceSEQ_TEST.CURRVALisnotyetdefinedinthissessionfromdual;第一次初始化序列，让它得到第一值fromdual;第一次初始化序列，让它得到第一值1NEXTVAL1SQL>selectseq_test.currvalfromdual;当前值CURRVAL1SQL>selectseq_test.nextvalfromdual;一不是第一次，nextval返回下个值,并且序列增加1NEXTVAL2SQL>selectseq_test.currvalfromdual;经过nextval后，序列的确增加「CURRVALLEVELROWIDrowid顾名思义就是某条记录在数据库中的存储地址，在9i中它包括下列内容：1、这条记录属于哪个对象的，记录了这个对象在数据库中id。2,这条记录属于数据文件的哪个块的3、这条记录是数据块的哪个行（这个行就记录了这条数据）4、这条记录属于哪个数据文件，以数据文件的id表示。因此rowid能够快速的定位到数据在数据库中的位置，因此通过rowid来访问数据一般都是比较快的。一般情况下，rowid具有唯一性，即一条记录一个rowid,但是在cluster中，多个表的公用部分是只存一个记录的，因此所有这些表的公用部分一个记录就一个rowid,其实那还是一个记录一个rowidorowid伪列具有rowid类型或者是urowid类型。rowid反映的是当前这个记录在数据库中的“逻辑”位置，即它是哪个对象的在哪个数据文件的哪个块的哪个行上，所以，当使用导入导出工具进行移动数据以后，rowid会发生变化，类似发生变化的还有如oracle对物理文件进行了相关的接合整理也会出现整理前后rowid发生变化等。你可以在语句的select部分以及where部分中使用rowid这个伪列。注意它是伪列，即它不在表中存储，你只能查询而不能增删改。下面给个小例子：SQL>selectt.*,rowidfromdeptt;ROWIDDEPTNODNAMELOG10ACCOUNTINGNEWYORKAAAMfNAAEAAAAAQAAA20RESEARCHDALLASAAAMfNAAEAAAAAQAAB30SALESCHICAGOAAAMfNAAEAAAAAQAAC40OPERATIONSBOSTONAAAMfNAAEAAAAAQAADSQL>select*fromdeptwhererowid=，AAAMfNAAEAAAAAQAAA';DEPTNODNAME l.OC10ACCOUNTINGNEWYORKROWNUM语句查询语句所返回的每一行记录，Oracle会给其指定一个顺序的数字（rownum）,用于标志顺序，第一行记录是1,第一行记录是2,依此类推。你可以通过rownum来实现限制返回的行数的目的，即在条件判断中使用rownum小于或者小于等于某个数的情况，请看下面这个例子：SQL>selectrownum,empno,enamefromempwhererownum<3;ROWNUMEMPNOENAME1 7369 SMITH2 7499 ALLENSQL>selectrownum,empno,enamefromempwhererownum<=3;ROWNUM EMPNO ENAME1 7369 SMITH2 7499 ALLEN3 7521 WARDROWNUM一般只能用于v或<=的情况，rownum使用=的时候只能是1,不能是其他任何数，否则不会返回结果，请看一个例子：SQL>selectrownum,empno,enamefromempwhererownum=l;ROWNUMEMPNOENAME7369SMITH

SQL>selectrownum,empno,enamefromempwhererownum=2;未选定行SQL>selectrownum,empno,enamefromempwhererownum=3：未选定行为什么呢？如果指定的条件是ROWNUM=2的话，对于满足其他条件的第一条记录如果返回的话ROWNUM=1,但是这不满足ROWNUM=2的条件，因此这条记录不会返回。找到下一条记录，这时候ROWNUM仍然等于1,因为目前还没有返回1条符合的数据，同理这条记录也不会满足ROWNUM=2的条件，也不会返回直到最后一条，都不会满足ROWNUM=2的条件。所以指定非1的值只能返回无。rownum使用〉的时候只能大于。或负数，而这没有任何意义，因此一般不会出现大于的情况。由上面的例子，可以看出rownum是出现在where子句中和select子句中的，where的执行是在orderby子句执行的前面，因此当有orderby的时候，rownum如果显示出来就会出现顺序错乱的现象，请看下面这个例子：SQL>selectrownum,empno,enamefromempwhererownum<=3orderbyename;ROWNUMEMPNOENAME2 7499 ALLEN1 7369 SMITH3 7521 WARD由此我们可以联想到这样一类句子怎么去做，就是返回某个表的前（后，最）多少行,请看下面这个例子，返回emp表的前（后）3行：SQL>selectrownum,empno,enamefromemp;ROWNUMEMPNOENAMErownum<=3;ROWNUMrownum<=3;ROWNUMEMPNOENAME17369SMITH27499ALLEN37521WARD47566JONES57654MARTIN67698BLAKE77782CLARK87788SCOTT97839KING107844TURNER117876ADAMS127900JAMES137902FORD147934MILLER已选择14行。SQL>selectrownum,empno,enamefrom(selectt.empno,t.enamefromemptorderbyempno)where1 7369SMITH2 7499ALLEN3 7521WARDSQL>selectrownum,empno,enamefrom(selectt.erapno,t.enamefromemptorderbyempnodesc)whererownum<=3;ROWNUMEMPNOENAME1 7934 MILLER2 7902 FORD3 7900 JAMES上面这种似乎大家还是不是很满意？因为你得到的所有最多少条，是经过某个列的排序以后才得到的，那么如何得到没有经过排序的，即你们认为在表中原始存储的后10条数据呢(前10条太简单了，之间rownum<11就可以了)，或者是取道中间的第2到第3条数据呢？这个时候，我们就可以利用嵌套查询来实现这个操作，请看下面这个例子：SQL>select*from(selectrownumasrn,tl.empno,tl.enamefromemptl)wherern>2andrn<5;RNEMPNOENAME3 7521WARD4 7566JONESSQL>select*from(select*from(selectrownumasrn,tl.empno,tl.enamefromemptl)orderbyrndesc)whererownum<3;RNEMPNOENAME14 7934MILLER13 7902FORDXMLDATA第四节注释在sql语句内部的注释不会影响到语句的执行,但是却能增加语句的可读性与可维护性。它可以出现在任何的关键字，参数和标点符号之间。有两种注释形式：1、以“/*”开始，以结束，在这之间的所有东西都是注释部分，可以跨行，多行注释。2、以“-”开始，此行之后的所有部分为注释，不能跨行，单行注释。第五节数据库的对象.模式对象一个模式（schema）就是数据库的数据逻辑结构的集合，或称模式对象。一个模式属于一个数据库的用户，并且它的名字就是用户的名字，如在数据库中有一个taobao用户，那么在数据库中就有一个taobao模式，taobao用户的所有对象的集合就称为taobao模式。因此，很多情况下，它们两个通用。既然模式对象和某个用户是相互对应关联的，也就是说某个用户存在的时候，其相对应的模式对象才有存在的意义，因此对应的用户被删除以后，其所对应的模式对象也将全部被删除，基于这些模式对象而建立起来的其他的对象都将失效，如a用户存储过程p是中引用了b用户的表t,那么b被删除以后，t被删除，p不会被删除，但是p过程就失效了。下面列出数据库的模式对象：名称解释ClustersConstraintsDatabaselinksDatabasetriggersDimensionsExternalprocedurelibrariesIndex-organizedtablesIndexesIndextypesJavaclassesJavaresourcesJavasourcesMaterializedviewsMaterializedviewlogsObjecttablesObjecttypesObjectviewsOperatorsPackagesSequencesStoredfunctionsstoredproceduresSynonymsTablesViews

.非模式对象非模式对象，与模式对象进行对照，很显然，它是不会随着用户的消失而离开的独立的数据库对象，它包括：名称解释ContextsDirectoriesParameterfiles(PFILEs)Serverparameterfiles(SPFILEs)ProfilesRolesRollbacksegmentsTablespacesUsers具有讽刺意味的是user也）是非模式对象，可以这样理解用户a独立于、不依赖于用户b而存在。.部分模式对象第六节格式模型1.时间格式元素表示含义/>“test”这些标点符号和具体的字符串，最终会显示出来，其实他们启到的作用就是分隔，如下面这个日期：2007-9-16这里符合“-”就启到了分隔年月日的作用。ADA.D.公元后BCB.C.公元前AMA.M.早上，上午PMP.M.卜午D一周的第几天，星期天是1

DAY星期几,，一共用9个字符大小来显示，如果没有这么长，名称的右边用空格来填充DD一个月的第几天，11〜31］DDD一年的第几天，［1-366】DY星期几，但是是简称，如周一可能就是MONFF[O..9]这个是用于timestamp类型的，用于表示将秒分成多少位数来表示，在FF后面跟上一个0到9的数字。HH小时，【1〜12】HH12小时，【1〜12】HH24小时，【0〜23】MI分钟，【0〜59】MM月份，MON月份，但是是月份的简称，如1月可能是JAN来表示MONTH月份，月份的名称，如9月可能是September来表示，一共用9个字符大小来显示，如果没有这么长，名称的右边用空格来填充Q季节，［1,2,3,4］分别代表春夏秋冬SS秒，【0〜59】SSSS从午夜0秒开始计算到现在的秒数ww一年的第几个周，【1〜53】w一个月的第几个周，【1〜5】Y,YYY年份，中间有个逗号YEARSYEAR年份，用字符串显示，S表示指示公元前(BC)使用表示YYYYSYYY4个数字的年份，S表示指示公元前(BC)使用表示YYYYYY分别表示年份的后面三个、两个、一个数字第七节操作符.操作符列表.操作符优先级.连接操作符.集合操作符第二章表达式第三章条件所谓条件就是通过计算指定的一个或者多个表达式和逻辑操作，而返回一个布尔值TRUE、FALSE或者UNKNOWN。条件可以分为很多种类型，有以下几类：condition::=屋8mparisonconditioi)^conditioncondition-^membershipconditionj--^rangecondition^ <nullcondition)(equalspath) (existscondition) -ZlikeconditiorA <isoftypecondition)—(underpath) dconditiondcondition那么条件在一个语句中的哪些位置可以放置呢？可以在下列位置放置：1、WHERE子句中2、STARTWITH子句中(大家很少用到)3、CONNECTBY子句中(大家也很少用到)4、HAVING子句中另外简单的条件是可以相互组合成一个复杂的条件，如(1=1)AND(5=7)o第一节条件的优先级那么当许多条件在一起的时候，哪个先执行，哪个后执行呢，即条件的优先级是什么呢?下面以优先级从高到低的顺序来列出条件的优先级：条件类型说明SQL操作符如加减乘除，请参考操作符的优先级=!=<><=>=比较IS[NOT]NULLLIKE[NOT]BETWEEN[NOT]IN比较

EXISTSISOFtypeNOT求累、逻辑否定AND连接，逻辑与OR分离，逻辑或第二节比较条件比较条件，顾名思义，它就是比较两个表达式的，它的结构返回一个布尔类型的值TRUE,FALSE或UNKNOWN。下面是比较条《二的列表：比较条件说明=相等的比较!=,"=,<>不等的比较，有些平台不能使用某些符号>,<大于，小于>=,<=大于或等于，小于或等于ANYSOME它的左边是一个值，它的右边是一系列值或者一个查询子句（它返回一个或者多个值），它的功能就是比较左边的是否在右边之中。如果它右边是一个查询子句，当这个查询子句无返回值的时候，那么整个比较条件返回FALSE,这两个比较操作必须跟在=，仁,>,<,>=,<=的后面。ALL和some,any的要求一样，只不过它的功能是比较它左边的值对右边值的比较必须所有的都正确结果才为TRUE,否则为FALSE»如果它右边是一个查询子句，当这个查询子句无返回值的时候，那么整个比较条件返回TRUE。它必须跟在=,!=,>,<,>=,<=的后面。关于any和some以及all,我想来做个实验，说明它们的作用。首先请看下面的数据分布情况：SQL>select*fromdept;DEPTNODNAME LOG10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON对any、some进行一个正确的等值判断，请看结果：SQL>selectdeptno,dnamefromdeptwhere*BOSTON,=any(selectlocfromdept);DEPTNODNAME10ACCOUNTING20RESEARCH30SALES40OPERATIONSSQL>selectdeptno,dnamefromdeptwhere*BOSTON*=some(selectlocfromdept);DEPTNODNAME10ACCOUNTING20RESEARCH30SALES40OPERATIONS下面在对any,some进行一个错误的等值判断，请看结果：SQL>selectdeptno,dnamefromdeptwherethangzhou,=any(selectlocfromdept);norowsselectedSQL>selectdeptno,dnamefromdeptwhere*hangzhou,=some(selectlocfromdept);norowsselected下面再对它们进行一个不等值判断，情况结果：SQL>selectdeptno,dnamefromdeptwherel>any(0,1,3);DEPTNODNAME10ACCOUNTING20RESEARCH30SALES40OPERATIONSSQL>selectdeptno,dnamefromdeptwherel>some(0,1,3);DEPTNODNAME10ACCOUNTING20RESEARCH30SALES40OPERATIONSSQL>selectdeptno,dnamefromdeptwherel>any(1,3);norowsselectedSQL>selectdeptno,dnamefromdeptwherel>some(1,3);norowsselected下面再看一下，如果它们右边的值为空的情况：SQL>selectdeptno,dnamefromdeptwherel>any(selectnullfromdual);norowsselectedSQL>selectdeptno,dnamefromdeptwherel>some(selectnul1fromdual);norowsselected由上面的实验可以知道，any与some在做比较条件的时候，它们的功能以及作用是一样的，没有区别，另外还可以加深一个印象，那就是它们的计算结果是一个布尔值。下面看一下ALL的情况：SQL>selectdeptno,dnamefromdeptwhere*BOSTON*=all(selectlocfromdept);norowsselectedSQL>selectdeptno,dnamenorowsselectedfromdeptwhereKalKl^,3);SQL>selectdeptno,dnameDEPTNODNAMEfromdeptwhereKal1(2,3);10ACCOUNTING20RESEARCH30SALES40OPERATIONSSQL>selectdeptno,dnamenorowsselectedfromdeptwhere*BOSTON*=all(selectnullfromdual)SQL>selectdeptno,dnamefromdeptwhereKall(selectnullfromdual);norowsselected一返回了null,1和mH1比较的结果是null(UNKNOWN)SQL>selectdeptno,dnamefromdeptwhereKal1(selectjobfromempwhere1=2);DEPTNODNAME—后面的子查询返回为空10ACCOUNTING20RESEARCH30SALES40OPERATIONS第三节逻辑条件第四节成员关系条件第五节范围条件第六节NULL条件第四章函数第一节单值函数单值函数在查询中返回单个值，可被应用到select,where子句，startwith以及connectby子句和having子句。1.数值型函数数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点，诸如：COS,COSH,EXP,LN,LOG,SIN,SINH,SQRT,TAN,andTANH支持36位小数点。ACOS,ASIN,ATAN,andATAN2支持30位小数点。下面是常用的数值型单值函数.MOD返回n2除以nl的余数，如果nl=0则返回n2的值。eg：14:18:53SQL>SELECTMOD(24,5)valueFROMDUAL;VALUE414:19:00SQL>SELECTMOD(24,0)valueFROMDUAL;VALUE24令ROUND它的作用是对数字number进行四舍五入，精确到哪个精度呢？由参数integer指定，如果integer是0表示精确到整数，如果integer是负数表示精确到小数点左边的第integer位，如果integer是正数表示精确到小数点右边的第integer位，不指定integer相当于其等于零。啥也不用说了，请看下面几个例子：SQL>selectround(1.232,2)nfromdual;N1.23SQL>selectround(1.238,2)nfromdual;N1.24SQL>selectround(1.23,0)nfromdual;N1SQL>selectround(123.25,-1)nfromdual;120SQL>selectround(128.25,-1)nfromdual;130SQL>selectround(1.23)nfromdual;-—这个是没有指定精度的情况,相当于精度为0精度能不能是小数？能，oracle会对精度进行取整数，然后进行运算:SQL>selectround(1.23,1.24)nfromdual;N1.2SQL>selectround(1.23,1.85)nfromdual;N1.2SQL>selectround(12.5,-1.2)nfromdual;N10SQL>selectround(12.5,-1.8)nfromdual;X10如果精度指定的值超过了数的范围，会是什么样子呢？请看:SQL>selectround(1.2,-3)nfromdual;N0SQL>selectround(1.2,3)nfromdual;N1.2OtruncTtrunc 它表示数字n1,以小数点为标准，截取n2位；n2为正表示截取小数部分，n2为负表示截取整数部分，n2为零表示在小数点出截取，默认n2为零。例如：14:25:30SQL>SELECTTRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1)FROMDUAL;TRUNC(23.56)TRUNC(23.56,1)TRUNC(23.56,-1)23 23.5 20下面是不太常用的数值型单值函数。©ABSabs::=返回一个数字的绝对值。eg：SQL>selectabs(-5)absolutevaluefromdual;ABSOLUTEVALUE5SQL>selectabs(-1/3)a_vfromdual;A_V.333333333令ACOSacos::=ACOS返回所给数字的arccosine值，n必须要是-1到1之间，所返回值是在0到n(圆周率)之间的一个数字。eg：SQL>selectacos(-1)vfromdual;V3.14159265SQL>selectacos(1)vfromdual;0令ASINasin::=返回所给数字n的arcsine值。n必须要是-1到1之间，所返回值是在-R/2到n/2(圆周率)之间的一个弧度值。eg：SQL>selectasin(-l)afromdual;A-1.5707963SQL>selectasin(0)afromdualA0令ATANatan::=*ATAN功能描述：返回所给数字n的arctangent值。n是一个任何值，所返回值是在-TI/2到TI/2(圆周率)之间的一个弧度值。举例：SQL>selectatan(50)afromdual;1.55079899《ATAN2功能描述：返回所给数字n和m的arctangent值。n是一个任何值，所返回值是在-H/2到H/2(圆周率)之间的一个弧度值。ATAN2(n,m)==ATAN2(n/m)举例：SQL>selectatan(l)afromdual;.785398163SQL>selectatan2(0.5,0.5)afromdual;•785398163《BIN_TO_NUM令BITAND-^CEIL令COS。FLOOR令SIN令SINH令2.返回字符值的字符型函数该类函数返回与输入类型相同的类型,其中：1、返回的CHAR类型值长度不超过2000字节：2、返回的VCHAR2类型值长度不超过4000字节；如果上述应返回的字符长度超出，oracle并不会报错而是直接截断至最大可支持长度返回。3、返回的CLOB类型值长度不超过4G；对于CLOB类型的函数，如果返回值长度超出，oracle不会返回任何错误而是直接抛出错误。下面是常用的返回字符值的字符型函数。。LOWER\LOWER|)(7))(char))(T)>将指定字符串内字符变为小写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型。eg：14:35:48SQL>SELECTLOWER(^haTistHis')lowerFROMDUAL;LOWERwhatisthis令UPPER卜|UPPER将指定字符串内字符变为大写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型。例如：14:35:54SQL〉SELECTUPPER('WhaTistHis')upperFROMDUAL;UPPERWHATISTHIS令LPADTLPAD返回指定长度=n的字符串，需要注意的有几点：.如果n<exprl.length则从右到左截取指定长度返回；.如果n>exprl.lengthandexpr2isnull,以空格从左向右补充字符长度至n并返回；.如果n>exprl.lengthandexpr2isnotnull,以指定字符expr2从左向右补充exprl长度至n并返回。eg:14:39:27SQL>SELECTLPAD(*WhaTistHis',5)LPAD1,14:39:472 LPAD('WhaTistHis',25)LPAD2,14:39:473 LPAD(*WhaTistHis',25,'」)LPAD314:39:47 4FROMDUAL;LPAD1LPAD2 LPAD3WhaT WhaTistHis WhaTistHis最后大家再猜一猜，如果n<0,结果会怎么样？14:41:15SQL>SELECTLPAD(*WHATISTHIS*,-5)LPADFROMDUAL;LPAD《RPAD返回指定长度=n的字符串，基本与LPAD含义相同，不过补充字符是从右向左方向正好与上相反。eg：14:42:18SQL>SELECTRPADCWhaTistHis*,5)RPAD1,14:43:59 2 RPADCWhaTistHis*,25)RPAD2,14:43:59 3 RPADCWhaTistHis',25,'」)RPAD314:43:59 4FROMDUAL;RPAD1RPAD2 RPAD3WhaTWhaTistHisWhaTistHisWhaTWhaTistHisWhaTistHis 令TRIMsource^Qj»source^Qj»看起来很复杂，理解起来很简单：.如果没有指定任何参数则oracle去除trim_source头尾空格。eg：14:44:00SQL>SELECTTRIMCWhaTistHis')TRIMFROMDUAL;TRIMWhaTistHis.如果指定了trim_character参数，则oracle去掉trim_source头尾trim_characteroeg：14:49:58SQL>SELECTTRIMC*W*FROM*WhaTistHiswW*)trimFROMDUAL;TRIMhaTistHisw.如果指定了leading参数则会去掉trim_source头部trim_characteroeg：14:51:58SQL>SELECTTRIM(leadingFROM*WhaTistHisw『)trimFROMDUAL;TRIMhaTistHiswW.如果指定了trailing参数则会去掉trimsource尾部trim_characteroeg：14:52:59SQL>SELECTTRIM(trailing'W'FROM*WhaTistHiswW')trimFROMDUAL;TRIMWhaTistHisw.如果指定了both参数则会去掉trim_source头尾trim_character(跟不指定没有区别)。eg：14:53:39SQL>SELECTTRIM(bothFROM*WhaTistHiswW*)trimFROMDUAL;TRIMhaTistHisw注意：c2长度=1令LTRIMTLTRIM从字符串char左侧截取掉与指定字符串set相同的字符并返回，并且char的开头部分必须要是set,否则不会截取，如果set为空则默认截取单空格。eg：14:56:38SQL〉SELECTLTRIMCWWhhhhhaTistHisw『，'W')ltrim2FROMDUAL;LTRIM2hhhhhaTistHiswW在其之前的所有的部分都公被截掉14:56:01SQL>SELECTLTRIMCWhhhhhaTistHisw『，'Wh')ItrimlFROMDUAL;LTRIM1aTistHiswW 在其之前的所仃的部分都会被截掉，在其之后的连续的与c2仃相同的字母全部被裁取，这里所有的h都被截取掉了。14:58:54SQL>SELECTLTRIMCWWhhhhhaTistHiswW','w')ltrim3FROMDUAL;LTRIM3WWhhhhhaTistHiswW -这里不是以■开头，故失去截取功能令RTRIMJRTRIM J 与LTRIM含义类似，只不过方向相反。eg：14:56:44SQL>SELECTRTRIMCWhhhhhaTistHiswWTSOUNDEX返回字符串参数的语音表示形式，对于比较一些读音相同，但是拼写不同的单词非常有用。计算语音的算法如下：•TSOUNDEX返回字符串参数的语音表示形式，对于比较一些读音相同，但是拼写不同的单词非常有用。计算语音的算法如下：•保留字符串首字母，但删除a、e、h、i、。、w、yo•将下表中的数字赋给相对应的字母：1：b、f、p、v2：c、g>k、q>s>x、z3：d>t4：15：m>n6：R如果字符串中存在拥有相同数字的2个以上（包含2个）的字母在一起（例如b和f）,或者只有h或W,则删除其他的，只保留1个；•只返回前4个字节，不够用0填充eg:18:05:00SQL>SELECTSOUNDEXCdog'）dog,soundexCboy'）boyFROMDUAL：DOGBOYRTRIMWWhhhhhaTistHis令REPLACEtREPLACE)[^^Cepla8mentstringtREPLACE将char字符串中的searchstring替换为replace_string,如果replace_string为空，则从char中删除所有search_stringoeg：15:06:16SQL>SELECTREPLACE(,WhhhhhaTistHisw replaceFROMDUAL;REPLACE——hhhhhaTistHisw-令SOUNDEXD200BOOO令SUBSTR截取指定长度的字符串，稍不注意就可能充满了陷阱的函数。position是开始截取的位置,substringlength要截取的字符串长度，如果为空，默认截取到字符串结尾：如果position=0thenpositional18:19:49SQL>SELECTSUBSTRCWhatisthis',0,3)subFROMDUAL;SUBWha2、如果position>0,则oracle从左向右确认起始位置截取18:20:50SQL>SELECTSUBSTR(*Whatisthis',5,3)subFROMDUAL;SUBis3、如果position<0,则oracle从右向左数确认起始位置18:20:52SQL>SELECTSUBSTRCWhatisthis\-5,3)subFROMDUAL;SUBth4、如果position>char.length则返回空18:21:31SQL>SELECTSUBSTR(*Whatisthis',50,3)subFROMDUAL;S此处是nul)5、如果substring_length<l,则不截取18:24:10SQL>SELECTSUBSTRCWhatisthis',4,0)subFROMDUAL;S一此处是null18:24:25SQL〉SELECTSUBSTRCWhatisthis',4,T)subFROMDUAL;S--此处是null《TRANSLATETRANSLATE fromstringX^y^tostring)^)~»就功能而言，此函数与replace有些相似。但需要注意的一点是，translate是绝对匹配替换，这点与replace函数具有非常大区别。什么是绝对匹配替换呢？简单的说，是将字符串expr中出现在格式from_string中的字符都 替换为to_stringo如：18:44:41SQL>SELECTtranslateCWhatisthis',' translateFROMDUAL;TRANSLATEWhat-Cs-thCs原字符串中空格被替换成,i被替换成Cr18:48:29SQL>selectreplace(*WTiatisthis','i','-')replacefromdual;REPLACEWhat-sthis 原字符串中连在一起的空格和i被「很显然，上面这种情况是要求fromstring和to_string的字符的个数要对应，那么如果from_string中字符的个数如果比to_string中的个数要多的话，那么这些from_string后面多余的部分，如果它们出现在expr中，则会直接被移除。如：18:55:18SQL>SELECTtranslateCWhatisthis','is',',)translateFROMDUAL;TRANSLATEWhat-th--原字符串的字母s就被移除掉了，i被替换成-如果from_string的个数比to_string要少，那么to_string后面多余的字符串直接丢弃不管，如：18:55:33SQL>SELECTtranslateCWhatisthis',」'，'-*')translateFROMDUAL;TRANSLATEWhat-sth-s—很显然，字符直接被丢弃。如果你想通过给to_string用一个空字符串，来达到清空expr的目的，这样是不行的。oracle会把传给to_string的空字符串当成是null,这样传入null,则直接输出null,而非被移除所有字符的空字符串expr。如：18:56:44SQL>SELECTtranslateCWhatisthis',7'，'')translateFROMDUAL;T此处是null19:00:49SQL>SELECTtranslateCWhatisthis'i',null)translateFROMDUAL;T此处是null从上面的解释，我们可以这样理解replace和translate：replace是替换，而translate则像是过滤。下面是不太常见的返回字符值的字符型函数。令.返回数字值的字符型函数本类函数支持所有的数据类型.令INSTRinstr::=这些"instringn函数的作用都是在string中查询substring,函数返回一个整数来表示substring在string中从position位置计算开始出现第occurrence次的位置。INS