Oracle内置函数

Oracle内置函数2ORACLE一、SQL简介SQL全名是结构化查询语言（StructuredQueryLanguage），是用于数据库中的标准数据查询语言，IBM公司最早使用在其开发的数据库系统中。1986年10月，美国ANSI对SQL进行规范后，以此作为关系式数据库管理系统的标准语言(ANSIX3.135-1986)，1987年得到国际标准组织的支持下成为国际标准。3ORACLE各种通行的数据库系统在其实践过程中都对SQL规范作了某些编改和扩充。所以，实际上不同数据库系统之间的SQL语言不能完全相互通用。现在最新的标准是SQL99，ORACLE不但对标准的SQL完全兼容，而且有自己更为方便的增强SQL。4ORACLE二、SQL的分类：DML语句（数据操作语言）Insert、Update、Delete、Merge、SelectDDL语句（数据定义语言）Create、Alter、Drop、TruncateDCL语句（数据控制语言）Grant、Revoke事务控制语句Commit、Rollback、Savepoint5ORACLE三、SQL运算符算术表达式+-/*连接运算符||空值isnull比较运算符(!=or)betweenandin操作notin模糊查询like去除重复行distinct逻辑运算符orandnot6ORACLE四、字符函数Ascii:返回与指定的字符对应的十进制数；Chr:给出整数,返回对应的字符；Upper：返回字符串,并将所有的字符大写；Lower：返回字符串,并将所有的字符小写；Initcap：返回字符串将字符串的第一个字母变为大写；7ORACLE例：selectascii('A')fromdual;selectchr(66)fromdual;selectupper('abCDef')fromdual;selectlower('abCDef')fromdual;selectinitcap('abCDef')fromdual;8ORACLESubstr(string,start,count)：取子字符串,从start开始,取count个；Substrb(string,start,count)；Instr(C1,C2,I,J)：在一个字符串中搜索指定的字符,返回发现指定的字符的位置；C1被搜索的字符串，C2希望搜索的字符串，I搜索的开始位置,默认为1，J出现的次数,默认为1;Instrb(C1,C2,I,J)；9ORACLEselectsubstr('aaabbb第一个测试实例',1,10)fromdual;selectsubstrb('aaabbb第一个测试实例',1,10)fromdual;selectinstr('aaabbb第一个测试实例','测试',1,1)fromdual;selectinstrb('aaabbb第一个测试实例','测试',1,1)fromdual;10ORACLEConcat：连接两个字符串；Length：返回字符串的长度；Lpad：在列的左边粘贴字符；Rpad：在列的右边粘贴字符；Trim：删除两边出现的字符串；Ltrim:删除左边出现的字符串；Rtrim:删除右边出现的字符串；11ORACLEselectConcat('aa','bb')fromdual;selectLength('aabbccdd')fromdual;selectLpad('aabbccdd',20,‘345')fromdual;selectLpad('aabbccdd',2,‘345')fromdual;selectRpad('aabbccdd',20,‘345')fromdual;selectlength(Trim('aabbccdd'))fromdual;selectlength(Ltrim('aabbccdd'))fromdual;selectlength(Rtrim('aabbccdd'))fromdual;12ORACLEReplace(’string’,’s1’,’s2’)：string希望被替换的字符或变量；s1被替换的字符串，s2要替换的字符串。Selectreplace('aabbccddee','bb','ff')fromdual;13ORACLE五、数值函数Abs:返回指定值的绝对值；Round:按照指定的精度进行四舍五入；Ceil:返回大于或等于给出数字的最小整数；Trunc:对给定的数字取整数；Floor:返回小于或等于给出数字的最大整数；;14ORACLEselectabs(-2)fromdual;selectround(45.12)fromdual;selectround(45.12,1)fromdual;selectround(45.12,-1)fromdual;selectceil(45.12)fromdualselecttrunc(45.12)fromdual;selectfloor(45.82)fromdual;15ORACLEMod(n1,n2):返回一个n1除以n2的余数；Sign:取数字n的符号,大于0返回1,小于0返回-1,等于0返回0；Sqrt:返回数字n的根；Log(n1,n2):返回一个以n1为底n2的对数；Power(n1,n2):返回n1的n2次方；;16ORACLEselectmod(12,5)fromdual;selectsign(-5)fromdual;selectsqrt(9)fromdual;selectlog(2,8)fromdual;selectpower(2,4)fromdual;17ORACLE六、日期函数Months_between返回两个日期之间的月份；Add_months增加或减去月份；Next_day(date,’day’)给出日期date和星期x之后计算下一个星期的日期；Last_day返回当月日期的最后一天；Trunc：截取时间；例如：selecttrunc(sysdate,‘w’)fromdual；18ORACLEselectmonths_between(to_date('20100804','yyyymmdd'),to_date('20100608','yyyymmdd'))fromdualselectadd_months(sysdate,4.2)fromdual;selectnext_day(sysdate,2)fromdual;selectlast_day(sysdate)fromdual;selecttrunc(sysdate,'w')fromdual;19ORACLESysdate用来得到系统的当前日期例如：selectsysdatefromdual;selectto_char(sysdate,‘yyyymmddhh24miss’)fromdual;systimestamp用来得到系统的当前日期,精确到毫秒；例如：selectsystimestampfromdual;20ORACLE六、转换函数To_char将日期转化为字符串；To_number将给出的字符转换为数字；To_date将字符串转化为日期。21ORACLE七、通用函数NVL(exp1,exp2)：如果表达式exp1为空，返回exp2；NULLIF(exp1,exp2)：如果表达式exp1与exp2的值相等则返回null，否则返回exp1的值；22ORACLEDECODE()函数SELECTempno,ename,sal,DECODE(deptno,10,'财务部',20,'研发部',30,'销售部','未知部门')部门FROMemp;23ORACLECASE表达式SELECTempno,ename,sal,CASEdeptnoWHEN10THEN'财务部'WHEN20THEN'研发部'WHEN30THEN'销售部'ELSE'未知部门'END部门FROMemp;24ORACLE八、分组函数Count:返回结果集的记录数；(如果结果是为空会怎样？)Avg:返回平均数；Max:返回最大值；Min:返回最小值；Sum:返回数值和；25ORACLE1、找出雇佣时间小于25年的员工；2、所有员工名字前加上Dear,并且名字首字母大写；3、找出姓名为5个字母的员工；4、找出姓名中不带R这个字母的员工；5、显示所有员工的姓名的第一个字；6、显示所有员工，按名字降序排列，若相同，则按工资升序排序；7、分组统计各部门下工资1000的员工的平均工资；8、统计各部门下平均工资大于1300的部门；9、算出每个职位的员工数和最低工资；10、算出部门30中得到最多工资的员工姓名；11、算出每个职位的平均工资，如果平均工资大于3000，显示“工资不错”，如果平均工资1000到3000，显示“工资一般”，否则显示“工资差”，按平均工资降序排列；