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

第1页共37页ORACLE一.DDL(数据定义语言)Oracle自动在语句执行前后都加上commit,则DDL之前的没提交改变被隐式提交,不能回滚.数据类型1.合法表示符（标示符）1-30个字符,以字符(可以#$_)开始,数据库链接可以包含(@,)两个特殊符号.不能包含引号,不能是保留字.特殊情况:数据库名称最多8个字符,数据库链接最多128个字符,把列名放入引号可以使用保留字作为列名.ORACLE数据库的端口默认是15212.NUMBERcolumn_nameNumber(precision[,scale])precision:有效位数总数,1---38,包括小数位scale:小数位数,没有小数可以省略小数和小数点;省略此参数表示一个整数;当scaleprecision,则会在有效位之前加03.CHARcolumn_namechar(size)size:1---2000字节,值不足size时,会自动在值后面补充空格.column_namechar(size[CHAR|BYTE])指出基于字符或字节,9i以上版本考虑数据库参数NLS_LENGTH_SEMATICS(showparametersnls_length_semantics可以看当前设置).4.NCHAR与char差别在于它规定的是最大字符个数.支持Unicode可变长度的字符集.最大也是2000字节容量.5.VarChar2与char差别仅仅是:不会自动在值后面补充空格.最大4000字节,默认基于字节.6.NVarChar2存储可变长度且基于字符数量的数据.7.RAW第2页共37页2与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(日期值,格式串)生成一个日期,格式串:yyyymmdd分别表示年月日.使用DD-MON-YYYY,MON-DD-YYYYYYYY-MON-DD-YYYY表示时都应该基于其安装的系统,中文Oracle需要使用’月’标识月字段.showparametersnls_date_format(查看默认日期格式),9i默认的格式:DD-Mon-RR8.x:DD-Mon-RR7.3及以下:DD-Mon-YY如果在windows系统中设置了了注册表中的NLS_LANG,则所有的NLS_*参数都会被忽略.这时或者根本没有设置nls_date_format参数,使用不加格式串的to_date函数就会失败.---insertintoTest(event,data)---values('TableMade',TO_DATE('Dec-2-2004','MON-DD-YYYY'));//Dec无效!中文Oracle需要使用’月’标识月字段.insertintoTest(event,data)values('TableMade',SYSDATE);//SYSDATE生成系统日期insertintotest2(event,Date)values('TableMade',TO_DATE('01-12月-04','DD-MON-YY'));insertintotest2(event,Date)values('Insertfirstvalue',to_date('12月-21–2004','MON-DD-YYYY'));insertintotest2(event,Date)values('Insertfirstvalue',to_date('2004-12月-31','YYYY-MON-DD'));insertintotest2(event,Date)values('TableMade',TO_DATE('01-12月-04','DD-MON-YYYY'));第3页共37页3insertintotest2(event,Date)values('TableMade',TO_DATE('01-12月-03'))//日-月-年月-日-年年-月-日三种顺序有效!!;SQLselectto_char(Date,'DD-MON-YYHH12:MI:SS')Datefromtest2;Date-------------------31-12月-0403:52:3931-12月-0412:00:0021-12月-0412:00:0010.TIMESTAMP对秒提供最多9位小数精度的时间.9inew!Sysdate可以直接产生一个timestamp的.column_nameTimestamp[seconds_precision]//精度指出秒的小数位数,默认为6,可选0-9.11.TIMESTAMPWITHTIMEZONEcolTimestamp[seconds_precision]WITHTimeZonetimestamp‘2004-12-3115:00:000000GMT’timestamp‘2006-08-1417:41:000000–5:00’12.TIMESTAMPWITHLOCALTIMEZONE插入的数据在查询时将考虑时区转换到当前时区.altersessionsettime_zone=’-05:00’//设置时区,执行查询时将依据设置的时区返回相应的结果.插入:timestamp‘2004-12-3120:00:000000GMT’如果当地时区为-05:00,则查询结果为31-Dec-0403.00.00.000000PM改时区为’GMT’再次查询,结果为:31-Dec-0408.00.00.000000PM--CreatetablecreatetableTIMECOMP(EVENTVARCHAR2(100),NORMALCDATE,TIMESTAMPCTIMESTAMP(6),TIMESTAMPZCTIMESTAMP(6)WITHTIMEZONE,TIMESTAMPLOCALZCTIMESTAMP(6)WITHLOCALTIMEZONE第4页共37页4)insertintotimecomp(event,normalc,timestampc,timestampzc,timestamplocalzc)values('Test各个日期的区别',sysdate,sysdate,timestamp'2006-08-1418:14:15.000000-5:00',timestamp'2006-08-1418:14:15.000000EST')select*fromtimecomp;EVENTTest各个日期的区别NORMALC2006-8-1418:36:25TIMESTAMPC14-8月-0606.36.25.000000下午TIMESTAMPZC14-8月-0606.14.15.000000下午-05:00TIMESTAMPLOCALZC15-8月-0606.14.15.000000上午altersessionsettime_zone='-5:00'select*fromtimecomp;EVENTTest各个日期的区别NORMALC2006-8-1418:36:25TIMESTAMPC14-8月-0606.36.25.000000下午TIMESTAMPZC14-8月-0606.14.15.000000下午-05:00TIMESTAMPLOCALZC14-8月-0605.14.15.000000下午altersessionsettime_zone='-8:00'select*fromtimecomp;EVENTTest各个日期的区别NORMALC2006-8-1418:36:25TIMESTAMPC14-8月-0606.36.25.000000下午TIMESTAMPZC14-8月-0606.14.15.000000下午-05:00TIMESTAMPLOCALZC14-8月-0602.14.15.000000下午13.INTERVALYEARTOMONTHINTERVALYEAR[(YEAR_PRECISION)]TOMONTH//精度默认2,可选0—914.INTERVALDAYTOSECONDINTERVALDAY[(DAY_PRCISION)]TOSECOND[(SECOND_PRECISION)]//可选0-9,日精度默认2,秒精度默认6.createtableINTERVAL(NAMEVARCHAR2(20),第5页共37页5EVENTVARCHAR2(100),BEGINTIMESTAMP(6)WITHTIMEZONE,ENDTIMESTAMP(6)WITHTIMEZONE,BREAKINTERVALDAY(4)TOSECOND(2))insertintointerval(name,event,begin,end)values('AiSee','²âÊÔintervalÊý¾ÝÀàÐÍ',timestamp'2006-08-1419:12:19.000000-5:00',timestamp'2006-08-1419:13:45.000000-6:00')select*fromintervalAiSee测试interval数据类型14-8月-0607.12.19.000000下午-05:0014-8月-0607.13.45.000000下午-06:00insertintointervalselectname,event,begin,end,end-beginfromintervalselect*fromintervalAiSee测试interval数据类型14-8月-0607.12.19.000000下午-05:0014-8月-0607.13.45.000000下午-06:00AiSee测试interval数据类型14-8月-0607.12.19.000000下午-05:0014-8月-0607.13.45.000000下午-06:00+000001:01:26.0015.ANSI数据类型ORACLE支持工业标准的ANSI数据类型,以及IBMDB/2和SQL/DS数据类型.出于某种原因选择它们比较合适:一些数据库设计工具可以导出能被其他数据库使用的ANSI兼容的SQL.在其他数据库上使用的SQL教本不需要转换称为ORACLE语法就可以在ORACLE使用,当使用ANSI,IBMBD/2,SQL/DS数据类型建立列的时候,会被隐式转换位合适的ORACLE内部类型.createtableANSITYPE2(INTEGER2NUMBERINTEGER,DEC2NUMBERdec(9,2),CHARACTER2VARCHAR2charactervarying(30),NATIONALCHAR2NCHARNationalCHAR(3))SQLdescANSITYPE;NameTypeNullableDefaultComments-----------------------------------------------------第6页共37页6INTEGER2NUMBERINTEGERYDEC2NUMBERNUMBER(9,2)YCHARACTER2VARCHAR2VARCHAR2(30)YNATIONALCHAR2NCHARNCHAR(3)Y等价于:--CreatetablecreatetableANSITYPE(INTEGER2NUMBERINTEGER,DEC2NUMBERNUMBER(9,2),CHARACTER2VARCHAR2VARCHAR2(30),NATIO