完美Oracle数据库知识学习文档总汇

?

?ORACLE

一.DDL(数据定义语言)

Oracle自动在语句执行前后都加上commit, 则DDL之前的没提交改变被隐式提交, 不能回滚.

数据类型

1.合法表示符（标示符）

1-30个字符,以字符(可以# $ _ )开始,数据库链接可以包含(@ , )两个特殊符号. 不能包含引号,不能是保留字.

特殊情况: 数据库名称最多8个字符, 数据库链接最多128个字符, 把列名放入引号可以使用保留字作为列名.

ORACLE数据库的端口默认是1521

2.NUMBER

Number(precision[,scale])

precision: 有效位数总数, 1---38 , 包括小数位

scale: 小数位数,没有小数可以省略小数和小数点; 省略此参数表示一个整数;当 scale> precision,则会在有效位之前加0

3.CHAR

char(size)

size: 1---2000字节, 值不足size时,会自动在值后面补充空格.

char(size [CHAR|BYTE]) 指出基于字符或字节,9i以上版本

考虑数据库参数 NLS_LENGTH_SEMATICS

(show parameters nls_length_semantics 可以看当前设置).

4.NCHAR

与char差别在于它规定的是最大字符个数.支持Unicode可变长度的字符集. 最大也是2000字节容量.

5.VarChar2

与char差别仅仅是:不会自动在值后面补充空格.最大4000字节,默认基于字节.

6.NVarChar2

存储可变长度且基于字符数量的数据.

7.RAW

与VarChar2类似,用于可变长度的二进制数据,但最大只有2000字节.

8.LOB

大对象,包括BLOB(二进制字节) CLOB(字符) NCLOB(国家字符) BFILE(二进制文件), 最大可到4GB 的数据. 支持分段访问, 支持Oracle对象, 还可以用PL/SQL的DBMS_LOB包进行处理.

9.Date 精确到秒的时间. 通常被误认为只是到天. 9i之前唯一存在的时间类型.

Date ‘yyyy-mm-dd’生成一个日期, date ‘2006-08-15’ . 这里没有小时,分,秒, 也必须yyyy-mm-dd的格式.

To_date(日期值, 格式串) 生成一个日期, 格式串: yyyy mm dd 分别表示年月日.

使用DD- MON-YYYY, MON-DD-YYYY YYYY-MON-DD-YYYY表示时都应该基于其安装的系统, 中文Oracle 需要使用’月’标识月字段.

show parameters nls_date_format(查看默认日期格式),

9i默认的格式: DD-Mon-RR

8.x: DD-Mon-RR

7.3及以下: DD-Mon-YY

如果在windows系统中设置了了注册表中的NLS_LANG, 则所有的NLS_*参数都会被忽略. 这时或者根本没有设置nls_date_format参数, 使用不加格式串的to_date函数就会失败.

--- insert into Test(event,"data")

--- values('Table Made',TO_DATE('Dec-2-2004','MON-DD-YYYY') ); //Dec无效! 中文Oracle需要使用’月’标识月字段.

insert into Test(event,"data")

values('Table Made',SYSDATE ) ; //SYSDATE生成系统日期

insert into test2(event,"Date")

values('Table Made',TO_DATE('01-12月-04','DD-MON-YY') ) ;

insert into test2(event,"Date")

values ('Insert first value', to_date('12月-21–2004 ', ' MON-DD - YYYY '));

insert into test2(event,"Date")

values ('Insert first value', to_date('2004-12月-31','YYYY-MON -DD') );

insert into test2(event,"Date")

values('Table Made',TO_DATE('01-12月-04','DD-MON-YYYY'));

insert into test2(event,"Date")

values('Table Made',TO_DATE('01-12月-03'))

// 日-月-年月-日-年年-月-日三种顺序有效 !!;

SQL> select to_char("Date",'DD-MON-YY HH12:MI:SS') "Date" from test2;

Date

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

31-12月-04 03:52:39

31-12月-04 12:00:00

21-12月-04 12:00:00

10.TIMESTAMP 对秒提供最多9位小数精度的时间. 9i new!

Sysdate 可以直接产生一个timestamp的.

Timestamp [seconds_precision] //精度指出秒的小数位数,默认为6,可选0-9.

11.TIMESTAMP WITH TIME ZONE

Timestamp [seconds_precision] WITH Time Zone

timestamp ‘2004-12-31 15:00:000000 GMT’

timestamp ‘2006-08-14 17:41:000000 –5:00’

12.TIMESTAMP WITH LOCAL TIME ZONE

插入的数据在查询时将考虑时区转换到当前时区.

alter session set time_zone=’-05:00’ //设置时区, 执行查询时将依据设置的时区返回相应的结果.

插入:

timestamp ‘2004-12-31 20:00:000000 GMT’

如果当地时区为-05:00,则查询结果为

31-Dec-04 03.00.00.000000PM

改时区为’GMT’再次查询,结果为:

31-Dec-04 08.00.00.000000PM

-- Create table

create table TIMECOMP

(

EVENT VARCHAR2(100),

NORMALC DATE,

TIMESTAMPC TIMESTAMP(6),

TIMESTAMPZC TIMESTAMP(6) WITH TIME ZONE,

TIMESTAMPLOCALZC TIMESTAMP(6) WITH LOCAL TIME ZONE

)

insert into timecomp (event,normalc,timestampc,timestampzc,timestamplocalzc)

values('Test 各个日期的区别',sysdate, sysdate,

timestamp '2006-08-14 18:14:15.000000 -5:00',

timestamp '2006-08-14 18:14:15.000000 EST')

select * from timecomp;

EVENT Test 各个日期的区别

NORMALC 2006-8-14 18:36:25

TIMESTAMPC 14-8月 -06 06.36.25.000000 下午

TIMESTAMPZC 14-8月 -06 06.14.15.000000 下午 -05:00

TIMESTAMPLOCALZC 15-8月 -06 06.14.15.000000 上午

alter session set time_zone = '-5:00'

select * from timecomp;

EVENT Test 各个日期的区别

NORMALC 2006-8-14 18:36:25

TIMESTAMPC 14-8月 -06 06.36.25.000000 下午

TIMESTAMPZC 14-8月 -06 06.14.15.000000 下午 -05:00

TIMESTAMPLOCALZC 14-8月 -06 05.14.15.000000 下午

alter session set time_zone = '-8:00'

select * from timecomp;

EVENT Test 各个日期的区别

NORMALC 2006-8-14 18:36:25

TIMESTAMPC 14-8月 -06 06.36.25.000000 下午

TIMESTAMPZC 14-8月 -06 06.14.15.000000 下午 -05:00

TIMESTAMPLOCALZC 14-8月 -06 02.14.15.000000 下午

13.INTERVAL YEAR TO MONTH

INTERVAL YEAR [(YEAR_PRECISION)] TO MONTH

//精度默认2,可选0—9

14.INTERVAL DAY TO SECOND

INTERVAL DAY[(DAY_PRCISION)] TO SECOND [(SECOND_ PRECISION)] //可选0-9, 日精度默认2, 秒精度默认6 .

create table INTERVAL

(

NAME VARCHAR2(20),

EVENT VARCHAR2(100),

BEGIN TIMESTAMP(6) WITH TIME ZONE,

END TIMESTAMP(6) WITH TIME ZONE,

BREAK INTERVAL DAY(4) TO SECOND(2)

)

insert into interval(name,event,begin,end)

values ('AiSee','2aê?intervalêy?YààDí', timestamp '2006-08-14 19:12:19.000000 -5:00' , timestamp '2006-08-14 19:13:45.000000 -6:00' ) select * from interval

AiSee 测试interval数据类型 14-8月 -06 07.12.19.000000 下午 -05:00 14-8月 -06 07.13.45.000000 下午 -06:00

insert into interval select name, event,begin,end,end-begin from interval

select * from interval

AiSee 测试interval数据类型 14-8月 -06 07.12.19.000000 下午 -05:00 14-8月 -06

07.13.45.000000 下午 -06:00

AiSee 测试interval数据类型 14-8月 -06 07.12.19.000000 下午 -05:00 14-8月 -06

07.13.45.000000 下午 -06:00 +0000 01:01:26.00

15.ANSI 数据类型

ORACLE支持工业标准的ANSI数据类型, 以及IBM DB/2 和 SQL/DS数据类型. 出于某种原因选择它们比较合适:

一些数据库设计工具可以导出能被其他数据库使用的ANSI兼容的SQL.

在其他数据库上使用的SQL教本不需要转换称为ORACLE语法就可以在ORACLE使用,

当使用ANSI, IBM BD/2 , SQL/DS 数据类型建立列的时候, 会被隐式转换位合适的

ORACLE内部类型.

create table ANSITYPE2

(

INTEGER2NUMBER INTEGER,

DEC2NUMBER dec(9,2),

CHARACTER2VARCHAR2 character varying(30),

NATIONALCHAR2NCHAR National CHAR(3)

)

SQL> desc ANSITYPE ;

Name Type Nullable Default Comments

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

INTEGER2NUMBER INTEGER Y

DEC2NUMBER NUMBER(9,2) Y

CHARACTER2VARCHAR2 VARCHAR2(30) Y

NATIONALCHAR2NCHAR NCHAR(3) Y

等价于:

-- Create table

create table ANSITYPE

(

INTEGER2NUMBER INTEGER,

DEC2NUMBER NUMBER(9,2),

CHARACTER2VARCHAR2 VARCHAR2(30),

NATIONALCHAR2NCHAR NCHAR(3)

)

可用SQL数据类型Oracle等价数据类型

NUMERIC(PRECISION,SCALE) NUMBER(PRECISION,SCALE)

DECIMAL(PRECISION,SCALE) NUMBER(PRECISION,SCALE)

INTEGER NUMBER(38)

INT NUMBER(38)

SMALLINT NUMBER(38)

FLOAT(b) NUMBER

DOUBLEPRECISION NUMBER

REAL NUMBER

CHARACTER(size) CHAR(size)

CHAR(size) CHAR(size)

CHARACTER VARYING(size) VARCHAR(size)

CHAR VARING(size) VARCHAR(size)

NATIONAL CHARACTER(size) NCHAR(size)

NATIONAL CHAR(size) NCHAR(size)

NCHAR(size) NCHAR(size) NATIONAL CHARACTER VARYING(size) NVARCHAR2(size) NATIONAL CHAR VARYING(size) NVARCHAR2(size)

NCHAR VARYING(size) NVARCHAR2(size)

二.SELECT

Select增值服务

16.有哪些表,试图呢 ?

select table_name from dba_tables;

17.当前用户是谁 ?

select user from user_tables group by user ;

show user ; -- The same as above paragraphs

18.当前用户有哪些表 :

SQL> select table_name from user_tables ;

19.选择某一用户的表名

select table_name from dba_tables where owner = 'SYSTEM' ;

20.想知道有多少个用户吗 ?

select owner from dba_tables group by owner ;

21.想知道表EMP是谁的吗 ?

select owner from dba_tables where table_name = 'EMP' ;

22.索引增加的描述

SELECT NAME ,HEIGHT,LF_BLKS,LF_ROWS,DEL_LF_ROWS, PCT_USED

FROM INDEX_STATS

查询: 表名,索引高度,叶子结点数,行数,标记为删除的行数,索引结点占空间的百分比例(有自由空间时一定小于100)

由于针对索引表,前期需要:

SQL> create index index_objname_indexTest on indexTest(object_name) pctfree 10;

索引已创建。

SQL> analyze index index_objname_indexTest validate structure ;

索引已分析

SQL> analyze indexTest index_objname_indexTest compute statistics [ for owner ] ;

索引已分析

查询过滤

23.where 限定语句

select from

where

a=b a>b ab [ NULL 不等于NULL ]

BETWEEN a AND b

a LIKE

b [ 表达式b 使用通配符 %表示0,1,多个字符 _ 代表一个字符 ]

a NOT LIKE <条件>

24.and / or :

在where中实现连接多个子句,形成复合条件,

注意 AND 的优先级高于 OR

注意可以用（括号）提高优先级

25.DISTINCT 不想要重复的信息 ?