Oracle 单行函数练习

　　在看完Oracle单行函数之后的练习题，巩固一下知识。

先回顾雇员表表的所有数据

SECLECT * FROM emp;

1、找出每个月倒数第三天受雇的所有员工

　·　在emp表中跟雇佣日期有关的字段是 hiredate,每个雇员有自己的雇佣日期，则每个雇佣日期所在月的最后一天也一定不相同；
　·　如果想要求出某一个雇佣日期所在月的最后一天，则使用LAST_DAY()函数即可。
　·　WHERE 子句的判断条件: hiredate=LAST_DAY(hiredate)-2;

SELECT empno,ename,hiredate,LAST_DAY(hiredate),LAST_DAY(hiredate)-2 FROM emp;

范例：最终实现

SELECT * FROM emp WHERE hiredate=LAST_DAY(hiredate)-2;

2、找出早于12年前受雇的员工

　　· 12年前受雇，肯定要求年，求年最精确的做法是按照 月/12 计算；
　　· 两个日期之间的月使用 MONTHS_BETWEEN()函数完成;
　　· WHERE 子句之中只需要判断计算结果大于12即可;

SELECT * FROM emp WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/12 > 12;

3、找出在(任何年份) 2 月受聘的所有员工

　　· 要想从雇佣日期中取出 月 使用 TO_CHAR() 函数

SELECT * FROM emp WHERE TO_CHAR(hiredate,'mm')='02';

// Oracle支持自动数据类型转换，所以使用2也是可以的
SELECT * FROM emp WHERE TO_CHAR(hiredate,'mm')=2;

4、显示满 10 年服务年限的员工的姓名和受雇日期

SELECT ename,hiredate FROM emp WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/12 > 10;

5、显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面

　　· 最老员工排序使用ORDER BY,按照升序排序

SELECT ename,hiredate FROM emp ORDER BY hiredate ASC;

6、 显示所有员工姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面。

　　· 要想求出年份和月份，使用TO_CHAR()函数；
　　· 按照雇佣所在月排序，可以在 SELECT 子句中为查询的结果设置一个别名(月)；
　　· 月如果相同，则继续增加一个排序的字段，这个字段同样使用一个别名(年)；

SELECT ename,TO_CHAR(hiredate,'yyyy') year,'mm') months FROM emp ORDER By months,year;

7、 显示在一个月为30天的情况所有员工的日薪金，忽略余数

　　· 忽略余数使用 TRUNC() 函数

SELECT ename,TRUNC(sal/30) FROM emp;

8、 以年月日的方式显示所有员工的服务年限

　　例如，现在有一位雇员“JONES”，他的雇佣日期是“02-4月 -81”（1981-04-02），今天的日期是2016-09-04，那么这位员工到今天为止的雇佣年限是：35年，05月，02天。
　　
　　· 求出服务的年限：应该利用： 月/12;

SELECT ename,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year FROM emp;

　　· 求出服务的月：在计算年的时候使用的是 “月/12”,那么除不尽的部分就是月，求余数使用 MOD() 函数；

SELECT
    empno,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year,TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) months
FROM emp ;

　　· 求出剩余的天数；
　　|- 现在唯一知道计算天数的操作公式“日期1 – 日期2 = 天数”，那么现在的问题是这两个日期怎么定？
　　|- 日期1，应该是今天的日期，使用SYSDATE求出，SYSDATE – 日期2 = 天数；
　　|- 日期2，应该将这个日期2由雇员的雇佣日期（hiredate）进行一个提升，提升到与SYSDATE同在一个月的范围（准确来讲在31天之内）内才可以计算。而且提升的日期不应该包含天数。
ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))，把日期提升了。

SELECT
    empno,12)) months,TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) days
FROM emp ;

　　所有的这些操作都是利用函数完成，利用函数可以帮助用户方便的解决一些复杂的计算逻辑，并且代码可以更加的简单。

知识点回顾:

1、单行函数：

　· 字符串函数：
　　 |- 转大写：字符串 UPPER(字符串 | 列);
　　 |- 转小写：字符串 LOWER(字符串 | 列)；
　　 |- 首字母大写：字符串 INITCAP(字符串 | 列);
　　 |- 计算字符串长度：数字 LENGTH(字符串 | 列);
　　 |- 字符串替换：字符串 REPLACE(字符串 | 列,旧的内容,新的内容);
　　 |- 字符串截取：字符串SUBSTR(字符串| 列,截取开始点 [,截取结束点]);
　　 |- 字符串查找：数字 INSTR(字符串 | 列,查找的内容),找不到返回0;

　· 数值函数：
　　 |- 四舍五入：数字 ROUND(数字 | 列);
　　 |- 截取小数：数字 TRUNC(数字 | 列);
　　 |- 求模：数字 MOD(数字 | 列,数字 | 列);

　　· 日期函数：
　　 |- 当前日期取得: SYSDATE
　　 　|- 日期 + 数字 = 日期，表示若干天之后的日期；
　　　 |- 日期 – 数字 = 日期，表示若干天之前的日期；
　　　 |- 日期 – 日期 = 数字，两个日期之间经过的天数。
　　|- 求两个日期之间经历的月数：数字 MONTHS_BETWEEN(日期1 | 列,日期2 | 列);
　　|- 求增加指定月之后的日期：日期 ADD_MONTHS(日期1 | 列,日期2 | 列);
　　|- 求出下一个指定的一周时间数： 日期 NEXT_DAY(日期 | 列,周数);
　　|- 求出指定日期所在月的最后一天：日期 LAST_DAY(日期 | 列);
　
　· 转换函数：NUMBER、VARCHAR2、DATE 三者之间的转换;
　 　|- 将日期或数字变为字符串：字符串 TO_CHAR(日期 | 数字 | 列,转换格式)：
　 　　|- 日期时间数：年(yyyy)、月(mm)、日(dd)、时(hh24)、分(mi)、秒(ss);
　 　　|- 数字表示：每位数字(9)、货币符号(L);
　 　|- 将字符串变为日期：日期 TO_DATE(字符串 | 列,转换格式)；
　 　|- 将字符串变为数字：数字 TO_NUMBER(字符串 | 列)；
　
　· 转换函数： 通用函数：
　　|- 处理NULL：数字 NVL(列,默认值);
　　|- 多值判断：数据类型 DECODE(数值 | 列,判断数值1,显示内容1,…..);

2、多表查询：

　　· 从多张数据表之中同时取出数据一起显示的操作;
　　· 进行多表查询时，一定会产生积（笛卡尔积），消除笛卡尔积的方式是使用关联字段进行等值连接，但是一定记住，任何的表连接查询都需要一个前提：存在关联字段;
　　· 标的连接分为两种：内连接（等值连接）、外连接（左外连接、右外连接、全外连接），使用 “(+)”　表示;
　　· 其他数据库如果需要操作外连接，可以采用 SQL:1999 语法完成。

3、数据库的连接查询：UNION、UNION ALL、INTERSECT、MINUS。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!