ORACLE数据库SQL基础培训

海颐软件股份有限公司ORACLE技术文档之一第1页共90页保密1ORACLE数据库SQL应用基础培训培训目标：了解Oracle，为开发服务初级培训：计划培训：Oracle的一些常用函数、SQL技巧、SQL优化以及一些需要注意的问题、树view_next_dept、物化视图、索引面向对象：Oracle刚入门中级培训：计划培训：Oracle数据库管理，包括数据库的创建、日常维护、数据库监控面向对象：Oracle已入门高级培训：计划培训：Oracle数据库备份与回复、数据库高级复制面向对象：Oracle比较熟练1．Oracle基本知识一个表空间只能属于一个数据库每个数据库最少有一个控制文件（建议=3个，分别放在不同的磁盘上）每个数据库最少有一个表空间（SYSTEM表空间）建立SYSTEM表空间的目的是尽量将目的相同的表存放在一起，以提高使用效率，只应存放数据字典每个数据库最少有两个联机日志组，每组最少一个联机日志文件一个数据文件只能属于一个表空间一个数据文件一旦被加入到一个表空间中，就不能再从这个表空间中移走，也不能再加入到其他表空间中海颐软件股份有限公司ORACLE技术文档之一第2页共90页保密2建立新的表空间需要建立新的数据文件数据文件被ORACLE格式化为ORACLE块，Oracle9i以前版本中，ORACLE块的大小是在第一次创建数据库时设定的，并且以后不能改变，要想改变，只能重建数据库一个段segment只能属于一个表空间，但可以属于多个数据文件一个区extent只能属于一个数据文件，即区间（extent）不能跨越数据文件PCTFREE和PCTUSED总和不能大于等于100单独一个事务不能跨越多个回滚段索引表不含ROWID值拥有不同大小的回滚段没有任何益处COMMIT后，数据不一定立即写盘（数据文件）一个事务即使不被提交，也会被写入到重做日志中。Oracle8.0.4中，在初始安装时建立的缺省数据库，实例名为ORCL一个块的最大长度为16KB(有2K、4K、8K、16K)每个数据库最大文件数（按块大小）2K块20000个文件4K块40000个文件8K块或以上65536个文件Oracleserver可以同时启动多个数据库一套操作系统上可以安装多个版本的ORACLE数据库系统（UNIX可以，NT不可以）一套ORACLE数据库系统中可以有多个ORACLE数据库及其相对应的实例海颐软件股份有限公司ORACLE技术文档之一第3页共90页保密3每个ORACLE数据库拥有一个数据库实例（INSTANCE）（OPS除外）所以，一套操作系统上同时可以有多个oracle数据库实例启动2．Oracle数据类型Oracle8数据类型数据类型参数描述char(n)n=1to2000字节定长字符串，n字节长，如果不指定长度，缺省为1个字节长（一个汉字为2字节）varchar2(n)n=1to4000字节可变长的字符串，具体定义时指明最大长度n，这种数据类型可以放数字、字母以及ASCII码字符集(或者EBCDIC等数据库系统接受的字符集标准)中的所有符号。如果数据长度没有达到最大值n，Oracle8i会根据数据大小自动调节字段长度，如果你的数据前后有空格，Oracle8i会自动将其删去。VARCHAR2是最常用的数据类型。可做索引的最大长度3209。number(m,n)m=1to38n=-84to127可变长的数值列，允许0、正值及负值，m是所有有效数字的位数，n是小数点以后的位数。如：number(5,2)，则这个字段的最大值是99,999，如果数值超出了位数限制就会被截取多余的位数。如：number(5,2)，但在一行数据中的这个海颐软件股份有限公司ORACLE技术文档之一第4页共90页保密4字段输入575.316，则真正保存到字段中的数值是575.32。如：number(3,0)，输入575.316，真正保存的数据是575。date无从公元前4712年1月1日到公元4712年12月31日的所有合法日期，Oracle8i其实在内部是按7个字节来保存日期数据，在定义中还包括小时、分、秒。缺省格式为DD-MON-YY，如07-11月-00表示2000年11月7日。long无可变长字符列，最大长度限制是2GB，用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。long是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。raw(n)n=1to2000可变长二进制数据，在具体定义字段的时候必须指明最大长度n，Oracle8i用这种格式来保存较小的图形文件或带格式的文本文件，如MiceosoftWord文档。raw是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。longraw无可变长二进制数据，最大长度是2GB。Oracle8i用这种格式来保存较大的图形文件或带格式的文本文件，如MiceosoftWord文档，以及音频、视频等非文本文件。在同一张表中不能同时有long类型和long海颐软件股份有限公司ORACLE技术文档之一第5页共90页保密5raw类型，longraw也是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。blobclobnclob无三种大型对象(LOB)，用来保存较大的图形文件或带格式的文本文件，如MiceosoftWord文档，以及音频、视频等非文本文件，最大长度是4GB。LOB有几种类型，取决于你使用的字节的类型，Oracle8i实实在在地将这些数据存储在数据库内部保存。可以执行读取、存储、写入等特殊操作。bfile无在数据库外部保存的大型二进制对象文件，最大长度是4GB。这种外部的LOB类型，通过数据库记录变化情况，但是数据的具体保存是在数据库外部进行的。Oracle8i可以读取、查询BFILE，但是不能写入。大小由操作系统决定。3．OracleSQL*Plus中的一些命令show和set命令是两条用于维护SQL*Plus系统变量的命令SQLshowall--查看所有68个系统变量值SQLshowuser--显示当前连接用户SQLshowerror--显示错误SQLsetheadingoff--禁止输出列标题，默认值为ON海颐软件股份有限公司ORACLE技术文档之一第6页共90页保密6SQLsetfeedbackoff--禁止显示最后一行的计数反馈信息，默认值为对6个或更多的记录，回送ONSQLsettimingon--默认为OFF，设置查询耗时，可用来估计SQL语句的执行时间，测试性能SQLsetsqlpromptSQL--设置默认提示符，默认值就是SQLSQLsetlinesize1000--设置屏幕显示行宽，默认100SQLsetautocommitON--设置是否自动提交，默认为OFFSQLsetpauseon--默认为OFF，设置暂停，会使屏幕显示停止，等待按下ENTER键，再显示下一页SQLsetarraysize1--默认为15SQLsetlong1000--默认为80说明：long值默认为80，设置1000是为了显示更多的内容，因为很多数据字典视图中用到了long数据类型，如：SQLdescuser_views列名可空值否类型-------------------------------------------VIEW_NAMENOTNULLVARCHAR2(30)TEXT_LENGTHNUMBERTEXTLONGSQLdefinea='''2000010112:01:01'''--定义局部变量，如果想用一个类似在各种显示中所包括的回车那样的常量，可以用define命令来设置SQLselect&afromdual;原值1:select&afromdual新值1:select'2000010112:01:01'fromdual'2000010112:01:01海颐软件股份有限公司ORACLE技术文档之一第7页共90页保密7-----------------2000010112:01:014．Oracle函数小解SQL中的单记录函数1.ASCII返回与指定的字符对应的十进制数;SQLselectascii('A')A,ascii('a')a,ascii('0')zero,ascii('')spacefromdual;2.CHR给出整数,返回对应的字符;SQLselectchr(54740)zhao,chr(65)chr65fromdual;3.CONCAT等价与||,推荐CONCAT连接两个字符串;SQLselectconcat('010-','88888888')||'转23'高乾竞电话fromdual;4.INITCAP返回字符串并将字符串的第一个字母变为大写;SQLselectinitcap('smith')uppfromdual;5.INSTR(C1,C2,I,J)和INSTRB(C1,C2,I,J)在一个字符串中搜索指定的字符,返回发现指定的字符的位置;C1被搜索的字符串C2希望搜索的字符串I搜索的开始位置,默认为1J第几次出现的位置,默认为1SQLselectinstr('oracletraning','ra',1,2)instringfromdual;6.LENGTH和LENGTHB返回字符串的长度;LENGTHB按照字节进行返回SQLselectname,length(name),addr,length(addr),sal,length(to_char(sal))from海颐软件股份有限公司ORACLE技术文档之一第8页共90页保密8gao.nchar_tst;7.LOWER返回字符串,并将所有的字符小写SQLselectlower('AaBbCcDd')AaBbCcDdfromdual;8.UPPER返回字符串,并将所有的字符大写SQLselectupper('AaBbCcDd')upperfromdual;9.RPAD和LPAD(粘贴字符)字符串填充RPAD在列的右边粘贴字符LPAD在列的左边粘贴字符SQLselectlpad(rpad('gao',10,'*'),17,'*')fromdual;10.LTRIM和RTRIMLTRIM删除左边出现的字符串RTRIM删除右边出现的字符串SQLselectltrim(rtrim('gaoqianjing',''),'')fromdual;11.SUBSTR(string,start,count)和SUBSTRB(string,start,count)取子字符串,从start开始,取count个SQLselectsubstr('13088888888',3,8)fromdual;12.REPLACE('string','s1','s2')string希望被替换的字符或变量s1被替换的字符串s2要替换的字符串SQLselectreplace('heloveyou','he','i')fromdual;13.SOUNDEX返回一个与给定的字符串读音相同的字符串SQLcreatetabletable1(xmvarchar(8));SQLinsertintotable1values('weather');SQLinsertintotable1values('wether');SQLinsertintotable1values('gao');海颐软件股份有限公司ORACLE技术文档之一第9页共90页保密9SQLselectxmfromtable1wheresoundex(xm)=soundex('weather');14.TRIM('s'from'string')LEADING剪掉前面的字符TRAILING剪掉后面的字符如果不指定,默认为空格符15.ABS返回指定值的绝对值SQLselectabs(100),abs(-100