ORACLE实用讲座

1ORACLE数据库实用讲座第一部分ORACLE数据库的使用1、预备知识1.1数据类型数据类型参数描述char(n)n=1to2000字节定长字符串，n字节长，如果不指定长度，缺省为1个字节长（一个汉字为2字节）varchar2(n)n=1to4000字节可变长的字符串，具体定义时指明最大长度n，这种数据类型可以放数字、字母以及ASCII码字符集(或者EBCDIC等数据库系统接受的字符集标准)中的所有符号。如果数据长度没有达到最大值n，Oracle会根据数据大小自动调节字段长度，如果你的数据前后有空格，Oracle会自动将其删去。VARCHAR2是最常用的数据类型。可做索引的最大长度3209。number(m,n)m=1to38n=-84to127可变长的数值列，允许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其实在内部是按7个字节来保存日期数据，在定义中还包括小时、分、秒。缺省格式为DD-MON-YY，如07-11月-00表示2000年11月7日。2long无可变长字符列，最大长度限制是2GB，用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。long是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。raw(n)n=1to2000可变长二进制数据，在具体定义字段的时候必须指明最大长度n，Oracle用这种格式来保存较小的图形文件或带格式的文本文件，如MiceosoftWord文档。raw是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。longraw无可变长二进制数据，最大长度是2GB。Oracle8i用这种格式来保存较大的图形文件或带格式的文本文件，如MiceosoftWord文档，以及音频、视频等非文本文件。在同一张表中不能同时有long类型和longraw类型，longraw也是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。blobclobnclob无三种大型对象(LOB)，用来保存较大的图形文件或带格式的文本文件，如MiceosoftWord文档，以及音频、视频等非文本文件，最大长度是4GB。LOB有几种类型，取决于你使用的字节的类型，Oracle实实在在地将这些数据存储在数据库内部保存。可以执行读取、存储、写入等特殊操作。bfile无在数据库外部保存的大型二进制对象文件，最大长度是4GB。这种外部的LOB类型，通过数据库记录变化情况，但是数据的具体保存是在数据库外部进行的。Oracle可以读取、查询BFILE，但是不能写入。大小由操作系统决定。31.2常用的各种比较运算符号有：=等于大于小于=小于等于=大于等于不等于LIKE字符串比较测验1.3运算符（部分）数学运算符+加-减*乘/除%求余并置运算符||连接两个字符串逻辑运算符AND与OR或条件运算符（NOT）IN所查询列的值（不）在IN后边括号里的列表值中（NOT）BETWEEN值1AND值2所查询列的值（不）在值1和值2范围内(含值1和值2)（NOT）EXISTS所查询列的值（不）在EXISTS后边括号里查询子句所指的结果集中联合UNION(ALL)集合的并，不包含重复行(包含重复行)外联Left(RIGHT)OUTERJOIN…..ON(+)左联，右联交INTERSECT集合的交，不包含重复行差MINUS集合的差，不包含重复行1.4常用的数学函数（部分）ABS(x)返回x的绝对值SIGN(x)当x为负数、零、正数的时候分别返回x的符号-1、0或者1MOD(x,y)返回x除以y的余数，跟x%y作用一样FLOOR(x)返回小于等于x的最大整数CEILING(x)或CEIL(x)返回大于等于x的最小整数POWER(x,y)返回x的y次方的数值4ROUND(x)返回最接近于x的数ROUND(x,d)返回小数点数为d的接近于x的数Nvl(value,v)如果value为null，则返回vSQRT(x)返回x的平方根1.5单值（分组）函数（部分）MIN返回一个给定列中最小的数值MAX返回一个给定列中最大的数值SUM返回一个给定列中所有数值的总和AVG返回一个给定列中所有数值的平均值COUNT(*)返回一个表中的行数单值函数用于从SELECT语句中计算”列的值”。虽然它们往往需要GROUPBY子句（后面介绍），但是这些函数也可以在不用使用GROUPBY子句的情况使用。例1、统计出emp表中所有雇员的平均工资SELECTAVG(sal)FROMemp;结果：AVG(SAL)----------2073.21429SELECTround(AVG(sal),2)“平均工资”FROMemp;结果：平均工资----------2073.21例2、统计emp表中部门号为‘10’的雇员的平均工资。SELECTround(AVG(sal),2)“平均工资”FROMempWHEREdeptno='10';平均工资--------------2916.67例3、统计emp表的雇员数SELECTCount(*)FROMemp;（不好，因为系统内部执行时要把*号换成表的全部列名）SELECTCount(0)FROMemp;（好）51.6字符串函数（部分）Substr(string,position[,length])在string中从position开始的长度为length的子串Upper(string)将string变为大写Lower(string)将string变为小写Instr(string,substring,start_position,n)寻找string中从start_position位置开始的第n次出现的substringSoundex(string)查找发音相同的stringLength(string)返回string长度Lpad(string,len[,character])在string左边填上len个字符Rpad(string,len[,character])在string右边填上len个字符Ltrim(string[,’substring’])从string左边删去substringRtrim(string[,’substring’])从string右边删去substringTo_char(number)将number转换为字符串1.7日期函数（部分）SYSDATE系统日期Add_months(date,n)在date上增加n个月，n可取负Greatest(dat1,date2,………)取dat1,date2,………中最大日期Least(dat1,date2,………)取dat1,date2,………中最小日期Last_day(date)取指定月的最后日期Months_between(date2,date1)date2和date1间的月数（可为小数）Next_day(date,’day’)Day的下一天，day是Monday,Tuesday等To_char(date,’format’)将date按format指定的格式转换为字符串To_date(string,’format’)将字符串string按format指定的格式转换dateformat：’yyyy-mm-dd’等例1、查询每个雇员的聘用日期selectename,to_char(HIREDATE,'yyyy-mm-ddhh24:mi:ss')fromemp;结果：ENAMETO_CHAR(HI--------------------SMITH1980-12-17ALLEN1981-02-20WARD1981-02-22JONES1981-04-02MARTIN1981-09-28BLAKE1981-05-01CLARK1981-06-09SCOTT1987-04-19KING1981-11-176TURNER1981-09-08ADAMS1987-05-23JAMES1981-12-03FORD1981-12-03MILLER1982-01-2314rowsselected.1.8DECODE函数DECODE(value,if1,then1,if2,then2,if3,then3,……,else)其含义是“如果value的值为if1，那么取then1的值，如果value的值为if2，那么取then2的值，如果value的值为if3，那么取then3的值，……….，否则取else的值。”decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)2、SELECT语句SELECT语句是SQL的核心，在你的SQL语句中可能用的最多的就是SELECT语句。SELECT语句用于检索与指定条件相匹配数据。SELECT语句有五个主要的子句，而FROM是唯一必须的子句。其它子句都是可选的，每一个子句有大量的选择项、参数等等。这些子句将罗列在下面，以后将做更为详细的介绍。SELECT语句的格式：SELECT[ALL|DISTINCT]column1[,column2]FROMtable1[,table2][WHEREconditions][CONNECTBY……….][GROUPBYcolumn-list][HAVINGconditions][ORDERBYcolumn-list[ASC|DESC]]ALL和DISTINCT是SQL中的关键字，缺省为(ALL)，如果想检索单一记录，则使用DISTINCT关键子。Selectdistinctdeptnofromemp;GROUPBY子句(分组统计)语法：SELECTcolumn1,column2,…..,SUM(column4),SUM(column5),……..7FROMtable1,table2,……GROUPBYcolumn1,column2;这个GROUPBY子句将按column1,column2,…..分组,分别求出column4,column5,…..之和。例1：从emp表中按部门检索工资最高的雇员：SELECTdeptno,max(sal)FROMempGROUPBYdeptno;结果：DEPTNOMAX(SAL)----------------105000203000302850上面的查询缺少雇员姓名，如何把雇员姓名一起查询出来？selectename,deptno,salfromempwheresal=(selectmax(sal)fromempwheredeptno='10');结果：ENAMEDEPTNOSAL------------------------------KING105000selectename,deptno,salfromempwheresal=(selectmax(sal)fromempwheredeptno='20');结果：ENAMEDEPTNOSAL------------------------------SCOTT203000FORD203000selectename,deptno,salfromempwheresal=(selectmax(sal)fromempwheredeptno='30');结果：ENAMEDEPTNOSAL------------------------------BLAKE302850selectename,deptno,salfromempwheresal=(selectmax(sal)fromempwherede