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

✧✧ORACLE一.DDL(数据定义语言)Oracle自动在语句执行前后都加上commit, 则DDL之前的没提交改变被隐式提交, 不能回滚.数据类型1.合法表示符（标示符）1-30个字符,以字符(可以# $ _ )开始,数据库链接可以包含(@ , )两个特殊符号. 不能包含引号,不能是保留字.特殊情况: 数据库名称最多8个字符, 数据库链接最多128个字符, 把列名放入引号可以使用保留字作为列名.ORACLE数据库的端口默认是15212.NUMBER<column_name> Number(precision[,scale])precision: 有效位数总数, 1---38 , 包括小数位scale: 小数位数,没有小数可以省略小数和小数点; 省略此参数表示一个整数;当 scale> precision,则会在有效位之前加03.CHAR<column_name> char(size)size: 1---2000字节, 值不足size时,会自动在值后面补充空格.<column_name> 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-RR8.x: DD-Mon-RR7.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:3931-12月-04 12:00:0021-12月-04 12:00:0010.TIMESTAMP 对秒提供最多9位小数精度的时间. 9i new!Sysdate 可以直接产生一个timestamp的.<column_name> Timestamp [seconds_precision] //精度指出秒的小数位数,默认为6,可选0-9.11.TIMESTAMP WITH TIME ZONE<col> Timestamp [seconds_precision] WITH Time Zonetimestamp ‘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 tablecreate 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:25TIMESTAMPC 14-8月 -06 06.36.25.000000 下午TIMESTAMPZC 14-8月 -06 06.14.15.000000 下午 -05:00TIMESTAMPLOCALZC 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:25TIMESTAMPC 14-8月 -06 06.36.25.000000 下午TIMESTAMPZC 14-8月 -06 06.14.15.000000 下午 -05:00TIMESTAMPLOCALZC 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:25TIMESTAMPC 14-8月 -06 06.36.25.000000 下午TIMESTAMPZC 14-8月 -06 06.14.15.000000 下午 -05:00TIMESTAMPLOCALZC 14-8月 -06 02.14.15.000000 下午13.INTERVAL YEAR TO MONTHINTERVAL YEAR [(YEAR_PRECISION)] TO MONTH//精度默认2,可选0—914.INTERVAL DAY TO SECONDINTERVAL 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','²âÊÔintervalÊý¾ÝÀàÐÍ', timestamp '2006-08-14 19:12:19.000000 -5:00' , timestamp '2006-08-14 19:13:45.000000 -6:00' ) select * from intervalAiSee 测试interval数据类型 14-8月 -06 07.12.19.000000 下午 -05:00 14-8月 -06 07.13.45.000000 下午 -06:00insert into interval select name, event,begin,end,end-begin from intervalselect * from intervalAiSee 测试interval数据类型 14-8月 -06 07.12.19.000000 下午 -05:00 14-8月 -0607.13.45.000000 下午 -06:00AiSee 测试interval数据类型 14-8月 -06 07.12.19.000000 下午 -05:00 14-8月 -0607.13.45.000000 下午 -06:00 +0000 01:01:26.0015.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 YDEC2NUMBER NUMBER(9,2) YCHARACTER2VARCHAR2 VARCHAR2(30) YNATIONALCHAR2NCHAR NCHAR(3) Y等价于:-- Create tablecreate 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) NUMBERDOUBLEPRECISION NUMBERREAL NUMBERCHARACTER(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)二.SELECTSelect增值服务16.有哪些表,试图呢 ?select table_name from dba_tables;17.当前用户是谁 ?select user from user_tables group by user ;show user ; -- The same as above paragraphs18.当前用户有哪些表 :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_USEDFROM INDEX_STATS查询: 表名,索引高度,叶子结点数,行数,标记为删除的行数,索引结点占空间的百分比例(有自由空间时一定小于100)由于针对索引表,前期需要:SQL> create index index_objname_indexTest on indexTest(object_name) pctfree 10;索引已创建。