ORACLE数据库SQL基础培训.doc

海颐软件股份有限公司ORACLE技术文档之一 ORACLE数据库SQL应用基础培训培训目标：了解Oracle，为开发服务初级培训：计划培训：Oracle的一些常用函数、SQL技巧、SQL优化以及一些需要注意的问题、树 view_next_dept、物化视图、索引面向对象：Oracle刚入门中级培训：计划培训：Oracle数据库管理，包括数据库的创建、日常维护、数据库监控面向对象：Oracle已入门高级培训：计划培训：Oracle数据库备份与回复、数据库高级复制面向对象：Oracle比较熟练1Oracle 基本知识一个表空间只能属于一个数据库 每个数据库最少有一个控制文件（建议=3个，分别放在不同的磁盘上） 每个数据库最少有一个表空间（SYSTEM表空间） 建立SYSTEM表空间的目的是尽量将目的相同的表存放在一起，以提高使用效率，只应存放数据字典 每个数据库最少有两个联机日志组，每组最少一个联机日志文件 一个数据文件只能属于一个表空间 一个数据文件一旦被加入到一个表空间中，就不能再从这个表空间中移走，也不能再加入到其他表空间中 建立新的表空间需要建立新的数据文件 数据文件被ORACLE格式化为ORACLE块，Oracle9i以前版本中，ORACLE块的大小是在第一次创建数据库时设定的，并且以后不能改变，要想改变，只能重建数据库 一个段segment只能属于一个表空间，但可以属于多个数据文件 一个区extent只能属于一个数据文件，即区间（extent）不能跨越数据文件 PCTFREE和PCTUSED总和不能大于等于100 单独一个事务不能跨越多个回滚段 索引表不含ROWID值 拥有不同大小的回滚段没有任何益处 COMMIT后，数据不一定立即写盘（数据文件） 一个事务即使不被提交，也会被写入到重做日志中。 Oracle 8.0.4中，在初始安装时建立的缺省数据库，实例名为ORCL 一个块的最大长度为16KB(有2K、4K、8K、16K)每个数据库最大文件数（按块大小）2K块20000个文件4K块40000个文件8K块或以上65536个文件Oracle server可以同时启动多个数据库 一套操作系统上可以安装多个版本的ORACLE数据库系统（UNIX可以，NT不可以） 一套ORACLE数据库系统中可以有多个ORACLE数据库及其相对应的实例 每个ORACLE数据库拥有一个数据库实例（INSTANCE）（OPS除外） 所以，一套操作系统上同时可以有多个oracle数据库实例启动 2Oracle 数据类型Oracle8 数据类型数据类型参数描述char(n)n=1 to 2000字节定长字符串，n字节长，如果不指定长度，缺省为1个字节长（一个汉字为2字节）varchar2(n)n=1 to 4000字节可变长的字符串，具体定义时指明最大长度n，这种数据类型可以放数字、字母以及ASCII码字符集(或者EBCDIC等数据库系统接受的字符集标准)中的所有符号。如果数据长度没有达到最大值n，Oracle 8i会根据数据大小自动调节字段长度，如果你的数据前后有空格，Oracle 8i会自动将其删去。VARCHAR2是最常用的数据类型。可做索引的最大长度3209。number(m,n)m=1 to 38n=-84 to 127可变长的数值列，允许0、正值及负值，m是所有有效数字的位数，n是小数点以后的位数。如：number(5,2)，则这个字段的最大值是99,999，如果数值超出了位数限制就会被截取多余的位数。如：number(5,2)，但在一行数据中的这个字段输入575.316，则真正保存到字段中的数值是575.32。如：number(3,0)，输入575.316，真正保存的数据是575。 date无从公元前4712年1月1日到公元4712年12月31日的所有合法日期，Oracle 8i其实在内部是按7个字节来保存日期数据，在定义中还包括小时、分、秒。缺省格式为DD-MON-YY，如07-11月-00 表示2000年11月7日。 long无可变长字符列，最大长度限制是2GB，用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。long是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。 raw(n)n=1 to 2000可变长二进制数据，在具体定义字段的时候必须指明最大长度n，Oracle 8i用这种格式来保存较小的图形文件或带格式的文本文件，如Miceosoft Word文档。raw是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。 long raw无可变长二进制数据，最大长度是2GB。Oracle 8i用这种格式来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件。在同一张表中不能同时有long类型和long raw类型，long raw也是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。 blobclobnclob无三种大型对象(LOB)，用来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件，最大长度是4GB。LOB有几种类型，取决于你使用的字节的类型，Oracle 8i实实在在地将这些数据存储在数据库内部保存。可以执行读取、存储、写入等特殊操作。 bfile无在数据库外部保存的大型二进制对象文件，最大长度是4GB。这种外部的LOB类型，通过数据库记录变化情况，但是数据的具体保存是在数据库外部进行的。Oracle 8i可以读取、查询BFILE，但是不能写入。大小由操作系统决定。 3Oracle SQL*Plus中的一些命令show和set命令是两条用于维护SQL*Plus系统变量的命令SQL show all-查看所有68个系统变量值SQL show user-显示当前连接用户SQL show error-显示错误SQL set heading off-禁止输出列标题，默认值为ONSQL set feedback off-禁止显示最后一行的计数反馈信息，默认值为对6个或更多的记录，回送ONSQL set timing on-默认为OFF，设置查询耗时，可用来估计SQL语句的执行时间，测试性能SQL set sqlprompt SQL -设置默认提示符，默认值就是SQL SQL set linesize 1000-设置屏幕显示行宽，默认100SQL set autocommit ON-设置是否自动提交，默认为OFFSQL set pause on-默认为OFF，设置暂停，会使屏幕显示停止，等待按下ENTER键，再显示下一页SQL set arraysize 1-默认为15SQL set long 1000-默认为80说明：long值默认为80，设置1000是为了显示更多的内容，因为很多数据字典视图中用到了long数据类型，如：SQL desc user_views 列名 可空值否 类型 - - - VIEW_NAME NOT NULL VARCHAR2(30) TEXT_LENGTH NUMBER TEXT LONGSQL define a = 20000101 12:01:01 -定义局部变量，如果想用一个类似在各种显示中所包括的回车那样的常量，可以用define命令来设置SQL select &a from dual;原值 1: select &a from dual新值 1: select 20000101 12:01:01 from dual2000010112:01:01-20000101 12:01:014Oracle函数小解SQL中的单记录函数1.ASCII返回与指定的字符对应的十进制数;SQL select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual;2.CHR给出整数,返回对应的字符;SQL select chr(54740) zhao,chr(65) chr65 from dual;3.CONCAT 等价与|,推荐CONCAT连接两个字符串;SQL select concat(010-,88888888)|转23 高乾竞电话 from dual;4.INITCAP返回字符串并将字符串的第一个字母变为大写;SQL select initcap(smith) upp from dual;5.INSTR(C1,C2,I,J)和INSTRB(C1,C2,I,J)在一个字符串中搜索指定的字符,返回发现指定的字符的位置;C1 被搜索的字符串C2 希望搜索的字符串I 搜索的开始位置,默认为1J 第几次出现的位置,默认为1SQL select instr(oracle traning,ra,1,2) instring from dual;6.LENGTH和LENGTHB返回字符串的长度; LENGTHB按照字节进行返回SQL select name,length(name),addr,length(addr),sal,length(to_char(sal) from gao.nchar_tst;7.LOWER返回字符串,并将所有的字符小写SQL select lower(AaBbCcDd)AaBbCcDd from dual;8.UPPER返回字符串,并将所有的字符大写SQL select upper(AaBbCcDd) upper from dual;9.RPAD和LPAD(粘贴字符) 字符串填充RPAD 在列的右边粘贴字符LPAD 在列的左边粘贴字符SQL select lpad(rpad(gao,10,*),17,*)from dual;10.LTRIM和RTRIMLTRIM 删除左边出现的字符串RTRIM 删除右边出现的字符串SQL select ltrim(rtrim( gao qian jing , ), ) from dual;11.SUBSTR(string,start,count)和SUBSTRB(string,start,count)取子字符串,从start开始,取count个SQL select substr3,8) from dual;12.REPLACE(string,s1,s2)string 希望被替换的字符或变量 s1 被替换的字符串s2 要替换的字符串SQL select replace(he love you,he,i) from dual;13.SOUNDEX返回一个与给定的字符串读音相同的字符串SQL create table table1(xm varchar(8);SQL insert into table1 values(weather);SQL insert into table1 values(wether);SQL insert into table1 values(gao);SQL select xm from table1 where soundex(xm)=soundex(weather);14.TRIM(s from string)LEADING 剪掉前面的字符TRAILING 剪掉后面的字符如果不指定,默认为空格符 15.ABS返回指定值的绝对值SQL select abs(100),abs(-100) from dual;16.ACOS给出反余弦的值SQL select acos(-1) from dual;17.ASIN给出反正弦的值SQL select asin(0.5) from dual;18.ATAN返回一个数字的反正切值SQL select atan(1) from dual;19.CEIL - FLOOR返回大于或等于给出数字的最小整数SQL select ceil(3.1415927) from dual;20.COS返回一个给定数字的余弦SQL select cos(-3.1415927) from dual;21.COSH返回一个数字反余弦值SQL select cosh(20) from dual;22.EXP返回一个数字e的n次方根SQL select exp(2),exp(1) from dual;23.FLOOR对给定的数字取整数，返回小于或等于给定数的最大整数与CEIL相反SQL select floor(2345.67) from dual;24.LN返回一个数字的对数值SQL select ln(1),ln(2),ln(2.7182818) from dual;25.LOG(n1,n2)返回一个以n1为底n2的对数 SQL select log(2,1),log(2,4) from dual;26.MOD(n1,n2)返回一个n1除以n2的余数SQL select mod(10,3),mod(3,3),mod(2,3) from dual;27.POWER返回n1的n2次方根SQL select power(2,10),power(3,3) from dual;28.ROUND和TRUNC(x,m,n)按照指定的精度进行舍入SQL select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;29.SIGN 符号函数取数字n的符号,大于0返回1,小于0返回-1,等于0返回0SQL select sign(123),sign(-100),sign(0) from dual;30.SIN返回一个数字的正弦值SQL select sin(1.57079) from dual;31.SIGH返回双曲正弦的值SQL select sin(20),sinh(20) from dual;32.SQRT返回数字n的根SQL select sqrt(64),sqrt(10) from dual;33.TAN返回数字的正切值SQL select tan(20),tan(10) from dual;34.TANH返回数字n的双曲正切值SQL select tanh(20),tan(20) from dual;35.TRUNC按照指定的精度截取一个数SQL select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;36.ADD_MONTHS增加或减去月份SQL select to_char(add_months(to_date(199912,yyyymm),2),yyyymm) from dual;SQL select to_char(add_months(to_date(199912,yyyymm),-2),yyyymm) from dual;37.LAST_DAY返回日期的最后一天SQL select to_char(sysdate,yyyy.mm.dd),to_char(sysdate)+1,yyyy.mm.dd) from dual;SQL select last_day(sysdate) from dual;38.MONTHS_BETWEEN(date2,date1)给出date2-date1的月份SQL select months_between(19-12月-1999,19-3月-1999) mon_between from dual;SQLselectmonths_between(to_date(2000.05.20,yyyy.mm.dd),to_date(2005.05.20,yyyy.mm.dd) mon_betw from dual;39.NEW_TIME(date,this,that)给出在this时区=other时区的日期和时间SQL select to_char(sysdate,yyyy.mm.dd hh24:mi:ss) bj_time,to_char(new_time 2 (sysdate,PDT,GMT),yyyy.mm.dd hh24:mi:ss) los_angles from dual;40.NEXT_DAY(date,day)给出日期date和星期x之后计算下一个星期的日期SQL select next_day(18-5月-2001,星期五) next_day from dual;41.SYSDATE用来得到系统的当前日期SQL select to_char(sysdate,dd-mm-yyyy day) from dual;42.CHARTOROWID将字符数据类型转换为ROWID类型SQL select rowid,rowidtochar(rowid),ename from scott.emp;43.CONVERT(c,dset,sset)将源字符串 sset从一个语言字符集转换到另一个目的dset字符集SQL select convert(strutz,we8hp,f7dec) conversion from dual;44.HEXTORAW将一个十六进制构成的字符串转换为二进制45.RAWTOHEXT将一个二进制构成的字符串转换为十六进制46.ROWIDTOCHAR将ROWID数据类型转换为字符类型47.TO_CHAR(date,format)SQL select to_char(sysdate,yyyy/mm/dd hh24:mi:ss) from dual;48.TO_DATE(string,format)将字符串转化为ORACLE中的一个日期49.TO_MULTI_BYTE将字符串中的单字节字符转化为多字节字符SQL select to_multi_byte(高) from dual;50.TO_NUMBER将给出的字符转换为数字SQL select to_number(1999) year from dual;51.BFILENAME(dir,file)指定一个外部二进制文件SQLinsert into file_tb1 values(bfilename(lob_dir1,image1.gif);52.CONVERT(x,desc,source)将x字段或变量的源source转换为desc53.DUMP(s,fmt,start,length)DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值SQL col global_name for a30SQL col dump_string for a50SQL set lin 200SQL select global_name,dump(global_name,1017,8,5) dump_string from global_name;54.EMPTY_BLOB()和EMPTY_CLOB()这两个函数都是用来对大数据类型字段进行初始化操作的函数55.GREATEST返回一组表达式中的最大值,即比较字符的编码大小.SQL select greatest(AA,AB,AC) from dual;SQL select greatest(啊,安,天) from dual;56.LEAST返回一组表达式中的最小值 SQL select least(啊,安,天) from dual;57.UID返回标识当前用户的唯一整数SQL show userSQL select username,user_id from dba_users where user_id=uid;58.USER返回当前用户的名字SQL select user from dual;59.USEREVN返回当前用户环境的信息,opt可以是:ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZEISDBA 查看当前用户是否是DBA如果是则返回trueSQL select userenv(isdba) from dual;SESSION返回会话标志SQL select userenv(sessionid) from dual;ENTRYID返回会话人口标志SQL select userenv(entryid) from dual;INSTANCE返回当前INSTANCE的标志SQL select userenv(instance) from dual;LANGUAGE返回当前环境变量SQL select userenv(language) from dual;LANG返回当前环境的语言的缩写SQL select userenv(lang) from dual;TERMINAL返回用户的终端或机器的标志SQL select userenv(terminal) from dual;VSIZE(X)返回X的大小(字节)数SQL select vsize(user),user from dual;60.AVG(DISTINCT|ALL)all表示对所有的值求平均值,distinct只对不同的值求平均值SQL create table table3(xm varchar(8),sal number(7,2);SQL insert into table3 values(gao,1111.11);SQL insert into table3 values(gao,1111.11);SQL insert into table3 values(zhu,5555.55);SQL commit;SQL select avg(distinct sal) from table3;SQL select avg(all sal) from gao.table3;61.MAX(DISTINCT|ALL)求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次SQL select max(distinct sal) from scott.emp;62.MIN(DISTINCT|ALL)求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次SQL select min(all sal) from gao.table3;63.STDDEV(distinct|all)求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差SQL select stddev(sal) from scott.emp;SQL select stddev(distinct sal) from scott.emp;64.VARIANCE(DISTINCT|ALL)求协方差 SQL select variance(sal) from scott.emp;65.GROUP BY主要用来对一组数进行统计SQL select deptno,count(*),sum(sal) from scott.emp group by deptno;66.HAVING对分组统计再加限制条件SQL select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)=5;SQL select deptno,count(*),sum(sal) from scott.emp having count(*)=5 group by deptno ;67.ORDER BY用于对查询到的结果进行排序输出SQL select deptno,ename,sal from scott.emp order by deptno,sal desc;68.DECODE 条件判断赋值，很有用处，相当于IF判断。可以简化查询、数据转换、提高性能等功效 DECODE(expr,adjust1,value1,default value) Select User_no,Decode(User_type1,10,专变户,20,公变户,考核户) From User_files Where Business_Place_code=0914269.NVL 空值判断 Select Nvl(write_sect_no,无抄表区段) from user_files70.SUM 求和 Select Sum(Total_power) from df_money_files where total_money 071.to_char(n,fmt) 数字转换成字符其实：to_number,to_char,to_date等转换函数都可以在很多数据类型之间进行转换,to_lob一般只能将long、long raw转换为clob、blob、nclob类型数字格式： 72.round/trunc(date,fmt)按照给出的要求将日期截断,如果fmt=mi表示保留分,截断秒SQL select to_char(trunc(sysdate,hh),yyyy.mm.dd hh24:mi:ss) hh, to_char(trunc(sysdate,mi),yyyy.mm.dd hh24:mi:ss) hhmm from dual;日期格式： 73.SYS_CONTEXT(namespace,attribute_name,length)返回应用程序上下文命名空间的属性值Namespace命名空间一般由create context创建的，也可制定默认的USERENVselectSYS_CONTEXT(USERENV,TERMINAL) terminal,SYS_CONTEXT(USERENV,IP_ADDRESS) ip_addressFrom dual;74.SYS_GUID()返回全局唯一标识的16位或32位raw类型值，一般为主机ID、进程ID和序列号的组合值。还有一些函数没有列出，感兴趣的话，可以查看一些帮助手册。5Oracle 的一些操作符及控制语句和PL/SQL块Oracle中有很多的操作符，每种操作符都有自己的含义，在使用时需要很好的理解其中的内涵。这些操作符与平时大家见到的一些操作符几乎是一样的，含义也差不多。 需要注意一点的是，oracle中的赋值语句用:=的方式，而不是=。在oracle中，其pl/sql语句控制与普通高级语言类似。也有if、loop、while、go to等语句控制，下面只是简单的列一下他们的格式，需要在使用过程中进行体会。IF THEN Statement;Statement BlockELSIF THEN Statement;Statement BlockELSE Statement;Statement BlockEND IF ;LOOPEXIT;EXIT WHEN Condition;Statement;Statement BlockEND LOOP ;WHILE Condition LOOPStatement;Statement BlockEND LOOP ;FOR Counter IN REVERSE start.end LOOPStatement;Statement BlockEND LOOP ;GOTO ;PL/SQL语句块的构成：Declare VaribleBEGINStatement;PL/SQL Statement BlockEXCEPTION WHEN Exception THEN Statement;END ;需要注意的是，在oracle中需要时常注意异常EXCEPTION的处理，因为在编写程序的过程中，由于考虑不周或者其它什么别的原因，可能处处引发异常，而且oracle的异常是可以传播的，如不进行异常处理，将导致意想不到的结果，我们也就无法获得正确的结果。建议每个语句块都应该有相应的异常处理，以便于程序出现问题时，好进行错误排除。可以申明自己的异常。申明格式为：Your_exception EXCEPTION;申明完后，可以将其加入oracle的异常列表中：PRAGMA EXCEPTION_INIT(Your_exception_name,Ora_number);可以在程序的某个地方引发定义的异常：RAISE Your_exception_name;或者在没定义时可用RAISE_APPLICATION_ERROR(error_number,error_text,keep_errors);来引用。在没有自定义异常而且也没有进行预定义异常处理，在异常处理时还可以调用OTHERS方法进行，如WHEN OTHERS THEN 。对于过程、函数、包等形式的语句块，有自己的定义方式。对于包，大家可以多使用，因为包有很多的优点：1、全局变量定义；2、函数重载；3、便于维护和存储。包是函数、过程、变量、游标等的一个集合体。过程定义：函数定义：包的定义：定义包头：定义包体：修改包头或包体：删除包：触发器的定义：6Oracle 的物化视图及快照 物化视图与快照是一种相对静态的类似视图的一种体现。普通视图，是一种动态的，本身没有数据，只有在进行查询、更新或删除时才动态加载，物化视图在进行查询、更新或删除时，相当于一个真实的库表，其数据在处理前就已经加载，减少了加载的过程，同时它还可以在其上面创建索引以优化查询，这对于数据变化不太而数据量又相对较大的库表建立物化视图很有好处：简化操作、提高性能。物化视图的数据需要定义其更新的时间和下次更新的间隔，与快照一样。物化视图其实就是一种快照。在数据发生变更，而还没有到数据更新的时间时，就需要手动进行数据刷新处理。所以，物化视图不要用在变动频繁的库表上，否则需要增加频繁的手动处理，若是将刷新时间间隔设的很短的话，又会影响系统的整体性能。7Oracle 的SELECT、DELETE、INSERT与UPDATE语法关于层次树的问题，可以用select的一种方式来实现。下面这个语句就体现了部门之间的层次关系：select level,a.dept_id,a.dept_code,a.dept_name,a.upper_dept_id from rs_dept astart with a.upper_dept_id is null - 根记录的条件connect by prior a.dept_id=a.upper_dept_id -子记录和父记录的连接方式。是一个特定的条件表达式，必须为其中之一：- PRIOR expr comparison_operator expr - expr comparison_operator PRIOR expr 注意，这种表达式，对于根记录应该是唯一的，否则，查询可能出错。使用这种表达方式，可以很好的解决有上下级关系的树的体现，非常的方便而且速度很快。目前MIS系统中已经比较多的采用了这种方式来组建视图，体现上下级部门。Select 语句中还可以使用case语句select user_no,group_no,total_power, case when total_power 10000 then 用电小户when total_power 20000 then 用电大户1 when total_power 30000 then 用电大户 2 when total_power 、这样的范围条件判断情况。二者都可以用到where条件中。Case语句还可以用户where条件中，这时需要注意写法，否则可能性能不好。select user_no,group_no,total_power, case when total_power 10000 then 用电小户when total_power 20000 then 用电大户1 when total_power 30000 then 用电大户 2 when total_power 50 * executions ) s, sys.v_$sqltext pwhere s.ranking 500000order by buffer_gets desc) where rownum=30;- 执行次数多的SQL select sql_text,executions from (select sql_text,executions from v$sqlarea order by executions desc) where rownum81; - 读硬盘多的SQL select sql_text,disk_reads from (select sql_text,disk_reads from v$sqlarea order by disk_reads desc) where rownum21; - 排序多的SQL select