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

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

册

编写、修订、审核记录

编号文档版本修订章节修订原因修订日期修订人

11.0全文新建文档2007-9-27胜通

22.0补充文档2008-1-6胜通

3

4

5

6

7

8

9

10

目录

第一章基本元素..................................................................6

第一节数据类型................................................................6

1.VARCHAR2(size)：...............................................................................................................6

2.CHAR(size)..........................................................................................................................6

3.LONG.........................................................................................................................................7

4.CLOB.........................................................................................................................................7

5.NUMBER(p,s)....................................................................................................................7

6.DATE........................................................................................................................................8

7.TIMESTAMP(factional_secondsprecision)....................................................................8

第二节NULL.......................................................................................................................................8

第三节伪列..........................................................11

1.CURRVAL和NEXTVAL........................................................................................................11

2.LEVEL.....................................................................................................................................12

3.ROWID....................................................................................................................................12

4.ROWNUM................................................................................................................................13

5.XMLDATA................................................................................................................................15

第四节注释...................................................................15

第五节数据库的对象...........................................................16

1.模式对象.................................................................................................................................16

2.非模式对象............................................................................................................................17

3.部分模式对象.........................................................................................................................17

第六节格式模型...............................................................17

1.时间格式................................................................................................................................17

第七节操作符.................................................................19

7.操作符列表.............................................................................................................................19

2.操作符优先级....................................................................................................................19

3.连接操作符........................................................................................................................19

4.集合操作符........................................................................................................................19

第二章表达式...................................................................19

第三章条件.....................................................................19

第一节条件的优先级.........................................20

第二节比较条件...............................................................21

第三节逻辑条件...............................................................23

第四节成员关系条件...........................................................23

第五节范围条件...............................................................23

第六节NULL条件..............................................................23

第四章函数.....................................................................23

第一节单值函数...............................................................23

1.数值型函数.............................................................................................................................24

令MOD....................................................................................................................................24

OROUND................................................................................................................................24

◊TRUNC...........................................................................................................................................................26

令ABS................................................................................................................................................................26

令ACOS.............................................................................................................................................................26

令ASIN...............................................................................................................................................................27

◊ATAN..............................................................................................................................................................27

◊ATAN2............................................................................................................................................................28

令BIN_TO_NUM..............................................................................................................................................29

◊BITAND.........................................................................................................................................................29

令CEIL...............................................................................................................................................................29

令COS................................................................................................................................................................29

令FLOOR...........................................................................................................................................................29

令SIN...................................................................................................................................................................29

◊SINH...............................................................................................................................................................29

0..............................................................................................................................................................................29

2.返回字符值的字符型函数................................................29

令LOWER..........................................................................................................................................................29

©UPPER............................................................................................................................................................30

令LPAD..............................................................................................................................................................30

令RPAD..............................................................................................................................................................31

◊TRIM..............................................................................................................................................................31

令ITRIM............................................................................................................................................................32

◊RTRIM............................................................................................................................................................32

令REPLACE......................................................................................................................................................33

◊SOUNDEX....................................................................................................................................................33

令SUBSTR.........................................................................................................................................................34

令TRANSLATE................................................................................................................................................35

0..............................................................................................................................................................................36

3.返回数字值的字符型函数..................................................36

。INSTR...........................................................................................................................................................36

令LENGTH........................................................................................................................................................37

0..............................................................................................................................................................................37

4.日期函数................................................................37

令ADD_MONTHS............................................................................................................................................38

令CURRENT_DATE........................................................................................................................................39

令CURRENT_TIMESTAMP..........................................................................................................................40

。SYSDATE....................................................................................................................................................40

令SYSTIMESTAMP........................................................................................................................................41

◊LAST_DAY...................................................................................................................................................42

令NEXT.DAY..................................................................................................................................................42

◊MONTHS_BETWEEN.................................................................................................................................42

令ROUND....................................................................................................................................................43

令TRUNC.........................................................................................................44

5.转换函数..............................................................45

令TOCHAR.....................................................................................................................................................45

D4*7

TUIB・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・

6.其他辅助函数..................................................................47

◊GREATEST.........................................................................................................................................................................................49

◊LEAST49

ONVL50

令NVI251

令SYS_CONNECT_BY_PATH.....................................................................................................................................................52

C<sYSCONTFXT52

・・・•・・・・・••・•・・・・・••・・・・・・•・•・•・・・・•・・•・・・・・・・•・・・・・・・・••・・・・・・・・•・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・•・・・・・・・・・・・・・・・・・・•・・・・

。AVG54

・・・・・・・♦•・・・・・・・♦•・・・・・・・♦•・・♦・・・・♦•••・・・・・♦・••・・・・・・♦••・・・・・・♦♦・・・・・・・・・・・・・・・・・♦・・・・・・・・♦・・・・・・・・・・・・・・・・・・♦・・・・・・・・・・・・・・・・・・・5

•・•••・••••••••••・・••••••・•・•••••••・•••••••••••・••••・•・•・•••••・・・・•・•••••・・•・・••・・・・・・•••・・・・・・•••••・・・・•••••・・・・••••・56^

9•••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••

°FIRST..........................................................................................................................................................................................................58

CMIN................................................................................................................................................................................................................59

°SUM.............................................................................................................................................................................................................59

第三节分析函数...............................................................60

/.概述...........................................................................60

•一I一・・・・♦♦♦♦・・・・・♦・♦♦・・・・・♦♦♦♦♦・・・・・♦♦♦♦•・・・♦♦♦♦♦••・・・♦♦♦♦•・・・・♦♦♦♦♦・・・・♦♦♦♦♦・・・•・♦♦♦•・・・・・♦♦♦♦・・・・・♦♦♦・・・・・・♦♦♦・・・・・・・・・♦・・・・・・・♦♦・1

3.语...........................................................................62

4•f)49J••・・•・・・・・•♦・•・・・・•••♦・・・・•・•・♦♦•・・•・••♦・・•・・・・♦♦♦・・・・•♦♦♦•♦・・・・♦♦♦•・・・・・・♦♦♦♦・・•・・•♦♦•・・・・・•••♦・•・••••♦・•・・・・••・♦・

••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••«•••••••■•^^5

・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・•・・・・・・•・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・♦

令MIN69

◊DENSE_RANK70

・・・・・・・・・・・・・・・・・・・・•・・・・・・•・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・•・・・・・・•・・・・・・•・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・•・・・・・・・・・・・・・・•・・・・・・・・・・・・・・・・・・72

‘7：•・・・♦♦・・・・・・・・♦・・・・・・・♦♦♦•・・・・・♦♦♦♦・・・・・♦♦♦♦・・・・・♦♦♦♦・・・・・・♦♦♦♦・・・・♦♦♦♦・・・・・♦♦♦♦・・・・・・♦♦♦♦・・・・・♦♦♦♦・・・・・・♦♦♦・・・・・♦♦♦♦・・・・・♦・♦♦・^7

令TOP-N问题73

令交叉查询问题.................................................73

。移除重复值问题............................................................73

第四节对象引用函数..............................................................73

第五节函数列表（字母序）........................................................73

7.ABS..........................................................................................................................................73

2.ACOS......................................................................................................................................73

3.ADD_MONTHS......................................................................................................................73

4.ASCII......................................................................................................................................74

5.ASIN........................................................................................................................................74

6.ATAN.......................................................................................................................................74

7.A7AN2.....................................................................................................................................74

8.AVG.........................................................................................................................................75

9.BINJV_NUM........................................................................................................................75

JO.BINAND................................................................................................................................75

〃.CHART0R0W1D..................................................................................................................76

12.COUNT.................................................................................................................................76

13.CURRENT_DATE.................................................................................................................76

14.CURRENT_TIMES7AMP.....................................................................................................76

15.DECODE..............................................................................................................................76

16.DENSE_RANK.....................................................................................................................77

17.DUMP...................................................................................................................................77

18.FIRST....................................................................................................................................77

79.FIRST_VALUE......................................................................................................................77

20.FLOOR.................................................................................................................................77

21.GREATEST...........................................................................................................................77

22.GROUPJD..........................................................................................................................77

23.GROUPING..........................................................................................................................77

24.GROUPINGJD....................................................................................................................77

25.INSTR...................................................................................................................................77

26.LAG......................................................................................................................................77

27.LAST.....................................................................................................................................78

28.LAST_DAY............................................................................................................................78

29.LAST_VALUE.......................................................................................................................78

30.LEAD....................................................................................................................................78

31.LEAST...................................................................................................................................78

32.LENGTH...............................................................................................................................78

33.LOWER.................................................................................................................................78

34.LRAD.....................................................................................................................................78

35.MAX......................................................................................................................................78

36.MIN.......................................................................................................................................79

37.MOD.....................................................................................................................................79

38.MONTHS_BETWEEN..........................................................................................................79

39.NEXT_DAY...........................................................................................................................79

40.NULLIF................................................................................................................................79

41.NVL.......................................................................................................................................79

42.NVL2.......................................................................................................................79

43.RANK...................................................................................................................................79

44.REPLACE...........................................................................................................................79

45.ROUND(date).....................................................................................................................80

46.ROUND(number)................................................................................................................80

47.ROW_NUMBER.................................................................................................................80

48.RPAD..................................................................................................................................80

49.RTR1M................................................................................................................................80

50.SOUNDEX..........................................................................................................................80

51.SUBSTR..............................................................................................................................80

52.SUM....................................................................................................................................80

53.SYSDATE............................................................................................................................80

54.SYSTIMESTAMP................................................................................................................81

55.TO_CHAR...........................................................................................................................81

56.TO_DATE...........................................................................................................................81

57.TO_NUMBER.....................................................................................................................81

58.TRANSLATE.......................................................................................................................81

59.TRIM....................................................................................................................................81

60.TRUNC(number)................................................................................................................81

61.TRUNC(date)......................................................................................................................81

62.UPPER...............................................................................................................................81

第五章后记.....................................................................82

第一章基本元素