关于Oracle数据库的学习记录：
九、单行函数----字符串函数虽然各个数据库有自己的开发架构，但是对于所有的数据库而言，基本上对开发者就两块重要的内容，SQL语句+单行函数所有的单行函数，严格来讲，程序都可以处理所谓单行函数指的就是完成某一具体功能的操作函数，例如：转大写、或者进行日期转换等等，一般而言，单行函数的格式：“返回值 函数名称（参数）”单行函数按照类型一共分为以下几种：字符串函数、数值函数、日期函数、转换函数、通用函数1.字符串函数字符串函数主要是处理字符串数据的（对于字符串的数据有可能是从列上找到的，或者是直接设置的字符串常量），那么包含的函数有如下几种：No 函数名称 描述1 字符串 UPPER(列|字符串) 转大写2 字符串 LOWER(列|字符串) 转小写3 字符串 INITCAP(列|字符串) 开头大写，其他小写4 数字 LENGTH(列|字符串) 取得指定字符串长度5 字符串 SUBSTR(列|字符串，开始索引，[长度]) 进行字符串的截取6 字符串 REPLACE(列|字符串，旧内容，新内容) 将指定字符串的数据用新数据代替在Oracle里面，所有的函数如果要想进行验证，也必须编写SQL语句,为了方便用户进行一些验证或者而是一些不需要查询表的查询操作，专门提供了一个dual的虚拟表1.转大写、小写操作范例：观察基本操作SELECT UPPER('hello') FROM dual;范例：将所有的姓名转小写SELECT LOWER(ename) FROM emp;在Oracle数据库中本身数据是区分大小写的，但是为什么又需要提供以上两个函数呢？在实际项目的开发之中，会发现有许多的代码本身是不区分大小写关系的，所以用户进行信息查询的时候也往往不会考虑到大小写问题范例：由用户输入要查询的雇员姓名，而后显示雇员的完整信息**在Oracle数据库要想实现数据的输入操作，可以使用替代变量的方式完成，格式"&标记"SELECT * FROM emp WHERE ename=&inputname;SELECT * FROM emp WHERE ename=UPPER('&inputname');一般在一些不区分数据大小写的情况下，都会同意的将所有的内容转成大写或小写2.首字母大写其他字母小写范例：将所有雇员姓名以首字母大写的形式保存SELECT ename,INITCAP(ename) FORM emp;3.取得字符串的长度范例：基础操作SELECT LENGTH('helloworld!!!') FROM dual;范例：查询雇员姓名长度为5的全部雇员信息SELECT * FROM emp WHERE LENGTH(ename)=5;4.字符串的截取**从指定位置截取到结尾**从指定位置截取到指定位置范例：验证函数SELECT SUBSTR('helloworld',6) FROM dual;SELECT SUBSTR('helloworld',0,5) FROM dual;截取5个长度SELECT SUBSTR('helloworld',1,5) FROM dual;在程序里面，所有字符串的首字母的索引都是0，但是在Oracle里面，所有字符串的首字母的索引是1，如果你设置的是0，那么也会按照1的方式来进行处理范例：要求截取每个雇员姓名的前3个字母SELECT ename,SUBSTR(ename,1,3) FROM emp;范例：要求截取每个雇员姓名的后3个字母SELECT ename,SUBSTR(ename,LENGTH(ename)-2,3) FROM emp;对于Oracle而言有一点设计比较厉害，就是在设置SUBSTR()函数的时候考虑到了由后截取的情况，也可以设置为负数，表示后面的第几个索引SELECT ename,SUBSTR(ename,-3) FROM emp;此类的设置方式只有Oracle才有十、单行函数-----数值函数1.数字 ROUND（列|数字[,小数位]) 实现数据的四舍五入，可以设置小数保留位2.数字 TRUNC（列|数字[,小数位]) 实现数据的截取，不进位3.数字 MOD（列|数字[,小数位]) 求模，即计算余数范例：使用ROUND（）函数SELECT ROUND(789.5671234) FROM dual;如果没有设置小数点的保留位数，那么会直接不保留小数位进行进位范例：使用ROUND（）函数SELECT ROUND(789.5671234,2) FROM dual;如果设置了小数位，那么就会在指定的小数位上实现四舍五入，如果设置为负数，那么就表示进行整数位的四舍五入范例：验证TRUNC()函数该函数与ROUND()函数差别不大，区别在于TRUNC()函数是不会进位的SELECT TRUNC(789.5671234,2) ,TRUNC(789.5671234),TRUNC(789.5671234,-2)FROM dual;范例：求模函数SELECT MOD(10,3) FROM dual;在以后的学习中，如果见到了round这个单词一般都表示四舍五入十一、单行函数----日期函数如果要想处理任何日期，那么都有一个基本前提，必须知道当前日期是什么。如果要想取得当前日期时间，在Oracle中专门提供了一个伪列"SYSDATE"(SYSTIMESTAMP)范例：验证伪列SELECT SYSDATE FROM dual;SELECT SYSTIMESTAMP FROM dual;范例：进一步观察伪列SELECT ename,job,sal,SYSDATE FROM emp;SYSDATE伪列里面包含有日期时间的内容，只不过现在只显示了日期数据。如果清楚了当前日期时间的取得之后，需要清除三个日期公式：**日期+数字=日期（表示若干天之后的日期）；**日期-数字=日期（表示若干天之前的日期）；**日期-日期=数字（天数)范例：实现日期的基本操作SELECT SYSDATE-7,SYSDATE+120 FROM dual;对于日期而言，由于每个月的天数是不同的，所以直接进行天数的计算是不准确的范例：要求查询出每个雇员的编号、姓名、职位、已经被雇佣的天数SELECT empno,ename,job,SYSDATE-hiredate FROM emp;如果直接使用天数来实现年或月的计算，那么最终的结果一定是不准确的为了准确的进行日期操作，在Oracle里面提供了四个日期处理函数：1. 日期 ADD_MONTHS(列|日期，月数) 在指定的日期上增加若干个月后的日期2. 数字 MONTHS_BETWEEN(列|日期，列|日期) 返回两个日期之间所经历的月数3. 日期 LAST_DAY(列|日期) 取得日期所在月的最后一天4. 日期 NEXT_DAY(列|日期，星期X) 返回下一个指定的一周时间数对应的日期范例：在当前时间下增加指定月数SELECT ADD_MONTHS(SYSDATE,4),ADD_MONTHS(SYSDATE,20) FROM dual;范例：计算所有雇员到今天为止雇佣的月数SELECT empno,ename,hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp;范例：计算当前时间所在月的最后一天SELECT LAST_DAY(SYSDATE) FROM dual;范例：要求查询出所有在雇佣所在月倒数第三天雇佣的雇员信息SELECT * FROM emp WHERE hiredate=LAST_DAY(hiredate)-2;范例：验证NEXT_DAY()函数SELECT NEXT_DAY(SYSDATE,'星期三') FROM dual;范例：要求以年、月、日的方式计算出每个雇员到现在为止雇佣年限第一步：求出每一位雇员到现在为止雇佣的年份在整个Oracle里面提供了两种方式来计算年份：方式一：（日期-日期=天数）/365=年方式二：MONTHS_BETWEEN(SYSDATE,hiredate)/12=年第一种方式无法去除掉闰年的情况SELECT empno,ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year FROM emp;第二步：求出雇用的月数SELECT empno,ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year ，TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) months, FROM emp;第三步：求出雇用的天数在Oracle之中提供的计算天数的操作只有一种“日期1-日期2=天数”**日期1：使用SYSDATE，表示当前时间**日期2：由于时间跨度太长了，必须回避掉年和月的问题 ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) SELECT empno,ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year ，TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) months, TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) )) day FROM emp;十二、单行函数----转换函数1 字符串 TO_CHAR(列|日期|数字，转换格式) 将日期或数字转换为指定格式的字符串2 日期 TO_DATE(列|字符串，转换格式) 按照指定格式将字符串转换为日期3 数字 TO_NUMBER(列|字符串) 将字符串转换为数字1.如果要想将一个日期或数字变为字符串，那么首先必须清楚转换格式的标记日期：年(yyyy)、月(mm)、日(dd)时间：时(hh,hh24)、分(mi)、秒(ss)数字：任意数字(9)、本地货币符号(L)范例：将日期显示格式化SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM dual;范例：格式化日期时间SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM dual;使用TO_CHAR()函数有一个最为强悍的功能就是可以实现日期数据的拆分范例：实现日期数据的拆分SELECT TO_CHAR(SYSDATE,'yyyy'),TO_CHAR(SYSDATE,'mm') ,TO_CHAR(SYSDATE,'dd') FROM dual;范例：查询出所有在2月份雇佣的雇员SELECT * FROM emp WHERE TO_CHAR(hiredate,'mm')='02';SELECT * FROM emp WHERE TO_CHAR(hiredate,'mm')='2';Oracle之中提供有自动的转型操作，如果发现类型不匹配会自动的完成除了转换日期之外还能转换数字范例：格式化数字SELECT TO_CHAR(54925239824,'L999,999,999,999,999') From dual;2.转日期函数范例：将字符串变为日期SELECT TO_DATE('1889-10-19','yyyy-mm-dd') FROM dual;3.转数字函数SELECT TO_NUMBER('1')+TO_NUMBER('2') FROM dual;SELECT '1'+'2' FROM dual;十三、单行函数----通用函数通用函数一般都是指的是Oracle特色函数，主要有两个通用函数1 数字 NVL(列|NULL，默认值) 如果传入的是NULL，则使用默认值，如果不是NULL，使用传入值2 数据类型 DECODE（列|数值|字符串，比较内容1，显示内容1，比较内容2，显示内容2，...[,默认 显示]) 设置的内容会与每一个比较内容进行比较，如果内容相同，则会使用显示内容进行输出，如果都不相同，则使用最后的默认1.处理NULL函数范例：要求查询出每个雇员的编号、姓名、基本工资、佣金、年薪SELECT empno.ename,sal,comm,(sal+comm)*12 FROM emp;会发现，没有佣金的员工没有年薪存在，因为佣金为null时所作的计算结果都为null,为了保证计算的准确性，必须将null替换为0,那么就使用NVL()SELECT empno.ename,sal,comm,(sal+NVL(comm))*12 FROM emp;2.decode()函数 类似if...else范例：将所有职位信息替换为中文显示SELECT ename,job DECODE(job,'CLERK','办事员','SALESMAN','销售员','manager','经理','---') FROM emp;
