Yuens' blog
内容参考《李兴华Oracle数据库》的单行函数部分(链接见文末参考),本节主要包括:[toc]
以上给出的格式执行的顺序:FROM、WHERE、SELECT、ORDER BY(永远都是最后一个执行);
在编写WHERE子句之中可以设置多个判断条件:关系运算符(<、>、>=、<=、=、!=、<>)、逻辑运算符(AND、OR、NOT,若干个条件的连接只能使用AND或OR)、BETWEEN…AND、IN(使用NOT IN的时候不能出现NULL,否则不会有任何的数据返回)、IS NULL、LIKE(LIKE可以使用于各种数据类型,如果没有设置关键字则表示查询全部“%%”)。
ORDER BY子句是唯一可以使用SELECT子句定义的别名的语句。
单行函数的功能非常简单,就是完成某一个功能,例如:现在希望将字母变为大写,这样的功能就可以通过单行函数完成,在Oracle之中提供的单行函数非常的多,今天主要是看核心的几个。
单行函数分类:字符串函数、数字函数、日期函数、转化函数、通用函数。所有的单行函数在进行记忆的时候要记清楚单行函数的名称、返回值类型、接受的参数数据。
字符串函数的主要功能是进行字符串数据处理,下面按照不同的类型来观察函数的使用。
在Oracle之中为方便用户验证函数,专门提供了一个dual的虚拟表,下面将用这张表讲解。
SELECT UPPER(‘Hello’), LOWER(‘Hello’) FROM dual;
在Oracle之中为方便用户进行函数验证,专门提供dual的虚拟表
SELECT LOWER(ename) FROM emp;
通过以上验证,发现UPPER()和LOWER()函数操作的时候是不关心原本的字符串是大写还是小写的,都会按照统一的规则转换,那么就可以实际中利用这两个函数进行一些数据处理,某些系统上某些用户名是不区分大小写的,而这样的功能都是在数据保存的时候将保存数据变为统一的大写或小写。
那么此时首先要解决的问题就是如何实现用户的数据输入,在Oracle数据库里提供了一种输入方法:&。
SELECT * FROM &tablename; -- 要查询的数据表名称由用户输入
那么下面就是利用替代变量输入雇员姓名
SELECT * FROM emp WHERE ename=UPPER(‘&name’);
在工作中若有些系统不区分大小写,那么就要用统一形式保存,使用LOWER()、UPPER()函数进行转换。
SELECT INITCAP(ename) FROM emp;
SELECT LENGTH(‘Hello world’) FROM dual;
SELECT ename, LENGTH(ename) FROM dual;
此时一定要加入WHERE子句来进行数据行的控制,同时还需要用LENGTH判断:
SELECT ename, LENGTH(ename) FROM emp WHERE LENGTH(ename)=5;
SELECT REPLACE(‘hello world’, ‘l’, ‘_’) FROM dual;
SELECT ename, REPLACE(ename, ‘A’, ‘_’) FROM emp;
取得world:
SELECT SUBSTR(‘hello world’, 7) FROM dual;
取得hello:
SELECT SUBSTR(‘hello world’, 0. 5) FROM dual;
SELECT SUBSTR(‘hello world’, 1, 5) FROM dual;
实际在Oracle中,字符串索引下标是从1开始的(程序大多是从0开始),但为程序员的使用习惯,即使设置0,也表示从1开始。这点会由Oracle自己处理。
SELECT ename, SUBSTR(ename, 1, 3) FROM emp;
SELECT ename, SUBSTR(ename, LENGTH(ename)-2) FROM emp;
改善思路:在Oracle设计之初,SUBSTR()函数已经考虑到了右后向前的截取。
SELECT ename, SUBSTR(ename, -3) FROM emp;
面试题:请问Oracle之中的SUBSTR()函数进行截取的时候是从0开始还是从1开始?
使用TRIM函数可以去掉的只是左右空格,但中间的空格是无法取消的。
SELECT TRIM(‘ hello world ’), TRIM(‘ hello world ’) FROM dual;
这类操作在进行数据接受的时候非常有用处。
数字函数主要是针对数字进行的一些小处理。
SELECT ROUND(9615.7652), ROUND(9615.7652, 2), ROUND(9615.7652, -2), ROUND(-15.5) FROM dual; | 结果:9616 |
结果:9615.77 | |
结果:9600 | |
结果:16 | |
如果没有编写保留小数位数,那么就会将小数点之后的数据直接进行进位,不保留小数位。
SELECT TRUNC(9615.7652), TRUNC(9615.7652, 2), TRUNC(9615.7652, -2), TRUNC(-15.5) FROM dual; | è9615 |
è9615.76 | |
è9600 | |
è-15 | |
SELECT MOD(10,3) FROM dual;
以后再工作中,只要见到ROUND这个单词,一定要清楚其在四舍五入。
如果要想操作日期函数,实际上有一个首要的问题需要解决。Oracle里提供两个伪列(实不存在表中的列,但可直接使用的列):SYSDATE、SYSTIMESTAMP。
SELECT SYSDATE FROM dual;
SELECT SYSTIMESTAMP FROM dual;
当有了系统日期之后就可以通过以下的三个公式来进行一些简单的计算:
SELECT SYSDATE + 3, SYSDATE + 120 FROM dual;
SELECT SYSDATE - 120 FROM dual;
计算的时候是按照自然日的天数进行计算,是不会考虑那个月由30天或28天等。
SELECT ename, SYSDATE – hiredate FROM emp;
如果在一个很长的时间内,通过天计算年,并不准确(不确定365、366),所以想准确地计算出天数,需要计算相距不要超过30天。
如果想准确地进行日期时间的操作,那么需要提供的日期函数。
年数的计算最准确地一定是通过月数完成的,一年是不会被改变的。
SELECT ename, MONTHS_BETWEEN(SYSDATE, HIREDATE) MONTHS_BETWEEN(SYSDATE, hiredate)/12 FROM emp;
SELECT ename, MONTHS_BETWEEN(SYSDATE, hiredate), TRUNC(MONTHS_BETWEEN(SYSDATE, hiredate)/12) FROM emp;
SELECT * FROM emp WHERE MONTHS_BETWEEN(SYSDATE, hiredate)/12>33;
SELECT SYSDATE + 120, ADD_MONTHS(SYSDATE, 4), ADD_MONTHS(SYSDATE, 500) FROM dual;
在进行计算的时候,数字的大小是不受限制的。
思考题:要求计算出每一位雇员到今天为止雇佣的年限(理解)
第一步:计算出每一位雇员到今天为止的雇佣年份,直接利用月数除以12
SELECT ename, hiredate, TRUNC(MONTHS_BETWEEN(SYSDATE, hriedate)/12) year FROM emp;
第二步:计算月,在进行年计算的时候发现存在有小数,这些小数就包含了月
SELECT ename, hiredate, TRUNC(MONTHS_BETWEEN(SYSDATE, hriedate)/12) year, TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, hiredate), 12)) months FROM emp;
第三步:计算天数,对于天数的计算实际上只学会一个公式:“日期1 – 日期2 = 天数”,于是分析日期:
SELECT ename, hiredate, TRUNC(MONTHS_BETWEEN(SYSDATE, hriedate)/12) year, TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, hiredate), 12)) months, TRUNC(SYSDATE – ADD_MONTHS(hiredate, MONTHS_BETWEEN(SYSDATE, hriedate))) day FROM emp;
【为何ADD_MONTHS的结果不TRUNC?】
只有日期函数可准确计算出日期,其它直接操作无法进行。
一周时间数:周一,周二等等
SELECT NEXT_DAY(SYSDATE, ‘星期二’) FROM dual;
SELECT LAST_DAY(SYSDATE) FROM dual;
SELECT ename, hiredate, LAST_DAY(hiredate)-2 FROM emp WHERE LAST_DAY(hiredate)-2=hiredate;
所有的日期函数实际上在进行一些统计操作的时候还是有用处的。
目前为止,我们已经接触到Oracle提供的三类数据:字符串、数字、日期,那么这三种数据可通过转换函数实现类型转换。
在进行转换格式设置的时候,要根据不同的数据类型进行格式标记的定义:
SELECT TO_CHAR(SYSDATE, ‘yyyy-mm-dd’) FROM dual; |
2014-08-12 |
SELECT TO_CHAR(SYSDATE, ‘fmyyyy-mm-dd’) FROM dual; |
2014-8-12 |
SELECT TO_CHAR(SYSDATE, ‘yyyy-mm-dd hh24:mi:ss’) FROM dual;
那么,实际上用户也可利用TO_CHAR()函数进行年、月、日数据的拆分。
SELECT TO_CHAR(SYSDATE, ‘yyyy’) year, TO_CHAR(SYSDATE, ‘mm’) months, TO_CHAR(SYSDATE, ‘dd’) day FROM dual;
SELECT * FROM emp WHERE TO_CHAR(hiredate, ‘mm’)=’02’;
使用TO_CHAR()还可以格式化数字显示。
23567823651 = 23,567,823,651,格式化数字任意一位用9表示(多了也没事),L代表本地货币。
SELECT TO_CHAR(23567823651, ‘L999,999,999,999,999’) FROM mep;
SELECT ‘1981-09-15’, TO_DATE(‘1981-09-15’, ‘yyyy-mm-dd’) FROM dual;
但是在Oracle之中提供有自动的转换方式,如果字符串按照日期的格式编写,那么可以自动由字符串变为日期。
SELECT TO_NUMBER(‘1’) + TO_NUMBER(‘2’) FROM dual;
或
SELECT ‘1’ + ‘2’ FROM dual;
在Oracle中这些自动的数据类型转换功能是非常方便的,包括日期和字符串、字符串和数字。
通用函数主要是Oracle的特色函数,有两个:NVL()、DECODE()。
SELECT ename, sal, comm, (sal+comm)*12 FROM emp;
此时,发现有一部分人没有年薪,是因为佣金为NULL。
在数据库中,NULL与任何数据计算都是NULL。那么如果遇到了NULL应按照数字0来处理,那么我们可用NULL来处理。
SELECT ename, sal, comm, (sal+NVL(comm, 0))*12 FROM emp;
SELECT ename, job, DECODE(job, ‘CLERK’, ‘办事员’, ‘SALESMAN’, ‘销售’, ‘MANAGER’, ‘经理’, ‘ANALYST’, ‘分析员’, ‘PRESIDENTIAL’, ‘总裁’, ‘未处理’) FROM emp;
非常类似IF…ELSE…IF语句(或者是SWICTH…CASE)。
若没有给出对应的职位信息的解码信息,那么遇到这条数据时候会给默认输出,若没有给出默认输出,则为NULL。
No. | 方法名称 | 类型 |
1 | 字符串 UPPER(字符串 | 数据列) | 将特定的字符串或者指定的列数据变为大写 |
2 | 字符串 LOWER(字符串 | 数据列) | 将特定的字符串或者指定的列数据变为小写 |
3 | 字符串 INITCAP(字符串 | 列) | 将特定字符串或者指定列的数据的首字母大写,其余字母小写 |
4 | 数字 LENGTH(字符串 | 列) | 计算出指定字符串或者数据列的数据长度 |
5 | 字符串 REPLACE(字符串 | 列,要替换的内容,新的内容) | 将指定字符串或者数据列中的数据按照指定的新内容 |
6 | 字符串 SUBSTR(字符串 | 数据列,截取开始索引) | 由指定位置截取到结尾 |
7 | 字符串 SUBSTR(字符串 | 数据列,截取开始索引,截取结束索引) | 指定截取的开始和结束位置 |
8 | 字符串 TRIM(字符串 | 列) | 去掉左右空格函数 |
9 | 数字 ROUND(数字 | 列 [,保留小数位]) | 四舍五入操作 |
10 | 数字 TRUNC(数字 | 列 [,保留小数位]) | 截取小数 |
11 | 数字 MOD(数字1 | 列1,数字2 | 列2) | 求模(余数) |
12 | 数字MONTHS_BETWEEN(日期1 | 列1,日期2 | 列2) | 计算两个日期之间所经历的月数 |
13 | ADD_MONTHS(日期 | 列,月数) | 在指定日期上增加若干月之后的日期 |
14 | 日期 NEXT_DAY(日期 | 列,一周时间数) | 求出指定的下一个一周时间数的日期 |
15 | 日期 LAST_DAY(日期 | 列) | 求出指定日期所在月的最后一天日期 |
16 | 字符串 TO_CHAR(数字 | 日期 | 列,转换格式) | 转字符串数据 |
17 | 日期 TO_DATE(字符串,转换格式) | 转日期数据 |
18 | 数字 TO_NUMER(字符串,转换格式) | 转数字数据 |
19 | 数字NVL(列 | 数据,默认值) | 处理NULL数据 |
20 | 数据DECODE(字段 | 数据,判断数据1,显示数据1,判断数据2,显示数据2,…[默认显示]) | 多数据判断 |