淘寶網開發(fā)人員數據庫知識手冊

淘寶網開發(fā)人員數據庫知識參考手

冊

編寫、修訂、審核記錄

編號文檔版本修訂章節(jié)修訂原因修訂日期修訂人

11.0全文新建文檔2007-9-27勝通

22.0補充文檔2008-1-6勝通

3

4

5

6

7

8

9

10

目錄

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

第一節(jié)數據類型................................................................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

第二節(jié)NULL.......................................................................................................................................8

第三節(jié)偽列..........................................................11

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

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

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

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

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

第四節(jié)注釋...................................................................15

第五節(jié)數據庫的對象...........................................................16

1.模式對象.................................................................................................................................16

2.非模式對象............................................................................................................................17

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

第六節(jié)格式模型...............................................................17

1.時間格式................................................................................................................................17

第七節(jié)操作符.................................................................19

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

2.操作符優(yōu)先級....................................................................................................................19

3.連接操作符........................................................................................................................19

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

第二章表達式...................................................................19

第三章條件.....................................................................19

第一節(jié)條件的優(yōu)先級.........................................20

第二節(jié)比較條件...............................................................21

第三節(jié)邏輯條件...............................................................23

第四節(jié)成員關系條件...........................................................23

第五節(jié)范圍條件...............................................................23

第六節(jié)NULL條件..............................................................23

第四章函數.....................................................................23

第一節(jié)單值函數...............................................................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

第三節(jié)分析函數...............................................................60

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

?一I一????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????1

3.語...........................................................................62

4?f)49J???????????????????????????????????????????????????????????????????????????????????????????????????????????????????

????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????■?^^5

?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

令MIN69

?DENSE_RANK70

?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????72

‘7：???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????^7

令TOP-N問題73

令交叉查詢問題.................................................73

。移除重復值問題............................................................73

第四節(jié)對象引用函數..............................................................73

第五節(jié)函數列表（字母序）........................................................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

第一章基本元素