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

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

册

编写、修订、审核记录

目录

第一章基本元素 (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_seconds_precision ） (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)

1.操作符列表 (19)

2.操作符优先级 (19)

3.连接操作符 (19)

4.集合操作符 (19)

第二章表达式 (19)

第三章条件 (19)

第一节条件的优先级 (20)

第二节比较条件 (21)

第三节逻辑条件 (23)

第四节成员关系条件 (23)

第五节范围条件 (23)

第六节NULL条件 (23)

第四章函数 (23)

第一节单值函数 (23)

1.数值型函数 (24)

?MOD (24)

?ROUND (24)

?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)

? (29)

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

?LOWER (29)

?UPPER (30)

?LPAD (30)

?RPAD (31)

?TRIM (31)

?LTRIM (32)

?RTRIM (32)

?REPLACE (33)

?SOUNDEX (33)

?SUBSTR (34)

?TRANSLA TE (35)

? (36)

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

?INSTR (36)

?LENGTH (37)

? (37)

4.日期函数 (37)

?ADD_MONTHS (38)

?CURRENT_DA TE (39)

?CURRENT_TIMESTAMP (40)

?SYSDA TE (40)

?SYSTIMESTAMP (41)

?LAST_DAY (42)

?NEXT_DAY (42)

?MONTHS_BETWEEN (42)

?ROUND (43)

?TRUNC (44)

5.转换函数 (45)

?TO_CHAR (45)

?TO_NUMBER (47)

6.其他辅助函数 (47)

?DECODE (48)

?GREATEST (49)

?LEAST (49)

?NULLIF (50)

?NVL (50)

?NVL2 (51)

?SYS_CONNECT_BY_PA TH (52)

?SYS_CONTEXT (52)

第二节聚集函数 (52)

?AVG (54)

?COUNT (55)

?DENSE_RANK (56)

?RANK (57)

?FIRST (58)

?LAST (58)

?MAX (58)

?MIN (59)

?SUM (59)

? (60)

第三节分析函数 (60)

1.概述 (60)

2.工作机制 (61)

3.语法 (62)

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

?A VG (65)

?COUNT (66)

?FIRST_V ALUE (66)

?LAST_V ALUE (67)

?LAG (68)

?LEAD (68)

?MAX (69)

?MIN (69)

?DENSE_RANK (70)

?RANK (71)

?ROW_NUMBER (72)

?SUM (72)

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

6.常见问题 (73)

?计算动态累计问题 (73)

?TOP-N问题 (73)

?交叉查询问题 (73)

第四节对象引用函数 (73)

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

1.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)

15.DECODE (76)

16.DENSE_RANK (77)

17.DUMP (77)

18.FIRST (77)

19.FIRST_VALUE (77)

20.FLOOR (77)

21.GREATEST (77)

22.GROUP_ID (77)

23.GROUPING (77)

24.GROUPING_ID (77)

25.INSTR (77)

https://www.360docs.net/doc/1a5765524.html,G (77)

https://www.360docs.net/doc/1a5765524.html,ST (78)

https://www.360docs.net/doc/1a5765524.html,ST_DAY (78)

https://www.360docs.net/doc/1a5765524.html,ST_VALUE (78)

30.LEAD (78)

31.LEAST (78)

32.LENGTH (78)

33.LOWER (78)

34.LP AD (78)

35.MAX (78)

36.MIN (79)

37.MOD (79)

38.MONTHS_BETWEEN (79)

39.NEXT_DAY (79)

40.NULLIF (79)

41.NVL (79)

43.RANK (79)

44.REPLACE (79)

45.ROUND(date) (80)

46.ROUND(number) (80)

47.ROW_NUMBER (80)

48.RP AD (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.V ARCHAR2（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字节。

5.NUMBER（p，s）

oracle中唯一存储数字的数据类型。最大最小值分别为9.99...999 ×10125（38个9后面跟了88个0，因此它的数字精度就是38）和1.0×10-130。如果你使用的数字超过这个数字，那么oracle返回错误。

p表示这个数字一共有多少个数字组成，范围是从1到38。

s就表示0右边的精度有多少位，就小数是多少位，范围是-84到127。

p和s要么都不指定，就表示范围是使用最大值，即p=38,s=127；要么就指定p，那么s=0；要么都必须同时指定。不存在只指定s不指定p的情况。

下面给个例子，来详细说明这个类型：

下面是“不正常”情况，s的值大于p的值的情况：

从上面的例子可以看到，s是表示小数点右边的有多少个数字，即有多少为小数，如果

它为负值，那么就表示小数点左边有多少位数字；当所指定的数字超过了这个s精度指定的访问，那么oracle就执行四舍五入。p表示的是一共有多少为有效数字，不能超过这个有效数字，超过了就返回相应的错误。

6.DATE

时间类型，oracle存储时间的数据包括：世纪、年、月、日、时、分、秒。可见它只精确到秒级别。往date类型你们插入数据要使用函数to_date()，把date类型数据转换为字符串类型数据，要使用to_char()函数。

有关日期的计算：

可以对日期进行加、减计算，不能对其进行乘法和除法运算。指定一个数字，oracle 对其附加天作为单位来和日期进行计算。如sysdate-1 就表示昨天，sysdate+1就表示明天，sysdate-7就表示一周以前，而sysdate-10/1440就表示10分钟以前。

7.TIMESTAMP（factional_seconds_precision ）

TIMESTAMP表示时间戳类型，就是一个时间点。oracle存储的时间戳数据包括：年、月、日、时、分、秒。可见它不存储世纪。对于faction_seconds_precision，表示将秒精确到哪个级别，它的值是从0到9，默认是6，如果是3，就表示把秒精确到毫秒级别，9就表示精确到“纳米”级别。看个例子就明白了：

SQL> select to_char(systimestamp,'yyyy-mm-dd,hh24:mi:ssxff') time from dual;

TIME

--------------------------

2007-09-19,11:20:41.035829

存储TIMESTAMP类型，要使用TO_TIMESTAMP()函数，显示TIMESTAMP类型数据要使用TO_CHAR()函数，如例子中那样。

第二节NULL

null表示no value，unknown，它不表示0，也不表示一个空的字符串。

SQL函数中的NULL

所有的函数，除了REPLACE , NVL , CONCAT以外，如果你用null作为它的参数，那么结果就是返回null。

大部分的聚集函数如（COUNT，AVG），他们会忽略null，如一个列上面有10行记录，其中2行是null，那么count这个列的结果就是8。

例如

在增加月份函数中使用null进行计算的情况：

SQL> select to_char(sysdate,'yyyy-mm-dd') today from dual;

TODAY

----------

2007-09-23

SQL> select to_char(add_months(sysdate,1),'yyyy-mm-dd') another_day from dual;

ANOTHER_DA

----------

2007-10-23

SQL> select to_char(add_months(sysdate,null),'yyyy-mm-dd') another_day from dual; ANOTHER_DA

----------

聚集函数的情况：

22:11:28 SQL> select empno,ename,comm from emp;

EMPNO ENAME COMM

----- ------- ----------

7369 SMITH

7499 ALLEN 300

7521 WARD 500

7566 JONES

7654 MARTIN 1400

7698 BLAKE

7782 CLARK

7788 SCOTT

7839 KING

7844 TURNER 0

7876 ADAMS

7900 JAMES

7902 FORD

7934 MILLER

已选择14行。

22:11:43 SQL> select count(empno) from emp;

COUNT(EMPNO)

------------

14

22:12:05 SQL> select count(comm) from emp;

COUNT(COMM)

------------

4

条件中的null。

对NULL所进行的任何数学计算结果都是null，如10+null结果是null。

oracle始终认为两个null是不相等的（除了少数情况），不能用null=null这个条件来进行判断两个null是否相等，null不等于其他的null，你只能用is null或者is not null来判断某个记录的某个字段是不是null。

但是oracle在使用decode函数的时候，它认为两个null是相等的。

oracle还有一种情况也认为两个不同的null是相等的，就是在一个复合键中，如果这两个复合键值中非null部分是相等的，那么这两个复合键值是相等的。

下面给出null出现的条件表格：

例如：

SQL> select empno,ename,comm from emp;

EMPNO ENAME COMM

---------- ---------- ----------

7369 SMITH

7499 ALLEN 300

7521 WARD 500

7566 JONES

7654 MARTIN 1400

7698 BLAKE

7782 CLARK

7788 SCOTT

7839 KING

7844 TURNER 0

7876 ADAMS

7900 JAMES

7902 FORD

7934 MILLER

已选择14行。

SQL> select empno,ename,comm from emp where comm=null;

未选定行

SQL> select empno,ename,comm from emp where comm <> null;

未选定行

SQL> select empno,ename,comm from emp where comm is null;

EMPNO ENAME COMM

---------- ---------- ----------

7369 SMITH

7566 JONES

.......... .......... ..........

已选择10行。

SQL> select empno,ename,comm from emp where comm is not null; EMPNO ENAME COMM

---------- ---------- ----------

7499 ALLEN 300

7521 WARD 500

7654 MARTIN 1400

7844 TURNER 0

第三节伪列

伪列就像是一个表的列，但却不是，它不存储在表中。你可以对伪列进行查询，而却不能进行增删改。伪列包括下面几类：

1.CURRV AL 和NEXTV AL

这两个伪列主要是用于访问序列（sequence）的值。currval返回当前值，而nextval 返回下一个值并且导致sequence一次值的增加。

你只能通过下列的形式来访问这两个伪列：

sequencename.CURRVAL

sequencename.NEXTVAL

哪里可以使用sequence的值？

1、查询语句的select部分，但不是在子句的select中，如select a from （select b from。。。），

这个里面b就在查询语句的子句的select中，可以使用sequence的位置（即使用currval 和nextval的地方）是在a这个部位。

2、 insert语句的子句select部分中，如insert into t select a from。。。，就是在a这个位置。

3、 insert的value子句中，如insert into t values(a。。。)，就是在a这个位置。

4、 update的set子句中，如UPDATE T set a。。。，就是在a这个位置。

哪些情况对sequence值的使用有限制，即currval和nextval不能使用的场合？

1、 DELETE,SELECT,UPDATE的子句，如delete from t where a in (select a ....)，这个a

位置不能使用。

2、在查询视图和物化视图的语句中不能使用。

3、包含DISTINCT关键字的查询语句中不能使用。

4、包含GROUP BY和ORDER BY子句的查询语句中不能使用。

5、集合操作中不是第一个select语句的语句中不能使用，如SELECT a... UNION ALL

SELECT b ...，这里b的位置不能使用，a位置能用。

6、 WHERE中的select子句，如SELECT ... FROM ... WHERE (SELECT a...)中a的位置。

7、在create table和alter table中，某个有default值的列上不能使用。

8、 check约束中不能使用。

如何使用序列值？

当你建立一个序列的时候，你会指定这个序列的第一个值和增加值，当你在第一次调用这个序列的时候，你不能使用sequencename.CURRVAL来访问它的当前值，因为它还没开始，而必须使用sequencename.NEXTVAL来让它开始初始化第一个值，第一次使用这个时候返回的时候是序列的第一个值。之后在时候currval是返回序列的当前值，nextval 返回序列的下一个值并且导致序列增加一个值。

oracle对序列的增加只是一次性的，单向的，即它只增加，不能减少，并且只能增加一次。

下面给个简单的例子：

SQL> create sequence seq_test start with 1 increment by 1 nomaxvalue; --创建序列，第一值1，每次增加1

Sequence created.

SQL> select seq_test.currval from dual; --第一次必须通过nextval让序列初始化

select seq_test.currval from dual

*

ERROR at line 1:

ORA-08002: sequence SEQ_TEST.CURRVAL is not yet defined in this session

SQL> select seq_test.nextval from dual;--第一次初始化序列，让它得到第一值1

NEXTVAL

----------

1

SQL> select seq_test.currval from dual;--当前值

CURRVAL

----------

1

SQL> select seq_test.nextval from dual;--不是第一次，nextval返回下个值，并且序列增加1

NEXTVAL

----------

2

SQL> select seq_test.currval from dual;--经过nextval后，序列的确增加了

CURRVAL

----------

2

2.LEVEL

3.ROWID

rowid顾名思义就是某条记录在数据库中的存储地址，在9i中它包括下列内容：

1、这条记录属于哪个对象的，记录了这个对象在数据库中id。

2、这条记录属于数据文件的哪个块的

3、这条记录是数据块的哪个行（这个行就记录了这条数据）

4、这条记录属于哪个数据文件，以数据文件的id表示。

因此rowid能够快速的定位到数据在数据库中的位置，因此通过rowid来访问数据一般都是比较快的。

一般情况下，rowid具有唯一性，即一条记录一个rowid，但是在cluster中，多个表的公用部分是只存一个记录的，因此所有这些表的公用部分一个记录就一个rowid，其实那还

是一个记录一个rowid。

rowid伪列具有rowid类型或者是urowid类型。

rowid反映的是当前这个记录在数据库中的“逻辑”位置，即它是哪个对象的在哪个数据文件的哪个块的哪个行上，所以，当使用导入导出工具进行移动数据以后，rowid会发生变化，类似发生变化的还有如oracle对物理文件进行了相关的接合整理也会出现整理前后rowid发生变化等。

你可以在语句的select部分以及where部分中使用rowid这个伪列。注意它是伪列，即它不在表中存储，你只能查询而不能增删改。下面给个小例子：

SQL> select t.*,rowid from dept t;

DEPTNO DNAME LOC ROWID

---------- -------------- ------------- ------------------

10 ACCOUNTING NEW YORK AAAMfNAAEAAAAAQAAA

20 RESEARCH DALLAS AAAMfNAAEAAAAAQAAB

30 SALES CHICAGO AAAMfNAAEAAAAAQAAC

40 OPERATIONS BOSTON AAAMfNAAEAAAAAQAAD

SQL> select * from dept where rowid='AAAMfNAAEAAAAAQAAA';

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

4.ROWNUM

语句查询语句所返回的每一行记录，Oracle会给其指定一个顺序的数字（rownum），用于标志顺序，第一行记录是1，第一行记录是2，依此类推。

你可以通过rownum来实现限制返回的行数的目的，即在条件判断中使用rownum小于或者小于等于某个数的情况，请看下面这个例子：

SQL> select rownum,empno,ename from emp where rownum<3;

ROWNUM EMPNO ENAME

---------- ---------- ----------

1 7369 SMITH

2 7499 ALLEN

SQL> select rownum,empno,ename from emp where rownum<=3;

ROWNUM EMPNO ENAME

---------- ---------- ----------

1 7369 SMITH

2 7499 ALLEN

3 7521 WARD

ROWNUM一般只能用于< 或<=的情况，rownum使用=的时候只能是1，不能是其他任何数，否则不会返回结果，请看一个例子：

SQL> select rownum,empno,ename from emp where rownum=1;

ROWNUM EMPNO ENAME

---------- ---------- ----------

1 7369 SMITH

SQL> select rownum,empno,ename from emp where rownum=2;

未选定行

SQL> select rownum,empno,ename from emp where rownum=3;

未选定行

为什么呢？

如果指定的条件是ROWNUM = 2的话，对于满足其他条件的第一条记录如果返回的话ROWNUM = 1，但是这不满足ROWNUM = 2的条件，因此这条记录不会返回。找到下一条记录，这时候ROWNUM仍然等于1，因为目前还没有返回1条符合的数据，同理这条记录也不会满足ROWNUM = 2的条件，也不会返回直到最后一条，都不会满足ROWNUM = 2的条件。所以指定非1的值只能返回无。

rownum使用>的时候只能大于0或负数，而这没有任何意义，因此一般不会出现大于的情况。

由上面的例子，可以看出rownum是出现在where子句中和select子句中的，where 的执行是在order by子句执行的前面，因此当有order by的时候，rownum如果显示出来就会出现顺序错乱的现象，请看下面这个例子：

SQL> select rownum,empno,ename from emp where rownum<=3 order by ename;

ROWNUM EMPNO ENAME

---------- ---------- ----------

2 7499 ALLEN

1 7369 SMITH

3 7521 WARD

由此我们可以联想到这样一类句子怎么去做，就是返回某个表的前（后，最）多少行，请看下面这个例子，返回emp表的前（后）3行：

SQL> select rownum,empno,ename from emp;

ROWNUM EMPNO ENAME

---------- ---------- ----------

1 7369 SMITH

2 7499 ALLEN

3 7521 WARD

4 7566 JONES

5 7654 MARTIN

6 7698 BLAKE

7 7782 CLARK

8 7788 SCOTT

9 7839 KING

10 7844 TURNER

11 7876 ADAMS

12 7900 JAMES

13 7902 FORD

14 7934 MILLER

已选择14行。

SQL> select rownum,empno,ename from (select t.empno,t.ename from emp t order by empno) where rownum<=3;

ROWNUM EMPNO ENAME

---------- ---------- ----------

1 7369 SMITH

2 7499 ALLEN

3 7521 WARD

SQL> select rownum,empno,ename from (select t.empno,t.ename from emp t order by empno desc) where rownum<=3;

ROWNUM EMPNO ENAME

---------- ---------- ----------

1 7934 MILLER

2 7902 FORD

3 7900 JAMES

上面这种似乎大家还是不是很满意？因为你得到的所有最多少条，是经过某个列的排序以后才得到的，那么如何得到没有经过排序的，即你们认为在表中原始存储的后10条数据呢（前10条太简单了，之间rownum<11就可以了），或者是取道中间的第2到第3条数据呢？这个时候，我们就可以利用嵌套查询来实现这个操作，请看下面这个例子：

SQL> select * from (select rownum as rn,t1.empno,t1.ename from emp t1) where rn>2 and rn<5; RN EMPNO ENAME

---------- ---------- ----------

3 7521 WARD

4 7566 JONES

SQL> select * from (select * from (select rownum as rn,t1.empno,t1.ename from emp t1) order by rn desc) where rownum<3;

RN EMPNO ENAME

---------- ---------- ----------

14 7934 MILLER

13 7902 FORD

5.XMLDATA

第四节注释

在sql语句内部的注释不会影响到语句的执行，但是却能增加语句的可读性与可维护性。它可以出现在任何的关键字，参数和标点符号之间。有两种注释形式：

1、以“/*”开始，以“*/”结束，在这之间的所有东西都是注释部分，可以跨行，多行注

释。

2、以“--”开始，此行之后的所有部分为注释，不能跨行，单行注释。

第五节数据库的对象

1.模式对象

一个模式（schema）就是数据库的数据逻辑结构的集合，或称模式对象。一个模式属于一个数据库的用户，并且它的名字就是用户的名字，如在数据库中有一个taobao用户，那么在数据库中就有一个taobao模式，taobao用户的所有对象的集合就称为taobao模式。因此，很多情况下，它们两个通用。

既然模式对象和某个用户是相互对应关联的，也就是说某个用户存在的时候，其相对应的模式对象才有存在的意义，因此对应的用户被删除以后，其所对应的模式对象也将全部被删除，基于这些模式对象而建立起来的其他的对象都将失效，如a用户存储过程p是中引用了b用户的表t，那么b被删除以后，t被删除，p不会被删除，但是p过程就失效了。

2.非模式对象

非模式对象，与模式对象进行对照，很显然，它是不会随着用户的消失而离开的独立的

具有讽刺意味的是user也是非模式对象，可以这样理解用户a独立于、不依赖于用户b而存在。

3.部分模式对象

第六节格式模型

1.时间格式

第七节操作符

1.操作符列表

2.操作符优先级

3.连接操作符

4.集合操作符

第二章表达式

第三章条件

所谓条件就是通过计算指定的一个或者多个表达式和逻辑操作，而返回一个布尔值TRUE、FALSE或者UNKNOWN。

条件可以分为很多种类型，有以下几类：