oracle经验总结

oracle经验总结2主题内容SQL支持命令类别Oracle支持的数据类型SQL支持的操作符SQL函数3SQL简介SQL支持下列类别的命令：数据定义语言（DDL）数据操纵语言（DML）事务控制语言（TCL）数据控制语言（DCL）数据定义语言CREATEALTERDROP数据操纵语言INSERTSELECTDELETEUPDATE事务控制语言COMMITSAVEPOINTROLLBACK数据控制语言GRANTREVOKE4事务控制语言事务是最小的工作单元，作为一个整体进行工作保证事务的整体成功或失败，称为事务控制用于事务控制的语句有：COMMIT-提交并结束事务处理ROLLBACK-撤销事务中已完成的工作SAVEPOINT–标记事务中可以回滚的点UPDATEexatSET下拨房租=‘130’WHERE序号=‘3’;SAVEPOINTmark1;DELETEFROMexaWHERE序号='3';SAVEPOINTmark2;ROLLBACKTOSAVEPOINTmark2;COMMIT;5数据控制语言数据控制语言为用户提供权限控制命令用于权限控制的命令有：GRANT授予权限REVOKE撤销已授予的权限SQLGRANTSELECT,UPDATEONorder_masterTOMARTIN;SQLGRANTUPDATE(qty_hand,re_level)ONitemfileTOMARTIN;SQLGRANTSELECTONpeTOaccountsWITHGRANTOPTION;SQLREVOKESELECT,UPDATEONpeFROMpms;6Oracle数据类型1创建表时，必须为各个列指定数据类型以下是Oracle数据类型的类别：数据类型字符数值日期时间RAW/LONGRAWLOB7Oracle数据类型2字符数据类型CHARVARCHAR2LONG当需要固定长度的字符串时，使用CHAR数据类型。CHAR数据类型存储字母数字值。CHAR数据类型的列长度可以是1到2000个字节。VARCHAR2数据类型支持可变长度字符串VARCHAR2数据类型存储字母数字值VARCHAR2数据类型的大小在1至4000个字节范围内LONG数据类型存储可变长度字符数据LONG数据类型最多能存储2GB8Oracle数据类型3数值数据类型可以存储整数、浮点数和实数最高精度为38位数值数据类型的声明语法：NUMBER[(p[,s])]P表示精度，S表示小数点的位数日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒主要的日期时间类型有：DATE-存储日期和时间部分，精确到整个的秒TIMESTAMP-存储日期、时间和时区信息，秒值精确到小数点后6位9Oracle数据类型4RAW数据类型用于存储二进制数据RAW数据类型最多能存储2000字节LONGRAW数据类型用于存储可变长度的二进制数据LONGRAW数据类型最多能存储2GBLOBLOB称为“大对象”数据类型，可以存储多达4GB的非结构化信息，例如声音剪辑和视频文件等LOB数据类型允许对数据进行高效、随机、分段的访问BLOBCLOBBFILECLOB即CharacterLOB（字符LOB），它能够存储大量字符数据BLOB即BinaryLOB（二进制LOB），可以存储较大的二进制对象，如图形、视频剪辑和声音文件BFILE即BinaryFile（二进制文件），它用于将二进制数据存储在数据库外部的操作系统文件中10Oracle数据类型5Oracle中伪列就像一个表列，但是它并没有存储在表中伪列可以从表中查询，但不能插入、更新和删除它们的值常用的伪列有ROWID和ROWNUMROWID是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用ROWID伪列快速地定位表中的一行ROWNUM是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数11SQL操作符SQL操作符集合操作符逻辑操作符比较操作符算术操作符连接操作符Oracle支持的SQL操作符分类如下：12算术操作符算术操作符用于执行数值计算可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成算术操作符包括加(+)、减(-)、乘(*)、除(/)SQLelect(e.总房租/e.出租数量)单价房租fromexae;SQLselect(e.所收房租-e.下拨房租)剩余房租fromexae;13比较操作符比较操作符用于比较两个表达式的值比较操作符包括=、!=、、、=、=、BETWEEN…AND、IN、LIKE和ISNULL等SQLselecte.*fromexaewheree.所收房租e.下拨房租;SQLselectt.*,t.rowidfromuserstwheret.gwlike‘后__心';14逻辑操作符逻辑操作符用于组合多个计较运算的结果以生成一个或真或假的结果。逻辑操作符包括与(AND)、或(OR)和非(NOT)。15集合操作符集合操作符将两个查询的结果组合成一个结果集合操作符UNIONUNIONALLINTERSECTMINUSINTERSECT操作符只返回两个查询的公共行。MINUS操作符返回从第一个查询结果中排除第二个查询中出现的行。16连接操作符连接操作符用于将多个字符串或数据值合并成一个字符串SQLselect(e.城市||e.市区||e.街道||e.门牌号)地址fromexame;通过使用连接操作符可以将表中的多个列合并成逻辑上的一行列17操作符的优先级SQL操作符的优先级从高到低的顺序是：算术操作符--------最高优先级连接操作符比较操作符NOT逻辑操作符AND逻辑操作符OR逻辑操作符--------最低优先级18SQL函数Oracle提供一系列用于执行特定操作的函数SQL函数带有一个或多个参数并返回一个值以下是SQL函数的分类：SQL函数单行函数分析函数分组函数19单行函数分类单行函数对于从表中查询的每一行只返回一个值可以出现在SELECT子句中和WHERE子句中单行函数可以大致划分为：日期函数字符函数数字函数转换函数其他函数20日期函数日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果日期函数包括：ADD_MONTHS(d,n)MONTHS_BETWEEN(d1,d2)LAST_DAY(d)NEXT_DAY(d,day)TRUNC(d,[fmt])21字符函数2-1函数输入输出Initcap(char)Selectinitcap(‘hello’)fromdual;HelloLower(char)Selectlower(‘FUN’)fromdual;funUpper(char)Selectupper(‘sun’)fromdual;SUNLtrim(char,set)Selectltrim(‘xyzadams’,’xyz’)fromdual;adamsRtrim(char,set)Selectrtrim(‘xyzadams’,’ams’)fromdual;xyzadTranslate(char,from,to)Selecttranslate(‘jack’,’j’,’b’)fromdual;backReplace(char,searchstring,[repstring])Selectreplace(‘jackandjue’,’j’,’bl’)fromdual;blackandblueInstr(char,m)Selectinstr(‘worldwide’,’d’)fromdual;5Substr(char,m,n)Selectsubstr(‘abcdefg’,3,2)fromdual;cdConcat(expr1,expr2)Selectconcat(‘Hello’,’world’)fromdual;Helloworld字符函数字符函数接受字符输入并返回字符或数值22字符函数2-2SQLSELECTCHR(67)FROMdual;以下是一些其它的字符函数：CHR和ASCIILPAD(string,number,string)RPAD(string,number,string)TRIMLENGTHDECODE(string,if(a1),a2)SQLSELECTLPAD(‘function’,15,’=’)FROMdual;SQLSELECTTRIM(9from9999876789999)FROMdual;SQLselectsum(decode(u.sex,‘男',1))asmennumfromusersu;23数字函数数字函数接受数字输入并返回数值结果函数输入输出Abs(n)Selectabs(-15)fromdual;15Ceil(n)Selectceil(44.778)fromdual;45Floor(n)Selectfloor(100.2)fromdual;100Power(m,n)Selectpower(4,2)fromdual;16Mod(m,n)Selectmod(10,3)fromdual;1MOD(-3,2)等于1(与后面的数的符号相同）Round(m,n)Selectround(100.256,2)fromdual;100.26Trunc(m,n)Selecttrunc(100.256,2)fromdual;100.25Sqrt(n)Selectsqrt(4)fromdual;2数字函数转换函数转换函数将值从一种数据类型转换为另一种数据类型常用的转换函数有：TO_CHARTO_DATETO_NUMBERSELECTTO_CHAR(sysdate,'YYYY年fmMM月fmDD日HH24:MI:SS')FROMdual;SELECTTO_CHAR(itemrate,‘C99999’)FROMitemfile;SELECTTO_DATE(‘2005-12-06’,‘yyyy-mm-dd’)FROMdual;SELECTTO_NUMBER('100')FROMdual;其它函数以下是几个用来转换空值的函数：NVLNVL2NULLIF(string,string)selectnvl(i.bound,1)fromitemi;selectnvl2(i.bound,1,0)fromitemi;selectNULLIF(i.itemname,i.bound)fromitemi;26分组函数分组函数基于一组行来返回结果为每一组行返回一个值AVGMINMAXSUMCOUNT分组函数27GROUPBY和HAVING子句GROUPBY子句用于将信息划分为更小的组每一组行返回针对该组的单个结果HAVING子句用于指定GROUPBY子句检索行的条件selectbd.eid,max(bd.area)areafrombuildingbdgroupbybd.eid;selectbd.eid,max(bd.area)areafrombuildingbdgroupbybd.eidhavingbd.eid='330f00cf-0e40-44c5-9f64-583cc94b0b7b'28分析函数2-1分析函数根据一组行来计算聚合值用于计算完成聚集的累计排名、移动平均数等分析函数为每组记录返回多个行分析函数DENSE_RANKRANKROW_NUMBER29分析函数2-2以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始ROW_NUMBER返回连续的排位，不论值是否相等RANK具有相等值的行排位相同，序数随后跳跃DENSE_RANK具有相等值的行排位相同，序号是连续的SELECTename,job,deptno,sal,ROW_NUMBER()OVER(ORDERBYsalDESC)ASSAL_R