一个菜鸟的oracle之路-----二

平538

　　今天主要的功课是oracle的select语句，sql语句在上一篇文章
　　查询语句中使用函数

round(**)用于数字的四舍五入。

案例1 计算金额的四舍五入。


select ename,salary*0.1234567 s1,
round(salary*0.1234567,2) s2,
round(salary*0.1234567) s3 from emp_xxx;



案例2 数字函数 trunc(**)

trunc()用于截取，如果没有第二个参数，默认是0
select ename,salary*0.1234567 s1,
round(salary*0.1234567,2) s2,
round(salary*0.1234567) s3,
trunc(salary*0.1234567,2) s4 from emp_xxx;



案例3，日期函数sysdate

获取系统当前时间

select sysdate from dual;

//虚表dual是Oracle用于操作函数的方式，属于

sys用户，共享给所以用户使用，虚表dual是单行

单列的表，表中存放一个常量数据x
select * from dual;



虚表的意义，更方便的操作函数或者查询常量

如下例。表中有多少条数据，常量就会出现多少次
select sysdate from emp_xxx;



//感觉查询结果与真实的有出入

//没有出入，有几条数据则显示几次常量(当前时间)

案例4  日期数据相减

计算员工入职多少天

//我的错误解 select sysdate-hiredate from emp_xxx;

select ename,hiredate,(sysdate-hiredate)days from emp_xxx;

日期数据相减，得到两个日期之间的天数差，不足一天用小数表示，

可以用round函数处理，

select ename, hiredate,round(sysdate-hiredate)days from emp_xxx;

案例5  日期函数 months_between()**

计算员工入职多少个月,保留有小数
select ename,hiredate,
months_between(sysdate,hiredate)months from emp_xxx;



案例6   不保留小数。入职为整数个月
select ename,hiredate,
round(months_between(sysdate,hiredate))months from emp_xxx;



案例7 计算12个月之前的时间点
select add_months(sysdate,-12)from dual;



//暂时未发现有add_year等方法 select add_year(sysdate,-5)from dual;

案例8 日期函数 last_day()*

计算本月最后一天
select last_day(sysdate) from dual;



案例9 转换函数

转换函数 to_char(日期数据，格式)：把日期转化为字符数据

把时间数据按照指定格式输出。

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

案例10 把时间数据按指定格式输出
select to_char(sysdate,'year month dd day dy') from dual;



//输出星期几的时候 用day 用dy也可以

select to_char(sysdate,'year month dd dy') from dual;

案例11 把时间数据按指定格式输出
select to_char(sysdate,'yyyy/mm/dd')from dual;
select to_char(sysdate,'mm/yyyy/dd')from dual;
select to_char(sysdate,'mm/y/dd')from dual;



小结：日期格式

1，常用日期格式

yyyy 四位数字年 如：2011

year 全拼的年  如 twenty eleven

month 全频的月 如 november 或者11月

mm  两位数字月 如 11

mon  简拼的月  如nov，中文没有简拼

dd  两位数字日

day  全拼的星期 如 tuesday

dy  简拼的星期

am  上午/下午 如：am/pm

sqlplus 中的日期默认格式是 dd-mon-rr 日月年。

yy格式是Oracle早起的时间表示方式，存在千年虫问题。

假设现在时间为2011年，05年和98年分别被Oracle解释为

YY    RR

05年  2005   2005

98年  2098   1998

假设现在时间为1998年，05年和98年分别被oracle解释为

YY    RR

05年  1905   2005

98年  1995   1995

案例12，转换函数 to_date()**

插入一条数据，标号为1012，姓名为amy,入职时间为当前系统时间
insert into emp_xxx(empno,ename,hiredate)values(1012,'amy',sysdate);



案例13 插入一条数据，编号为1012，姓名为amy,入职时间是2011年10月10日

insert into emp_xxx(empno,ename,hiredate)values(1012,'amy','10-10月-11');

//该时间格式不符合中国常用时间表示格式

案例14  按指定时间格式插入数据
insert into emp_xxx(empno,ename,hiredate)values(1012,'amy',
to_date('2019-10-10','yyyy-mm-dd'));            
select * from emp_xxx where empno=1012;



案例15.

按指定格式显示员工姓名和入职时间 显示格式为 amy 2011-10-10
select ename,to_char(hiredate,'yyyy-mm-dd')from emp_xxx;



小结：to_date()和to_char()是时间处理函数

to_date 将字符串数据按指定格式转化为日期数据

to_char()将日期数据按指定格式转化为字符串数据

案例16.

函数 coalesce() coalesce(参数列表)函数的作用

返回参数列表中第一个非空参数，参数列表中最后一个值通常为常量

计算员工的年终奖

要求如下：

如果bonus不是null，发年终奖为bonus

如果bonus是null，发年终奖salary*0.5

如果bonus和salary都是null，发100元安慰一下。
  select salary*12+nvl(bonus,0)*12 年薪,ename from emp_xxx
select salary*12+nvl(bonus,0)*12 年薪,ename from emp_xxx



使用nvl函数得到的结果
select ename ,nvl(bonus,nvl(salary,200)*0.5)年终奖 from emp_xxx;



使用coalesce()函数得到的结果
select ename,bonus,salary,coalesce(bonus,salary*0.5,100) bonus from emp_xxx;



//coalesce 函数返回参数列表中第一个非空数据