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

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

册

编写、修订、审核记录

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

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.CURRVALfaNEXTVAL........................................................................................................11

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

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

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

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

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

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

I.模式对象....................................................................16

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

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

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

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

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

1.操作符列表..................................................................19

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

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

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

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

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

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

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

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

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

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

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

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

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

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

<MOD.............................................................................................................................................................24

4ROUND........................................................................................................................................................24

令TRUNC...........................................................................................................................................................26

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

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

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

令ATAN..............................................................................................................................................................27

令ATAN2............................................................................................................................................................28

0BIN_TO_NUM..............................................................................................................................................29

令BITAND.........................................................................................................................................................29

◊CEIL...............................................................................................................................................................29

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

OFLOOR...........................................................................................................................................................29

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

QSINH...............................................................................................................................................................29

令........................................................................................................29

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

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

令UPPER............................................................................................................................................................30

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

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

令TRIM..............................................................................................................................................................31

令UTRIM............................................................................................................................................................32

令RTRIM............................................................................................................................................................32

0REPLACE.....................................................................................................................................................33

令SOUNDEX....................................................................................................................................................33

◊SUBSTR.........................................................................................................................................................34

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

O..............................................................................................................................................................................36

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

QINSTR..............................................................................................................................................................36

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

。........................................................................................................37

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

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

令CURRENT.DATE........................................................................................................................................39

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

令SYSDATE......................................................................................................................................................40

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

令LAST.DAY...................................................................................................................................................42

0NEXT_DAY..................................................................................................................................................42

令MONTHS.BETWEEN.................................................................................................................................42

◊ROUND..........................................................................................................................................................43

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

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

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

令TO_DATE.....................................................................................................................................................47

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

令DECODE48

令GREATEST.................................................................................................................................................49

令LEAST...........................................................................................................................................................49

0NULLIF.........................................................................................................................................................50

令NVL...............................................................................................................................................................50

令NVL2.............................................................................................................................................................51

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

OSYS_CONTEXT..........................................................................................................................................52

第二节聚集函数...............................................................52

°AVG...........................................................................................................................54

0DENSE_RANK.........................................................................................................56

。RANK.........................................................................................................................57

。FIRST.........................................................................................................................58

°MAX...........................................................................................................................58

。MIN............................................................................................................................59

0SUM..........................................................................................................................59

。...................................................................................60

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

7.概述...........................................................................................................................60

2.工作机制...................................................................................................................61

3语法62

4.常用的分析函数.........................................................................................................65

。LAST_VALUE.............................................................................................................................................67

令MIN...............................................................................................................................................................69

令DENSE.RANK............................................................................................................................................70

令RANK...........................................................................................................................................................71

5.不太常用的分析函数..................................................................................................73

6.常见问题...................................................................................................................73

。计算动态累计问题..............................................................73

<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.ATAN2.....................................................................................................................................74

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

9.BIN_TO_NUM........................................................................................................................75

10.BINAND................................................................................................................................75

11.CHARTOROWID..................................................................................................................76

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

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

14.CURRENT_TIMESTAMP.....................................................................................................76

75.DECODE.............................................................................................................................76

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

77.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.MG......................................................................................................................................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.LPAD.....................................................................................................................................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.RTRIM................................................................................................................................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

第一章基本元素

第一节数据类型

1.VARCHAR2(size)：

可变长度的字符串，最大size个字节。size最大可取值是4000,如果你指定了size是

4000,然后插入超过4000子句的字符串，oracle会字段截取先进入库的4000字节，后面

的它就不管了。如果你知道的size没有4000,而是其他一个数字如200,那么如果你插入

的字符串超过200,oracle就会返回一个错误。size必须指定个正数。

有关VARCHAR类型，它其实和VARCHAR2一样，是它的同义词。但是不宜指定

VARCHAR作为数据类型，因为你不确定oracle哪天头脑发热，把VARCHAR的含义改掉

了或者不支持它，那就完了。

2.CHAR(size)

固定长度的字符串，最大size个字节。如果你插入的字符串没有size个字节,那么oracle

就会使用空白来填充它。size最大可取值是2000,如果你指定了size是2000,然后插入

超过2000子句的字符串，oracle会字段截取先进入库的4000字节，后面的它就不管了。

如果你知道的size没有2000,而是其他一个数字如200,那么如果你插入的字符串超过200,

oracle就会返回一个错误。如果你不指定size,那么它的默认值就是1。

3.LONG

用于存储字符串，最多可以存储2G个字节的字符。LONG类型有很多的限制，已经不

被oracle推荐使用，大家最好不要用，会有一系列操作限制，导致一些隐形的问题。

4.CLOB

存储字符串，即支持可变长度的，也支持固定长度的字符串，最大可支持到4G字节。